<?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.VirtualCustUserMapper">
    
    <resultMap type="VirtualCustUser" id="GridVirtualCustUserResult">
        <result property="id"    column="id"    />
        <result property="gridId"    column="grid_id"    />
        <result property="custId"    column="cust_id"    />
        <result property="userName"    column="user_name"    />
        <result property="nickName"    column="nick_name"    />
        <result property="relateDeptId"    column="relate_dept_id"    />
        <result property="relateDeptName"    column="relate_dept_name"    />
        <result property="relateFlag"    column="relate_flag"    />
    </resultMap>

    <resultMap type="CustManageInfo" id="CustManageInfoResult">
        <result property="custId"    column="cust_id"    />
        <result property="custName"    column="cust_name"    />
        <result property="custNo"    column="cust_idn"    />
        <result property="belongUserId"    column="user_name"    />
        <result property="belongUserName"    column="nick_name"    />
        <result property="custPattern"    column="cust_type"    />
        <result property="virtualGridName"    column="grid_name"    />
        <result property="virtualGridDutyType"    column="grid_duty_type"    />
        <result property="createRole"    column="dept_type"    />
        <result property="belongBranchName"    column="branch_name"    />
        <result property="belongOutletName"    column="outlet_name"    />
    </resultMap>

    <resultMap id="VirtualGridCustVO" type="VirtualGridCustVO">
        <result property="gridId" column="grid_id"/>
        <result property="gridName" column="grid_name"/>
        <result property="createDeptName" column="create_dept_name"/>
        <result property="gridDutyType" column="grid_duty_type"/>
        <result property="custId" column="cust_id"/>
        <result property="custIdn" column="cust_idn"/>
        <result property="custName" column="cust_name"/>
        <result property="custType" column="cust_type"/>
        <result property="userName" column="user_name"/>
        <result property="nickName" column="nick_name"/>
        <result property="unbindFlag" column="unbind_flag"/>
    </resultMap>


    <sql id="selectGridVirtualCustUserVo">
        select id, grid_id, cust_id, user_name, nick_name, relate_dept_id, relate_dept_name, relate_flag from grid_virtual_cust_user
    </sql>

    <select id="selectVirtualCustInfoByManage" parameterType="CustManageInfo"  resultMap="CustManageInfoResult">
        select distinct
        b.cust_id,b.cust_name,b.cust_idn,a.user_name,a.nick_name,b.cust_type,c.grid_name,c.grid_duty_type,c.dept_type,d.dept_name
        as branch_name,e.dept_name as outlet_name
        from grid_virtual_cust_user a
        inner join grid_virtual_cust b on a.grid_id = b.grid_id and a.cust_id = b.cust_id
        inner join grid_virtual_grid c on a.grid_id = c.grid_id
        left join (select distinct dept_id, dept_name, dept_type from sys_dept) d on a.relate_dept_id = d.dept_id and
        d.dept_type = 'branch'
        left join (select distinct dept_id, dept_name, dept_type from sys_dept) e on a.relate_dept_id = e.dept_id and
        e.dept_type = 'outlet'
        where a.grid_id = #{gridId}
        <if test="custPattern != null  and custPattern != ''">
            and b.cust_type = #{custPattern}
        </if>
        <if test="custId != null  and custId != ''"> and a.cust_id like concat('%', #{custId}, '%') </if>
        <if test="custName != null  and custName != ''"> and b.cust_name like concat('%', #{custName}, '%') </if>
        <if test="custNo != null  and custNo != ''"> and b.cust_idn like concat('%', #{custNo}, '%') </if>
        <if test="distributeTag != null  and distributeTag != ''"> and a.relate_flag = #{distributeTag} </if>
    </select>

    <select id="getCustList" parameterType="GridCustListDTO" resultMap="VirtualGridCustVO">
        select a.grid_id , b.grid_name, c.dept_name as create_dept_name, b.grid_duty_type ,f.*, d.cust_type, a.user_name, a.nick_name, concat(su.nick_name, '-', b.create_by) as creator,
        (select count(0) from grid_virtual_cust_user_unbind e where e.grid_id = a.grid_id and e.cust_id = a.cust_id and
        e.user_name = a.user_name and e.delete_flag = '0') as unbind_flag
        from grid_virtual_cust_user a
        left join (select * from grid_virtual_grid where delete_flag = '0') b on a.grid_id = b.grid_id
        left join sys_dept c on b.dept_id = c.dept_id
        left join (select * from grid_virtual_cust where delete_flag = '0') d on a.cust_id = d.cust_id and a.grid_id =
        d.grid_id
        left join sys_user su on su.user_name = b.create_by
        <if test="custType == 0">
            left join (select cust_id,cust_name,cust_idc as cust_idn,cust_isn,cust_phone as lp_tel,cust_gender from cust_info_retail)f
            on a.cust_id = f.cust_id
        </if>
        <if test="custType == 1">
            left join (select cust_id,cust_name,social_credit_code as cust_idn,cust_idc as lp_idc,status,lp_name,cust_phone as lp_tel from cust_info_merchant)f
            on a.cust_id = f.cust_id
        </if>
        <if test="custType == 2">
            left join (select cust_id,cust_name,social_credit_code as cust_idn,cust_idc as lp_idc,status,lp_name,cust_phone as lp_tel from cust_info_business)f
            on a.cust_id = f.cust_id
        </if>
        where
        a.grid_id = #{gridId}
        and a.relate_dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or
        find_in_set(#{deptId},ancestors))
        <if test="custId != null  and custId != ''">AND a.cust_id like concat('%', #{custId}, '%')</if>
        <if test="custName != null  and custName != ''">AND d.cust_name like concat('%', #{custName}, '%')</if>
        <if test="custType != null  and custType != ''">AND d.cust_type = #{custType}</if>
    </select>

    <select id="getCustListByManager" parameterType="GridCustListDTO" resultMap="VirtualGridCustVO">
        select a.grid_id , b.grid_name, c.dept_name as create_dept_name, b.grid_duty_type , f.* , d.cust_type, a.user_name, a.nick_name, concat(su.nick_name, '-', b.create_by) as creator
        from grid_virtual_cust_user a
        left join (select * from grid_virtual_grid where delete_flag = '0') b on a.grid_id = b.grid_id
        left join sys_user su on su.user_name = b.create_by
        left join sys_dept c on b.dept_id = c.dept_id
        left join (select * from grid_virtual_cust where delete_flag = '0') d on a.cust_id = d.cust_id and a.grid_id =
        d.grid_id
        <if test="custType == 0">
            left join (select cust_id,cust_name,cust_idc as cust_idn,cust_isn,cust_phone as lp_tel,cust_gender from cust_info_retail)f
            on a.cust_id = f.cust_id
        </if>
        <if test="custType == 1">
            left join (select cust_id,cust_name,social_credit_code as cust_idn,cust_idc as lp_idc,status,lp_name,cust_phone as lp_tel from cust_info_merchant)f
            on a.cust_id = f.cust_id
        </if>
        <if test="custType == 2">
            left join (select cust_id,cust_name,social_credit_code as cust_idn,cust_idc as lp_idc,status,lp_name,cust_phone as lp_tel from cust_info_business)f
            on a.cust_id = f.cust_id
        </if>
        where
        a.user_name = #{userName}
        and a.grid_id = #{gridId}
        and not exists(
        select 1 from grid_virtual_cust_user_unbind e where e.grid_id = a.grid_id and e.cust_id = a.cust_id and
        e.user_name = a.user_name and e.delete_flag = '0'
        )
        <if test="custId != null  and custId != ''">AND a.cust_id like concat('%', #{custId}, '%')</if>
        <if test="custName != null  and custName != ''">AND d.cust_name like concat('%', #{custName}, '%')</if>
        <if test="custType != null  and custType != ''">AND d.cust_type = #{custType}</if>
    </select>

    <select id="getCountByGridIdAndUserName" resultType="Long">
        select COUNT(distinct a.cust_id)
        from grid_virtual_cust_user a
                 left join (select * from grid_virtual_cust where delete_flag = '0') b
                           on a.cust_id = b.cust_id and a.grid_id = b.grid_id
        where a.user_name = #{userName}
          and a.grid_id = #{gridId}
          and b.cust_type = #{custType}
          and not exists(select 1
                         from grid_virtual_cust_user_unbind c
                         where c.grid_id = a.grid_id
                           and a.cust_id = c.cust_id
                           and c.delete_flag = '0');

    </select>

    <select id="getUnrelateCustList" parameterType="GridCustListDTO" resultMap="VirtualGridCustVO">
        select a.grid_id , b.grid_name, c.dept_name as create_dept_name, b.grid_duty_type , f.* , d.cust_type, a.user_name, a.nick_name
        from grid_virtual_cust_user a
        left join (select * from grid_virtual_grid where delete_flag = '0') b on a.grid_id = b.grid_id
        left join sys_dept c on b.dept_id = c.dept_id
        left join (select * from grid_virtual_cust where delete_flag = '0') d on a.cust_id = d.cust_id and a.grid_id =
        d.grid_id
        <if test="custType == 0">
            left join (select cust_id,cust_name,cust_idc as cust_idn,cust_isn,cust_phone as lp_tel,cust_gender from cust_info_retail)f
            on a.cust_id = f.cust_id
        </if>
        <if test="custType == 1">
            left join (select cust_id,cust_name,social_credit_code as cust_idn,cust_idc as lp_idc,status,lp_name,cust_phone as lp_tel from cust_info_merchant)f
            on a.cust_id = f.cust_id
        </if>
        <if test="custType == 2">
            left join (select cust_id,cust_name,social_credit_code as cust_idn,cust_idc as lp_idc,status,lp_name,cust_phone as lp_tel from cust_info_business)f
            on a.cust_id = f.cust_id
        </if>
        where a.grid_id = #{gridId} and a.user_name is null and a.relate_dept_id is null
        <if test="custId != null  and custId != ''">AND a.cust_id like concat('%', #{custId}, '%')</if>
        <if test="custName != null  and custName != ''">AND d.cust_name like concat('%', #{custName}, '%')</if>
        <if test="custType != null  and custType != ''">AND d.cust_type = #{custType}</if>
    </select>

    <select id="selectVirtualCustUserList" resultMap="GridVirtualCustUserResult">
        select id, grid_id, cust_id, user_name, nick_name, relate_dept_id, relate_dept_name from grid_virtual_cust_user
        <where>
            <if test="gridId != null and gridId != ''">
                and grid_id = #{gridId}
            </if>
            <if test="custId != null and custId != ''">
                and cust_id = #{custId}
            </if>
            <if test="relateDeptId != null and relateDeptId != ''">
                and relate_dept_id in (select dept_id from sys_dept where dept_id = #{relateDeptId} or find_in_set(#{relateDeptId},ancestors))
            </if>
        </where>

    </select>


</mapper>