<?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.keyDmLeave" name="请假申请">

    <select id="selectList" parameterType="page" resultType="map">
        SELECT
            la.id,la.user_id,la.user_name,la.department_id,la.department_name,la.leave_type_id,la.leave_type_name,
            la.start_time,la.end_time,la.duration_unit,la.duration,la.reason,la.emergency_contact,la.emergency_phone,
            la.status,la.back_reason,la.submit_time,la.approval_complete_time,la.op_result,la.is_used,la.create_by,la.create_time,la.update_by,la.update_time,la.order_no
        FROM jl_key_dm_leave_application la
        LEFT JOIN jl_key_dm_user u ON la.user_id = u.id
        LEFT JOIN sys_office so on u.office_id = so.id
        <where>
            AND la.is_used = 1
            AND la.status !=0
            AND so.area_id = #{params.area_id}
<!--            AND (u.office_id = #{params._user.company_id} OR so.parent_ids like CONCAT(#{params._user.parent_ids}, ',', #{params._user.company_id}, '%'))-->
            <if test="params.user_name != null and params.user_name != ''">
                AND la.user_name LIKE CONCAT('%', #{params.user_name}, '%')
            </if>
            <!-- 支持按起止时间区间查询（来自 params.params.start_time / params.params.end_time）：返回与区间有交集的请假记录 -->
            <if test="params.params != null and params.params.start_time != null and params.params.start_time != ''">
                AND la.submit_time &gt;= #{params.params.start_time}
            </if>
            <if test="params.params != null and params.params.end_time != null and params.params.end_time != ''">
                AND la.submit_time &lt; #{params.params.end_time}
            </if>
            <!-- 支持按状态过滤（来自 params.params.status，例如: 0,1,2,9,-1） -->
            <if test="params.params != null and params.params.status != null and params.params.status != ''">
                AND la.status = #{params.params.status}
            </if>
            <if test="params._user.company_id != null and params._user.company_id != ''">
                AND la.department_id LIKE CONCAT('%', #{params._user.parent_id}, '%')
            </if>
        </where>
        ORDER BY so.grade,la.submit_time DESC
    </select>

    <select id="selectListByUserId" parameterType="page" resultType="map">
        SELECT
        r.id,
        a.id as leave_id,
        a.user_id,
        r.approver_id,
        r.approver_name,
        a.user_name,
        a.department_id,
        a.department_name,
        a.leave_type_id,
        a.leave_type_name,
        a.start_time,
        a.end_time,
        a.duration_unit,
        a.duration,
        a.reason,
        a.emergency_contact,
        a.emergency_phone,
        a.status,
        a.back_reason,
        a.submit_time,
        a.approval_complete_time,
        a.op_result,
        a.is_used,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        a.order_no
        FROM jl_key_dm_leave_application a
        left join jl_key_dm_leave_approval_record r on r.leave_id=a.id
        <where>
            AND a.is_used = 1
            AND r.node_type = 1
            <if test="params._user.id != null and params._user.id != ''">
                AND a.user_id LIKE CONCAT('%', #{params._user.id}, '%')
            </if>
            <!-- 支持按起止时间查询（来自 params.params.start_time / params.params.end_time）：可单独填写一个时间进行查询 -->
            <if test="params.params != null and params.params.start_time != null and params.params.start_time != ''">
                AND a.end_time &gt;= #{params.params.start_time}
            </if>
            <if test="params.params != null and params.params.end_time != null and params.params.end_time != ''">
                AND a.start_time &lt; #{params.params.end_time}
            </if>
            <!-- 支持按状态过滤（来自 params.params.status） -->
            <if test="params.params != null and params.params.status != null and params.params.status != ''">
                AND a.status = #{params.params.status}
            </if>
        </where>
        ORDER BY a.status ASC
    </select>

    <select id="selectPendingList" parameterType="page" resultType="map">
        SELECT
        r.id,
        a.id AS leave_id,
        a.user_id,
        a.user_name,
        a.department_id,
        a.department_name,
        a.leave_type_id,
        a.leave_type_name,
        a.start_time,
        a.end_time,
        a.duration_unit,
        a.duration,
        a.reason,
        a.emergency_contact,
        a.emergency_phone,
        a.status,
        a.back_reason,
        a.submit_time,
        a.approval_complete_time,
        a.op_result,
        a.is_used,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        a.order_no
        FROM jl_key_dm_leave_application a
        left JOIN jl_key_dm_leave_approval_record r ON r.leave_id=a.id
        <where>
            AND a.is_used = 1
            AND r.status = 1

            <if test="params._user.id != null and params._user.id != ''">
                AND r.approver_id = #{params._user.id}
            </if>
            <!-- 支持按起止时间查询（来自 params.params.start_time / params.params.end_time）：可单独填写一个时间进行查询 -->
            <if test="params.params != null and params.params.start_time != null and params.params.start_time != ''">
                AND a.end_time &gt;= #{params.params.start_time}
            </if>
            <if test="params.params != null and params.params.end_time != null and params.params.end_time != ''">
                AND a.start_time &lt; #{params.params.end_time}
            </if>
            <!-- 支持按状态过滤（来自 params.params.status） -->
            <if test="params.params != null and params.params.status != null and params.params.status != ''">
                AND a.status = #{params.params.status}
            </if>
        </where>
        ORDER BY a.submit_time DESC
    </select>
    
    <!-- 待审核数量统计（用于快速获取 totalRecord 或计数） -->
    <select id="selectPendingCount" parameterType="map" resultType="int">
        SELECT COUNT(DISTINCT a.id) AS cnt
        FROM jl_key_dm_leave_application a
        LEFT JOIN jl_key_dm_leave_approval_record r ON r.leave_id = a.id
        <where>
            AND a.is_used = 1
            AND r.status = 1
            <!-- 支持 page.params._user.id 或直接传入 _user.id 两种情况 -->
            <if test="params != null and params._user != null and params._user.id != null and params._user.id != ''">
                AND r.approver_id = #{params._user.id}
            </if>
            <if test="_user != null and _user.id != null and _user.id != ''">
                AND r.approver_id = #{_user.id}
            </if>
            <!-- 支持按起止时间查询：可单独填写一个时间进行查询 -->
            <if test="params != null and params.params != null and params.params.start_time != null and params.params.start_time != ''">
                AND a.end_time &gt;= #{params.params.start_time}
            </if>
            <if test="params != null and params.params != null and params.params.end_time != null and params.params.end_time != ''">
                AND a.start_time &lt; #{params.params.end_time}
            </if>
            <if test="params != null and params.params != null and params.params.status != null and params.params.status != ''">
                AND a.status = #{params.params.status}
            </if>
        </where>
    </select>

    <select id="selectTransferApprovalList" parameterType="page" resultType="map">
        SELECT
        r.id,
        r.leave_id AS leave_id,
        r.node_index,
        r.node_type,
        r.approver_id,
        r.approver_name,
        r.approver_result,
        r.approver_time,
        r.status,
        r.comment,
        r.transfer_to_id,
        r.transfer_to_name,
        r.transfer_reason,
        r.is_used,
        r.create_by,
        r.create_time,
        r.update_by,
        r.update_time,
        r.order_no,
        a.submit_time
        FROM jl_key_dm_leave_application a
        LEFT JOIN jl_key_dm_leave_approval_record r
        ON r.leave_id = a.id
        WHERE
        a.is_used = 1
        And r.is_used = 1
        AND r.id != #{leave_id}
        AND r.leave_id = (select leave_id from jl_key_dm_leave_approval_record where id = #{leave_id} )
        <!-- 可选过滤：按起止时间和状态，可单独填写一个时间进行查询 -->
        <if test="params.start != null and params.start != ''">
            AND a.end_time &gt;= #{params.start}
        </if>
        <if test="params.end != null and params.end != ''">
            AND a.start_time &lt; #{params.end}
        </if>
        <if test="params.status != null and params.status != ''">
            AND a.status = #{params.status}
        </if>
        ORDER BY a.submit_time DESC
    </select>

    <select id="getById" parameterType="map" resultType="map">
        SELECT * FROM jl_key_dm_leave_application WHERE id = #{leave_id} LIMIT 1
    </select>

    <insert id="insert" parameterType="map">
        INSERT INTO jl_key_dm_leave_application(
            id,user_id,user_name,department_id,department_name,leave_type_id,leave_type_name,
            start_time,end_time,duration_unit,duration,reason,emergency_contact,emergency_phone,
            status,submit_time,is_used,create_by,create_time,order_no
        ) VALUES (
            #{leave_id},#{_user.id},#{_user.name},#{_user.parent_id},#{_user.company_name},#{leave_type_id},#{leave_type_name},
            #{start_time},#{end_time},#{duration_unit},#{duration},#{reason},#{emergency_contact},#{emergency_phone},
            #{status},#{submit_time},1,#{_user.id},NOW(),#{order_no}
        )
    </insert>

    <update id="update" parameterType="map">
        UPDATE jl_key_dm_leave_application
        SET
            leave_type_id = #{leave_type_id},
            leave_type_name = #{leave_type_name},
            start_time = #{start_time},
            end_time = #{end_time},
            duration_unit = #{duration_unit},
            duration = #{duration},
            reason = #{reason},
            emergency_contact = #{emergency_contact},
            emergency_phone = #{emergency_phone},
            update_by = #{_user.id},
            update_time = NOW()
        WHERE id = #{leave_id}
    </update>


    <update id="deleteById" parameterType="map">
        UPDATE jl_key_dm_leave_application set is_used=0 WHERE id = #{leave_id}
    </update>

    <!-- 提交：设置 status=1 (审核中)，submit_time=NOW() -->
    <update id="submit" parameterType="map">
        UPDATE jl_key_dm_leave_application
        SET status = 1, submit_time = NOW(), op_result = 0, update_by = #{_user.id}, update_time = NOW()
        WHERE id = #{leave_id}
    </update>

    <update id="transferApproval" parameterType="map">
        UPDATE jl_key_dm_leave_application
        SET status = 2, op_result = 0, update_by = #{_user.id}, update_time = NOW()
        WHERE id = #{leave_id} AND status = 1
    </update>

    <!-- 撤回：仅限审核中状态撤回，设置 status=0 -->
    <update id="revoke" parameterType="map">
        UPDATE jl_key_dm_leave_application
        SET status = 0, op_result = 0, update_by = #{_user.id}, update_time = NOW()
        WHERE id = #{leave_id} AND status = 1
    </update>

    <!-- 审批通过：设置 status=9, op_result=1, approval_complete_time -->
    <update id="approve" parameterType="map">
        UPDATE jl_key_dm_leave_application
        SET status = 9, op_result = 1, approval_complete_time = NOW(), update_by = #{_user.id}, update_time = NOW()
        WHERE id = #{leave_id}
    </update>

    <!-- 驳回：设置 status=-1, op_result=-1, back_reason -->
    <update id="reject" parameterType="map">
        UPDATE jl_key_dm_leave_application
        SET status = -1, op_result = -1, back_reason = #{comment}, approval_complete_time = NOW(), update_by = #{_user.id}, update_time = NOW()
        WHERE id = #{leave_id}
    </update>

    <!-- 统计：按人员统计已通过请假时长（按天数汇总），params.start, params.end, params.department -->
    <select id="selectStats" parameterType="map" resultType="map">
        SELECT la.user_id, la.user_name, la.department_id, la.department_name, SUM(la.duration) AS passed_days
        FROM jl_key_dm_leave_application la
        LEFT JOIN jl_key_dm_user u ON la.user_id = u.id
        LEFT JOIN sys_office so on u.office_id = so.id
        <where>
            AND la.is_used = 1
            AND la.status = 9
            AND so.area_id = #{area_id}
            <if test="start != null and start != ''">
                AND la.end_time &gt;= #{start}
            </if>
            <if test="end != null and end != ''">
                And la.start_time &lt;= #{end}
            </if>
        </where>
        GROUP BY la.user_id, la.user_name, la.department_id, la.department_name
        ORDER BY passed_days DESC
    </select>

</mapper>


