jsh_erp/src/main/java/com/jsh/erp/service/impl/MaterialServiceImpl.java

package com.jsh.erp.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.jsh.erp.constants.BusinessConstants;
import com.jsh.erp.constants.ExceptionConstants;
import com.jsh.erp.datasource.entities.*;
import com.jsh.erp.datasource.mappers.*;
import com.jsh.erp.datasource.pda.dto.PDAInventoryDTO;
import com.jsh.erp.datasource.pda.vo.PDADepotItemVO;
import com.jsh.erp.datasource.vo.MaterialCurrentStock4SystemSku;
import com.jsh.erp.datasource.pda.vo.PDATypeTree;
import com.jsh.erp.datasource.vo.MaterialVoSearch;
import com.jsh.erp.datasource.vo.MaterialWarnListVo;
import com.jsh.erp.datasource.vo.TreeNode;
import com.jsh.erp.exception.BusinessRunTimeException;
import com.jsh.erp.exception.JshException;
import com.jsh.erp.query.LambdaQueryWrapperX;
import com.jsh.erp.query.QueryWrapperX;
import com.jsh.erp.service.*;
import com.jsh.erp.utils.*;
import jxl.Sheet;
import jxl.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class MaterialServiceImpl extends ServiceImpl<MaterialMapper, Material> implements MaterialService {
    private Logger logger = LoggerFactory.getLogger(MaterialServiceImpl.class);

    @Resource
    private MaterialMapper materialMapper;
    @Resource
    private MaterialExtendMapper materialExtendMapper;
    @Resource
    private MaterialMapperEx materialMapperEx;
    @Resource
    private MaterialCategoryMapperEx materialCategoryMapperEx;
    @Resource
    private MaterialExtendMapperEx materialExtendMapperEx;
    @Resource
    private LogService logService;
    @Resource
    private UserService userService;
    @Resource
    private DepotItemMapperEx depotItemMapperEx;
    @Resource
    private DepotItemService depotItemService;
    @Resource
    private MaterialCategoryService materialCategoryService;
    @Resource
    private UnitService unitService;
    @Resource
    private MaterialInitialStockMapper materialInitialStockMapper;
    @Resource
    private MaterialInitialStockMapperEx materialInitialStockMapperEx;
    @Resource
    private MaterialCurrentStockMapper materialCurrentStockMapper;
    @Resource
    private MaterialCurrentStockMapperEx materialCurrentStockMapperEx;
    @Resource
    private DepotService depotService;
    @Resource
    private MaterialExtendService materialExtendService;
    @Resource
    private SystemConfigService systemConfigService;
    @Resource
    private DepotHeadService depotHeadService;
    @Resource
    private SupplierService supplierService;
    @Resource
    private DepotMapperEx depotMapperEx;

    @Value(value="${file.uploadType}")
    private Long fileUploadType;

    @Override
    public Material getMaterial(long id)throws Exception {
        Material result=null;
        try{
            result=materialMapper.selectByPrimaryKey(id);
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        return result;
    }

    @Override
    public List<Material> getMaterialListByIds(String ids)throws Exception {
        List<Long> idList = StringUtil.strToLongList(ids);
        List<Material> list = new ArrayList<>();
        try{
            MaterialExample example = new MaterialExample();
            example.createCriteria().andIdIn(idList);
            list = materialMapper.selectByExample(example);
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        return list;
    }

    @Override
    public List<Material> getMaterial() throws Exception{
        MaterialExample example = new MaterialExample();
        example.createCriteria().andDeleteFlagNotEqualTo(BusinessConstants.DELETE_FLAG_DELETED);
        List<Material> list=null;
        try{
            list=materialMapper.selectByExample(example);
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        return list;
    }


    /**
     * 查询商品管理-商品信息列表查询
     */
    @Override
    public List<MaterialVo4Unit> select(String materialParam, String standard, String model, String color, String brand, String mfrs,
                                        String materialOther, String weight, String expiryNum, String enableSerialNumber,
                                        String enableBatchNumber, String position, String enabled, String remark, String categoryId,
                                        String mpList)
            throws Exception{
        String[] mpArr = new String[]{};
        if(StringUtil.isNotEmpty(mpList)){
            mpArr= mpList.split(",");
        }
        List<MaterialVo4Unit> list = new ArrayList<>();
        try{
            List<Long> idList = new ArrayList<>();
            if(StringUtil.isNotEmpty(categoryId)){
                idList = getListByParentId(Long.parseLong(categoryId));
            }
            PageUtils.startPage();
            list= materialMapperEx.selectByConditionMaterial(materialParam, standard, model, color, brand, mfrs, materialOther, weight, expiryNum,
                    enableSerialNumber, enableBatchNumber, position, enabled, remark, idList, mpList);
            if (null != list && list.size()>0) {
                Map<Long,BigDecimal> initialStockMap = getInitialStockMapByMaterialList(list);
                Map<Long,BigDecimal> currentStockMap = getCurrentStockMapByMaterialList(list);
                for (MaterialVo4Unit m : list) {
                    if(fileUploadType == 2) {
                        m.setImgSmall("small");
                        m.setImgLarge("large");
                    }
                    m.setMaterialOther(getMaterialOtherByParam(mpArr, m));
                    m.setInitialStock(initialStockMap.get(m.getId())!=null? initialStockMap.get(m.getId()): BigDecimal.ZERO);
                    m.setBigUnitInitialStock(getBigUnitStock(m.getInitialStock(), m.getUnitId()));
                    m.setStock(currentStockMap.get(m.getId())!=null? currentStockMap.get(m.getId()): BigDecimal.ZERO);
                    m.setBigUnitStock(getBigUnitStock(m.getStock(), m.getUnitId()));
                }
            }
        } catch(Exception e){
            JshException.readFail(logger, e);
        }
        return list;
    }

    /**
     * 新增商品信息
     */
    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public int insertMaterial(JSONObject obj, HttpServletRequest request)throws Exception {
        //商品主表信息
        Material m = JSONObject.parseObject(obj.toJSONString(), Material.class);
        m.setEnabled(true);
        //获取类型编码
        Long serial_no = materialCategoryService.getMaterialCategory(m.getCategoryId()).getSerialNo();
        String sku = serial_no + DateUtils.dateTimeNow();
        //设置系统sku
        m.setSystemSku(sku);
        User user = userService.getCurrentUser();
        m.setTenantId(user.getId());
        try{
            //添加商品
            materialMapperEx.insertSelectiveEx(m);
            Long mId = m.getId();
            //添加商品拓展记录
            materialExtendService.saveDetials(obj, obj.getString("sortList"), mId, "insert");
            //设置初始库存
            if(obj.get("stock")!=null) {
                JSONArray stockArr = obj.getJSONArray("stock");
                for (int i = 0; i < stockArr.size(); i++) {
                    JSONObject jsonObj = stockArr.getJSONObject(i);
                    if(jsonObj.get("id")!=null && jsonObj.get("initStock")!=null) {
                        String number = jsonObj.getString("initStock");
                        BigDecimal lowSafeStock = null;
                        BigDecimal highSafeStock = null;
                        if(jsonObj.get("lowSafeStock")!=null) {
                            lowSafeStock = jsonObj.getBigDecimal("lowSafeStock");
                        }
                        if(jsonObj.get("highSafeStock")!=null) {
                            highSafeStock = jsonObj.getBigDecimal("highSafeStock");
                        }
                        Long depotId = jsonObj.getLong("id");
                        if(StringUtil.isNotEmpty(number) && Double.parseDouble(number)>0 || lowSafeStock!=null || highSafeStock!=null) {
                            //设置初始库
                            insertInitialStockByMaterialAndDepot(depotId, mId, parseBigDecimalEx(number), lowSafeStock, highSafeStock);
                            //设置当前库
                            //insertCurrentStockByMaterialAndDepot(depotId, mId, parseBigDecimalEx(number));
                            //更新当前库存
                            depotItemService.updateCurrentStockFun(mId, depotId);
                        }
                    }
                }
            }

            logService.insertLog("商品",
                    new StringBuffer(BusinessConstants.LOG_OPERATION_TYPE_ADD).append(m.getName()).toString(), request);
            return 1;
        }
        catch (BusinessRunTimeException ex) {
            throw new BusinessRunTimeException(ex.getCode(), ex.getMessage());
        }
        catch(Exception e){
            JshException.writeFail(logger, e);
            return 0;
        }
    }

    /**
     * 修改商品
     * @param obj
     * @param request
     */
    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public int updateMaterial(JSONObject obj, HttpServletRequest request) throws Exception{
        Material material = JSONObject.parseObject(obj.toJSONString(), Material.class);
        try{
            //修改商品属性
            materialMapper.updateByPrimaryKeySelective(material);
            //
            if(material.getUnitId() == null) {
                materialMapperEx.setUnitIdToNull(material.getId());
            }
//            if(material.getExpiryNum() == null) {
//                materialMapperEx.setExpiryNumToNull(material.getId());
//            }
            materialExtendService.saveDetials(obj, obj.getString("sortList"),material.getId(), "update");
            if(obj.get("stock")!=null) {
                JSONArray stockArr = obj.getJSONArray("stock");
                for (int i = 0; i < stockArr.size(); i++) {
                    JSONObject jsonObj = stockArr.getJSONObject(i);
                    if (jsonObj.get("id") != null && jsonObj.get("initStock") != null) {
                        String number = jsonObj.getString("initStock");
                        BigDecimal lowSafeStock = null;
                        BigDecimal highSafeStock = null;
                        if(jsonObj.get("lowSafeStock")!=null) {
                            lowSafeStock = jsonObj.getBigDecimal("lowSafeStock");
                        }
                        if(jsonObj.get("highSafeStock")!=null) {
                            highSafeStock = jsonObj.getBigDecimal("highSafeStock");
                        }
                        Long depotId = jsonObj.getLong("id");
                        //初始库存-先清除再插入
                        MaterialInitialStockExample example = new MaterialInitialStockExample();
                        example.createCriteria().andMaterialIdEqualTo(material.getId()).andDepotIdEqualTo(depotId);
                        materialInitialStockMapper.deleteByExample(example);
                        if (StringUtil.isNotEmpty(number) || lowSafeStock!=null || highSafeStock!=null) {
                            insertInitialStockByMaterialAndDepot(depotId, material.getId(), parseBigDecimalEx(number), lowSafeStock, highSafeStock);
                        }
                        //更新当前库存
                        depotItemService.updateCurrentStockFun(material.getId(), depotId);
                    }
                }
            }
            logService.insertLog("商品",
                    new StringBuffer(BusinessConstants.LOG_OPERATION_TYPE_EDIT).append(material.getName()).toString(), request);
            return 1;
        }catch(Exception e){
            JshException.writeFail(logger, e);
            return 0;
        }
    }

    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public int deleteMaterial(Long id, HttpServletRequest request)throws Exception {
        return batchDeleteMaterialByIds(id.toString());
    }

    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public int batchDeleteMaterial(String ids, HttpServletRequest request)throws Exception {
        return batchDeleteMaterialByIds(ids);
    }

    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public int batchDeleteMaterialByIds(String ids) throws Exception{
        String [] idArray=ids.split(",");
        //校验单据子表	jsh_depot_item
        List<DepotItem> depotItemList =null;
        try{
            depotItemList=  depotItemMapperEx.getDepotItemListListByMaterialIds(idArray);
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        if(depotItemList!=null&&depotItemList.size()>0){
            logger.error("异常码[{}],异常提示[{}],参数,MaterialIds[{}]",
                    ExceptionConstants.DELETE_FORCE_CONFIRM_CODE,ExceptionConstants.DELETE_FORCE_CONFIRM_MSG,ids);
            throw new BusinessRunTimeException(ExceptionConstants.DELETE_FORCE_CONFIRM_CODE,
                    ExceptionConstants.DELETE_FORCE_CONFIRM_MSG);
        }
        //记录日志
        StringBuffer sb = new StringBuffer();
        sb.append(BusinessConstants.LOG_OPERATION_TYPE_DELETE);
        //路径列表
        List<String> pathList = new ArrayList<>();
        List<Material> list = getMaterialListByIds(ids);
        for(Material material: list){
            sb.append("[").append(material.getName()).append("]");
            if(StringUtil.isNotEmpty(material.getImgName())) {
                pathList.add(material.getImgName());
            }
        }
        logService.insertLog("商品", sb.toString(),
                ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest());
        User userInfo=userService.getCurrentUser();
        //校验通过执行删除操作
        try{
            //逻辑删除商品
            materialMapperEx.batchDeleteMaterialByIds(new Date(),userInfo==null?null:userInfo.getId(),idArray);
            //逻辑删除商品价格扩展
            materialExtendMapperEx.batchDeleteMaterialExtendByMIds(idArray);
            //逻辑删除文件
            systemConfigService.deleteFileByPathList(pathList);
            return 1;
        }catch(Exception e){
            JshException.writeFail(logger, e);
            return 0;
        }
    }

    @Override
    public int checkIsNameExist(Long id, String name)throws Exception {
        MaterialExample example = new MaterialExample();
        example.createCriteria().andIdNotEqualTo(id).andNameEqualTo(name).andDeleteFlagNotEqualTo(BusinessConstants.DELETE_FLAG_DELETED);
        List<Material> list =null;
        try{
            list=  materialMapper.selectByExample(example);
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        return list==null?0:list.size();
    }

    @Override
    public int checkIsExist(Long id, String name, String model, String color, String standard, String mfrs,
                            String otherField1, String otherField2, String otherField3, String unit, Long unitId)throws Exception {
        return materialMapperEx.checkIsExist(id, name, model, color, standard, mfrs, otherField1,
                otherField2, otherField3, unit, unitId);
    }

    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public int batchSetStatus(Boolean status, String ids)throws Exception {
        logService.insertLog("商品",
                new StringBuffer(BusinessConstants.LOG_OPERATION_TYPE_EDIT).append(ids).toString(),
                ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest());
        List<Long> materialIds = StringUtil.strToLongList(ids);
        Material material = new Material();
        material.setEnabled(status);
        MaterialExample example = new MaterialExample();
        example.createCriteria().andIdIn(materialIds);
        int result =0;
        try{
            result=  materialMapper.updateByExampleSelective(material, example);
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        return result;
    }

    @Override
    public Unit findUnit(Long mId)throws Exception{
        Unit unit = new Unit();
        try{
            List<Unit> list = materialMapperEx.findUnitList(mId);
            if(list!=null && list.size()>0) {
                unit = list.get(0);
            }
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        return unit;
    }

    @Override
    public List<MaterialVo4Unit> findById(Long id)throws Exception{
        List<MaterialVo4Unit> list =null;
        try{
            list=  materialMapperEx.findById(id);
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        return list;
    }

    @Override
    public List<MaterialVo4Unit> findByIdWithBarCode(Long meId)throws Exception{
        List<MaterialVo4Unit> list =null;
        try{
            list=  materialMapperEx.findByIdWithBarCode(meId);
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        return list;
    }

    /**
     * 根据商品类型id获取子类型id集合
     * @param parentId 商品类型父id
     * @return
     */
    @Override
    public List<Long> getListByParentId(Long parentId) {
        List<Long> idList = new ArrayList<Long>();
        List<MaterialCategory> list = materialCategoryMapperEx.getListByParentId(parentId);
        idList.add(parentId);
        if(list!=null && list.size()>0) {
            getIdListByParentId(idList, parentId);
        }
        return idList;
    }

    @Override
    public List<Long> getIdListByParentId(List<Long> idList, Long parentId){
        List<MaterialCategory> list = materialCategoryMapperEx.getListByParentId(parentId);
        if(list!=null && list.size()>0) {
            for(MaterialCategory mc : list){
                idList.add(mc.getId());
                getIdListByParentId(idList, mc.getId());
            }
        }
        return idList;
    }

    @Override
    public JSONArray getMaterialByParam(String materialParam) {
        JSONArray arr = new JSONArray();
        List<MaterialVoSearch> list = materialMapperEx.getMaterialByParam(materialParam);
        for(MaterialVoSearch item: list) {
            JSONObject obj = new JSONObject();
            StringBuilder sb = new StringBuilder();
            sb.append(item.getBatchNumber());
            sb.append("_").append(item.getName());
            if(StringUtil.isNotEmpty(item.getMnemonic())) {
                sb.append("(").append(item.getMnemonic()).append(")");
            }
            if(StringUtil.isNotEmpty(item.getStandard())) {
                sb.append("(").append(item.getStandard()).append(")");
            }
            if(StringUtil.isNotEmpty(item.getModel())) {
                sb.append("(").append(item.getModel()).append(")");
            }
            if(StringUtil.isNotEmpty(item.getColor())) {
                sb.append("(").append(item.getColor()).append(")");
            }
            if(StringUtil.isNotEmpty(item.getUnit())) {
                sb.append("(").append(item.getUnit()).append(")");
            }
            obj.put("batchNumber", item.getBatchNumber());
            obj.put("materialStr", sb.toString());
            arr.add(obj);
        }
        return arr;
    }

    @Override
    public List<MaterialVo4Unit> findBySelectWithBarCode(Long categoryId, String q, String standardOrModel, String color,
                                                         String brand, String mfrs, String enableSerialNumber, String enableBatchNumber,
                                                         Integer offset, Integer rows, Long depotId) throws Exception{
        List<MaterialVo4Unit> list =null;
        try{
            List<Long> idList = new ArrayList<>();
            if(categoryId!=null){
                Long parentId = categoryId;
                idList = getListByParentId(parentId);
            }
            if(StringUtil.isNotEmpty(q)) {
                q = q.replace("'", "");
                q = q.trim();
            }
            list=  materialMapperEx.findBySelectWithBarCode(idList, q, standardOrModel, color, brand, mfrs,
                    enableSerialNumber, enableBatchNumber, offset, rows,depotId);
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        return list;
    }

    @Override
    public int findBySelectWithBarCodeCount(Long categoryId, String q, String standardOrModel, String color,
                                            String brand, String mfrs, String enableSerialNumber, String enableBatchNumber, Long depotId) throws Exception{
        int result=0;
        try{
            List<Long> idList = new ArrayList<>();
            if(categoryId!=null){
                Long parentId = categoryId;
                idList = getListByParentId(parentId);
            }
            if(StringUtil.isNotEmpty(q)) {
                q = q.replace("'", "");
            }
            result = materialMapperEx.findBySelectWithBarCodeCount(idList, q, standardOrModel, color, brand, mfrs,
                    enableSerialNumber, enableBatchNumber,depotId);
        }catch(Exception e){
            logger.error("异常码[{}],异常提示[{}],异常[{}]",
                    ExceptionConstants.DATA_READ_FAIL_CODE,ExceptionConstants.DATA_READ_FAIL_MSG,e);
            throw new BusinessRunTimeException(ExceptionConstants.DATA_READ_FAIL_CODE,
                    ExceptionConstants.DATA_READ_FAIL_MSG);
        }
        return result;
    }

    /**
     * 导出商品信息
     * @param categoryId
     * @param materialParam
     * @param color
     * @param materialOther
     * @param weight
     * @param expiryNum
     * @param enabled
     * @param enableSerialNumber
     * @param enableBatchNumber
     * @param remark
     * @param response
     * @throws Exception
     */
    @Override
    public void exportExcel(String categoryId, String materialParam, String color, String materialOther, String weight,
                            String expiryNum, String enabled, String enableSerialNumber, String enableBatchNumber,
                            String remark, HttpServletResponse response)throws Exception {
        //查询类型子集合id
        List<Long> idList = new ArrayList<>();
        if(StringUtil.isNotEmpty(categoryId)){
            idList = getListByParentId(Long.parseLong(categoryId));
        }
        //查询商品主条码相关列表
        List<MaterialVo4Unit> dataList = materialMapperEx.exportExcel(materialParam, color, materialOther, weight, expiryNum, enabled, enableSerialNumber,
                enableBatchNumber, remark, idList);
        //查询商品副条码相关列表
        Map<Long, MaterialExtend> otherMaterialMap = new HashMap<>();
        List<MaterialExtend> otherDataList = materialMapperEx.getOtherMaterialList();
        for(MaterialExtend me: otherDataList) {
            //遇到多个副条码的情况，只加第一个
            otherMaterialMap.putIfAbsent(me.getMaterialId(), me);
        }
        String nameStr = "名称*,规格,型号,颜色,品牌,类别,基础重量(kg),基本单位*,副单位,基本条码*,副条码,比例,多属性," +
                "采购价,零售价,销售价,最低售价,状态*,序列号,批号,自定义1,自定义2,自定义3,备注,系统sku,生产日期,保质期,供应商,商品条码,批次号,仓库名称,仓位货架";
        List<String> nameList = StringUtil.strToStringList(nameStr);
        //仓库列表
        List<Depot> depotList = depotService.getAllList();
        if (nameList != null) {
            for(Depot depot: depotList) {
                nameList.add(depot.getName());
            }
        }
        //期初库存缓存
        List<MaterialInitialStock> misList = materialInitialStockMapperEx.getListExceptZero();
        Map<String, BigDecimal> misMap = new HashMap<>();
        if (misList != null) {
            for (MaterialInitialStock mis : misList) {
                misMap.put(mis.getMaterialId() + "_" + mis.getDepotId(), mis.getNumber());
            }
        }
        String[] names = StringUtil.listToStringArray(nameList);
        String title = "商品信息";
        List<String[]> objects = new ArrayList<>();
        if (null != dataList) {
            for (MaterialVo4Unit m : dataList) {
                String[] objs = new String[names.length];
                objs[0] = m.getName();
                objs[1] = m.getStandard();
                objs[2] = m.getModel();
                objs[3] = m.getColor();
                objs[4] = m.getBrand();
                objs[5] = m.getCategoryName();
                objs[6] = m.getWeight() == null ? "" : m.getWeight().setScale(3, BigDecimal.ROUND_HALF_UP).toString();
                objs[7] = m.getCommodityUnit();
                objs[8] = otherMaterialMap.get(m.getId()) == null ? "" : otherMaterialMap.get(m.getId()).getCommodityUnit();
                objs[9] = m.getmBarCode();
                objs[10] = otherMaterialMap.get(m.getId()) == null ? "" : otherMaterialMap.get(m.getId()).getBarCode();
                objs[11] = m.getRatio() == null ? "" : m.getRatio().toString();
                objs[12] = m.getSku();
                objs[13] = m.getPurchaseDecimal() == null ? "" : m.getPurchaseDecimal().setScale(3, BigDecimal.ROUND_HALF_UP).toString();
                objs[14] = m.getCommodityDecimal() == null ? "" : m.getCommodityDecimal().setScale(3, BigDecimal.ROUND_HALF_UP).toString();
                objs[15] = m.getWholesaleDecimal() == null ? "" : m.getWholesaleDecimal().setScale(3, BigDecimal.ROUND_HALF_UP).toString();
                objs[16] = m.getLowDecimal() == null ? "" : m.getLowDecimal().setScale(3, BigDecimal.ROUND_HALF_UP).toString();
                objs[17] = m.getEnabled() ? "1" : "0";
                objs[18] = m.getEnableSerialNumber();
                objs[19] = m.getEnableBatchNumber();
                objs[20] = m.getOtherField1();
                objs[21] = m.getOtherField2();
                objs[22] = m.getOtherField3();
                objs[23] = m.getRemark();
                objs[24] = m.getSystemSku();
                objs[25] = m.getProductionDate() == null ? "" : m.getProductionDate().toString();
                objs[26] = m.getExpiryNum() == null ? "" : m.getExpiryNum().toString();
                objs[27] = m.getSupplierId() == null ? "" : m.getSupplierId().toString();
                objs[28] = m.getBarCode();
                objs[29] = m.getBatchNumber();
                objs[30] = m.getDepotId() == null ? "" : m.getDepotId().toString();
                objs[31] = m.getPosition();
                //仓库期初库存
                int i = 32;
                for(Depot depot: depotList) {
                    BigDecimal number = misMap.get(m.getId() + "_" + depot.getId());
                    objs[i] = number == null ? "0" : number.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                    i++;
                }
                objects.add(objs);
            }
        }
        File file = ExcelUtils.exportObjectsOneSheet(title, "*导入时本行内容请勿删除，切记！", names, title, objects);
        ExcelUtils.downloadExcel(file, file.getName(), response);
    }

    /**
     * 导入商品信息
     * @param file
     * @param request
     */
    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public BaseResponseInfo importExcel(MultipartFile file, HttpServletRequest request) throws Exception {
        BaseResponseInfo info = new BaseResponseInfo();
        try {
            Long beginTime = System.currentTimeMillis();
            //文件扩展名只能为xls
            String fileName = file.getOriginalFilename();
            if(StringUtil.isNotEmpty(fileName)) {
                String fileExt = fileName.substring(fileName.indexOf(".")+1);
                if(!"xls".equals(fileExt)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_EXTENSION_ERROR_CODE,
                            ExceptionConstants.MATERIAL_EXTENSION_ERROR_MSG);
                }
            }
            Workbook workbook = Workbook.getWorkbook(file.getInputStream());
            Sheet src = workbook.getSheet(0);
            //获取真实的行数，剔除掉空白行
            int rightRows = ExcelUtils.getRightRows(src);
            List<Depot> depotList= depotService.getDepot();
            int depotCount = depotList.size();
            Map<String, Long> depotMap = parseDepotToMap(depotList);
            User user = userService.getCurrentUser();
            List<MaterialWithInitStock> mList = new ArrayList<>();
            //单次导入超出1000条
            if(rightRows > 1002) {
                throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_IMPORT_OVER_LIMIT_CODE,
                        String.format(ExceptionConstants.MATERIAL_IMPORT_OVER_LIMIT_MSG));
            }
            for (int i = 2; i < rightRows; i++) {
                String name = ExcelUtils.getContent(src, i, 0); //名称
                String standard = ExcelUtils.getContent(src, i, 1); //规格
                String model = ExcelUtils.getContent(src, i, 2); //型号
                String color = ExcelUtils.getContent(src, i, 3); //颜色
                String brand = ExcelUtils.getContent(src, i, 4); //品牌
                String categoryName = ExcelUtils.getContent(src, i, 5); //类别
                String weight = ExcelUtils.getContent(src, i, 6); //基础重量(kg)
                String expiryNum = ExcelUtils.getContent(src, i, 7); //保质期(天)
                String unit = ExcelUtils.getContent(src, i, 8); //基本单位
                //名称为空
                if(StringUtil.isEmpty(name)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_NAME_EMPTY_CODE,
                            String.format(ExceptionConstants.MATERIAL_NAME_EMPTY_MSG, i+1));
                }
                //名称长度超出
                if(name.length()>100) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_NAME_OVER_CODE,
                            String.format(ExceptionConstants.MATERIAL_NAME_OVER_MSG, i+1));
                }
                //规格长度超出
                if(StringUtil.isNotEmpty(standard) && standard.length()>100) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_STANDARD_OVER_CODE,
                            String.format(ExceptionConstants.MATERIAL_STANDARD_OVER_MSG, i+1));
                }
                //型号长度超出
                if(StringUtil.isNotEmpty(model) && model.length()>100) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_MODEL_OVER_CODE,
                            String.format(ExceptionConstants.MATERIAL_MODEL_OVER_MSG, i+1));
                }
                //基本单位为空
                if(StringUtil.isEmpty(unit)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_UNIT_EMPTY_CODE,
                            String.format(ExceptionConstants.MATERIAL_UNIT_EMPTY_MSG, i+1));
                }
                //列别为空
                if(StringUtil.isEmpty(categoryName)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_Category_Name_EMPTY_CODE,
                            String.format(ExceptionConstants.MATERIAL_Category_Name_EMPTY_MSG, i+1));
                }
                MaterialWithInitStock m = new MaterialWithInitStock();
                m.setName(name);
                m.setStandard(standard);
                m.setModel(model);
                m.setColor(color);
                m.setBrand(brand);
                //通过名称生成助记码
                m.setMnemonic(PinYinUtil.getFirstLettersLo(name));
                Long categoryId = materialCategoryService.getCategoryIdByName(categoryName);
                //获取类型编码
                Long serial_no = categoryId == null ? null : materialCategoryService.getMaterialCategory(m.getCategoryId()).getSerialNo();
                //设置系统sku
                m.setSystemSku(serial_no + DateUtils.dateTimeNow());
                if(null!=categoryId){
                    m.setCategoryId(categoryId);
                }
                if(StringUtil.isNotEmpty(weight)) {
                    //校验基础重量是否是数字（含小数）
                    if(!StringUtil.isPositiveBigDecimal(weight)) {
                        throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_WEIGHT_NOT_DECIMAL_CODE,
                                String.format(ExceptionConstants.MATERIAL_WEIGHT_NOT_DECIMAL_MSG, i+1));
                    }
                    m.setWeight(new BigDecimal(weight));
                }
                if(StringUtil.isNotEmpty(expiryNum)) {
                    //校验保质期是否是正整数
                    if(!StringUtil.isPositiveLong(expiryNum)) {
                        throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_EXPIRY_NUM_NOT_INTEGER_CODE,
                                String.format(ExceptionConstants.MATERIAL_EXPIRY_NUM_NOT_INTEGER_MSG, i+1));
                    }
                    //m.setExpiryNum(Integer.parseInt(expiryNum));
                }
                String manyUnit = ExcelUtils.getContent(src, i, 9); //副单位
                String barCode = ExcelUtils.getContent(src, i, 10); //基础条码
                String manyBarCode = ExcelUtils.getContent(src, i, 11); //副条码
                String ratio = ExcelUtils.getContent(src, i, 12); //比例
                String sku = ExcelUtils.getContent(src, i, 13); //多属性
                String purchaseDecimal = ExcelUtils.getContent(src, i, 14); //采购价
                String commodityDecimal = ExcelUtils.getContent(src, i, 15); //零售价
                String wholesaleDecimal = ExcelUtils.getContent(src, i, 16); //销售价
                String lowDecimal = ExcelUtils.getContent(src, i, 17); //最低售价
                String enabled = ExcelUtils.getContent(src, i, 18); //状态
                String enableSerialNumber = ExcelUtils.getContent(src, i, 19); //序列号
                String enableBatchNumber = ExcelUtils.getContent(src, i, 20); //批号
                String position = ExcelUtils.getContent(src, i, 21); //仓位货架
                String mfrs = ExcelUtils.getContent(src, i, 22); //制造商
                String otherField1 = ExcelUtils.getContent(src, i, 23); //自定义1
                String otherField2 = ExcelUtils.getContent(src, i, 24); //自定义2
                String otherField3 = ExcelUtils.getContent(src, i, 25); //自定义3
                String remark = ExcelUtils.getContent(src, i, 26); //备注
               // m.setPosition(StringUtil.isNotEmpty(position)?position:null);
                //m.setMfrs(StringUtil.isNotEmpty(mfrs)?mfrs:null);
                m.setOtherField1(StringUtil.isNotEmpty(otherField1)?otherField1:null);
                m.setOtherField2(StringUtil.isNotEmpty(otherField2)?otherField2:null);
                m.setOtherField3(StringUtil.isNotEmpty(otherField3)?otherField3:null);
                m.setRemark(remark);
                //状态格式错误
                if(!"1".equals(enabled) && !"0".equals(enabled)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_ENABLED_ERROR_CODE,
                            String.format(ExceptionConstants.MATERIAL_ENABLED_ERROR_MSG, i+1));
                }
                //基本条码为空
                if(StringUtil.isEmpty(barCode)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_BARCODE_EMPTY_CODE,
                            String.format(ExceptionConstants.MATERIAL_BARCODE_EMPTY_MSG, i+1));
                }
                //校验基本条码长度为4到40位
                if(!StringUtil.checkBarCodeLength(barCode)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_BARCODE_LENGTH_ERROR_CODE,
                            String.format(ExceptionConstants.MATERIAL_BARCODE_LENGTH_ERROR_MSG, barCode));
                }
                //校验副条码长度为4到40位
                if(StringUtil.isNotEmpty(manyBarCode) && !StringUtil.checkBarCodeLength(manyBarCode)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_BARCODE_LENGTH_ERROR_CODE,
                            String.format(ExceptionConstants.MATERIAL_BARCODE_LENGTH_ERROR_MSG, manyBarCode));
                }
                //批量校验excel中有无重复商品，是指名称、规格、型号、颜色、单位、多属性
                batchCheckExistMaterialListByParam(mList, name, standard, model, color, unit, sku);
                //批量校验excel中有无重复条码（1-文档自身校验，2-和数据库里面的商品校验）
                batchCheckExistBarCodeByParam(mList, barCode, manyBarCode);
                //设置商品拓展属性
                JSONObject materialExObj = new JSONObject();
                JSONObject basicObj = new JSONObject();
                basicObj.put("barCode", barCode);
                basicObj.put("commodityUnit", unit);
                basicObj.put("sku", sku);
                basicObj.put("purchaseDecimal", purchaseDecimal);
                basicObj.put("commodityDecimal", commodityDecimal);
                basicObj.put("wholesaleDecimal", wholesaleDecimal);
                basicObj.put("lowDecimal", lowDecimal);
                materialExObj.put("basic", basicObj);
                if(StringUtil.isNotEmpty(manyUnit) && StringUtil.isNotEmpty(ratio)){ //多单位
                    //校验比例是否是数字（含小数）
                    if(!StringUtil.isPositiveBigDecimal(ratio.trim())) {
                        throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_RATIO_NOT_INTEGER_CODE,
                                String.format(ExceptionConstants.MATERIAL_RATIO_NOT_INTEGER_MSG, i+1));
                    }
                    Long unitId = unitService.getUnitIdByParam(unit, manyUnit, new BigDecimal(ratio.trim()));
                    if(unitId != null) {
                        m.setUnitId(unitId);
                        m.setUnit("");
                    } else {
                        throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_UNIT_MATE_CODE,
                                String.format(ExceptionConstants.MATERIAL_UNIT_MATE_MSG, manyBarCode));
                    }
                    JSONObject otherObj = new JSONObject();
                    otherObj.put("barCode", manyBarCode);
                    otherObj.put("commodityUnit", manyUnit);
                    otherObj.put("purchaseDecimal", parsePrice(purchaseDecimal,ratio));
                    otherObj.put("commodityDecimal", parsePrice(commodityDecimal,ratio));
                    otherObj.put("wholesaleDecimal", parsePrice(wholesaleDecimal,ratio));
                    otherObj.put("lowDecimal", parsePrice(lowDecimal,ratio));
                    materialExObj.put("other", otherObj);
                } else {
                    m.setUnit(unit);
                    m.setUnitId(null);
                }
                m.setMaterialExObj(materialExObj);
                m.setEnabled("1".equals(enabled));
                if(StringUtil.isNotEmpty(enableSerialNumber) && "1".equals(enableSerialNumber)) {
                    m.setEnableSerialNumber("1");
                } else {
                    m.setEnableSerialNumber("0");
                }
                if(StringUtil.isNotEmpty(enableBatchNumber) && "1".equals(enableBatchNumber)) {
                    m.setEnableBatchNumber("1");
                } else {
                    m.setEnableBatchNumber("0");
                }
                if("1".equals(enableSerialNumber) && "1".equals(enableBatchNumber)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_ENABLE_MUST_ONE_CODE,
                            String.format(ExceptionConstants.MATERIAL_ENABLE_MUST_ONE_MSG, barCode));
                }
                m.setStockMap(getStockMapCache(src, depotCount, depotMap, i));
                mList.add(m);
            }
            List<Long> deleteInitialStockMaterialIdList = new ArrayList<>();
            List<Long> deleteCurrentStockMaterialIdList = new ArrayList<>();
            List<MaterialInitialStock> insertInitialStockMaterialList = new ArrayList<>();
            List<MaterialCurrentStock> insertCurrentStockMaterialList = new ArrayList<>();
            //防止初始库存和当前库存出现重复
            Map<String, String> materialDepotInitialMap = new HashMap<>();
            Map<String, String> materialDepotCurrentMap = new HashMap<>();
            for(MaterialWithInitStock m: mList) {
                Long mId = 0L;
                //判断该商品是否存在，如果不存在就新增，如果存在就更新
                String basicBarCode = getBasicBarCode(m);
                //根据条件返回产品列表
                List<Material> materials = getMaterialListByParam(m.getName(),m.getStandard(),m.getModel(),m.getColor(),m.getUnit(),m.getUnitId(), basicBarCode);
                if(materials.size() == 0) { //产品列表为0，新增商品
                    materialMapperEx.insertSelectiveEx(m);
                    mId = m.getId();
                } else { //产品列表不为0，商品存在，修改商品属性
                    mId = materials.get(0).getId();
                    String materialJson = JSON.toJSONString(m);
                    Material material = JSONObject.parseObject(materialJson, Material.class);
                    material.setId(mId);
                    materialMapper.updateByPrimaryKeySelective(material);
                    //更新多单位
                    if(material.getUnitId() == null) {
                        materialMapperEx.setUnitIdToNull(material.getId());
                    }
                    //如果之前有保质期，则更新保质期
//                    if(materials.get(0).getExpiryNum()!=null && material.getExpiryNum() == null) {
//                        materialMapperEx.setExpiryNumToNull(material.getId());
//                    }
                }
                //给商品新增或更新条码与价格相关信息
                JSONObject materialExObj = m.getMaterialExObj();
                insertOrUpdateMaterialExtend(materialExObj, "basic", "1", mId, user);
                insertOrUpdateMaterialExtend(materialExObj, "other", "0", mId, user);
                //给商品更新库存
                Map<Long, BigDecimal> stockMap = m.getStockMap();
                for(Depot depot: depotList){
                    Long depotId = depot.getId();
                    String materialDepotKey = mId + "_" + depotId;
                    //获取初始库存
                    BigDecimal initStock = getInitStock(mId, depotId);
                    //excel里面的当前库存
                    BigDecimal stock = stockMap.get(depot.getId());
                    //新增或更新初始库存
                    if(stock!=null && stock.compareTo(BigDecimal.ZERO)!=0) {
                        String basicStr = materialExObj.getString("basic");
                        MaterialExtend materialExtend = JSONObject.parseObject(basicStr, MaterialExtend.class);
                        if(StringUtil.isNotEmpty(materialExtend.getSku())) {
                            throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_SKU_BEGIN_STOCK_FAILED_CODE,
                                    String.format(ExceptionConstants.MATERIAL_SKU_BEGIN_STOCK_FAILED_MSG, materialExtend.getBarCode()));
                        }
                        buildChangeInitialStock(deleteInitialStockMaterialIdList, insertInitialStockMaterialList, materialDepotInitialMap, mId, depotId, materialDepotKey, stock);
                    } else {
                        if(initStock.compareTo(BigDecimal.ZERO)!=0) {
                            buildChangeInitialStock(deleteInitialStockMaterialIdList, insertInitialStockMaterialList, materialDepotInitialMap, mId, depotId, materialDepotKey, stock);
                        }
                    }
                    //新增或更新当前库存
                    Long billCount = depotItemService.getCountByMaterialAndDepot(mId, depotId);
                    if(billCount == 0) {
                        if(stock!=null && stock.compareTo(BigDecimal.ZERO)!=0) {
                            buildChangeCurrentStock(deleteCurrentStockMaterialIdList, insertCurrentStockMaterialList, materialDepotCurrentMap, mId, depotId, materialDepotKey, stock);
                        } else {
                            if(initStock.compareTo(BigDecimal.ZERO)!=0) {
                                buildChangeCurrentStock(deleteCurrentStockMaterialIdList, insertCurrentStockMaterialList, materialDepotCurrentMap, mId, depotId, materialDepotKey, stock);
                            }
                        }
                    } else {
                        BigDecimal currentNumber = getCurrentStockByMaterialIdAndDepotId(mId, depotId);
                        //当前库存的更新：减去初始库存，再加上导入的新初始库存
                        if(currentNumber!=null && initStock!=null && stock!=null) {
                            currentNumber = currentNumber.subtract(initStock).add(stock);
                        }
                        buildChangeCurrentStock(deleteCurrentStockMaterialIdList, insertCurrentStockMaterialList, materialDepotCurrentMap, mId, depotId, materialDepotKey, currentNumber);
                    }
                }
            }
            //批量更新库存,先删除后新增
            if(insertInitialStockMaterialList.size()>0) {
                batchDeleteInitialStockByMaterialList(deleteInitialStockMaterialIdList);
                materialInitialStockMapperEx.batchInsert(insertInitialStockMaterialList);
            }
            if(insertCurrentStockMaterialList.size()>0) {
                batchDeleteCurrentStockByMaterialList(deleteCurrentStockMaterialIdList);
                materialCurrentStockMapperEx.batchInsert(insertCurrentStockMaterialList);
            }
            //添加日志
            logService.insertLog("商品",
                    new StringBuffer(BusinessConstants.LOG_OPERATION_TYPE_IMPORT).append(mList.size()).append(BusinessConstants.LOG_DATA_UNIT).toString(),
                    ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest());
            Long endTime = System.currentTimeMillis();
            logger.info("导入耗时：{}", endTime-beginTime);
            info.code = 200;
            info.data = "导入成功";
        } catch (BusinessRunTimeException e) {
            info.code = e.getCode();
            info.data = e.getData().get("message");
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            info.code = 500;
            info.data = "导入失败";
        }
        return info;
    }

    /**
     * 构造初始库存的变化
     */
    private void buildChangeInitialStock(List<Long> deleteInitialStockMaterialIdList, List<MaterialInitialStock> insertInitialStockMaterialList,
                                         Map<String, String> materialDepotInitialMap, Long mId, Long depotId, String materialDepotKey, BigDecimal stock) {
        if(materialDepotInitialMap.get(materialDepotKey)==null) {
            MaterialInitialStock materialInitialStock = new MaterialInitialStock();
            materialInitialStock.setMaterialId(mId);
            materialInitialStock.setDepotId(depotId);
            materialInitialStock.setNumber(stock);
            insertInitialStockMaterialList.add(materialInitialStock);
            deleteInitialStockMaterialIdList.add(mId);
            materialDepotInitialMap.put(materialDepotKey, materialDepotKey);
        }
    }

    /**
     * 构造当前库存的变化
     */
    private void buildChangeCurrentStock(List<Long> deleteCurrentStockMaterialIdList, List<MaterialCurrentStock> insertCurrentStockMaterialList,
                                         Map<String, String> materialDepotCurrentMap, Long mId, Long depotId, String materialDepotKey, BigDecimal stock) {
        if(materialDepotCurrentMap.get(materialDepotKey)==null) {
            MaterialCurrentStock materialCurrentStock = new MaterialCurrentStock();
            materialCurrentStock.setMaterialId(mId);
            materialCurrentStock.setDepotId(depotId);
            materialCurrentStock.setCurrentNumber(stock);
            insertCurrentStockMaterialList.add(materialCurrentStock);
            deleteCurrentStockMaterialIdList.add(mId);
            materialDepotCurrentMap.put(materialDepotKey, materialDepotKey);
        }
    }

    private Map<String, Long> parseDepotToMap(List<Depot> depotList) {
        Map<String, Long> map = new HashMap<>();
        for(Depot depot: depotList) {
            map.put(depot.getName(), depot.getId());
        }
        return map;
    }

    /**
     * 缓存各个仓库的库存信息
     * @param src
     * @param depotCount
     * @param depotMap
     * @param i
     * @return
     * @throws Exception
     */
    private Map<Long, BigDecimal> getStockMapCache(Sheet src, int depotCount, Map<String, Long> depotMap, int i) throws Exception {
        Map<Long, BigDecimal> stockMap = new HashMap<>();
        for(int j = 1; j<= depotCount; j++) {
            int col = 26 + j;
            if(col < src.getColumns()){
                String depotName = ExcelUtils.getContent(src, 1, col); //获取仓库名称
                if(StringUtil.isNotEmpty(depotName)) {
                    Long depotId = depotMap.get(depotName);
                    if(depotId!=null && depotId!=0L){
                        String stockStr = ExcelUtils.getContent(src, i, col);
                        if(StringUtil.isNotEmpty(stockStr)) {
                            stockMap.put(depotId, parseBigDecimalEx(stockStr));
                        }
                    }
                }
            }
        }
        return stockMap;
    }

    /**
     * 批量校验excel中有无重复商品，是指名称、规格、型号、颜色、单位
     * @param mList
     */
    @Override
    public void batchCheckExistMaterialListByParam(List<MaterialWithInitStock> mList, String name, String standard,
                                                   String model, String color, String unit, String sku) {
        for(MaterialWithInitStock material: mList){
            String materialSku = "";
            JSONObject materialExObj = material.getMaterialExObj();
            if(materialExObj!=null && materialExObj.get("basic")!=null) {
                JSONObject basicObj = materialExObj.getJSONObject("basic");
                if(basicObj!=null && materialExObj.get("sku")!=null) {
                    materialSku = basicObj.getString("sku");
                }
            }
            if(name.equals(material.getName()) &&
                    standard.equals(material.getStandard()) &&
                    model.equals(material.getModel()) &&
                    color.equals(material.getColor()) &&
                    unit.equals(material.getUnit()) &&
                    sku.equals(materialSku)) {
                String info = name + "-" + standard + "-" + model + "-" + color + "-" + unit + "-" + sku;
                throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_EXCEL_IMPORT_EXIST_CODE,
                        String.format(ExceptionConstants.MATERIAL_EXCEL_IMPORT_EXIST_MSG, info));
            }
        }
    }

    /**
     * 批量校验excel中有无重复条码（1-文档自身校验，2-和数据库里面的商品校验）
     * @param mList
     */
    @Override
    public void batchCheckExistBarCodeByParam(List<MaterialWithInitStock> mList,
                                              String barCode, String manyBarCode) throws Exception {
        if(StringUtil.isNotEmpty(manyBarCode)) {
            if(barCode.equals(manyBarCode)) {
                //同一个商品的主副条码重复了，进行提醒
                throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_EXCEL_IMPORT_BARCODE_EXIST_CODE,
                        String.format(ExceptionConstants.MATERIAL_EXCEL_IMPORT_BARCODE_EXIST_MSG, manyBarCode));
            }
            //EXCEL中有副条码在系统中已存在（除自身商品之外）
            int count = materialExtendService.getCountByManyBarCodeWithoutUs(manyBarCode, barCode);
            if (count>0) {
                throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_EXCEL_IMPORT_MANY_BARCODE_EXIST_CODE,
                        String.format(ExceptionConstants.MATERIAL_EXCEL_IMPORT_MANY_BARCODE_EXIST_MSG, manyBarCode));
            }
        }
        for(MaterialWithInitStock material: mList){
            JSONObject materialExObj = material.getMaterialExObj();
            String basicBarCode = "";
            String otherBarCode = "";
            if(materialExObj.get("basic")!=null) {
                JSONObject basicObj = materialExObj.getJSONObject("basic");
                basicBarCode = basicObj.getString("barCode");
            }
            if(materialExObj.get("other")!=null) {
                JSONObject otherObj = materialExObj.getJSONObject("other");
                otherBarCode = otherObj.getString("barCode");
            }
            if(barCode.equals(basicBarCode) || barCode.equals(otherBarCode)){
                throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_EXCEL_IMPORT_BARCODE_EXIST_CODE,
                        String.format(ExceptionConstants.MATERIAL_EXCEL_IMPORT_BARCODE_EXIST_MSG, barCode));
            }
            if(StringUtil.isNotEmpty(manyBarCode)) {
                if(manyBarCode.equals(basicBarCode) || manyBarCode.equals(otherBarCode)){
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_EXCEL_IMPORT_BARCODE_EXIST_CODE,
                            String.format(ExceptionConstants.MATERIAL_EXCEL_IMPORT_BARCODE_EXIST_MSG, manyBarCode));
                }
            }
        }
    }
    /**
     * 给商品新增或更新条码与价格相关信息
     * @param materialExObj
     * @param type
     * @param defaultFlag
     * @param mId
     * @param user
     */
    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public void insertOrUpdateMaterialExtend(JSONObject materialExObj, String type, String defaultFlag, Long mId, User user) throws Exception {
        if(StringUtil.isExist(materialExObj.get(type))){
            //获取对应的商品拓展字符
            String basicStr = materialExObj.getString(type);
            MaterialExtend materialExtend = JSONObject.parseObject(basicStr, MaterialExtend.class);
            materialExtend.setMaterialId(mId);
            materialExtend.setDefaultFlag(defaultFlag);
            materialExtend.setCreateTime(new Date());
            materialExtend.setUpdateTime(System.currentTimeMillis());
            materialExtend.setCreateSerial(user.getLoginName());
            materialExtend.setUpdateSerial(user.getLoginName());
            Long meId = 0L;
            if(StringUtil.isNotEmpty(materialExtend.getSku())){
                //含sku的商品，特殊逻辑
                meId = materialExtendService.selectIdByMaterialIdAndBarCode(mId, materialExtend.getBarCode());
                List<MaterialExtend> meList = materialExtendService.getListByMaterialIdAndDefaultFlagAndBarCode(mId, "1", materialExtend.getBarCode());
                if(meList.size() == 0) {
                    materialExtend.setDefaultFlag("1");
                } else {
                    materialExtend.setDefaultFlag("0");
                }
            } else {
                meId = materialExtendService.selectIdByMaterialIdAndDefaultFlag(mId, defaultFlag);
            }
            if(meId==0L){
                materialExtendMapper.insertSelective(materialExtend);
            } else {
                materialExtend.setId(meId);
                materialExtendMapper.updateByPrimaryKeySelective(materialExtend);
                //如果金额为空，此处单独置空
                materialExtendMapperEx.specialUpdatePrice(materialExtend);
            }
        }
    }

    @Override
    public String getBasicBarCode(MaterialWithInitStock m) {
        String barCode = "";
        JSONObject materialExObj = m.getMaterialExObj();
        if(StringUtil.isExist(materialExObj.get("basic"))) {
            String basicStr = materialExObj.getString("basic");
            MaterialExtend basicMaterialExtend = JSONObject.parseObject(basicStr, MaterialExtend.class);
            barCode = basicMaterialExtend.getBarCode();
        }
        return barCode;
    }

    /**
     * 根据条件返回产品列表
     * @param name
     * @param standard
     * @param model
     * @param color
     * @param unit
     * @param unitId
     * @return
     */
    @Override
    public List<Material> getMaterialListByParam(String name, String standard, String model, String color, String unit, Long unitId, String basicBarCode) throws Exception {
        List<Material> list = new ArrayList<>();
        MaterialExample example = new MaterialExample();
        MaterialExample.Criteria criteria = example.createCriteria();
        criteria.andNameEqualTo(name);
        if (StringUtil.isNotEmpty(model)) {
            criteria.andModelEqualTo(model);
        }
        if (StringUtil.isNotEmpty(color)) {
            criteria.andColorEqualTo(color);
        }
        if (StringUtil.isNotEmpty(standard)) {
            criteria.andStandardEqualTo(standard);
        }
        if (StringUtil.isNotEmpty(unit)) {
            criteria.andUnitEqualTo(unit);
        }
        if (unitId !=null) {
            criteria.andUnitIdEqualTo(unitId);
        }
        criteria.andDeleteFlagNotEqualTo(BusinessConstants.DELETE_FLAG_DELETED);
        list = materialMapper.selectByExample(example);
        if(list.size()==0) {
            //如果通过组合条件没有查到该商品，则通过条码再查一次
            MaterialExtend materialExtend = materialExtendService.getInfoByBarCode(basicBarCode);
            if(materialExtend != null && materialExtend.getMaterialId()!=null) {
                Material material = new Material();
                material.setId(materialExtend.getMaterialId());
                list.add(material);
            }
        }
        return list;
    }

    /**
     * 写入初始库存
     * @param depotId 仓库id
     * @param mId   商品id
     * @param stock 库存数量
     */
    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public void insertInitialStockByMaterialAndDepot(Long depotId, Long mId, BigDecimal stock, BigDecimal lowSafeStock, BigDecimal highSafeStock){
        MaterialInitialStock materialInitialStock = new MaterialInitialStock();
        materialInitialStock.setDepotId(depotId);
        materialInitialStock.setMaterialId(mId);
        stock = stock == null? BigDecimal.ZERO: stock;
        materialInitialStock.setNumber(stock);
        if(lowSafeStock!=null) {
            materialInitialStock.setLowSafeStock(lowSafeStock);
        }
        if(highSafeStock!=null) {
            materialInitialStock.setHighSafeStock(highSafeStock);
        }
        materialInitialStockMapper.insertSelective(materialInitialStock); //存入初始库存
    }

    /**
     * 写入当前库存
     * @param depotId 仓库id
     * @param mId   商品id
     * @param stock 库存数量
     */
    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public void insertCurrentStockByMaterialAndDepot(Long depotId, Long mId, BigDecimal stock){
        MaterialCurrentStock materialCurrentStock = new MaterialCurrentStock();
        materialCurrentStock.setDepotId(depotId);
        materialCurrentStock.setMaterialId(mId);
        materialCurrentStock.setCurrentNumber(stock);
        materialCurrentStockMapper.insertSelective(materialCurrentStock); //存入当前库存
    }

    /**
     * 批量删除初始库存
     * @param mIdList
     */
    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public void batchDeleteInitialStockByMaterialList(List<Long> mIdList){
        MaterialInitialStockExample example = new MaterialInitialStockExample();
        example.createCriteria().andMaterialIdIn(mIdList);
        materialInitialStockMapper.deleteByExample(example);
    }

    /**
     * 批量删除当前库存
     * @param mIdList
     */
    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public void batchDeleteCurrentStockByMaterialList(List<Long> mIdList){
        MaterialCurrentStockExample example = new MaterialCurrentStockExample();
        example.createCriteria().andMaterialIdIn(mIdList);
        materialCurrentStockMapper.deleteByExample(example);
    }

    @Override
    public List<MaterialVo4Unit> getMaterialEnableSerialNumberList(String q, Integer offset, Integer rows)throws Exception {
        List<MaterialVo4Unit> list =null;
        try{
            list=  materialMapperEx.getMaterialEnableSerialNumberList(q, offset, rows);
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        return list;
    }

    @Override
    public Long getMaterialEnableSerialNumberCount(String q)throws Exception {
        Long count =null;
        try{
            count=  materialMapperEx.getMaterialEnableSerialNumberCount(q);
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        return count;
    }

    @Override
    public BigDecimal parseBigDecimalEx(String str) throws Exception{
        if(!StringUtil.isEmpty(str)) {
            return  new BigDecimal(str);
        } else {
            return null;
        }
    }

    @Override
    public BigDecimal parsePrice(String price, String ratio) throws Exception{
        if(StringUtil.isEmpty(price) || StringUtil.isEmpty(ratio)) {
            return BigDecimal.ZERO;
        } else {
            BigDecimal pr=new BigDecimal(price);
            BigDecimal r=new BigDecimal(ratio);
            return pr.multiply(r);
        }
    }

    /**
     * 根据商品获取初始库存-多仓库
     * @param depotList
     * @param materialId
     * @return
     */
    @Override
    public BigDecimal getInitStockByMidAndDepotList(List<Long> depotList, Long materialId) {
        BigDecimal stock = BigDecimal.ZERO;
        MaterialInitialStockExample example = new MaterialInitialStockExample();
        if(depotList!=null && depotList.size()>0) {
            example.createCriteria().andMaterialIdEqualTo(materialId).andDepotIdIn(depotList)
                    .andDeleteFlagNotEqualTo(BusinessConstants.DELETE_FLAG_DELETED);
        } else {
            example.createCriteria().andMaterialIdEqualTo(materialId)
                    .andDeleteFlagNotEqualTo(BusinessConstants.DELETE_FLAG_DELETED);
        }
        List<MaterialInitialStock> list = materialInitialStockMapper.selectByExample(example);
        if(list!=null && list.size()>0) {
            for(MaterialInitialStock ms: list) {
                if(ms!=null) {
                    stock = stock.add(ms.getNumber());
                }
            }
        }
        return stock;
    }

    /**
     * 根据商品和仓库获取初始库存
     * @param materialId
     * @param depotId
     * @return
     */
    @Override
    public BigDecimal getInitStock(Long materialId, Long depotId) {
        BigDecimal stock = BigDecimal.ZERO;
        MaterialInitialStockExample example = new MaterialInitialStockExample();
        example.createCriteria().andMaterialIdEqualTo(materialId).andDepotIdEqualTo(depotId)
                .andDeleteFlagNotEqualTo(BusinessConstants.DELETE_FLAG_DELETED);
        List<MaterialInitialStock> list = materialInitialStockMapper.selectByExample(example);
        if(list!=null && list.size()>0) {
            stock = list.get(0).getNumber();
        }
        return stock;
    }

    /**
     * 根据商品和仓库获取当前库存
     * @param materialId
     * @param depotId
     * @return
     */
    @Override
    public BigDecimal getCurrentStockByMaterialIdAndDepotId(Long materialId, Long depotId) {
        BigDecimal stock = BigDecimal.ZERO;
        MaterialCurrentStockExample example = new MaterialCurrentStockExample();
        example.createCriteria().andMaterialIdEqualTo(materialId).andDepotIdEqualTo(depotId)
                .andDeleteFlagNotEqualTo(BusinessConstants.DELETE_FLAG_DELETED);
        List<MaterialCurrentStock> list = materialCurrentStockMapper.selectByExample(example);
        if(list!=null && list.size()>0) {
            stock = list.get(0).getCurrentNumber();
        } else {
            stock = getInitStock(materialId,depotId);
        }
        return stock;
    }

    /**
     * 根据商品列表获取初始库存Map
     * @param list
     * @return
     */
    @Override
    public Map<Long,BigDecimal> getInitialStockMapByMaterialList(List<MaterialVo4Unit> list) {
        Map<Long,BigDecimal> map = new HashMap<>();
        List<Long> materialIdList = new ArrayList<>();
        for(MaterialVo4Unit materialVo4Unit: list) {
            materialIdList.add(materialVo4Unit.getId());
        }
        List<MaterialInitialStock> mcsList = materialInitialStockMapperEx.getInitialStockMapByIdList(materialIdList);
        for(MaterialInitialStock materialInitialStock: mcsList) {
            map.put(materialInitialStock.getMaterialId(), materialInitialStock.getNumber());
        }
        return map;
    }

    /**
     * 根据商品列表获取当前库存Map
     * @param list
     * @return
     */
    @Override
    public Map<Long,BigDecimal> getCurrentStockMapByMaterialList(List<MaterialVo4Unit> list) {
        Map<Long,BigDecimal> map = new HashMap<>();
        List<Long> materialIdList = new ArrayList<>();
        for(MaterialVo4Unit materialVo4Unit: list) {
            materialIdList.add(materialVo4Unit.getId());
        }
        List<MaterialCurrentStock> mcsList = materialCurrentStockMapperEx.getCurrentStockMapByIdList(materialIdList);
        for(MaterialCurrentStock materialCurrentStock: mcsList) {
            map.put(materialCurrentStock.getMaterialId(), materialCurrentStock.getCurrentNumber());
        }
        return map;
    }

    /**
     * 根据商品和仓库获取安全库存信息
     * @param materialId
     * @param depotId
     * @return
     */
    @Override
    public MaterialInitialStock getSafeStock(Long materialId, Long depotId) {
        MaterialInitialStock materialInitialStock = new MaterialInitialStock();
        MaterialInitialStockExample example = new MaterialInitialStockExample();
        example.createCriteria().andMaterialIdEqualTo(materialId).andDepotIdEqualTo(depotId)
                .andDeleteFlagNotEqualTo(BusinessConstants.DELETE_FLAG_DELETED);
        List<MaterialInitialStock> list = materialInitialStockMapper.selectByExample(example);
        if(list!=null && list.size()>0) {
            materialInitialStock = list.get(0);
        }
        return materialInitialStock;
    }

    @Override
    public List<MaterialVo4Unit> getMaterialByMeId(Long meId) {
        List<MaterialVo4Unit> result = new ArrayList<MaterialVo4Unit>();
        try{
            if(meId!=null) {
                result= materialMapperEx.getMaterialByMeId(meId);
            }
        }catch(Exception e){
            JshException.readFail(logger, e);
        }
        return result;
    }

    @Override
    public String getMaxBarCode() {
        List<String> barCodeOldList = materialMapperEx.getBarCodeList();
        // 使用 Stream API 处理条码列表
        OptionalLong maxBarcode = barCodeOldList.stream()
                .filter(StringUtil::isNumeric)   // 过滤掉非数字条码
                .mapToLong(Long::parseLong)      // 将字符串转换为 Long 类型
                .max();                          // 获取最大值
        // 如果存在最大值，返回它；否则返回 1000L
        Long maxBarCodeOld = maxBarcode.orElse(1000L);
        return maxBarCodeOld + "";
    }

    @Override
    public List<String> getMaterialNameList() {
        return materialMapperEx.getMaterialNameList();
    }

    /**
     * 根据条码查询商品信息
     * @param barCode 商品条码
     */
    @Override
    public List<MaterialVo4Unit> getMaterialByBarCode(String barCode) {
        String [] barCodeArray=barCode.split(",");
        return materialMapperEx.getMaterialByBarCode(barCodeArray);
    }

    @Override
    public List<MaterialVo4Unit> getMaterialByBarCode(List<String> barCodeList) {
        // 将 List<String> 转换为 String[]
        String[] barCodeArray = barCodeList.toArray(new String[0]);
        return materialMapperEx.getMaterialByBarCode(barCodeArray);
    }

    @Override
    public List<MaterialVo4Unit> getMaterialByBarCodeAndWithOutMId(String barCode, Long mId) {
        String [] barCodeArray=barCode.split(",");
        return materialMapperEx.getMaterialByBarCodeAndWithOutMId(barCodeArray, mId);
    }

    @Override
    public List<MaterialInitialStockWithMaterial> getInitialStockWithMaterial(List<Long> depotList) {
        return materialMapperEx.getInitialStockWithMaterial(depotList);
    }

    @Override
    public List<MaterialVo4Unit> getListWithStock(List<Long> depotList, List<Long> idList, String position, String materialParam,
                                                  Boolean moveAvgPriceFlag, Integer zeroStock, String column, String order,
                                                  Integer offset, Integer rows) throws Exception {
        Map<Long, BigDecimal> initialStockMap = new HashMap<>();
        List<MaterialInitialStockWithMaterial> initialStockList = getInitialStockWithMaterial(depotList);
        for (MaterialInitialStockWithMaterial mism: initialStockList) {
            initialStockMap.put(mism.getMaterialId(), mism.getNumber());
        }
        List<MaterialVo4Unit> dataList = materialMapperEx.getListWithStock(depotList, idList, position, materialParam, zeroStock, column, order, offset, rows);
        for(MaterialVo4Unit item: dataList) {
            if(moveAvgPriceFlag) {
                item.setPurchaseDecimal(item.getCurrentUnitPrice());
                item.setCurrentStockPrice(item.getCurrentStockMovePrice());
            }
            item.setUnitName(null!=item.getUnitId()?item.getUnitName() + "[多单位]":item.getUnitName());
            item.setInitialStock(null!=initialStockMap.get(item.getId())?initialStockMap.get(item.getId()):BigDecimal.ZERO);
            item.setBigUnitStock(getBigUnitStock(item.getCurrentStock(), item.getUnitId()));
            if(fileUploadType == 2) {
                item.setImgSmall("small");
                item.setImgLarge("large");
            }
        }
        return dataList;
    }

    @Override
    public int getListWithStockCount(List<Long> depotList, List<Long> idList, String position, String materialParam, Integer zeroStock) {
        return materialMapperEx.getListWithStockCount(depotList, idList, position, materialParam, zeroStock);
    }

    @Override
    public MaterialVo4Unit getTotalStockAndPrice(List<Long> depotList, List<Long> idList, String position, String materialParam) {
        return materialMapperEx.getTotalStockAndPrice(depotList, idList, position, materialParam);
    }

    /**
     * 将小单位的库存换算为大单位的库存
     * @param stock
     * @param unitId
     * @return
     * @throws Exception
     */
    @Override
    public String getBigUnitStock(BigDecimal stock, Long unitId) throws Exception {
        String bigUnitStock = "";
        if(null!= unitId) {
            Unit unit = unitService.getUnit(unitId);
            if(unit.getRatio()!=null && unit.getRatio().compareTo(BigDecimal.ZERO)!=0 && stock!=null) {
                bigUnitStock = stock.divide(unit.getRatio(),2,BigDecimal.ROUND_HALF_UP) + unit.getOtherUnit();
            }
        }
        return bigUnitStock;
    }

    /**
     * 构造扩展信息
     * @param mpArr
     * @param m
     * @return
     */
    @Override
    public String getMaterialOtherByParam(String[] mpArr, MaterialVo4Unit m) {
        String materialOther = "";
        for (int i = 0; i < mpArr.length; i++) {
            if (mpArr[i].equals("自定义1")) {
                materialOther = materialOther + ((m.getOtherField1() == null || m.getOtherField1().equals("")) ? "" : "(" + m.getOtherField1() + ")");
            }
            if (mpArr[i].equals("自定义2")) {
                materialOther = materialOther + ((m.getOtherField2() == null || m.getOtherField2().equals("")) ? "" : "(" + m.getOtherField2() + ")");
            }
            if (mpArr[i].equals("自定义3")) {
                materialOther = materialOther + ((m.getOtherField3() == null || m.getOtherField3().equals("")) ? "" : "(" + m.getOtherField3() + ")");
            }
        }
        return materialOther;
    }

    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public int batchSetMaterialCurrentStock(String ids) throws Exception {
        int res = 0;
        List<Long> idList = StringUtil.strToLongList(ids);
        List<Depot> depotList = depotService.getAllList();
        for(Long mId: idList) {
            for(Depot depot: depotList) {
                depotItemService.updateCurrentStockFun(mId, depot.getId());
                res = 1;
            }
        }
        return res;
    }

    @Override
    @Transactional(value = "transactionManager", rollbackFor = Exception.class)
    public int batchSetMaterialCurrentUnitPrice(String ids) throws Exception {
        int res = 0;
        List<Long> idList = StringUtil.strToLongList(ids);
        for(Long mId: idList) {
            DepotItem depotItem = new DepotItem();
            depotItem.setMaterialId(mId);
            depotItemService.updateCurrentUnitPrice(depotItem);
            res = 1;
        }
        return res;
    }

    @Override
    public int batchUpdate(JSONObject jsonObject) {
        String ids = jsonObject.getString("ids");
        String materialStr = jsonObject.getString("material");
        List<Long> idList = StringUtil.strToLongList(ids);
        Material material = JSONObject.parseObject(materialStr, Material.class);
        MaterialExample example = new MaterialExample();
        example.createCriteria().andIdIn(idList).andDeleteFlagNotEqualTo(BusinessConstants.DELETE_FLAG_DELETED);
        return materialMapper.updateByExampleSelective(material, example);
    }

    @Override
    public MaterialExtend getMaterialExtendBySerialNumber(String serialNumber) {
        return materialMapperEx.getMaterialExtendBySerialNumber(serialNumber);
    }

    /**
     * 根据批次号查询商品信息
     * @param batchNumber 批次号
     */
    @Override
    public List<MaterialVo4Unit> getMaterialByBatchNumber(String batchNumber) {
        String [] batchNumberArray=batchNumber.split(",");
        List<MaterialVo4Unit> list =  materialMapperEx.getMaterialByBatchNumber(batchNumberArray);
        list.forEach(v -> {
            v.setUnitList(v.getUnitId() == null ? null : unitService.getUnitListByID(v.getUnitId()));
        });
        return list;
    }

    /**
     * 根据商品id查询主表及子表信息
     * @param id 商品id
     * @return
     */
    @Override
    public Material getMaterialById(Long id){
        Material material = materialMapper.selectByPrimaryKey(id);
        List<MaterialExtend> list = materialExtendMapper.selectByMId(id);
        material.setList(list);
        return material;
    }

    @Override
    public List<MaterialVo4Unit> getMaterialBySystemSku(List<String> systemSkuList) {
        if (CollectionUtil.isEmpty(systemSkuList)){
            return null;
        }
        String[] systemSkuArray = systemSkuList.toArray(new String[systemSkuList.size()]);
        return materialMapperEx.getMaterialBySystemSku(systemSkuArray);
    }

    @Override
    public List<MaterialCurrentStock4SystemSku> getMaterialCurrentPriceByIdList(List<Long> idList) {
        if (CollectionUtil.isEmpty(idList)) {
            return null;
        }
        return materialMapperEx.getMaterialCurrentPriceByIdList(idList);
    }

    /**
     * 获取商品提醒
     * @return
     */
    @Override
    public MaterialWarnListVo getMaterialWarn() {
        MaterialWarnListVo vo = new MaterialWarnListVo();
        List<String> noMovingPinReminders = new ArrayList<>();
        List<String> expirationReminders = new ArrayList<>();
        List<String> inventoryReminders = new ArrayList<>();
        //获取商品信息
        MaterialExample materialExample = new MaterialExample();
        materialExample.createCriteria().andDeleteFlagEqualTo("0");
        List<Material> materials = materialMapper.selectByExample(materialExample);
        //获取商品子表信息
        MaterialExtendExample materialExtendExample = new MaterialExtendExample();
        materialExtendExample.createCriteria().andDeleteFlagEqualTo("0");
        List<MaterialExtend> extendList = materialExtendMapper.selectByExample(materialExtendExample);
        //无动销提醒
        materials.stream().filter( v -> v.getMovingPinReminderCycle() != null && !"".equals(v.getMovingPinReminderCycle()))
                        .forEach(v -> {
                            //获取商品最后一条动销订单数据
                            DepotHead depotHead = depotHeadService.getDepotLastByMaterialId(v.getId());
                            if (depotHead != null){
                                if (DateUtils.differentDaysByMillisecond(depotHead.getOperTime(),new Date()) > Integer.valueOf(v.getMovingPinReminderCycle())){
                                    //当前时间对比订单时间是否大于动销提醒周期
                                    String str = "商品名称：" + v.getName() + ",在[无动销提醒周期]内，无动销，请及时处理";
                                    noMovingPinReminders.add(str);
                                }
                            }else{
                                //获取商品批次信息
                                MaterialExtend m = materialExtendMapper.selectByMId(v.getId()).get(0);
                                if (DateUtils.differentDaysByMillisecond(m.getCreateTime(),new Date()) > Integer.valueOf(v.getMovingPinReminderCycle())){
                                    String str = "商品名称：" + v.getName() + ",在[无动销提醒周期]内，无动销，请及时处理";
                                    noMovingPinReminders.add(str);
                                }
                            }

                        });
        vo.setNoMovingPinReminder(noMovingPinReminders);
        //过期提醒
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        extendList.stream().filter(v -> v.getInventory() != null && v.getInventory().doubleValue() > 0 && v.getProductionDate() != null && v.getExpiryNum() != null)
                .forEach(v ->{
                    if (DateUtils.differentDaysByMillisecond(v.getProductionDate(),new Date()) >  (v.getExpiryNum() -30)){
                        String name = materialMapper.selectByPrimaryKey(v.getMaterialId()).getName();
                        String str = "商品名称:" + name
                                + ", 批次号：" + v.getBatchNumber()
                                + ", 条码：" + v.getBarCode()
                                + ", 生产日期：" + sdf.format(v.getProductionDate())
                                + ", 保质期：" + v.getExpiryNum() + "天"
                                + ", 库存：" + v.getInventory()
                                + ",即将要过期，请及时处理";
                        expirationReminders.add(str);
                    }
                });
        vo.setExpirationReminder(expirationReminders);
        //库存提醒
        MaterialInitialStockExample initialStockExample = new MaterialInitialStockExample();
        initialStockExample.createCriteria().andDeleteFlagEqualTo("0");

        List<MaterialInitialStock> initialStocks = materialInitialStockMapper.selectByExample(initialStockExample);
        initialStocks.stream().filter(v -> v.getLowSafeStock() != null && v.getLowSafeStock().doubleValue() > 0)
                .forEach(v -> {
                    //根据商品id和仓库id查询当前库存
                    BigDecimal currentNumber = getCurrentStockByMaterialIdAndDepotId(v.getMaterialId(),v.getDepotId());
                    if (currentNumber.doubleValue() <= v.getLowSafeStock().doubleValue()){
                        Material material = materialMapper.selectByPrimaryKey(v.getMaterialId());
                        String str = "商品名称:" + material.getName()
                                + ",库存告警，请及时处理";
                        inventoryReminders.add(str);
                    }
                });
        vo.setInventoryReminder(inventoryReminders);
        return vo;
    }

    @Override
    public BaseResponseInfo importExcelTwo(MultipartFile file, HttpServletRequest request) throws Exception {
        BaseResponseInfo info = new BaseResponseInfo();
        try {
            Long beginTime = System.currentTimeMillis();
            //文件扩展名只能为xls
            String fileName = file.getOriginalFilename();
            if(StringUtil.isNotEmpty(fileName)) {
                String fileExt = fileName.substring(fileName.indexOf(".")+1);
                if(!"xls".equals(fileExt)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_EXTENSION_ERROR_CODE,
                            ExceptionConstants.MATERIAL_EXTENSION_ERROR_MSG);
                }
            }
            Workbook workbook = Workbook.getWorkbook(file.getInputStream());
            Sheet src = workbook.getSheet(0);
            //获取真实的行数，剔除掉空白行
            int rightRows = ExcelUtils.getRightRows(src);
            //获取所有仓库
            List<Depot> depotList= depotService.getDepot();
            int depotCount = depotList.size();
            Map<String, Long> depotMap = parseDepotToMap(depotList);
            User user = userService.getCurrentUser();
            List<MaterialWithInitStock> mList = new ArrayList<>();
            //单次导入超出1000条
            if(rightRows > 1002) {
                throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_IMPORT_OVER_LIMIT_CODE,
                        String.format(ExceptionConstants.MATERIAL_IMPORT_OVER_LIMIT_MSG));
            }
            for (int i = 2; i < rightRows; i++) {
                String name = ExcelUtils.getContent(src, i, 0); //名称
                //名称为空
                if(StringUtil.isEmpty(name)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_NAME_EMPTY_CODE,
                            String.format(ExceptionConstants.MATERIAL_NAME_EMPTY_MSG, i+1));
                }
                //名称长度超出
                if(name.length()>100) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_NAME_OVER_CODE,
                            String.format(ExceptionConstants.MATERIAL_NAME_OVER_MSG, i+1));
                }
                String standard = ExcelUtils.getContent(src, i, 1); //规格
                //规格长度超出
                if(StringUtil.isNotEmpty(standard) && standard.length()>100) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_STANDARD_OVER_CODE,
                            String.format(ExceptionConstants.MATERIAL_STANDARD_OVER_MSG, i+1));
                }
                String model = ExcelUtils.getContent(src, i, 2); //型号
                //型号长度超出
                if(StringUtil.isNotEmpty(model) && model.length()>100) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_MODEL_OVER_CODE,
                            String.format(ExceptionConstants.MATERIAL_MODEL_OVER_MSG, i+1));
                }
                String color = ExcelUtils.getContent(src, i, 3); //颜色
                String brand = ExcelUtils.getContent(src, i, 4); //品牌
                String categoryName = ExcelUtils.getContent(src, i, 5); //类别
                //类别为空
                if(StringUtil.isEmpty(categoryName)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_Category_Name_EMPTY_CODE,
                            String.format(ExceptionConstants.MATERIAL_Category_Name_EMPTY_MSG, i+1));
                }
                //通过类型名查询类型编号
                Long categoryId = materialCategoryService.getCategoryIdByName(categoryName);
                if(null == categoryId){
                    //类别不存在
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_TYPE_NOT_DECIMAL_CODE,
                            String.format(ExceptionConstants.MATERIAL_TYPE_NOT_DECIMAL_MSG, i+1));
                }
                String weight = ExcelUtils.getContent(src, i, 6); //基础重量(kg)
                if(StringUtil.isNotEmpty(weight)) {
                    //校验基础重量是否是数字（含小数）
                    if(!StringUtil.isPositiveBigDecimal(weight)) {
                        throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_WEIGHT_NOT_DECIMAL_CODE,
                                String.format(ExceptionConstants.MATERIAL_WEIGHT_NOT_DECIMAL_MSG, i+1));
                    }
                }
                String unit = ExcelUtils.getContent(src, i, 7); //基本单位
                //基本单位为空
                if(StringUtil.isEmpty(unit)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_UNIT_EMPTY_CODE,
                            String.format(ExceptionConstants.MATERIAL_UNIT_EMPTY_MSG, i+1));
                }
                String manyUnit = ExcelUtils.getContent(src, i, 8); //副单位
                String ratio = ExcelUtils.getContent(src, i, 9); //比例
                String sku = ExcelUtils.getContent(src, i, 10); //多属性
                String purchaseDecimal = ExcelUtils.getContent(src, i, 11); //采购价
                String commodityDecimal = ExcelUtils.getContent(src, i, 12); //零售价
                String wholesaleDecimal = ExcelUtils.getContent(src, i, 13); //销售价
                String lowDecimal = ExcelUtils.getContent(src, i, 14); //最低售价
                String enabled = ExcelUtils.getContent(src, i, 15); //状态
                //状态格式错误
                if(!"1".equals(enabled) && !"0".equals(enabled)) {
                    throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_ENABLED_ERROR_CODE,
                            String.format(ExceptionConstants.MATERIAL_ENABLED_ERROR_MSG, i+1));
                }
                String enableSerialNumber = ExcelUtils.getContent(src, i, 16); //序列号
                String productionDate = ExcelUtils.getContent(src, i, 17); //生产日期
                String expiryNum = ExcelUtils.getContent(src, i, 18); //保质期天数
                if(StringUtil.isNotEmpty(expiryNum)) {
                    //校验保质期是否是正整数
                    if(!StringUtil.isPositiveLong(expiryNum)) {
                        throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_EXPIRY_NUM_NOT_INTEGER_CODE,
                                String.format(ExceptionConstants.MATERIAL_EXPIRY_NUM_NOT_INTEGER_MSG, i+1));
                    }
                }
                String supplier = ExcelUtils.getContent(src, i, 19); //供应商
                Long supplierId = null;
                if(StringUtil.isNotEmpty(supplier)) {
                    //根据供应商查询供应商id
                    Supplier s  = supplierService.getOne(new LambdaQueryWrapperX<Supplier>().eq(Supplier::getSupplier,supplier));
                    supplierId = s.getId();
                    if (supplierId == null){
                        //供应商不存在
                        throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_SUPPLIER_NOT_DECIMAL_CODE,
                                String.format(ExceptionConstants.MATERIAL_SUPPLIER_NOT_DECIMAL_MSG, i+1));
                    }
                }
                String barCode = ExcelUtils.getContent(src, i, 20); //商品条码
                String depotName = ExcelUtils.getContent(src, i, 21); //仓库名称
                Long depotId = null;
                if(StringUtil.isNotEmpty(supplier)) {
                    //根据仓库名查询仓库id
                    depotId = depotMapperEx.selectByConditionDepot(depotName,null,null).get(0).getId();
                    if (depotId == null){
                        //仓库不存在
                        throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_DEPOT_NOT_DECIMAL_CODE,
                                String.format(ExceptionConstants.MATERIAL_DEPOT_NOT_DECIMAL_MSG, i+1));
                    }
                }
                String position = ExcelUtils.getContent(src, i, 22); //仓位货架
                String inventory = ExcelUtils.getContent(src,i,23);//库存
                String otherField1 = ExcelUtils.getContent(src, i, 24); //自定义1
                String otherField2 = ExcelUtils.getContent(src, i, 25); //自定义2
                String otherField3 = ExcelUtils.getContent(src, i, 26); //自定义3
                String remark = ExcelUtils.getContent(src, i, 27); //备注
                MaterialWithInitStock m = new MaterialWithInitStock();
                //设置商品名字、规格、型号、颜色、品牌、类型id
                m.setName(name);
                m.setStandard(standard);
                m.setModel(model);
                m.setColor(color);
                m.setBrand(brand);
                m.setCategoryId(categoryId);
                //通过名称生成助记码
                m.setMnemonic(PinYinUtil.getFirstLettersLo(name));
                //设置单位、备注、基础重量
                m.setUnit(unit);
                m.setRemark(remark);
                m.setWeight(new BigDecimal(weight));
                //设置商品是否启用
                m.setEnabled("1".equals(enabled));
                //设置商品是否开启序列号
                if(StringUtil.isNotEmpty(enableSerialNumber) && "1".equals(enableSerialNumber)) {
                    m.setEnableSerialNumber("1");
                } else {
                    m.setEnableSerialNumber("0");
                }
                //获取类型编码
                Long serial_no = categoryId == null ? null : materialCategoryService.getMaterialCategory(m.getCategoryId()).getSerialNo();
                //设置系统sku
                m.setSystemSku(serial_no + DateUtils.dateTimeNow() + RandomHelper.getRandomStr(6));
                m.setOtherField1(StringUtil.isNotEmpty(otherField1)?otherField1:null);
                m.setOtherField2(StringUtil.isNotEmpty(otherField2)?otherField2:null);
                m.setOtherField3(StringUtil.isNotEmpty(otherField3)?otherField3:null);
                m.setRemark(remark);
                //设置商品拓展属性
                JSONObject materialExObj = new JSONObject();
                JSONObject basicObj = new JSONObject();
                basicObj.put("commodityUnit", unit);    //商品单位
                basicObj.put("sku", sku);   //商品属性
                basicObj.put("purchaseDecimal", purchaseDecimal);   //采购价格
                basicObj.put("commodityDecimal", commodityDecimal); //零售价格
                basicObj.put("wholesaleDecimal", wholesaleDecimal); //销售价格
                basicObj.put("lowDecimal", lowDecimal); //最低售价
                basicObj.put("productionDate",productionDate); //生产日期
                basicObj.put("expiryNum",expiryNum);    //保质期天数
                basicObj.put("supplierId",supplierId); //供应商id
                basicObj.put("barCode", barCode); //商品条码
                basicObj.put("inventory",inventory); //库存
                basicObj.put("depotId",depotId);    //仓库id
                basicObj.put("position",position);  //仓位货架
                materialExObj.put("basic", basicObj);
                if(StringUtil.isNotEmpty(manyUnit) && StringUtil.isNotEmpty(ratio)){ //多单位
                    //校验比例是否是数字（含小数）
                    if(!StringUtil.isPositiveBigDecimal(ratio.trim())) {
                        throw new BusinessRunTimeException(ExceptionConstants.MATERIAL_RATIO_NOT_INTEGER_CODE,
                                String.format(ExceptionConstants.MATERIAL_RATIO_NOT_INTEGER_MSG, i+1));
                    }
                    Long unitId = unitService.getUnitIdByParam(unit, manyUnit, new BigDecimal(ratio.trim()));
                    if(unitId != null) {
                        m.setUnitId(unitId);
                        m.setUnit("");
                    }
//                    JSONObject otherObj = new JSONObject();
//                    otherObj.put("commodityUnit", manyUnit);
//                    otherObj.put("purchaseDecimal", parsePrice(purchaseDecimal,ratio));
//                    otherObj.put("commodityDecimal", parsePrice(commodityDecimal,ratio));
//                    otherObj.put("wholesaleDecimal", parsePrice(wholesaleDecimal,ratio));
//                    otherObj.put("lowDecimal", parsePrice(lowDecimal,ratio));
//                    materialExObj.put("other", otherObj);
                } else {
                    m.setUnit(unit);
                    m.setUnitId(null);
                }
                m.setMaterialExObj(materialExObj);
                //设置库存
                m.setStockMap(getStockMapCache(src, depotCount, depotMap, i));
                mList.add(m);
            }
            //处理表单信息，转为对象集合
            List<Material> materialList = parseMapByExcelData(mList);
            for (Material material : materialList) {
                //添加商品信息
                materialMapperEx.insertSelectiveEx(material);
                //添加商品子信息
                for (MaterialExtend materialExtend : material.getList()) {
                    //设置商品id
                    materialExtend.setMaterialId( material.getId());
                    materialExtendService.insertMaterialExtend(materialExtend);
                }
            }
            //添加日志
            logService.insertLog("商品",
                    new StringBuffer(BusinessConstants.LOG_OPERATION_TYPE_IMPORT).append(mList.size()).append(BusinessConstants.LOG_DATA_UNIT).toString(),
                    ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest());
            Long endTime = System.currentTimeMillis();
            logger.info("导入耗时：{}", endTime-beginTime);
            info.code = 200;
            info.data = "导入成功";
        } catch (BusinessRunTimeException e) {
            info.code = e.getCode();
            info.data = e.getData().get("message");
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            info.code = 500;
            info.data = "导入失败";
        }
        return info;
    }

    @Override
    public List<PDADepotItemVO> inventoryInquiry(PDAInventoryDTO pdaInventoryDTO) throws Exception {
        //查询类型id的子类型
        pdaInventoryDTO.setCategoryIds(selectCategoryIds(pdaInventoryDTO.getCategoryId()));
        return materialMapperEx.inventoryInquiry(pdaInventoryDTO);
    }

    /**
     * 查询库位树
     */
    @Override
    public List<PDATypeTree> selectPosition() {
        List<String> positions = materialExtendMapper.selectPosition();
        Map<String,List<String>> map = new HashMap<>();
        for (String s : positions) {
            String [] str = s.split("-");
            if (map.get(str[0]) == null){
                List<String> list = new ArrayList<>();
                map.put(str[0],list);
            }
            if (str.length > 2){
                map.get(str[0]).add(str[1]);
            }
        }
        List<PDATypeTree> typeTrees = new ArrayList<>();
        map.forEach((key,value) -> {
            PDATypeTree typeTree = new PDATypeTree();
            typeTree.setLabel(key);
            typeTree.setValue(key);
            List<PDATypeTree> children = new ArrayList<>();
            for (String s : value) {
                PDATypeTree childrenTree = new PDATypeTree();
                childrenTree.setLabel(key + "-" + s);
                childrenTree.setValue(key + "-" + s);
                children.add(childrenTree);
            }
            typeTree.setChildren(children);
            typeTrees.add(typeTree);
        });
        return typeTrees;
    }

    /**
     * 解析excel表格数据为商品对象
     */
    public List<Material> parseMapByExcelData(List<MaterialWithInitStock> mList){
        List<Material> materials = new ArrayList<>();
        Map<String,Material> materialMap = new HashMap<>();
        for (MaterialWithInitStock m : mList) {
            String materialSku = "";
            JSONObject materialExObj = m.getMaterialExObj();
            JSONObject basicObj = materialExObj.getJSONObject("basic");
            if(materialExObj!=null && materialExObj.get("basic")!=null) {
                if(basicObj!=null && materialExObj.get("sku")!=null) {
                    materialSku = basicObj.getString("sku");
                }
            }
            //名称，型号，品牌，规格，颜色，单位一样视为同一商品
            String str = m.getName()
                    +"-" + m.getModel()
                    + "-" + m.getStandard()
                    + "-" + m.getBrand()
                    + "-" + m.getColor()
                    + "-" + m.getUnit()
                    + "-" + materialSku;
            if (materialMap.get(str) == null) {
                //商品主表不存在，创建商品主表
                Material material = new Material();
                //名称
                material.setName(m.getName());
                //型号
                material.setModel(m.getModel());
                //规格
                material.setStandard(m.getStandard());
                //品牌
                material.setBrand(m.getBrand());
                //助记码
                material.setMnemonic(m.getMnemonic());
                //颜色
                material.setColor(m.getColor());
                //商品类别
                material.setCategoryId(m.getCategoryId());
                //单位-单个
                material.setUnit(m.getUnit());
                //计量单位Id
                material.setUnitId(m.getUnitId());
                //备注
                material.setRemark(m.getRemark());
                //基础重量
                material.setWeight(m.getWeight());
                //启用 0-禁用  1-启用
                material.setEnabled(m.getEnabled());
                //自定义1
                material.setOtherField1(m.getOtherField1());
                //自定义2
                material.setOtherField2(m.getOtherField2());
                //自定义3
                material.setOtherField3(m.getOtherField3());
                //是否开启序列号
                material.setEnableSerialNumber(m.getEnableSerialNumber());
                //系统sku
                material.setSystemSku(m.getSystemSku());
                List<MaterialExtend> list = new ArrayList<>();
                material.setList(list);
                materialMap.put(str,material);
            }
            //添加子表信息
            MaterialExtend materialExtend = new MaterialExtend();
            //商品单位
            materialExtend.setCommodityUnit(m.getUnit());
            //商品属性
            materialExtend.setSku(materialSku);
            //采购价格
            materialExtend.setPurchaseDecimal(basicObj.getBigDecimal("purchaseDecimal"));
            //零售价格
            materialExtend.setCommodityDecimal(basicObj.getBigDecimal("commodityDecimal"));
            //销售价格
            materialExtend.setWholesaleDecimal(basicObj.getBigDecimal("wholesaleDecimal"));
            //最低售价
            materialExtend.setLowDecimal(basicObj.getBigDecimal("lowDecimal"));
            //生产日期
            materialExtend.setProductionDate(basicObj.getDate("productionDate"));
            //保质期天数
            materialExtend.setExpiryNum(basicObj.getInteger("expiryNum"));
            //供应商id
            materialExtend.setSupplierId(basicObj.getLong("supplierId"));
            //商品条码
            materialExtend.setBarCode(basicObj.getString("barCode"));
            //设置批次号
            String batchNumber = DateUtils.dateTimeNow("yyyyMMdd") + RandomHelper.getRandomStr(6);
            materialExtend.setBatchNumber(batchNumber);
            //仓库id
            materialExtend.setDepotId(basicObj.getLong("depotId"));
            //仓位货架
            materialExtend.setPosition(basicObj.getString("position"));
            //库存
            materialExtend.setInventory(basicObj.getBigDecimal("inventory"));
            materialMap.get(str).getList().add(materialExtend);
        }
        materialMap.values().forEach(v -> materials.add(v));
        return materials;
    }

    public List<Long> selectCategoryIds(Long id) throws Exception {
        List<Long> longs = new ArrayList<>();
        longs.add(id);
        List<MaterialCategory> list = materialCategoryService.list(new LambdaQueryWrapperX<MaterialCategory>().eq(MaterialCategory::getParentId,id).eq(MaterialCategory::getDeleteFlag,"0"));
        for (MaterialCategory materialCategory : list) {
            longs.addAll(selectCategoryIds(materialCategory.getId()));
        }
        return longs;
    }
}