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


    <select id="statWorkPlanNumByOrg" resultType="com.testor.module.hazard.model.vo.HazardWorkPlanStatisticsVO"
            parameterType="com.testor.module.hazard.model.dto.HazardWorkPlanStatisticsDto" databaseId="mysql">
        SELECT
            so.org_id coordinateId,
            so.tree_level treeLevel,
            so.nature nature,
            so.org_name coordinateName,
            count( hp.id ) AS num
        FROM
            t_sys_org so
                LEFT JOIN (
                SELECT
                    p.id,
                    p.work_type,
                    p.work_status,
                    p.is_escalated,
                    p.work_level,
                    p.create_date,
                    p.STATUS,
                    concat( o.parent_ids, ',', p.org_id ) AS org_all_path
                FROM
                    t_hazard_work_plan p
                        LEFT JOIN t_sys_org o ON p.org_id = o.org_id
            ) hp ON hp.org_all_path LIKE CONCAT( '%', so.org_id, '%' )
        WHERE
            hp.STATUS != 1
        and hp.work_status != 6
        AND so.STATUS = 0
        AND so.is_dept != 1
        AND (so.parent_id = #{query.orgId} or ((so.tree_level=4 or so.nature=4) and so.org_id=#{query.orgId}))
        <if test="query.workType!=null and query.workType != ''">
            and hp.work_type = #{query.workType}
        </if>
        <if test="query.workLevel!=null and query.workLevel != ''">
            and hp.work_level = #{query.workLevel}
        </if>
        <if test="query.isEscalated!=null and query.isEscalated != ''">
            and hp.is_escalated = #{query.isEscalated}
        </if>
        <if test="query.workStatus!=null and query.workStatus != ''">
            and find_in_set(hp.work_status, #{query.workStatus})
        </if>
        <if test="query.reportStartDate!=null and query.reportStartDate != '' and query.reportEndDate!=null and query.reportEndDate != ''">
            and date_format(hp.create_date, '%Y%m%d') &gt;=date_format(#{query.reportStartDate}, '%Y%m%d')
            and date_format(hp.create_date, '%Y%m%d') &lt;=date_format(#{query.reportEndDate}, '%Y%m%d')
        </if>
        GROUP BY so.org_id
        ORDER BY  num DESC, so.org_name ASC, so.tree_level ASC, so.tree_sort ASC
    </select>
    <select id="statWorkPlanNumByOrg" resultType="com.testor.module.hazard.model.vo.HazardWorkPlanStatisticsVO"
            parameterType="com.testor.module.hazard.model.dto.HazardWorkPlanStatisticsDto" databaseId="kingbase">
        SELECT
        so.org_id coordinateId,
        so.tree_level treeLevel,
        so.nature nature,
        so.org_name coordinateName,
        count( hp.id ) AS num
        FROM
        t_sys_org so
        LEFT JOIN (
        SELECT
        p.id,
        p.work_type,
        p.work_status,
        p.is_escalated,
        p.work_level,
        p.create_date,
        p.STATUS,
        concat( o.parent_ids, ',', p.org_id ) AS org_all_path
        FROM
        t_hazard_work_plan p
        LEFT JOIN t_sys_org o ON p.org_id = o.org_id
        ) hp ON hp.org_all_path LIKE CONCAT( '%', so.org_id, '%' )
        WHERE
        hp.STATUS != 1
        and hp.work_status != 6
        AND so.STATUS = 0
        AND so.is_dept != 1
        AND (so.parent_id = #{query.orgId} or ((so.tree_level=4 or so.nature=4) and so.org_id=#{query.orgId}))
        <if test="query.workType!=null and query.workType != ''">
            and hp.work_type = #{query.workType}
        </if>
        <if test="query.workLevel!=null and query.workLevel != ''">
            and hp.work_level = #{query.workLevel}
        </if>
        <if test="query.isEscalated!=null and query.isEscalated != ''">
            and hp.is_escalated = #{query.isEscalated}
        </if>
        <if test="query.workStatus!=null and query.workStatus != ''">
            and find_in_set(hp.work_status, #{query.workStatus})
        </if>
        <if test="query.reportStartDate!=null and query.reportStartDate != '' and query.reportEndDate!=null and query.reportEndDate != ''">

            and hp.create_date &gt;= #{query.reportStartDate}   and hp.create_date &lt;=  #{query.reportEndDate}

        </if>
        GROUP BY so.org_id
        ORDER BY  num DESC, so.org_name ASC, so.tree_level ASC, so.tree_sort ASC
    </select>




    <select id="statWorkPlanNumByTime"
            resultType="com.testor.module.hazard.model.vo.HazardWorkPlanStatisticsVO"
            parameterType="com.testor.module.hazard.model.dto.HazardWorkPlanStatisticsDto" databaseId="mysql">
        SELECT
            date_format(t.timeStr, '%Y-%m-%d' ) timeStr,
            ifnull(hp.num,0) num
        FROM
        ( SELECT date_add( date_sub( #{query.reportStartDate}, INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS
        signed INTEGER ) + 1 ) DAY ) timeStr
        FROM mysql.help_topic
        WHERE help_topic_id &lt; DATEDIFF(#{query.reportEndDate},date_sub( #{query.reportStartDate}, INTERVAL 1 DAY ))
        ORDER BY help_topic_id ) t
        LEFT JOIN (SELECT date_format( p.create_date, '%Y-%m-%d' ) AS timeStr, count( id ) AS num
        FROM t_hazard_work_plan p
        WHERE
        p.STATUS != 1
        and p.work_status != 6
        and p.org_id in (select org_id from t_sys_org so where ((so.tree_level=4 or so.nature=4) and so.org_id=#{query.orgId}) or so.parent_ids like CONCAT('%,', #{query.orgId} ,'%'))
        <if test="query.workType!=null and query.workType != ''">
            and p.work_type = #{query.workType}
        </if>
        <if test="query.workLevel!=null and query.workLevel != ''">
            and p.work_level = #{query.workLevel}
        </if>
        <if test="query.isEscalated!=null and query.isEscalated != ''">
            and p.is_escalated = #{query.isEscalated}
        </if>
        <if test="query.workStatus!=null and query.workStatus != ''">
            and find_in_set(p.work_status, #{query.workStatus})
        </if>
        <if test="query.reportStartDate!=null and query.reportStartDate != '' and query.reportEndDate!=null and query.reportEndDate != ''">
            and date_format(p.create_date, '%Y%m%d') &gt;=date_format(#{query.reportStartDate}, '%Y%m%d')
            and date_format(p.create_date, '%Y%m%d') &lt;=date_format(#{query.reportEndDate}, '%Y%m%d')
        </if>
        GROUP BY date_format( p.create_date, '%Y-%m-%d' )) hp ON date_format(t.timeStr, '%Y-%m-%d' ) = date_format(hp.timeStr, '%Y-%m-%d' )
        ORDER BY t.timeStr ASC
    </select>
    <select id="statWorkPlanNumByTime"
            resultType="com.testor.module.hazard.model.vo.HazardWorkPlanStatisticsVO"
            parameterType="com.testor.module.hazard.model.dto.HazardWorkPlanStatisticsDto" databaseId="kingbase">

        WITH date_series AS (
        SELECT generate_series(
        #{query.reportStartDate} ::timestamp,
        #{query.reportEndDate}::timestamp,
        '1 day'::interval
        )::date AS timeStr
        ),
        org_filter AS (
        SELECT org_id
        FROM t_sys_org so
        WHERE (so.tree_level = 4 OR so.nature = 4) AND so.org_id = #{query.orgId}
        OR so.parent_ids LIKE CONCAT('%,', #{query.orgId}, '%')
        ),
        work_plan_counts AS (
        SELECT p.create_date::date AS timeStr, COUNT(id) AS num
        FROM t_hazard_work_plan p
        WHERE p.status != 1
        AND p.work_status != 6
        AND p.org_id IN (SELECT org_id FROM org_filter)
        <if test="query.workType != null and query.workType != ''">
            AND p.work_type = #{query.workType}
        </if>
        <if test="query.workLevel != null and query.workLevel != ''">
            AND p.work_level = #{query.workLevel}
        </if>
        <if test="query.isEscalated != null and query.isEscalated != ''">
            AND p.is_escalated = #{query.isEscalated}
        </if>
        <if test="query.workStatus != null and query.workStatus != ''">
            AND p.work_status IN (#{query.workStatus})
        </if>
        <if test="query.reportStartDate != null and query.reportStartDate != '' and query.reportEndDate != null and query.reportEndDate != ''">
            AND  p.create_date  >=   #{query.reportStartDate}  AND  p.create_date   &lt;=   #{query.reportEndDate}
        </if>
        GROUP BY p.create_date::date
        )
        SELECT ds.timeStr, COALESCE(wpc.num, 0) AS num
        FROM date_series ds
        LEFT JOIN work_plan_counts wpc ON ds.timeStr = wpc.timeStr
        ORDER BY ds.timeStr ASC;
    </select>





    <select id="statStakeholderWorkNum"
            resultType="com.testor.module.hazard.model.vo.HazardWorkPlanStatisticsVO"
            parameterType="com.testor.module.hazard.model.dto.HazardWorkPlanStatisticsDto" databaseId="mysql">
        SELECT
            d.dict_value as stakeholderWork,
            ifnull(hp.num,0) num,
            hp.rate
        FROM
            ( SELECT p.stakeholder_work, count( id ) AS num,
                     round(count(id )/sum(count( id ))over(),4) as rate FROM t_hazard_work_plan p
            WHERE
            p.STATUS != 1
            and p.work_status != 6
            and p.org_id in (select org_id from t_sys_org so where ((so.tree_level=4 or so.nature=4) and so.org_id=#{query.orgId}) or so.parent_ids like CONCAT('%,', #{query.orgId} ,'%'))
            <if test="query.workType!=null and query.workType != ''">
                and p.work_type = #{query.workType}
            </if>
            <if test="query.workLevel!=null and query.workLevel != ''">
                and p.work_level = #{query.workLevel}
            </if>
            <if test="query.isEscalated!=null and query.isEscalated != ''">
                and p.is_escalated = #{query.isEscalated}
            </if>
            <if test="query.workStatus!=null and query.workStatus != ''">
                and find_in_set(p.work_status, #{query.workStatus})
            </if>
            <if test="query.reportStartDate!=null and query.reportStartDate != '' and query.reportEndDate!=null and query.reportEndDate != ''">
                and date_format(p.create_date, '%Y%m%d') &gt;=date_format(#{query.reportStartDate}, '%Y%m%d')
                and date_format(p.create_date, '%Y%m%d') &lt;=date_format(#{query.reportEndDate}, '%Y%m%d')
            </if>
            GROUP BY p.stakeholder_work ) hp
                LEFT JOIN t_sys_dict_data d ON hp.stakeholder_work = d.dict_data_id
    </select>
    <select id="statStakeholderWorkNum"
            resultType="com.testor.module.hazard.model.vo.HazardWorkPlanStatisticsVO"
            parameterType="com.testor.module.hazard.model.dto.HazardWorkPlanStatisticsDto" databaseId="kingbase">
        SELECT
        d.dict_value as stakeholderWork,
        ifnull(hp.num,0) num,
        hp.rate
        FROM
        ( SELECT p.stakeholder_work, count( id ) AS num,
        round(count(id )/sum(count( id ))over(),4) as rate FROM t_hazard_work_plan p
        WHERE
        p.STATUS != 1
        and p.work_status != 6
        and p.org_id in (select org_id from t_sys_org so where ((so.tree_level=4 or so.nature=4) and so.org_id=#{query.orgId}) or so.parent_ids like CONCAT('%,', #{query.orgId} ,'%'))
        <if test="query.workType!=null and query.workType != ''">
            and p.work_type = #{query.workType}
        </if>
        <if test="query.workLevel!=null and query.workLevel != ''">
            and p.work_level = #{query.workLevel}
        </if>
        <if test="query.isEscalated!=null and query.isEscalated != ''">
            and p.is_escalated = #{query.isEscalated}
        </if>
        <if test="query.workStatus!=null and query.workStatus != ''">
            and find_in_set(p.work_status, #{query.workStatus})
        </if>
        <if test="query.reportStartDate!=null and query.reportStartDate != '' and query.reportEndDate!=null and query.reportEndDate != ''">

            and  p.create_date  &gt;= #{query.reportStartDate}   and  p.create_date  &lt;= #{query.reportEndDate}

        </if>
        GROUP BY p.stakeholder_work ) hp
        LEFT JOIN t_sys_dict_data d ON hp.stakeholder_work = d.dict_data_id
    </select>




    <select id="statIsRentNum" resultType="com.testor.module.hazard.model.vo.HazardWorkPlanStatisticsVO"
            parameterType="com.testor.module.hazard.model.dto.HazardWorkPlanStatisticsDto" databaseId="mysql">
        SELECT CASE WHEN so.is_rent = 0 THEN '自有库' ELSE '租赁库'  END AS isRent,
               count( hp.id ) num,
               round(count( hp.id )/sum(count( hp.id ))over(),4) as rate
        FROM
            t_hazard_work_plan hp,
            t_sys_org so
        WHERE
            hp.STATUS != 1
            and hp.work_status != 6
            AND hp.org_id = so.org_id
            AND so.STATUS = 0
            AND so.is_dept != 1
            AND (so.parent_id = #{query.orgId} or ((so.tree_level=4 or so.nature=4) and so.org_id=#{query.orgId}) or so.parent_ids like CONCAT('%,', #{query.orgId} ,'%'))
        <if test="query.workType!=null and query.workType != ''">
            and hp.work_type = #{query.workType}
        </if>
        <if test="query.workLevel!=null and query.workLevel != ''">
            and hp.work_level = #{query.workLevel}
        </if>
        <if test="query.isEscalated!=null and query.isEscalated != ''">
            and hp.is_escalated = #{query.isEscalated}
        </if>
        <if test="query.workStatus!=null and query.workStatus != ''">
            and find_in_set(hp.work_status, #{query.workStatus})
        </if>
        <if test="query.reportStartDate!=null and query.reportStartDate != '' and query.reportEndDate!=null and query.reportEndDate != ''">
            and date_format(hp.create_date, '%Y%m%d') &gt;=date_format(#{query.reportStartDate}, '%Y%m%d')
            and date_format(hp.create_date, '%Y%m%d') &lt;=date_format(#{query.reportEndDate}, '%Y%m%d')
        </if>
        GROUP BY so.is_rent
    </select>
    <select id="statIsRentNum" resultType="com.testor.module.hazard.model.vo.HazardWorkPlanStatisticsVO"
            parameterType="com.testor.module.hazard.model.dto.HazardWorkPlanStatisticsDto" databaseId="kingbase">
        SELECT CASE WHEN so.is_rent = 0 THEN '自有库' ELSE '租赁库'  END AS isRent,
            count( hp.id ) num,
            round(count( hp.id )/sum(count( hp.id ))over(),4) as rate
        FROM
            t_hazard_work_plan hp,
            t_sys_org so
        WHERE
            hp.STATUS != 1
            and hp.work_status != 6
            AND hp.org_id = so.org_id
            AND so.STATUS = 0
            AND so.is_dept != 1
            AND (so.parent_id = #{query.orgId} or ((so.tree_level=4 or so.nature=4) and so.org_id=#{query.orgId}) or so.parent_ids like CONCAT('%,', #{query.orgId} ,'%'))
        <if test="query.workType!=null and query.workType != ''">
            AND hp.work_type = #{query.workType}
        </if>
        <if test="query.workLevel!=null and query.workLevel != ''">
            AND hp.work_level = #{query.workLevel}
        </if>
        <if test="query.isEscalated!=null and query.isEscalated != ''">
            AND hp.is_escalated = #{query.isEscalated}
        </if>
        <if test="query.workStatus!=null and query.workStatus != ''">
            and find_in_set(hp.work_status, #{query.workStatus})
        </if>
        <if test="query.reportStartDate!=null and query.reportStartDate != '' and query.reportEndDate!=null and query.reportEndDate != ''">
            AND  hp.create_date  >=  #{query.reportStartDate}   AND  hp.create_date  &lt;=  #{query.reportEndDate}
        </if>
        GROUP BY  so.is_rent
    </select>


    <select id="statOperatorNum" resultType="com.testor.module.hazard.model.vo.HazardWorkPlanStatisticsVO"
            parameterType="com.testor.module.hazard.model.dto.HazardWorkPlanStatisticsDto"  databaseId="mysql" >
        select
        case when t.operator='0-' then '自有作业人员' else '相关方作业人员' end as operator,
        count(t.id) as num,
        round(count(t.id)/sum(count(t.id))over(),4) as rate
        from (
        SELECT
        hp.id,
        substr(substring_index( substring_index( hp.operator, ',', b.help_topic_id + 1 ), ',',- 1 ),1,2) AS operator
        FROM
        t_hazard_work_plan hp
        INNER JOIN mysql.help_topic b ON b.help_topic_id &lt; ( LENGTH( hp.operator ) - LENGTH( REPLACE ( hp.operator,
        ',', '' )) + 1 )
        WHERE
        hp.operator !='' and hp.operator is not null
        and hp.STATUS != 1
        and hp.work_status != 6
        and hp.org_id in (select org_id from t_sys_org so where ((so.tree_level=4 or so.nature=4) and
        so.org_id=#{query.orgId}) or so.parent_ids like CONCAT('%,', #{query.orgId} ,'%'))
        <if test="query.workType!=null and query.workType != ''">
            and hp.work_type = #{query.workType}
        </if>
        <if test="query.workLevel!=null and query.workLevel != ''">
            and hp.work_level = #{query.workLevel}
        </if>
        <if test="query.isEscalated!=null and query.isEscalated != ''">
            and hp.is_escalated = #{query.isEscalated}
        </if>
        <if test="query.workStatus!=null and query.workStatus != ''">
            and find_in_set(hp.work_status, #{query.workStatus})
        </if>
        <if test="query.reportStartDate!=null and query.reportStartDate != '' and query.reportEndDate!=null and query.reportEndDate != ''">
            and date_format(hp.create_date, '%Y%m%d') &gt;=date_format(#{query.reportStartDate}, '%Y%m%d')
            and date_format(hp.create_date, '%Y%m%d') &lt;=date_format(#{query.reportEndDate}, '%Y%m%d')
        </if>
        )t group by t.operator
    </select>
    <select id="statOperatorNum" resultType="com.testor.module.hazard.model.vo.HazardWorkPlanStatisticsVO"
            parameterType="com.testor.module.hazard.model.dto.HazardWorkPlanStatisticsDto"  databaseId="kingbase"  >
        SELECT
        CASE
        WHEN t.operator = '0-' THEN '自有作业人员'
        ELSE '相关方作业人员'
        END AS operator,
        COUNT(t.id) AS num,
        ROUND(COUNT(t.id) * 1.0 / SUM(COUNT(t.id)) OVER (), 4) AS rate
        FROM (
            SELECT  hp.id, SUBSTRING(SPLIT_PART(hp.operator, ',', n) FROM 1 FOR 2) AS operator
            FROM t_hazard_work_plan hp, GENERATE_SERIES(1, LENGTH(hp.operator) - LENGTH(REPLACE(hp.operator, ',', '')) + 1) AS n
            WHERE  hp.operator IS NOT NULL  AND hp.status != 1  AND hp.work_status != 6
            AND hp.org_id IN (  SELECT org_id  FROM t_sys_org so  WHERE ((so.tree_level = 4 OR so.nature = 4) AND so.org_id = #{query.orgId})
                OR so.parent_ids LIKE CONCAT('%,', #{query.orgId}, '%') )

            <if test="query.workType!=null and query.workType != ''">
                AND hp.work_type = #{query.workType}
            </if>
            <if test="query.workLevel!=null and query.workLevel != ''">
                AND hp.work_level = #{query.workLevel}
            </if>
            <if test="query.isEscalated!=null and query.isEscalated != ''">
                AND hp.is_escalated = #{query.isEscalated}
            </if>
            <if test="query.workStatus!=null and query.workStatus != ''">
                AND POSITION(hp.work_status IN #{query.workStatus}) > 0
            </if>
            <if test="query.reportStartDate!=null and query.reportStartDate != '' and query.reportEndDate!=null and query.reportEndDate != ''">
                AND  hp.create_date  >=  #{query.reportStartDate}   AND  hp.create_date  &lt;=  #{query.reportEndDate}
            </if>
        )t
        GROUP BY t.operator;

    </select>
</mapper>