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

    <select id="listPage" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListDO">
        SELECT tl.*,
        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(td_agg.task_count, 0) AS taskCount,
        CASE WHEN td_agg.has_abnormal &gt; 0 THEN 2 ELSE 1 END AS feedbackStatus
        FROM safety_accountability_task_list tl
        LEFT JOIN corp_info exec_corp ON tl.execute_corp_id = exec_corp.id
        LEFT JOIN department exec_dept ON tl.execute_department_id = exec_dept.id
        LEFT JOIN user exec_user ON tl.execute_user_id = exec_user.id
        LEFT JOIN department rating_dept ON tl.rating_department_id = rating_dept.id
        LEFT JOIN user rating_user ON tl.rating_user_id = rating_user.id
        LEFT JOIN (
            SELECT task_list_id,
            COUNT(*) AS task_count,
            SUM(CASE WHEN feedback_status = 2 THEN 1 ELSE 0 END) AS has_abnormal
            FROM safety_accountability_task_detail
            WHERE delete_enum = 'FALSE'
            GROUP BY task_list_id
        ) td_agg ON tl.task_list_id = td_agg.task_list_id
        <where>
            tl.delete_enum = 'FALSE'
            <if test="params.createCorpId != null">
                AND tl.create_corp_id = #{params.createCorpId}
            </if>
            <if test="params.executeCorpId != null">
                AND tl.execute_corp_id = #{params.executeCorpId}
            </if>
            <if test="params.executeUserId != null">
                AND tl.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.switchFlag != null">
                AND tl.switch_flag = #{params.switchFlag}
            </if>
            <if test="params.feedbackStatus != null and params.feedbackStatus == 2">
                AND td_agg.has_abnormal &gt; 0
            </if>
            <if test="params.feedbackStatus != null and params.feedbackStatus == 1">
                AND (td_agg.has_abnormal = 0 OR td_agg.has_abnormal IS NULL)
            </if>
        </where>
        ORDER BY tl.create_time DESC
    </select>

    <select id="getInfoById" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListDO">
        SELECT tl.*,
        exec_dept.name   AS executeDepartmentName,
        exec_user.name   AS executeUserName,
        rating_dept.name  AS ratingDepartmentName,
        rating_user.name  AS ratingUserName,
        td_agg.task_count AS taskCount,
        CASE WHEN td_agg.has_abnormal &gt; 0 THEN 2 ELSE 1 END AS feedbackStatus
        FROM safety_accountability_task_list tl
        LEFT JOIN department exec_dept ON tl.execute_department_id = exec_dept.id
        LEFT JOIN user exec_user ON tl.execute_user_id = exec_user.id
        LEFT JOIN department rating_dept ON tl.rating_department_id = rating_dept.id
        LEFT JOIN user rating_user ON tl.rating_user_id = rating_user.id
        LEFT JOIN (
            SELECT task_list_id,
            COUNT(*) AS task_count,
            SUM(CASE WHEN feedback_status = 2 THEN 1 ELSE 0 END) AS has_abnormal
            FROM safety_accountability_task_detail
            WHERE delete_enum = 'FALSE'
            GROUP BY task_list_id
        ) td_agg ON tl.task_list_id = td_agg.task_list_id
        WHERE tl.id = #{id} and tl.delete_enum = 'FALSE'
    </select>

    <select id="getByTaskListId" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListDO">
        SELECT tl.*,
        exec_dept.name   AS executeDepartmentName,
        exec_user.name   AS executeUserName,
        rating_dept.name  AS ratingDepartmentName,
        rating_user.name  AS ratingUserName,
        td_agg.task_count AS taskCount,
        CASE WHEN td_agg.has_abnormal &gt; 0 THEN 2 ELSE 1 END AS feedbackStatus
        FROM safety_accountability_task_list tl
        LEFT JOIN department exec_dept ON tl.execute_department_id = exec_dept.id
        LEFT JOIN user exec_user ON tl.execute_user_id = exec_user.id
        LEFT JOIN department rating_dept ON tl.rating_department_id = rating_dept.id
        LEFT JOIN user rating_user ON tl.rating_user_id = rating_user.id
        LEFT JOIN (
            SELECT task_list_id,
            COUNT(*) AS task_count,
            SUM(CASE WHEN feedback_status = 2 THEN 1 ELSE 0 END) AS has_abnormal
            FROM safety_accountability_task_detail
            WHERE delete_enum = 'FALSE'
            GROUP BY task_list_id
        ) td_agg ON tl.task_list_id = td_agg.task_list_id
        WHERE tl.task_list_id = #{taskListId} and tl.delete_enum = 'FALSE'
    </select>

    <select id="getInfoByTaskListId" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListDO">
        SELECT *
        FROM safety_accountability_task_list
        WHERE task_list_id = #{taskListId} and delete_enum = 'FALSE'
    </select>

    <select id="list" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListDO">
        SELECT tl.*,
        exec_dept.name   AS executeDepartmentName,
        exec_user.name   AS executeUserName,
        rating_dept.name  AS ratingDepartmentName,
        rating_user.name  AS ratingUserName,
        td_agg.task_count AS taskCount,
        CASE WHEN td_agg.has_abnormal &gt; 0 THEN 2 ELSE 1 END AS feedbackStatus
        FROM safety_accountability_task_list tl
        LEFT JOIN department exec_dept ON tl.execute_department_id = exec_dept.id
        LEFT JOIN user exec_user ON tl.execute_user_id = exec_user.id
        LEFT JOIN department rating_dept ON tl.rating_department_id = rating_dept.id
        LEFT JOIN user rating_user ON tl.rating_user_id = rating_user.id
        LEFT JOIN (
            SELECT task_list_id,
            COUNT(*) AS task_count,
            SUM(CASE WHEN feedback_status = 2 THEN 1 ELSE 0 END) AS has_abnormal
            FROM safety_accountability_task_detail
            WHERE delete_enum = 'FALSE'
            GROUP BY task_list_id
        ) td_agg ON tl.task_list_id = td_agg.task_list_id
        <where>
            tl.delete_enum = 'FALSE'
            <if test="params.createCorpId != null">
                AND tl.create_corp_id = #{params.createCorpId}
            </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>
        </where>
        ORDER BY tl.create_time DESC
    </select>

    <select id="listExpiredInProgress" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListDO">
        SELECT * FROM safety_accountability_task_list
        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.TaskListDO">
        SELECT * FROM safety_accountability_task_list
        WHERE issue_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>

    <select id="corpStatistics" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.CorpStatisticsDO">
        SELECT tl.execute_corp_id AS corpId,
        ci.corp_name AS corpName,
        COUNT(DISTINCT td.task_detail_id) AS totalTaskCount,
        SUM(CASE WHEN td.task_status = 2 THEN 1 ELSE 0 END) AS completedTaskCount,
        SUM(CASE WHEN td.task_status = 3 THEN 1 ELSE 0 END) AS closedTaskCount
        FROM safety_accountability_task_list tl
        LEFT JOIN safety_accountability_task_detail td ON tl.task_list_id = td.task_list_id AND td.delete_enum = 'FALSE'
        LEFT JOIN corp_info ci ON tl.execute_corp_id = ci.id
        WHERE tl.issue_status = 1 AND tl.delete_enum = 'FALSE'
        <if test="params.corpName != null and params.corpName != ''">
            AND ci.corp_name LIKE CONCAT('%', #{params.corpName}, '%')
        </if>
        GROUP BY tl.execute_corp_id, ci.corp_name
        ORDER BY tl.create_corp_id
    </select>

    <select id="evaluationList" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.EvaluationListDO">
        SELECT tl.execute_corp_id AS corpId,
        ci.corp_name AS corpName,
        COUNT(*) AS listCount
        FROM safety_accountability_task_list tl
        LEFT JOIN corp_info ci ON tl.execute_corp_id = ci.id
        WHERE tl.issue_status = 1 AND tl.delete_enum = 'FALSE' AND tl.status IN (2,3)
        <if test="params.corpName != null and params.corpName != ''">
            AND ci.corp_name LIKE CONCAT('%', #{params.corpName}, '%')
        </if>
        <if test="params.scoreStatus != null and params.scoreStatus == 0">
            AND tl.rating_score IS NULL
        </if>
        <if test="params.scoreStatus != null and params.scoreStatus == 1">
            AND tl.rating_score IS NOT NULL
        </if>
        GROUP BY tl.execute_corp_id, ci.corp_name
        ORDER BY tl.execute_corp_id
    </select>

    <select id="issueStatistics" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.IssueStatisticsDO">
        SELECT tl.execute_corp_id AS corpId,
        ci.corp_name AS corpName,
        COUNT(*) AS issueCount
        FROM safety_accountability_task_list tl
        LEFT JOIN corp_info ci ON tl.execute_corp_id = ci.id
        WHERE tl.issue_status = 1 AND tl.delete_enum = 'FALSE'
        <if test="params.corpName != null and params.corpName != ''">
            AND ci.corp_name LIKE CONCAT('%', #{params.corpName}, '%')
        </if>
        GROUP BY tl.execute_corp_id, ci.corp_name
        ORDER BY tl.execute_corp_id
    </select>

</mapper>
