package com.gk.hotwork.Service.ServiceImpl;
|
|
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
|
import com.gk.hotwork.Domain.*;
|
import com.gk.hotwork.Domain.Exception.BusinessException;
|
import com.gk.hotwork.Domain.Utils.*;
|
import com.gk.hotwork.Domain.Vo.DepartmentVo;
|
import com.gk.hotwork.Service.*;
|
import org.apache.commons.lang3.ObjectUtils;
|
import org.apache.log4j.LogManager;
|
import org.apache.log4j.Logger;
|
import org.apache.poi.hssf.usermodel.*;
|
import org.apache.poi.ss.usermodel.FormulaEvaluator;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.xssf.usermodel.*;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.util.*;
|
import java.util.regex.Pattern;
|
import java.util.stream.Collectors;
|
|
@Service("ExcelExportService")
|
public class ExcelExportServiceImpl implements ExcelExportService {
|
private Logger logger = LogManager.getLogger(getClass());
|
|
String[] districttitle = {"code", "name", "parentcode", "type"};
|
|
String[] userTitle = {"手机号", "密码", "姓名", "身份证", "单位", "部门"};
|
|
|
String[] supplierUserTitle = {"手机号", "密码", "姓名", "身份证", "邮箱", "部门", "职务",
|
"是否单位负责人", "是否部门负责人", "用户类型", "状态"};
|
|
String[] selfCheckTitle = {"作业类型","类型","检查类型", "检查内容", "参考判断", "排序", "备注"};
|
|
|
String[] riskEventTitle = {"安全风险分析单元名称", "安全风险事件名称"};
|
|
String[] riskAnaUnitTitle = {"安全风险分析对象编码", "安全风险分析单元名称", "责任部门", "责任人"};
|
|
String[] riskControlMeasureTitle = {"安全风险事件名称", "管控方式", "管控方式描述", "管控措施分类1"
|
, "管控措施分类2", "管控措施分类3", "隐患排查内容", "周期", "单位"};
|
|
String[] riskSourceTitle = {"生产装置", "风险等级", "可能导致事故的主要原因", "区域位置", "所属部门"};
|
//正整数
|
String pattern = "^[0-9]*[1-9][0-9]*$";
|
|
Pattern pattern2 = Pattern.compile("^(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,2})?$");
|
|
@Autowired
|
private DistrictService districtService;
|
@Autowired
|
private UserService userService;
|
@Autowired
|
private UserRolesService userRolesService;
|
@Autowired
|
private RoleService roleService;
|
@Autowired
|
SelfCheckService selfCheckService;
|
@Autowired
|
DepartmentService departmentService;
|
@Autowired
|
CompanyService companyService;
|
@Autowired
|
RiskEventService riskEventService;
|
@Autowired
|
RiskAnaUnitService riskAnaUnitService;
|
@Autowired
|
RiskControlMeasureService riskControlMeasureService;
|
@Autowired
|
RiskControlClassifyService riskControlClassifyService;
|
@Autowired
|
private RiskSourceService riskSourceService;
|
|
|
|
@Override
|
public BooleanReason importDistrictExcel(InputStream in, String operator, Boolean isExcel2007) {
|
BooleanReason blret = new BooleanReason();
|
List<DistrictInfo> districtInfoList = new ArrayList<>();
|
String retmsg = "";
|
blret.setValue(false);
|
try {
|
Workbook workbook = null;
|
if (isExcel2007) {
|
workbook = new XSSFWorkbook(in);
|
} else {
|
workbook = new HSSFWorkbook(in);
|
}
|
|
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
|
Sheet sheet = workbook.getSheetAt(0);
|
if (sheet == null) {
|
blret.addReason("excel 第1页不能为空");
|
return blret;
|
}
|
Row row = sheet.getRow(0);
|
// 标题总列数
|
int colNum = row.getPhysicalNumberOfCells();
|
String[] title = new String[colNum];
|
|
if (colNum != districttitle.length) {
|
blret.addReason("上传文件的列的个数错误");
|
return blret;
|
}
|
|
for (int i = 0; i < colNum; i++) {
|
title[i] = row.getCell(i).getStringCellValue();
|
if (StringUtils.isNotBlank(title[i])) {
|
title[i] = title[i].trim();
|
}
|
if (!title[i].equals((districttitle[i]))) {
|
blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + districttitle[i]);
|
return blret;
|
}
|
}
|
|
for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
|
int realrow = irow + 1;
|
DistrictInfo districtInfo = new DistrictInfo();
|
Object value = null;
|
row = sheet.getRow(irow);
|
|
value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,编码不能为空.<br/>";
|
continue;
|
} else {
|
districtInfo.setCode(value.toString().trim());
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,名称不能为空.<br/>";
|
continue;
|
} else {
|
districtInfo.setName(value.toString().trim());
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,父编码不能为空.<br/>";
|
continue;
|
} else {
|
districtInfo.setParentcode(value.toString().trim());
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,类型不能为空.<br/>";
|
continue;
|
} else {
|
districtInfo.setType(Byte.valueOf(value.toString()));
|
}
|
|
districtInfoList.add(districtInfo);
|
}
|
|
for (DistrictInfo districtInfo : districtInfoList) {
|
districtService.save(districtInfo);
|
}
|
|
if (StringUtils.isNotBlank(retmsg)) {
|
retmsg = retmsg + "导入成功" + districtInfoList.size() + "个，" + "导入失败" + (sheet.getLastRowNum() - districtInfoList.size()) + "个。";
|
blret.addReason(retmsg);
|
return blret;
|
}
|
} catch (IOException e) {
|
logger.error(e.getLocalizedMessage());
|
blret.addReason("导入错误");
|
return blret;
|
}
|
|
blret.setValue(true);
|
return blret;
|
}
|
|
@Override
|
@Transactional
|
public void importUserExcel(InputStream in, String username, Boolean isExcel2007) {
|
List<UserInfo> userInfoList = new ArrayList<>();
|
//准备单位和部门
|
Set<String> companySet = new HashSet<>();
|
List<CompanyInfo> companyInfos = companyService.selectByAll();
|
for (CompanyInfo companyInfo : companyInfos) {
|
companySet.add(companyInfo.getCompany());
|
}
|
Set<String> departmentSet = new HashSet<>();
|
List<DepartmentVo> departmentVos = departmentService.selectByAll();
|
for (DepartmentVo departmentVo : departmentVos) {
|
departmentSet.add(departmentVo.getDepartment());
|
}
|
try {
|
Workbook workbook = null;
|
if (isExcel2007) {
|
workbook = new XSSFWorkbook(in);
|
} else {
|
workbook = new HSSFWorkbook(in);
|
}
|
|
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
|
Sheet sheet = workbook.getSheetAt(0);
|
if (sheet == null) {
|
throw new BusinessException("excel 第1页不能为空");
|
}
|
Row row = sheet.getRow(0);
|
// 标题总列数
|
int colNum = row.getPhysicalNumberOfCells();
|
String[] title = new String[colNum];
|
|
if (colNum != userTitle.length) {
|
throw new BusinessException("上传文件的列的个数错误");
|
}
|
|
for (int i = 0; i < colNum; i++) {
|
title[i] = row.getCell(i).getStringCellValue();
|
if (StringUtils.isNotBlank(title[i])) {
|
title[i] = title[i].trim();
|
}
|
if (!title[i].equals((userTitle[i]))) {
|
throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + userTitle[i]);
|
}
|
}
|
|
for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
|
int realrow = irow + 1;
|
UserInfo userInfo = new UserInfo();
|
Object value = null;
|
row = sheet.getRow(irow);
|
|
value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,手机号不能为空.<br/>");
|
} else {
|
userInfo.setUsername(value.toString().trim());
|
if (userInfo.getUsername().length() != 11) {
|
throw new BusinessException("手机号不为11");
|
}
|
if (userService.phoneIsOccupied(userInfo.getUsername())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,手机号重复.<br/>");
|
}
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,密码不能为空.<br/>");
|
} else {
|
userInfo.setPassword(MD5Utils.encode(value.toString().trim()));
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,姓名不能为空.<br/>");
|
} else {
|
userInfo.setRealname(value.toString().trim());
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
|
if (null != value && StringUtils.isNotBlank(value.toString())) {
|
userInfo.setIdcard(value.toString().trim());
|
if (!IdCardUtil.strongVerifyIdNumber(userInfo.getIdcard())) {
|
throw new BusinessException( "导入失败,第" + realrow + "行,身份证非法.<br/>");
|
}
|
if (userService.idCardIsOccupied(userInfo.getIdcard())) {
|
throw new BusinessException( "导入失败,第" + realrow + "行,身份证重复.<br/>");
|
}
|
|
}
|
|
|
|
value = ExcelProperty.getCellValue(row.getCell(4), evaluator);
|
if (null != value && StringUtils.isNotBlank(value.toString())) {
|
userInfo.setCompany(value.toString().trim());
|
if (!companySet.contains(userInfo.getCompany()))
|
throw new BusinessException("导入失败,第" + realrow + "行,单位不存在.<br/>");
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(5), evaluator);
|
if (null != value && StringUtils.isNotBlank(value.toString())) {
|
DepartmentInfo infoByName = departmentService.getInfoByName(value.toString().trim());
|
if (infoByName != null) {
|
userInfo.setDepartment(infoByName.getId());
|
}
|
|
if (!departmentSet.contains(userInfo.getDepartment())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,部门不存在.<br/>");
|
}
|
}
|
|
|
|
|
|
userInfo.setIsupload((byte) 0);
|
userInfo.setIsdel((byte) 0);
|
userInfo.setCreatedby(username);
|
userInfo.setCreateddate(new Date());
|
userInfo.setLastmodifiedby(username);
|
userInfo.setLastmodifieddate(new Date());
|
userInfo.setType(3);
|
userInfo.setStatus((byte)1);
|
userInfo.setIsdepartment((byte) 0);
|
userInfo.setIscompany((byte) 0);
|
userInfoList.add(userInfo);
|
}
|
//sheet 用户手机号和身份证重复判断
|
List<String> phoneList = userInfoList.stream().map(UserInfo::getUsername).distinct().collect(Collectors.toList());
|
if(phoneList.size() != userInfoList.size()) throw new BusinessException("本sheet中包含重复手机号");
|
List<String> idCardList = userInfoList.stream().map(UserInfo::getIdcard).distinct().collect(Collectors.toList());
|
if(idCardList.size() != userInfoList.size()) throw new BusinessException("本sheet中包含重复身份证号");
|
//保存用户信息 同时赋予橘色
|
|
RoleInfo role = roleService.getOne(new LambdaQueryWrapper<RoleInfo>().eq(RoleInfo::getName, "员工"));
|
if (role == null) throw new BusinessException("请查询角色是否存在员工");
|
for (UserInfo userInfo : userInfoList) {
|
//只赋予员工角色
|
userService.save(userInfo);
|
UserRolesInfo ur = new UserRolesInfo();
|
ur.setUserid(userInfo.getId());
|
ur.setRoleid(role.getId());
|
userRolesService.save(ur);
|
|
}
|
|
} catch (IOException e) {
|
logger.error(e.getLocalizedMessage());
|
throw new BusinessException("导入错误");
|
}
|
|
}
|
|
@Override
|
public BooleanReason importSupplierUserExcel(InputStream in, String username, String company, Boolean isExcel2007) {
|
BooleanReason blret = new BooleanReason();
|
List<UserInfo> userInfoList = new ArrayList<>();
|
String retmsg = "";
|
blret.setValue(false);
|
try {
|
Workbook workbook = null;
|
if (isExcel2007) {
|
workbook = new XSSFWorkbook(in);
|
} else {
|
workbook = new HSSFWorkbook(in);
|
}
|
|
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
|
Sheet sheet = workbook.getSheetAt(0);
|
if (sheet == null) {
|
blret.addReason("excel 第1页不能为空");
|
return blret;
|
}
|
Row row = sheet.getRow(0);
|
// 标题总列数
|
int colNum = row.getPhysicalNumberOfCells();
|
String[] title = new String[colNum];
|
|
if (colNum != supplierUserTitle.length) {
|
blret.addReason("上传文件的列的个数错误");
|
return blret;
|
}
|
|
for (int i = 0; i < colNum; i++) {
|
title[i] = row.getCell(i).getStringCellValue();
|
if (StringUtils.isNotBlank(title[i])) {
|
title[i] = title[i].trim();
|
}
|
if (!title[i].equals((supplierUserTitle[i]))) {
|
blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + supplierUserTitle[i]);
|
return blret;
|
}
|
}
|
|
for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
|
int realrow = irow + 1;
|
UserInfo userInfo = new UserInfo();
|
Object value = null;
|
row = sheet.getRow(irow);
|
|
value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,手机号不能为空.<br/>";
|
continue;
|
} else {
|
userInfo.setUsername(value.toString().trim());
|
List<UserInfo> userInfoExist = userService.selectUserInfo(null, userInfo.getUsername());
|
if (userInfoExist.size() > 0) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,手机号重复.<br/>";
|
continue;
|
}
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,密码不能为空.<br/>";
|
continue;
|
} else {
|
String PW_PATTERN = "(?=.*[A-Z])(?=.*[a-z])(?=.*[0-9])(?=.*[~!@#$%^&*_.]).{8,}";
|
if (!value.toString().trim().matches(PW_PATTERN)) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,密码必须8位以上,并且包含大小写字母、数字、特殊符号三种以上.<br/>";
|
continue;
|
} else {
|
userInfo.setPassword(Base64Encrypt.encode(value.toString().trim().getBytes()));
|
}
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,姓名不能为空.<br/>";
|
continue;
|
} else {
|
userInfo.setRealname(value.toString().trim());
|
UserInfo userInfoExist = userService.selectByRealName(null, userInfo.getRealname());
|
if (null != userInfoExist) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,姓名重复.<br/>";
|
continue;
|
}
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
|
if (null != value && StringUtils.isNotBlank(value.toString())) {
|
userInfo.setIdcard(value.toString().trim());
|
UserInfo idCardExist = userService.selectByIdCard(null, userInfo.getIdcard());
|
if (null != idCardExist) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,身份证重复.<br/>";
|
continue;
|
}
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(4), evaluator);
|
if (null != value && StringUtils.isNotBlank(value.toString())) {
|
userInfo.setEmail(value.toString().trim());
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(6), evaluator);
|
if (null != value && StringUtils.isNotBlank(value.toString())) {
|
DepartmentInfo infoByName = departmentService.getInfoByName(value.toString().trim());
|
if (infoByName != null) {
|
userInfo.setDepartment(infoByName.getId());
|
}
|
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(7), evaluator);
|
if (null != value && StringUtils.isNotBlank(value.toString())) {
|
userInfo.setJob(value.toString().trim());
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(8), evaluator);
|
if (null != value && StringUtils.isNotBlank(value.toString()) && value.toString().trim().equals("是")) {
|
userInfo.setIscompany((byte) 1);
|
} else {
|
userInfo.setIscompany((byte) 0);
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(9), evaluator);
|
if (null != value && StringUtils.isNotBlank(value.toString()) && value.toString().trim().equals("是")) {
|
userInfo.setIsdepartment((byte) 1);
|
} else {
|
userInfo.setIsdepartment((byte) 0);
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(10), evaluator);
|
if (null != value && StringUtils.isNotBlank(value.toString()) && value.toString().trim().equals("管理员")) {
|
userInfo.setType(2);
|
} else {
|
userInfo.setType(3);
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(11), evaluator);
|
if (null != value && StringUtils.isNotBlank(value.toString()) && value.toString().trim().equals("停用")) {
|
userInfo.setStatus((byte) 0);
|
} else {
|
userInfo.setStatus((byte) 1);
|
}
|
userInfo.setCompany(company);
|
userInfo.setIsupload((byte) 0);
|
userInfo.setIsdel((byte) 0);
|
userInfo.setCreatedby(username);
|
userInfo.setCreateddate(new Date());
|
userInfo.setLastmodifiedby(username);
|
userInfo.setLastmodifieddate(new Date());
|
userService.save(userInfo);
|
userInfoList.add(userInfo);
|
}
|
|
if (StringUtils.isNotBlank(retmsg)) {
|
retmsg = retmsg + "导入成功" + userInfoList.size() + "个，" + "导入失败" + (sheet.getLastRowNum() - userInfoList.size()) + "个。";
|
blret.addReason(retmsg);
|
return blret;
|
}
|
} catch (IOException e) {
|
logger.error(e.getLocalizedMessage());
|
blret.addReason("导入错误");
|
return blret;
|
}
|
|
blret.setValue(true);
|
return blret;
|
}
|
|
|
|
@Override
|
public BooleanReason imporSelfCheckExcel(InputStream in, String username, Boolean isExcel2007) {
|
BooleanReason blret = new BooleanReason();
|
List<SelfCheckInfo> selfCheckInfoList = new ArrayList<>();
|
String retmsg = "";
|
blret.setValue(false);
|
try {
|
Workbook workbook = null;
|
if (isExcel2007) {
|
workbook = new XSSFWorkbook(in);
|
} else {
|
workbook = new HSSFWorkbook(in);
|
}
|
|
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
|
Sheet sheet = workbook.getSheetAt(0);
|
if (sheet == null) {
|
blret.addReason("excel 第1页不能为空");
|
return blret;
|
}
|
Row row = sheet.getRow(0);
|
// 标题总列数
|
int colNum = row.getPhysicalNumberOfCells();
|
String[] title = new String[colNum];
|
|
if (colNum != selfCheckTitle.length) {
|
blret.addReason("上传文件的列的个数错误");
|
return blret;
|
}
|
|
for (int i = 0; i < colNum; i++) {
|
title[i] = row.getCell(i).getStringCellValue();
|
if (StringUtils.isNotBlank(title[i])) {
|
title[i] = title[i].trim();
|
}
|
if (!title[i].equals((selfCheckTitle[i]))) {
|
blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]);
|
return blret;
|
}
|
}
|
|
for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
|
int realrow = irow + 1;
|
SelfCheckInfo selfCheckInfo = new SelfCheckInfo();
|
Object value = null;
|
row = sheet.getRow(irow);
|
|
value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,作业类型不能为空.<br/>";
|
continue;
|
} else {
|
selfCheckInfo.setTasktype(value.toString().trim());
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,类型不能为空.<br/>";
|
continue;
|
} else {
|
selfCheckInfo.setType(value.toString().trim());
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,检查类型不能为空.<br/>";
|
continue;
|
} else {
|
selfCheckInfo.setChecktype(value.toString().trim());
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,检查内容不能为空.<br/>";
|
continue;
|
} else {
|
selfCheckInfo.setContent(value.toString().trim());
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(4), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
retmsg = retmsg + "导入失败,第" + realrow + "行,参考判断不能为空.<br/>";
|
continue;
|
} else {
|
selfCheckInfo.setStandard(value.toString().trim());
|
}
|
|
value = ExcelProperty.getCellValue(row.getCell(5), evaluator);
|
if (null != value && StringUtils.isNotBlank(value.toString())) {
|
try {
|
selfCheckInfo.setSort(Integer.parseInt(value.toString().trim()));
|
}catch (Exception e){
|
e.printStackTrace();
|
}
|
}
|
selfCheckInfo.setCreatedby(username);
|
selfCheckInfo.setModifiedby(username);
|
selfCheckInfo.setCreateddate(new Date());
|
selfCheckInfo.setModifieddate(new Date());
|
selfCheckInfo.setIsdel((byte)0);
|
selfCheckInfoList.add(selfCheckInfo);
|
}
|
selfCheckService.saveBatch(selfCheckInfoList);
|
|
if (StringUtils.isNotBlank(retmsg)) {
|
retmsg = retmsg + "导入成功" + selfCheckInfoList.size() + "个，" + "导入失败" + (sheet.getLastRowNum() - selfCheckInfoList.size()) + "个。";
|
blret.addReason(retmsg);
|
return blret;
|
}
|
} catch (IOException e) {
|
logger.error(e.getLocalizedMessage());
|
blret.addReason("导入错误");
|
return blret;
|
}
|
|
blret.setValue(true);
|
return blret;
|
}
|
|
@Override
|
public void importRiskEventExcel(InputStream in, String operator, Boolean isExcel2007) {
|
List<RiskEvent> riskEventList = new ArrayList<>();
|
//校验文件
|
try {
|
Workbook workbook = null;
|
if (isExcel2007) {
|
workbook = new XSSFWorkbook(in);
|
} else {
|
workbook = new HSSFWorkbook(in);
|
}
|
|
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
|
Sheet sheet = workbook.getSheetAt(0);
|
if (sheet == null) {
|
throw new BusinessException("excel 第1页不能为空");
|
}
|
Row row = sheet.getRow(0);
|
// 标题总列数
|
int colNum = row.getPhysicalNumberOfCells();
|
String[] title = new String[colNum];
|
|
if (colNum != riskEventTitle.length) {
|
throw new BusinessException("上传文件的列的个数错误");
|
}
|
|
for (int i = 0; i < colNum; i++) {
|
title[i] = row.getCell(i).getStringCellValue();
|
if (StringUtils.isNotBlank(title[i])) {
|
title[i] = title[i].trim();
|
}
|
if (!title[i].equals((riskEventTitle[i]))) {
|
throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + riskEventTitle[i]);
|
}
|
}
|
|
for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
|
int realrow = irow + 1;
|
RiskEvent riskEvent = new RiskEvent();
|
Object value = null;
|
Object value1 = null;
|
row = sheet.getRow(irow);
|
//安全风险分析单元
|
//TODO
|
value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
|
value1 = ExcelProperty.getCellValue(row.getCell(2), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,安全风险分析单元名称不能为空.<br/>");
|
} else {
|
String trim = value.toString().trim();
|
RiskAnaUnit infoByRiskUnitName = riskAnaUnitService.getInfoByRiskUnitName(value.toString().trim());
|
if (ObjectUtils.isEmpty(infoByRiskUnitName)){
|
throw new BusinessException("请检查风险分析单元名称是否正确或者是否有事件对应的分析单元");
|
}
|
riskEvent.setRiskUnitId(infoByRiskUnitName.getId());
|
}
|
//安全风险事件
|
value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,安全风险事件名称不能为空.<br/>");
|
} else {
|
riskEvent.setRiskEventName(value.toString().trim());
|
}
|
|
|
//设置属性
|
riskEvent.setValidFlag(Boolean.TRUE);
|
riskEvent.setUpdateBy(operator);
|
riskEvent.setCreateBy(operator);
|
riskEvent.setCreateTime(new Date());
|
riskEvent.setUpdateTime(new Date());
|
|
riskEventList.add(riskEvent);
|
}
|
|
for (RiskEvent riskEvent : riskEventList) {
|
riskEventService.save(riskEvent);
|
}
|
|
} catch (IOException e) {
|
logger.error(e.getLocalizedMessage());
|
throw new BusinessException("导入错误");
|
}
|
|
}
|
|
@Override
|
public void importRiskAnaUnitExcel(InputStream in, String operator, Boolean isExcel2007) {
|
List<RiskAnaUnit> riskAnaUnitList = new ArrayList<>();
|
//校验文件
|
try {
|
Workbook workbook = null;
|
if (isExcel2007) {
|
workbook = new XSSFWorkbook(in);
|
} else {
|
workbook = new HSSFWorkbook(in);
|
}
|
|
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
|
Sheet sheet = workbook.getSheetAt(0);
|
if (sheet == null) {
|
throw new BusinessException("excel 第1页不能为空");
|
}
|
Row row = sheet.getRow(0);
|
// 标题总列数
|
int colNum = row.getPhysicalNumberOfCells();
|
String[] title = new String[colNum];
|
|
if (colNum != riskAnaUnitTitle.length) {
|
throw new BusinessException("上传文件的列的个数错误");
|
}
|
|
for (int i = 0; i < colNum; i++) {
|
|
title[i] = row.getCell(i).getStringCellValue();
|
if (StringUtils.isNotBlank(title[i])) {
|
title[i] = title[i].trim();
|
}
|
if (!title[i].equals((riskAnaUnitTitle[i]))) {
|
throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + riskAnaUnitTitle[i]);
|
}
|
}
|
|
for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
|
int realrow = irow + 1;
|
RiskAnaUnit riskAnaUnit = new RiskAnaUnit();
|
Object value = null;
|
Object value1 = null;
|
row = sheet.getRow(irow);
|
|
//字段-安全风险分析对象编码
|
value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,安全风险分析对象编码不能为空.<br/>");
|
} else {
|
String trim = value.toString().trim();
|
riskAnaUnit.setHazardCode(value.toString().trim());
|
}
|
|
//字段2-安全风险分析单元名称
|
value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,安全风险分析单元名称不能为空.<br/>");
|
} else {
|
riskAnaUnit.setRiskUnitName(value.toString().trim());
|
}
|
|
//参数属性3-责任部门, 责任部门id
|
value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,责任部门不能为空.<br/>");
|
} else {
|
DepartmentInfo depInfoByName = departmentService.getInfoByName(value.toString().trim());
|
if (ObjectUtils.isEmpty(depInfoByName)){
|
throw new BusinessException("请检查部门名称是否正确");
|
}
|
riskAnaUnit.setHazardDep(value.toString().trim());
|
riskAnaUnit.setHazardDepId(depInfoByName.getId());
|
}
|
|
//参数属性4-责任人 ,责任人id
|
value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
|
value1 = ExcelProperty.getCellValue(row.getCell(2), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,责任人不能为空.<br/>");
|
} else {
|
UserInfo userByRealName = userService.getByRealName(value.toString().trim(), value1.toString().trim());
|
if (ObjectUtils.isEmpty(userByRealName)){
|
throw new BusinessException("请检查责任人名称是否正确");
|
}
|
riskAnaUnit.setHazardLiablePerson(value.toString().trim());
|
|
riskAnaUnit.setHazardLiablePersonId(userByRealName.getId());
|
}
|
|
//设置属性
|
riskAnaUnit.setCreateTime(new Date());
|
riskAnaUnit.setUpdateTime(new Date());
|
riskAnaUnit.setCreateBy(operator);
|
riskAnaUnit.setUpdateBy(operator);
|
riskAnaUnit.setValidFlag(Boolean.TRUE);
|
riskAnaUnit.setValidFlag(Boolean.TRUE);
|
|
riskAnaUnitList.add(riskAnaUnit);
|
}
|
|
for (RiskAnaUnit riskAnaUnit : riskAnaUnitList) {
|
riskAnaUnitService.save(riskAnaUnit);
|
}
|
|
} catch (IOException e) {
|
logger.error(e.getLocalizedMessage());
|
throw new BusinessException("导入错误");
|
}
|
|
}
|
|
@Override
|
public void importRiskControlMeasureExcel(InputStream in, String operator, Boolean isExcel2007) {
|
List<RiskControlMeasure> riskControlMeasureList = new ArrayList<>();
|
//校验文件
|
try {
|
Workbook workbook = null;
|
if (isExcel2007) {
|
workbook = new XSSFWorkbook(in);
|
} else {
|
workbook = new HSSFWorkbook(in);
|
}
|
|
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
|
Sheet sheet = workbook.getSheetAt(0);
|
if (sheet == null) {
|
throw new BusinessException("excel 第1页不能为空");
|
}
|
Row row = sheet.getRow(0);
|
// 标题总列数
|
int colNum = row.getPhysicalNumberOfCells();
|
String[] title = new String[colNum];
|
|
if (colNum != riskControlMeasureTitle.length) {
|
throw new BusinessException("上传文件的列的个数错误");
|
}
|
|
for (int i = 0; i < colNum; i++) {
|
|
title[i] = row.getCell(i).getStringCellValue();
|
if (StringUtils.isNotBlank(title[i])) {
|
title[i] = title[i].trim();
|
}
|
if (!title[i].equals((riskControlMeasureTitle[i]))) {
|
throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + riskControlMeasureTitle[i]);
|
}
|
}
|
|
for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
|
int realrow = irow + 1;
|
RiskControlMeasure riskControlMeasure = new RiskControlMeasure();
|
Object value = null;
|
row = sheet.getRow(irow);
|
|
//字段-安全风险事件id
|
value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,安全风险事件名称不能为空.<br/>");
|
} else {
|
RiskEvent byRiskEventName = riskEventService.getByRiskEventName(value.toString().trim());
|
if (ObjectUtils.isEmpty(byRiskEventName)){
|
throw new BusinessException("请检查安全风险事件名称是否正确");
|
}
|
riskControlMeasure.setRiskEventId(byRiskEventName.getId());
|
}
|
|
//字段2-管控方式
|
String auto= "自动化监控";
|
String riskCheck= "隐患排查";
|
value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,管控方式不能为空.<br/>");
|
}else {
|
if (value != auto || value !=riskCheck){
|
riskControlMeasure.setDataSrc(value.toString().trim());
|
}else {
|
throw new BusinessException("请检查管控方式是否正确");
|
}
|
}
|
|
|
|
|
//参数属性3-管控方式描述
|
value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,管控方式描述不能为空.<br/>");
|
} else {
|
riskControlMeasure.setRiskMeasureDesc(value.toString().trim());
|
}
|
|
//参数属性4，5-管控措施分类
|
Object value1 = null;
|
value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
|
value1 = ExcelProperty.getCellValue(row.getCell(4), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,管控措施分类1不能为空.<br/>");
|
}
|
if ( value1 == null || StringUtils.isBlank(value1.toString()) ){
|
throw new BusinessException("导入失败,管控措施分类2不能为空.<br/>");
|
}else {
|
RiskControlClassify byClassify1AndClassify2 =
|
riskControlClassifyService.getByClassify1AndClassify12(value.toString().trim(), value1.toString().trim());
|
if (ObjectUtils.isEmpty(byClassify1AndClassify2)){
|
throw new BusinessException("请检查管控措施分类1,2是否正确");
|
}
|
riskControlMeasure.setClassify1(value.toString().trim());
|
riskControlMeasure.setClassify2(value1.toString().trim());
|
}
|
|
//参数属性6-管控分类措施3
|
value = ExcelProperty.getCellValue(row.getCell(5), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
riskControlMeasure.setClassify3("");
|
} else {
|
riskControlMeasure.setClassify3(value.toString().trim());
|
}
|
|
|
|
//参数属性7-隐患排查内容
|
value = ExcelProperty.getCellValue(row.getCell(6), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,隐患排查内容不能为空.<br/>");
|
} else {
|
riskControlMeasure.setTroubleshootContent(value.toString().trim());
|
}
|
|
//参数属性8-隐患排查措施-时间周期
|
value = ExcelProperty.getCellValue(row.getCell(7), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,隐患排查周期不能为空.<br/>");
|
} else {
|
riskControlMeasure.setTimeEffect(value.toString().trim());
|
}
|
|
//参数属性9-隐患排查措施-时间单位
|
value = ExcelProperty.getCellValue(row.getCell(8), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,隐患排查单位不能为空.<br/>");
|
} else {
|
riskControlMeasure.setTimeUnit(value.toString().trim());
|
}
|
|
//设置属性
|
riskControlMeasure.setCreateBy(operator);
|
riskControlMeasure.setUpdateBy(operator);
|
riskControlMeasure.setCreateTime(new Date());
|
riskControlMeasure.setUpdateTime(new Date());
|
riskControlMeasure.setValidFlag(Boolean.TRUE);
|
|
riskControlMeasureList.add(riskControlMeasure);
|
}
|
|
|
for (RiskControlMeasure riskControlMeasure : riskControlMeasureList) {
|
riskControlMeasureService.save(riskControlMeasure);
|
}
|
|
} catch (IOException e) {
|
logger.error(e.getLocalizedMessage());
|
throw new BusinessException("导入错误");
|
}
|
}
|
|
/** 生产装置导入*/
|
@Override
|
public void importRiskSourceExcel(InputStream in, String realname, Boolean isExcel2007) {
|
|
List<RiskSource> riskSourceList = new ArrayList<>();
|
//校验文件
|
try {
|
Workbook workbook = null;
|
if (isExcel2007) {
|
workbook = new XSSFWorkbook(in);
|
} else {
|
workbook = new HSSFWorkbook(in);
|
}
|
|
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
|
Sheet sheet = workbook.getSheetAt(0);
|
if (sheet == null) {
|
throw new BusinessException("excel 第1页不能为空");
|
}
|
Row row = sheet.getRow(0);
|
// 标题总列数
|
int colNum = row.getPhysicalNumberOfCells();
|
String[] title = new String[colNum];
|
|
if (colNum != riskSourceTitle.length) {
|
throw new BusinessException("上传文件的列的个数错误");
|
}
|
|
for (int i = 0; i < colNum; i++) {
|
|
title[i] = row.getCell(i).getStringCellValue();
|
if (StringUtils.isNotBlank(title[i])) {
|
title[i] = title[i].trim();
|
}
|
if (!title[i].equals((riskSourceTitle[i]))) {
|
throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + riskSourceTitle[i]);
|
}
|
}
|
|
for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
|
int realrow = irow + 1;
|
RiskSource riskSource = new RiskSource();
|
Object value = null;
|
row = sheet.getRow(irow);
|
|
//字段-生产装置
|
value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,生产装置不能为空.<br/>");
|
} else {
|
riskSource.setName(value.toString().trim());
|
}
|
|
//字段2-风险等级
|
String level1="低风险";
|
value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,风险等级不能为空.<br/>");
|
}
|
/*1-低风险，2-一般风险，3-较大风险，4-重大风险*/
|
if(value.toString().trim().equals(level1)){
|
riskSource.setLevel((byte) 1);
|
}
|
else if(value.toString().trim().equals("一般风险")){
|
riskSource.setLevel((byte) 2);
|
}
|
else if(value.toString().trim().equals("较大风险")){
|
riskSource.setLevel((byte) 3);
|
}
|
else if(value.toString().trim().equals("重大风险")){
|
riskSource.setLevel((byte) 4);
|
} else {
|
throw new BusinessException("导入失败,第" + realrow + "行,风险等级不符合规范.<br/>");
|
}
|
|
//参数属性3-可能导致事故的主要原因
|
value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,可能导致事故的主要原因不能为空.<br/>");
|
} else {
|
riskSource.setAccidentDesc(value.toString().trim());
|
}
|
|
//参数属性-区域位置
|
value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,区域位置不能为空.<br/>");
|
} else {
|
riskSource.setLocation(value.toString().trim());
|
}
|
|
//参数属性-所属部门 并查询设置id
|
value = ExcelProperty.getCellValue(row.getCell(4), evaluator);
|
if (null == value || StringUtils.isBlank(value.toString())) {
|
throw new BusinessException("导入失败,第" + realrow + "行,所属部门不能为空.<br/>");
|
} else {
|
|
DepartmentInfo infoByName = departmentService.getInfoByName(value.toString().trim());
|
riskSource.setDepName(value.toString().trim());
|
riskSource.setDepId(infoByName.getId());
|
}
|
//设置属性
|
UserInfo userInfo = userService.getByRealName(realname, value.toString().trim());
|
|
riskSource.setType((byte) 1);
|
riskSource.setStatus((byte) 1);
|
riskSource.setCreateTime(new Date());
|
riskSource.setCreateUid(userInfo.getId());
|
riskSource.setCreateUname(realname);
|
riskSource.setEditTime(new Date());
|
riskSource.setEditUid(userInfo.getId());
|
riskSource.setEditUname(realname);
|
riskSourceList.add(riskSource);
|
}
|
|
for (RiskSource riskSource : riskSourceList) {
|
riskSourceService.save(riskSource);
|
}
|
|
} catch (IOException e) {
|
logger.error(e.getLocalizedMessage());
|
throw new BusinessException("导入错误");
|
}
|
}
|
|
}
|
