业务一张图返回乡镇

guoshilong

2024-01-12 9701dfc74de2bb27e918557eacf3b5633ff59f4c

业务一张图返回乡镇

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

@@ -1654,6 +1654,74 @@
        group by r.countyCode,r.countyName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode

    UNION ALL

    SELECT
    tmp.*,
    'sy' as type,
    parent."p_ad_code" pAdCode,
    parent."p_ad_name" pAdName
    FROM(
    SELECT
    r.townName as adName,
    r.townCode as adCode,
    count(*) as total,
    '4' AS adGrad
    FROM (
    SELECT
    resList."res_cd",
    case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
    case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
    case when town."ad_grad" = 3 THEN town."ad_code"
    when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
    case when town."ad_grad" = 3 THEN town."ad_name"
    when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
    case when town."ad_grad" = 2 THEN town."ad_code"
    when county."ad_grad" = 2 THEN county."ad_code"
    ELSE city."ad_code" END AS cityCode,
    case when town."ad_grad" = 2 THEN town."ad_name"
    when county."ad_grad" = 2 THEN county."ad_name"
    ELSE city."ad_name" END AS cityName,
    case when town."ad_grad" = 1 THEN town."ad_code"
    when county."ad_grad" = 1 THEN county."ad_code"
    when city."ad_grad" = 1 THEN city."ad_code"
    ELSE province."ad_code" END AS provinceCode,
    case when town."ad_grad" = 1 THEN town."ad_name"
    when county."ad_grad" = 1 THEN county."ad_name"
    when city."ad_grad" = 1 THEN city."ad_name"
    ELSE province."ad_name" END AS provinceName
    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=" 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>
    GROUP BY "res_cd"


    ) resList
    LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList."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" )r
    group by r.townName, r.townCode)TMP
    LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode


    </select>
    <select id="getDamSlRegion" resultType="cn.gistack.sm.damSecurity.vo.DamStatisticsRegion">
@@ -1792,6 +1860,7 @@
        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>
@@ -1868,6 +1937,7 @@
        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>
@@ -1881,6 +1951,82 @@
        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" )r
        group by r.countyCode,r.countyName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
        UNION ALL

        SELECT
        tmp.*,
        'sl' as type,
        parent."p_ad_code" pAdCode,
        parent."p_ad_name" pAdName
        FROM(
        SELECT
        r.townName as adName,
        r.townCode as adCode,
        count(*) as total,
        '4' AS adGrad
        FROM (
        SELECT
        resList."res_cd",
        case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
        case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
        case when town."ad_grad" = 3 THEN town."ad_code"
        when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
        case when town."ad_grad" = 3 THEN town."ad_name"
        when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
        case when town."ad_grad" = 2 THEN town."ad_code"
        when county."ad_grad" = 2 THEN county."ad_code"
        ELSE city."ad_code" END AS cityCode,
        case when town."ad_grad" = 2 THEN town."ad_name"
        when county."ad_grad" = 2 THEN county."ad_name"
        ELSE city."ad_name" END AS cityName,
        case when town."ad_grad" = 1 THEN town."ad_code"
        when county."ad_grad" = 1 THEN county."ad_code"
        when city."ad_grad" = 1 THEN city."ad_code"
        ELSE province."ad_code" END AS provinceCode,
        case when town."ad_grad" = 1 THEN town."ad_name"
        when county."ad_grad" = 1 THEN county."ad_name"
        when city."ad_grad" = 1 THEN city."ad_name"
        ELSE province."ad_name" END AS provinceName
        FROM(



        SELECT
        "res_cd",
        COUNT(*) slStation
        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"
        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>
        GROUP BY "res_cd"


        ) resList
        LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList."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" )r
        group by r.townCode,r.townName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
    </select>

@@ -2095,6 +2241,77 @@
        LEFT JOIN SJZT_MD."att_ad_base" province ON province."guid" = city."p_ad_code" )r
        group by r.countyCode,r.countyName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode

        UNION ALL

        SELECT
        tmp.*,
        'wy' as type,
        parent."p_ad_code" pAdCode,
        parent."p_ad_name" pAdName
        FROM(
        SELECT
        r.townName as adName,
        r.townCode as adCode,
        count(*) as total,
        '4' AS adGrad
        FROM (
        SELECT
        resList."res_cd",
        case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
        case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
        case when town."ad_grad" = 3 THEN town."ad_code"
        when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
        case when town."ad_grad" = 3 THEN town."ad_name"
        when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
        case when town."ad_grad" = 2 THEN town."ad_code"
        when county."ad_grad" = 2 THEN county."ad_code"
        ELSE city."ad_code" END AS cityCode,
        case when town."ad_grad" = 2 THEN town."ad_name"
        when county."ad_grad" = 2 THEN county."ad_name"
        ELSE city."ad_name" END AS cityName,
        case when town."ad_grad" = 1 THEN town."ad_code"
        when county."ad_grad" = 1 THEN county."ad_code"
        when city."ad_grad" = 1 THEN city."ad_code"
        ELSE province."ad_code" END AS provinceCode,
        case when town."ad_grad" = 1 THEN town."ad_name"
        when county."ad_grad" = 1 THEN county."ad_name"
        when city."ad_grad" = 1 THEN city."ad_name"
        ELSE province."ad_name" END AS provinceName
        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=" 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>
        GROUP BY "res_cd"


        ) resList
        LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList."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" )r
        group by r.townCode,r.townName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode
    </select>


@@ -2306,6 +2523,74 @@
        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" )r
        group by r.countyCode,r.countyName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode

        UNION ALL

        SELECT
        tmp.*,
        parent."p_ad_code" pAdCode,
        parent."p_ad_name" pAdName
        FROM(
        SELECT
        r.townName as adName,
        r.townCode as adCode,

        count(*) as total,
        '4' AS adGrad
        FROM (
        SELECT
        resList.resCd,
        case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
        case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
        case when town."ad_grad" = 3 THEN town."ad_code"
        when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
        case when town."ad_grad" = 3 THEN town."ad_name"
        when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
        case when town."ad_grad" = 2 THEN town."ad_code"
        when county."ad_grad" = 2 THEN county."ad_code"
        ELSE city."ad_code" END AS cityCode,
        case when town."ad_grad" = 2 THEN town."ad_name"
        when county."ad_grad" = 2 THEN county."ad_name"
        ELSE city."ad_name" END AS cityName,
        case when town."ad_grad" = 1 THEN town."ad_code"
        when county."ad_grad" = 1 THEN county."ad_code"
        when city."ad_grad" = 1 THEN city."ad_code"
        ELSE province."ad_code" END AS provinceCode,
        case when town."ad_grad" = 1 THEN town."ad_name"
        when county."ad_grad" = 1 THEN county."ad_name"
        when city."ad_grad" = 1 THEN city."ad_name"
        ELSE province."ad_name" END AS provinceName
        FROM(

        SELECT DAM_ID 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 1= 1 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
        <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.type =='sy' ">
            AND NAG.type = '1'
        </if>
        <if test="dam.type =='sl' ">
            AND NAG.type = '2'
        </if>
        <if test="dam.type =='wy' ">
            AND (NAG.type = '3'  or NAG.type ='4' or NAG.type ='5' )
        </if>

        GROUP BY DAM_ID
        ) resList
        LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
        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" )r
        group by r.townCode,r.townName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode


@@ -2605,16 +2890,102 @@
        group by r.countyCode,r.countyName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode

        UNION ALL

        SELECT
        tmp.*,
        parent."p_ad_code" pAdCode,
        parent."p_ad_name" pAdName
        FROM(
        SELECT
        r.townName as adName,
        r.townCode as adCode,
        count(*) as total,
        '4' AS adGrad
        FROM (
        SELECT
        resList.resCd,
        case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
        case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
        case when town."ad_grad" = 3 THEN town."ad_code"
        when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
        case when town."ad_grad" = 3 THEN town."ad_name"
        when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
        case when town."ad_grad" = 2 THEN town."ad_code"
        when county."ad_grad" = 2 THEN county."ad_code"
        ELSE city."ad_code" END AS cityCode,
        case when town."ad_grad" = 2 THEN town."ad_name"
        when county."ad_grad" = 2 THEN county."ad_name"
        ELSE city."ad_name" END AS cityName,
        case when town."ad_grad" = 1 THEN town."ad_code"
        when county."ad_grad" = 1 THEN county."ad_code"
        when city."ad_grad" = 1 THEN city."ad_code"
        ELSE province."ad_code" END AS provinceCode,
        case when town."ad_grad" = 1 THEN town."ad_name"
        when county."ad_grad" = 1 THEN county."ad_name"
        when city."ad_grad" = 1 THEN city."ad_name"
        ELSE province."ad_name" END AS provinceName
        FROM(

        SELECT normalList."res_cd" as resCd FROM (
        SELECT allList."res_cd",errorList."name" 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=" 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>
        GROUP BY "res_cd"
        ) allList LEFT JOIN

        (
        SELECT DAM_ID as resCd,arb."name" 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 1= 1 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
        <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.type =='sy' ">
            AND NAG.type = '1'
        </if>
        <if test="dam.type =='sl' ">
            AND NAG.type = '2'
        </if>
        <if test="dam.type =='wy' ">
            AND (NAG.type = '3'  or NAG.type ='4' or NAG.type ='5' )
        </if>

        GROUP BY DAM_ID,arb."name"


        ) errorList ON allList."res_cd" = errorList.resCd
        )normalList where normalList."name" is null
        ) resList
        LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
        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" )r
        group by r.townCode,r.townName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode

    </select>
    <select id="getDamSlRegionNormal" resultType="cn.gistack.sm.damSecurity.vo.DamStatisticsRegion">








        <if test="dam.adCode == '420000000000'">
            SELECT
            tmp.*,
@@ -2678,6 +3049,7 @@
            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>
@@ -2788,6 +3160,7 @@
        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>
@@ -2897,6 +3270,7 @@
        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>
@@ -2941,21 +3315,113 @@
        group by r.countyCode,r.countyName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode

        UNION ALL

        SELECT
        tmp.*,
        parent."p_ad_code" pAdCode,
        parent."p_ad_name" pAdName
        FROM(
        SELECT
        r.townName as adName,
        r.townCode as adCode,
        count(*) as total,
        '4' AS adGrad
        FROM (
        SELECT
        resList.resCd,
        case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
        case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
        case when town."ad_grad" = 3 THEN town."ad_code"
        when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
        case when town."ad_grad" = 3 THEN town."ad_name"
        when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
        case when town."ad_grad" = 2 THEN town."ad_code"
        when county."ad_grad" = 2 THEN county."ad_code"
        ELSE city."ad_code" END AS cityCode,
        case when town."ad_grad" = 2 THEN town."ad_name"
        when county."ad_grad" = 2 THEN county."ad_name"
        ELSE city."ad_name" END AS cityName,
        case when town."ad_grad" = 1 THEN town."ad_code"
        when county."ad_grad" = 1 THEN county."ad_code"
        when city."ad_grad" = 1 THEN city."ad_code"
        ELSE province."ad_code" END AS provinceCode,
        case when town."ad_grad" = 1 THEN town."ad_name"
        when county."ad_grad" = 1 THEN county."ad_name"
        when city."ad_grad" = 1 THEN city."ad_name"
        ELSE province."ad_name" END AS provinceName
        FROM(
        SELECT normalList."res_cd" as resCd FROM (
        SELECT allList."res_cd",errorList."name" FROM (




        SELECT
        "res_cd",
        COUNT(*) slStation
        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"
        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>
        GROUP BY "res_cd"



        ) allList LEFT JOIN

        (
        SELECT DAM_ID as resCd,arb."name" 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 1= 1 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
        <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.type =='sy' ">
            AND NAG.type = '1'
        </if>
        <if test="dam.type =='sl' ">
            AND NAG.type = '2'
        </if>
        <if test="dam.type =='wy' ">
            AND (NAG.type = '3'  or NAG.type ='4' or NAG.type ='5' )
        </if>

        GROUP BY DAM_ID,arb."name"








        ) errorList ON allList."res_cd" = errorList.resCd
        )normalList where normalList."name" is null
        ) resList
        LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
        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" )r
        group by r.townCode,r.townName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode



@@ -3279,6 +3745,108 @@
        group by r.countyCode,r.countyName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode

        UNION ALL

        SELECT
        tmp.*,
        parent."p_ad_code" pAdCode,
        parent."p_ad_name" pAdName
        FROM(
        SELECT
        r.townName as adName,
        r.townCode as adCode,
        count(*) as total,
        '4' AS adGrad
        FROM (
        SELECT
        resList.resCd,
        case when town."ad_grad" = 4 THEN town."ad_code" ELSE NULL END AS townCode,
        case when town."ad_grad" = 4 THEN town."ad_name" ELSE NULL END AS townName,
        case when town."ad_grad" = 3 THEN town."ad_code"
        when county."ad_grad" = 3 THEN county."ad_code" END AS countyCode,
        case when town."ad_grad" = 3 THEN town."ad_name"
        when county."ad_grad" = 3 THEN county."ad_name" END AS countyName,
        case when town."ad_grad" = 2 THEN town."ad_code"
        when county."ad_grad" = 2 THEN county."ad_code"
        ELSE city."ad_code" END AS cityCode,
        case when town."ad_grad" = 2 THEN town."ad_name"
        when county."ad_grad" = 2 THEN county."ad_name"
        ELSE city."ad_name" END AS cityName,
        case when town."ad_grad" = 1 THEN town."ad_code"
        when county."ad_grad" = 1 THEN county."ad_code"
        when city."ad_grad" = 1 THEN city."ad_code"
        ELSE province."ad_code" END AS provinceCode,
        case when town."ad_grad" = 1 THEN town."ad_name"
        when county."ad_grad" = 1 THEN county."ad_name"
        when city."ad_grad" = 1 THEN city."ad_name"
        ELSE province."ad_name" END AS provinceName
        FROM(

        SELECT normalList."res_cd" as resCd FROM (
        SELECT allList."res_cd",errorList."name" 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=" 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>
        GROUP BY "res_cd"



        ) allList LEFT JOIN

        (
        SELECT DAM_ID as resCd,arb."name" 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 1= 1 AND DATE_FORMAT(time,'%Y-%m-%d')>= CURDATE()
        <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.type =='sy' ">
            AND NAG.type = '1'
        </if>
        <if test="dam.type =='sl' ">
            AND NAG.type = '2'
        </if>
        <if test="dam.type =='wy' ">
            AND (NAG.type = '3'  or NAG.type ='4' or NAG.type ='5' )
        </if>

        GROUP BY DAM_ID,arb."name"


        ) errorList ON allList."res_cd" = errorList.resCd
        )normalList where normalList."name" is null
        ) resList
        LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = resList.resCd
        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" )r
        group by r.townCode,r.townName)TMP
        LEFT JOIN SJZT_MD."att_ad_base" parent ON parent."guid" = tmp.adCode