lemonBeans/ruoyi-shipping/src/main/java/com/ruoyi/shipping/service/impl/TCntrnoServiceImpl.java

package com.ruoyi.shipping.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.function.Function;
import java.util.function.Predicate;
import java.util.stream.Collectors;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.ruoyi.common.constant.UserConstants;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.domain.entity.SysDictData;
import com.ruoyi.common.core.domain.model.LoginUser;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.shipping.domain.*;
import com.ruoyi.shipping.excel.*;
import com.ruoyi.shipping.mapper.*;
import com.ruoyi.system.mapper.SysDictDataMapper;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.shipping.service.ITCntrnoService;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.multipart.MultipartFile;

import static com.ruoyi.common.utils.poi.Excel.filter;

/**
 * 集装箱号Service业务层处理
 *
 * @author ruoyi
 * @date 2021-03-22
 */
@Service
public class TCntrnoServiceImpl implements ITCntrnoService {
    @Autowired
    private TCntrnoMapper tCntrnoMapper;
    @Autowired
    private TCntrtrackingMapper tCntrtrackingMapper;
    @Autowired
    private TCntrMapper tCntrMapper;
    @Autowired
    private SysDictDataMapper sysDictDataMapper;
    @Autowired
    private TAddressMapper tAddressMapper;
    @Autowired
    private TConditionAccessoryMapper tConditionAccessoryMapper;

    /**
     * 查询集装箱号
     *
     * @param fId 集装箱号ID
     * @return 集装箱号
     */
    @Override
    public TCntrno selectTCntrnoById(Long fId) {
        return tCntrnoMapper.selectTCntrnoById(fId);
    }

    /**
     * 查询集装箱号列表
     *
     * @param tCntrno 集装箱号
     * @return 集装箱号
     */
    @Override
    public List<TCntrno> selectTCntrnoList(TCntrno tCntrno) {
        return tCntrnoMapper.selectTCntrnoList(tCntrno);
    }

    /**
     * 新增集装箱号
     *
     * @param tCntrno 集装箱号
     * @return 结果
     */
    @Override
    public int insertTCntrno(TCntrno tCntrno) {
        tCntrno.setCreateTime(DateUtils.getNowDate());
        tCntrno.setCreateBy(SecurityUtils.getUsername());
        tCntrno.setfUpdatetime(DateUtils.getNowDate());
        tCntrno.setfStatus("T");
        return tCntrnoMapper.insertTCntrno(tCntrno);
    }

    /**
     * 修改集装箱号
     *
     * @param tCntrno 集装箱号
     * @return 结果
     */
    @Override
    @Transactional
    public int updateTCntrno(List<TCntrno> tCntrno) {
        int update = 0;
        if (CollectionUtils.isNotEmpty(tCntrno)) {
            for (TCntrno cntrno : tCntrno) {
                String remark = cntrno.getRemark();
                cntrno.setUpdateTime(DateUtils.getNowDate());
                cntrno.setUpdateBy(SecurityUtils.getUsername());
                //最新时间
                if (cntrno.getFoldtime() == null) {
                    cntrno.setfUpdatetime(DateUtils.getNowDate());
                } else {
                    cntrno.setfUpdatetime(cntrno.getFoldtime());
                }
                //调入地点
                if (StringUtils.isNotEmpty(cntrno.getFoldSite())) {
                    cntrno.setfUpdateaddress(cntrno.getFoldSite());
                }
                //调入空重
                if (StringUtils.isNotEmpty(cntrno.getFoldFUpdateef())) {
                    cntrno.setfUpdateef(cntrno.getFoldFUpdateef());
                }
                //调入状态
                if (StringUtils.isNotEmpty(cntrno.getFoldFCntrstatus())) {
                    cntrno.setfCntrstatus(cntrno.getFoldFCntrstatus());
                }
                if (StringUtils.isNotEmpty(remark)) {
                    cntrno.setRemark(null);
                }
                //更改t_cntro表信息
                update = tCntrnoMapper.updateTCntrno((cntrno));
                //保存信息
                TCntrno cntrnoById = tCntrnoMapper.selectTCntrnoById(cntrno.getfId());
                if (cntrnoById.getfStatus().equals("T") || cntrnoById.getfStatus().equals("正常")) {
                    cntrnoById.setfStatus("T");
                } else {
                    cntrnoById.setfStatus("F");
                }
                if (StringUtils.isNotEmpty(cntrno.getfMblno())) {//提单号
                    cntrnoById.setfMblno(cntrno.getfMblno());
                }
                if (StringUtils.isNotEmpty(cntrno.getfVoy())) {//航次
                    cntrnoById.setfVoy(cntrno.getfVoy());
                }
                if (StringUtils.isNotEmpty(cntrno.getfVsl())) {//船名
                    cntrnoById.setfVsl(cntrno.getfVsl());
                }
                if (StringUtils.isNotEmpty(remark)) {
                    cntrnoById.setRemark(remark);
                }
                TCntrtracking setfield = setfield(cntrnoById);
                tCntrtrackingMapper.insertTCntrtracking(setfield);
                if (CollectionUtils.isNotEmpty(cntrno.getAccessoryList())) {
                    for (TConditionAccessory tConditionAccessory : cntrno.getAccessoryList()) {
                        tConditionAccessory.setfOrgId(10L);
                        tConditionAccessory.setfPid(setfield.getfId());
                        tConditionAccessory.setCreateBy(SecurityUtils.getUsername());
                        tConditionAccessory.setCreateTime(DateUtils.getNowDate());
                        tConditionAccessoryMapper.insertTConditionAccessory(tConditionAccessory);
                    }
                }
            }
        }
        return update;
    }

    @Override
    @Transactional
    public AjaxResult updateAirForceStation(List<TCntrno> tCntrno) {
        int update = 0;
        if (CollectionUtils.isNotEmpty(tCntrno)) {
            for (TCntrno cntrno : tCntrno) {
                String remark = cntrno.getRemark();
                cntrno.setUpdateTime(DateUtils.getNowDate());
                cntrno.setUpdateBy(SecurityUtils.getUsername());
                TCntrno cnTrNoByfNo = tCntrnoMapper.selectTCnTrNoByfNo(cntrno.getfNo());
                if (cnTrNoByfNo == null){
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return AjaxResult.error("箱号:"+cntrno.getfNo()+"系统中不存在请维护");
                }else {
                    cntrno.setfId(cnTrNoByfNo.getfId());
                    //更改t_cntro表信息
                    update = tCntrnoMapper.updateTCntrno((cntrno));
                }
                //保存历史信息
                TCntrno cntrnoById = tCntrnoMapper.selectTCntrnoById(cntrno.getfId());
                if (cntrnoById.getfStatus().equals("T") || cntrnoById.getfStatus().equals("正常")) {
                    cntrnoById.setfStatus("T");
                } else {
                    cntrnoById.setfStatus("F");
                }
                if (StringUtils.isNotEmpty(cntrno.getfMblno())) {//提单号
                    cntrnoById.setfMblno(cntrno.getfMblno());
                }
                if (StringUtils.isNotEmpty(cntrno.getfVoy())) {//航次
                    cntrnoById.setfVoy(cntrno.getfVoy());
                }
                if (StringUtils.isNotEmpty(cntrno.getfVsl())) {//船名
                    cntrnoById.setfVsl(cntrno.getfVsl());
                }
                if (StringUtils.isNotEmpty(remark)) {
                    cntrnoById.setRemark(remark);
                }
                TCntrtracking setfield = setfield(cntrnoById);
                update = tCntrtrackingMapper.insertTCntrtracking(setfield);
            }
        }
        if (update <= 0 ){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("保存场站信息失败");
        }
        return AjaxResult.success();
    }

    @Override
    @Transactional
    public int updateTCntrnoMessage(TCntrno tCntrno) {
        tCntrno.setUpdateTime(DateUtils.getNowDate());
        tCntrno.setUpdateBy(SecurityUtils.getUsername());
        tCntrno.setfUpdatetime(DateUtils.getNowDate());
        if (tCntrno.getfStatus().equals("T") || tCntrno.getfStatus().equals("正常")) {
            tCntrno.setfStatus("T");
        } else {
            tCntrno.setfStatus("F");
        }
        TCntrtracking setfield = setfield(tCntrno);
        tCntrtrackingMapper.insertTCntrtracking(setfield);
        //更改t_cntro表信息
        return tCntrnoMapper.updateTCntrno((tCntrno));

    }

    /**
     * 批量删除集装箱号
     *
     * @param fIds 需要删除的集装箱号ID
     * @return 结果
     */
    @Override
    public int deleteTCntrnoByIds(Long[] fIds) {
        return tCntrnoMapper.deleteTCntrnoByIds(fIds);
    }

    /**
     * 删除集装箱号信息
     *
     * @param fId 集装箱号ID
     * @return 结果
     */
    @Override
    public int deleteTCntrnoById(Long fId) {
        return tCntrnoMapper.deleteTCntrnoById(fId);
    }

    /**
     * 批量更新数据状态将正常状态改为停用
     *
     * @param fIds
     * @return
     */
    @Override
    public int updateTcntrnoStatus(Long[] fIds) {
        return tCntrnoMapper.updateTcntrnoStatus(fIds);
    }

    @Override
    public List<TCntrno> selectTcntrnoMessage(TCntrno tCntrno) {
        return tCntrnoMapper.selectTcntrnoMessage(tCntrno, null, null, null, null, null);
    }

    @Override
    public List<Map<String, Object>> getTCntrnoMessage(TCntrno tCntrno,List<String> list ) {
        List<Map<String, Object>> mapList = new ArrayList<>();
        StringBuffer stringBuffer = new StringBuffer();//查类型数据
        StringBuffer standBuffer = new StringBuffer();//查空重
        /*StringBuffer bareWeightBuffer = new StringBuffer();//查好坏*/
        String sql = null;//查类型数据
        String stand = null;//查空重
        /*String bareWeight = null;//查好坏*/
        if (CollectionUtils.isNotEmpty(list)) {
            for (String s : list) {
                stringBuffer.append("MAX( CASE WHEN tt.f_no = '" + s);
                stringBuffer.append("' THEN tt.typeidCount ELSE 0 END ) AS '" + s + "',");
                standBuffer.append("MAX( CASE WHEN a.typeName ='"+s+"' AND a.dict_label = '空'");
                standBuffer.append("THEN a.efCount ELSE 0 END ) AS '" + s + "空',");
                standBuffer.append("MAX( CASE WHEN a.typeName ='"+s+"' AND a.dict_label = '重'");
                standBuffer.append("THEN a.efCount ELSE 0 END ) AS '" + s + "重',");
                /*bareWeightBuffer.append("MAX( CASE WHEN b.typeName ='"+s+"' AND b.dict_label = '好'");
                bareWeightBuffer.append("THEN b.statusCount ELSE 0 END ) AS '" + s + "好',");
                bareWeightBuffer.append("MAX( CASE WHEN b.typeName ='"+s+"' AND b.dict_label = '坏'");
                bareWeightBuffer.append("THEN b.statusCount ELSE 0 END ) AS '" + s + "坏',");*/
            }
            sql = stringBuffer.subSequence(0, stringBuffer.length() - 1).toString();
            stand = standBuffer.subSequence(0, standBuffer.length() - 1).toString();
            /*bareWeight = bareWeightBuffer.subSequence(0, bareWeightBuffer.length() - 1).toString();*/
            mapList = tCntrnoMapper.getTCntrnoMessage(sql,stand,tCntrno);
        }
        return mapList;
    }

    @Override
    public List<TCntrno> getTCntrnoDistribute(TCntrno tCntrno) {
        //箱类型不为空查箱类型id
        String address = null;//地点
        String typeidName = null;//箱类型
        String cntrstatusName = null;//箱状态
        String updateEFName = null;//空重
        if (StringUtils.isNotEmpty(tCntrno.getAddressName())) {
            address = tCntrno.getAddressName();
        }else {
            tCntrno.setfUpdateaddress("0");
        }
        if (StringUtils.isNotEmpty(tCntrno.getTypeidName())) {
            typeidName = tCntrno.getTypeidName();
        }
        //箱状态不为空查箱状态
        if (StringUtils.isNotEmpty(tCntrno.getCntrstatusName())) {
            cntrstatusName = tCntrno.getCntrstatusName();
        }
        //箱空重部位空查空重
        if (StringUtils.isNotEmpty(tCntrno.getUpdateEFName())) {
            updateEFName = tCntrno.getUpdateEFName();
        }
        return tCntrnoMapper.selectTcntrnoMessage(tCntrno, address, typeidName, cntrstatusName, updateEFName, tCntrno.getCntrsize());
    }

    @Override
    public List<String> getTCntrnoChange(TCntrno tCntrno) {
        List<String> key = new ArrayList<>();
        key.add("地点");
        /*List<String> list = tCntrMapper.selectTCntrFNo();
        List<String> mapList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(list)){
            for (String s : list) {
                mapList.add(s);
                mapList.add(s+"空");
                mapList.add(s+"重");
               *//* mapList.add(s+"好");
                mapList.add(s+"坏");*//*
            }
        }
        list.addAll(mapList);
        Collections.sort(list);*/
        key.add("20GP");
        key.add("20HC");
        key.add("20GP空");
        key.add("20GP重");
        key.add("20HC空");
        key.add("20HC重");
        return key;
    }

    @Override
    public String selectTCntrnoFNo(TCntrno tCntrno) {
        TCntrno cntrno = tCntrnoMapper.selectTCntrnoFNo(tCntrno);
        if (StringUtils.isNotNull(cntrno) && !cntrno.getfId().equals(tCntrno.getfId())) {
            return UserConstants.NOT_UNIQUE;
        }
        return UserConstants.UNIQUE;
    }

    /**
     * 后续维护
     *
     * @param fileName 更新箱信息数据列表
     * @return
     */
    @Override
    public AjaxResult importUpdateDataTcntrnoExcel(MultipartFile fileName) {
        return null;
    }

    /**
     * 后续维护
     *
     * @param fileName 更新箱信息数据列表
     * @return
     */
    @Override
    public AjaxResult importTcntrnoDataExcel(MultipartFile fileName) {
        return null;
    }

    @Override
    public AjaxResult importCreateCompanyMsg(MultipartFile file) throws Exception {
        List<TCntrnoExcel> fleetCompanyMsgs = new ArrayList<>();

        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
            String originalFilename = file.getOriginalFilename();
            Workbook workbook = null;
            if (originalFilename.endsWith("xls")) {
                workbook = WorkbookFactory.create(inputStream);
            } else if (originalFilename.endsWith("xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
            Sheet sheet = workbook.getSheetAt(0);
            int line = 0;
            for (Row rows : sheet) {
                line++;
                if (rows.getRowNum() == 0) {
                    continue;
                }
                TCntrnoExcel companyMsg = new TCntrnoExcel();
                for (int a = 0; a < rows.getPhysicalNumberOfCells(); a++) {
                    Cell cell = rows.getCell(a);
                    if (cell == null){
                        continue;
                    }
                    String value = filter(sheet.getRow(0), CellReference.convertNumToColString(cell.getColumnIndex()));
                    switch (value) {
                        case "箱号":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                /*return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少箱号");*/
                                break;
                            }
                            companyMsg.setfNo(cell.getStringCellValue());
                            break;
                        case "箱类型":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                /*return AjaxResult.error("Excel箱信息数据第" + line + "箱类型");*/
                                break;
                            }
                            companyMsg.setTypeidName(cell.getStringCellValue());
                            if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                                Long fId = tCntrMapper.selectRcntrFId(companyMsg.getTypeidName());
                                companyMsg.setfTypeid(fId);
                            }
                            break;
                        case "最新地点":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                /*return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少最新地点");*/
                                break;
                            }
                            companyMsg.setAddressName((cell.getStringCellValue()));
                            break;
                        case "租赁公司":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                /*return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少货名");*/
                                break;
                            }
                            companyMsg.setOwnerName(cell.getStringCellValue());
                            if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                                String fPackageid = sysDictDataMapper.selectDictValue("f_owner", cell.getStringCellValue());
                                if (StringUtils.isNotEmpty(fPackageid)) {
                                    companyMsg.setfOwner(fPackageid);
                                }
                            }
                            break;
                        case "箱状态":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                /*return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少箱状态");*/
                                break;
                            }
                            companyMsg.setCntrstatusName(cell.getStringCellValue());
                            if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                                String setCntrstatusid = sysDictDataMapper.selectDictValue("f_cntrstatus", cell.getStringCellValue());
                                if (StringUtils.isNotEmpty(setCntrstatusid)) {
                                    companyMsg.setfCntrstatus(setCntrstatusid);
                                }
                            }
                            break;
                        case "空重":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                /*return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少箱型");*/
                                break;
                            }
                            companyMsg.setUpdateEFName(cell.getStringCellValue());
                            if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                                String setUpdateEFid = sysDictDataMapper.selectDictValue("f_updateEF", cell.getStringCellValue());
                                if (StringUtils.isNotEmpty(setUpdateEFid)) {
                                    companyMsg.setfUpdateef(setUpdateEFid);
                                }
                            }
                            break;
                        case "地点类型":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                /*return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少地点类型");*/
                                break;
                            }
                            companyMsg.setAddressType(cell.getStringCellValue());
                            if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                                String fPackageid = sysDictDataMapper.selectDictValue("f_types", cell.getStringCellValue());
                                if (StringUtils.isNotEmpty(fPackageid)) {
                                    companyMsg.setAddressTypeId(fPackageid);
                                }
                            }
                            break;
                        /*case "箱来源":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            companyMsg.setSourceName(cell.getStringCellValue());
                            if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                                String setSourceid = sysDictDataMapper.selectDictValue("f_source", cell.getStringCellValue());
                                if (StringUtils.isNotEmpty(setSourceid)) {
                                    companyMsg.setfSource(setSourceid);
                                }
                            }
                            break;
                        case "租赁方式":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            companyMsg.setRentName(cell.getStringCellValue());
                            if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                                String setRentid = sysDictDataMapper.selectDictValue("f_rent", cell.getStringCellValue());
                                if (StringUtils.isNotEmpty(setRentid)) {
                                    companyMsg.setfRent(setRentid);
                                }
                            }
                            break;
                        case "备注":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            companyMsg.setRemark(cell.getStringCellValue());
                            break;*/
                    }
                    if (StringUtils.isNotEmpty(companyMsg.getAddressName()) && StringUtils.isNotEmpty(companyMsg.getAddressTypeId())) {
                        TAddress tAddress = new TAddress();
                        tAddress.setfName(companyMsg.getAddressName());
                        tAddress.setfTypes(companyMsg.getAddressTypeId());
                        TAddress address = tAddressMapper.selectAddressFName(tAddress);
                        if (address != null){
                            companyMsg.setfUpdateaddress(address.getfId().toString());
                        }
                    }
                }
               if (StringUtils.isNotEmpty(companyMsg.getfNo())){
                   fleetCompanyMsgs.add(companyMsg);
               }
            }
            fleetCompanyMsgs = fleetCompanyMsgs.stream().filter(distinctByKey(TCntrnoExcel::getfNo)).collect(Collectors.toList());
        } catch (IOException e) {
            return AjaxResult.error("上传文件失败,请检查文件是否损坏");
        }
        return AjaxResult.success("success", fleetCompanyMsgs);
    }

    @Override
    @Transactional
    public AjaxResult batch(String tCntrno, LoginUser loginUser) {
        JSONArray jsonCrArray = JSONArray.parseArray(tCntrno);
        List<TCntrno> tCntrnoList = JSONObject.parseArray(jsonCrArray.toJSONString(), TCntrno.class);
        StringBuffer stringBuffer = new StringBuffer();
        if (CollectionUtils.isNotEmpty(tCntrnoList)) {
            for (TCntrno cntrno : tCntrnoList) {
                if (UserConstants.NOT_UNIQUE.equals(this.selectTCntrnoFNo(cntrno))) {
                    stringBuffer.append(cntrno.getfNo() + "编号已存在,");
                    continue;
                } else {
                    cntrno.setCreateTime(DateUtils.getNowDate());
                    cntrno.setCreateBy(SecurityUtils.getUsername());
                    cntrno.setfUpdatetime(DateUtils.getNowDate());
                    cntrno.setfStatus("T");
                    tCntrnoMapper.insertTCntrno(cntrno);
                }
            }
        }
        if (StringUtils.isNotEmpty(stringBuffer)){
            return AjaxResult.success("success",stringBuffer.toString());
        }else {
            return AjaxResult.success();
        }
    }

    @Override
    public List<Map<String, Object>> getTcntrnoFno(TCntrno tCntrno) {
        return tCntrnoMapper.getTcntrnoFno(tCntrno);
    }

    @Override
    public AjaxResult importAirForceStation(MultipartFile file) throws Exception{
        List<TCntrno> tCntrno = new ArrayList<>();
        InputStream inputStream = null;
            inputStream = file.getInputStream();
            String originalFilename = file.getOriginalFilename();
            Workbook workbook = null;
            if (originalFilename.endsWith("xls")){
                workbook = WorkbookFactory.create(inputStream);
            }else if (originalFilename.endsWith("xlsx")){
                workbook = new XSSFWorkbook(inputStream);
            }
            Sheet sheet = workbook.getSheetAt(0);
            int line = 0;
            for (Row rows : sheet) {
                line++;
                if (rows.getRowNum() == 0) {
                    continue;
                }
                TCntrno companyMsg = new TCntrno();
                for (int a = 0; a < rows.getPhysicalNumberOfCells(); a++) {
                    Cell cell = rows.getCell(a);
                    if (cell == null){
                        continue;
                    }
                    String value = filter(sheet.getRow(0), CellReference.convertNumToColString(cell.getColumnIndex()));
                    switch (value) {
                        case "箱动态":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            companyMsg.setOpctnstatusName(cell.getStringCellValue());
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少箱动态");
                            }
                            String fOpCTnsTaTus = sysDictDataMapper.selectDictValue("f_opctnstatus", cell.getStringCellValue());
                            if (StringUtils.isEmpty(fOpCTnsTaTus)){
                                return AjaxResult.error("系统缺少箱动态"+cell.getStringCellValue()+"请维护");
                            }
                            companyMsg.setfOpctnstatus(fOpCTnsTaTus);
                            break;
                        case "箱号":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少箱号");
                            }
                            companyMsg.setfNo(cell.getStringCellValue());
                            break;
                        case "空重":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少空重");
                            }
                            String fUpDaTeEf = sysDictDataMapper.selectDictValue("f_updateEF", cell.getStringCellValue());
                            if (StringUtils.isEmpty(fUpDaTeEf)){
                                return AjaxResult.error("系统缺少空重:"+cell.getStringCellValue()+"请维护");
                            }
                            companyMsg.setfUpdateef(fUpDaTeEf);
                            companyMsg.setUpdateEFName(cell.getStringCellValue());
                            break;
                        case "动态日期":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            String dataFormatString = cell.getStringCellValue();
                            if (StringUtils.isEmpty(dataFormatString)) {
                                return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少动态日期");
                            }
                            Date parse = null;
                            try {
                                parse = HSSFDateUtil.getJavaDate(Double.valueOf(dataFormatString));
                            }catch (Exception e){
                                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                parse = simpleDateFormat.parse(dataFormatString);
                            }
                            companyMsg.setfUpdatetime(parse);
                            break;
                        case "船名":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            /*if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少船名");
                            }*/
                            companyMsg.setfVsl(cell.getStringCellValue());
                            break;
                        case "航次":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            /*if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少航次");
                            }*/
                            companyMsg.setfVoy(cell.getStringCellValue());
                            break;
                        case "重量":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            /*if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少重量");
                            }*/
                            BigDecimal bigDecimal = new BigDecimal(cell.getStringCellValue());
                            companyMsg.setfBoxWeight(bigDecimal);
                            break;
                        case "地点类型 如: 堆场,码头":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少港口名称");
                            }
                            String fTypes = sysDictDataMapper.selectDictValue("f_types", cell.getStringCellValue());
                            if (StringUtils.isEmpty(fTypes)){
                                return AjaxResult.error("系统缺少地点类型"+cell.getStringCellValue()+"请维护");
                            }
                            companyMsg.setPort(fTypes);
                            companyMsg.setPortName(cell.getStringCellValue());
                            break;
                        case "堆放地点":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少堆放地点");
                            }
                            companyMsg.setAddressName(cell.getStringCellValue());
                            if (StringUtils.isEmpty(companyMsg.getPort())){
                                return AjaxResult.error("缺少地点类型"+companyMsg.getPortName());
                            }
                            TAddress aLong = tAddressMapper.selectTAddressByfNameType(cell.getStringCellValue(), companyMsg.getPort());
                            if (aLong == null){
                                return AjaxResult.error("系统缺少类型为"+companyMsg.getPortName()+"的地点"+cell.getStringCellValue()+"请维护");
                            }
                            companyMsg.setfUpdateaddress(aLong.getfId().toString());
                            break;
                        case "提单号":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            /*if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少提单号");
                            }*/
                            companyMsg.setfMblno(cell.getStringCellValue());
                            break;
                        case "铅封号":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            /*if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少铅封号");
                            }*/
                            companyMsg.setfSealno(cell.getStringCellValue());
                            break;
                        case "是否箱损 如:好,坏":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            companyMsg.setCntrstatusName(cell.getStringCellValue());
                            if (StringUtils.isEmpty(cell.getStringCellValue())) {
                                return AjaxResult.error("Excel箱信息数据第" + line + "行数据缺少箱损状态");
                            }
                            String fCnTrsTaTus = sysDictDataMapper.selectDictValue("f_cntrstatus", cell.getStringCellValue());
                            if (StringUtils.isEmpty(fCnTrsTaTus)){
                                return AjaxResult.error("系统缺少箱状态"+cell.getStringCellValue()+"请维护");
                            }
                            companyMsg.setfCntrstatus(fCnTrsTaTus);
                            break;
                        case "箱损描述(备注)":
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            companyMsg.setRemark(cell.getStringCellValue());
                            break;
                    }
                }
                tCntrno.add(companyMsg);
            }
        return AjaxResult.success("success", tCntrno);
    }

    @Override
    public List<BoxDistributionStatistics> boxDistributedStatistics() {
        List<BoxDistributionStatistics> list = new ArrayList<>();
        //先获取箱地址为港口信息
        List<TCntrno> boxPortMessage = tCntrnoMapper.getBoxPortMessage();
        //获取箱信息地址不为港口的信息
        List<TCntrno> addressMessageList = tCntrnoMapper.getBoxAddressMessage();
        //获取箱动态信息
        List<SysDictData> fOpcTnsTaTus = sysDictDataMapper.selectDictDataByType("f_opctnstatus");
        //获取箱空重状态
        List<SysDictData> fUpdateEF = sysDictDataMapper.selectDictDataByType("f_updateEF");
        //获取箱好坏状态
        List<SysDictData> fCnTrStatus = sysDictDataMapper.selectDictDataByType("f_cntrstatus");
        //在获取港口下的堆场对应的箱信息
        if (CollectionUtils.isNotEmpty(boxPortMessage) && CollectionUtils.isNotEmpty(fOpcTnsTaTus)){
            for (TCntrno cntrno : boxPortMessage) {
                BoxDistributionStatistics boxDistributionStatistics = new BoxDistributionStatistics();
                boxDistributionStatistics.setHaven(cntrno.getAddressName());
                List<BoxAddressMessage> boxAddressMessageList = new ArrayList<>();
                if (CollectionUtils.isNotEmpty(addressMessageList)){
                    for (TCntrno trno : addressMessageList) {
                        if (cntrno.getAddressName().equals(trno.getPortName()) && !trno.getAddressName().equals("在船")){
                            List<BoxMessage> boxMessageList = new ArrayList<>();
                            BoxAddressMessage boxAddressMessage = new BoxAddressMessage();
                            boxAddressMessage.setAddress(trno.getAddressName());
                            boxAddressMessage.setNumberSize(fOpcTnsTaTus.size()+fUpdateEF.size()+fCnTrStatus.size());
                            List<BoxMessage> opcTnsTaTus = this.getOpcTnsTaTus(fOpcTnsTaTus, trno);//获取调箱状态的箱新
                            List<BoxMessage> updateEF = this.getUpdateEF(fUpdateEF, trno);//获取空重的箱信息
                            List<BoxMessage> cnTrStatus = this.getCnTrStatus(fCnTrStatus, trno);//获取好坏的箱信息
                            if (CollectionUtils.isNotEmpty(opcTnsTaTus)){
                                boxMessageList.addAll(opcTnsTaTus);
                            }
                            if (CollectionUtils.isNotEmpty(updateEF)){
                                boxMessageList.addAll(updateEF);
                            }if (CollectionUtils.isNotEmpty(cnTrStatus)){
                                boxMessageList.addAll(cnTrStatus);
                            }
                            boxAddressMessage.setBoxMessages(boxMessageList);
                            boxAddressMessageList.add(boxAddressMessage);
                        }else if (cntrno.getAddressName().equals(trno.getPortName()) && trno.getAddressName().equals("在船")){
                            //获取在船的备注
                            List<TCntrno> boxRemark = tCntrnoMapper.getBoxRemark(trno.getfUpdateaddress());
                            if (CollectionUtils.isNotEmpty(boxRemark)){
                                for (TCntrno tCntrno : boxRemark) {
                                    List<BoxMessage> boxMessageList = new ArrayList<>();
                                    BoxAddressMessage boxAddressMessage = new BoxAddressMessage();
                                    boxAddressMessage.setAddress(trno.getAddressName()+tCntrno.getRemark());
                                    boxAddressMessage.setNumberSize(fOpcTnsTaTus.size()+fUpdateEF.size()+fCnTrStatus.size());
                                    List<BoxMessage> opcTnsTaTus = this.getOpcTnsTaTus(fOpcTnsTaTus, trno);//获取调箱状态的箱新
                                    List<BoxMessage> updateEF = this.getUpdateEF(fUpdateEF, trno);//获取空重的箱信息
                                    List<BoxMessage> cnTrStatus = this.getCnTrStatus(fCnTrStatus, trno);//获取好坏的箱信息
                                    if (CollectionUtils.isNotEmpty(opcTnsTaTus)){
                                        boxMessageList.addAll(opcTnsTaTus);
                                    }
                                    if (CollectionUtils.isNotEmpty(updateEF)){
                                        boxMessageList.addAll(updateEF);
                                    }if (CollectionUtils.isNotEmpty(cnTrStatus)){
                                        boxMessageList.addAll(cnTrStatus);
                                    }
                                    boxAddressMessage.setBoxMessages(boxMessageList);
                                    boxAddressMessageList.add(boxAddressMessage);
                                }
                            }
                        }
                    }
                }
                boxDistributionStatistics.setBoxAddressMessages(boxAddressMessageList);
                boxDistributionStatistics.setNumber(fOpcTnsTaTus.size()*boxAddressMessageList.size()+fUpdateEF.size()*boxAddressMessageList.size()+fCnTrStatus.size()*boxAddressMessageList.size());
                list.add(boxDistributionStatistics);
            }

        }
        return list;
    }

    @Override
    public List<Map<String, Object>> homeBox() {
        List<Map<String, Object>> list = tCntrnoMapper.homeBox();
        list.get(list.size() - 1).put("details", tCntrnoMapper.homeBoxCount());
        return list;
    }

    @Override
    @Transactional
    public int updateHire(TCntrno tCntrno) {
        return tCntrnoMapper.updateHire(tCntrno);
    }

    /**
     * set字段
     *
     * @param tCntrno
     * @return
     */
    public TCntrtracking setfield(TCntrno tCntrno) {
        TCntrtracking tCntrtracking = new TCntrtracking();
        BeanUtils.copyProperties(tCntrno, tCntrtracking);
        tCntrtracking.setfId(null);
        return tCntrtracking;
    }

    public List<String> GetMapKey(List<Map<String, Object>> listResult) {
        if ((listResult != null) && (!listResult.isEmpty())) {
            List listKey = new ArrayList();
            Map mapResult = (Map) listResult.get(0);
            Set mapKeySet = mapResult.keySet();
            String listHead = "";
            Iterator iteratorKey = mapKeySet.iterator();
            while (iteratorKey.hasNext()) {
                listHead = iteratorKey.next().toString();
                listKey.add(listHead);
            }
            return listKey;
        }
        return null;
    }
    private static <T> Predicate<T> distinctByKey(Function<? super T, Object> keyExtractor) {
        Map<Object, Boolean> seen = new ConcurrentHashMap<>();
        return t -> seen.putIfAbsent(keyExtractor.apply(t), Boolean.TRUE) == null;
    }

    /**
     * 获取调箱动态的箱信息
     * @param fOpcTnsTaTus
     * @param trno
     * @return
     */
    public List<BoxMessage> getOpcTnsTaTus(List<SysDictData> fOpcTnsTaTus,TCntrno trno){
        List<BoxMessage> boxMessageList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(fOpcTnsTaTus)){
            for (SysDictData opcTnsTaTus : fOpcTnsTaTus) {
                TCntrno tro = new TCntrno();
                BoxMessage boxMessage = new BoxMessage();
                tro.setfUpdateaddress(trno.getfUpdateaddress());
                tro.setfOpctnstatus(opcTnsTaTus.getDictValue());
                BoxMessage message = tCntrnoMapper.boxDistributionStatistics(tro);
                if (message != null){
                    boxMessage = message;
                }
                boxMessage.setOpcTnStatus(opcTnsTaTus.getDictLabel());
                boxMessage.setUNIT(boxMessage.getTWENTYFR() +boxMessage.getTWENTYGP() + boxMessage.getTWENTYOT()
                        +boxMessage.getTWENTYRF()+boxMessage.getTWENTYTK() +boxMessage.getFORTYFR()+boxMessage.getFORTYGP()
                        +boxMessage.getFORTYHC()+boxMessage.getFORTYHR()+boxMessage.getFORTYOT()+boxMessage.getFORTYTK());
                boxMessage.setTEU(boxMessage.getTWENTYFR() +boxMessage.getTWENTYGP() + boxMessage.getTWENTYOT()
                        +boxMessage.getTWENTYRF()+boxMessage.getTWENTYTK() +boxMessage.getFORTYFR()*2+boxMessage.getFORTYGP()*2
                        +boxMessage.getFORTYHC()*2+boxMessage.getFORTYHR()*2+boxMessage.getFORTYOT()*2+boxMessage.getFORTYTK()*2);
                boxMessageList.add(boxMessage);
            }
        }
        return boxMessageList;
    }
    public List<BoxMessage> getUpdateEF(List<SysDictData> fUpdateEF,TCntrno trno){
        List<BoxMessage> boxMessageList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(fUpdateEF)){
            for (SysDictData sysDictData : fUpdateEF) {
                TCntrno tro = new TCntrno();
                BoxMessage boxMessage = new BoxMessage();
                tro.setfUpdateaddress(trno.getfUpdateaddress());
                tro.setfUpdateef(sysDictData.getDictValue());
                BoxMessage message = tCntrnoMapper.boxDistributionStatistics(tro);
                if (message != null){
                    boxMessage = message;
                }
                boxMessage.setOpcTnStatus(sysDictData.getDictLabel());
                boxMessage.setUNIT(boxMessage.getTWENTYFR() +boxMessage.getTWENTYGP() + boxMessage.getTWENTYOT()
                        +boxMessage.getTWENTYRF()+boxMessage.getTWENTYTK() +boxMessage.getFORTYFR()+boxMessage.getFORTYGP()
                        +boxMessage.getFORTYHC()+boxMessage.getFORTYHR()+boxMessage.getFORTYOT()+boxMessage.getFORTYTK());
                boxMessage.setTEU(boxMessage.getTWENTYFR() +boxMessage.getTWENTYGP() + boxMessage.getTWENTYOT()
                        +boxMessage.getTWENTYRF()+boxMessage.getTWENTYTK() +boxMessage.getFORTYFR()*2+boxMessage.getFORTYGP()*2
                        +boxMessage.getFORTYHC()*2+boxMessage.getFORTYHR()*2+boxMessage.getFORTYOT()*2+boxMessage.getFORTYTK()*2);
                boxMessageList.add(boxMessage);
            }
        }
        return boxMessageList;
    }
    public List<BoxMessage> getCnTrStatus(List<SysDictData> fCnTrStatus,TCntrno trno){
        List<BoxMessage> boxMessageList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(fCnTrStatus)){
            for (SysDictData cnTrStatus : fCnTrStatus) {
                TCntrno tro = new TCntrno();
                BoxMessage boxMessage = new BoxMessage();
                tro.setfUpdateaddress(trno.getfUpdateaddress());
                tro.setfCntrstatus(cnTrStatus.getDictValue());
                BoxMessage message = tCntrnoMapper.boxDistributionStatistics(tro);
                if (message != null){
                    boxMessage = message;
                }
                boxMessage.setOpcTnStatus(cnTrStatus.getDictLabel());
                boxMessage.setUNIT(boxMessage.getTWENTYFR() +boxMessage.getTWENTYGP() + boxMessage.getTWENTYOT()
                        +boxMessage.getTWENTYRF()+boxMessage.getTWENTYTK() +boxMessage.getFORTYFR()+boxMessage.getFORTYGP()
                        +boxMessage.getFORTYHC()+boxMessage.getFORTYHR()+boxMessage.getFORTYOT()+boxMessage.getFORTYTK());
                boxMessage.setTEU(boxMessage.getTWENTYFR() +boxMessage.getTWENTYGP() + boxMessage.getTWENTYOT()
                        +boxMessage.getTWENTYRF()+boxMessage.getTWENTYTK() +boxMessage.getFORTYFR()*2+boxMessage.getFORTYGP()*2
                        +boxMessage.getFORTYHC()*2+boxMessage.getFORTYHR()*2+boxMessage.getFORTYOT()*2+boxMessage.getFORTYTK()*2);
                boxMessageList.add(boxMessage);
            }
        }
        return boxMessageList;
    }
}