<?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.datavisual.mapper.AdminRegionDataMapper">
    <select id="getTownDataList" resultType="AdminRegionDataVO">
        select d.dt,a.label as region_name, a.code, b.relate_depts, b.relate_grids, c.grid as wkt, d.create_time ,d.item_name, d.item_val,b.ops_dept,d.evoFlag
        from (
        select * from grid_admin_division where level = 4 and
        <foreach collection="codeList" item="code" index="index" open="(" separator="OR" close=")">
            code LIKE CONCAT(#{code}, '%')
        </foreach>
        ) a
        left join (
        select left(a.code, 9) as town_code,a.ops_dept,
        group_concat(distinct  b.relate_dept_name) as relate_depts,
        group_concat(distinct c.grid_name) as relate_grids
        from grid_region_admin_division_relate a
        left join grid_region_user_relate b on a.grid_id = b.grid_id
        left join grid_region_grid c on a.grid_id = c.grid_id
        where a.delete_flag = '0' and b.delete_flag = '0' and c.delete_flag = '0'
        and c.grid_level = '1'
        and a.ops_dept = #{opsDept}
        <if test=" userLevel != null and userLevel != ''  and userLevel =='branch'">
            and b.relate_dept_id = #{deptId}
        </if>
        group by town_code
        ) b on b.town_code = a.code
        left join grid_admin_division_fence c on a.id = c.id
        LEFT JOIN (select dt, create_time ,item_name, item_val,ops_dept ,item_idx,code
        ,CASE WHEN EXISTS (SELECT 1 FROM admin_region_index WHERE code = t.code
        <if test=" branchName != null and branchName != '' ">
            AND   branch_name like CONCAT('%',#{branchName},'%')
        </if>
        <if test="outletName != null and outletName != ''">
            AND   outlet_name like CONCAT('%',#{outletName},'%')
        </if>
        <if test="nickName != null and nickName != ''">
            AND  ( nick_name like CONCAT('%',#{nickName},'%')  or user_name like CONCAT('%',#{nickName},'%') )
        </if>
        ) THEN 'true' ELSE 'false' END AS evoFlag
        from admin_region_index t
        where ops_dept = #{opsDept} and item_idx = #{itemIdx} and dt = #{dt}
        <if test="userLevel == 'head'">  and    index_type = 'head' </if>
        <if test="userLevel == 'branch'">  and    index_type = 'branch' and branch_id = #{deptId} </if>
        group by dt, create_time ,item_name, item_val,ops_dept ,item_idx,code
        )d on a.code = d.code
        where d.dt = #{dt}

    </select>

    <select id="getVillageDataList" resultType="AdminRegionDataVO">
        select d.dt,a.label as region_name, a.code, b.relate_users, b.relate_grids, c.grid as wkt, d.create_time, d.item_name, d.item_val,b.ops_dept,d.evoFlag
        from (
        select * from grid_admin_division where level = 5
        <!--  code !=null  总行支行查询   -->
        <if test="code != null and code != ''">  and code LIKE CONCAT(#{code}, '%') </if>
        ) a
        left join (
        select a.code,a.ops_dept,
        group_concat(distinct  concat(b.nick_name, '-', b.user_name)) as relate_users,
        group_concat(distinct c.grid_name) as relate_grids
        from grid_region_admin_division_relate a
        left join grid_region_user_relate b on a.grid_id = b.grid_id
        left join grid_region_grid c on a.grid_id = c.grid_id
        where a.delete_flag = '0' and b.delete_flag = '0' and c.delete_flag = '0'
        and a.ops_dept = #{opsDept}
        <if test=" userLevel != null and userLevel != ''  and (userLevel == 'outlet' ) ">and b.relate_dept_id = #{deptId}</if>
        <if test="code != null and code != ''">  and a.code LIKE CONCAT(#{code}, '%') </if>
        <if test="managerId != null and managerId != ''">
            and b.user_name = #{managerId}
        </if>
        <if test="managerId == null or managerId == ''">
            and b.relate_dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
        </if>
        and c.grid_level = '2'
        group by a.code
        ) b on b.code = a.code
        left join grid_admin_division_fence c on a.id = c.id
        LEFT JOIN (select  dt,create_time ,item_name, item_val,ops_dept ,item_idx,code
        ,CASE WHEN EXISTS (SELECT 1 FROM admin_region_index WHERE code = t.code
        <if test=" branchName != null and branchName != '' ">
            AND   branch_name like CONCAT('%',#{branchName},'%')
        </if>
        <if test="outletName != null and outletName != ''">
            AND   outlet_name like CONCAT('%',#{outletName},'%')
        </if>
        <if test="nickName != null and nickName != ''">
            AND  ( nick_name like CONCAT('%',#{nickName},'%')  or user_name like CONCAT('%',#{nickName},'%') )
        </if>
        ) THEN 'true' ELSE 'false' END AS evoFlag
        from admin_region_index t
        where ops_dept = #{opsDept} and item_idx = #{itemIdx}
        <if test="userLevel == 'manager'"> and    user_name = #{managerId} </if>
        <if test="userLevel == 'head'">    and    index_type = 'head'   </if>
        <if test="userLevel == 'branch'">  and    index_type = 'branch' and branch_id = #{deptId} </if>
        <if test="userLevel == 'outlet'">  and    index_type = 'outlet' and outlet_id = #{deptId} </if>
        group by  dt,create_time ,item_name, item_val,ops_dept ,item_idx,code
        )d on a.code = d.code
        where 1=1
        and dt = #{dt}
        <if test="userLevel == 'manager' or userLevel == 'outlet' or userLevel == 'branch'">
            and    b.code is not null;
        </if>
    </select>


    <select id="getIndexDetail" resultType="AdminRegionDataVO">
        select id, create_time, level, code, item_name, item_val, dept_id, ops_dept, item_idx ,branch_name, outlet_name,  concat( nick_name,'-', user_name) as nick_name,index_type,head_name,
        user_name, branch_id, outlet_id
        from admin_region_index ari
        where  ari.code = #{code} and ari.ops_dept = #{opsDept} and ari.item_idx = #{itemIdx} and dt = #{dt}
        order by ari.head_id, ari.branch_id,ari.outlet_id,ari.nick_name asc
    </select>
</mapper>