<?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.cmpm.mapper.GridCmpmMapper">
    <select id="selectManageList" resultType="GridCmpm">
        select *
        from grid_cmpm_${gridType}
        where user_name is not null
            <if test="branchId != null">and branch_id = #{branchId}</if>
            <if test="outletId != null">and outlet_id = #{outletId}</if>
            <if test="curUserName != null and curUserName !='' ">and user_name = #{curUserName}</if>
            <if test="custId != null and custId !='' ">and cust_id like concat('%',#{custId},'%') </if>
            <if test="custName != null and custName !='' ">and cust_name like concat('%',#{custName},'%')</if>
            <if test="userName != null and userName !='' ">and user_name like concat('%',#{userName},'%')</if>
            <if test="custTypes != null and custTypes.size() > 0 ">
                and cust_type in
                <foreach item="custType" collection="custTypes" open="(" separator="," close=")">
                    #{custType}
                </foreach>
            </if>
            <if test="custLevels != null and custLevels.size() > 0 ">
                and cust_level in
                <foreach item="custLevel" collection="custLevels" open="(" separator="," close=")">
                    #{custLevel}
                </foreach>
            </if>
        order by cust_level desc, user_name
    </select>

    <select id="selectCommonList" resultType="GridCmpm">
        select *
        from grid_cmpm_${gridType} gc
        where gc.user_name is null
        and not exists (
            select 1 from grid_cmpm_transfer
            where transfer_status != '3' and transfer_status != '-1'
            and cust_id = gc.cust_id
            <if test="gridType == 'corporate_account'">
                and account_no = gc.account_no
            </if>
        )
        <if test="branchId != null">and (gc.branch_id = #{branchId} or gc.branch_id is null)</if>
        <if test=" custId != null and custId !='' ">and gc.cust_id like concat('%',#{custId},'%') </if>
        <if test="custName != null and custName !='' ">and gc.cust_name like concat('%',#{custName},'%')</if>
        <if test="userName != null and userName !='' ">and gc.user_name like concat('%',#{userName},'%')</if>
        <if test="custTypes != null and custTypes.size() > 0 ">
            and gc.cust_type in
            <foreach item="custType" collection="custTypes" open="(" separator="," close=")">
                #{custType}
            </foreach>
        </if>
        <if test="custLevels != null and custLevels.size() > 0 ">
            and gc.cust_level in
            <foreach item="custLevel" collection="custLevels" open="(" separator="," close=")">
                #{custLevel}
            </foreach>
        </if>
        order by gc.cust_level desc
    </select>

    <select id="getCustLevel" resultType="String">
        select cust_level from grid_cmpm_${gridType}
        <where>
            <if test="custId != null and custId !='' ">and cust_id = #{custId}</if>
            <if test="accountNo != null and accountNo !='' ">and account_no = #{accountNo}</if>
        </where>
    </select>



    <select id="getGridCmpmByCustKey" resultType="GridCmpm">
        select * from grid_cmpm_${gridType}_${deptCode} where cust_id = #{custId}
        <if test="gridType == 'corporate_account'">
            and account_no = #{accountNo}
        </if>
    </select>

    <select id="queryGridCmpmByCustKey" resultType="GridCmpm">
        select * from grid_cmpm_${gridType}_${deptCode}
        where cust_id in
            <foreach item="custId" collection="custIds" open="(" separator="," close=")">
                #{custId}
            </foreach>
        <if test="accountNos != null and accountNos.size() > 0'">
            and account_no in
            <foreach item="accountNo" collection="accountNos" open="(" separator="," close=")">
                #{accountNo}
            </foreach>
        </if>

    </select>

    <select id="getGridCmpmByUserName" resultType="GridCmpm">
        select * from grid_cmpm_${gridType}_${deptCode} gc
        where gc.user_name = #{userName}
        and not exists (
            select 1 from grid_cmpm_transfer
            where (transfer_status != '3' and transfer_status != '-1')
            and cust_id = gc.cust_id
            <if test="gridType == 'corporate_account'">
                and account_no = gc.account_no
            </if>
        )
    </select>

    <update id="updateGridCmpm">
        update grid_cmpm_${gridType}_${deptCode}
        set user_name = #{userName},
        nick_name = #{nickName},
        outlet_id = #{outletId},
        outlet_name = #{outletName},
        branch_id = #{branchId},
        branch_name = #{branchName},
        source = '手动指定',
        lock_flag = '1'
        where cust_id = #{custId}
        <if test="gridType == 'corporate_account'">
            and account_no = #{accountNo}
        </if>
    </update>

    <select id="getAllCustLevel" resultType="String">
        select distinct cust_level from grid_cmpm_${gridType} where cust_level is not null order by cust_level;
    </select>

    <select id="queryClaimList" resultType="GridCmpmClaimVO">
        select gc.cust_name, gc.cust_type, gc.cust_id, gc.account_no, gc.user_name, gc.nick_name
        from grid_cmpm_${gridType}_${deptCode} gc
        where gc.cust_id like concat('%',#{custKey},'%') or gc.account_no like concat('%',#{custKey},'%') or gc.cust_name like concat('%',#{custKey},'%')
    </select>

    <select id="getCustManagerList" resultType="DwbRetailCustLevelManagerDetailVO">
        select id, outlet_id, outlet_name, branch_id, branch_name, cust_name, cust_idc, cust_isn, cust_age, cust_sex, cust_phone, cust_address, cust_aum_bal,
            aum_bal_comp_lm, cust_aum_month_avg, cust_level, cust_level_comp_lm, manager_name, manager_id
        from dwb_retail_cust_level_manager_detail
        <where>
            <if test="managerId != null and managerId !='' ">and manager_id = #{managerId}</if>
            <if test="outletId != null and outletId !='' ">and outlet_id = #{outletId}</if>
            <if test="branchId != null and branchId !='' ">and branch_id = #{branchId}</if>
        </where>
    </select>

    <select id="getCustLevelCount" resultType="Integer">
        select count(1)
        from dwb_retail_cust_level_manager_detail
        <where>
            <if test="custLevel != null and custLevel !='' ">and cust_level = #{custLevel}</if>
            <if test="managerId != null and managerId !='' ">and manager_id = #{managerId}</if>
            <if test="outletId != null and outletId !='' ">and outlet_id = #{outletId}</if>
            <if test="branchId != null and branchId !='' ">and branch_id = #{branchId}</if>
        </where>
    </select>

    <select id="selectCustInfoFromGridCmpm" resultType="CustBaseInfo">
        select a.* from
        <if test="custPattern == 0">
            cust_info_retail a
        </if>
        <if test="custPattern == 1">
            cust_info_merchant a
        </if>
        <if test="custPattern == 2">
            cust_info_business a
        </if>
        where a.cust_id in (
        <if test="custPattern == 0">
            select distinct cust_id from grid_cmpm_retail
            where user_name = #{cmpmUserName}
            <if test="custLevel != null and custLevel !='' ">and cust_level = #{custLevel}</if>
        </if>
        <if test="custPattern == 1">
            select distinct cust_id from grid_cmpm_merchant
            where user_name = #{cmpmUserName}
            <if test="custLevel != null and custLevel !='' ">and cust_level = #{custLevel}</if>
        </if>
        <if test="custPattern == 2">
            select distinct cust_id from grid_cmpm_corporate
            where user_name = #{cmpmUserName}
            <if test="custLevel != null and custLevel !='' ">and cust_level = #{custLevel}</if>
            union all
            select distinct cust_id from grid_cmpm_corporate_account
            where user_name = #{cmpmUserName}
            <if test="custLevel != null and custLevel !='' ">and cust_level = #{custLevel}</if>
        </if>

        )
        <if test="custId != null and custId !='' ">and a.cust_id like concat('%',#{custId},'%') </if>
        <if test="custName != null and custName !='' ">and a.cust_name like concat('%',#{custName},'%')</if>
        <if test="searchStr != null  and searchStr != ''"> and (a.cust_id like concat('%', #{searchStr}, '%') or a.cust_name like concat('%', #{searchStr}, '%')) </if>
        <if test="regionTopGridIds != null and regionTopGridIds.size() > 0 ">
            AND a.cust_id IN (
            SELECT cust_id
            FROM grid_region_cust_user grcu
            <where>
                <if test="regionTopGridIds != null and regionTopGridIds.size()>0">
                    <foreach collection="regionTopGridIds" item="param" separator="or">
                        grcu.top_grid_id = #{param.value}
                    </foreach>
                </if>
                )
            </where>
        </if>
        <if test="regionSecGridIds != null and regionSecGridIds.size()>0">
            AND a.cust_id IN (
            SELECT cust_id
            FROM grid_region_cust_user grcu
            <where>
                <foreach collection="regionSecGridIds" item="param" separator="or">
                    grcu.sec_grid_id = #{param.value}
                </foreach>
            </where>
            )
        </if>
        <if test="virtualGridIds != null and virtualGridIds.size() > 0 ">
            AND a.cust_id IN (
            SELECT cust_id
            FROM grid_virtual_cust_user gvcu
            <where>
                <foreach collection="virtualGridIds" item="param" separator="or">
                    gvcu.grid_id = #{param.value}
                </foreach>
            </where>
            )
        </if>
        <if test="drawGridIds != null and drawGridIds.size() > 0 ">
            AND a.cust_id IN (
            SELECT dsc.cust_id
            FROM grid_draw_user_relate gdur
            INNER JOIN grid_draw_shape_relate gdsr  ON gdur.grid_id = gdsr.grid_id
            INNER JOIN draw_shape_cust dsc ON gdsr.shape_id = dsc.shape_id
            <where>
                <foreach collection="drawGridIds" item="param" separator="or">
                    gdur.grid_id = #{param.value}
                </foreach>
            </where>
            )
        </if>
        <if test="continuousParams != null and continuousParams.size() > 0 or discreteParams != null and discreteParams.size() > 0">
            <if test="continuousParams != null and continuousParams.size() > 0">
                and
                <foreach collection="continuousParams" item="param" separator="and">
                    <choose>
                        <when test="param.value[0] != null and param.value[0] != '' and param.value[1] != null and param.value[1] != ''">
                            CAST(${param.key} AS DECIMAL(10,2)) BETWEEN CAST(#{param.value[0]} AS DECIMAL(10,2)) AND CAST(#{param.value[1]} AS DECIMAL(10,2))
                        </when>
                        <when test="param.value[0] != null and param.value[0] != ''">
                            CAST(${param.key} AS DECIMAL(10,2)) &gt;=  CAST(#{param.value[0]} AS DECIMAL(10,2))
                        </when>
                        <when test="param.value[1] != null and param.value[1] != ''">
                            CAST(${param.key} AS DECIMAL(10,2)) &lt;=  CAST(#{param.value[1]} AS DECIMAL(10,2))
                        </when>
                    </choose>
                </foreach>
            </if>
            <if test="discreteParams != null and discreteParams.size() > 0">
                and
                <foreach collection="discreteParams" item="param" separator="and">
                    ${param.key} = #{param.value}
                </foreach>
            </if>
        </if>
        <if test="custTag != null  and custTag != ''"> and
            ((CASE WHEN substr(#{custTag}, 1, 1) = '1' THEN substr(a.cust_tag, 1, 1) = '1' end)
            OR (CASE WHEN substr(#{custTag}, 2, 1) = '1' THEN substr(a.cust_tag, 2, 1) = '1' end)
            OR (CASE WHEN substr(#{custTag}, 3, 1) = '1' THEN substr(a.cust_tag, 3, 1) = '1' end))
        </if>
        <if test="custScale != null  and custScale != ''">
            and ( a.cust_scale = '5'
            <if test="
                custScale0 != null  and custScale0 != ''">or a.cust_scale = #{custScale0}</if>
            <if test="
                custScale1 != null  and custScale1 != ''">or a.cust_scale = #{custScale1}</if>
            <if test="
                custScale2 != null  and custScale2 != ''">or a.cust_scale = #{custScale2}</if>
            <if test="
                custScale3 != null  and custScale3 != ''">or a.cust_scale = #{custScale3}</if>
            )
        </if>
        limit #{start},#{size}
    </select>


</mapper>