<?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.draw.mapper.DrawGridMapper">
    <resultMap id="DrawGridListVO" type="DrawGridListVO">
        <result property="gridId" column="grid_id"/>
        <result property="gridName" column="grid_name"/>
        <result property="layerId" column="layer_id"/>
        <result property="gridDutyType" column="grid_duty_type"/>
        <result property="deptId" column="dept_id"/>
        <result property="deptName" column="dept_name"/>
    </resultMap>

    <select id="getGridList" parameterType="DrawGridListDTO" resultMap="DrawGridListVO">
        SELECT distinct d.*, CONCAT(su.nick_name, '-',d.create_by) as creator FROM grid_draw_grid d
        left join grid_draw_user_relate u on d.grid_id = u.grid_id
        left join sys_user su on su.user_name = d.create_by
        WHERE d.delete_flag = '0' and u.delete_flag = '0'
        AND (d.dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
        or (u.relate_dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or
        find_in_set(#{deptId},ancestors)))
        )
        <if test="gridName != null  and gridName != ''">AND d.grid_name like concat('%', #{gridName}, '%')</if>
        <if test="gridDutyType != null  and gridDutyType != ''">AND d.grid_duty_type = #{gridDutyType}</if>
        <if test="layerId != null  and layerId != ''">AND d.layer_id = #{layerId}</if>
        <if test="opsDept != null  and opsDept != ''">AND d.ops_dept = #{opsDept}</if>
        <if test="relateDeptName != null  and relateDeptName != ''">
            AND EXISTS(
            SELECT 1 FROM grid_draw_user_relate a WHERE a.delete_flag = '0' AND a.grid_id = d.grid_id AND
            a.relate_dept_name like concat('%', #{relateDeptName}, '%')
            )
        </if>
        <if test="relateUserName != null and relateUserName != ''">
            AND EXISTS(
            SELECT 1 FROM grid_draw_user_relate b WHERE b.delete_flag = '0' AND b.grid_id = d.grid_id AND b.user_name
            like concat('%', #{relateUserName}, '%')
            )
        </if>
    </select>

    <select id="getGridListByManager" parameterType="DrawGridListDTO" resultMap="DrawGridListVO">
        SELECT d.*, CONCAT(su.nick_name, '-',d.create_by) as creator FROM grid_draw_user_relate u
        LEFT JOIN (SELECT * FROM grid_draw_grid WHERE delete_flag = '0') d ON u.grid_id = d.grid_id
        left join sys_user su on su.user_name = d.create_by
        WHERE u.user_name = #{userName}
        AND u.delete_flag = '0'
        <if test="gridName != null  and gridName != ''">AND d.grid_name like concat('%', #{gridName}, '%')</if>
        <if test="gridDutyType != null  and gridDutyType != ''">AND d.grid_duty_type = #{gridDutyType}</if>
        <if test="layerId != null  and layerId != ''">AND d.layer_id = #{layerId}</if>
        <if test="opsDept != null  and opsDept != ''">AND d.ops_dept = #{opsDept}</if>
    </select>

    <select id="getGridCount" resultType="Long">
        SELECT count(distinct d.grid_id)
        from grid_draw_grid d
                 left join grid_draw_user_relate u on d.grid_id = u.grid_id
        WHERE d.delete_flag = '0'
          and u.delete_flag = '0'
        <if test="opsDept != null  and opsDept != ''">AND d.ops_dept = #{opsDept}</if>
          AND (d.dept_id in
               (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId}, ancestors))
            or u.relate_dept_id in
               (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId}, ancestors))
            )
    </select>

    <select id="getGridCountByManager" resultType="Long">
        SELECT count(distinct grid_id)
        from grid_draw_user_relate
        WHERE delete_flag = '0'
          and user_name = #{userName}
    </select>

    <select id="getCustCount" resultType="Long">
        select count(distinct a.cust_id)
        from draw_shape_cust a
                 left join grid_draw_shape_relate b on a.shape_id = b.shape_id
                 left join grid_draw_user_relate c on b.grid_id = c.grid_id
                 left join grid_draw_grid d on b.grid_id = d.grid_id
        where b.delete_flag = '0'
          and c.delete_flag = '0'
          and d.delete_flag = '0'
        <if test="opsDept != null  and opsDept != ''">AND d.ops_dept = #{opsDept}</if>
        and a.cust_type = #{custType}
          and c.relate_dept_id in
              (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId}, ancestors));
    </select>

    <select id="getCustCountByManager" resultType="Long">
        select count(distinct a.cust_id)
        from draw_shape_cust a
                 left join grid_draw_shape_relate b on a.shape_id = b.shape_id
                 left join grid_draw_user_relate c on b.grid_id = c.grid_id
        where b.delete_flag = '0'
          and c.delete_flag = '0'
          and a.cust_type = #{custType}
          and c.user_name = #{userName};
    </select>

    <select id="getGridListByOpsAndLayer" parameterType="LayerGridDTO" resultType="DrawGrid">
        select d.*
        from grid_draw_grid d
                 left join grid_draw_user_relate u on d.grid_id = u.grid_id
        WHERE d.delete_flag = '0'
          and u.delete_flag = '0'
          AND d.layer_id = #{layerId}
          and d.ops_dept = #{opsDept}
          AND (d.dept_id in
               (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId}, ancestors))
            or (u.relate_dept_id in
                (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId}, ancestors))))
    </select>
</mapper>