hbwri/skjcmanager.git

			@@ -28,39 +28,6 @@

			</select>
			<select id="getCountyStatisticsBar" resultType="cn.gistack.sm.sjztmd.statisticsVO.StatisticsBar">

			<!-- SELECT-->
			<!-- trd."guid" as code,-->
			<!-- trd."name" as name,-->
			<!-- trd."dyke_length",-->
			<!-- CASE WHEN dykeTmp.termiteLength THEN dykeTmp.termiteLength ELSE 0 END as termiteLength-->
			<!-- FROM-->
			<!-- sjzt_md."tb_res_dyke" trd-->
			<!-- LEFT JOIN SJZT_MD."tb_dyke_investigation_state" tdis ON tdis."dyke_guid" = trd."guid" AND (tdis."check_state" = 1 or tdis."check_state" = 2)-->
			<!-- LEFT JOIN (-->
			<!-- SELECT-->
			<!-- TEMP1.*,-->
			<!-- TEMP1.dangerLength/temp1.num as termiteLength-->
			<!-- 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-->
			<!-- ) dykeTmp on dykeTmp."tb_state_id" = tdis."guid"-->
			<!-- where 1=1-->
			<!-- <if test="params.adCode != null and params.adCode != ''">-->
			<!-- AND trd."ad_code" LIKE #{params.adCode}-->
			<!-- </if>-->



			SELECT
			aab."guid" code,
			aab."ad_name" name,

			@@ -135,11 +135,11 @@
			tps.curMonthActualFund,
			project.totalActualFund,

			resNum,
			bigNum,
			midNum,
			smallOneNum,
			smallTwoNum,
			ifNull(resNum,0) resNum,
			ifNull(bigNum,0) bigNum,
			ifNull(midNum,0) midNum,
			ifNull(smallOneNum,0) smallOneNum,
			ifNull(smallTwoNum,0) smallTwoNum,

			dykeLength,
			levelOneLength,
			@@ -162,6 +162,12 @@
			(
			<include refid="selectResByRegion"/>
			) arb ON ((arb.townCode = aab."guid")or(arb.countyCode = aab."guid") or (arb.cityCode = aab."guid") or (arb.provinceCode = aab."guid") )
			right join sjzt_md."tb_res_general_investigation_state" trgis on trgis."res_guid" = arb."code"
			<!--统计待审核和已审核的-->
			and (trgis."check_state" = 1 or trgis."check_state" = 2)
			<if test="params.tbYear != null and params.tbYear != ''">
			and trgis."tb_year" = #{params.tbYear}
			</if>
			group by aab."guid",aab."ad_name"
			)res on res."guid" = aab."guid"
			left join (
			@@ -169,30 +175,59 @@
			aab."guid",
			aab."ad_name",


			SUM(CASE WHEN trd."guid" is not null then trd."dyke_length" else 0 END) as dykeLength,
			SUM(CASE WHEN trd."dyke_level" = '1级' THEN trd."dyke_length" ELSE 0 END) AS levelOneLength,
			SUM(CASE WHEN trd."dyke_level" = '2级' THEN trd."dyke_length" ELSE 0 END) AS levelTwoLength,
			SUM(CASE WHEN trd."dyke_level" = '3级' THEN trd."dyke_length" ELSE 0 END)AS levelThreeLength,
			SUM(CASE WHEN trd."dyke_level" = '4级' THEN trd."dyke_length" ELSE 0 END) AS levelFourLength,
			SUM(CASE WHEN trd."dyke_level" = '5级' THEN trd."dyke_length" ELSE 0 END) AS levelFiveLength
			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,
			SUM(CASE WHEN trd."dyke_level" = '3级' THEN trd.dangerLength ELSE 0 END)AS levelThreeLength,
			SUM(CASE WHEN trd."dyke_level" = '4级' THEN trd.dangerLength ELSE 0 END) AS levelFourLength,
			SUM(CASE WHEN trd."dyke_level" = '5级' THEN trd.dangerLength ELSE 0 END) AS levelFiveLength
			FROM YWXT."att_ad_base" aab

			LEFT JOIN (

			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 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"
			)trd on ((trd.countyCode = aab."guid") or (trd.cityCode = aab."guid") or (trd.provinceCode = aab."guid"))
			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

			AND <if test="params.tbYear != null and params.tbYear != ''">
			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"
			) 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"
			)dyke on dyke."guid" = aab."guid"

	skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbDykeInvestigationMapper.xml	33 ●●●●● patch \| view \| raw \| blame \| history
	skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbProjectMapper.xml	75 ●●●●● patch \| view \| raw \| blame \| history