<?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.partner.dao.TJobStatisticsDao">
    <!-- 开启二级缓存 -->
    <!-- <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/> -->
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.testor.module.partner.model.domain.TJobStatistics">
        <id column="DHZY_ZYZT" property="dhzyZyzt"/>
        <id column="DHZY_GCMZ" property="dhzyGcmz"/>
        <id column="DHZY_ZKSSPRY" property="dhzyZksspry"/>
        <id column="SFZYK" property="sfzyk"/>
        <id column="XGFDWRY" property="xgfdwry"/>
        <id column="DHZY_ZYJSSJ" property="dhzyZyjssj"/>
        <id column="DHZY_ZYKSSJ" property="dhzyZykssj"/>
        <id column="DHZY_ZYLB" property="dhzyZylb"/>
        <id column="dhzy_id" property="dhzyId"/>
        <id column="parent_id" property="parentId"/>
        <id column="id" property="id"/>
        <id column="org_id" property="orgId"/>
        <id column="code" property="code"/>
    </resultMap>

    <resultMap id="DangerousNumVO" type="com.testor.module.partner.model.vo.DangerousNumVO">
        <id column="name" property="name"/>
        <id column="num" property="num"/>
        <id column="orgId" property="orgId"/>
    </resultMap>

    <resultMap id="RegionalStatisticsVO" type="com.testor.module.partner.model.vo.RegionalStatisticsVO">
        <id column="name" property="name"/>
        <id column="done" property="done"/>
        <id column="undone" property="undone"/>
    </resultMap>
    <select id="regionalStatisticsCountGrain" resultType="long" databaseId="mysql">
        select count(*) from
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        where job.status = 0 and org.org_id = #{param.orgId}
          and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d')  &gt;=  DATE_FORMAT(#{param.zykssj}, '%Y-%m-%d')
          and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  now()
    </select>
    <select id="regionalStatisticsCountGrain" resultType="long" databaseId="kingbase">
        select count(*) from
            t_job_statistics job
                LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        where job.status = 0 and org.org_id = #{param.orgId}
          and to_char(DHZY_ZYKSSJ, '%Y-%m-%d')  &gt;=  to_char(#{param.zykssj}::DATE, '%Y-%m-%d')
          and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  now()
    </select>

    <select id="regionalStatisticsCount" resultType="long" databaseId="mysql">
        select count(*) from
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        where job.status = 0 and org.org_id in (select org.org_id from t_sys_org org where org.parent_ids like concat('%',#{param.orgId},'%') or org.org_id = #{param.orgId})
        <if test="param.treeLevel == '2'.toString()">
            and org.org_id != #{param.orgId}
        </if>
        and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d')  &gt;=  DATE_FORMAT(#{param.zykssj}, '%Y-%m-%d')
        and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  now()
    </select>
    <select id="regionalStatisticsCount" resultType="long" databaseId="kingbase">
        select count(*) from
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        where job.status = 0 and org.org_id in (select org.org_id from t_sys_org org where org.parent_ids like concat('%',#{param.orgId},'%') or org.org_id = #{param.orgId})
        <if test="param.treeLevel == '2'.toString()">
            and org.org_id != #{param.orgId}
        </if>
        and to_char(DHZY_ZYKSSJ, '%Y-%m-%d')  &gt;=  to_char(#{param.zykssj}::DATE, '%Y-%m-%d')
        and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  now()
    </select>

    <select id="regionalStatisticsGrain" resultMap="RegionalStatisticsVO" databaseId="mysql">
        select  DHZY_ZYZT as done ,
                DHZY_ZYLB as name
              FROM
	            t_job_statistics job
	        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
	        where org.org_id = #{param.orgId}
              and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d')  &gt;=  DATE_FORMAT(#{param.zykssj}, '%Y-%m-%d')
              and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  now()
    </select>
    <select id="regionalStatisticsGrain" resultMap="RegionalStatisticsVO" databaseId="kingbase">
        select  DHZY_ZYZT as done ,
                DHZY_ZYLB as name
        FROM
            t_job_statistics job
                LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        where org.org_id = #{param.orgId}
          and to_char(DHZY_ZYKSSJ, '%Y-%m-%d')  &gt;=  to_char(#{param.zykssj}::DATE, '%Y-%m-%d')
          and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &lt;=  now()
    </select>

    <select id="regionalStatistics" resultMap="RegionalStatisticsVO" databaseId="mysql">
        SELECT
            sum(CASE DHZY_ZYZT  WHEN '已关闭' THEN 0 ELSE 1 END) as 'undone',
            sum(CASE DHZY_ZYZT  WHEN '已关闭' THEN 1 ELSE 0 END) as 'done',
            name as name,id as id
        from
            (SELECT
	            job.DHZY_ZYZT,
                CASE org.tree_level  WHEN '2' THEN org.org_name ELSE a.org_name END as name,
                CASE org.tree_level  WHEN '2' THEN org.org_id ELSE a.org_id END as id
        FROM t_job_statistics job
	        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
	        LEFT JOIN t_sys_org a ON org.parent_id = a.org_id
	        where job.status = 0 and org.org_id in (select org.org_id from t_sys_org org where org.parent_ids like concat('%',#{param.orgId},'%') or org.org_id = #{param.orgId})
            <if test="param.treeLevel == '2'.toString()">
                and org.org_id != #{param.orgId}
            </if>
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d')  &gt;=  DATE_FORMAT(#{param.zykssj}, '%Y-%m-%d')
	        and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  now()
	) b GROUP BY name,id
    </select>
    <select id="regionalStatistics" resultMap="RegionalStatisticsVO" databaseId="kingbase">
        SELECT
        sum(CASE DHZY_ZYZT  WHEN '已关闭' THEN 0 ELSE 1 END) as 'undone',
        sum(CASE DHZY_ZYZT  WHEN '已关闭' THEN 1 ELSE 0 END) as 'done',
        name as name,id as id
        from
        (SELECT
        job.DHZY_ZYZT,
        CASE org.tree_level  WHEN '2' THEN org.org_name ELSE a.org_name END as name,
        CASE org.tree_level  WHEN '2' THEN org.org_id ELSE a.org_id END as id
        FROM t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        LEFT JOIN t_sys_org a ON org.parent_id = a.org_id
        where job.status = 0 and org.org_id in (select org.org_id from t_sys_org org where org.parent_ids like concat('%',#{param.orgId},'%') or org.org_id = #{param.orgId})
        <if test="param.treeLevel == '2'.toString()">
            and org.org_id != #{param.orgId}
        </if>
        and to_char(DHZY_ZYKSSJ, '%Y-%m-%d')  &gt;=  to_char(#{param.zykssj}::DATE, '%Y-%m-%d')
        and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  now()
        ) b GROUP BY name,id
    </select>

    <select id="foreignGrainDepot" resultMap="DangerousNumVO" databaseId="mysql">
        SELECT
        org.org_name as name,
        org.org_id as orgId ,
        sum(job.XGFDWRY) AS num
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        where
        job.status = 0
        <if test="param.grainDepotId != '' and param.grainDepotId != null">
            and org.org_id = #{param.grainDepotId}
        </if>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        GROUP BY org.org_name ORDER BY num desc
    </select>
    <select id="foreignGrainDepot" resultMap="DangerousNumVO" databaseId="kingbase">
        SELECT
        org.org_name as name,
        org.org_id as orgId ,
        sum(job.XGFDWRY) AS num
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        where
        job.status = 0
        <if test="param.grainDepotId != '' and param.grainDepotId != null">
            and org.org_id = #{param.grainDepotId}
        </if>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        GROUP BY org.org_name,org.org_id ORDER BY num desc
    </select>

    <select id="foreignRentalLibrary" resultMap="DangerousNumVO" databaseId="mysql">
        SELECT
        org.org_name as name,
        org.org_id as orgId ,
        sum(job.XGFDWRY) AS num
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0
        and job.SFZYK = '是'
        <if test="param.areaId != '' and param.areaId != null">
            and org.org_id = #{param.areaId}
        </if>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        GROUP BY org.org_name ORDER BY num desc
    </select>
    <select id="foreignRentalLibrary" resultMap="DangerousNumVO" databaseId="kingbase">
        SELECT
        org.org_name as name,
        org.org_id as orgId ,
        sum(job.XGFDWRY) AS num
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0
        and job.SFZYK = '是'
        <if test="param.areaId != '' and param.areaId != null">
            and org.org_id = #{param.areaId}
        </if>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        GROUP BY org.org_name,org.org_id ORDER BY num desc
    </select>

    <select id="foreignRegionalGrain" resultMap="DangerousNumVO" databaseId="mysql">
        SELECT
        org.org_name as name,
        org.org_id as orgId ,
        sum(job.XGFDWRY) AS num
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.code = org.org_code
        WHERE
        job.status= 0 and
        org.org_id in
        <foreach collection="param.ids" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        GROUP BY org.org_id ORDER BY num desc
    </select>
    <select id="foreignRegionalGrain" resultMap="DangerousNumVO" databaseId="kingbase">
        SELECT
        org.org_name as name,
        org.org_id as orgId ,
        sum(job.XGFDWRY) AS num
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.code = org.org_code
        WHERE
        job.status= 0 and
        org.org_id in
        <foreach collection="param.ids" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        GROUP BY org.org_id,org.org_name ORDER BY num desc
    </select>

    <select id="foreignHeadquarters" resultMap="DangerousNumVO" databaseId="mysql">

        SELECT
        mm.name,
        sum(mm.num) AS num,
        org.org_id orgId
        FROM
        (
        SELECT
        org.org_id AS a ,
        org.org_name as name,
        job.XGFDWRY num,
        org.org_id as orgId
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0 and
        org.tree_level = 2
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZykssj != null">
            and DHZY_ZYKSSJ between #{param.dhzyZykssj} AND #{param.dhzyZyjssj}
        </if>
        UNION ALL
        SELECT
        org.parent_id AS a ,
        org.org_name as name,
        job.XGFDWRY num,
        org.org_id as orgId
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0 and
        org.tree_level = 3
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        ) mm
        LEFT JOIN t_sys_org org ON mm.a = org.org_id
        group by mm.name ,org.org_id
    </select>
    <select id="foreignHeadquarters" resultMap="DangerousNumVO" databaseId="kingbase">

        SELECT
        mm.name,
        sum(mm.num) AS num,
        org.org_id orgId
        FROM
        (
        SELECT
        org.org_id AS a ,
        org.org_name as name,
        job.XGFDWRY num,
        org.org_id as orgId
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0 and
        org.tree_level = 2
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZykssj != null">
            and DHZY_ZYKSSJ between #{param.dhzyZykssj} AND #{param.dhzyZyjssj}
        </if>
        UNION ALL
        SELECT
        org.parent_id AS a ,
        org.org_name as name,
        job.XGFDWRY num,
        org.org_id as orgId
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0 and
        org.tree_level = 3
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        ) mm
        LEFT JOIN t_sys_org org ON mm.a = org.org_id
        group by mm.name ,org.org_id
    </select>


    <select id="distributionGrainDepot" resultType="com.testor.module.partner.model.vo.HazardousOperation"
            databaseId="mysql">
        SELECT

        sum(CASE DHZY_ZYLB  WHEN '进出仓作业' THEN 1 ELSE 0 END) as 'warehouseEntryExitOperation',
        sum(CASE DHZY_ZYLB  WHEN '高处作业' THEN 1 ELSE 0 END) as 'moreHomework',
        sum(CASE DHZY_ZYLB  WHEN '临时用电' THEN 1 ELSE 0 END) as 'temporaryElectricity',
        sum(CASE DHZY_ZYLB  WHEN '动火作业' THEN 1 ELSE 0 END) as 'fireOperation',
        sum(CASE DHZY_ZYLB  WHEN '设备检维修作业' THEN 1 ELSE 0 END) as 'equipmentInspection',
        sum(CASE DHZY_ZYLB  WHEN '熏蒸作业' THEN 1 ELSE 0 END) as 'fumigationOperations',
        sum( CASE  WHEN   DHZY_ZYLB  = ('进出仓作业' OR '高处作业' OR '临时用电' OR '动火作业' OR '设备检维修作业' OR '熏蒸作业' ) THEN 0 ELSE 1 END )
        AS otherDangerousOperations
        <if test="param.flag == '1'.toString()">
            ,org.org_id as orgId
        </if>
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        where
        job.status = 0

        <if test="param.grainDepotId != '' and param.grainDepotId != null">
            and org.org_id = #{param.grainDepotId}
        </if>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        <if test="param.flag == '1'.toString()">
            group by org.org_id
        </if>

    </select>
    <select id="distributionGrainDepot" resultType="com.testor.module.partner.model.vo.HazardousOperation"
            databaseId="kingbase">
        SELECT

        sum(CASE DHZY_ZYLB  WHEN '进出仓作业' THEN 1 ELSE 0 END) as warehouseEntryExitOperation,
        sum(CASE DHZY_ZYLB  WHEN '高处作业' THEN 1 ELSE 0 END) as moreHomework,
        sum(CASE DHZY_ZYLB  WHEN '临时用电' THEN 1 ELSE 0 END) as temporaryElectricity,
        sum(CASE DHZY_ZYLB  WHEN '动火作业' THEN 1 ELSE 0 END) as fireOperation,
        sum(CASE DHZY_ZYLB  WHEN '设备检维修作业' THEN 1 ELSE 0 END) as equipmentInspection,
        sum(CASE DHZY_ZYLB  WHEN '熏蒸作业' THEN 1 ELSE 0 END) as fumigationOperations,

        sum(CASE WHEN DHZY_ZYLB IN ('进出仓作业', '高处作业', '临时用电', '动火作业', '设备检维修作业', '熏蒸作业') THEN 0 ELSE 1 END) AS otherDangerousOperations

        <if test="param.flag == '1'.toString()">
            ,org.org_id as orgId
        </if>
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        where
        job.status = 0

        <if test="param.grainDepotId != '' and param.grainDepotId != null">
            and org.org_id = #{param.grainDepotId}
        </if>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.flag == '1'.toString()">
            group by org.org_id
        </if>

    </select>

    <select id="distributionRegionalCompaniesRentStorage"
            resultType="com.testor.module.partner.model.vo.HazardousOperation" databaseId="mysql">
        SELECT
        sum(CASE DHZY_ZYLB  WHEN '进出仓作业' THEN 1 ELSE 0 END) as 'warehouseEntryExitOperation',
        sum(CASE DHZY_ZYLB  WHEN '高处作业' THEN 1 ELSE 0 END) as 'moreHomework',
        sum(CASE DHZY_ZYLB  WHEN '临时用电' THEN 1 ELSE 0 END) as 'temporaryElectricity',
        sum(CASE DHZY_ZYLB  WHEN '动火作业' THEN 1 ELSE 0 END) as 'fireOperation',
        sum(CASE DHZY_ZYLB  WHEN '设备检维修作业' THEN 1 ELSE 0 END) as 'equipmentInspection',
        sum(CASE DHZY_ZYLB  WHEN '熏蒸作业' THEN 1 ELSE 0 END) as 'fumigationOperations',
        sum( CASE  WHEN   DHZY_ZYLB  = ('进出仓作业' OR '高处作业' OR '临时用电' OR '动火作业' OR '设备检维修作业' OR '熏蒸作业')  THEN 0 ELSE 1 END )
        AS otherDangerousOperations

        <if test="param.flag == '1'.toString()">
            ,org.org_id as orgId
        </if>
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0
        and job.SFZYK = '是'

        <if test="param.areaId != '' and param.areaId != null">
            and org.org_id = #{param.areaId}
        </if>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>

        <if test="param.flag == '1'.toString()">
            group by org.org_id
        </if>
    </select>
    <select id="distributionRegionalCompaniesRentStorage"
            resultType="com.testor.module.partner.model.vo.HazardousOperation" databaseId="kingbase">
        SELECT
        sum(CASE DHZY_ZYLB  WHEN '进出仓作业' THEN 1 ELSE 0 END) as warehouseEntryExitOperation,
        sum(CASE DHZY_ZYLB  WHEN '高处作业' THEN 1 ELSE 0 END) as moreHomework,
        sum(CASE DHZY_ZYLB  WHEN '临时用电' THEN 1 ELSE 0 END) as temporaryElectricity,
        sum(CASE DHZY_ZYLB  WHEN '动火作业' THEN 1 ELSE 0 END) as fireOperation,
        sum(CASE DHZY_ZYLB  WHEN '设备检维修作业' THEN 1 ELSE 0 END) as equipmentInspection,
        sum(CASE DHZY_ZYLB  WHEN '熏蒸作业' THEN 1 ELSE 0 END) as fumigationOperations,
        sum(CASE WHEN DHZY_ZYLB IN ('进出仓作业', '高处作业', '临时用电', '动火作业', '设备检维修作业', '熏蒸作业') THEN 0 ELSE 1 END) AS otherDangerousOperations


        <if test="param.flag == '1'.toString()">
            ,org.org_id as orgId
        </if>
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0
        and job.SFZYK = '是'

        <if test="param.areaId != '' and param.areaId != null">
            and org.org_id = #{param.areaId}
        </if>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>

        <if test="param.flag == '1'.toString()">
            group by org.org_id
        </if>
    </select>

    <select id="distributionRegionalCompanyGrainDepot"
            resultType="com.testor.module.partner.model.vo.HazardousOperation" databaseId="mysql">
        SELECT
        sum(CASE DHZY_ZYLB  WHEN '进出仓作业' THEN 1 ELSE 0 END) as 'warehouseEntryExitOperation',
        sum(CASE DHZY_ZYLB  WHEN '高处作业' THEN 1 ELSE 0 END) as 'moreHomework',
        sum(CASE DHZY_ZYLB  WHEN '临时用电' THEN 1 ELSE 0 END) as 'temporaryElectricity',
        sum(CASE DHZY_ZYLB  WHEN '动火作业' THEN 1 ELSE 0 END) as 'fireOperation',
        sum(CASE DHZY_ZYLB  WHEN '设备检维修作业' THEN 1 ELSE 0 END) as 'equipmentInspection',
        sum(CASE DHZY_ZYLB  WHEN '熏蒸作业' THEN 1 ELSE 0 END) as 'fumigationOperations',
        sum( CASE  WHEN   DHZY_ZYLB  = ('进出仓作业' OR '高处作业' OR '临时用电' OR '动火作业' OR '设备检维修作业' OR '熏蒸作业' ) THEN 0 ELSE 1 END )
        AS otherDangerousOperations
        <if test="param.flag == '1'.toString()">
            ,org.org_id as orgId
        </if>
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.code = org.org_code
        WHERE
        job.status= 0 and
        org.org_id in
        <foreach collection="param.ids" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>

        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        <if test="param.flag == '1'.toString()">
            group by org.org_id
        </if>
    </select>
    <select id="distributionRegionalCompanyGrainDepot"
            resultType="com.testor.module.partner.model.vo.HazardousOperation" databaseId="kingbase">
        SELECT
        sum(CASE DHZY_ZYLB  WHEN '进出仓作业' THEN 1 ELSE 0 END) as warehouseEntryExitOperation,
        sum(CASE DHZY_ZYLB  WHEN '高处作业' THEN 1 ELSE 0 END) as moreHomework,
        sum(CASE DHZY_ZYLB  WHEN '临时用电' THEN 1 ELSE 0 END) as temporaryElectricity,
        sum(CASE DHZY_ZYLB  WHEN '动火作业' THEN 1 ELSE 0 END) as fireOperation,
        sum(CASE DHZY_ZYLB  WHEN '设备检维修作业' THEN 1 ELSE 0 END) as equipmentInspection,
        sum(CASE DHZY_ZYLB  WHEN '熏蒸作业' THEN 1 ELSE 0 END) as fumigationOperations,
        sum(CASE WHEN DHZY_ZYLB IN ('进出仓作业', '高处作业', '临时用电', '动火作业', '设备检维修作业', '熏蒸作业') THEN 0 ELSE 1 END) AS otherDangerousOperations
        <if test="param.flag == '1'.toString()">
            ,org.org_id as orgId
        </if>
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.code = org.org_code
        WHERE
        job.status= 0 and
        org.org_id in
        <foreach collection="param.ids" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>

        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.flag == '1'.toString()">
            group by org.org_id
        </if>
    </select>

    <select id="distributionSum" resultType="com.testor.module.partner.model.vo.HazardousOperation" databaseId="mysql">
        SELECT
        sum(CASE name  WHEN '进出仓作业' THEN 1 ELSE 0 END) as 'warehouseEntryExitOperation',
        sum(CASE name  WHEN '高处作业' THEN 1 ELSE 0 END) as 'moreHomework',
        sum(CASE name  WHEN '临时用电' THEN 1 ELSE 0 END) as 'temporaryElectricity',
        sum(CASE name  WHEN '动火作业' THEN 1 ELSE 0 END) as 'fireOperation',
        sum(CASE name  WHEN '设备检维修作业' THEN 1 ELSE 0 END) as 'equipmentInspection',
        sum(CASE name  WHEN '熏蒸作业' THEN 1 ELSE 0 END) as 'fumigationOperations',
        sum( CASE  WHEN   name  = ('进出仓作业' OR '高处作业' OR '临时用电' OR '动火作业' OR '设备检维修作业' OR '熏蒸作业' ) THEN 0 ELSE 1 END )
         AS otherDangerousOperations

        <if test="param.flag == '1'.toString()">
           ,org.org_id as orgId
        </if>
        FROM
        (
        SELECT
        org.org_id AS a ,
        job.DHZY_ZYLB as name
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0 and
        org.tree_level = 2
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
        and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        UNION ALL
        SELECT
        org.parent_id AS a ,
        job.DHZY_ZYLB as name
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0 and
        org.tree_level = 3

        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        ) mm
        LEFT JOIN t_sys_org org ON mm.a = org.org_id

        <if test="param.flag == '1'.toString()">
            group by org.org_id
        </if>
    </select>
    <select id="distributionSum" resultType="com.testor.module.partner.model.vo.HazardousOperation"
            databaseId="kingbase">

        SELECT
        sum(CASE name WHEN '进出仓作业' THEN 1 ELSE 0 END) as warehouseEntryExitOperation,
        sum(CASE name WHEN '高处作业' THEN 1 ELSE 0 END) as moreHomework,
        sum(CASE name WHEN '临时用电' THEN 1 ELSE 0 END) as temporaryElectricity,
        sum(CASE name WHEN '动火作业' THEN 1 ELSE 0 END) as fireOperation,
        sum(CASE name WHEN '设备检维修作业' THEN 1 ELSE 0 END) as equipmentInspection,
        sum(CASE name WHEN '熏蒸作业' THEN 1 ELSE 0 END) as fumigationOperations,
        sum(CASE WHEN name NOT IN ('进出仓作业', '高处作业', '临时用电', '动火作业', '设备检维修作业', '熏蒸作业') THEN 1 ELSE 0 END) AS otherDangerousOperations
        <if test="param.flag == '1'.toString()">
            ,org.org_id as orgId
        </if>
        FROM
        (
        SELECT org.org_id AS a, job.DHZY_ZYLB as name
        FROM t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE job.status = 0
        AND org.tree_level = 2
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        UNION ALL
        SELECT org.parent_id AS a, job.DHZY_ZYLB as name
        FROM t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE job.status = 0
        AND org.tree_level = 3
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        ) mm
        LEFT JOIN t_sys_org org ON mm.a = org.org_id
        <if test="param.flag == '1'.toString()">
            group by org.org_id
        </if>
    </select>

    <select id="grainDepot" resultMap="DangerousNumVO" databaseId="mysql">
        SELECT
        org.org_id as orgId ,
        org.org_name as name,
        count(*) num ,
        org.org_id
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        where
        job.status = 0

        <if test="param.grainDepotId != '' and param.grainDepotId != null">
            and org.org_id = #{param.grainDepotId}
        </if>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        GROUP BY org_id
    </select>
    <select id="grainDepot" resultMap="DangerousNumVO" databaseId="kingbase">
        SELECT
        org.org_id as orgId ,
        org.org_name as name,
        count(*) num ,
        org.org_id
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        where
        job.status = 0

        <if test="param.grainDepotId != '' and param.grainDepotId != null">
            and org.org_id = #{param.grainDepotId}
        </if>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        GROUP BY org.org_id,org.org_name
    </select>

    <select id="area" resultMap="DangerousNumVO" databaseId="mysql">
        SELECT
        org.org_id as orgId ,
        job.DHZY_ZKSSPRY,
        count(*) num
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0
        and job.SFZYK = '是'
        <if test="param.areaId != '' and param.areaId != null">
            and org.org_id = #{param.areaId}
        </if>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        GROUP BY job.DHZY_ZKSSPRY

    </select>
    <select id="area" resultMap="DangerousNumVO" databaseId="kingbase">
        SELECT
        org.org_id as orgId ,
        job.DHZY_ZKSSPRY,
        count(*) num
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0
        and job.SFZYK = '是'
        <if test="param.areaId != '' and param.areaId != null">
            and org.org_id = #{param.areaId}
        </if>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        GROUP BY org.org_id,job.DHZY_ZKSSPRY
    </select>

    <select id="dangerousNum" resultMap="DangerousNumVO" databaseId="mysql">
        SELECT

        mm.a as orgId,org.org_name as name ,count(*) as num
        FROM
        (
        SELECT
        org.org_id AS a
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0 and
        org.tree_level = 2
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        UNION ALL
        SELECT
        org.parent_id AS a
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0 and
        org.tree_level = 3
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        ) mm
        LEFT JOIN t_sys_org org ON mm.a = org.org_id
        GROUP BY mm.a
    </select>
    <select id="dangerousNum" resultMap="DangerousNumVO" databaseId="kingbase">
        SELECT
        mm.a as orgId,org.org_name as name ,count(*) as num
        FROM
        (
        SELECT
        org.org_id AS a
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0 and
        org.tree_level = 2
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and TO_CHAR(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  TO_CHAR(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and TO_CHAR(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  TO_CHAR(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        UNION ALL
        SELECT
        org.parent_id AS a
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        WHERE
        job.status = 0 and
        org.tree_level = 3
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        ) mm
        LEFT JOIN t_sys_org org ON mm.a = org.org_id
        GROUP BY mm.a,org.org_name
    </select>

    <select id="regionalGrain" resultMap="DangerousNumVO" databaseId="mysql">
        SELECT
        org.org_id AS orgId,
        org.org_name as name,
        count(*) num
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.code = org.org_code
        WHERE
        job.status= 0 and
        org.org_id in
        <foreach collection="param.ids" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  DATE_FORMAT(#{param.dhzyZykssj}, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  DATE_FORMAT(#{param.dhzyZyjssj}, '%Y-%m-%d')
        </if>
        GROUP BY org.org_id

    </select>
    <select id="regionalGrain" resultMap="DangerousNumVO" databaseId="kingbase">
        SELECT
        org.org_id AS orgId,
        org.org_name as name,
        count(*) num
        FROM
        t_job_statistics job
        LEFT JOIN t_sys_org org ON job.code = org.org_code
        WHERE
        job.status= 0 and
        org.org_id in
        <foreach collection="param.ids" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="param.sfzyk != '' and param.sfzyk != null">
            and job.SFZYK = #{param.sfzyk}
        </if>
        <if test="param.dhzyZykssj != null and param.dhzyZykssj != null">
            and to_char(DHZY_ZYKSSJ, '%Y-%m-%d') &gt;=  to_char(#{param.dhzyZykssj}::DATE, '%Y-%m-%d')
        </if>
        <if test="param.dhzyZyjssj != null and param.dhzyZyjssj != null">
            and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  to_char(#{param.dhzyZyjssj}::DATE, '%Y-%m-%d')
        </if>
        GROUP BY org.org_id,org.org_name

    </select>

    <select id="hazardousWork" resultType="com.testor.module.partner.model.vo.RegionalStatisticsVO" databaseId="mysql">
       SELECT
        job.DHZY_ZYZT,
        CASE org.tree_level  WHEN '2' THEN org.org_name ELSE a.org_name END as name,
        CASE org.tree_level  WHEN '2' THEN org.org_id ELSE a.org_id END as id
        FROM t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        LEFT JOIN t_sys_org a ON org.parent_id = a.org_id
        where job.status = 0 and org.org_id in (select org.org_id from t_sys_org org where org.parent_ids like concat('%',#{param.orgId},'%') or org.org_id = #{param.orgId})
        <if test="param.treeLevel == '2'.toString()">
            and org.org_id != #{param.orgId}
        </if>
        and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d')  &gt;=  DATE_FORMAT(#{param.zykssj}, '%Y-%m-%d')
        and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  now()
         b GROUP BY name,id
    </select>
    <select id="hazardousWork" resultType="com.testor.module.partner.model.vo.RegionalStatisticsVO"
            databaseId="kingbase">
        SELECT
        job.DHZY_ZYZT,
        CASE org.tree_level  WHEN '2' THEN org.org_name ELSE a.org_name END as name,
        CASE org.tree_level  WHEN '2' THEN org.org_id ELSE a.org_id END as id
        FROM t_job_statistics job
        LEFT JOIN t_sys_org org ON job.CODE = org.org_code
        LEFT JOIN t_sys_org a ON org.parent_id = a.org_id
        where job.status = 0 and org.org_id in (select org.org_id from t_sys_org org where org.parent_ids like concat('%',#{param.orgId},'%') or org.org_id = #{param.orgId})
        <if test="param.treeLevel == '2'.toString()">
            and org.org_id != #{param.orgId}
        </if>
        and to_char(DHZY_ZYKSSJ, '%Y-%m-%d')  &gt;=  to_char(#{param.zykssj}::DATE, '%Y-%m-%d')
        and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  now()
        b GROUP BY name,id
    </select>

    <select id="selectCountBatch" resultType="com.testor.module.partner.model.vo.RegionalStatisticsVO"
            databaseId="mysql">
        SELECT
        COALESCE(SUM(CASE WHEN dhzy_zyzt = '已关闭' THEN 1 ELSE 0 END), 0) AS done,
        COALESCE(SUM(CASE WHEN dhzy_zyzt != '已关闭' THEN 1 ELSE 0 END), 0) AS undone
        FROM t_job_statistics
        WHERE org_id IN
        <foreach collection="orgIds" item="orgId" open="(" close=")" separator=",">
            #{orgId}
        </foreach>
        and DATE_FORMAT(DHZY_ZYKSSJ, '%Y-%m-%d')  &gt;=  DATE_FORMAT(#{today}, '%Y-%m-%d')
        and DATE_FORMAT(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  now()
    </select>
    <select id="selectCountBatch" resultType="com.testor.module.partner.model.vo.RegionalStatisticsVO"
            databaseId="kingbase">
        SELECT
        COALESCE(SUM(CASE WHEN dhzy_zyzt = '已关闭' THEN 1 ELSE 0 END), 0) AS done,
        COALESCE(SUM(CASE WHEN dhzy_zyzt != '已关闭' THEN 1 ELSE 0 END), 0) AS undone
        FROM t_job_statistics
        WHERE org_id IN
        <foreach collection="orgIds" item="orgId" open="(" close=")" separator=",">
            #{orgId}
        </foreach>
        and to_char(DHZY_ZYKSSJ, '%Y-%m-%d')  &gt;=  to_char(#{today}::DATE, '%Y-%m-%d')
        and to_char(DHZY_ZYJSSJ, '%Y-%m-%d') &lt;=  now()
    </select>
</mapper>