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