From 6b8971db2990a8bcb4c67ba0d07df2368a00a45e Mon Sep 17 00:00:00 2001
From: guoshilong <123456>
Date: Thu, 28 Dec 2023 19:57:42 +0800
Subject: [PATCH] 大坝一张图接口
---
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml | 1837 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.java | 14
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/vo/DamStatisticsRegion.java | 28
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/controller/DamSecurityController.java | 6
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/impl/DamSecurityServiceImpl.java | 31
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/IDamSecurityService.java | 2
6 files changed, 1,918 insertions(+), 0 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 80404d6..b4d4684 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
@@ -175,6 +175,12 @@
return R.data(list);
}
+ @GetMapping(value = "/getDamInfoCountByRegion")
+ public R getDamInfoCountByRegion(DamStatisticsRegion damStatisticsRegion){
+ List<DamStatisticsRegion> list = damSecurityService.getDamInfoCountByRegion(damStatisticsRegion);
+ return R.data(list);
+ }
+
/**
* 预警状态修改
* @param alarmGet
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 5852713..a60d0bc 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
@@ -45,4 +45,18 @@
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);
+
+ List<DamStatisticsRegion> getDamWyRegion(@Param("dam") DamStatisticsRegion damStatisticsRegion);
+
+ List<DamStatisticsRegion> getErrorDamRegion(@Param("dam") DamStatisticsRegion damStatisticsRegion);
+
+ List<DamStatisticsRegion> getDamSyRegionNormal(@Param("dam") DamStatisticsRegion damStatisticsRegion);
+
+ List<DamStatisticsRegion> getDamSlRegionNormal(@Param("dam") DamStatisticsRegion damStatisticsRegion);
+
+ List<DamStatisticsRegion> getDamWyRegionNormal(@Param("dam") DamStatisticsRegion damStatisticsRegion);
}
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 a20e7b8..ae244b1 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
@@ -1447,4 +1447,1841 @@
</select>
+
+ <select id="getDamSyRegion" resultType="cn.gistack.sm.damSecurity.vo.DamStatisticsRegion">
+
+ <if test="dam.adCode == '420000000000'">
+ SELECT
+ tmp.*,
+ 'sy' as type,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.provinceName as adName,
+ r.provinceCode as adCode,
+ count(*) as total,
+ '1' AS adGrad
+ FROM (
+ SELECT
+ resList."res_cd",
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+
+ 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
+ ) temp
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp."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
+ <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>
+ GROUP BY "res_cd"
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList."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" )r
+ group by r.provinceName,r.provinceCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+ </if>
+
+ SELECT
+ tmp.*,
+ 'sy' as type,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.cityName as adName,
+ r.cityCode as adCode,
+ count(*) as total,
+ '2' AS adGrad
+ FROM (
+ SELECT
+ resList."res_cd",
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+
+ 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
+ ) temp
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp."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
+ <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>
+ GROUP BY "res_cd"
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList."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" )r
+ group by r.cityName,r.cityCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+
+ SELECT
+ tmp.*,
+ 'sy' as type,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.countyCode as adCode,
+ r.countyName as adName,
+ count(*) as total,
+ '3' AS adGrad
+ FROM (
+ SELECT
+ resList."res_cd",
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+
+ 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
+ ) temp
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp."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
+ <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>
+ GROUP BY "res_cd"
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList."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" )r
+ group by r.countyCode,r.countyName)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+
+ </select>
+ <select id="getDamSlRegion" resultType="cn.gistack.sm.damSecurity.vo.DamStatisticsRegion">
+ <if test="dam.adCode == '420000000000'">
+ SELECT
+ tmp.*,
+ 'sl' as type,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.provinceName as adName,
+ r.provinceCode as adCode,
+ count(*) as total,
+ '1' AS adGrad
+ FROM (
+ SELECT
+ resList."res_cd",
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ 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"
+ 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"
+ <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>
+ GROUP BY "res_cd"
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList."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" )r
+ group by r.provinceName,r.provinceCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+ </if>
+
+ SELECT
+ tmp.*,
+ 'sl' as type,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.cityName as adName,
+ r.cityCode as adCode,
+ count(*) as total,
+ '2' AS adGrad
+ FROM (
+ SELECT
+ resList."res_cd",
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ 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"
+ 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"
+ <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>
+ GROUP BY "res_cd"
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList."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" )r
+ group by r.cityName,r.cityCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+
+ SELECT
+ tmp.*,
+ 'sl' as type,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.countyCode as adCode,
+ r.countyName as adName,
+ count(*) as total,
+ '3' AS adGrad
+ FROM (
+ SELECT
+ resList."res_cd",
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ 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"
+ 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"
+ <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>
+ GROUP BY "res_cd"
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList."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" )r
+ group by r.countyCode,r.countyName)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+ </select>
+
+ <select id="getDamWyRegion" resultType="cn.gistack.sm.damSecurity.vo.DamStatisticsRegion">
+ <if test="dam.adCode == '420000000000'">
+ SELECT
+ tmp.*,
+ 'wy' as type,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.provinceName as adName,
+ r.provinceCode as adCode,
+ count(*) as total,
+ '1' AS adGrad
+ FROM (
+ SELECT
+ resList."res_cd",
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ 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"
+ 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
+ <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>
+ GROUP BY "res_cd"
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList."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" )r
+ group by r.provinceName,r.provinceCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+ </if>
+
+ SELECT
+ tmp.*,
+ 'wy' as type,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.cityName as adName,
+ r.cityCode as adCode,
+ count(*) as total,
+ '2' AS adGrad
+ FROM (
+ SELECT
+ resList."res_cd",
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ 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"
+ 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
+ <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>
+ GROUP BY "res_cd"
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList."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" )r
+ group by r.cityName,r.cityCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+
+ SELECT
+ tmp.*,
+ 'wy' as type,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.countyCode as adCode,
+ r.countyName as adName,
+ count(*) as total,
+ '3' AS adGrad
+ FROM (
+ SELECT
+ resList."res_cd",
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ 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"
+ 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
+ <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>
+ GROUP BY "res_cd"
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList."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" )r
+ group by r.countyCode,r.countyName)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+ </select>
+
+
+
+ <select id="getErrorDamRegion" resultType="cn.gistack.sm.damSecurity.vo.DamStatisticsRegion">
+
+ <if test="dam.adCode == '420000000000'">
+ SELECT
+ tmp.*,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.provinceName as adName,
+ r.provinceCode as adCode,
+ count(*) as total,
+ '1' AS adGrad
+ FROM (
+ SELECT
+ resList.resCd,
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+ SELECT DAM_ID as resCd FROM YWXT.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" 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 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
+ <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.type =='sy' ">
+ AND NAG.type = '1'
+ </if>
+ <if test="dam.type =='sl' ">
+ AND NAG.type = '2'
+ </if>
+ <if test="dam.type =='wy' ">
+ AND (NAG.type = '3' or NAG.type ='4' or NAG.type ='5' )
+ </if>
+
+ GROUP BY DAM_ID
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
+ 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" )r
+ group by r.provinceName,r.provinceCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+ </if>
+
+ SELECT
+ tmp.*,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.cityName as adName,
+ r.cityCode as adCode,
+ count(*) as total,
+ '2' AS adGrad
+ FROM (
+ SELECT
+ resList.resCd,
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+
+ SELECT DAM_ID as resCd FROM YWXT.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" 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 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
+ <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.type =='sy' ">
+ AND NAG.type = '1'
+ </if>
+ <if test="dam.type =='sl' ">
+ AND NAG.type = '2'
+ </if>
+ <if test="dam.type =='wy' ">
+ AND (NAG.type = '3' or NAG.type ='4' or NAG.type ='5' )
+ </if>
+
+ GROUP BY DAM_ID
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
+ 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" )r
+ group by r.cityName,r.cityCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+
+ SELECT
+ tmp.*,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.countyCode as adCode,
+ r.countyName as adName,
+ count(*) as total,
+ '3' AS adGrad
+ FROM (
+ SELECT
+ resList.resCd,
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+ SELECT DAM_ID as resCd FROM YWXT.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" 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 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
+ <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.type =='sy' ">
+ AND NAG.type = '1'
+ </if>
+ <if test="dam.type =='sl' ">
+ AND NAG.type = '2'
+ </if>
+ <if test="dam.type =='wy' ">
+ AND (NAG.type = '3' or NAG.type ='4' or NAG.type ='5' )
+ </if>
+
+ GROUP BY DAM_ID
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
+ 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" )r
+ group by r.countyCode,r.countyName)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+
+
+ </select>
+ <select id="getDamSyRegionNormal" resultType="cn.gistack.sm.damSecurity.vo.DamStatisticsRegion">
+
+ <if test="dam.adCode == '420000000000'">
+ SELECT
+ tmp.*,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.provinceName as adName,
+ r.provinceCode as adCode,
+ count(*) as total,
+ '1' AS adGrad
+ FROM (
+ SELECT
+ resList.resCd,
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+ SELECT normalList."res_cd" as resCd FROM (
+ SELECT allList."res_cd",errorList."name" FROM (
+
+
+
+
+ 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
+ ) temp
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp."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
+ <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>
+ GROUP BY "res_cd"
+
+
+
+ ) allList LEFT JOIN
+
+ (
+ SELECT DAM_ID as resCd,arb."name" FROM YWXT.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" 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 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
+ <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.type =='sy' ">
+ AND NAG.type = '1'
+ </if>
+ <if test="dam.type =='sl' ">
+ AND NAG.type = '2'
+ </if>
+ <if test="dam.type =='wy' ">
+ AND (NAG.type = '3' or NAG.type ='4' or NAG.type ='5' )
+ </if>
+
+ GROUP BY DAM_ID,arb."name"
+
+
+ ) errorList ON allList."res_cd" = errorList.resCd
+ )normalList where normalList."name" is null
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
+ 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" )r
+ group by r.provinceName,r.provinceCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+ </if>
+
+ SELECT
+ tmp.*,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.cityName as adName,
+ r.cityCode as adCode,
+ count(*) as total,
+ '2' AS adGrad
+ FROM (
+ SELECT
+ resList.resCd,
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+ SELECT normalList."res_cd" as resCd FROM (
+ SELECT allList."res_cd",errorList."name" FROM (
+ 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
+ ) temp
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp."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
+ <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>
+ GROUP BY "res_cd"
+ ) allList LEFT JOIN
+
+ (
+ SELECT DAM_ID as resCd,arb."name" FROM YWXT.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" 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 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
+ <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.type =='sy' ">
+ AND NAG.type = '1'
+ </if>
+ <if test="dam.type =='sl' ">
+ AND NAG.type = '2'
+ </if>
+ <if test="dam.type =='wy' ">
+ AND (NAG.type = '3' or NAG.type ='4' or NAG.type ='5' )
+ </if>
+
+ GROUP BY DAM_ID,arb."name"
+
+
+ ) errorList ON allList."res_cd" = errorList.resCd
+ )normalList where normalList."name" is null
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
+ 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" )r
+ group by r.cityName,r.cityCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+
+ SELECT
+ tmp.*,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.countyCode as adCode,
+ r.countyName as adName,
+ count(*) as total,
+ '3' AS adGrad
+ FROM (
+ SELECT
+ resList.resCd,
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+ SELECT normalList."res_cd" as resCd FROM (
+ SELECT allList."res_cd",errorList."name" FROM (
+ 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
+ ) temp
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp."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
+ <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>
+ GROUP BY "res_cd"
+ ) allList LEFT JOIN
+
+ (
+ SELECT DAM_ID as resCd,arb."name" FROM YWXT.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" 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 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
+ <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.type =='sy' ">
+ AND NAG.type = '1'
+ </if>
+ <if test="dam.type =='sl' ">
+ AND NAG.type = '2'
+ </if>
+ <if test="dam.type =='wy' ">
+ AND (NAG.type = '3' or NAG.type ='4' or NAG.type ='5' )
+ </if>
+
+ GROUP BY DAM_ID,arb."name"
+
+
+ ) errorList ON allList."res_cd" = errorList.resCd
+ )normalList where normalList."name" is null
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
+ 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" )r
+ group by r.countyCode,r.countyName)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ </select>
+ <select id="getDamSlRegionNormal" resultType="cn.gistack.sm.damSecurity.vo.DamStatisticsRegion">
+
+
+
+
+
+
+
+
+ <if test="dam.adCode == '420000000000'">
+ SELECT
+ tmp.*,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.provinceName as adName,
+ r.provinceCode as adCode,
+ count(*) as total,
+ '1' AS adGrad
+ FROM (
+ SELECT
+ resList.resCd,
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+ SELECT normalList."res_cd" as resCd FROM (
+ SELECT allList."res_cd",errorList."name" 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"
+ 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"
+ <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>
+ GROUP BY "res_cd"
+
+
+
+ ) allList LEFT JOIN
+
+ (
+ SELECT DAM_ID as resCd,arb."name" FROM YWXT.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" 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 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
+ <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.type =='sy' ">
+ AND NAG.type = '1'
+ </if>
+ <if test="dam.type =='sl' ">
+ AND NAG.type = '2'
+ </if>
+ <if test="dam.type =='wy' ">
+ AND (NAG.type = '3' or NAG.type ='4' or NAG.type ='5' )
+ </if>
+
+ GROUP BY DAM_ID,arb."name"
+
+
+ ) errorList ON allList."res_cd" = errorList.resCd
+ )normalList where normalList."name" is null
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
+ 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" )r
+ group by r.provinceName,r.provinceCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+ </if>
+
+ SELECT
+ tmp.*,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.cityName as adName,
+ r.cityCode as adCode,
+ count(*) as total,
+ '2' AS adGrad
+ FROM (
+ SELECT
+ resList.resCd,
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+ SELECT normalList."res_cd" as resCd FROM (
+ SELECT allList."res_cd",errorList."name" 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"
+ 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"
+ <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>
+ GROUP BY "res_cd"
+
+
+
+ ) allList LEFT JOIN
+
+ (
+ SELECT DAM_ID as resCd,arb."name" FROM YWXT.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" 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 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
+ <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.type =='sy' ">
+ AND NAG.type = '1'
+ </if>
+ <if test="dam.type =='sl' ">
+ AND NAG.type = '2'
+ </if>
+ <if test="dam.type =='wy' ">
+ AND (NAG.type = '3' or NAG.type ='4' or NAG.type ='5' )
+ </if>
+
+ GROUP BY DAM_ID,arb."name"
+
+
+ ) errorList ON allList."res_cd" = errorList.resCd
+ )normalList where normalList."name" is null
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
+ 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" )r
+ group by r.cityName,r.cityCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+
+ SELECT
+ tmp.*,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.countyCode as adCode,
+ r.countyName as adName,
+ count(*) as total,
+ '3' AS adGrad
+ FROM (
+ SELECT
+ resList.resCd,
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+ SELECT normalList."res_cd" as resCd FROM (
+ SELECT allList."res_cd",errorList."name" 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"
+ 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"
+ <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>
+ GROUP BY "res_cd"
+
+
+
+ ) allList LEFT JOIN
+
+ (
+ SELECT DAM_ID as resCd,arb."name" FROM YWXT.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" 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 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
+ <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.type =='sy' ">
+ AND NAG.type = '1'
+ </if>
+ <if test="dam.type =='sl' ">
+ AND NAG.type = '2'
+ </if>
+ <if test="dam.type =='wy' ">
+ AND (NAG.type = '3' or NAG.type ='4' or NAG.type ='5' )
+ </if>
+
+ GROUP BY DAM_ID,arb."name"
+
+
+ ) errorList ON allList."res_cd" = errorList.resCd
+ )normalList where normalList."name" is null
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
+ 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" )r
+ group by r.countyCode,r.countyName)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ </select>
+ <select id="getDamWyRegionNormal" resultType="cn.gistack.sm.damSecurity.vo.DamStatisticsRegion">
+
+ <if test="dam.adCode == '420000000000'">
+ SELECT
+ tmp.*,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.provinceName as adName,
+ r.provinceCode as adCode,
+ count(*) as total,
+ '1' AS adGrad
+ FROM (
+ SELECT
+ resList.resCd,
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+ SELECT normalList."res_cd" as resCd FROM (
+ SELECT allList."res_cd",errorList."name" 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"
+ 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
+ <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>
+ GROUP BY "res_cd"
+
+
+
+ ) allList LEFT JOIN
+
+ (
+ SELECT DAM_ID as resCd,arb."name" FROM YWXT.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" 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 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
+ <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.type =='sy' ">
+ AND NAG.type = '1'
+ </if>
+ <if test="dam.type =='sl' ">
+ AND NAG.type = '2'
+ </if>
+ <if test="dam.type =='wy' ">
+ AND (NAG.type = '3' or NAG.type ='4' or NAG.type ='5' )
+ </if>
+
+ GROUP BY DAM_ID,arb."name"
+
+
+ ) errorList ON allList."res_cd" = errorList.resCd
+ )normalList where normalList."name" is null
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
+ 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" )r
+ group by r.provinceName,r.provinceCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+ </if>
+
+ SELECT
+ tmp.*,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.cityName as adName,
+ r.cityCode as adCode,
+ count(*) as total,
+ '2' AS adGrad
+ FROM (
+ SELECT
+ resList.resCd,
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+ SELECT normalList."res_cd" as resCd FROM (
+ SELECT allList."res_cd",errorList."name" 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"
+ 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
+ <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>
+ GROUP BY "res_cd"
+
+
+
+
+ ) allList LEFT JOIN
+
+ (
+ SELECT DAM_ID as resCd,arb."name" FROM YWXT.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" 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 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
+ <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.type =='sy' ">
+ AND NAG.type = '1'
+ </if>
+ <if test="dam.type =='sl' ">
+ AND NAG.type = '2'
+ </if>
+ <if test="dam.type =='wy' ">
+ AND (NAG.type = '3' or NAG.type ='4' or NAG.type ='5' )
+ </if>
+
+ GROUP BY DAM_ID,arb."name"
+
+
+ ) errorList ON allList."res_cd" = errorList.resCd
+ )normalList where normalList."name" is null
+
+
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
+ 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" )r
+ group by r.cityName,r.cityCode)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+ UNION ALL
+
+ SELECT
+ tmp.*,
+ parent."p_ad_code" pAdCode,
+ parent."p_ad_name" pAdName
+ FROM(
+ SELECT
+ r.countyCode as adCode,
+ r.countyName as adName,
+ count(*) as total,
+ '3' AS adGrad
+ FROM (
+ SELECT
+ resList.resCd,
+ case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
+ case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
+ case when town."ad_grad" = 3 THEN town."ad_code"
+ when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
+ case when town."ad_grad" = 3 THEN town."ad_name"
+ when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
+ case when town."ad_grad" = 2 THEN town."ad_code"
+ when county."ad_grad" = 2 THEN county."ad_code"
+ ELSE city."ad_code" END AS cityCode,
+ case when town."ad_grad" = 2 THEN town."ad_name"
+ when county."ad_grad" = 2 THEN county."ad_name"
+ ELSE city."ad_name" END AS cityName,
+ case when town."ad_grad" = 1 THEN town."ad_code"
+ when county."ad_grad" = 1 THEN county."ad_code"
+ when city."ad_grad" = 1 THEN city."ad_code"
+ ELSE province."ad_code" END AS provinceCode,
+ case when town."ad_grad" = 1 THEN town."ad_name"
+ when county."ad_grad" = 1 THEN county."ad_name"
+ when city."ad_grad" = 1 THEN city."ad_name"
+ ELSE province."ad_name" END AS provinceName
+ FROM(
+
+ SELECT normalList."res_cd" as resCd FROM (
+ SELECT allList."res_cd",errorList."name" 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"
+ 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
+ <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>
+ GROUP BY "res_cd"
+
+
+
+ ) allList LEFT JOIN
+
+ (
+ SELECT DAM_ID as resCd,arb."name" FROM YWXT.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" 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 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
+ <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.type =='sy' ">
+ AND NAG.type = '1'
+ </if>
+ <if test="dam.type =='sl' ">
+ AND NAG.type = '2'
+ </if>
+ <if test="dam.type =='wy' ">
+ AND (NAG.type = '3' or NAG.type ='4' or NAG.type ='5' )
+ </if>
+
+ GROUP BY DAM_ID,arb."name"
+
+
+ ) errorList ON allList."res_cd" = errorList.resCd
+ )normalList where normalList."name" is null
+ ) resList
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
+ 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" )r
+ group by r.countyCode,r.countyName)TMP
+ LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
+
+
+
+
+ </select>
+
+
</mapper>
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 f513aaa..e4e4e95 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
@@ -63,4 +63,6 @@
IPage<AlarmGetVO> pageMonitor(AlarmGetVO alarmGet, IPage<AlarmGetVO> page);
List<LevelPo> levelMonitorPie(DamMonitor damMonitor);
+
+ List<DamStatisticsRegion> getDamInfoCountByRegion(DamStatisticsRegion damStatisticsRegion);
}
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 ddd2690..207d34a 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
@@ -4,7 +4,9 @@
import cn.gistack.sm.damSecurity.mapper.DamSecurityMapper;
import cn.gistack.sm.damSecurity.service.IDamSecurityService;
import cn.gistack.sm.damSecurity.vo.*;
+import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
+import com.mysql.cj.protocol.x.ReusableInputStream;
import org.springblade.core.tool.utils.ObjectUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@@ -133,4 +135,33 @@
return damSecurityMapper.getRegionList(damMonitor);
}
+ @Override
+ public List<DamStatisticsRegion> getDamInfoCountByRegion(DamStatisticsRegion damStatisticsRegion) {
+ List<DamStatisticsRegion> list = new ArrayList<>();
+
+ if (damStatisticsRegion.getStatus().equals("total")){
+ //点击总数
+ if (damStatisticsRegion.getType().equals("sy")){
+ list = damSecurityMapper.getDamSyRegion(damStatisticsRegion);
+ }else if (damStatisticsRegion.getType().equals("sl")){
+ list = damSecurityMapper.getDamSlRegion(damStatisticsRegion);
+ }else if (damStatisticsRegion.getType().equals("wy")){
+ list = damSecurityMapper.getDamWyRegion(damStatisticsRegion);
+ }
+ }else if (damStatisticsRegion.getStatus().equals("error")){
+ //点异常
+ list = damSecurityMapper.getErrorDamRegion(damStatisticsRegion);
+ }else if (damStatisticsRegion.getStatus().equals("normal")){
+ //点正常
+ if (damStatisticsRegion.getType().equals("sy")){
+ list = damSecurityMapper.getDamSyRegionNormal(damStatisticsRegion);
+ }else if (damStatisticsRegion.getType().equals("sl")){
+ list = damSecurityMapper.getDamSlRegionNormal(damStatisticsRegion);
+ }else if (damStatisticsRegion.getType().equals("wy")){
+ list = damSecurityMapper.getDamWyRegionNormal(damStatisticsRegion);
+ }
+ }
+
+ return list;
+ }
}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/vo/DamStatisticsRegion.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/vo/DamStatisticsRegion.java
new file mode 100644
index 0000000..c21ae1b
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/vo/DamStatisticsRegion.java
@@ -0,0 +1,28 @@
+package cn.gistack.sm.damSecurity.vo;
+
+import lombok.Data;
+
+/**
+ * 根据行政区划统计
+ */
+@Data
+public class DamStatisticsRegion {
+
+ private String adCode;
+
+ private String adName;
+
+ private String pAdCode;
+
+ private String pAdName;
+
+ private Integer total;
+
+ //类型( sy、sl、wy )
+ private String type;
+
+
+ //状态(1、正常，2、异常)
+ private String status;
+
+}
--
Gitblit v1.9.3