大坝安全监测渗流查询修改

guoshilong

2023-08-30 f65769547907200ac62e936f2ffa3a96bf27ca44

大坝安全监测渗流查询修改

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

@@ -456,8 +456,6 @@
    </select>

    <select id="damResInfoCount" resultType="cn.gistack.sm.damSecurity.vo.DamStatisticsRes">


        SELECT
               base.total AS total,
               base.errorNum AS errorNum,
@@ -585,11 +583,20 @@
                    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
                    FROM  (
                        SELECT
                        b."guid" as "res_cd",
                        'sl' as "sys_resource"
                        FROM sjzt_ods."dsm_spg_spqn" a
                        LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
                        WHERE
                        b."guid" IS NOT NULL
                        AND      a."mstm" &gt;=now()-30 and a."mstm"&lt;= now()



                        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"
@@ -682,8 +689,8 @@


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

    <select id="getResSyNormalList" resultType="cn.gistack.sm.damSecurity.vo.DamStationStatistics">
        SELECT
            DISTINCT arb."guid" AS resCd,
                     arb."name" AS resName,
@@ -727,36 +734,43 @@


    </select>

    <select id="getResSlNormalList" 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 (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
                AND b."guid"  NOT IN (
                  ifnull(
                      (SELECT wm_concat(temp.resCd) FROM
                          (
                              SELECT DISTINCT arb."guid" AS resCd
        FROM (

                              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 NAG.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>
                          ) temp),''
                      )
                  )
              group by b."guid" ) baseRes
        SELECT
        b."guid" as "res_cd",
        'sl' as "sys_resource"
        FROM sjzt_ods."dsm_spg_spqn" a
        LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
        WHERE
        b."guid" IS NOT NULL
        AND      a."mstm" &gt;=now()-30 and a."mstm"&lt;= now()
        AND b."guid"  NOT IN (
        ifnull(
        (SELECT wm_concat(temp.resCd) FROM
        (
        SELECT DISTINCT arb."guid" AS resCd

        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 NAG.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>
        ) temp),''
        )
        )
        group by b."guid" ) baseRes
                 LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = baseRes."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"
@@ -838,36 +852,40 @@
    <select id="getSlList" 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 (SELECT b."guid" as "res_cd",'sl' as "sys_resource"
        FROM sjzt_ods."dsm_spg_spqnmp" a
            DISTINCT arb."guid" AS resCd,
            arb."name" AS resName,
            arb."center_long" AS longitude,
            arb."center_lat" AS latitude
        FROM (
        SELECT
        b."guid" as "res_cd",
        'sl' as "sys_resource"
        FROM sjzt_ods."dsm_spg_spqn" a
        LEFT JOIN SJZT_MD."att_res_base" b on a."rscd" = b."res_reg_code"
        WHERE b."guid"  IS NOT NULL
        WHERE
            b."guid" IS NOT NULL
        AND      a."mstm" &gt;=now()-30 and a."mstm"&lt;= now()
        <if test="status == 2">
            AND b."guid"  NOT IN (
            ifnull(
            (SELECT wm_concat(temp.resCd) FROM
            (
            SELECT DISTINCT arb."guid" AS resCd

          <if test="status == 2">
              AND b."guid"  NOT IN (
              ifnull(
              (SELECT wm_concat(temp.resCd) FROM
              (
              SELECT DISTINCT arb."guid" AS resCd

              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 NAG.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>
              ) temp),''
              )
              )
          </if>
            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 NAG.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>
            ) temp),''
            )
            )
        </if>

        <if test="status == 3">
            AND b."guid"  IN (
@@ -903,8 +921,8 @@


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

    <select id="getWyList" resultType="cn.gistack.sm.damSecurity.vo.DamStationStatistics">
        SELECT
        DISTINCT arb."guid" AS resCd,
        arb."name" AS resName,