<?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.ruoyi.ibs.grid.mapper.RegionCustUserMapper">

    <select id="selectCustByCode" resultType="CustVO">
        SELECT '0' as cust_type, ci.cust_name, ci.cust_id, ci.region_code as code
        FROM cust_info_retail_${dept} ci
        WHERE ci.region_code in
        <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
            #{code}
        </foreach>
        UNION ALL
        SELECT '2' as cust_type, ci.cust_name, ci.cust_id, ci.region_code as code
        FROM cust_info_business_${dept} ci
        WHERE ci.region_code in
        <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
            #{code}
        </foreach>
        UNION ALL
        SELECT '1' as cust_type, ci.cust_name, ci.cust_id, ci.region_code as code
        FROM cust_info_merchant_${dept} ci
        WHERE ci.region_code in
        <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
            #{code}
        </foreach>
    </select>

    <select id="selectPrivateCustByCode" resultType="CustVO">
        SELECT '0' as cust_type, ci.cust_name, ci.cust_id, ci.region_code as code
        FROM cust_info_retail_${dept} ci
        WHERE ci.region_code in
        <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
            #{code}
        </foreach>
        UNION ALL
        SELECT '1' as cust_type, ci.cust_name, ci.cust_id, ci.region_code as code
        FROM cust_info_merchant_${dept} ci
        WHERE ci.region_code in
        <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
            #{code}
        </foreach>
    </select>

    <select id="selectPublicCustByCode" resultType="CustVO">
        SELECT '2' as cust_type, ci.cust_name, ci.cust_id, ci.region_code as code
        FROM cust_info_business_${dept} ci
        WHERE ci.region_code in
        <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
            #{code}
        </foreach>
    </select>

    <insert id="batchInsert">
        INSERT INTO grid_region_cust_user_${dept} (top_grid_id, top_grid_name, sec_grid_id, code,
        ops_dept, creator, creator_id, creator_name, create_dept_id, create_dept_name,
        cust_id, cust_name, cust_type, user_names, nick_names, branch_ids, branch_names, outlet_ids, outlet_names)
        VALUES
        <foreach collection="custList" item="cust" separator=",">
            (#{cust.topGridId},#{cust.topGridName},#{cust.secGridId},#{cust.code},#{cust.opsDept},
            #{cust.creator},#{cust.creatorId},#{cust.creatorName},#{cust.createDeptId},#{cust.createDeptName},
            #{cust.custId},#{cust.custName},#{cust.custType},#{cust.userNames},#{cust.nickNames},#{cust.branchIds},#{cust.branchNames},#{cust.outletIds},#{cust.outletNames})
        </foreach>
    </insert>

    <update id="batchUpdate">
        UPDATE grid_region_cust_user_${dept}
        SET sec_grid_id = #{secGridId}, sec_grid_name = #{secGridName},user_names = #{userNames}, nick_names = #{nickNames},
            outlet_ids = #{outletIds}, outlet_names = #{outletNames}
        WHERE code IN
        <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
            #{code}
        </foreach>
        AND top_grid_id = #{topGridId}

    </update>

    <delete id="batchDeleteTopGrid">
        DELETE
        FROM grid_region_cust_user_${dept}
        WHERE top_grid_id = #{gridId}
    </delete>

    <update id="batchDeleteSecGrid">
        UPDATE grid_region_cust_user_${dept}
        SET sec_grid_id = null, sec_grid_name = null, user_names = null, nick_names = null, outlet_ids = null, outlet_names = null
        WHERE sec_grid_id = #{gridId}
    </update>

</mapper>