<?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.zcloud.safetyDutyList.persistence.mapper.tasklist.TaskExecutionMapper">

    <select id="listPage" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskExecutionDO">
        SELECT te.*,
        td.execute_content AS executeContent,
        td.feedback_cycle_type AS feedbackCycleType,
        td.task_score AS taskScore,
        tl.task_list_name AS taskListName,
        tl.task_level AS taskLevel,
        tl.responsibility_post AS responsibilityPost,
        rating_dept.name AS ratingDepartmentName,
        rating_user.name AS ratingUserName,
        IFNULL(fb_agg.feedback_count, 0) AS feedbackCount,
        IFNULL(fb_agg.current_period_feedback, 0) AS currentPeriodFeedback
        FROM safety_accountability_task_execution te
        LEFT JOIN safety_accountability_task_detail td ON te.task_detail_id = td.task_detail_id AND td.delete_enum = 'FALSE'
        LEFT JOIN safety_accountability_task_list tl ON te.task_list_id = tl.task_list_id AND tl.delete_enum = 'FALSE'
        LEFT JOIN department rating_dept ON te.rating_department_id = rating_dept.id
        LEFT JOIN user rating_user ON te.rating_user_id = rating_user.id
        LEFT JOIN (
            SELECT task_execution_id,
            COUNT(*) AS feedback_count,
            MAX(CASE WHEN feedback_period_flag = DATE_FORMAT(NOW(), '%Y-%m') THEN 1 ELSE 0 END) AS current_period_feedback
            FROM safety_accountability_feedback
            WHERE delete_enum = 'FALSE'
            GROUP BY task_execution_id
        ) fb_agg ON te.task_execution_id = fb_agg.task_execution_id
        <where>
            te.delete_enum = 'FALSE'
            <if test="params.taskIssueId != null and params.taskIssueId != ''">
                AND te.task_issue_id = #{params.taskIssueId}
            </if>
            <if test="params.taskListId != null and params.taskListId != ''">
                AND te.task_list_id = #{params.taskListId}
            </if>
            <if test="params.taskContent != null and params.taskContent != ''">
                AND td.execute_content LIKE CONCAT('%', #{params.taskContent}, '%')
            </if>
            <if test="params.feedbackCycleType != null and params.feedbackCycleType != ''">
                AND td.feedback_cycle_type = #{params.feedbackCycleType}
            </if>
            <if test="params.feedbackStatus != null and params.feedbackStatus != ''">
                AND te.feedback_status = #{params.feedbackStatus}
            </if>
            <if test="params.taskStatus != null and params.taskStatus != ''">
                AND te.task_status = #{params.taskStatus}
            </if>
        </where>
        ORDER BY te.id ASC
    </select>

    <select id="getByTaskExecutionId" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskExecutionDO">
        SELECT te.*,
               td.execute_content AS executeContent,
               td.feedback_cycle_type AS feedbackCycleType,
               td.task_score AS taskScore,
               tl.task_list_name AS taskListName,
               tl.task_level AS taskLevel,
               tl.responsibility_post AS responsibilityPost,
               rating_dept.name AS ratingDepartmentName,
               rating_user.name AS ratingUserName,
               IFNULL(fb_agg.feedback_count, 0) AS feedbackCount,
               IFNULL(fb_agg.current_period_feedback, 0) AS currentPeriodFeedback
        FROM safety_accountability_task_execution te
                 LEFT JOIN safety_accountability_task_detail td ON te.task_detail_id = td.task_detail_id AND td.delete_enum = 'FALSE'
                 LEFT JOIN safety_accountability_task_list tl ON te.task_list_id = tl.task_list_id AND tl.delete_enum = 'FALSE'
                 LEFT JOIN department rating_dept ON te.rating_department_id = rating_dept.id
                 LEFT JOIN user rating_user ON te.rating_user_id = rating_user.id
                 LEFT JOIN (
            SELECT task_execution_id,
                   COUNT(*) AS feedback_count,
                   MAX(CASE WHEN feedback_period_flag = DATE_FORMAT(NOW(), '%Y-%m') THEN 1 ELSE 0 END) AS current_period_feedback
            FROM safety_accountability_feedback
            WHERE delete_enum = 'FALSE'
            GROUP BY task_execution_id
        ) fb_agg ON te.task_execution_id = fb_agg.task_execution_id
        WHERE te.task_execution_id = #{taskExecutionId} AND te.delete_enum = 'FALSE'
    </select>

    <select id="listByTaskIssueId" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskExecutionDO">
        SELECT te.*,
        td.execute_content AS executeContent,
        td.feedback_cycle_type AS feedbackCycleType,
        td.task_score AS taskScore
        FROM safety_accountability_task_execution te
        LEFT JOIN safety_accountability_task_detail td ON te.task_detail_id = td.task_detail_id AND td.delete_enum = 'FALSE'
        WHERE te.task_issue_id = #{taskIssueId} AND te.delete_enum = 'FALSE'
        ORDER BY te.id ASC
    </select>

    <select id="listByTaskListId" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskExecutionDO">
        SELECT * FROM safety_accountability_task_execution
        WHERE task_list_id = #{taskListId} AND delete_enum = 'FALSE'
        ORDER BY id ASC
    </select>

    <select id="listByTaskDetailId" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskExecutionDO">
        SELECT * FROM safety_accountability_task_execution
        WHERE task_detail_id = #{taskDetailId} AND delete_enum = 'FALSE'
        ORDER BY id ASC
    </select>

</mapper>
