入户宣传导出即查询优化

linwe

2024-06-17 448464bbb8e043c2b8e410947db1f07aa4fd9a4d

入户宣传导出即查询优化

 src/main/java/org/springblade/common/config/WebConfig.java

New file
@@ -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);

    }
}

 src/main/java/org/springblade/common/interceptor/CorsInterceptor.java

New file
@@ -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;
    }

}

 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);

    }
}

 src/main/java/org/springblade/modules/backblast/excel/BackblastPubRecordExcel.java

New file
@@ -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;
}

 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);
}

 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') &gt;= #{backblastPubRecord.startTime}
        </if>
        <if test="backblastPubRecord.endTime != null and backblastPubRecord.endTime != ''">
            and date_format(jbpr.create_time,'%Y-%m-%d') &lt;= #{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>

 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);
}

 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());
    }
}

 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;

    /**
     * 反诈宣防对象
     */