<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dk.mdm.mapper.mst.SupplierMapper">

    <!-- 通用设置 -->
    <!-- 通用查询列 -->
    <sql id="Base_Column_List">
        sup_id, sup_code, sup_name, sup_type, contact_name,
contact_phone, org_id, staff_id, service_categories,
remarks, flg_valid, cp_id, op_create_time, op_create_user_id,
op_update_time, op_update_user_id, op_app_code, op_timestamp, op_db_user,return_address
    </sql>
<!--    ,taobj.sum_residue-->
    <sql id="msl_Base_Column_List">
        msl.sup_id, msl.sup_code, msl.sup_name,msl.sup_type,msl.service_categories,
        msl.contact_name, msl.contact_phone, msl.org_id, msl.staff_id,
        msl.remarks, msl.flg_valid, msl.cp_id,
        msl.op_create_time, msl.op_create_user_id, msl.op_update_time,
        msl.op_update_user_id, msl.op_app_code, msl.op_timestamp,
        msl.op_db_user, msl.return_address
    </sql>

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.dk.mdm.model.response.mst.SpplierResponse">
        <id column="sup_id" property="supId"/>
                <result column="sup_code" property="supCode"/>
                <result column="sup_name" property="supName"/>
                <result column="sup_type" property="supType"/>
                <result column="contact_name" property="contactName"/>
                <result column="contact_phone" property="contactPhone"/>
                <result column="org_id" property="orgId" typeHandler="UuidTypeHandler"/>
                <result column="staff_id" property="staffId" typeHandler="UuidTypeHandler"/>
                <result column="service_categories" property="serviceCategories" typeHandler="StringListTypeHandler"/>
                <result column="remarks" property="remarks"/>
                <result column="flg_valid" property="flgValid"/>
                <result column="cp_id" property="cpId"/>
        <result column="return_address" property="returnAddress"/>
        <result column="staff_name" property="staffName"/>
        <result column="org_name" property="orgName"/>
                <result column="sum_residue" property="sumResidue"/>
        <result column="op_create_time" property="opCreateTime" typeHandler="TimestampTypeHandler"/>
        <result column="op_create_user_id" property="opCreateUserId" typeHandler="UuidTypeHandler"/>
        <result column="op_update_time" property="opUpdateTime" typeHandler="TimestampTypeHandler"/>
        <result column="op_update_user_id" property="opUpdateUserId" typeHandler="UuidTypeHandler"/>
        <result column="op_app_code" property="opAppCode"/>
        <result column="op_timestamp" property="opTimestamp" typeHandler="TimestampTypeHandler"/>
        <result column="op_db_user" property="opDbUser"/>
        <result column="payable_residue" property="payableResidue"/>
        <result column="payment_residue" property="paymentResidue"/>
    </resultMap>

    <!-- 通用条件列 -->
    <sql id="Condition">
        <where>
            <if test="supCode != null and supCode != ''">
                AND sup_code = #{supCode}
            </if>
            <if test="supName != null and supName != ''">
                AND sup_name  LIKE concat('%',my_ex.likequery(#{supName}),'%')
            </if>
            <if test="supType != null and supType != ''">
                AND sup_type = #{supType}
            </if>
            <if test="contactName != null and contactName != ''">
                AND contact_name = #{contactName}
            </if>
            <if test="contactPhone != null and contactPhone != ''">
                AND contact_phone = #{contactPhone}
            </if>
            <if test="orgId != null and orgId != ''">
                AND org_id = #{orgId}
            </if>
            <if test="staffId != null and staffId != ''">
                AND staff_id = #{staffId}
            </if>
            <if test="serviceCategories != null and serviceCategories != ''">
                AND service_categories = #{serviceCategories}
            </if>
            <if test="remarks != null and remarks != ''">
                AND remarks = #{remarks}
            </if>
            <if test="flgValid != null">
                AND flg_valid = #{flgValid}
            </if>
            <if test="cpId != null">
                AND cp_id = #{cpId}
            </if>
            <if test="opCreateTime != null">
                AND op_create_time = #{opCreateTime}
            </if>
            <if test="opCreateUserId != null and opCreateUserId != ''">
                AND op_create_user_id = #{opCreateUserId}
            </if>
            <if test="opUpdateTime != null">
                AND op_update_time = #{opUpdateTime}
            </if>
            <if test="opUpdateUserId != null and opUpdateUserId != ''">
                AND op_update_user_id = #{opUpdateUserId}
            </if>
            <if test="opAppCode != null and opAppCode != ''">
                AND op_app_code = #{opAppCode}
            </if>
            <if test="opTimestamp != null">
                AND op_timestamp = #{opTimestamp}
            </if>
            <if test="opDbUser != null and opDbUser != ''">
                AND op_db_user = #{opDbUser}
            </if>
        </where>
    </sql>
    <sql id="msl_Condition">
        <where>
            <if test="supCode != null and supCode != ''">
                AND msl.sup_code = #{supCode}
            </if>
            <if test="supName != null and supName != ''">
                AND msl.sup_name  LIKE concat('%', #{supName}, '%')
            </if>
            <if test="supType != null and supType != ''">
                AND msl.sup_type = #{supType}
            </if>
            <if test="contactName != null and contactName != ''">
                AND msl.contact_name = #{contactName}
            </if>
            <if test="contactPhone != null and contactPhone != ''">
                AND msl.contact_phone = #{contactPhone}
            </if>
            <if test="orgId != null and orgId != ''">
                AND msl.org_id = #{orgId}
            </if>
            <if test="staffId != null and staffId != ''">
                AND msl.staff_id = #{staffId}
            </if>
            <if test="serviceCategories != null and serviceCategories != ''">
                AND msl.service_categories = #{serviceCategories}
            </if>
            <if test="remarks != null and remarks != ''">
                AND msl.remarks = #{remarks}
            </if>
            <if test="flgValid != null">
                AND msl.flg_valid = #{flgValid}
            </if>
            <if test="cpId != null">
                AND msl.cp_id = #{cpId}
            </if>
            <if test="opCreateTime != null">
                AND msl.op_create_time = #{opCreateTime}
            </if>
            <if test="opCreateUserId != null and opCreateUserId != ''">
                AND msl.op_create_user_id = #{opCreateUserId}
            </if>
            <if test="opUpdateTime != null">
                AND msl.op_update_time = #{opUpdateTime}
            </if>
            <if test="opUpdateUserId != null and opUpdateUserId != ''">
                AND msl.op_update_user_id = #{opUpdateUserId}
            </if>
            <if test="opAppCode != null and opAppCode != ''">
                AND msl.op_app_code = #{opAppCode}
            </if>
            <if test="opTimestamp != null">
                AND msl.op_timestamp = #{opTimestamp}
            </if>
            <if test="opDbUser != null and opDbUser != ''">
                AND msl.op_db_user = #{opDbUser}
            </if>
            <if test="flgValidList != null and flgValidList.size>0">
                AND msl.flg_valid  =any(#{flgValidList, typeHandler=BooleanListTypeHandler})
            </if>
            <if test="searchText !=null">
                AND ( msl.sup_name LIKE concat('%', #{searchText}, '%')
                or msl.contact_name LIKE concat('%', #{searchText}, '%')
                or msl.contact_phone LIKE concat('%', #{searchText}, '%'))
            </if>
        </where>
    </sql>

    <sql id="idsForeach">
        <!-- 根据主键supId批量操作 -->
        WHERE sup_id in
        <foreach collection="ids" index="index" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </sql>

    <!-- 查询表t_mst_supplier，（条件查询+分页）列表   LEFT JOIN dkic_b.t_acc_object taobj ON taobj.object_id = msl.sup_id -->
    <select id="selectByCond" resultMap="BaseResultMap">
        SELECT
        <include refid="msl_Base_Column_List"/>
        ,tms.staff_name,sys.f_get_name_i18n(tdk.kind_name_i18n, #{i18n} ) AS  "supTypeName"
          ,
        (
        SELECT
        string_agg ( sys.f_get_name_i18n ( tdk1.kind_name_i18n,  #{i18n} ), ',' )
        FROM
        sys.t_data_kind AS tdk1
        WHERE
        tdk1.kind_code = ANY ( msl.service_categories )
        ) AS "serviceCategoriesName",
        (SELECT COALESCE
        ( array_to_string( ARRAY_AGG ( tsm.brand_name ), ',' ), '' )
        FROM
        dkic_b.t_mst_supplier_brand tsmb
        LEFT JOIN dkic_b.t_mst_goods_brand tsm ON tsm.brand_id = tsmb.brand_id
        WHERE
        tsmb.sup_id = msl.sup_id
        ) AS "brandName"
        ,tmo.org_name
        ,tmo.org_code
        ,tma.payable_residue
        ,tma.payment_residue
        FROM dkic_b.t_mst_supplier msl
        LEFT JOIN dkic_b.t_mst_staff  tms   ON tms.staff_id = msl.staff_id
        LEFT JOIN dkic_b.t_mst_org  tmo   ON tmo.org_id = msl.org_id
        LEFT JOIN sys.t_data_kind tdk   ON tdk.kind_code = msl.sup_type
        LEFT JOIN  dkic_b.t_mac_account tma  on tma.object_id = msl.sup_id and tma.object_type in ('对象类型-供应商')
        <include refid="msl_Condition"/>
        ORDER BY  msl.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>

    <!-- 查询表t_mst_supplier，（条件查询）个数 -->
    <select id="countByCond" resultType="Long">
        SELECT
        count(1)
        FROM dkic_b.t_mst_supplier msl
        LEFT JOIN dkic_b.t_mst_staff  tms   ON tms.staff_id = msl.staff_id
        LEFT JOIN dkic_b.t_mst_org  tmo   ON tmo.org_id = msl.org_id
        LEFT JOIN sys.t_data_kind tdk   ON tdk.kind_code = msl.sup_type
        LEFT JOIN  dkic_b.t_mac_account tma  on tma.object_id = msl.sup_id and tma.object_type in ('对象类型-供应商')
        <include refid="msl_Condition"/>
    </select>

    <!-- 根据主键查询表t_mst_supplier的一行数据 -->
    <select id="selectById" resultMap="BaseResultMap">
        SELECT
        <include refid="msl_Base_Column_List"/>
        ,tms.staff_name,sys.f_get_name_i18n(tdk.kind_name_i18n, #{i18n} )AS  "supTypeName"
        ,
        (
        SELECT
        string_agg ( sys.f_get_name_i18n ( tdk1.kind_name_i18n,  #{i18n}), ',' )
        FROM
        sys.t_data_kind AS tdk1
        WHERE
        tdk1.kind_code = ANY ( msl.service_categories )
        ) AS "serviceCategoriesName",
        (SELECT COALESCE
        ( array_to_string( ARRAY_AGG ( tsm.brand_name ), ',' ), '' )
        FROM
        dkic_b.t_mst_supplier_brand tsmb
        LEFT JOIN dkic_b.t_mst_goods_brand tsm ON tsm.brand_id = tsmb.brand_id
        WHERE
        tsmb.sup_id = msl.sup_id
        ) AS "brandNames"
        ,tmo.org_name,
        (SELECT COALESCE
        ( array_to_string( ARRAY_AGG ( tsm.brand_id ), ',' ), '' )
        FROM
        dkic_b.t_mst_supplier_brand tsmb
        LEFT JOIN dkic_b.t_mst_goods_brand tsm ON tsm.brand_id = tsmb.brand_id
        WHERE
        tsmb.sup_id = msl.sup_id
        ) AS "brandIds"
        FROM dkic_b.t_mst_supplier msl
        LEFT JOIN dkic_b.t_mst_staff  tms   ON tms.staff_id = msl.staff_id
        LEFT JOIN dkic_b.t_mst_org  tmo   ON tmo.org_id = msl.org_id
        LEFT JOIN sys.t_data_kind tdk   ON tdk.kind_code = msl.sup_type
        WHERE msl.sup_id = #{id}::uuid
    </select>

    <!-- 根据主键锁定表t_mst_supplier的一行数据 -->
    <select id="selectByIdForUpdate" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_mst_supplier
        WHERE sup_id = #{supId}
        for update
    </select>

    <!-- 根据主键锁定表t_mst_supplier的多行数据 -->
    <select id="selectByIdsForUpdate" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_mst_supplier
        <include refid="idsForeach"/>
        for update
    </select>
    <select id="selectUuid" resultType="java.lang.String">
        select  sys.f_uuid_seq()

    </select>
    <select id="selectSupCode" resultType="java.lang.String">
        select  dkic_b.f_create_code( #{cpId},#{supId}::uuid,'供应商')
    </select>

    <insert id="insertBatch">
        insert into t_mst_supplier
        (
        <trim suffixOverrides=",">
            sup_code,
            sup_name,
            sup_type,
            contact_name,
            contact_phone,
            org_id,
            staff_id,
            service_categories,
            remarks,
            cp_id,
            op_app_code,
        </trim>
        )
        values
        <foreach collection="list" index="index" item="item" separator=",">
            (
            <trim suffixOverrides=",">
                #{item.supCode},
                #{item.supName},
                #{item.supType},
                #{item.contactName},
                #{item.contactPhone},
                #{item.orgId}::uuid,
                #{item.staffId}::uuid,
                #{item.serviceCategories},
                #{item.remarks},
                #{item.cpId},
                #{item.opAppCode},
            </trim>
            )
        </foreach>
    </insert>
    <insert id="insertSupplierBrandtBatch">
        insert into dkic_b.t_mst_supplier_brand
        (
        <trim suffixOverrides=",">
            sup_id,
            brand_id
        </trim>
        )
        values
        <foreach collection="list" index="index" item="item" separator=",">
            (
            <trim suffixOverrides=",">
                #{item.supId}::uuid,
                #{item.brandId}::uuid,
            </trim>
            )
        </foreach>
    </insert>
    <insert id="insertSupplierBrand">
        insert into dkic_b.t_mst_supplier_brand
        (
            sup_id,
            brand_id
        )
        values
            (
                #{supId}::uuid,
                #{brandId}::uuid
            )
    </insert>
    <delete id="batchDeleteBrand">
        DELETE FROM dkic_b.t_mst_supplier_brand
        WHERE sup_id =  #{supId}::uuid;
    </delete>
</mapper>
