From 0dfd475fe431d0d67339711f9b4a7b9a90e46de7 Mon Sep 17 00:00:00 2001
From: xieb <vip_xiaobin810@163.com>
Date: Wed, 06 Mar 2024 14:57:06 +0800
Subject: [PATCH] 水库列表增加水库编码、工程规模筛选
---
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/AttResBaseMapper.xml | 222 +++++++++++++++++++++++++++++++------------------------
1 files changed, 126 insertions(+), 96 deletions(-)
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/AttResBaseMapper.xml b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/AttResBaseMapper.xml
index 024122d..26450b0 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/AttResBaseMapper.xml
+++ b/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>
--
Gitblit v1.9.3