<?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.*
        FROM safety_accountability_task_list tl
        <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="getInfoById" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.TaskListDO">
        SELECT * FROM safety_accountability_task_list
        WHERE id = #{id} AND delete_enum = 'FALSE'
    </select>

    <select id="getByTaskListId" 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="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.*
        FROM safety_accountability_task_list tl
        <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="corpStatistics" resultType="com.zcloud.safetyDutyList.persistence.dataobject.tasklist.CorpStatisticsDO">
        SELECT tli.execute_corp_id AS corpId,
        ci.corp_name AS corpName,
        COUNT(DISTINCT te.task_execution_id) AS totalTaskCount,
        SUM(CASE WHEN te.task_status = 2 THEN 1 ELSE 0 END) AS completedTaskCount,
        SUM(CASE WHEN te.task_status = 3 THEN 1 ELSE 0 END) AS closedTaskCount
        FROM safety_accountability_task_list_issue tli
        LEFT JOIN safety_accountability_task_execution te ON tli.task_issue_id = te.task_issue_id AND te.delete_enum = 'FALSE'
        LEFT JOIN corp_info ci ON tli.execute_corp_id = ci.id
        WHERE tli.issue_status = 1 AND tli.delete_enum = 'FALSE'
        <if test="params.corpName != null and params.corpName != ''">
            AND ci.corp_name LIKE CONCAT('%', #{params.corpName}, '%')
        </if>
        GROUP BY tli.execute_corp_id, ci.corp_name
        ORDER BY tli.execute_corp_id
    </select>

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

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

</mapper>
