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<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){
|
return R.data(accreditationRecordsService.save(accreditationRecords));
|
}
|
|
|
|
/**
|
* 制证申请(批量）
|
* @param accreditationRecords 制证记录信息对象
|
*/
|
@PostMapping("/securityApply")
|
@ApiOperation(value = "新增", notes = "传入accreditationRecords")
|
public R securityApply(@RequestBody AccreditationRecordsVo accreditationRecords){
|
return R.data(accreditationRecordsService.securityApply(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<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){
|
//修改为未制证状态
|
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<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(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<ExportSecurityBookPaperExcel> excels = accreditationRecordsService.exportSecurityBookPaper(accreditationRecords);
|
String fileName = null;
|
try {
|
response.setContentType("application/vnd.ms-excel");
|
response.setCharacterEncoding(org.apache.commons.codec.Charsets.UTF_8.name());
|
fileName = URLEncoder.encode("证书制证数据"+ DateUtil.time(), Charsets.UTF_8.name());
|
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
|
//修改单元格格式为文本格式
|
EasyExcel.write(response.getOutputStream(), ExportSecurityBookPaperExcel.class)
|
.sheet("证书制证数据表")
|
.doWrite(excels);
|
} catch (Throwable var6) {
|
throw var6;
|
}
|
}
|
|
|
/**
|
* 导出证书制证信息(包含照片)
|
*/
|
@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;
|
// }
|
// }
|
|
|
}
|
