<?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.yeejoin.amos.boot.module.jxiop.biz.mcbwarningmapper.McbWarningMapper">

    <select id="queryTodayCount" resultType="java.util.Map">
        SELECT
            COUNT(*) as count
        FROM
            amos_mcb.mcb_warning_warning_info log
                LEFT JOIN amos_mcb.mcb_warning_base_source_attribution source ON source.CODE = log.SOURCE_ATTRIBUTION
        <where>
            date(log.CREATE_DATE) = CURRENT_DATE
            and  WARNING_SOURCE_TYPE_CODE in (SELECT code FROM amos_mcb.mcb_data_dictionary WHERE remark = 'OVERVIEW' )
            <if test="projectOrgCodes != null and projectOrgCodes.size() > 0">
                AND SOURCE_ATTRIBUTION IN
                <foreach collection="projectOrgCodes" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="processingStatus != null and processingStatus != ''">
                AND log.processing_status = #{processingStatus}
            </if>
        </where>
    </select>

    <select id="queryWeekCount" resultType="java.util.Map">
<!--        SELECT-->
<!--            DATE_FORMAT(b.alarmDate, '%m/%d') AS axisData,-->
<!--            b.code AS code,-->
<!--            b.province AS province,-->
<!--            IFNULL(count(log.SEQUENCE_NBR), 0) AS seriesData-->
<!--        FROM-->
<!--            (-->
<!--                SELECT-->
<!--                    source.CODE AS code,-->
<!--                    a.DATE AS alarmDate,-->
<!--                    source.PROVINCE AS province-->
<!--                FROM-->
<!--                    amos_mcb.mcb_warning_base_source_attribution source,-->
<!--                    (-->
<!--                        SELECT CURDATE() AS date-->
<!--                            UNION ALL-->
<!--                        SELECT DATE_SUB( CURDATE(), INTERVAL 1 DAY ) AS date-->
<!--                            UNION ALL-->
<!--                        SELECT DATE_SUB( CURDATE(), INTERVAL 2 DAY ) AS date-->
<!--                            UNION ALL-->
<!--                        SELECT DATE_SUB( CURDATE(), INTERVAL 3 DAY ) AS date-->
<!--                            UNION ALL-->
<!--                        SELECT DATE_SUB( CURDATE(), INTERVAL 4 DAY ) AS date-->
<!--                            UNION ALL-->
<!--                        SELECT DATE_SUB( CURDATE(), INTERVAL 5 DAY ) AS date-->
<!--                            UNION ALL-->
<!--                        SELECT DATE_SUB( CURDATE(), INTERVAL 6 DAY ) AS date-->
<!--                    ) a-->
<!--            ) b-->
<!--            LEFT JOIN amos_mcb.mcb_warning_warning_info log ON date(log.CREATE_DATE) = b.alarmDate AND log.SOURCE_ATTRIBUTION = b.CODE-->
<!--            <where>-->
<!--                <if test="projectOrgCodes != null and projectOrgCodes.size() > 0">-->
<!--                    AND b.CODE IN-->
<!--                    <foreach collection="projectOrgCodes" item="item" open="(" separator="," close=")">-->
<!--                        #{item}-->
<!--                    </foreach>-->
<!--                </if>-->
<!--            </where>-->
<!--            GROUP BY-->
<!--&#45;&#45;             b.code,-->
<!--            b.alarmDate-->
<!--        ORDER BY-->
<!--            b.alarmDate ASC-->
        SELECT
        DATE_FORMAT(a.date,'%m/%d')  as axisData ,
        IFNULL(b.seriesData,0) as seriesData
        FROM
        (
        SELECT
        DATE_SUB( CURDATE( ), INTERVAL 6 DAY ) AS date UNION ALL
        SELECT
        DATE_SUB( CURDATE( ), INTERVAL 5 DAY ) AS date UNION ALL
        SELECT
        DATE_SUB( CURDATE( ), INTERVAL 4 DAY ) AS date UNION ALL
        SELECT
        DATE_SUB( CURDATE( ), INTERVAL 3 DAY ) AS date UNION ALL
        SELECT
        DATE_SUB( CURDATE( ), INTERVAL 2 DAY ) AS date UNION ALL
        SELECT
        DATE_SUB( CURDATE( ), INTERVAL 1 DAY ) AS date UNION ALL
        SELECT
        CURDATE( ) AS date
        ) a
        LEFT JOIN  (SELECT
        IFNULL( count( log.SEQUENCE_NBR ), 0 ) AS seriesData,
        date( log.CREATE_DATE ) AS axisData
        FROM
        amos_mcb.mcb_warning_warning_info log
        WHERE
        log.WARNING_SOURCE_TYPE_CODE IN ( SELECT CODE FROM amos_mcb.mcb_data_dictionary WHERE remark = 'OVERVIEW' )
        AND date( log.CREATE_DATE ) >= CURDATE( ) - INTERVAL 7 DAY
        <if test="projectOrgCodes != null and projectOrgCodes.size() > 0">
            AND log.SOURCE_ATTRIBUTION IN
            <foreach collection="projectOrgCodes" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY
        date( log.CREATE_DATE )) b ON a.date = b.axisData ORDER BY axisData
    </select>

    <select id="queryWarningList" resultType="java.util.Map">
        SELECT
            log.SEQUENCE_NBR,
            DATE_FORMAT(log.CREATE_DATE, '%Y-%m-%d %H:%i:%s') AS CREATE_DATE,
            log.EVENT_LEVEL_DESC,
            log.EVENT_LEVEL,
            log.SOURCE_ATTRIBUTION_DESC,
            log.SOURCE_ATTRIBUTION,
            log.CONTENT,
            LEVEL.COLOR,
            source.PROVINCE,
            log.WARNING_SOURCE_TYPE,
            log.OBJECT_NAME,
            log.PROCESSING_STATUS,
            log.PROCESSING_STATUS_DESC
        FROM
            amos_mcb.mcb_warning_warning_info log
                LEFT JOIN amos_mcb.mcb_warning_base_warning_level LEVEL ON log.EVENT_LEVEL = LEVEL.CODE and LEVEL.SEQUENCE_NBR in (1, 2, 3)
	            LEFT JOIN amos_mcb.mcb_warning_base_source_attribution source ON source.CODE = log.SOURCE_ATTRIBUTION
        <where>
            <if test="projectOrgCodes != null and projectOrgCodes.size() > 0">
                AND log.SOURCE_ATTRIBUTION IN
                <foreach collection="projectOrgCodes" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="processingStatus != null">
                AND log.PROCESSING_STATUS = #{processingStatus}
            </if>
            <if test="eventLevel != null and eventLevel != ''">
                AND log.EVENT_LEVEL = #{eventLevel}
            </if>
            <if test="createDate != null and createDate != ''">
                AND log.CREATE_DATE &gt;= concat(#{createDate}, ' 00:00:00')
                AND log.CREATE_DATE &lt;= concat(#{createDate}, ' 23:59:59')
            </if>
            <if test="startTime != null and startTime != ''">
                AND log.CREATE_DATE &gt;= concat(#{startTime}, ' 00:00:00')
            </if>
            <if test="endTime != null and endTime != ''">
                AND log.CREATE_DATE &lt;= concat(#{endTime}, ' 23:59:59')
            </if>
            <if test="warningSourceType != null and warningSourceType != ''">
                AND log.WARNING_SOURCE_TYPE like concat('%', #{warningSourceType}, '%')
            </if>
            <if test="objectName != null and objectName != ''">
                AND log.OBJECT_NAME like concat('%', #{objectName}, '%')
            </if>
	        <if test="overView != null and overView != ''">
                AND log.WARNING_SOURCE_TYPE_CODE IN (select code from mcb_data_dictionary where remark = 'OVERVIEW')
            </if>
        </where>
        ORDER BY
            log.CREATE_DATE DESC
        LIMIT #{start}, #{size}
    </select>

    <select id="queryWarningCount" resultType="java.lang.Long">
        SELECT
            count(*)
        FROM
            amos_mcb.mcb_warning_warning_info log
                LEFT JOIN amos_mcb.mcb_warning_base_warning_level LEVEL ON log.EVENT_LEVEL = LEVEL.CODE and LEVEL.SEQUENCE_NBR in (1, 2, 3)
                LEFT JOIN amos_mcb.mcb_warning_base_source_attribution source ON source.CODE = log.SOURCE_ATTRIBUTION
        <where>
            <if test="projectOrgCodes != null and projectOrgCodes.size() > 0">
                AND log.SOURCE_ATTRIBUTION IN
                <foreach collection="projectOrgCodes" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="processingStatus != null">
                AND log.PROCESSING_STATUS = #{processingStatus}
            </if>
            <if test="eventLevel != null and eventLevel != ''">
                AND log.EVENT_LEVEL = #{eventLevel}
            </if>
            <if test="createDate != null and createDate != ''">
                AND log.CREATE_DATE &gt;= concat(#{createDate}, ' 00:00:00')
                AND log.CREATE_DATE &lt;= concat(#{createDate}, ' 23:59:59')
            </if>
            <if test="startTime != null and startTime != ''">
                AND log.CREATE_DATE &gt;= concat(#{startTime}, ' 00:00:00')
            </if>
            <if test="endTime != null and endTime != ''">
                AND log.CREATE_DATE &lt;= concat(#{endTime}, ' 23:59:59')
            </if>
            <if test="warningSourceType != null and warningSourceType != ''">
                AND log.WARNING_SOURCE_TYPE like concat('%', #{warningSourceType}, '%')
            </if>
            <if test="objectName != null and objectName != ''">
                AND log.OBJECT_NAME like concat('%', #{objectName}, '%')
            </if>
            <if test="overView != null and overView != ''">
                AND log.WARNING_SOURCE_TYPE_CODE IN (select code from mcb_data_dictionary where remark = 'OVERVIEW')
            </if>
        </where>
    </select>

    <select id="overviewQuestionList" resultType="com.yeejoin.amos.boot.module.jxiop.api.dto.OverviewQuestionDto">
        SELECT
        warning.SEQUENCE_NBR warningId,
        question.CREATE_DATE createDate,
        warning.CONTENT content,
        question.COMPLETION_STATUS_DESC completionStatusDesc,
        question.SOURCE_ATTRIBUTION_DESC sourceAttributionDesc
        FROM
        mcb_warning_question_info question,
        mcb_warning_warning_info warning
      where
            question.NUM IS NOT NULL
            AND question.NUM = warning.QUESTION_NUM
            AND question.COMPLETION_STATUS != '1'
            AND warning.WARNING_SOURCE_TYPE in (select mcb_data_dictionary.name from mcb_data_dictionary WHERE mcb_data_dictionary.remark = 'OVERVIEW' )
            <if test="startTime != null and startTime != ''">
                AND question.CREATE_DATE >= #{startTime}
            </if>
            <if test="endTime != null and endTime != ''">
                AND question.CREATE_DATE &lt;= #{endTime}
            </if>
            <if test="projectOrgCodes != null and projectOrgCodes.size() > 0">
                AND question.SOURCE_ATTRIBUTION IN
                <foreach collection="projectOrgCodes" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        ORDER BY question.CREATE_DATE DESC Limit #{offset}, #{size}
    </select>

    <select id="overviewQuestionListTotal" resultType="int">
        SELECT
        count(1)
        FROM
        mcb_warning_question_info question,
        mcb_warning_warning_info warning
        where
        question.NUM IS NOT NULL
        AND question.NUM = warning.QUESTION_NUM
        AND question.COMPLETION_STATUS != '1'
        AND warning.WARNING_SOURCE_TYPE in (select mcb_data_dictionary.name from mcb_data_dictionary WHERE mcb_data_dictionary.remark = 'OVERVIEW' )
        <if test="startTime != null and startTime != ''">
            AND question.CREATE_DATE >= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            AND question.CREATE_DATE &lt;= #{endTime}
        </if>
        <if test="projectOrgCodes != null and projectOrgCodes.size() > 0">
            AND question.SOURCE_ATTRIBUTION IN
            <foreach collection="projectOrgCodes" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="queryQuestionList" resultType="java.util.Map">
        SELECT
            question.SEQUENCE_NBR,
            DATE_FORMAT(ifnull(question.REC_DATE, question.CREATE_DATE), '%Y-%m-%d %H:%i:%s') AS REC_DATE,
            question.COMPLETION_STATUS_DESC,
            question.COMPLETION_STATUS,
            question.SOURCE_ATTRIBUTION_DESC,
            question.SOURCE_ATTRIBUTION,
            question.CONTENT,
            question.LEVEL_DESC,
            source.PROVINCE
        FROM
        (SELECT
        QUESTION_NUM
        FROM
        amos_mcb.mcb_warning_warning_info
        WHERE
        mcb_warning_warning_info.PROCESSING_STATUS = 2
        AND QUESTION_NUM IS NOT NULL
        AND mcb_warning_warning_info.WARNING_SOURCE_TYPE_CODE IN ( SELECT CODE FROM amos_mcb.mcb_data_dictionary WHERE remark = 'OVERVIEW' )
        ) warnNum LEFT  join   amos_mcb.mcb_warning_question_info question on question.NUM = warnNum.QUESTION_NUM
        LEFT JOIN amos_mcb.mcb_warning_base_source_attribution source ON source.CODE = question.SOURCE_ATTRIBUTION
        <where>
            question.COMPLETION_STATUS != 1
            <if test="projectOrgCodes != null and projectOrgCodes.size() > 0">
                AND question.SOURCE_ATTRIBUTION IN
                <foreach collection="projectOrgCodes" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="completionStatus != null">
                AND question.COMPLETION_STATUS = #{completionStatus}
            </if>
            <if test="startTime != null and startTime != ''">
                AND question.CREATE_DATE &gt;= concat(#{startTime}, ' 00:00:00')
            </if>
            <if test="endTime != null and endTime != ''">
                AND question.CREATE_DATE &lt;= concat(#{endTime}, ' 23:59:59')
            </if>
        </where>
        ORDER BY
            question.REC_DATE DESC
        LIMIT #{start}, #{offset}
    </select>

    <select id="queryQuestionCount" resultType="java.lang.Long">
        select
        count(*)
        FROM
        (SELECT
        QUESTION_NUM
        FROM
        mcb_warning_warning_info
        WHERE
        mcb_warning_warning_info.PROCESSING_STATUS = 2
        AND QUESTION_NUM IS NOT NULL
        AND mcb_warning_warning_info.WARNING_SOURCE_TYPE_CODE IN ( SELECT CODE FROM mcb_data_dictionary WHERE remark = 'OVERVIEW' )
        ) warnNum inner  join   mcb_warning_question_info question on question.NUM = warnNum.QUESTION_NUM
        <if test="completionStatus != null and completionStatus == 0">
            AND question.COMPLETION_STATUS in (0,2)
        </if>
        <if test="completionStatus != null and completionStatus != 0">
            AND question.COMPLETION_STATUS = #{completionStatus}
        </if>
        LEFT JOIN mcb_warning_base_source_attribution source ON source.CODE = question.SOURCE_ATTRIBUTION
        <where>
            <if test="projectOrgCodes != null and projectOrgCodes.size() > 0">
                AND question.SOURCE_ATTRIBUTION IN
                <foreach collection="projectOrgCodes" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

            <if test="startTime != null and startTime != ''">
                AND question.CREATE_DATE &gt;= concat(#{startTime}, ' 00:00:00')
            </if>
            <if test="endTime != null and endTime != ''">
                AND question.CREATE_DATE &lt;= concat(#{endTime}, ' 23:59:59')
            </if>
        </where>
    </select>


    <select id="stationOverNum" resultType="map">
        WITH
            SubQuery AS (
                SELECT
                    SOURCE_ATTRIBUTION,
                    SUM(IFNULL(RPN, 0)) AS totalRPN,
                    COUNT(SEQUENCE_NBR) AS todayAlarm
                FROM
                    amos_mcb.mcb_warning_warning_info
                WHERE
            date(CREATE_DATE) = CURRENT_DATE and mcb_warning_warning_info.WARNING_SOURCE_TYPE_CODE in (select code FROM amos_mcb.mcb_data_dictionary WHERE remark = 'OVERVIEW')
        GROUP BY
            SOURCE_ATTRIBUTION
            )
        SELECT
            a.SEQUENCE_NBR AS sequenceNbr,
            COALESCE(a.NAME, '') AS `name`,
            COALESCE(a.CODE, '') AS `code`,
            COALESCE(a.PARENT_ID, '') AS parentId,
            COALESCE(a.LONGITUDE, '') AS longitude,
            COALESCE(a.LATITUDE, '') AS latitude,
            COALESCE(a.PROVINCE, '') AS province,
            COALESCE(a.LINK_SOURCE_ATTRIBUTION_ID, '') AS linkSourceAttributionId,
            COALESCE(a.MAP_URL, '') AS mapUrl,
            COALESCE(sq.totalRPN / NULLIF(COUNT(b.SOURCE_ATTRIBUTION), 0), 0) AS rpn,
            IFNULL(sq.todayAlarm,0) as todayAlarm
        FROM
            amos_mcb.mcb_warning_base_source_attribution a
                LEFT JOIN
            SubQuery sq ON a.CODE = sq.SOURCE_ATTRIBUTION
                LEFT JOIN
            amos_mcb.mcb_warning_warning_info b ON a.CODE = b.SOURCE_ATTRIBUTION AND date(b.CREATE_DATE) = CURRENT_DATE
        <where>
            <if test="sourceAttribution != null and sourceAttribution != ''">
                a.CODE LIKE CONCAT(#{sourceAttribution}, '%')
            </if>
        </where>
        GROUP BY
            a.CODE, a.SEQUENCE_NBR, sq.totalRPN, sq.todayAlarm
    </select>


    <select id="getYearTotal" resultType="integer">

        SELECT
            count( 1 ) AS yearNum
        FROM
            amos_mcb.mcb_warning_warning_info
                Left join mcb_warning_base_source_attribution mwbsa  on mwbsa.`CODE` = mcb_warning_warning_info.SOURCE_ATTRIBUTION
        WHERE
            YEAR(CREATE_DATE) = YEAR(CURDATE())
        <if test="province != null and province != '' ">
            and mwbsa.PROVINCE LIKE  CONCAT (#{province},'%')
        </if>
        <if test="sourceAttribution != null and sourceAttribution != '' ">
            and mwbsa.CODE LIKE  CONCAT (#{sourceAttribution},'%')
        </if>
        and mcb_warning_warning_info.WARNING_SOURCE_TYPE_CODE in (select code FROM amos_mcb.mcb_data_dictionary WHERE remark = 'OVERVIEW')

  	</select>
    <select id="getMonthTotal" resultType="integer">
        SELECT
            count( 1 ) AS monNum
        FROM
            amos_mcb.mcb_warning_warning_info
                Left join mcb_warning_base_source_attribution mwbsa  on mwbsa.`CODE` = mcb_warning_warning_info.SOURCE_ATTRIBUTION
        WHERE
            MONTH(CREATE_DATE) = MONTH(CURDATE())
        <if test="province != null and province != '' ">
            and mwbsa.PROVINCE LIKE  CONCAT (#{province},'%')
        </if>
        <if test="sourceAttribution != null and sourceAttribution != '' ">
            and mwbsa.CODE LIKE  CONCAT (#{sourceAttribution},'%')
        </if>
        and mcb_warning_warning_info.WARNING_SOURCE_TYPE_CODE in (select code FROM amos_mcb.mcb_data_dictionary WHERE remark = 'OVERVIEW')
    </select>
    <select id="getDayTotal" resultType="integer">
        SELECT
            count( 1 ) AS dayNUM ,
            mwbsa.PROVINCE
        FROM
            amos_mcb.mcb_warning_warning_info
                Left join mcb_warning_base_source_attribution mwbsa  on mwbsa.`CODE` = mcb_warning_warning_info.SOURCE_ATTRIBUTION
        WHERE
            DATE_FORMAT(CREATE_DATE,'%Y-%m-%d') =  CURDATE()
        <if test="province != null and province != '' ">
            and mwbsa.PROVINCE LIKE  CONCAT (#{province},'%')
        </if>
        <if test="sourceAttribution != null and sourceAttribution != '' ">
            and mwbsa.CODE LIKE  CONCAT (#{sourceAttribution},'%')
        </if>
        and mcb_warning_warning_info.WARNING_SOURCE_TYPE_CODE in (select code FROM amos_mcb.mcb_data_dictionary WHERE remark = 'OVERVIEW')
</select>
</mapper>
