<?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.testor.module.hazard.dao.THazardWorkPlanCheckDao">
    <!-- 开启二级缓存 -->
    <!-- <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/> -->
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.testor.module.hazard.model.domain.THazardWorkPlanCheck">
        <id column="id" property="id"/>
        <id column="plan_id" property="planId"/>
        <id column="check_time" property="checkTime"/>
        <id column="is_compliant" property="isCompliant"/>
        <id column="problem_type" property="problemType"/>
        <id column="check_description" property="checkDescription"/>
        <id column="check_images" property="checkImages"/>
        <id column="check_signature" property="checkSignature"/>
        <id column="check_status" property="checkStatus"/>
        <id column="code" property="code"/>
        <id column="workType" property="workType"/>
        <id column="workLevel" property="workLevel"/>
        <id column="stakeholderWork" property="stakeholderWork"/>
        <id column="check_signature_time" property="checkSignatureTime"/>
        <id column="check_name" property="checkName"/>
        <id column="check_id" property="checkId"/>
        <id column="basic_id" property="basicId"/>
    </resultMap>

    <select id="selectTHazardWorkPlanCheckList" resultMap="BaseResultMap">
        select hwpc.*,hwp.code code,hwp.work_type workType,hwp.work_level workLevel,hwp.stakeholder_work stakeholderWork
        from t_hazard_work_plan_check hwpc
        left join t_hazard_work_plan hwp on hwpc.plan_id = hwp.id
        where hwpc.status = '0' and hwpc.plan_id = #{param.planId}
        order by hwpc.create_date desc
    </select>

    <select id="selectAppTHazardWorkPlanCheckList" resultMap="BaseResultMap">
        select hwpc.*,hwp.code code,hwp.work_type workType,hwp.work_level workLevel,hwp.stakeholder_work stakeholderWork
        from t_hazard_work_plan_check hwpc
        left join t_hazard_work_plan hwp on hwpc.plan_id = hwp.id
        left join
        t_sys_dict_data dd on hwp.work_type = dd.dict_data_id
        left join
        t_sys_dict_data dd2 on hwp.work_level = dd2.dict_data_id
        left join
        t_sys_dict_data dd3 on hwpc.problem_type = dd3.dict_data_id
        where hwpc.status = '0'  and hwpc.create_by=#{param.createBy}
        <if test="param.planId!=null and param.planId!=''">
            and hwpc.plan_id = #{param.planId}
        </if>
        <if test="param.workType!=null and param.workType!=''">
            and hwp.work_type = #{param.workType}
        </if>
        <if test="param.keywords!=null and param.keywords!=''">
            AND (
            hwpc.basic_unit LIKE CONCAT('%', #{param.keywords}, '%')
            OR dd.dict_value LIKE CONCAT('%', #{param.keywords}, '%')
            OR dd2.dict_value LIKE CONCAT('%', #{param.keywords}, '%')
            OR hwpc.check_description LIKE CONCAT('%', #{param.keywords}, '%')
            OR (
            CASE
            WHEN hwpc.is_compliant = 1 THEN '是'
            WHEN hwpc.is_compliant = 0 THEN '否'
            ELSE ''
            END
            ) LIKE CONCAT('%', #{param.keywords}, '%')
            OR dd3.dict_value LIKE CONCAT('%', #{param.keywords}, '%')
            OR (
            CASE
            WHEN hwpc.check_status = 1 THEN '暂存'
            WHEN hwpc.check_status = 2 THEN '已完成'
            ELSE ''
            END
            ) LIKE CONCAT('%', #{param.keywords}, '%')
            )
        </if>
        order by hwpc.check_time desc
    </select>

    <select id="selectTHazardWorkPlanCheckManageList" resultMap="BaseResultMap">
        select hwpc.*,hwp.code code,hwp.work_type workType,hwp.work_level workLevel,hwp.stakeholder_work stakeholderWork
        from t_hazard_work_plan_check hwpc
        left join t_hazard_work_plan hwp on hwpc.plan_id = hwp.id
        where hwpc.status = '0'
        <if test="param.code!=null and param.code!=''">
            and hwp.code like concat('%', #{param.code}, '%')
        </if>
        <if test="param.workType!=null and param.workType!=''">
            and hwp.work_type = #{param.workType}
        </if>
        <if test="param.workLevel!=null and param.workLevel!=''">
            and hwp.work_level = #{param.workLevel}
        </if>
        <if test="param.stakeholderWork!=null and param.stakeholderWork!=''">
            and hwp.stakeholder_work = #{param.stakeholderWork}
        </if>
        <if test="param.isCompliant!=null and param.isCompliant!=''">
            and hwpc.is_compliant = #{param.isCompliant}
        </if>
        <if test="param.problemType!=null and param.problemType!=''">
            and hwpc.problem_type = #{param.problemType}
        </if>
        <if test="param.checkDescription!=null and param.checkDescription!=''">
            and hwpc.check_description like concat('%', #{param.checkDescription}, '%')
        </if>
        <if test="param.checkName!=null and param.checkName!=''">
            and hwpc.check_name like concat('%', #{param.checkName}, '%')
        </if>
        <if test="param.startTime != null and param.startTime != ''  and param.endTime != null and param.endTime != ''">
            and hwpc.check_time between #{param.startTime} and #{param.endTime}
        </if>
        <if test="param.orgIdList != null and param.orgIdList.size()>0">
            and (
            <foreach item="id" collection="param.orgIdList" separator=" OR ">
                hwpc.basic_id LIKE concat('%', #{id}, '%')
            </foreach>
            )
        </if>
        order by hwpc.check_time desc
    </select>

    <select id="selectTHazardWorkPlanCheckManageExportList" resultMap="BaseResultMap">
        select
        hwpc.id,hwpc.plan_id,hwpc.check_time,hwpc.is_compliant,dd4.dict_value problem_type,hwpc.check_description,hwpc.check_images,hwpc.check_signature,hwpc.status,
        hwpc.create_by,hwpc.create_date,hwpc.update_by,hwpc.update_date,hwpc.remarks,hwpc.check_unit,hwpc.basic_unit,hwpc.check_status,hwpc.check_signature_time,hwpc.check_name,hwpc.check_id,hwpc.basic_id,
        hwp.code code,
        dd.dict_value as workType,
        dd2.dict_value workLevel,
        dd3.dict_value stakeholderWork
        from
        t_hazard_work_plan_check hwpc
        left join
        t_hazard_work_plan hwp on hwpc.plan_id = hwp.id
        left join
        t_sys_dict_data dd on hwp.work_type = dd.dict_data_id
        left join
        t_sys_dict_data dd2 on hwp.work_level = dd2.dict_data_id
        left join
        t_sys_dict_data dd3 on hwp.stakeholder_work = dd3.dict_data_id
        left join
        t_sys_dict_data dd4 on hwpc.problem_type = dd4.dict_data_id
        where hwpc.status = '0'
        <if test="param.code!=null and param.code!=''">
            and hwp.code like concat('%', #{param.code}, '%')
        </if>
        <if test="param.workType!=null and param.workType!=''">
            and hwp.work_type = #{param.workType}
        </if>
        <if test="param.workLevel!=null and param.workLevel!=''">
            and hwp.work_level = #{param.workLevel}
        </if>
        <if test="param.stakeholderWork!=null and param.stakeholderWork!=''">
            and hwp.stakeholder_work = #{param.stakeholderWork}
        </if>
        <if test="param.isCompliant!=null and param.isCompliant!=''">
            and hwpc.is_compliant = #{param.isCompliant}
        </if>
        <if test="param.problemType!=null and param.problemType!=''">
            and hwpc.problem_type = #{param.problemType}
        </if>
        <if test="param.checkDescription!=null and param.checkDescription!=''">
            and hwpc.check_description like concat('%', #{param.checkDescription}, '%')
        </if>
        <if test="param.checkName!=null and param.checkName!=''">
            and hwpc.check_name like concat('%', #{param.checkName}, '%')
        </if>
        <if test="param.startTime != null and param.startTime != ''  and param.endTime != null and param.endTime != ''">
            and hwpc.check_time between #{param.startTime} and #{param.endTime}
        </if>
        <if test="param.orgIdList != null and param.orgIdList.size()>0">
            and (
            <foreach item="id" collection="param.orgIdList" separator=" OR ">
                hwpc.basic_id LIKE concat('%', #{id}, '%')
            </foreach>
            )
        </if>
        order by hwpc.check_time desc
    </select>

    <select id="detail" resultMap="BaseResultMap">
        select hwpc.*,hwp.code code,hwp.work_type workType,hwp.work_level workLevel,hwp.stakeholder_work stakeholderWork
        from t_hazard_work_plan_check hwpc
        left join t_hazard_work_plan hwp on hwpc.plan_id = hwp.id
        where hwpc.status = '0' and hwpc.id = #{id}
    </select>

    <select id="selectCheckComplianceStatistics" resultType="com.testor.module.hazard.model.vo.HazardWorkPlanCheckComplianceStatisticsVO">
        SELECT
        COALESCE(COUNT(*), 0) AS checkCount,
        COALESCE(SUM(CASE WHEN hwpc.is_compliant = 1 THEN 1 ELSE 0 END), 0) AS checkQualifiedCount,
        COALESCE(ROUND(
        CASE
        WHEN COUNT(*) = 0 THEN 0
        ELSE SUM(CASE WHEN hwpc.is_compliant = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)
        END,
        2
        ), 0) AS complianceRatePercent
        FROM
        t_hazard_work_plan_check hwpc
        LEFT JOIN t_hazard_work_plan hwp ON hwpc.plan_id = hwp.id
        WHERE
        hwpc.status = '0'
        <if test="param.orgIdList != null and param.orgIdList.size()>0">
            and (
            <foreach item="id" collection="param.orgIdList" separator=" OR ">
                hwpc.basic_id LIKE concat('%', #{id}, '%')
            </foreach>
            )
        </if>
        <if test="param.workType!=null and param.workType!=''">
            and hwp.work_type = #{param.workType}
        </if>
        <if test="param.workLevel!=null and param.workLevel!=''">
            and hwp.work_level = #{param.workLevel}
        </if>
        <if test="param.stakeholderWork!=null and param.stakeholderWork!=''">
            and hwp.stakeholder_work = #{param.stakeholderWork}
        </if>
        <if test="param.startTime != null and param.startTime != ''  and param.endTime != null and param.endTime != ''">
            and hwpc.check_time between #{param.startTime} and #{param.endTime}
        </if>
    </select>

    <select id="selectCheckProblemTypeStatistics" resultType="com.testor.module.hazard.model.vo.HazardWorkPlanCheckProblemTypeStatisticsVO">
        select dd.dict_value problemName,count(*) problemCount
        from  t_sys_dict_data dd
        left join t_hazard_work_plan_check hwpc on hwpc.problem_type = dd.dict_data_id
        LEFT JOIN t_hazard_work_plan hwp ON hwpc.plan_id = hwp.id
        where hwpc.status = '0' AND hwpc.is_compliant = 0 AND hwpc.problem_type IS NOT NULL
        <if test="param.orgIdList != null and param.orgIdList.size()>0">
            and (
            <foreach item="id" collection="param.orgIdList" separator=" OR ">
                hwpc.basic_id LIKE concat('%', #{id}, '%')
            </foreach>
            )
        </if>
        <if test="param.workType!=null and param.workType!=''">
            and hwp.work_type = #{param.workType}
        </if>
        <if test="param.workLevel!=null and param.workLevel!=''">
            and hwp.work_level = #{param.workLevel}
        </if>
        <if test="param.stakeholderWork!=null and param.stakeholderWork!=''">
            and hwp.stakeholder_work = #{param.stakeholderWork}
        </if>
        <if test="param.startTime != null and param.startTime != ''  and param.endTime != null and param.endTime != ''">
            and hwpc.check_time between #{param.startTime} and #{param.endTime}
        </if>
        group by dd.dict_value
    </select>

    <select id="selectCheckProblemCountStatistics" resultType="com.testor.module.hazard.model.vo.HazardWorkPlanCheckProblemCountStatisticsVO">
        SELECT
        dates.timeStr AS problemDate,
        COUNT(hwpc.id) AS problemCount
        FROM
        (SELECT generate_series(
        #{param.startTime}::timestamp,
        #{param.endTime}::timestamp,
        '1 day'::interval
        )::date AS timeStr) dates
        LEFT JOIN t_hazard_work_plan_check hwpc ON hwpc.check_time::date = dates.timeStr
        AND hwpc.status = '0'
        AND hwpc.is_compliant = 0
        AND hwpc.problem_type IS NOT NULL
        <if test="param.orgIdList != null and param.orgIdList.size()>0">
            and (
            <foreach item="id" collection="param.orgIdList" separator=" OR ">
                hwpc.basic_id LIKE concat('%', #{id}, '%')
            </foreach>
            )
        </if>
        <if test="param.startTime != null and param.startTime != ''  and param.endTime != null and param.endTime != ''">
            and hwpc.check_time between #{param.startTime} and #{param.endTime}
        </if>
        LEFT JOIN t_hazard_work_plan hwp ON hwpc.plan_id = hwp.id
        <if test="param.workType!=null and param.workType!=''">
            and hwp.work_type = #{param.workType}
        </if>
        <if test="param.workLevel!=null and param.workLevel!=''">
            and hwp.work_level = #{param.workLevel}
        </if>
        <if test="param.stakeholderWork!=null and param.stakeholderWork!=''">
            and hwp.stakeholder_work = #{param.stakeholderWork}
        </if>

        GROUP BY
          dates.timeStr
        ORDER BY
          dates.timeStr
    </select>


    <sql id="orgIdFilter">
        AND basic_id IN (
        SELECT org_id FROM t_sys_org
        WHERE org_id = #{param.orgId} OR parent_ids LIKE CONCAT('%', #{param.orgId}, '%')
        )
    </sql>

    <sql id="checkTimeFilter">
        <if test="param.startTime != null and param.startTime != '' and param.endTime != null and param.endTime != ''">
            AND check_time BETWEEN #{param.startTime} AND #{param.endTime}
        </if>
    </sql>

    <select id="selectHazardWorkPlanCheckStats" resultType="com.testor.module.hazard.model.dto.HazardWorkPlanCheckStatsDTO">
        SELECT
        <!-- 企业总数（有检查记录的企业）-->
        (SELECT COUNT(DISTINCT basic_unit)
        FROM t_hazard_work_plan_check
        WHERE status = '0'
        AND check_status = '2'
        <include refid="orgIdFilter" />
        <include refid="checkTimeFilter" />
        ) AS basicUnitCount,

        <!-- 合规企业数（企业合规率 >= 95%）-->
        (SELECT COUNT(*)
        FROM (
        SELECT basic_unit
        FROM t_hazard_work_plan_check
        WHERE status = '0'
        AND check_status = '2'
        <include refid="orgIdFilter" />
        <include refid="checkTimeFilter" />
        GROUP BY basic_unit
        HAVING
        CASE
        WHEN COUNT(*) = 0 THEN 0
        ELSE (SUM(CASE WHEN is_compliant = '1' THEN 1 ELSE 0 END) * 100.0 / COUNT(*))
        END <![CDATA[ >= ]]> 95
        ) AS compliant_enterprises) AS complianceBasicUnitCount,

        <!-- 不合规企业数（企业合规率 < 95%）-->
        (SELECT COUNT(*)
        FROM (
        SELECT basic_unit
        FROM t_hazard_work_plan_check
        WHERE status = '0'
        AND check_status = '2'
        <include refid="orgIdFilter" />
        <include refid="checkTimeFilter" />
        GROUP BY basic_unit
        HAVING
        CASE
        WHEN COUNT(*) = 0 THEN 0
        ELSE (SUM(CASE WHEN is_compliant = '1' THEN 1 ELSE 0 END) * 100.0 / COUNT(*))
        END <![CDATA[ < ]]> 95
        ) AS non_compliant_enterprises) AS noComplianceBasicUnitCount,

        <!-- 问题数-->
        (SELECT COUNT(*)
        FROM t_hazard_work_plan_check
        WHERE status = '0'
        AND check_status = '2'
        AND is_compliant = '0'
        <include refid="orgIdFilter" />
        <include refid="checkTimeFilter" />
        ) AS numberCount,

        <!--危险作业数量-->
        (select count(1) from t_hazard_work_plan
        where work_status!='0'
        AND org_id IN (
        SELECT org_id FROM t_sys_org
        WHERE org_id = #{param.orgId} OR parent_ids LIKE CONCAT('%', #{param.orgId}, '%')
        )
        <if test="param.startTime != null and param.startTime != '' and param.endTime != null and param.endTime != ''">
            AND create_date BETWEEN #{param.startTime} AND #{param.endTime}
        </if>) As workCount

        FROM DUAL
    </select>

    <select id="selectComplianceCount" resultType="java.lang.Integer">
        SELECT COUNT(*)
        FROM (
        SELECT basic_unit
        FROM t_hazard_work_plan_check
        WHERE status = '0'
        AND check_status = '2'
        <include refid="orgIdFilter" />
        <include refid="checkTimeFilter" />
        GROUP BY basic_unit
        HAVING
        CASE
        WHEN COUNT(*) = 0 THEN 0
        ELSE (SUM(CASE WHEN is_compliant = '1' THEN 1 ELSE 0 END) * 100.0 / COUNT(*))
        END <![CDATA[ >= ]]> 95
        ) AS compliant_enterprises
    </select>

    <select id="selectNoComplianceCount" resultType="java.lang.Double">
        SELECT COUNT(1)
        FROM t_hazard_work_plan_check
        WHERE status = '0'
        AND check_status = '2'
        AND is_compliant = '0'
        <include refid="orgIdFilter" />
        <include refid="checkTimeFilter" />
        <if test="problemType!=null and problemType!=''">
            AND problem_type = #{problemType}
        </if>
    </select>

    <select id="corporateComplianceRate" resultType="com.testor.module.hazard.model.vo.HazardWorkPlanCheckProblemCountStatisticsVO">
        SELECT
        t.basic_id,
        t.basic_unit,
        <!-- 计算合规率（数值）-->
        ROUND(
        (SUM(CASE WHEN is_compliant = '1' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)),
        2
        ) AS complianceRate,
        <!-- 判断是否合规（合规率 >= 95%）-->
        CASE
        WHEN COUNT(*) = 0 THEN '无数据'
        WHEN (SUM(CASE WHEN is_compliant = '1' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) <![CDATA[ >= ]]> 95 THEN '合规'
        ELSE '不合规'
        END AS isCompliant
        FROM t_hazard_work_plan_check t
        WHERE status = '0'
        AND check_status = '2'
        <include refid="orgIdFilter" />
        <if test="param.basicId != null and param.basicId != ''">
            AND t.basic_id = #{param.basicId}
        </if>
        <include refid="checkTimeFilter" />
        GROUP BY t.basic_id, t.basic_unit
        ORDER BY complianceRate DESC
    </select>

</mapper>