From 7293f04d18fb2e882e152f962c3aa1d8fcb1f93e Mon Sep 17 00:00:00 2001
From: Administrator <admin>
Date: Wed, 17 Nov 2021 15:46:35 +0800
Subject: [PATCH] 证书打印信息导出修改
---
src/main/java/org/springblade/modules/accreditation/service/impl/AccreditationRecordsServiceImpl.java | 11 +
src/main/java/org/springblade/modules/system/mapper/UserMapper.xml | 4
src/main/java/org/springblade/common/utils/ConvertUtil.java | 55 +++++
src/main/java/org/springblade/modules/accreditation/mapper/AccreditationRecordsMapper.xml | 120 ++++++++++
src/main/resources/xls/securityPaper.xlsx | 0
src/main/java/org/springblade/modules/exam/service/impl/ExamScoreServiceImpl.java | 42 +-
src/main/java/org/springblade/common/excel/MySheetWriteHandler.java | 45 ++++
src/main/resources/application-test.yml | 4
src/main/java/org/springblade/modules/accreditation/controller/AccreditationRecordsController.java | 275 ++++++++++++++++++++++++
src/main/java/org/springblade/modules/accreditation/mapper/AccreditationRecordsMapper.java | 7
src/main/java/org/springblade/modules/system/service/impl/UserServiceImpl.java | 20 +
src/main/java/org/springblade/modules/exam/excel/ExamScoreExcel.java | 6
src/main/java/org/springblade/modules/accreditation/service/AccreditationRecordsService.java | 5
src/main/java/org/springblade/modules/accreditation/excel/ExportSecurityPaperExcelVO.java | 40 +++
src/main/resources/xls/export_package_template.xlsx | 0
src/main/resources/application.yml | 1
src/main/java/org/springblade/modules/system/vo/UserVO.java | 5
17 files changed, 602 insertions(+), 38 deletions(-)
diff --git a/src/main/java/org/springblade/common/excel/MySheetWriteHandler.java b/src/main/java/org/springblade/common/excel/MySheetWriteHandler.java
new file mode 100644
index 0000000..b614835
--- /dev/null
+++ b/src/main/java/org/springblade/common/excel/MySheetWriteHandler.java
@@ -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("联系方式");
+ }
+}
diff --git a/src/main/java/org/springblade/common/utils/ConvertUtil.java b/src/main/java/org/springblade/common/utils/ConvertUtil.java
new file mode 100644
index 0000000..9217095
--- /dev/null
+++ b/src/main/java/org/springblade/common/utils/ConvertUtil.java
@@ -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));
+ }
+}
diff --git a/src/main/java/org/springblade/modules/accreditation/controller/AccreditationRecordsController.java b/src/main/java/org/springblade/modules/accreditation/controller/AccreditationRecordsController.java
index 4900cc0..4e72d3a 100644
--- a/src/main/java/org/springblade/modules/accreditation/controller/AccreditationRecordsController.java
+++ b/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;
+// }
+// }
+
+
}
diff --git a/src/main/java/org/springblade/modules/accreditation/excel/ExportSecurityPaperExcelVO.java b/src/main/java/org/springblade/modules/accreditation/excel/ExportSecurityPaperExcelVO.java
new file mode 100644
index 0000000..8e5470d
--- /dev/null
+++ b/src/main/java/org/springblade/modules/accreditation/excel/ExportSecurityPaperExcelVO.java
@@ -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;
+}
diff --git a/src/main/java/org/springblade/modules/accreditation/mapper/AccreditationRecordsMapper.java b/src/main/java/org/springblade/modules/accreditation/mapper/AccreditationRecordsMapper.java
index 181efb3..789d5df 100644
--- a/src/main/java/org/springblade/modules/accreditation/mapper/AccreditationRecordsMapper.java
+++ b/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);
}
diff --git a/src/main/java/org/springblade/modules/accreditation/mapper/AccreditationRecordsMapper.xml b/src/main/java/org/springblade/modules/accreditation/mapper/AccreditationRecordsMapper.xml
index e8db204..317e9c1 100644
--- a/src/main/java/org/springblade/modules/accreditation/mapper/AccreditationRecordsMapper.xml
+++ b/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>
diff --git a/src/main/java/org/springblade/modules/accreditation/service/AccreditationRecordsService.java b/src/main/java/org/springblade/modules/accreditation/service/AccreditationRecordsService.java
index 3c0893f..bdc7240 100644
--- a/src/main/java/org/springblade/modules/accreditation/service/AccreditationRecordsService.java
+++ b/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);
}
diff --git a/src/main/java/org/springblade/modules/accreditation/service/impl/AccreditationRecordsServiceImpl.java b/src/main/java/org/springblade/modules/accreditation/service/impl/AccreditationRecordsServiceImpl.java
index 7860264..3d19de5 100644
--- a/src/main/java/org/springblade/modules/accreditation/service/impl/AccreditationRecordsServiceImpl.java
+++ b/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;
+ }
}
diff --git a/src/main/java/org/springblade/modules/exam/excel/ExamScoreExcel.java b/src/main/java/org/springblade/modules/exam/excel/ExamScoreExcel.java
index 74b074b..aa747f6 100644
--- a/src/main/java/org/springblade/modules/exam/excel/ExamScoreExcel.java
+++ b/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("实操得分*")
diff --git a/src/main/java/org/springblade/modules/exam/service/impl/ExamScoreServiceImpl.java b/src/main/java/org/springblade/modules/exam/service/impl/ExamScoreServiceImpl.java
index ec73a36..8c750f6 100644
--- a/src/main/java/org/springblade/modules/exam/service/impl/ExamScoreServiceImpl.java
+++ b/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());
+// }
// }
// }
diff --git a/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml b/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml
index 96c11eb..822efbb 100644
--- a/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml
+++ b/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml
@@ -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
diff --git a/src/main/java/org/springblade/modules/system/service/impl/UserServiceImpl.java b/src/main/java/org/springblade/modules/system/service/impl/UserServiceImpl.java
index 0d955ae..c62718f 100644
--- a/src/main/java/org/springblade/modules/system/service/impl/UserServiceImpl.java
+++ b/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());
+// }
// }
diff --git a/src/main/java/org/springblade/modules/system/vo/UserVO.java b/src/main/java/org/springblade/modules/system/vo/UserVO.java
index a0e8276..345ad9e 100644
--- a/src/main/java/org/springblade/modules/system/vo/UserVO.java
+++ b/src/main/java/org/springblade/modules/system/vo/UserVO.java
@@ -123,5 +123,10 @@
*/
private String trainingUnitId;
+ /**
+ * 根据身份证计算性别
+ */
+ private Integer sexs;
+
}
diff --git a/src/main/resources/application-test.yml b/src/main/resources/application-test.yml
index 73dbff2..d971c80 100644
--- a/src/main/resources/application-test.yml
+++ b/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
diff --git a/src/main/resources/application.yml b/src/main/resources/application.yml
index c439993..12ac43b 100644
--- a/src/main/resources/application.yml
+++ b/src/main/resources/application.yml
@@ -210,6 +210,7 @@
- /blade-desk/**
- /directive/**
- /blade-system/**
+ - /blade-user/**
- /blade-resource/**
- /liveLocation/**
- /qrCode/**
diff --git a/src/main/resources/xls/export_package_template.xlsx b/src/main/resources/xls/export_package_template.xlsx
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/src/main/resources/xls/export_package_template.xlsx
diff --git a/src/main/resources/xls/securityPaper.xlsx b/src/main/resources/xls/securityPaper.xlsx
new file mode 100644
index 0000000..a1207ec
--- /dev/null
+++ b/src/main/resources/xls/securityPaper.xlsx
Binary files differ
--
Gitblit v1.9.3