<?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.TaskListIssueMapper">

    <select id="listPage" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListIssueDO">
        SELECT tli.*,
        tl.task_list_name AS taskListName,
        tl.task_level AS taskLevel,
        tl.responsibility_post AS responsibilityPost,
        exec_corp.corp_name AS executeCorpName,
        exec_dept.name AS executeDepartmentName,
        exec_user.name AS executeUserName,
        rating_dept.name AS ratingDepartmentName,
        rating_user.name AS ratingUserName,
        IFNULL(te_agg.task_count, 0) AS taskCount,
        CASE WHEN te_agg.has_abnormal &gt; 0 THEN 2 ELSE 1 END AS feedbackStatus,
        tl.switch_flag AS switchFlag
        FROM safety_accountability_task_list_issue tli
        LEFT JOIN safety_accountability_task_list tl ON tli.task_list_id = tl.task_list_id AND tl.delete_enum = 'FALSE'
        LEFT JOIN corp_info exec_corp ON tli.execute_corp_id = exec_corp.id
        LEFT JOIN department exec_dept ON tli.execute_department_id = exec_dept.id
        LEFT JOIN user exec_user ON tli.execute_user_id = exec_user.id
        LEFT JOIN department rating_dept ON tli.rating_department_id = rating_dept.id
        LEFT JOIN user rating_user ON tli.rating_user_id = rating_user.id
        LEFT JOIN (
            SELECT task_issue_id,
            COUNT(*) AS task_count,
            SUM(CASE WHEN feedback_status = 2 THEN 1 ELSE 0 END) AS has_abnormal
            FROM safety_accountability_task_execution
            WHERE delete_enum = 'FALSE'
            GROUP BY task_issue_id
        ) te_agg ON tli.task_issue_id = te_agg.task_issue_id
        <where>
            tli.delete_enum = 'FALSE'
            <if test="params.taskListId != null and params.taskListId != ''">
                AND tli.task_list_id = #{params.taskListId}
            </if>
            <if test="params.executeCorpId != null">
                AND tli.execute_corp_id = #{params.executeCorpId}
            </if>
            <if test="params.executeUserId != null">
                AND tli.execute_user_id = #{params.executeUserId}
            </if>
            <if test="params.taskListName != null and params.taskListName != ''">
                AND tl.task_list_name LIKE CONCAT('%', #{params.taskListName}, '%')
            </if>
            <if test="params.taskLevel != null">
                AND tl.task_level = #{params.taskLevel}
            </if>
            <if test="params.issueStatus != null">
                AND tli.issue_status = #{params.issueStatus}
            </if>
            <if test="params.status != null">
                AND tli.status = #{params.status}
            </if>
            <if test="params.scoreStatus != null">
                <!-- 状态必须是 2 已完成 或 3 已关闭 -->
                AND tli.status IN (2, 3)
                <!-- scoreStatus = 0：分数为空 -->
                <if test="params.scoreStatus == 0">
                    AND tli.rating_score IS NULL
                </if>
                <!-- scoreStatus = 1：分数不为空 -->
                <if test="params.scoreStatus == 1">
                    AND tli.rating_score IS NOT NULL
                </if>
            </if>
            <if test="params.feedbackStatus != null and params.feedbackStatus == 2">
                AND te_agg.has_abnormal &gt; 0
            </if>
            <if test="params.feedbackStatus != null and params.feedbackStatus == 1">
                AND (te_agg.has_abnormal = 0 OR te_agg.has_abnormal IS NULL)
            </if>
            <if test="params.statusList != null and params.statusList.size > 0">
                AND tli.status IN
                <foreach collection="params.statusList" item="status" open="(" separator="," close=")">
                    #{status}
                </foreach>
            </if>
        </where>
        ORDER BY tli.create_time DESC
    </select>

    <select id="listPageByTaskListQry" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListIssueDO">
        SELECT tli.*,
        tl.task_list_name AS taskListName,
        tl.task_level AS taskLevel,
        tl.responsibility_post AS responsibilityPost,
        tl.switch_flag AS switchFlag,
        exec_dept.name AS executeDepartmentName,
        exec_user.name AS executeUserName,
        rating_dept.name AS ratingDepartmentName,
        rating_user.name AS ratingUserName,
        IFNULL(te_agg.task_count, 0) AS taskCount,
        CASE WHEN te_agg.has_abnormal &gt; 0 THEN 2 ELSE 1 END AS feedbackStatus,
        tl.switch_flag AS switchFlag
        FROM safety_accountability_task_list_issue tli
        LEFT JOIN safety_accountability_task_list tl ON tli.task_list_id = tl.task_list_id AND tl.delete_enum = 'FALSE'
        LEFT JOIN department exec_dept ON tli.execute_department_id = exec_dept.id
        LEFT JOIN user exec_user ON tli.execute_user_id = exec_user.id
        LEFT JOIN department rating_dept ON tli.rating_department_id = rating_dept.id
        LEFT JOIN user rating_user ON tli.rating_user_id = rating_user.id
        LEFT JOIN (
            SELECT task_issue_id,
            COUNT(*) AS task_count,
            SUM(CASE WHEN feedback_status = 2 THEN 1 ELSE 0 END) AS has_abnormal
            FROM safety_accountability_task_execution
            WHERE delete_enum = 'FALSE'
            GROUP BY task_issue_id
        ) te_agg ON tli.task_issue_id = te_agg.task_issue_id
        <where>
            tli.issue_status = 1 AND tli.delete_enum = 'FALSE'
            <if test="params.executeCorpId != null">
                AND tli.execute_corp_id = #{params.executeCorpId}
            </if>
            <if test="params.taskListName != null and params.taskListName != ''">
                AND tl.task_list_name LIKE CONCAT('%', #{params.taskListName}, '%')
            </if>
            <if test="params.taskLevel != null">
                AND tl.task_level = #{params.taskLevel}
            </if>
            <if test="params.switchFlag != null">
                AND tl.switch_flag = #{params.switchFlag}
            </if>
            <if test="params.feedbackStatus != null and params.feedbackStatus == 2">
                AND te_agg.has_abnormal &gt; 0
            </if>
            <if test="params.feedbackStatus != null and params.feedbackStatus == 1">
                AND (te_agg.has_abnormal = 0 OR te_agg.has_abnormal IS NULL)
            </if>
        </where>
        ORDER BY tli.issue_time DESC
    </select>

    <select id="getByTaskIssueId" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListIssueDO">
        SELECT tli.*,
               tl.task_list_name AS taskListName,
               tl.task_level AS taskLevel,
               tl.responsibility_post AS responsibilityPost,
               exec_corp.corp_name AS executeCorpName,
               exec_dept.name AS executeDepartmentName,
               exec_user.name AS executeUserName,
               rating_dept.name AS ratingDepartmentName,
               rating_user.name AS ratingUserName,
               IFNULL(te_agg.task_count, 0) AS taskCount,
               CASE WHEN te_agg.has_abnormal &gt; 0 THEN 2 ELSE 1 END AS feedbackStatus,
               tl.switch_flag AS switchFlag
        FROM safety_accountability_task_list_issue tli
                 LEFT JOIN safety_accountability_task_list tl ON tli.task_list_id = tl.task_list_id AND tl.delete_enum = 'FALSE'
                 LEFT JOIN corp_info exec_corp ON tli.execute_corp_id = exec_corp.id
                 LEFT JOIN department exec_dept ON tli.execute_department_id = exec_dept.id
                 LEFT JOIN user exec_user ON tli.execute_user_id = exec_user.id
                 LEFT JOIN department rating_dept ON tli.rating_department_id = rating_dept.id
                 LEFT JOIN user rating_user ON tli.rating_user_id = rating_user.id
                 LEFT JOIN (
            SELECT task_issue_id,
                   COUNT(*) AS task_count,
                   SUM(CASE WHEN feedback_status = 2 THEN 1 ELSE 0 END) AS has_abnormal
            FROM safety_accountability_task_execution
            WHERE delete_enum = 'FALSE'
            GROUP BY task_issue_id
        ) te_agg ON tli.task_issue_id = te_agg.task_issue_id
        WHERE tli.task_issue_id = #{taskIssueId} AND tli.delete_enum = 'FALSE'
    </select>

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

    <select id="listByIssueInfo" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListIssueDO">
        SELECT * FROM safety_accountability_task_list_issue
        WHERE
            delete_enum = 'FALSE'
          AND task_list_id = #{taskListId}
          AND execute_corp_id = #{executeCorpId}
          AND execute_department_id = #{executeDepartmentId}
          AND execute_user_id = #{executeUserId}
          AND period_start_time &lt; #{periodEndTime}
          AND period_end_time &gt; #{periodStartTime}
        ORDER BY create_time DESC
    </select>

    <select id="listExpiredInProgress" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListIssueDO">
        SELECT * FROM safety_accountability_task_list_issue
        WHERE status = 1 AND issue_status = 1 AND (period_end_time IS NOT NULL AND period_end_time &lt; NOW()) AND delete_enum = 'FALSE'
    </select>

    <select id="listIssuedWithPeriod" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListIssueDO">
        SELECT * FROM safety_accountability_task_list_issue
        WHERE issue_status = 1 AND status = 1 AND period_start_time IS NOT NULL AND period_end_time IS NOT NULL
        AND period_end_time > DATE_SUB(NOW(), INTERVAL 1 MONTH) AND delete_enum = 'FALSE'
    </select>

</mapper>
