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); } } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/excel/SkxsQkTjExcel.java
New file @@ -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; } skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztods/excel/SkxsQkTjImport.java
New file @@ -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); } } 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); } 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> 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); } 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); } }
