<?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.sundot.airport.system.mapper.approval.ApprovalInstanceMapper">

    <resultMap type="ApprovalInstance" id="ApprovalInstanceResult">
        <id property="id" column="id"/>
        <result property="instanceNo" column="instance_no"/>
        <result property="workflowId" column="workflow_id"/>
        <result property="title" column="title"/>
        <result property="businessType" column="business_type"/>
        <result property="businessId" column="business_id"/>
        <result property="businessData" column="business_data"/>
        <result property="submitterId" column="submitter_id"/>
        <result property="submitterName" column="submitter_name"/>
        <result property="submitTime" column="submit_time"/>
        <result property="currentNodeId" column="current_node_id"/>
        <result property="status" column="status"/>
        <result property="completionTime" column="completion_time"/>
        <result property="urgentLevel" column="urgent_level"/>
        <result property="formData" column="form_data"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
        <association property="workflowDefinition" javaType="ApprovalWorkflowDefinition" resultMap="WorkflowResult"/>
        <association property="currentNode" javaType="ApprovalNodeDefinition" resultMap="NodeResult"/>
        <collection property="tasks" javaType="java.util.List" resultMap="TaskResult"/>
        <collection property="histories" javaType="java.util.List" resultMap="HistoryResult"/>
    </resultMap>

    <resultMap id="WorkflowResult" type="ApprovalWorkflowDefinition">
        <id property="id" column="workflow_id"/>
        <result property="workflowCode" column="workflow_code"/>
        <result property="workflowName" column="workflow_name"/>
        <result property="workflowType" column="workflow_type"/>
        <result property="description" column="workflow_description"/>
    </resultMap>

    <resultMap id="NodeResult" type="ApprovalNodeDefinition">
        <id property="id" column="current_node_id"/>
        <result property="nodeCode" column="current_node_code"/>
        <result property="nodeName" column="current_node_name"/>
        <result property="nodeType" column="current_node_type"/>
    </resultMap>

    <resultMap id="TaskResult" type="ApprovalTask">
        <id property="id" column="task_id"/>
        <result property="taskNo" column="task_no"/>
        <result property="taskName" column="task_name"/>
        <result property="assigneeId" column="assignee_id"/>
        <result property="assigneeName" column="assignee_name"/>
        <result property="status" column="task_status"/>
    </resultMap>

    <resultMap id="HistoryResult" type="ApprovalHistory">
        <id property="id" column="history_id"/>
        <result property="action" column="action"/>
        <result property="operatorId" column="operator_id"/>
        <result property="operatorName" column="operator_name"/>
        <result property="operationTime" column="operation_time"/>
        <result property="comment" column="history_comment"/>
    </resultMap>

    <sql id="selectApprovalInstanceVo">
        select id,
               instance_no,
               workflow_id,
               title,
               business_type,
               business_id,
               business_data,
               submitter_id,
               submitter_name,
               submit_time,
               current_node_id,
               status,
               completion_time,
               urgent_level,
               form_data,
               create_by,
               create_time,
               update_by,
               update_time,
               remark
        from approval_instance
    </sql>

    <select id="selectApprovalInstanceList" parameterType="ApprovalInstance" resultMap="ApprovalInstanceResult">
        <include refid="selectApprovalInstanceVo"/>
        <where>
            <if test="instanceNo != null  and instanceNo != ''">and instance_no like concat('%', #{instanceNo}, '%')
            </if>
            <if test="workflowId != null ">and workflow_id = #{workflowId}</if>
            <if test="title != null  and title != ''">and title like concat('%', #{title}, '%')</if>
            <if test="businessType != null  and businessType != ''">and business_type = #{businessType}</if>
            <if test="businessId != null ">and business_id = #{businessId}</if>
            <if test="submitterId != null ">and submitter_id = #{submitterId}</if>
            <if test="submitterName != null  and submitterName != ''">and submitter_name like concat('%',
                #{submitterName}, '%')
            </if>
            <if test="status != null  and status != ''">and status = #{status}</if>
            <if test="urgentLevel != null  and urgentLevel != ''">and urgent_level = #{urgentLevel}</if>
            <if test="params != null and params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
                and date_format(submit_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
            </if>
            <if test="params != null and params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
                and date_format(submit_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
            </if>
        </where>
        order by submit_time desc
    </select>

    <!-- 根据工作流ID列表、时间范围和用户ID列表查询审批实例 -->
    <select id="selectApprovalInstancesByWorkflowIdsAndTimeRangeAndUsers" parameterType="map"
            resultMap="ApprovalInstanceResult">
        <include refid="selectApprovalInstanceVo"/>
        <where>
            <if test="workflowIds != null and workflowIds.size() > 0">
                and workflow_id in
                <foreach item="item" collection="workflowIds" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="beginTime != null"><!-- 开始时间检索 -->
                and submit_time &gt;= #{beginTime}
            </if>
            <if test="endTime != null"><!-- 结束时间检索 -->
                and submit_time &lt;= #{endTime}
            </if>
            <if test="userIds != null and userIds.size() > 0">
                and submitter_id in
                <foreach item="item" collection="userIds" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        order by submit_time desc
    </select>

    <!-- 查询部门及其子部门下的所有用户ID -->
    <select id="selectUserIdsByDeptAndSubDepts" parameterType="long" resultType="long">
        select user_id
        from sys_user
        where dept_id in (select dept_id
                          from sys_dept
                          where dept_id = #{deptId}
                             or parent_id = #{deptId}
                             or ancestors like concat('%,', #{deptId}, ',%')
                             or ancestors like concat(#{deptId}, ',%')
                             or ancestors like concat('%,', #{deptId})
                             or ancestors = #{deptId})
          and del_flag = '0'
    </select>

    <select id="selectApprovalInstanceById" parameterType="Long" resultMap="ApprovalInstanceResult">
        <include refid="selectApprovalInstanceVo"/>
        where id = #{id}
    </select>

    <select id="selectApprovalInstanceByNo" parameterType="String" resultMap="ApprovalInstanceResult">
        <include refid="selectApprovalInstanceVo"/>
        where instance_no = #{instanceNo}
    </select>

    <select id="selectApprovalInstanceByBusiness" resultMap="ApprovalInstanceResult">
        <include refid="selectApprovalInstanceVo"/>
        where business_type = #{businessType} and business_id = #{businessId}
        order by submit_time desc
    </select>

    <select id="selectApprovalInstanceBySubmitter" resultMap="ApprovalInstanceResult">
        <include refid="selectApprovalInstanceVo"/>
        where submitter_id = #{submitterId}
        <if test="status != null and status != ''">and status = #{status}</if>
        order by submit_time desc
    </select>

    <select id="selectApprovalInstanceDetail" parameterType="Long" resultMap="ApprovalInstanceResult">
        <include refid="selectApprovalInstanceVo"/>
        where id = #{id}
    </select>

    <select id="generateInstanceNo" resultType="String">
        SELECT CONCAT('INST', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), LPAD(FLOOR(RAND() * 1000), 3, '0')) AS new_instance_no
    </select>

    <insert id="insertApprovalInstance" parameterType="ApprovalInstance" useGeneratedKeys="true" keyProperty="id">
        insert into approval_instance
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="instanceNo != null and instanceNo != ''">instance_no,</if>
            <if test="workflowId != null">workflow_id,</if>
            <if test="title != null and title != ''">title,</if>
            <if test="businessType != null and businessType != ''">business_type,</if>
            <if test="businessId != null">business_id,</if>
            <if test="businessData != null">business_data,</if>
            <if test="submitterId != null">submitter_id,</if>
            <if test="submitterName != null and submitterName != ''">submitter_name,</if>
            <if test="submitTime != null">submit_time,</if>
            <if test="currentNodeId != null">current_node_id,</if>
            <if test="status != null">status,</if>
            <if test="completionTime != null">completion_time,</if>
            <if test="urgentLevel != null">urgent_level,</if>
            <if test="formData != null">form_data,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="instanceNo != null and instanceNo != ''">#{instanceNo},</if>
            <if test="workflowId != null">#{workflowId},</if>
            <if test="title != null and title != ''">#{title},</if>
            <if test="businessType != null and businessType != ''">#{businessType},</if>
            <if test="businessId != null">#{businessId},</if>
            <if test="businessData != null">#{businessData},</if>
            <if test="submitterId != null">#{submitterId},</if>
            <if test="submitterName != null and submitterName != ''">#{submitterName},</if>
            <if test="submitTime != null">#{submitTime},</if>
            <if test="currentNodeId != null">#{currentNodeId},</if>
            <if test="status != null">#{status},</if>
            <if test="completionTime != null">#{completionTime},</if>
            <if test="urgentLevel != null">#{urgentLevel},</if>
            <if test="formData != null">#{formData},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
        </trim>
    </insert>

    <update id="updateApprovalInstance" parameterType="ApprovalInstance">
        update approval_instance
        <trim prefix="SET" suffixOverrides=",">
            <if test="instanceNo != null and instanceNo != ''">instance_no = #{instanceNo},</if>
            <if test="workflowId != null">workflow_id = #{workflowId},</if>
            <if test="title != null and title != ''">title = #{title},</if>
            <if test="businessType != null and businessType != ''">business_type = #{businessType},</if>
            <if test="businessId != null">business_id = #{businessId},</if>
            <if test="businessData != null">business_data = #{businessData},</if>
            <if test="submitterId != null">submitter_id = #{submitterId},</if>
            <if test="submitterName != null and submitterName != ''">submitter_name = #{submitterName},</if>
            <if test="submitTime != null">submit_time = #{submitTime},</if>
            <if test="currentNodeId != null">current_node_id = #{currentNodeId},</if>
            <if test="status != null">status = #{status},</if>
            <if test="completionTime != null">completion_time = #{completionTime},</if>
            <if test="urgentLevel != null">urgent_level = #{urgentLevel},</if>
            <if test="formData != null">form_data = #{formData},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteApprovalInstanceById" parameterType="Long">
        delete
        from approval_instance
        where id = #{id}
    </delete>

    <delete id="deleteApprovalInstanceByIds" parameterType="String">
        delete from approval_instance where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>