新增保安员证打印，审核

Administrator

2022-06-07 2879b560fe53998eef383f9d537144b72801586b

新增保安员证打印，审核

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

New file
@@ -0,0 +1,536 @@
package org.springblade.modules.accreditation.controller;

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.metadata.IPage;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.AllArgsConstructor;
import org.apache.commons.codec.Charsets;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springblade.core.mp.support.Condition;
import org.springblade.core.mp.support.Query;
import org.springblade.core.tool.api.R;
import org.springblade.core.tool.utils.DateUtil;
import org.springblade.core.tool.utils.Func;
import org.springblade.modules.accreditation.entity.AccreditationRecords;
import org.springblade.modules.accreditation.excel.ExportSecurityBookPaperExcel;
import org.springblade.modules.accreditation.excel.ExportSecurityPaperExcel;
import org.springblade.modules.accreditation.excel.ExportSecurityPaperExcelVO;
import org.springblade.modules.accreditation.service.AccreditationRecordsService;
import org.springblade.modules.accreditation.vo.AccreditationRecordsVo;
import org.springblade.modules.system.entity.User;
import org.springblade.modules.system.service.IUserService;
import org.springframework.web.bind.annotation.*;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URL;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * @author zhongrj
 * @time 2021-11-3
 * @desc 制证记录控制层
 */
@RestController
@AllArgsConstructor
@RequestMapping("/accreditationRecords")
public class AccreditationRecordsController {

    private final AccreditationRecordsService accreditationRecordsService;

    private final IUserService userService;

    /**
     * 自定义分页
     * @param query page,size
     * @param accreditationRecords 制证记录信息对象
     */
    @GetMapping("/page")
    public R<IPage<AccreditationRecordsVo>> page(AccreditationRecordsVo accreditationRecords, Query query) {
        IPage<AccreditationRecordsVo> pages = accreditationRecordsService.selectAccreditationRecordsPage(Condition.getPage(query), accreditationRecords);
        return R.data(pages);
    }

    /**
     * 新增（补证）
     * @param accreditationRecords 制证记录信息对象
     */
    @PostMapping("/save")
    @ApiOperation(value = "新增", notes = "传入accreditationRecords")
    public R save(@RequestBody AccreditationRecords accreditationRecords){
        accreditationRecords.setCreateTime(new Date());
        accreditationRecords.setStatus(1);
        accreditationRecords.setAuditStatus(1);
        boolean save = accreditationRecordsService.save(accreditationRecords);

        //修改用户信息
        User user = new User();
        user.setId(accreditationRecords.getUserId());
        user.setUpdateTime(new Date());
        //修改为未制证状态
        user.setUserType(7);
        userService.updateById(user);
        return R.data(save);
    }



    /**
     * 制证申请(批量）
     * @param accreditationRecords 制证记录信息对象
     */
    @PostMapping("/securityApply")
    public R securityApply(@RequestBody AccreditationRecordsVo accreditationRecords){
        return R.data(accreditationRecordsService.securityApply(accreditationRecords));
    }

    /**
     * 批量修改为已制证
     * @param accreditationRecords 制证记录信息对象
     */
    @PostMapping("/batchAccreditation")
    public R batchAccreditation(@RequestBody AccreditationRecordsVo accreditationRecords){
        return R.data(accreditationRecordsService.batchAccreditation(accreditationRecords));
    }


    /**
     * 审核
     * @param accreditationRecords 制证记录信息对象
     */
    @PostMapping("/audit")
    public R audit(@RequestBody AccreditationRecords accreditationRecords){
        accreditationRecords.setAuditTime(new Date());

        boolean b = accreditationRecordsService.updateById(accreditationRecords);
        //审核通过
        if (accreditationRecords.getAuditStatus()==2){
            //修改为未制证状态
            AccreditationRecords records = accreditationRecordsService.getById(accreditationRecords);
            User user = userService.getById(records.getUserId());
            if(accreditationRecords.getType()==2) {
                user.setUserType(7);
                user.setUpdateTime(new Date());
                //更新
                userService.updateById(user);
            }
        }
        //返回
        return R.status(b);
    }


    /**
     * 批量审核
     * @param accreditationRecords 制证记录信息对象
     */
    @PostMapping("/batchAudit")
    public R batchAudit(@RequestBody AccreditationRecordsVo accreditationRecords){
        //取出申请id
        String ids = accreditationRecords.getIds();
        List<String> list = Arrays.asList(ids.split(","));
        //批量审核
        list.forEach(id->{
            accreditationRecords.setId(Long.parseLong(id));
            accreditationRecords.setAuditTime(new Date());
            accreditationRecordsService.updateById(accreditationRecords);
            //审核通过
            if (accreditationRecords.getAuditStatus()==2) {
                if (accreditationRecords.getType() == 2) {
                    //修改为未制证状态
                    AccreditationRecords records = accreditationRecordsService.getById(id);
                    User user = userService.getById(records.getUserId());
                    user.setUserType(7);
                    user.setUpdateTime(new Date());
                    //更新
                    userService.updateById(user);
                }
            }
        });
        //返回
        return R.status(true);
    }


    /**
     * 修改
     * @param accreditationRecords 制证记录信息对象
     */
    @PostMapping("/update")
    public R update(@RequestBody AccreditationRecords accreditationRecords){
        return R.status(accreditationRecordsService.updateById(accreditationRecords));
    }

    /**
     * 新增或修改
     * @param accreditationRecords 制证记录信息对象
     */
    @PostMapping("/submit")
    public R submit(@RequestBody AccreditationRecords accreditationRecords){
        return R.data(accreditationRecordsService.saveOrUpdate(accreditationRecords));
    }

    /**
     * 删除
     * @param ids 制证记录信息ids 数组
     */
    @PostMapping("/remove")
    public R remove(@ApiParam(value = "主键集合") @RequestParam String ids) {
        return R.status(accreditationRecordsService.removeByIds(Func.toLongList(ids)));
    }

    /**
     * 详情
     * @param accreditationRecords 制证记录信息对象
     */
    @GetMapping("/detail")
    @ApiOperation(value = "详情", notes = "传入accreditationRecords")
    public R<AccreditationRecords> detail(AccreditationRecords accreditationRecords) {
        AccreditationRecords detail = accreditationRecordsService.getOne(Condition.getQueryWrapper(accreditationRecords));
        return R.data(detail);
    }


    /**
     * 详情
     * @param accreditationRecords 制证记录信息对象
     */
    @GetMapping("/details")
    @ApiOperation(value = "详情", notes = "传入accreditationRecords")
    public R<AccreditationRecordsVo> details(AccreditationRecords accreditationRecords) {
        AccreditationRecordsVo detail = accreditationRecordsService.getAccreditationRecordsDetails(accreditationRecords);
        return R.data(detail);
    }

    /**
     * 导出保安员证信息（上岗证）
     */
    @GetMapping("export-security-paper")
    public void exportSecurityPaper(AccreditationRecordsVo accreditationRecords, HttpServletResponse response) throws IOException {
        //按条件查询成绩数据
        List<ExportSecurityPaperExcel> excels = accreditationRecordsService.exportSecurityPaperList(accreditationRecords);
        String fileName = null;
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding(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(), ExportSecurityPaperExcel.class)
                .sheet("保安员证数据表")
                //自定义行高
//                .registerWriteHandler(new CustomCellWriteHeightConfig())
                //自定义宽度
//                .registerWriteHandler(new CustomCellWriteWeightConfig())
                //写入
                .doWrite(excels);
        } catch (Throwable var6) {
            throw var6;
        }
//        ExcelUtil.export(response, "保安员证数据" + DateUtil.time(), "保安员证数据表", excels, ExportSecurityPaperExcel.class);
    }


    /**
     * 导出证书制证信息
     */
    @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(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;
        }
    }


    /**
     * 导出证书制证信息(包含照片)
     */
    @GetMapping("export-security-book-papers")
    public void exportSecurityBookPapers(AccreditationRecordsVo accreditationRecords, HttpServletResponse response) throws Exception {
        //按条件查询成绩数据
        List<AccreditationRecordsVo> excels = accreditationRecordsService.exportSecurityBookPapers(accreditationRecords);
        List<ExportSecurityPaperExcelVO> list = new ArrayList<>();
        //数据转换封装
        excels.forEach(accreditationRecordsVo -> {
            ExportSecurityPaperExcelVO exportSecurityPaperExcelVO = new ExportSecurityPaperExcelVO();
            //如果头像有
            if (null != accreditationRecordsVo.getAvatar() && accreditationRecordsVo.getAvatar() != "") {
                exportSecurityPaperExcelVO.setAvatar(accreditationRecordsVo.getAvatar());
            }
            exportSecurityPaperExcelVO.setName(accreditationRecordsVo.getRealName()+accreditationRecordsVo.getIdCardNo());
            //封装
            list.add(exportSecurityPaperExcelVO);
        });
        // 声明一个工作薄
        HSSFWorkbook workBook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workBook.createSheet();
        //首行锁定
//        sheet.createFreezePane(0, 1, 0, 1);
        workBook.setSheetName(0, "证书打印信息");
        CellStyle style = workBook.createCellStyle();
        Font font = workBook.getFontAt((short) 0);
        font.setCharSet(HSSFFont.DEFAULT_CHARSET);
        //更改默认字体大小
        font.setFontHeightInPoints((short) 12);
        font.setFontName("宋体");
        style.setFont(font);
        // 上下居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //默认宽高
        sheet.setDefaultColumnWidth((short)11);

        // 创建表格标题行 第一行
//        HSSFRow titleRow = sheet.createRow(0);
//        titleRow.setHeight((short) 300);
//        titleRow.createCell(0).setCellValue("员工姓名");
//        titleRow.createCell(1).setCellValue("员工照片");
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        //如果总数小于等于7
        if (list.size()<=7){
            HSSFRow row = sheet.createRow(0);
            row.setHeight((short) 1980);

            HSSFRow row1 = sheet.createRow(1);
            row1.setHeight((short) 300);

            for (int i = 0; i < list.size(); i++) {
                ExportSecurityPaperExcelVO checkWorkVo = list.get(i);
                row1.createCell(i).setCellValue(checkWorkVo.getName());
                //如果头像有
                if (null != list.get(i).getAvatar() && list.get(i).getAvatar() != "") {
                    if (list.get(i).getAvatar().contains("http:")) {
                        //获取图片格式
                        int lastIndexOf = list.get(i).getAvatar().lastIndexOf(".");
                        String pictureType = list.get(i).getAvatar().substring(lastIndexOf + 1);
                        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                        BufferedImage bufferImg = null;
                        //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
                        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                        try {
                            bufferImg = ImageIO.read(new URL(list.get(i).getAvatar()));
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                        try {
                            ImageIO.write(bufferImg, pictureType, byteArrayOut);
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                        //anchor主要用于设置图片的属性
                        HSSFClientAnchor anchor =
                            new HSSFClientAnchor
                                (        0, //x缩放
                                    0, // y缩放
                                    1023, //最大1023
                                    255, //最大255
                                    (short) i,  //宽度 0开始
                                    0, //在第几行
                                    (short) i, //宽度 0开始
                                    0 //第几列
                                );
                        //插入图片
                        patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                }
            }
        }else {
            //总数大于7
            List<ExportSecurityPaperExcelVO> excelList = new ArrayList<>();
            //标记行,初始为0
            int rowNum = -2;
            for (int i = 0; i < list.size(); i++) {
                //每次从集合中读取7个对象,求余数
                if (i != 0 && (i + 1) % 7 == 0) {
                    excelList.add(list.get(i));
                    rowNum = rowNum + 2;
                    //写入表格
                    writeBookForEach(excelList,rowNum,workBook,sheet,patriarch);
                    //清空集合
                    excelList.clear();
                } else {
                    excelList.add(list.get(i));
                }
            }
            //判断余下的
            if (excelList.size()>0){
                rowNum = rowNum + 2;
                //写入表格
                writeBookForEach(excelList,rowNum,workBook,sheet,patriarch);
            }
        }
        //导出数据
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding(Charsets.UTF_8.name());
        String fileName = URLEncoder.encode("证书打印信息导出"+ DateUtil.time(), Charsets.UTF_8.name());
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        workBook.write(response.getOutputStream());
    }

    /**
     * 循环写入表格
     * @param list 每行需要插入的数据
     * @param rowNum 行标记
     * @param workBook book
     * @param sheet sheet
     * @param patriarch 画图对象
     */
    private void writeBookForEach(List<ExportSecurityPaperExcelVO> list, int rowNum, HSSFWorkbook workBook, HSSFSheet sheet, HSSFPatriarch patriarch) {
        HSSFRow row = sheet.createRow(rowNum);
        row.setHeight((short) 1980);
        HSSFRow row1 = sheet.createRow(rowNum+1);
        row1.setHeight((short) 300);
        for (int i = 0; i < list.size(); i++) {
            ExportSecurityPaperExcelVO checkWorkVo = list.get(i);
            row1.createCell(i).setCellValue(checkWorkVo.getName());
            //如果头像有
            if (null != list.get(i).getAvatar() && list.get(i).getAvatar() != "") {
                if (list.get(i).getAvatar().contains("http:")) {
                    //获取图片格式
                    int lastIndexOf = list.get(i).getAvatar().lastIndexOf(".");
                    String pictureType = list.get(i).getAvatar().substring(lastIndexOf + 1);
                    //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                    BufferedImage bufferImg = null;
                    //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                    try {
                        bufferImg = ImageIO.read(new URL(list.get(i).getAvatar()));
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                    try {
                        ImageIO.write(bufferImg, pictureType, byteArrayOut);
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                    //anchor主要用于设置图片的属性
                    HSSFClientAnchor anchor =
                        new HSSFClientAnchor
                            (        0, //x缩放
                                0, // y缩放
                                1023, //最大1023
                                255, //最大255
                                (short) i,  //宽度占几格 0开始
                                rowNum, //在第几行
                                (short) i, //宽度占几格 0开始
                                rowNum //第几列
                            );
                    //插入图片
                    patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
                }
            }
        }
    }


//    /**
//     * 导出证书制证信息(包含照片)  easyExcel 填充
//     */
//    @GetMapping("export-security-book-papers")
//    public void exportSecurityBookPapers(AccreditationRecordsVo accreditationRecords, HttpServletResponse response) throws Exception {
//        //按条件查询成绩数据
//        List<AccreditationRecordsVo> excels = accreditationRecordsService.exportSecurityBookPapers(accreditationRecords);
//        List<ExportSecurityPaperExcelVO> list = new ArrayList<>();
//        excels.forEach(accreditationRecordsVo -> {
//            ExportSecurityPaperExcelVO exportSecurityPaperExcelVO = new ExportSecurityPaperExcelVO();
//            //如果头像有
//            if (null != accreditationRecordsVo.getAvatar() && accreditationRecordsVo.getAvatar() != "") {
//                try {
//                    if (accreditationRecordsVo.getAvatar().contains("http:")) {
//                        exportSecurityPaperExcelVO.setAvatar(new URL(accreditationRecordsVo.getAvatar()));
//                    }
//                } catch (MalformedURLException e) {
//                    e.printStackTrace();
//                }
//            }
//            exportSecurityPaperExcelVO.setName(accreditationRecordsVo.getRealName()+accreditationRecordsVo.getIdCardNo());
//            //封装
//            list.add(exportSecurityPaperExcelVO);
//        });
//        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(), AccreditationRecordsVo.class)
////                .sheet("证书制证数据表")
////                .doWrite(excels);
//            // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
//            // {} 代表普通变量 {.} 代表是list的变量securityPaper.xlsx
//            String templateFileName = "xls" + File.separator + "securityPaper.xlsx";
////            Resource resource = new ClassPathResource("xls" + File.separator + "securityPaper.xlsx");
////            InputStream is = resource.getInputStream();
////            ByteArrayOutputStream os = ConvertUtil.parse(is);
//////            ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
////            ExcelWriter excelWriter = EasyExcel.write().file(os).withTemplate(templateFileName).build();
//
//            ExcelWriter excelWriter = null;
//            try {
//                excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate
//                    (new ClassPathResource(templateFileName).getInputStream()).build();
//            } catch (IOException e) {
//            }
//
//            //设置横向填充
//            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
//            WriteSheet writeSheet = EasyExcel.writerSheet().build();
//            excelWriter.fill(list, fillConfig, writeSheet);
////            //如果总数小于等于8
////            if (list.size()<=8){
////                WriteSheet writeSheet = EasyExcel.writerSheet().build();
////                excelWriter.fill(list, fillConfig, writeSheet);
////            }else {
////                //总数大于8
////                List<ExportSecurityPaperExcelVO> excelList = new ArrayList<>();
////                for (int i = 0; i < list.size(); i++) {
////                    //每次从集合中读取8个对象
////                    if (i != 0 && (i + 1) / 8 == 0) {
////                        WriteSheet writeSheet = EasyExcel.writerSheet().build();
////                        excelWriter.fill(excelList, fillConfig, writeSheet);
////                        //清空集合
////                        excelList.clear();
////                    } else {
////                        excelList.add(list.get(i));
////                    }
////                }
////                //判断余下的
////                if (excelList.size()>0){
////                    WriteSheet writeSheet = EasyExcel.writerSheet().build();
////                    excelWriter.fill(excelList, fillConfig, writeSheet);
////                }
////            }
//            // 别忘记关闭流
//            excelWriter.finish();
//        } catch (Throwable var6) {
//            throw var6;
//        }
//    }


}

 src/main/java/org/springblade/modules/accreditation/entity/AccreditationRecords.java

New file
@@ -0,0 +1,106 @@
package org.springblade.modules.accreditation.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

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

/**
 * 制证记录实体类
 * @author zhongrj
 * @time 2021-11-3
 */
@Data
@TableName("sys_accreditation_records")
public class AccreditationRecords implements Serializable {
    private static final long serialVersionUID = 1L;

    /**
     * 制证记录主键id,非自增
     */
    @TableId(value = "id",type = IdType.ASSIGN_ID)
    private Long id;

    /**
     * 制证人 user_id
     */
    @TableField("user_id")
    private Long userId;

    /**
     * 制证人 组织机构id
     */
//    @TableField("dept_id")
//    private Long deptId;

    /**
     * 申请时间
     */
    @TableField("create_time")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;

    /**
     * 创建人
     */
    @TableField("create_user")
    private String createUser;

    /**
     * 状态 1：未制证  2：已制证
     */
    private Integer status;

    /**
     * 制证时间
     */
    @TableField("accreditation_time")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date accreditationTime;

    /**
     * 状态 1：上岗证  2：证书
     */
    private Integer type;

    /**
     * 审核状态  1：待审核   2：审核通过   3：审核不通过
     */
    @TableField("audit_status")
    private Integer auditStatus;

    /**
     * 审核时间
     */
    @TableField("audit_time")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date auditTime;

    /**
     * 审核详情
     */
    @TableField("audit_detail")
    private String auditDetail;

    /**
     * 审核人员id
     */
    @TableField("audit_user")
    private Long auditUser;

    /**
     * 证书打印时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date paperTime;
}

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

New file
@@ -0,0 +1,108 @@
/*
 *      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 lombok.Data;

import java.io.Serializable;
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/excel/ExportSecurityPaperExcel.java

New file
@@ -0,0 +1,72 @@
/*
 *      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.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;

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

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

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

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

    @ColumnWidth(20)
    @ExcelProperty("性别")
    private String sex;

    @ColumnWidth(35)
    @ExcelProperty("企业名称")
    private String deptName;


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

    @ColumnWidth(20)
    @ExcelProperty("发证日期")
    private String paperTime;

    @ColumnWidth(12)
    @ExcelProperty("照片")
    private URL avatar;

    @ColumnWidth(16)
    @ExcelProperty("二维码图片")
    private byte[] qrCode;
}

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

New file
@@ -0,0 +1,35 @@
/*
 *      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 lombok.Data;

import java.io.Serializable;

/**
 * ExamPaymentExcelVO
 * @author zhongrj
 * @since 2021-11-16
 */
@Data
public class ExportSecurityPaperExcelVO implements Serializable {
    private static final long serialVersionUID = 1L;

    private String avatar;

    private String name;
}

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

New file
@@ -0,0 +1,68 @@
package org.springblade.modules.accreditation.mapper;

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.entity.AccreditationRecords;
import org.springblade.modules.accreditation.excel.ExportSecurityBookPaperExcel;
import org.springblade.modules.accreditation.vo.AccreditationRecordsVo;

import java.util.List;

/**
 * 制证记录Mapper 接口
 * @author zhongrj
 */
public interface AccreditationRecordsMapper extends BaseMapper<AccreditationRecords> {



    /**
     * 自定义分页
     *
     * @param page   分页
     * @param recruitment 实体
     * @return
     */
    List<AccreditationRecordsVo> selectAccreditationRecordsPage(IPage<AccreditationRecordsVo> page, @Param("accreditationRecords") AccreditationRecordsVo recruitment);

    /**
     * 自定义详情信息
     * @param accreditationRecords
     * @return
     */
    AccreditationRecordsVo getAccreditationRecordsDetails(@Param("accreditationRecords") AccreditationRecords accreditationRecords);

    /**
     * 导出保安员证信息
     * @param accreditationRecords
     * @return
     */
    List<AccreditationRecordsVo> exportSecurityPaperList(@Param("accreditationRecords") AccreditationRecordsVo accreditationRecords);

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

    /**
     * 导出证书制证信息(包含照片)
     */
    List<AccreditationRecordsVo> exportSecurityBookPapers(@Param("accreditationRecords") AccreditationRecordsVo accreditationRecords);

    /**
     * 根据用户 id 查询上岗证申请记录
     * @param id 用户id
     * @return
     */
    int getAccreditationRecordsByUserIdCount(@Param("userId") String id);

    /**
     * 根据用户 id 查询当前人员是否有待审核和审核通过的记录数
     * @param id 用户id
     * @return
     */
    int getAccreditationRecordsByUserIdAuditCount(@Param("userId") String id,@Param("type") Integer type);
}

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

New file
@@ -0,0 +1,445 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.springblade.modules.accreditation.mapper.AccreditationRecordsMapper">

    <!--制证申请分页信息-->
    <select id="selectAccreditationRecordsPage" resultType="org.springblade.modules.accreditation.vo.AccreditationRecordsVo">
        SELECT
            sar.*,
            bt.dept_name AS deptName,
            bu.real_name realName,
            IF(mod(SUBSTR(bu.cardid,17,1),2),1,2) sex,
            bu.cardid idCardNo,
            bu.securitynumber securityNumber,
            bu.avatar,
            ifnull(DATE_FORMAT(NOW(), '%Y') - SUBSTRING( bu.cardid,7,4),0) age,
            bu1.real_name applyName,
            bt1.dept_name applyUnit,
            bu.registered,
            bu.user_type 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==1">
            and sar.status = #{accreditationRecords.status}
        </if>
        <if test="accreditationRecords.status==2">
            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.userType!=null">
            and bu.user_type = #{accreditationRecords.userType}
        </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.id desc
    </select>

    <!--自定义详情信息-->
    <select id="getAccreditationRecordsDetails" resultType="org.springblade.modules.accreditation.vo.AccreditationRecordsVo">
        SELECT
        sar.*,
        bt.dept_name AS deptName,
        bu.real_name realName,
        IF(mod(SUBSTR(bu.cardid,17,1),2),1,2) sex,
        bu.cardid idCardNo,
        bu.securitynumber securityNumber,
        bu.avatar,
        ifnull(DATE_FORMAT(NOW(), '%Y') - SUBSTRING( bu.cardid,7,4),0) age
        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
        WHERE
        1=1
        and sar.id =#{accreditationRecords.id}
    </select>


    <!--导出保安员证信息(上岗证)-->
    <select id="exportSecurityPaperList" resultType="org.springblade.modules.accreditation.vo.AccreditationRecordsVo">
        SELECT
        sar.*,
        bt.dept_name AS deptName,
        bu.real_name realName,
        IF(mod(SUBSTR(bu.cardid,17,1),2),1,2) sex,
        bu.cardid idCardNo,
        bu.securitynumber securityNumber,
        bu.avatar,
        ifnull(DATE_FORMAT(NOW(), '%Y') - SUBSTRING( bu.cardid,7,4),0) age
        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.status==1">
            and sar.status = #{accreditationRecords.status}
        </if>
        <if test="accreditationRecords.status==2">
            and sar.status = #{accreditationRecords.status}
        </if>
        <if test="accreditationRecords.securityNumber!=null and  accreditationRecords.securityNumber!=''">
            and bu.securitynumber like concat('%', #{accreditationRecords.securityNumber},'%')
        </if>
        <if test="accreditationRecords.userType!=null">
            and bu.user_type = #{accreditationRecords.userType}
        </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>
        <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.auditStatus!=null">
            and sar.audit_status = #{accreditationRecords.auditStatus}
        </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.id desc
    </select>


    <!--导出证书制证信息-->
    <select id="exportSecurityBookPaper" resultType="org.springblade.modules.accreditation.excel.ExportSecurityBookPaperExcel">
        SELECT
        bu.real_name realName,
        IF(mod(SUBSTR(bu.cardid,17,1),2),1,2) 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.status==1">
            and sar.status = #{accreditationRecords.status}
        </if>
        <if test="accreditationRecords.status==2">
            and sar.status = #{accreditationRecords.status}
        </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.userType!=null">
            and bu.user_type = #{accreditationRecords.userType}
        </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.id desc
    </select>


    <!--导出证书制证信息(包含照片)-->
    <select id="exportSecurityBookPapers" resultType="org.springblade.modules.accreditation.vo.AccreditationRecordsVo">
        SELECT
        sar.*,
        bt.dept_name AS deptName,
        bu.real_name realName,
        IF(mod(SUBSTR(bu.cardid,17,1),2),1,2) sex,
        bu.cardid idCardNo,
        bu.securitynumber securityNumber,
        bu.avatar,
        ifnull(DATE_FORMAT(NOW(), '%Y') - SUBSTRING( bu.cardid,7,4),0) age,
        bu1.real_name applyName,
        bt1.dept_name applyUnit,
        bu.registered,
        bu.user_type 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.status==1">
            and sar.status = #{accreditationRecords.status}
        </if>
        <if test="accreditationRecords.status==2">
            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.userType!=null">
            and bu.user_type = #{accreditationRecords.userType}
        </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.id desc
    </select>

    <!--根据用户 id 查询上岗证申请记录-->
    <select id="getAccreditationRecordsByUserIdCount" resultType="java.lang.Integer">
        select count(*) from sys_accreditation_records where user_id = #{userId} and type = 1
    </select>


    <!--根据用户 id 查询当前人员是否有待审核和审核通过的记录数-->
    <select id="getAccreditationRecordsByUserIdAuditCount" resultType="java.lang.Integer">
        select count(*) from sys_accreditation_records where (audit_status = 1 or audit_status = 2) and user_id = #{userId} and type = #{type}
    </select>
</mapper>

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

New file
@@ -0,0 +1,63 @@
package org.springblade.modules.accreditation.service;

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.excel.ExportSecurityPaperExcel;
import org.springblade.modules.accreditation.vo.AccreditationRecordsVo;

import java.util.List;

/**
 * 制证记录服务类
 * @author zhongrj
 */
public interface AccreditationRecordsService extends IService<AccreditationRecords> {

    /**
     * 自定义分页
     * @param page
     * @param accreditationRecords
     * @return
     */
    IPage<AccreditationRecordsVo> selectAccreditationRecordsPage(IPage<AccreditationRecordsVo> page, AccreditationRecordsVo accreditationRecords);

    /**
     * 制证申请(批量）
     * @param accreditationRecords 制证记录信息对象
     */
    boolean securityApply(AccreditationRecordsVo accreditationRecords);

    /**
     * 自定义详情信息
     * @param accreditationRecords
     * @return
     */
    AccreditationRecordsVo getAccreditationRecordsDetails(AccreditationRecords accreditationRecords);

    /**
     * 导出保安员证信息
     * @param accreditationRecords
     * @return
     */
    List<ExportSecurityPaperExcel> exportSecurityPaperList(AccreditationRecordsVo accreditationRecords);

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

    /**
     * 导出证书制证信息(包含照片)
     */
    List<AccreditationRecordsVo> exportSecurityBookPapers(AccreditationRecordsVo accreditationRecords);

    /**
     * 批量修改为已制证
     * @param accreditationRecords 制证记录信息对象
     */
    boolean batchAccreditation(AccreditationRecordsVo accreditationRecords);
}

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

New file
@@ -0,0 +1,212 @@

package org.springblade.modules.accreditation.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.AllArgsConstructor;
import org.springblade.common.utils.QRCodeUtil;
import org.springblade.modules.accreditation.entity.AccreditationRecords;
import org.springblade.modules.accreditation.excel.ExportSecurityBookPaperExcel;
import org.springblade.modules.accreditation.excel.ExportSecurityPaperExcel;
import org.springblade.modules.accreditation.mapper.AccreditationRecordsMapper;
import org.springblade.modules.accreditation.service.AccreditationRecordsService;
import org.springblade.modules.accreditation.vo.AccreditationRecordsVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * 制证记录服务实现类
 *
 * @author zhongrj
 */
@Service
@AllArgsConstructor
public class AccreditationRecordsServiceImpl extends ServiceImpl<AccreditationRecordsMapper, AccreditationRecords> implements AccreditationRecordsService {

    /**
     * 自定义分页
     *
     * @param page
     * @param accreditationRecords
     * @return
     */
    @Override
    public IPage<AccreditationRecordsVo> selectAccreditationRecordsPage(IPage<AccreditationRecordsVo> page, AccreditationRecordsVo accreditationRecords) {
        List<AccreditationRecordsVo> accreditationRecordsVos = baseMapper.selectAccreditationRecordsPage(page, accreditationRecords);
        return page.setRecords(accreditationRecordsVos);
    }

    /**
     * 制证申请(批量）
     *
     * @param accreditationRecords 制证记录信息对象
     */
    @Override
    public boolean securityApply(AccreditationRecordsVo accreditationRecords) {
        //读取ids
        List<String> list = Arrays.asList(accreditationRecords.getIds().split(","));
        //遍历
        list.forEach(id -> {
            AccreditationRecords records = new AccreditationRecords();
            records.setCreateTime(new Date());
            records.setCreateUser(accreditationRecords.getCreateUser());
            records.setStatus(1);
            records.setAuditStatus(1);
            records.setType(accreditationRecords.getType());
            records.setUserId(Long.parseLong(id));
            //判断类型，如果是上岗证则判断是否有申请，有申请的不在新增记录
            if (accreditationRecords.getType() == 1) {
                //查询当前人员是否有申请记录
                int count = baseMapper.getAccreditationRecordsByUserIdCount(id);
                if (count < 1) {
                    //新增
                    this.save(records);
                }
            }
            //判断类型，如果是证书的，审核未通过的可以再次申请，审核通过的，暂时不给于新增记录
            if (accreditationRecords.getType() == 2) {
                //查询当前人员是否有待审核和审核通过的记录数
                int count = baseMapper.getAccreditationRecordsByUserIdAuditCount(id, 2);
                if (count < 1) {
                    //新增
                    this.save(records);
                }
            }

        });
        return true;
    }

    /**
     * 自定义详情信息
     *
     * @param accreditationRecords
     * @return
     */
    @Override
    public AccreditationRecordsVo getAccreditationRecordsDetails(AccreditationRecords accreditationRecords) {
        return baseMapper.getAccreditationRecordsDetails(accreditationRecords);
    }

    /**
     * 导出保安员证信息
     *
     * @param accreditationRecords
     * @return
     */
    @Override
    public List<ExportSecurityPaperExcel> exportSecurityPaperList(AccreditationRecordsVo accreditationRecords) {
        //查询数据
        List<AccreditationRecordsVo> accreditationRecordsVos = baseMapper.exportSecurityPaperList(accreditationRecords);
        if (accreditationRecordsVos.size() > 0) {
            //返回的集合数据
            List<ExportSecurityPaperExcel> list = new ArrayList<>();
            //遍历
            accreditationRecordsVos.forEach(accreditationRecordsVo -> {
                //导出即修改为已制证状态
                AccreditationRecords accreditationRecords1 = new AccreditationRecords();
                accreditationRecords1.setId(accreditationRecordsVo.getId());
                accreditationRecords1.setStatus(2);
                //记录导出时间为制证时间
                accreditationRecords1.setPaperTime(new Date());
                //修改
                this.updateById(accreditationRecords1);

                ExportSecurityPaperExcel excel = new ExportSecurityPaperExcel();
                //数据转换封装
                excel.setRealName(accreditationRecordsVo.getRealName());
                excel.setIdCardNo(accreditationRecordsVo.getIdCardNo());
                excel.setDeptName(accreditationRecordsVo.getDeptName());
                if (accreditationRecordsVo.getSex().equals("1")) {
                    excel.setSex("男");
                } else {
                    excel.setSex("女");
                }
                excel.setPaperTime(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
                //如果头像有
                if (null != accreditationRecordsVo.getAvatar() && accreditationRecordsVo.getAvatar() != "") {
                    try {
                        if (accreditationRecordsVo.getAvatar().contains("http:")) {
                            excel.setAvatar(new URL(accreditationRecordsVo.getAvatar()));
                        }
                    } catch (MalformedURLException e) {
                        e.printStackTrace();
                    }
                }
                //如果保安证编号不为空
                if (null != accreditationRecordsVo.getSecurityNumber() && accreditationRecordsVo.getSecurityNumber() != "") {
                    //去生成二维码
                    String url = "http://61.131.136.25:2080/securityInfo.html";
                    String encoded = null;
                    try {
                        encoded = URLEncoder.encode(accreditationRecordsVo.getSecurityNumber(), "UTF-8");
                        String content = url + "?securityNumber=" + encoded;
                        URLEncoder.encode(content, "utf-8");
                        byte[] qrCodeImage = QRCodeUtil.getQRCodeImage(content, 350, 350);
                        //设置二维码
                        excel.setQrCode(qrCodeImage);
                        excel.setSecurityNumber(accreditationRecordsVo.getSecurityNumber());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                list.add(excel);
            });
            return list;
        }
        return null;
    }

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

    /**
     * 导出证书制证信息(包含照片)
     */
    @Override
    public List<AccreditationRecordsVo> exportSecurityBookPapers(AccreditationRecordsVo accreditationRecords) {
        //查询数据
        List<AccreditationRecordsVo> accreditationRecordsVos = baseMapper.exportSecurityBookPapers(accreditationRecords);
//        System.out.println("accreditationRecordsVos = " + accreditationRecordsVos.size());
        return accreditationRecordsVos;
    }

    /**
     * 批量修改为已制证
     *
     * @param accreditationRecords 制证记录信息对象
     */
    @Override
    public boolean batchAccreditation(AccreditationRecordsVo accreditationRecords) {
        //读取ids
        List<String> list = Arrays.asList(accreditationRecords.getIds().split(","));
        //遍历
        list.forEach(id -> {
            AccreditationRecords records = new AccreditationRecords();
            records.setStatus(2);
            records.setId(Long.parseLong(id));
            //更新
            this.updateById(records);
        });
        return true;
    }
}

 src/main/java/org/springblade/modules/accreditation/vo/AccreditationRecordsVo.java

New file
@@ -0,0 +1,100 @@
package org.springblade.modules.accreditation.vo;

import lombok.Data;
import org.springblade.modules.accreditation.entity.AccreditationRecords;

import java.io.Serializable;

/**
 * 制证记录vo
 * @author zhongrj
 * @since 2021-11-2
 */
@Data
public class AccreditationRecordsVo extends AccreditationRecords implements Serializable {

    /**
     * 制证人员姓名
     */
    private String realName;

    /**
     * 制证人员组织机构名称
     */
    private String deptName;

    /**
     * 性别
     */
    private String sex;

    /**
     * 制证人员身份证号
     */
    private String idCardNo;

    /**
     * 头像
     */
    private String avatar;

    /**
     * 保安证二维码
     */
    private String qrCode;

    /**
     * 保安证编号
     */
    private String securityNumber;

    /**
     * 年龄
     */
    private Integer age;

    /**
     * 申请ids
     */
    private String ids;

    /**
     *开始时间
     */
    private String startTime;

    /**
     * 结束时间
     */
    private String endTime;

    /**
     * 是否有头像  1:有  2：没有上传
     */
    private Integer isAvatar;

    /**
     * 申请人姓名
     */
    private String applyName;

    /**
     * 申请人单位
     */
    private String applyUnit;

    /**
     * 身份证地址
     */
    private String registered;


    /**
     * 是否制证  6：是  7：否
     */
    private Integer userType;

    private Long deptId;

    private String jurisdiction;
}