<?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.CustInfoMapper">
    <select id="countBusinessCustByCode" resultType="int">
        SELECT COUNT(*) FROM cust_info_business
        WHERE
        <foreach collection="codeList" item="code" index="index" open="(" separator="OR" close=")"  >
            region_code LIKE CONCAT(#{code}, '%')
        </foreach>

    </select>

    <select id="countRetailCustByCode" resultType="int">
        SELECT COUNT(*) FROM cust_info_retail
        WHERE
        <foreach collection="codeList" item="code" index="index" open="(" separator="OR" close=")"  >
            region_code LIKE CONCAT(#{code}, '%')
        </foreach>

    </select>

    <select id="countMerchantCustByCode" resultType="int">
        SELECT COUNT(*) FROM cust_info_merchant
        WHERE
        <foreach collection="codeList" item="code" index="index" open="(" separator="OR" close=")">
            region_code LIKE CONCAT(#{code}, '%')
        </foreach>

    </select>

    <select id="selectRegionCustByCode" resultType="RegionCustVO">
        SELECT '个人' as cust_type, cust_name, cust_id
        FROM cust_info_retail
        WHERE region_code LIKE CONCAT(#{code}, '%')
        UNION ALL
        SELECT '企业' as cust_type, cust_name, cust_id
        FROM cust_info_business
        WHERE region_code LIKE CONCAT(#{code}, '%')
        UNION ALL
        SELECT '商户' as cust_type, cust_name, cust_id
        FROM cust_info_merchant
        WHERE region_code LIKE CONCAT(#{code}, '%')
    </select>

    <select id="selectRegionRetailByCode" resultType="RegionCustVO">
        SELECT '个人' as cust_type, cust_name, cust_id
        FROM cust_info_retail
        WHERE region_code LIKE CONCAT(#{code}, '%')
    </select>
    <select id="selectRegionBusinessByCode" resultType="RegionCustVO">
        SELECT '企业' as cust_type, cust_name, cust_id
        FROM cust_info_business
        WHERE region_code LIKE CONCAT(#{code}, '%')
    </select>
    <select id="selectRegionMerchantByCode" resultType="RegionCustVO">
        SELECT '商户' as cust_type, cust_name, cust_id
        FROM cust_info_merchant
        WHERE region_code LIKE CONCAT(#{code}, '%')
    </select>

    <select id="selectCustByCode" resultType="ShapeCustVO">
        SELECT '0' as cust_type, ci.cust_name, ci.cust_id, iaa.longitude, iaa.latitude, iaa.address_name, iaa.province,
        iaa.city, iaa.county, iaa.street, iaa.community, iaa.address_detail
        FROM cust_info_retail_${dept} ci
        LEFT JOIN ibs_anchor_${dept} ia on ci.cust_id = ia.cust_id
        LEFT JOIN ibs_anchor_address_${dept} iaa on ia.address_id = iaa.id
        WHERE ia.address_id != 0
        <if test="codeList != null and codeList.size() > 0">
            and ci.region_code in
            <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
                #{code}
            </foreach>
        </if>
        UNION ALL
        SELECT '2' as cust_type, ci.cust_name, ci.cust_id, iaa.longitude, iaa.latitude, iaa.address_name, iaa.province,
        iaa.city, iaa.county, iaa.street, iaa.community, iaa.address_detail
        FROM cust_info_business_${dept} ci
        LEFT JOIN ibs_anchor_${dept} ia on ci.cust_id = ia.cust_id
        LEFT JOIN ibs_anchor_address_${dept} iaa on ia.address_id = iaa.id
        WHERE ia.address_id != 0
        <if test="codeList != null and codeList.size() > 0">
            and ci.region_code in
            <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
                #{code}
            </foreach>
        </if>
        UNION ALL
        SELECT '1' as cust_type, ci.cust_name, ci.cust_id, iaa.longitude, iaa.latitude, iaa.address_name, iaa.province,
        iaa.city, iaa.county, iaa.street, iaa.community, iaa.address_detail
        FROM cust_info_merchant_${dept} ci
        LEFT JOIN ibs_anchor_${dept} ia on ci.cust_id = ia.cust_id
        LEFT JOIN ibs_anchor_address_${dept} iaa on ia.address_id = iaa.id
        WHERE ia.address_id != 0
        <if test="codeList != null and codeList.size() > 0">
        and ci.region_code in
            <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
                #{code}
            </foreach>
        </if>
    </select>

    <select id="selectPrivateCustByCode" resultType="ShapeCustVO">
        SELECT '0' as cust_type, ci.cust_name, ci.cust_id, iaa.longitude, iaa.latitude, iaa.address_name, iaa.province,
        iaa.city, iaa.county, iaa.street, iaa.community, iaa.address_detail
        FROM cust_info_retail_${dept} ci
        LEFT JOIN ibs_anchor_${dept} ia on ci.cust_id = ia.cust_id
        LEFT JOIN ibs_anchor_address_${dept} iaa on ia.address_id = iaa.id
        WHERE ia.address_id != 0
        <if test="codeList != null and codeList.size() > 0">
            and ci.region_code in
            <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
                #{code}
            </foreach>
        </if>
        UNION ALL
        SELECT '1' as cust_type, ci.cust_name, ci.cust_id, iaa.longitude, iaa.latitude, iaa.address_name, iaa.province,
        iaa.city, iaa.county, iaa.street, iaa.community, iaa.address_detail
        FROM cust_info_merchant_${dept} ci
        LEFT JOIN ibs_anchor_${dept} ia on ci.cust_id = ia.cust_id
        LEFT JOIN ibs_anchor_address_${dept} iaa on ia.address_id = iaa.id
        WHERE ia.address_id != 0
        <if test="codeList != null and codeList.size() > 0">
            and ci.region_code in
            <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
                #{code}
            </foreach>
        </if>
    </select>

    <select id="selectPublicCustByCode" resultType="ShapeCustVO">
        SELECT '2' as cust_type, ci.cust_name, ci.cust_id, iaa.longitude, iaa.latitude, iaa.address_name, iaa.province,
        iaa.city, iaa.county, iaa.street, iaa.community, iaa.address_detail
        FROM cust_info_business_${dept} ci
        LEFT JOIN ibs_anchor_${dept} ia on ci.cust_id = ia.cust_id
        LEFT JOIN ibs_anchor_address_${dept} iaa on ia.address_id = iaa.id
        WHERE ia.address_id != 0
        <if test="codeList != null and codeList.size() > 0">
            and ci.region_code in
            <foreach collection="codeList" item="code" index="index" open="(" separator="," close=")">
                #{code}
            </foreach>
        </if>
    </select>
    
    <insert id="insertToRetailByVirtualCustDTO">
        INSERT INTO cust_info_retail_${dept}
        (cust_id,cust_name, cust_idc, update_by, update_time, cust_phone)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.custId},#{item.custName}, #{item.custIdn}, #{updateBy}, #{updateTime}, #{item.phoneNum})
        </foreach>
    </insert>
    <insert id="insertToMerchantByVirtualCustDTO">
        INSERT INTO cust_info_merchant_${dept}
        (cust_id,cust_name, social_credit_code, update_by, update_time, cust_phone)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.custId},#{item.custName}, #{item.custIdn}, #{updateBy}, #{updateTime}, #{item.phoneNum})
        </foreach>
    </insert>
    <insert id="insertToBusinessByVirtualCustDTO">
        INSERT INTO cust_info_business_${dept}
        (cust_id,cust_name, social_credit_code, update_by, update_time, cust_phone)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.custId},#{item.custName}, #{item.custIdn}, #{updateBy}, #{updateTime}, #{item.phoneNum})
        </foreach>
    </insert>
    <select id="selectExistCustByCustId" resultType="String">
        select cust_id from
        <if test="custType == 0">cust_info_retail_${dept}</if>
        <if test="custType == 1">cust_info_merchant_${dept}</if>
        <if test="custType == 2">cust_info_business_${dept}</if>
        where find_in_set(cust_id ,#{custIds});
    </select>
</mapper>