<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zcloud.mapper.dsno1.api.PreventionApiMapper">

    <select id="getDiagnosis" parameterType="pd" resultType="pd">
        SELECT
            (select count(1) from BUS_RISKUNIT f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.ISDELETE ='0' and f.CORPINFO_ID = #{CORPINFO_ID}) as unit_count,
            (select count(1) from BUS_IDENTIFICATIONPARTS f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.ISDELETE ='0' and f.CORPINFO_ID = #{CORPINFO_ID}) as id_count,
            (select count(1) from BUS_RISKPOINT f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.ISDELETE ='0' and f.CORPINFO_ID = #{CORPINFO_ID}) as risk_count,
            (select count(1) from BUS_RISKPOINT f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.ISDELETE ='0' and f.CORPINFO_ID = #{CORPINFO_ID} and f.LEVELID = 'levelA') as a_count,
            (select count(1) from BUS_RISKPOINT f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.ISDELETE ='0' and f.CORPINFO_ID = #{CORPINFO_ID} and f.LEVELID = 'levelB') as b_count,
            (select count(1) from BUS_RISKPOINT f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.ISDELETE ='0' and f.CORPINFO_ID = #{CORPINFO_ID} and f.LEVELID = 'levelC') as c_count,
            (select count(1) from BUS_RISKPOINT f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.ISDELETE ='0' and f.CORPINFO_ID = #{CORPINFO_ID} and f.LEVELID = 'levelD') as d_count
    </select>

    <select id="listAllListsForDiagnosis" parameterType="pd" resultType="pd">
        select
            f.*,
            u.NAME AS USER_NAME
        from
            BUS_LISTMANAGER f
        LEFT JOIN
            SYS_USER u on u.USER_ID = f.USER_ID
        where
            DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and f.ISDELETE ='0' and f.CORPINFO_ID = #{CORPINFO_ID}
        order by f.OPERATTIME desc
    </select>

    <select id="listDaterangeListmanager" parameterType="pd" resultType="pd">
        select
            f.*,
            u.NAME AS USER_NAME
        from
            BUS_LISTMANAGER f
        LEFT JOIN
            SYS_USER u on u.USER_ID = f.USER_ID
        where
            DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.ISDELETE ='0' and f.CORPINFO_ID = #{CORPINFO_ID}
    </select>

    <select id="listHiddenForDiagnosis" parameterType="pd" resultType="pd">
        select
        f.*,
        u.NAME as USER_NAME,
        d.NAME as DEPARTMENT_NAME,
        (select t.FILEPATH from bus_imgfiles t where t.TYPE = 3 and t.FOREIGN_KEY = f.HIDDEN_ID and t.FILEPATH not like '%.mp4' limit 1) as HD_IMGS,
        (select t.FILEPATH from bus_imgfiles t where t.TYPE = 4 and t.FOREIGN_KEY = f.HIDDEN_ID limit 1) as ZG_IMGS
        from
            BUS_HIDDEN f
        left join SYS_USER u on u.USER_ID = f.CREATOR
        left join OA_DEPARTMENT d on d.DEPARTMENT_ID = f.HIDDENFINDDEPT
        where f.STATE != 0 and f.ISDELETE = '0'
            and f.CORPINFO_ID=#{CORPINFO_ID}
            and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME}
        order by CREATTIME desc
    </select>

    <!--人员负责清单数 -->
    <select id="countListByUser" parameterType="pd" resultType="pd">
        SELECT
            count(1) NUM,
            f.USER_ID
        FROM
            bus_listmanager f
                LEFT JOIN SYS_USER u ON u.USER_ID = f.USER_ID
        WHERE
            f.ISDELETE = '0'
          and f.CORPINFO_ID = #{CORPINFO_ID}
        GROUP BY
            f.USER_ID
    </select>

    <!--人员应处理清单数 -->
    <select id="countStaByUser" parameterType="pd" resultType="pd">
        SELECT
            sum(f.NOTEXAMINEDNUM) NUM,
            f.USER_ID
        FROM
            bus_list_statistics f
        WHERE
            f.CORPINFO_ID = #{CORPINFO_ID}
          and f.USER_ID is not null
          AND f.USER_ID != ''
			and
			(
				f.STARTTIME BETWEEN CONCAT(#{STARTTIME},' 00:00:00') and CONCAT(#{ENDTIME},' 23:59:59')
           or
            f.ENDTIME  BETWEEN CONCAT(#{STARTTIME},' 00:00:00') and CONCAT(#{ENDTIME},' 23:59:59')
           or
            (f.STARTTIME &lt;= CONCAT(#{STARTTIME},' 00:00:00') and f.ENDTIME &gt;= CONCAT(#{ENDTIME},' 23:59:59'))
            )
        GROUP BY
            f.USER_ID
    </select>

    <!-- 查询人员检查数 -->
    <select id="countCheByUser" parameterType="pd" resultType="pd">
        SELECT
            c.USER_ID,
            count( c.CHECKUSER_ID ) NUM
        FROM
            bus_checkuser c
                LEFT JOIN bus_checkrecord cr ON c.CHECKRECORD_ID = cr.CHECKRECORD_ID
        WHERE
            cr.isdelete = '0'
          AND cr.FINISHED = '1'
          AND cr.ISSTATISTICS = '1'
          AND c.user_id IS NOT NULL
          AND c.user_id != ''
			AND cr.CORPINFO_ID = #{CORPINFO_ID}
          and
            cr.CHECK_TIME BETWEEN CONCAT(#{STARTTIME},' 00:00:00') and CONCAT(#{ENDTIME},' 23:59:59')
        GROUP BY
            c.user_id
    </select>

    <select id="getHiddenNum" parameterType="pd" resultType="pd">
        <if test="TYPE != null and TYPE != ''">
            <if test="TYPE==1"><!-- 所有 -->
                SELECT
                count( 1 ) NUMBER,
                DATE_FORMAT( h.CREATTIME, '%Y-%m' ) TIME
                FROM
                bus_hidden h
                WHERE
                h.ISDELETE = '0'
                AND h.CORPINFO_ID = #{CORPINFO_ID}
                AND h.state != '0'
                AND h.HIDDENLEVEL = 'hiddenLevel0001'
                AND DATE_FORMAT( h.CREATTIME, '%Y-%m' ) &gt;= #{TIME}
                AND DATE_FORMAT( h.CREATTIME, '%Y-%m' ) &lt;= DATE_FORMAT( NOW(), '%Y-%m' )
                GROUP BY
                DATE_FORMAT( h.CREATTIME, '%Y-%m' )
                ORDER BY
                DATE_FORMAT( h.CREATTIME, '%Y-%m' )
            </if>
            <if test="TYPE==2"><!-- 未整改   -->
                SELECT
                count( 1 ) NUMBER,
                DATE_FORMAT( h.CREATTIME, '%Y-%m' ) TIME
                FROM
                bus_hidden h
                WHERE
                h.ISDELETE = '0'
                AND h.CORPINFO_ID = #{CORPINFO_ID}
                AND h.STATE in('1','-1')
                AND h.HIDDENLEVEL = 'hiddenLevel0001'
                AND DATE_FORMAT( h.CREATTIME, '%Y-%m' ) &gt;= #{TIME}
                AND DATE_FORMAT( h.CREATTIME, '%Y-%m' ) &lt;= DATE_FORMAT( NOW(), '%Y-%m' )
                GROUP BY
                DATE_FORMAT( h.CREATTIME, '%Y-%m' )
                ORDER BY
                DATE_FORMAT( h.CREATTIME, '%Y-%m' )
            </if>
            <if test="TYPE==3"><!-- 已整改   -->
                SELECT
                count( 1 ) NUMBER,
                DATE_FORMAT( h.CREATTIME, '%Y-%m' ) TIME
                FROM
                bus_hidden h
                WHERE
                h.ISDELETE = '0'
                AND h.CORPINFO_ID = #{CORPINFO_ID}
                AND h.HIDDENLEVEL = 'hiddenLevel0001'
                AND h.STATE in('3')
                AND DATE_FORMAT( h.CREATTIME, '%Y-%m' ) &gt;= #{TIME}
                AND DATE_FORMAT( h.CREATTIME, '%Y-%m' ) &lt;= DATE_FORMAT( NOW(), '%Y-%m' )
                GROUP BY
                DATE_FORMAT( h.CREATTIME, '%Y-%m' )
                ORDER BY
                DATE_FORMAT( h.CREATTIME, '%Y-%m' )
            </if>
            <if test="TYPE==4"><!-- 已验收  -->
                SELECT
                count( 1 ) NUMBER,
                DATE_FORMAT( h.CREATTIME, '%Y-%m' ) TIME
                FROM
                bus_hidden h
                WHERE
                h.ISDELETE = '0'
                AND h.CORPINFO_ID = #{CORPINFO_ID}
                AND h.HIDDENLEVEL = 'hiddenLevel0001'
                AND h.STATE in('4')
                AND DATE_FORMAT( h.CREATTIME, '%Y-%m' ) &gt;= #{TIME}
                AND DATE_FORMAT( h.CREATTIME, '%Y-%m' ) &lt;= DATE_FORMAT( NOW(), '%Y-%m' )
                GROUP BY
                DATE_FORMAT( h.CREATTIME, '%Y-%m' )
                ORDER BY
                DATE_FORMAT( h.CREATTIME, '%Y-%m' )
            </if>
        </if>
    </select>

    <!-- 列表 -->
    <select id="examineDeptList" parameterType="pd" resultType="pd">
        select
            avg(e.SCORE1) as SCORE1,
            avg(e.SCORE2) as SCORE2,
            avg(e.SCORE3) as SCORE3,
            avg(e.SCORE4) as SCORE4,
            avg(e.SCORE5) as SCORE5,
            getFullName(e.DEPARTMENT_ID) as DEPT_NAME_ALL
        from
            BUS_PERFORMANCE_EXAMINE_DEPT e
        left join OA_DEPARTMENT d on d.DEPARTMENT_ID = e.DEPARTMENT_ID
        where 1=1
        <if test="CORPINFO_ID != null and CORPINFO_ID != ''"><!-- 企业名称 -->
            and  e.CORPINFO_ID = #{CORPINFO_ID}
        </if>
        <if test="MONTH != null and MONTH != ''"><!-- 企业名称 -->
            and  e.EXAMINE_DATE = #{MONTH}
        </if>
        group by e.DEPARTMENT_ID
        ORDER BY (avg(e.SCORE1)+avg(e.SCORE2)+avg(e.SCORE3)+avg(e.SCORE4)+avg(e.SCORE5)) desc
    </select>

    <!-- 列表 -->
    <select id="examineUserList" parameterType="pd" resultType="pd">
        select
            avg(e.SCORE1) as SCORE1,
            avg(e.SCORE2) as SCORE2,
            avg(e.SCORE3) as SCORE3,
            avg(e.SCORE4) as SCORE4,
            avg(e.SCORE5) as SCORE5,
            max(u.NAME) as USER_NAME,
            getFullName(max(u.DEPARTMENT_ID)) as DEPT_NAME_ALL
        from
            BUS_PERFORMANCE_EXAMINE_USER e
        left join SYS_USER u on u.USER_ID=e.USER_ID
        LEFT JOIN OA_DEPARTMENT d ON d.DEPARTMENT_ID = u.DEPARTMENT_ID
        where 1=1 and
            d.PARENT_ID != '0'
        <if test="CORPINFO_ID != null and CORPINFO_ID != ''"><!-- 企业名称 -->
            and  e.CORPINFO_ID = #{CORPINFO_ID}
        </if>
        <if test="MONTH != null and MONTH != ''"><!-- 企业名称 -->
            and  e.EXAMINE_DATE = #{MONTH}
        </if>
        group by e.USER_ID
        ORDER BY (avg(e.SCORE1)+avg(e.SCORE2)+avg(e.SCORE3)+avg(e.SCORE4)+avg(e.SCORE5)) desc
    </select>

    <select id="goCorpInfoBasicInfo" parameterType="pd" resultType="pd">
        select
            c.CORP_NAME,
            c.CODE,
            c.CREATE_DATE,
            c.CORP_TYPE_NAME,
            train.NAME TRAINTYPE_NAME,
            r.ROLE_NAME VIPLEVELNAME,
        (
        CASE
        WHEN bmm.CORPINFO_ID IS NOT NULL
        AND bmm.MAINTENANCE_TIME_START <![CDATA[ <= ]]> NOW( ) AND bmm.MAINTENANCE_TIME_END > NOW( )
        AND bmm.MAINTENANCE_STATUS = '1' THEN
        '维护'
        WHEN TIMESTAMPDIFF( DAY, NOW( ), max( endtime.USE_DATE_END ) ) <![CDATA[ <= ]]> '90' AND TIMESTAMPDIFF( DAY, NOW( ), max( endtime.USE_DATE_END ) ) >= '0' THEN
        '临期' ELSE '正常'
        END
        ) AS ENTERPRISE_STATUS
        from bus_corp_info c
        LEFT JOIN SYS_ROLE r ON r.ROLE_ID = c.VIPLEVEL
        LEFT JOIN SYS_DICTIONARIES train on train.DICTIONARIES_ID = c.TRAINTYPE
        LEFT JOIN BUS_CORP_TIME endtime ON endtime.CORPINFO_ID = c.CORPINFO_ID
            AND endtime.ISDELETE = '0'
         LEFT JOIN(
            SELECT
                bmm3.CORPINFO_ID CORPINFO_ID,
                bmm3.MAINTENANCE_REASON MAINTENANCE_REASON,
                bmm3.MAINTENANCE_TIME_START MAINTENANCE_TIME_START,
                bmm3.MAINTENANCE_TIME_END,
                bmm3.IS_DEACTIVATE IS_DEACTIVATE,
                bmm3.MAINTENANCE_STATUS MAINTENANCE_STATUS,
                bmm3.ID ID
            FROM
                bus_maintenance_management bmm3
                LEFT JOIN ( SELECT MAX( bmm1.CREATTIME ) CREATTIME, CORPINFO_ID FROM bus_maintenance_management bmm1 WHERE bmm1.ISDELETE = '0' GROUP BY bmm1.CORPINFO_ID ) bmm2 ON bmm2.CORPINFO_ID = bmm3.CORPINFO_ID
            WHERE
                bmm3.ISDELETE = '0'
                AND bmm3.CREATTIME = bmm2.CREATTIME
            GROUP BY
                bmm3.CORPINFO_ID
            ) bmm ON bmm.CORPINFO_ID = c.CORPINFO_ID
        where c.CORPINFO_ID = #{CORPINFO_ID}
    </select>

    <select id="listAllRiskImg" parameterType="pd" resultType="pd">
        select
           f.*
        from BUS_IMGFILES f
        where exists (select 1 from BUS_RISKWARNING r where r.CORPINFO_ID = #{CORPINFO_ID}
                          and r.RISKWARNING_ID = f.FOREIGN_KEY and r.ISDELETE = 0 and r.TYPE = '1')
    </select>

    <select id="getDeptExamine" parameterType="pd" resultType="pd">
        select
            d.CORPINFO_ID,
            count(1) dept_count,
            sum(case when rc.risk_count > 0 then 1 else 0 end) risk_count
        from oa_department d
        left join (select count(1) risk_count,p.DEPARTMENT_ID DEPARTMENT_ID from bus_riskpoint p where p.ISDELETE = 0 group by p.DEPARTMENT_ID)
            rc on rc.DEPARTMENT_ID = d.DEPARTMENT_ID
        where d.ISDELETE = 0
            and d.PARENT_ID != '0'
        group by d.CORPINFO_ID
    </select>
</mapper>
