<?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.jeecg.modules.stm.metric.mapper.MetricReportMapper">
    <select id="queryMetricReportData" resultType="java.util.Map">
        SELECT
        B.id as reportId,
        A.mtrc_name AS mtrcName,
        A.mtrc_no AS mtrcNo,
        CASE
        WHEN A.coll_freq = 1 THEN '每天'
        WHEN A.coll_freq = 2 THEN '每周'
        WHEN A.coll_freq = 3 THEN '每月'
        WHEN A.coll_freq = 4 THEN '每季度'
        WHEN A.coll_freq = 5 THEN '每半年'
        WHEN A.coll_freq = 6 THEN '每一年'
        ELSE '其他频率'
        END AS collFreq,
        B.fill_time AS fillTime,
        CASE
        WHEN A.coll_freq = 1 THEN DATE_FORMAT(B.fill_time, '%Y-%m-%d')
        WHEN A.coll_freq = 2 THEN CONCAT(YEAR(B.fill_time), '年第', WEEK(B.fill_time), '周')
        WHEN A.coll_freq = 3 THEN DATE_FORMAT(B.fill_time, '%Y年%m月')
        WHEN A.coll_freq = 4 THEN CONCAT(YEAR(B.fill_time), '年第', QUARTER(B.fill_time), '季度')
        WHEN A.coll_freq = 5 THEN
        CASE
        WHEN MONTH(B.fill_time) &lt;= 6 THEN CONCAT(YEAR(B.fill_time), '年上半年')
        ELSE CONCAT(YEAR(B.fill_time), '年下半年')
        END
        WHEN A.coll_freq = 6 THEN CONCAT(YEAR(B.fill_time), '年')
        ELSE DATE_FORMAT(B.fill_time, '%Y-%m-%d')
        END AS timeName,
        CASE
        WHEN B.risk_level = 1 THEN '低风险'
        WHEN B.risk_level = 2 THEN '中风险'
        WHEN B.risk_level = 3 THEN '高风险'
        ELSE '低风险'
        END AS riskLevel,
        C.domain_name AS domainName,
        CASE
        WHEN A.mtrc_ctp = 1 THEN '基本指标'
        WHEN A.mtrc_ctp = 2 THEN '计算指标'
        ELSE '其他类型'
        END AS mtrcCtp,
        B.fill_vals AS fillVals,
        CASE
        WHEN B.app_sta = 1 THEN '通过'
        WHEN B.app_sta = 2 THEN '未通过'
        ELSE '未审批'
        END AS appSta
        FROM metric_monitor_set A
        INNER JOIN metric_report B ON A.mtrc_no = B.mtrc_no
        INNER JOIN st_domain C ON A.domain_no LIKE CONCAT(C.domain_code, '%') AND C.level = 2
        <where>
        <if test="query.startTime != null and query.endTime != null">
            AND B.fill_time BETWEEN #{query.startTime} AND #{query.endTime}
        </if>
        <if test="query.mtrcNo != null and query.mtrcNo != ''">
            AND A.mtrc_no = #{query.mtrcNo}
        </if>
        <if test="query.riskLevel != null">
            AND B.risk_level = #{query.riskLevel}
        </if>
        <if test="query.collFreq != null">
            AND A.coll_freq = #{query.collFreq}
        </if>

        <if test="query.collMethod != null ">
            AND A.coll_method = #{query.collMethod}
        </if>
        <if test="query.mtrcCtp != null ">
            AND A.mtrc_ctp = #{query.mtrcCtp}
        </if>

        <if test="query.mtrcTp != null ">
            AND A.mtrc_tp = #{query.mtrcTp}
        </if>

        <if test="query.qyear != null ">
            AND B.fill_time LIKE CONCAT(#{query.qyear}, '%')
        </if>

        <if test="query.appSta != null ">
            AND B.app_sta = #{query.appSta}
        </if>

        <if test="query.mtrcName != null ">
            AND A.mtrc_name like  CONCAT('%',#{query.mtrcName}, '%')
        </if>

        </where>
        ORDER BY B.fill_time DESC,B.app_sta
    </select>
</mapper>