src/main/java/org/springblade/common/excel/MySheetWriteHandler.java
New file @@ -0,0 +1,45 @@ package org.springblade.common.excel; import com.alibaba.excel.write.handler.SheetWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; public class MySheetWriteHandler implements SheetWriteHandler { @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { Workbook workbook = writeWorkbookHolder.getWorkbook(); Sheet sheet = workbook.getSheetAt(0); Row row1 = sheet.createRow(0); row1.setHeight((short) 500); Cell cell = row1.createCell(0); //设置单元格内容 cell.setCellValue("附件2"); //设置标题 Row row2 = sheet.createRow(1); row2.setHeight((short) 800); Cell cell1 = row2.createCell(0); cell1.setCellValue("存量建筑垃圾堆体治理进度月报表"); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.CENTER); Font font = workbook.createFont(); font.setBold(true); font.setFontHeight((short) 400); cellStyle.setFont(font); cell1.setCellStyle(cellStyle); sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 17)); //设置填表日期,填报人,联系方式 Row row3 = sheet.createRow(2); row3.setHeight((short) 500); row3.createCell(1).setCellValue("填表日期"); row3.createCell(11).setCellValue("填表人"); row3.createCell(15).setCellValue("联系方式"); } } src/main/java/org/springblade/common/utils/ConvertUtil.java
New file @@ -0,0 +1,55 @@ package org.springblade.common.utils; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.InputStream; import java.io.OutputStream; public class ConvertUtil { // inputStream转outputStream public static ByteArrayOutputStream parse(final InputStream in) throws Exception { final ByteArrayOutputStream swapStream = new ByteArrayOutputStream(); int ch; while ((ch = in.read()) != -1) { swapStream.write(ch); } return swapStream; } // outputStream转inputStream public ByteArrayInputStream parse(final OutputStream out) throws Exception { ByteArrayOutputStream baos = new ByteArrayOutputStream(); baos = (ByteArrayOutputStream) out; final ByteArrayInputStream swapStream = new ByteArrayInputStream(baos.toByteArray()); return swapStream; } // inputStream转String public String parse_String(final InputStream in) throws Exception { final ByteArrayOutputStream swapStream = new ByteArrayOutputStream(); int ch; while ((ch = in.read()) != -1) { swapStream.write(ch); } return swapStream.toString(); } // OutputStream 转String public String parse_String(final OutputStream out) throws Exception { ByteArrayOutputStream baos = new ByteArrayOutputStream(); baos = (ByteArrayOutputStream) out; final ByteArrayInputStream swapStream = new ByteArrayInputStream(baos.toByteArray()); return swapStream.toString(); } // String转inputStream public ByteArrayInputStream parse_inputStream(final String in) throws Exception { final ByteArrayInputStream input = new ByteArrayInputStream(in.getBytes()); return input; } // String 转outputStream public ByteArrayOutputStream parse_outputStream(final String in) throws Exception { return parse(parse_inputStream(in)); } } src/main/java/org/springblade/modules/accreditation/controller/AccreditationRecordsController.java
@@ -1,11 +1,20 @@ 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; @@ -14,15 +23,22 @@ 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.io.IOException; 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.*; @@ -274,4 +290,261 @@ } /** * 导出证书制证信息(包含照片) */ @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:")) { //画图的顶级管理器，一个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, "jpg", 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++; //写入表格 writeBookForEach(excelList,rowNum,workBook,sheet,patriarch); } } //导出数据 response.setContentType("application/vnd.ms-excel; charset=UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + "证书打印信息导出"+DateUtil.time() + ".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:")) { //画图的顶级管理器，一个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, "jpg", 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/excel/ExportSecurityPaperExcelVO.java
New file @@ -0,0 +1,40 @@ /* * 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; /** * 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
@@ -26,7 +26,7 @@ * @param recruitment 实体 * @return */ List<AccreditationRecordsVo> selectAccreditationRecordsPage(IPage page, @Param("accreditationRecords") AccreditationRecordsVo recruitment); List<AccreditationRecordsVo> selectAccreditationRecordsPage(IPage<AccreditationRecordsVo> page, @Param("accreditationRecords") AccreditationRecordsVo recruitment); /** * 自定义详情信息 @@ -48,4 +48,9 @@ * @return */ List<ExportSecurityBookPaperExcel> exportSecurityBookPaper(@Param("accreditationRecords") AccreditationRecordsVo accreditationRecords); /** * 导出证书制证信息(包含照片) */ List<AccreditationRecordsVo> exportSecurityBookPapers(@Param("accreditationRecords") AccreditationRecordsVo accreditationRecords); } src/main/java/org/springblade/modules/accreditation/mapper/AccreditationRecordsMapper.xml
@@ -8,7 +8,8 @@ sar.*, bt.dept_name AS deptName, bu.real_name realName, bu.sex,bu.cardid idCardNo, 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, @@ -73,6 +74,9 @@ <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> @@ -91,7 +95,6 @@ <if test="accreditationRecords.endTime!=null and accreditationRecords.endTime!='' and accreditationRecords.endTime!='undefined'"> and sar.create_time <= #{accreditationRecords.endTime} </if> order by sar.create_time desc </select> <!--自定义详情信息--> @@ -100,7 +103,8 @@ sar.*, bt.dept_name AS deptName, bu.real_name realName, bu.sex,bu.cardid idCardNo, 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 @@ -126,7 +130,8 @@ sar.*, bt.dept_name AS deptName, bu.real_name realName, bu.sex,bu.cardid idCardNo, 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 @@ -172,6 +177,9 @@ <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> @@ -202,7 +210,6 @@ <if test="accreditationRecords.endTime!=null and accreditationRecords.endTime!='' and accreditationRecords.endTime!='undefined'"> and sar.create_time <= #{accreditationRecords.endTime} </if> order by sar.create_time desc </select> @@ -210,7 +217,8 @@ <select id="exportSecurityBookPaper" resultType="org.springblade.modules.accreditation.excel.ExportSecurityBookPaperExcel"> SELECT bu.real_name realName, bu.sex,bu.cardid idCardNo, 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, @@ -271,6 +279,9 @@ <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> @@ -295,6 +306,101 @@ <if test="accreditationRecords.endTime!=null and accreditationRecords.endTime!='' and accreditationRecords.endTime!='undefined'"> and sar.create_time <= #{accreditationRecords.endTime} </if> order by sar.create_time 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.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 >= #{accreditationRecords.startTime} </if> <if test="accreditationRecords.endTime!=null and accreditationRecords.endTime!='' and accreditationRecords.endTime!='undefined'"> and sar.create_time <= #{accreditationRecords.endTime} </if> </select> </mapper> src/main/java/org/springblade/modules/accreditation/service/AccreditationRecordsService.java
@@ -49,4 +49,9 @@ * @return */ List<ExportSecurityBookPaperExcel> exportSecurityBookPaper(AccreditationRecordsVo accreditationRecords); /** * 导出证书制证信息(包含照片) */ List<AccreditationRecordsVo> exportSecurityBookPapers(AccreditationRecordsVo accreditationRecords); } src/main/java/org/springblade/modules/accreditation/service/impl/AccreditationRecordsServiceImpl.java
@@ -152,4 +152,15 @@ 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; } } src/main/java/org/springblade/modules/exam/excel/ExamScoreExcel.java
@@ -56,9 +56,9 @@ @ExcelProperty("准考证号*") private String candidateNo; // @ColumnWidth(15) // @ExcelProperty("理论得分*") // private Integer theoryGrade; @ColumnWidth(15) @ExcelProperty("理论得分*") private Integer theoryGrade; @ColumnWidth(15) @ExcelProperty("实操得分*") src/main/java/org/springblade/modules/exam/service/impl/ExamScoreServiceImpl.java
@@ -3,9 +3,7 @@ import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import lombok.AllArgsConstructor; import org.apache.commons.lang3.StringUtils; import org.springblade.common.utils.arg; import org.springblade.core.log.exception.ServiceException; import org.springblade.core.mp.support.Condition; import org.springblade.modules.FTP.FtpUtil; @@ -23,7 +21,6 @@ import org.springblade.modules.exam.vo.ExamResultVO; import org.springblade.modules.exam.vo.ExamScoreVO; import org.springblade.modules.exam.vo.UpdateParamVo; import org.springblade.modules.system.entity.Role; import org.springblade.modules.system.entity.User; import org.springblade.modules.system.service.IRoleService; import org.springblade.modules.system.service.IUserService; @@ -39,8 +36,7 @@ import java.text.SimpleDateFormat; import java.util.*; import java.util.concurrent.atomic.AtomicBoolean; import static com.bstek.ureport.expression.model.condition.Join.and; import java.util.concurrent.atomic.AtomicInteger; /** * 考试成绩服务实现类 @@ -370,30 +366,30 @@ // List<Object> errorList = new ArrayList<>(); // //导入状态，默认为true ,如果有一个出现问题则为 false // AtomicBoolean status = new AtomicBoolean(true); // AtomicInteger num = new AtomicInteger(); // //遍历 // examScoreExcelList.forEach(examScoreExcel -> { // //根据身份证号查询培训信息 // User userInfoByIdCardNo = userService.getUserInfoByIdCardNo(examScoreExcel.getIdCardNo()); // TrainingRegistration registration = new TrainingRegistration(); // registration.setUserId(userInfoByIdCardNo.getId().toString()); // TrainingRegistration one1 = trainingRegistrationService.getOne(Condition.getQueryWrapper(registration)); // User user = userService.getUserInfoByIdCardNo(examScoreExcel.getIdCardNo()); //// TrainingRegistration registration = new TrainingRegistration(); //// registration.setCandidateNo(examScoreExcel.getCandidateNo()); //// TrainingRegistration one1 = trainingRegistrationService.getOne(Condition.getQueryWrapper(registration)); //// 测试生成考试成绩 // ExamScore examScore = new ExamScore(); // examScore.setCandidateNo(one1.getCandidateNo()); // ExamScore examScore1 = new ExamScore(); // examScore1.setCandidateNo(examScoreExcel.getCandidateNo()); // examScore1.setUserId(user.getId().toString()); // //根据准考证号和用户id查询考试成绩 // ExamScore examScore = this.getOne(Condition.getQueryWrapper(examScore1)); // examScore.setQualified(0); // //根据身份证号查询用户 // examScore.setUserId(userInfoByIdCardNo.getId().toString()); // examScore.setExamId(one1.getTrainExamId()); // examScore.setApplyId(one1.getId()); // examScore.setLearnGrade(examScoreExcel.getLearnGrade()); // examScore.setTheoryGrade(examScoreExcel.getTheoryGrade()); // examScore.setAllGrade((examScore.getLearnGrade()+examScore.getTheoryGrade())/2); // // //新增 // this.save(examScore); // this.updateById(examScore); // // //生成准考证号 // if (null==userInfoByIdCardNo.getSecuritynumber() || userInfoByIdCardNo.getSecuritynumber().equals("") ) { // if (null==user.getSecuritynumber() || user.getSecuritynumber().equals("") ) { // //去生成保安证编号 // String pre = SecurityPaperUtil.getSecurityPaper(); // //查询当前年份已有的保安证编号 @@ -407,12 +403,13 @@ // count++; // result = pre + (decimalFormat.format(count)); // } // userInfoByIdCardNo.setSecuritynumber(result); // user.setSecuritynumber(result); // //修改为持证保安 // userInfoByIdCardNo.setHold("1"); // userInfoByIdCardNo.setRegistered(examScoreExcel.getExamName()); // user.setHold("1"); //// user.setRegistered(examScoreExcel.getExamName()); // //更新保安数据 // userService.updateById(userInfoByIdCardNo); // userService.updateById(user); // num.getAndIncrement(); // } // }); // //如果所有数据导入有一个异常 @@ -420,6 +417,9 @@ // String errorAccount = StringUtils.join(errorList, "\\\n"); // throw new ServiceException("用户:["+errorAccount+"]实操成绩导入失败!账号与准考证号不匹配!"); // } // if (status.get()){ // throw new ServiceException("更新成功："+ num.get()); // } // } // }
@@ -54,7 +54,7 @@ <select id="selectUserPages" resultMap="userResultMap"> select distinct bu.*, bu.*,IF(mod(SUBSTR(bu.cardid,17,1),2),1,2) sexs, sll.longitude,sll.latitude, bd.dept_name from @@ -233,7 +233,7 @@ <!--保安员列表--> <select id="selectUserPageSecurity" resultMap="userResultMap"> select bu.* bu.*,IF(mod(SUBSTR(bu.cardid,17,1),2),1,2) sexs from blade_user bu left join src/main/java/org/springblade/modules/system/service/impl/UserServiceImpl.java
@@ -39,6 +39,8 @@ import org.springblade.core.tool.support.Kv; import org.springblade.core.tool.utils.*; import org.springblade.modules.FTP.FtpUtil; import org.springblade.modules.accreditation.entity.AccreditationRecords; import org.springblade.modules.accreditation.service.AccreditationRecordsService; import org.springblade.modules.auth.enums.UserEnum; import org.springblade.modules.dispatcher.vo.DispatcherVO; import org.springblade.modules.information.entity.Information; @@ -66,6 +68,7 @@ import java.text.SimpleDateFormat; import java.util.*; import java.util.concurrent.atomic.AtomicBoolean; import java.util.concurrent.atomic.AtomicInteger; import java.util.stream.Collectors; /** @@ -85,6 +88,7 @@ private final IInformationService iInformationService; private final IDeptService deptService; private final JurisdictionService jurisdictionService; private final AccreditationRecordsService accreditationRecordsService; @Override @Transactional(rollbackFor = Exception.class) @@ -1169,6 +1173,7 @@ // List<String> errorList = new ArrayList<>(); // //导入状态，默认为true ,如果有一个出现问题则为 false // AtomicBoolean status = new AtomicBoolean(true); // AtomicInteger count = new AtomicInteger(); // data.forEach(userExcel -> { // //判断当前用户是否已在本单位，如果是的更新数据 // User user1 = new User(); @@ -1179,9 +1184,18 @@ // if (null!=user2){ // if (null!=userExcel.getRegistered() && userExcel.getRegistered()!=""){ // user2.setRegistered(userExcel.getRegistered()); // user2.setUserType(7); //// user2.setUserType(7); // } // this.updateById(user2); // count.getAndIncrement(); // //// AccreditationRecords accreditationRecords = new AccreditationRecords(); //// accreditationRecords.setType(2); //// accreditationRecords.setAuditStatus(2); //// accreditationRecords.setUserId(user2.getId()); //// accreditationRecords.setStatus(1); //// accreditationRecords.setCreateTime(new Date()); //// accreditationRecordsService.save(accreditationRecords); // }else { // errorList.add(userExcel.getCardid()); // } @@ -1191,6 +1205,10 @@ // String errorAccount = StringUtils.join(errorList, "\\\n"); // throw new ServiceException("用户:["+errorAccount+"]导入失败!已在其他单位存在!"); // } // if (status.get()){ // String errorAccount = StringUtils.join(errorList, "\\\n"); // throw new ServiceException("成功导入用户:"+count.get()); // } // }
@@ -123,5 +123,10 @@ */ private String trainingUnitId; /** * 根据身份证计算性别 */ private Integer sexs; } src/main/resources/application-test.yml
@@ -13,7 +13,7 @@ # commandTimeout: 5000 datasource: # MySql url: jdbc:mysql://223.82.109.183:2083/zhbaw-test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true url: jdbc:mysql://223.82.109.183:2083/zhbaw?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true username: root password: zhba0728 @@ -39,7 +39,7 @@ #ftp 设置 ftp: sqlConnect: jdbc:mysql://223.82.109.183:2083/zhbaw-test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true sqlConnect: jdbc:mysql://223.82.109.183:2083/zhbaw?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true ftpHost: 192.168.0.199 ftpPort: 21 ftpUserName: arsn src/main/resources/application.yml
@@ -210,6 +210,7 @@ - /blade-desk/** - /directive/** - /blade-system/** - /blade-user/** - /blade-resource/** - /liveLocation/** - /qrCode/** src/main/resources/xls/export_package_template.xlsx
src/main/resources/xls/securityPaper.xlsxBinary files differ
