<?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.safe.dao.TSafeHazardOrgDao">
    <!-- 开启二级缓存 -->
    <!-- <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/> -->
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.testor.module.safe.model.domain.TSafeHazardOrg">
        <id column="id" property="id"/>
        <id column="ref_id" property="refId"/>
        <id column="parent_id" property="parentId"/>
        <id column="tenant_id" property="tenantId"/>
        <id column="process_id" property="processId"/>
        <id column="process_status" property="processStatus"/>
        <id column="org_id" property="orgId"/>
        <id column="name" property="name"/>
        <id column="check_type" property="checkType"/>
        <id column="manage_object" property="manageObject"/>
        <id column="equipment" property="equipment"/>
        <id column="location" property="location"/>
        <id column="images" property="images"/>
        <id column="work_order" property="workOrder"/>
        <id column="classify" property="classify"/>
        <id column="level" property="level"/>
        <id column="type" property="type"/>
        <id column="rule" property="rule"/>
        <id column="guard_against" property="guardAgainst"/>
        <id column="rectify_people" property="rectifyPeople"/>
        <id column="rectify_start_date" property="rectifyStartDate"/>
        <id column="rectify_end_date" property="rectifyEndDate"/>
        <id column="acceptance" property="acceptance"/>
    </resultMap>

    <select id="selectBaseList" resultType="com.testor.module.safe.model.dto.TSafeHazardOrgDTO"
            parameterType="com.testor.module.safe.model.dto.TSafeHazardOrgParam">
        SELECT
        tsho.id,
        tsho.numbering,
        tsho.remarks,
        tsho.rectification,
        tso.org_id,
        tso.org_name,
        tsho.NAME,
        tsho.classify,
        tsho.level,
        tsho.type,
        tsho.check_type,
        tsho.process_status,
        tsho.create_date,
        tsho.process_id,
        tsho.rectify_date,
        tsho.hidden_danger_reason,
        tsho.rectify_people,
        tsho.acceptance,
        tsho.is_repeat,
        tsho.score,
        tsu.user_name as createName
        FROM
        t_safe_hazard_org AS tsho
        LEFT JOIN t_sys_user AS tsu ON tsho.create_by = tsu.user_id
        LEFT JOIN t_sys_org AS tso ON tsho.org_id = tso.org_id
        <where>
            tsho.status = '0'
            <if test="param.condOrgIdList!=null">
                AND tsho.org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.ids!=null">
                AND tsho.id IN
                <foreach collection="param.ids" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>

            <if test="param.processStatusList!=null and param.processStatusList!=''">
                AND tsho.process_status
                IN
                <foreach collection="param.processStatusList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.processNotInStatusList!=null and param.processNotInStatusList!=''">
                AND tsho.process_status
                not IN
                <foreach collection="param.processNotInStatusList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>

            <if test="param.processStatus != null and param.processStatus != '' and param.processStatus == '20'">
                AND tsho.process_status &lt;&gt; 5 and  tsho.level is not null and  tsho.level != ''
            </if>
            <if test="param.classify!=null and param.classify!=''">
                AND tsho.classify = #{param.classify}
            </if>
            <if test="param.type!=null and param.type!=''">
                AND tsho.type = #{param.type}
            </if>
            <if test="param.level!=null and param.level!=''">
                AND tsho.level = #{param.level}
            </if>
            <if test="param.checkType!=null and param.checkType!=''">
                AND tsho.check_type = #{param.checkType}
            </if>
            <if test="param.isCount!=null and param.isCount!=''">
                AND tsho.is_count = #{param.isCount}
            </if>
            <if test="param.createBy!=null and param.createBy!=''">
                AND tsu.user_name like concat('%',#{param.createBy},'%')
            </if>
            <if test="param.userId != null and param.userId != ''">
                AND tsho.create_by = #{param.userId}
            </if>
            <if test="param.name!=null and param.name!=''">
                AND tsho.name like concat('%',#{param.name},'%')
            </if>
            <if test="param.equipment!=null and param.equipment!=''">
                AND tsho.equipment = #{param.equipment}
            </if>
            <if test="param.rectifyDepartment!=null and param.rectifyDepartment!=''">
                AND tsho.rectify_department = #{param.rectifyDepartment}
            </if>
            <if test="param.startTime!=null and param.startTime!='' and param.endTime!=null and param.endTime!=''">
                AND tsho.create_date between #{param.startTime} and #{param.endTime}
            </if>
            <if test="param.overdue!=null and param.overdue!='' and param.overdue == 0">
                AND ((tsho.rectify_date &gt;= tsho.rectify_start_date and tsho.rectify_start_date is not null) or (tsho.rectify_date &gt;= NOW()  and tsho.rectify_start_date is null) or tsho.rectify_date is null)
            </if>
            <if test="param.overdue!=null and param.overdue!='' and param.overdue == 1">
                AND ((tsho.rectify_date &lt; tsho.rectify_start_date and tsho.rectify_start_date is not null) or (tsho.rectify_date &lt; NOW()  and tsho.rectify_start_date is null))
            </if>
            <if test="param.remarks!=null and param.remarks!=''">
                AND tsho.remarks like concat('%',#{param.remarks},'%')
            </if>
            <if test="param.taskId!=null and param.taskId!=''">
                AND tsho.id in (select ref_id from t_safe_check_task_record where task_id = #{param.taskId} and ref_type = '2')
            </if>
        </where>
        ORDER BY tsho.create_date DESC
    </select>
    <select id="exportList" resultType="com.testor.module.safe.model.vo.TSafeHazardExportVO">
        SELECT 	tsho.*,
        hol.dict_value AS levelName,
        COALESCE ( holl.dict_value, tsho.location ) AS locationName,
        hoc.dict_value AS classifyName,
        hot.dict_value AS typeName,
        hopc.dict_value AS problemCauses,
        hoct.dict_value AS checkType,
        CASE
        WHEN tsho.process_status IN ( '9', '11', '12' ) THEN
        '已完成' ELSE hops.dict_value
        END AS statusName,
        tsur.user_name AS rectifyPeopleName,
        tsua.user_name AS acceptanceName,
        CASE
        WHEN tsho.is_repeat = '1' THEN
        '是'
        WHEN tsho.is_repeat = '0' THEN
        '否'
        END as isRepeat
        FROM t_safe_hazard_org as tsho
        LEFT JOIN (SELECT * from t_sys_dict_data WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_level' and status = 0) AND status = '0' )
        as hol on hol.dict_key = tsho.level
        LEFT JOIN (SELECT * from t_sys_dict_data WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_location' and status = 0) AND status = '0' )
        as holl on holl.dict_key = tsho.location
        LEFT JOIN (SELECT * from t_sys_dict_data WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_classify' and status = 0) AND status = '0' )
        as hoc on hoc.dict_key = tsho.classify
        LEFT JOIN (SELECT * from t_sys_dict_data WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_type' and status = 0) AND status = '0' )
        as hot on hot.dict_key = tsho.type
        LEFT JOIN (SELECT * from t_sys_dict_data WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_problem_causes' and status = 0) AND status = '0' )
        as hopc on hopc.dict_key = tsho.problem_causes
        LEFT JOIN (SELECT * from t_sys_dict_data WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_check_type' and status = 0) AND status = '0' )
        as hoct on hoct.dict_key = tsho.check_type
        LEFT JOIN (SELECT * from t_sys_dict_data WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_process_status' and status = 0) AND status = '0' )
        as hops on hops.dict_key = tsho.process_status
        left join t_sys_user as tsur on tsur.user_id = tsho.rectify_people
        left join t_sys_user as tsua on tsua.user_id = tsho.acceptance
        left join t_sys_user as tsu on tsu.user_id = tsho.create_by
        <where>
            tsho.status = '0'
            <if test="param.condOrgIdList!=null">
                AND tsho.org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.processStatusList!=null and param.processStatusList!=''">
                AND tsho.process_status
                IN
                <foreach collection="param.processStatusList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param.processNotInStatusList!=null and param.processNotInStatusList!=''">
                AND tsho.process_status
                not IN
                <foreach collection="param.processNotInStatusList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>

            <if test="param.processStatus != null and param.processStatus != '' and param.processStatus == '20'">
                AND tsho.process_status &lt;&gt; 5 and  tsho.level is not null and  tsho.level != ''
            </if>
            <if test="param.classify!=null and param.classify!=''">
                AND tsho.classify = #{param.classify}
            </if>
            <if test="param.type!=null and param.type!=''">
                AND tsho.type = #{param.type}
            </if>
            <if test="param.level!=null and param.level!=''">
                AND tsho.level = #{param.level}
            </if>
            <if test="param.checkType!=null and param.checkType!=''">
                AND tsho.check_type = #{param.checkType}
            </if>
            <if test="param.isCount!=null and param.isCount!=''">
                AND tsho.is_count = #{param.isCount}
            </if>
            <if test="param.isRepeat!=null and param.isRepeat!=''">
                AND tsho.is_repeat = #{param.isRepeat}
            </if>
            <if test="param.createBy!=null and param.createBy!=''">
                AND tsu.user_name like concat('%',#{param.createBy},'%')
            </if>
            <if test="param.userId != null and param.userId != ''">
                AND tsho.create_by = #{param.userId}
            </if>
            <if test="param.name!=null and param.name!=''">
                AND tsho.name like concat('%',#{param.name},'%')
            </if>
            <if test="param.equipment!=null and param.equipment!=''">
                AND tsho.equipment = #{param.equipment}
            </if>
            <if test="param.rectifyDepartment!=null and param.rectifyDepartment!=''">
                AND tsho.rectify_department = #{param.rectifyDepartment}
            </if>
            <if test="param.startTime!=null and param.startTime!='' and param.endTime!=null and param.endTime!=''">
                AND tsho.create_date between #{param.startTime} and #{param.endTime}
            </if>
            <if test="param.overdue!=null and param.overdue!='' and param.overdue == 0">
                AND ((tsho.rectify_date &gt;= tsho.rectify_start_date and tsho.rectify_start_date is not null) or (tsho.rectify_date &gt;= NOW()  and tsho.rectify_start_date is null) or tsho.rectify_date is null)
            </if>
            <if test="param.overdue!=null and param.overdue!='' and param.overdue == 1">
                AND ((tsho.rectify_date &lt; tsho.rectify_start_date and tsho.rectify_start_date is not null) or (tsho.rectify_date &lt; NOW()  and tsho.rectify_start_date is null))
            </if>
             <if test="param.remarks!=null and param.remarks!=''">
                AND tsho.remarks like concat('%',#{param.remarks},'%')
            </if>
            <if test="param.taskId!=null and param.taskId!=''">
                AND tsho.id in (select ref_id from t_safe_check_task_record where task_id = #{param.taskId} and ref_type = '2')
            </if>
        </where>

        order by tsho.create_date desc
    </select>
    <select id="hiddenDangerStatistics" parameterType="com.testor.module.safe.model.dto.TSafeHazardOrgParam"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsVO">
        SELECT
        t.org_id AS orgId,
        ( SELECT COUNT(*) FROM t_safe_hazard_org WHERE org_id in
        <foreach collection="param.orgIds" index="index" item="item" open="(" separator=","
                 close=")">
            #{item}
        </foreach>
        AND LEVEL = 1 AND process_status=5 ) AS
        generallyFinish,
        ( SELECT COUNT(*) FROM t_safe_hazard_org WHERE org_id in
        <foreach collection="param.orgIds" index="index" item="item" open="(" separator=","
                 close=")">
            #{item}
        </foreach>
        AND LEVEL = 1 AND process_status &lt;&gt; 5 ) AS
        generallyUndone,
        ( SELECT COUNT(*) FROM t_safe_hazard_org WHERE org_id in
        <foreach collection="param.orgIds" index="index" item="item" open="(" separator=","
                 close=")">
            #{item}
        </foreach>
        AND LEVEL = 2 AND process_status=5) AS
        majorFinish,
        ( SELECT COUNT(*) FROM t_safe_hazard_org WHERE org_id in
        <foreach collection="param.orgIds" index="index" item="item" open="(" separator=","
                 close=")">
            #{item}
        </foreach>
        AND LEVEL = 2 AND process_status &lt;&gt; 5) AS
        majorUndone
        FROM
        t_safe_hazard_org AS t
        <where>
            <if test="param.classify!=null and param.classify!=''">
                AND t.classify=#{param.classify}
            </if>
            <if test="param.startTime!=null and param.endTime!=null">
                AND t.create_date BETWEEN #{param.startTime} AND #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND t.org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY t.org_id
    </select>

    <select id="hiddenDangerStatisticsEquipmentType"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO"
            parameterType="com.testor.module.safe.model.dto.TSafeHazardOrgParam">
        SELECT e.id,
        e.name,
        t.count
        FROM ( SELECT SUBSTRING(equipment FROM 1 FOR POSITION(',' IN equipment) - 1) AS s,
                      COUNT(*) AS count
            FROM t_safe_hazard_org
            <where>
                manage_object = #{param.manageObject}
                <if test="param.classify!=null and param.classify!=''">
                    AND classify=#{param.classify}
                </if>
                <if test="param.startTime!=null and param.endTime!=null">
                    AND create_date BETWEEN #{param.startTime} AND #{param.endTime}
                </if>
                <if test="param.condOrgIdList!=null">
                    AND org_id IN
                    <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                             close=")">
                        #{item}
                    </foreach>
                </if>
            </where>
        GROUP BY s) AS t
        LEFT JOIN t_safe_equipment_type AS e ON t.s = e.id
    </select>

    <select id="hiddenDangerStatisticsSpaceType"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO"
            parameterType="com.testor.module.safe.model.dto.TSafeHazardOrgParam">
        SELECT e.id,
        e.name,
        t.count
        FROM (SELECT   SUBSTRING(equipment FROM 1 FOR POSITION(',' IN equipment) - 1) AS s,
                     COUNT(*) AS count
        FROM t_safe_hazard_org
        <where>
            manage_object = #{param.manageObject}
            <if test="param.classify!=null and param.classify!=''">
                AND classify=#{param.classify}
            </if>
            <if test="param.startTime!=null and param.endTime!=null">
                AND create_date BETWEEN #{param.startTime} AND #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY s) AS t
        LEFT JOIN t_safe_space_type AS e ON t.s = e.id
    </select>

    <select id="hiddenDangerStatisticsStandardType"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO"
            parameterType="com.testor.module.safe.model.dto.TSafeHazardOrgParam">
        SELECT e.id,
        e.category_item as name,
        t.count
        FROM (SELECT SUBSTRING(equipment FROM 1 FOR POSITION(',' IN equipment) - 1) AS s,   COUNT(*) AS count
        FROM t_safe_hazard_org
        <where>
            manage_object = #{param.manageObject}
            <if test="param.classify!=null and param.classify!=''">
                AND classify=#{param.classify}
            </if>
            <if test="param.startTime!=null and param.endTime!=null">
                AND create_date BETWEEN #{param.startTime} AND #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY s) AS t
        LEFT JOIN t_safe_hazard_standard_type AS e ON t.s = e.id
    </select>

    <select id="hiddenDangerStatisticsType" resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO"
            parameterType="com.testor.module.safe.model.dto.TSafeHazardOrgParam">
        SELECT type as name,COUNT(*) AS count
        FROM t_safe_hazard_org
        <where>
            <if test="param.level!=null and param.level!=''">
                AND level = #{param.level}
            </if>
            <if test="param.isRepeat!=null and param.isRepeat!=''">
                AND is_repeat = #{param.isRepeat}
            </if>
            <if test="param.isCount!=null and param.isCount!=''">
                AND is_count = #{param.isCount}
            </if>
            <if test="param.classify!=null and param.classify!=''">
                AND classify=#{param.classify}
            </if>
            <if test="param.startTime!=null and param.endTime!=null">
                AND create_date BETWEEN #{param.startTime} AND #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY type
    </select>

    <select id="hiddenDangerStatisticsCheckType"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO"
            parameterType="com.testor.module.safe.model.dto.TSafeHazardOrgParam">
        SELECT check_type as name,COUNT(*) AS count
        FROM t_safe_hazard_org
        <where>
            <if test="param.classify!=null and param.classify!=''">
                AND classify=#{param.classify}
            </if>
            <if test="param.startTime!=null and param.endTime!=null">
                AND create_date BETWEEN #{param.startTime} AND #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY check_type
    </select>


    <select id="hiddenDangerStatisticsReason"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO"
            parameterType="com.testor.module.safe.model.dto.TSafeHazardOrgParam">
        SELECT hidden_danger_reason as name,COUNT(*) AS count
        FROM t_safe_hazard_org
        <where>
            <if test="param.processStatus!=null and param.processStatus!=''">
                AND process_status=#{param.processStatus}
            </if>
            <if test="param.classify!=null and param.classify!=''">
                AND classify=#{param.classify}
            </if>
            <if test="param.startTime!=null and param.endTime!=null">
                AND create_date BETWEEN #{param.startTime} AND #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY hidden_danger_reason
    </select>

    <select id="getHiddenDangerNumber" resultType="com.testor.module.safe.model.vo.HiddenDangerVO"
            parameterType="java.util.List" databaseId="mysql">
        select
        a.org_id,
        sum(a.generally) as generally,
        sum(a.major) as major,
        b.org_name
        from (
        SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(ts.parent_ids,',',ts.org_id) ,',', #{treeLevel}), ',', -1) AS org_id,COUNT(th.level='1' OR NULL) AS generally,COUNT(th.level='2' OR NULL) AS major
        FROM t_safe_hazard_org AS th LEFT JOIN t_sys_org AS ts ON th.org_id=ts.org_id
        WHERE th.process_status &lt;&gt; 5
        <if test="orgIds!=null">
            AND th.org_id IN
            <foreach collection="orgIds" index="index" item="item" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        group by ts.org_id) a left join t_sys_org b on a.org_id = b.org_id
        GROUP BY a.org_id
    </select>

    <select id="getHiddenDangerNumber" resultType="com.testor.module.safe.model.vo.HiddenDangerVO"
            parameterType="java.util.List" databaseId="kingbase">
        SELECT
        a.org_id,
        SUM(a.generally) AS generally,
        SUM(a.major) AS major,
        b.org_name
        FROM (
        SELECT
        SPLIT_PART(SUBSTRING(CONCAT(ts.parent_ids, ',', ts.org_id) FROM 1 FOR POSITION(',' IN CONCAT(ts.parent_ids, ',', ts.org_id)) + #{treeLevel} - 1), ',', -1) AS org_id,
        COUNT(CASE WHEN th.level = '1' THEN 1 END) AS generally,
        COUNT(CASE WHEN th.level = '2' THEN 1 END) AS major
        FROM
        t_safe_hazard_org AS th
        LEFT JOIN t_sys_org AS ts ON th.org_id = ts.org_id
        WHERE
        th.process_status &lt;&gt; 5
        <if test="orgIds!=null">
            AND th.org_id IN
            <foreach collection="orgIds" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY ts.org_id
        ) a
        LEFT JOIN t_sys_org b ON a.org_id = b.org_id
        GROUP BY a.org_id, b.org_name;


    </select>

    <select id="getHiddenDanger" resultType="com.testor.module.safe.model.vo.HiddenDangerVO"
            parameterType="java.util.List">
        SELECT th.org_id,COUNT(th.level='1' OR NULL) AS generally,COUNT(th.level='2' OR NULL) AS major,ts.org_name
        FROM t_safe_hazard_org AS th LEFT JOIN t_sys_org AS ts ON th.org_id=ts.org_id
        WHERE th.process_status &lt;&gt; 5
        <if test="orgIds!=null">
            AND th.org_id IN
            <foreach collection="orgIds" index="index" item="item" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY th.org_id
    </select>
    <select id="getHiddenDangerCount" resultType="java.lang.Integer">
        SELECT count(th.id)
        FROM t_safe_hazard_org AS th LEFT JOIN t_sys_org AS ts ON th.org_id=ts.org_id
        where th.status = '0'
        AND th.org_id  in (select org.org_id from t_sys_org org where org.parent_ids like concat('%',#{orgId},'%') or org.org_id = #{orgId})
    </select>

    <select id="hiddenDangerStatisticsLocation"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO"  databaseId="mysql">
        SELECT
        tsdd.dict_value as name,
        COALESCE(b.count, 0) AS count,
        COALESCE(b.count, 0) / NULLIF(SUM(COALESCE(b.count, 0)) OVER (), 0) * 100 AS percentage
        FROM
        t_sys_dict_data AS tsdd
        LEFT JOIN (
        SELECT
        SUBSTRING_INDEX(COALESCE(holl.dict_value, tsho.location), '/', 1) AS name,
        COUNT(*) AS count
        FROM
        t_safe_hazard_org AS tsho
        LEFT JOIN (
        SELECT *
        FROM t_sys_dict_data
        WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_location' AND status = 0)
        AND status = '0'
        ) AS holl
        ON
        holl.dict_key = tsho.location
        <where>
            <if test="param.level!=null and param.level!=''">
                AND tsho.level = #{param.level}
            </if>
            <if test="param.isRepeat!=null and param.isRepeat!=''">
                AND tsho.is_repeat = #{param.isRepeat}
            </if>
            <if test="param.isCount!=null and param.isCount!=''">
                AND tsho.is_count = #{param.isCount}
            </if>
            <if test="param.classify!=null and param.classify!=''">
                AND tsho.classify=#{param.classify}
            </if>
            <if test="param.startTime != null">
                AND tsho.create_date &gt;= #{param.startTime}
            </if>
            <if test="param.endTime != null">
                AND tsho.create_date &lt;= #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND tsho.org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY
        SUBSTRING_INDEX(COALESCE(holl.dict_value, tsho.location), '/', 1)
        ) AS b ON b.name = tsdd.dict_value
        WHERE tsdd.dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_location' AND status = 0)
        AND status = '0'
        order by tsdd.tree_sort
    </select>
    <select id="hiddenDangerStatisticsLocation"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO"  databaseId="kingbase">
        SELECT
        tsdd.dict_value as name,
        COALESCE(b.count, 0) AS count,
        COALESCE(b.count, 0) / NULLIF(SUM(COALESCE(b.count, 0)) OVER (), 0) * 100 AS percentage
        FROM
        t_sys_dict_data AS tsdd
        LEFT JOIN (
        SELECT


        SUBSTRING(COALESCE(holl.dict_value, tsho."location") FROM 1 FOR
            CASE WHEN POSITION('/' IN COALESCE(holl.dict_value, tsho."location")) > 0
            THEN POSITION('/' IN COALESCE(holl.dict_value, tsho."location")) - 1 ELSE 0 END) AS name,

        COUNT(*) AS count
        FROM
        t_safe_hazard_org AS tsho
        LEFT JOIN (
        SELECT *
        FROM t_sys_dict_data
        WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_location' AND status = 0)
        AND status = '0'
        ) AS holl
        ON
        holl.dict_key = tsho.location
        <where>
            <if test="param.level!=null and param.level!=''">
                AND tsho.level = #{param.level}
            </if>
            <if test="param.isRepeat!=null and param.isRepeat!=''">
                AND tsho.is_repeat = #{param.isRepeat}
            </if>
            <if test="param.isCount!=null and param.isCount!=''">
                AND tsho.is_count = #{param.isCount}
            </if>
            <if test="param.classify!=null and param.classify!=''">
                AND tsho.classify=#{param.classify}
            </if>
            <if test="param.startTime != null">
                AND tsho.create_date &gt;= #{param.startTime}
            </if>
            <if test="param.endTime != null">
                AND tsho.create_date &lt;= #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND tsho.org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY


        SUBSTRING(COALESCE(holl.dict_value, tsho."location") FROM 1 FOR
        CASE WHEN POSITION('/' IN COALESCE(holl.dict_value, tsho."location")) > 0
        THEN POSITION('/' IN COALESCE(holl.dict_value, tsho."location")) - 1 ELSE 0 END)


        ) AS b ON b.name = tsdd.dict_value
        WHERE tsdd.dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_location' AND status = 0)
        AND status = '0'
        order by tsdd.tree_sort
    </select>


    <select id="hiddenDangerStatisticsCause"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO">
        SELECT
            tsdd.dict_value as name,
            COALESCE(b.count, 0) AS count,
    COALESCE(b.count, 0) / NULLIF(SUM(COALESCE(b.count, 0)) OVER (), 0) * 100 AS percentage
        FROM
            t_sys_dict_data AS tsdd
            LEFT JOIN (
            SELECT
            holl.dict_value AS name,
            COUNT(*) AS count
            FROM
            t_safe_hazard_org AS tsho
            LEFT JOIN (
            SELECT *
            FROM t_sys_dict_data
            WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_problem_causes' AND status = 0)
            AND status = '0'
            ) AS holl
            ON
            holl.dict_key = tsho.problem_causes
        <where>
            <if test="param.level!=null and param.level!=''">
                AND tsho.level = #{param.level}
            </if>
            <if test="param.isRepeat!=null and param.isRepeat!=''">
                AND tsho.is_repeat = #{param.isRepeat}
            </if>
            <if test="param.isCount!=null and param.isCount!=''">
                AND tsho.is_count = #{param.isCount}
            </if>
            <if test="param.classify!=null and param.classify!=''">
                AND tsho.classify=#{param.classify}
            </if>
            <if test="param.startTime != null">
                AND tsho.create_date &gt;= #{param.startTime}
            </if>
            <if test="param.endTime != null">
                AND tsho.create_date &lt;= #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND tsho.org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
            GROUP BY
            holl.dict_value
            ) AS b ON b.name = tsdd.dict_value
        WHERE tsdd.dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_problem_causes' AND status = 0)
          AND status = '0'
        order by tsdd.tree_sort
    </select>
    <select id="hiddenDangerStatisticsByType"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO">
        SELECT
        tsdd.dict_value as name,
        COALESCE(b.count, 0) AS count,
        COALESCE(b.count, 0) / NULLIF(SUM(COALESCE(b.count, 0)) OVER (), 0) * 100 AS percentage
        FROM
        t_sys_dict_data AS tsdd
        LEFT JOIN (
        SELECT
        holl.dict_value AS name,
        COUNT(*) AS count
        FROM
        t_safe_hazard_org AS tsho
        LEFT JOIN (
        SELECT *
        FROM t_sys_dict_data
        WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_type' AND status = 0)
        AND status = '0'
        ) AS holl
        ON
        holl.dict_key = tsho.type
        <where>
            <if test="param.level!=null and param.level!=''">
                AND tsho.level = #{param.level}
            </if>
            <if test="param.isRepeat!=null and param.isRepeat!=''">
                AND tsho.is_repeat = #{param.isRepeat}
            </if>
            <if test="param.isCount!=null and param.isCount!=''">
                AND tsho.is_count = #{param.isCount}
            </if>
            <if test="param.classify!=null and param.classify!=''">
                AND tsho.classify=#{param.classify}
            </if>
            <if test="param.startTime != null">
                AND tsho.create_date &gt;= #{param.startTime}
            </if>
            <if test="param.endTime != null">
                AND tsho.create_date &lt;= #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND tsho.org_id IN
                <foreach collection="param.condOrgIdList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY
        holl.dict_value
        ) AS b ON b.name = tsdd.dict_value
        WHERE tsdd.dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_type' AND status = 0)
        AND status = '0'
        order by tsdd.tree_sort
    </select>
    <select id="hiddenDangerStatisticsClassify"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO">
        SELECT
        tsdd.dict_value as name,
        COALESCE(b.count, 0) AS count,
        COALESCE(b.count, 0) / NULLIF(SUM(COALESCE(b.count, 0)) OVER (), 0) * 100 AS percentage
        FROM
        t_sys_dict_data AS tsdd
        LEFT JOIN (
        SELECT
        holl.dict_value AS name,
        COUNT(*) AS count
        FROM
        t_safe_hazard_org AS tsho
        LEFT JOIN (
        SELECT *
        FROM t_sys_dict_data
        WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_classify' AND status = 0)
        AND status = '0'
        ) AS holl
        ON
        holl.dict_key = tsho.classify
        <where>
            <if test="param.level!=null and param.level!=''">
                AND tsho.level = #{param.level}
            </if>
            <if test="param.isRepeat!=null and param.isRepeat!=''">
                AND tsho.is_repeat = #{param.isRepeat}
            </if>
            <if test="param.isCount!=null and param.isCount!=''">
                AND tsho.is_count = #{param.isCount}
            </if>
            <if test="param.classify!=null and param.classify!=''">
                AND tsho.classify=#{param.classify}
            </if>
            <if test="param.startTime != null">
                AND tsho.create_date &gt;= #{param.startTime}
            </if>
            <if test="param.endTime != null">
                AND tsho.create_date &lt;= #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND tsho.org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY
        holl.dict_value
        ) AS b ON b.name = tsdd.dict_value
        WHERE tsdd.dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_classify' AND status = 0)
        AND status = '0'
        order by tsdd.tree_sort
    </select>
    <select id="hiddenDangerStatisticsByCheckType"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO">
        SELECT
        tsdd.dict_value as name,
        COALESCE(b.count, 0) AS count,
        COALESCE(b.count, 0) / NULLIF(SUM(COALESCE(b.count, 0)) OVER (), 0) * 100 AS percentage
        FROM
        t_sys_dict_data AS tsdd
        LEFT JOIN (
        SELECT
        holl.dict_value AS name,
        COUNT(*) AS count
        FROM
        t_safe_hazard_org AS tsho
        LEFT JOIN (
        SELECT *
        FROM t_sys_dict_data
        WHERE dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_check_type' AND status = 0)
        AND status = '0'
        ) AS holl
        ON
        holl.dict_key = tsho.check_type
        <where>
            <if test="param.level!=null and param.level!=''">
                AND tsho.level = #{param.level}
            </if>
            <if test="param.isRepeat!=null and param.isRepeat!=''">
                AND tsho.is_repeat = #{param.isRepeat}
            </if>
            <if test="param.isCount!=null and param.isCount!=''">
                AND tsho.is_count = #{param.isCount}
            </if>
            <if test="param.classify!=null and param.classify!=''">
                AND tsho.classify=#{param.classify}
            </if>
            <if test="param.startTime != null">
                AND tsho.create_date &gt;= #{param.startTime}
            </if>
            <if test="param.endTime != null">
                AND tsho.create_date &lt;= #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND tsho.org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY
        holl.dict_value
        ) AS b ON b.name = tsdd.dict_value
        WHERE tsdd.dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'safe_hazard_org_check_type' AND status = 0)
        AND status = '0'
        order by tsdd.tree_sort
    </select>
    <select id="hiddenDangerStatisticsOrgType"
            resultType="com.testor.module.safe.model.vo.TSafeHazardOrgStatisticsTypeVO">
        SELECT
            tsdd.dict_value as name,
            COALESCE(b.count, 0) AS count,
        COALESCE(b.count, 0) / NULLIF(SUM(COALESCE(b.count, 0)) OVER (), 0) * 100 AS percentage
        FROM
            t_sys_dict_data AS tsdd
            LEFT JOIN (

            SELECT
            tso.is_rent AS name,
            COUNT(*) AS count
            FROM
            t_safe_hazard_org AS tsho
            LEFT JOIN
            t_sys_org AS tso ON tsho.org_id = tso.org_id
        <where>
            <if test="param.level!=null and param.level!=''">
                AND tsho.level = #{param.level}
            </if>
            <if test="param.isRepeat!=null and param.isRepeat!=''">
                AND tsho.is_repeat = #{param.isRepeat}
            </if>
            <if test="param.isCount!=null and param.isCount!=''">
                AND tsho.is_count = #{param.isCount}
            </if>
            <if test="param.classify!=null and param.classify!=''">
                AND tsho.classify=#{param.classify}
            </if>
            <if test="param.startTime != null">
                AND tsho.create_date &gt;= #{param.startTime}
            </if>
            <if test="param.endTime != null">
                AND tsho.create_date &lt;= #{param.endTime}
            </if>
            <if test="param.condOrgIdList!=null">
                AND tsho.org_id IN
                <foreach collection="param.condOrgIdList" index="index" item="item" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
            GROUP BY
            tso.is_rent
            ) AS b ON b.name = tsdd.dict_key
        WHERE tsdd.dict_id = (SELECT dict_id FROM t_sys_dict_type WHERE dict_type = 'org_type' AND status = 0)
          AND status = '0'
        order by tsdd.tree_sort
    </select>
</mapper>