大坝获取全部数据接口

guoshilong

2023-08-08 91ae2a44bfbdf4f34b1fa710965a59e8af8f71c2

大坝获取全部数据接口

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

@@ -72,6 +72,18 @@
    }

    /**
     * 大坝安全监测表格导出
     * @param damStationStatistics
     * @return
     */
    @ApiOperation(value = "大坝安全统计-大坝安全监测表格导出", notes = "大坝安全统计-大坝安全监测表格导出")
    @GetMapping(value = "/tableResCountAll")
    public R tableResCountAll(DamStationStatistics damStationStatistics) {
        List<DamStationStatistics> list  =  damSecurityService.tableResCount(damStationStatistics);
        return R.data(list);
    }

    /**
     * 大坝安全监测预警详情
     * @param alarmGet
     * @return

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

@@ -204,72 +204,117 @@
    </select>
    <select id="tableResCount" resultType="cn.gistack.sm.damSecurity.vo.DamStationStatistics">

        SELECT info."guid",info."name",info."eng_scal",info."town_ad_code",info."town_ad_name",info."county_ad_code",info."county_ad_name",info."city_ad_code",info."city_ad_name",
               (info.wy+info.sy+info.sl) total,(info.wy+info.sy+info.sl-info.errorNum) normalNum,info.errorNum FROM
            (
                SELECT baseRes.*,ifnull(wy.wyStation,0) wy ,ifnull(sy.syStation,0) sy,ifnull(sl.slStation,0) sl,ifnull(alarm.errorNum,0) errorNum 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 (
                            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" )
                            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")
                        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" )
                        GROUP BY "res_cd"
                    ) sl ON sl."res_cd" = baseRes."guid"
                        LEFT JOIN ( SELECT DAM_ID ,COUNT(*) errorNum FROM YWXT.NKY_ALARM_GET GROUP BY "DAM_ID"  ) alarm ON alarm.DAM_ID = baseRes."guid"
                        LEFT JOIN SJZT_MD."att_ad_base" town ON town."guid" = "town_ad_code"
                        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>
            ) info
        SELECT
        info."guid" AS resCd,
        info."name" AS resName,
        info."eng_scal" AS engScal,
        info."town_ad_code" AS townCode,
        info."town_ad_name" AS townName,
        info."county_ad_code" AS countyCode,
        info."county_ad_name" AS countyName ,
        info."city_ad_code" AS cityCode,
        info."city_ad_name" AS cityName,
        (info.wy+info.sy+info.sl) AS total,
        (info.wy+info.sy+info.sl-info.errorCountNum) AS normalNum,
        info.errorNum  AS errorNum
        FROM
        (
        SELECT baseRes.*,ifnull(wy.wyStation,0) wy ,ifnull(sy.syStation,0) sy,ifnull(sl.slStation,0) sl,ifnull(alarm.errorNum,0) errorNum, ifnull(c1.errorCountNum,0) errorCountNum 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 (
        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" )
        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")
        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" )
        GROUP BY "res_cd"
        ) sl ON sl."res_cd" = baseRes."guid"
        LEFT JOIN (
        SELECT DAM_ID ,COUNT(*) errorNum FROM NKY_ALARM_GET
        WHERE 1=1

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


        GROUP BY "DAM_ID"
        ) alarm ON alarm.DAM_ID = baseRes."guid"

        LEFT JOIN (
        SELECT DAM_ID ,COUNT(*) errorCountNum
        FROM (
        SELECT DAM_ID, COUNT(*) FROM YWXT.NKY_ALARM_GET
        WHERE 1=1
        <if test="dam.startTime != null and dam.startTime != ''">
            AND DATE_FORMAT(time,'%Y-%m-%d')  &gt;= #{dam.startTime}
        </if>
        <if test="dam.endTime != null and dam.endTime !='' ">
            AND DATE_FORMAT(time,'%Y-%m-%d') &lt;= #{dam.endTime}
        </if>
        GROUP BY DAM_ID,POINT_ID
        ) baseCount GROUP BY DAM_ID) c1 ON c1.dam_id = baseRes."guid"

        LEFT JOIN SJZT_MD."att_ad_base" town ON town."guid" = "town_ad_code"
        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>

        <if test="dam.resName != null and dam.resName !='' ">
            AND "name" LIKE CONCAT('%',#{dam.resName},'%')
        </if>
        ) info

    </select>

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

        SELECT

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

@@ -21,6 +21,11 @@
     */
    List<DamSecurityStatistics> listRegionCount(DamSecurityStatistics damSecurityStatistics);

    /**
     * 获取全部数据
     * @param damStationStatistics
     * @return
     */
    List<DamStationStatistics> tableResCount(DamStationStatistics damStationStatistics);

    IPage<DamStationStatistics> tableResCountPage(DamStationStatistics damStationStatistics, IPage<DamStationStatistics> page);