<?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.keyDmUserCategory" name="人员分类管理">

    <!-- 获取人员权限配置列表（从字典获取权限并关联用户） -->
    <select id="selectPermissionConfigList" resultType="map">
        SELECT
        d.value AS permission_code,
        d.label AS permission_name,
        GROUP_CONCAT(CONCAT(u.name) SEPARATOR ',') AS user_names,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'user_id', pu.user_id,
                'user_name', u.name
            )
        ) AS users
        FROM sys_dict d
        LEFT JOIN jl_key_dm_permission_user pu ON pu.permission_code = d.value
        LEFT JOIN jl_key_dm_user u ON u.id = pu.user_id AND u.is_used = 1 AND u.is_leave = 0
        WHERE d.type = 'daily_manage_dict_permission'
        AND d.del_flag = '0'
        GROUP BY d.value, d.label
        ORDER BY d.sort ASC
    </select>

    <!-- 删除权限用户配置 -->
    <delete id="deletePermissionUsers" parameterType="map">
        DELETE FROM jl_key_dm_permission_user
        WHERE permission_code = #{permission_code}
    </delete>

    <!-- 插入权限用户配置 -->
    <insert id="insertPermissionUser" parameterType="map">
        INSERT INTO jl_key_dm_permission_user(
            id,
            permission_code,
            user_id,
            create_by,
            create_time,
            order_no
        ) VALUES (
            UUID(),
            #{permission_code},
            #{user_id},
            #{_user.id},
            NOW(),
            0
        )
    </insert>

    <!-- 获取人员日常管理权限列表 -->
    <select id="selectUserDmPermissionList" resultType="map">
        SELECT
        pu.permission_code as code
        FROM jl_key_dm_permission_user pu
        WHERE pu.user_id = #{_user.id}
    </select>

    <!-- ===== 请假类型管理 ===== -->
    <select id="selectLeaveTypeList" parameterType="page" resultType="map">
        SELECT
            id,
            type_code,
            type_name,
            remark,
            base_days,
            max_days_per_year,
            allow_half_day,
            allow_hourly,
            require_attachment,
            attachment_types,
            attachment_desc,
            need_approval,
            approval_flow_id,
            status,
            is_used,
            create_by,
            create_time,
            update_by,
            update_time,
            order_no
        FROM jl_key_dm_leave_type
        <where>
            AND is_used = 1
            <if test="params.type_name != null and params.type_name != ''">
                AND type_name LIKE CONCAT('%', #{params.type_name}, '%')
            </if>
            <if test="params.type_code != null and params.type_code != ''">
                AND type_code LIKE CONCAT('%', #{params.type_code}, '%')
            </if>
        </where>
        ORDER BY order_no ASC, create_time DESC
    </select>

    <select id="selectLeaveTypeById" parameterType="String" resultType="String">
        SELECT
        type_name
        FROM jl_key_dm_leave_type
        where id = #{id}
        ORDER BY order_no ASC, create_time DESC
    </select>

    <select id="findLeaveTypeList" parameterType="map" resultType="map">
        SELECT
        id,
        type_code,
        type_name,
        remark,
        base_days,
        max_days_per_year,
        allow_half_day,
        allow_hourly,
        require_attachment,
        attachment_types,
        attachment_desc,
        need_approval,
        approval_flow_id,
        status,
        is_used,
        create_by,
        create_time,
        update_by,
        update_time,
        order_no
        FROM jl_key_dm_leave_type
        <where>
            AND is_used = 1
            <if test="type_name != null and type_name != ''">
                AND type_name LIKE CONCAT('%', #{type_name}, '%')
            </if>
            <if test="type_code != null and type_code != ''">
                AND type_code LIKE CONCAT('%', #{type_code}, '%')
            </if>
        </where>
        ORDER BY order_no ASC, create_time DESC
    </select>

    <insert id="insertLeaveType" parameterType="map">
        INSERT INTO jl_key_dm_leave_type(
            id,
            type_code,
            type_name,
            remark,
            base_days,
            max_days_per_year,
            allow_half_day,
            allow_hourly,
            require_attachment,
            attachment_types,
            attachment_desc,
            need_approval,
            approval_flow_id,
            status,
            is_used,
            create_by,
            create_time,
            order_no
        ) VALUES (
            #{id},
            #{type_code},
            #{type_name},
            #{remark},
            #{base_days},
            #{max_days_per_year},
            COALESCE(#{allow_half_day}, 1),
            COALESCE(#{allow_hourly}, 1),
            COALESCE(#{require_attachment}, 0),
            #{attachment_types},
            #{attachment_desc},
            COALESCE(#{need_approval}, 1),
            #{approval_flow_id},
            COALESCE(#{status}, 1),
            1,
            #{_user.id},
            NOW(),
            #{order_no}
        )
    </insert>

    <update id="updateLeaveType" parameterType="map">
        UPDATE jl_key_dm_leave_type
        SET
            type_code = #{type_code},
            type_name = #{type_name},
            remark = #{remark},
            base_days = #{base_days},
            max_days_per_year = #{max_days_per_year},
            allow_half_day = #{allow_half_day},
            allow_hourly = #{allow_hourly},
            require_attachment = #{require_attachment},
            attachment_types = #{attachment_types},
            attachment_desc = #{attachment_desc},
            need_approval = #{need_approval},
            approval_flow_id = #{approval_flow_id},
            status = #{status},
            order_no = #{order_no},
            update_by = #{_user.id},
            update_time = NOW()
        WHERE id = #{id}
    </update>

    <update id="deleteLeaveType" parameterType="map">
        UPDATE jl_key_dm_leave_type
        SET
            is_used = 0,
            update_by = #{_user.id},
            update_time = NOW()
        WHERE id = #{id}
    </update>

    <!-- ===== 办公用品分类管理 ===== -->
    <select id="selectMaterialCategoryList" parameterType="page" resultType="map">
        SELECT
            id,
            category_code,
            category_name,
            parent_id,
            level,
            full_path,
            remark,
            status,
            is_used,
            create_by,
            create_time,
            update_by,
            update_time,
            order_no
        FROM jl_key_dm_material_category
        <where>
            AND is_used = 1
            <if test="params.category_name != null and params.category_name != ''">
                AND category_name LIKE CONCAT('%', #{params.category_name}, '%')
            </if>
            <if test="params.category_code != null and params.category_code != ''">
                AND category_code LIKE CONCAT('%', #{params.category_code}, '%')
            </if>
        </where>
        ORDER BY order_no ASC, create_time DESC
    </select>

    <select id="findMaterialCategoryList" parameterType="map" resultType="map">
        SELECT
        id,
        category_code,
        category_name,
        parent_id,
        level,
        full_path,
        remark,
        status,
        is_used,
        create_by,
        create_time,
        update_by,
        update_time,
        order_no
        FROM jl_key_dm_material_category
        <where>
            AND is_used = 1
            <if test="category_name != null and category_name != ''">
                AND category_name LIKE CONCAT('%', #{category_name}, '%')
            </if>
            <if test="category_code != null and category_code != ''">
                AND category_code LIKE CONCAT('%', #{category_code}, '%')
            </if>
        </where>
        ORDER BY order_no ASC, create_time DESC
    </select>

    <insert id="insertMaterialCategory" parameterType="map">
        INSERT INTO jl_key_dm_material_category(
            id,
            category_code,
            category_name,
            parent_id,
            level,
            full_path,
            remark,
            status,
            is_used,
            create_by,
            create_time,
            order_no
        ) VALUES (
            #{id},
            #{category_code},
            #{category_name},
            COALESCE(#{parent_id}, 0),
            COALESCE(#{level}, 1),
            #{full_path},
            #{remark},
            COALESCE(#{status}, 1),
            1,
            #{_user.id},
            NOW(),
            #{order_no}
        )
    </insert>

    <update id="updateMaterialCategory" parameterType="map">
        UPDATE jl_key_dm_material_category
        SET
            category_code = #{category_code},
            category_name = #{category_name},
            parent_id = #{parent_id},
            level = #{level},
            full_path = #{full_path},
            remark = #{remark},
            status = #{status},
            order_no = #{order_no},
            update_by = #{_user.id},
            update_time = NOW()
        WHERE id = #{id}
    </update>

    <update id="deleteMaterialCategory" parameterType="map">
        UPDATE jl_key_dm_material_category
        SET
            is_used = 0,
            update_by = #{_user.id},
            update_time = NOW()
        WHERE id = #{id}
    </update>

    <!-- ===== 办公用品管理 ===== -->
    <select id="selectMaterialList" parameterType="page" resultType="map">
        SELECT
            m.id,
            m.category_id,
            m.material_code,
            m.material_name,
            m.material_spec,
            m.unit,
            m.unit_price,
            m.supplier_name,
            m.storage_location,
            m.min_stock,
            m.max_stock,
            m.status,
            m.can_borrow,
            m.material_desc,
            m.is_used,
            m.create_by,
            m.create_time,
            m.update_by,
            m.update_time,
            m.order_no,
            c.category_name AS category_name,
            (COALESCE(i.total_quantity,0) - COALESCE(i.borrowed_quantity) - COALESCE(i.damaged_quantity)) as available_quantity
        FROM jl_key_dm_material m
        LEFT JOIN jl_key_dm_material_category c ON c.id = m.category_id
        left join jl_key_dm_inventory as i on i.material_id = m.id AND i.apply_dep_code = #{params.areaId}
        <where>
            AND m.is_used = 1
            <if test="params.material_name != null and params.material_name != ''">
                AND m.material_name LIKE CONCAT('%', #{params.material_name}, '%')
            </if>
            <if test="params.material_code != null and params.material_code != ''">
                AND m.material_code LIKE CONCAT('%', #{params.material_code}, '%')
            </if>
            <if test="params.category_id != null and params.category_id != ''">
                AND m.category_id = #{params.category_id}
            </if>
<!--            <if test="params.areaId != null and params.areaId != ''">-->
<!--                AND i.apply_dep_code = #{params.areaId}-->
<!--            </if>-->
        </where>
        ORDER BY c.order_no,m.order_no ASC, m.create_time DESC
    </select>

    <select id="findMaterialList" parameterType="map" resultType="map">
        SELECT
        m.id,
        m.category_id,
        m.material_code,
        m.material_name,
        m.material_spec,
        m.unit,
        m.unit_price,
        m.supplier_name,
        m.storage_location,
        m.min_stock,
        m.max_stock,
        m.status,
        m.can_borrow,
        m.material_desc,
        m.is_used,
        m.create_by,
        m.create_time,
        m.update_by,
        m.update_time,
        m.order_no,
        c.category_name AS category_name
        FROM jl_key_dm_material m
        LEFT JOIN jl_key_dm_material_category c ON c.id = m.category_id
        <where>
            AND m.is_used = 1
            <if test="material_name != null and material_name != ''">
                AND m.material_name LIKE CONCAT('%', #{material_name}, '%')
            </if>
            <if test="material_code != null and material_code != ''">
                AND m.material_code LIKE CONCAT('%', #{material_code}, '%')
            </if>
            <if test="category_id != null and category_id != ''">
                AND m.category_id = #{category_id}
            </if>
        </where>
        ORDER BY m.order_no ASC, m.create_time DESC
    </select>

    <insert id="insertMaterial" parameterType="map">
        INSERT INTO jl_key_dm_material(
            id,
            category_id,
            material_code,
            material_name,
            material_spec,
            unit,
            unit_price,
            supplier_name,
            storage_location,
            min_stock,
            max_stock,
            status,
            can_borrow,
            material_desc,
            is_used,
            create_by,
            create_time,
            order_no
        ) VALUES (
            #{id},
            #{category_id},
            #{material_code},
            #{material_name},
            #{material_spec},
            #{unit},
            #{unit_price},
            #{supplier_name},
            #{storage_location},
            #{min_stock},
            #{max_stock},
            COALESCE(#{status}, 1),
            COALESCE(#{can_borrow}, 1),
            #{material_desc},
            1,
            #{_user.id},
            NOW(),
            #{order_no}
        )
    </insert>

    <update id="updateMaterial" parameterType="map">
        UPDATE jl_key_dm_material
        SET
            category_id = #{category_id},
            material_code = #{material_code},
            material_name = #{material_name},
            material_spec = #{material_spec},
            unit = #{unit},
            unit_price = #{unit_price},
            supplier_name = #{supplier_name},
            storage_location = #{storage_location},
            min_stock = #{min_stock},
            max_stock = #{max_stock},
            status = #{status},
            can_borrow = #{can_borrow},
            material_desc = #{material_desc},
            order_no = #{order_no},
            update_by = #{_user.id},
            update_time = NOW()
        WHERE id = #{id}
    </update>

    <update id="deleteMaterial" parameterType="map">
        UPDATE jl_key_dm_material
        SET
            is_used = 0,
            update_by = #{_user.id},
            update_time = NOW()
        WHERE id = #{id}
    </update>
    <select id="selectOrgPermission" parameterType="map" resultType="map">
        SELECT
        u.id,
        u.is_ext,
        u.is_leave,
        u.rc_role_id,
        u.office_id,
        u.name,
        u.gh,
        u.email,
        u.phone,
        u.mobile,
        u.birthday,
        u.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_permission_user p
        left join jl_key_dm_user u on p.user_id = u.id
        left join sys_office so on u.office_id = so.id
        <where>
            AND is_used = 1
            AND so.area_id = #{areaId}
<!--            <if test="_user.company_id != null">-->
<!--                AND u.office_id = #{_user.company_id}-->
<!--            </if>-->
            <if test="_user.id != null and _user.id != ''">
                AND u.id != #{_user.id}
            </if>
            <if test="permissionType != null">
                AND p.permission_code = #{permissionType}
            </if>
        </where>
        ORDER BY order_no ASC, create_time DESC
    </select>
    <!-- ===== 图表分析 ===== -->
    <!-- 获取请假总次数 -->
    <select id="getLeaveCount" resultType="map">
        SELECT COUNT(*) as count
        FROM jl_key_dm_leave_application
        WHERE is_used = 1
    </select>

    <!-- 获取申领总次数 -->
    <select id="getBorrowCount" resultType="map">
        SELECT COUNT(*) as count
        FROM jl_key_dm_borrow_application
        WHERE is_used = 1
    </select>

    <!-- 获取入库总次数 -->
    <select id="getInboundCount" resultType="map">
        SELECT COUNT(*) as count
        FROM jl_key_dm_inbound_record
        WHERE is_used = 1
    </select>

    <!-- 请假类型统计 -->
    <select id="getLeaveTypeStats" resultType="map">
        SELECT
            lt.type_name,
            COUNT(la.id) as count
        FROM jl_key_dm_leave_type lt
        LEFT JOIN jl_key_dm_leave_application la ON la.leave_type_id = lt.id AND la.is_used = 1
        WHERE lt.is_used = 1 AND lt.status = 1
        GROUP BY lt.id, lt.type_name
        ORDER BY count DESC
    </select>

    <!-- 人员请假统计（月度） -->
    <select id="getLeaveUserStats" resultType="map">
        SELECT
            u.name as user_name,
            COUNT(la.id) as leave_count
        FROM jl_key_dm_user u
        LEFT JOIN jl_key_dm_leave_application la ON la.user_id = u.id AND la.is_used = 1
            AND DATE_FORMAT(la.create_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')
        WHERE u.is_used = 1 AND u.is_leave = 0
        GROUP BY u.id, u.name
        HAVING leave_count > 0
        ORDER BY leave_count DESC
    </select>

    <!-- 用品领用统计（月度） -->
    <select id="getMaterialUsageStats" resultType="map">
        SELECT
            m.material_name,
            SUM(bd.apply_quantity) as total_quantity
        FROM jl_key_dm_material m
        LEFT JOIN jl_key_dm_borrow_application_detail bd ON bd.material_id = m.id
        LEFT JOIN jl_key_dm_borrow_application ba ON ba.id = bd.application_id AND ba.is_used = 1
            AND DATE_FORMAT(ba.create_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')
        WHERE m.is_used = 1
        GROUP BY m.id, m.material_name
        HAVING total_quantity &gt; 0
        ORDER BY total_quantity DESC
        LIMIT 20
    </select>

    <!-- 库存预警列表 -->
    <select id="getStockWarningList" resultType="map">
        SELECT
            m.material_name,
            i.available_quantity,
            m.min_stock,
            m.max_stock,
            CASE
                WHEN i.available_quantity &lt; m.min_stock THEN 'low'
                WHEN i.available_quantity &gt; m.max_stock THEN 'high'
            END as status
        FROM jl_key_dm_material m
        LEFT JOIN jl_key_dm_inventory i ON i.material_id = m.id
        WHERE m.is_used = 1
            AND (i.available_quantity &lt; m.min_stock OR i.available_quantity &gt; m.max_stock)
        ORDER BY
            CASE
                WHEN i.available_quantity &lt; m.min_stock THEN 1
                WHEN i.available_quantity &gt; m.max_stock THEN 2
            END,
            ABS(i.available_quantity - (m.min_stock + m.max_stock) / 2) DESC
    </select>

    <!-- ===== 部门管理 ===== -->
    <select id="selectOrgList" parameterType="page" resultType="map">
<!--        SELECT-->
<!--            id,-->
<!--            source_org,-->
<!--            org_code,-->
<!--            org_name,-->
<!--            remark,-->
<!--            is_used,-->
<!--            create_by,-->
<!--            create_time,-->
<!--            update_by,-->
<!--            update_time,-->
<!--            order_no-->
<!--        FROM jl_key_dm_org-->
<!--        <where>-->
<!--            AND is_used = 1-->
<!--            <if test="params.org_name != null and params.org_name != ''">-->
<!--                AND org_name LIKE CONCAT('%', #{params.org_name}, '%')-->
<!--            </if>-->
<!--            <if test="params.org_code != null and params.org_code != ''">-->
<!--                AND org_code LIKE CONCAT('%', #{params.org_code}, '%')-->
<!--            </if>-->
<!--        </where>-->
<!--        ORDER BY order_no ASC, create_time DESC-->
        SELECT
        org.id,
        org.org_name,
        org.source_org,
        org.org_code,
        org.order_no,
        GROUP_CONCAT(CONCAT(u.name) SEPARATOR ',') AS user_names,
        JSON_ARRAYAGG(
        JSON_OBJECT(
        'user_id', u.id,
        'user_name', u.name
        )
        ) AS users
        FROM jl_key_dm_org org
        LEFT JOIN jl_key_dm_user u ON u.belong_org_id = org.id AND u.is_used = 1
        <where>
            AND org.is_used = 1 AND source_org = #{params.area_id}
            <if test="params.org_name != null and params.org_name != ''">
                AND org_name LIKE CONCAT('%', #{params.org_name}, '%')
            </if>
        </where>
        GROUP BY org.id, org.org_name,org.source_org,org.org_code,org.order_no
        ORDER BY org.order_no
    </select>

    <insert id="insertOrg" parameterType="map">
        INSERT INTO jl_key_dm_org(
            id,
            source_org,
            org_code,
            org_name,
            remark,
            is_used,
            create_by,
            create_time,
            order_no
        ) VALUES (
            #{id},
            #{source_org},
            #{org_code},
            #{org_name},
            #{remark},
            1,
            #{_user.id},
            NOW(),
            #{order_no}
        )
    </insert>

    <update id="updateOrg" parameterType="map">
        UPDATE jl_key_dm_org
        SET
            org_name = #{org_name},
            remark = #{remark},
            order_no = #{order_no},
            update_by = #{_user.id},
            update_time = NOW()
        WHERE id = #{id}
    </update>

    <update id="deleteOrg" parameterType="map">
        UPDATE jl_key_dm_org
        SET
            is_used = 0,
            update_by = #{_user.id},
            update_time = NOW()
        WHERE id = #{id}
    </update>

    <!-- 清除部门用户分配 -->
    <update id="clearOrgUsers" parameterType="map">
        UPDATE jl_key_dm_user
        SET
            belong_org_id = NULL,
            belong_org_code = NULL,
            belong_org_name = NULL,
            update_by = #{_user.id},
            update_time = NOW()
        WHERE belong_org_id = #{belong_org_id}
        AND office_id in (select id from sys_office where id = #{area_id} or parent_ids like #{parent_ids})
    </update>

    <!-- 分配用户到部门 -->
    <update id="assignUserToOrg" parameterType="map">
        UPDATE jl_key_dm_user
        SET
            belong_org_id = #{belong_org_id},
            belong_org_code = (SELECT org_code FROM jl_key_dm_org WHERE id = #{belong_org_id}),
            belong_org_name = (SELECT org_name FROM jl_key_dm_org WHERE id = #{belong_org_id}),
            update_by = #{_user.id},
            update_time = NOW()
        WHERE id = #{user_id}
    </update>

    <select id="findLeader" parameterType="String" resultType="String">
        select leader from jl_key_dm_user where id = #{param}
    </select>

    <!-- //统计部分 -->
    <!-- 使用统计：按部门、物料统计领用数量 -->
    <select id="selectUsageStats" parameterType="map" resultType="map">
        SELECT
        ba.department_id,
        ba.department_name,
        ad.material_id,
        ad.material_name,
        sum(ad.apply_quantity)-sum(ad.returned_quantity) as total_quantity
        from jl_key_dm_borrow_application_detail ad
        LEFT JOIN jl_key_dm_borrow_application ba on ad.application_id = ba.id
        LEFT JOIN jl_key_dm_material m on ad.material_id = m.id
        LEFT JOIN sys_office so on so.id = ba.department_id
        <where>
            AND ba.is_used=1 and ad.is_used=1 and ba.approval_status = 9
<!--            AND so.area_id = #{areaId}-->
            AND ba.apply_dep_code = #{areaId}
            <if test="start != null and start != ''">
                AND ba.create_time &gt;= #{start}
            </if>
            <if test="end != null and end != ''">
                AND ba.create_time &lt;= #{end}
            </if>
            <if test="material != null and material != ''">
                AND (ad.material_name LIKE CONCAT('%', #{material}, '%') OR ad.material_code LIKE CONCAT('%', #{material}, '%'))
            </if>
            <if test="dep != null and dep != ''">
                AND ad.department_name LIKE CONCAT('%', #{dep}, '%')
            </if>
        </where>
        GROUP BY ba.department_id, ba.department_name, ad.material_id,ad.material_name
        ORDER BY total_quantity DESC
    </select>

    <!-- 使用明细 -->
    <select id="selectUsageDetails" parameterType="map" resultType="map">
        SELECT
        ba.applicant_name user_name,
        ba.department_id,
        ba.department_name,
        ad.material_id,
        ad.material_name,
        DATE_FORMAT(ba.submit_time, '%Y-%m-%d') as apply_time,
        ad.apply_quantity,
        ad.returned_quantity
        from jl_key_dm_borrow_application_detail ad
        LEFT JOIN jl_key_dm_borrow_application ba on ad.application_id = ba.id
        LEFT JOIN jl_key_dm_material m on ad.material_id = m.id
        LEFT JOIN sys_office so on so.id = ba.department_id
        <where>
            AND ba.is_used=1 and ad.is_used=1 and ba.approval_status = 9
            <if test="start != null and start != ''">
                AND ba.create_time &gt;= #{start}
            </if>
            <if test="end != null and end != ''">
                AND ba.create_time &lt;= #{end}
            </if>
            <if test="material != null and material != ''">
                AND ad.material_id = #{material}
            </if>
            <if test="department != null and department != ''">
                AND ba.department_id = #{department}
            </if>
        </where>
        ORDER BY apply_quantity DESC
    </select>

    <!-- 工作量统计：合并多表数据后按人员统计 -->
    <select id="selectWorkloadStats" parameterType="map" resultType="map">
        SELECT uid AS user_id, uname AS user_name, o.name AS depName, o.grade,
        SUM(leave_app) AS leave_applications,
        SUM(leave_approve) AS leave_approvals,
        SUM(borrow_app) AS borrow_applications,
        SUM(borrow_approval) AS borrow_approval,
        SUM(inbound_cnt) AS inbound_count,
        SUM(leave_duration) AS leave_duration
        FROM (
        SELECT l.user_id AS uid, l.user_name AS uname, 1 AS leave_app, 0 AS leave_approve, 0 AS borrow_app, 0 AS borrow_approval, 0 AS inbound_cnt, COALESCE(l.duration,0) AS leave_duration
        FROM jl_key_dm_leave_application l
        <where>
            AND l.is_used = 1 AND l.STATUS != 0
            <if test="start != null and start != ''">
                AND l.start_time &gt;= #{start}
            </if>
            <if test="end != null and end != ''">
                AND l.start_time &lt;= #{end}
            </if>
        </where>
        UNION ALL
        SELECT r.approver_id AS uid, r.approver_name AS uname, 0, CASE WHEN r.approver_result = 1 THEN 1 ELSE 0 END, 0, 0, 0, 0
        FROM jl_key_dm_leave_approval_record r
        <where>
            AND r.is_used = 1 AND r.STATUS = 2
            <if test="start != null and start != ''">
                AND r.approver_time &gt;= #{start}
            </if>
            <if test="end != null and end != ''">
                AND r.approver_time &lt;= #{end}
            </if>
        </where>
        UNION ALL
        SELECT b.applicant_id AS uid, b.applicant_name AS uname, 0, 0, 1, 0, 0, 0
        FROM jl_key_dm_borrow_application b
        <where>
            AND b.is_used = 1 AND b.approval_status != 0 AND b.apply_dep_code = #{areaId}
            <if test="start != null and start != ''">
                AND b.submit_time &gt;= #{start}
            </if>
            <if test="end != null and end != ''">
                AND b.submit_time &lt;= #{end}
            </if>
        </where>
        UNION ALL
        SELECT ba.approver_id AS uid, u.name AS uname, 0, 0, 0, 1, 0, 0
        FROM jl_key_dm_borrow_application ba left join jl_key_dm_user u on ba.approver_id = u.id
        <where>
            AND ba.is_used = 1 AND (ba.approval_status = 9 OR ba.approval_status = -1) AND ba.apply_dep_code = #{areaId}
            <if test="start != null and start != ''">
                AND ba.approval_time &gt;= #{start}
            </if>
            <if test="end != null and end != ''">
                AND ba.approval_time &lt;= #{end}
            </if>
        </where>
        UNION ALL
        SELECT ir.create_by AS uid, du.`name` AS uname, 0, 0, 0, 0, 1, 0
        FROM jl_key_dm_inbound_record ir LEFT JOIN jl_key_dm_user du on ir.create_by = du.id
        <where>
            AND ir.is_used = 1 AND ir.apply_dep_code = #{areaId}
            <if test="start != null and start != ''">
                AND ir.create_time &gt;= #{start}
            </if>
            <if test="end != null and end != ''">
                AND ir.create_time &lt;= #{end}
            </if>
        </where>
        ) t LEFT JOIN jl_key_dm_user u on t.uid=u.id LEFT JOIN sys_office o on u.office_id=o.id
        WHERE o.area_id = #{areaId}
        GROUP BY t.uid, t.uname, o.name, o.grade, o.sort
        ORDER BY o.grade, o.sort, leave_applications DESC, borrow_applications DESC
    </select>

    <!-- 工作量明细：按类型返回对应明细 -->
    <select id="selectWorkloadDetails" parameterType="map" resultType="map">
        <choose>
            <when test="type == 'leave'">
                SELECT id, user_id, user_name, start_time, end_time, duration, reason, `status` FROM jl_key_dm_leave_application
                <where>
                    AND user_id = #{user_id} AND STATUS != 0
                    <if test="start != null and start != ''">
                        AND create_time &gt;= #{start}
                    </if>
                    <if test="end != null and end != ''">
                        AND create_time &lt;= #{end}
                    </if>
                </where>
                ORDER BY create_time DESC
            </when>
            <when test="type == 'inventory'">
                SELECT b.application_no, b.applicant_id, b.applicant_name, d.material_name, b.approval_status, d.apply_quantity, d.returned_quantity,b.submit_time
                FROM jl_key_dm_borrow_application_detail d
                LEFT JOIN jl_key_dm_borrow_application b ON b.id = d.application_id
                <where>
                    AND b.is_used = 1 AND b.approval_status != 0 AND b.applicant_id = #{user_id}
                    <if test="start != null and start != ''">
                        AND b.submit_time &gt;= #{start}
                    </if>
                    <if test="end != null and end != ''">
                        AND b.submit_time &lt;= #{end}
                    </if>
                </where>
                ORDER BY b.submit_time DESC
            </when>
            <otherwise>
                SELECT 1 AS dummy
            </otherwise>
        </choose>
    </select>

</mapper>

