From 59e91a4e9ddaf23cebb12993c774aa899ab22d16 Mon Sep 17 00:00:00 2001
From: 郑永安 <zyazyz250@sina.com>
Date: Mon, 19 Jun 2023 14:22:45 +0800
Subject: [PATCH] 描述
---
src/main/java/com/gk/firework/Service/ServiceImpl/ExcelExportServiceImpl.java | 1767 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 1,767 insertions(+), 0 deletions(-)
diff --git a/src/main/java/com/gk/firework/Service/ServiceImpl/ExcelExportServiceImpl.java b/src/main/java/com/gk/firework/Service/ServiceImpl/ExcelExportServiceImpl.java
new file mode 100644
index 0000000..bf1d5ac
--- /dev/null
+++ b/src/main/java/com/gk/firework/Service/ServiceImpl/ExcelExportServiceImpl.java
@@ -0,0 +1,1767 @@
+package com.gk.firework.Service.ServiceImpl;
+import com.gk.firework.Domain.BO.SaleOrderDetailInfoBO;
+import com.google.common.collect.Lists;
+import java.util.Date;
+
+import com.alibaba.fastjson.JSONObject;
+import com.gk.firework.Domain.*;
+import com.gk.firework.Domain.Enum.Element;
+import com.gk.firework.Domain.Enum.EnterpriseSafetySupervision;
+import com.gk.firework.Domain.Exception.BusinessException;
+import com.gk.firework.Domain.Utils.*;
+import com.gk.firework.Domain.Vo.DirectionDetail;
+import com.gk.firework.Domain.Vo.FireworkDeal;
+import com.gk.firework.Domain.Vo.Product2JsonVo;
+import com.gk.firework.Domain.Vo.ProductVo;
+import com.gk.firework.Service.*;
+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 javax.management.relation.Role;
+import java.io.IOException;
+import java.io.InputStream;
+import java.math.BigDecimal;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.util.*;
+import java.util.regex.Pattern;
+
+@Service("ExcelExportService")
+public class ExcelExportServiceImpl implements ExcelExportService {
+ private Logger logger = LogManager.getLogger(getClass());
+
+ String[] districttitle = {"code", "name", "parentcode", "type"};
+
+ String[] selfCheckTitle = {"检查类型", "检查内容", "参考判断", "排序", "备注"};
+
+ String[] enterpriseTitle = {"企业名称", "单位编号", "安全监管分类", "省", "市", "区", "密码"};
+
+ String[] feedTitle = {"反馈内容", "发现时间"};
+
+ String[] userTitle = {"用户名", "密码", "单位", "省", "市", "区", "街道", "社区", "角色", "办公电话", "手机号", "邮箱", "部门", "职务"};
+
+ String[] userCodeTitle = {"单位", "统一社会信用代码"};
+
+ String[] productTitle = {"产品名称", "产品编号", "产品数量"};
+
+ String[] contractProductTitle = {"产品名称", "产品编号", "产品数量", "单箱进价"};
+
+ String[] productPriceTitle = {"产品名称", "流向码", "售价", "生产厂家", "规格", "含药量", "大类", "小类", "产品级别", "箱含量", "生产日期"};
+
+ String[] returnTags = {"电子标签号", "产品名称", "生产厂家", "总装药量", "箱含量", "产品类型", "产品级别", "创建时间", "包装标识码", "进价"};
+
+ String[] productDataTitle = {"流向码", "产品名称", "产品大类", "产品小类", "产品级别", "生产厂家", "规格", "含药量", "箱含量", "包装方式", "生产日期"};
+
+ String[] companyCodeTitle = {"单位名称", "单位代码"};
+
+ String[] saleProductTitle = {"包装标识码"};
+
+ String[] saleOrderTitle = {"零售单位名称","购买人身份证号","购买时间","购买数量"};
+
+ //正整数
+ 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 SelfCheckService selfCheckService;
+ @Autowired
+ private EnterpriseService enterpriseService;
+ @Autowired
+ private UserService userService;
+ @Autowired
+ private UserRolesService userRolesService;
+ @Autowired
+ private RoleService roleService;
+ @Autowired
+ private EnterpriseFeedService enterpriseFeedService;
+ @Autowired
+ private ProductService productService;
+ @Autowired
+ private ProductPriceService productPriceService;
+ @Autowired
+ private ProductCategoryService productCategoryService;
+ @Autowired
+ private SaleProductService saleProductService;
+ @Autowired
+ private CustomerService customerService;
+ @Autowired
+ private SaleOrderService saleOrderService;
+ @Autowired
+ private ProductLocusService productLocusService;
+
+ @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
+ 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.setType(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,检查内容不能为空.<br/>";
+ continue;
+ } else {
+ selfCheckInfo.setContent(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,参考判断不能为空.<br/>";
+ continue;
+ } else {
+ selfCheckInfo.setStandard(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(3), 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 BooleanReason importEnterpriseExcel(InputStream in, UserInfo userInfo, Boolean isExcel2007) {
+ BooleanReason blret = new BooleanReason();
+ List<Enterprise> enterpriseList = new ArrayList<>();
+ Date now = new Date();
+
+ 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 != enterpriseTitle.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((enterpriseTitle[i]))) {
+ blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]);
+ return blret;
+ }
+ }
+
+ for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
+ int realrow = irow + 1;
+ Enterprise enterprise = new Enterprise();
+ 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 if (enterpriseService.isDuplicateEnterpriseName(value.toString().trim(), null)) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,企业名称重复.<br/>";
+ continue;
+ } else {
+ enterprise.setEnterprisename(value.toString().trim());
+ }
+
+ //单位编号
+ value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
+
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,单位编号不能为空.<br/>";
+ continue;
+ } else if (enterpriseService.isDuplicateNumber(value.toString().trim(), null)) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,单位编号重复.<br/>";
+ continue;
+ } else {
+ enterprise.setEnterprisenumber(value.toString().trim());
+ }
+
+ //安全监管分类
+ //判断监管分类的类型是否能在数据字典找到
+ value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,安全监管分类不能为空.<br/>";
+ continue;
+ } else if (!EnterpriseSafetySupervision.isInclude(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,安全监管分类输入错误.<br/>";
+ continue;
+ } else {
+ enterprise.setSafetysupervision(value.toString().trim());
+ }
+
+ //省
+ value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,省份不能为空.<br/>";
+ continue;
+ } else {
+ enterprise.setProvince(value.toString().trim());
+ }
+
+ //市
+ value = ExcelProperty.getCellValue(row.getCell(4), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,市不能为空.<br/>";
+ continue;
+ } else {
+ enterprise.setCity(value.toString().trim());
+ }
+
+ //区
+ value = ExcelProperty.getCellValue(row.getCell(5), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,区不能为空.<br/>";
+ continue;
+ } else {
+ enterprise.setDistrict(value.toString().trim());
+ }
+
+
+ //密码
+ String PW_PATTERN = "(?=.*[A-Z])(?=.*[a-z])(?=.*[0-9])(?=.*[~!@#$%^&*_.]).{8,}";
+ value = ExcelProperty.getCellValue(row.getCell(6), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,参考判断不能为空.<br/>";
+ continue;
+ } else if (!value.toString().matches(PW_PATTERN)) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,密码必须8位以上,并且包含大小写字母、数字、特殊符号三种以上.<br/>";
+ continue;
+ } else {
+ enterprise.setPassword(Base64Encrypt.encode(value.toString().getBytes()));
+ }
+
+ //计算安全监管分类个数
+ {
+ int i = enterpriseService.countBySafetySupervision(enterprise.getSafetysupervision());
+ enterprise.setDivideflag((byte) (DivideFlagUtil.enterpriseDivideFlagGenerate(i)));
+ }
+
+ enterprise.setInfocreatebyname(userInfo.getUsername());
+ enterprise.setInfocreateby(userInfo.getId());
+ enterprise.setInfocreatetime(now);
+ enterprise.setInfoupdatetime(now);
+ enterprise.setValidflag(true);
+
+ String randomStr = Element.random(Element.class).getValue().toString() + Element.random(Element.class).getValue().toString();
+ while (enterpriseService.isDuplicate2BitCode(randomStr)) {
+ randomStr = Element.random(Element.class).getValue().toString() + Element.random(Element.class).getValue().toString();
+ }
+ enterprise.setTwobitcode(randomStr);
+ enterpriseService.save(enterprise);
+ enterpriseList.add(enterprise);
+ //保存用户
+ UserInfo user = new UserInfo();
+ user.setIsdel((byte) 0);
+ user.setProvince(enterprise.getProvince());
+ user.setCity(enterprise.getCity());
+ user.setArea(enterprise.getDistrict());
+ user.setUsername(enterprise.getEnterprisename());
+ user.setCompany(enterprise.getEnterprisename());
+ user.setIssale((byte) 0);
+ //普通用户
+ user.setType(3);
+ user.setCreatedby(user.getUsername());
+ user.setCompanyid(enterprise.getId());
+ user.setCreateddate(new Date());
+ //设置密码
+ user.setPassword(enterprise.getPassword());
+ user.setCompanynumber(enterprise.getEnterprisenumber());
+ //执行
+ userService.save(user);
+
+ //如果有企业角色设置用户角色为企业
+ UserRolesInfo uri = new UserRolesInfo();
+ uri.setUserid(user.getId());
+ if (enterprise.getSafetysupervision().equals(EnterpriseSafetySupervision.PRODUCE.getMsg())) {
+ uri.setRoleid((long) 3);
+ } else {
+ uri.setRoleid((long) 2);
+ }
+ userRolesService.save(uri);
+ //终端机
+ user.setId(null);
+ user.setUsername(enterprise.getEnterprisenumber());
+ user.setIssale((byte) 1);
+ user.setStatus((byte) 1);
+/* Calendar cal = Calendar.getInstance();
+ cal.setTime(new Date());
+ cal.add(Calendar.DATE, +365);
+ user.setExpiredate(cal.getTime());*/
+ userService.save(user);
+ }
+
+
+ if (StringUtils.isNotBlank(retmsg)) {
+ retmsg = retmsg + "导入成功" + enterpriseList.size() + "个，" + "导入失败" + (sheet.getLastRowNum() - enterpriseList.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 importUserExcel(InputStream in, String username, 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 != userTitle.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((userTitle[i]))) {
+ blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + userTitle[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());
+ UserInfo userInfoExist = userService.selectByUser(userInfo.getUsername());
+ if (null != userInfoExist) {
+ 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.setCompany(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
+ if (null != value && StringUtils.isNotBlank(value.toString())) {
+ userInfo.setProvince(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(4), evaluator);
+ if (null != value && StringUtils.isNotBlank(value.toString())) {
+ userInfo.setCity(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(5), evaluator);
+ if (null != value && StringUtils.isNotBlank(value.toString())) {
+ userInfo.setArea(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(6), evaluator);
+ if (null != value && StringUtils.isNotBlank(value.toString())) {
+ userInfo.setTown(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(7), evaluator);
+ if (null != value && StringUtils.isNotBlank(value.toString())) {
+ userInfo.setCommunity(value.toString().trim());
+ }
+
+ Long roleId = null;
+ value = ExcelProperty.getCellValue(row.getCell(8), evaluator);
+ if (null != value && StringUtils.isNotBlank(value.toString())) {
+ //角色
+ RoleInfo roleInfo = new RoleInfo();
+ roleInfo.setName(value.toString().trim());
+ RoleInfo roleInfoExist = roleService.selectRoleByName(roleInfo);
+ if (roleInfoExist == null) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,角色不存在.<br/>";
+ continue;
+ } else {
+ roleId = roleInfoExist.getId();
+ }
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(9), evaluator);
+ if (null != value && StringUtils.isNotBlank(value.toString())) {
+ userInfo.setPhone(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(10), evaluator);
+ if (null != value && StringUtils.isNotBlank(value.toString())) {
+ if (value.toString().length() == 11) {
+ userInfo.setMobile(value.toString().trim());
+ } else {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,手机号必须为11位数.<br/>";
+ continue;
+ }
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(11), evaluator);
+ if (null != value && StringUtils.isNotBlank(value.toString())) {
+ userInfo.setEmail(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(11), evaluator);
+ if (null != value && StringUtils.isNotBlank(value.toString())) {
+ userInfo.setDepartment(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(11), evaluator);
+ if (null != value && StringUtils.isNotBlank(value.toString())) {
+ userInfo.setJob(value.toString().trim());
+ }
+ userInfo.setIsdel((byte) 0);
+ userInfo.setIssale((byte) 0);
+ userInfo.setCreatedby(username);
+ userInfo.setCreateddate(new Date());
+ userInfo.setLastmodifiedby(username);
+ userInfo.setLastmodifieddate(new Date());
+
+ userService.save(userInfo);
+ if (roleId != null) {
+ UserRolesInfo userRolesInfo = new UserRolesInfo();
+ userRolesInfo.setUserid(userInfo.getId());
+ userRolesInfo.setRoleid(roleId);
+ userRolesService.save(userRolesInfo);
+ }
+ 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 List<Product2JsonVo> parsingProduct(InputStream inputStream, UserInfo userInfo, boolean isExcel2007, String enterprisenumber) {
+ List<Product2JsonVo> productJsons = new ArrayList<>();
+ try {
+ Workbook workbook = null;
+ if (isExcel2007) {
+ workbook = new XSSFWorkbook(inputStream);
+ } else {
+ workbook = new HSSFWorkbook(inputStream);
+ }
+
+ 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 != contractProductTitle.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((contractProductTitle[i]))) {
+ throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]);
+ }
+ }
+
+ for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
+ int realrow = irow + 1;
+ Product2JsonVo productInfoJson = new Product2JsonVo();
+ 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 + "行," + contractProductTitle[0] + "不能为空.");
+ }
+
+
+ //产品编号
+ value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + contractProductTitle[1] + "不能为空.");
+ } else {
+ String directionCode = value.toString().trim();
+ //检查产品编号是否是企业内的产品
+ int i = productService.countByEnterpriseNumberAndDirectionCode(enterprisenumber, directionCode);
+ assert i <= 1;
+ if (i < 1) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + "产品信息不为所在生产企业的产品.>");
+ }
+ ProductInfo productInfo = productService.selectByDirection(directionCode);
+ if (productInfo.getIsOld() == 1) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + "产品信息为旧系统产品，已不允许签订合同使用.>");
+ }
+ productInfoJson.setId(productInfo.getId());
+ productInfoJson.setName(productInfo.getName());
+ //使用数据库内容的流向码，区分大小写
+ productInfoJson.setDirectionCode(productInfo.getDirectionCode());
+ productInfoJson.setManufacturer(productInfo.getManufacturer());
+ productInfoJson.setSpecification(productInfo.getSpecification());
+ productInfoJson.setExplosiveContent(productInfo.getExplosiveContent());
+ productInfoJson.setType(productInfo.getType());
+ productInfoJson.setSecondaryType(productInfo.getSecondaryType());
+ productInfoJson.setLevel(productInfo.getLevel());
+ //箱含量
+ productInfoJson.setBoxNumber(productInfo.getBoxNumber());
+ productInfoJson.setPacking(productInfo.getPacking());
+ }
+
+
+ value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + contractProductTitle[2] + "不能为空.");
+ } else if (!Pattern.matches(pattern, value.toString().trim())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + contractProductTitle[2] + "不为正整数.");
+ } else {
+ productInfoJson.setNum(Integer.valueOf(value.toString().trim()));
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + contractProductTitle[3] + "不能为空.");
+ } else if (!pattern2.matcher(value.toString().trim()).matches()) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + contractProductTitle[3] + "不为金额.");
+ } else {
+ productInfoJson.setPrice(new BigDecimal(value.toString().trim()));
+ }
+
+ productJsons.add(productInfoJson);
+
+ }
+
+ } catch (IOException ex) {
+ ex.printStackTrace();
+ throw new BusinessException("导入失败，请联系管理员");
+ }
+
+ return productJsons;
+
+ }
+
+
+ @Override
+ public List<Product2JsonVo> parsingProduct(InputStream inputStream, UserInfo userInfo, boolean isExcel2007) {
+ List<Product2JsonVo> productJsons = new ArrayList<>();
+ try {
+ Workbook workbook = null;
+ if (isExcel2007) {
+ workbook = new XSSFWorkbook(inputStream);
+ } else {
+ workbook = new HSSFWorkbook(inputStream);
+ }
+
+ 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 != productTitle.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((productTitle[i]))) {
+ throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]);
+ }
+ }
+
+ for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
+ int realrow = irow + 1;
+ Product2JsonVo productInfoJson = new Product2JsonVo();
+ 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 + "行," + productTitle[0] + "不能为空.");
+ }
+
+ //产品编号
+ value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productTitle[1] + "不能为空.");
+ } else {
+ String directionCode = value.toString().trim();
+ ProductInfo productInfo = productService.selectByDirection(directionCode);
+ productInfoJson.setId(productInfo.getId());
+ productInfoJson.setName(productInfo.getName());
+ productInfoJson.setDirectionCode(directionCode);
+ productInfoJson.setManufacturer(productInfo.getManufacturer());
+ productInfoJson.setSpecification(productInfo.getSpecification());
+ productInfoJson.setExplosiveContent(productInfo.getExplosiveContent());
+ productInfoJson.setType(productInfo.getType());
+ productInfoJson.setSecondaryType(productInfo.getSecondaryType());
+ productInfoJson.setLevel(productInfo.getLevel());
+ //箱含量
+ productInfoJson.setBoxNumber(productInfo.getBoxNumber());
+ productInfoJson.setPacking(productInfo.getPacking());
+ }
+ value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productTitle[2] + "不能为空.");
+ } else if (!Pattern.matches(pattern, value.toString().trim())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productTitle[2] + "不为正整数.");
+ } else {
+ productInfoJson.setNum(Integer.valueOf(value.toString().trim()));
+ }
+ productJsons.add(productInfoJson);
+
+ }
+
+ } catch (IOException ex) {
+ ex.printStackTrace();
+ throw new BusinessException("导入失败，请联系管理员");
+ }
+
+ return productJsons;
+
+ }
+
+ @Override
+ public BooleanReason imporEditPriceExcel(InputStream in, String companynumber, String username, Boolean isExcel2007) {
+ BooleanReason blret = new BooleanReason();
+ List<ProductPriceInfo> productPriceInfoList = 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 != productPriceTitle.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((productPriceTitle[i]))) {
+ blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + productPriceTitle[i]);
+ return blret;
+ }
+ }
+
+ for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
+ int realrow = irow + 1;
+ ProductPriceInfo productPriceInfo = new ProductPriceInfo();
+ Object value = null;
+ row = sheet.getRow(irow);
+
+ value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,流向码不能为空.<br/>";
+ continue;
+ } else {
+ productPriceInfo.setItemcode(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,售价不能为空.<br/>";
+ continue;
+ } else {
+ try {
+ productPriceInfo.setPrice(new BigDecimal(value.toString().trim()));
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ productPriceInfo.setCompanynumber(companynumber);
+ productPriceInfo.setUpdated(new Date());
+ productPriceInfo.setUpdator(username);
+ productPriceInfoList.add(productPriceInfo);
+ }
+ for (ProductPriceInfo productInfo : productPriceInfoList) {
+ ProductPriceInfo productPriceInfo = productPriceService.selectByCode(companynumber, productInfo.getItemcode());
+ if (productPriceInfo != null) {
+ productPriceInfo.setPrice(productInfo.getPrice());
+ productPriceInfo.setUpdated(new Date());
+ productPriceInfo.setUpdator(username);
+ productPriceService.updateById(productPriceInfo);
+ } else {
+ ProductPriceInfo productPrice = new ProductPriceInfo();
+ productPrice.setCompanynumber(companynumber);
+ productPrice.setItemcode(productInfo.getItemcode());
+ productPrice.setPrice(productInfo.getPrice());
+ productPrice.setUpdated(new Date());
+ productPrice.setUpdator(username);
+ productPriceService.save(productPrice);
+ }
+ }
+
+ if (StringUtils.isNotBlank(retmsg)) {
+ retmsg = retmsg + "导入成功" + productPriceInfoList.size() + "个，" + "导入失败" + (sheet.getLastRowNum() - productPriceInfoList.size()) + "个。";
+ blret.addReason(retmsg);
+ return blret;
+ }
+ } catch (IOException e) {
+ logger.error(e.getLocalizedMessage());
+ blret.addReason("导入错误");
+ return blret;
+ }
+
+ blret.setValue(true);
+ return blret;
+ }
+
+ /**
+ * @Description: 退货电子标签数据解析
+ * @date 2021/6/3 17:15
+ */
+ @Override
+ public List<String> parseProductCode(InputStream in, UserInfo userInfo, boolean isExcel2007) {
+
+
+ Workbook workbook = null;
+ try {
+
+ 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 != returnTags.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((returnTags[i]))) {
+ throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]);
+ }
+ }
+
+ List<String> allReturnTags = new ArrayList<>();
+ for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
+ int realrow = irow + 1;
+ EnterpriseFeed feed = new EnterpriseFeed();
+ 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 {
+ allReturnTags.add(value.toString().trim());
+ }
+ }
+
+ return allReturnTags;
+
+ } catch (IOException e) {
+ e.printStackTrace();
+ throw new BusinessException("导入失败，请联系管理员");
+ }
+
+
+ }
+
+
+ /**
+ * @Description: 旧系统导入产品信息
+ * @date 2021/6/30 12:29
+ */
+ @Override
+ public List<ProductInfo> parseProductFromOldSystem(InputStream in, UserInfo user, boolean isExcel2007) {
+
+
+ try {
+ Workbook workbook = null;
+ if (isExcel2007) workbook = new XSSFWorkbook(in);
+ if (!isExcel2007) 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 != productDataTitle.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((productDataTitle[i]))) {
+ throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + productDataTitle[i]);
+ }
+ }
+ List<ProductInfo> result = new ArrayList<>();
+ ProductInfo product = null;
+ for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
+ int realrow = irow + 1;
+ Object value = null;
+ product = new ProductInfo();
+ row = sheet.getRow(irow);
+ value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[0] + "不能为空.");
+ } else {
+ //流向码
+ String productCode = value.toString().trim();
+ product.setDirectionCode(productCode);
+ if (productCode.length() != 10)
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[0] + "长度不为10.");
+ //重复判断
+ List<ProductInfo> productInfosExist = productService.selectByProduct(product);
+ if (productInfosExist != null && productInfosExist.size() > 0)
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[0] + "已经存在.");
+
+
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[1] + "不能为空.");
+ } else {
+ //产品名称
+ String productName = value.toString().trim();
+ product.setName(productName);
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[2] + "不能为空.");
+ } else {
+ //产品大类
+ String firstType = value.toString().trim();
+ product.setType(firstType);
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[3] + "不能为空.");
+ } else {
+ //产品小类
+ String secondaryType = value.toString().trim();
+ product.setSecondaryType(secondaryType);
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(4), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[4] + "不能为空.");
+ } else {
+ //产品级别
+ String level = value.toString().trim();
+ product.setLevel(level);
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(5), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[5] + "不能为空.");
+ } else {
+ //生产厂家
+ String manufacturer = value.toString().trim();
+ Enterprise enterprise = enterpriseService.selectEnterpriseByName(manufacturer);
+ //查不到企业 或者 不为生产企业
+ if (enterprise == null || !EnterpriseSafetySupervision.PRODUCE.getMsg().equals(enterprise.getSafetysupervision()))
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[5] + "查找不到企业名称或者不为生产企业.");
+ product.setManufacturer(manufacturer);
+ product.setCompanyNumber(enterprise.getEnterprisenumber());
+ }
+
+
+ value = ExcelProperty.getCellValue(row.getCell(6), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[6] + "不能为空.");
+ } else {
+ //规格
+ String specification = value.toString().trim();
+ product.setSpecification(specification);
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(7), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[7] + "不能为空.");
+ } else {
+ //含药量
+ String explosiveContentStr = value.toString().trim();
+ try {
+ BigDecimal explosiveContent = new BigDecimal(explosiveContentStr);
+ product.setExplosiveContent(explosiveContent);
+ //正数
+ if (explosiveContent.compareTo(BigDecimal.ZERO) < 1)
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[7] + "需要大于等于0.");
+ } catch (NumberFormatException e) {
+ e.printStackTrace();
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[7] + "不合法.");
+ }
+
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(8), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[8] + "不能为空.");
+ } else {
+ //箱含量
+ String boxNumberStr = value.toString().trim();
+ try {
+ int boxNumber = Integer.parseInt(boxNumberStr);
+ product.setBoxNumber(boxNumber);
+ if (boxNumber < 1)
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[8] + "需要大于0.");
+ } catch (NumberFormatException e) {
+ e.printStackTrace();
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[8] + "不合法.");
+ }
+
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(9), evaluator);
+ if (null != value && StringUtils.isBlank(value.toString())) {
+ //包装方式
+ String packing = value.toString().trim();
+ product.setPacking(packing);
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(10), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[10] + "不能为空.");
+ } else {
+ //生产日期
+ try {
+ String productDateStr = value.toString().trim();
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
+ Date productDate = sdf.parse(productDateStr);
+ product.setProductDate(productDate);
+ } catch (ParseException e) {
+ e.printStackTrace();
+ throw new BusinessException("导入失败,第" + realrow + "行," + productDataTitle[10] + "日期请填写2021-09-27的字符型.");
+ }
+
+ }
+
+ //最后校验第一类型和第二类型和产品级别
+ boolean isExist = productCategoryService.selectOne(product.getType(), product.getSecondaryType(), product.getLevel());
+ if (!isExist)
+ throw new BusinessException("导入失败,第" + realrow + "行,大小类、级别不合法.");
+ product.setCreatedBy(user.getUsername());
+ product.setModifiedBy(user.getUsername());
+ product.setCreatedDate(new Date());
+ product.setModifiedDate(new Date());
+ result.add(product);
+ }
+
+ return result;
+
+ } catch (IOException e) {
+ e.printStackTrace();
+ throw new BusinessException("导入失败，请联系管理员");
+ }
+
+
+ }
+
+ @Override
+ public BooleanReason importUserCodeExcel(InputStream in, String operator, Boolean isExcel2007) {
+ BooleanReason blret = new BooleanReason();
+ int z = 0;
+ 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 != userCodeTitle.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((userCodeTitle[i]))) {
+ blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + userTitle[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);
+
+ String company = "";
+ value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,单位名不能为空.<br/>";
+ continue;
+ } else {
+ company = value.toString().trim();
+ List<UserInfo> userInfos = userService.selectByCompany(company);
+ if (userInfos.size() < 1) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,单位不存在.<br/>";
+ continue;
+ }
+
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,统一社会信用代码不能为空.<br/>";
+ } else {
+ String code = value.toString().trim();
+ userService.updateCodeByCompany(company, code);
+ z++;
+
+ }
+
+
+ }
+
+ if (StringUtils.isNotBlank(retmsg)) {
+ retmsg = retmsg + "导入成功" + z + "个，" + "导入失败" + (sheet.getLastRowNum() - z) + "个。";
+ blret.addReason(retmsg);
+ return blret;
+ }
+ } catch (IOException e) {
+ logger.error(e.getLocalizedMessage());
+ blret.addReason("导入错误");
+ return blret;
+ }
+
+ blret.setValue(true);
+ return blret;
+ }
+
+ @Override
+ public BooleanReason importDLCompanyCodeExcel(InputStream in, UserInfo userInfo, boolean isExcel2007) {
+ Date now = new Date();
+ List<Enterprise> updates = 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 != companyCodeTitle.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((companyCodeTitle[i]))) {
+ throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + companyCodeTitle[i]);
+ }
+ }
+ if (sheet.getLastRowNum() < 1)
+ throw new BusinessException("导入数据为空");
+
+ int kk = 0;
+ StringBuffer sb = new StringBuffer();
+ for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
+ int realrow = irow + 1;
+ Object value = null;
+ Enterprise upOne = new Enterprise();
+ row = sheet.getRow(irow);
+ value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ sb.append("导入失败,第").append(realrow).append("行,单位名称不能为空.<br/>");
+ continue;
+ } else {
+ String enterpriseName = value.toString().trim();
+ Enterprise enterprise = enterpriseService.selectEnterpriseByName(enterpriseName);
+ if (enterprise == null) {
+ sb.append("找不到该企业:").append(enterpriseName).append(",第").append(realrow).append("行");
+ continue;
+ }
+ upOne.setId(enterprise.getId());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ sb.append("导入失败,第").append(realrow).append("行,单位代码不能为空.<br/>");
+ continue;
+ } else {
+ String code = value.toString().trim();
+ upOne.setDlcompanycode(code);
+ upOne.setInfoupdatebyname(userInfo.getUsername());
+ upOne.setInfoupdateby(userInfo.getId());
+ upOne.setInfoupdatetime(now);
+ }
+ updates.add(upOne);
+ }
+
+ for (Enterprise e : updates) {
+ enterpriseService.updateCompanyCode(e);
+ }
+ sb.append("成功" + updates.size() + "个,失败" + (sheet.getLastRowNum() - updates.size()) + "个");
+ BooleanReason booleanReason = new BooleanReason();
+ booleanReason.addReason(sb.toString());
+
+ return booleanReason;
+
+ } catch (IOException ex) {
+ ex.printStackTrace();
+ throw new BusinessException("导入失败，请联系管理员");
+ }
+
+ }
+
+
+ /**
+ * @Description: 企业反馈导入
+ * @date 2021/5/13 19:32
+ */
+ @Override
+ public void importFeedExcel(InputStream in, UserInfo user, Boolean isExcel2007, Long enterpriseId) {
+ List<EnterpriseFeed> feeds = new ArrayList<>();
+ Date now = new Date();
+ 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 != feedTitle.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((feedTitle[i]))) {
+ throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]);
+ }
+ }
+
+ for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
+ int realrow = irow + 1;
+ EnterpriseFeed feed = new EnterpriseFeed();
+ 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 {
+ feed.setContent(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ throw new BusinessException("导入失败,第" + realrow + "行,发现时间不能为空.<br/>");
+ } else if (!DateUtils.isLegalDate(value.toString().length(), value.toString(), "d/M/yy")) {
+ throw new BusinessException("日期格式不正确，应如2020-10-10或者2020/10/10");
+ } else {
+ java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("d/M/yy");
+ Date date = sdf.parse(value.toString());
+ feed.setFindtime(date);
+ }
+ feed.setEnterpriseid(enterpriseId);
+ feed.setCreateby(user.getId());
+ feed.setCreatetime(now);
+ feed.setCreatebyname(user.getUsername());
+ feed.setValidflag(true);
+ feeds.add(feed);
+ }
+
+ enterpriseFeedService.saveBatch(feeds);
+ } catch (ParseException ex) {
+ ex.printStackTrace();
+ throw new BusinessException("类型转换错误");
+ } catch (IOException ex) {
+ ex.printStackTrace();
+ throw new BusinessException("导入失败，请联系管理员");
+ }
+
+ }
+
+ @Override
+ public BooleanReason imporSaleProductExcel(InputStream in, String username, Boolean isExcel2007) {
+ BooleanReason blret = new BooleanReason();
+ int importSize = 0;
+ 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 != saleProductTitle.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((saleProductTitle[i]))) {
+ blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + saleProductTitle[i]);
+ return blret;
+ }
+ }
+
+ for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
+ int realrow = irow + 1;
+ List<SaleProductInfo> saleProductInfoList = new ArrayList<>();
+ 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 {
+ String directionCodeStr = value.toString().trim();
+ ProductInfo productInfo = productService.selectByDirection(directionCodeStr.substring(0, 10));
+ if (FireworkDeal.is22Characters(directionCodeStr)){
+ List<ProductVo>productVoList = new ArrayList<>();
+ DirectionDetail directionDetail = FireworkDeal.dealDirectionCode(directionCodeStr);
+ ProductVo productVo = productService.selectVoByDirection(directionCodeStr);
+ FireworkDeal.getProductVos(directionCodeStr,directionDetail,directionDetail,productVoList,productVo);
+ for (ProductVo productVo1 : productVoList){
+ SaleProductInfo saleProductInfo = new SaleProductInfo();
+ saleProductInfo.setItemcode(productInfo.getDirectionCode());
+ saleProductInfo.setDirectioncode(productVo1.getDirectionCode());
+ saleProductInfo.setItemname(productVo1.getName());
+ saleProductInfo.setSpecification(productVo1.getSpecification());
+ saleProductInfo.setPrice(new BigDecimal(0));
+ saleProductInfo.setCreatedby(username);
+ saleProductInfo.setCreatedat(new Date());
+ saleProductInfo.setIsuse((byte)0);
+ saleProductInfoList.add(saleProductInfo);
+ }
+ }else {
+ SaleProductInfo saleProductInfo = new SaleProductInfo();
+ saleProductInfo.setItemcode(productInfo.getDirectionCode());
+ saleProductInfo.setDirectioncode(directionCodeStr);
+ saleProductInfo.setItemname(productInfo.getName());
+ saleProductInfo.setSpecification(productInfo.getSpecification());
+ saleProductInfo.setPrice(new BigDecimal(0));
+ saleProductInfo.setCreatedby(username);
+ saleProductInfo.setCreatedat(new Date());
+ saleProductInfo.setIsuse((byte)0);
+ saleProductInfoList.add(saleProductInfo);
+ }
+ }
+
+ if (saleProductInfoList.size() > 0){
+ saleProductService.saveBatch(saleProductInfoList);
+ importSize ++;
+ }
+ }
+
+ if (StringUtils.isNotBlank(retmsg)) {
+ retmsg = retmsg + "导入成功" + importSize + "个，" + "导入失败" + (sheet.getLastRowNum() - importSize) + "个。";
+ blret.addReason(retmsg);
+ return blret;
+ }
+ } catch (IOException e) {
+ logger.error(e.getLocalizedMessage());
+ blret.addReason("导入错误");
+ return blret;
+ }
+
+ blret.setValue(true);
+ return blret;
+ }
+
+ @Override
+ public BooleanReason imporSaleOrderExcel(InputStream in, String username, Boolean isExcel2007) {
+ BooleanReason blret = new BooleanReason();
+ String retmsg = "";
+ blret.setValue(false);
+ int size = 0;
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ 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 != saleOrderTitle.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((saleOrderTitle[i]))) {
+ blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + saleOrderTitle[i]);
+ return blret;
+ }
+ }
+
+ for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
+ int realrow = irow + 1;
+ Object value = null;
+ row = sheet.getRow(irow);
+ SaleOrderInfo saleOrderInfo = new SaleOrderInfo();
+ List<SaleOrderDetailInfoBO> detailInfoList = new ArrayList<>();
+ value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,零售单位名称不能为空.<br/>";
+ continue;
+ }else {
+ Enterprise enterprise = enterpriseService.selectEnterpriseByName(value.toString().trim());
+ if (enterprise == null){
+ continue;
+ }else {
+ saleOrderInfo.setCreatedby(enterprise.getEnterprisenumber());
+ saleOrderInfo.setShop(enterprise.getEnterprisename());
+ saleOrderInfo.setCompanynumber(enterprise.getEnterprisenumber());
+ }
+ }
+ value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,身份证不能为空.<br/>";
+ continue;
+ }else {
+ saleOrderInfo.setIdcardnum(value.toString().trim());
+ }
+
+ value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,购买时间不能为空.<br/>";
+ continue;
+ }else {
+ String now = sdf.format(new Date());
+ String purseTime = value.toString().trim()+ now.substring(10);
+ saleOrderInfo.setCreatedat(sdf.parse(purseTime));
+ }
+
+ List<SaleProductInfo> saleProductInfoList = new ArrayList<>();
+ value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
+ if (null == value || StringUtils.isBlank(value.toString())) {
+ retmsg = retmsg + "导入失败,第" + realrow + "行,购买数量不能为空.<br/>";
+ continue;
+ }else {
+ saleOrderInfo.setBoxnum(new BigDecimal(Integer.valueOf(value.toString().trim())));
+ saleProductInfoList = saleProductService.selectRandom(Integer.valueOf(value.toString().trim()));
+ for (SaleProductInfo saleProductInfo : saleProductInfoList){
+ SaleOrderDetailInfoBO detailInfo = new SaleOrderDetailInfoBO();
+ detailInfo.setItemcode(saleProductInfo.getItemcode());
+ detailInfo.setItemname(saleProductInfo.getItemname());
+ detailInfo.setDirectioncode(saleProductInfo.getDirectioncode());
+ detailInfo.setCreatedat(saleOrderInfo.getCreatedat());
+ detailInfo.setCreatedby(saleOrderInfo.getCompanynumber());
+ detailInfo.setPrice(new BigDecimal("0"));
+ detailInfo.setSpecification(saleProductInfo.getSpecification());
+ detailInfo.setReturnflag((byte)0);
+ detailInfoList.add(detailInfo);
+ }
+ }
+ customerService.createOrUpdateCard(saleOrderInfo.getIdcardnum(),detailInfoList.size(),saleOrderInfo.getCreatedat());
+ CustomerInfo customerInfo = customerService.getCustomerByIdCardNum(saleOrderInfo.getIdcardnum());
+ UserInfo userInfo = userService.selectByUser(saleOrderInfo.getCompanynumber());
+ SaleOrderInfo saleOrderInfo1 = saleOrderService.createOrder(userInfo,detailInfoList,"1",detailInfoList.size(),saleOrderInfo.getCreatedat(),customerInfo.getId(),"0","0","0", "");
+ for (SaleProductInfo saleProductInfo : saleProductInfoList){
+ saleProductInfo.setIsuse((byte)1);
+ saleProductService.updateById(saleProductInfo);
+
+ //插入流向轨迹
+ ProductLocusInfo productLocusInfo = new ProductLocusInfo(saleProductInfo.getDirectioncode(),saleOrderInfo.getCreatedat(),saleOrderInfo.getCreatedat(),
+ userInfo.getCompany(),customerInfo.getId(),ProductLocusInfo.SALES_STATUS,null);
+ productLocusService.insertProductLocus(productLocusInfo);
+ }
+ size ++;
+ }
+
+ if (StringUtils.isNotBlank(retmsg)) {
+ retmsg = retmsg + "导入成功" + size + "个，" + "导入失败" + (sheet.getLastRowNum() - size) + "个。";
+ blret.addReason(retmsg);
+ return blret;
+ }
+ } catch (IOException e) {
+ logger.error(e.getLocalizedMessage());
+ blret.addReason("导入错误");
+ return blret;
+ } catch (ParseException e) {
+ e.printStackTrace();
+ }
+
+ blret.setValue(true);
+ return blret;
+ }
+}
--
Gitblit v1.9.2