skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml
@@ -110,8 +110,6 @@ left join sjzt_md."att_ad_base" adc on (adc."guid" = adb."p_ad_code") left join sjzt_md."att_ad_base" addd on (addd."guid" = adc."p_ad_code") where res."interior_ad_guid" = aab."guid" or ada."guid" = aab."guid" or adb."guid" = aab."guid" or adc."guid" = aab."guid" or addd."guid" = aab."guid" ) as errorNum, ( SELECT COUNT(*) FROM ( @@ -231,7 +229,6 @@ OR b."ad_code" = aab."guid" ) ) ) allNum FROM SJZT_MD."att_ad_base" aab where <choose> @@ -244,21 +241,14 @@ </choose> ) )TMP </select> <select id="listRegionCount" resultType="cn.gistack.sm.damSecurity.vo.DamSecurityStatistics"> SELECT allNum,errorNum, allNum-errorNum normalNum,adName,adCode FROM ( SELECT ( SELECT COUNT(*) FROM @@ -339,7 +329,6 @@ or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" ) ) ) allNum, ( SELECT COUNT(*)FROM (SELECT distinct DAM_ID FROM NKY_ALARM_GET NAG LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = NAG.DAM_ID @@ -347,14 +336,12 @@ left join SJZT_MD."att_ad_base" c on b."p_ad_code" = c."ad_code" left join SJZT_MD."att_ad_base" d on c."p_ad_code" = d."ad_code" where (d."ad_code" = aab."guid" or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" ) <if test="dam.startTime != null and dam.startTime != ''"> AND time >= #{dam.startTime} </if> <if test="dam.endTime != null and dam.endTime !='' "> AND time <= #{dam.endTime} </if> )) errorNum, aab."ad_name" adName , aab."guid" adCode from SJZT_MD."att_ad_base" aab where @@ -367,7 +354,6 @@ </otherwise> </choose> ) </select> <select id="listRegionCountOptimize" resultType="cn.gistack.sm.damSecurity.vo.DamSecurityStatistics"> select "ad_code","ad_name",allNum,errorNum,allNum-errorNum normalNum from ( @@ -385,8 +371,6 @@ left join sjzt_md."att_ad_base" adc on (adc."guid" = adb."p_ad_code") left join sjzt_md."att_ad_base" addd on (addd."guid" = adc."p_ad_code") where res."interior_ad_guid" = aab."guid" or ada."guid" = aab."guid" or adb."guid" = aab."guid" or adc."guid" = aab."guid" or addd."guid" = aab."guid" ) as errorNum, ( SELECT COUNT(*) FROM ( @@ -506,7 +490,6 @@ OR b."ad_code" = aab."guid" ) ) ) allNum FROM SJZT_MD."att_ad_base" aab where <choose> @@ -818,7 +801,6 @@ <select id="tableResCountPageOptimize" resultType="cn.gistack.sm.damSecurity.vo.DamStationStatistics"> SELECT info."guid" AS resCd, info."res_reg_code" as resRegCode, @@ -863,30 +845,22 @@ d."order_id" ELSE e."order_id" END AS "province_order_id" FROM sjzt_md."att_res_base" a LEFT JOIN ( SELECT distinct (dri."res_cd") FROM sjzt_dw."dim_res_info_a" dri LEFT JOIN sjzt_ods."dsm_spg_spprmp" dss ON dri."oth_reg_cd" = dss."rscd" where dss."rscd" is not null ) f ON a."guid" = f."res_cd" LEFT JOIN ( SELECT distinct(dri."res_cd") from sjzt_dw."dim_res_info_a" dri left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd" where dss."rscd" is not null ) G on a."guid" = g."res_cd" LEFT JOIN ( SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb where awcb."cd" != '00' ) h ON a."guid" = h."res_cd" 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" @@ -896,23 +870,15 @@ <if test="dam.adCode != null and dam.adCode !='' "> AND (c."ad_code" = #{dam.adCode} or d."ad_code" =#{dam.adCode}or e."ad_code" = #{dam.adCode}) </if> ) baseRes LEFT JOIN ( select res."res_cd",count(*) wyStation from( SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb where awcb."cd" != '00' )res LEFT JOIN SJZT_MD."att_wy_cd_base" acb ON acb."res_cd" = res."res_cd" where acb."cd_type" = 'wy' group by res."res_cd" ) wy ON wy."res_cd" = baseRes."guid" LEFT JOIN( select res."res_cd",count(*) syStation from( SELECT distinct (dri."res_cd") @@ -922,40 +888,27 @@ where dss."rscd" is not null )res LEFT JOIN SJZT_MD."att_cd_base" acb ON acb."res_cd" = res."res_cd" where acb."cd_type" = 'sy' group by res."res_cd" ) sy ON sy."res_cd" = baseRes."guid" LEFT JOIN ( select res."res_cd",count(*) slStation from( SELECT distinct(dri."res_cd") from sjzt_dw."dim_res_info_a" dri left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd" where dss."rscd" is not null )res LEFT JOIN SJZT_MD."att_cd_base" acb ON acb."res_cd" = res."res_cd" where acb."cd_type" = 'sl' group by res."res_cd" ) sl ON sl."res_cd" = baseRes."guid" LEFT JOIN ( SELECT DAM_ID ,COUNT(*) errorNum FROM NKY_ALARM_GET WHERE 1=1 <if test="dam.startTime != null and dam.startTime != ''"> AND time >= #{dam.startTime} </if> <if test="dam.endTime != null and dam.endTime !='' "> AND time <= #{dam.endTime} </if> GROUP BY "DAM_ID" ) alarm ON alarm.DAM_ID = baseRes."guid" LEFT JOIN ( SELECT DAM_ID ,COUNT(*) errorCountNum FROM ( @@ -969,12 +922,7 @@ </if> GROUP BY DAM_ID,POINT_ID ) baseCount GROUP BY DAM_ID) c1 ON c1.dam_id = baseRes."guid" WHERE 1=1 <!--这里加条件--> <if test="dam.resName != null and dam.resName !='' "> AND "name" LIKE CONCAT('%',#{dam.resName},'%') </if> skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/message/mapper/MessageRecordMapper.xml
@@ -26,12 +26,9 @@ <if test="messageRecord.source!=null and messageRecord.source!=''"> and source = #{messageRecord.source} </if> <if test="messageRecord.createTime != null"> AND DATE_FORMAT(create_time,'%Y-%m-%d') = DATE_FORMAT(#{messageRecord.createTime},'%Y-%m-%d') </if> ORDER BY ID DESC </select>
@@ -133,16 +133,13 @@ aab."ad_name" adName, paab."guid" padCode, paab."ad_name" padName, tps.curMonthActualFund, project.totalActualFund, ifNull(resNum,0) resNum, ifNull(bigNum,0) bigNum, ifNull(midNum,0) midNum, ifNull(smallOneNum,0) smallOneNum, ifNull(smallTwoNum,0) smallTwoNum, dykeLength, levelOneLength, levelTwoLength, @@ -176,7 +173,6 @@ SELECT aab."guid", aab."ad_name", SUM(CASE WHEN trd."dyke_guid" is not null then trd.dangerLength else 0 END) as dykeLength, SUM(CASE WHEN trd."dyke_level" = '1级' THEN trd.dangerLength ELSE 0 END) AS levelOneLength, SUM(CASE WHEN trd."dyke_level" = '2级' THEN trd.dangerLength ELSE 0 END) AS levelTwoLength, @@ -218,8 +214,6 @@ 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> @@ -235,7 +229,6 @@ )trd on ((trd.countyCode = aab."guid") or (trd.cityCode = aab."guid") or (trd.provinceCode = aab."guid")) group by aab."guid",aab."ad_name" )dyke on dyke."guid" = aab."guid" left join ( SELECT aab."guid", @@ -248,20 +241,17 @@ tb."county_code" countyCode, province."guid" provinceCode, tps."actual_fund" from sjzt_md."tb_project" tb LEFT JOIN SJZT_MD."tb_project_schedule" tps on tps."tb_project_id" = tb."guid" LEFT JOIN YWXT."att_ad_base" province ON province."ad_code" = tb."city_code" )tb on ((tb.countyCode = aab."guid") or (tb.cityCode = aab."guid") or (tb.provinceCode = aab."guid")) group by aab."guid",aab."ad_name" ) project on project."guid" = aab."guid" left join ( SELECT aab."guid", aab."ad_name", tb."actual_fund" curMonthActualFund FROM YWXT."att_ad_base" aab LEFT JOIN ( select @@ -269,7 +259,6 @@ tb."county_code" countyCode, province."guid" provinceCode, tps."actual_fund" from sjzt_md."tb_project" tb LEFT JOIN SJZT_MD."tb_project_schedule" tps on tps."tb_project_id" = tb."guid" LEFT JOIN YWXT."att_ad_base" province ON province."ad_code" = tb."city_code" @@ -283,7 +272,6 @@ </if> )tb on ((tb.countyCode = aab."guid") or (tb.cityCode = aab."guid") or (tb.provinceCode = aab."guid")) ) tps on tps."guid" = aab."guid" left join ywxt."att_ad_base" paab on paab."guid" = aab."p_ad_code" where 1=1 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResDykeMapper.xml
@@ -61,30 +61,21 @@ <!-- 堤防普查列表 --> <select id="findTbResDykePcBy" resultType="cn.gistack.sm.sjztmd.vo.TbResDykeVO"> SELECT base.* from ( select trd.*, tdis."check_state" AS tb_state, tdis."guid" as tbStateId, aa."p_ad_code" as pAdCode, aa."p_ad_name" as pAdName, (CASE WHEN tmp.totalDangerNum>0 THEN 1 ELSE 2 END) as hasDanger, tmp.totalDangerNum, tmp.termiteNum, tmp.otherNum from sjzt_md."tb_res_dyke" trd left join "SJZT_MD"."tb_dyke_investigation_state" tdis on trd."guid" = tdis."dyke_guid" left join YWXT."att_ad_base" aa on aa."guid" = trd."ad_code" left join ( select temp2."tb_state_id", @@ -117,7 +108,6 @@ temp2.totalDangerNum, temp2.termiteNum, temp2.otherNum ) tmp on tmp."tb_state_id" = tdis."guid" where 1 = 1 <if test="vo.name !=null and vo.name !=''"> @@ -138,7 +128,5 @@ <if test="vo.hasDanger != null and vo.hasDanger != '' "> and base.hasDanger = #{vo.hasDanger} </if> </select> </mapper> skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResGeneralInvestigationMapper.xml
@@ -387,7 +387,6 @@ order by aab."guid" asc </select> <select id="getStatisticsTable" resultType="cn.gistack.sm.sjztmd.statisticsVO.StatisticsTable"> SELECT aab."guid" adCode, aab."ad_name" adName, @@ -402,7 +401,6 @@ (town."ad_code" = aab."guid" or county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid") ) ) resNum, ( SELECT COUNT(*) FROM SJZT_MD."tb_res_general_investigation_state" trgis @@ -411,16 +409,13 @@ LEFT JOIN YWXT."att_ad_base" county ON county."ad_code" = town."p_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" where 1=1 and (town."ad_code" = aab."guid" or county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid") <if test="params.tbYear != null and params.tbYear !=''"> and trgis."tb_year" = #{params.tbYear} </if> and trgis."check_state" = 1 )investigationResNum, ( SELECT COUNT(*) FROM SJZT_MD."tb_res_general_investigation_state" trgis @@ -429,17 +424,13 @@ LEFT JOIN YWXT."att_ad_base" county ON county."ad_code" = town."p_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" where 1=1 and (town."ad_code" = aab."guid" or county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid") <if test="params.tbYear != null and params.tbYear !=''"> and trgis."tb_year" = #{params.tbYear} </if> and trgis."check_state" = 2 )checkResNum, ( SELECT COUNT(*) FROM SJZT_MD."tb_res_dyke" trd @@ -449,7 +440,6 @@ where 1=1 and ( county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid") )dykeNum, ( SELECT COUNT(*) FROM SJZT_MD."tb_dyke_investigation_state" tdis @@ -457,7 +447,6 @@ 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" where 1=1 and (county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid") <if test="params.tbYear != null and params.tbYear !=''"> @@ -465,7 +454,6 @@ </if> and tdis."check_state" = 1 )investigationDykeNum, ( SELECT COUNT(*) FROM SJZT_MD."tb_dyke_investigation_state" tdis @@ -473,7 +461,6 @@ 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" where 1=1 and (county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid") <if test="params.tbYear != null and params.tbYear !=''"> @@ -481,8 +468,6 @@ </if> and tdis."check_state" = 2 )checkDykeNum, IFNULL( ( SELECT @@ -493,8 +478,6 @@ LEFT JOIN ywxt."att_ad_base" province on province."ad_code" = city."p_ad_code" where (county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid") ),0) AS dykeTotalLength, IFNULL(( SELECT SUM(CASE WHEN trd.dangerLength THEN trd.dangerLength ELSE 0 END) @@ -525,7 +508,6 @@ <if test="params.tbYear != null and params.tbYear != ''"> AND tdi."tb_year" = #{params.tbYear} </if> group by tdi."tb_state_id",tdi."dyke_guid",tdi."unit" )TEMP1 ) tdi on tdi."tb_state_id" = tdis."guid" @@ -536,9 +518,6 @@ LEFT JOIN YWXT."att_ad_base" province ON province."ad_code" = city."p_ad_code" where (county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid") ),0) AS dangerTotalLength FROM YWXT."att_ad_base" aab LEFT JOIN YWXT."att_ad_base" paab ON paab."guid" = aab."p_ad_code" where
