From 916b76ee90fceff31fe7969db9d9da995d919d52 Mon Sep 17 00:00:00 2001
From: guoshilong <123456>
Date: Fri, 28 Jul 2023 17:05:26 +0800
Subject: [PATCH] 巡查报表接口修改
---
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/mapper/PatrolTaskMapper.java | 54 +----
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/mapper/PatrolTaskMapper.xml | 435 ++++++++++++++++++++++---------------------
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/service/impl/PatrolTaskServiceImpl.java | 61 +----
3 files changed, 248 insertions(+), 302 deletions(-)
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/mapper/PatrolTaskMapper.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/mapper/PatrolTaskMapper.java
index cc606e6..87fbb0d 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/mapper/PatrolTaskMapper.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/mapper/PatrolTaskMapper.java
@@ -58,12 +58,6 @@
PatrolTaskVO customizeGetById(@Param("id") String id);
- /**
- * 巡查报表，市拿区
- * @param patrolTaskReport
- * @return
- */
- List<PatrolTaskReport> getCountyReportPage(IPage<PatrolTaskReport> page,@Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);
/**
* 巡查报表，获取水库巡查报表数据
@@ -72,22 +66,6 @@
*/
List<PatrolTaskReport> getReservoirReportPage(IPage<PatrolTaskReport> page,@Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);
- /**
- * 巡查报表，获取市级巡查报表数据
- * @param page
- * @param patrolTaskReport
- * @return
- */
- List<PatrolTaskReport> getCityReportPage(IPage<PatrolTaskReport> page,@Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);
-
-
- /**
- * 巡查报表，获取乡镇巡查数据
- * @param page
- * @param patrolTaskReport
- * @return
- */
- List<PatrolTaskReport> getTownReportPage(IPage<PatrolTaskReport> page, @Param("patrolTaskReport")PatrolTaskReport patrolTaskReport);
/**
* 巡查报表详情 获取行政区下的水库
@@ -102,35 +80,23 @@
/**
- * 巡查报表，市拿区
- * @param patrolTaskReport
- * @return
- */
- List<PatrolTaskReport> getCountyReportList(@Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);
-
- /**
* 巡查报表，获取水库巡查报表数据
* @param patrolTaskReport
* @return
*/
List<PatrolTaskReport> getReservoirReportList(@Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);
- /**
- * 巡查报表，获取市级巡查报表数据
- * @param patrolTaskReport
- * @return
- */
- List<PatrolTaskReport> getCityReportList(@Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);
-
-
- /**
- * 巡查报表，获取乡镇巡查数据
- * @param patrolTaskReport
- * @return
- */
- List<PatrolTaskReport> getTownReportList( @Param("patrolTaskReport")PatrolTaskReport patrolTaskReport);
-
List<TaskStatusCount> getTaskStatusCount(@Param("vo") PatrolTaskVO patrolTaskVO);
List<TaskStatusCount> getTaskTypeCount(@Param("vo") PatrolTaskVO patrolTaskVO);
+
+ /**
+ * 获取省市区统计报表
+ * @param page
+ * @param patrolTaskReport
+ * @return
+ */
+ List<PatrolTaskReport> getRegionReportPage(IPage<PatrolTaskReport> page, @Param("patrolTaskReport") PatrolTaskReport patrolTaskReport);
+
+ List<PatrolTaskReport> getRegionReportList(@Param("patrolTaskReport")PatrolTaskReport patrolTaskReport);
}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/mapper/PatrolTaskMapper.xml b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/mapper/PatrolTaskMapper.xml
index 466c402..83a8a2d 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/mapper/PatrolTaskMapper.xml
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/mapper/PatrolTaskMapper.xml
@@ -200,225 +200,230 @@
WHERE T.ID = #{id}
AND T.IS_DELETED = 0
</select>
- <!--获取市级巡查数据-->
- <select id="getCityReportPage" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
- SELECT
- a."p_ad_code" adCode,
- a."p_ad_name" adName,
- sum(a.need) AS need,
- sum(a.finish) AS finish,
- sum(a.todo) AS todo,
- #{patrolTaskReport.startTime} AS startTime,
- #{patrolTaskReport.endTime} AS endTime
- FROM (
- SELECT
- aab."p_ad_code",aab."p_ad_name",
- COUNT(*) AS need,
- SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
- SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
- FROM SM_PATROL_TASK t
- LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
- LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = CONCAT(arb."ad_guid",'000000')
- where t.is_deleted = 0 AND aab."p_ad_code" != '420000000000'
- <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
- </if>
- <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
- </if>
- GROUP BY (aab."p_ad_code",aab."p_ad_name")
- ORDER BY aab."p_ad_code"
- ) A
- GROUP BY (a."p_ad_code",a."p_ad_name") order by a."p_ad_code"
+ <!--行政区统计分页-->
+ <select id="getRegionReportPage" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
+ select * from (
+ select "ad_name" AS adName ,"guid" AS adCode,"ad_grad","order_id", #{patrolTaskReport.startTime} AS startTime,#{patrolTaskReport.endTime} AS endTime,
+ (
+ select count(*) from YWXT.SM_PATROL_TASK spt
+ left join SJZT_MD."att_res_base" a on (spt.project_id = a."guid")
+ left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
+ 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" )
+ and spt.is_deleted = 0
+ <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
+ </if>
+ <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
+ </if>
+ ) need ,
+ (
+ select count(*) from YWXT.SM_PATROL_TASK spt
+ left join SJZT_MD."att_res_base" a on (spt.project_id = a."guid")
+ left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
+ 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" )
+ and spt.is_deleted = 0 and spt.state = 3
+ <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
+ </if>
+ <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
+ </if>
+ ) finish ,
+ (
+ select count(*) from YWXT.SM_PATROL_TASK spt
+ left join SJZT_MD."att_res_base" a on (spt.project_id = a."guid")
+ left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
+ 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" )
+ and spt.is_deleted = 0 and (spt.state = 1 or spt.state = 2)
+ <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
+ </if>
+ <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
+ </if>
+ ) todo ,
+ (
+ select count(*) from SJZT_MD."att_res_base" a
+ left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
+ 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" )
+ ) res_count
+ from SJZT_MD."att_ad_base" aab where
+ <choose>
+ <when test="patrolTaskReport.adCode != null and patrolTaskReport.adCode !='' ">
+ "p_ad_code" = #{patrolTaskReport.adCode}
+ </when>
+ <otherwise>
+ "ad_grad" = 2
+ </otherwise>
+ </choose>
+ )
+ where res_count >0 order by "order_id" asc
</select>
- <!--获取区县巡查数据-->
- <select id="getCountyReportPage" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
- SELECT
- aab."ad_name" AS adName,
- aab."guid" AS adCode,
- #{patrolTaskReport.startTime} AS startTime,
- #{patrolTaskReport.endTime} AS endTime,
- COUNT(*) AS need,
- SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
- SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
- FROM SM_PATROL_TASK t
- LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
- LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = CONCAT(arb."ad_guid",'000000')
- WHERE t.IS_DELETED = 0
- <if test="patrolTaskReport.padCode !=null and patrolTaskReport.padCode !=''">
- AND aab."p_ad_code" = #{patrolTaskReport.padCode}
- </if>
+
+ <!--行政区统计列表-->
+ <select id="getRegionReportList" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
+ select * from (
+ select "ad_name","ad_grad","order_id", #{patrolTaskReport.startTime} AS startTime,#{patrolTaskReport.endTime} AS endTime,
+ (
+ select count(*) from YWXT.SM_PATROL_TASK spt
+ left join SJZT_MD."att_res_base" a on (spt.project_id = a."guid")
+ left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
+ 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" )
+ and spt.is_deleted = 0
<if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
</if>
<if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
</if>
- GROUP BY (aab."ad_name",aab."guid")
- ORDER BY aab."guid"
- </select>
- <!--获取乡镇巡查数据-->
- <select id="getTownReportPage" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
- SELECT
- aab."ad_name" AS adName,
- aab."guid" AS adCode,
- #{patrolTaskReport.startTime} AS startTime,
- #{patrolTaskReport.endTime} AS endTime,
- COUNT(*) AS need,
- SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
- SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
- FROM SM_PATROL_TASK t
- LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
- LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
- WHERE t.IS_DELETED = 0
- <if test="patrolTaskReport.padCode !=null and patrolTaskReport.padCode !=''">
- AND aab."p_ad_code" = #{patrolTaskReport.padCode}
- </if>
+ ) need ,
+ (
+ select count(*) from YWXT.SM_PATROL_TASK spt
+ left join SJZT_MD."att_res_base" a on (spt.project_id = a."guid")
+ left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
+ 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" )
+ and spt.is_deleted = 0 and spt.state = 3
<if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
</if>
<if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
</if>
- GROUP BY (aab."ad_name",aab."guid")
- ORDER BY aab."guid"
+ ) finish ,
+ (
+ select count(*) from YWXT.SM_PATROL_TASK spt
+ left join SJZT_MD."att_res_base" a on (spt.project_id = a."guid")
+ left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
+ 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" )
+ and spt.is_deleted = 0 and (spt.state = 1 or spt.state = 2)
+ <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
+ </if>
+ <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
+ </if>
+ ) todo ,
+ (
+ select count(*) from SJZT_MD."att_res_base" a
+ left join SJZT_MD."att_ad_base" b on a."interior_ad_guid" = b."ad_code"
+ 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" )
+ ) res_count
+ from SJZT_MD."att_ad_base" aab where
+ <choose>
+ <when test="patrolTaskReport.adCode != null and patrolTaskReport.adCode !='' ">
+ "p_ad_code" = #{patrolTaskReport.adCode}
+ </when>
+ <otherwise>
+ "ad_grad" = 2
+ </otherwise>
+ </choose>
+ )
+ where res_count >0 order by "order_id" asc
</select>
+
+
+
+
+
+
<!--获取水库巡查数据-->
<select id="getReservoirReportPage" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
SELECT
- arb."guid" AS reservoirId,
- arb."name" AS reservoirName,
- aab."guid" AS adCode,
- aab."ad_name" AS adName,
- #{patrolTaskReport.startTime} AS startTime,
- #{patrolTaskReport.endTime} AS endTime,
- COUNT(*) AS need,
- SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
- SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
- FROM SM_PATROL_TASK t
- LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
- LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
- WHERE
- t.IS_DELETED = 0
- <if test="patrolTaskReport.adCode != null and patrolTaskReport.adCode !=''">
- AND aab."ad_code" = #{patrolTaskReport.adCode}
- </if>
+ arb."name" AS reservoirName,
+ arb."guid" AS reservoirId,
+ arb."interior_ad_guid" AS adCode,
+ aab."ad_name" AS adName,
+ #{patrolTaskReport.startTime} AS startTime,
+ #{patrolTaskReport.endTime} AS endTime,
+ (
+ select count(*) from YWXT.SM_PATROL_TASK spt
+ where arb."guid" = spt.project_id
+ and spt.is_deleted = 0
<if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
</if>
<if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
</if>
- GROUP BY (arb."guid",arb."name",aab."guid",aab."ad_name")
- ORDER BY arb."guid"
- </select>
-
-
-
- <!--获取市级巡查数据-->
- <select id="getCityReportList" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
- SELECT
- a."p_ad_code" adCode,
- a."p_ad_name" adName,
- sum(a.need) AS need,
- sum(a.finish) AS finish,
- sum(a.todo) AS todo,
- #{patrolTaskReport.startTime} AS startTime,
- #{patrolTaskReport.endTime} AS endTime
- FROM (
- SELECT
- aab."p_ad_code",aab."p_ad_name",
- COUNT(*) AS need,
- SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
- SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
- FROM SM_PATROL_TASK t
- LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
- LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = CONCAT(arb."ad_guid",'000000')
- where t.is_deleted = 0 AND aab."p_ad_code" != '420000000000'
+ ) need,
+ (
+ select count(*) from YWXT.SM_PATROL_TASK spt
+ where arb."guid" = spt.project_id
+ and spt.is_deleted = 0 and spt.state = 3
<if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
</if>
<if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
</if>
- GROUP BY (aab."p_ad_code",aab."p_ad_name")
- ORDER BY aab."p_ad_code"
- ) A
- GROUP BY (a."p_ad_code",a."p_ad_name") order by a."p_ad_code"
+ ) finish,
+ (
+ select count(*) from YWXT.SM_PATROL_TASK spt
+ where arb."guid" = spt.project_id
+ and spt.is_deleted = 0 and (spt.state = 1 or spt.state =2)
+ <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
+ </if>
+ <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
+ AND DATE_FORMAT(spt.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
+ </if>
+ ) todo
+ FROM SJZT_MD."att_res_base" arb
+ LEFT JOIN SJZT_MD."att_ad_base" aab ON aab."guid" = arb."interior_ad_guid"
+ WHERE "is_deleted" = 0
+ <if test="patrolTaskReport.adCode != null and patrolTaskReport.adCode !='' ">
+ AND arb."interior_ad_guid" = #{patrolTaskReport.adCode}
+ </if>
</select>
- <!--获取区县巡查数据-->
- <select id="getCountyReportList" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
- SELECT
- aab."ad_name" AS adName,
- aab."guid" AS adCode,
- #{patrolTaskReport.startTime} AS startTime,
- #{patrolTaskReport.endTime} AS endTime,
- COUNT(*) AS need,
- SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
- SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
- FROM SM_PATROL_TASK t
- LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
- LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = CONCAT(arb."ad_guid",'000000')
- WHERE t.IS_DELETED = 0
- <if test="patrolTaskReport.padCode !=null and patrolTaskReport.padCode !=''">
- AND aab."p_ad_code" = #{patrolTaskReport.padCode}
- </if>
- <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
- </if>
- <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
- </if>
- GROUP BY (aab."ad_name",aab."guid")
- ORDER BY aab."guid"
- </select>
- <!--获取乡镇巡查数据-->
- <select id="getTownReportList" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
- SELECT
- aab."ad_name" AS adName,
- aab."guid" AS adCode,
- #{patrolTaskReport.startTime} AS startTime,
- #{patrolTaskReport.endTime} AS endTime,
- COUNT(*) AS need,
- SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
- SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
- FROM SM_PATROL_TASK t
- LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
- LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
- WHERE t.IS_DELETED = 0
- <if test="patrolTaskReport.padCode !=null and patrolTaskReport.padCode !=''">
- AND aab."p_ad_code" = #{patrolTaskReport.padCode}
- </if>
- <if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
- </if>
- <if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
- AND DATE_FORMAT(t.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
- </if>
- GROUP BY (aab."ad_name",aab."guid")
- ORDER BY aab."guid"
- </select>
+
<!--获取水库巡查数据-->
<select id="getReservoirReportList" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
SELECT
- arb."guid" AS reservoirId,
arb."name" AS reservoirName,
- aab."guid" AS adCode,
+ arb."guid" AS reservoirId,
+ arb."interior_ad_guid" AS adCode,
aab."ad_name" AS adName,
#{patrolTaskReport.startTime} AS startTime,
#{patrolTaskReport.endTime} AS endTime,
- COUNT(*) AS need,
- SUM(CASE WHEN t.state = 3 THEN 1 ELSE 0 END) AS finish,
- SUM(CASE WHEN t.state = 1 or state = 2 THEN 1 ELSE 0 END) AS todo
- FROM SM_PATROL_TASK t
- LEFT JOIN SJZT_MD."att_res_base" arb on arb."guid" = t.project_id
- LEFT JOIN SJZT_MD."att_ad_base" aab on aab."guid" = arb."interior_ad_guid"
- WHERE
- t.IS_DELETED = 0
- <if test="patrolTaskReport.adCode != null and patrolTaskReport.adCode !=''">
- AND aab."ad_code" = #{patrolTaskReport.adCode}
+ (
+ select count(*) from YWXT.SM_PATROL_TASK spt
+ where arb."guid" = spt.project_id
+ and spt.is_deleted = 0
+ ) need,
+ (
+ select count(*) from YWXT.SM_PATROL_TASK spt
+ where arb."guid" = spt.project_id
+ and spt.is_deleted = 0 and spt.state = 3
+ ) finish,
+ (
+ select count(*) from YWXT.SM_PATROL_TASK spt
+ where arb."guid" = spt.project_id
+ and spt.is_deleted = 0 and (spt.state = 1 or spt.state =2)
+ ) todo
+ FROM SJZT_MD."att_res_base" arb
+ LEFT JOIN SJZT_MD."att_ad_base" aab ON aab."guid" = arb."interior_ad_guid"
+ WHERE "is_deleted" = 0
+ <if test="patrolTaskReport.adCode != null and patrolTaskReport.adCode !='' ">
+ AND "interior_ad_guid" = #{patrolTaskReport.adCode}
</if>
<if test="patrolTaskReport.startTime !=null and patrolTaskReport.startTime !=''">
AND DATE_FORMAT(t.start_time,'%Y-%m-%d') >= #{patrolTaskReport.startTime}
@@ -426,8 +431,6 @@
<if test="patrolTaskReport.endTime !=null and patrolTaskReport.endTime !=''">
AND DATE_FORMAT(t.start_time,'%Y-%m-%d') <= #{patrolTaskReport.endTime}
</if>
- GROUP BY (arb."guid",arb."name",aab."guid",aab."ad_name")
- ORDER BY arb."guid"
</select>
@@ -438,27 +441,30 @@
<!--水库巡查-->
<select id="getTaskReportDetailRegion" resultType="cn.gistack.sm.patrol.vo.PatrolTaskReport">
SELECT
- arb."guid" AS reservoirId,
- arb."name" AS reservoirName,
- aab."ad_name" AS adName,
- aab."guid" AS adCode,
- t.start_time,
- u.real_name patrolUserName,
- t.state,
- (
- CASE t.state
- WHEN '1' THEN '未完成'
- WHEN '2' THEN '进行中'
- ELSE '已完成'
- END
- )stateName
- FROM SM_PATROL_TASK T
- LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = t.project_id
- LEFT JOIN SJZT_MD."att_ad_base" aab ON aab."guid" = CONCAT(arb."ad_guid",'000000')
- LEFT JOIN BLADE_USER u ON u.id = t.to_user_id
+ arb."guid" AS reservoirId,
+ arb."name" AS reservoirName,
+ aab."ad_name" AS adName,
+ aab."guid" AS adCode,
+ t.start_time,
+ u.real_name patrolUserName,
+ t.state,
+ (
+ CASE t.state
+ WHEN '1' THEN '未完成'
+ WHEN '2' THEN '进行中'
+ ELSE '已完成'
+ END
+ )stateName
+ FROM YWXT.SM_PATROL_TASK T
+ LEFT JOIN BLADE_USER u ON u.id = t.to_user_id
+ LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = t.project_id
+ 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"
WHERE T.IS_DELETED = 0
<if test="patrolTaskReport.adCode !=null and patrolTaskReport.adCode !=''">
- AND aab."guid" LIKE CONCAT('%',#{patrolTaskReport.adCode},'%')
+ and (aab."ad_code" =#{patrolTaskReport.adCode} or aa."ad_code" = #{patrolTaskReport.adCode} or ab."ad_code" = #{patrolTaskReport.adCode} or ac."ad_code" = #{patrolTaskReport.adCode})
</if>
<if test="patrolTaskReport.reservoirName !=null and patrolTaskReport.reservoirName !=''">
AND arb."name" LIKE CONCAT('%',#{patrolTaskReport.reservoirName},'%')
@@ -472,12 +478,15 @@
<if test="patrolTaskReport.reservoirId !=null and patrolTaskReport.reservoirId !=''">
AND arb."guid" = #{patrolTaskReport.reservoirId}
</if>
+ <if test="patrolTaskReport.state != null and patrolTaskReport.state !='' ">
+ and t.state = #{patrolTaskReport.state}
+ </if>
</select>
<select id="getTaskStatusCount" resultType="cn.gistack.sm.patrol.vo.TaskStatusCount">
- SELECT '未完成' AS name,'1' AS value , count(*) as taskCount FROM YWXT.SM_PATROL_TASK T2
+ SELECT '未完成' AS name,'1' AS value , count(*) as taskCount FROM SM_PATROL_TASK T2
LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = T2.project_id
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"
@@ -497,7 +506,7 @@
AND T2.reservoir_name LIKE CONCAT('%',#{vo.reservoirName},'%')
</if>
UNION ALL
- SELECT '进行中' AS name,'2' AS value , count(*) as taskCount FROM YWXT.SM_PATROL_TASK T3
+ SELECT '进行中' AS name,'2' AS value , count(*) as taskCount FROM SM_PATROL_TASK T3
LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = T3.project_id
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"
@@ -517,7 +526,7 @@
AND T3.reservoir_name LIKE CONCAT('%',#{vo.reservoirName},'%')
</if>
UNION ALL
- SELECT '已完成' AS name,'3' AS value , count(*) as taskCount FROM YWXT.SM_PATROL_TASK T4
+ SELECT '已完成' AS name,'3' AS value , count(*) as taskCount FROM SM_PATROL_TASK T4
LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = T4.project_id
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"
@@ -538,7 +547,7 @@
</if>
UNION ALL
SELECT '全部' AS name,'' AS value , count(*) as taskCount
- FROM YWXT.SM_PATROL_TASK T1
+ FROM SM_PATROL_TASK T1
LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = T1.project_id
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"
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/service/impl/PatrolTaskServiceImpl.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/service/impl/PatrolTaskServiceImpl.java
index d5aa954..c3120e2 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/service/impl/PatrolTaskServiceImpl.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/patrol/service/impl/PatrolTaskServiceImpl.java
@@ -215,23 +215,14 @@
@Override
public IPage<PatrolTaskReport> getTaskReportPage(IPage<PatrolTaskReport> page,PatrolTaskReport patrolTaskReport) {
- List<PatrolTaskReport> list = new ArrayList();
+ List<PatrolTaskReport> list = new ArrayList<>();
- switch (patrolTaskReport.getRegionFlag()){
- case "1": //省 获取市级数据
- list = baseMapper.getCityReportPage(page,patrolTaskReport);
- break;
- case "2"://市 获取区县级数据
- list = baseMapper.getCountyReportPage(page,patrolTaskReport);
- break;
- case "3"://区县 获取乡镇级数据
- list = baseMapper.getTownReportPage(page,patrolTaskReport);
- break;
- case "4"://乡镇 获取水库数据
- list = baseMapper.getReservoirReportPage(page,patrolTaskReport);
- break;
- default:
- break;
+ //获取行政区划统计报表
+ if (patrolTaskReport.getRegionFlag().equals("1")){
+ list = baseMapper.getRegionReportPage(page,patrolTaskReport);
+ }else {
+ //获取水库巡查报表
+ list = baseMapper.getReservoirReportPage(page,patrolTaskReport);
}
return page.setRecords(list);
}
@@ -240,21 +231,12 @@
public List<PatrolTaskReport> getTaskReportList(PatrolTaskReport patrolTaskReport) {
List<PatrolTaskReport> list = new ArrayList();
- switch (patrolTaskReport.getRegionFlag()){
- case "1": //省 获取市级数据
- list = baseMapper.getCityReportList(patrolTaskReport);
- break;
- case "2"://市 获取区县级数据
- list = baseMapper.getCountyReportList(patrolTaskReport);
- break;
- case "3"://区县 获取乡镇级数据
- list = baseMapper.getTownReportList(patrolTaskReport);
- break;
- case "4"://乡镇 获取水库数据
- list = baseMapper.getReservoirReportList(patrolTaskReport);
- break;
- default:
- break;
+ //获取行政区划统计报表
+ if (patrolTaskReport.getRegionFlag().equals("1")){
+ list = baseMapper.getRegionReportList(patrolTaskReport);
+ }else {
+ //获取水库巡查报表
+ list = baseMapper.getReservoirReportList(patrolTaskReport);
}
return list;
}
@@ -273,20 +255,9 @@
public IPage<PatrolTaskReport> getTaskReportDetail(IPage<PatrolTaskReport> page, PatrolTaskReport patrolTaskReport) {
List<PatrolTaskReport> list = new ArrayList();
- if (Func.isNotEmpty(patrolTaskReport.getReservoirId())){
- //获取该水库的巡查
- list = baseMapper.getTaskReportDetailRegion(page,patrolTaskReport);
- }else {
- String code = patrolTaskReport.getAdCode();
- if (code.substring(0,4).indexOf("00") > -1) {
- //前4位包含00 =》省
- patrolTaskReport.setAdCode(code.substring(0,2));
- } else if (code.substring(0,6).indexOf("00") > -1) {
- //前六位包含00 =》市
- patrolTaskReport.setAdCode(code.substring(0,4));
- }
- list = baseMapper.getTaskReportDetailRegion(page,patrolTaskReport);
- }
+
+ list = baseMapper.getTaskReportDetailRegion(page,patrolTaskReport);
+
return page.setRecords(list);
}
--
Gitblit v1.9.3