From 658c3d5749673d68de17ff86a5ee2ea994e09e02 Mon Sep 17 00:00:00 2001
From: zhongrj <646384940@qq.com>
Date: Thu, 19 Oct 2023 21:36:18 +0800
Subject: [PATCH] 导出查询调整
---
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/XsfsQktjServiceImpl.java | 2
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.xml | 79 ++++++++++++++++++++++++-
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/vo/SkxsQkTjVO.java | 17 +++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/XsfsQktjMapper.java | 7 ++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/XsfsQktjMapper.xml | 12 +++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.java | 18 +++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/SkxsQkTjServiceImpl.java | 12 ++-
7 files changed, 132 insertions(+), 15 deletions(-)
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.java
index 7d5d6e2..a1f240a 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.java
@@ -4,6 +4,7 @@
import cn.gistack.sm.sjztods.excel.SkxsQkTjExcel;
import cn.gistack.sm.sjztods.vo.SkxsQkTjVO;
import com.baomidou.dynamic.datasource.annotation.DS;
+import com.baomidou.mybatisplus.annotation.InterceptorIgnore;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.ibatis.annotations.Param;
@@ -33,12 +34,27 @@
* @param skxsQkTj
* @return
*/
- Object getStatisticList(@Param("skxsQkTj") SkxsQkTjVO skxsQkTj);
+ List<SkxsQkTjVO> getStatisticList(@Param("skxsQkTj") SkxsQkTjVO skxsQkTj);
/**
* 查询导出数据
* @param skxsQkTj
* @return
*/
+ @InterceptorIgnore(tenantLine = "true")
List<SkxsQkTjExcel> exportSkxsQkTjList(@Param("skxsQkTj") SkxsQkTjVO skxsQkTj);
+
+ /**
+ * 通过adCode 获取adGrad
+ * @param adCode
+ * @return
+ */
+ String getAdGradByAdCode(@Param("adCode") String adCode);
+
+ /**
+ * 查询详情数据
+ * @param skxsQkTjExcel
+ * @return
+ */
+ SkxsQkTj selectSkxsQkTj(@Param("skxsQkTjExcel") SkxsQkTjExcel skxsQkTjExcel);
}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.xml b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.xml
index 83521ba..7da8d4d 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.xml
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.xml
@@ -27,20 +27,76 @@
left join SJZT_MD."att_ad_base" d ON c."p_ad_code" = d."guid"
where 1=1
<if test="skxsQkTj.statisticTime!=null">
- and date_format(sqt."statistic_time","%Y-%m-%d") = #{skxsQkTj.statisticTime}
+ and DATE_FORMAT(sqt."statistic_time",'%Y-%m-%d') = #{skxsQkTj.statisticTime}
</if>
<if test="skxsQkTj.resName!=null and skxsQkTj.resName!=''">
and sqt."res_name" like concat('%',#{skxsQkTj.resName},'%')
</if>
) x
- <if test="skxsQkTj.adCode!=null and skxsQkTj.adCode!=''">
+ <if test="skxsQkTj.adCode!=null and skxsQkTj.adCode!='' and skxsQkTj.adCode!='420000000000'">
and (townCode = #{skxsQkTj.adCode} or countyCode = #{skxsQkTj.adCode} or cityCode = #{skxsQkTj.adCode})
</if>
</select>
<!--按行政区统计-->
<select id="getStatisticList" resultType="cn.gistack.sm.sjztods.vo.SkxsQkTjVO">
-
+ select
+ <if test="skxsQkTj.adGrad==1">
+ cityName,
+ </if>
+ <if test="skxsQkTj.adGrad==2">
+ countyName,
+ </if>
+ <if test="skxsQkTj.adGrad==3">
+ townName,
+ </if>
+ ifnull(sum("ws"),0) as ws,
+ ifnull(sum("last_year_ws"),0) as lastYearWs,
+ ifnull(sum("his_ws"),0) as hisWs
+ from (
+ select
+ sqt.*,
+ arb."res_reg_code" resRegCode,arb."eng_scal" engScal,
+ case when b."ad_grad" = 4 THEN b."ad_name" ELSE NULL END AS townName,
+ case when b."ad_grad" = 4 THEN b."ad_code" ELSE NULL END AS townCode,
+ 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" = 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,
+ 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_ODS."skxs_qk_tj" sqt
+ left join SJZT_MD."att_res_base" arb on arb."guid" = sqt."res_code"
+ left join SJZT_MD."att_ad_base" b ON arb."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 1=1
+ <if test="skxsQkTj.queryTime!=null and skxsQkTj.queryTime!=''">
+ and DATE_FORMAT(sqt."statistic_time",'%Y-%m-%d') = #{skxsQkTj.queryTime}
+ </if>
+ <if test="skxsQkTj.resName!=null and skxsQkTj.resName!=''">
+ and sqt."res_name" like concat('%',#{skxsQkTj.resName},'%')
+ </if>
+ ) a
+ <if test="skxsQkTj.adCode!=null and skxsQkTj.adCode!='' and skxsQkTj.adCode!='420000000000'">
+ and (townCode = #{skxsQkTj.adCode} or countyCode = #{skxsQkTj.adCode} or cityCode = #{skxsQkTj.adCode})
+ </if>
+ <if test="skxsQkTj.adGrad==1">
+ group by cityName,cityCode
+ order by cityCode
+ </if>
+ <if test="skxsQkTj.adGrad==2">
+ group by countyName,countyCode
+ order by countyCode
+ </if>
+ <if test="skxsQkTj.adGrad==3">
+ group by townName,townCode
+ order by townCode
+ </if>
</select>
@@ -69,14 +125,27 @@
left join SJZT_MD."att_ad_base" d ON c."p_ad_code" = d."guid"
where 1=1
<if test="skxsQkTj.statisticTime!=null">
- and date_format(sqt."statistic_time","%Y-%m-%d") = #{skxsQkTj.statisticTime}
+ and DATE_FORMAT(sqt."statistic_time",'%Y-%m-%d') = #{skxsQkTj.statisticTime}
</if>
<if test="skxsQkTj.resName!=null and skxsQkTj.resName!=''">
and sqt."res_name" like concat('%',#{skxsQkTj.resName},'%')
</if>
) x
- <if test="skxsQkTj.adCode!=null and skxsQkTj.adCode!=''">
+ <if test="skxsQkTj.adCode!=null and skxsQkTj.adCode!='' and skxsQkTj.adCode!='420000000000'">
and (townCode = #{skxsQkTj.adCode} or countyCode = #{skxsQkTj.adCode} or cityCode = #{skxsQkTj.adCode})
</if>
</select>
+
+ <!--通过adCode 获取adGrad-->
+ <select id="getAdGradByAdCode" resultType="java.lang.String">
+ select "ad_grad" from SJZT_MD."att_ad_base" where "guid" = #{adCode}
+ </select>
+
+ <!--查询详情数据-->
+ <select id="selectSkxsQkTj" resultType="cn.gistack.sm.sjztods.entity.SkxsQkTj">
+ select * from SJZT_ODS."skxs_qk_tj"
+ where 1=1
+ and date_format("statistic_time",'%Y-%m-%d') = #{skxsQkTjExcel.statisticTime}
+ and "res_code" = #{skxsQkTjExcel.resCode}
+ </select>
</mapper>
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/XsfsQktjMapper.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/XsfsQktjMapper.java
index 7940a4c..2b943b1 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/XsfsQktjMapper.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/XsfsQktjMapper.java
@@ -41,4 +41,11 @@
* @return
*/
List<XsfsQktjExcel> exportXsfsQktjList(@Param("xsfsQktj") XsfsQktjVO xsfsQktj);
+
+ /**
+ * 查询详情信息
+ * @param xsfsQktjExcel
+ * @return
+ */
+ XsfsQktj selectXsfsQktj(@Param("xsfsQktjExcel") XsfsQktjExcel xsfsQktjExcel);
}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/XsfsQktjMapper.xml b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/XsfsQktjMapper.xml
index ef7d5af..bad6699 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/XsfsQktjMapper.xml
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/XsfsQktjMapper.xml
@@ -23,7 +23,7 @@
from SJZT_ODS."xsfs_qktj" xq
where 1=1
<if test="xsfsQktj.statisticTime!=null">
- and date_format(xq."statistic_time","%Y-%m-%d") = #{xsfsQktj.statisticTime}
+ and date_format(xq."statistic_time",'%Y-%m-%d') = #{xsfsQktj.statisticTime}
</if>
<if test="xsfsQktj.adCode!=null and xsfsQktj.adCode!=''">
and xq."ad_code" = #{xsfsQktj.adCode}
@@ -59,10 +59,18 @@
from SJZT_ODS."xsfs_qktj" xq
where 1=1
<if test="xsfsQktj.statisticTime!=null">
- and date_format(xq."statistic_time","%Y-%m-%d") = #{xsfsQktj.statisticTime}
+ and date_format(xq."statistic_time",'%Y-%m-%d') = #{xsfsQktj.statisticTime}
</if>
<if test="xsfsQktj.adCode!=null and xsfsQktj.adCode!=''">
and xq."ad_code" = #{xsfsQktj.adCode}
</if>
</select>
+
+ <!--查询详情数据-->
+ <select id="selectXsfsQktj" resultType="cn.gistack.sm.sjztods.entity.XsfsQktj">
+ select * from SJZT_ODS."skxs_qk_tj"
+ where 1=1
+ and date_format("statistic_time",'%Y-%m-%d') = #{xsfsQktjExcel.statisticTime}
+ and "ad_code" = #{xsfsQktjExcel.adCode}
+ </select>
</mapper>
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/SkxsQkTjServiceImpl.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/SkxsQkTjServiceImpl.java
index 1923420..d563aed 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/SkxsQkTjServiceImpl.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/SkxsQkTjServiceImpl.java
@@ -35,7 +35,7 @@
@Override
@DS("ztznwh")
public boolean exportSkxsQkTj(SkxsQkTj skxsQkTj) {
- // 查询库中是否包含当前日期，当前行政区的数据，如果有，则更新，没有则新增
+ // 查询库中是否包含当前日期，当前行政区的数据，如果有，则更新，没有则新增 ---有问题
QueryWrapper<SkxsQkTj> wrapper = new QueryWrapper<>();
wrapper.eq("\"statistic_time\"",skxsQkTj.getStatisticTime())
.eq("\"res_code\"",skxsQkTj.getResCode());
@@ -78,6 +78,11 @@
*/
@Override
public Object getStatisticList(SkxsQkTjVO skxsQkTj) {
+ // 通过adCode 获取adGrad
+ String adGrad = baseMapper.getAdGradByAdCode(skxsQkTj.getAdCode());
+ if (null!= adGrad && !adGrad.equals("")){
+ skxsQkTj.setAdGrad(adGrad);
+ }
return baseMapper.getStatisticList(skxsQkTj);
}
@@ -92,10 +97,7 @@
data.forEach(skxsQkTjExcel -> {
SkxsQkTj skxsQkTj = Objects.requireNonNull(BeanUtil.copy(skxsQkTjExcel, SkxsQkTj.class));
// 查询库中是否包含当前日期，当前行政区的数据，如果有，则更新，没有则新增
- QueryWrapper<SkxsQkTj> wrapper = new QueryWrapper<>();
- wrapper.eq("\"statistic_time\"",skxsQkTj.getStatisticTime())
- .eq("\"res_code\"",skxsQkTj.getResCode());
- SkxsQkTj qkTj = baseMapper.selectOne(wrapper);
+ SkxsQkTj qkTj = baseMapper.selectSkxsQkTj(skxsQkTjExcel);
// 设置更新人员，更新时间，来源信息
skxsQkTj.setUpdateTime(new Date());
skxsQkTj.setUpdateUser(AuthUtil.getUserId());
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/XsfsQktjServiceImpl.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/XsfsQktjServiceImpl.java
index 3dcdd63..081d104 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/XsfsQktjServiceImpl.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/XsfsQktjServiceImpl.java
@@ -94,7 +94,7 @@
QueryWrapper<XsfsQktj> wrapper = new QueryWrapper<>();
wrapper.eq("\"statistic_time\"",xsfsQktj.getStatisticTime())
.eq("\"ad_code\"",xsfsQktj.getAdCode());
- XsfsQktj qkTj = baseMapper.selectOne(wrapper);
+ XsfsQktj qkTj = baseMapper.selectXsfsQktj(xsfsQktjExcel);
// 设置更新人员，更新时间，来源信息
xsfsQktj.setUpdateTime(new Date());
xsfsQktj.setUpdateUser(AuthUtil.getUserId());
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/vo/SkxsQkTjVO.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/vo/SkxsQkTjVO.java
index 75655fc..80cbdee 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/vo/SkxsQkTjVO.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/vo/SkxsQkTjVO.java
@@ -7,9 +7,19 @@
public class SkxsQkTjVO extends SkxsQkTj {
/**
- * 按行政区划统计
+ * 行政区划编号
*/
private String adCode;
+
+ /**
+ * 行政区划名称
+ */
+ private String adName;
+
+ /**
+ * 行政区划等级
+ */
+ private String adGrad;
/**
* 水库规模
@@ -40,4 +50,9 @@
* 乡镇名称
*/
private String townName;
+
+ /**
+ * 查询时间
+ */
+ private String queryTime;
}
--
Gitblit v1.9.3