证书申请新增导出

Administrator

2021-11-12 be134977032d985301be327ec35786caff3087ea

证书申请新增导出

 src/main/java/org/springblade/modules/accreditation/controller/AccreditationRecordsController.java

@@ -12,6 +12,7 @@
import org.springblade.core.tool.utils.DateUtil;
import org.springblade.core.tool.utils.Func;
import org.springblade.modules.FTP.FtpUtil;
import org.springblade.modules.accreditation.excel.ExportSecurityBookPaperExcel;
import org.springblade.modules.accreditation.excel.ExportSecurityPaperExcel;
import org.springblade.modules.system.service.IUserService;
import org.springblade.modules.accreditation.entity.AccreditationRecords;
@@ -202,4 +203,27 @@
    }


    /**
     * 导出证书制证信息
     */
    @GetMapping("export-security-book-paper")
    public void exportSecurityBookPaper(AccreditationRecordsVo accreditationRecords, HttpServletResponse response) throws IOException {
        //按条件查询成绩数据
        List<ExportSecurityBookPaperExcel> excels = accreditationRecordsService.exportSecurityBookPaper(accreditationRecords);
        String fileName = null;
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding(org.apache.commons.codec.Charsets.UTF_8.name());
            fileName = URLEncoder.encode("证书制证数据"+ DateUtil.time(), Charsets.UTF_8.name());
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            //修改单元格格式为文本格式
            EasyExcel.write(response.getOutputStream(), ExportSecurityBookPaperExcel.class)
                .sheet("证书制证数据表")
                .doWrite(excels);
        } catch (Throwable var6) {
            throw var6;
        }
    }


}

 src/main/java/org/springblade/modules/accreditation/excel/ExportSecurityBookPaperExcel.java

New file
@@ -0,0 +1,111 @@
/*
 *      Copyright (c) 2018-2028, Chill Zhuang All rights reserved.
 *
 *  Redistribution and use in source and binary forms, with or without
 *  modification, are permitted provided that the following conditions are met:
 *
 *  Redistributions of source code must retain the above copyright notice,
 *  this list of conditions and the following disclaimer.
 *  Redistributions in binary form must reproduce the above copyright
 *  notice, this list of conditions and the following disclaimer in the
 *  documentation and/or other materials provided with the distribution.
 *  Neither the name of the dreamlu.net developer nor the names of its
 *  contributors may be used to endorse or promote products derived from
 *  this software without specific prior written permission.
 *  Author: Chill 庄骞 (smallchill@163.com)
 */
package org.springblade.modules.accreditation.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
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 com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.io.Serializable;
import java.net.URL;
import java.util.Date;

/**
 * ExamPaymentExcel
 * @author zhongrj
 * @since 2021-10-29
 */
@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(30)
public class ExportSecurityBookPaperExcel implements Serializable {
    private static final long serialVersionUID = 1L;

    @ColumnWidth(15)
    @ExcelProperty("姓名")
    private String realName;

    @ColumnWidth(20)
    @ExcelProperty("身份证号码")
    private String idCardNo;

    @ColumnWidth(35)
    @ExcelProperty("所属公司")
    private String deptName;


    @ColumnWidth(20)
    @ExcelProperty("保安证编号")
    private String securityNumber;

    /**
     * 申请人姓名
     */
    @ColumnWidth(20)
    @ExcelProperty("申请人")
    private String applyName;

    /**
     * 申请人单位
     */
    @ColumnWidth(20)
    @ExcelProperty("申请人单位")
    private String applyUnit;


    @ColumnWidth(20)
    @ExcelProperty("申请人时间")
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    private Date createTime;

    /**
     * 审核状态  1：待审核   2：审核通过   3：审核不通过
     */
    @ColumnWidth(15)
    @ExcelProperty("审核状态")
    private String auditStatus;

    /**
     * 审核时间
     */
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ColumnWidth(20)
    @ExcelProperty("审核时间")
    private Date auditTime;

    /**
     * 审核详情
     */
    @ColumnWidth(20)
    @ExcelProperty("审核详情")
    private String auditDetail;

    /**
     * 是否制证
     */
    @ColumnWidth(10)
    @ExcelProperty("是否制证")
    private String userType;


}

 src/main/java/org/springblade/modules/accreditation/mapper/AccreditationRecordsMapper.java

@@ -3,6 +3,7 @@
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.ibatis.annotations.Param;
import org.springblade.modules.accreditation.excel.ExportSecurityBookPaperExcel;
import org.springblade.modules.apply.excel.ApplyInfoExcel;
import org.springblade.modules.accreditation.entity.AccreditationRecords;
import org.springblade.modules.accreditation.vo.AccreditationRecordsVo;
@@ -40,4 +41,11 @@
     * @return
     */
    List<AccreditationRecordsVo> exportSecurityPaperList(@Param("accreditationRecords") AccreditationRecordsVo accreditationRecords);

    /**
     * 导出证书制证信息
     * @param accreditationRecords
     * @return
     */
    List<ExportSecurityBookPaperExcel> exportSecurityBookPaper(@Param("accreditationRecords") AccreditationRecordsVo accreditationRecords);
}

 src/main/java/org/springblade/modules/accreditation/mapper/AccreditationRecordsMapper.xml

@@ -140,6 +140,22 @@
        blade_dept bt
        ON
        bu.dept_id = bt.id
        left join
        blade_user bu1
        on
        sar.create_user = bu1.id
        LEFT JOIN
        blade_dept bt1
        ON
        bu1.dept_id = bt1.id
        left join
        sys_information si
        on
        bt.id = si.departmentid
        left join
        sys_jurisdiction sj
        on
        sj.id = si.jurisdiction
        WHERE
        1=1
        and bu.status = 1
@@ -156,6 +172,21 @@
        <if test="accreditationRecords.securityNumber!=null and  accreditationRecords.securityNumber!=''">
            and bu.securitynumber like concat('%', #{accreditationRecords.securityNumber},'%')
        </if>
        <if test="accreditationRecords.jurisdiction!=null and accreditationRecords.jurisdiction!='' and accreditationRecords.jurisdiction!='1372091709474910209'">
            and (sj.id = #{accreditationRecords.jurisdiction} or sj.parent_id = #{accreditationRecords.jurisdiction})
        </if>
        <if test="accreditationRecords.applyUnit!=null and  accreditationRecords.applyUnit!=''">
            and bt1.dept_name like concat('%', #{accreditationRecords.applyUnit},'%')
        </if>
        <if test="accreditationRecords.createUser!=null">
            and sar.create_user = #{accreditationRecords.createUser}
        </if>
        <if test="accreditationRecords.deptId!=null">
            and bt.id = #{accreditationRecords.deptId}
        </if>
        <if test="accreditationRecords.type!=null">
            and sar.type = #{accreditationRecords.type}
        </if>
        <if test="accreditationRecords.status!=null">
            and sar.status = #{accreditationRecords.status}
        </if>
@@ -173,4 +204,97 @@
        </if>
        order by sar.create_time desc
    </select>


    <!--导出证书制证信息-->
    <select id="exportSecurityBookPaper" resultType="org.springblade.modules.accreditation.excel.ExportSecurityBookPaperExcel">
        SELECT
        bu.real_name realName,
        bu.sex,bu.cardid idCardNo,
        bt.dept_name AS deptName,
        bu.securitynumber securityNumber,
        bu1.real_name applyName,
        bt1.dept_name applyUnit,
        sar.create_time createTime,
        ELT(sar.audit_status,"待审核","审核通过","审核不通过") auditStatus,
        sar.audit_time auditTime,
        sar.audit_detail auditDetail,
        if(bu.user_type=6,"是","否") userType
        FROM
        sys_accreditation_records sar
        left join
        blade_user bu
        on
        sar.user_id = bu.id
        LEFT JOIN
        blade_dept bt
        ON
        bu.dept_id = bt.id
        left join
        blade_user bu1
        on
        sar.create_user = bu1.id
        LEFT JOIN
        blade_dept bt1
        ON
        bu1.dept_id = bt1.id
        left join
        sys_information si
        on
        bt.id = si.departmentid
        left join
        sys_jurisdiction sj
        on
        sj.id = si.jurisdiction
        WHERE
        1=1
        and bu.status = 1
        and bu.is_deleted = 0
        <if test="accreditationRecords.deptName!=null and  accreditationRecords.deptName!=''">
            and bt.dept_name like concat('%', #{accreditationRecords.deptName},'%')
        </if>
        <if test="accreditationRecords.realName!=null and  accreditationRecords.realName!=''">
            and bu.real_name like concat('%', #{accreditationRecords.realName},'%')
        </if>
        <if test="accreditationRecords.idCardNo!=null and  accreditationRecords.idCardNo!=''">
            and bu.cardid like concat('%', #{accreditationRecords.idCardNo},'%')
        </if>
        <if test="accreditationRecords.applyUnit!=null and  accreditationRecords.applyUnit!=''">
            and bt1.dept_name like concat('%', #{accreditationRecords.applyUnit},'%')
        </if>
        <if test="accreditationRecords.securityNumber!=null and  accreditationRecords.securityNumber!=''">
            and bu.securitynumber like concat('%', #{accreditationRecords.securityNumber},'%')
        </if>
        <if test="accreditationRecords.jurisdiction!=null and accreditationRecords.jurisdiction!='' and accreditationRecords.jurisdiction!='1372091709474910209'">
            and (sj.id = #{accreditationRecords.jurisdiction} or sj.parent_id = #{accreditationRecords.jurisdiction})
        </if>
        <if test="accreditationRecords.status!=null">
            and sar.status = #{accreditationRecords.status}
        </if>
        <if test="accreditationRecords.createUser!=null">
            and sar.create_user = #{accreditationRecords.createUser}
        </if>
        <if test="accreditationRecords.deptId!=null">
            and bt.id = #{accreditationRecords.deptId}
        </if>
        <if test="accreditationRecords.type!=null">
            and sar.type = #{accreditationRecords.type}
        </if>
        <if test="accreditationRecords.auditStatus!=null">
            and sar.audit_status = #{accreditationRecords.auditStatus}
        </if>
        <if test="accreditationRecords.isAvatar==1">
            and bu.avatar is not null and bu.avatar!=""
        </if>
        <if test="accreditationRecords.isAvatar==2">
            and (bu.avatar is null or bu.avatar="")
        </if>
        <if test="accreditationRecords.startTime!=null and accreditationRecords.startTime!='' and accreditationRecords.startTime!='undefined'">
            and sar.create_time &gt;= #{accreditationRecords.startTime}
        </if>
        <if test="accreditationRecords.endTime!=null and accreditationRecords.endTime!='' and accreditationRecords.endTime!='undefined'">
            and sar.create_time &lt;= #{accreditationRecords.endTime}
        </if>
        order by sar.create_time desc
    </select>
</mapper>

 src/main/java/org/springblade/modules/accreditation/service/AccreditationRecordsService.java

@@ -3,6 +3,7 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import org.springblade.modules.accreditation.entity.AccreditationRecords;
import org.springblade.modules.accreditation.excel.ExportSecurityBookPaperExcel;
import org.springblade.modules.accreditation.vo.AccreditationRecordsVo;
import org.springblade.modules.accreditation.excel.ExportSecurityPaperExcel;

@@ -41,4 +42,11 @@
     * @return
     */
    List<ExportSecurityPaperExcel> exportSecurityPaperList(AccreditationRecordsVo accreditationRecords);

    /**
     * 导出证书制证信息
     * @param accreditationRecords
     * @return
     */
    List<ExportSecurityBookPaperExcel> exportSecurityBookPaper(AccreditationRecordsVo accreditationRecords);
}

 src/main/java/org/springblade/modules/accreditation/service/impl/AccreditationRecordsServiceImpl.java

@@ -6,6 +6,7 @@
import lombok.AllArgsConstructor;
import org.springblade.common.utils.QRCodeUtil;
import org.springblade.modules.FTP.FtpUtil;
import org.springblade.modules.accreditation.excel.ExportSecurityBookPaperExcel;
import org.springblade.modules.accreditation.excel.ExportSecurityPaperExcel;
import org.springblade.modules.accreditation.entity.AccreditationRecords;
import org.springblade.modules.accreditation.mapper.AccreditationRecordsMapper;
@@ -139,4 +140,16 @@
        }
        return null;
    }

    /**
     * 导出证书制证信息
     * @param accreditationRecords
     * @return
     */
    @Override
    public List<ExportSecurityBookPaperExcel> exportSecurityBookPaper(AccreditationRecordsVo accreditationRecords) {
        //查询数据
        List<ExportSecurityBookPaperExcel> accreditationRecordsVos = baseMapper.exportSecurityBookPaper(accreditationRecords);
        return accreditationRecordsVos;
    }
}

 src/main/java/org/springblade/modules/system/mapper/DeptMapper.java

@@ -59,9 +59,10 @@
     *
     * @param tenantId
     * @param parentId
     * @param type 不为空时查公安局
     * @return
     */
    List<DeptVO> lazyTree(String tenantId, Long parentId);
    List<DeptVO> lazyTree(String tenantId, Long parentId,Integer type);

    /**
     * 懒加载获取树形节点

 src/main/java/org/springblade/modules/system/mapper/DeptMapper.xml

@@ -100,6 +100,9 @@
        <if test="param1!=null and param1!=''">
            and dept.tenant_id = #{param1}
        </if>
        <if test="param3!=null">
            and dept.dept_category = #{param3}
        </if>
        ORDER BY dept.sort
    </select>


 src/main/java/org/springblade/modules/system/service/impl/DeptServiceImpl.java

@@ -121,6 +121,10 @@
                        if (dept.getParentId().equals(1123598813738675201L)) {
                            return ForestNodeMerger.merge(baseMapper.lazyTreeCity(tenantId, parentId));
                        }
                        if (dept.getParentId().equals(0L)){
                            //南昌市公安局
                            return ForestNodeMerger.merge(baseMapper.lazyTree(tenantId, null,2));
                        }
                    }
                }
            }else {
@@ -129,7 +133,7 @@
            }
        }
        //admin
        return ForestNodeMerger.merge(baseMapper.lazyTree(tenantId, parentId));
        return ForestNodeMerger.merge(baseMapper.lazyTree(tenantId, parentId,null));
    }

    @Override