<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//oos.juyouhx.com//DTD Mapper 3.0//EN" "http://oss.juyouhx.com/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.scpyun.platform.standard.jilinsscgsdp.keyDmUser" name="日常人员管理">

    <!-- 分页查询日常人员（可按姓名过滤） -->
    <select id="selectList" parameterType="page" resultType="map">
        SELECT
            u.id,
            u.is_ext,
            u.is_leave,
            u.rc_role_id,
            u.office_id,
            o.name AS office_name,
            u.name,
            u.gh,
            u.email,
            u.phone,
            u.mobile,
            u.birthday,
            u.leave_time,
            DATE_FORMAT(u.in_work_time,'%Y-%m-%d') in_work_time,
            u.leader,
            u.leader_name,
            u.is_used,
            u.create_by,
            u.create_time,
            u.update_by,
            u.update_time,
            u.order_no
        FROM jl_key_dm_user u
        LEFT JOIN sys_office o ON o.id = u.office_id
        <where>
            AND u.is_used = 1
            AND o.area_id = #{params.areaId}
<!--            AND (o.parent_ids LIKE CONCAT(#{params.parentIds}, '%') OR u.office_id = #{params._user.company_id})-->
            <if test="params.name != null and params.name != ''">
                AND u.name LIKE CONCAT('%', #{params.name}, '%')
            </if>
        </where>
        ORDER BY o.grade,o.sort,u.order_no ASC, u.gh
    </select>

    <select id="selectUserLeaderById" parameterType="page" resultType="map">
        SELECT
        u.id,
        u.is_ext,
        u.is_leave,
        u.rc_role_id,
        u.office_id,
        o.name AS office_name,
        u.name,
        u.gh,
        u.email,
        u.phone,
        u.mobile,
            u.birthday,
            u.in_work_time,
            u.leave_time,
            u.leader,
            u.leader_name,
            u.is_used,
        u.create_by,
        u.create_time,
        u.update_by,
        u.update_time,
        u.order_no
        FROM jl_key_dm_user u
        LEFT JOIN sys_office o ON o.id = u.office_id
        <where>
            u.is_used = 1
            <!-- 仅按前端当前格式（params.params）读取参数 -->
            <if test="params != null and params.params != null and params.params.name != null and params.params.name != ''">
                AND (u.name LIKE CONCAT('%', #{params.params.name}, '%') OR u.gh LIKE CONCAT('%', #{params.params.name}, '%'))
            </if>

            <!-- 查上级：params.params.parent_mode='up' 且提供 params.params.office_id -->
            <if test="params != null and params.params != null and params.params.parent_mode != null and params.params.parent_mode == 'up' and params.params.office_id != null and params.params.office_id != ''">
                AND o.id IN (
                    SELECT parent_id FROM sys_office WHERE id = #{params.params.office_id}
                    UNION
                    SELECT #{params.params.office_id} FROM DUAL
                )
            </if>
        </where>

        ORDER BY u.order_no ASC, u.create_time DESC
    </select>
    <select id="selectUserOfficeById" parameterType="page" resultType="map">
        SELECT
        o.*
        FROM sys_office o
        <where>
            AND (o.del_flag = '0' OR o.useable = '1')
            <!-- 仅按前端当前格式（params.params）读取机构查询条件 -->
            <if test="params != null and params.params != null and params.params.name != null and params.params.name != ''">
                AND (o.name LIKE CONCAT('%', #{params.params.name}, '%') OR o.code LIKE CONCAT('%', #{params.params.name}, '%'))
            </if>

            <!-- 查上级 parent_mode='up'（使用 params.params.office_id） -->
            <if test="params != null and params.params != null and params.params.parent_mode != null and params.params.parent_mode == 'up' and params.params.office_id != null and params.params.office_id != ''">
                AND o.id IN (
                    SELECT parent_id FROM sys_office WHERE id = #{params.params.office_id}
                    UNION
                    SELECT #{params.params.office_id} FROM DUAL
                )
            </if>

            <!-- 默认按 params.params.company_id 或 params.params.office_id 过滤所属/子机构 -->
            <if test="params != null and params.params != null and params.params.office_id != null and params.params.office_id != ''">
                AND (o.parent_ids LIKE CONCAT('%', #{params.params.office_id}, '%'))
            </if>
        </where>
        ORDER BY o.grade DESC
    </select>

    <!-- 从 sys_user 查询指定机构（及下属机构）的用户，office_code 为机构 code 字段, 注：内部控制只有长春市由此功能，功能只覆盖长春市范围 -->
    <select id="selectSysUserByOfficeCode" parameterType="map" resultType="map">
        SELECT su.*
        FROM sys_user su LEFT JOIN sys_office so on su.office_id=so.id
        WHERE su.del_flag=0 AND so.del_flag=0 AND so.area_id = #{areaId}
        AND (so.id='220100000000' OR so.parent_ids like '0,220000000000,220100000000%')
    </select>

    <!-- 根据 id 获取日常人员 -->
    <select id="getById" parameterType="map" resultType="map">
        SELECT * FROM jl_key_dm_user WHERE id = #{id} LIMIT 1
    </select>

    <!-- 插入 -->
    <insert id="insert" parameterType="map">
        INSERT INTO jl_key_dm_user(
            id, is_ext, is_leave, rc_role_id, office_id, name, gh, email, phone, mobile,
            birthday, in_work_time, leave_time, leader, leader_name, is_used, create_by, create_time, order_no
        ) VALUES (
            #{id},
            #{is_ext},
            #{is_leave},
            #{rc_role_id},
            #{office_id},
            #{name},
            #{gh},
            #{email},
            #{phone},
            #{mobile},
            #{birthday},
            #{in_work_time},
            #{leave_time},
            #{leader},
            #{leader_name},
            COALESCE(#{is_used},1),
            #{_user.id},
            NOW(),
            #{order_no}
        )
    </insert>

    <!-- 有选择性地更新字段（null 不覆盖） -->
    <update id="updateSelective" parameterType="map">
        UPDATE jl_key_dm_user
        <set>
            <if test="office_id != null">office_id = #{office_id},</if>
            <if test="email != null">email = #{email},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="mobile != null">mobile = #{mobile},</if>
            <if test="is_leave != null">is_leave = #{is_leave},</if>
            <if test="rc_role_id != null">rc_role_id = #{rc_role_id},</if>
            <if test="birthday != null">birthday = #{birthday},</if>
            <if test="in_work_time != null">in_work_time = #{in_work_time},</if>
            <if test="leader != null">leader = #{leader},</if>
            <if test="leader_name != null">leader_name = #{leader_name},</if>
            <if test="leave_time != null">leave_time = #{leave_time},</if>
            update_by = #{_user.id},
            update_time = NOW()
        </set>
        WHERE id = #{id}
    </update>

    <!-- 人员选择器列表查询（根据机构和权限过滤） -->
    <select id="selectUserSelectorList" parameterType="page" resultType="map">
        SELECT
            u.id,
            u.name,
            u.gh,
            u.email,
            u.phone,
            u.mobile,
            o.name AS office_name
        FROM jl_key_dm_user u
        LEFT JOIN sys_office o ON o.id = u.office_id
        <where>
            AND u.is_used = 1 AND u.is_leave = 0 AND o.del_flag = 0
            AND (o.id = #{params.parentId} or o.area_id = #{params.areaId})
            <if test="params.permission != null and params.permission != ''">
                AND u.id in (select id from jl_key_dm_permission_user where permission_code = #{params.permission})
            </if>
            <if test="params.name != null and params.name != ''">
                AND u.name LIKE CONCAT('%', #{params.name}, '%')
            </if>
        </where>
        GROUP BY u.id, u.name, u.gh, u.email, u.phone, u.mobile, o.name
        ORDER BY o.grade, o.sort, u.name ASC
    </select>

    <!-- 人员多选器列表查询（所有在职人员） -->
    <select id="selectUserMultiSelectorList" parameterType="page" resultType="map">
        SELECT
            u.id,
            u.name,
            u.gh,
            u.email,
            u.phone,
            u.mobile,
            u.office_id,
            o.name AS office_name
        FROM jl_key_dm_user u
        LEFT JOIN sys_office o ON o.id = u.office_id
        <where>
            AND u.is_used = 1
            AND IFNULL(u.is_leave, 0) = 0
            <if test="params.round == '1'.toString()">
                AND (u.office_id = #{params.parentId} or o.area_id = #{params.areaId} or o.parent_ids LIKE CONCAT(#{params.parentIds}, '%') )
            </if>
            <if test="params.round == '1'.toString() and params.curId != '' and params.curPids != ''">
                AND (o.area_id = #{params.curId} or o.parent_ids LIKE CONCAT(#{params.curPids}, '%') )
            </if>
            <if test="params.round == '2'.toString()">
                AND o.area_id = #{params.areaId}
            </if>
            <if test="params.name != null and params.name != ''">
                AND u.name LIKE CONCAT('%', #{params.name}, '%')
            </if>
            <if test="params.pcode != null and params.pcode != ''">
                AND NOT EXISTS (SELECT id from jl_key_dm_permission_user where u.id=user_id is null and permission_code=#{params.pcode})
            </if>
        </where>
        ORDER BY o.grade, o.sort
    </select>


<!--    将用户id和name做成字典-->
    <select id="getUserMap" resultType="map">
        select Id,name
        from jl_key_dm_user
        where is_used = 1
    </select>
    <!-- 获取日常人员 -->
    <select id="selectDmUserByOfficeCode" parameterType="map" resultType="map">
        SELECT du.*
        FROM jl_key_dm_user du
<!--        LEFT JOIN sys_office so on du.office_id=so.id-->
<!--        WHERE du.is_used=1 AND so.del_flag=0 AND so.area_id = #{areaId}-->
<!--        AND (so.id='220100000000' OR so.parent_ids like '0,220000000000,220100000000%')-->
    </select></mapper>


