大坝安全统计接口

guoshilong

2023-08-10 f4624736f4be23966b0ec573816d1b5ceed1cd97

大坝安全统计接口

 skjcmanager/skjcmanager-service/skjcmanager-nky/src/main/java/cn/gistack/nky/mapper/AlarmGetMapper.xml

@@ -60,6 +60,9 @@
        <if test="vo.type != null and vo.type !='' ">
            AND type = #{vo.type}
        </if>
        <if test="vo.pointId != null and vo.pointId !='' ">
            AND POINT_id = #{vo.pointId}
        </if>
        AND dam_id =  #{vo.damId}
    </select>
</mapper>

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

@@ -6,6 +6,7 @@
import cn.gistack.sm.damSecurity.service.IDamSecurityService;
import cn.gistack.sm.damSecurity.vo.DamSecurityStatistics;
import cn.gistack.sm.damSecurity.vo.DamStationStatistics;
import cn.gistack.sm.damSecurity.vo.DamStatisticsRes;
import com.baomidou.mybatisplus.core.metadata.IPage;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
@@ -117,4 +118,16 @@
        return R.data(list);
    }

    /**
     * 业务一张图 大坝安全监测
     * @param adCode
     * @return
     */
    @ApiOperation(value = "大坝安全统计-大坝安全监测", notes = "大坝安全统计-大坝安全监测")
    @GetMapping(value = "/damResInfoCount")
    public R damResInfoCount(String adCode){
        List<DamStatisticsRes> list = damSecurityService.damResInfoCount(adCode);
        return R.data(list);
    }

}

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

@@ -2,6 +2,7 @@

import cn.gistack.sm.damSecurity.vo.DamSecurityStatistics;
import cn.gistack.sm.damSecurity.vo.DamStationStatistics;
import cn.gistack.sm.damSecurity.vo.DamStatisticsRes;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
@@ -19,4 +20,8 @@
    List<DamStationStatistics> tableResCountPage(@Param("dam")DamStationStatistics damStationStatistics, IPage<DamStationStatistics> page);

    List<DamStationStatistics> alarmResCount(@Param("dam") DamStationStatistics damStationStatistics);

    List<DamStatisticsRes> damResInfoCount(@Param("adCode") String adCode);

    List<DamStationStatistics> getResErrorList(@Param("dam") DamStatisticsRes damStatisticsRes, @Param("adCode") String adCode);
}

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

@@ -69,7 +69,6 @@
                            <if test="dam.endTime != null and dam.endTime !='' ">
                                AND DATE_FORMAT(time,'%Y-%m-%d') &lt;= #{dam.endTime}
                            </if>

                        )) errorNum
            ),SJZT_MD."att_ad_base" aab
            where 1=1
@@ -252,21 +251,30 @@
        where (f."res_cd" is not null OR g."res_cd" is not null OR h."res_cd" is not null)
        ) baseRes
        LEFT JOIN (
        SELECT "res_cd",COUNT(*) wyStation
        FROM ( SELECT b."guid" as "res_cd",'wy' as "sys_resource"
        FROM (SELECT "station_code" from sjzt_md."att_mqtt_calc_data" group by "station_code") a LEFT JOIN SJZT_MD."att_res_base" b on substr(a."station_code",0,length(a."station_code")-2) = b."guid" )
            SELECT "res_cd",COUNT(*) wyStation
            FROM
                 (
                SELECT b."guid" as "res_cd",'wy' as "sys_resource"
                FROM (
                    SELECT "station_code" from sjzt_md."att_mqtt_calc_data" group by "station_code") a LEFT JOIN SJZT_MD."att_res_base" b on substr(a."station_code",0,length(a."station_code")-2) = b."guid"
                    WHERE b."guid" is not null
                 )
        GROUP BY "res_cd"
        ) wy ON wy."res_cd" = baseRes."guid"
        LEFT JOIN(
        SELECT "res_cd", COUNT(*) syStation
        FROM( SELECT b."guid" as "res_cd",'sy' as "sys_resource"
        FROM sjzt_ods."dsm_spg_spprmp" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code")
        FROM sjzt_ods."dsm_spg_spprmp" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
        WHERE b."guid" is not null
            )
        GROUP BY "res_cd"
        ) sy ON sy."res_cd" = baseRes."guid"
        LEFT JOIN (
        SELECT "res_cd",COUNT(*) slStation
        FROM  ( SELECT b."guid" as "res_cd",'sl' as "sys_resource"
        FROM sjzt_ods."dsm_spg_spqnmp" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code" group by b."guid" )
        FROM sjzt_ods."dsm_spg_spqnmp" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
        WHERE b."guid" is not null
        group by b."guid" )
        GROUP BY "res_cd"
        ) sl ON sl."res_cd" = baseRes."guid"
        LEFT JOIN (
@@ -367,19 +375,21 @@
        LEFT JOIN (
            SELECT "res_cd",COUNT(*) wyStation
            FROM ( SELECT b."guid" as "res_cd",'wy' as "sys_resource"
            FROM (SELECT "station_code" from sjzt_md."att_mqtt_calc_data" group by "station_code") a LEFT JOIN SJZT_MD."att_res_base" b on substr(a."station_code",0,length(a."station_code")-2) = b."guid" )
            FROM (SELECT "station_code" from sjzt_md."att_mqtt_calc_data" group by "station_code") a LEFT JOIN SJZT_MD."att_res_base" b on substr(a."station_code",0,length(a."station_code")-2) = b."guid"
        WHERE b."guid" is not null)
            GROUP BY "res_cd"
        ) wy ON wy."res_cd" = baseRes."guid"
        LEFT JOIN(
            SELECT "res_cd", COUNT(*) syStation
            FROM( SELECT b."guid" as "res_cd",'sy' as "sys_resource"
            FROM sjzt_ods."dsm_spg_spprmp" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code")
            FROM sjzt_ods."dsm_spg_spprmp" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
        WHERE b."guid" is not null)
            GROUP BY "res_cd"
        ) sy ON sy."res_cd" = baseRes."guid"
        LEFT JOIN (
            SELECT "res_cd",COUNT(*) slStation
            FROM  ( SELECT b."guid" as "res_cd",'sl' as "sys_resource"
            FROM sjzt_ods."dsm_spg_spqnmp" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code" group by b."guid" )
            FROM sjzt_ods."dsm_spg_spqnmp" a LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code" WHERE b."guid" is not null group by b."guid" )
            GROUP BY "res_cd"
        ) sl ON sl."res_cd" = baseRes."guid"
        LEFT JOIN (
@@ -446,4 +456,184 @@
            AND  (province."ad_code" =#{dam.adCode} or city."ad_code" = #{dam.adCode} or county."ad_code" = #{dam.adCode})
        </if>
    </select>
    <select id="damResInfoCount" resultType="cn.gistack.sm.damSecurity.vo.DamStatisticsRes">


        SELECT
               base.total AS total,
               base.errorNum AS errorNum,
               (base.total - base.errorNum) AS normalNum,
               base.type AS type,
               base.typeName AS typeName
        FROM
             (
                    SELECT * FROM
                    (
                    --  位移水库数
                    SELECT
                    COUNT(*) total,
                    '位移' AS typeName,
                    '3' AS type,
                    (SELECT COUNT(*)FROM
                    (
                    SELECT DAM_ID FROM YWXT.NKY_ALARM_GET temp
                    LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp.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
                    (TYPE = 3 or type = 4 or type = 5) AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
                    <if test=" adCode!= null and adCode !='' ">
                        AND  (province."ad_code" =#{adCode} or city."ad_code" = #{adCode} or county."ad_code" = #{adCode})
                    </if>
                    GROUP BY DAM_ID
                    )
                    ) errorNum
                    FROM
                    (
                    SELECT "res_cd",COUNT(*) wyStation
                    FROM
                    (
                    SELECT b."guid" as "res_cd",'wy' as "sys_resource"
                    FROM
                    (
                    SELECT "station_code" from sjzt_md."att_mqtt_calc_data" group by "station_code"
                    ) a LEFT JOIN SJZT_MD."att_res_base" b on substr(a."station_code",0,length(a."station_code")-2) = b."guid"
                    where b."guid" is not null
                    ) temp
                    LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp."res_cd"
                    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=" adCode!= null and adCode !='' ">
                        AND  (province."ad_code" =#{adCode} or city."ad_code" = #{adCode} or county."ad_code" = #{adCode})
                    </if>
                    GROUP BY "res_cd"
                    )
                    UNION ALL
                    --  渗压水库数
                    SELECT
                    COUNT(*) total,
                    '渗压' AS typeName ,
                    '1' AS type,
                    (SELECT COUNT(*) FROM
                    (
                    SELECT DAM_ID FROM YWXT.NKY_ALARM_GET temp
                    LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp.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
                    TYPE = 1
                    AND  DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
                    <if test=" adCode!= null and adCode !='' ">
                        AND  (province."ad_code" =#{adCode} or city."ad_code" = #{adCode} or county."ad_code" = #{adCode})
                    </if>
                    GROUP BY DAM_ID
                    )
                    ) errorNum
                    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=" adCode!= null and adCode !='' ">
                        AND  (province."ad_code" =#{adCode} or city."ad_code" = #{adCode} or county."ad_code" = #{adCode})
                    </if>
                    GROUP BY "res_cd"
                    )
                    UNION ALL
                    -- 渗流水库数
                    SELECT
                    COUNT(*) total,
                    '渗流' AS typeName,
                    '2' AS type,
                    (
                    SELECT COUNT(*) FROM
                    (
                    SELECT DAM_ID FROM YWXT.NKY_ALARM_GET temp
                    LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = temp.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
                    TYPE = 2
                    AND  DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
                    <if test=" adCode!= null and adCode !='' ">
                        AND  (province."ad_code" =#{adCode} or city."ad_code" = #{adCode} or county."ad_code" = #{adCode})
                    </if>
                    GROUP BY DAM_ID
                    )
                    ) errorNum
                    FROM
                    (
                    SELECT
                    "res_cd",
                    COUNT(*) slStation
                    FROM  ( SELECT b."guid" as "res_cd",'sl' as "sys_resource"
                    FROM sjzt_ods."dsm_spg_spqnmp" a
                    LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
                    WHERE b."guid" is not null
                    group by b."guid" ) 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=" adCode!= null and adCode !='' ">
                        AND  (province."ad_code" =#{adCode} or city."ad_code" = #{adCode} or county."ad_code" = #{adCode})
                    </if>
                    GROUP BY "res_cd"
                    )
                    )
            ) base

    </select>
    <select id="getResErrorList" resultType="cn.gistack.sm.damSecurity.vo.DamStationStatistics">

        SELECT
            DISTINCT
            arb."guid" AS resCd,
            arb."name" AS resName,
            arb."center_long" AS longitude,
            arb."center_lat" AS latitude
        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
          DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
        <if test="dam.type != null and dam.type != '' and dam.type == 1">
              AND NAG.TYPE =  #{dam.type}
          </if>
        <if test="dam.type != null and dam.type != '' and dam.type == 2">
            AND NAG.TYPE =  #{dam.type}
        </if>
        <if test="dam.type != null and dam.type !='' and dam.type == 3">
            AND (NAG.TYPE = '3' or NAG.TYPE  = '4' or NAG.TYPE  = '5')
        </if>
        <if test="adCode != null and adCode !='' ">
            AND  (province."ad_code" =#{adCode} or city."ad_code" = #{adCode} or county."ad_code" = #{adCode})
        </if>
    </select>


</mapper>

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

@@ -2,6 +2,7 @@

import cn.gistack.sm.damSecurity.vo.DamSecurityStatistics;
import cn.gistack.sm.damSecurity.vo.DamStationStatistics;
import cn.gistack.sm.damSecurity.vo.DamStatisticsRes;
import com.baomidou.mybatisplus.core.metadata.IPage;

import java.util.List;
@@ -36,4 +37,11 @@
     * @return
     */
    List<DamStationStatistics> alarmResCount(DamStationStatistics damStationStatistics);

    /**
     * 业务一张图大坝安全监测
     * @param adCode 行政区划
     * @return
     */
    List<DamStatisticsRes> damResInfoCount(String adCode);
}

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

@@ -4,6 +4,7 @@
import cn.gistack.sm.damSecurity.service.IDamSecurityService;
import cn.gistack.sm.damSecurity.vo.DamSecurityStatistics;
import cn.gistack.sm.damSecurity.vo.DamStationStatistics;
import cn.gistack.sm.damSecurity.vo.DamStatisticsRes;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.springblade.core.tool.utils.ObjectUtil;
import org.springframework.beans.factory.annotation.Autowired;
@@ -44,4 +45,18 @@
    public List<DamStationStatistics> alarmResCount(DamStationStatistics damStationStatistics) {
        return damSecurityMapper.alarmResCount(damStationStatistics);
    }

    @Override
    public List<DamStatisticsRes> damResInfoCount(String adCode) {

        List<DamStatisticsRes> list = damSecurityMapper.damResInfoCount(adCode);

        list.forEach(damStatisticsRes -> {

            List<DamStationStatistics> errorList = damSecurityMapper.getResErrorList(damStatisticsRes,adCode);
            damStatisticsRes.setErrorList(errorList);
        });

        return list;
    }
}

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

New file
@@ -0,0 +1,39 @@
package cn.gistack.sm.damSecurity.vo;

import lombok.Data;

import java.util.List;

@Data
/**
 * 业务一张图 大坝安全监测
 */
public class DamStatisticsRes {

    /**
     * 总数
     */
    private Integer total;

    /**
     * 正常数量
     */
    private Integer normalNum;

    /**
     * 异常数量
     */
    private Integer errorNum;

    /**
     * 类型
     */
    private String type;

    /**
     * 类型名
     */
    private String typeName;

    private List<DamStationStatistics> errorList;
}