From 62791e3a8fb4ee4cfd06c3d01f163bf4f652fcc9 Mon Sep 17 00:00:00 2001
From: zhongrj <646384940@qq.com>
Date: Thu, 19 Oct 2023 14:20:42 +0800
Subject: [PATCH] 新增大中型水库统计数据导出和导入接口
---
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.xml | 36 ++++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/controller/SkxsQkTjController.java | 32 +++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/ISkxsQkTjService.java | 16 ++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/mapper/SkxsQkTjMapper.java | 8 +
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/impl/SkxsQkTjServiceImpl.java | 50 ++++++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/excel/SkxsQkTjExcel.java | 188 +++++++++++++++++++++++++++++++
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/excel/SkxsQkTjImport.java | 24 ++++
7 files changed, 354 insertions(+), 0 deletions(-)
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/controller/SkxsQkTjController.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/controller/SkxsQkTjController.java
index 9f0d542..80b8753 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/controller/SkxsQkTjController.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/controller/SkxsQkTjController.java
@@ -1,19 +1,29 @@
package cn.gistack.sm.sjztods.controller;
+import cn.gistack.sm.sjztmd.excel.AttResStagCapDiscExcel;
+import cn.gistack.sm.sjztmd.excel.AttResStagCapDiscImport;
import cn.gistack.sm.sjztods.entity.SkxsQkTj;
+import cn.gistack.sm.sjztods.excel.SkxsQkTjExcel;
+import cn.gistack.sm.sjztods.excel.SkxsQkTjImport;
import cn.gistack.sm.sjztods.service.ISkxsQkTjService;
import cn.gistack.sm.sjztods.vo.SkxsQkTjVO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
+import org.springblade.core.excel.util.ExcelUtil;
import org.springblade.core.mp.support.Condition;
import org.springblade.core.mp.support.Query;
import org.springblade.core.secure.utils.AuthUtil;
import org.springblade.core.tool.api.R;
+import org.springblade.core.tool.utils.DateUtil;
import org.springframework.web.bind.annotation.*;
+import org.springframework.web.multipart.MultipartFile;
+import javax.servlet.http.HttpServletResponse;
+import java.util.ArrayList;
import java.util.Date;
+import java.util.List;
/**
@@ -64,4 +74,26 @@
return R.data(skxsQkTjService.getStatisticList(skxsQkTj));
}
+ /**
+ * 导入
+ * @param file
+ * @return
+ */
+ @PostMapping("/import-skxsQkTj")
+ public R importSkxsQkTj(MultipartFile file) {
+ SkxsQkTjImport skxsQkTjImport = new SkxsQkTjImport(skxsQkTjService);
+ ExcelUtil.save(file, skxsQkTjImport, SkxsQkTjExcel.class);
+ return R.success("操作成功");
+ }
+
+ /**
+ * 导出
+ * @param skxsQkTj
+ */
+ @GetMapping("/export-skxsQkTj")
+ public void exportSkxsQkTj(HttpServletResponse response,SkxsQkTjVO skxsQkTj) {
+ List<SkxsQkTjExcel> list = skxsQkTjService.exportSkxsQkTjList(skxsQkTj);
+ ExcelUtil.export(response, "水库统计数据" + DateUtil.time(), "水库统计数据表", list, SkxsQkTjExcel.class);
+ }
+
}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/excel/SkxsQkTjExcel.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/excel/SkxsQkTjExcel.java
new file mode 100644
index 0000000..220854e
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/excel/SkxsQkTjExcel.java
@@ -0,0 +1,188 @@
+package cn.gistack.sm.sjztods.excel;
+
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.alibaba.excel.annotation.write.style.ColumnWidth;
+import com.alibaba.excel.annotation.write.style.ContentRowHeight;
+import com.alibaba.excel.annotation.write.style.HeadRowHeight;
+import com.baomidou.mybatisplus.annotation.TableField;
+import io.swagger.annotations.ApiModelProperty;
+import lombok.Data;
+
+import java.io.Serializable;
+
+@Data
+@ColumnWidth(25)
+@HeadRowHeight(20)
+@ContentRowHeight(18)
+public class SkxsQkTjExcel implements Serializable {
+ private static final long serialVersionUID = 1L;
+
+ /**
+ * 水库编号
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("水库编号")
+ private String resCode;
+
+ /**
+ * 水库名称
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("水库名称")
+ private String resName;
+
+ /**
+ * 市州名称
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("市州名称")
+ private String cityName;
+
+ /**
+ * 区县名称
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("区县名称")
+ private String countyName;
+
+ /**
+ * 乡镇名称
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("乡镇名称")
+ private String townName;
+
+ /**
+ * 总库容
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("总库容")
+ private Double totalCap;
+
+ /**
+ * 当前水位
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("当前水位")
+ private Double rz;
+
+ /**
+ * 当日蓄水量
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("当日蓄水量")
+ private Double ws;
+
+ /**
+ * 上月
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("上月")
+ private Double upMonthWs;
+
+ /**
+ * 去年同期蓄水量
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("去年同期蓄水量")
+ private Double lastYearWs;
+
+ /**
+ * 历史同期蓄水量
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("历史同期蓄水量")
+ private Double hisWs;
+
+ /**
+ * 今年以来灌溉放水量
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("今年以来灌溉放水量")
+ private Double nowYearIrrDw;
+
+ /**
+ * 今年以来生活放水量
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("今年以来生活放水量")
+ private Double nowYearLiveDw;
+
+ /**
+ * 今年以来生态放水量
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("今年以来生态放水量")
+ private Double nowYearZooDw;
+
+ /**
+ * 总蓄水现有-历史
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("总蓄水现有-历史")
+ private String totalWsDif;
+
+ /**
+ * 总蓄水较历史同期（%）
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("总蓄水较历史同期（%）")
+ private String totalWsHisPro;
+
+ /**
+ * 死库容
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("死库容")
+ private Double deadCap;
+
+ /**
+ * 当前有效蓄水
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("当前有效蓄水")
+ private Double nowValidWs;
+
+ /**
+ * 兴利库容
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("兴利库容")
+ private Double benResCap;
+
+ /**
+ * 有效蓄水/兴利（%）
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("有效蓄水/兴利（%）")
+ private String validWsBenPro;
+
+ /**
+ * 历史同期有效蓄水
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("历史同期有效蓄水")
+ private Double hisValidWs;
+
+ /**
+ * 当前有效比历史同期（%）
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("当前有效比历史同期（%）")
+ private String nowValidHisPro;
+
+ /**
+ * 去年有效蓄水
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("去年有效蓄水")
+ private Double lastYearValidWs;
+
+ /**
+ * 当前有效比去年同期（%）
+ */
+ @ColumnWidth(15)
+ @ExcelProperty("当前有效比去年同期（%）")
+ private String nowValidLastPro;
+
+}
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/excel/SkxsQkTjImport.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/excel/SkxsQkTjImport.java
new file mode 100644
index 0000000..6ceb4cc
--- /dev/null
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/excel/SkxsQkTjImport.java
@@ -0,0 +1,24 @@
+package cn.gistack.sm.sjztods.excel;
+
+import cn.gistack.sm.sjztmd.service.IAttResStagCapDicsService;
+import cn.gistack.sm.sjztods.service.ISkxsQkTjService;
+import lombok.RequiredArgsConstructor;
+import org.springblade.core.excel.support.ExcelImporter;
+
+import java.util.List;
+
+/**
+ * 导入水库库容数据
+ * @author zhongrj
+ * @date 2023-09-04
+ */
+@RequiredArgsConstructor
+public class SkxsQkTjImport implements ExcelImporter<SkxsQkTjExcel> {
+
+ private final ISkxsQkTjService service;
+
+ @Override
+ public void save(List<SkxsQkTjExcel> data) {
+ service.importSkxsQkTjData(data);
+ }
+}
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 c1c50ff..7d5d6e2 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
@@ -1,6 +1,7 @@
package cn.gistack.sm.sjztods.mapper;
import cn.gistack.sm.sjztods.entity.SkxsQkTj;
+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.core.mapper.BaseMapper;
@@ -33,4 +34,11 @@
* @return
*/
Object getStatisticList(@Param("skxsQkTj") SkxsQkTjVO skxsQkTj);
+
+ /**
+ * 查询导出数据
+ * @param skxsQkTj
+ * @return
+ */
+ List<SkxsQkTjExcel> exportSkxsQkTjList(@Param("skxsQkTj") SkxsQkTjVO skxsQkTj);
}
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 dbd770b..83521ba 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
@@ -43,4 +43,40 @@
</select>
+
+ <!--查询导出数据-->
+ <select id="exportSkxsQkTjList" resultType="cn.gistack.sm.sjztods.excel.SkxsQkTjExcel">
+ select * 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.statisticTime!=null">
+ 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!=''">
+ and (townCode = #{skxsQkTj.adCode} or countyCode = #{skxsQkTj.adCode} or cityCode = #{skxsQkTj.adCode})
+ </if>
+ </select>
</mapper>
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/ISkxsQkTjService.java b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/ISkxsQkTjService.java
index e141274..7de9859 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/ISkxsQkTjService.java
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/service/ISkxsQkTjService.java
@@ -1,9 +1,12 @@
package cn.gistack.sm.sjztods.service;
import cn.gistack.sm.sjztods.entity.SkxsQkTj;
+import cn.gistack.sm.sjztods.excel.SkxsQkTjExcel;
import cn.gistack.sm.sjztods.vo.SkxsQkTjVO;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
+
+import java.util.List;
/**
* @ClassName ISkxsQkTjService
@@ -33,4 +36,17 @@
* @return
*/
Object getStatisticList(SkxsQkTjVO skxsQkTj);
+
+ /**
+ * 导入统计上报数据
+ * @param data
+ */
+ void importSkxsQkTjData(List<SkxsQkTjExcel> data);
+
+ /**
+ * 查询导出数据
+ * @param skxsQkTj
+ * @return
+ */
+ List<SkxsQkTjExcel> exportSkxsQkTjList(SkxsQkTjVO skxsQkTj);
}
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 b8fa2ec..1923420 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
@@ -1,17 +1,22 @@
package cn.gistack.sm.sjztods.service.impl;
import cn.gistack.sm.sjztods.entity.SkxsQkTj;
+import cn.gistack.sm.sjztods.excel.SkxsQkTjExcel;
import cn.gistack.sm.sjztods.mapper.SkxsQkTjMapper;
import cn.gistack.sm.sjztods.service.ISkxsQkTjService;
import cn.gistack.sm.sjztods.vo.SkxsQkTjVO;
+import cn.gistack.system.user.entity.User;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springblade.core.secure.utils.AuthUtil;
+import org.springblade.core.tool.utils.BeanUtil;
import org.springframework.stereotype.Service;
import java.util.Date;
+import java.util.List;
+import java.util.Objects;
/**
* @ClassName SkxsQkTjServiceImpl
@@ -75,5 +80,50 @@
public Object getStatisticList(SkxsQkTjVO skxsQkTj) {
return baseMapper.getStatisticList(skxsQkTj);
}
+
+ /**
+ * 导入统计上报数据
+ * @param data
+ */
+ @Override
+ public void importSkxsQkTjData(List<SkxsQkTjExcel> data) {
+ if (data.size()>0){
+ // 遍历
+ 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.setUpdateTime(new Date());
+ skxsQkTj.setUpdateUser(AuthUtil.getUserId());
+ skxsQkTj.setSysResource("sb");
+ if (null!= qkTj){
+ skxsQkTj.setGuid(qkTj.getGuid());
+ // 更新并返回
+ updateById(skxsQkTj);
+ return;
+ }
+ // 设置创建人员，时间信息
+ skxsQkTj.setCreateUser(AuthUtil.getUserId());
+ skxsQkTj.setCreateTime(new Date());
+ // 保存并返回
+ save(skxsQkTj);
+ return;
+ });
+ }
+ }
+
+ /**
+ * 查询导出数据
+ * @param skxsQkTj
+ * @return
+ */
+ @Override
+ public List<SkxsQkTjExcel> exportSkxsQkTjList(SkxsQkTjVO skxsQkTj) {
+ return baseMapper.exportSkxsQkTjList(skxsQkTj);
+ }
}
--
Gitblit v1.9.3