<?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.customerselect.mapper.GridSelectMapper">

    <select id="getRegionGridList" resultType="GridSelectVO">
        select distinct a.grid_name, a.grid_id
        from grid_region_grid a
        left join sys_dept b on a.dept_id = b.dept_id
        left join grid_region_user_relate c on c.grid_id = a.grid_id
        where a.delete_flag = '0' and c.delete_flag = '0' and a.grid_level = #{gridLevel} and a.ops_dept = #{opsDept}
        <if test="deptId != null and deptId != ''">
            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>
        <if test="userName != null and userName != ''">
            and c.user_name = #{userName}
        </if>
    </select>

    <select id="getVirtualGridList" resultType="GridSelectVO">
        SELECT distinct a.grid_name, 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' and a.ops_dept = #{opsDept}
        <if test="deptId != null and deptId != ''">
            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>
        <if test="userName != null and userName != ''">
            and c.user_name = #{userName}
        </if>
    </select>

    <select id="getDrawGridList" resultType="GridSelectVO">
        SELECT distinct a.grid_name, a.grid_id
        FROM grid_draw_grid a
        left join grid_draw_user_relate c on a.grid_id = c.grid_id
        WHERE a.delete_flag = '0' and c.delete_flag = '0' and a.ops_dept = #{opsDept}
        <if test="deptId != null and deptId != ''">
            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>
        <if test="userName != null and userName != ''">
            and c.user_name = #{userName}
        </if>
    </select>

    <select id="getDrawGridRelate" resultType="GridRelateVO">
        select a.cust_id as cust_id,
               group_concat(distinct concat(c.nick_name,'-',c.user_name)) as user_names,
               group_concat(distinct d.grid_name) as grid_names,
               group_concat(distinct c.relate_branch_name) as branch_names,
               group_concat(distinct c.relate_outlet_name) as outlet_names
        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 c.grid_id = b.grid_id
                 left join grid_draw_grid d on c.grid_id = d.grid_id
        where a.cust_id in
        <foreach collection="custIds" item="custId" open="(" separator="," close=")">
            #{custId}
        </foreach>
        group by a.cust_id
    </select>

    <select id="getRegionGridRelate" resultType="GridRelateVO" >
        select a.cust_id, a.nick_names as user_names, a.top_grid_name, a.sec_grid_name, a.branch_names, a.outlet_names
        from grid_region_cust_user a
        where cust_id in
        <foreach collection="custIds" item="custId" open="(" separator="," close=")">
            #{custId}
        </foreach>
    </select>

    <select id="getVirtualGridRelate" resultType="GridRelateVO">
        select cust_id,
        group_concat(distinct concat(user_name,'-',nick_name)) as user_names,
        group_concat(distinct relate_dept_id) as dept_ids,
        group_concat(distinct b.grid_name) as grid_names
        from grid_virtual_cust_user a
        left join grid_virtual_grid b on a.grid_id = b.grid_id
        where cust_id in
        <foreach collection="custIds" item="custId" open="(" separator="," close=")">
            #{custId}
        </foreach>
        <if test="userName != null and userName != ''">
            and a.user_name = #{userName}
        </if>
        group by cust_id;
    </select>

</mapper>