package org.springblade.modules.accreditation.controller; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.enums.WriteDirectionEnum; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.metadata.fill.FillConfig; 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.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.*; import org.springblade.common.utils.ConvertUtil; 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.FTP.FtpUtil; 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.system.entity.User; import org.springblade.modules.system.service.IUserService; import org.springblade.modules.accreditation.entity.AccreditationRecords; import org.springblade.modules.accreditation.service.AccreditationRecordsService; import org.springblade.modules.accreditation.vo.AccreditationRecordsVo; import org.springframework.core.io.ClassPathResource; import org.springframework.core.io.Resource; import org.springframework.web.bind.annotation.*; import javax.imageio.ImageIO; import javax.servlet.http.HttpServletResponse; import java.awt.image.BufferedImage; import java.io.*; import java.net.MalformedURLException; import java.net.URL; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.*; /** * @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> page(AccreditationRecordsVo accreditationRecords, Query query) { IPage 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); //内网同步 //内网新增 String s = "insert into sys_accreditation_records(id,user_id,create_time,create_user,status,type,audit_status) " + "values(" + "'" + accreditationRecords.getId() + "'" + "," + "'" + accreditationRecords.getUserId() + "'" + "," + "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getCreateTime()) + "'" + "," + "'" + accreditationRecords.getCreateUser() +"'" + "," + "'" + accreditationRecords.getStatus() +"'" + "," + "'" + accreditationRecords.getType() +"'" + "," +"'" + accreditationRecords.getAuditStatus() + "'" + ");" + "update blade_user set user_type = " + "'" + user.getUserType() + "'" + ",update_time = " + "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getUpdateTime()) + "'" + " " + "where id = " + "'" + user.getId() + "'"; FtpUtil.sqlFileUpload(s); 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()); user.setUserType(7); user.setUpdateTime(new Date()); //更新 userService.updateById(user); //内网同步 String s = "update sys_accreditation_records set audit_status = " + accreditationRecords.getAuditStatus() + "" + ",audit_detail = " + accreditationRecords.getAuditDetail() + "" + ",audit_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getAuditTime()) + "" + ",audit_user = " + accreditationRecords.getAuditUser() + " " + "where id = " + "'" + accreditationRecords.getId() + "';" + "update blade_user set user_type = " + user.getUserType() + "" + ",update_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getUpdateTime()) + "" + "where id = " + "'" + user.getId() + "'"; FtpUtil.sqlFileUpload(s); }else { //内网同步 String s = "update sys_accreditation_records set audit_status = " + accreditationRecords.getAuditStatus() + "" + ",audit_detail = " + accreditationRecords.getAuditDetail() + "" + ",audit_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getAuditTime()) + "" + ",audit_user = " + accreditationRecords.getAuditUser() + " " + "where id = " + "'" + accreditationRecords.getId() + "'"; FtpUtil.sqlFileUpload(s); } //返回 return R.status(b); } /** * 批量审核 * @param accreditationRecords 制证记录信息对象 */ @PostMapping("/batchAudit") public R batchAudit(@RequestBody AccreditationRecordsVo accreditationRecords){ //取出申请id String ids = accreditationRecords.getIds(); List list = Arrays.asList(ids.split(",")); //批量审核 list.forEach(id->{ accreditationRecords.setId(Long.parseLong(id)); accreditationRecords.setAuditTime(new Date()); accreditationRecordsService.updateById(accreditationRecords); //审核通过 if (accreditationRecords.getAuditStatus()==2){ //修改为未制证状态 AccreditationRecords records = accreditationRecordsService.getById(id); User user = userService.getById(records.getUserId()); user.setUserType(7); user.setUpdateTime(new Date()); //更新 userService.updateById(user); //内网同步 String s = "update sys_accreditation_records set audit_status = " + accreditationRecords.getAuditStatus() + "" + ",audit_detail = " + accreditationRecords.getAuditDetail() + "" + ",audit_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getAuditTime()) + "" + ",audit_user = " + accreditationRecords.getAuditUser() + " " + "where id = " + "'" + accreditationRecords.getId() + "';" + "update blade_user set user_type = " + user.getUserType() + "" + ",update_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getUpdateTime()) + "" + "where id = " + "'" + user.getId() + "'"; FtpUtil.sqlFileUpload(s); }else { //内网同步 String s = "update sys_accreditation_records set audit_status = " + accreditationRecords.getAuditStatus() + "" + ",audit_detail = " + accreditationRecords.getAuditDetail() + "" + ",audit_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getAuditTime()) + "" + ",audit_user = " + accreditationRecords.getAuditUser() + " " + "where id = " + "'" + accreditationRecords.getId() + "'"; FtpUtil.sqlFileUpload(s); } }); //返回 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 detail(AccreditationRecords accreditationRecords) { AccreditationRecords detail = accreditationRecordsService.getOne(Condition.getQueryWrapper(accreditationRecords)); return R.data(detail); } /** * 详情 * @param accreditationRecords 制证记录信息对象 */ @GetMapping("/details") @ApiOperation(value = "详情", notes = "传入accreditationRecords") public R 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 excels = accreditationRecordsService.exportSecurityPaperList(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(), 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 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; } } /** * 导出证书制证信息(包含照片) */ @GetMapping("export-security-book-papers") public void exportSecurityBookPapers(AccreditationRecordsVo accreditationRecords, HttpServletResponse response) throws Exception { //按条件查询成绩数据 List excels = accreditationRecordsService.exportSecurityBookPapers(accreditationRecords); List 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 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(org.apache.commons.codec.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 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 excels = accreditationRecordsService.exportSecurityBookPapers(accreditationRecords); // List 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 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; // } // } }