From f41f7a7efb363425a88e1a060f81f785ba2b57a8 Mon Sep 17 00:00:00 2001
From: Administrator <admin>
Date: Fri, 29 Oct 2021 21:00:04 +0800
Subject: [PATCH] 新增保安员证相关信息导出接口（包含图片，二维码）
---
src/main/java/org/springblade/modules/system/mapper/UserMapper.xml | 53 +++++++
src/main/java/org/springblade/common/excel/CustomCellWriteWeightConfig.java | 83 +++++++++++
src/main/java/org/springblade/modules/exam/service/ExamScoreService.java | 4
src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.java | 1
src/main/java/org/springblade/modules/system/service/impl/UserServiceImpl.java | 60 ++++++++
src/main/java/org/springblade/common/excel/CustomCellWriteHeightConfig.java | 49 +++++++
src/main/java/org/springblade/modules/system/controller/UserController.java | 31 ++++
src/main/java/org/springblade/modules/system/excel/ExportSecurityPaperExcel.java | 64 +++++++++
src/main/java/org/springblade/modules/exam/controller/ExamScoreController.java | 2
src/main/java/org/springblade/modules/system/mapper/UserMapper.java | 7 +
src/main/java/org/springblade/modules/system/service/IUserService.java | 9 +
11 files changed, 362 insertions(+), 1 deletions(-)
diff --git a/src/main/java/org/springblade/common/excel/CustomCellWriteHeightConfig.java b/src/main/java/org/springblade/common/excel/CustomCellWriteHeightConfig.java
new file mode 100644
index 0000000..5530055
--- /dev/null
+++ b/src/main/java/org/springblade/common/excel/CustomCellWriteHeightConfig.java
@@ -0,0 +1,49 @@
+package org.springblade.common.excel;
+
+import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Row;
+
+import java.util.Iterator;
+
+/**
+ * 自适应行高
+ * @author zhongrj
+ * @since 2021-10-29
+ */
+public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
+ /**
+ * 默认高度
+ */
+ private static final Integer DEFAULT_HEIGHT = 300;
+
+ @Override
+ protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
+ }
+
+ @Override
+ protected void setContentColumnHeight(Row row, int relativeRowIndex) {
+ Iterator<Cell> cellIterator = row.cellIterator();
+ if (!cellIterator.hasNext()) {
+ return;
+ }
+
+ // 默认为 1行高度
+ Integer maxHeight = 1;
+ while (cellIterator.hasNext()) {
+ Cell cell = cellIterator.next();
+ switch (cell.getCellTypeEnum()) {
+ case STRING:
+ if (cell.getStringCellValue().indexOf("\n") != -1) {
+ int length = cell.getStringCellValue().split("\n").length;
+ maxHeight = Math.max(maxHeight, length);
+ }
+ break;
+ default:
+ break;
+ }
+ }
+
+ row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
+ }
+}
diff --git a/src/main/java/org/springblade/common/excel/CustomCellWriteWeightConfig.java b/src/main/java/org/springblade/common/excel/CustomCellWriteWeightConfig.java
new file mode 100644
index 0000000..c8ec134
--- /dev/null
+++ b/src/main/java/org/springblade/common/excel/CustomCellWriteWeightConfig.java
@@ -0,0 +1,83 @@
+package org.springblade.common.excel;
+
+import com.alibaba.excel.enums.CellDataTypeEnum;
+import com.alibaba.excel.metadata.CellData;
+import com.alibaba.excel.metadata.Head;
+import com.alibaba.excel.util.CollectionUtils;
+import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
+import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Sheet;
+
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+/**
+ * 自适应行款
+ * @author zhongrj
+ * @since 2021-10-29
+ */
+public class CustomCellWriteWeightConfig extends AbstractColumnWidthStyleStrategy {
+ private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
+
+ @Override
+ protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
+ boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
+ if (needSetWidth) {
+ Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
+ if (maxColumnWidthMap == null) {
+ maxColumnWidthMap = new HashMap<>();
+ CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
+ }
+
+ Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
+ if (columnWidth >= 0) {
+ if (columnWidth > 254) {
+ columnWidth = 254;
+ }
+
+ Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
+ if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
+ maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
+ Sheet sheet = writeSheetHolder.getSheet();
+ sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
+ }
+
+ }
+ }
+ }
+
+ /**
+ * 计算长度
+ * @param cellDataList
+ * @param cell
+ * @param isHead
+ * @return
+ */
+ private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
+ if (isHead) {
+ return cell.getStringCellValue().getBytes().length;
+ } else {
+ CellData cellData = cellDataList.get(0);
+ CellDataTypeEnum type = cellData.getType();
+ if (type == null) {
+ return -1;
+ } else {
+ switch (type) {
+ case STRING:
+ // 换行符（数据需要提前解析好）
+ int index = cellData.getStringValue().indexOf("\n");
+ return index != -1 ?
+ cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
+ case BOOLEAN:
+ return cellData.getBooleanValue().toString().getBytes().length;
+ case NUMBER:
+ return cellData.getNumberValue().toString().getBytes().length;
+ default:
+ return -1;
+ }
+ }
+ }
+ }
+}
diff --git a/src/main/java/org/springblade/modules/exam/controller/ExamScoreController.java b/src/main/java/org/springblade/modules/exam/controller/ExamScoreController.java
index f3a1e4b..f3155ab 100644
--- a/src/main/java/org/springblade/modules/exam/controller/ExamScoreController.java
+++ b/src/main/java/org/springblade/modules/exam/controller/ExamScoreController.java
@@ -28,6 +28,7 @@
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.excel.ExportSecurityPaperExcel;
import org.springblade.modules.system.excel.UserExcel;
import org.springblade.modules.system.service.IRoleService;
import org.springblade.modules.system.service.IUserService;
@@ -425,7 +426,6 @@
//导出成绩集合数据
ExcelUtil.export(response, "成绩数据", "成绩数据表", examScoreExcels, ExportExamScoreExcel.class);
}
-
/**
* 考试成绩恢复状态重置（重考）
diff --git a/src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.java b/src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.java
index b70c398..014d5d6 100644
--- a/src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.java
+++ b/src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.java
@@ -6,6 +6,7 @@
import org.springblade.modules.exam.entity.ExamScore;
import org.springblade.modules.exam.excel.ExportExamScoreExcel;
import org.springblade.modules.exam.vo.ExamScoreVO;
+import org.springblade.modules.system.vo.UserVO;
import org.springblade.modules.training.entity.TrainingRegistration;
import java.util.List;
diff --git a/src/main/java/org/springblade/modules/exam/service/ExamScoreService.java b/src/main/java/org/springblade/modules/exam/service/ExamScoreService.java
index 99033ca..36a63fc 100644
--- a/src/main/java/org/springblade/modules/exam/service/ExamScoreService.java
+++ b/src/main/java/org/springblade/modules/exam/service/ExamScoreService.java
@@ -7,6 +7,7 @@
import org.springblade.modules.exam.excel.ExportExamScoreExcel;
import org.springblade.modules.exam.vo.ExamScoreVO;
import org.springblade.modules.exam.vo.UpdateParamVo;
+import org.springblade.modules.system.excel.ExportSecurityPaperExcel;
import java.util.List;
import java.util.Map;
@@ -90,4 +91,7 @@
* @return
*/
Boolean refreshTrainExamScore();
+
+
+
}
diff --git a/src/main/java/org/springblade/modules/system/controller/UserController.java b/src/main/java/org/springblade/modules/system/controller/UserController.java
index a3fe945..1888ff1 100644
--- a/src/main/java/org/springblade/modules/system/controller/UserController.java
+++ b/src/main/java/org/springblade/modules/system/controller/UserController.java
@@ -33,6 +33,8 @@
import org.springblade.common.cache.DictCache;
import org.springblade.common.config.FtpConfig;
import org.springblade.common.enums.DictEnum;
+import org.springblade.common.excel.CustomCellWriteHeightConfig;
+import org.springblade.common.excel.CustomCellWriteWeightConfig;
import org.springblade.common.excel.RowWriteHandler;
import org.springblade.common.utils.arg;
import org.springblade.core.cache.utils.CacheUtil;
@@ -50,6 +52,7 @@
import org.springblade.core.tool.support.Kv;
import org.springblade.core.tool.utils.*;
import org.springblade.modules.FTP.FtpUtil;
+import org.springblade.modules.exam.excel.ExportExamScoreExcel;
import org.springblade.modules.experience.entity.Experience;
import org.springblade.modules.experience.service.IExperienceService;
import org.springblade.modules.jurisdiction.entity.Jurisdiction;
@@ -413,6 +416,34 @@
// }
/**
+ * 导出保安员证信息
+ */
+ @GetMapping("export-security-paper")
+ public void exportSecurityPaper(UserVO user, HttpServletResponse response) throws IOException {
+ //按条件查询成绩数据
+ List<ExportSecurityPaperExcel> excels = userService.exportSecurityPaperList(user);
+ String fileName = null;
+ try {
+ response.setContentType("application/vnd.ms-excel");
+ response.setCharacterEncoding(org.apache.commons.codec.Charsets.UTF_8.name());
+ fileName = URLEncoder.encode("保安员证数据", 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-template")
diff --git a/src/main/java/org/springblade/modules/system/excel/ExportSecurityPaperExcel.java b/src/main/java/org/springblade/modules/system/excel/ExportSecurityPaperExcel.java
new file mode 100644
index 0000000..b41ac5b
--- /dev/null
+++ b/src/main/java/org/springblade/modules/system/excel/ExportSecurityPaperExcel.java
@@ -0,0 +1,64 @@
+/*
+ * 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.system.excel;
+
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.alibaba.excel.annotation.write.style.ColumnWidth;
+import com.alibaba.excel.annotation.write.style.ContentRowHeight;
+import com.alibaba.excel.annotation.write.style.HeadRowHeight;
+import lombok.Data;
+
+import java.io.Serializable;
+import java.net.URL;
+
+/**
+ * ExamPaymentExcel
+ * @author zhongrj
+ * @since 2021-10-29
+ */
+@Data
+@ColumnWidth(25)
+@HeadRowHeight(20)
+@ContentRowHeight(125)
+public class ExportSecurityPaperExcel implements Serializable {
+ private static final long serialVersionUID = 1L;
+
+ @ColumnWidth(15)
+ @ExcelProperty("姓名")
+ private String realName;
+
+ @ColumnWidth(20)
+ @ExcelProperty("身份证号码")
+ private String idCardNo;
+
+ @ColumnWidth(35)
+ @ExcelProperty("所属公司")
+ private String deptName;
+
+
+ @ColumnWidth(20)
+ @ExcelProperty("保安证编号")
+ private String securityNumber;
+
+ @ColumnWidth(20)
+ @ExcelProperty("照片")
+ private URL avatar;
+
+ @ColumnWidth(20)
+ @ExcelProperty("二维码图片")
+ private byte[] qrCode;
+}
diff --git a/src/main/java/org/springblade/modules/system/mapper/UserMapper.java b/src/main/java/org/springblade/modules/system/mapper/UserMapper.java
index 00086c7..1af5851 100644
--- a/src/main/java/org/springblade/modules/system/mapper/UserMapper.java
+++ b/src/main/java/org/springblade/modules/system/mapper/UserMapper.java
@@ -206,4 +206,11 @@
* @return
*/
List<Map<String, Object>> getSecurityInfo(@Param("jurisdiction")String jurisdiction);
+
+ /**
+ * 导出保安员证信息
+ * @param user
+ * @return
+ */
+ List<UserVO> exportSecurityPaperList(@Param("user") UserVO user);
}
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 c4802b1..cc4e627 100644
--- a/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml
+++ b/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml
@@ -575,4 +575,57 @@
and bu.is_deleted = 0
and br.role_alias = "保安"
</select>
+
+
+ <!--导出保安员证信息-->
+ <select id="exportSecurityPaperList" resultType="org.springblade.modules.system.vo.UserVO">
+ select
+ bu.*,bd.dept_name deptName
+ from
+ blade_user bu
+ left join
+ blade_dept bd
+ on
+ bu.dept_id = bd.id
+ left join
+ blade_role br
+ on
+ br.id = bu.role_id
+ where
+ bu.is_deleted = 0
+ and role_alias = '保安'
+ <if test="user.realName!=null and user.realName != ''">
+ and bu.real_name like concat(concat('%', #{user.realName}),'%')
+ </if>
+ <if test="user.userType!=null and user.userType != ''">
+ and bu.user_type = #{user.userType}
+ </if>
+ <if test="user.nativeplace!=null and user.nativeplace != ''">
+ and bu.nativeplace = #{user.nativeplace}
+ </if>
+ <if test="user.deptId!=null and user.deptId != ''">
+ and bu.dept_id = #{user.deptId}
+ </if>
+ <if test="user.sex!=null and user.sex != ''">
+ and bu.sex = #{user.sex}
+ </if>
+ <if test="user.dispatch!=null and user.dispatch != ''">
+ <if test="user.dispatch==1">
+ and (bu.dispatch = #{user.dispatch} or bu.dispatch is null)
+ </if>
+ <if test="user.dispatch==0">
+ and bu.dispatch = #{user.dispatch}
+ </if>
+ </if>
+ <if test="user.status!=null and user.status != ''">
+ and bu.status = #{user.status}
+ </if>
+ <if test="user.jurisdiction!=null and user.jurisdiction != ''">
+ and bu.jurisdiction = #{user.jurisdiction}
+ </if>
+ <if test="user.securitynumber!=null and user.securitynumber != ''">
+ and bu.securitynumber = #{user.securitynumber}
+ </if>
+ ORDER BY bu.id
+ </select>
</mapper>
diff --git a/src/main/java/org/springblade/modules/system/service/IUserService.java b/src/main/java/org/springblade/modules/system/service/IUserService.java
index 53ddb25..a2f9939 100644
--- a/src/main/java/org/springblade/modules/system/service/IUserService.java
+++ b/src/main/java/org/springblade/modules/system/service/IUserService.java
@@ -23,9 +23,11 @@
import org.springblade.core.mp.base.BaseService;
import org.springblade.core.mp.support.Query;
import org.springblade.modules.auth.enums.UserEnum;
+import org.springblade.modules.exam.vo.ExamScoreVO;
import org.springblade.modules.system.entity.User;
import org.springblade.modules.system.entity.UserInfo;
import org.springblade.modules.system.entity.UserOauth;
+import org.springblade.modules.system.excel.ExportSecurityPaperExcel;
import org.springblade.modules.system.excel.QrCodeExcel;
import org.springblade.modules.system.excel.UserExcel;
import org.springblade.modules.system.node.TreeNode;
@@ -323,4 +325,11 @@
* @param deptId
*/
void importQrCode(List<QrCodeExcel> data, Boolean isCovered, String deptId);
+
+ /**
+ * 导出保安员证信息
+ * @param user
+ * @return
+ */
+ List<ExportSecurityPaperExcel> exportSecurityPaperList(UserVO user);
}
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 eba795a..553ae83 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
@@ -44,6 +44,7 @@
import org.springblade.modules.information.entity.Information;
import org.springblade.modules.information.service.IInformationService;
import org.springblade.modules.system.entity.*;
+import org.springblade.modules.system.excel.ExportSecurityPaperExcel;
import org.springblade.modules.system.excel.QrCodeExcel;
import org.springblade.modules.system.excel.UserExcel;
import org.springblade.modules.system.mapper.UserMapper;
@@ -51,6 +52,10 @@
import org.springblade.modules.system.service.*;
import org.springblade.modules.system.vo.UserVO;
import org.springblade.modules.system.wrapper.UserWrapper;
+import org.springframework.http.HttpHeaders;
+import org.springframework.http.HttpStatus;
+import org.springframework.http.MediaType;
+import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@@ -58,6 +63,8 @@
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
+import java.net.MalformedURLException;
+import java.net.URL;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
@@ -883,4 +890,57 @@
}
});
}
+
+
+ /**
+ * 导出保安员证信息
+ * @param user
+ * @return
+ */
+ @Override
+ public List<ExportSecurityPaperExcel> exportSecurityPaperList(UserVO user) {
+ //查询数据
+ List<UserVO> userVOList = baseMapper.exportSecurityPaperList(user);
+ if (userVOList.size()>0) {
+ //返回的集合数据
+ List<ExportSecurityPaperExcel> list = new ArrayList<>();
+ //遍历
+ userVOList.forEach(userVO -> {
+ ExportSecurityPaperExcel excel = new ExportSecurityPaperExcel();
+ //数据转换封装
+ excel.setRealName(userVO.getRealName());
+ excel.setIdCardNo(userVO.getCardid());
+ excel.setDeptName(userVO.getDeptName());
+ //如果头像有
+ if (null != userVO.getAvatar() && userVO.getAvatar() != "") {
+ try {
+ if (userVO.getAvatar().contains("http:")) {
+ excel.setAvatar(new URL(userVO.getAvatar()));
+ }
+ } catch (MalformedURLException e) {
+ e.printStackTrace();
+ }
+ }
+ //如果保安证编号不为空
+ if (null != userVO.getSecuritynumber() && userVO.getSecuritynumber() != "") {
+ //去生成二维码
+ String url = "http://223.82.109.183:2080/securityInfo.html";
+ String encoded = null;
+ try {
+ encoded = URLEncoder.encode(userVO.getSecuritynumber(), "UTF-8");
+ String content = url + "?securityNumber=" + encoded;
+ URLEncoder.encode(content, "utf-8");
+ byte[] qrCodeImage = QRCodeUtil.getQRCodeImage(content, 350, 350);
+ //设置二维码
+ excel.setQrCode(qrCodeImage);
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+ list.add(excel);
+ });
+ return list;
+ }
+ return null;
+ }
}
--
Gitblit v1.9.3