mescloud/src/main/java/com/jeesite/modules/mes/erp/ErpPickMaterialBillService.java

package com.jeesite.modules.mes.erp;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jeesite.common.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * ERP 生产领料单列表 BillQuery（PRD_PickMtrl）。
 */
@Service
public class ErpPickMaterialBillService {

	private static final Logger logger = LoggerFactory.getLogger(ErpPickMaterialBillService.class);
	private static final String FORM_ID = "PRD_PickMtrl";
	/** BillQuery 分录字段：除分录内码外，明细字段一般不带 FEntity_ 前缀 */
	private static final String MO_BILL_NO_FIELD = "FMoBillNo";
	private static final String MATERIAL_NUMBER_FIELD = "FMaterialId.FNUMBER";
	private static final String HEADER_FIELD_KEYS = String.join(",",
			"FID",
			"FBILLNO",
			"FDate",
			"FDocumentStatus",
			"FPrdOrgId.FNUMBER",
			"FPrdOrgId.FNAME",
			"FStockOrgId.FNUMBER",
			"FStockOrgId.FNAME");
	private static final String DETAIL_FIELD_KEYS = String.join(",",
			"FID",
			"FBILLNO",
			"FDate",
			"FDocumentStatus",
			"FPrdOrgId.FNUMBER",
			"FPrdOrgId.FNAME",
			"FStockOrgId.FNUMBER",
			"FStockOrgId.FNAME",
			"FEntryWorkShopId.FNUMBER",
			"FEntryWorkShopId.FNAME",
			"FEntity_FEntryID",
			"FEntity_FSeq",
			"FMoEntrySeq",
			MATERIAL_NUMBER_FIELD,
			"FMaterialName",
			"FUnitID.FNAME",
			"FAppQty",
			"FActualQty",
			MO_BILL_NO_FIELD,
			"FPPBomBillNo",
			"FStockId.FNUMBER",
			"FStockId.FNAME");

	private final KingdeeErpClient erpClient;
	private final KingdeeErpProperties erpProperties;
	private final MesErpPickKcService mesErpPickKcService;

	public ErpPickMaterialBillService(KingdeeErpClient erpClient, KingdeeErpProperties erpProperties,
			MesErpPickKcService mesErpPickKcService) {
		this.erpClient = erpClient;
		this.erpProperties = erpProperties;
		this.mesErpPickKcService = mesErpPickKcService;
	}

	/**
	 * 列表页：按领料单号聚合（始终走明细分录 BillQuery，避免表头查询 + FMoBillNo 过滤时条数偏少）。
	 */
	public List<ErpPickMaterialBill> findPageList(ErpPickMaterialBill query, int startRow, int limit) {
		String moFilter = buildProductionOrderFilter(query);
		return findAggregatedPageList(query, moFilter, startRow, limit);
	}

	/**
	 * 生产订单下查：关联领料明细分录。
	 */
	public List<ErpPickMaterialBill> findTraceListByMoBillNo(String moBillNo) {
		if (StringUtils.isBlank(moBillNo)) {
			return new ArrayList<>();
		}
		ErpPickMaterialBill query = new ErpPickMaterialBill();
		query.setMoBillNoQuery(moBillNo.trim());
		String moFilter = buildProductionOrderFilter(query);
		return findDetailList(query, moFilter, 0, 2000);
	}

	/**
	 * 明细页：指定领料单号的全部分录行。
	 */
	public List<ErpPickMaterialBill> findDetailList(String billNo) {
		if (StringUtils.isBlank(billNo)) {
			return new ArrayList<>();
		}
		List<String> billNos = new ArrayList<>();
		billNos.add(billNo.trim());
		List<ErpPickMaterialBill> list = findDetailListByBillNos(billNos, null);
		try {
			mesErpPickKcService.increaseFromBillDetails(billNo.trim(), list);
		} catch (Exception ex) {
			logger.error("ERP pick bill detail MES kc sync failed, billNo={}", billNo, ex);
		}
		return list;
	}

	private List<ErpPickMaterialBill> findAggregatedPageList(ErpPickMaterialBill query, String moFilter,
			int startRow, int limit) {
		int pageSize = Math.max(limit, 1);
		int targetSize = startRow + pageSize;
		int fetchStart = 0;
		// 每张领料单往往有多行分录，需多拉分录行再按单号聚合
		int fetchLimit = Math.max(pageSize * 15, 100);
		int maxFetchRows = Math.max(targetSize * 40, 3000);
		Map<String, ErpPickMaterialBill> billMap = new LinkedHashMap<>();

		while (billMap.size() < targetSize && fetchStart < maxFetchRows) {
			List<ErpPickMaterialBill> chunk = findDetailList(query, moFilter, fetchStart, fetchLimit);
			if (chunk.isEmpty()) {
				break;
			}
			for (ErpPickMaterialBill detail : chunk) {
				ErpPickMaterialBill header = billMap.get(detail.getBillNo());
				if (header == null) {
					header = toHeaderFromDetail(detail);
					header.setEntryCount(0);
					header.setTotalAppQty(BigDecimal.ZERO);
					header.setTotalActualQty(BigDecimal.ZERO);
					billMap.put(detail.getBillNo(), header);
				}
				if (StringUtils.isBlank(header.getMoBillNo()) && StringUtils.isNotBlank(detail.getMoBillNo())) {
					header.setMoBillNo(detail.getMoBillNo());
				}
				if (StringUtils.isBlank(header.getWorkshopName()) && StringUtils.isNotBlank(detail.getWorkshopName())) {
					header.setWorkshopNumber(detail.getWorkshopNumber());
					header.setWorkshopName(detail.getWorkshopName());
				}
				header.setEntryCount(header.getEntryCount() + 1);
				if (detail.getAppQty() != null) {
					header.setTotalAppQty(header.getTotalAppQty().add(detail.getAppQty()));
				}
				if (detail.getActualQty() != null) {
					header.setTotalActualQty(header.getTotalActualQty().add(detail.getActualQty()));
				}
			}
			if (chunk.size() < fetchLimit) {
				break;
			}
			fetchStart += fetchLimit;
		}
		List<ErpPickMaterialBill> allBills = new ArrayList<>(billMap.values());
		if (startRow >= allBills.size()) {
			return new ArrayList<>();
		}
		return new ArrayList<>(allBills.subList(startRow, Math.min(startRow + pageSize, allBills.size())));
	}

	private List<ErpPickMaterialBill> findHeaderList(ErpPickMaterialBill query, String moFilter, int startRow, int limit) {
		JSONObject data = new JSONObject();
		data.put("FormId", FORM_ID);
		data.put("FieldKeys", HEADER_FIELD_KEYS);
		data.put("FilterString", buildFilterString(query, moFilter, false));
		data.put("OrderString", "FDate DESC,FBILLNO DESC");
		data.put("StartRow", Math.max(startRow, 0));
		data.put("Limit", Math.max(limit, 1));
		logger.info("ERP pick material bill header query: {}", data.toJSONString());
		return mapHeaderRows(erpClient.billQuery(data));
	}

	private List<ErpPickMaterialBill> findDetailList(ErpPickMaterialBill query, String moFilter, int startRow, int limit) {
		JSONObject data = new JSONObject();
		data.put("FormId", FORM_ID);
		data.put("FieldKeys", DETAIL_FIELD_KEYS);
		data.put("FilterString", buildFilterString(query, moFilter, true));
		data.put("OrderString", "FDate DESC,FBILLNO DESC,FEntity_FSeq");
		data.put("StartRow", Math.max(startRow, 0));
		data.put("Limit", Math.max(limit, 1));
		logger.info("ERP pick material bill detail query: {}", data.toJSONString());
		return mapDetailRows(erpClient.billQuery(data));
	}

	private List<ErpPickMaterialBill> findDetailListByBillNos(List<String> billNos, String moFilter) {
		if (billNos == null || billNos.isEmpty()) {
			return new ArrayList<>();
		}
		JSONObject data = new JSONObject();
		data.put("FormId", FORM_ID);
		data.put("FieldKeys", DETAIL_FIELD_KEYS);
		data.put("FilterString", buildInFilterString(billNos, moFilter));
		data.put("OrderString", "FBILLNO DESC,FEntity_FSeq");
		data.put("StartRow", 0);
		data.put("Limit", 2000);
		logger.info("ERP pick material bill detail by billNos query: {}", data.toJSONString());
		return mapDetailRows(erpClient.billQuery(data));
	}

	private List<ErpPickMaterialBill> mapHeaderRows(JSONArray rows) {
		Map<String, ErpPickMaterialBill> billMap = new LinkedHashMap<>();
		for (int i = 0; i < rows.size(); i++) {
			ErpPickMaterialBill bill = toHeader(rows.getJSONObject(i));
			if (StringUtils.isBlank(bill.getBillNo())) {
				continue;
			}
			billMap.putIfAbsent(bill.getBillNo(), bill);
		}
		return new ArrayList<>(billMap.values());
	}

	private List<ErpPickMaterialBill> mapDetailRows(JSONArray rows) {
		List<ErpPickMaterialBill> list = new ArrayList<>();
		for (int i = 0; i < rows.size(); i++) {
			ErpPickMaterialBill bill = toDetail(rows.getJSONObject(i), i);
			if (StringUtils.isBlank(bill.getMoBillNo())) {
				continue;
			}
			list.add(bill);
		}
		return list;
	}

	private ErpPickMaterialBill toHeader(JSONObject row) {
		ErpPickMaterialBill bill = new ErpPickMaterialBill();
		bill.setFid(readString(row, "FID"));
		bill.setBillNo(readString(row, "FBILLNO", "FBillNo"));
		bill.setId(bill.getBillNo());
		fillHeaderFields(bill, row);
		return bill;
	}

	private ErpPickMaterialBill toHeaderFromDetail(ErpPickMaterialBill detail) {
		ErpPickMaterialBill bill = new ErpPickMaterialBill();
		bill.setFid(detail.getFid());
		bill.setBillNo(detail.getBillNo());
		bill.setId(detail.getBillNo());
		bill.setDocumentStatus(detail.getDocumentStatus());
		bill.setDocumentStatusName(detail.getDocumentStatusName());
		bill.setDate(detail.getDate());
		bill.setPrdOrgNumber(detail.getPrdOrgNumber());
		bill.setPrdOrgName(detail.getPrdOrgName());
		bill.setStockOrgNumber(detail.getStockOrgNumber());
		bill.setStockOrgName(detail.getStockOrgName());
		bill.setMoBillNo(detail.getMoBillNo());
		bill.setWorkshopNumber(detail.getWorkshopNumber());
		bill.setWorkshopName(detail.getWorkshopName());
		return bill;
	}

	private ErpPickMaterialBill toDetail(JSONObject row, int index) {
		ErpPickMaterialBill bill = new ErpPickMaterialBill();
		bill.setFid(readString(row, "FID"));
		bill.setBillNo(readString(row, "FBILLNO", "FBillNo"));
		bill.setEntryId(readString(row, "FEntity_FEntryID", "FEntity_FEntryId"));
		bill.setSeq(firstNonBlank(
				readString(row, "FEntity_FSeq"),
				readString(row, "FMoEntrySeq")));
		String rowId = bill.getBillNo();
		if (StringUtils.isNotBlank(bill.getEntryId())) {
			rowId = rowId + "-" + bill.getEntryId();
		} else if (index >= 0) {
			rowId = rowId + "-" + index;
		}
		bill.setId(rowId);
		fillHeaderFields(bill, row);
		bill.setWorkshopNumber(readString(row,
				"FEntryWorkShopId.FNUMBER", "FEntryWorkShopId.FNumber",
				"FWorkShopId.FNUMBER", "FWorkShopId.FNumber"));
		bill.setWorkshopName(readString(row,
				"FEntryWorkShopId.FNAME", "FEntryWorkShopId.FName",
				"FWorkShopId.FNAME", "FWorkShopId.FName"));
		bill.setMaterialNumber(readString(row,
				"FMaterialId.FNUMBER", "FMaterialId.FNumber",
				"FEntity_FMaterialId.FNUMBER", "FEntity_FMaterialId.FNumber"));
		bill.setMaterialName(readString(row,
				"FMaterialName",
				"FMaterialId.FNAME", "FMaterialId.FName",
				"FEntity_FMaterialId.FNAME", "FEntity_FMaterialId.FName"));
		bill.setUnitName(readString(row,
				"FUnitID.FNAME", "FUnitId.FNAME", "FUnitId.FName",
				"FEntity_FUnitId.FNAME", "FEntity_FUnitId.FName"));
		bill.setAppQty(firstBigDecimal(row, "FAppQty", "FEntity_FAppQty"));
		bill.setActualQty(firstBigDecimal(row, "FActualQty", "FEntity_FActualQty"));
		bill.setMoBillNo(readString(row, MO_BILL_NO_FIELD, "FEntity_FMoBillNo"));
		bill.setPpbomBillNo(readString(row, "FPPBomBillNo", "FEntity_FPPBomBillNo"));
		bill.setStockName(readString(row,
				"FStockId.FNAME", "FStockId.FName",
				"FEntity_FStockId.FNAME", "FEntity_FStockId.FName"));
		return bill;
	}

	private void fillHeaderFields(ErpPickMaterialBill bill, JSONObject row) {
		bill.setDocumentStatus(readString(row, "FDocumentStatus"));
		bill.setDocumentStatusName(documentStatusName(bill.getDocumentStatus()));
		bill.setDate(formatErpDate(readString(row, "FDate")));
		bill.setPrdOrgNumber(readString(row, "FPrdOrgId.FNUMBER", "FPrdOrgId.FNumber"));
		bill.setPrdOrgName(readString(row, "FPrdOrgId.FNAME", "FPrdOrgId.FName"));
		bill.setStockOrgNumber(readString(row, "FStockOrgId.FNUMBER", "FStockOrgId.FNumber"));
		bill.setStockOrgName(readString(row, "FStockOrgId.FNAME", "FStockOrgId.FName"));
	}

	/**
	 * 按生产订单号筛选；留空时不限制具体订单，仅要求分录已关联生产订单（FMoBillNo 非空）。
	 */
	private String buildProductionOrderFilter(ErpPickMaterialBill query) {
		String moBillNo = query == null ? "" : StringUtils.trim(query.getMoBillNoQuery());
		if (StringUtils.isBlank(moBillNo)) {
			return "";
		}
		return MO_BILL_NO_FIELD + "='" + escape(moBillNo) + "'";
	}

	private String buildFilterString(ErpPickMaterialBill query, String moFilter, boolean includeMaterialFilter) {
		String filter = appendAnd(buildCurrentOrgFilter(), moFilter);
		// 排除未关联生产订单的分录
		filter = appendAnd(filter, MO_BILL_NO_FIELD + "<>''");
		if (query == null) {
			return filter;
		}
		if (StringUtils.isNotBlank(query.getBillNo())) {
			filter = appendAnd(filter, "FBILLNO='" + escape(query.getBillNo()) + "'");
		}
		if (includeMaterialFilter && StringUtils.isNotBlank(query.getMaterialNumberQuery())) {
			filter = appendAnd(filter,
					MATERIAL_NUMBER_FIELD + " like '%" + escape(query.getMaterialNumberQuery()) + "%'");
		}
		return filter;
	}

	private String buildInFilterString(List<String> billNos, String moFilter) {
		StringBuilder builder = new StringBuilder(appendAnd(buildCurrentOrgFilter(), MO_BILL_NO_FIELD + "<>''"));
		if (StringUtils.isNotBlank(moFilter)) {
			builder.append(" AND ").append(moFilter);
		}
		builder.append(" AND FBILLNO IN(");
		for (int i = 0; i < billNos.size(); i++) {
			if (i > 0) {
				builder.append(",");
			}
			builder.append("'").append(escape(billNos.get(i))).append("'");
		}
		builder.append(")");
		return builder.toString();
	}

	private String buildInFilter(String fieldName, List<String> values) {
		StringBuilder builder = new StringBuilder(fieldName).append(" IN(");
		for (int i = 0; i < values.size(); i++) {
			if (i > 0) {
				builder.append(",");
			}
			builder.append("'").append(escape(values.get(i))).append("'");
		}
		builder.append(")");
		return builder.toString();
	}

	private String buildCurrentOrgFilter() {
		if (!erpProperties.isFilterCurrentOrg()) {
			return "";
		}
		if (StringUtils.isNotBlank(erpProperties.getOrgNumber())) {
			return "FPrdOrgId.FNUMBER='" + escape(erpProperties.getOrgNumber()) + "'";
		}
		Long currentOrganizationId = erpClient.getCurrentOrganizationId();
		if (currentOrganizationId == null) {
			return "1=1";
		}
		return "FPrdOrgId=" + currentOrganizationId;
	}

	private String appendAnd(String left, String right) {
		if (StringUtils.isBlank(left)) {
			return right;
		}
		if (StringUtils.isBlank(right)) {
			return left;
		}
		return left + " AND " + right;
	}

	private String escape(String value) {
		return value == null ? "" : value.trim().replace("'", "''");
	}

	private String readString(JSONObject row, String... keys) {
		for (String key : keys) {
			String value = row.getString(key);
			if (StringUtils.isNotBlank(value)) {
				return value.trim();
			}
		}
		return "";
	}

	private String firstNonBlank(String... values) {
		for (String value : values) {
			if (StringUtils.isNotBlank(value)) {
				return value.trim();
			}
		}
		return "";
	}

	private BigDecimal firstBigDecimal(JSONObject row, String... keys) {
		for (String key : keys) {
			BigDecimal value = row.getBigDecimal(key);
			if (value != null) {
				return ErpKingdeeParseUtils.normalizeQtyNullable(value);
			}
		}
		return null;
	}

	private String formatErpDate(String rawValue) {
		if (StringUtils.isBlank(rawValue)) {
			return "";
		}
		String trimmed = rawValue.trim();
		return trimmed.length() >= 10 ? trimmed.substring(0, 10) : trimmed;
	}

	private String documentStatusName(String status) {
		if ("A".equals(status)) {
			return "创建";
		}
		if ("B".equals(status)) {
			return "审核中";
		}
		if ("C".equals(status)) {
			return "已审核";
		}
		if ("D".equals(status)) {
			return "重新审核";
		}
		return status;
	}
}