<?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.VirtualGridMapper">

    <select id="getGridList" parameterType="VirtualGridListDTO" resultType="VirtualGridListVO">
        SELECT distinct a.* , CONCAT(u.nick_name, '-',a.create_by ) as creator FROM grid_virtual_grid a
        <if test="relateUserName != null  and relateUserName != ''">
            LEFT JOIN grid_virtual_user b on a.grid_id = b.grid_id
        </if>
        left join grid_virtual_cust_user c on c.grid_id = a.grid_id
        left join sys_user u on u.user_name = a.create_by
        WHERE a.delete_flag = '0'
        <if test="opsDept != null  and opsDept != ''">and a.ops_dept = #{opsDept}</if>
        AND (a.dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
            or c.relate_dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or
            find_in_set(#{deptId},ancestors))
        )
        <if test="gridType != null  and gridType != ''">and a.grid_type = #{gridType}</if>
        <if test="gridDutyType != null  and gridDutyType != ''">and a.grid_duty_type = #{gridDutyType}</if>
        <if test="gridName != null  and gridName != ''">AND a.grid_name like concat('%', #{gridName}, '%')</if>
        <if test="relateUserName != null  and relateUserName != ''">
            and b.delete_flag = '0' AND b.user_name like concat('%', #{relateUserName}, '%')
        </if>
        <if test="custType != null  and custType != ''">
            and case when #{custType} ='0' then a.has_retail = '1'
            when #{custType} ='2' then a.has_business = '1'
            when #{custType} ='1' then a.has_retail_business = '1'
            end
        </if>
        ORDER BY a.update_time DESC
    </select>

    <select id="getGridListByManager" parameterType="VirtualGridListDTO" resultType="VirtualGridListVO">
        SELECT a.* ,CONCAT(u.nick_name, '-',a.create_by ) as creator FROM grid_virtual_grid a
        <if test="relateUserName != null  and relateUserName != ''">LEFT JOIN grid_virtual_user c on a.grid_id =
            c.grid_id
        </if>
        left join sys_user u on u.user_name = a.create_by
        WHERE a.delete_flag = '0'
        AND exists( SELECT 1 from grid_virtual_cust_user b
        where b.grid_id = a.grid_id and b.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 = b.cust_id and c.user_name = #{userName} and c.delete_flag = '0'
        )
        )
        <if test="gridType != null  and gridType != ''">and a.grid_type = #{gridType}</if>
        <if test="gridDutyType != null  and gridDutyType != ''">and a.grid_duty_type = #{gridDutyType}</if>
        <if test="gridName != null  and gridName != ''">AND a.grid_name like concat('%', #{gridName}, '%')</if>
        <if test="relateUserName != null  and relateUserName != ''">and c.delete_flag = '0' AND c.user_name like
            concat('%', #{relateUserName}, '%')
        </if>
        <if test="deptType != null  and deptType != ''">
            and case when #{deptType} = '0' then a.dept_type ='head' else a.dept_type ='branch' end
        </if>
        <if test="custType != null  and custType != ''">
            and case when #{custType} ='0' then a.has_retail = '1'
            when #{custType} ='2' then a.has_business = '1'
            when #{custType} ='1' then a.has_retail_business = '1'
            else 1 = 1 end
        </if>
        ORDER BY a.update_time DESC
    </select>


    <select id="getVirtualGridNumByDeptId" parameterType="Long" resultType="java.lang.Long">
        SELECT count(distinct a.grid_id) FROM grid_virtual_grid a
        left join grid_virtual_cust_user c on c.grid_id = a.grid_id
        WHERE a.delete_flag = '0'
        <if test="opsDept != null  and opsDept != ''">and a.ops_dept = #{opsDept}</if>
        AND (a.dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
        or c.relate_dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or
        find_in_set(#{deptId},ancestors)))
    </select>

    <select id="getVirtualGridNumByUserId" parameterType="String" resultType="java.lang.Long">
        SELECT count(distinct a.grid_id)
        FROM grid_virtual_grid a
        WHERE a.delete_flag = '0'
          AND exists(SELECT 1
                     from grid_virtual_cust_user b
                     where b.grid_id = a.grid_id
                       and b.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 = b.cust_id
                                        and c.user_name = #{userName}
                                        and c.delete_flag = '0'))
    </select>

    <select id="searchVirtualGrid" resultType="VirtualGridVO">
        SELECT distinct a.* FROM grid_virtual_grid a
        LEFT JOIN grid_virtual_cust_user c on a.grid_id = c.grid_id
        WHERE a.delete_flag = '0' and a.ops_dept = #{opsDept}
        <if test="gridName != null  and gridName != ''">AND a.grid_name like concat('%', #{gridName}, '%')</if>
        AND (a.dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
        or c.relate_dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or
        find_in_set(#{deptId},ancestors)))
    </select>

</mapper>