<?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.VirtualCustMapper">
    
    <resultMap type="VirtualCust" id="GridVirtualCustResult">
        <result property="id"    column="id"    />
        <result property="gridId"    column="grid_id"    />
        <result property="custType"    column="cust_type"    />
        <result property="custId"    column="cust_id"    />
        <result property="custIdType"    column="cust_id_type"    />
        <result property="custIdn"    column="cust_idn"    />
        <result property="custIsn"    column="cust_isn"    />
        <result property="custName"    column="cust_name"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="deleteFlag"    column="delete_flag"    />
    </resultMap>

    <sql id="selectGridVirtualCustVo">
        select id, grid_id, cust_type, cust_id, cust_id_type, cust_idn, cust_isn, cust_name, create_by, create_time, update_by, update_time, delete_flag from grid_virtual_cust
    </sql>

    <select id="getCustNumByGridId"  resultType="java.lang.Long">
        select count(distinct a.cust_id)
        from grid_virtual_cust a
        where a.grid_id = #{gridId}
          and a.cust_type = #{custType}
    </select>

    <select id="getCustNumByGridIdAndDept" resultType="java.lang.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.relate_dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},
        ancestors))
        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.user_name = a.user_name
        and c.delete_flag = '0');
    </select>

    <select id="getCustNumByVitualDeptId"  resultType="java.lang.Long">
        select count(distinct a.cust_id) from grid_virtual_cust a
        inner join  grid_virtual_cust_user b
        on a.grid_id =b.grid_id
        where (b.relate_dept_id in
               (select dept_id from sys_dept where dept_id = #{headDeptId} or find_in_set(#{headDeptId},ancestors)) or b.user_name = #{headDeptId})
          and a.cust_type = #{custType} and a.delete_flag = '0'
    </select>

    <select id="getCustNumByDeptId" resultType="java.lang.Long">
        select count(distinct a.cust_id) from grid_virtual_cust a
        inner join grid_virtual_cust_user b on a.grid_id =b.grid_id
        left join grid_virtual_grid c on a.grid_id = c.grid_id
        where (b.relate_dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or
        find_in_set(#{deptId},ancestors)) or b.user_name = #{deptId})
        and a.cust_type = #{custType} and a.delete_flag = '0' and c.delete_flag = '0'
        <if test="opsDept != null  and opsDept != ''">and c.ops_dept = #{opsDept}</if>
    </select>

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

</mapper>