From 4d1176b8a24b388843a609fcb56c8e26fefd0080 Mon Sep 17 00:00:00 2001
From: guoshilong <123456>
Date: Sat, 05 Aug 2023 16:25:27 +0800
Subject: [PATCH] 大坝安全监测统计
---
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml | 189 +++++++++++++++++++++++++++++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.java | 16 ++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/vo/DamSecurityStatistics.java | 32 +++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/controller/DamSecurityController.java | 54 +++++++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/impl/DamSecurityServiceImpl.java | 26 ++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/IDamSecurityService.java | 21 +++
6 files changed, 338 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
new file mode 100644
index 0000000..ca0ebd5
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/controller/DamSecurityController.java
@@ -0,0 +1,54 @@
+package cn.gistack.sm.damSecurity.controller;
+
+import cn.gistack.sm.damSecurity.service.IDamSecurityService;
+import cn.gistack.sm.damSecurity.vo.DamSecurityStatistics;
+import cn.gistack.sm.exam.entity.ExamPlan;
+import com.baomidou.mybatisplus.core.metadata.IPage;
+import io.swagger.annotations.Api;
+import io.swagger.annotations.ApiOperation;
+import lombok.AllArgsConstructor;
+import lombok.extern.slf4j.Slf4j;
+import org.springblade.core.boot.ctrl.BladeController;
+import org.springblade.core.mp.support.Condition;
+import org.springblade.core.mp.support.Query;
+import org.springblade.core.tool.api.R;
+import org.springframework.web.bind.annotation.GetMapping;
+import org.springframework.web.bind.annotation.RequestMapping;
+import org.springframework.web.bind.annotation.RestController;
+
+import java.util.List;
+
+@Slf4j
+@Api(tags = "大坝安全统计")
+@RestController
+@RequestMapping("/damSecurity/damSecurity")
+@AllArgsConstructor
+public class DamSecurityController extends BladeController {
+
+ private final IDamSecurityService damSecurityService;
+
+ /**
+ * 大坝安全监测状况统计
+ * @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 = "/listRegionCount")
+ public R listRegionCount(DamSecurityStatistics damSecurityStatistics) {
+ List<DamSecurityStatistics> list = damSecurityService.listRegionCount(damSecurityStatistics);
+ return R.data(list);
+ }
+
+}
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
new file mode 100644
index 0000000..944fcc7
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.java
@@ -0,0 +1,16 @@
+package cn.gistack.sm.damSecurity.mapper;
+
+import cn.gistack.sm.damSecurity.vo.DamSecurityStatistics;
+import org.apache.ibatis.annotations.Param;
+import org.springframework.stereotype.Repository;
+
+import java.util.List;
+
+@Repository
+public interface DamSecurityMapper {
+
+
+ DamSecurityStatistics getSingleRegionCount(@Param("dam") DamSecurityStatistics damSecurityStatistics);
+
+ List<DamSecurityStatistics> listRegionCount(@Param("dam") DamSecurityStatistics damSecurityStatistics);
+}
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
new file mode 100644
index 0000000..64c2387
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml
@@ -0,0 +1,189 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
+<mapper namespace="cn.gistack.sm.damSecurity.mapper.DamSecurityMapper">
+
+ <select id="getSingleRegionCount" 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 "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"
+ 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"
+ 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>
+ )) 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">
+
+ 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
+ "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"
+ 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"
+ 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" )
+ )) 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>
+</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
new file mode 100644
index 0000000..2a73bed
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/IDamSecurityService.java
@@ -0,0 +1,21 @@
+package cn.gistack.sm.damSecurity.service;
+
+import cn.gistack.sm.damSecurity.vo.DamSecurityStatistics;
+
+import java.util.List;
+
+public interface IDamSecurityService {
+ /**
+ * 查询单行政区下的统计数据
+ * @param damSecurityStatistics
+ * @return
+ */
+ DamSecurityStatistics getSingleRegionCount(DamSecurityStatistics damSecurityStatistics);
+
+ /**
+ * 查询该行政区下的统计数据
+ * @param damSecurityStatistics
+ * @return
+ */
+ List<DamSecurityStatistics> listRegionCount(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
new file mode 100644
index 0000000..baf182a
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/impl/DamSecurityServiceImpl.java
@@ -0,0 +1,26 @@
+package cn.gistack.sm.damSecurity.service.impl;
+
+import cn.gistack.sm.damSecurity.mapper.DamSecurityMapper;
+import cn.gistack.sm.damSecurity.service.IDamSecurityService;
+import cn.gistack.sm.damSecurity.vo.DamSecurityStatistics;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+
+import java.util.List;
+
+@Service
+public class DamSecurityServiceImpl implements IDamSecurityService {
+
+ @Autowired
+ private DamSecurityMapper damSecurityMapper;
+
+ @Override
+ public DamSecurityStatistics getSingleRegionCount(DamSecurityStatistics damSecurityStatistics) {
+ return damSecurityMapper.getSingleRegionCount(damSecurityStatistics);
+ }
+
+ @Override
+ public List<DamSecurityStatistics> listRegionCount(DamSecurityStatistics damSecurityStatistics) {
+ return damSecurityMapper.listRegionCount(damSecurityStatistics);
+ }
+}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/vo/DamSecurityStatistics.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/vo/DamSecurityStatistics.java
new file mode 100644
index 0000000..1c1be90
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/vo/DamSecurityStatistics.java
@@ -0,0 +1,32 @@
+package cn.gistack.sm.damSecurity.vo;
+
+import lombok.Data;
+
+@Data
+public class DamSecurityStatistics {
+
+ /**
+ * 行政区划编码
+ */
+ private String adCode;
+
+ /**
+ * 行政区划名
+ */
+ private String adName;
+
+ /**
+ * 正常数量
+ */
+ private Integer normalNum;
+
+ /**
+ * 异常数量
+ */
+ private Integer errorNum;
+
+ /**
+ * 总数
+ */
+ private Integer allNum;
+}
--
Gitblit v1.9.3