From 2ee155e0e9ddc92a68349851f3bf6e7221ad8cf3 Mon Sep 17 00:00:00 2001
From: ZhuDongming <773644075@qq.com>
Date: Wed, 31 Jul 2019 15:30:54 +0800
Subject: [PATCH] 修改排放量sql语句，新增选择公司和设备的条件
---
src/main/resources/mapper/HistoryDailyMapper.xml | 197 +++++++++++++++++++++++++-----------------------
1 files changed, 103 insertions(+), 94 deletions(-)
diff --git a/src/main/resources/mapper/HistoryDailyMapper.xml b/src/main/resources/mapper/HistoryDailyMapper.xml
index 7628af6..78e7f3f 100644
--- a/src/main/resources/mapper/HistoryDailyMapper.xml
+++ b/src/main/resources/mapper/HistoryDailyMapper.xml
@@ -1,106 +1,115 @@
<?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.moral.mapper.HistoryDailyMapper">
- <select id="getEmissionsData" resultType="java.util.Map">
- SELECT
- SUM(hd.`json` ->'$.${sensorKey}[0]') AS '${sensorKey}',
- vdi.mac,
- mp.`name` 'name'
- FROM
- history_daily hd,
- view_device_info vdi,
- monitor_point mp
- <where>
- hd.mac = vdi.mac
- AND vdi.monitor_point_id = mp.id
- AND vdi.device_tech = 1
- <if test="start != null">
- AND hd.time >= #{start}
- </if>
- <if test="end != null">
- AND hd.time < #{end}
- </if>
- <if test="regionCode != null">
- AND mp.${regionType}_code = #{regionCode}
- </if>
- <if test="orgIds != null and orgIds.size > 0">
- AND mp.organization_id IN
- <foreach collection="orgIds" open="(" separator="," close=")"
- item="listItem">
- #{listItem}
- </foreach>
- </if>
- </where>
- GROUP BY mp.`name`, vdi.mac
- </select>
+ <select id="getEmissionsData" resultType="java.util.Map">
+ SELECT
+ SUM(hd.`json` ->'$.${sensorKey}[0]') AS '${sensorKey}',
+ vdi.mac,
+ mp.`name` 'name'
+ FROM
+ history_daily hd,
+ view_device_info vdi,
+ monitor_point mp
+ <where>
+ hd.mac = vdi.mac
+ AND vdi.monitor_point_id = mp.id
+ AND vdi.device_tech = 1
+ <if test="monitorPointId != null and monitorPointId!=''">
+ AND mp.id = #{monitorPointId}
+ </if>
+ <if test="mac != null and mac!=''">
+ AND hd.mac = #{mac}
+ </if>
+ <if test="start != null">
+ AND hd.time >= #{start}
+ </if>
+ <if test="end != null">
+ AND hd.time < #{end}
+ </if>
+ <if test="regionCode != null">
+ AND mp.${regionType}_code = #{regionCode}
+ </if>
+ <if test="orgIds != null and orgIds.size > 0">
+ AND mp.organization_id IN
+ <foreach collection="orgIds" open="(" separator="," close=")"
+ item="listItem">
+ #{listItem}
+ </foreach>
+ </if>
+ </where>
+ GROUP BY mp.`name`, vdi.mac
+ </select>
- <select id="getOverproofData" resultType="java.util.Map">
- SELECT
- AVG( hd.`json` -> '$.${sensorKey}[0]' ) avg,
- ( SELECT DISTINCT limit_val ->> '$.${sensorKey}' FROM view_device_density WHERE monitor_point_id = vdd.monitor_point_id AND device_tech = vdd.device_tech ) `limit`,
- ( SELECT mp.`name` FROM monitor_point mp WHERE mp.id = vdd.monitor_point_id ) `name`,
- vdd.device_tech
- FROM
- history_daily hd,
- view_device_density vdd,
- (
- SELECT
- vdd.monitor_point_id
- FROM
- history_daily hd,
- device vdd,
- monitor_point mp
- <where>
- hd.mac = vdd.mac
- AND mp.id = vdd.monitor_point_id
- <if test="start != null">
- AND hd.time >= #{start}
- </if>
- <if test="end != null">
- AND hd.time < #{end}
- </if>
- <if test="regionCode != null">
- AND mp.${regionType}_code = #{regionCode}
- </if>
- <if test="orgIds != null and orgIds.size > 0">
- AND mp.organization_id IN
- <foreach collection="orgIds" open="(" separator="," close=")"
- item="listItem">
- #{listItem}
- </foreach>
- </if>
- </where>
- GROUP BY
- vdd.mac,
- vdd.monitor_point_id
- HAVING
- AVG( hd.`json` -> '$.${sensorKey}[0]' ) >= ( SELECT limit_val ->> '$.${sensorKey}' FROM view_device_density WHERE mac = vdd.mac ) * (1 + #{overproofRatio} * 1)
- <if test="overproofRatio != 0 and overproofRatio != 3">
- AND AVG( hd.`json` -> '$.${sensorKey}[0]' ) < ( SELECT limit_val ->> '$.${sensorKey}' FROM view_device_density WHERE mac = vdd.mac ) * (2 + #{overproofRatio} * 1)
- </if>
- ) ta
- <where>
- hd.mac = vdd.mac
- AND vdd.monitor_point_id = ta.monitor_point_id
- <if test="start != null">
- AND hd.time >= #{start}
- </if>
- <if test="end != null">
- AND hd.time < #{end}
- </if>
- </where>
- GROUP BY
- vdd.monitor_point_id,
- vdd.device_tech
- </select>
-
+ <select id="getOverproofData" resultType="java.util.Map">
+ SELECT
+ AVG( hd.`json` -> '$.${sensorKey}[0]' ) avg,
+ ( SELECT DISTINCT limit_val ->> '$.${sensorKey}' FROM view_device_density WHERE monitor_point_id =
+ vdd.monitor_point_id AND device_tech = vdd.device_tech ) `limit`,
+ ( SELECT mp.`name` FROM monitor_point mp WHERE mp.id = vdd.monitor_point_id ) `name`,
+ vdd.device_tech
+ FROM
+ history_daily hd,
+ view_device_density vdd,
+ (
+ SELECT
+ vdd.monitor_point_id
+ FROM
+ history_daily hd,
+ device vdd,
+ monitor_point mp
+ <where>
+ hd.mac = vdd.mac
+ AND mp.id = vdd.monitor_point_id
+ <if test="start != null">
+ AND hd.time >= #{start}
+ </if>
+ <if test="end != null">
+ AND hd.time < #{end}
+ </if>
+ <if test="regionCode != null">
+ AND mp.${regionType}_code = #{regionCode}
+ </if>
+ <if test="orgIds != null and orgIds.size > 0">
+ AND mp.organization_id IN
+ <foreach collection="orgIds" open="(" separator="," close=")"
+ item="listItem">
+ #{listItem}
+ </foreach>
+ </if>
+ </where>
+ GROUP BY
+ vdd.mac,
+ vdd.monitor_point_id
+ HAVING
+ AVG( hd.`json` -> '$.${sensorKey}[0]' ) >= ( SELECT limit_val ->> '$.${sensorKey}' FROM view_device_density
+ WHERE mac = vdd.mac ) * (1 + #{overproofRatio} * 1)
+ <if test="overproofRatio != 0 and overproofRatio != 3">
+ AND AVG( hd.`json` -> '$.${sensorKey}[0]' ) < ( SELECT limit_val ->> '$.${sensorKey}' FROM
+ view_device_density WHERE mac = vdd.mac ) * (2 + #{overproofRatio} * 1)
+ </if>
+ ) ta
+ <where>
+ hd.mac = vdd.mac
+ AND vdd.monitor_point_id = ta.monitor_point_id
+ <if test="start != null">
+ AND hd.time >= #{start}
+ </if>
+ <if test="end != null">
+ AND hd.time < #{end}
+ </if>
+ </where>
+ GROUP BY
+ vdd.monitor_point_id,
+ vdd.device_tech
+ </select>
+
<!-- ������������mac������������������������������������������������������ -->
<select id="getTraceabilityData" resultType="java.util.Map">
SELECT
- json ->'$.${sensorKey}[0]' AS '${sensorKey}'
+ json ->'$.${sensorKey}[0]' AS '${sensorKey}'
FROM
- history_daily
+ history_daily
<where>
<if test="mac!=null and mac!=''">
AND mac=#{mac}
--
Gitblit v1.8.0