From 5b1aa6d3fd9c43ae87366148bfd3574e561efebb Mon Sep 17 00:00:00 2001
From: guoshilong <123456>
Date: Tue, 23 Jan 2024 15:45:15 +0800
Subject: [PATCH] 大坝监测预警接口逻辑修改
---
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml | 614 +++++++++++++++++++++++++++++++++++++++++---------
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.java | 9
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/controller/DamSecurityController.java | 26 +
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/impl/DamSecurityServiceImpl.java | 12
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/IDamSecurityService.java | 2
5 files changed, 539 insertions(+), 124 deletions(-)
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/controller/DamSecurityController.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/controller/DamSecurityController.java
index f255202..53d858e 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/controller/DamSecurityController.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/controller/DamSecurityController.java
@@ -32,20 +32,32 @@
@Autowired
private INkyClient nkyClient;
+// /**
+// * 大坝安全监测状况统计
+// * @param damSecurityStatistics
+// * @return
+// */
+// @ApiOperation(value = "大坝安全统计-大坝安全监测状况统计", notes = "大坝安全统计-大坝安全监测状况统计")
+// @GetMapping(value = "/singleRegionCount")
+// public R singleRegionCount(DamSecurityStatistics damSecurityStatistics) {
+// DamSecurityStatistics data = damSecurityService.getSingleRegionCount(damSecurityStatistics);
+// return R.data(data);
+// }
+
/**
- * 大坝安全监测状况统计
+ * 大坝安全监测状况统计(逻辑已修改)
* @param damSecurityStatistics
* @return
*/
@ApiOperation(value = "大坝安全统计-大坝安全监测状况统计", notes = "大坝安全统计-大坝安全监测状况统计")
@GetMapping(value = "/singleRegionCount")
- public R singleRegionCount(DamSecurityStatistics damSecurityStatistics) {
- DamSecurityStatistics data = damSecurityService.getSingleRegionCount(damSecurityStatistics);
+ public R singleRegionCountOptimize(DamSecurityStatistics damSecurityStatistics) {
+ DamSecurityStatistics data = damSecurityService.getSingleRegionCountOptimize(damSecurityStatistics);
return R.data(data);
}
/**
- * 大坝安全监测区域统计
+ * 大坝安全监测区域统计(逻辑已修改)
* @param damSecurityStatistics
* @return
*/
@@ -91,7 +103,7 @@
}
/**
- * 大坝安全监测表格
+ * 大坝安全监测表格(逻辑已修改)
* @param damStationStatistics
* @return
*/
@@ -103,7 +115,7 @@
}
/**
- * 大坝安全监测表格导出
+ * 大坝安全监测表格导出（逻辑已修改）
* @param damStationStatistics
* @return
*/
@@ -176,7 +188,7 @@
}
/**
- * 业务一张图 大坝安全监测（地图）
+ * 业务一张图 大坝安全监测（地图）（逻辑已修改）
* @param obj(adCode :行政区划;type:监测类型;status :状态 1全部;2正常;3异常)
* @return
*/
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.java
index dfb6f16..e25b1c1 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.java
@@ -11,12 +11,17 @@
public interface DamSecurityMapper {
DamSecurityStatistics getSingleRegionCount(@Param("dam") DamSecurityStatistics damSecurityStatistics);
+ DamSecurityStatistics getSingleRegionCountOptimize(@Param("dam") DamSecurityStatistics damSecurityStatistics);
List<DamSecurityStatistics> listRegionCount(@Param("dam") DamSecurityStatistics damSecurityStatistics);
+ List<DamSecurityStatistics> listRegionCountOptimize(@Param("dam") DamSecurityStatistics damSecurityStatistics);
+
List<DamStationStatistics> tableResCount(@Param("dam") DamStationStatistics damStationStatistics);
List<DamStationStatistics> tableResCountPage(@Param("dam")DamStationStatistics damStationStatistics, IPage<DamStationStatistics> page);
+ List<DamStationStatistics> tableResCountPageOptimize(@Param("dam") DamStationStatistics damStationStatistics, IPage<DamStationStatistics> page);
+
List<DamStationStatistics> alarmResCount(@Param("dam") DamStationStatistics damStationStatistics,@Param("basCodes") String basCodes);
@@ -44,8 +49,6 @@
List<AlarmGetVO> pageMonitorWy(IPage<AlarmGetVO> page, @Param("vo") AlarmGetVO alarmGet);
- List<LevelPo> levelMonitorPie(@Param("dam") DamMonitor damMonitor);
-
List<DamStatisticsRegion> getDamSyRegion(@Param("dam") DamStatisticsRegion damStatisticsRegion);
List<DamStatisticsRegion> getDamSlRegion(@Param("dam") DamStatisticsRegion damStatisticsRegion);
@@ -65,4 +68,6 @@
DamStatisticsRes damResInfoCountSl(@Param("adCode") String adCode);
DamStatisticsRes damResInfoCountWy(@Param("adCode") String adCode);
+
+
}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml
index 809085d..6a7a1d6 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml
@@ -93,6 +93,112 @@
</select>
+ <select id="getSingleRegionCountOptimize" resultType="cn.gistack.sm.damSecurity.vo.DamSecurityStatistics">
+
+ SELECT allNum,errorNum,AllNum-errorNum normalNum,aab."ad_name" adName,aab."guid" adCode FROM
+ (
+ SELECT
+ (
+ SELECT count(*) FROM
+ (
+ SELECT
+ a."guid",
+ a."name",
+ a."eng_scal",
+ case when b."ad_grad" = 4 THEN b."ad_code" ELSE NULL END AS "town_ad_code",
+ case when b."ad_grad" = 4 THEN b."ad_name" ELSE NULL END AS "town_ad_name",
+ case when b."ad_grad" = 4 THEN b."order_id" ELSE NULL END AS "town_order_id",
+ case when b."ad_grad" = 3 THEN b."ad_code" when c."ad_grad" = 3 THEN c."ad_code" END AS "county_ad_code",
+ case when b."ad_grad" = 3 THEN b."ad_name"when c."ad_grad" = 3 THEN C."ad_name" END AS "county_ad_name",
+ case when b."ad_grad" = 3 THEN b."order_id"when c."ad_grad" = 3 THEN C."order_id" END AS "county_order_id",
+ case when b."ad_grad" = 2 THEN b."ad_code"when c."ad_grad" = 2 THEN c."ad_code"ELSE d."ad_code" END AS
+ "city_ad_code",
+ case when b."ad_grad" = 2 THEN b."ad_name"when c."ad_grad" = 2 THEN c."ad_name"ELSE d."ad_name" END AS
+ "city_ad_name",
+ case when b."ad_grad" = 2 THEN b."order_id" when c."ad_grad" = 2 THEN c."order_id"ELSE d."order_id" END AS
+ "city_order_id",
+ case when b."ad_grad" = 1 THEN b."ad_code"when c."ad_grad" = 1 THEN c."ad_code"when d."ad_grad" = 1 THEN
+ d."ad_code"ELSE e."ad_code" END AS "province_ad_code",
+ case when b."ad_grad" = 1 THEN b."ad_name"when c."ad_grad" = 1 THEN c."ad_name"when d."ad_grad" = 1 THEN
+ d."ad_name"ELSE e."ad_name" END AS "province_ad_name",
+ case when b."ad_grad" = 1 THEN b."order_id"when c."ad_grad" = 1 THEN c."order_id" when d."ad_grad" = 1 THEN
+ d."order_id"ELSE e."order_id" END AS "province_order_id"
+ FROM sjzt_md."att_res_base" a
+ LEFT JOIN (
+
+ SELECT
+ distinct (dri."res_cd")
+ FROM
+ sjzt_dw."dim_res_info_a" dri
+ LEFT JOIN sjzt_ods."dsm_spg_spprmp" dss ON dri."oth_reg_cd" = dss."rscd"
+ where dss."rscd" is not null
+
+
+ ) f ON a."guid" = f."res_cd"
+ LEFT JOIN (
+
+
+ SELECT distinct(dri."res_cd")
+ from sjzt_dw."dim_res_info_a" dri
+ left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd"
+ where dss."rscd" is not null
+
+
+ ) G on a."guid" = g."res_cd"
+ LEFT JOIN (
+
+
+ SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb
+ where awcb."cd" != '00'
+
+ ) h ON a."guid" = h."res_cd"
+ LEFT JOIN sjzt_md."att_ad_base" b ON a."interior_ad_guid" = b."guid"
+ LEFT JOIN sjzt_md."att_ad_base" c ON b."p_ad_code" = c."guid"
+ LEFT JOIN sjzt_md."att_ad_base" d ON c."p_ad_code" = d."guid"
+ LEFT JOIN sjzt_md."att_ad_base" e ON d."p_ad_code" = e."guid"
+ where (f."res_cd" is not null OR g."res_cd" is not null OR h."res_cd" is not null)
+ ) baseRes
+ LEFT JOIN SJZT_MD."att_ad_base" aab ON aab."guid" = baseRes."town_ad_code"
+ LEFT JOIN SJZT_MD."att_ad_base" aa ON aab."p_ad_code" = aa."ad_code"
+ LEFT JOIN SJZT_MD."att_ad_base" ab ON aa."p_ad_code" = ab."ad_code"
+ LEFT JOIN SJZT_MD."att_ad_base" ac ON ab."p_ad_code" = ac."ad_code"
+ where 1=1
+ <if test="dam.adCode != null and dam.adCode != '' ">
+ AND (aab."ad_code" =#{dam.adCode} or aa."ad_code" = #{dam.adCode} or ab."ad_code" = #{dam.adCode} or
+ ac."ad_code" = #{dam.adCode})
+ </if>
+ ) AllNum,
+ (
+ SELECT COUNT(*)
+ FROM (SELECT distinct DAM_ID FROM NKY_ALARM_GET NAG
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = NAG.DAM_ID
+ LEFT JOIN SJZT_MD."att_ad_base" aab ON aab."guid" = arb."interior_ad_guid"
+ LEFT JOIN SJZT_MD."att_ad_base" aa ON aab."p_ad_code" = aa."ad_code"
+ LEFT JOIN SJZT_MD."att_ad_base" ab ON aa."p_ad_code" = ab."ad_code"
+ LEFT JOIN SJZT_MD."att_ad_base" ac ON ab."p_ad_code" = ac."ad_code"
+ WHERE 1=1
+ <if test="dam.adCode != null and dam.adCode != '' ">
+ and (aab."ad_code" =#{dam.adCode} or aa."ad_code" = #{dam.adCode} or ab."ad_code" = #{dam.adCode} or
+ ac."ad_code" = #{dam.adCode})
+ </if>
+
+ <if test="dam.startTime != null and dam.startTime != ''">
+ AND DATE_FORMAT(time,'%Y-%m-%d') >= #{dam.startTime}
+ </if>
+ <if test="dam.endTime != null and dam.endTime !='' ">
+ AND DATE_FORMAT(time,'%Y-%m-%d') <= #{dam.endTime}
+ </if>
+ )) errorNum
+ ),SJZT_MD."att_ad_base" aab
+ where 1=1
+ <if test="dam.adCode != null and dam.adCode != '' ">
+ and aab."guid" = #{dam.adCode}
+ </if>
+
+ </select>
+
+
+
<select id="listRegionCount" resultType="cn.gistack.sm.damSecurity.vo.DamSecurityStatistics">
@@ -150,13 +256,6 @@
LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
UNION
SELECT
- b."guid" as "res_cd",
- 'sy' as "sys_resource"
- FROM
- sjzt_ods."dsm_spg_spprmp" a
- LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code") WHERE "res_cd" is not null) f ON a."guid" =
- f."res_cd"
- LEFT JOIN (SELECT
"res_cd",
"sys_resource"
FROM
@@ -216,6 +315,118 @@
)
</select>
+ <select id="listRegionCountOptimize" resultType="cn.gistack.sm.damSecurity.vo.DamSecurityStatistics">
+
+ SELECT allNum,errorNum, allNum-errorNum normalNum,adName,adCode FROM
+ (
+
+ SELECT
+ (
+ SELECT COUNT(*) FROM
+ (
+ SELECT
+ a."guid",
+ a."name",
+ a."eng_scal",
+ case when b."ad_grad" = 4 THEN b."ad_code" ELSE NULL END AS "town_ad_code",
+ case when b."ad_grad" = 4 THEN b."ad_name" ELSE NULL END AS "town_ad_name",
+ case when b."ad_grad" = 4 THEN b."order_id" ELSE NULL END AS "town_order_id",
+ case when b."ad_grad" = 3 THEN b."ad_code"
+ when c."ad_grad" = 3 THEN c."ad_code" END AS "county_ad_code",
+ case when b."ad_grad" = 3 THEN b."ad_name"
+ when c."ad_grad" = 3 THEN C."ad_name" END AS "county_ad_name",
+ case when b."ad_grad" = 3 THEN b."order_id"
+ when c."ad_grad" = 3 THEN C."order_id" END AS "county_order_id",
+ case when b."ad_grad" = 2 THEN b."ad_code"
+ when c."ad_grad" = 2 THEN c."ad_code"
+ ELSE d."ad_code" END AS "city_ad_code",
+ case when b."ad_grad" = 2 THEN b."ad_name"
+ when c."ad_grad" = 2 THEN c."ad_name"
+ ELSE d."ad_name" END AS "city_ad_name",
+ case when b."ad_grad" = 2 THEN b."order_id"
+ when c."ad_grad" = 2 THEN c."order_id"
+ ELSE d."order_id" END AS "city_order_id",
+ case when b."ad_grad" = 1 THEN b."ad_code"
+ when c."ad_grad" = 1 THEN c."ad_code"
+ when d."ad_grad" = 1 THEN d."ad_code"
+ ELSE e."ad_code" END AS "province_ad_code",
+ case when b."ad_grad" = 1 THEN b."ad_name"
+ when c."ad_grad" = 1 THEN c."ad_name"
+ when d."ad_grad" = 1 THEN d."ad_name"
+ ELSE e."ad_name" END AS "province_ad_name",
+ case when b."ad_grad" = 1 THEN b."order_id"
+ when c."ad_grad" = 1 THEN c."order_id"
+ when d."ad_grad" = 1 THEN d."order_id"
+ ELSE e."order_id" END AS "province_order_id"
+ FROM sjzt_md."att_res_base" a
+ LEFT JOIN (
+
+
+ SELECT
+ distinct (dri."res_cd")
+ FROM
+ sjzt_dw."dim_res_info_a" dri
+ LEFT JOIN sjzt_ods."dsm_spg_spprmp" dss ON dri."oth_reg_cd" = dss."rscd"
+ where dss."rscd" is not null
+
+ ) f ON a."guid" = f."res_cd"
+
+ LEFT JOIN (
+
+ SELECT distinct(dri."res_cd")
+ from sjzt_dw."dim_res_info_a" dri
+ left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd"
+ where dss."rscd" is not null
+
+ ) G on a."guid" = g."res_cd"
+
+
+ LEFT JOIN (
+
+
+ SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb
+ where awcb."cd" != '00'
+
+
+ ) h ON a."guid" = h."res_cd"
+ LEFT JOIN sjzt_md."att_ad_base" b ON a."interior_ad_guid" = b."guid"
+ LEFT JOIN sjzt_md."att_ad_base" c ON b."p_ad_code" = c."guid"
+ LEFT JOIN sjzt_md."att_ad_base" d ON c."p_ad_code" = d."guid"
+ LEFT JOIN sjzt_md."att_ad_base" e ON d."p_ad_code" = e."guid"
+ where (f."res_cd" is not null OR g."res_cd" is not null OR h."res_cd" is not null) and (d."ad_code" = aab."guid"
+ or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
+ )
+ ) allNum,
+
+ (
+ SELECT COUNT(*)FROM (SELECT distinct DAM_ID FROM NKY_ALARM_GET NAG
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = NAG.DAM_ID
+ left join SJZT_MD."att_ad_base" b on arb."interior_ad_guid" = b."ad_code"
+ left join SJZT_MD."att_ad_base" c on b."p_ad_code" = c."ad_code"
+ left join SJZT_MD."att_ad_base" d on c."p_ad_code" = d."ad_code"
+ where (d."ad_code" = aab."guid" or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
+
+ <if test="dam.startTime != null and dam.startTime != ''">
+ AND DATE_FORMAT(time,'%Y-%m-%d') >= #{dam.startTime}
+ </if>
+ <if test="dam.endTime != null and dam.endTime !='' ">
+ AND DATE_FORMAT(time,'%Y-%m-%d') <= #{dam.endTime}
+ </if>
+
+ )) errorNum,
+ aab."ad_name" adName , aab."guid" adCode from SJZT_MD."att_ad_base" aab
+ where
+ <choose>
+ <when test="dam.adCode != null and dam.adCode !='' ">
+ "p_ad_code" = #{dam.adCode}
+ </when>
+ <otherwise>
+ "ad_grad" = 2
+ </otherwise>
+ </choose>
+ )
+ </select>
+
<select id="tableResCount" resultType="cn.gistack.sm.damSecurity.vo.DamStationStatistics">
@@ -262,20 +473,29 @@
case when b."ad_grad" = 1 THEN b."order_id" when c."ad_grad" = 1 THEN c."order_id" when d."ad_grad" = 1 THEN
d."order_id" ELSE e."order_id" END AS "province_order_id"
FROM sjzt_md."att_res_base" a
+
+
LEFT JOIN (
- SELECT "res_cd","sys_resource" FROM (SELECT b."guid" as "res_cd",'sy' as "sys_resource" FROM
- sjzt_ods."dsm_spg_pztb" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
- UNION
- SELECT b."guid" as "res_cd", 'sy' as "sys_resource" FROM sjzt_ods."dsm_spg_spprmp" a LEFT JOIN
- SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code") WHERE "res_cd" is not null) f ON a."guid" = f."res_cd"
+ SELECT
+ distinct (dri."res_cd")
+ FROM
+ sjzt_dw."dim_res_info_a" dri
+ LEFT JOIN sjzt_ods."dsm_spg_spprmp" dss ON dri."oth_reg_cd" = dss."rscd"
+ where dss."rscd" is not null
+ ) f ON a."guid" = f."res_cd"
+
+
LEFT JOIN (
- SELECT "res_cd", "sys_resource" FROM (SELECT b."guid" as "res_cd", 'sl' as "sys_resource" FROM
- sjzt_ods."dsm_spg_spqnmp" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code" group by b."guid")
- WHERE "res_cd" is not null) G on a."guid" = g."res_cd"
- LEFT JOIN (SELECT "res_cd", "sys_resource" FROM (SELECT b."guid" as "res_cd", 'wy' as "sys_resource" FROM
- (SELECT "station_code" from sjzt_md."att_mqtt_calc_data" group by "station_code") a LEFT JOIN
- SJZT_MD."att_res_base" b on substr(a."station_code",0,length(a."station_code")-2) = b."guid" group by b."guid")
- WHERE "res_cd" is not null) h ON a."guid" = h."res_cd"
+ SELECT distinct(dri."res_cd")
+ from sjzt_dw."dim_res_info_a" dri
+ left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd"
+ where dss."rscd" is not null
+ ) G on a."guid" = g."res_cd"
+ LEFT JOIN (
+ SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb
+ where awcb."cd" != '00'
+ ) h ON a."guid" = h."res_cd"
+
LEFT JOIN sjzt_md."att_ad_base" b ON a."interior_ad_guid" = b."guid"
LEFT JOIN sjzt_md."att_ad_base" c ON b."p_ad_code" = c."guid"
LEFT JOIN sjzt_md."att_ad_base" d ON c."p_ad_code" = d."guid"
@@ -283,33 +503,50 @@
where (f."res_cd" is not null OR g."res_cd" is not null OR h."res_cd" is not null)
) baseRes
LEFT JOIN (
- SELECT "res_cd",COUNT(*) wyStation
- FROM
- (
- SELECT b."guid" as "res_cd",'wy' as "sys_resource"
- FROM (
- SELECT "station_code" from sjzt_md."att_mqtt_calc_data" group by "station_code") a LEFT JOIN
- SJZT_MD."att_res_base" b on substr(a."station_code",0,length(a."station_code")-2) = b."guid"
- WHERE b."guid" is not null
- )
- GROUP BY "res_cd"
+
+
+ select res."res_cd",count(*) wyStation from(
+
+ SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb
+ where awcb."cd" != '00'
+
+ )res LEFT JOIN SJZT_MD."att_wy_cd_base" acb ON acb."res_cd" = res."res_cd"
+ where acb."cd_type" = 'wy' group by res."res_cd"
+
+
) wy ON wy."res_cd" = baseRes."guid"
LEFT JOIN(
- SELECT "res_cd", COUNT(*) syStation
- FROM( SELECT b."guid" as "res_cd",'sy' as "sys_resource"
- FROM sjzt_ods."dsm_spg_spprmp" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
- WHERE b."guid" is not null
- )
- GROUP BY "res_cd"
+
+ select res."res_cd",count(*) syStation from(
+ SELECT
+ distinct (dri."res_cd")
+ FROM
+ sjzt_dw."dim_res_info_a" dri
+ LEFT JOIN sjzt_ods."dsm_spg_spprmp" dss ON dri."oth_reg_cd" = dss."rscd"
+ where dss."rscd" is not null
+ )res LEFT JOIN SJZT_MD."att_cd_base" acb ON acb."res_cd" = res."res_cd"
+ where acb."cd_type" = 'sy' group by res."res_cd"
+
) sy ON sy."res_cd" = baseRes."guid"
LEFT JOIN (
- SELECT "res_cd",COUNT(*) slStation
- FROM ( SELECT b."guid" as "res_cd",'sl' as "sys_resource"
- FROM sjzt_ods."dsm_spg_spqnmp" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
- WHERE b."guid" is not null
- group by b."guid" )
- GROUP BY "res_cd"
+
+
+ select res."res_cd",count(*) slStation from(
+
+
+ SELECT distinct(dri."res_cd")
+ from sjzt_dw."dim_res_info_a" dri
+ left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd"
+ where dss."rscd" is not null
+
+
+ )res LEFT JOIN SJZT_MD."att_cd_base" acb ON acb."res_cd" = res."res_cd"
+ where acb."cd_type" = 'sl' group by res."res_cd"
+
+
) sl ON sl."res_cd" = baseRes."guid"
+
+
LEFT JOIN (
SELECT DAM_ID ,COUNT(*) errorNum FROM NKY_ALARM_GET
WHERE 1=1
@@ -489,6 +726,174 @@
</select>
+
+ <select id="tableResCountPageOptimize" resultType="cn.gistack.sm.damSecurity.vo.DamStationStatistics">
+
+ SELECT
+ info."guid" AS resCd,
+ info."name" AS resName,
+ info."eng_scal" AS engScal,
+ info."town_ad_code" AS townCode,
+ info."town_ad_name" AS townName,
+ info."county_ad_code" AS countyCode,
+ info."county_ad_name" AS countyName ,
+ info."city_ad_code" AS cityCode,
+ info."city_ad_name" AS cityName,
+ (info.wy+info.sy+info.sl) AS total,
+ (info.wy+info.sy+info.sl-info.errorCountNum) AS normalNum,
+ info.errorNum AS errorNum
+ FROM
+ (
+ SELECT baseRes.*,ifnull(wy.wyStation,0) wy ,ifnull(sy.syStation,0) sy,ifnull(sl.slStation,0)
+ sl,ifnull(alarm.errorNum,0) errorNum, ifnull(c1.errorCountNum,0) errorCountNum FROM
+ (
+ SELECT
+ a."guid",
+ a."name",
+ a."eng_scal",
+ case when b."ad_grad" = 4 THEN b."ad_code" ELSE NULL END AS "town_ad_code",
+ case when b."ad_grad" = 4 THEN b."ad_name" ELSE NULL END AS "town_ad_name",
+ case when b."ad_grad" = 4 THEN b."order_id" ELSE NULL END AS "town_order_id",
+ case when b."ad_grad" = 3 THEN b."ad_code" when c."ad_grad" = 3 THEN c."ad_code" END AS "county_ad_code",
+ case when b."ad_grad" = 3 THEN b."ad_name" when c."ad_grad" = 3 THEN C."ad_name" END AS "county_ad_name",
+ case when b."ad_grad" = 3 THEN b."order_id" when c."ad_grad" = 3 THEN C."order_id" END AS "county_order_id",
+ case when b."ad_grad" = 2 THEN b."ad_code" when c."ad_grad" = 2 THEN c."ad_code" ELSE d."ad_code" END AS
+ "city_ad_code",
+ case when b."ad_grad" = 2 THEN b."ad_name" when c."ad_grad" = 2 THEN c."ad_name" ELSE d."ad_name" END AS
+ "city_ad_name",
+ case when b."ad_grad" = 2 THEN b."order_id" when c."ad_grad" = 2 THEN c."order_id" ELSE d."order_id" END AS
+ "city_order_id",
+ case when b."ad_grad" = 1 THEN b."ad_code" when c."ad_grad" = 1 THEN c."ad_code" when d."ad_grad" = 1 THEN
+ d."ad_code" ELSE e."ad_code" END AS "province_ad_code",
+ case when b."ad_grad" = 1 THEN b."ad_name" when c."ad_grad" = 1 THEN c."ad_name" when d."ad_grad" = 1 THEN
+ d."ad_name" ELSE e."ad_name" END AS "province_ad_name",
+ case when b."ad_grad" = 1 THEN b."order_id" when c."ad_grad" = 1 THEN c."order_id" when d."ad_grad" = 1 THEN
+ d."order_id" ELSE e."order_id" END AS "province_order_id"
+ FROM sjzt_md."att_res_base" a
+ LEFT JOIN (
+
+
+ SELECT
+ distinct (dri."res_cd")
+ FROM
+ sjzt_dw."dim_res_info_a" dri
+ LEFT JOIN sjzt_ods."dsm_spg_spprmp" dss ON dri."oth_reg_cd" = dss."rscd"
+ where dss."rscd" is not null
+
+ ) f ON a."guid" = f."res_cd"
+ LEFT JOIN (
+
+
+ SELECT distinct(dri."res_cd")
+ from sjzt_dw."dim_res_info_a" dri
+ left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd"
+ where dss."rscd" is not null
+
+ ) G on a."guid" = g."res_cd"
+ LEFT JOIN (
+
+ SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb
+ where awcb."cd" != '00'
+
+ ) h ON a."guid" = h."res_cd"
+ LEFT JOIN sjzt_md."att_ad_base" b ON a."interior_ad_guid" = b."guid"
+ LEFT JOIN sjzt_md."att_ad_base" c ON b."p_ad_code" = c."guid"
+ LEFT JOIN sjzt_md."att_ad_base" d ON c."p_ad_code" = d."guid"
+ LEFT JOIN sjzt_md."att_ad_base" e ON d."p_ad_code" = e."guid"
+ where (f."res_cd" is not null OR g."res_cd" is not null OR h."res_cd" is not null)
+ ) baseRes
+ LEFT JOIN (
+
+
+ select res."res_cd",count(*) wyStation from(
+
+ SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb
+ where awcb."cd" != '00'
+
+ )res LEFT JOIN SJZT_MD."att_wy_cd_base" acb ON acb."res_cd" = res."res_cd"
+ where acb."cd_type" = 'wy' group by res."res_cd"
+
+
+ ) wy ON wy."res_cd" = baseRes."guid"
+ LEFT JOIN(
+
+ select res."res_cd",count(*) syStation from(
+ SELECT
+ distinct (dri."res_cd")
+ FROM
+ sjzt_dw."dim_res_info_a" dri
+ LEFT JOIN sjzt_ods."dsm_spg_spprmp" dss ON dri."oth_reg_cd" = dss."rscd"
+ where dss."rscd" is not null
+ )res LEFT JOIN SJZT_MD."att_cd_base" acb ON acb."res_cd" = res."res_cd"
+ where acb."cd_type" = 'sy' group by res."res_cd"
+
+ ) sy ON sy."res_cd" = baseRes."guid"
+ LEFT JOIN (
+
+
+ select res."res_cd",count(*) slStation from(
+
+
+ SELECT distinct(dri."res_cd")
+ from sjzt_dw."dim_res_info_a" dri
+ left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd"
+ where dss."rscd" is not null
+
+
+ )res LEFT JOIN SJZT_MD."att_cd_base" acb ON acb."res_cd" = res."res_cd"
+ where acb."cd_type" = 'sl' group by res."res_cd"
+
+
+ ) sl ON sl."res_cd" = baseRes."guid"
+ LEFT JOIN (
+ SELECT DAM_ID ,COUNT(*) errorNum FROM NKY_ALARM_GET
+ WHERE 1=1
+
+ <if test="dam.startTime != null and dam.startTime != ''">
+ AND DATE_FORMAT(time,'%Y-%m-%d') >= #{dam.startTime}
+ </if>
+ <if test="dam.endTime != null and dam.endTime !='' ">
+ AND DATE_FORMAT(time,'%Y-%m-%d') <= #{dam.endTime}
+ </if>
+
+
+ GROUP BY "DAM_ID"
+ ) alarm ON alarm.DAM_ID = baseRes."guid"
+
+ LEFT JOIN (
+ SELECT DAM_ID ,COUNT(*) errorCountNum
+ FROM (
+ SELECT DAM_ID, COUNT(*) FROM YWXT.NKY_ALARM_GET
+ WHERE 1=1
+ <if test="dam.startTime != null and dam.startTime != ''">
+ AND DATE_FORMAT(time,'%Y-%m-%d') >= #{dam.startTime}
+ </if>
+ <if test="dam.endTime != null and dam.endTime !='' ">
+ AND DATE_FORMAT(time,'%Y-%m-%d') <= #{dam.endTime}
+ </if>
+ GROUP BY DAM_ID,POINT_ID
+ ) baseCount GROUP BY DAM_ID) c1 ON c1.dam_id = baseRes."guid"
+
+ LEFT JOIN SJZT_MD."att_ad_base" town ON town."guid" = "town_ad_code"
+ LEFT JOIN SJZT_MD."att_ad_base" county ON county."guid" = town."p_ad_code"
+ LEFT JOIN SJZT_MD."att_ad_base" city ON city."guid" = county."p_ad_code"
+ LEFT JOIN SJZT_MD."att_ad_base" province ON province."guid" = city."p_ad_code"
+ WHERE 1=1
+ <!--这里加条件-->
+ <if test="dam.adCode != null and dam.adCode !='' ">
+ AND (province."ad_code" = #{dam.adCode} or city."ad_code" =#{dam.adCode}or county."ad_code" = #{dam.adCode}
+ )
+ </if>
+
+ <if test="dam.resName != null and dam.resName !='' ">
+ AND "name" LIKE CONCAT('%',#{dam.resName},'%')
+ </if>
+ ) info
+ </select>
+
+
+
+
<select id="alarmResCount" resultType="cn.gistack.sm.damSecurity.vo.DamStationStatistics">
SELECT
arb."center_long" AS longitude,
@@ -563,28 +968,24 @@
) errorNum
FROM
(
- SELECT "res_cd",COUNT(*) wyStation
- FROM
- (
- SELECT b."guid" as "res_cd",'wy' as "sys_resource"
- FROM
- (
- SELECT "station_code" from sjzt_md."att_mqtt_calc_data" group by "station_code"
- ) a LEFT JOIN SJZT_MD."att_res_base" b on substr(a."station_code",0,length(a."station_code")-2) = b."guid"
- where b."guid" is not null
- ) temp
- LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp."res_cd"
+
+
+ SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = awcb."res_cd"
LEFT JOIN SJZT_MD."att_ad_base" town ON town."guid" = arb."interior_ad_guid"
LEFT JOIN SJZT_MD."att_ad_base" county ON county."guid" = town."p_ad_code"
LEFT JOIN SJZT_MD."att_ad_base" city ON city."guid" = county."p_ad_code"
LEFT JOIN SJZT_MD."att_ad_base" province ON province."guid" = city."p_ad_code"
- WHERE 1= 1
+ where awcb."cd" != '00'
<if test=" adCode!= null and adCode !='' ">
AND (province."ad_code" =#{adCode} or city."ad_code" = #{adCode} or county."ad_code" = #{adCode})
</if>
- GROUP BY "res_cd"
+
+
)
UNION ALL
+
+
-- 渗压水库数
SELECT
COUNT(*) total,
@@ -609,26 +1010,29 @@
) errorNum
FROM
(
- SELECT "res_cd", COUNT(*) syStation
+
+
+ SELECT
+ distinct (dri."res_cd")
FROM
- (
- SELECT b."guid" as "res_cd",'sy' as "sys_resource"
- FROM sjzt_ods."dsm_spg_spprmp" a
- LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
- WHERE b."guid" is not null
- ) temp
- LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp."res_cd"
+ sjzt_dw."dim_res_info_a" dri
+ LEFT JOIN sjzt_ods."dsm_spg_spprmp" dss ON dri."oth_reg_cd" = dss."rscd"
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = dri."res_cd"
LEFT JOIN SJZT_MD."att_ad_base" town ON town."guid" = arb."interior_ad_guid"
LEFT JOIN SJZT_MD."att_ad_base" county ON county."guid" = town."p_ad_code"
LEFT JOIN SJZT_MD."att_ad_base" city ON city."guid" = county."p_ad_code"
LEFT JOIN SJZT_MD."att_ad_base" province ON province."guid" = city."p_ad_code"
- WHERE 1=1
+
+ where dss."rscd" is not null
<if test=" adCode!= null and adCode !='' ">
AND (province."ad_code" =#{adCode} or city."ad_code" = #{adCode} or county."ad_code" = #{adCode})
</if>
- GROUP BY "res_cd"
+
+
)
UNION ALL
+
+
-- 渗流水库数
SELECT
COUNT(*) total,
@@ -654,31 +1058,24 @@
) errorNum
FROM
(
- SELECT
- "res_cd",
- COUNT(*) slStation
- FROM (
- SELECT
- b."guid" as "res_cd",
- 'sl' as "sys_resource"
- FROM sjzt_ods."dsm_spg_spqn" a
- LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
- WHERE
- b."guid" IS NOT NULL
- AND a."mstm" >=now()-30 and a."mstm"<= now()
- group by b."guid"
- ) temp
- LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp."res_cd"
+
+ SELECT distinct(dri."res_cd")
+ from sjzt_dw."dim_res_info_a" dri
+ left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd"
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = dri."res_cd"
LEFT JOIN SJZT_MD."att_ad_base" town ON town."guid" = arb."interior_ad_guid"
LEFT JOIN SJZT_MD."att_ad_base" county ON county."guid" = town."p_ad_code"
LEFT JOIN SJZT_MD."att_ad_base" city ON city."guid" = county."p_ad_code"
LEFT JOIN SJZT_MD."att_ad_base" province ON province."guid" = city."p_ad_code"
+ where dss."rscd" is not null
<if test=" adCode!= null and adCode !='' ">
AND (province."ad_code" =#{adCode} or city."ad_code" = #{adCode} or county."ad_code" = #{adCode})
</if>
- GROUP BY "res_cd"
+
+
+
)
)
) base
@@ -862,14 +1259,17 @@
arb."center_long" AS longitude,
arb."center_lat" AS latitude
FROM(
+
+
SELECT
- b."guid" as "res_cd",
- 'sy' as "sys_resource"
- FROM sjzt_ods."dsm_spg_spprmp" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
- WHERE b."guid" IS NOT NULL
+ distinct (dri."res_cd")
+ FROM
+ sjzt_dw."dim_res_info_a" dri
+ LEFT JOIN sjzt_ods."dsm_spg_spprmp" dss ON dri."oth_reg_cd" = dss."rscd"
+ where dss."rscd" is not null
<if test="status ==2">
- AND b."guid" NOT IN (
+ AND dri."res_cd" NOT IN (
ifnull(
(
SELECT wm_concat(temp.resCd)
@@ -890,7 +1290,7 @@
</if>
<if test="status == 3">
- AND b."guid" IN (
+ AND dri."res_cd" IN (
ifnull(
(
SELECT wm_concat(temp.resCd)
@@ -929,16 +1329,14 @@
arb."center_long" AS longitude,
arb."center_lat" AS latitude
FROM (
- SELECT
- b."guid" as "res_cd",
- 'sl' as "sys_resource"
- FROM sjzt_ods."dsm_spg_spqn" a
- LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
- WHERE
- b."guid" IS NOT NULL
- AND a."mstm" >=now()-30 and a."mstm"<= now()
+
+ SELECT distinct(dri."res_cd")
+ from sjzt_dw."dim_res_info_a" dri
+ left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd"
+ where dss."rscd" is not null
+
<if test="status == 2">
- AND b."guid" NOT IN (
+ AND dri."res_cd" NOT IN (
ifnull(
(SELECT wm_concat(temp.resCd) FROM
(
@@ -960,7 +1358,7 @@
</if>
<if test="status == 3">
- AND b."guid" IN (
+ AND dri."res_cd" IN (
ifnull(
(SELECT wm_concat(temp.resCd) FROM
(
@@ -980,7 +1378,8 @@
)
)
</if>
- group by b."guid" ) baseRes
+
+ ) baseRes
LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = baseRes."res_cd"
LEFT JOIN SJZT_MD."att_ad_base" town ON town."guid" = arb."interior_ad_guid"
LEFT JOIN SJZT_MD."att_ad_base" county ON county."guid" = town."p_ad_code"
@@ -1001,18 +1400,13 @@
arb."center_long" AS longitude,
arb."center_lat" AS latitude
FROM (
- SELECT
- b."guid" as "res_cd",
- 'wy' as "sys_resource"
- FROM (
- SELECT "station_code" from sjzt_md."att_mqtt_calc_data" group by "station_code"
- ) a LEFT JOIN SJZT_MD."att_res_base" b on substr(a."station_code",0,length(a."station_code")-2) = b."guid"
- WHERE
- b."guid" IS NOT NULL
+
+ SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb
+ where awcb."cd" != '00'
<if test="status == 2">
- and b."guid" NOT IN (
+ and awcb."res_cd" NOT IN (
ifnull(
(
SELECT wm_concat(temp.resCd) FROM(
@@ -1035,7 +1429,7 @@
</if>
<if test="status == 3">
- and b."guid" IN (
+ and awcb."res_cd" IN (
ifnull(
(
SELECT wm_concat(temp.resCd) FROM(
@@ -1437,12 +1831,6 @@
</foreach>
</if>
ORDER BY NAG.TIME DESC
-
- </select>
-
-
-
- <select id="levelMonitorPie" resultType="cn.gistack.sm.damSecurity.vo.LevelPo">
</select>
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/IDamSecurityService.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/IDamSecurityService.java
index a6ac9d9..d2077df 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/IDamSecurityService.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/IDamSecurityService.java
@@ -67,4 +67,6 @@
List<DamStatisticsRegion> getDamInfoCountByRegion(DamStatisticsRegion damStatisticsRegion);
List<DamStatisticsRes> damResInfoCountOptimize(String adCode);
+
+ DamSecurityStatistics getSingleRegionCountOptimize(DamSecurityStatistics damSecurityStatistics);
}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/impl/DamSecurityServiceImpl.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/impl/DamSecurityServiceImpl.java
index 6ef5123..b285188 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/impl/DamSecurityServiceImpl.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/impl/DamSecurityServiceImpl.java
@@ -30,8 +30,14 @@
}
@Override
+ public DamSecurityStatistics getSingleRegionCountOptimize(DamSecurityStatistics damSecurityStatistics) {
+ return damSecurityMapper.getSingleRegionCountOptimize(damSecurityStatistics);
+ }
+
+ @Override
public List<DamSecurityStatistics> listRegionCount(DamSecurityStatistics damSecurityStatistics) {
- return damSecurityMapper.listRegionCount(damSecurityStatistics);
+// return damSecurityMapper.listRegionCount(damSecurityStatistics);
+ return damSecurityMapper.listRegionCountOptimize(damSecurityStatistics);
}
@Override
@@ -41,7 +47,8 @@
@Override
public IPage<DamStationStatistics> tableResCountPage(DamStationStatistics damStationStatistics, IPage<DamStationStatistics> page) {
- return page.setRecords(damSecurityMapper.tableResCountPage(damStationStatistics,page));
+// return page.setRecords(damSecurityMapper.tableResCountPage(damStationStatistics,page));
+ return page.setRecords(damSecurityMapper.tableResCountPageOptimize(damStationStatistics,page));
}
@Override
@@ -178,4 +185,5 @@
list.add(wy);
return list;
}
+
}
--
Gitblit v1.9.3