<?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.RegionGridMapper">
    <select id="getSelectedRegionCodes" resultType="String">
        select distinct r.code
        from grid_region_admin_division_relate r
        left join grid_region_grid g
        on r.grid_id = g.grid_id
        where g.dept_id like concat( left(#{deptId}, 3), '%')
        and g.grid_level = #{gridLevel}
        and g.ops_dept = #{opsDept}
        <if test="gridId != null  and gridId != ''">and g.grid_id != #{gridId}</if>
        and g.delete_flag = '0'
        and r.delete_flag = '0'
    </select>

    <select id="getDeptListByRegionCode" resultType="Long">
        select distinct a.relate_dept_id
        from grid_region_user_relate a
                 left join grid_region_admin_division_relate b on a.grid_id = b.grid_id
        where a.relate_type = '1'
          and a.delete_flag = '0'
          and b.delete_flag = '0'
          and b.code = #{regionCode}
          and b.ops_dept = #{opsDept}
    </select>

    <select id="getUserListByRegionCode" resultType="String">
        select distinct concat(a.nick_name, '-', a.user_name)
        from grid_region_user_relate a
                 left join grid_region_admin_division_relate b on a.grid_id = b.grid_id
        where a.relate_type = '1'
          and a.delete_flag = '0'
          and b.delete_flag = '0'
          and b.code = #{regionCode}
          and b.ops_dept = #{opsDept}
    </select>

    <select id="getUserUnbindList" resultType="RegionUnbindVo">
        select a.grid_id,
               a.user_name,
               a.nick_name,
               a.relate_dept_name,
               (select count(*)
                from grid_region_cust_user_unbind d
                where d.grid_id = a.grid_id
                  and d.user_name = a.user_name
                  and d.cust_id = #{custId}
                  and d.delete_flag = '0') as unbind_flag
        from grid_region_user_relate a
        where a.relate_type = '1'
          and a.delete_flag = '0'
          and a.grid_id = #{gridId}
    </select>

    <select id="getSecGridIdByCode" resultType="RegionGrid">
        select a.*
        from grid_region_grid a
                 left join grid_region_admin_division_relate b on b.grid_id = a.grid_id
        where b.parent_grid_id = #{gridId}
          and b.code = #{code}
          and b.delete_flag = '0'
          and a.delete_flag = '0'
    </select>

    <select id="getRetailCust" parameterType="GridCustListDTO" resultType="RegionGridCustVO">
        select a.cust_id,a.cust_name,a.cust_idc as cust_idn,a.region_code,'0' as cust_type from cust_info_retail a
        where a.region_code in (select distinct code from grid_region_admin_division_relate where grid_id = #{gridId} and delete_flag = '0')
        <if test="custName != null  and custName != ''">and a.cust_name like concat('%', #{custName}, '%')</if>
        <if test="custId != null  and custId != ''">and a.cust_id like concat('%', #{custId}, '%')</if>
    </select>


    <select id="getRetailCustFromTopByManager" parameterType="GridCustListDTO" resultType="RegionGridCustVO">
        select a.cust_id,a.cust_name,a.cust_idc as cust_idn,a.region_code,'0' as cust_type, concat(d.town,'-',d.village) as region_name from cust_info_retail a
        left join (select distinct code,grid_id, parent_grid_id from grid_region_admin_division_relate where delete_flag
        = '0' ) b on b.code =a.region_code
        left join (select grid_id,parent_grid_id,relate_dept_id,user_name,nick_name from grid_region_user_relate where
        delete_flag ='0') c on c.grid_id = b.grid_id
        left join grid_admin_division d on d.code = a.region_code
        where c.parent_grid_id = #{gridId} and c.user_name = #{userName}
        <if test="custName != null  and custName != ''">and a.cust_name like concat('%', #{custName}, '%')</if>
        <if test="custId != null  and custId != ''">and a.cust_id like concat('%', #{custId}, '%')</if>
    </select>

    <select id="getRetailCustFromSecByManager" parameterType="GridCustListDTO" resultType="RegionGridCustVO">
        select a.cust_id,a.cust_name,a.cust_idc as cust_idn,a.region_code,'0' as cust_type from cust_info_retail a
        where a.region_code in (
            select distinct code from grid_region_admin_division_relate a
            left join grid_region_user_relate b on a.grid_id = b.grid_id
            where a.delete_flag = '0' and b.delete_flag  = '0' and b.user_name = #{userName} and a.grid_id = #{gridId}
        )
        <if test="custName != null  and custName != ''">and a.cust_name like concat('%', #{custName}, '%')</if>
        <if test="custId != null  and custId != ''">and a.cust_id like concat('%', #{custId}, '%')</if>
    </select>

    <select id="getMerchantCust" parameterType="GridCustListDTO" resultType="RegionGridCustVO">
        select a.cust_id,a.cust_name,a.social_credit_code as cust_idn,a.region_code,'1' as cust_type from cust_info_merchant a
        where a.region_code in (select distinct code from grid_region_admin_division_relate where grid_id = #{gridId} and delete_flag = '0')
        <if test="custName != null  and custName != ''">and a.cust_name like concat('%', #{custName}, '%')</if>
        <if test="custId != null  and custId != ''">and a.cust_id like concat('%', #{custId}, '%')</if>
    </select>

    <select id="getMerchantCustFromTopByManager" parameterType="GridCustListDTO" resultType="RegionGridCustVO">
        select a.cust_id,a.cust_name,a.social_credit_code as cust_idn,a.region_code,'1' as cust_type , concat(d.town,'-',d.village) as region_name  from cust_info_merchant a
        left join (select distinct code,grid_id, parent_grid_id from grid_region_admin_division_relate where delete_flag
        = '0' ) b on b.code =a.region_code
        left join (select grid_id,parent_grid_id,relate_dept_id,user_name,nick_name from grid_region_user_relate where
        delete_flag ='0') c on c.grid_id = b.grid_id
        left join grid_admin_division d on d.code = a.region_code
        where c.parent_grid_id = #{gridId} and c.user_name = #{userName}
        <if test="custName != null  and custName != ''">and a.cust_name like concat('%', #{custName}, '%')</if>
        <if test="custId != null  and custId != ''">and a.cust_id like concat('%', #{custId}, '%')</if>
    </select>

    <select id="getMerchantCustFromSecByManager" parameterType="GridCustListDTO" resultType="RegionGridCustVO">
        select a.cust_id,a.cust_name,a.social_credit_code as cust_idn,a.region_code,'1' as cust_type from cust_info_merchant a
        where a.region_code in (
            select distinct code from grid_region_admin_division_relate a
            left join grid_region_user_relate b on a.grid_id = b.grid_id
            where a.delete_flag = '0' and b.delete_flag  = '0' and b.user_name = #{userName} and a.grid_id = #{gridId}
        )
        <if test="custName != null  and custName != ''">and a.cust_name like concat('%', #{custName}, '%')</if>
        <if test="custId != null  and custId != ''">and a.cust_id like concat('%', #{custId}, '%')</if>
    </select>

    <select id="getBusinessCust" parameterType="GridCustListDTO" resultType="RegionGridCustVO">
        select a.cust_id,a.cust_name,a.social_credit_code as cust_idn,a.region_code,'2' as cust_type from cust_info_business a
        where a.region_code in (select distinct code from grid_region_admin_division_relate where grid_id = #{gridId} and delete_flag = '0')
        <if test="custName != null  and custName != ''">and a.cust_name like concat('%', #{custName}, '%')</if>
        <if test="custId != null  and custId != ''">and a.cust_id like concat('%', #{custId}, '%')</if>
    </select>

    <select id="getBusinessCustFromTopByManager" parameterType="GridCustListDTO" resultType="RegionGridCustVO">
        select a.cust_id,a.cust_name,a.social_credit_code as cust_idn,a.region_code,'2' as cust_type, concat(d.town,'-',d.village) as region_name
        from cust_info_business a
        left join (select distinct code,grid_id, parent_grid_id from grid_region_admin_division_relate where delete_flag
        = '0' ) b on b.code =a.region_code
        left join (select grid_id,parent_grid_id,relate_dept_id,user_name,nick_name from grid_region_user_relate where
        delete_flag ='0') c on c.grid_id = b.grid_id
        left join grid_admin_division d on d.code = a.region_code
        where c.parent_grid_id = #{gridId} and c.user_name = #{userName}
        <if test="custName != null  and custName != ''">and a.cust_name like concat('%', #{custName}, '%')</if>
        <if test="custId != null  and custId != ''">and a.cust_id like concat('%', #{custId}, '%')</if>
    </select>

    <select id="getBusinessCustFromSecByManager" parameterType="GridCustListDTO" resultType="RegionGridCustVO">
        select a.cust_id,a.cust_name,a.social_credit_code as cust_idn,a.region_code,'2' as cust_type from cust_info_business a
        where a.region_code in (
            select distinct code from grid_region_admin_division_relate a
            left join grid_region_user_relate b on a.grid_id = b.grid_id
            where a.delete_flag = '0' and b.delete_flag  = '0' and b.user_name = #{userName} and a.grid_id = #{gridId}
        )
        <if test="custName != null  and custName != ''">and a.cust_name like concat('%', #{custName}, '%')</if>
        <if test="custId != null  and custId != ''">and a.cust_id like concat('%', #{custId}, '%')</if>
    </select>

    <select id="getTopGridList" parameterType="RegionGridListDTO" resultType="RegionGridListVO">
        select distinct a.grid_name , a.grid_duty_type , a.grid_id, a.dept_id , b.dept_name as create_dept_name,
        a.ops_dept, CONCAT(e.nick_name, '-',a.create_by) as creator, a.update_time from grid_region_grid a
        left join sys_dept b on a.dept_id = b.dept_id
        left join sys_user e on a.create_by = e.user_name
        left join grid_region_user_relate c on c.grid_id = a.grid_id
        <if test="relateBranchName != null  and relateBranchName != ''">left join grid_region_user_relate d on d.grid_id
            = a.grid_id
        </if>
        where a.delete_flag = '0' and a.grid_level = '1' and c.delete_flag = '0'
        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="opsDept != null  and opsDept != ''">and a.ops_dept = #{opsDept}</if>
        <if test="gridDutyType != null  and gridDutyType != ''">and a.grid_duty_type = #{gridDutyType}</if>
        <if test="relateBranchName != null  and relateBranchName != ''">
            and d.delete_flag = '0' and d.relate_dept_type = 'branch'
            and d.relate_dept_name like concat('%',#{relateBranchName}, '%')
        </if>
        <if test="gridName != null  and gridName != ''">and a.grid_name like concat('%',#{gridName}, '%')</if>
        ORDER BY a.update_time DESC
    </select>
    <select id="getSecGridList" parameterType="RegionGridListDTO" resultType="RegionGridListVO">
        select distinct a.grid_name ,a.parent_grid_id, a.grid_duty_type , a.grid_id, a.dept_id , b.dept_name as
        create_dept_name, CONCAT(c.nick_name, '-',a.create_by) as creator, d.grid_name as parent_grid_name, a.ops_dept,
        a.update_time
        from grid_region_grid a
        left join sys_dept b on a.dept_id = b.dept_id
        left join sys_user c on a.create_by = c.user_name
        left join grid_region_grid d on a.parent_grid_id = d.grid_id
        left join grid_region_user_relate h on h.grid_id = a.grid_id
        <if test="relateBranchName != null  and relateBranchName != ''">left join grid_region_user_relate e on e.grid_id
            = a.grid_id
        </if>
        <if test="relateOutletName != null  and relateOutletName != ''">left join grid_region_user_relate f on f.grid_id
            = a.grid_id
        </if>
        <if test="relateUserName != null  and relateUserName != ''">left join grid_region_user_relate g on g.grid_id =
            a.grid_id
        </if>
        where a.delete_flag = '0' and a.grid_level = '2' and h.delete_flag = '0'
        and (a.dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId}, ancestors))
        or h.relate_dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},
        ancestors))
        )
        <if test="opsDept != null  and opsDept != ''">and a.ops_dept = #{opsDept}</if>
        <if test="gridDutyType != null  and gridDutyType != ''">and a.grid_duty_type = #{gridDutyType}</if>
        <if test="relateBranchName != null  and relateBranchName != ''">
            and e.delete_flag = '0' and e.relate_dept_type = 'branch'
            and e.relate_dept_name like concat('%',#{relateBranchName}, '%')
        </if>
        <if test="relateOutletName != null  and relateOutletName != ''">
            and f.delete_flag = '0' and f.relate_dept_type = 'outlet'
            and f.relate_dept_name like concat('%',#{relateOutletName}, '%')
        </if>
        <if test="relateUserName != null  and relateUserName != ''">
            and g.delete_flag = '0' and g.relate_type = '1'
            and g.user_name like concat('%',#{relateUserName}, '%')
        </if>
        <if test="gridName != null  and gridName != ''">and a.grid_name like concat('%',#{gridName}, '%')</if>
        <if test="parentGridName != null  and parentGridName != ''">and d.grid_name like concat('%',#{parentGridName},
            '%')
        </if>
        ORDER BY a.update_time DESC
    </select>

    <select id="getSecGridListByManager" parameterType="RegionGridListDTO" resultType="RegionGridListVO">
        select distinct a.grid_name ,a.parent_grid_id, a.grid_duty_type , a.grid_id, a.dept_id , b.dept_name as
        create_dept_name, CONCAT(c.nick_name, '-',a.create_by) as creator, d.grid_name as parent_grid_name, a.ops_dept,
        a.update_time
        from grid_region_grid a
        left join sys_dept b on a.dept_id = b.dept_id
        left join sys_user c on a.create_by = c.user_name
        left join grid_region_grid d on a.parent_grid_id = d.grid_id
        left join grid_region_user_relate h on h.grid_id = a.grid_id
        <if test="relateBranchName != null  and relateBranchName != ''">left join grid_region_user_relate e on e.grid_id
            = a.grid_id
        </if>
        <if test="relateOutletName != null  and relateOutletName != ''">left join grid_region_user_relate f on f.grid_id
            = a.grid_id
        </if>
        <if test="relateUserName != null  and relateUserName != ''">left join grid_region_user_relate g on g.grid_id =
            a.grid_id
        </if>
        where a.delete_flag = '0' and a.grid_level = '2' and h.delete_flag = '0'
        and h.user_name = #{userName}
        <if test="opsDept != null  and opsDept != ''">and a.ops_dept = #{opsDept}</if>
        <if test="gridDutyType != null  and gridDutyType != ''">and a.grid_duty_type = #{gridDutyType}</if>
        <if test="relateBranchName != null  and relateBranchName != ''">
            and e.delete_flag = '0' and e.relate_dept_type = 'branch'
            and e.relate_dept_name like concat('%',#{relateBranchName}, '%')
        </if>
        <if test="relateOutletName != null  and relateOutletName != ''">
            and f.delete_flag = '0' and f.relate_dept_type = 'outlet'
            and f.relate_dept_name like concat('%',#{relateOutletName}, '%')
        </if>
        <if test="relateUserName != null  and relateUserName != ''">
            and g.delete_flag = '0' and g.relate_type = '1'
            and g.user_name like concat('%',#{relateUserName}, '%')
        </if>
        <if test="gridName != null  and gridName != ''">and a.grid_name like concat('%',#{gridName}, '%')</if>
        <if test="parentGridName != null  and parentGridName != ''">and d.grid_name like concat('%',#{parentGridName},
            '%')
        </if>
        ORDER BY a.update_time DESC
    </select>

    <select id="getGridCountByDeptId" resultType="Long">
        select count(DISTINCT a.grid_id)
        from grid_region_grid a
                 left join grid_region_user_relate 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.grid_level = #{gridLevel}
          and c.delete_flag = '0'
          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="getSecGridCountByUserName" resultType="Long">
        select count(distinct a.grid_id)
        from grid_region_grid a
                 left join grid_region_user_relate b on b.grid_id = a.grid_id
        where a.delete_flag = '0'
          and a.grid_level = '2'
          and b.delete_flag = '0'
          and b.user_name = #{userName}
    </select>

    <select id="getCodeByDeptId" resultType="String">
        select distinct b.code
        from grid_region_admin_division_relate b
                 left join grid_region_grid a on b.grid_id = a.grid_id
                 left join grid_region_user_relate c on c.grid_id = a.grid_id
        where a.delete_flag = '0'
          and a.grid_level = '1'
          and c.delete_flag = '0'
          and b.delete_flag = '0'
        <if test="opsDept != null  and opsDept != ''">and b.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="getCodeByOutletId" resultType="String">
        select distinct b.code
        from grid_region_admin_division_relate b
                 left join grid_region_grid a on b.grid_id = a.grid_id
                 left join grid_region_user_relate c on c.grid_id = a.grid_id
        where a.delete_flag = '0'
          and a.grid_level = '2'
          and c.delete_flag = '0'
          and b.delete_flag = '0'
          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="selectTopGridByName" resultType="RegionGrid">
        select * from grid_region_grid grg
        left join grid_region_user_relate grur on grur.grid_id = grg.grid_id
        where grur.relate_type = 2 and grur.relate_dept_id = #{deptId} and grg.grid_name = #{gridName};
    </select>

    <select id="getRegionNamesByGridId" resultType="String">
        select concat(b.town, '-', b.village)  from grid_region_admin_division_relate a
        left join grid_admin_division b on a.code = b.code
        where a.delete_flag = '0' and a.grid_id = #{gridId}
    </select>

    <select id="getSecGridIdByTopGridId" resultType="Long">
        select grid_id from grid_region_grid where parent_grid_id = #{gridId} and grid_level = '2' and delete_flag = '0'
    </select>
</mapper>