package com.govmade.gds.cbs.controller;
import cn.hutool.core.text.StrBuilder;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.govmade.gds.admin.api.entity.SysLog;
import com.govmade.gds.admin.api.feign.RemoteDeptService;
import com.govmade.gds.admin.api.feign.RemoteDictService;
import com.govmade.gds.cbs.api.dto.MatDataMapDTO;
import com.govmade.gds.cbs.api.entity.*;
import com.govmade.gds.cbs.service.*;
import com.govmade.gds.common.core.util.R;
import com.govmade.gds.common.core.util.SpringContextHolder;
import com.govmade.gds.common.core.util.TemplateUtils;
import com.govmade.gds.common.log.event.SysLogEvent;
import com.govmade.gds.common.log.util.SysLogUtils;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
@RestController
@RequestMapping("/matExcel")
public class MatExeclController {
private final static String XLS = "xls";
private final static String XLSX = "xlsx";
@Autowired
private RemoteDictService remoteDictService;
@Autowired
private RemoteDeptService remoteDeptService;
@Autowired
private MatMatterService matMatterService;
@Autowired
private MatGuideService matGuideService;
@Autowired
private MatMaterialService matMaterialService;
@Autowired
private MatThemeService matThemeService;
@Autowired
private MatMaterialItemService matMaterialItemService;
@Autowired
private MatGuideMaterialService matGuideMaterialService;
@Autowired
private MatDataMapService matDataMapService;
/**
* 读入excel文件，解析后返回
*
* @param file
* @param
* @param startNum 内容开始行
* @throws IOException
*/
public static List<String[]> readExcel(MultipartFile file, String sheetName, Integer startNum) throws IOException {
//检查文件
checkFile(file);
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
List<String[]> list = new ArrayList<String[]>();
if (workbook != null) {
//获得当前sheet工作表
Sheet sheet = workbook.getSheet(sheetName);
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//循环除了第一行的所有行 为了过滤到第一行因为我的第一行是数据库的列
for (int rowNum = firstRowNum + startNum - 1; rowNum <= lastRowNum; rowNum++) {
//获得当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数 为空列获取
int lastCellNum = row.getLastCellNum();
String[] cells = new String[row.getLastCellNum()];
//循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
}
return list;
}
private static void checkFile(MultipartFile file) throws IOException {
if (null == file) {
throw new FileNotFoundException("文件不存在！");
}
String fileName = file.getOriginalFilename();
if (!fileName.endsWith(XLS) && !fileName.endsWith(XLSX)) {
throw new IOException(fileName + "不是excel文件");
}
}
private static Workbook getWorkBook(MultipartFile file) {
Workbook workbook = null;
try {
InputStream is = file.getInputStream();
workbook = WorkbookFactory.create(is);
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
@SuppressWarnings("deprecation")
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
//把数字当成String来读，避免出现1读成1.0的情况
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
//判断数据的类型
switch (cell.getCellType()) {
//数字
case Cell.CELL_TYPE_NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
//字符串
case Cell.CELL_TYPE_STRING:
cellValue = String.valueOf(cell.getStringCellValue());
break;
//Boolean
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
//公式
case Cell.CELL_TYPE_FORMULA:
cellValue = String.valueOf(cell.getStringCellValue());
break;
//空值
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
//故障
case Cell.CELL_TYPE_ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
/**
* 判断单元格内容是为空还是有值却不是数字
*
* @param messge sheet名
* @param index 验证时循环的下标
* @param cellValue 单元格内容
* @param headLine 标题
* @return 错误提示信息
*/
public static R<Boolean> cellJudge(StrBuilder messge, int index, String cellValue, String headLine) {
if (StrUtil.isBlank(cellValue)) {
messge.append(index).append("行").append(headLine).append("默认填0");
return R.ok(Boolean.FALSE, messge.toString());
} else {
messge.append(index).append("行").append(headLine).append("只能填入数字");
return R.ok(Boolean.FALSE, messge.toString());
}
}
/**
* 判断字符串是否为null，" ",不可见字符或是否是纯数字
*
* @param str 被检测的字符串
* @return 是否为空或是否纯数字
*/
public static boolean isNumeric(String str) {
if (StrUtil.isBlank(str)) {
return false;
}
int sz = str.length();
for (int i = 0; i < sz; i++) {
if (Character.isDigit(str.charAt(i)) == false) {
return false;
}
}
return true;
}
@Transactional(rollbackFor = {Exception.class})
@PostMapping("/service_import")
@ApiOperation(value = "excel导入公共服务", notes = "excel导入公共服务", httpMethod = "POST")
public R<Boolean> importExcel(@RequestParam(value = "file") MultipartFile file) {
// 技术
int count = 0;
try {
List<String> encrpList = new ArrayList<>();
//验证事项目类
List<String[]> matterList = readExcel(file, "事项目录", 3);
for (int i = 0; i < matterList.size(); i++) {
int number = i + 3;
StrBuilder messge = new StrBuilder("在事项目录中，第");
Integer deptId = remoteDeptService.findDeptIdByName(matterList.get(i)[1]);
if (deptId == null) {
messge.append(i + 3).append("行机构不存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(matterList.get(i)[0])) {
messge.append(i + 3).append("行事项名称不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(matterList.get(i)[4])) {
messge.append(i + 3).append("行事项编码不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(matterList.get(i)[3])) {
messge.append(i + 3).append("行事项类别不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (matMatterService.selectByNameAndDeptId(matterList.get(i)[0], deptId) != null) {
messge.append(i + 3).append("行事项已存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (matMatterService.findByMatEncrp(matterList.get(i)[4]) != null) {
messge.append(i + 3).append("行事项编码已存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(matterList.get(i)[6])) {
messge.append(i + 3).append("行基本代码不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (!isNumeric(matterList.get(i)[6])) {
messge.append(i + 3).append("行基本代码必须为数字！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(matterList.get(i)[7])) {
messge.append(i + 3).append("行事项层级不能为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (matterList.get(i)[6].length() != 5 && matterList.get(i)[7].equals("1")) {
messge.append(i + 3).append("行基本代码为主项时只能有5位数字！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (matterList.get(i)[6].length() != 3 && matterList.get(i)[7].equals("2")) {
messge.append(i + 3).append("行基本代码为子项时只能有3位数字！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (matterList.get(i)[6].length() != 2 && matterList.get(i)[7].equals("3")) {
messge.append(i + 3).append("行基本代码为办理项时只能有2位数字！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (!isNumeric(matterList.get(i)[2])) {
return cellJudge(messge, number, matterList.get(i)[2], "事项类别一级");
} else {
if (Integer.parseInt(matterList.get(i)[2]) != 1 && Integer.parseInt(matterList.get(i)[2]) != 2) {
messge.append(i + 3).append("行事项类别只能是1或2！");
return R.ok(Boolean.FALSE, messge.toString());
}
}
if (!isNumeric(matterList.get(i)[3])) {
return cellJudge(messge, number, matterList.get(i)[3], "事项类别二级");
}
if (!isNumeric(matterList.get(i)[7])) {
return cellJudge(messge, number, matterList.get(i)[7], "事项层级");
}
if (!isNumeric(matterList.get(i)[8])) {
return cellJudge(messge, number, matterList.get(i)[8], "是否有子项");
}
encrpList.add(matterList.get(i)[4]);
}
//验证公共服务
List<String[]> serviceList = readExcel(file, "公共服务", 4);
for (int i = 0; i < serviceList.size(); i++) {
String service = serviceList.get(i)[4];
StrBuilder messge = new StrBuilder("在公共服务中，第");
int number = i + 4;
if (!encrpList.contains(service)) {
messge.append(i + 4).append("行事项代码在事项目录中不存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (!isNumeric(serviceList.get(i)[9])) {
return cellJudge(messge, number, serviceList.get(i)[9], "办件类型");
}
if (!isNumeric(serviceList.get(i)[15])) {
return cellJudge(messge, number, serviceList.get(i)[15], "法定办结时限");
}
if (!isNumeric(serviceList.get(i)[16])) {
return cellJudge(messge, number, serviceList.get(i)[16], "法定办结时限单位");
}
if (!isNumeric(serviceList.get(i)[17])) {
return cellJudge(messge, number, serviceList.get(i)[17], "承诺办结时限");
}
if (!isNumeric(serviceList.get(i)[18])) {
return cellJudge(messge, number, serviceList.get(i)[18], "承诺办结时限单位");
}
if (!isNumeric(serviceList.get(i)[25])) {
return cellJudge(messge, number, serviceList.get(i)[25], "是否收费");
}
if (!isNumeric(serviceList.get(i)[34])) {
return cellJudge(messge, number, serviceList.get(i)[34], "是否物流快递");
}
}
// 验证申请材料
Map<String, HashSet<String>> map = new HashMap<>();
List<String[]> list3 = readExcel(file, "申请材料", 2);
for (int i = 0; i < list3.size(); i++) {
int number = i + 2;
StrBuilder messge = new StrBuilder("申请材料第");
if (!encrpList.contains(list3.get(i)[1])) {
messge.append(i + 2).append("行事项编码不存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(list3.get(i)[3])) {
messge.append(i + 2).append("行材料名称不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (!isNumeric(list3.get(i)[4])) {
return cellJudge(messge, number, list3.get(i)[4], "材料类型");
}
if (!isNumeric(list3.get(i)[5])) {
return cellJudge(messge, number, list3.get(i)[5], "材料规格");
}
if (!isNumeric(list3.get(i)[6])) {
return cellJudge(messge, number, list3.get(i)[6], "原件份数");
}
if (!isNumeric(list3.get(i)[7])) {
return cellJudge(messge, number, list3.get(i)[7], "复印件份数");
}
if (!isNumeric(list3.get(i)[9])) {
return cellJudge(messge, number, list3.get(i)[9], "是否材料样本");
}
if (!isNumeric(list3.get(i)[11])) {
return cellJudge(messge, number, list3.get(i)[11], "是否示范样本");
}
if (map.containsKey(list3.get(i)[1])) {
HashSet<String> set = map.get(list3.get(i)[1]);
if (set.contains(list3.get(i)[3])) {
messge.append(i + 2).append("行材料名称重复，同一事项下材料名称不可重复！");
return R.ok(Boolean.FALSE, messge.toString());
} else {
set.add(list3.get(i)[3]);
map.put(list3.get(i)[1], set);
}
} else {
HashSet<String> set = new HashSet<>();
set.add(list3.get(i)[3]);
map.put(list3.get(i)[1], set);
}
}
//导入事项
for (String[] matterlist : matterList) {
MatMatter matter = new MatMatter();
matter.setDeptId(Long.valueOf(remoteDeptService.findDeptIdByName(matterlist[1])));
matter.setMatName(matterlist[0]);
matter.setTypeFirst(Integer.parseInt(matterlist[2]));
matter.setTypeSecond(Integer.parseInt(matterlist[3]));
matter.setMatEncrp(matterlist[4]);
matter.setMatCode(matterlist[6]);
String matLevel = matterlist[7];
if ("1".equals(matLevel)) {
matter.setMatLevel(1);
matter.setParentId(0L);
} else if ("2".equals(matLevel) || "3".equals(matLevel)) {
matter.setMatLevel(Integer.parseInt(matLevel));
matter.setParentId(matMatterService.findByMatEncrp(matterlist[9]).getId());
}
matter.setIsChild(Integer.parseInt(matterlist[8]));
//matter.setCreatorId(SecurityUtils.getUser().getId());
matter.setAuditState(1);
matMatterService.save(matter);
// 保存日志
SysLog sysLog = SysLogUtils.getSysLog();
sysLog.setTitle("导入事项");
sysLog.setException("mat_matter");
sysLog.setServiceId(""+matter.getId());
Long startTime = System.currentTimeMillis();
Long endTime = System.currentTimeMillis();
sysLog.setTime(endTime - startTime);
// SpringContextHolder.publishEvent(new SysLogEvent(sysLog));
count++;
}
//导入公共服务
for (String[] servicelist : serviceList) {
MatGuide guide = new MatGuide();
MatMatter matter = matMatterService.findByMatEncrp(servicelist[4]);
guide.setMatId(matter.getId());
guide.setApplScen(servicelist[3]);
guide.setMatCode(servicelist[5]);
guide.setType(matter.getTypeFirst());
guide.setSvrObj(servicelist[8]);
guide.setDocType(Integer.parseInt(servicelist[9]));
guide.setOperDept(matter.getDeptId());
guide.setAccoDept(servicelist[11]);
guide.setCtLim(servicelist[12]);
guide.setRsName(servicelist[13]);
guide.setRsSamp(servicelist[14]);
guide.setLegalTime(Integer.parseInt(servicelist[15]));
guide.setLegalUnit(Integer.parseInt(servicelist[16]));
guide.setPromiseTime(Integer.parseInt(servicelist[17]));
guide.setPromiseUnit(Integer.parseInt(servicelist[18]));
guide.setConsultTel(servicelist[19]);
guide.setSupTel(servicelist[20]);
guide.setAdmiss(servicelist[21]);
String isOnline = servicelist[22];
if (isOnline.contains("1")) {
guide.setIsOnlineProc(2);
guide.setOnlineProc(servicelist[23]);
} else {
guide.setIsOnlineProc(1);
}
if (isOnline.contains("2")) {
guide.setIsWindowProc(2);
guide.setWindowProc(servicelist[24]);
} else {
guide.setIsWindowProc(1);
}
String isChg = servicelist[25];
if ("1".equals(isChg)) {
guide.setIsChg(1);
} else if ("2".equals(isChg)) {
guide.setIsChg(2);
guide.setChgStd(servicelist[26]);
guide.setChgBasis(servicelist[27]);
}
String worktype = servicelist[28];
if (worktype.contains("1")) {
guide.setIsWindow(2);
guide.setWindowLoc(servicelist[29]);
guide.setWindowTime(servicelist[30]);
guide.setTrafficGuide(servicelist[31]);
} else {
guide.setIsWindow(1);
}
if (worktype.contains("2")) {
guide.setIsOnline(2);
guide.setOnlineLink(servicelist[32]);
} else {
guide.setIsOnline(1);
}
if (worktype.contains("3")) {
guide.setIsTelDeal(2);
guide.setDetailTel(servicelist[33]);
} else {
guide.setIsTelDeal(1);
}
String isEps = servicelist[34];
if ("1".equals(isEps)) {
guide.setIsEps(1);
} else if ("2".equals(isEps)) {
guide.setIsEps(2);
guide.setEpsChoose(servicelist[35]);
}
guide.setFaq(servicelist[36]);
guide.setAuditState(1);
//guide.setCreatorId(SecurityUtils.getUser().getId());
matGuideService.save(guide);
// 保存日志
SysLog sysLog = SysLogUtils.getSysLog();
sysLog.setTitle("导入公共服务");
sysLog.setException("mat_guide");
sysLog.setServiceId(""+guide.getId());
Long startTime = System.currentTimeMillis();
Long endTime = System.currentTimeMillis();
sysLog.setTime(endTime - startTime);
// SpringContextHolder.publishEvent(new SysLogEvent(sysLog));
count++;
}
// 导入申请材料
for (String[] strings : list3) {
MatGuideMaterial mgm = new MatGuideMaterial();
MatMatter matter = matMatterService.findByMatEncrp(strings[1]);
mgm.setGuideId(matGuideService.findByMatId(matter.getId()));
if (matMaterialService.selectMaterialByName(strings[3]) == null) {
MatMaterial material = new MatMaterial();
material.setMaterialName(strings[3]);
material.setMaterialIder(strings[2]);
material.setMaterialType(Integer.valueOf(strings[4]));
material.setMaterialSpec(strings[5]);
material.setOrigNum(Integer.valueOf(strings[6]));
material.setCopyNum(Integer.valueOf(strings[7]));
material.setSourceChannel(strings[8]);
material.setIsMaterialSamp(Integer.valueOf(strings[9]));
material.setIsDemoSamp(Integer.valueOf(strings[11]));
material.setNotes(strings[13]);
//material.setCreatorId(SecurityUtils.getUser().getId());
material.setAuditState(1);
//material.setDeptId(matter.getDeptId());
material.setPolicy(strings[14]);
matMaterialService.save(material);
// 保存日志
SysLog sysLog = SysLogUtils.getSysLog();
sysLog.setTitle("导入材料");
sysLog.setException("mat_material");
sysLog.setServiceId(""+material.getId());
Long startTime = System.currentTimeMillis();
Long endTime = System.currentTimeMillis();
sysLog.setTime(endTime - startTime);
SpringContextHolder.publishEvent(new SysLogEvent(sysLog));
mgm.setMaterialId(material.getId());
} else {
mgm.setMaterialId(matMaterialService.selectMaterialByName(strings[3]).getId());
}
matGuideMaterialService.save(mgm);
count++;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "文件不存在！");
} catch (IOException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "不是excel文件！");
} catch (NumberFormatException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请按规范填写文档");
} catch (DataIntegrityViolationException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "文档内容过多，材料请不要重复导入");
} catch (NullPointerException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请查看父子级事项编码是否填写正确，机构是否在系统中已存在，最后一列单元格是否未填");
} catch (ArrayIndexOutOfBoundsException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请查看每行数据最后一格单元格是否填写");
} catch (Exception e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "excel文件有误，请按表头提示填写！");
}
return R.ok(Boolean.TRUE, "已导入" + count + "条数据");
}
@Transactional(rollbackFor = {Exception.class})
@PostMapping("/right_import")
@ApiOperation(value = "excel导入行政权力", notes = "excel导入行政权力", httpMethod = "POST")
public R<Boolean> importRight(@RequestParam(value = "file") MultipartFile file) {
int count = 0;
try {
// 验证事项目录
List<String> encrpList = new ArrayList<>();
List<String[]> list1 = readExcel(file, "事项目录", 3);
for (int i = 0; i < list1.size(); i++) {
int number = i + 3;
StrBuilder messge = new StrBuilder("事项目录第");
Integer deptId = remoteDeptService.findDeptIdByName(list1.get(i)[1]);
if (deptId == null) {
messge.append(i + 3).append("行机构不存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(list1.get(i)[0])) {
messge.append(i + 3).append("行事项名称不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(list1.get(i)[3])) {
messge.append(i + 3).append("行事项类别不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(list1.get(i)[4])) {
messge.append(i + 3).append("行事项编码不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (matMatterService.selectByNameAndDeptId(list1.get(i)[0], deptId) != null) {
messge.append(i + 3).append("行事项已存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (matMatterService.findByMatEncrp(list1.get(i)[4]) != null) {
messge.append(i + 3).append("行事项编码已存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(list1.get(i)[6])) {
messge.append(i + 3).append("行基本代码不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (!isNumeric(list1.get(i)[6])) {
messge.append(i + 3).append("行基本代码必须为数字！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(list1.get(i)[7])) {
messge.append(i + 3).append("行事项层级不能为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (list1.get(i)[6].length() != 5 && "1".equals(list1.get(i)[7])) {
messge.append(i + 3).append("行基本代码为主项时只能有5位数字！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (list1.get(i)[6].length() != 3 && "2".equals(list1.get(i)[7])) {
messge.append(i + 3).append("行基本代码为子项时只能有3位数字！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (list1.get(i)[6].length() != 2 && "3".equals(list1.get(i)[7])) {
messge.append(i + 3).append("行基本代码为办理项时只能有2位数字！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (!isNumeric(list1.get(i)[2])) {
return cellJudge(messge, number, list1.get(i)[2], "事项类别一级");
} else {
if (Integer.parseInt(list1.get(i)[2]) != 1 && Integer.parseInt(list1.get(i)[2]) != 2) {
messge.append(i + 3).append("行事项类别只能是1或2！");
return R.ok(Boolean.FALSE, messge.toString());
}
}
if (!isNumeric(list1.get(i)[3])) {
return cellJudge(messge, number, list1.get(i)[3], "事项类别二级");
}
if (!isNumeric(list1.get(i)[7])) {
return cellJudge(messge, number, list1.get(i)[7], "事项层级");
}
if (!isNumeric(list1.get(i)[8])) {
return cellJudge(messge, number, list1.get(i)[8], "是否有子项");
}
encrpList.add(list1.get(i)[4]);
}
// 验证行政权力
List<String[]> list2 = readExcel(file, "行政权力", 4);
for (int i = 0; i < list2.size(); i++) {
StrBuilder messge = new StrBuilder("行政权力第");
int number = i + 4;
if (!encrpList.contains(list2.get(i)[4])) {
messge.append(i + 4).append("行事项编码在事项目录中不存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (!isNumeric(list2.get(i)[9])) {
return cellJudge(messge, number, list2.get(i)[9], "办件类型");
}
if (!isNumeric(list2.get(i)[10])) {
return cellJudge(messge, number, list2.get(i)[10], "通版范围");
}
if (!isNumeric(list2.get(i)[14])) {
return cellJudge(messge, number, list2.get(i)[14], "实施主体性质");
}
if (!isNumeric(list2.get(i)[16])) {
return cellJudge(messge, number, list2.get(i)[16], "是否存在联办机构");
}
if (Integer.valueOf(list2.get(i)[16]) == 2) {
Integer deptId = remoteDeptService.findDeptIdByName(list2.get(i)[17]);
if (deptId == null) {
messge.append(i + 4).append("行联办机构不存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
}
if (!isNumeric(list2.get(i)[18])) {
return cellJudge(messge, number, list2.get(i)[18], "行使类型");
}
if (!isNumeric(list2.get(i)[20])) {
return cellJudge(messge, number, list2.get(i)[20], "运行系统");
}
if (!isNumeric(list2.get(i)[26])) {
return cellJudge(messge, number, list2.get(i)[26], "法定办结时限");
}
if (!isNumeric(list2.get(i)[27])) {
return cellJudge(messge, number, list2.get(i)[27], "法定办结时限单位");
}
if (!isNumeric(list2.get(i)[28])) {
return cellJudge(messge, number, list2.get(i)[28], "承诺办结时限");
}
if (!isNumeric(list2.get(i)[29])) {
return cellJudge(messge, number, list2.get(i)[29], "承诺办结时限单位");
}
if (!isNumeric(list2.get(i)[39])) {
return cellJudge(messge, number, list2.get(i)[39], "是否收费");
}
if (!isNumeric(list2.get(i)[58])) {
return cellJudge(messge, number, list2.get(i)[58], "是否存在中介服务");
}
if (!StrUtil.isBlank(list2.get(i)[61])) {
if (!StringUtils.isNumeric(list2.get(i)[61])) {
messge.append(number).append("中介服务是否收费只能填入数字");
return R.ok(Boolean.FALSE, messge.toString());
}
}
if (!isNumeric(list2.get(i)[64])) {
return cellJudge(messge, number, list2.get(i)[64], "是否预约办理");
}
if (!isNumeric(list2.get(i)[66])) {
return cellJudge(messge, number, list2.get(i)[66], "是否网上支付");
}
if (!isNumeric(list2.get(i)[68])) {
return cellJudge(messge, number, list2.get(i)[68], "是否物流快递");
}
}
// 验证申请材料
Map<String, HashSet<String>> map = new HashMap<>();
List<String[]> list3 = readExcel(file, "申请材料", 2);
for (int i = 0; i < list3.size(); i++) {
int number = i + 2;
StrBuilder messge = new StrBuilder("申请材料第");
if (!encrpList.contains(list3.get(i)[1])) {
messge.append(i + 2).append("行事项编码不存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(list3.get(i)[3])) {
messge.append(i + 2).append("行材料名称不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (!isNumeric(list3.get(i)[4])) {
return cellJudge(messge, number, list3.get(i)[4], "材料类型");
}
if (!isNumeric(list3.get(i)[5])) {
return cellJudge(messge, number, list3.get(i)[5], "材料规格");
}
if (!isNumeric(list3.get(i)[6])) {
return cellJudge(messge, number, list3.get(i)[6], "原件份数");
}
if (!isNumeric(list3.get(i)[7])) {
return cellJudge(messge, number, list3.get(i)[7], "复印件份数");
}
if (!isNumeric(list3.get(i)[9])) {
return cellJudge(messge, number, list3.get(i)[9], "是否材料样本");
}
if (!isNumeric(list3.get(i)[11])) {
return cellJudge(messge, number, list3.get(i)[11], "是否示范样本");
}
if (map.containsKey(list3.get(i)[1])) {
HashSet<String> set = map.get(list3.get(i)[1]);
if (set.contains(list3.get(i)[3])) {
messge.append(i + 2).append("行材料名称重复，同一事项下材料名称不可重复！");
return R.ok(Boolean.FALSE, messge.toString());
} else {
set.add(list3.get(i)[3]);
map.put(list3.get(i)[1], set);
}
} else {
HashSet<String> set = new HashSet<>();
set.add(list3.get(i)[3]);
map.put(list3.get(i)[1], set);
}
}
// 导入事项
for (String[] strings : list1) {
MatMatter matter = new MatMatter();
matter.setDeptId(Long.valueOf(remoteDeptService.findDeptIdByName(strings[1])));
matter.setMatName(strings[0]);
matter.setTypeFirst(Integer.parseInt(strings[2]));
matter.setTypeSecond(Integer.parseInt(strings[3]));
matter.setMatEncrp(strings[4]);
matter.setMatCode(strings[6]);
String matLevel = strings[7];
if ("1".equals(matLevel)) {
matter.setMatLevel(Integer.parseInt(matLevel));
matter.setParentId(0L);
} else if ("2".equals(matLevel) || "3".equals(matLevel)) {
matter.setMatLevel(Integer.parseInt(matLevel));
matter.setParentId(matMatterService.findByMatEncrp(strings[9]).getId());
}
matter.setIsChild(Integer.parseInt(strings[8]));
matter.setAuditState(1);
//matter.setCreatorId(SecurityUtils.getUser().getId());
matMatterService.save(matter);
// 保存日志
SysLog sysLog = SysLogUtils.getSysLog();
sysLog.setTitle("导入事项");
sysLog.setException("mat_matter");
sysLog.setServiceId(""+matter.getId());
Long startTime = System.currentTimeMillis();
Long endTime = System.currentTimeMillis();
sysLog.setTime(endTime - startTime);
// SpringContextHolder.publishEvent(new SysLogEvent(sysLog));
count++;
}
// 导入行政权力
for (String[] strings : list2) {
MatGuide guide = new MatGuide();
MatMatter matter = matMatterService.findByMatEncrp(strings[4]);
guide.setMatId(matter.getId());
guide.setApplScen(strings[3]);
guide.setMatCode(strings[5]);
guide.setType(matter.getTypeFirst());
guide.setSvrObj(strings[8]);
guide.setDocType(Integer.valueOf(strings[9]));
guide.setOperScope(Integer.valueOf(strings[10]));
guide.setSetGuide(strings[11]);
guide.setOperDept(matter.getDeptId());
guide.setAccoDept(strings[13]);
guide.setOperSubject(Integer.valueOf(strings[14]));
if (Integer.valueOf(strings[14]) == 3) {
guide.setEntrOrgt(strings[15]);
}
guide.setIsJoint(Integer.valueOf(strings[16]));
if (Integer.valueOf(strings[16]) == 2) {
guide.setJointDept(Long.valueOf(remoteDeptService.findDeptIdByName(strings[17])));
}
guide.setEnforceType(Integer.valueOf(strings[18]));
guide.setEnforceLevel(strings[19].replaceAll("，", ","));
guide.setRunSys(Integer.valueOf(strings[20]));
guide.setAuthDiv(strings[21]);
guide.setEnforceCont(strings[22]);
guide.setCtLim(strings[23]);
guide.setRsName(strings[24]);
guide.setRsSamp(strings[25]);
guide.setLegalTime(Integer.valueOf(strings[26]));
guide.setLegalUnit(Integer.valueOf(strings[27]));
guide.setPromiseTime(Integer.valueOf(strings[28]));
guide.setPromiseUnit(Integer.valueOf(strings[29]));
guide.setConsultTel(strings[30]);
guide.setSupTel(strings[31]);
guide.setAdmiss(strings[32]);
if (strings[33].contains("1")) {
guide.setIsOnlineProc(2);
guide.setOnlineProc(strings[34]);
} else {
guide.setIsOnlineProc(1);
}
if (strings[33].contains("2")) {
guide.setIsWindowProc(2);
guide.setWindowProc(strings[35]);
} else {
guide.setIsWindowProc(1);
}
if (strings[33].contains("3")) {
guide.setIsSpec(2);
guide.setSpecProc(strings[36].replaceAll("，", ","));
guide.setSpecProcer(strings[37]);
guide.setProcSuggTime(strings[38]);
} else {
guide.setIsSpec(1);
}
guide.setIsChg(Integer.valueOf(strings[39]));
if (Integer.valueOf(strings[39]) == 2) {
guide.setChgStd(strings[40]);
guide.setChgBasis(strings[41]);
}
if (strings[42].contains("1")) {
guide.setIsWindow(2);
guide.setWindowLoc(strings[43]);
guide.setWindowTime(strings[44]);
guide.setTrafficGuide(strings[45]);
} else {
guide.setIsWindow(1);
}
if (strings[42].contains("2")) {
guide.setIsOnline(2);
guide.setOnlineLink(strings[46]);
} else {
guide.setIsOnline(1);
}
if (strings[42].contains("3")) {
guide.setIsTelDeal(2);
guide.setDetailTel(strings[47]);
} else {
guide.setIsTelDeal(1);
}
guide.setLegalRight(strings[48]);
guide.setLegalDuty(strings[49]);
guide.setReconDept(strings[50]);
guide.setReconAddr(strings[51]);
guide.setReconTel(strings[52]);
guide.setReconUrl(strings[53]);
guide.setLtgtDept(strings[54]);
guide.setLtgtAddr(strings[55]);
guide.setLtgtTel(strings[56]);
guide.setLtgtUrl(strings[57]);
guide.setIsAgcy(Integer.valueOf(strings[58]));
if (Integer.valueOf(strings[58]) == 2) {
guide.setAgcyDept(strings[59]);
guide.setAgcyCont(strings[60]);
guide.setIsAgcyChg(Integer.valueOf(strings[61]));
if (Integer.valueOf(strings[61]) == 2) {
guide.setAgcyChgStd(strings[62]);
guide.setAgcyChgAmt(new BigDecimal(strings[63]));
}
}
guide.setIsApptProc(Integer.valueOf(strings[64]));
if (Integer.valueOf(strings[64]) == 2) {
guide.setApptUrl(strings[65]);
}
guide.setIsOnlinePay(Integer.valueOf(strings[66]));
if (Integer.valueOf(strings[66]) == 2) {
guide.setPayment(strings[67]);
}
guide.setIsEps(Integer.valueOf(strings[68]));
if (Integer.valueOf(strings[68]) == 2) {
guide.setEpsChoose(strings[69]);
}
guide.setFaq(strings[70]);
guide.setAuditState(1);
//guide.setCreatorId(SecurityUtils.getUser().getId());
matGuideService.save(guide);
// 保存日志
SysLog sysLog = SysLogUtils.getSysLog();
sysLog.setTitle("导入行政权力");
sysLog.setException("mat_guide");
sysLog.setServiceId(""+guide.getId());
Long startTime = System.currentTimeMillis();
Long endTime = System.currentTimeMillis();
sysLog.setTime(endTime - startTime);
// SpringContextHolder.publishEvent(new SysLogEvent(sysLog));
count++;
}
// 导入申请材料
for (String[] strings : list3) {
MatGuideMaterial mgm = new MatGuideMaterial();
MatMatter matter = matMatterService.findByMatEncrp(strings[1]);
mgm.setGuideId(matGuideService.findByMatId(matter.getId()));
if (matMaterialService.selectMaterialByName(strings[3]) == null) {
MatMaterial material = new MatMaterial();
material.setMaterialName(strings[3]);
material.setMaterialIder(strings[2]);
material.setMaterialType(Integer.valueOf(strings[4]));
material.setMaterialSpec(strings[5]);
material.setOrigNum(Integer.valueOf(strings[6]));
material.setCopyNum(Integer.valueOf(strings[7]));
material.setSourceChannel(strings[8]);
material.setIsMaterialSamp(Integer.valueOf(strings[9]));
material.setIsDemoSamp(Integer.valueOf(strings[11]));
material.setNotes(strings[13]);
//material.setCreatorId(SecurityUtils.getUser().getId());
//material.setDeptId(matter.getDeptId());
material.setPolicy(strings[14]);
material.setAuditState(1);
matMaterialService.save(material);
// 保存日志
SysLog sysLog = SysLogUtils.getSysLog();
sysLog.setTitle("导入材料");
sysLog.setType("mat_material");
sysLog.setServiceId(""+material.getId());
Long startTime = System.currentTimeMillis();
Long endTime = System.currentTimeMillis();
sysLog.setTime(endTime - startTime);
// SpringContextHolder.publishEvent(new SysLogEvent(sysLog));
mgm.setMaterialId(material.getId());
} else {
mgm.setMaterialId(matMaterialService.selectMaterialByName(strings[3]).getId());
}
matGuideMaterialService.save(mgm);
count++;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "文件不存在！");
} catch (IOException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "不是excel文件！");
} catch (NumberFormatException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请按规范填写文档");
} catch (DataIntegrityViolationException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "文档内容过多，材料请不要重复导入");
} catch (NullPointerException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请查看父子级事项编码是否填写正确，机构是否在系统中已存在，最后一列单元格是否未填");
} catch (ArrayIndexOutOfBoundsException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请查看每行数据最后一格单元格是否填写");
} catch (Exception e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "excel文件有误，请按表头提示填写！");
}
return R.ok(Boolean.TRUE, "已导入" + count + "条数据");
}
@Transactional(rollbackFor = {Exception.class})
@PostMapping("/theme_import")
@ApiOperation(value = "excel导入主题模版", notes = "excel导入主题模版", httpMethod = "POST")
public R<Boolean> importTheme(@RequestParam(value = "file") MultipartFile file) {
Map<String, String> nameSet = new HashMap<>();
try {
// 验证主题模板
List<String[]> list = readExcel(file, "主题模板", 3);
StrBuilder messge = new StrBuilder("主题模板第");
for (int i = 0; i < list.size(); i++) {
int number = i + 3;
if (StrUtil.isBlank(list.get(i)[7])) {
messge.append(i + 3).append("行事项编码不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (matMatterService.findByMatEncrp(list.get(i)[7]) == null) {
messge.append(i + 3).append("行事项编码不存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(list.get(i)[1])) {
messge.append(i + 3).append("行主题名称不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (matThemeService.findByThemeName(list.get(i)[1]) > 0) {
messge.append(i + 3).append("行主题已存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
Integer deptId = remoteDeptService.findDeptIdByName(list.get(i)[4]);
if (deptId == null) {
messge.append(i + 3).append("行牵头部门不存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (!isNumeric(list.get(i)[3])) {
return cellJudge(messge, number, list.get(i)[3], "主题类型");
}
if (!isNumeric(list.get(i)[8])) {
return cellJudge(messge, number, list.get(i)[8], "顺序");
}
nameSet.put(list.get(i)[1], list.get(i)[5]);
}
for (String name : nameSet.keySet()) {
MatTheme theme = new MatTheme();
theme.setThemeName(name);
theme.setServiceResult(nameSet.get(name));
Integer type = null;
Long deptId = 0L;
for (String[] strings1 : list) {
if (StrUtil.equals(name, strings1[1])) {
type = Integer.valueOf(strings1[3]);
deptId = Long.valueOf(remoteDeptService.findDeptIdByName(strings1[4]));
}
}
theme.setThemeType(type);
theme.setLeadDept(deptId);
theme.setAuditState(1);
matThemeService.save(theme);
// 保存日志
SysLog sysLog = SysLogUtils.getSysLog();
sysLog.setTitle("导入主题");
sysLog.setType("mat_theme");
sysLog.setServiceId(""+theme.getId());
Long startTime = System.currentTimeMillis();
Long endTime = System.currentTimeMillis();
sysLog.setTime(endTime - startTime);
// SpringContextHolder.publishEvent(new SysLogEvent(sysLog));
List<MatThemeGuide> themeGuideList = new ArrayList<MatThemeGuide>();
for (String[] strings : list) {
if (strings[1].equals(name)) {
MatMatter matter = matMatterService.findByMatEncrp(strings[7]);
MatThemeGuide matThemeGuide = new MatThemeGuide();
Long guideId = matGuideService.findByMatId(matter.getId());
matThemeGuide.setGuideId(guideId);
matThemeGuide.setGuideWeight(Integer.parseInt(strings[8]));
themeGuideList.add(matThemeGuide);
}
}
matThemeService.insertThemeGuide(theme.getId(), themeGuideList);
matThemeService.updateById(theme);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "文件不存在！");
} catch (IOException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "不是excel文件！");
} catch (NumberFormatException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请按规范填写文档");
} catch (DataIntegrityViolationException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "文档内容过多");
} catch (NullPointerException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请查看父子级事项编码是否填写正确，机构是否在系统中已存在");
} catch (ArrayIndexOutOfBoundsException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请查看每行数据最后一格单元格是否填写");
} catch (Exception e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "excel文件有误！");
}
return R.ok(Boolean.TRUE, "已导入" + nameSet.size() + "条主题！");
}
/**
* 材料目录导入
* @param file
* @return
*/
@Transactional(rollbackFor = {Exception.class})
@PostMapping("/importMatMaterial")
@ApiOperation(value = "excel导入信息项模版", notes = "excel导入信息项模版", httpMethod = "POST")
@PreAuthorize("@pms.hasPermission('cbs_material_import')")
public R<Boolean> importMatMaterial(@RequestParam(value = "file") MultipartFile file) {
List<MatMaterial> matMaterials = new ArrayList<>();
try{
StrBuilder messge = new StrBuilder("材料目录模板第");
List<String[]> list = readExcel(file, "信息项", 2);
List<String> dict = new ArrayList<>();
dict.add("element_material_type"); //材料分类
dict.add("material_type"); //材料类型
dict.add("material_format"); //材料形式
Map<String, Map<String, String>> dictMaps = remoteDictService.getDictItemValues(dict);
for (int i = 0; i < list.size(); i++) {
if (StrUtil.isBlank(list.get(i)[0])) {
messge.append(i + 1).append("行材料名称不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (StrUtil.isBlank(list.get(i)[1])) {
messge.append(i + 1).append("行材料分类不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
MatMaterial matMaterial = new MatMaterial();
matMaterial.setMaterialType(1);
matMaterial.setMaterialName(list.get(i)[0]); //材料名称
matMaterial.setModelType(dictMaps.get("element_material_type").get(list.get(i)[1])); //材料分类
if (list.get(i).length >2 && !StrUtil.isBlank(list.get(i)[2]))
matMaterial.setMaterialClassify(Integer.parseInt(dictMaps.get("material_type").get(list.get(i)[2]))); //材料类型
if (list.get(i).length >3 &&!StrUtil.isBlank(list.get(i)[3]))
matMaterial.setMaterialFormat(Integer.parseInt(dictMaps.get("material_format").get(list.get(i)[3]))); //材料形式
if (list.get(i).length >4 &&!StrUtil.isBlank(list.get(i)[4])) {
String sourceType = "";
if ("申请人自备".equals(list.get(i)[4])) {
sourceType = "1";
} else if ("政府部门核发".equals(list.get(i)[4])) {
sourceType = "2";
} else if ("其他".equals(list.get(i)[4])) {
sourceType = "3";
}
matMaterial.setSourceChannel(sourceType); //来源渠道
}
if (list.get(i).length >5 &&!StrUtil.isBlank(list.get(i)[5]))
matMaterial.setSourceExplain(list.get(i)[5]); //来源渠道说明
if (list.get(i).length >6 &&!StrUtil.isBlank(list.get(i)[6]))
matMaterial.setCopyNum(Integer.parseInt(list.get(i)[6])); //复印件数量
if (list.get(i).length >7 && !StrUtil.isBlank(list.get(i)[7]))
matMaterial.setMaterialSpec(list.get(i)[7]); //原件数量
if (list.get(i).length >8 &&!StrUtil.isBlank(list.get(i)[8]))
matMaterial.setNotes(list.get(i)[8]); //填报须知
if (list.get(i).length >9 &&!StrUtil.isBlank(list.get(i)[9]))
matMaterial.setAcceptStand(list.get(i)[9]); //受理标准
if (list.get(i).length >10 &&!StrUtil.isBlank(list.get(i)[10]))
matMaterial.setRemark(list.get(i)[10]); //备注
matMaterials.add(matMaterial);
}
matMaterialService.saveBatch(matMaterials);
} catch (NumberFormatException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请按规范填写文档");
} catch (DataIntegrityViolationException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "文档内容过多，材料请不要重复导入");
} catch (NullPointerException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请查看父子级事项编码是否填写正确，机构是否在系统中已存在，最后一列单元格是否未填");
} catch (IOException e) {
e.printStackTrace();
}
return R.ok(Boolean.TRUE, "已导入" + matMaterials.size() + "条信息项！");
}
@Transactional(rollbackFor = {Exception.class})
@PostMapping("/item_import")
@ApiOperation(value = "excel导入信息项模版", notes = "excel导入信息项模版", httpMethod = "POST")
public R<Boolean> importItem(@RequestParam(value = "file") MultipartFile file) {
int count;
try {
// 验证信息项模板
List<String[]> list = readExcel(file, "信息项", 2);
count = list.size();
StrBuilder messge = new StrBuilder("信息项第");
Map<String, HashSet<String>> map = new HashMap<>();
for (int i = 0; i < list.size(); i++) {
int number = i + 2;
if (StrUtil.isBlank(list.get(i)[1])) {
messge.append(i + 2).append("行信息项名称不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (matMaterialService.findMaterialByName(list.get(i)[2]) == null) {
if (matMaterialService.selectMaterialByName(list.get(i)[2]) == null) {
messge.append(i + 2).append("行材料名称不存在！");
} else {
messge.append(i + 2).append("行材料类型不是申请表类型！");
}
return R.ok(Boolean.FALSE, messge.toString());
}
if (map.containsKey(list.get(i)[2])) {
HashSet<String> set = map.get(list.get(i)[2]);
if (set.contains(list.get(i)[1])) {
messge.append(i + 2).append("行信息项名称重复，同一材料下信息项名称不可重复！");
return R.ok(Boolean.FALSE, messge.toString());
} else {
set.add(list.get(i)[1]);
map.put(list.get(i)[2], set);
}
} else {
HashSet<String> set = new HashSet<>();
set.add(list.get(i)[1]);
map.put(list.get(i)[2], set);
}
Long materialId = matMaterialService.findMaterialByName(list.get(i)[2]).getId();
if (matMaterialItemService.selectItemByMaterialIdAndName(materialId, list.get(i)[1]) != null) {
messge.append(i + 2).append("行信息项在").append(list.get(i)[2]).append("中已存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
if (!isNumeric(list.get(i)[4])) {
return cellJudge(messge, number, list.get(i)[4], "数据类型1");
}
if (!isNumeric(list.get(i)[5])) {
return cellJudge(messge, number, list.get(i)[5], "数据类型2");
}
if (!isNumeric(list.get(i)[6])) {
return cellJudge(messge, number, list.get(i)[6], "数据长度");
}
if (!isNumeric(list.get(i)[7])) {
return cellJudge(messge, number, list.get(i)[7], "共享属性");
}
if (!isNumeric(list.get(i)[8])) {
return cellJudge(messge, number, list.get(i)[8], "开放属性");
}
}
for (String[] strings : list) {
MatMaterialItem item = new MatMaterialItem();
item.setMaterialId(matMaterialService.findMaterialByName(strings[2]).getId());
item.setItemName(strings[1]);
// item.setDatatypeFirst(Integer.valueOf(strings[4]));
// item.setDatatypeSecond(Integer.valueOf(strings[5]));
item.setDataLen(Integer.valueOf(strings[6]));
item.setShareProp(Integer.valueOf(strings[7]));
item.setOpenProp(Integer.valueOf(strings[8]));
item.setDateValue(strings[9]);
item.setExplain(strings[10]);
//item.setCreatorId(SecurityUtils.getUser().getId());
matMaterialItemService.save(item);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "文件不存在！");
} catch (IOException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "不是excel文件！");
} catch (NumberFormatException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请按规范填写文档");
} catch (DataIntegrityViolationException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "文档内容过多，材料请不要重复导入");
} catch (NullPointerException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请查看父子级事项编码是否填写正确，机构是否在系统中已存在，最后一列单元格是否未填");
} catch (ArrayIndexOutOfBoundsException e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请查看每行数据最后一格单元格是否填写");
} catch (Exception e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "excel文件有误，请按表头提示填写！");
}
return R.ok(Boolean.TRUE, "已导入" + count + "条信息项！");
}
@PostMapping("/down_models")
@ApiOperation(value = "excel模版下载（1公共服务模版2行政权力模版3主题事项模版4信息项模版6数据项映射模版）", notes = "excel模版下载（1公共服务模版2行政权力模版3主题事项模版4信息项模版）",
httpMethod = "POST")
public ResponseEntity<byte[]> downModel(@RequestParam(value = "type") String type) throws IOException {
String path = "/models/";
String name = "";
if (StrUtil.equals("1", type)) {
path += "公共服务模版.xlsx";
name = "公共服务模版.xlsx";
} else if (StrUtil.equals("2", type)) {
path += "行政权力模版.xlsx";
name = "行政权力模版.xlsx";
} else if (StrUtil.equals("3", type)) {
path += "主题事项模版.xlsx";
name = "主题事项模版.xlsx";
} else if (StrUtil.equals("4", type)) {
path += "信息项模版.xlsx";
name = "信息项模版.xlsx";
}else if (StrUtil.equals("6", type)) {
path += "数据项映射模版.xlsx";
name = "数据项映射模版.xlsx";
}else if (StrUtil.equals("7", type)) {
path += "材料目录模板.xlsx";
name = "材料目录模板.xlsx";
}
InputStream in = this.getClass().getResourceAsStream(path);
HttpHeaders headers = new HttpHeaders();
headers.setContentDispositionFormData("attachment", URLEncoder.encode(name, "UTF-8"));
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
return new ResponseEntity<byte[]>(TemplateUtils.toByteArray(in),
headers, HttpStatus.CREATED);
}
@Transactional(rollbackFor = {Exception.class})
@PostMapping("/dataMap_import")
@ApiOperation(value = "excel导入数据项映射", notes = "excel导入数据项映射", httpMethod = "POST")
public R<Boolean> importDataMap(@RequestParam(value = "file") MultipartFile file) {
//用来计数共插入多少数据
int count = 0;
int number = 1;
MatDataMapDTO matDataMapDTO = new MatDataMapDTO();
try {
// 验证数据项映射字段是否规范
List<String[]> list = readExcel(file, "数据元", 2);
for (int i = 0; i < list.size() && !list.isEmpty(); i++) {
//不读取空数据
if (!(StrUtil.isBlank(list.get(i)[1]) && StrUtil.isBlank(list.get(i)[2]))) {
number = number + 1;
StrBuilder messge = new StrBuilder("事项目录第");
if (StrUtil.isBlank(list.get(i)[1])) {
messge.append(number).append("行申请表单名称不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
} else {
//查询导入文件名是否存在
QueryWrapper entityWrapper=new QueryWrapper();
// EntityWrapper<MatMaterial> entityWrapper = new EntityWrapper<>();
entityWrapper.eq("material_name", list.get(i)[1]);
entityWrapper.eq("material_type", 1);
entityWrapper.eq("audit_state", 1);
entityWrapper.eq("is_delete", 2);
List<MatMaterial> matMaterials = matMaterialService.list(entityWrapper);
if (!(matMaterials != null && matMaterials.size() > 0)) {
messge.append(number).append("行申请表单名称不存在！");
return R.ok(Boolean.FALSE, messge.toString());
}
}
if (StrUtil.isBlank(list.get(i)[2])) {
messge.append(number).append("行数据项名称不可为空！");
return R.ok(Boolean.FALSE, messge.toString());
}
int falg = matDataMapService.findAlreadyExist(list.get(i)[1], list.get(i)[2]);
if(falg > 0) {
return R.ok(1,null, "该条数据已重复");
}
}
}
// 导入事项
for (int i=0;i<number-1;i++) {
MatDataMap matDataMap = new MatDataMap();
matDataMap.setMaterialName(list.get(i)[1].trim());
matDataMap.setDataName(list.get(i)[2].trim());
//DTO转换为entity
BeanUtils.copyProperties(matDataMap, matDataMapDTO);
matDataMapService.add(matDataMapDTO);
count++;
}
} catch(FileNotFoundException e){
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "文件不存在！");
} catch(IOException e){
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "不是excel文件！");
} catch(NumberFormatException e){
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请按规范填写文档");
} catch(DataIntegrityViolationException e){
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "文档内容过多，材料请不要重复导入");
// } catch (NullPointerException e) {
// e.printStackTrace();
// TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
// return R.ok(Boolean.FALSE, "请查看父子级事项编码是否填写正确，机构是否在系统中已存在，最后一列单元格是否未填");
} catch(ArrayIndexOutOfBoundsException e){
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "请查看每行数据最后一格单元格是否填写");
} catch(Exception e){
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return R.ok(Boolean.FALSE, "excel文件有误，请按表头提示填写！");
}
return R.ok(Boolean.TRUE, "已导入" + count + "条数据");
}
}
