package cn.estsh.i3plus.ext.mes.pcn.apiservice.serviceimpl.busi;

import cn.estsh.i3plus.ext.mes.pcn.api.busi.IMesShippingKanbanCfgService;
import cn.estsh.i3plus.ext.mes.pcn.pojo.model.CustQtyByTimeChartDataModel;
import cn.estsh.i3plus.ext.mes.pcn.pojo.model.MesShippingKanbanCfgModel;
import cn.estsh.i3plus.ext.mes.pcn.pojo.model.MesShippingKanbanModel;
import cn.estsh.i3plus.ext.mes.pcn.pojo.model.MesShippingKanbanViewModel;
import cn.estsh.i3plus.ext.mes.pcn.pojo.util.MesPcnExtConstWords;
import cn.estsh.i3plus.mes.pcn.util.DateUtil;
import cn.estsh.i3plus.platform.common.convert.ConvertBean;
import cn.estsh.i3plus.platform.common.tool.TimeTool;
import cn.estsh.i3plus.pojo.base.bean.DdlPackBean;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.base.tool.DdlPreparedPack;
import cn.estsh.i3plus.pojo.mes.bean.MesCustQtyByTime;
import cn.estsh.i3plus.pojo.mes.bean.MesPartShippingGroup;
import cn.estsh.i3plus.pojo.mes.bean.MesProductionRecord;
import cn.estsh.i3plus.pojo.mes.bean.seres.MesCimSeresJisVinOverPoint;
import cn.estsh.i3plus.pojo.mes.bean.shipping.MesShippingKanbanCfg;
import cn.estsh.i3plus.pojo.mes.bean.shipping.MesShippingKanbanCfgDetail;
import cn.estsh.i3plus.pojo.mes.repository.MesCustQtyByTimeRepository;
import cn.estsh.i3plus.pojo.mes.repository.MesPartShippingGroupRepository;
import cn.estsh.i3plus.pojo.mes.repository.MesShippingKanbanCfgDetailRepository;
import cn.estsh.i3plus.pojo.mes.repository.MesShippingKanbanCfgRepository;
import cn.estsh.i3plus.pojo.mes.repository.seres.IMesCimSeresJisVinOverPointRepository;
import cn.estsh.i3plus.pojo.mes.util.MesExtEnumUtil;
import cn.hutool.core.util.NumberUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class MesShippingKanbanCfgServiceImpl implements IMesShippingKanbanCfgService {
    @Autowired
    private MesShippingKanbanCfgRepository shippingKanbanCfgRDao;
    @Autowired
    private MesShippingKanbanCfgDetailRepository shippingKanbanCfgDetailRDao;
    @Autowired
    private MesPartShippingGroupRepository partShippingGroupRDao;
    @Autowired
    private IMesCimSeresJisVinOverPointRepository vinOverPointRDao;
    @Autowired
    private MesCustQtyByTimeRepository custQtyByTimeRDao;
    @Autowired
    private EntityManager entityManager;

    @Override
    public MesShippingKanbanCfgModel queryShippingKanbanCfg(String organizeCode, String shippingGroupCode) {
        MesShippingKanbanCfgModel model = new MesShippingKanbanCfgModel();
        if (StringUtils.isEmpty(shippingGroupCode)) {
            return model;
        }
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(organizeCode);
        DdlPreparedPack.getStringEqualPack(shippingGroupCode, "shippingGroupCode", packBean);
        MesShippingKanbanCfg kanbanCfg = shippingKanbanCfgRDao.getByProperty(packBean);
        if (kanbanCfg != null) {
            DdlPackBean detailPackBean = DdlPackBean.getDdlPackBean(organizeCode);
            DdlPreparedPack.getNumEqualPack(kanbanCfg.getId(), "configID", detailPackBean);
            List<MesShippingKanbanCfgDetail> details = shippingKanbanCfgDetailRDao.findByHqlWhere(detailPackBean);
            model.setDetails(details);
        } else {
            kanbanCfg = new MesShippingKanbanCfg();
            kanbanCfg.setShippingGroupCode(shippingGroupCode);
        }
        model.setConfig(kanbanCfg);

        if (model.getDetails() == null) {
            model.setDetails(new ArrayList<>());
        }
        Map<String, MesShippingKanbanCfgDetail> cfgDetailMap = model.getDetails().stream().collect(Collectors.toMap(MesShippingKanbanCfgDetail::getDetailCode, v -> v));
        for (MesExtEnumUtil.SHIPPING_KANBAN_DETAIL_INDEX index : MesExtEnumUtil.SHIPPING_KANBAN_DETAIL_INDEX.values()) {
            if (cfgDetailMap.containsKey(index.getValue())) {
                continue;
            }
            MesShippingKanbanCfgDetail detail = new MesShippingKanbanCfgDetail();
            detail.setConfigID(kanbanCfg.getId());
            detail.setDetailCode(index.getValue());
            detail.setDetailName(index.getDescription());
            detail.setIsShow(CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue());
            model.getDetails().add(detail);
        }

        return model;
    }

    @Override
    public void doSaveShippingKanbanCfg(MesShippingKanbanCfgModel request, String organizeCode, String username) {
        if (request.getConfig() == null) {
            return;
        }

        MesShippingKanbanCfg cfg = request.getConfig();
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(organizeCode);
        DdlPreparedPack.getStringEqualPack(cfg.getShippingGroupCode(), "shippingGroupCode", packBean);
        MesShippingKanbanCfg oldCfg = shippingKanbanCfgRDao.getByProperty(packBean);
        if (oldCfg == null) {
            cfg.setOrganizeCode(organizeCode);
            ConvertBean.serviceModelInitialize(cfg, username);
            oldCfg = shippingKanbanCfgRDao.insert(cfg);
        } else {
            oldCfg.setRefreshFrequency(cfg.getRefreshFrequency());
            oldCfg.setKanbanName(cfg.getKanbanName());
            oldCfg.setOnlinePoint(cfg.getOnlinePoint());
            oldCfg.setAdjustValue(cfg.getAdjustValue());
            oldCfg.setRefinedPoint(cfg.getRefinedPoint());
            oldCfg.setOnWayDate(cfg.getOnWayDate());
            oldCfg.setCustStartShift(cfg.getCustStartShift());
            oldCfg.setInterStartShift(cfg.getInterStartShift());
            oldCfg.setCustJph(cfg.getCustJph());
            oldCfg.setCarouselTime(cfg.getCarouselTime());
            ConvertBean.serviceModelUpdate(oldCfg, username);
            shippingKanbanCfgRDao.update(oldCfg);

            shippingKanbanCfgDetailRDao.deleteByProperties(new String[]{"organizeCode", "configID"},
                    new Object[]{organizeCode, oldCfg.getId()});
        }

        if (!CollectionUtils.isEmpty(request.getDetails())) {
            for (MesShippingKanbanCfgDetail detail : request.getDetails()) {
                detail.setId(null);
                detail.setConfigID(oldCfg.getId());
                detail.setOrganizeCode(oldCfg.getOrganizeCode());
                ConvertBean.serviceModelInitialize(detail, username);
                shippingKanbanCfgDetailRDao.insert(detail);
            }
        }
    }

    @Override
    public MesShippingKanbanModel queryShippingKanbanContext(String organizeCode, String shippingGroupCode) {
        MesShippingKanbanModel model = new MesShippingKanbanModel();

        Map<String, MesShippingKanbanCfgDetail> cfgDetailMap = null;
        MesShippingKanbanCfgModel cfgModel = queryShippingKanbanCfg(organizeCode, shippingGroupCode);
        MesShippingKanbanCfg cfg = cfgModel != null ? cfgModel.getConfig() : null;
        if (cfg == null) {
            return model;
        }
        StringBuilder kabanTitle = new StringBuilder();
        if (!StringUtils.isEmpty(cfg.getShippingGroupCode())) {
            for (String groupCode : cfg.getShippingGroupCode().split(",")) {
                DdlPackBean groupPackBean = DdlPackBean.getDdlPackBean(organizeCode);
                DdlPreparedPack.getStringEqualPack(groupCode, "shippingGroupCode", groupPackBean);
                MesPartShippingGroup shippingGroup = partShippingGroupRDao.getByProperty(groupPackBean);
                if (shippingGroup != null) {
                    if (kabanTitle.length() > 0) {
                        kabanTitle.append(" ");
                    }
                    kabanTitle.append(shippingGroup.getShippingGroupName());
                }
            }
        }
        if (kabanTitle.length() > 0) {
            kabanTitle.append("|");
        }
        kabanTitle.append("发运看板");
        model.setKanbanTitle(kabanTitle.toString());

        if (!CollectionUtils.isEmpty(cfgModel.getDetails())) {
            cfgDetailMap = cfgModel.getDetails().stream().collect(Collectors.toMap(MesShippingKanbanCfgDetail::getDetailCode, v -> v));
        }

        for (MesExtEnumUtil.SHIPPING_KANBAN_DETAIL_INDEX index : MesExtEnumUtil.SHIPPING_KANBAN_DETAIL_INDEX.values()) {
            MesShippingKanbanCfgDetail cfgDetail = cfgDetailMap != null ? cfgDetailMap.get(index.getValue()) : null;
            if (cfgDetail == null || cfgDetail.getIsShow() == null || cfgDetail.getIsShow() != CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue() || cfgDetail.getOrderNumber() == null) {
                continue;
            }

            MesShippingKanbanViewModel viewModel = new MesShippingKanbanViewModel();
            viewModel.setDetailName(index.getDescription());
            viewModel.setOrderNumber(cfgDetail.getOrderNumber());
            String rangDescription = "";
            if (!StringUtils.isEmpty(cfgDetail.getRangValueLess())) {
                rangDescription += "<" + cfgDetail.getRangValueLess();
            }
            if (!StringUtils.isEmpty(cfgDetail.getRangValueMore())) {
                if (StringUtils.isEmpty(rangDescription)) {
                    rangDescription += "&";
                }
                rangDescription += ">=" + cfgDetail.getRangValueMore();
            }
            viewModel.setRangDescription(rangDescription);
            List<String> values = null;
            switch (index) {
                case CLIENT_STOCK_QTY:
                    values = getClientStockQty(cfg);
                    break;
                case WAIT_SHIPPING_QTY:
                    values = getWaitShippingQty(cfg, cfgDetail);
                    break;
                case CP_WAIT_SHIPPING_QTY:
                    values = getCPWaitShippingQty(cfg, cfgDetail);
                    break;
                case WAIT_PRODUCT_QTY:
                    values = getWaitProductQty(cfg, cfgDetail);
                    break;
                case LAST_SHIPPING_TIME:
                    values = getLastShippingTime(cfg, cfgDetail);
                    break;
                case CP_LAST_SHIPPING_TIME:
                    values = getCPLastShippingTime(cfg, cfgDetail);
                    break;
                case NEXT_SHIPPING_TIME:
                    values = getNextShippingTime(cfg, cfgDetail);
                    break;
                case TREE_STOP_WARNING:
                    values = getTreeStopWarning(cfg, cfgDetail);
                    break;
                case SHIPPING_VIN:
                    values = getShippingVin(cfg, cfgDetail);
                    break;
                case CP_SHIPPING_VIN:
                    values = getCPShippingVin(cfg, cfgDetail);
                    break;
                case TODAY_ONLINE:
                    values = getTodayOnline(cfg, cfgDetail);
                    break;
                case TODAY_SHIPPING:
                    values = getTodayShipping(cfg, cfgDetail);
                    break;
                case TODAY_CAR:
                    values = getTodayCar(cfg, cfgDetail);
                    break;
                case CLIENT_JPH:
                    values = getClientJPH(cfg, cfgDetail);
                    break;
                case PRODUCT_JPH:
                    values = getProductJPH(cfg, cfgDetail);
                    break;
            }
            if (values == null) {
                values = new ArrayList<>();
            }
            viewModel.setValues(values);
            viewModel.setColor(getColor(cfgDetail, values));
            model.getDetails().add(viewModel);
        }
        model.getDetails().sort(Comparator.comparing(MesShippingKanbanViewModel::getOrderNumber));
        return model;
    }

    /**
     * 定时根据发运组获取客户端库存
     * @param cfg 发运组配置参数
     * @param shippingGroupCode 发运组编码
     * @return 返回客户端库存数据
     */
    private int getClientStockQtyForJob(MesShippingKanbanCfg cfg, String shippingGroupCode) {
        if (StringUtils.isEmpty(shippingGroupCode)) {
            return 0;
        }

        // 获取赛力斯上线的最新流水号
        String onNumber = "";
        if (!StringUtils.isEmpty(cfg.getOnlinePoint())) {
            StringBuilder onNumberHql = new StringBuilder();
            onNumberHql.append("select max(serialNumber) from ").append(MesCimSeresJisVinOverPoint.class.getName());
            onNumberHql.append(" where organizeCode = :organizeCode and  isDeleted = :isDeleted and isValid = :isValid ");
            onNumberHql.append(" and overPoint = :overPoint ");
            try {
                onNumber = entityManager.createQuery(onNumberHql.toString(), String.class)
                        .setParameter("organizeCode", cfg.getOrganizeCode())
                        .setParameter("isValid", CommonEnumUtil.VALID)
                        .setParameter("isDeleted", CommonEnumUtil.FALSE)
                        .setParameter("overPoint", cfg.getOnlinePoint())
                        .getSingleResult();
            } catch (NoResultException ignored) {
            }
        }
        if (StringUtils.isEmpty(onNumber)) {
            onNumber = "0";
        }

        List<String> strShippingGroupList = new ArrayList<>();
        strShippingGroupList.add(shippingGroupCode);
        String offNumber = getMaxOffNumber(cfg.getOrganizeCode(), strShippingGroupList);
        if (StringUtils.isEmpty(offNumber)) {
            offNumber = "0";
        }

        int adjustValue = cfg.getAdjustValue() != null ? cfg.getAdjustValue() : 0;
        return Integer.parseInt(offNumber) - Integer.parseInt(onNumber) + adjustValue;
    }

    @Override
    public List<CustQtyByTimeChartDataModel> queryCustQtyByTimeChartData(String organizeCode, String shippingGroupCode) {
        List<CustQtyByTimeChartDataModel> result = new ArrayList<>();
        if (StringUtils.isEmpty(organizeCode) || StringUtils.isEmpty(shippingGroupCode)) {
            return result;
        }
        for (String groupCode : shippingGroupCode.split(",")) {
            Date endTime = DateUtil.now();
            Date startTime = DateUtil.addDays(endTime, -1);
            DdlPackBean packBean = DdlPackBean.getDdlPackBean(organizeCode);
            DdlPreparedPack.getStringEqualPack(groupCode, "shippingGroupCode", packBean);
            DdlPreparedPack.getStringBiggerPack(DateUtil.formatDate(startTime), "createDatetime", packBean);
            DdlPreparedPack.getStringSmallerPack(DateUtil.formatDate(endTime), "createDatetime", packBean);
            DdlPreparedPack.getOrderBy("createDatetime", CommonEnumUtil.ASC_OR_DESC.ASC.getValue(), packBean);
            List<MesCustQtyByTime> custQtyByTimes = custQtyByTimeRDao.findByHqlWhere(packBean);
            if (CollectionUtils.isEmpty(custQtyByTimes)) {
                continue;
            }

            CustQtyByTimeChartDataModel model = new CustQtyByTimeChartDataModel();
            model.setTitle(groupCode);
            for (MesCustQtyByTime byTime : custQtyByTimes) {
                Date createDate = DateUtil.parse(byTime.getCreateDatetime());
                model.getXAxis().add(TimeTool.getDay(createDate) + "日"  + TimeTool.getHour(createDate) + "时");
                model.getSeries().add(byTime.getCustQty());
            }
            result.add(model);
        }

        return result;
    }

    @Override
    public void doCreateCustQtyByTime(String organizeCode, String username) {
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(organizeCode);
        Set<String> handleShippingGroup = new HashSet<>();
        List<MesShippingKanbanCfg> kanbanCfg = shippingKanbanCfgRDao.findByHqlWhere(packBean);
        for (MesShippingKanbanCfg cfg : kanbanCfg) {
            if (StringUtils.isEmpty(cfg.getShippingGroupCode())) {
                continue;
            }
            for (String shippingGroupCode : cfg.getShippingGroupCode().split(",")) {
                if (handleShippingGroup.contains(shippingGroupCode)) {
                    continue;
                }
                handleShippingGroup.add(shippingGroupCode);

                int custQty = getClientStockQtyForJob(cfg, shippingGroupCode);
                MesCustQtyByTime byTime = new MesCustQtyByTime();
                byTime.setShippingGroupCode(shippingGroupCode);
                byTime.setCustQty(custQty);
                byTime.setOrganizeCode(organizeCode);
                ConvertBean.serviceModelInitialize(byTime, username);
                custQtyByTimeRDao.insert(byTime);
            }
        }
    }

    private String getColor(MesShippingKanbanCfgDetail cfgDetail, List<String> values) {
        if (CollectionUtils.isEmpty(values)) {
            return cfgDetail.getDefaultColor();
        }
        if (!StringUtils.isEmpty(cfgDetail.getRangValueLess())) {
            String rangValue = cfgDetail.getRangValueLess();
            for (String value : values) {
                if (StringUtils.isEmpty(value)) {
                    continue;
                }
                if (NumberUtil.isNumber(value)) {
                    if (Long.parseLong(value) < Long.parseLong(rangValue)) {
                        return cfgDetail.getRangColorLess();
                    }
                } else {
                    if (value.compareTo(rangValue) < 0) {
                        return cfgDetail.getRangColorLess();
                    }
                }
            }
        }

        if (!StringUtils.isEmpty(cfgDetail.getRangValueMore())) {
            String rangValue = cfgDetail.getRangValueMore();
            for (String value : values) {
                if (StringUtils.isEmpty(value)) {
                    continue;
                }
                if (NumberUtil.isNumber(value)) {
                    if (Long.parseLong(value) >= Long.parseLong(rangValue)) {
                        return cfgDetail.getRangColorMore();
                    }
                } else {
                    if (value.compareTo(rangValue) >= 0) {
                        return cfgDetail.getRangColorMore();
                    }
                }
            }
        }

        return cfgDetail.getDefaultColor();
    }

    private List<MesPartShippingGroup> getShippingGroups(MesShippingKanbanCfg cfg) {
        List<MesPartShippingGroup> shippingGroups = new ArrayList<>();
        if (StringUtils.isEmpty(cfg.getShippingGroupCode())) {
            return shippingGroups;
        }
        List<String> strShippingGroupList = Arrays.asList(cfg.getShippingGroupCode().split(","));
        if (CollectionUtils.isEmpty(strShippingGroupList)) {
            return shippingGroups;
        }
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(cfg.getOrganizeCode());
        DdlPreparedPack.getInPackList(strShippingGroupList, "shippingGroupCode", packBean);
        return partShippingGroupRDao.findByHqlWhere(packBean);
    }

    private String getMesShippingVin(String organizeCode, List<String> shippingGroupCodes, List<Integer> status) {
        StringBuilder hql = new StringBuilder();
        hql.append("select sd.vin");
        hql.append(" from MesShippingOrderManagement s inner join MesShippingOrderManagementDetail sd on s.id = sd.pid and sd.organizeCode = :organizeCode and sd.isDeleted=:isDeleted and sd.isValid=:isValid ");
        hql.append(" and s.status in (:status) ");
        if (shippingGroupCodes.size() == 1) {
            hql.append(" and s.shippingGroupCode = :shippingGroupCode ");
        } else {
            hql.append(" and s.shippingGroupCode in (:shippingGroupCode) ");
        }
        hql.append(" ORDER BY sd.id desc");
        try {
            Query query = entityManager.createQuery(hql.toString(), String.class);
            query.setParameter("organizeCode", organizeCode);
            query.setParameter("isValid", CommonEnumUtil.VALID);
            query.setParameter("isDeleted", CommonEnumUtil.FALSE);
            query.setParameter("status", status);
            if (shippingGroupCodes.size() == 1) {
                query.setParameter("shippingGroupCode", shippingGroupCodes.get(0));
            } else {
                query.setParameter("shippingGroupCode", shippingGroupCodes);
            }
            query.setMaxResults(1);
            return query.getSingleResult().toString();
        } catch (NoResultException e) {
            return "";
        }
    }

    /**
     * 最新下线的排序发运单客户流水号
     * @param organizeCode
     * @param shippingGroupCodes
     * @return
     */
    private String getMaxOffNumber(String organizeCode, List<String> shippingGroupCodes) {
        String vinCode = getMesShippingVin(organizeCode, shippingGroupCodes, Arrays.asList(MesExtEnumUtil.SHIPPING_ORDER_STATUS.SHIPPED.getValue(), MesExtEnumUtil.SHIPPING_ORDER_STATUS.LOADING.getValue()));
        if (StringUtils.isEmpty(vinCode)) {
            return "";
        }
        StringBuilder offNumberHql = new StringBuilder();
        offNumberHql.append("select max(serialNumber) from ").append(MesCimSeresJisVinOverPoint.class.getName());
        offNumberHql.append(" where organizeCode = :organizeCode and  isDeleted = :isDeleted and isValid = :isValid ");
        offNumberHql.append(" and vin = :vin ");
        try {
            return entityManager.createQuery(offNumberHql.toString(), String.class)
                    .setParameter("organizeCode", organizeCode)
                    .setParameter("isValid", CommonEnumUtil.VALID)
                    .setParameter("isDeleted", CommonEnumUtil.FALSE)
                    .setParameter("vin", vinCode)
                    .getSingleResult();
        } catch (NoResultException e) {
            return "";
        }
    }

    /**
     * 客户端库存
     * 最新下线的排序发运单客户流水号（SERIAL_NUMBER）- 赛力斯上线的最新流水号+调整值。
     * @param cfg
     * @return
     */
    private List<String> getClientStockQty(MesShippingKanbanCfg cfg) {
        List<String> values = new ArrayList<>();
        List<String> strShippingGroupList = Arrays.asList(cfg.getShippingGroupCode().split(","));
        if (CollectionUtils.isEmpty(strShippingGroupList)) {
            values.add("0");
            return values;
        }

        // 获取赛力斯上线的最新流水号
        String onNumber = "";
        if (!StringUtils.isEmpty(cfg.getOnlinePoint())) {
            StringBuilder onNumberHql = new StringBuilder();
            onNumberHql.append("select max(serialNumber) from ").append(MesCimSeresJisVinOverPoint.class.getName());
            onNumberHql.append(" where organizeCode = :organizeCode and  isDeleted = :isDeleted and isValid = :isValid ");
            onNumberHql.append(" and overPoint = :overPoint ");
            try {
                onNumber = entityManager.createQuery(onNumberHql.toString(), String.class)
                        .setParameter("organizeCode", cfg.getOrganizeCode())
                        .setParameter("isValid", CommonEnumUtil.VALID)
                        .setParameter("isDeleted", CommonEnumUtil.FALSE)
                        .setParameter("overPoint", cfg.getOnlinePoint())
                        .getSingleResult();
            } catch (NoResultException ignored) {
            }
        }
        if (StringUtils.isEmpty(onNumber)) {
            onNumber = "0";
        }

        String offNumber = getMaxOffNumber(cfg.getOrganizeCode(), strShippingGroupList);
        if (StringUtils.isEmpty(offNumber)) {
            offNumber = "0";
        }

        int adjustValue = cfg.getAdjustValue() != null ? cfg.getAdjustValue() : 0;
        int result = Integer.parseInt(offNumber) - Integer.parseInt(onNumber) + adjustValue;
        values.add(String.valueOf(result));
        return values;
    }

    /**
     * 待发运数量
     * 排序单装箱扫描流水号-发运扫描流水号，精排最新流水号-辊道线下件流水号（cockpit）
     * @param cfg
     * @param cfgDetail
     * @return
     */
    private List<String> getWaitShippingQty(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        List<String> strShippingGroupList = Arrays.asList(cfg.getShippingGroupCode().split(","));
        if (CollectionUtils.isEmpty(strShippingGroupList)) {
            values.add("0");
            return values;
        }

        if (strShippingGroupList.stream().anyMatch(s -> s.contains("CK"))) {
            // 获获取精排信息点的流水号
            String refinedPointNumber = "";
            if (!StringUtils.isEmpty(cfg.getRefinedPoint())) {
                StringBuilder refinedPointHql = new StringBuilder();
                refinedPointHql.append("select max(serialNumber) from " + MesCimSeresJisVinOverPoint.class.getName());
                refinedPointHql.append(" where organizeCode = :organizeCode and  isDeleted = :isDeleted and isValid = :isValid ");
                refinedPointHql.append(" and overPoint = :overPoint ");
                try {
                    refinedPointNumber = entityManager.createQuery(refinedPointHql.toString(), String.class)
                            .setParameter("organizeCode", cfg.getOrganizeCode())
                            .setParameter("isValid", CommonEnumUtil.VALID)
                            .setParameter("isDeleted", CommonEnumUtil.FALSE)
                            .setParameter("overPoint", cfg.getRefinedPoint())
                            .getSingleResult();
                } catch (NoResultException ignored) {
                }
            }
            if (StringUtils.isEmpty(refinedPointNumber)) {
                refinedPointNumber = "0";
            }

            String offNumber = getMaxOffNumber(cfg.getOrganizeCode(), strShippingGroupList);
            if (StringUtils.isEmpty(offNumber)) {
                offNumber = "0";
            }

            int result = Integer.parseInt(refinedPointNumber) - Integer.parseInt(offNumber);
            values.add(String.valueOf(result));
        } else {
            StringBuilder hql = new StringBuilder();
            hql.append("select new Map(s.shippingGroupCode as shippingGroupCode, COUNT(sd.id) as COUNT)");
            hql.append(" from MesShippingOrderManagement s inner join MesShippingOrderManagementDetail sd on s.id = sd.pid and sd.organizeCode = :organizeCode and sd.isDeleted=:isDeleted and sd.isValid=:isValid ");
            hql.append(" and s.status = :status ");
            hql.append(" and s.shippingGroupCode in (:shippingGroupCode) ");
            hql.append(" group by s.shippingGroupCode");
            List<Map> ddd = entityManager.createQuery(hql.toString())
                    .setParameter("organizeCode", cfg.getOrganizeCode())
                    .setParameter("isValid", CommonEnumUtil.VALID)
                    .setParameter("isDeleted", CommonEnumUtil.FALSE)
                    .setParameter("status", MesExtEnumUtil.SHIPPING_ORDER_STATUS.SHIPPED.getValue())
                    .setParameter("shippingGroupCode", strShippingGroupList)
                    .getResultList();
            Map<String, String> shippingGroupCount = new HashMap<>();
            if (!CollectionUtils.isEmpty(ddd)) {
                for (Map dd : ddd) {
                    Object count = dd.get("COUNT");
                    if (count != null) {
                        shippingGroupCount.put(dd.get("shippingGroupCode").toString(), count.toString());
                    }
                }
            }
            for (String shippingGroupCode : strShippingGroupList) {
                values.add(shippingGroupCount.getOrDefault(shippingGroupCode, "0"));
            }
        }
        return values;
    }

    private List<String> getCPWaitShippingQty(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        List<String> strShippingGroupList = Arrays.asList(cfg.getShippingGroupCode().split(","));
        if (CollectionUtils.isEmpty(strShippingGroupList)) {
            values.add("0");
            return values;
        }

        if (strShippingGroupList.stream().anyMatch(s -> s.contains("CK"))) {
            // 获获取精排信息点的流水号
            String refinedPointNumber = "";
            if (!StringUtils.isEmpty(cfg.getRefinedPoint())) {
                StringBuilder refinedPointHql = new StringBuilder();
                refinedPointHql.append("select max(serialNumber) from " + MesCimSeresJisVinOverPoint.class.getName());
                refinedPointHql.append(" where organizeCode = :organizeCode and  isDeleted = :isDeleted and isValid = :isValid ");
                refinedPointHql.append(" and overPoint = :overPoint ");
                try {
                    refinedPointNumber = entityManager.createQuery(refinedPointHql.toString(), String.class)
                            .setParameter("organizeCode", cfg.getOrganizeCode())
                            .setParameter("isValid", CommonEnumUtil.VALID)
                            .setParameter("isDeleted", CommonEnumUtil.FALSE)
                            .setParameter("overPoint", cfg.getRefinedPoint())
                            .getSingleResult();
                } catch (NoResultException ignored) {
                }
            }
            if (StringUtils.isEmpty(refinedPointNumber)) {
                refinedPointNumber = "0";
            }

            strShippingGroupList = strShippingGroupList.stream().filter(s -> s.contains("CK")).collect(Collectors.toList());
            String offNumber = getMaxOffNumber(cfg.getOrganizeCode(), strShippingGroupList);
            if (StringUtils.isEmpty(offNumber)) {
                offNumber = "0";
            }

            int result = Integer.parseInt(refinedPointNumber) - Integer.parseInt(offNumber);
            values.add(String.valueOf(result));
        } else {
            values.add("0");
        }
        return values;
    }

    /**
     * 待生产/装箱数量
     * 精排最新流水号-排序单装箱扫描流水号
     * @param cfg
     * @param cfgDetail
     * @return
     */
    private List<String> getWaitProductQty(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        List<String> strShippingGroupList = Arrays.asList(cfg.getShippingGroupCode().split(","));
        if (CollectionUtils.isEmpty(strShippingGroupList)) {
            values.add("0");
            return values;
        }

        if (strShippingGroupList.stream().anyMatch(s -> s.contains("CK"))) {
            values.add("0");
        } else {
            // 获获取精排信息点的流水号
            String refinedPointNumber = "";
            if (!StringUtils.isEmpty(cfg.getRefinedPoint())) {
                StringBuilder refinedPointHql = new StringBuilder();
                refinedPointHql.append("select max(serialNumber) from " + MesCimSeresJisVinOverPoint.class.getName());
                refinedPointHql.append(" where organizeCode = :organizeCode and  isDeleted = :isDeleted and isValid = :isValid ");
                refinedPointHql.append(" and overPoint = :overPoint ");
                try {
                    refinedPointNumber = entityManager.createQuery(refinedPointHql.toString(), String.class)
                            .setParameter("organizeCode", cfg.getOrganizeCode())
                            .setParameter("isValid", CommonEnumUtil.VALID)
                            .setParameter("isDeleted", CommonEnumUtil.FALSE)
                            .setParameter("overPoint", cfg.getRefinedPoint())
                            .getSingleResult();
                } catch (NoResultException ignored) {
                }
            }
            if (StringUtils.isEmpty(refinedPointNumber)) {
                refinedPointNumber = "0";
            }

            StringBuilder hql = new StringBuilder();
            hql.append("select new Map(s.shippingGroupCode as shippingGroupCode, max(sd.custInfoSeq) as custInfoSeq)");
            hql.append(" from MesShippingOrderManagement s inner join MesShippingOrderManagementDetail sd on s.id = sd.pid and sd.organizeCode = :organizeCode and sd.isDeleted=:isDeleted and sd.isValid=:isValid ");
            hql.append(" and s.status in (:status) ");
            hql.append(" and s.shippingGroupCode in (:shippingGroupCode) ");
            hql.append(" group by s.shippingGroupCode");
            List<Map> ddd = entityManager.createQuery(hql.toString())
                    .setParameter("organizeCode", cfg.getOrganizeCode())
                    .setParameter("isValid", CommonEnumUtil.VALID)
                    .setParameter("isDeleted", CommonEnumUtil.FALSE)
                    .setParameter("status", Arrays.asList(MesExtEnumUtil.SHIPPING_ORDER_STATUS.SHIPPED.getValue(), MesExtEnumUtil.SHIPPING_ORDER_STATUS.LOADING.getValue()))
                    .setParameter("shippingGroupCode", strShippingGroupList)
                    .getResultList();
            Map<String, String> shippingGroupCount = new HashMap<>();
            if (!CollectionUtils.isEmpty(ddd)) {
                for (Map dd : ddd) {
                    Object custInfoSeq = dd.get("custInfoSeq");
                    if (custInfoSeq != null) {
                        shippingGroupCount.put(dd.get("shippingGroupCode").toString(), custInfoSeq.toString());
                    }
                }
            }
            for (String shippingGroupCode : strShippingGroupList) {
                if (shippingGroupCount.containsKey(shippingGroupCode)) {
                    values.add(Long.parseLong(refinedPointNumber) - Long.parseLong(shippingGroupCount.get(shippingGroupCode)) + "");
                } else {
                    values.add("0");
                }
            }
        }
        return values;
    }

    /**
     * 距离上一次发运时间(Min)
     * 当前时间-最近一次发运扫描时间
     * @param cfg
     * @param cfgDetail
     * @return
     */
    private List<String> getLastShippingTime(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        List<String> strShippingGroupList = Arrays.asList(cfg.getShippingGroupCode().split(","));
        if (CollectionUtils.isEmpty(strShippingGroupList)) {
            return values;
        }

        if (strShippingGroupList.stream().anyMatch(s -> s.contains("CK"))) {
            StringBuilder hql = new StringBuilder();
            hql.append("select max(s.endScanTime)");
            hql.append(" from MesShippingOrderManagement s inner join MesShippingOrderManagementDetail sd on s.id = sd.pid and sd.organizeCode = :organizeCode and sd.isDeleted=:isDeleted and sd.isValid=:isValid ");
            hql.append(" and s.status in (:status) ");
            hql.append(" and s.shippingGroupCode in (:shippingGroupCode) ");
            String strScanEndTime = "";
            try {
                strScanEndTime = entityManager.createQuery(hql.toString(), String.class)
                        .setParameter("organizeCode", cfg.getOrganizeCode())
                        .setParameter("isValid", CommonEnumUtil.VALID)
                        .setParameter("isDeleted", CommonEnumUtil.FALSE)
                        .setParameter("status", Arrays.asList(MesExtEnumUtil.SHIPPING_ORDER_STATUS.SHIPPED.getValue(), MesExtEnumUtil.SHIPPING_ORDER_STATUS.LOADING.getValue()))
                        .setParameter("shippingGroupCode", strShippingGroupList)
                        .getSingleResult();
            } catch (NoResultException ignored) {
            }

            if (!StringUtils.isEmpty(strScanEndTime)) {
                Date scanEndTime = DateUtil.parse(strScanEndTime);
                values.add(DateUtil.formatDate(DateUtil.SHORT_FORMAT_HOUR, scanEndTime));
            }
        } else {
            StringBuilder hql = new StringBuilder();
            hql.append("select new Map(s.shippingGroupCode as shippingGroupCode, max(l.endScanTime) as endScanTime)");
            hql.append(" from MesLoadingList l inner join MesLoadingListDetail ld on l.id = ld.pid inner join MesShippingOrderManagement s on s.id = ld.shippingId and l.organizeCode = :organizeCode and l.isDeleted=:isDeleted and l.isValid=:isValid ");
            hql.append(" and l.status in (:status) ");
            hql.append(" and s.shippingGroupCode in (:shippingGroupCode) ");
            hql.append(" group by s.shippingGroupCode");
            List<Map> ddd = entityManager.createQuery(hql.toString())
                    .setParameter("organizeCode", cfg.getOrganizeCode())
                    .setParameter("isValid", CommonEnumUtil.VALID)
                    .setParameter("isDeleted", CommonEnumUtil.FALSE)
                    .setParameter("status", Arrays.asList(MesExtEnumUtil.MES_LOADING_STATUS.PUBLISH.getValue(), MesExtEnumUtil.MES_LOADING_STATUS.SCANNED.getValue()))
                    .setParameter("shippingGroupCode", strShippingGroupList)
                    .getResultList();
            Map<String, String> shippingGroupCount = new HashMap<>();
            if (!CollectionUtils.isEmpty(ddd)) {
                for (Map dd : ddd) {
                    Object endScanTime = dd.get("endScanTime");
                    if (endScanTime != null) {
                        shippingGroupCount.put(dd.get("shippingGroupCode").toString(), endScanTime.toString());
                    }
                }
            }
            for (String shippingGroupCode : strShippingGroupList) {
                if (shippingGroupCount.containsKey(shippingGroupCode)) {
                    String strScanEndTime = shippingGroupCount.get(shippingGroupCode);
                    if (!StringUtils.isEmpty(strScanEndTime)) {
                        Date scanEndTime = DateUtil.parse(strScanEndTime);
                        values.add(DateUtil.formatDate(DateUtil.SHORT_FORMAT_HOUR, scanEndTime));
                    }
                } else {
                    values.add("");
                }
            }
        }
        return values;
    }

    private List<String> getCPLastShippingTime(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(cfg.getOrganizeCode());
        List<MesPartShippingGroup> shippingGroups = partShippingGroupRDao.findByHqlWhere(packBean);
        if (CollectionUtils.isEmpty(shippingGroups)) {
            return values;
        }

        List<String> strShippingGroupList = shippingGroups.stream().map(MesPartShippingGroup::getShippingGroupCode).collect(Collectors.toList());
        if (strShippingGroupList.stream().anyMatch(s -> s.contains("CK"))) {
            strShippingGroupList = strShippingGroupList.stream().filter(s -> s.contains("CK")).collect(Collectors.toList());
            StringBuilder hql = new StringBuilder();
            hql.append("select max(s.endScanTime)");
            hql.append(" from MesShippingOrderManagement s inner join MesShippingOrderManagementDetail sd on s.id = sd.pid and sd.organizeCode = :organizeCode and sd.isDeleted=:isDeleted and sd.isValid=:isValid ");
            hql.append(" and s.status in (:status) ");
            hql.append(" and s.shippingGroupCode in (:shippingGroupCode) ");
            String strScanEndTime = "";
            try {
                strScanEndTime = entityManager.createQuery(hql.toString(), String.class)
                        .setParameter("organizeCode", cfg.getOrganizeCode())
                        .setParameter("isValid", CommonEnumUtil.VALID)
                        .setParameter("isDeleted", CommonEnumUtil.FALSE)
                        .setParameter("status", Arrays.asList(MesExtEnumUtil.SHIPPING_ORDER_STATUS.SHIPPED.getValue(), MesExtEnumUtil.SHIPPING_ORDER_STATUS.LOADING.getValue()))
                        .setParameter("shippingGroupCode", strShippingGroupList)
                        .getSingleResult();
            } catch (NoResultException ignored) {
            }
            if (!StringUtils.isEmpty(strScanEndTime)) {
                Date scanEndTime = DateUtil.parse(strScanEndTime);
                values.add(DateUtil.formatDate(DateUtil.SHORT_FORMAT_HOUR, scanEndTime));
            }
        } else {
            values.add("0");
        }
        return values;
    }

    /**
     * 下一车最晚发运时间
     * 当前时间+客户端库存/客户JPH-在途时间
     * @param cfg
     * @param cfgDetail
     * @return
     */
    private List<String> getNextShippingTime(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        Date nowTime = DateUtil.now();
        //获取客户端库存
        List<String> clientStockValue = getClientStockQty(cfg);
        if (StringUtils.isEmpty(clientStockValue)) {
            return values;
        }
        //获取客户JPH
        List<String> customerJPHValue = getClientJPH(cfg, cfgDetail);
        if (CollectionUtils.isEmpty(customerJPHValue)) {
            return values;
        }
        double clientStock = Double.parseDouble(clientStockValue.get(0));
        double customerJPH = Double.parseDouble(customerJPHValue.get(0));
        //获取在途时间
        int onTheWayDate = cfg.getOnWayDate() != null ? cfg.getOnWayDate() : 0;

        //客户端库存除以客户JPH
        double customerJPHInt = clientStock / customerJPH;
        BigDecimal b = new BigDecimal(customerJPHInt);
        customerJPHInt = b.setScale(1, RoundingMode.HALF_UP).doubleValue();

        int minutes = (int)(customerJPHInt * 60);
        Date newDate = DateUtil.addMinutes(nowTime, minutes - onTheWayDate);
        values.add(DateUtil.formatDate(DateUtil.BASE_FORMAT3, newDate));
        return values;
    }

    /**
     * 三级停线预警(Min)
     * 当前时间+客户端库存/客户JPH-在途时间
     * @param cfg
     * @param cfgDetail
     * @return
     */
    private List<String> getTreeStopWarning(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        //获取下一车最晚发运时间
        List<String> nextShippingTimeValue = getNextShippingTime(cfg, cfgDetail);
        if (StringUtils.isEmpty(nextShippingTimeValue)) {
            values.add("0");
            return values;
        }
        Date nowTime = DateUtil.parse(DateUtil.formatDate(DateUtil.BASE_FORMAT3, DateUtil.now()), DateUtil.BASE_FORMAT3);
        Date nextShippingTime = DateUtil.parse(nextShippingTimeValue.get(0), DateUtil.BASE_FORMAT3);
        long minutes = DateUtil.minutesBetweenTwoTime(nextShippingTime, nowTime);
        values.add(String.valueOf(minutes));
        return values;
    }

    /**
     * 最近发运VIN/流水号，客户排序信息序号，截取后6位
     * @param cfg
     * @param cfgDetail
     * @return
     */
    private List<String> getShippingVin(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        List<String> strShippingGroupList = Arrays.asList(cfg.getShippingGroupCode().split(","));
        if (CollectionUtils.isEmpty(strShippingGroupList)) {
            values.add("0");
            return values;
        }

        if (strShippingGroupList.stream().anyMatch(s -> s.contains("CK"))) {
            StringBuilder hql = new StringBuilder();
            hql.append("select sd.custInfoSeq");
            hql.append(" from MesShippingOrderManagement s inner join MesShippingOrderManagementDetail sd on s.id = sd.pid and sd.organizeCode = :organizeCode and sd.isDeleted=:isDeleted and sd.isValid=:isValid ");
            hql.append(" and s.status in (:status) ");
            hql.append(" and s.shippingGroupCode in (:shippingGroupCode) ");
            hql.append(" ORDER BY sd.id DESC");
            Long custInfoSeq = null;
            try {
                custInfoSeq = entityManager.createQuery(hql.toString(), Long.class)
                        .setParameter("organizeCode", cfg.getOrganizeCode())
                        .setParameter("isValid", CommonEnumUtil.VALID)
                        .setParameter("isDeleted", CommonEnumUtil.FALSE)
                        .setParameter("status", Arrays.asList(MesExtEnumUtil.SHIPPING_ORDER_STATUS.SHIPPED.getValue(), MesExtEnumUtil.SHIPPING_ORDER_STATUS.LOADING.getValue()))
                        .setParameter("shippingGroupCode", strShippingGroupList)
                        .setMaxResults(1)
                        .getSingleResult();
            } catch (NoResultException e) {
            }
            String strCustInfoSeq = custInfoSeq != null ? Long.toString(custInfoSeq) : "";
            if (strCustInfoSeq.length() >= 6) {
                values.add(strCustInfoSeq.substring(strCustInfoSeq.length() - 6));
            } else {
                values.add(strCustInfoSeq);
            }
        } else {
            StringBuilder hql = new StringBuilder();
            hql.append("select new Map(s.shippingGroupCode as shippingGroupCode, max(sd.custInfoSeq) as custInfoSeq)");
            hql.append(" from MesLoadingList l inner join MesLoadingListDetail ld on l.id = ld.pid inner join MesShippingOrderManagement s on s.id = ld.shippingId inner join MesShippingOrderManagementDetail sd on s.id = sd.pid  and l.organizeCode = :organizeCode and l.isDeleted=:isDeleted and l.isValid=:isValid ");
            hql.append(" and l.status in (:status) ");
            hql.append(" and s.shippingGroupCode in (:shippingGroupCode) ");
            hql.append(" group by s.shippingGroupCode");
            List<Map> ddd = entityManager.createQuery(hql.toString())
                    .setParameter("organizeCode", cfg.getOrganizeCode())
                    .setParameter("isValid", CommonEnumUtil.VALID)
                    .setParameter("isDeleted", CommonEnumUtil.FALSE)
                    .setParameter("status", Arrays.asList(MesExtEnumUtil.MES_LOADING_STATUS.PUBLISH.getValue(), MesExtEnumUtil.MES_LOADING_STATUS.SCANNED.getValue()))
                    .setParameter("shippingGroupCode", strShippingGroupList)
                    .getResultList();

            Map<String, String> shippingGroupCount = new HashMap<>();
            if (!CollectionUtils.isEmpty(ddd)) {
                for (Map dd : ddd) {
                    Object custInfoSeq = dd.get("custInfoSeq");
                    if (custInfoSeq != null) {
                        shippingGroupCount.put(dd.get("shippingGroupCode").toString(), custInfoSeq.toString());
                    }
                }
            }
            for (String shippingGroupCode : strShippingGroupList) {
                if (shippingGroupCount.containsKey(shippingGroupCode)) {
                    String strCustInfoSeq = shippingGroupCount.get(shippingGroupCode);
                    if (StringUtils.isEmpty(strCustInfoSeq)) {
                        strCustInfoSeq = "0";
                    }
                    if (strCustInfoSeq.length() >= 6) {
                        values.add(strCustInfoSeq.substring(strCustInfoSeq.length() - 6));
                    } else {
                        values.add(strCustInfoSeq);
                    }
                } else {
                    values.add("0");
                }
            }
        }
        return values;
    }

    /**
     * 最近发运VIN/流水号，客户排序信息序号，截取后6位
     * @param cfg
     * @param cfgDetail
     * @return
     */
    private List<String> getCPShippingVin(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        DdlPackBean packBean = DdlPackBean.getDdlPackBean(cfg.getOrganizeCode());
        List<MesPartShippingGroup> shippingGroups = partShippingGroupRDao.findByHqlWhere(packBean);
        if (CollectionUtils.isEmpty(shippingGroups)) {
            values.add("0");
            return values;
        }

        List<String> strShippingGroupList = shippingGroups.stream().map(MesPartShippingGroup::getShippingGroupCode).collect(Collectors.toList());
        if (strShippingGroupList.stream().anyMatch(s -> s.contains("CK"))) {
            strShippingGroupList = strShippingGroupList.stream().filter(s -> s.contains("CK")).collect(Collectors.toList());
            StringBuilder hql = new StringBuilder();
            hql.append("select sd.custInfoSeq");
            hql.append(" from MesShippingOrderManagement s inner join MesShippingOrderManagementDetail sd on s.id = sd.pid and sd.organizeCode = :organizeCode and sd.isDeleted=:isDeleted and sd.isValid=:isValid ");
            hql.append(" and s.status in (:status) ");
            hql.append(" and s.shippingGroupCode in (:shippingGroupCode) ");
            hql.append(" ORDER BY sd.id DESC");
            Long custInfoSeq = null;
            try {
                custInfoSeq = entityManager.createQuery(hql.toString(), Long.class)
                        .setParameter("organizeCode", cfg.getOrganizeCode())
                        .setParameter("isValid", CommonEnumUtil.VALID)
                        .setParameter("isDeleted", CommonEnumUtil.FALSE)
                        .setParameter("status", Arrays.asList(MesExtEnumUtil.SHIPPING_ORDER_STATUS.SHIPPED.getValue(), MesExtEnumUtil.SHIPPING_ORDER_STATUS.LOADING.getValue()))
                        .setParameter("shippingGroupCode", strShippingGroupList)
                        .setMaxResults(1)
                        .getSingleResult();
            } catch (NoResultException e) {
            }

            String strCustInfoSeq = custInfoSeq != null ? Long.toString(custInfoSeq) : "";
            if (strCustInfoSeq.length() >= 5) {
                values.add(strCustInfoSeq.substring(strCustInfoSeq.length() - 5));
            } else {
                values.add(strCustInfoSeq);
            }
        } else {
            values.add("0");
        }
        return values;
    }

    /**
     * 当天上线数，要设置客户开班时间、信息点
     * 基于信息点到当前时间过点数量。按天显示。
     * 当天上线数/当班上线数
     * @param cfg
     * @param cfgDetail
     * @return
     */
    private List<String> getTodayOnline(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        if (StringUtils.isEmpty(cfg.getCustStartShift()) || StringUtils.isEmpty(cfg.getOnlinePoint())) {
            values.add("0");
            return values;
        }

        String startDateTime = "";
        String endDateTime = "";

        Date nowTime = DateUtil.now();
        Date configTime = DateUtil.parse(cfg.getCustStartShift(), DateUtil.SHORT_FORMAT_HOUR);
        Date currentTime = DateUtil.parse(DateUtil.formatDate(DateUtil.SHORT_FORMAT_HOUR, nowTime), DateUtil.SHORT_FORMAT_HOUR);
        if (!configTime.after(currentTime)) {
            startDateTime = DateUtil.formatDate(DateUtil.SHORT_FORMAT, nowTime) + " " + cfg.getCustStartShift();
            endDateTime = DateUtil.formatDate(DateUtil.SHORT_FORMAT, DateUtil.addDays(nowTime, 1)) + " " + cfg.getCustStartShift();
        } else {
            //小于配置时间，如凌晨1点则跨天，需将当前时间减一天
            startDateTime = DateUtil.formatDate(DateUtil.SHORT_FORMAT, DateUtil.addDays(nowTime, -1)) + " " + cfg.getCustStartShift();
            endDateTime = DateUtil.formatDate(DateUtil.SHORT_FORMAT, nowTime) + " " + cfg.getCustStartShift();
        }

        StringBuilder onNumberHql = new StringBuilder();
        onNumberHql.append("select count(1) from " + MesCimSeresJisVinOverPoint.class.getName());
        onNumberHql.append(" where organizeCode = :organizeCode and  isDeleted = :isDeleted and isValid = :isValid ");
        onNumberHql.append(" and overPoint = :overPoint ");
        onNumberHql.append(" and createDatetime BETWEEN :startDateTime AND :endDateTime ");
        Long onNumber = entityManager.createQuery(onNumberHql.toString(), Long.class)
                .setParameter("organizeCode", cfg.getOrganizeCode())
                .setParameter("isValid", CommonEnumUtil.VALID)
                .setParameter("isDeleted", CommonEnumUtil.FALSE)
                .setParameter("overPoint", cfg.getOnlinePoint())
                .setParameter("startDateTime", startDateTime)
                .setParameter("endDateTime", endDateTime)
                .getSingleResult();
        values.add(String.valueOf(onNumber != null ? onNumber : 0));
        return values;
    }

    /**
     * 当天发运数
     * 设置内部开班时间，显示已发运数、已装车数，发运单状态为已发运及已装车。
     * @param cfg
     * @param cfgDetail
     * @return
     */
    private List<String> getTodayShipping(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        List<String> strShippingGroupList = Arrays.asList(cfg.getShippingGroupCode().split(","));
        if (CollectionUtils.isEmpty(strShippingGroupList) || StringUtils.isEmpty(cfg.getInterStartShift())) {
            values.add("0");
            return values;
        }

        String startDateTime = "";
        String endDateTime = "";

        Date nowTime = DateUtil.now();
        Date configTime = DateUtil.parse(cfg.getInterStartShift(), DateUtil.SHORT_FORMAT_HOUR);
        Date currentTime = DateUtil.parse(DateUtil.formatDate(DateUtil.SHORT_FORMAT_HOUR, nowTime), DateUtil.SHORT_FORMAT_HOUR);
        if (!configTime.after(currentTime)) {
            startDateTime = DateUtil.formatDate(DateUtil.SHORT_FORMAT, nowTime) + " " + cfg.getInterStartShift();
            endDateTime = DateUtil.formatDate(DateUtil.SHORT_FORMAT, DateUtil.addDays(nowTime, 1)) + " " + cfg.getInterStartShift();
        } else {
            //小于配置时间，如凌晨1点则跨天，需将当前时间减一天
            startDateTime = DateUtil.formatDate(DateUtil.SHORT_FORMAT, DateUtil.addDays(nowTime, -1)) + " " + cfg.getInterStartShift();
            endDateTime = DateUtil.formatDate(DateUtil.SHORT_FORMAT, nowTime) + " " + cfg.getInterStartShift();
        }

        StringBuilder hql = new StringBuilder();
        hql.append("select new Map(s.shippingGroupCode as shippingGroupCode, count(sd.id) as totalCount)");
        hql.append(" from MesShippingOrderManagement s inner join MesShippingOrderManagementDetail sd on s.id = sd.pid and sd.organizeCode = :organizeCode and sd.isDeleted=:isDeleted and sd.isValid=:isValid ");
        hql.append(" and s.status in (:status) ");
        hql.append(" and s.modifyDatetime BETWEEN :startDateTime AND :endDateTime ");
        hql.append(" and s.shippingGroupCode in (:shippingGroupCode) ");
        hql.append(" group by s.shippingGroupCode");
        List<Map> ddd = entityManager.createQuery(hql.toString())
                .setParameter("organizeCode", cfg.getOrganizeCode())
                .setParameter("isValid", CommonEnumUtil.VALID)
                .setParameter("isDeleted", CommonEnumUtil.FALSE)
                .setParameter("status", Arrays.asList(MesExtEnumUtil.SHIPPING_ORDER_STATUS.SHIPPED.getValue(), MesExtEnumUtil.SHIPPING_ORDER_STATUS.LOADING.getValue()))
                .setParameter("startDateTime", startDateTime)
                .setParameter("endDateTime", endDateTime)
                .setParameter("shippingGroupCode", strShippingGroupList)
                .getResultList();
        Map<String, String> shippingGroupCount = new HashMap<>();
        if (!CollectionUtils.isEmpty(ddd)) {
            for (Map dd : ddd) {
                Object totalCount = dd.get("totalCount");
                if (totalCount != null) {
                    shippingGroupCount.put(dd.get("shippingGroupCode").toString(), totalCount.toString());
                }
            }
        }
        for (String shippingGroupCode : strShippingGroupList) {
            values.add(shippingGroupCount.getOrDefault(shippingGroupCode, "0"));
        }
        return values;
    }

    /**
     * 当天装车数
     * 设置内部开班时间，显示已装车数，装车单状态为已扫描。
     * @param cfg
     * @param cfgDetail
     * @return
     */
    private List<String> getTodayCar(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        List<String> strShippingGroupList = Arrays.asList(cfg.getShippingGroupCode().split(","));
        if (CollectionUtils.isEmpty(strShippingGroupList) || StringUtils.isEmpty(cfg.getInterStartShift())) {
            values.add("0");
            return values;
        }

        String startDateTime = "";
        String endDateTime = "";

        Date nowTime = DateUtil.now();
        Date configTime = DateUtil.parse(cfg.getInterStartShift(), DateUtil.SHORT_FORMAT_HOUR);
        Date currentTime = DateUtil.parse(DateUtil.formatDate(DateUtil.SHORT_FORMAT_HOUR, nowTime), DateUtil.SHORT_FORMAT_HOUR);
        if (!configTime.after(currentTime)) {
            startDateTime = DateUtil.formatDate(DateUtil.SHORT_FORMAT, nowTime) + " " + cfg.getInterStartShift();
            endDateTime = DateUtil.formatDate(DateUtil.SHORT_FORMAT, DateUtil.addDays(nowTime, 1)) + " " + cfg.getInterStartShift();
        } else {
            //小于配置时间，如凌晨1点则跨天，需将当前时间减一天
            startDateTime = DateUtil.formatDate(DateUtil.SHORT_FORMAT, DateUtil.addDays(nowTime, -1)) + " " + cfg.getInterStartShift();
            endDateTime = DateUtil.formatDate(DateUtil.SHORT_FORMAT, nowTime) + " " + cfg.getInterStartShift();
        }

        StringBuilder hql = new StringBuilder();
        hql.append("select new Map(s.shippingGroupCode as shippingGroupCode, sum(sd.planQty) as totalCount)");
        hql.append(" from MesLoadingList l inner join MesLoadingListDetail ld on l.id = ld.pid inner join MesShippingOrderManagement s on s.id = ld.shippingId inner join MesShippingOrderManagementDetail sd on s.id = sd.pid  and l.organizeCode = :organizeCode and l.isDeleted=:isDeleted and l.isValid=:isValid ");
        hql.append(" and l.status = :status ");
        hql.append(" and l.modifyDatetime BETWEEN :startDateTime AND :endDateTime ");
        hql.append(" and s.shippingGroupCode in (:shippingGroupCode) ");
        hql.append(" group by s.shippingGroupCode ");
        List<Map> dataList = entityManager.createQuery(hql.toString())
                .setParameter("organizeCode", cfg.getOrganizeCode())
                .setParameter("isValid", CommonEnumUtil.VALID)
                .setParameter("isDeleted", CommonEnumUtil.FALSE)
                .setParameter("status", MesExtEnumUtil.MES_LOADING_STATUS.SCANNED.getValue())
                .setParameter("startDateTime", startDateTime)
                .setParameter("endDateTime", endDateTime)
                .setParameter("shippingGroupCode", strShippingGroupList)
                .getResultList();

        Map<String, String> shippingGroupCount = new HashMap<>();
        if (!CollectionUtils.isEmpty(dataList)) {
            for (Map data : dataList) {
                Object totalCount = data.get("totalCount");
                if (totalCount != null) {
                    shippingGroupCount.put(data.get("shippingGroupCode").toString(), String.format("%.0f", (Double)totalCount));
                }
            }
        }
        for (String shippingGroupCode : strShippingGroupList) {
            values.add(shippingGroupCount.getOrDefault(shippingGroupCode, "0"));
        }
        return values;
    }

    /**
     * 客户端JPH
     * 上线点，当前时间往前一小时的过点总数维护默认值，当数值为0时，显示为默认值
     * @param cfg
     * @param cfgDetail
     * @return
     */
    private List<String> getClientJPH(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        List<String> strShippingGroupList = Arrays.asList(cfg.getShippingGroupCode().split(","));
        if (CollectionUtils.isEmpty(strShippingGroupList)) {
            return Collections.singletonList("0");
        }

        Date nowTime = DateUtil.now();
        Date prevHourTime = DateUtil.addMinutes(nowTime, -60);

        StringBuilder offNumberHql = new StringBuilder();
        offNumberHql.append("select count(1) from " + MesCimSeresJisVinOverPoint.class.getName());
        offNumberHql.append(" where organizeCode = :organizeCode and  isDeleted = :isDeleted and isValid = :isValid ");
        offNumberHql.append(" and createDatetime BETWEEN :startDateTime AND :endDateTime ");
        offNumberHql.append(" and overPoint = :overPoint ");
        Long vpCount = entityManager.createQuery(offNumberHql.toString(), Long.class)
                .setParameter("organizeCode", cfg.getOrganizeCode())
                .setParameter("isValid", CommonEnumUtil.VALID)
                .setParameter("isDeleted", CommonEnumUtil.FALSE)
                .setParameter("startDateTime", DateUtil.formatDate(nowTime))
                .setParameter("endDateTime", DateUtil.formatDate(prevHourTime))
                .setParameter("overPoint", cfg.getOnlinePoint())
                .getSingleResult();

        if (vpCount == null || Objects.equals(vpCount, 0L)) {
            if (!StringUtils.isEmpty(cfg.getCustJph())) {
                values.add(cfg.getCustJph());
            }

        } else {
            values.add(vpCount.toString());
        }

        if (values.isEmpty()) {
            values.add("1");
        }
        return values;
    }

    /**
     * 生产JPH
     * 当前时间往前一小时的完成数/下线数
     * @param cfg
     * @param cfgDetail
     * @return
     */
    private List<String> getProductJPH(MesShippingKanbanCfg cfg, MesShippingKanbanCfgDetail cfgDetail) {
        List<String> values = new ArrayList<>();
        List<MesPartShippingGroup> partShippingGroups = getShippingGroups(cfg);
        if (CollectionUtils.isEmpty(partShippingGroups)) {
            return Collections.singletonList("0");
        }

        Date nowTime = DateUtil.now();
        Date prevHourTime = DateUtil.addMinutes(nowTime, -60);

        // Use a single query to fetch counts grouped by work center code
        List<Map> productionCounts = entityManager.createQuery(
                        "SELECT new map(pr.workCenterCode as workCenterCode, COUNT(pr) as totalCount) " +
                                "FROM " + MesProductionRecord.class.getName() + " pr " +
                                "WHERE pr.organizeCode = :organizeCode AND pr.isDeleted = :isDeleted AND pr.isValid = :isValid " +
                                "AND pr.createDatetime BETWEEN :startDateTime AND :endDateTime " +
                                "GROUP BY pr.workCenterCode", Map.class)
                .setParameter("organizeCode", cfg.getOrganizeCode())
                .setParameter("isValid", CommonEnumUtil.VALID)
                .setParameter("isDeleted", CommonEnumUtil.FALSE)
                .setParameter("startDateTime", DateUtil.formatDate(prevHourTime))
                .setParameter("endDateTime", DateUtil.formatDate(nowTime))
                .getResultList();

        // Use a map to store counts for quick access
        Map<String, String> shippingGroupCount = productionCounts.stream()
                .collect(Collectors.toMap(
                        map -> map.get("workCenterCode").toString(),
                        map -> map.get("totalCount").toString(),
                        (existing, replacement) -> existing
                ));

        // Collect counts for each shipping group
        for (MesPartShippingGroup shippingGroup : partShippingGroups) {
            if (StringUtils.isEmpty(shippingGroup.getCarrierCode())) {
                continue;
            }
            values.add(shippingGroupCount.getOrDefault(shippingGroup.getCarrierCode(), "0"));
        }

        // Ensure at least one value is returned
        if (values.isEmpty()) {
            values.add("0");
        }
        return values;
    }
}
