From 448464bbb8e043c2b8e410947db1f07aa4fd9a4d Mon Sep 17 00:00:00 2001
From: linwe <872216996@qq.com>
Date: Mon, 17 Jun 2024 15:05:27 +0800
Subject: [PATCH] 入户宣传导出即查询优化
---
src/main/java/org/springblade/common/interceptor/CorsInterceptor.java | 32 +++++
src/main/java/org/springblade/modules/backblast/controller/BackblastPubRecordController.java | 18 +++
src/main/java/org/springblade/modules/backblast/mapper/BackblastPubRecordMapper.java | 14 +
src/main/java/org/springblade/common/config/WebConfig.java | 22 +++
src/main/java/org/springblade/modules/backblast/mapper/BackblastPubRecordMapper.xml | 130 +++++++++++++++++++++
src/main/java/org/springblade/modules/backblast/service/impl/BackblastPubRecordServiceImpl.java | 13 ++
src/main/java/org/springblade/modules/backblast/excel/BackblastPubRecordExcel.java | 93 +++++++++++++++
src/main/java/org/springblade/modules/backblast/service/IBackblastPubRecordService.java | 3
src/main/java/org/springblade/modules/backblast/vo/BackblastPubRecordVO.java | 6 +
9 files changed, 326 insertions(+), 5 deletions(-)
diff --git a/src/main/java/org/springblade/common/config/WebConfig.java b/src/main/java/org/springblade/common/config/WebConfig.java
new file mode 100644
index 0000000..a8f7314
--- /dev/null
+++ b/src/main/java/org/springblade/common/config/WebConfig.java
@@ -0,0 +1,22 @@
+package org.springblade.common.config;
+
+
+import org.springblade.common.interceptor.CorsInterceptor;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.context.annotation.Configuration;
+import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
+import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
+
+@Configuration
+public class WebConfig implements WebMvcConfigurer {
+
+ @Autowired
+ private CorsInterceptor corsInterceptor;
+
+ @Override
+ public void addInterceptors(InterceptorRegistry registry) {
+ //跨域拦截器需放在最上面
+ registry.addInterceptor(corsInterceptor);
+
+ }
+}
diff --git a/src/main/java/org/springblade/common/interceptor/CorsInterceptor.java b/src/main/java/org/springblade/common/interceptor/CorsInterceptor.java
new file mode 100644
index 0000000..fde8fd3
--- /dev/null
+++ b/src/main/java/org/springblade/common/interceptor/CorsInterceptor.java
@@ -0,0 +1,32 @@
+package org.springblade.common.interceptor;
+
+
+import org.springframework.http.HttpMethod;
+import org.springframework.http.HttpStatus;
+import org.springframework.stereotype.Component;
+import org.springframework.web.servlet.HandlerInterceptor;
+
+import javax.servlet.http.HttpServletRequest;
+import javax.servlet.http.HttpServletResponse;
+
+@Component
+public class CorsInterceptor implements HandlerInterceptor {
+
+ @Override
+ public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) {
+ response.setHeader("Access-Control-Allow-Origin", "*");
+ response.setHeader("Access-Control-Allow-Credentials", "true");
+ response.setHeader("Access-Control-Allow-Methods", "GET, HEAD, POST, PUT, PATCH, DELETE, OPTIONS");
+ response.setHeader("Access-Control-Max-Age", "86400");
+ response.setHeader("Access-Control-Allow-Headers", "*");
+
+ // 如果是OPTIONS则结束请求
+ if (HttpMethod.OPTIONS.toString().equals(request.getMethod())) {
+ response.setStatus(HttpStatus.NO_CONTENT.value());
+ return false;
+ }
+
+ return true;
+ }
+
+}
diff --git a/src/main/java/org/springblade/modules/backblast/controller/BackblastPubRecordController.java b/src/main/java/org/springblade/modules/backblast/controller/BackblastPubRecordController.java
index 168af96..9056f8e 100644
--- a/src/main/java/org/springblade/modules/backblast/controller/BackblastPubRecordController.java
+++ b/src/main/java/org/springblade/modules/backblast/controller/BackblastPubRecordController.java
@@ -21,8 +21,11 @@
import io.swagger.annotations.ApiParam;
import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport;
import lombok.AllArgsConstructor;
+
+import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
+import org.springblade.core.excel.util.ExcelUtil;
import org.springblade.core.log.annotation.ApiLog;
import org.springblade.core.log.logger.BladeLogger;
import org.springblade.core.secure.BladeUser;
@@ -30,7 +33,10 @@
import org.springblade.core.mp.support.Query;
import org.springblade.core.tool.api.R;
import org.springblade.core.tool.jackson.JsonUtil;
+import org.springblade.core.tool.utils.DateUtil;
import org.springblade.core.tool.utils.Func;
+import org.springblade.modules.backblast.excel.BackblastPubRecordExcel;
+import org.springblade.modules.house.excel.HouseRentalExcel;
import org.springframework.web.bind.annotation.*;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.springblade.modules.backblast.entity.BackblastPubRecordEntity;
@@ -38,6 +44,8 @@
import org.springblade.modules.backblast.wrapper.BackblastPubRecordWrapper;
import org.springblade.modules.backblast.service.IBackblastPubRecordService;
import org.springblade.core.boot.ctrl.BladeController;
+
+import java.util.List;
/**
* 反炸宣传记录表 控制器
@@ -156,4 +164,14 @@
BackblastPubRecordVO detail = backblastPubRecordService.getDetail(backblastPubRecord);
return R.data(detail);
}
+
+
+ @GetMapping("/export")
+ @ApiOperationSupport(order = 10)
+ @ApiOperation(value = "导出", notes = "")
+ public void export(BackblastPubRecordVO backblastPubRecord, HttpServletResponse response) {
+ List<BackblastPubRecordExcel> list = backblastPubRecordService.export(backblastPubRecord);
+ ExcelUtil.export(response, "入户宣传" + DateUtil.time(), "入户宣传数据表", list, BackblastPubRecordExcel.class);
+
+ }
}
diff --git a/src/main/java/org/springblade/modules/backblast/excel/BackblastPubRecordExcel.java b/src/main/java/org/springblade/modules/backblast/excel/BackblastPubRecordExcel.java
new file mode 100644
index 0000000..61760a9
--- /dev/null
+++ b/src/main/java/org/springblade/modules/backblast/excel/BackblastPubRecordExcel.java
@@ -0,0 +1,93 @@
+package org.springblade.modules.backblast.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.fasterxml.jackson.annotation.JsonFormat;
+import lombok.Data;
+import org.springblade.common.excel.ExcelDictConverter;
+import org.springblade.common.excel.ExcelDictItem;
+import org.springblade.common.excel.ExcelDictItemLabel;
+
+import java.util.Date;
+@Data
+@ColumnWidth(25)
+@HeadRowHeight(20)
+@ContentRowHeight(18)
+public class BackblastPubRecordExcel {
+
+
+
+ @ExcelProperty(value = "街道名称")
+ private String townName;
+
+
+ @ExcelProperty(value = "社区名称")
+ private String communityName;
+
+
+ @ExcelProperty(value = "派出所名称")
+ private String pcsName;
+
+
+ @ExcelProperty(value = "机构名称")
+ private String deptName;
+
+
+ @ExcelProperty(value = "小区名称")
+ private String aoiName;
+
+
+ @ExcelProperty(value = "房屋名称")
+ private String houseName;
+
+ @ExcelProperty(value = "宣传记录民警姓名")
+ private String policeman;
+
+ @ExcelProperty(value = "宣传记录民警电话")
+ private String policemanPhone;
+
+ @ExcelProperty(value = "宣传内容")
+ private String pubContent;
+
+ // @ExcelProperty(value = "宣传现场照片")
+ // private String pubUrls;
+
+ // @ExcelProperty(value = "网格编号")
+ // private String gridCode;
+
+ // @ExcelProperty(value = "警务网格编号")
+ // private String jwGridCode;
+
+ @ExcelProperty(value = "经度")
+ private String lng;
+
+ @ExcelProperty(value = "纬度")
+ private String lat;
+
+ @ExcelProperty(value = "地址")
+ private String address;
+
+ @ExcelProperty(value = "创建时间")
+ @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
+ private Date createTime;
+
+ // @ExcelProperty(value = "是否下载国家反诈app 1：是 2：否")
+ @ExcelProperty(value = "是否下载国家反诈app", converter = ExcelDictConverter.class)
+ @ExcelDictItemLabel(type = "isFlag")
+ @ExcelDictItem(type = "isFlag")
+ private String isFzApp;
+
+ // @ExcelProperty(value = "是否打开预警功能 1：是 2：否")
+ @ExcelProperty(value = "是否打开预警功能", converter = ExcelDictConverter.class)
+ @ExcelDictItem(type = "isFlag")
+ @ExcelDictItemLabel(type = "isFlag")
+ private String isOpenAlarm;
+
+ @ExcelProperty(value = "是否接受过其他反诈宣防内容")
+ private Integer isOther;
+
+ @ExcelProperty(value = "其他防诈内容")
+ private String otherId;
+}
diff --git a/src/main/java/org/springblade/modules/backblast/mapper/BackblastPubRecordMapper.java b/src/main/java/org/springblade/modules/backblast/mapper/BackblastPubRecordMapper.java
index 46a01ba..859e2d3 100644
--- a/src/main/java/org/springblade/modules/backblast/mapper/BackblastPubRecordMapper.java
+++ b/src/main/java/org/springblade/modules/backblast/mapper/BackblastPubRecordMapper.java
@@ -16,11 +16,13 @@
*/
package org.springblade.modules.backblast.mapper;
-import org.apache.ibatis.annotations.Param;
-import org.springblade.modules.backblast.entity.BackblastPubRecordEntity;
-import org.springblade.modules.backblast.vo.BackblastPubRecordVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
+import org.apache.ibatis.annotations.Param;
+import org.springblade.modules.backblast.entity.BackblastPubRecordEntity;
+import org.springblade.modules.backblast.excel.BackblastPubRecordExcel;
+import org.springblade.modules.backblast.vo.BackblastPubRecordVO;
+
import java.util.List;
/**
@@ -47,8 +49,14 @@
/**
* 反炸宣传记录表 自定义详情
+ *
* @param backblastPubRecord
* @return
*/
BackblastPubRecordVO getDetail(@Param("backblastPubRecord") BackblastPubRecordVO backblastPubRecord);
+
+ List<BackblastPubRecordExcel> selectBackblastPubRecordExport(@Param("backblastPubRecord") BackblastPubRecordVO backblastPubRecord,
+ @Param("isAdministrator") Integer isAdministrator,
+ @Param("regionChildCodesList") List<String> regionChildCodesList,
+ @Param("gridCodeList") List<String> gridCodeList);
}
diff --git a/src/main/java/org/springblade/modules/backblast/mapper/BackblastPubRecordMapper.xml b/src/main/java/org/springblade/modules/backblast/mapper/BackblastPubRecordMapper.xml
index 219df2a..87d7359 100644
--- a/src/main/java/org/springblade/modules/backblast/mapper/BackblastPubRecordMapper.xml
+++ b/src/main/java/org/springblade/modules/backblast/mapper/BackblastPubRecordMapper.xml
@@ -10,9 +10,11 @@
<select id="selectBackblastPubRecordPage" resultMap="backblastPubRecordResultMap">
select
jbpr.*,
- br.town_name as townName,
- br.name as communityName,
+ br.town_name AS townName,
+ br.NAME AS communityName,
jpag.pcs_name pcsName,
+ jda.aoi_name,
+ CONCAT( jda.building_name, '-', jda.unit_name, '-', jda.house_name ) houseName,
bd.dept_name
from jczz_backblast_pub_record jbpr
LEFT JOIN jczz_grid jg on jg.grid_code = jbpr.grid_code and jg.is_deleted = 0
@@ -20,6 +22,7 @@
LEFT JOIN blade_region br on br.code = jpag.community_code
LEFT JOIN blade_user bu ON bu.id = jbpr.create_user
LEFT JOIN blade_dept bd on bd.id = bu.dept_id
+ LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jbpr.house_code
where jbpr.is_deleted = 0
<if test="backblastPubRecord.townName != null and backblastPubRecord.townName != ''">
and br.town_name like concat('%',#{backblastPubRecord.townName},'%')
@@ -185,4 +188,127 @@
</select>
+ <select id="selectBackblastPubRecordExport"
+ resultType="org.springblade.modules.backblast.excel.BackblastPubRecordExcel">
+ select
+ jbpr.*,
+ br.town_name AS townName,
+ br.NAME AS communityName,
+ jpag.pcs_name pcsName,
+ jda.aoi_name,
+ CONCAT( jda.building_name, '-', jda.unit_name, '-', jda.house_name ) houseName,
+ bd.dept_name
+ from jczz_backblast_pub_record jbpr
+ LEFT JOIN jczz_grid jg on jg.grid_code = jbpr.grid_code and jg.is_deleted = 0
+ LEFT JOIN jczz_police_affairs_grid jpag on jbpr.jw_grid_code= jpag.jw_grid_code and jpag.is_deleted = 0
+ LEFT JOIN blade_region br on br.code = jpag.community_code
+ LEFT JOIN blade_user bu ON bu.id = jbpr.create_user
+ LEFT JOIN blade_dept bd on bd.id = bu.dept_id
+ LEFT JOIN jczz_doorplate_address jda ON jda.address_code = jbpr.house_code
+ where jbpr.is_deleted = 0
+ <if test="backblastPubRecord.townName != null and backblastPubRecord.townName != ''">
+ and br.town_name like concat('%',#{backblastPubRecord.townName},'%')
+ </if>
+ <if test="backblastPubRecord.communityName != null and backblastPubRecord.communityName != ''">
+ and jbr.name like concat('%',#{backblastPubRecord.communityName},'%')
+ </if>
+ <if test="backblastPubRecord.pcsName != null and backblastPubRecord.pcsName != ''">
+ and jpag.pcs_name like concat('%',#{backblastPubRecord.pcsName},'%')
+ </if>
+ <if test="backblastPubRecord.houseCode != null and backblastPubRecord.houseCode != ''">
+ and jbpr.house_code = #{backblastPubRecord.houseCode}
+ </if>
+ <if test="backblastPubRecord.address != null and backblastPubRecord.address != ''">
+ and jbpr.address like concat('%',#{backblastPubRecord.address},'%')
+ </if>
+ <if test="backblastPubRecord.deptName != null and backblastPubRecord.deptName != ''">
+ and bd.dept_name like concat('%',#{backblastPubRecord.deptName},'%')
+ </if>
+ <if test="backblastPubRecord.pubContent != null and backblastPubRecord.pubContent != ''">
+ and jbpr.pub_content like concat('%',#{backblastPubRecord.pubContent},'%')
+ </if>
+ <if test="backblastPubRecord.policeman != null and backblastPubRecord.policeman != ''">
+ and jbpr.policeman like concat('%',#{backblastPubRecord.policeman},'%')
+ </if>
+ <if test="backblastPubRecord.createUser != null">
+ and jbpr.create_user = #{backblastPubRecord.createUser}
+ </if>
+ <if test="backblastPubRecord.policemanPhone != null and backblastPubRecord.policemanPhone != ''">
+ and jbpr.policeman_phone like concat('%',#{backblastPubRecord.policemanPhone},'%')
+ </if>
+ <if test="backblastPubRecord.startTime != null and backblastPubRecord.startTime != ''">
+ and date_format(jbpr.create_time,'%Y-%m-%d') >= #{backblastPubRecord.startTime}
+ </if>
+ <if test="backblastPubRecord.endTime != null and backblastPubRecord.endTime != ''">
+ and date_format(jbpr.create_time,'%Y-%m-%d') <= #{backblastPubRecord.endTime}
+ </if>
+ <if test="backblastPubRecord.searchKey!=null and backblastPubRecord.searchKey!=''">
+ and CONCAT(
+ ifnull(jbpr.policeman_phone,''),
+ ifnull(jbpr.address,''),
+ ifnull(jbpr.pub_content,''),
+ ifnull(jbpr.policeman,'')
+ ) like CONCAT ('%', #{backblastPubRecord.searchKey},'%')
+ </if>
+ <if test="isAdministrator==2">
+ <choose>
+ <when test="backblastPubRecord.roleName != null and backblastPubRecord.roleName != ''">
+ <if test="backblastPubRecord.roleName=='wgy'">
+ <choose>
+ <when test="gridCodeList !=null and gridCodeList.size()>0">
+ and jg.grid_code in
+ <foreach collection="gridCodeList" item="code" open="(" close=")" separator=",">
+ #{code}
+ </foreach>
+ </when>
+ <otherwise>
+ and jg.grid_code in ('')
+ </otherwise>
+ </choose>
+ </if>
+ <if test="backblastPubRecord.roleName=='mj'">
+ <choose>
+ <when test="regionChildCodesList !=null and regionChildCodesList.size()>0">
+ and jpag.community_code in
+ <foreach collection="regionChildCodesList" item="code" open="(" close=")" separator=",">
+ #{code}
+ </foreach>
+ </when>
+ <otherwise>
+ and jpag.community_code in ('')
+ </otherwise>
+ </choose>
+ </if>
+ </when>
+ <otherwise>
+ <choose>
+ <when test="regionChildCodesList !=null and regionChildCodesList.size()>0">
+ and
+ (
+ jg.grid_code in
+ <foreach collection="regionChildCodesList" item="code" open="(" close=")" separator=",">
+ #{code}
+ </foreach>
+ or
+ jpag.community_code in
+ <foreach collection="regionChildCodesList" item="code" open="(" close=")" separator=",">
+ #{code}
+ </foreach>
+ )
+ </when>
+ <otherwise>
+ and
+ (
+ jg.grid_code in ('') or jpag.community_code in ('')
+ )
+ </otherwise>
+ </choose>
+ </otherwise>
+ </choose>
+ </if>
+ order by jbpr.id desc,jbpr.create_time desc
+
+ </select>
+
+
</mapper>
diff --git a/src/main/java/org/springblade/modules/backblast/service/IBackblastPubRecordService.java b/src/main/java/org/springblade/modules/backblast/service/IBackblastPubRecordService.java
index 5ccbdf6..5599564 100644
--- a/src/main/java/org/springblade/modules/backblast/service/IBackblastPubRecordService.java
+++ b/src/main/java/org/springblade/modules/backblast/service/IBackblastPubRecordService.java
@@ -18,6 +18,7 @@
import com.baomidou.mybatisplus.extension.service.IService;
import org.springblade.modules.backblast.entity.BackblastPubRecordEntity;
+import org.springblade.modules.backblast.excel.BackblastPubRecordExcel;
import org.springblade.modules.backblast.vo.BackblastPubRecordVO;
import org.springblade.core.mp.base.BaseService;
import com.baomidou.mybatisplus.core.metadata.IPage;
@@ -60,4 +61,6 @@
* 反炸宣传记录表 删除
*/
boolean removeBackblastPubByIds(List<Long> toLongList);
+
+ List<BackblastPubRecordExcel> export(BackblastPubRecordVO backblastPubRecord);
}
diff --git a/src/main/java/org/springblade/modules/backblast/service/impl/BackblastPubRecordServiceImpl.java b/src/main/java/org/springblade/modules/backblast/service/impl/BackblastPubRecordServiceImpl.java
index 83214f3..47310fc 100644
--- a/src/main/java/org/springblade/modules/backblast/service/impl/BackblastPubRecordServiceImpl.java
+++ b/src/main/java/org/springblade/modules/backblast/service/impl/BackblastPubRecordServiceImpl.java
@@ -10,6 +10,7 @@
import org.springblade.core.tool.utils.SpringUtil;
import org.springblade.modules.backblast.entity.BackblastPubPersonEntity;
import org.springblade.modules.backblast.entity.BackblastPubRecordEntity;
+import org.springblade.modules.backblast.excel.BackblastPubRecordExcel;
import org.springblade.modules.backblast.service.IBackblastPubPersonService;
import org.springblade.modules.backblast.vo.BackblastPubRecordVO;
import org.springblade.modules.backblast.mapper.BackblastPubRecordMapper;
@@ -200,4 +201,16 @@
}
return flag;
}
+
+ @Override
+ public List<BackblastPubRecordExcel> export(BackblastPubRecordVO backblastPubRecord) {
+ CommonParamSet commonParamSet = new CommonParamSet<>().invoke(BackblastPubRecordVO.class, backblastPubRecord);
+ if (!Strings.isBlank(backblastPubRecord.getRoleName())){
+ backblastPubRecord.setCreateUser(AuthUtil.getUserId());
+ }
+ return baseMapper.selectBackblastPubRecordExport(backblastPubRecord,
+ commonParamSet.getIsAdministrator(),
+ commonParamSet.getRegionChildCodesList(),
+ commonParamSet.getGridCodeList());
+ }
}
diff --git a/src/main/java/org/springblade/modules/backblast/vo/BackblastPubRecordVO.java b/src/main/java/org/springblade/modules/backblast/vo/BackblastPubRecordVO.java
index 8c8ee6a..dbdf216 100644
--- a/src/main/java/org/springblade/modules/backblast/vo/BackblastPubRecordVO.java
+++ b/src/main/java/org/springblade/modules/backblast/vo/BackblastPubRecordVO.java
@@ -84,6 +84,12 @@
@ApiModelProperty(value = "机构名称", example = "")
private String deptName;
+ @ApiModelProperty(value = "小区名称", example = "")
+ private String aoiName;
+
+ @ApiModelProperty(value = "房屋名称", example = "")
+ private String houseName;
+
/**
* 反诈宣防对象
*/
--
Gitblit v1.9.3