From 56f9e4bc47c53efcfab40ca75adf8c13ca334ae2 Mon Sep 17 00:00:00 2001
From: yxm <zhp1521624>
Date: Thu, 04 Jul 2024 11:53:26 +0800
Subject: [PATCH] 堤防水库危害统计v20240704
---
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/service/ITbResAndDykeAllService.java | 14 ++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResAndDykeAllMapper.xml | 136 +++++++++++++++++++++++++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/service/impl/TbResAndDykeAllServiceImpl.java | 28 +++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResAndDykeAllMapper.java | 13 ++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/vo/ResAndDykeAll.java | 55 +++++++++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/controller/TbResAndDykeController.java | 34 ++++++
6 files changed, 280 insertions(+), 0 deletions(-)
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/controller/TbResAndDykeController.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/controller/TbResAndDykeController.java
new file mode 100644
index 0000000..d74b8ea
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/controller/TbResAndDykeController.java
@@ -0,0 +1,34 @@
+package cn.gistack.sm.sjztmd.controller;
+
+
+import cn.gistack.sm.sjztmd.service.ITbResAndDykeAllService;
+import cn.gistack.sm.sjztmd.vo.ResAndDykeAll;
+import io.swagger.annotations.Api;
+import lombok.AllArgsConstructor;
+import lombok.extern.slf4j.Slf4j;
+import org.springblade.core.boot.ctrl.BladeController;
+import org.springblade.core.tool.api.R;
+import org.springframework.beans.factory.annotation.Autowired;
+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("/sjztmd/resAndDkyeAll")
+@AllArgsConstructor
+public class TbResAndDykeController extends BladeController {
+
+ @Autowired
+ private ITbResAndDykeAllService tbProjectAllService;
+
+ @GetMapping("/getStatistics")
+ public R getStatistics(String code){
+ List<ResAndDykeAll> list = tbProjectAllService.getStatisticsTable(code);
+ return R.data(list);
+ }
+
+}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResAndDykeAllMapper.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResAndDykeAllMapper.java
new file mode 100644
index 0000000..93a1f3f
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResAndDykeAllMapper.java
@@ -0,0 +1,13 @@
+package cn.gistack.sm.sjztmd.mapper;
+
+import cn.gistack.sm.sjztmd.entity.TbProject;
+import cn.gistack.sm.sjztmd.vo.ResAndDykeAll;
+import com.baomidou.mybatisplus.core.mapper.BaseMapper;
+import org.apache.ibatis.annotations.Mapper;
+
+import java.util.List;
+
+@Mapper
+public interface TbResAndDykeAllMapper extends BaseMapper<TbProject> {
+ List<ResAndDykeAll> getStatisticsTable(String code);
+}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResAndDykeAllMapper.xml b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResAndDykeAllMapper.xml
new file mode 100644
index 0000000..d13daa4
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResAndDykeAllMapper.xml
@@ -0,0 +1,136 @@
+<?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.sjztmd.mapper.TbResAndDykeAllMapper">
+
+ <select id="getStatisticsTable" resultType="cn.gistack.sm.sjztmd.vo.ResAndDykeAll">
+ SELECT
+ a.*,
+ att."p_ad_code" pcode,
+ att."p_ad_name" pname,
+ att."ad_grad" adGrad,
+ ROUND(IFNULL(b.lengthSum, 0) / 1000, 3) AS lengthSum,
+ ROUND(IFNULL(b.levelOneLengthOne, 0) / 1000, 3) AS levelOneLengthOne,
+ ROUND(IFNULL(b.levelOneLengthTwo, 0) / 1000, 3) AS levelOneLengthTwo,
+ ROUND(IFNULL(b.levelOneLengthThree, 0) / 1000, 3) AS levelOneLengthThree,
+ ROUND(IFNULL(b.levelTwoLengthOne, 0) / 1000, 3) AS levelTwoLengthOne,
+ ROUND(IFNULL(b.levelTwoLengthTwo, 0) / 1000, 3) AS levelTwoLengthTwo,
+ ROUND(IFNULL(b.levelTwoLengthThree, 0) / 1000, 3) AS levelTwoLengthThree,
+ ROUND(IFNULL(b.levelThreeLengthOne, 0) / 1000, 3) AS levelThreeLengthOne,
+ ROUND(IFNULL(b.levelThreeLengthTwo, 0) / 1000, 3) AS levelThreeLengthTwo,
+ ROUND(IFNULL(b.levelThreeLengthThree, 0) / 1000, 3) AS levelThreeLengthThree,
+ ROUND(IFNULL(b.levelFourLengthOne, 0) / 1000, 3) AS levelFourLengthOne,
+ ROUND(IFNULL(b.levelFourLengthTwo, 0) / 1000, 3) AS levelFourLengthTwo,
+ ROUND(IFNULL(b.levelFourLengthThree, 0) / 1000, 3) AS levelFourLengthThree,
+ ROUND(IFNULL(b.levelFiveLengthOne, 0) / 1000, 3) AS levelFiveLengthOne,
+ ROUND(IFNULL(b.levelFiveLengthTwo, 0) / 1000, 3) AS levelFiveLengthTwo,
+ ROUND(IFNULL(b.levelFiveLengthThree, 0) / 1000, 3) AS levelFiveLengthThree
+ FROM
+ (
+ SELECT
+ a."ad_code" CODE,
+ a."ad_name" NAME,
+ count( b."danger_level" ) AS resNum,
+ sum( CASE WHEN b."eng_scal" = '大（1）型' AND b."danger_level" = '1' THEN 1 ELSE 0 END ) AS bigOneNum,
+ sum( CASE WHEN b."eng_scal" = '大（1）型' AND b."danger_level" = '2' THEN 1 ELSE 0 END ) AS bigTwoNum,
+ sum( CASE WHEN b."eng_scal" = '大（1）型' AND b."danger_level" = '3' THEN 1 ELSE 0 END ) AS bigThreeNum,
+ sum( CASE WHEN b."eng_scal" = '大（2）型' AND b."danger_level" = '1' THEN 1 ELSE 0 END ) AS bigtOneNum,
+ sum( CASE WHEN b."eng_scal" = '大（2）型' AND b."danger_level" = '2' THEN 1 ELSE 0 END ) AS bigtTwoNum,
+ sum( CASE WHEN b."eng_scal" = '大（2）型' AND b."danger_level" = '3' THEN 1 ELSE 0 END ) AS bigtThreeNum,
+ sum( CASE WHEN b."eng_scal" = '中型' AND b."danger_level" = '1' THEN 1 ELSE 0 END ) AS midOneNum,
+ sum( CASE WHEN b."eng_scal" = '中型' AND b."danger_level" = '2' THEN 1 ELSE 0 END ) AS midTwoNum,
+ sum( CASE WHEN b."eng_scal" = '中型' AND b."danger_level" = '3' THEN 1 ELSE 0 END ) AS midThreeNum,
+ sum( CASE WHEN b."eng_scal" = '小（1）型' AND b."danger_level" = '1' THEN 1 ELSE 0 END ) AS smallOneNum,
+ sum( CASE WHEN b."eng_scal" = '小（1）型' AND b."danger_level" = '2' THEN 1 ELSE 0 END ) AS smallTwoNum,
+ sum( CASE WHEN b."eng_scal" = '小（1）型' AND b."danger_level" = '3' THEN 1 ELSE 0 END ) AS smallThreeNum,
+ sum( CASE WHEN b."eng_scal" = '小（2）型' AND b."danger_level" = '1' THEN 1 ELSE 0 END ) AS smalltOneNum,
+ sum( CASE WHEN b."eng_scal" = '小（2）型' AND b."danger_level" = '2' THEN 1 ELSE 0 END ) AS smalltTwoNum,
+ sum( CASE WHEN b."eng_scal" = '小（2）型' AND b."danger_level" = '3' THEN 1 ELSE 0 END ) AS smalltThreeNum
+ FROM
+ YWXT."att_ad_base" a
+ LEFT JOIN (
+ SELECT
+ c."danger_level",
+ a."eng_scal",
+ e."guid" AS countyCode,
+ f."guid" AS cityCode,
+ g."guid" AS provinceCode
+ FROM
+ "SJZT_MD"."att_res_base" a
+ RIGHT JOIN ( SELECT * FROM "SJZT_MD"."tb_res_general_investigation_state" WHERE "danger_level" <![CDATA[<>]]> '0' AND "check_state" = '3' ) c ON a."guid" = c."res_guid"
+ LEFT JOIN YWXT."att_ad_base" d ON d."ad_code" = a."interior_ad_guid"
+ LEFT JOIN YWXT."att_ad_base" e ON d."p_ad_code" = e."ad_code"
+ LEFT JOIN YWXT."att_ad_base" f ON e."p_ad_code" = f."ad_code"
+ LEFT JOIN YWXT."att_ad_base" g ON f."p_ad_code" = g."ad_code"
+ ) b ON ((
+ b.countyCode = a."guid"
+ )
+ OR ( b.cityCode = a."guid" )
+ OR ( b.provinceCode = a."guid" ))
+
+ GROUP BY
+ a."ad_code",
+ a."ad_name"
+ ) a
+ LEFT JOIN (
+ SELECT
+ a."ad_code" CODE,
+ a."ad_name" NAME,
+ sum( b."danger_length" ) AS lengthSum,
+ sum( CASE WHEN b."dyke_level" = '1级' AND b."danger_level" = '1' THEN b."danger_length" ELSE 0 END ) AS levelOneLengthOne,
+ sum( CASE WHEN b."dyke_level" = '1级' AND b."danger_level" = '2' THEN b."danger_length" ELSE 0 END ) AS levelOneLengthTwo,
+ sum( CASE WHEN b."dyke_level" = '1级' AND b."danger_level" = '3' THEN b."danger_length" ELSE 0 END ) AS levelOneLengthThree,
+ sum( CASE WHEN b."dyke_level" = '2级' AND b."danger_level" = '1' THEN b."danger_length" ELSE 0 END ) AS levelTwoLengthOne,
+ sum( CASE WHEN b."dyke_level" = '2级' AND b."danger_level" = '2' THEN b."danger_length" ELSE 0 END ) AS levelTwoLengthTwo,
+ sum( CASE WHEN b."dyke_level" = '2级' AND b."danger_level" = '3' THEN b."danger_length" ELSE 0 END ) AS levelTwoLengthThree,
+ sum( CASE WHEN b."dyke_level" = '3级' AND b."danger_level" = '1' THEN b."danger_length" ELSE 0 END ) AS levelThreeLengthOne,
+ sum( CASE WHEN b."dyke_level" = '3级' AND b."danger_level" = '2' THEN b."danger_length" ELSE 0 END ) AS levelThreeLengthTwo,
+ sum( CASE WHEN b."dyke_level" = '3级' AND b."danger_level" = '3' THEN b."danger_length" ELSE 0 END ) AS levelThreeLengthThree,
+ sum( CASE WHEN b."dyke_level" = '4级' AND b."danger_level" = '1' THEN b."danger_length" ELSE 0 END ) AS levelFourLengthOne,
+ sum( CASE WHEN b."dyke_level" = '4级' AND b."danger_level" = '2' THEN b."danger_length" ELSE 0 END ) AS levelFourLengthTwo,
+ sum( CASE WHEN b."dyke_level" = '4级' AND b."danger_level" = '3' THEN b."danger_length" ELSE 0 END ) AS levelFourLengthThree,
+ sum( CASE WHEN b."dyke_level" = '5级' AND b."danger_level" = '1' THEN b."danger_length" ELSE 0 END ) AS levelFiveLengthOne,
+ sum( CASE WHEN b."dyke_level" = '5级' AND b."danger_level" = '2' THEN b."danger_length" ELSE 0 END ) AS levelFiveLengthTwo,
+ sum( CASE WHEN b."dyke_level" = '5级' AND b."danger_level" = '3' THEN b."danger_length" ELSE 0 END ) AS levelFiveLengthThree
+ FROM
+ YWXT."att_ad_base" a
+ RIGHT JOIN (
+ SELECT
+ a."dyke_level",
+ c."danger_level",
+ c."danger_length",
+ d."guid" AS countyCode,
+ e."guid" AS cityCode,
+ f."guid" AS provinceCode
+ FROM
+ "SJZT_MD"."tb_res_dyke" a
+ RIGHT JOIN (
+ SELECT
+ a.*
+ FROM
+ SJZT_MD."tb_dyke_unit_danger" a
+ LEFT JOIN SJZT_MD."tb_dyke_investigation_state" b ON a."dyke_id" = b."dyke_guid"
+ WHERE
+ a."danger_level" <![CDATA[<>]]> '0'
+ AND b."check_state" = '3'
+ ) c ON a."guid" = c."dyke_id"
+ LEFT JOIN YWXT."att_ad_base" d ON d."ad_code" = a."ad_code"
+ LEFT JOIN YWXT."att_ad_base" e ON d."p_ad_code" = e."ad_code"
+ LEFT JOIN YWXT."att_ad_base" f ON e."p_ad_code" = f."ad_code"
+ ) b ON ((
+ b.countyCode = a."guid"
+ )
+ OR ( b.cityCode = a."guid" )
+ OR ( b.provinceCode = a."guid" ))
+
+ GROUP BY
+ a."ad_code",
+ a."ad_name"
+ ) b ON a.CODE = b.CODE
+ left join YWXT."att_ad_base" att on a.CODE = att."guid"
+ where att."p_ad_code"= #{code}
+
+
+
+ </select>
+</mapper>
+
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/service/ITbResAndDykeAllService.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/service/ITbResAndDykeAllService.java
new file mode 100644
index 0000000..7d08b84
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/service/ITbResAndDykeAllService.java
@@ -0,0 +1,14 @@
+package cn.gistack.sm.sjztmd.service;
+
+import cn.gistack.sm.sjztmd.entity.TbProject;
+
+import cn.gistack.sm.sjztmd.vo.ResAndDykeAll;
+import com.baomidou.mybatisplus.extension.service.IService;
+
+import java.util.List;
+
+
+public interface ITbResAndDykeAllService extends IService<TbProject> {
+
+ List<ResAndDykeAll> getStatisticsTable(String code);
+}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/service/impl/TbResAndDykeAllServiceImpl.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/service/impl/TbResAndDykeAllServiceImpl.java
new file mode 100644
index 0000000..6658d93
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/service/impl/TbResAndDykeAllServiceImpl.java
@@ -0,0 +1,28 @@
+package cn.gistack.sm.sjztmd.service.impl;
+
+import cn.gistack.sm.sjztmd.entity.TbProject;
+import cn.gistack.sm.sjztmd.mapper.TbResAndDykeAllMapper;
+import cn.gistack.sm.sjztmd.service.ITbResAndDykeAllService;
+
+import cn.gistack.sm.sjztmd.vo.ResAndDykeAll;
+import com.baomidou.dynamic.datasource.annotation.DS;
+import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+import org.springframework.transaction.annotation.Transactional;
+
+import java.util.List;
+
+@Service
+@DS("zt")
+@Transactional(rollbackFor = Exception.class)
+public class TbResAndDykeAllServiceImpl extends ServiceImpl<TbResAndDykeAllMapper,TbProject> implements ITbResAndDykeAllService {
+
+ @Autowired
+ private TbResAndDykeAllMapper tbResAndDykeAllMapper;
+
+ @Override
+ public List<ResAndDykeAll> getStatisticsTable(String code) {
+ return tbResAndDykeAllMapper.getStatisticsTable(code) ;
+ }
+}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/vo/ResAndDykeAll.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/vo/ResAndDykeAll.java
new file mode 100644
index 0000000..0456e13
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/vo/ResAndDykeAll.java
@@ -0,0 +1,55 @@
+package cn.gistack.sm.sjztmd.vo;
+
+import lombok.Data;
+
+@Data
+public class ResAndDykeAll {
+
+ private String code;
+
+ private String name;
+
+ private String pcode;
+
+ private String pname;
+
+ private String adGrad;
+
+ private int resNum;
+
+ private int bigOneNum;
+ private int bigTwoNum;
+ private int bigThreeNum;
+ private int bigtOneNum;
+ private int bigtTwoNum;
+ private int bigtThreeNum;
+ private int midOneNum;
+ private int midTwoNum;
+ private int midThreeNum;
+ private int smallOneNum;
+ private int smallTwoNum;
+ private int smallThreeNum;
+ private int smalltOneNum;
+ private int smalltTwoNum;
+ private int smalltThreeNum;
+
+ private double lengthSum;
+ private double levelOneLengthOne;
+ private double levelOneLengthTwo;
+ private double levelOneLengthThree;
+ private double levelTwoLengthOne;
+ private double levelTwoLengthTwo;
+ private double levelTwoLengthThree;
+ private double levelThreeLengthOne;
+ private double levelThreeLengthTwo;
+ private double levelThreeLengthThree;
+ private double levelFourLengthOne;
+ private double levelFourLengthTwo;
+ private double levelFourLengthThree;
+ private double levelFiveLengthOne;
+ private double levelFiveLengthTwo;
+ private double levelFiveLengthThree;
+
+
+
+}
--
Gitblit v1.9.3