大坝安全监测统计

guoshilong

2023-08-05 4d1176b8a24b388843a609fcb56c8e26fefd0080

大坝安全监测统计

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/controller/DamSecurityController.java

New file
@@ -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);
    }

}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.java

New file
@@ -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);
}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml

New file
@@ -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>

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/IDamSecurityService.java

New file
@@ -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);
}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/service/impl/DamSecurityServiceImpl.java

New file
@@ -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);
    }
}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/vo/DamSecurityStatistics.java

New file
@@ -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;
}