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;
|
}
|
}
|
