水库列表增加水库编码、工程规模筛选

xieb

2024-03-06 0dfd475fe431d0d67339711f9b4a7b9a90e46de7

水库列表增加水库编码、工程规模筛选

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

@@ -11,18 +11,22 @@
        </if>
    </select>

    <select id="selectAttResBaseGeneralInvestigation" resultType="cn.gistack.sm.sjztmd.vo.AttResBaseGeneralInvestigationVO">
    <select id="selectAttResBaseGeneralInvestigation"
            resultType="cn.gistack.sm.sjztmd.vo.AttResBaseGeneralInvestigationVO">
        select a.* from (
            select *,
            case
            when (select count(*) from sjzt_md."tb_res_general_investigation" b where a."guid" = b."res_guid" and b."check_state" = 2) > 0 THEN  '2'
            when (select count(*) from sjzt_md."tb_res_general_investigation" b where a."guid" = b."res_guid" and b."check_state" = 1) > 0 THEN  '1'
            when (select count(*) from sjzt_md."tb_res_general_investigation" b where a."guid" = b."res_guid" and b."check_state" = 0) > 0 THEN  '0'
            ELSE '-1'
            END AS tb_state
        select *,
        case
        when (select count(*) from sjzt_md."tb_res_general_investigation" b where a."guid" = b."res_guid" and
        b."check_state" = 2) > 0 THEN '2'
        when (select count(*) from sjzt_md."tb_res_general_investigation" b where a."guid" = b."res_guid" and
        b."check_state" = 1) > 0 THEN '1'
        when (select count(*) from sjzt_md."tb_res_general_investigation" b where a."guid" = b."res_guid" and
        b."check_state" = 0) > 0 THEN '0'
        ELSE '-1'
        END AS tb_state


            from sjzt_md."att_res_base" a
        from sjzt_md."att_res_base" a
        ) a
        left join "SJZT_MD"."att_ad_base" aab on aab."guid" = a."interior_ad_guid"
        left join "SJZT_MD"."att_ad_base" aa on aab."p_ad_code" = aa."ad_code"
@@ -44,7 +48,8 @@
            AND a.tb_state = #{attResBase.tbState}
        </if>
        <if test="attResBase.adCode != null and attResBase.adCode != '' ">
            and (aab."ad_code" = #{attResBase.adCode} or aa."ad_code" = #{attResBase.adCode} or ab."ad_code" = #{attResBase.adCode} or ac."ad_code" = #{attResBase.adCode})
            and (aab."ad_code" = #{attResBase.adCode} or aa."ad_code" = #{attResBase.adCode} or ab."ad_code" =
            #{attResBase.adCode} or ac."ad_code" = #{attResBase.adCode})
        </if>
    </select>

@@ -56,16 +61,25 @@
        left join "att_ad_base" ac on ab."p_ad_code" = ac."ad_code"
        WHERE arb."is_deleted"=0
        <if test="obj.code != null and obj.code != '' ">
            and (aab."ad_code" = #{obj.code} or aa."ad_code" = #{obj.code} or ab."ad_code" = #{obj.code} or ac."ad_code" = #{obj.code})
            and (aab."ad_code" = #{obj.code} or aa."ad_code" = #{obj.code} or ab."ad_code" = #{obj.code} or ac."ad_code"
            = #{obj.code})
        </if>
         <if test="obj.name !=null and obj.name !=''">
             AND "name" LIKE CONCAT('%',#{obj.name},'%')
         </if>
        <if test="obj.name !=null and obj.name !=''">
            AND "name" LIKE CONCAT('%',#{obj.name},'%')
        </if>
        <if test="obj.guid !=null and obj.guid !=''">
            AND "guid" LIKE CONCAT('%',#{obj.guid},'%')
        </if>
        <if test="obj.engScal !=null and obj.engScal !=''">
            AND "eng_scal" = #{obj.engScal}
        </if>

    </select>
    <select id="getDetailByCode" resultType="cn.gistack.sm.sjztmd.entity.AttResBase">
        SELECT * FROM "att_res_base"
        WHERE "is_deleted"=0 and "code" = #{code}
        SELECT *
        FROM "att_res_base"
        WHERE "is_deleted" = 0
          and "code" = #{code}
    </select>

    <!--获取水库对应责任人分页数据-->
@@ -76,39 +90,49 @@
        REGEXP_SUBSTR (arb."res_loc",'[^-]+',1,3) as countyName,
        REGEXP_SUBSTR (arb."res_loc",'[^-]+',1,4) as townName,
        aab."ad_name" as adName,
        trmp1."user_unit" areaUnitName,trmp1."user_name" areaUsername,trmp1."user_phone" areaPhone,trmp1."position" areaPosition,trmp1."guid" areaUserGuid,
        trmp2."user_unit" customsUnitName,trmp2."user_name" customsUsername,trmp2."user_phone" customsPhone,trmp2."position" customsPosition,trmp2."guid" customsUserGuid,
        trmp3."user_unit" manageUnitUnitName,trmp3."user_name" manageUnitUsername,trmp3."user_phone" manageUnitPhone,trmp3."position" manageUnitPosition,trmp3."guid" manageUnitUserGuid,
        trmp4."user_unit" technologyUnitName,trmp4."user_name" technologyUsername,trmp4."user_phone" technologyPhone,trmp4."position" technologyPosition,trmp4."guid" technologyUserGuid,
        trmp5."user_unit" patrolUnitName,trmp5."user_name" patrolUsername,trmp5."user_phone" patrolPhone,trmp5."position" patrolPosition,trmp5."guid" patrolUserGuid
        trmp1."user_unit" areaUnitName,trmp1."user_name" areaUsername,trmp1."user_phone" areaPhone,trmp1."position"
        areaPosition,trmp1."guid" areaUserGuid,
        trmp2."user_unit" customsUnitName,trmp2."user_name" customsUsername,trmp2."user_phone"
        customsPhone,trmp2."position" customsPosition,trmp2."guid" customsUserGuid,
        trmp3."user_unit" manageUnitUnitName,trmp3."user_name" manageUnitUsername,trmp3."user_phone"
        manageUnitPhone,trmp3."position" manageUnitPosition,trmp3."guid" manageUnitUserGuid,
        trmp4."user_unit" technologyUnitName,trmp4."user_name" technologyUsername,trmp4."user_phone"
        technologyPhone,trmp4."position" technologyPosition,trmp4."guid" technologyUserGuid,
        trmp5."user_unit" patrolUnitName,trmp5."user_name" patrolUsername,trmp5."user_phone"
        patrolPhone,trmp5."position" patrolPosition,trmp5."guid" patrolUserGuid
        from SJZT_MD."att_res_base" arb
        left join "SJZT_MD"."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
        left join "SJZT_MD"."att_ad_base" aa on aab."p_ad_code" = aa."ad_code"
        left join "SJZT_MD"."att_ad_base" ab on aa."p_ad_code" = ab."ad_code"
        left join "SJZT_MD"."att_ad_base" ac on ab."p_ad_code" = ac."ad_code"
        left join (
        select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name") "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position"
        from "SJZT_MD"."att_res_manage_person" where  "type"=1 GROUP BY "res_guid"
        select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name")
        "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position"
        from "SJZT_MD"."att_res_manage_person" where "type"=1 GROUP BY "res_guid"
        ) trmp1 on arb."guid" = trmp1."res_guid"

        left join (
        select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name") "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position"
        from "SJZT_MD"."att_res_manage_person" where  "type"=2 GROUP BY "res_guid"
        select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name")
        "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position"
        from "SJZT_MD"."att_res_manage_person" where "type"=2 GROUP BY "res_guid"
        ) trmp2 on arb."guid" = trmp2."res_guid"

        left join (
        select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name") "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position"
        from "SJZT_MD"."att_res_manage_person" where  "type"=3 GROUP BY "res_guid"
        select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name")
        "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position"
        from "SJZT_MD"."att_res_manage_person" where "type"=3 GROUP BY "res_guid"
        ) trmp3 on arb."guid" = trmp3."res_guid"

        left join (
        select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name") "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position"
        from "SJZT_MD"."att_res_manage_person" where  "type"=4 GROUP BY "res_guid"
        select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name")
        "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position"
        from "SJZT_MD"."att_res_manage_person" where "type"=4 GROUP BY "res_guid"
        ) trmp4 on arb."guid" = trmp4."res_guid"

        left join (
        select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name") "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position"
        from "SJZT_MD"."att_res_manage_person" where  "type"=5 GROUP BY "res_guid"
        select "res_guid",WM_Concat("guid") "guid",WM_Concat("user_unit") "user_unit",WM_Concat("user_name")
        "user_name",WM_Concat("user_phone") "user_phone",WM_Concat("position") "position"
        from "SJZT_MD"."att_res_manage_person" where "type"=5 GROUP BY "res_guid"
        ) trmp5 on arb."guid" = trmp5."res_guid"
        where 1=1
        <if test="params.name !=null and params.name !=''">
@@ -122,46 +146,48 @@
        </if>
        <if test="params.isWorkable==1">
            AND (
                (
                (arb."eng_scal" = '大（1）型' or arb."eng_scal" = '大（2）型' or arb."eng_scal" = '中型')
                and
                trmp1."user_unit" is not null and trmp2."user_unit" is not null and trmp3."user_unit" is not null
                )
                or
                (
                (arb."eng_scal" = '小（1）型' or arb."eng_scal" = '小（2）型')
                and
                trmp1."user_unit" is not null and trmp2."user_unit" is not null and trmp3."user_unit" is not null and trmp4."user_unit" is not null and trmp5."user_unit" is not null
                )
            (
            (arb."eng_scal" = '大（1）型' or arb."eng_scal" = '大（2）型' or arb."eng_scal" = '中型')
            and
            trmp1."user_unit" is not null and trmp2."user_unit" is not null and trmp3."user_unit" is not null
            )
            or
            (
            (arb."eng_scal" = '小（1）型' or arb."eng_scal" = '小（2）型')
            and
            trmp1."user_unit" is not null and trmp2."user_unit" is not null and trmp3."user_unit" is not null and
            trmp4."user_unit" is not null and trmp5."user_unit" is not null
            )
            )
        </if>
        <if test="params.isWorkable==2">
            AND (
                (arb."eng_scal" = '大（1）型' or arb."eng_scal" = '大（2）型' or arb."eng_scal" = '中型')
                and
                (trmp1."user_unit" is null or trmp2."user_unit" is null or trmp3."user_unit" is null)
                or
                (
                (arb."eng_scal" = '小（1）型' or arb."eng_scal" = '小（2）型')
                and
                (trmp1."user_unit" is null or trmp2."user_unit" is null or trmp3."user_unit" is null or trmp4."user_unit" is null or trmp5."user_unit" is null)
                )
            (arb."eng_scal" = '大（1）型' or arb."eng_scal" = '大（2）型' or arb."eng_scal" = '中型')
            and
            (trmp1."user_unit" is null or trmp2."user_unit" is null or trmp3."user_unit" is null)
            or
            (
            (arb."eng_scal" = '小（1）型' or arb."eng_scal" = '小（2）型')
            and
            (trmp1."user_unit" is null or trmp2."user_unit" is null or trmp3."user_unit" is null or trmp4."user_unit" is
            null or trmp5."user_unit" is null)
            )
            )
        </if>

        <if test="params.adCode != null and params.adCode != '' ">
            and (aab."ad_code" = #{params.adCode} or aa."ad_code" = #{params.adCode} or ab."ad_code" = #{params.adCode} or ac."ad_code" = #{params.adCode})
            and (aab."ad_code" = #{params.adCode} or aa."ad_code" = #{params.adCode} or ab."ad_code" = #{params.adCode}
            or ac."ad_code" = #{params.adCode})
        </if>
    </select>

    <!--查询各类型责任人-->
    <select id="getUserByResBaseGuid" resultType="cn.gistack.sm.sjztmd.vo.ManagePersonVO">
        select
        trmp."type",
        trmp."user_name" as username,
        trmp."user_unit" as unitName,
        trmp."position",
        trmp."user_phone" as phone
        select trmp."type",
               trmp."user_name"  as username,
               trmp."user_unit"  as unitName,
               trmp."position",
               trmp."user_phone" as phone
        from "SJZT_MD"."att_res_manage_person" trmp
        where trmp."res_guid" = #{guid}
    </select>
@@ -177,7 +203,7 @@
        <if test="attResBase.label ==null or attResBase.label == ''">
            "label" = '',
        </if>
            "is_gate" = #{attResBase.isGate},
        "is_gate" = #{attResBase.isGate},
        "update_time" = #{attResBase.updateTime}
        WHERE "guid" = #{attResBase.guid}
    </update>
@@ -185,44 +211,49 @@

    <!--查询当前水库对应的市级编号和县区编号-->
    <select id="getCityCountyCodeByResGuid" resultType="cn.gistack.sm.sjztmd.vo.AttResBaseVO">
        SELECT
            case when b."ad_grad" = 3 THEN b."ad_code"
                 when c."ad_grad" = 3 THEN c."ad_code" END AS countyCode,
            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 cityCode
        SELECT case
                   when b."ad_grad" = 3 THEN b."ad_code"
                   when c."ad_grad" = 3 THEN c."ad_code" END AS countyCode,
               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 cityCode
        FROM sjzt_md."att_res_base" a
        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" 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"
        where a."guid" = #{guid}
    </select>

    <!--查询当前水库对应的市级编号和县区编号-->
    <select id="getWaterRegionInfoByResGuid" resultType="cn.gistack.sm.sjztmd.vo.AttResAdVO">
        SELECT
            a."guid" as resGuid,
            case when b."ad_grad" = 4 THEN b."ad_code" ELSE NULL END AS townCode,
            case when b."ad_grad" = 4 THEN b."ad_name" ELSE NULL END AS townName,
            case when b."ad_grad" = 3 THEN b."ad_code"
                 when c."ad_grad" = 3 THEN c."ad_code" END AS countyCode,
            case when b."ad_grad" = 3 THEN b."ad_name"
                 when c."ad_grad" = 3 THEN C."ad_name" END AS countyName,
            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 cityCode,
            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 cityName
        SELECT a."guid"                                                 as resGuid,
               case when b."ad_grad" = 4 THEN b."ad_code" ELSE NULL END AS townCode,
               case when b."ad_grad" = 4 THEN b."ad_name" ELSE NULL END AS townName,
               case
                   when b."ad_grad" = 3 THEN b."ad_code"
                   when c."ad_grad" = 3 THEN c."ad_code" END            AS countyCode,
               case
                   when b."ad_grad" = 3 THEN b."ad_name"
                   when c."ad_grad" = 3 THEN C."ad_name" END            AS countyName,
               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 cityCode,
               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 cityName
        FROM sjzt_md."att_res_base" a
        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" 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"
        where a."guid" = #{guid}
    </select>

    <select id="getDimResInfoACount" resultType="java.lang.Integer">
        select count(*) from sjzt_dw."dim_res_info_a"
        select count(*)
        from sjzt_dw."dim_res_info_a"
    </select>


@@ -238,18 +269,17 @@

    <!--根据水库名称、所在区域查询改水库的数量-->
    <select id="getWaterCountByResNameAndCountyName" resultType="java.lang.Integer">
        SELECT
            count(1)
        SELECT count(1)
        FROM sjzt_md."att_res_base" a
        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" 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"
        where a."name" = #{resName}
        and (
            (b."ad_grad" = 3 and b."ad_code" = #{countyCode})
            or
            (c."ad_grad" = 3 and c."ad_code" = #{countyCode})
        )
          and (
                (b."ad_grad" = 3 and b."ad_code" = #{countyCode})
                or
                (c."ad_grad" = 3 and c."ad_code" = #{countyCode})
            )
    </select>