<?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="org.stm.metric.mapper.MetricStatReportMapper">
    <select id="queryMetricStatByDomain" resultType="java.util.Map">
        SELECT
            IFNULL(D.domain_code, -1) AS code,
            IFNULL(D.domain_name, '无领域') AS name,
            IFNULL(C.risk_level, 0) AS riskLevel,
            COUNT(B.mtrc_no) AS cnt
        FROM metric_monitor_set B
                 INNER JOIN metric_report C ON B.mtrc_no = C.mtrc_no
                 INNER JOIN st_domain D ON B.domain_no LIKE CONCAT(D.domain_code, '%')
            AND D.level = 2
        WHERE B.mtrc_tp = 1
          AND C.risk_level IS NOT NULL
          AND (
            B.coll_freq = #{query.tp}
                OR
            (
                DATE_FORMAT(C.fill_time, '%Y-%m') = DATE_FORMAT(#{query.endDate}, '%Y-%m')
                    AND B.coll_freq &lt;&gt; #{query.tp}
                )
            )
        GROUP BY D.domain_code, D.domain_name, C.risk_level
    </select>
    <select id="queryMetricStatByMtricClass" resultType="java.util.Map">
        SELECT
            IFNULL(D.item_value, -1) AS code,
            IFNULL(D.item_text, '无分类') AS name,
            IFNULL(C.risk_level, 0) AS riskLevel,
            COUNT(B.mtrc_no) AS cnt
        FROM metric_monitor_set B
                 INNER JOIN metric_report C ON B.mtrc_no = C.mtrc_no
                 INNER JOIN sys_dict_item D ON B.mtrc_class = D.item_value
                 INNER JOIN sys_dict E ON D.dict_id = E.id AND E.dict_code = 'mtrc_class'
        WHERE B.mtrc_tp = 1
          AND (B.mtrc_class IS NULL OR E.dict_code = 'mtrc_class')
          AND C.risk_level IS NOT NULL
          AND (
            (
                C.fill_time BETWEEN #{query.stDate} AND #{query.endDate}
                    AND B.coll_freq = #{query.tp}
                )
                OR
            (
                DATE_FORMAT(C.fill_time, '%Y-%m') = DATE_FORMAT(#{query.endDate}, '%Y-%m')
                    AND B.coll_freq &lt;&gt; #{query.tp}
                )
            )
        GROUP BY B.mtrc_class, C.risk_level, D.item_text
    </select>
    <select id="queryMetricStatDetail" resultType="java.util.Map">
        SELECT
            B.domain_no AS domainCode,
            B.mtrc_class AS mtrcClass,
            B.mtrc_name AS mtrcName,
            B.mtrc_no AS mtrcNo,
            B.des AS mtrcDes,
            C.risk_level AS riskLevel,
            B.tol_val AS tolVal,
            B.warn_val AS warnVal,
            C.fill_vals AS fillVals,
            B.unit
        FROM metric_monitor_set B
                 INNER JOIN metric_report C ON B.mtrc_no = C.mtrc_no
                 INNER JOIN st_domain D ON B.domain_no LIKE CONCAT(D.domain_code, '%')
            AND D.level = 2
        WHERE B.mtrc_tp = 1
          AND C.risk_level IS NOT NULL
          AND
            (B.coll_freq = #{query.tp} OR (
                DATE_FORMAT(C.fill_time, '%Y-%m') = DATE_FORMAT(#{query.endDate}, '%Y-%m')
                    AND B.coll_freq &lt;&gt; #{query.tp}
                )
                )
        ORDER BY B.mtrc_no ASC
    </select>
    <select id="queryReportSubTitle" resultType="java.util.Map">
        SELECT id, QUARTER(report_date) AS curQuarter, YEAR(report_date) AS curYear,WEEK(report_date) AS curWeek,
        MONTH(report_date) AS curMonth FROM metric_stat_report
        <where>
            <if test="query.id!=null">
                id= #{query.id}
            </if>
        </where>
    </select>
</mapper>
