堤防柱状图数据问题修改

guoshilong

2024-04-03 db7509f3fcad6a92446c189e4a2294ce0c6378af

堤防柱状图数据问题修改

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/AttAdBaseMapper.java

@@ -47,4 +47,5 @@
    String getAdGradByAdCode(@Param("adCode") String adCode);


    AttAdBase getYwxtAdDetail(@Param("adCode") String adCode);
}

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/AttAdBaseMapper.xml

@@ -167,6 +167,9 @@
    <select id="getAdGradByAdCode" resultType="java.lang.String">
        select "ad_grad" from SJZT_MD."att_ad_base" where "guid" = #{adCode}
    </select>
    <select id="getYwxtAdDetail" resultType="cn.gistack.sm.sjztmd.entity.AttAdBase">
        select * from ywxt."att_ad_base" where "guid" = #{adCode}
    </select>


</mapper>

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbDykeInvestigationMapper.xml

@@ -194,6 +194,98 @@


    <select id="getStatisticsBar" resultType="cn.gistack.sm.sjztmd.statisticsVO.StatisticsBar">
        SELECT
            aab."guid" code,
            aab."ad_name" name,
            dyke.dykeTotalLength as dykeLength,
            dangerDyke.dykeLength as termiteLength
        FROM YWXT."att_ad_base" aab
        LEFT JOIN (
            SELECT
                aab."guid" adCode,
                aab."ad_name" adName,
                SUM(CASE WHEN dykeLengthInfo."dyke_length" THEN dykeLengthInfo."dyke_length" ELSE 0 END) dykeTotalLength
            FROM YWXT."att_ad_base" aab
            LEFT JOIN (
                SELECT
                    county."ad_code" countyCode,
                    city."ad_code" cityCode,
                    province."ad_code" provinceCode,
                    trd."dyke_length"
                FROM SJZT_MD."tb_res_dyke" trd
                         LEFT JOIN ywxt."att_ad_base" county on county."ad_code" = trd."ad_code"
                         LEFT JOIN ywxt."att_ad_base" city on city."ad_code" = county."p_ad_code"
                         LEFT JOIN ywxt."att_ad_base" province on province."ad_code" = city."p_ad_code"
            ) dykeLengthInfo on ((dykeLengthInfo.countyCode = aab."guid") or (dykeLengthInfo.cityCode = aab."guid") or (dykeLengthInfo.provinceCode = aab."guid"))
            group by aab."guid",aab."ad_name"
        )dyke on dyke.adCode = aab."guid"

        left join (
            SELECT
        aab."guid",
        aab."ad_name",
        SUM(CASE WHEN trd."dyke_guid" is not null then trd.dangerLength else 0 END) as dykeLength
        FROM YWXT."att_ad_base" aab
        LEFT JOIN (
        SELECT
        trd."guid",
        trd."dyke_length",
        trd."dyke_level",
        trd."dyke_guid",
        trd.dangerLength,
        county."guid" as countyCode,
        city."guid" as cityCode,
        province."guid" as provinceCode
        FROM (
        SELECT
        tdis."guid",
        tdis."dyke_guid",
        trd."ad_code",
        trd."dyke_level",
        trd."dyke_length",
        tdi.dangerLength
        FROM SJZT_MD."tb_dyke_investigation_state" tdis
        LEFT JOIN SJZT_MD."tb_res_dyke" trd on trd."guid" = tdis."dyke_guid"
        LEFT JOIN (
        SELECT
        TEMP1."tb_state_id",
        TEMP1."unit",
        TEMP1.dangerLength/temp1.num as dangerLength
        FROM (
        SELECT
        tdi."tb_state_id",
        tdi."dyke_guid",
        tdi."unit",
        count(*) as num,
        SUM(CASE WHEN (tdi."danger_length") THEN tdi."danger_length" ELSE 0 END) AS dangerLength
        from sjzt_md."tb_dyke_investigation" tdi
        where 1=1
        <if test="params.tbYear!=null and params.tbYear!=''">
            AND    tdi."tb_year" = #{params.tbYear}
        </if>
        and tdi."investigation_variety" = '白蚁'
        group by tdi."tb_state_id",tdi."dyke_guid",tdi."unit"
        )TEMP1
        ) tdi on tdi."tb_state_id" = tdis."guid"
        where (tdis."check_state" = 1 or tdis."check_state" = 2)
        ) trd
        LEFT JOIN YWXT."att_ad_base" county ON county."ad_code" = trd."ad_code"
        LEFT JOIN YWXT."att_ad_base" city ON city."ad_code" = county."p_ad_code"
        LEFT JOIN YWXT."att_ad_base" province ON province."ad_code" = city."p_ad_code"
        )trd on ((trd.countyCode = aab."guid") or (trd.cityCode = aab."guid") or (trd.provinceCode = aab."guid"))
        group by aab."guid",aab."ad_name"
        )dangerDyke on dangerDyke."guid" = aab."guid"

        where 1= 1
        <if test="params.adGrad !=3">
            <if test="params.adCode != null and params.adCode !=''">
                AND aab."p_ad_code" = #{params.adCode}
            </if>
        </if>
        <if test="params.adGrad ==3">
            <if test="params.adCode != null and params.adCode !=''">
                AND aab."ad_code" = #{params.adCode}
            </if>
        </if>
    </select>
</mapper>