Compare commits
41 Commits
codex/stoc
...
main
| Author | SHA1 | Date | |
|---|---|---|---|
| 6833b7bc79 | |||
| 893f4d6aca | |||
| 3d9b12eace | |||
| c9715c0e5b | |||
| c80d28c33f | |||
| 62cac712db | |||
| e5030d4864 | |||
| 0a2fe1c3a3 | |||
| 1d4eecb03c | |||
| 2bdc4b5713 | |||
| c6c7feaeea | |||
|
fa0e604bf8 | ||
| cf8d5f68ae | |||
| e15898693f | |||
| 214ede07a5 | |||
| 99fa402092 | |||
| 8f12b889c0 | |||
| 88a39975d3 | |||
| 195351deae | |||
| 31f184edd0 | |||
| ea2942f118 | |||
| 817cc74a40 | |||
| e71607386d | |||
| 7505a04e37 | |||
| 0afcb26119 | |||
| 70550b5499 | |||
| ada8d09c27 | |||
| a93691f5d8 | |||
| b0b618e7df | |||
| 30554720b2 | |||
| 901212a32c | |||
| f7fc7fef4f | |||
| 23e1b365d6 | |||
| 6c3b14fc10 | |||
| aeac4bcc81 | |||
| 2745cf18ff | |||
| 38804f6624 | |||
| fc280bfe27 | |||
| 4d419c5c7c | |||
| 2c8ed86c26 | |||
| 151d420831 |
153
backend/scripts/validateDailyInventoryFromBiz.js
Normal file
153
backend/scripts/validateDailyInventoryFromBiz.js
Normal file
@ -0,0 +1,153 @@
|
||||
'use strict';
|
||||
/**
|
||||
* 用业务库（JSReportReplica）反算流水，校验已导入的聚水潭官方进销存数据。
|
||||
* 用法（先起隧道）: cd backend && node scripts/validateDailyInventoryFromBiz.js 2026-10-06
|
||||
*
|
||||
* 注意：业务库 jst_inventoryInfo 是 T-1 同步快照，只能精确校验「昨天」的期末；
|
||||
* 更早的历史日会带自然变动（实测 10-05 差 0.1%~1.2%），属正常。
|
||||
*/
|
||||
const mysql = require('mysql2/promise');
|
||||
const sql = require('mssql');
|
||||
|
||||
const BIZ_DATE = process.argv[2] || '2026-10-06';
|
||||
|
||||
const SYS = {
|
||||
host: '127.0.0.1',
|
||||
port: 13307,
|
||||
user: 'cangku_67c48c6660be787f',
|
||||
password: 'wIJugzi26RQHF4J4BgnwxCxxecp157goR93M_IXEhqc',
|
||||
database: 'shujuku2_system',
|
||||
};
|
||||
|
||||
// 业务库能还原的 4 个流水列（与 dailyInventoryFlows.js 口径一致，这里加单日上界）
|
||||
const FLOWS = [
|
||||
{
|
||||
field: 'purchase_in',
|
||||
label: '采购进仓',
|
||||
sql: `SELECT p.wms_co_id AS wmsCoId, SUM(CAST(ISNULL(pi.qty,0) AS DECIMAL(18,2))) AS qty
|
||||
FROM rpt.jst_purchase p JOIN rpt.jst_purchaseItem pi ON pi.po_id = p.po_id
|
||||
WHERE p.wms_co_id IN (%W%) AND p.po_date >= @from AND p.po_date < @to
|
||||
AND ISNULL(p.status,'') IN ('Confirmed','Finished') GROUP BY p.wms_co_id`,
|
||||
},
|
||||
{
|
||||
field: 'purchase_return',
|
||||
label: '采购退货',
|
||||
sql: `SELECT o.wms_co_id AS wmsCoId, SUM(CAST(ISNULL(oi.qty,0) AS DECIMAL(18,2))) AS qty
|
||||
FROM rpt.jst_purchaseout o JOIN rpt.jst_purchaseoutItem oi ON oi.io_id = o.io_id
|
||||
WHERE o.wms_co_id IN (%W%) AND o.io_date >= @from AND o.io_date < @to
|
||||
GROUP BY o.wms_co_id`,
|
||||
},
|
||||
{
|
||||
field: 'sales_out',
|
||||
label: '销售出仓',
|
||||
sql: `SELECT o.wms_co_id AS wmsCoId, SUM(CAST(ISNULL(oi.qty,0) AS DECIMAL(18,2))) AS qty
|
||||
FROM rpt.jst_Orders o JOIN rpt.jst_OrderItem oi ON oi.o_id = o.o_id
|
||||
WHERE o.wms_co_id IN (%W%) AND o.send_date >= @from AND o.send_date < @to
|
||||
AND ISNULL(o.status,'') NOT IN ('Delete','Cancelled') GROUP BY o.wms_co_id`,
|
||||
},
|
||||
{
|
||||
field: 'sales_return',
|
||||
label: '销售退货',
|
||||
sql: `SELECT r.wms_co_id AS wmsCoId, SUM(CAST(ISNULL(ri.r_qty,0) AS DECIMAL(18,2))) AS qty
|
||||
FROM rpt.jst_returnAndRefundInfo r JOIN rpt.jst_returnAndRefundItem ri ON ri.as_id = r.as_id
|
||||
WHERE r.wms_co_id IN (%W%) AND r.as_date >= @from AND r.as_date < @to
|
||||
AND ISNULL(r.type,'') IN (N'普通退货',N'拒收退货',N'换货',N'维修') GROUP BY r.wms_co_id`,
|
||||
},
|
||||
];
|
||||
|
||||
(async () => {
|
||||
const sys = await mysql.createConnection(SYS);
|
||||
const [official] = await sys.query(
|
||||
'SELECT wms_co_id, warehouse, opening_qty, closing_qty, purchase_in, purchase_return, sales_out, sales_return FROM daily_inventory_official WHERE DATE(biz_date)=? ORDER BY closing_qty DESC',
|
||||
[BIZ_DATE]
|
||||
);
|
||||
if (!official.length) {
|
||||
console.log('官方表无 ' + BIZ_DATE + ' 数据');
|
||||
await sys.end();
|
||||
return;
|
||||
}
|
||||
const ids = official.map((r) => String(r.wms_co_id)).filter(Boolean);
|
||||
const clause = ids.map((_, i) => '@w' + i).join(',');
|
||||
const wparams = {};
|
||||
ids.forEach((v, i) => (wparams['w' + i] = v));
|
||||
|
||||
// 业务库凭据从系统库 database_configs 现取（不落盘）
|
||||
const [cfgRows] = await sys.query(
|
||||
"SELECT host, port, database_name, username, password FROM database_configs WHERE name='cloud_database_public' LIMIT 1"
|
||||
);
|
||||
const biz = cfgRows[0];
|
||||
await sql.connect({
|
||||
server: biz.host,
|
||||
port: Number(biz.port),
|
||||
database: biz.database_name,
|
||||
user: biz.username,
|
||||
password: biz.password,
|
||||
options: { encrypt: false, trustServerCertificate: true },
|
||||
connectionTimeout: 20000,
|
||||
requestTimeout: 180000,
|
||||
});
|
||||
|
||||
const from = BIZ_DATE + ' 00:00:00';
|
||||
const d = new Date(BIZ_DATE + 'T00:00:00Z');
|
||||
d.setUTCDate(d.getUTCDate() + 1);
|
||||
const to = d.toISOString().slice(0, 10) + ' 00:00:00';
|
||||
|
||||
const computed = {};
|
||||
for (const f of FLOWS) {
|
||||
const req = new sql.Request();
|
||||
req.input('from', sql.DateTime, new Date(from));
|
||||
req.input('to', sql.DateTime, new Date(to));
|
||||
ids.forEach((v, i) => req.input('w' + i, sql.NVarChar, v));
|
||||
const r = await req.query(f.sql.replace('%W%', clause));
|
||||
r.recordset.forEach((row) => {
|
||||
computed[String(row.wmsCoId)] = computed[String(row.wmsCoId)] || {};
|
||||
computed[String(row.wmsCoId)][f.field] = Number(row.qty) || 0;
|
||||
});
|
||||
}
|
||||
|
||||
console.log('业务库口径反算 vs 聚水潭官方导出 —— ' + BIZ_DATE);
|
||||
console.log('（业务库仅能还原 4/12 列；采购退货/销售出仓为流出，官方值取绝对值比较）\n');
|
||||
const pad = (s, n) => String(s).padEnd(n);
|
||||
console.log(
|
||||
pad('仓库', 26) + pad('采购进仓 官方/业务库', 26) + pad('采购退货 官方/业务库', 26) + pad('销售出仓 官方/业务库', 26) + '销售退货 官方/业务库'
|
||||
);
|
||||
let diffs = { purchase_in: 0, sales_out: 0, purchase_return: 0, sales_return: 0 };
|
||||
for (const o of official) {
|
||||
const c = computed[String(o.wms_co_id)] || {};
|
||||
const cells = ['purchase_in', 'purchase_return', 'sales_out', 'sales_return'].map((k) => {
|
||||
const ov = Math.abs(Number(o[k]) || 0);
|
||||
const cv = Math.abs(Number(c[k]) || 0);
|
||||
if (Math.abs(ov - cv) > 0.005) diffs[k]++;
|
||||
return pad(ov + ' / ' + cv, 26);
|
||||
});
|
||||
console.log(pad(o.warehouse, 26) + cells.join(''));
|
||||
}
|
||||
console.log('\n不一致仓数：' + JSON.stringify(diffs) + ' / 共 ' + official.length + ' 仓');
|
||||
|
||||
// 期末 vs 业务库实时库存（仅作量级参考，业务库只有实时库存）
|
||||
const req2 = new sql.Request();
|
||||
ids.forEach((v, i) => req2.input('w' + i, sql.NVarChar, v));
|
||||
const inv = await req2.query(
|
||||
`SELECT wms_co_id AS wmsCoId, SUM(CAST(ISNULL(qty,0) AS DECIMAL(18,2))) AS qty
|
||||
FROM rpt.jst_inventoryInfo WHERE wms_co_id IN (${clause}) GROUP BY wms_co_id`
|
||||
);
|
||||
const invMap = {};
|
||||
inv.recordset.forEach((r) => (invMap[String(r.wmsCoId)] = Number(r.qty) || 0));
|
||||
console.log('\n【参考】官方' + BIZ_DATE + '期末 vs 业务库实时库存（业务库无历史快照，仅看量级）');
|
||||
official
|
||||
.slice()
|
||||
.sort((a, b) => Number(b.closing_qty) - Number(a.closing_qty))
|
||||
.slice(0, 8)
|
||||
.forEach((o) => {
|
||||
const live = invMap[String(o.wms_co_id)] || 0;
|
||||
const off = Number(o.closing_qty) || 0;
|
||||
const pct = off ? (((live - off) / off) * 100).toFixed(1) : '-';
|
||||
console.log(pad(o.warehouse, 26) + '期末 ' + off + ' | 实时 ' + live + ' | 差 ' + pct + '%');
|
||||
});
|
||||
|
||||
await sql.close();
|
||||
await sys.end();
|
||||
})().catch((e) => {
|
||||
console.log('FAIL', e.message);
|
||||
process.exit(1);
|
||||
});
|
||||
282
backend/src/api/pickingBinSalesAnalysis.js
Normal file
282
backend/src/api/pickingBinSalesAnalysis.js
Normal file
@ -0,0 +1,282 @@
|
||||
const express = require('express');
|
||||
const cloudDb = require('../config/cloudDatabase');
|
||||
const { DAILY_INVENTORY_BASELINE } = require('../config/dailyInventoryBaseline');
|
||||
const {
|
||||
PICKING_BIN_GRADE_RULES,
|
||||
PICKING_BIN_WINDOW_OPTIONS,
|
||||
PICKING_BIN_DEFAULT_DAYS,
|
||||
PICKING_BIN_TOTAL_LABEL,
|
||||
PICKING_BIN_BIN_TYPES,
|
||||
} = require('../config/pickingBinSalesAnalysis');
|
||||
const { fetchPickingBinSkuRows } = require('../services/pickingBinSalesAnalysis');
|
||||
const { sendWorkbookDownload } = require('../services/exportWorkbook');
|
||||
|
||||
const router = express.Router();
|
||||
|
||||
/** 库存衡量基准时刻：快照日次日 01:00（报表库每日凌晨跑批，标注 T-1） */
|
||||
const AS_OF_TIME = '01:00';
|
||||
|
||||
/** 结果缓存：一次要扫订单明细 + 仓位明细两张大表，避免连续刷新重复打库 */
|
||||
const RESULT_CACHE_TTL_MS = 5 * 60 * 1000;
|
||||
const cacheByKey = new Map();
|
||||
|
||||
const round2 = (v) => Math.round((Number(v) || 0) * 100) / 100;
|
||||
const round4 = (v) => Math.round((Number(v) || 0) * 10000) / 10000;
|
||||
|
||||
/** 快照日 + 次日 01:00 作为统一的统计基准时刻 */
|
||||
function nextDayAsOf(bizDate) {
|
||||
if (!bizDate || !/^\d{4}-\d{2}-\d{2}$/.test(bizDate)) return null;
|
||||
const d = new Date(`${bizDate}T00:00:00Z`);
|
||||
d.setUTCDate(d.getUTCDate() + 1);
|
||||
return `${d.toISOString().slice(0, 10)} ${AS_OF_TIME}`;
|
||||
}
|
||||
|
||||
/** 发货窗口：[asOf - N 天, asOf) */
|
||||
function buildShipWindow(asOf, days) {
|
||||
const to = new Date(`${asOf.slice(0, 10)}T${AS_OF_TIME}:00Z`);
|
||||
const from = new Date(to.getTime() - days * 86400000);
|
||||
const fmt = (dt) => dt.toISOString().slice(0, 19).replace('T', ' ');
|
||||
return { shipFrom: fmt(from), shipTo: fmt(to) };
|
||||
}
|
||||
|
||||
async function fetchLatestBizDate() {
|
||||
const rows = await cloudDb.query(
|
||||
`SELECT MAX(CONVERT(VARCHAR(10), date, 23)) AS d FROM rpt.pm_stockMonitor`
|
||||
);
|
||||
return rows[0]?.d || null;
|
||||
}
|
||||
|
||||
async function fetchBinTakenAt() {
|
||||
const rows = await cloudDb.query(
|
||||
`SELECT MAX(updateTime) AS takenAt FROM rpt.jst_packInventoryInfo`
|
||||
);
|
||||
return rows[0]?.takenAt || null;
|
||||
}
|
||||
|
||||
/** 解析并收敛统计窗口天数，只允许白名单内的取值 */
|
||||
function resolveDays(raw) {
|
||||
const n = Number.parseInt(String(raw ?? ''), 10);
|
||||
return PICKING_BIN_WINDOW_OPTIONS.includes(n) ? n : PICKING_BIN_DEFAULT_DAYS;
|
||||
}
|
||||
|
||||
/** 解析仓库过滤，只允许出现在仓库清单里的 wmsCoId，防止任意拼参 */
|
||||
function resolveWarehouses(raw) {
|
||||
const known = DAILY_INVENTORY_BASELINE.filter((b) => b.wmsCoId != null);
|
||||
const requested = String(raw || '')
|
||||
.split(',')
|
||||
.map((v) => v.trim())
|
||||
.filter(Boolean);
|
||||
const picked = requested.length > 0
|
||||
? known.filter((b) => requested.includes(String(b.wmsCoId)))
|
||||
: known;
|
||||
return picked;
|
||||
}
|
||||
|
||||
const GRADE_MAP = new Map(PICKING_BIN_GRADE_RULES.map((g) => [g.key, g]));
|
||||
|
||||
/** 明细行按「仓库 × 商品编码」去重后的商品个数 */
|
||||
function dedupeCount(list) {
|
||||
return new Set(list.map((it) => `${it.wmsCoId}::${it.skuId}`)).size;
|
||||
}
|
||||
|
||||
/**
|
||||
* GET /api/picking-bin-sales-analysis
|
||||
* 拣选库位动销分析：仓库 × 仓位 × 商品编码 → 库存数量 / 商品销量 / 商品日销 / 动销等级
|
||||
* 动销等级按「商品日销」判定（商品整档销量 ÷ 天数），不做仓位分摊。
|
||||
*/
|
||||
router.get('/', async (req, res) => {
|
||||
try {
|
||||
const force = String(req.query?.refresh || '') === '1';
|
||||
const days = resolveDays(req.query?.days);
|
||||
const warehouses = resolveWarehouses(req.query?.wmsCoId);
|
||||
const wmsCoIds = warehouses.map((b) => String(b.wmsCoId));
|
||||
|
||||
const cacheKey = `${days}::${wmsCoIds.join('|')}`;
|
||||
const latestBizDate = await fetchLatestBizDate().catch(() => null);
|
||||
const cached = cacheByKey.get(cacheKey);
|
||||
if (!force && cached && cached.latestBizDate === latestBizDate
|
||||
&& Date.now() - cached.at < RESULT_CACHE_TTL_MS) {
|
||||
return res.json({ success: true, data: cached.payload, cached: true });
|
||||
}
|
||||
|
||||
const asOf = nextDayAsOf(latestBizDate);
|
||||
if (!asOf) {
|
||||
return res.status(503).json({
|
||||
success: false,
|
||||
error: '业务库暂无库存监控数据，无法定位统计基准日',
|
||||
});
|
||||
}
|
||||
|
||||
const { shipFrom, shipTo } = buildShipWindow(asOf, days);
|
||||
const [rows, takenAt] = await Promise.all([
|
||||
fetchPickingBinSkuRows(cloudDb, wmsCoIds, { shipFrom, shipTo, days }),
|
||||
fetchBinTakenAt().catch(() => null),
|
||||
]);
|
||||
|
||||
const whMap = new Map(warehouses.map((b) => [String(b.wmsCoId), b.warehouse]));
|
||||
const items = rows.map((r) => ({
|
||||
wmsCoId: r.wmsCoId,
|
||||
binName: r.binName,
|
||||
binType: r.binType,
|
||||
skuId: r.skuId,
|
||||
warehouse: whMap.get(r.wmsCoId) || r.wmsCoId,
|
||||
stockQty: r.stockQty,
|
||||
skuCount: r.skuCount,
|
||||
salesQty: round2(r.salesQty),
|
||||
dailySales: round4(r.dailySales),
|
||||
gradeKey: r.gradeKey,
|
||||
gradeLabel: GRADE_MAP.get(r.gradeKey)?.label || '',
|
||||
}));
|
||||
|
||||
// 每个「仓库 × 商品」只计一次销量，避免同一商品跨多个仓位被重复累加
|
||||
const skuSalesMap = new Map();
|
||||
rows.forEach((r) => {
|
||||
const key = `${r.wmsCoId}::${r.skuId}`;
|
||||
if (!skuSalesMap.has(key)) skuSalesMap.set(key, r.salesQty);
|
||||
});
|
||||
const dedupeSales = (list) => {
|
||||
const seen = new Set();
|
||||
return list.reduce((acc, it) => {
|
||||
const key = `${it.wmsCoId}::${it.skuId}`;
|
||||
if (seen.has(key)) return acc;
|
||||
seen.add(key);
|
||||
return acc + (skuSalesMap.get(key) || 0);
|
||||
}, 0);
|
||||
};
|
||||
|
||||
// 各动销等级汇总：商品数按「仓库 × 商品」去重，库存按明细行求和（无重复）
|
||||
const summary = PICKING_BIN_GRADE_RULES.map((grade) => {
|
||||
const hit = items.filter((it) => it.gradeKey === grade.key);
|
||||
const salesQty = round2(dedupeSales(hit));
|
||||
return {
|
||||
key: grade.key,
|
||||
label: grade.label,
|
||||
tip: grade.tip,
|
||||
rowCount: hit.length,
|
||||
skuCount: dedupeCount(hit),
|
||||
stockQty: round2(hit.reduce((acc, it) => acc + it.stockQty, 0)),
|
||||
salesQty,
|
||||
dailySales: round4(salesQty / days),
|
||||
};
|
||||
});
|
||||
|
||||
const dedupeAll = new Set(items.map((it) => `${it.wmsCoId}::${it.skuId}`));
|
||||
const totalSales = round2(dedupeSales(items));
|
||||
const total = {
|
||||
key: '__total__',
|
||||
label: PICKING_BIN_TOTAL_LABEL,
|
||||
rowCount: items.length,
|
||||
skuCount: dedupeAll.size,
|
||||
stockQty: round2(summary.reduce((acc, s) => acc + s.stockQty, 0)),
|
||||
salesQty: totalSales,
|
||||
dailySales: round4(totalSales / days),
|
||||
};
|
||||
|
||||
const payload = {
|
||||
days,
|
||||
windowOptions: PICKING_BIN_WINDOW_OPTIONS,
|
||||
binTypes: [...PICKING_BIN_BIN_TYPES],
|
||||
binTypeOptions: [...PICKING_BIN_BIN_TYPES],
|
||||
latestBizDate,
|
||||
asOf,
|
||||
shipWindow: { shipFrom, shipTo, days },
|
||||
takenAt,
|
||||
grades: PICKING_BIN_GRADE_RULES.map((g) => ({ key: g.key, label: g.label, tip: g.tip })),
|
||||
totalLabel: PICKING_BIN_TOTAL_LABEL,
|
||||
warehouses: warehouses.map((b) => ({ wmsCoId: String(b.wmsCoId), warehouse: b.warehouse })),
|
||||
items,
|
||||
summary,
|
||||
total,
|
||||
};
|
||||
|
||||
cacheByKey.set(cacheKey, { latestBizDate, at: Date.now(), payload });
|
||||
return res.json({ success: true, data: payload });
|
||||
} catch (error) {
|
||||
console.error('[picking-bin-sales-analysis] 查询失败:', error.message);
|
||||
return res.status(500).json({
|
||||
success: false,
|
||||
error: '拣选库位动销分析数据查询失败',
|
||||
detail: error.message,
|
||||
});
|
||||
}
|
||||
});
|
||||
|
||||
/**
|
||||
* GET /api/picking-bin-sales-analysis/export
|
||||
* 导出库位明细（只含拣选位）。query: days、wmsCoId（逗号分隔）、grade（逗号分隔）、format（xlsx|csv）
|
||||
*/
|
||||
router.get('/export', async (req, res) => {
|
||||
try {
|
||||
const days = resolveDays(req.query?.days);
|
||||
const warehouses = resolveWarehouses(req.query?.wmsCoId);
|
||||
const wmsCoIds = warehouses.map((b) => String(b.wmsCoId));
|
||||
const format = String(req.query?.format || 'xlsx').toLowerCase() === 'csv' ? 'csv' : 'xlsx';
|
||||
|
||||
const grades = String(req.query?.grade || '')
|
||||
.split(',')
|
||||
.map((v) => v.trim())
|
||||
.filter((v) => GRADE_MAP.has(v));
|
||||
|
||||
const latestBizDate = await fetchLatestBizDate().catch(() => null);
|
||||
const asOf = nextDayAsOf(latestBizDate);
|
||||
if (!asOf) {
|
||||
return res.status(503).json({
|
||||
success: false,
|
||||
error: '业务库暂无库存监控数据，无法定位统计基准日',
|
||||
});
|
||||
}
|
||||
|
||||
const { shipFrom, shipTo } = buildShipWindow(asOf, days);
|
||||
// 导出按「仓位 × 商品编码」拆行：一个仓位有 N 个商品就输出 N 行
|
||||
const rows = await fetchPickingBinSkuRows(cloudDb, wmsCoIds, { shipFrom, shipTo, days });
|
||||
|
||||
const whMap = new Map(warehouses.map((b) => [String(b.wmsCoId), b.warehouse]));
|
||||
const filtered = grades.length > 0
|
||||
? rows.filter((r) => grades.includes(r.gradeKey))
|
||||
: rows;
|
||||
|
||||
const sheetRows = filtered.map((r) => [
|
||||
whMap.get(r.wmsCoId) || r.wmsCoId,
|
||||
r.binName,
|
||||
r.binType,
|
||||
r.skuId,
|
||||
r.stockQty,
|
||||
r.skuCount,
|
||||
round2(r.salesQty),
|
||||
round4(r.allocatedSalesQty),
|
||||
round4(r.dailySales),
|
||||
GRADE_MAP.get(r.gradeKey)?.label || r.gradeKey,
|
||||
days,
|
||||
latestBizDate || '',
|
||||
]);
|
||||
|
||||
const fileName = `拣选库位动销分析-${(latestBizDate || '').replace(/-/g, '') || '未知日期'}`;
|
||||
const { totalRows } = sendWorkbookDownload(res, {
|
||||
fileName,
|
||||
format,
|
||||
sheets: [{
|
||||
name: '库位动销明细',
|
||||
header: [
|
||||
'仓库', '仓位', '类型', '商品编码', '库存数量', '仓位SKU数',
|
||||
`商品销量(近${days}天)`, `分摊销量(近${days}天)`, '商品日销',
|
||||
'动销等级', '统计天数', '统计基准日',
|
||||
],
|
||||
rows: sheetRows,
|
||||
}],
|
||||
});
|
||||
console.log(`[picking-bin-sales-analysis] 导出: ${totalRows} 行, 仓库 ${wmsCoIds.length} 个, 仅拣选位, 窗口 ${days} 天`);
|
||||
return null;
|
||||
} catch (error) {
|
||||
console.error('[picking-bin-sales-analysis] 导出失败:', error.message);
|
||||
if (!res.headersSent) {
|
||||
return res.status(500).json({
|
||||
success: false,
|
||||
error: '拣选库位动销分析导出失败',
|
||||
detail: error.message,
|
||||
});
|
||||
}
|
||||
return null;
|
||||
}
|
||||
});
|
||||
|
||||
module.exports = router;
|
||||
@ -7,7 +7,8 @@ const {
|
||||
STOCK_SALES_WINDOW_OPTIONS,
|
||||
STOCK_SALES_DEFAULT_DAYS,
|
||||
} = require('../config/stockSalesAnalysis');
|
||||
const { fetchGradeRows } = require('../services/stockSalesAnalysis');
|
||||
const { fetchGradeRows, fetchZeroSaleSkuRows } = require('../services/stockSalesAnalysis');
|
||||
const { sendWorkbookDownload } = require('../services/exportWorkbook');
|
||||
|
||||
const router = express.Router();
|
||||
|
||||
@ -202,4 +203,79 @@ router.get('/', async (req, res) => {
|
||||
}
|
||||
});
|
||||
|
||||
/**
|
||||
* GET /api/stock-sales-analysis/zero-sales-export
|
||||
* 导出「销售=0」档的 SKU 级明细。
|
||||
* query: days（统计窗口）、wmsCoId（可选，逗号分隔，不传=全部仓库）、format（xlsx|csv，默认 xlsx）
|
||||
*/
|
||||
router.get('/zero-sales-export', async (req, res) => {
|
||||
try {
|
||||
const days = resolveDays(req.query?.days);
|
||||
const format = String(req.query?.format || 'xlsx').toLowerCase() === 'csv' ? 'csv' : 'xlsx';
|
||||
const latestBizDate = await fetchLatestBizDate().catch(() => null);
|
||||
|
||||
const asOf = nextDayAsOf(latestBizDate);
|
||||
if (!asOf) {
|
||||
return res.status(503).json({
|
||||
success: false,
|
||||
error: '业务库暂无库存监控数据，无法定位统计基准日',
|
||||
});
|
||||
}
|
||||
|
||||
const { shipFrom, shipTo } = buildShipWindow(asOf, days);
|
||||
|
||||
// 仓库过滤：只允许出现在仓库清单里的 wmsCoId，防止任意拼参
|
||||
const known = new Map(
|
||||
DAILY_INVENTORY_BASELINE.filter((b) => b.wmsCoId != null)
|
||||
.map((b) => [String(b.wmsCoId), b])
|
||||
);
|
||||
const requested = String(req.query?.wmsCoId || '')
|
||||
.split(',')
|
||||
.map((v) => v.trim())
|
||||
.filter((v) => known.has(v));
|
||||
const wmsCoIds = requested.length > 0 ? requested : [...known.keys()];
|
||||
|
||||
const rows = await fetchZeroSaleSkuRows(cloudDb, wmsCoIds, { shipFrom, shipTo, days });
|
||||
|
||||
const sheetRows = rows.map((r) => {
|
||||
const base = known.get(r.wmsCoId);
|
||||
return [
|
||||
base?.warehouse || r.wmsCoId,
|
||||
r.skuId,
|
||||
r.skuName,
|
||||
r.binName,
|
||||
r.stockQty,
|
||||
r.salesQty,
|
||||
days,
|
||||
latestBizDate || '',
|
||||
];
|
||||
});
|
||||
|
||||
const scopeLabel = requested.length === 1 ? `-${known.get(requested[0]).warehouse}` : '';
|
||||
const fileName = `库存动销分析-销售0明细${scopeLabel}-${(latestBizDate || '').replace(/-/g, '') || '未知日期'}`;
|
||||
const { totalRows } = sendWorkbookDownload(res, {
|
||||
fileName,
|
||||
format,
|
||||
sheets: [{
|
||||
name: '销售=0明细',
|
||||
header: ['仓库', 'SKU编码', 'SKU名称', '系统仓位', '库存数量', `销量(近${days}天)`, '统计天数', '统计基准日'],
|
||||
rows: sheetRows,
|
||||
}],
|
||||
});
|
||||
console.log(`[stock-sales-analysis] 销售=0明细导出: ${totalRows} 行, 仓库 ${wmsCoIds.length} 个, 窗口 ${days} 天`);
|
||||
return null;
|
||||
} catch (error) {
|
||||
console.error('[stock-sales-analysis] 销售=0明细导出失败:', error.message);
|
||||
// 响应可能已部分写出（sendWorkbookDownload 之后一般不会抛），仅在未发送时回 JSON
|
||||
if (!res.headersSent) {
|
||||
return res.status(500).json({
|
||||
success: false,
|
||||
error: '销售=0明细导出失败',
|
||||
detail: error.message,
|
||||
});
|
||||
}
|
||||
return null;
|
||||
}
|
||||
});
|
||||
|
||||
module.exports = router;
|
||||
|
||||
62
backend/src/api/warehouseShipTimeliness.js
Normal file
62
backend/src/api/warehouseShipTimeliness.js
Normal file
@ -0,0 +1,62 @@
|
||||
'use strict';
|
||||
|
||||
/**
|
||||
* 仓储中心 24 小时发货时效 —— HTTP 路由（2026-10-20 新增）
|
||||
* ────────────────────────────────────────────────────────────────────────────
|
||||
* GET /api/warehouse-ship-timeliness/list
|
||||
* query:
|
||||
* round 'NOON' | 'AFTERNOON' 手动指定核验轮次；不传按当前时刻自动判定
|
||||
* limit 明细条数上限（默认 500，最大 2000）
|
||||
*
|
||||
* GET /api/warehouse-ship-timeliness/rounds
|
||||
* 只返回今日两轮的描述（页面顶部提示用，不打订单表，秒回）
|
||||
*
|
||||
* 挂载见 src/index.js：`/api/warehouse-ship-timeliness` + authMiddleware
|
||||
*/
|
||||
|
||||
const express = require('express');
|
||||
const { getShipTimeliness } = require('../services/warehouseShipTimelinessService');
|
||||
const { describeRounds, CENTERS } = require('../services/warehouseShipTimelinessRegistry');
|
||||
|
||||
const router = express.Router();
|
||||
|
||||
function sendError(res, error, fallback) {
|
||||
const status = Number(error?.statusCode) || 500;
|
||||
console.error('[WarehouseShipTimeliness]', {
|
||||
message: error?.message || fallback,
|
||||
code: error?.code || null,
|
||||
status,
|
||||
});
|
||||
res.status(status).json({ success: false, error: error.message || fallback });
|
||||
}
|
||||
|
||||
// 时效总览：每个主仓「超时未发」数量 + 超时订单明细
|
||||
router.get('/list', async (req, res) => {
|
||||
try {
|
||||
const data = await getShipTimeliness(req.query || {});
|
||||
res.json({ success: true, data });
|
||||
} catch (error) {
|
||||
sendError(res, error, '加载仓储中心发货时效失败');
|
||||
}
|
||||
});
|
||||
|
||||
// 仅轮次信息（不查订单）
|
||||
router.get('/rounds', async (_req, res) => {
|
||||
try {
|
||||
res.json({
|
||||
success: true,
|
||||
data: {
|
||||
rounds: describeRounds(new Date()),
|
||||
centers: CENTERS.map((c) => ({
|
||||
key: c.key,
|
||||
name: c.name,
|
||||
placeholder: Boolean(c.placeholder),
|
||||
})),
|
||||
},
|
||||
});
|
||||
} catch (error) {
|
||||
sendError(res, error, '加载核验轮次失败');
|
||||
}
|
||||
});
|
||||
|
||||
module.exports = router;
|
||||
@ -764,7 +764,20 @@ async function connectWithCandidate(candidate) {
|
||||
}
|
||||
|
||||
async function queryWithCandidateConnection(candidate, sqlText, params = {}, options = {}) {
|
||||
const nextPool = new sql.ConnectionPool(candidate.mssqlConfig);
|
||||
// request.timeout（请求级）在 mssql 12.x 上实测不生效（始终按池配置的默认值掐断），
|
||||
// 慢查询必须通过「池配置级 requestTimeout」放宽，这里允许调用方覆盖候选连接的池级超时。
|
||||
let mssqlConfig = candidate.mssqlConfig;
|
||||
const poolRequestTimeout = parsePositiveInt(options.connectRequestTimeoutMs, 0);
|
||||
if (poolRequestTimeout > 0) {
|
||||
mssqlConfig = {
|
||||
...candidate.mssqlConfig,
|
||||
options: {
|
||||
...candidate.mssqlConfig.options,
|
||||
requestTimeout: poolRequestTimeout,
|
||||
},
|
||||
};
|
||||
}
|
||||
const nextPool = new sql.ConnectionPool(mssqlConfig);
|
||||
|
||||
try {
|
||||
await nextPool.connect();
|
||||
@ -1093,6 +1106,28 @@ const queryWithFreshConnection = async (sqlText, params = {}, options = {}) => {
|
||||
return [];
|
||||
};
|
||||
|
||||
/**
|
||||
* 慢查询专用入口：走独立临时连接，池级 requestTimeout 放宽到 SLOW_QUERY_POOL_TIMEOUT_MS。
|
||||
* 背景：mssql 12.x 的 request.timeout（请求级）不生效，超过池默认 60s 的查询必须用
|
||||
* 池配置级 requestTimeout 才能放宽。适用场景：动销分析这类多表大窗口聚合。
|
||||
*/
|
||||
const SLOW_QUERY_POOL_TIMEOUT_MS = parsePositiveInt(
|
||||
process.env.CLOUD_DB_SLOW_QUERY_TIMEOUT_MS,
|
||||
300000
|
||||
);
|
||||
|
||||
const querySlow = async (sqlText, params = {}, options = {}) => {
|
||||
const poolRequestTimeout = parsePositiveInt(
|
||||
options.connectRequestTimeoutMs,
|
||||
SLOW_QUERY_POOL_TIMEOUT_MS
|
||||
);
|
||||
return queryWithFreshConnection(sqlText, params, {
|
||||
...options,
|
||||
connectRequestTimeoutMs: poolRequestTimeout,
|
||||
requestTimeout: poolRequestTimeout,
|
||||
});
|
||||
};
|
||||
|
||||
/**
|
||||
* 测试连接
|
||||
*/
|
||||
@ -1143,6 +1178,7 @@ const reload = async () => {
|
||||
module.exports = {
|
||||
getPool,
|
||||
query,
|
||||
querySlow,
|
||||
queryWithFreshConnection,
|
||||
createPinnedQueryClient,
|
||||
test,
|
||||
|
||||
78
backend/src/config/pickingBinSalesAnalysis.js
Normal file
78
backend/src/config/pickingBinSalesAnalysis.js
Normal file
@ -0,0 +1,78 @@
|
||||
/**
|
||||
* 拣选库位动销分析 —— 口径配置（纯规则，不含业务数值）
|
||||
*
|
||||
* 一级菜单：大货库存监控
|
||||
* 二级菜单：拣选库位动销分析
|
||||
*
|
||||
* 表头（需求表原列为 所在仓库 | 仓位 | 数量 | 类型 | 动销等级，这里做了扩充与优化）：
|
||||
* 仓库 | 仓位 | 类型 | 商品编码 | 库存数量 | 近N天销量 | 日销 | 动销等级
|
||||
*
|
||||
* 粒度：仓位 × 商品编码（一个仓位有 N 个商品就输出 N 行），列表与导出共用。
|
||||
*
|
||||
* 口径：
|
||||
* 库存数量：rpt.jst_packInventoryInfo 按 wms_co_id + 仓位 + sku_id 汇总 qty
|
||||
* 统计范围：只统计真实拣选仓位（pack_type=Bin 且 bin_group 为空）；
|
||||
* 箱记录（pack_type=Pack）与存货位（bin_group=存货）不纳入
|
||||
* 销量： 4 个来源分支，均按 send_date 窗口（详见 services/pickingBinSalesAnalysis.js）：
|
||||
* ① jst_Orders 普通行；② jst_Orders 组合装行 → jst_comboProductItem 展开为子件；
|
||||
* ③ jst_qmcust_orderlist（全民客户 B2B 分销）普通行（排除拆单父单 Split）；
|
||||
* ④ qmcust 组合装行 → 同样展开。
|
||||
* 日销： 商品窗口销量 / 窗口天数（整档，不按仓位分摊）
|
||||
* 等级： 严格按商品日销判定，同一商品占多少个仓位都是同一等级
|
||||
*
|
||||
* 【为什么不分摊来定等级】同一 SKU 常常同时占用多个同类型仓位（崔桥工厂仓实测 3290 个
|
||||
* 拣选 SKU 中 1431 个跨多个拣选位，最多 24 个）。若把销量摊到仓位再定等级，
|
||||
* 一件爆款商品放在 10 个仓位就会被算成日销 1/10 的滞销品 —— 判断的是「摆法」而不是「商品」。
|
||||
* 因此等级一律以商品自身的销售速度为准。
|
||||
*
|
||||
* 【分摊销量列】导出仍附带「分摊销量」= 商品销量 ÷ 其占用的同类型仓位数，
|
||||
* 供需要按行求和的场景使用：恒等式（已实测校验，差异 0）
|
||||
* 全部行分摊销量之和 = 有库位记录的那些 SKU 的销量之和。
|
||||
* 注意它小于「全仓销量」：jst_packInventoryInfo 只覆盖部分 SKU 的仓位数据，
|
||||
* 没有仓位记录的 SKU 的销量不会体现在任何库位上。
|
||||
*/
|
||||
|
||||
const PICKING_BIN_LEVEL1 = '大货库存监控';
|
||||
const PICKING_BIN_LEVEL2 = '拣选库位动销分析';
|
||||
|
||||
/** 可选的统计窗口天数；默认 30 天（需求指定按近 30 天销量划分动销等级） */
|
||||
const PICKING_BIN_WINDOW_OPTIONS = [7, 15, 30, 60];
|
||||
const PICKING_BIN_DEFAULT_DAYS = 30;
|
||||
|
||||
/**
|
||||
* 仓位类型：本功能只统计真实拣选仓位（pack_type=Bin 且 bin_group 为空）。
|
||||
* 箱记录（pack_type=Pack）和存货位（bin_group=存货）不纳入动销等级统计，
|
||||
* 取数在仓位库存表上直接过滤，
|
||||
* 页面也不再提供类型切换。
|
||||
*/
|
||||
const BIN_TYPE_PICKING = '拣选';
|
||||
/** 固定统计范围：仅拣选位（保持数组形式，便于接口字段与后续扩展） */
|
||||
const PICKING_BIN_BIN_TYPES = [BIN_TYPE_PICKING];
|
||||
|
||||
/**
|
||||
* 动销等级（顺序即展示顺序）。判定依据为「商品日销」= 商品窗口整档销量 ÷ 天数，
|
||||
* 不做仓位分摊，同一商品在所有仓位显示同一等级。
|
||||
* lower 为日销下界（含），upper 为日销上界（不含）；null 表示不限制。
|
||||
* zero 档为「窗口内无销量」，由 salesQty <= 0 兜底。
|
||||
*/
|
||||
const PICKING_BIN_GRADE_RULES = [
|
||||
{ key: 'hot', label: '爆款：日销≥20', lower: 20, upper: null, tip: '商品日均销量 ≥ 20 件' },
|
||||
{ key: 'best', label: '热卖款：10≤日销＜20', lower: 10, upper: 20, tip: '商品日均销量 10 ~ 20 件（含 10，不含 20）' },
|
||||
{ key: 'normal', label: '平销款：3≤日销＜10', lower: 3, upper: 10, tip: '商品日均销量 3 ~ 10 件（含 3，不含 10）' },
|
||||
{ key: 'slow1', label: '滞销款-1：1≤日销＜3', lower: 1, upper: 3, tip: '商品日均销量 1 ~ 3 件（含 1，不含 3）' },
|
||||
{ key: 'slow2', label: '滞销款-2：0＜日销＜1', lower: 0, upper: 1, tip: '商品日均销量大于 0 且小于 1 件' },
|
||||
{ key: 'zero', label: '销售=0', lower: 0, upper: 0, tip: '统计窗口内该商品无销量（仍占用库存）' },
|
||||
];
|
||||
|
||||
const PICKING_BIN_TOTAL_LABEL = '汇总';
|
||||
|
||||
module.exports = {
|
||||
PICKING_BIN_LEVEL1,
|
||||
PICKING_BIN_LEVEL2,
|
||||
PICKING_BIN_WINDOW_OPTIONS,
|
||||
PICKING_BIN_DEFAULT_DAYS,
|
||||
BIN_TYPE_PICKING,
|
||||
PICKING_BIN_BIN_TYPES,
|
||||
PICKING_BIN_GRADE_RULES,
|
||||
PICKING_BIN_TOTAL_LABEL,
|
||||
};
|
||||
@ -45,6 +45,11 @@ const SIDEBAR_PAGES = [
|
||||
pageName: '大货库存监控 · 库存动销分析',
|
||||
path: '/planning/operation/stock-sales-analysis',
|
||||
}),
|
||||
buildPlanningPage({
|
||||
pageKey: 'picking_bin_sales_analysis',
|
||||
pageName: '大货库存监控 · 拣选库位动销分析',
|
||||
path: '/planning/operation/picking-bin-sales-analysis',
|
||||
}),
|
||||
{
|
||||
pageKey: 'settings',
|
||||
pageName: '系统设置',
|
||||
|
||||
@ -35,9 +35,11 @@ const promotionModuleRoutes = require('./api/promotionModule');
|
||||
const productMonitoringRoutes = require('./api/productMonitoring');
|
||||
const modularStockMonitorRoutes = require('./api/modularStockMonitor');
|
||||
const mkhBomRoutes = require('./api/mkhBom');
|
||||
const warehouseShipTimelinessRoutes = require('./api/warehouseShipTimeliness');
|
||||
const dailyInventoryRoutes = require('./api/dailyInventory');
|
||||
const dailyInventoryNotificationRoutes = require('./api/dailyInventoryNotification');
|
||||
const stockSalesAnalysisRoutes = require('./api/stockSalesAnalysis');
|
||||
const pickingBinSalesAnalysisRoutes = require('./api/pickingBinSalesAnalysis');
|
||||
const returnInventoryWarningRoutes = require('./api/returnInventoryWarning');
|
||||
const returnMonitorRoutes = require('./api/returnMonitor');
|
||||
const returnStockWarningRoutes = require('./api/returnStockWarning');
|
||||
@ -671,9 +673,12 @@ async function startServer(options = {}) {
|
||||
cubejsApp.use('/api/product-monitoring', authMiddleware, productMonitoringRoutes);
|
||||
cubejsApp.use('/api/modular-stock-monitor', authMiddleware, modularStockMonitorRoutes);
|
||||
cubejsApp.use('/api/mkh-bom', authMiddleware, mkhBomRoutes);
|
||||
// 仓储中心 24 小时发货时效（2026-10-20 新增）：列表左侧侧栏，11 个主仓超时未发
|
||||
cubejsApp.use('/api/warehouse-ship-timeliness', authMiddleware, warehouseShipTimelinessRoutes);
|
||||
cubejsApp.use('/api/daily-inventory', authMiddleware, dailyInventoryRoutes);
|
||||
cubejsApp.use('/api/daily-inventory-notification', authMiddleware, dailyInventoryNotificationRoutes);
|
||||
cubejsApp.use('/api/stock-sales-analysis', authMiddleware, stockSalesAnalysisRoutes);
|
||||
cubejsApp.use('/api/picking-bin-sales-analysis', authMiddleware, pickingBinSalesAnalysisRoutes);
|
||||
cubejsApp.use('/api/return-inventory-warning', authMiddleware, returnInventoryWarningRoutes);
|
||||
// 崔桥工厂仓客退：退货入库核销 + 每日库存监控
|
||||
cubejsApp.use('/api/return-monitor', authMiddleware, returnMonitorRoutes);
|
||||
|
||||
@ -51,15 +51,18 @@
|
||||
// 该表查不到 → 仓位为空，前端显示「未维护」。**不做任何回退**。
|
||||
// · 列表展开（/detail）、单品明细、导出配件明细共用 loadComponentLocations()，
|
||||
// 只打一条查询，按编码缓存 10 分钟。
|
||||
// ⚠️ 关联列别踩（2026-09-17 实测）：仓名必须用 `jst_warehouseInfo.Id = p.wh_id`；
|
||||
// 该表 `wms_co_id` **全表 NULL**，用 `w.wms_co_id = p.wms_co_id` 关联 85,152 行全部取不到仓名。
|
||||
// ⚠️ 2026-09-17 全量复核（别再重复排查）：该表全表 85,152 行，但只有 **3 个箱**
|
||||
// （914138566000001000 / …1003 / …4000）、2 个仓库（wh_id=1 常州简尚家居 85,145 行、
|
||||
// wh_id=4 简尚-新苏北仓 7 行）；`bin` / `bin_group` **全表为空**。
|
||||
// 且表内容与 MKH 配件**零交集**：正式 BOM 的 715 个配件编码在该表一个都查不到
|
||||
// （精确 + `code-%` 前缀都是 0；箱表里是 DNZ0135xx/0199xx/0203xx 老型号半成品）。
|
||||
// → MKH 配件仓位列当前**必然全部显示「未维护」**，这是数据源现状，不是代码问题；
|
||||
// 需业务在聚水潭给配件做装箱/维护货位后才会自动出现（代码不用再动）。
|
||||
// ⚠️ 关联列（09-19 修正，PR #22）：仓名用 `jst_warehouseInfo.wms_co_id = p.wms_co_id`
|
||||
// —— 09-19 实测该表 30,236 行 `wms_co_id` **0 NULL、14 个仓取值**。
|
||||
// ~~旧结论「该表 wms_co_id 全表 NULL，必须用 jst_warehouseInfo.Id = p.wh_id」~~
|
||||
// 只对 09-17 的表状态成立；误用 `w.Id = p.wh_id`（wh_id 仅 6 个取值、90% 为 1）
|
||||
// 会让 727 个配件的仓位仓名被统一显示成「常州简尚家居有限公司」。
|
||||
// 🔥 该表 **小时级增量刷新**，且 2026-09-18 被上游整表重建 ——
|
||||
// 09-17 的「全表 85,152 行、仅 3 个箱、`bin` / `bin_group` 全表为空、与 MKH 配件
|
||||
// **零交集**」这组结论**已全部失效**，别再照抄。
|
||||
// 09-19 实测：目标仓 794 个 SKU 命中 **727** 个（真货位如 ZT-8 / BCP01-01-1）；
|
||||
// 2026-09-30 实测：本配件仓（15718196）箱表有库存（bin 非空）的 SKU **734** 个。
|
||||
// → 配件货位 / 箱库存数据是真实存在并持续刷新的；
|
||||
// 该结论同时修正 ⑪ 的配件库存来源（箱表可作兜底，见下）。
|
||||
//
|
||||
// ⑥ 配件库存 —— 业务库 rpt.jst_inventoryInfo 按 sku_id 汇总（**商品**库存，不是配件仓位）。
|
||||
//
|
||||
@ -67,8 +70,11 @@
|
||||
// 规则：近 15 天日销 > 0 且 现有库存 < 3 天日销 → 预警补货
|
||||
// · 近15天日销 rpt.pm_stockMonitor.fifteenAvgSale
|
||||
// （warehouse = '简尚-崔桥工厂品牌仓'，按 date 取最新一条快照）
|
||||
// · 现有库存 rpt.jst_inventoryInfo（wms_co_id = 15870495 品牌仓）实时库存；
|
||||
// 该仓对 MKH 成品只覆盖约 256 / 4408 条（2026-09-16 快照；成品总数口径见 ①），
|
||||
// · 现有库存 rpt.jst_inventoryInfo（wms_co_id **仓白名单**）实时库存；
|
||||
// 🔥 2026-10-17 修正为**多仓求和**（原来是单仓 15870495）：商品档案 other_3 只记
|
||||
// 「归属仓」，实物会整批搬到同体系其它发货仓 —— 只查归属仓会把有货的算成 0。
|
||||
// 实测新仓「简尚-横林二仓品牌仓」(16132681，10-13 建仓) 58 个 MKH 成品 / 525 件
|
||||
// 全在它名下、归属仓为 0 → 5 个误报缺货 + 2 个误报预警。白名单见 BRAND_WMS_CO_IDS。
|
||||
// 缺失时回退监控快照 jsStock
|
||||
// · 结余库存 = max(0, 现有库存 − 待发货订单)
|
||||
// ⚠️ 2026-09-18 起：已下单未发货的部分已被占用，**所有口径一律先扣待发订单**
|
||||
@ -212,6 +218,28 @@
|
||||
// 若日后业务反馈「库存大面积停在某个时间点」，**先查 inventoryInfo 的
|
||||
// MAX(updateTime)**，再看箱表的，两者一对即可判断是上游停了还是本页取数问题。
|
||||
//
|
||||
// ── ⑦-e 成品库存多仓合并（2026-10-17 新增）────────────────────────
|
||||
// 业务原话：「MKHDNZ0409260600788 库存在横林二仓品牌仓，**重新加了一个品牌仓库存**，
|
||||
// 需要同步这个仓库库存」。
|
||||
// 🔥 根因：**商品档案的 `other_3` 记录的是「归属仓」，不是「实物所在仓」**。
|
||||
// 归属仓仍是「简尚-崔桥工厂品牌仓」(15870495)，但实物被整批搬到 10-13 新建的
|
||||
// 「简尚-横林二仓品牌仓」(16132681) —— 归属仓库存为 0，新仓有货。
|
||||
// 而 ⑦-d 的取数只看 `wms_co_id = 15870495` 单仓 → 该批商品库存全被算成 0。
|
||||
// 实测：新仓 58 个 SKU 全是 MKH 成品（other_3 全为崔桥品牌仓），合计 525 件；
|
||||
// 线上 49 个 SKU 库存值错误，其中 **5 个误报「缺货」、2 个误报「预警」**。
|
||||
// 规则：`jst_inventoryInfo` / `jst_packInventoryInfo` 都按 **仓白名单 `IN (...)`**
|
||||
// 取数，并 **按 SKU 多仓求和**（同一批货可能同时分散在多仓）。
|
||||
// · 白名单常量：BRAND_WMS_CO_IDS（主仓 + 横林二仓品牌仓）
|
||||
// · ⚠️ 原来的「按 updateTime DESC 取首条」在单仓时是去重，多仓时会**只留一个仓**，
|
||||
// 必须换成 SUM + GROUP BY（本次改动同时修掉这个隐患）。
|
||||
// · ⚠️ 「求和」而非「取最大」：两仓是独立仓位，同一 SKU 同批货不会重复记账
|
||||
// （实测该 SKU 两仓 bin 分别为 `PP01-15-22` 与空，无同箱重复）。
|
||||
// 影响面（2026-10-17 实测）：58 个 SKU 受影响，其中 49 个库存值变化；
|
||||
// 预警 30 → 25、缺货 21 → 16（消除 5 个误报缺货、2 个误报预警）。
|
||||
// ⚠️ 新增同类「成品发货仓-真实库存」时，往 BRAND_WMS_CO_IDS 追加 wms_co_id 即可。
|
||||
// ⚠️ 配件库存**不适用本口径**（配件仍只算 15718196）——新仓一个配件都没有，
|
||||
// 横林三仓/沭阳仓虽有 MKH 配件但归属别的项目，并入会让「配件不足」漏报。
|
||||
//
|
||||
// ── ⑧ 在途加工单（2026-09-17 业务确认口径）────────────────────────
|
||||
// 规则：**只要该 MKH 商品存在未完成的加工单，就不再列入补货预警**
|
||||
// （工厂已经在做了，不重复提示补货），改标为「已排产」。
|
||||
@ -303,13 +331,22 @@
|
||||
// ⚠️ **必须扣未完成加工单的占用**（业务明确要求）：加工单在途的成品已经把配件
|
||||
// 预定/领走了，不扣就会高估可组装能力。加工单占用按配件维度归集：
|
||||
// 占用[配件] = Σ over 未完成加工单 (该成品在途台数 × 该成品对该配件的单台用量)
|
||||
// · 配件现有库存 rpt.jst_inventoryInfo，仓 = 简尚-崔桥工厂半成品包装项目仓
|
||||
// （wms_co_id = 15718196）—— MKH BOM 的 784 个配件归这个仓，产出成品入品牌仓。
|
||||
// 实测：该仓 822 行 / 741 行有正库存；本页涉及的配件**全部只在该仓有值**，
|
||||
// 跨仓正库存为 0，所以不跨仓汇总。
|
||||
// · 配件现有库存 **两级来源**（2026-09-30 修正，与配件监控页 `buildRealtimeStockSql` 同口径）：
|
||||
// ① rpt.jst_inventoryInfo，仓 = 简尚-崔桥工厂半成品包装项目仓（wms_co_id = 15718196）
|
||||
// —— MKH BOM 的 784 个配件归这个仓，产出成品入品牌仓。
|
||||
// 实测：该仓 822 行 / 741 行有正库存；本页涉及的配件**全部只在该仓有值**，
|
||||
// 跨仓正库存为 0，所以不跨仓汇总。
|
||||
// ② 同仓 rpt.jst_packInventoryInfo 的 **bin 非空** 行合计 —— **仅当 ① 为 0 / 缺行时启用**。
|
||||
// 🔥 **推翻旧结论**（旧版此处写「不能用 jst_packInventoryInfo 当配件库存 ——
|
||||
// 与 MKH 配件零交集」，那是 09-17 的表状态，09-18 上游重建后已失效，见 ⑤）。
|
||||
// 漏掉这一级会让「库存表滞后停在 0、箱表已有货」的配件被误判不足 ——
|
||||
// 2026-09-30 案例 MKHDNZ0409260600849 的配件 DNZ040947N1440V01：
|
||||
// inventoryInfo 停在 09-28 值 0，箱表当日 10:30 已记 bin=ZT-30 qty=200；
|
||||
// 配件监控页显示 200（判「正常」），MKH-BOM 却因 0 判「配件不足」。
|
||||
// 当日 30 行配件不足预警里 **7 行**属此类误判（可组装数 0 → 30~46）。
|
||||
// ⚠️ 必须过滤 `bin` 为空的行 —— 那是装箱记录不是库存（见 ⑤）；
|
||||
// 且**只补 0、不覆盖正库存**（箱表可能只记部分装箱量，覆盖会压低有效库存）。
|
||||
// · 单台用量 rm_qty（jst_skuBomItem）实测取值为 1 / 2 / 4，必须参与计算，不能当 1。
|
||||
// ⚠️ 不能用 `jst_packInventoryInfo` 当配件库存 —— 它是「箱及仓位库存」，
|
||||
// 与 MKH 配件零交集（见 ⑤），仓位口径专用。
|
||||
// · 采购在途（purchase_qty）**不计入**可用量（业务确认：只认现有库存）。
|
||||
// 锁定列（order_lock / pick_lock / lock_qty / virtual_qty）实测几乎全为 0
|
||||
// （本页 80 个配件里仅 2 条非零且值为 1），故直接用 qty。
|
||||
@ -398,8 +435,17 @@ const MKH_WHERE = `s.sku_id LIKE N'MKH%'
|
||||
)`;
|
||||
|
||||
// ── 补货预警参数（见文件头 ⑦）────────────────────────────────────
|
||||
// 品牌仓 wms_co_id（rpt.jst_warehouseInfo 查得）
|
||||
const BRAND_WMS_CO_ID = 15870495;
|
||||
// 成品库存仓白名单（rpt.jst_warehouseInfo 查得）。**多仓求和**：
|
||||
// 商品档案的 other_3 只记录「归属仓」（仍是崔桥工厂品牌仓），但实物会成批搬到
|
||||
// 同体系的其它发货仓，此时库存表里只有新仓有数、归属仓为 0 —— 只查归属仓会把
|
||||
// 有货的商品算成 0（实测 2026-10-17：新仓「简尚-横林二仓品牌仓」10-13 建仓，
|
||||
// 58 个 MKH 成品 / 525 件全部只在它名下，导致 5 个误报缺货 + 2 个误报预警）。
|
||||
// · 15870495 简尚-崔桥工厂品牌仓（归属主仓）
|
||||
// · 16132681 简尚-横林二仓品牌仓（2026-10-13 新增，成品发货仓-真实库存）
|
||||
// ⚠️ 新增同类「成品发货仓-真实库存」时把 wms_co_id 追加进来即可，别再写死单仓。
|
||||
const BRAND_WMS_CO_IDS = Object.freeze([15870495, 16132681]);
|
||||
// 兼容旧引用：主仓仍作为「仓库名」文案与监控快照的 warehouse 过滤值
|
||||
const BRAND_WMS_CO_ID = BRAND_WMS_CO_IDS[0];
|
||||
// 日销统计窗口（天）。见文件头 ⑦：业务最初要 30 天，但全库无 30 天日销数据，
|
||||
// 实测最长只有 fifteenAvgSale(15 天)，经业务确认改用 15 天。
|
||||
// ⚠️ SALE_WINDOW_DAYS 决定页面文案与口径展示，DAILY_SALE_COLUMN 决定实际取数列，
|
||||
@ -525,8 +571,10 @@ const normalizeCode = (value) => {
|
||||
// 筛选 / 排序 / 分页 / 统计全部在内存完成，SQL 侧只负责取数。
|
||||
const REPLENISH_PARAMS = Object.freeze({
|
||||
brandWarehouse: DESIGNATED_WAREHOUSE,
|
||||
brandWmsCoId: BRAND_WMS_CO_ID,
|
||||
});
|
||||
// 成品库存仓白名单的 SQL 片段（纯代码常量插值，不含外部输入）。
|
||||
// 用 `IN (...)` 一条查完多仓，再由应用层按 SKU **求和**分仓结果。
|
||||
const BRAND_WMS_CO_ID_LIST_SQL = BRAND_WMS_CO_IDS.join(',');
|
||||
// 商品集合是主数据、监控快照为日更，10 分钟缓存足够
|
||||
const MKH_CACHE_TTL_MS = 10 * 60 * 1000;
|
||||
let mkhProductCache = null;
|
||||
@ -1745,21 +1793,23 @@ async function loadReplenishIndex() {
|
||||
REPLENISH_PARAMS,
|
||||
{ requestTimeout: 180000 }
|
||||
),
|
||||
// 实时库存：同一 SKU 可能多行，按 updateTime DESC 取首条
|
||||
// 实时库存（见文件头 ⑦-d）：仓白名单多仓，**按 SKU 求和**（同一批货可能分散在多仓）。
|
||||
// ⚠️ 不能再用「按 updateTime DESC 取首条」——那会只留一个仓的数，正好丢掉新仓。
|
||||
cloudDatabase.query(
|
||||
`SELECT i.sku_id AS skuId, i.qty AS qty
|
||||
`SELECT i.sku_id AS skuId, SUM(i.qty) AS qty
|
||||
FROM dbo.jst_inventoryInfo i
|
||||
WHERE i.wms_co_id = @brandWmsCoId AND i.sku_id LIKE N'MKH%'
|
||||
ORDER BY i.sku_id ASC, i.updateTime DESC`,
|
||||
WHERE i.wms_co_id IN (${BRAND_WMS_CO_ID_LIST_SQL}) AND i.sku_id LIKE N'MKH%'
|
||||
GROUP BY i.sku_id`,
|
||||
REPLENISH_PARAMS,
|
||||
{ requestTimeout: 180000 }
|
||||
),
|
||||
// 箱/货位库存（见文件头 ⑦-d）：inventoryInfo 是日更，当日入库当天看不到，
|
||||
// 用小时级的箱表补 0。只取 bin 非空行 —— bin 为空的是装箱记录，不是库存。
|
||||
// 同样按仓白名单多仓求和（见 BRAND_WMS_CO_IDS 注释）。
|
||||
cloudDatabase.query(
|
||||
`SELECT p.sku_id AS skuId, SUM(p.qty) AS qty
|
||||
FROM dbo.jst_packInventoryInfo p
|
||||
WHERE p.wms_co_id = @brandWmsCoId AND p.sku_id LIKE N'MKH%'
|
||||
WHERE p.wms_co_id IN (${BRAND_WMS_CO_ID_LIST_SQL}) AND p.sku_id LIKE N'MKH%'
|
||||
AND NULLIF(LTRIM(RTRIM(p.bin)), N'') IS NOT NULL
|
||||
GROUP BY p.sku_id`,
|
||||
REPLENISH_PARAMS,
|
||||
@ -1845,6 +1895,10 @@ async function loadReplenishIndex() {
|
||||
/**
|
||||
* 配件现有库存批量查询（见文件头 ⑪）—— 只查配件归属仓「简尚-崔桥工厂半成品包装项目仓」。
|
||||
* · 按基础配件编码精确匹配，SUM(qty) 汇总
|
||||
* · **两级来源**（与配件监控页 buildRealtimeStockSql 同口径）：
|
||||
* ① jst_inventoryInfo 有正库存 → 以它为准
|
||||
* ② 为 0 / 缺行 → 用同仓 jst_packInventoryInfo 的 **bin 非空** 行合计兜底
|
||||
* （2026-09-30 修正：旧版只查 ①，导致「库存表滞后为 0、箱表已有货」的配件被误判不足）
|
||||
* · 结果按编码缓存（TTL 10 分钟），**查不到也缓存 0**（该仓无记录 = 无库存）
|
||||
* · 任一分片查询失败 → 立即返回 degraded=true，调用方**放弃判定**（宁可少标不误标），
|
||||
* 且失败的分片不写缓存，下次调用重试
|
||||
@ -1872,25 +1926,42 @@ async function loadComponentStock(codes = []) {
|
||||
params[`cs${index}`] = code;
|
||||
return `@cs${index}`;
|
||||
});
|
||||
const codeFilter = `IN (${placeholders.join(', ')})`;
|
||||
// 一条语句同时取两张表（UNION ALL + src 标记），避免多一次往返、也保证两个来源同一时点。
|
||||
// 库存表不筛 bin；箱表**必须**筛 bin 非空 —— bin 为空的是装箱记录，不是库存（见文件头 ⑤）。
|
||||
// eslint-disable-next-line no-await-in-loop -- 有意分片串行，避免一次打满连接池
|
||||
const rows = await cloudDatabase.query(
|
||||
`SELECT i.sku_id AS code, SUM(i.qty) AS qty
|
||||
`SELECT N'inv' AS src, i.sku_id AS code, SUM(i.qty) AS qty
|
||||
FROM dbo.jst_inventoryInfo i WITH (NOLOCK)
|
||||
WHERE i.wms_co_id = @componentStockWmsCoId
|
||||
AND i.sku_id IN (${placeholders.join(', ')})
|
||||
GROUP BY i.sku_id`,
|
||||
WHERE i.wms_co_id = @componentStockWmsCoId AND i.sku_id ${codeFilter}
|
||||
GROUP BY i.sku_id
|
||||
UNION ALL
|
||||
SELECT N'pack' AS src, p.sku_id AS code, SUM(p.qty) AS qty
|
||||
FROM dbo.jst_packInventoryInfo p WITH (NOLOCK)
|
||||
WHERE p.wms_co_id = @componentStockWmsCoId AND p.sku_id ${codeFilter}
|
||||
AND NULLIF(LTRIM(RTRIM(p.bin)), N'') IS NOT NULL
|
||||
GROUP BY p.sku_id`,
|
||||
params,
|
||||
{ requestTimeout: 120000 }
|
||||
);
|
||||
const found = new Map();
|
||||
const inventoryQty = new Map();
|
||||
const packQty = new Map();
|
||||
rows.forEach((row) => {
|
||||
const code = toText(row.code);
|
||||
if (code) found.set(code, toNumber(row.qty) ?? 0);
|
||||
if (!code) return;
|
||||
const qty = toNumber(row.qty) ?? 0;
|
||||
if (toText(row.src) === 'pack') packQty.set(code, qty);
|
||||
else inventoryQty.set(code, qty);
|
||||
});
|
||||
const expiresAt = Date.now() + COMPONENT_STOCK_CACHE_TTL_MS;
|
||||
const writable = epoch === cacheEpoch;
|
||||
chunk.forEach((code) => {
|
||||
const qty = found.get(code) ?? 0;
|
||||
// 只补 0、不覆盖正库存：库存表有正数就认它，否则看箱表（箱表可能只记部分装箱量）
|
||||
let qty = inventoryQty.get(code) ?? 0;
|
||||
if (qty <= 0) {
|
||||
const packStock = packQty.get(code) ?? 0;
|
||||
if (packStock > 0) qty = packStock;
|
||||
}
|
||||
// 本批结果仍要返回给调用方（stockMap），只是不再写缓存
|
||||
if (writable) componentStockCache.set(code, { qty, expiresAt });
|
||||
stockMap.set(code, qty);
|
||||
@ -2649,6 +2720,9 @@ module.exports = {
|
||||
COMPONENT_STOCK_WAREHOUSE,
|
||||
COMPONENT_STOCK_CHUNK_SIZE,
|
||||
COMPONENT_SHORTAGE_MAX_LIST,
|
||||
// ⑦-d 成品库存仓白名单（多仓求和口径）
|
||||
BRAND_WMS_CO_IDS,
|
||||
BRAND_WMS_CO_ID,
|
||||
parseFlag,
|
||||
resolveScope,
|
||||
filterRows,
|
||||
|
||||
243
backend/src/services/pickingBinSalesAnalysis.js
Normal file
243
backend/src/services/pickingBinSalesAnalysis.js
Normal file
@ -0,0 +1,243 @@
|
||||
'use strict';
|
||||
|
||||
/**
|
||||
* 拣选库位动销分析 —— 业务库取数
|
||||
*
|
||||
* 库存：rpt.jst_packInventoryInfo 按 wms_co_id + 仓位 + sku_id 汇总 qty（仓位为空的行不参与）
|
||||
* 范围：只统计真实拣选仓位（pack_type=Bin 且 bin_group 为空）；
|
||||
* 箱记录（pack_type=Pack）与存货位（bin_group=存货）不纳入
|
||||
* 销量（4 个来源分支，均按 send_date 窗口）：
|
||||
* 1) rpt.jst_Orders × jst_OrderItem 普通行（sku_type<>combine），排除 Delete/Cancelled
|
||||
* 2) rpt.jst_Orders 组合装行（sku_id 为 ZH-... 拼接串）→ JOIN jst_comboProductItem 展开为子件×数量
|
||||
* 3) rpt.jst_qmcust_orderlist（全民客户 B2B 分销）普通行，排除 Delete/Cancelled/Split/Question
|
||||
* （Split 是拆单父单，与拆出的子单重复，必须排除）
|
||||
* 4) rpt.jst_qmcust_orderlist 组合装行 → 同样按映射表展开
|
||||
* 粒度：仓位 × 商品编码（一个仓位有 N 个商品就输出 N 行），列表与导出共用
|
||||
*
|
||||
* 【日销与动销等级口径】按「商品日销」判定，不做仓位分摊：
|
||||
* 商品日销 = 该商品在窗口内的整档销量 ÷ 窗口天数
|
||||
* 同一件商品无论占用几个仓位，各行显示的销量 / 日销 / 动销等级完全一致 ——
|
||||
* 不会因为货放得散就被算成滞销。
|
||||
*
|
||||
* 附带输出「分摊销量」= 商品销量 ÷ 该商品占用的同类型仓位数，用于需要按行求和的场景：
|
||||
* 恒等式（已实测校验，差异 0）：全部行分摊销量之和 = 有库位记录的那些 SKU 的销量之和。
|
||||
* 注意它小于「全仓销量」：jst_packInventoryInfo 只覆盖部分 SKU 的仓位数据，
|
||||
* 没有仓位记录的 SKU 的销量不会体现在任何库位上。
|
||||
*/
|
||||
|
||||
const { PICKING_BIN_GRADE_RULES, BIN_TYPE_PICKING } = require('../config/pickingBinSalesAnalysis');
|
||||
|
||||
/** mssql 不支持数组参数，按位展开成 @p0,@p1... */
|
||||
function buildInClause(values, prefix) {
|
||||
const params = {};
|
||||
const clause = values
|
||||
.map((v, i) => {
|
||||
const key = `${prefix}${i}`;
|
||||
params[key] = v;
|
||||
return `@${key}`;
|
||||
})
|
||||
.join(',');
|
||||
return { clause, params };
|
||||
}
|
||||
|
||||
/**
|
||||
* 日销分档表达式：从高档往低档依次命中，最后一档「销售=0」由 salesQty <= 0 兜底。
|
||||
* 传入的 salesExpr 是窗口销量（商品整档销量），除以天数得到日销。
|
||||
*/
|
||||
function buildGradeCase(salesExpr = 'ISNULL(s.salesQty, 0)', daysParam = '@days') {
|
||||
const branches = PICKING_BIN_GRADE_RULES
|
||||
.filter((rule) => rule.lower > 0)
|
||||
.map((rule) => {
|
||||
const daily = `(${salesExpr} / ${daysParam})`;
|
||||
const lower = `${daily} >= ${rule.lower}`;
|
||||
const upper = rule.upper == null ? null : `${daily} < ${rule.upper}`;
|
||||
const cond = upper ? `${lower} AND ${upper}` : lower;
|
||||
return `WHEN ${cond} THEN '${rule.key}'`;
|
||||
});
|
||||
|
||||
return `
|
||||
CASE
|
||||
${branches.join('\n ')}
|
||||
WHEN ${salesExpr} > 0 THEN 'slow2'
|
||||
ELSE 'zero'
|
||||
END`;
|
||||
}
|
||||
|
||||
/**
|
||||
* 拉取「仓位 × 商品编码」粒度明细（列表与导出共用）。
|
||||
*
|
||||
* - stockQty：该商品在此仓位的实际库存
|
||||
* - salesQty：该商品在此仓库的窗口销量（整档，不按仓位分摊），动销等级的判定依据
|
||||
* - dailySales：salesQty / 窗口天数 = 商品日销
|
||||
* - allocatedSalesQty：salesQty ÷ 该商品占用的同类型仓位数，用于需要按行求和的场景
|
||||
* - skuCount：该仓位内存放的商品数（仓位粒度，同一仓位多行重复）
|
||||
*
|
||||
* @param {object} cloudDb 业务库实例
|
||||
* @param {string[]} wmsCoIds 仓库编码
|
||||
* @param {{shipFrom:string, shipTo:string, days:number}} options
|
||||
* @returns {Promise<Array<{wmsCoId:string, binName:string, binType:string, skuId:string,
|
||||
* stockQty:number, skuCount:number, salesQty:number, allocatedSalesQty:number,
|
||||
* dailySales:number, gradeKey:string}>>}
|
||||
*/
|
||||
async function fetchPickingBinSkuRows(cloudDb, wmsCoIds, options = {}) {
|
||||
if (!cloudDb || typeof cloudDb.query !== 'function') {
|
||||
throw new TypeError('fetchPickingBinSkuRows 的第一个参数必须是业务库实例');
|
||||
}
|
||||
const { shipFrom, shipTo, days } = options;
|
||||
if (!Array.isArray(wmsCoIds) || wmsCoIds.length === 0) return [];
|
||||
if (!shipFrom || !shipTo || !days) return [];
|
||||
|
||||
const binIn = buildInClause(wmsCoIds, 'w');
|
||||
const shipIn = buildInClause(wmsCoIds, 'v');
|
||||
|
||||
// 统计范围固定为真实拣选仓位：pack_type=Bin 且 bin_group 为空。
|
||||
// 仅过滤 bin_group 会把同样为空的 Pack 箱记录误标为拣选位。
|
||||
const sqlText = `
|
||||
WITH bin AS (
|
||||
SELECT p.wms_co_id AS wmsCoId,
|
||||
LTRIM(RTRIM(p.bin)) AS binName,
|
||||
N'${BIN_TYPE_PICKING}' AS binType,
|
||||
p.sku_id AS skuId,
|
||||
SUM(CAST(ISNULL(p.qty, 0) AS DECIMAL(18, 2))) AS binSkuQty
|
||||
FROM rpt.jst_packInventoryInfo p
|
||||
WHERE p.wms_co_id IN (${binIn.clause})
|
||||
AND ISNULL(p.bin, '') <> ''
|
||||
AND ISNULL(p.qty, 0) <> 0
|
||||
AND p.pack_type = N'Bin'
|
||||
AND ISNULL(p.bin_group, '') = ''
|
||||
GROUP BY p.wms_co_id,
|
||||
LTRIM(RTRIM(p.bin)),
|
||||
p.sku_id
|
||||
),
|
||||
binSpan AS (
|
||||
SELECT wmsCoId, skuId, binType, COUNT(DISTINCT binName) AS binCount
|
||||
FROM bin
|
||||
GROUP BY wmsCoId, skuId, binType
|
||||
),
|
||||
ship AS (
|
||||
-- 1) 线上店铺订单：普通商品行（sku_type<>combine，组合装行在下一分支展开）
|
||||
SELECT o.wms_co_id AS wmsCoId,
|
||||
oi.sku_id AS skuId,
|
||||
SUM(CAST(ISNULL(oi.qty, 0) AS DECIMAL(18, 2))) AS salesQty
|
||||
FROM rpt.jst_OrderItem oi
|
||||
JOIN rpt.jst_Orders o ON o.o_id = oi.o_id
|
||||
WHERE o.wms_co_id IN (${shipIn.clause})
|
||||
AND o.send_date >= @shipFrom
|
||||
AND o.send_date < @shipTo
|
||||
AND ISNULL(o.status, '') NOT IN ('Delete', 'Cancelled')
|
||||
AND ISNULL(oi.sku_type, 'normal') <> 'combine'
|
||||
GROUP BY o.wms_co_id, oi.sku_id
|
||||
UNION ALL
|
||||
-- 2) 线上店铺订单：组合装行（sku_id 为 ZH-子件A-N-子件B-M 拼接串）→ 按映射表展开为子件
|
||||
SELECT o.wms_co_id AS wmsCoId,
|
||||
ci.src_sku_id AS skuId,
|
||||
SUM(CAST(ISNULL(oi.qty, 0) * ISNULL(ci.qty, 0) AS DECIMAL(18, 2))) AS salesQty
|
||||
FROM rpt.jst_OrderItem oi
|
||||
JOIN rpt.jst_Orders o ON o.o_id = oi.o_id
|
||||
JOIN rpt.jst_comboProductItem ci ON ci.combine_sku_id = oi.sku_id
|
||||
WHERE o.wms_co_id IN (${shipIn.clause})
|
||||
AND o.send_date >= @shipFrom
|
||||
AND o.send_date < @shipTo
|
||||
AND ISNULL(o.status, '') NOT IN ('Delete', 'Cancelled')
|
||||
AND oi.sku_type = 'combine'
|
||||
GROUP BY o.wms_co_id, ci.src_sku_id
|
||||
UNION ALL
|
||||
-- 3) 全民客户（B2B 分销）订单：普通商品行（拆单父单 Split 与子单重复，需排除）
|
||||
SELECT o.wms_co_id AS wmsCoId,
|
||||
oi.sku_id AS skuId,
|
||||
SUM(CAST(ISNULL(oi.qty, 0) AS DECIMAL(18, 2))) AS salesQty
|
||||
FROM rpt.jst_qmcust_orderlist_item oi
|
||||
JOIN rpt.jst_qmcust_orderlist o ON o.o_id = oi.o_id
|
||||
WHERE o.wms_co_id IN (${shipIn.clause})
|
||||
AND o.send_date >= @shipFrom
|
||||
AND o.send_date < @shipTo
|
||||
AND ISNULL(o.status, '') NOT IN ('Delete', 'Cancelled', 'Split', 'Question')
|
||||
AND ISNULL(oi.sku_type, 'normal') <> 'combine'
|
||||
GROUP BY o.wms_co_id, oi.sku_id
|
||||
UNION ALL
|
||||
-- 4) 全民客户订单：组合装行展开为子件
|
||||
SELECT o.wms_co_id AS wmsCoId,
|
||||
ci.src_sku_id AS skuId,
|
||||
SUM(CAST(ISNULL(oi.qty, 0) * ISNULL(ci.qty, 0) AS DECIMAL(18, 2))) AS salesQty
|
||||
FROM rpt.jst_qmcust_orderlist_item oi
|
||||
JOIN rpt.jst_qmcust_orderlist o ON o.o_id = oi.o_id
|
||||
JOIN rpt.jst_comboProductItem ci ON ci.combine_sku_id = oi.sku_id
|
||||
WHERE o.wms_co_id IN (${shipIn.clause})
|
||||
AND o.send_date >= @shipFrom
|
||||
AND o.send_date < @shipTo
|
||||
AND ISNULL(o.status, '') NOT IN ('Delete', 'Cancelled', 'Split', 'Question')
|
||||
AND oi.sku_type = 'combine'
|
||||
GROUP BY o.wms_co_id, ci.src_sku_id
|
||||
),
|
||||
shipAgg AS (
|
||||
-- 同一个 仓库+商品 可能同时命中多个来源分支（线上普通/线上组合装/全民客户普通/全民客户组合装），
|
||||
-- 必须先按 仓库+商品 汇总成一行再 join，否则一个仓位行会被拆成多行、每行只带某分支的销量。
|
||||
SELECT wmsCoId,
|
||||
skuId,
|
||||
SUM(CAST(salesQty AS DECIMAL(18, 2))) AS salesQty
|
||||
FROM ship
|
||||
GROUP BY wmsCoId, skuId
|
||||
),
|
||||
binAgg AS (
|
||||
SELECT wmsCoId,
|
||||
binName,
|
||||
binType,
|
||||
COUNT(DISTINCT skuId) AS skuCount
|
||||
FROM bin
|
||||
GROUP BY wmsCoId, binName, binType
|
||||
)
|
||||
SELECT b.wmsCoId,
|
||||
b.binName,
|
||||
b.binType,
|
||||
b.skuId,
|
||||
CAST(SUM(CAST(b.binSkuQty AS DECIMAL(18, 2))) AS DECIMAL(18, 2)) AS stockQty,
|
||||
CAST(a.skuCount AS INT) AS skuCount,
|
||||
CAST(ISNULL(s.salesQty, 0) AS DECIMAL(18, 2)) AS salesQty,
|
||||
CAST(ISNULL(s.salesQty, 0) / NULLIF(sp.binCount, 0) AS DECIMAL(18, 4)) AS allocatedSalesQty,
|
||||
CAST(ISNULL(s.salesQty, 0) / @days AS DECIMAL(18, 4)) AS dailySales,
|
||||
${buildGradeCase('ISNULL(s.salesQty, 0)')} AS gradeKey
|
||||
FROM bin b
|
||||
JOIN binSpan sp
|
||||
ON sp.wmsCoId = b.wmsCoId AND sp.skuId = b.skuId AND sp.binType = b.binType
|
||||
JOIN binAgg a
|
||||
ON a.wmsCoId = b.wmsCoId AND a.binName = b.binName AND a.binType = b.binType
|
||||
LEFT JOIN shipAgg s
|
||||
ON s.wmsCoId = b.wmsCoId AND s.skuId = b.skuId
|
||||
GROUP BY b.wmsCoId, b.binName, b.binType, b.skuId, a.skuCount, s.salesQty, sp.binCount
|
||||
HAVING ABS(SUM(CAST(b.binSkuQty AS DECIMAL(18, 2)))) > 0.0001
|
||||
ORDER BY b.wmsCoId, b.binType, b.binName, b.skuId`;
|
||||
|
||||
// 该查询 union 了 4 个销量来源（含 qmcust 全渠道扫描），全仓窗口可能超过默认 60s；
|
||||
// mssql 12.x 请求级 timeout 不生效，必须走池级放宽的慢查询入口。
|
||||
const runQuery = typeof cloudDb.querySlow === 'function' ? cloudDb.querySlow : cloudDb.query;
|
||||
const rows = await runQuery(
|
||||
sqlText,
|
||||
{
|
||||
...binIn.params,
|
||||
...shipIn.params,
|
||||
shipFrom,
|
||||
shipTo,
|
||||
days,
|
||||
},
|
||||
{ requestTimeout: 120000 }
|
||||
);
|
||||
|
||||
return rows.map((r) => ({
|
||||
wmsCoId: String(r.wmsCoId),
|
||||
binName: r.binName || '',
|
||||
binType: r.binType || '',
|
||||
skuId: r.skuId == null ? '' : String(r.skuId).trim(),
|
||||
stockQty: Number(r.stockQty) || 0,
|
||||
skuCount: Number(r.skuCount) || 0,
|
||||
salesQty: Number(r.salesQty) || 0,
|
||||
allocatedSalesQty: Number(r.allocatedSalesQty) || 0,
|
||||
dailySales: Number(r.dailySales) || 0,
|
||||
gradeKey: r.gradeKey || 'zero',
|
||||
}));
|
||||
}
|
||||
|
||||
module.exports = {
|
||||
buildGradeCase,
|
||||
buildInClause,
|
||||
fetchPickingBinSkuRows,
|
||||
};
|
||||
@ -14,13 +14,20 @@
|
||||
* · returnBinName = 用户口径的聚水潭仓位名，写进页面/导出的「数据来源」，便于核对；
|
||||
* · returnSlot = 副本里真正取数的那一列，必须与上面的仓位名一一对应。
|
||||
*
|
||||
* 仓位名 → 库存位 对照（2026-09-25 复测，条件 ISNULL(col,0) > 0）：
|
||||
* 箱及仓位库存 · 简尚-崔桥工厂仓 · 崔桥工厂客退临时上架仓 customize_qty_1 2 编码 / 2 件 ⚠
|
||||
* 箱及仓位库存 · 简尚-新苏北仓 · 新苏北客退上架仓 customize_qty_1 75 编码 / 362 件
|
||||
* 箱及仓位库存 · 简尚-睢宁仓 · 睢宁客退上架仓 customize_qty_1 98 编码 / 243 件
|
||||
* 箱及仓位库存 · 简尚-山东青岗集仓 · 青岗集客退上架仓 customize_qty_2 403 编码 / 912 件
|
||||
* 仓位名 → 库存位 对照（2026-09-27 复测，条件 ISNULL(col,0) > 0）：
|
||||
* 箱及仓位库存 · 简尚-崔桥工厂仓 · 崔桥工厂客退临时上架仓 customize_qty_1 349 编码 / 507 件
|
||||
* 箱及仓位库存 · 简尚-新苏北仓 · 新苏北客退上架仓 customize_qty_1 75 编码 / 362 件
|
||||
* 箱及仓位库存 · 简尚-睢宁仓 · 睢宁客退上架仓 customize_qty_1 98 编码 / 243 件
|
||||
* 箱及仓位库存 · 简尚-山东青岗集仓 · 青岗集客退上架仓 customize_qty_2 403 编码 / 912 件
|
||||
* 箱及仓位库存 · 简尚-河南洛宁仓 · 河南洛宁客退上架仓 三位全为 0（无数据）
|
||||
*
|
||||
* ── 2026-09-27 重要更新：崔桥工厂仓的客退库存已经同步进副本了 ──
|
||||
* 09-25 首次切换时该仓客退位只有 2 编码 / 2 件（当时判定「未同步进副本」），
|
||||
* 09-26 起 IT 侧补上了同步：09-26 当天写入 317 编码 / 460 件，09-27 又补 31 编码 / 46 件，
|
||||
* 09-27 10:11 实测 349 编码 / 507 件（= 副本 c1 合计，页面与副本完全对得上）。
|
||||
* ⚠ 所以下面「崔桥必然近乎空白 / 只能靠导入」的旧结论已经作废，不要再拿它当依据。
|
||||
* 现在这张看板能正常出数，**不需要**用导入功能兜底。
|
||||
*
|
||||
* ⚠ 崔桥工厂仓（15038426）是 2026-09-25 用户指定替换横林二仓的。复测结论：
|
||||
* 副本里该仓客退位只有 2 编码 / 2 件，但退货单量很大（ERP 售后单 3,115 条 +
|
||||
* 奇门退款单 1,812 条，warehouse='简尚-崔桥工厂仓销退仓'）——即「退货有，客退上架库存
|
||||
@ -58,10 +65,8 @@ const WAREHOUSE_LIST = [
|
||||
returnBinName: '崔桥工厂客退临时上架仓',
|
||||
// 客退临时上架仓占用的自定义库存位
|
||||
returnSlot: 'customize_qty_1',
|
||||
// ⚠ 2026-09-25 实测：该仓在副本 jst_inventoryInfo 里客退位只有 2 个编码 / 2 件
|
||||
//（qty 总库存 92,818 件；return_qty / c2 / c3 / sale_refund_qty 全为 0）。
|
||||
// 退货单是有的（ERP 售后单 3,115 条 + 奇门退款单 1,812 条），
|
||||
// 说明是「客退上架仓」这一仓位没同步进副本，不是业务上没有退货。
|
||||
// 2026-09-27 复测：客退位已同步进副本 —— 349 编码 / 507 件（c1 合计），
|
||||
// 退货单口径：ERP 售后单 3,115 条 + 奇门退款单 1,812 条（warehouse='简尚-崔桥工厂仓销退仓'）。
|
||||
hasReturnStock: true,
|
||||
},
|
||||
{
|
||||
|
||||
@ -64,6 +64,10 @@
|
||||
|
||||
const cloudDb = require('../config/cloudDatabase');
|
||||
const database = require('../config/database');
|
||||
const {
|
||||
loadSupplierExitDates,
|
||||
EMPTY_EXIT_DATES,
|
||||
} = require('./supplierExitDateProvider');
|
||||
const {
|
||||
WAREHOUSE_LIST,
|
||||
RETURN_SLOT_COLUMNS,
|
||||
@ -242,6 +246,7 @@ const buildKnownGaps = (warehouse = DEFAULT_WAREHOUSE) => {
|
||||
'单价 / 总金额：单价取商品资料 jst_sku 的成本价（cost_price），成本价缺失或为 0 时回退采购价（purchase_price）；总金额 = 单价 × 总库存（即「客退临时上架仓」的库存金额）。商品资料里两个价格都没有的编码（多为虚拟编码，如 HBZBF）单价与总金额显示「--」，且不计入顶部「库存总金额」，页面会单独提示这类编码的数量。',
|
||||
'单价预警：按商品单价单独分级，默认「单价 > 300 元 → 重点关注」「200 ~ 300 元 → 关注」「< 200 元 → 正常」，阈值可在页面上直接调。它与库龄预警是两个互补维度——库龄看「货压了多久」，单价看「每件压了多少钱」，两者叠加才能看出真正占资金的那批编码。',
|
||||
'退厂时限：单价 > 重点关注线（默认 300 元）的编码，要求入库后 7 天内完成库存退厂（时限可在页面调）。页面按「在库天数」对比时限，给出「已超期 N 天 / 剩余 N 天」，并在顶部单独提示这批编码的处置要求。「已退厂」的判定是「该编码已从当天库存中消失」（不再依赖出库表），一旦退厂完成，这条编码自然就不会再出现在报表里。',
|
||||
'最晚退厂时间（当前「待接入」）：业务上由工作平台对「不再合作」的供应商给出最晚退厂日期，看板按供应商名匹配后与「供应商是否合作」列并排展示。它不在报表副本里——数据属于工作平台库 WorkbenchDataShared（同实例另有 WorkbenchIdentity、DataPlatformControl），而 2026-09-28 取证时副本账号 cangku 访问该库被拒（The server principal "cangku" is not able to access the database "WorkbenchDataShared" under the current security context），系统设置里也没有它的连接配置，.env 的 JIAN_SUN_DB_* / WORK_ORDER_* 全为空；副本库内 5,496 个字段中没有任何「退厂」字段，最接近的 fys_supplier.NoCoopTime 是不可用的停更快照（崔桥仓 8 个不合作供应商里只有 1 个有值，且那 1 条自身还标着「合作」）。所以该列先以「待接入」占位，不影响其他列。要出数只需二选一：① 让 IT 给副本账号开通 WorkbenchDataShared 只读；② 让 IT 把工作平台「供应商不合作」表同步进 JSReportReplica。之后在 backend/src/services/supplierExitDateProvider.js 里填上查询语句即可自动出数，页面与导出都无需再改。',
|
||||
];
|
||||
if (!warehouse.hasReturnStock) {
|
||||
gaps.unshift(
|
||||
@ -367,10 +372,16 @@ const buildFullSql = (createdAtBasis = DEFAULT_CREATED_AT_BASIS, warehouse = DEF
|
||||
];
|
||||
if (useFirstInbound) selectColumns.push('f.first_inbound_time AS [firstInboundTime]');
|
||||
selectColumns.push("ISNULL(sk.supplier_name, N'') AS [supplier]");
|
||||
// 供应商是否合作：取自简尚供应商主数据 js_supplier.coopersgn（1=合作 / 2=不合作）
|
||||
selectColumns.push('sp.coopersgn AS [supplierCoopSign]');
|
||||
// 单价取自商品资料 jst_sku：成本价优先，缺失/为 0 时回退采购价（见 pickUnitPrice）
|
||||
selectColumns.push('sk.cost_price AS [costPrice]');
|
||||
selectColumns.push('sk.purchase_price AS [purchasePrice]');
|
||||
const joins = ['LEFT JOIN dbo.jst_sku sk ON sk.sku_id = s.product_code'];
|
||||
// ⚠ 关联键必须是 scmname（供应商简称），与 jst_sku.supplier_name 是同一套命名（2026-09-28 实测
|
||||
// 匹配 100/109）；tradebody（工商全称）只能匹配 1 条，别用。scmname 在 js_supplier 里唯一
|
||||
// （1,497 行 / 1,497 个不同值），所以 LEFT JOIN 不会放大行数。
|
||||
joins.push('LEFT JOIN dbo.js_supplier sp ON sp.scmname = sk.supplier_name');
|
||||
if (useFirstInbound) joins.push('LEFT JOIN first_in f ON f.product_code = s.product_code');
|
||||
|
||||
return `
|
||||
@ -392,7 +403,13 @@ const buildStockSyncedSql = (warehouse = DEFAULT_WAREHOUSE) => `
|
||||
FROM dbo.jst_inventoryInfo
|
||||
WHERE wms_co_id = ${warehouse.wmsCoId}`;
|
||||
|
||||
const CACHE_TTL_MS = 10 * 60 * 1000;
|
||||
// 默认 5 分钟：副本侧 ETL 约 10 分钟一批增量（2026-09-27 实测），
|
||||
// 这里再压一层缓存只是防止多人反复翻页把副本打爆；想更快用 env 调，或前端点「刷新数据」强制回源。
|
||||
const DEFAULT_CACHE_TTL_MS = 5 * 60 * 1000;
|
||||
const resolveCacheTtlMs = () => {
|
||||
const raw = Number.parseInt(process.env.RETURN_STOCK_WARNING_CACHE_TTL_MS, 10);
|
||||
return Number.isFinite(raw) && raw >= 0 ? raw : DEFAULT_CACHE_TTL_MS;
|
||||
};
|
||||
// 缓存键 = 仓库 + 创建时间口径（两者都会改变 SQL / 取到的行）
|
||||
const buildCacheKey = (warehouse, basis) => `${warehouse.key}|${basis}`;
|
||||
const rowCache = new Map();
|
||||
@ -408,7 +425,7 @@ async function loadReplicaRows({
|
||||
const basis = resolveCreatedAtBasis(createdAtBasis);
|
||||
const key = buildCacheKey(warehouse, basis);
|
||||
const cached = rowCache.get(key);
|
||||
if (!refresh && cached && cached.rows && Date.now() - cached.at < CACHE_TTL_MS) {
|
||||
if (!refresh && cached && cached.rows && Date.now() - cached.at < resolveCacheTtlMs()) {
|
||||
return cached.rows;
|
||||
}
|
||||
const running = inflightCache.get(key);
|
||||
@ -437,7 +454,7 @@ async function loadReplicaRows({
|
||||
/** 副本库存表最近一次同步时间（YYYY-MM-DD HH:mm:ss），页面「库存更新时间」用 */
|
||||
async function loadStockSyncedAt({ refresh = false, warehouse = DEFAULT_WAREHOUSE } = {}) {
|
||||
const state = stockSyncedAtState.get(warehouse.key);
|
||||
if (!refresh && state?.value && Date.now() - state.at < CACHE_TTL_MS) {
|
||||
if (!refresh && state?.value && Date.now() - state.at < resolveCacheTtlMs()) {
|
||||
return state.value;
|
||||
}
|
||||
try {
|
||||
@ -567,6 +584,24 @@ const resolveReturnLimitDays = (value) => {
|
||||
* 其余 → pending，returnRemainDays = 剩余天数（时限 − 在库天数）
|
||||
* 拿不到创建时间（算不出在库天数）时按 pending 处理，剩余天数留空，页面提示「待补创建时间」。
|
||||
*/
|
||||
// ---------------------------------------------------------------------------
|
||||
// 供应商是否合作（2026-09-28 新增）
|
||||
// 依据：简尚供应商主数据 dbo.js_supplier.coopersgn —— 1 = 合作中，2 = 不合作。
|
||||
// 语义已交叉验证：与 dbo.fys_supplier.IsCoop（'合作'/'不合作'）比对，
|
||||
// coopersgn=2 ↔ 不合作 823 条一致、coopersgn=1 ↔ 合作 270 条一致。
|
||||
// 两边冲突的 262 条以 js_supplier 为准 —— fys_supplier 是旧快照（DownloadTime 停在
|
||||
// 2025-11，样本多为 2023/2024），而 js_supplier 的 local_updated 已到 2026-09。
|
||||
// 匹配不上（供应商没进主数据、或该编码没有供应商）一律按「未知」展示，不要当「不合作」。
|
||||
// ---------------------------------------------------------------------------
|
||||
const SUPPLIER_COOP_LABELS = { cooperative: '合作', ended: '不合作', unknown: '未知' };
|
||||
const resolveSupplierCoop = (sign) => {
|
||||
if (sign === null || sign === undefined || sign === '') return 'unknown';
|
||||
const n = Number(sign);
|
||||
if (n === 1) return 'cooperative';
|
||||
if (n === 2) return 'ended';
|
||||
return 'unknown';
|
||||
};
|
||||
|
||||
const resolveReturnDeadline = ({ unitPriceLevel, stockDays, isOutbound, returnLimitDays }) => {
|
||||
if (unitPriceLevel !== 'high') return { status: 'none', remainDays: null };
|
||||
if (isOutbound) return { status: 'done', remainDays: null };
|
||||
@ -795,6 +830,8 @@ function composeRows({
|
||||
replicaRows, overrides, qtyBasis, createdAtBasis, today, scope, focusDays, alertDays, priceMap,
|
||||
priceFocusMin = DEFAULT_PRICE_FOCUS_MIN, priceHighMin = DEFAULT_PRICE_HIGH_MIN,
|
||||
returnLimitDays = DEFAULT_RETURN_LIMIT_DAYS, warehouse = DEFAULT_WAREHOUSE,
|
||||
// 供应商「最晚退厂时间」（工作平台数据，见 supplierExitDateProvider；未接入时是空 Map）
|
||||
exitDates = EMPTY_EXIT_DATES,
|
||||
}) {
|
||||
const hasImport = overrides.size > 0;
|
||||
const rows = [];
|
||||
@ -821,6 +858,11 @@ function composeRows({
|
||||
const deadline = resolveReturnDeadline({
|
||||
unitPriceLevel, stockDays, isOutbound, returnLimitDays,
|
||||
});
|
||||
// 供应商是否合作（js_supplier.coopersgn）
|
||||
const supplierCoop = resolveSupplierCoop(row.supplierCoopSign);
|
||||
// 最晚退厂时间（工作平台「供应商不合作」，按供应商名匹配；未接入时为空）
|
||||
const supplierName = String(row.supplier || '').trim();
|
||||
const latestExitDate = exitDates.bySupplier.get(supplierName) || '';
|
||||
|
||||
if (scope === 'imported' && !override) return;
|
||||
if (qty <= 0) return;
|
||||
@ -851,6 +893,11 @@ function composeRows({
|
||||
stockDays,
|
||||
warningLevel: resolveWarningLevel(stockDays, focusDays, alertDays),
|
||||
isOutbound,
|
||||
supplierCoop,
|
||||
supplierCoopLabel: SUPPLIER_COOP_LABELS[supplierCoop] || '',
|
||||
latestExitDate,
|
||||
latestExitDateLabel: latestExitDate,
|
||||
latestExitDateAvailable: exitDates.available,
|
||||
source: override ? 'jst' : 'replica',
|
||||
noInboundRecord: !createdAtRaw,
|
||||
});
|
||||
@ -870,6 +917,9 @@ function composeRows({
|
||||
const deadline = resolveReturnDeadline({
|
||||
unitPriceLevel, stockDays, isOutbound, returnLimitDays,
|
||||
});
|
||||
// 这条是「导入里有、副本里没有」补进来的编码，没走副本 join，拿不到 coopersgn → 标「未知」
|
||||
const supplierCoop = 'unknown';
|
||||
const supplierExitDate = exitDates.bySupplier.get(String(override.supplier || '').trim()) || '';
|
||||
rows.push({
|
||||
productCode: code,
|
||||
createdAt: formatDateTime(override.createdAt),
|
||||
@ -895,6 +945,11 @@ function composeRows({
|
||||
stockDays,
|
||||
warningLevel: resolveWarningLevel(stockDays, focusDays, alertDays),
|
||||
isOutbound,
|
||||
supplierCoop,
|
||||
supplierCoopLabel: SUPPLIER_COOP_LABELS[supplierCoop] || '',
|
||||
latestExitDate: supplierExitDate,
|
||||
latestExitDateLabel: supplierExitDate,
|
||||
latestExitDateAvailable: exitDates.available,
|
||||
source: 'jst',
|
||||
noInboundRecord: !override.createdAt,
|
||||
});
|
||||
@ -1057,10 +1112,11 @@ async function getReturnStockWarningList(options = {}) {
|
||||
const priceLevel = resolvePriceLevelFilter(options.priceLevel);
|
||||
const today = formatLocalDate();
|
||||
|
||||
const [replicaRows, overrides, latestImport] = await Promise.all([
|
||||
const [replicaRows, overrides, latestImport, exitDates] = await Promise.all([
|
||||
loadReplicaRows({ refresh: String(options.refresh) === 'true', createdAtBasis, warehouse }),
|
||||
loadImportOverrides(warehouse),
|
||||
loadLatestImportBatch(warehouse),
|
||||
loadSupplierExitDates(),
|
||||
]);
|
||||
|
||||
const priceMap = await loadPriceMapForOverrides(replicaRows, overrides);
|
||||
@ -1079,6 +1135,7 @@ async function getReturnStockWarningList(options = {}) {
|
||||
priceHighMin,
|
||||
returnLimitDays,
|
||||
warehouse,
|
||||
exitDates,
|
||||
});
|
||||
|
||||
const filtered = composed.rows.filter((row) => matchesKeyword(row, keyword) && matchesSupplier(row, supplier));
|
||||
@ -1129,6 +1186,12 @@ async function getReturnStockWarningList(options = {}) {
|
||||
summary,
|
||||
import: latestImport,
|
||||
hasImport: composed.hasImport,
|
||||
// 「最晚退厂时间」数据源状态（页面用它渲染「待接入」提示与列 Tooltip）
|
||||
exitDateMeta: {
|
||||
available: exitDates.available,
|
||||
sourceLabel: exitDates.sourceLabel,
|
||||
message: exitDates.message,
|
||||
},
|
||||
gaps: buildKnownGaps(warehouse),
|
||||
};
|
||||
}
|
||||
@ -1148,7 +1211,8 @@ const buildExportColumns = (warehouse = DEFAULT_WAREHOUSE) => [
|
||||
{ key: 'amount', title: '总金额' },
|
||||
{ key: 'unitPriceLevelLabel', title: '单价预警' },
|
||||
{ key: 'returnDeadlineLabel', title: '退厂时限' },
|
||||
{ key: 'isOutboundLabel', title: '是否出库' },
|
||||
{ key: 'supplierCoopLabel', title: '供应商是否合作' },
|
||||
{ key: 'latestExitDateLabel', title: '最晚退厂时间' },
|
||||
{ key: 'returnQty', title: '退货仓库存(return_qty)' },
|
||||
{ key: 'returnSlotQty', title: `客退临时上架库存(${warehouse.returnSlot})` },
|
||||
{ key: 'sourceLabel', title: '数据来源' },
|
||||
@ -1174,9 +1238,10 @@ async function getReturnStockWarningExportRows(options = {}) {
|
||||
const priceLevel = resolvePriceLevelFilter(options.priceLevel);
|
||||
const today = formatLocalDate();
|
||||
|
||||
const [replicaRows, overrides] = await Promise.all([
|
||||
const [replicaRows, overrides, exitDates] = await Promise.all([
|
||||
loadReplicaRows({ refresh: String(options.refresh) === 'true', createdAtBasis, warehouse }),
|
||||
loadImportOverrides(warehouse),
|
||||
loadSupplierExitDates(),
|
||||
]);
|
||||
|
||||
const priceMap = await loadPriceMapForOverrides(replicaRows, overrides);
|
||||
@ -1195,6 +1260,7 @@ async function getReturnStockWarningExportRows(options = {}) {
|
||||
priceHighMin,
|
||||
returnLimitDays,
|
||||
warehouse,
|
||||
exitDates,
|
||||
});
|
||||
|
||||
const filtered = composed.rows.filter((row) => matchesKeyword(row, keyword) && matchesSupplier(row, supplier));
|
||||
@ -1208,7 +1274,10 @@ async function getReturnStockWarningExportRows(options = {}) {
|
||||
warningLabel: WARNING_LABELS[row.warningLevel] || WARNING_LABELS.normal,
|
||||
unitPriceLevelLabel: PRICE_WARNING_LABELS[row.unitPriceLevel] || '',
|
||||
returnDeadlineLabel: formatReturnDeadlineLabel(row.returnDeadlineStatus, row.returnRemainDays),
|
||||
isOutboundLabel: row.isOutbound ? '是' : '否',
|
||||
supplierCoopLabel: row.supplierCoopLabel || SUPPLIER_COOP_LABELS.unknown,
|
||||
// 工作平台未接入时导出显示「待接入」，避免空列被误读成「没有退厂时间」
|
||||
latestExitDateLabel: row.latestExitDateLabel
|
||||
|| (row.latestExitDateAvailable ? '—' : '待接入'),
|
||||
sourceLabel: row.createdAtSourceLabel || '',
|
||||
createdAtRaw: undefined,
|
||||
}));
|
||||
|
||||
@ -135,8 +135,114 @@ async function fetchGradeRows(cloudDb, wmsCoIds, { shipFrom, shipTo, days }) {
|
||||
}));
|
||||
}
|
||||
|
||||
/**
|
||||
* 「销售=0」档的 SKU 级明细（导出用）。
|
||||
* 口径与 fetchGradeRows 完全一致：同一分档表达式命中 gradeKey='zero'，
|
||||
* 即统计窗口内销量 <= 0 且「有库存或有销量」的 SKU。
|
||||
*
|
||||
* 系统仓位取自 rpt.jst_packInventoryInfo.bin（jst_sku.bin 全为空、库存表无仓位字段）。
|
||||
* 每个仓位独立一行，库存数量按该仓位的实际 qty 统计（各仓位行之和 = SKU 总库存）：
|
||||
* - 有仓位数据的 SKU：按 (wms_co_id, sku_id, bin) 展开，qty=0 的仓位行剔除；
|
||||
* - 无仓位数据的 SKU（仓位表未覆盖的仓库）：保留一行，仓位为空、库存取 SKU 总库存，避免丢数。
|
||||
* @returns {Promise<Array<{wmsCoId:string, skuId:string, skuName:string, binName:string, stockQty:number, salesQty:number}>>}
|
||||
*/
|
||||
async function fetchZeroSaleSkuRows(cloudDb, wmsCoIds, { shipFrom, shipTo, days }) {
|
||||
if (!cloudDb || typeof cloudDb.query !== 'function') {
|
||||
throw new TypeError('fetchZeroSaleSkuRows 的第一个参数必须是业务库实例');
|
||||
}
|
||||
if (!Array.isArray(wmsCoIds) || wmsCoIds.length === 0) return [];
|
||||
if (!shipFrom || !shipTo || !days) return [];
|
||||
|
||||
const invIn = buildInClause(wmsCoIds, 'w');
|
||||
const shipIn = buildInClause(wmsCoIds, 'v');
|
||||
const gradeCase = buildGradeCase();
|
||||
|
||||
const sqlText = `
|
||||
WITH bin AS (
|
||||
SELECT p.wms_co_id AS wmsCoId,
|
||||
p.sku_id AS skuId,
|
||||
LTRIM(RTRIM(p.bin)) AS b,
|
||||
SUM(CAST(ISNULL(p.qty, 0) AS DECIMAL(18, 2))) AS binQty
|
||||
FROM rpt.jst_packInventoryInfo p
|
||||
WHERE ISNULL(p.bin, '') <> ''
|
||||
AND ISNULL(p.qty, 0) <> 0
|
||||
GROUP BY p.wms_co_id, p.sku_id, LTRIM(RTRIM(p.bin))
|
||||
),
|
||||
inv AS (
|
||||
SELECT i.wms_co_id AS wmsCoId,
|
||||
i.sku_id AS skuId,
|
||||
SUM(CAST(ISNULL(i.qty, 0) AS DECIMAL(18, 2))) AS stockQty
|
||||
FROM rpt.jst_inventoryInfo i
|
||||
WHERE i.wms_co_id IN (${invIn.clause})
|
||||
GROUP BY i.wms_co_id, i.sku_id
|
||||
),
|
||||
ship AS (
|
||||
SELECT o.wms_co_id AS wmsCoId,
|
||||
oi.sku_id AS skuId,
|
||||
SUM(CAST(ISNULL(oi.qty, 0) AS DECIMAL(18, 2))) AS salesQty
|
||||
FROM rpt.jst_OrderItem oi
|
||||
JOIN rpt.jst_Orders o ON o.o_id = oi.o_id
|
||||
WHERE o.wms_co_id IN (${shipIn.clause})
|
||||
AND o.send_date >= @shipFrom
|
||||
AND o.send_date < @shipTo
|
||||
AND ISNULL(o.status, '') NOT IN ('Delete', 'Cancelled')
|
||||
GROUP BY o.wms_co_id, oi.sku_id
|
||||
),
|
||||
base AS (
|
||||
SELECT u.wmsCoId,
|
||||
u.skuId,
|
||||
SUM(u.stockQty) AS stockQty,
|
||||
SUM(u.salesQty) AS salesQty
|
||||
FROM (
|
||||
SELECT wmsCoId, skuId, stockQty, CAST(0 AS DECIMAL(18, 2)) AS salesQty FROM inv
|
||||
UNION ALL
|
||||
SELECT wmsCoId, skuId, CAST(0 AS DECIMAL(18, 2)) AS stockQty, salesQty FROM ship
|
||||
) u
|
||||
GROUP BY u.wmsCoId, u.skuId
|
||||
),
|
||||
graded AS (
|
||||
SELECT b.wmsCoId,
|
||||
b.skuId,
|
||||
b.stockQty,
|
||||
b.salesQty,
|
||||
${gradeCase} AS gradeKey
|
||||
FROM base b
|
||||
WHERE ABS(ISNULL(b.stockQty, 0)) > 0.0001
|
||||
OR ABS(ISNULL(b.salesQty, 0)) > 0.0001
|
||||
)
|
||||
SELECT g.wmsCoId,
|
||||
g.skuId,
|
||||
ISNULL(s.name, '') AS skuName,
|
||||
ISNULL(bn.b, '') AS binName,
|
||||
CAST(COALESCE(bn.binQty, g.stockQty) AS DECIMAL(18, 2)) AS stockQty,
|
||||
CAST(g.salesQty AS DECIMAL(18, 2)) AS salesQty
|
||||
FROM graded g
|
||||
LEFT JOIN rpt.jst_sku s ON s.sku_id = g.skuId
|
||||
LEFT JOIN bin bn ON bn.wmsCoId = g.wmsCoId AND bn.skuId = g.skuId
|
||||
WHERE g.gradeKey = 'zero'
|
||||
ORDER BY g.wmsCoId, g.stockQty DESC, g.skuId, bn.b`;
|
||||
|
||||
const rows = await cloudDb.query(sqlText, {
|
||||
...invIn.params,
|
||||
...shipIn.params,
|
||||
shipFrom,
|
||||
shipTo,
|
||||
days,
|
||||
}, { requestTimeout: 180000 });
|
||||
|
||||
return rows.map((r) => ({
|
||||
wmsCoId: String(r.wmsCoId),
|
||||
skuId: String(r.skuId ?? ''),
|
||||
skuName: r.skuName == null ? '' : String(r.skuName),
|
||||
binName: r.binName == null ? '' : String(r.binName),
|
||||
stockQty: Number(r.stockQty) || 0,
|
||||
salesQty: Number(r.salesQty) || 0,
|
||||
}));
|
||||
}
|
||||
|
||||
module.exports = {
|
||||
buildInClause,
|
||||
buildGradeCase,
|
||||
fetchGradeRows,
|
||||
fetchZeroSaleSkuRows,
|
||||
};
|
||||
|
||||
140
backend/src/services/supplierExitDateProvider.js
Normal file
140
backend/src/services/supplierExitDateProvider.js
Normal file
@ -0,0 +1,140 @@
|
||||
'use strict';
|
||||
|
||||
/**
|
||||
* 「最晚退厂时间」取数（2026-09-28 新增）
|
||||
*
|
||||
* 业务口径：供应商不再合作时，工作平台会给该供应商一个「最晚退厂时间」，
|
||||
* 即它放在仓里的货最后必须拉走的日期，看板上跟「供应商是否合作」列并排展示。
|
||||
*
|
||||
* ── 数据在哪 ──────────────────────────────────────────────────────────────
|
||||
* 工作平台库。副本实例（218.93.9.21:1433）上共有 9 个库：
|
||||
* master / model / msdb / tempdb / SSISDB / JSReportReplica（副本，我们只读它）/
|
||||
* DataPlatformControl / WorkbenchDataShared / WorkbenchIdentity。
|
||||
* 「工作平台」= WorkbenchDataShared（配套身份库 WorkbenchIdentity，控制库 DataPlatformControl）。
|
||||
*
|
||||
* ── 当前为什么是空的（2026-09-28 已逐条取证，别再重复排查）───────────────
|
||||
* ① 副本账号 cangku 访问 WorkbenchDataShared / WorkbenchIdentity / DataPlatformControl
|
||||
* **一律被拒**：The server principal "cangku" is not able to access the database
|
||||
* "WorkbenchDataShared" under the current security context.
|
||||
* ② 系统设置（database_configs）里只有 cloud_database_public（JSReportReplica）与
|
||||
* product_information_database（PostgreSQL），没有工作平台库。
|
||||
* ③ 本机 .env 与生产 /opt/cangku/shared/backend.env 里的 JIAN_SUN_DB_HOST/USER/PASSWORD、
|
||||
* WORK_ORDER_* 全部为空（只留了端口/库名占位）。
|
||||
* ④ 副本库内部没有任何「退厂」字段：5,496 个字段全量搜过，COLUMN_NAME 含「退厂」的为 0；
|
||||
* 最接近的 fys_supplier.NoCoopTime（不合作时间）**不能拿来顶替** —— 它是停更快照
|
||||
* （DownloadTime 多在 2023~2024），崔桥仓 8 个不合作供应商里只有 1 个有值，
|
||||
* 而且那 1 条自身还标着 IsCoop='合作'，自相矛盾。
|
||||
*
|
||||
* ── IT 开通后怎么接（只需改本文件）────────────────────────────────────────
|
||||
* 二选一，改完即生效，页面与导出都会自动出数：
|
||||
* A. 给副本账号授只读：让 IT 执行 `USE WorkbenchDataShared; GRANT SELECT TO [cangku];`
|
||||
* （或建同义词/视图），然后把下面 EXIT_DATE_QUERY 填成跨库查询，例如：
|
||||
* SELECT SupplierName AS supplierName, CONVERT(varchar(10), LatestExitDate, 120) AS exitDate
|
||||
* FROM WorkbenchDataShared.dbo.<供应商不合作表>
|
||||
* B. 让 IT 把「供应商不合作」表同步进 JSReportReplica（像 js_supplier / fys_supplier 那样），
|
||||
* 再把 EXIT_DATE_QUERY 指向副本里的表名。
|
||||
* 查询只需要返回两列：supplierName（供应商名，口径同 js_supplier.scmname / jst_sku.supplier_name，
|
||||
* 即简称）与 exitDate（最晚退厂日期，字符串 'YYYY-MM-DD' 或 datetime 均可）。
|
||||
*
|
||||
* 未接入时本模块恒返回 available=false，页面该列显示「待接入」，不会报错、不影响其他列。
|
||||
*/
|
||||
|
||||
const cloudDb = require('../config/cloudDatabase');
|
||||
|
||||
const SOURCE_LABEL = '工作平台（WorkbenchDataShared）· 供应商不合作';
|
||||
const UNAVAILABLE_MESSAGE = '工作平台库尚未授权，待 IT 开通后自动出数';
|
||||
|
||||
// ---- 接入点（IT 开通后配这两个环境变量即可，不必改代码）--------------------
|
||||
// SUPPLIER_EXIT_DATE_ENABLED=true
|
||||
// SUPPLIER_EXIT_DATE_SQL=<查询语句，需返回 supplierName / exitDate 两列>
|
||||
// 也可以在下面 CODE_* 常量里直接写死（env 优先）。
|
||||
const CODE_EXIT_DATE_ENABLED = false;
|
||||
const CODE_EXIT_DATE_QUERY = '';
|
||||
const ENABLED = process.env.SUPPLIER_EXIT_DATE_ENABLED === 'true' || CODE_EXIT_DATE_ENABLED;
|
||||
const EXIT_DATE_QUERY = process.env.SUPPLIER_EXIT_DATE_SQL || CODE_EXIT_DATE_QUERY;
|
||||
// ---------------------------------------------------------------------------
|
||||
|
||||
const CACHE_TTL_MS = Number.parseInt(process.env.SUPPLIER_EXIT_DATE_CACHE_TTL_MS, 10) >= 0
|
||||
? Number.parseInt(process.env.SUPPLIER_EXIT_DATE_CACHE_TTL_MS, 10)
|
||||
: 30 * 60 * 1000;
|
||||
|
||||
let cache = null; // { at, payload }
|
||||
|
||||
const formatExitDate = (value) => {
|
||||
if (value === null || value === undefined || value === '') return '';
|
||||
if (value instanceof Date) {
|
||||
if (Number.isNaN(value.getTime())) return '';
|
||||
const y = value.getFullYear();
|
||||
const m = String(value.getMonth() + 1).padStart(2, '0');
|
||||
const d = String(value.getDate()).padStart(2, '0');
|
||||
return `${y}-${m}-${d}`;
|
||||
}
|
||||
const text = String(value).trim();
|
||||
if (!text) return '';
|
||||
const match = text.match(/^(\d{4})[-/.](\d{1,2})[-/.](\d{1,2})/);
|
||||
if (!match) return text;
|
||||
return `${match[1]}-${match[2].padStart(2, '0')}-${match[3].padStart(2, '0')}`;
|
||||
};
|
||||
|
||||
/**
|
||||
* 取「供应商 → 最晚退厂时间」映射。
|
||||
* 返回 { available, sourceLabel, message, bySupplier: Map<string, string> }
|
||||
*/
|
||||
async function loadSupplierExitDates() {
|
||||
const describe = () => ({
|
||||
available: false,
|
||||
sourceLabel: SOURCE_LABEL,
|
||||
message: UNAVAILABLE_MESSAGE,
|
||||
bySupplier: new Map(),
|
||||
});
|
||||
|
||||
if (!ENABLED || !EXIT_DATE_QUERY) return describe();
|
||||
if (cache && Date.now() - cache.at < CACHE_TTL_MS) return cache.payload;
|
||||
|
||||
try {
|
||||
const rows = await cloudDb.query(EXIT_DATE_QUERY, {}, { requestTimeout: 120000 });
|
||||
const bySupplier = new Map();
|
||||
(rows || []).forEach((row) => {
|
||||
const name = String(row.supplierName || '').trim();
|
||||
const date = formatExitDate(row.exitDate);
|
||||
if (!name || !date) return;
|
||||
const prev = bySupplier.get(name);
|
||||
// 同一供应商多条时取最晚的日期
|
||||
if (!prev || date > prev) bySupplier.set(name, date);
|
||||
});
|
||||
const payload = {
|
||||
available: true,
|
||||
sourceLabel: SOURCE_LABEL,
|
||||
message: '',
|
||||
bySupplier,
|
||||
};
|
||||
cache = { at: Date.now(), payload };
|
||||
return payload;
|
||||
} catch (error) {
|
||||
// 取不到不算致命：该列留空、页面提示待接入，其余列照常
|
||||
console.warn('[SupplierExitDate] 最晚退厂时间取数失败:', error.message);
|
||||
const payload = {
|
||||
available: false,
|
||||
sourceLabel: SOURCE_LABEL,
|
||||
message: `工作平台库读取失败（${error.message}），该列暂空`,
|
||||
bySupplier: new Map(),
|
||||
};
|
||||
cache = { at: Date.now(), payload };
|
||||
return payload;
|
||||
}
|
||||
}
|
||||
|
||||
const EMPTY_EXIT_DATES = Object.freeze({
|
||||
available: false,
|
||||
sourceLabel: SOURCE_LABEL,
|
||||
message: UNAVAILABLE_MESSAGE,
|
||||
bySupplier: new Map(),
|
||||
});
|
||||
|
||||
module.exports = {
|
||||
loadSupplierExitDates,
|
||||
formatExitDate,
|
||||
EMPTY_EXIT_DATES,
|
||||
EXIT_DATE_SOURCE_LABEL: SOURCE_LABEL,
|
||||
EXIT_DATE_UNAVAILABLE_MESSAGE: UNAVAILABLE_MESSAGE,
|
||||
};
|
||||
246
backend/src/services/warehouseShipTimelinessRegistry.js
Normal file
246
backend/src/services/warehouseShipTimelinessRegistry.js
Normal file
@ -0,0 +1,246 @@
|
||||
'use strict';
|
||||
|
||||
/**
|
||||
* 仓储中心 24 小时发货时效（2026-10-20 新增）
|
||||
* ────────────────────────────────────────────────────────────────────────────
|
||||
* 需求（yanhui 2026-10-20）：
|
||||
* MKH-BOM 列表左侧加一栏「仓储中心24小时发货时效」，下列 11 个仓：
|
||||
* 横林二仓 / 横林三仓 / 横林二仓品牌仓 / 崔桥工厂仓 / 崔桥工厂品牌仓 /
|
||||
* 崔桥工厂半成品仓 / 新苏北仓 / 睢宁仓 / 山东青岗集仓 / 山东品牌仓 / 河南洛宁仓
|
||||
* 从系统库存抓取「已发货订单」与「未发货订单」，按**订单创建时间**算是否及时。
|
||||
* 每天 12:00 核验「当天创建、7:00 前」的订单是否已发货；
|
||||
* 每天 16:00 核验「当天创建、16:00 前」的订单是否已发货。
|
||||
* 展示口径：**只显示超时未发**（业务确认）。
|
||||
*
|
||||
* 业务确认的口径（2026-10-20 AskUserQuestion）：
|
||||
* ① 子仓归属主仓 —— **连子仓一起算**（如 横林二仓 含京东云仓/京东自营仓/运营C专用仓 等）
|
||||
* ② 崔桥工厂半成品仓 —— 系统无同名仓，**先留空占位**（不映射到 15718196）
|
||||
* ③ 展示结构 —— **只显示超时未发**
|
||||
*
|
||||
* ── 数据源 ────────────────────────────────────────────────────────────────
|
||||
* 订单：两张表**店铺互斥，必须都读**（见 mkhBomService 文件头）
|
||||
* rpt.jst_Orders —— 京东 / 拼多多 / 抖音 / 得物 / 唯品会
|
||||
* rpt.jst_qmcust_orderlist —— 天猫 / 淘宝 / 1688
|
||||
* 字段口径：
|
||||
* · 创建时间：`order_date`（订单创建时间，业务要求按此算时效）
|
||||
* · 发货时间：`send_date`（为空 = 未发货）
|
||||
* · 发货仓： `wms_co_id`（订单**发货仓**，不是 SKU 的归属仓）
|
||||
* ⚠️ 用户原话「从系统库存抓取」—— 本实现取的是**订单维度**的仓库归属，
|
||||
* 因为 jst_inventoryInfo 只有「某 SKU 在某仓有多少件」，没有订单记录。
|
||||
*
|
||||
* ── 仓库归属（按名称包含匹配，最长匹配优先）────────────────────────────────
|
||||
* `rpt.jst_warehouseInfo` 实测（2026-10-20）：
|
||||
* 横林二仓 15033302 + 15197138/15301814/15835893/16040225/16132681
|
||||
* 横林三仓 15043561
|
||||
* 横林二仓品牌仓 16132681（**同时命中也属于「横林二仓」，靠最长匹配优先归到品牌仓**）
|
||||
* 崔桥工厂仓 15038426 + 15301815/16040410
|
||||
* 崔桥工厂品牌仓 15870495
|
||||
* 崔桥工厂半成品仓 —— 无同名仓，留空
|
||||
* 新苏北仓 15038347 + 15315031/15301812
|
||||
* 睢宁仓 15032603 + 15051907/15051960/15052203/15301813/15386911
|
||||
* 山东青岗集仓 15137523 + 15283033/15406664/15835935/16040432/16129076
|
||||
* 山东品牌仓 15182705
|
||||
* 河南洛宁仓 15539084
|
||||
* ⟹ 仓库 ID → 主仓 的映射**在启动时从库表读取并缓存**（不用手写 ID 常量，
|
||||
* 避免上游新增子仓后要改代码；新增子仓只要名字里带主仓关键词就会自动归入）。
|
||||
* 缓存 TTL 1 小时，可用 clearWarehouseCache() 手动清。
|
||||
*
|
||||
* ── 时效判定 ──────────────────────────────────────────────────────────────
|
||||
* 截止时刻（每日两轮，取**当前已生效且最晚**的那一轮；未到 12:00 时无截止）：
|
||||
* 12:00 → 核验「当天 00:00 ~ 07:00 创建」的订单
|
||||
* 16:00 → 核验「当天 00:00 ~ 16:00 创建」的订单
|
||||
* 判定：
|
||||
* · 超时未发 = `send_date IS NULL` 且 `order_date <= 截止时刻`
|
||||
* · 按时已发 = `send_date IS NOT NULL` 且 `send_date <= 截止时刻` → **不计入超时**
|
||||
* · 已发但超时 = `send_date > 截止时刻` → 也算超时（已发，但没赶上截止）
|
||||
* 业务只要「超时未发」，所以列表只返回 `send_date IS NULL` 的超时单；
|
||||
* 但 summary 里仍给出 `overdueShipped`，避免「已发但超时」被当成没超时。
|
||||
*
|
||||
* ── 时区 ──────────────────────────────────────────────────────────────────
|
||||
* 订单时间列存的是**东八区本地时间**（实测 `order_date` 与 `created` 相差十几秒，
|
||||
* 均为北京时间）。因此 SQL 里统一用 `CAST(GETDATE() AS date)` 取「今天」，
|
||||
* 服务器与数据库都在东八区；**不要**在 Node 侧用 UTC 日期做判断。
|
||||
*/
|
||||
|
||||
const cloudDatabase = require('../config/cloudDatabase');
|
||||
|
||||
// ── 主仓定义（业务给定的 11 个）──────────────────────────────────────────
|
||||
// keywords：该主仓在 rpt.jst_warehouseInfo.name 里应包含的关键词（任一命中即归属）
|
||||
// 顺序即展示顺序；`match` 用最长关键词优先，所以「横林二仓品牌仓」不会被「横林二仓」抢走。
|
||||
const CENTERS = Object.freeze([
|
||||
{ key: 'HL2', name: '横林二仓', keywords: ['横林二仓'] },
|
||||
{ key: 'HL3', name: '横林三仓', keywords: ['横林三仓'] },
|
||||
{ key: 'HL2_BRAND', name: '横林二仓品牌仓', keywords: ['横林二仓品牌仓'] },
|
||||
{ key: 'CQ', name: '崔桥工厂仓', keywords: ['崔桥工厂仓'] },
|
||||
{ key: 'CQ_BRAND', name: '崔桥工厂品牌仓', keywords: ['崔桥工厂品牌仓'] },
|
||||
// 系统内无同名仓（最接近的「简尚-崔桥工厂半成品包装项目仓」15718196 是配件仓，
|
||||
// 业务确认先留空占位，不并进来）
|
||||
{ key: 'CQ_WIP', name: '崔桥工厂半成品仓', keywords: [], placeholder: true },
|
||||
{ key: 'XSB', name: '新苏北仓', keywords: ['新苏北'] },
|
||||
{ key: 'SN', name: '睢宁仓', keywords: ['睢宁'] },
|
||||
{ key: 'SD_QGJ', name: '山东青岗集仓', keywords: ['山东青岗集'] },
|
||||
{ key: 'SD_BRAND', name: '山东品牌仓', keywords: ['山东品牌仓'] },
|
||||
{ key: 'HN_LN', name: '河南洛宁仓', keywords: ['河南洛宁'] },
|
||||
]);
|
||||
|
||||
// 「横林三仓」与「横林二仓」不含对方，安全；但「崔桥工厂品牌仓」含「崔桥工厂仓」片段，
|
||||
// 靠关键词长度倒序匹配即可（见 resolveCenter）。
|
||||
const SORTED_KEYWORDS = CENTERS
|
||||
.filter((c) => c.keywords.length)
|
||||
.flatMap((c) => c.keywords.map((kw) => ({ kw, center: c })))
|
||||
.sort((a, b) => b.kw.length - a.kw.length);
|
||||
|
||||
// ── 时效轮次（业务给定）──────────────────────────────────────────────────
|
||||
// cutoffHour：当天几点截止；windowEndHour：只核验「创建时间不晚于该时刻」的订单
|
||||
const ROUNDS = Object.freeze([
|
||||
{ key: 'NOON', label: '12:00 轮', cutoffHour: 12, windowEndHour: 7 },
|
||||
{ key: 'AFTERNOON', label: '16:00 轮', cutoffHour: 16, windowEndHour: 16 },
|
||||
]);
|
||||
|
||||
// 只排除取消/合并单；Question 仍计入（与 mkhBomService 待发货口径一致）
|
||||
//
|
||||
// 🔥 Split（拆单）：**必须排除**。拆单是「父单被拆成子单」的中间态，父单本身
|
||||
// 不会发货（`send_date` 永远为空），子单才是真正进仓发货的那一单。
|
||||
// 2026-10-20 实测：不加这个条件，一年前的 Split 父单全被算成「超时未发」，
|
||||
// 全库未发 Split 单占比极高 —— 会让页面变成上万条的垃圾列表。
|
||||
// ⚠️ 与 mkhBomService 的差异：那边是「预警今天要发的货」，Split 父单不带真实
|
||||
// 发货需求，被算进去也无害；这边是「考核仓库时效」，误算就要背锅，必须排除。
|
||||
const EXCLUDED_STATUS_SQL = `ISNULL(o.status, N'') NOT IN (N'Cancelled', N'Merged', N'Split')`;
|
||||
|
||||
const WAREHOUSE_CACHE_TTL_MS = 60 * 60 * 1000;
|
||||
let warehouseCache = { at: 0, rows: [] };
|
||||
|
||||
function toNumber(value) {
|
||||
const n = Number(value);
|
||||
return Number.isFinite(n) ? n : 0;
|
||||
}
|
||||
|
||||
function pad2(n) {
|
||||
return String(n).padStart(2, '0');
|
||||
}
|
||||
|
||||
/**
|
||||
* 用「关键词最长匹配」把仓库名归到主仓。
|
||||
* @returns {object|null} CENTERS 里的元素；无法归属返回 null
|
||||
*/
|
||||
function resolveCenter(warehouseName) {
|
||||
if (!warehouseName) return null;
|
||||
for (const item of SORTED_KEYWORDS) {
|
||||
if (warehouseName.includes(item.kw)) return item.center;
|
||||
}
|
||||
return null;
|
||||
}
|
||||
|
||||
/**
|
||||
* 拉取仓库表并建立「主仓 → wms_co_id 列表」映射。
|
||||
* 缓存 1 小时；仓库表变更不频繁，且新增子仓会自动被关键词捞到。
|
||||
*/
|
||||
async function loadCenterWarehouseMap(force = false) {
|
||||
const fresh = Date.now() - warehouseCache.at < WAREHOUSE_CACHE_TTL_MS;
|
||||
if (!force && fresh && warehouseCache.rows.length) {
|
||||
return warehouseCache.rows;
|
||||
}
|
||||
// ⚠️ cloudDatabase.query() 直接返回 recordset 数组，不是 {recordset} 对象
|
||||
const rows = (await cloudDatabase.query(
|
||||
`SELECT Id, name, wms_co_id
|
||||
FROM dbo.jst_warehouseInfo`,
|
||||
{},
|
||||
{ requestTimeout: 60000 }
|
||||
)) || [];
|
||||
const byCenter = new Map(CENTERS.map((c) => [c.key, []]));
|
||||
const unmatched = [];
|
||||
for (const row of rows) {
|
||||
const center = resolveCenter(String(row.name || ''));
|
||||
if (center && byCenter.has(center.key)) {
|
||||
byCenter.get(center.key).push({
|
||||
wmsCoId: Number(row.wms_co_id),
|
||||
warehouseId: Number(row.Id),
|
||||
name: String(row.name || ''),
|
||||
});
|
||||
} else {
|
||||
unmatched.push({ wmsCoId: Number(row.wms_co_id), name: String(row.name || '') });
|
||||
}
|
||||
}
|
||||
const list = CENTERS.map((c) => ({
|
||||
key: c.key,
|
||||
name: c.name,
|
||||
placeholder: Boolean(c.placeholder),
|
||||
warehouses: byCenter.get(c.key) || [],
|
||||
}));
|
||||
warehouseCache = { at: Date.now(), rows: list, unmatchedCount: unmatched.length };
|
||||
return list;
|
||||
}
|
||||
|
||||
function clearWarehouseCache() {
|
||||
warehouseCache = { at: 0, rows: [] };
|
||||
}
|
||||
|
||||
/**
|
||||
* 计算「当前已生效」的核验轮次。
|
||||
*
|
||||
* 12:00 之前 → **未到核验时间，返回 null**。
|
||||
* 业务明确要求（2026-10-20 确认）：12:00 之前不做核验，
|
||||
* 前端显示「未到核验时间」，**不要回填昨天的数据** ——
|
||||
* 昨天已结算的结果留到昨天，拿到今天看容易被当成今天的超时。
|
||||
* 12:00~15:59 → 用 12:00 轮（窗口 00:00~07:00）
|
||||
* 16:00 之后 → 用 16:00 轮（窗口 00:00~16:00，覆盖范围更大）
|
||||
*
|
||||
* @param {Date} now 以**东八区本地时间**为准的 Date（生产/本地机器均为东八区）
|
||||
* @returns {object|null} null 表示当前不在任何核验时段内
|
||||
*/
|
||||
function resolveActiveRound(now = new Date()) {
|
||||
const hour = now.getHours();
|
||||
const minute = now.getMinutes();
|
||||
const minutes = hour * 60 + minute;
|
||||
let active = null;
|
||||
for (const round of ROUNDS) {
|
||||
if (minutes >= round.cutoffHour * 60) active = round;
|
||||
}
|
||||
|
||||
// 12:00 前：首轮核验还没开始，不做判定
|
||||
if (!active) return null;
|
||||
|
||||
const y = now.getFullYear();
|
||||
const m = pad2(now.getMonth() + 1);
|
||||
const d = pad2(now.getDate());
|
||||
return {
|
||||
...active,
|
||||
deadline: `${y}-${m}-${d} ${pad2(active.cutoffHour)}:00:00`,
|
||||
windowStart: `${y}-${m}-${d} 00:00:00`,
|
||||
windowEnd: `${y}-${m}-${d} ${pad2(active.windowEndHour)}:00:00`,
|
||||
date: `${y}-${m}-${d}`,
|
||||
settled: false,
|
||||
isPreviousDay: false,
|
||||
};
|
||||
}
|
||||
|
||||
/**
|
||||
* 取「当天两轮」的完整描述（页面顶部展示用），不论是否已到时间。
|
||||
*/
|
||||
function describeRounds(now = new Date()) {
|
||||
const y = now.getFullYear();
|
||||
const m = pad2(now.getMonth() + 1);
|
||||
const d = pad2(now.getDate());
|
||||
const date = `${y}-${m}-${d}`;
|
||||
return ROUNDS.map((r) => ({
|
||||
key: r.key,
|
||||
label: r.label,
|
||||
date,
|
||||
deadline: `${date} ${pad2(r.cutoffHour)}:00:00`,
|
||||
windowStart: `${date} 00:00:00`,
|
||||
windowEnd: `${date} ${pad2(r.windowEndHour)}:00:00`,
|
||||
active: now.getHours() * 60 + now.getMinutes() >= r.cutoffHour * 60,
|
||||
}));
|
||||
}
|
||||
|
||||
module.exports = {
|
||||
CENTERS,
|
||||
ROUNDS,
|
||||
resolveCenter,
|
||||
loadCenterWarehouseMap,
|
||||
clearWarehouseCache,
|
||||
resolveActiveRound,
|
||||
describeRounds,
|
||||
EXCLUDED_STATUS_SQL,
|
||||
toNumber,
|
||||
};
|
||||
528
backend/src/services/warehouseShipTimelinessService.js
Normal file
528
backend/src/services/warehouseShipTimelinessService.js
Normal file
@ -0,0 +1,528 @@
|
||||
'use strict';
|
||||
|
||||
/**
|
||||
* 仓储中心 24 小时发货时效 —— 取数/聚合服务（2026-10-20 新增）
|
||||
* ────────────────────────────────────────────────────────────────────────────
|
||||
* 配置与口径定义见 warehouseShipTimelinessRegistry.js（主仓关键词、轮次、时区说明）。
|
||||
*
|
||||
* ── 一次请求做几件事 ──────────────────────────────────────────────────────
|
||||
* ① 读仓库表 → 主仓 → wms_co_id 集合（缓存 1h）
|
||||
* ② 一次 SQL 同时取「超时未发」和「已超时已发」两类，按 wms_co_id 分组
|
||||
* ③ 归并到主仓，返回每仓的 count / orderAmount，以及全量超时明细
|
||||
*
|
||||
* ── SQL 设计要点（踩过的坑）───────────────────────────────────────────────
|
||||
* · 两张订单表**店铺互斥**，用 UNION ALL 合并后一次性分组，不要分别查再在
|
||||
* Node 侧合并 —— 两趟扫描在 rpt 视图上代价翻倍。
|
||||
* · `wms_co_id IN (...)` 直接内联仓库 ID（都是整数，无注入风险），因为仓库
|
||||
* 集合动态生成，用参数化会引入可变数量的 @p0..@pN，反而难维护。
|
||||
* · **不要在 ORDER BY 里对聚合列做算术**（见 MEMORY：会触发灾难计划）。
|
||||
* 这里 SQL 只 GROUP BY 取原始计数/金额，排序与派生字段全在 Node 侧算。
|
||||
* · 时间比较一律用**字面量字符串**（形如 '2026-10-20 16:00:00'），由
|
||||
* resolveActiveRound 在东八区算出；不用 GETDATE() 做相对偏移，避免
|
||||
* 数据库与应用的时区假设不一致。
|
||||
* · 明细行带 `TOP (@limit)`，`@limit` 走参数化，避免界面一次拉几万行。
|
||||
*/
|
||||
|
||||
const cloudDatabase = require('../config/cloudDatabase');
|
||||
const registry = require('./warehouseShipTimelinessRegistry');
|
||||
|
||||
const { CENTERS, EXCLUDED_STATUS_SQL, toNumber } = registry;
|
||||
|
||||
// 明细上限：只用于页面展示，超出部分用 truncated 标记提示
|
||||
const DEFAULT_DETAIL_LIMIT = 500;
|
||||
const MAX_DETAIL_LIMIT = 2000;
|
||||
|
||||
/**
|
||||
* 把 UTC Date / 字符串 统一格式化成 'YYYY-MM-DD HH:mm:ss'（按服务器本地时区）。
|
||||
* mssql 驱动默认把 datetime 解析为 UTC 语义的 Date，这里只做展示，统一成
|
||||
* 与库内一致的东八区墙钟时间。
|
||||
*/
|
||||
function formatDateTime(value) {
|
||||
if (!value) return '';
|
||||
if (typeof value === 'string') return value.replace('T', ' ').slice(0, 19);
|
||||
const d = value instanceof Date ? value : new Date(value);
|
||||
if (Number.isNaN(d.getTime())) return '';
|
||||
const pad = (n) => String(n).padStart(2, '0');
|
||||
return `${d.getFullYear()}-${pad(d.getMonth() + 1)}-${pad(d.getDate())} `
|
||||
+ `${pad(d.getHours())}:${pad(d.getMinutes())}:${pad(d.getSeconds())}`;
|
||||
}
|
||||
|
||||
/** 两个 'YYYY-MM-DD HH:mm:ss' 相差多少分钟（四舍五入） */
|
||||
function diffMinutes(fromText, toText) {
|
||||
const a = new Date(String(fromText).replace(' ', 'T')).getTime();
|
||||
const b = new Date(String(toText).replace(' ', 'T')).getTime();
|
||||
if (!Number.isFinite(a) || !Number.isFinite(b)) return 0;
|
||||
return Math.round((b - a) / 60000);
|
||||
}
|
||||
|
||||
/**
|
||||
* 构造两表合并的子查询。
|
||||
*
|
||||
* 🔥 三条必须同时满足（2026-10-20 实测踩坑）：
|
||||
* ① `order_date >= @windowStart` —— **必须有下界**。只写 `<= @deadline` 会把
|
||||
* 一年前创建、至今未发的历史单全捞进来（实测 13,553 条，最早 2025-09-01）。
|
||||
* ② `order_date <= @deadline` —— 上界：截止时刻后创建的订单不参与本轮考核。
|
||||
* ③ Split 已在 EXCLUDED_STATUS_SQL 里排掉（拆单父单永不发货）。
|
||||
*
|
||||
* @param {number[]} wmsIds 主仓下所有 wms_co_id
|
||||
* @param {string} deadline 截止时刻字面量
|
||||
* @param {'pending'|'shippedLate'} mode 取哪一类
|
||||
*/
|
||||
function buildUnionSql(wmsIds, deadline, mode) {
|
||||
const idList = wmsIds.join(',');
|
||||
// pending：未发货且窗口内已创建 → 超时未发
|
||||
// shippedLate：已发货但发货时间晚于截止时刻 → 已超时已发
|
||||
const cond = mode === 'pending'
|
||||
? `o.send_date IS NULL`
|
||||
: `o.send_date IS NOT NULL AND o.send_date > @deadline`;
|
||||
const branch = (table) => `
|
||||
SELECT N'${table}' AS src, o.o_id, o.so_id, o.shop_name, o.shop_id,
|
||||
o.order_date, o.send_date, o.status, o.shop_status,
|
||||
o.wms_co_id, o.pay_amount, o.labels
|
||||
FROM dbo.${table} o
|
||||
WHERE o.wms_co_id IN (${idList})
|
||||
AND o.order_date >= @windowStart
|
||||
AND o.order_date <= @deadline
|
||||
AND ${cond}
|
||||
AND ${EXCLUDED_STATUS_SQL}`;
|
||||
return `${branch('jst_Orders')}
|
||||
UNION ALL${branch('jst_qmcust_orderlist')}`;
|
||||
}
|
||||
|
||||
/**
|
||||
* 单个主仓的仓库 ID 集合 → 逗号串（用于按主仓分桶时拼 IN 条件）
|
||||
*/
|
||||
function joinIds(ids) {
|
||||
return ids.join(',');
|
||||
}
|
||||
|
||||
/**
|
||||
* 主入口：计算某一天、某一轮次的仓储中心发货时效。
|
||||
*
|
||||
* @param {object} options
|
||||
* @param {string} [options.round] 'NOON' | 'AFTERNOON'，不传则按当前时刻自动判定
|
||||
* @param {Date} [options.now] 仅测试用
|
||||
* @param {number} [options.limit] 明细条数上限
|
||||
* @returns {Promise<object>}
|
||||
*/
|
||||
async function getShipTimeliness(options = {}) {
|
||||
const now = options.now instanceof Date ? options.now : new Date();
|
||||
const limit = Math.min(
|
||||
Math.max(Number(options.limit) || DEFAULT_DETAIL_LIMIT, 1),
|
||||
MAX_DETAIL_LIMIT
|
||||
);
|
||||
|
||||
// ① 主仓 → 仓库映射
|
||||
const centerList = await registry.loadCenterWarehouseMap();
|
||||
const pad2 = (n) => String(n).padStart(2, '0');
|
||||
|
||||
// ② 决定本轮截止时刻
|
||||
const rounds = registry.describeRounds(now);
|
||||
let round = null;
|
||||
if (options.round) {
|
||||
const target = registry.ROUNDS.find((r) => r.key === String(options.round).toUpperCase());
|
||||
if (!target) {
|
||||
const err = new Error(`未知的核验轮次: ${options.round}`);
|
||||
err.statusCode = 400;
|
||||
throw err;
|
||||
}
|
||||
const pad = (n) => String(n).padStart(2, '0');
|
||||
const date = `${now.getFullYear()}-${pad(now.getMonth() + 1)}-${pad(now.getDate())}`;
|
||||
round = {
|
||||
...target,
|
||||
date,
|
||||
deadline: `${date} ${pad(target.cutoffHour)}:00:00`,
|
||||
windowStart: `${date} 00:00:00`,
|
||||
windowEnd: `${date} ${pad(target.windowEndHour)}:00:00`,
|
||||
active: now.getHours() * 60 + now.getMinutes() >= target.cutoffHour * 60,
|
||||
};
|
||||
} else {
|
||||
round = registry.resolveActiveRound(now);
|
||||
}
|
||||
|
||||
// 🔥 12:00 前没有任何轮次生效（业务口径：12:00 才开始第一轮核验）。
|
||||
// 这里**直接返回「未到核验时间」**，既不查库、也不回填昨天 ——
|
||||
// 让业务清楚知道「现在还没到看数据的时候」，而不是拿旧数字去对仓库。
|
||||
if (!round) {
|
||||
const centerList = await registry.loadCenterWarehouseMap();
|
||||
const nextRound = rounds.find((r) => r.cutoffHour === 12) || rounds[0];
|
||||
return {
|
||||
generatedAt: formatDateTime(now),
|
||||
date: `${now.getFullYear()}-${pad2(now.getMonth() + 1)}-${pad2(now.getDate())}`,
|
||||
round: null,
|
||||
notStarted: true,
|
||||
isPreviousDay: false,
|
||||
rounds,
|
||||
nextRoundAt: nextRound ? nextRound.deadline : '',
|
||||
deadline: '',
|
||||
window: null,
|
||||
summary: emptySummary(),
|
||||
centers: buildEmptyCenters(centerList),
|
||||
orders: [],
|
||||
truncated: false,
|
||||
detailLimit: limit,
|
||||
detailDegraded: false,
|
||||
freshness: null,
|
||||
note: '今日 12:00 首轮核验开始后才展示超时数据',
|
||||
};
|
||||
}
|
||||
|
||||
// 没有归属任何主仓的仓库不用查
|
||||
const allWmsIds = [...new Set(
|
||||
centerList.flatMap((c) => c.warehouses.map((w) => w.wmsCoId)).filter((n) => Number.isFinite(n))
|
||||
)];
|
||||
|
||||
if (!allWmsIds.length) {
|
||||
return {
|
||||
generatedAt: formatDateTime(now),
|
||||
date: round.date,
|
||||
round: { key: round.key, label: round.label },
|
||||
isPreviousDay: Boolean(round.isPreviousDay),
|
||||
rounds,
|
||||
deadline: round.deadline,
|
||||
window: { start: round.windowStart, end: round.windowEnd },
|
||||
summary: emptySummary(),
|
||||
centers: buildEmptyCenters(centerList),
|
||||
orders: [],
|
||||
truncated: false,
|
||||
detailLimit: limit,
|
||||
note: '仓库表中未匹配到任何目标主仓，请检查仓库命名',
|
||||
};
|
||||
}
|
||||
|
||||
// ③ 一趟 SQL 取全量分组结果。原实现拆成「超时未发 / 已超时已发 / 窗口总数」
|
||||
// 三条 SQL，在 rpt 视图上三趟全表扫描实测 213 秒；合并成一趟后按
|
||||
// `src + 是否超时` 打标记再 GROUP BY，只扫一次两表。
|
||||
//
|
||||
// 分组维度：主仓 不直接在 SQL 里 GROUP（仓库→主仓的映射在 Node 侧缓存里），
|
||||
// 所以按 wms_co_id 分组，回 Node 侧累加到主仓。
|
||||
//
|
||||
// 🔥 「算不算已发货」的三态判定（2026-10-20 实测新增）：
|
||||
// 上游 `send_date` 回写**明显滞后** —— 实测 10-20 当天 700 单里只有 100 单
|
||||
// 有 send_date，且都集中在 08:16~08:44（刚回写的那几单）。若只看 send_date，
|
||||
// 中午 12:00 核验时当天早上的单会**全部被判超时**，页面毫无价值。
|
||||
// 因此引入出库表 `rpt.jst_salesOutboundInfo` 作为佐证：
|
||||
// · 判为「已发」= send_date 有值 **或** 出库表能查到该 o_id
|
||||
// · 判为「按时」= send_date <= 截止 **或** 出库行 io_date <= 截止
|
||||
// ⚠️ 出库表同样滞后（每天 04:00 起才有数据，当天往往 0 行，次日凌晨灌），
|
||||
// 所以它只能**减少误判**（把「已发的判成没发」纠正过来），
|
||||
// 不能凭空补出「没发的单已经发了」。最终仍可能有误报，故返回值里带
|
||||
// `freshness` 让前端明示「数据可能滞后」。
|
||||
// ⚠️ 出库表 `qty` 列全表恒 0（见 MEMORY），判断有无出库**只看行数 / io_date**。
|
||||
//
|
||||
// 每条订单归属哪一类（互斥）：
|
||||
// pending —— 两源都查不到发货痕迹 → 超时未发
|
||||
// shippedLate —— 有发货痕迹但发货时间 > 截止 → 已发但超时
|
||||
// onTime —— 发货时间 <= 截止 → 按时已发
|
||||
// ⚠️ OUTER APPLY 里只取 `io_date`，用「io_date 是否为 NULL」判断出库表有无命中。
|
||||
// 别在外面引用 `ob.o_id` —— APPLY 子查询只 SELECT 了 io_date，引用不存在的列
|
||||
// 会报 `Invalid column name 'o_id'`（2026-10-20 已踩）。
|
||||
//
|
||||
// ⚡ 性能关键（2026-10-20 实测）：
|
||||
// · 原实现「汇总 SQL + 明细 SQL」各扫一遍窗口，合计 108 秒；
|
||||
// · 窗口内订单只有几百条（实测 420 条），所以改成**一趟取回全部窗口订单明细**，
|
||||
// 汇总与明细都在 Node 里算 —— 少一次大表扫描。
|
||||
// · 返回列加上 pay_amount / status，供 Node 侧分桶。
|
||||
//
|
||||
// 🔥🔥 两表**不能 UNION ALL 成一条 SQL**（2026-10-20 实测，这是本文件最大的坑）：
|
||||
// · `jst_qmcust_orderlist` 是**宽视图**（上百列），拆开单独跑很快，但一旦参与
|
||||
// UNION ALL + 派生表 + OUTER APPLY 的嵌套，优化器会放弃谓词下推，
|
||||
// 变成先物化整表再过滤 —— 实测单条 SQL 跑 200~320 秒。
|
||||
// · 两表**分别查、Node 侧合并**：jst_Orders 5.5 秒、jst_qmcust_orderlist 35 秒，
|
||||
// 合计约 40 秒，比合并版快 5~8 倍。
|
||||
// · 两表店铺完全互斥（京东/拼多多/抖音/得物/唯品会 vs 天猫/淘宝/1688），
|
||||
// o_id 不会有交集，直接拼接即可，不需要再按 o_id 去重（仍保留一次去重兜底）。
|
||||
//
|
||||
// 🔥 `jst_qmcust_orderlist` 单表也有 35 秒，而同样条件的 `jst_Orders` 只要 2 秒。
|
||||
// 曾尝试把它的窗口过滤换成 `created`（实测快 10 倍），但**已证伪，不能用**：
|
||||
// · 2026-10-20 实测 2,581 行里，`order_date` 与 `created` 完全相等的只有 4 行，
|
||||
// 相差 >1 小时的有 412 行，最大差 24.7 天，**198 行甚至跨日**；
|
||||
// · 典型形态：`order_date=10-18 23:58` 而 `created=10-19 04:00:08` ——
|
||||
// 上游每天凌晨 4 点批量补录，`created` 是**灌库时间**不是下单时间；
|
||||
// · 同一窗口下 `created` 比 `order_date` 多命中 30 单（全是补录单）。
|
||||
// 业务口径是「按**订单创建时间**算是否及时」，只有 `order_date` 是对的。
|
||||
// 所以：**两表都用 `order_date`**，性能只能靠「两表分开查 + Node 侧合并」。
|
||||
// ⚠️ 换回 `created` 之前必须先跑 `.workbuddy/tmp/probe-od-vs-created.cjs` 复测。
|
||||
const windowBranch = (table) => `
|
||||
SELECT N'${table}' AS src,
|
||||
o.wms_co_id, o.o_id, o.so_id, o.shop_name, o.order_date,
|
||||
o.send_date, o.status, o.shop_status, o.pay_amount, o.labels,
|
||||
ob.io_date AS ob_io_date
|
||||
FROM dbo.${table} o
|
||||
OUTER APPLY (
|
||||
SELECT TOP 1 s.io_date
|
||||
FROM dbo.jst_salesOutboundInfo s
|
||||
WHERE s.o_id = o.o_id
|
||||
ORDER BY s.io_date ASC
|
||||
) ob
|
||||
WHERE o.wms_co_id IN (${joinIds(allWmsIds)})
|
||||
AND o.order_date >= @windowStart
|
||||
AND o.order_date <= @deadline
|
||||
AND ${EXCLUDED_STATUS_SQL}`;
|
||||
|
||||
const params = { deadline: round.deadline, windowStart: round.windowStart };
|
||||
// ⚠️ 超时给足：qmcust 单表实测 35~80 秒（视图谓词下推较差），
|
||||
// 不能按 jst_Orders 的 5 秒去设，否则会被误杀成 500。
|
||||
const queryOpts = { requestTimeout: 300000, connectRequestTimeoutMs: 320000 };
|
||||
|
||||
// 两表并行查（互不依赖），Node 侧合并
|
||||
const [ordersRows, qmcustRows] = await Promise.allSettled([
|
||||
cloudDatabase.query(windowBranch('jst_Orders'), params, queryOpts),
|
||||
cloudDatabase.query(windowBranch('jst_qmcust_orderlist'), params, queryOpts),
|
||||
]);
|
||||
|
||||
// 🔥 用 allSettled 而不是 all：一张表超时/报错时，另一张表的数据仍然可用，
|
||||
// 否则整页变空白。降级情况记录在 detailDegraded + note 里。
|
||||
const mergeWarnings = [];
|
||||
const windowRows = [];
|
||||
if (ordersRows.status === 'fulfilled') {
|
||||
windowRows.push(...(ordersRows.value || []));
|
||||
} else {
|
||||
mergeWarnings.push(`jst_Orders 查询失败：${ordersRows.reason?.message || '未知错误'}`);
|
||||
}
|
||||
if (qmcustRows.status === 'fulfilled') {
|
||||
windowRows.push(...(qmcustRows.value || []));
|
||||
} else {
|
||||
mergeWarnings.push(`jst_qmcust_orderlist 查询失败：${qmcustRows.reason?.message || '未知错误'}`);
|
||||
}
|
||||
if (!windowRows.length && mergeWarnings.length) {
|
||||
// 两表全挂 —— 这才是真正的失败，直接抛给上层
|
||||
const err = new Error(mergeWarnings.join('；'));
|
||||
err.statusCode = 502;
|
||||
throw err;
|
||||
}
|
||||
|
||||
// ④ 数据新鲜度：出库表最新 io_date。
|
||||
// ⚠️ 只用于「提示用户数据可能滞后」，不参与超时判定。
|
||||
// 两个源都滞后时（出库表当天 0 行 + send_date 昨天就停了），说明上游还没灌数，
|
||||
// 此时「超时未发」的准确性无法保证 —— 前端必须显式告警，不能让业务拿着
|
||||
// 一份假数据去考核仓库。
|
||||
const freshness = await loadShipFreshness(round);
|
||||
|
||||
// ⑤ 三态归类（在 Node 侧做，SQL 只负责取数）
|
||||
const wmsToCenter = new Map();
|
||||
for (const c of centerList) {
|
||||
for (const w of c.warehouses) wmsToCenter.set(w.wmsCoId, c.key);
|
||||
}
|
||||
|
||||
const acc = new Map(centerList.map((c) => [c.key, {
|
||||
pendingCount: 0,
|
||||
pendingAmount: 0,
|
||||
shippedLateCount: 0,
|
||||
onTimeCount: 0,
|
||||
}]));
|
||||
|
||||
const pendingRows = [];
|
||||
// 两表店铺互斥、o_id 理论不重叠，但历史上出现过同一 o_id 被两边都收录的情况，
|
||||
// 按 `${src}-${o_id}` 去重（跨表同 o_id 也算重复，取先到的 jst_Orders）。
|
||||
const seenKey = new Set();
|
||||
for (const row of windowRows || []) {
|
||||
const bucket = acc.get(wmsToCenter.get(Number(row.wms_co_id)));
|
||||
if (!bucket) continue;
|
||||
const dedupeKey = String(row.o_id || '');
|
||||
if (dedupeKey) {
|
||||
if (seenKey.has(dedupeKey)) continue;
|
||||
seenKey.add(dedupeKey);
|
||||
}
|
||||
const kind = bucketOf(row, round.deadline);
|
||||
if (kind === 'pending') {
|
||||
bucket.pendingCount += 1;
|
||||
bucket.pendingAmount += toNumber(row.pay_amount);
|
||||
pendingRows.push(row);
|
||||
} else if (kind === 'shippedLate') {
|
||||
bucket.shippedLateCount += 1;
|
||||
} else {
|
||||
bucket.onTimeCount += 1;
|
||||
}
|
||||
}
|
||||
|
||||
// 明细：只要「超时未发」，最早创建的最急，排前面
|
||||
// 用 formatDateTime 转成 'YYYY-MM-DD HH:mm:ss' 再比字符串 —— 字典序与时间序一致，
|
||||
// 且不受 mssql 驱动把 datetime 解析成 UTC Date 的影响（见文件头时区说明）。
|
||||
pendingRows.sort((a, b) => {
|
||||
const x = formatDateTime(a.order_date);
|
||||
const y = formatDateTime(b.order_date);
|
||||
if (x < y) return -1;
|
||||
if (x > y) return 1;
|
||||
return 0;
|
||||
});
|
||||
const detailRows = pendingRows.slice(0, limit);
|
||||
|
||||
const centers = centerList.map((c) => {
|
||||
const b = acc.get(c.key) || { pendingCount: 0, pendingAmount: 0, shippedLateCount: 0, onTimeCount: 0 };
|
||||
const totalCount = b.pendingCount + b.shippedLateCount + b.onTimeCount;
|
||||
return {
|
||||
key: c.key,
|
||||
name: c.name,
|
||||
placeholder: c.placeholder,
|
||||
warehouseCount: c.warehouses.length,
|
||||
warehouses: c.warehouses.map((w) => ({ wmsCoId: w.wmsCoId, name: w.name })),
|
||||
// 列表主展示：超时未发
|
||||
overdueUnshipped: b.pendingCount,
|
||||
overdueUnshippedAmount: Math.round(b.pendingAmount * 100) / 100,
|
||||
// 辅助信息
|
||||
overdueShipped: b.shippedLateCount,
|
||||
windowOrderCount: totalCount,
|
||||
onTimeRate: totalCount > 0
|
||||
? Math.round((b.onTimeCount / totalCount) * 1000) / 10
|
||||
: null,
|
||||
};
|
||||
});
|
||||
|
||||
const orders = (detailRows || []).map((r) => {
|
||||
const orderDate = formatDateTime(r.order_date);
|
||||
const center = centerList.find((c) => c.key === wmsToCenter.get(Number(r.wms_co_id)));
|
||||
const warehouse = center && center.warehouses.find((w) => w.wmsCoId === Number(r.wms_co_id));
|
||||
return {
|
||||
oId: String(r.o_id || ''),
|
||||
soId: String(r.so_id || ''),
|
||||
source: r.src === 'jst_qmcust_orderlist' ? 'qmcust' : 'orders',
|
||||
shopName: String(r.shop_name || ''),
|
||||
orderDate,
|
||||
sendDate: formatDateTime(r.send_date),
|
||||
status: String(r.status || ''),
|
||||
shopStatus: String(r.shop_status || ''),
|
||||
wmsCoId: Number(r.wms_co_id),
|
||||
warehouseName: warehouse ? warehouse.name : '',
|
||||
centerKey: center ? center.key : '',
|
||||
centerName: center ? center.name : '',
|
||||
payAmount: toNumber(r.pay_amount),
|
||||
labels: String(r.labels || ''),
|
||||
// 相对截止时刻的超时时长（分钟）
|
||||
overdueMinutes: diffMinutes(orderDate, round.deadline),
|
||||
};
|
||||
});
|
||||
|
||||
const summary = centers.reduce((acc2, c) => {
|
||||
acc2.overdueUnshipped += c.overdueUnshipped;
|
||||
acc2.overdueShipped += c.overdueShipped;
|
||||
acc2.overdueUnshippedAmount += c.overdueUnshippedAmount;
|
||||
acc2.windowOrderCount += c.windowOrderCount;
|
||||
if (c.overdueUnshipped > 0) acc2.overdueCenterCount += 1;
|
||||
return acc2;
|
||||
}, emptySummary());
|
||||
summary.overdueUnshippedAmount = Math.round(summary.overdueUnshippedAmount * 100) / 100;
|
||||
// 按时率统一口径：按时 = 窗口内总数 - 超时未发 - 已发超时
|
||||
// （与各仓算法完全一致，避免两处各算一遍算出不同数字）
|
||||
summary.onTimeRate = summary.windowOrderCount > 0
|
||||
? Math.round(((summary.windowOrderCount - summary.overdueUnshipped - summary.overdueShipped)
|
||||
/ summary.windowOrderCount) * 1000) / 10
|
||||
: null;
|
||||
|
||||
const totalPending = summary.overdueUnshipped;
|
||||
|
||||
return {
|
||||
generatedAt: formatDateTime(now),
|
||||
date: round.date,
|
||||
// ⚠️ 前端要展示「核验 07:00 / 16:00 前」，必须把 windowEnd 一起带出去。
|
||||
// 早先只回了 key/label，前端拿不到 cutoff，页面就渲染成「核验 前」。
|
||||
round: {
|
||||
key: round.key,
|
||||
label: round.label,
|
||||
cutoffHour: round.cutoffHour,
|
||||
windowEnd: round.windowEnd,
|
||||
},
|
||||
// 12:00 前不会走到这里（见上方 notStarted 分支），留着兼容显式指定 round 的调用
|
||||
notStarted: false,
|
||||
isPreviousDay: false,
|
||||
rounds,
|
||||
deadline: round.deadline,
|
||||
window: { start: round.windowStart, end: round.windowEnd },
|
||||
summary,
|
||||
centers,
|
||||
orders,
|
||||
truncated: totalPending > orders.length,
|
||||
detailLimit: limit,
|
||||
// 任一张订单表查询失败 → 数字不完整，前端要显式告警（不能拿残缺数据考核仓库）
|
||||
detailDegraded: mergeWarnings.length > 0,
|
||||
freshness,
|
||||
note: mergeWarnings.length ? `${mergeWarnings.join('；')}（数据不完整）` : '',
|
||||
};
|
||||
}
|
||||
|
||||
/**
|
||||
* 计算发货数据新鲜度。
|
||||
*
|
||||
* 🔥 业务上这是**最容易被误判成系统 bug** 的地方（见 MEMORY 入口 1）：
|
||||
* 上游 send_date / 出库表都滞后，「超时未发」数量会虚高。
|
||||
* 这里只如实报告滞后程度，不做任何「猜测补数」。
|
||||
*
|
||||
* @returns {Promise<{outboundMaxIo: string, sendDateMax: string, stale: boolean, hint: string}>}
|
||||
*/
|
||||
async function loadShipFreshness(round) {
|
||||
try {
|
||||
const rows = await cloudDatabase.query(
|
||||
`SELECT CONVERT(varchar(19), MAX(io_date), 120) AS outbound_max
|
||||
FROM dbo.jst_salesOutboundInfo`,
|
||||
{},
|
||||
{ requestTimeout: 60000 }
|
||||
);
|
||||
const outboundMaxIo = rows && rows[0] ? String(rows[0].outbound_max || '') : '';
|
||||
// 出库表最新一行早于今天 → 当天发货数据尚未灌入
|
||||
const outboundStale = !outboundMaxIo || outboundMaxIo.slice(0, 10) < round.date;
|
||||
return {
|
||||
outboundMaxIo,
|
||||
stale: outboundStale,
|
||||
hint: outboundStale
|
||||
? `出库表最新数据 ${outboundMaxIo || '暂无'}，今天（${round.date}）的发货记录尚未灌入，`
|
||||
+ '「超时未发」可能偏多，请以仓库实际发货情况为准'
|
||||
: '',
|
||||
};
|
||||
} catch (e) {
|
||||
// 新鲜度查询失败绝不能影响主结果
|
||||
console.warn('[ShipTimeliness] 取新鲜度失败:', e.message);
|
||||
return { outboundMaxIo: '', stale: true, hint: '发货数据新鲜度未知' };
|
||||
}
|
||||
}
|
||||
|
||||
/**
|
||||
* 单条订单的三态归类（与服务层口径一一对应）：
|
||||
* pending —— send_date 为空 **且** 出库表无命中 → 超时未发
|
||||
* shippedLate —— 有发货痕迹但发货时间晚于截止 → 已发但超时
|
||||
* onTime —— 发货时间不晚于截止 → 按时已发
|
||||
*
|
||||
* 「发货时间」取值优先级：send_date 有值就用 send_date，否则用出库表 io_date。
|
||||
* （两个都滞后时以 send_date 为准，因为它是订单系统自己的回写，语义最直接。）
|
||||
*
|
||||
* @param {object} row 含 send_date / ob_io_date
|
||||
* @param {string} deadline 'YYYY-MM-DD HH:mm:ss'
|
||||
*/
|
||||
function bucketOf(row, deadline) {
|
||||
const sendDate = formatDateTime(row.send_date);
|
||||
const ioDate = formatDateTime(row.ob_io_date);
|
||||
if (!sendDate && !ioDate) return 'pending';
|
||||
const shippedAt = sendDate || ioDate;
|
||||
return shippedAt > deadline ? 'shippedLate' : 'onTime';
|
||||
}
|
||||
|
||||
function emptySummary() {
|
||||
return {
|
||||
overdueUnshipped: 0,
|
||||
overdueShipped: 0,
|
||||
overdueUnshippedAmount: 0,
|
||||
overdueCenterCount: 0,
|
||||
windowOrderCount: 0,
|
||||
onTimeRate: null,
|
||||
};
|
||||
}
|
||||
|
||||
function buildEmptyCenters(centerList) {
|
||||
return centerList.map((c) => ({
|
||||
key: c.key,
|
||||
name: c.name,
|
||||
placeholder: c.placeholder,
|
||||
warehouseCount: c.warehouses.length,
|
||||
warehouses: c.warehouses.map((w) => ({ wmsCoId: w.wmsCoId, name: w.name })),
|
||||
overdueUnshipped: 0,
|
||||
overdueUnshippedAmount: 0,
|
||||
overdueShipped: 0,
|
||||
windowOrderCount: 0,
|
||||
onTimeRate: null,
|
||||
}));
|
||||
}
|
||||
|
||||
module.exports = {
|
||||
getShipTimeliness,
|
||||
DEFAULT_DETAIL_LIMIT,
|
||||
MAX_DETAIL_LIMIT,
|
||||
};
|
||||
@ -525,6 +525,74 @@ test('replenish index: hourly box/pallet stock surfaces same-day inbound (⑦-d)
|
||||
}
|
||||
});
|
||||
|
||||
// ⑦-e 成品库存多仓合并（2026-10-17）：
|
||||
// 实物搬到「简尚-横林二仓品牌仓」(16132681) 时，归属仓 (15870495) 为 0，
|
||||
// 只查归属仓会把有货的成品算成 0（本次 5 个误报缺货 + 2 个误报预警）。
|
||||
test('replenish index: sums stock across brand warehouse whitelist (⑦-e)', async () => {
|
||||
const cloudDatabase = require('../src/config/cloudDatabase');
|
||||
const svc = require('../src/services/mkhBomService');
|
||||
const originalQuery = cloudDatabase.query;
|
||||
const originalGetPool = cloudDatabase.getPool;
|
||||
const captured = [];
|
||||
|
||||
cloudDatabase.query = async (sql) => {
|
||||
captured.push(sql);
|
||||
if (sql.includes('jst_packInventoryInfo')) {
|
||||
// 箱表：同样的「老仓 0 / 新仓 10」格局
|
||||
return [{ skuId: 'MKHDNZ0409260600788', qty: 10 }];
|
||||
}
|
||||
if (sql.includes('jst_inventoryInfo')) {
|
||||
// 关键：SUM 之后返回的是**多仓合计**。旧口径只查单仓时这里拿不到这笔货。
|
||||
return [{ skuId: 'MKHDNZ0409260600788', qty: 10 }];
|
||||
}
|
||||
if (sql.includes('pm_stockMonitor')) {
|
||||
return [{
|
||||
skuId: 'MKHDNZ0409260600788',
|
||||
fifteenAvgSale: 1,
|
||||
monitorStock: 0,
|
||||
safetyDays: 3,
|
||||
suggestStock: 0,
|
||||
monitorDate: new Date('2026-10-17T00:00:00Z'),
|
||||
}];
|
||||
}
|
||||
if (sql.includes('jst_salesOutboundInfo') || sql.includes('jst_qmcust_salesoutbound')) return [];
|
||||
if (sql.includes('jst_qmcust_orderlist')) return [];
|
||||
return [];
|
||||
};
|
||||
cloudDatabase.getPool = async () => ({
|
||||
request: () => ({ input() {}, query: async () => ({ recordset: [] }) }),
|
||||
});
|
||||
|
||||
try {
|
||||
const index = await svc._private.loadReplenishIndex();
|
||||
const row = index.bySku.get('MKHDNZ0409260600788');
|
||||
assert.equal(row.stock, 10, '多仓求和后必须看到新仓的 10 件，不能是 0');
|
||||
|
||||
// SQL 层必须真的按白名单多仓取数，而不是退回单仓
|
||||
const invSql = captured.find((s) => s.includes('jst_inventoryInfo'));
|
||||
assert.ok(invSql.includes('IN (15870495,16132681)'),
|
||||
`实时库存必须按仓白名单取数，实际: ${invSql}`);
|
||||
assert.ok(invSql.includes('GROUP BY') && invSql.includes('SUM('),
|
||||
'实时库存必须多仓 SUM + GROUP BY（旧版是 ORDER BY updateTime DESC 取首条）');
|
||||
const packSql = captured.find((s) => s.includes('jst_packInventoryInfo'));
|
||||
assert.ok(packSql.includes('IN (15870495,16132681)'),
|
||||
`箱表必须按同一份仓白名单取数，实际: ${packSql}`);
|
||||
} finally {
|
||||
cloudDatabase.query = originalQuery;
|
||||
cloudDatabase.getPool = originalGetPool;
|
||||
svc.clearMkhBomCache();
|
||||
}
|
||||
});
|
||||
|
||||
// 白名单常量本身要被导出（新增同类仓时改这一处即可）
|
||||
test('replenish index: brand warehouse whitelist contains main + Henglin No.2 brand warehouse', () => {
|
||||
const svc = require('../src/services/mkhBomService');
|
||||
const ids = svc._private.BRAND_WMS_CO_IDS;
|
||||
assert.ok(Array.isArray(ids), 'BRAND_WMS_CO_IDS 必须是数组');
|
||||
assert.ok(ids.includes(15870495), '必须含崔桥工厂品牌仓（归属主仓）');
|
||||
assert.ok(ids.includes(16132681), '必须含横林二仓品牌仓（2026-10-13 新增）');
|
||||
});
|
||||
|
||||
test('order index: reads both channel tables, merges and dedupes by o_id', async () => {
|
||||
const cloudDatabase = require('../src/config/cloudDatabase');
|
||||
const svc = require('../src/services/mkhBomService');
|
||||
@ -1286,6 +1354,117 @@ test('cache epoch: 未经清缓存时缓存照常命中（回归保护）', asyn
|
||||
}
|
||||
});
|
||||
|
||||
// ─────────────────────────────────────────────────────────────────────────────
|
||||
// 配件库存的箱表兜底（2026-09-30 修正）
|
||||
//
|
||||
// 症状：配件监控页显示该配件有 200 件（货位 ZT-30、正常），MKH-BOM 却把成品
|
||||
// MKHDNZ0409260600849 判成「配件不足」、可组装数 0。
|
||||
// 成因：配件监控页是「库存表 → 同仓箱表 → 快照」三级兜底，而 MKH-BOM 的
|
||||
// loadComponentStock **只查 jst_inventoryInfo**。上游库存表滞后停在 0 时，
|
||||
// 箱表（小时级）已经有货 → 两个页面结论相反。
|
||||
// 口径：与配件监控页 buildRealtimeStockSql 对齐 —— 库存表有正数就认它，
|
||||
// 为 0 / 缺行才用同仓 jst_packInventoryInfo 的 bin 非空行合计兜底。
|
||||
// ─────────────────────────────────────────────────────────────────────────────
|
||||
|
||||
test('配件库存兜底：库存表为 0 / 缺行时用同仓箱表补，但正库存不被覆盖', async () => {
|
||||
const cloudDatabase = require('../src/config/cloudDatabase');
|
||||
const svc = require('../src/services/mkhBomService');
|
||||
const originalQuery = cloudDatabase.query;
|
||||
|
||||
const ONLY_PACK = 'DNZTESTPACK01'; // 库存表 0、箱表 200 → 必须取 200（误判就是这里）
|
||||
const HAS_INV = 'DNZTESTPACK02'; // 库存表 5、箱表 300 → 必须取 5（箱表只记部分装箱量）
|
||||
const PACK_ZERO = 'DNZTESTPACK03'; // 库存表 0、箱表 0 → 0
|
||||
const NEITHER = 'DNZTESTPACK04'; // 两表都没有 → 0
|
||||
|
||||
const INV_ROWS = [ // 库存表：滞后停在旧值
|
||||
{ code: ONLY_PACK, qty: 0 },
|
||||
{ code: HAS_INV, qty: 5 },
|
||||
{ code: PACK_ZERO, qty: 0 },
|
||||
];
|
||||
const PACK_ROWS = [ // 同仓箱表（bin 非空）：小时级，已反映真实到货
|
||||
{ code: ONLY_PACK, qty: 200 },
|
||||
{ code: HAS_INV, qty: 300 },
|
||||
{ code: PACK_ZERO, qty: 0 },
|
||||
];
|
||||
cloudDatabase.query = async (sql) => {
|
||||
if (!sql.includes('jst_inventoryInfo')) return [];
|
||||
// 按 SQL 形状分流：旧实现只查库存表（SQL 里没有箱表名）→ 拿不到箱表数据，
|
||||
// 兜底失效、ONLY_PACK 被判 0（对照组 = 修复前必然失败）。
|
||||
if (!sql.includes('jst_packInventoryInfo')) return INV_ROWS;
|
||||
return [
|
||||
...INV_ROWS.map((row) => ({ src: 'inv', ...row })),
|
||||
...PACK_ROWS.map((row) => ({ src: 'pack', ...row })),
|
||||
];
|
||||
};
|
||||
|
||||
try {
|
||||
svc.clearMkhBomCache();
|
||||
const { stockMap, degraded } = await svc._private.loadComponentStock([
|
||||
ONLY_PACK, HAS_INV, PACK_ZERO, NEITHER,
|
||||
]);
|
||||
assert.equal(degraded, false);
|
||||
assert.equal(stockMap.get(ONLY_PACK), 200,
|
||||
'库存表为 0 时必须用箱表兜底，否则配件被误判不足（本问题的根因）');
|
||||
assert.equal(stockMap.get(HAS_INV), 5,
|
||||
'库存表有正库存时必须以它为准，不能被箱表覆盖');
|
||||
assert.equal(stockMap.get(PACK_ZERO), 0, '箱表也是 0 时不能补出正数');
|
||||
assert.equal(stockMap.get(NEITHER), 0, '两表都查不到 = 该仓无库存，仍按 0');
|
||||
} finally {
|
||||
cloudDatabase.query = originalQuery;
|
||||
svc.clearMkhBomCache();
|
||||
}
|
||||
});
|
||||
|
||||
test('配件库存兜底：单条查询取两张表，且箱表必须过滤 bin 为空的行（防腐化）', async () => {
|
||||
const cloudDatabase = require('../src/config/cloudDatabase');
|
||||
const svc = require('../src/services/mkhBomService');
|
||||
const originalQuery = cloudDatabase.query;
|
||||
|
||||
const seen = [];
|
||||
cloudDatabase.query = async (sql) => {
|
||||
if (!sql.includes('jst_inventoryInfo')) return [];
|
||||
seen.push(sql);
|
||||
return [];
|
||||
};
|
||||
|
||||
try {
|
||||
svc.clearMkhBomCache();
|
||||
await svc._private.loadComponentStock(['DNZTESTPACK05']);
|
||||
assert.equal(seen.length, 1, '一个分片只应打一条查询（避免多一次往返与快照时点差异）');
|
||||
const sql = seen[0];
|
||||
assert.match(sql, /jst_packInventoryInfo/, '必须查箱表，否则兜底不存在');
|
||||
assert.match(sql, /NULLIF\(LTRIM\(RTRIM\(p\.bin\)\), N''\)\s+IS NOT NULL/,
|
||||
'箱表必须过滤 bin 为空的行 —— 那是装箱记录，不是库存');
|
||||
assert.match(sql, /p\.wms_co_id = @componentStockWmsCoId/,
|
||||
'箱表必须限定配件归属仓，不能跨仓汇总');
|
||||
} finally {
|
||||
cloudDatabase.query = originalQuery;
|
||||
svc.clearMkhBomCache();
|
||||
}
|
||||
});
|
||||
|
||||
test('配件库存兜底：查询失败仍降级为「不做判定」，不得当成 0', async () => {
|
||||
const cloudDatabase = require('../src/config/cloudDatabase');
|
||||
const svc = require('../src/services/mkhBomService');
|
||||
const originalQuery = cloudDatabase.query;
|
||||
|
||||
const CODE = 'DNZTESTPACK06';
|
||||
cloudDatabase.query = async (sql) => {
|
||||
if (!sql.includes('jst_inventoryInfo')) return [];
|
||||
throw new Error('模拟连接抖动');
|
||||
};
|
||||
|
||||
try {
|
||||
svc.clearMkhBomCache();
|
||||
const { stockMap, degraded } = await svc._private.loadComponentStock([CODE]);
|
||||
assert.equal(degraded, true, '查询失败必须降级，让调用方放弃配件不足判定（宁可少标不误标）');
|
||||
assert.equal(stockMap.get(CODE), undefined, '失败时不得把未知库存当作 0 返回');
|
||||
} finally {
|
||||
cloudDatabase.query = originalQuery;
|
||||
svc.clearMkhBomCache();
|
||||
}
|
||||
});
|
||||
|
||||
test('operational refresh: 并发刷新只查一次运营数据，短时重试复用新结果', async () => {
|
||||
const cloudDatabase = require('../src/config/cloudDatabase');
|
||||
const svc = require('../src/services/mkhBomService');
|
||||
|
||||
171
backend/test/pickingBinSalesAnalysis.test.js
Normal file
171
backend/test/pickingBinSalesAnalysis.test.js
Normal file
@ -0,0 +1,171 @@
|
||||
'use strict';
|
||||
|
||||
const test = require('node:test');
|
||||
const assert = require('node:assert/strict');
|
||||
|
||||
const {
|
||||
buildGradeCase,
|
||||
buildInClause,
|
||||
fetchPickingBinSkuRows,
|
||||
} = require('../src/services/pickingBinSalesAnalysis');
|
||||
const {
|
||||
PICKING_BIN_GRADE_RULES,
|
||||
PICKING_BIN_DEFAULT_DAYS,
|
||||
PICKING_BIN_WINDOW_OPTIONS,
|
||||
PICKING_BIN_BIN_TYPES,
|
||||
} = require('../src/config/pickingBinSalesAnalysis');
|
||||
|
||||
test('buildInClause 按位展开参数', () => {
|
||||
const { clause, params } = buildInClause([101, 102], 'w');
|
||||
assert.equal(clause, '@w0,@w1');
|
||||
assert.deepEqual(params, { w0: 101, w1: 102 });
|
||||
});
|
||||
|
||||
test('buildGradeCase 覆盖全部正向等级，并以销售=0 兜底', () => {
|
||||
const expr = buildGradeCase();
|
||||
PICKING_BIN_GRADE_RULES.filter((rule) => rule.lower > 0).forEach((rule) => {
|
||||
assert.ok(expr.includes(`THEN '${rule.key}'`), `${rule.key} 未出现在分档表达式中`);
|
||||
});
|
||||
assert.ok(expr.includes("THEN 'slow2'"));
|
||||
assert.ok(expr.includes("ELSE 'zero'"));
|
||||
});
|
||||
|
||||
test('等级按商品销量判定（默认表达式取 s.salesQty，不含仓位汇总列）', () => {
|
||||
const expr = buildGradeCase();
|
||||
assert.ok(expr.includes('ISNULL(s.salesQty, 0) / @days'));
|
||||
assert.ok(!expr.includes('x.salesQty'));
|
||||
});
|
||||
|
||||
test('默认窗口为 30 天，统计范围固定为拣选位', () => {
|
||||
assert.equal(PICKING_BIN_DEFAULT_DAYS, 30);
|
||||
assert.ok(PICKING_BIN_WINDOW_OPTIONS.includes(30));
|
||||
assert.deepEqual(PICKING_BIN_BIN_TYPES, ['拣选']);
|
||||
});
|
||||
|
||||
test('fetchPickingBinSkuRows 首参不是数据库实例时抛错', async () => {
|
||||
await assert.rejects(
|
||||
() => fetchPickingBinSkuRows({}, [101], { shipFrom: 'a', shipTo: 'b', days: 30 }),
|
||||
TypeError
|
||||
);
|
||||
});
|
||||
|
||||
test('fetchPickingBinSkuRows 缺失必要窗口参数时返回空数组，不打库', async () => {
|
||||
let called = 0;
|
||||
const db = { query: async () => { called += 1; return [{ skuId: 'S1' }]; } };
|
||||
assert.deepEqual(await fetchPickingBinSkuRows(db, [], { shipFrom: 'a', shipTo: 'b', days: 30 }), []);
|
||||
assert.deepEqual(await fetchPickingBinSkuRows(db, [101], { shipFrom: null, shipTo: 'b', days: 30 }), []);
|
||||
assert.deepEqual(await fetchPickingBinSkuRows(db, [101], { shipFrom: 'a', shipTo: 'b', days: 0 }), []);
|
||||
assert.equal(called, 0);
|
||||
});
|
||||
|
||||
test('fetchPickingBinSkuRows 按仓位×商品输出，等级与日销来自商品整档销量', async () => {
|
||||
let captured = null;
|
||||
const db = {
|
||||
query: async (sqlText, params) => {
|
||||
captured = { sqlText, params };
|
||||
return [
|
||||
{
|
||||
wmsCoId: 101, binName: 'XA01-01-01', binType: '拣选', skuId: 'SKU1',
|
||||
stockQty: 12, skuCount: 2, salesQty: 600, allocatedSalesQty: 300,
|
||||
dailySales: 20, gradeKey: 'hot',
|
||||
},
|
||||
{
|
||||
wmsCoId: 101, binName: 'XA01-01-01', binType: '拣选', skuId: 'SKU2',
|
||||
stockQty: 8, skuCount: 2, salesQty: 45, allocatedSalesQty: 22.5,
|
||||
dailySales: 1.5, gradeKey: 'slow1',
|
||||
},
|
||||
];
|
||||
},
|
||||
};
|
||||
|
||||
const rows = await fetchPickingBinSkuRows(db, [101, 102], {
|
||||
shipFrom: '2026-09-08 01:00:00',
|
||||
shipTo: '2026-10-08 01:00:00',
|
||||
days: 30,
|
||||
});
|
||||
|
||||
assert.equal(rows.length, 2);
|
||||
assert.deepEqual(rows.map((r) => r.skuId), ['SKU1', 'SKU2']);
|
||||
// 同一仓位两个商品拆两行，库存是各商品在该仓位的实际库存
|
||||
assert.deepEqual(rows.map((r) => r.stockQty), [12, 8]);
|
||||
// 等级按商品自身日销，同一仓位的多行可以不同
|
||||
assert.deepEqual(rows.map((r) => r.gradeKey), ['hot', 'slow1']);
|
||||
// 分摊销量 = 商品销量 ÷ 占用的同类型仓位数（用于按行求和）
|
||||
assert.deepEqual(rows.map((r) => r.allocatedSalesQty), [300, 22.5]);
|
||||
assert.deepEqual(rows[0], {
|
||||
wmsCoId: '101',
|
||||
binName: 'XA01-01-01',
|
||||
binType: '拣选',
|
||||
skuId: 'SKU1',
|
||||
stockQty: 12,
|
||||
skuCount: 2,
|
||||
salesQty: 600,
|
||||
allocatedSalesQty: 300,
|
||||
dailySales: 20,
|
||||
gradeKey: 'hot',
|
||||
});
|
||||
|
||||
// 参数必须带上两个仓库、发货窗口与天数
|
||||
assert.equal(captured.params.w0, 101);
|
||||
assert.equal(captured.params.w1, 102);
|
||||
assert.equal(captured.params.v0, 101);
|
||||
assert.equal(captured.params.shipFrom, '2026-09-08 01:00:00');
|
||||
assert.equal(captured.params.shipTo, '2026-10-08 01:00:00');
|
||||
assert.equal(captured.params.days, 30);
|
||||
|
||||
// 商品销量按占用仓位数分摊（供求和），但等级依据的是未分摊的商品销量
|
||||
assert.ok(captured.sqlText.includes('/ NULLIF(sp.binCount, 0)'));
|
||||
assert.ok(captured.sqlText.includes('ISNULL(s.salesQty, 0) / @days AS DECIMAL(18, 4)'));
|
||||
// 统计范围固定为真实拣选仓位：pack_type=Bin 且 bin_group 为空；只统计有库存的行
|
||||
assert.ok(captured.sqlText.includes("p.pack_type = N'Bin'"));
|
||||
assert.ok(captured.sqlText.includes("ISNULL(p.bin_group, '') = ''"));
|
||||
assert.ok(captured.sqlText.includes('ISNULL(p.qty, 0) <> 0'));
|
||||
assert.ok(captured.sqlText.includes('HAVING ABS(SUM(CAST(b.binSkuQty AS DECIMAL(18, 2)))) > 0.0001'));
|
||||
});
|
||||
|
||||
test('fetchPickingBinSkuRows 只统计真实拣选仓位，排除 Pack 箱记录且不带类型参数', async () => {
|
||||
let captured = null;
|
||||
const db = {
|
||||
query: async (sqlText, params) => { captured = { sqlText, params }; return []; },
|
||||
};
|
||||
await fetchPickingBinSkuRows(db, [101], { shipFrom: 'a', shipTo: 'b', days: 15 });
|
||||
// Pack 箱记录和存货位被排除，且不再有 binType IN (...) 的可变过滤
|
||||
assert.ok(captured.sqlText.includes("p.pack_type = N'Bin'"));
|
||||
assert.ok(captured.sqlText.includes("ISNULL(p.bin_group, '') = ''"));
|
||||
assert.ok(!captured.sqlText.includes('b.binType IN'));
|
||||
assert.equal(captured.params.t0, undefined);
|
||||
assert.equal(captured.params.binTypes, undefined);
|
||||
// 多来源 union 后必须再按 仓库+商品 汇总，否则同一仓位行会被拆成多行
|
||||
assert.ok(captured.sqlText.includes('shipAgg AS ('));
|
||||
assert.ok(captured.sqlText.includes('LEFT JOIN shipAgg s'));
|
||||
assert.ok(!captured.sqlText.includes('LEFT JOIN ship s'));
|
||||
});
|
||||
|
||||
test('销量覆盖 4 个来源：线上普通行 + 组合装展开 + 全民客户普通行 + 全民客户组合装展开', async () => {
|
||||
let captured = null;
|
||||
const db = {
|
||||
query: async (sqlText, params) => { captured = { sqlText, params }; return []; },
|
||||
};
|
||||
await fetchPickingBinSkuRows(db, [101], { shipFrom: 'a', shipTo: 'b', days: 30 });
|
||||
|
||||
// ① 线上店铺订单普通行
|
||||
assert.ok(captured.sqlText.includes('FROM rpt.jst_OrderItem'), '缺少 jst_OrderItem 普通行分支');
|
||||
// ② 线上订单组合装行按映射表展开为子件
|
||||
assert.ok(captured.sqlText.includes('JOIN rpt.jst_comboProductItem'), '缺少组合装展开分支');
|
||||
assert.ok(captured.sqlText.includes("oi.sku_type = 'combine'"), '缺少组合装行识别条件');
|
||||
// ③ 全民客户（B2B 分销）渠道普通行，排除拆单父单 Split 与问题单
|
||||
assert.ok(captured.sqlText.includes('FROM rpt.jst_qmcust_orderlist_item'), '缺少 qmcust 分支');
|
||||
assert.ok(captured.sqlText.includes("'Split', 'Question'"), 'qmcust 未排除拆单父单/问题单');
|
||||
// 四个分支共用同一发货窗口
|
||||
assert.equal(captured.sqlText.split('o.send_date >= @shipFrom').length, 5);
|
||||
});
|
||||
|
||||
test('gradeKey 缺失时兜底为 zero', async () => {
|
||||
const db = { query: async () => [{ wmsCoId: 101, binName: 'X', binType: '拣选', skuId: 'S1', stockQty: 5 }] };
|
||||
const rows = await fetchPickingBinSkuRows(db, [101], { shipFrom: 'a', shipTo: 'b', days: 15 });
|
||||
assert.equal(rows[0].gradeKey, 'zero');
|
||||
assert.equal(rows[0].salesQty, 0);
|
||||
assert.equal(rows[0].allocatedSalesQty, 0);
|
||||
assert.equal(rows[0].dailySales, 0);
|
||||
assert.equal(rows[0].skuId, 'S1');
|
||||
});
|
||||
@ -7,6 +7,7 @@ const {
|
||||
buildGradeCase,
|
||||
buildInClause,
|
||||
fetchGradeRows,
|
||||
fetchZeroSaleSkuRows,
|
||||
} = require('../src/services/stockSalesAnalysis');
|
||||
const { SALES_GRADE_RULES } = require('../src/config/stockSalesAnalysis');
|
||||
|
||||
@ -71,3 +72,78 @@ test('fetchGradeRows 把仓库参数与窗口参数一并传给业务库', async
|
||||
assert.ok(captured.sqlText.includes('rpt.jst_inventoryInfo'));
|
||||
assert.ok(captured.sqlText.includes('rpt.jst_OrderItem'));
|
||||
});
|
||||
|
||||
test('fetchZeroSaleSkuRows 首参不是数据库实例时抛错，避免静默返回空数组', async () => {
|
||||
await assert.rejects(
|
||||
() => fetchZeroSaleSkuRows({}, [101], { shipFrom: 'a', shipTo: 'b', days: 30 }),
|
||||
TypeError
|
||||
);
|
||||
});
|
||||
|
||||
test('fetchZeroSaleSkuRows 无仓库或缺失窗口时直接返回空结果', async () => {
|
||||
let called = 0;
|
||||
const db = { query: async () => { called += 1; return []; } };
|
||||
assert.deepEqual(await fetchZeroSaleSkuRows(db, [], { shipFrom: 'a', shipTo: 'b', days: 30 }), []);
|
||||
assert.deepEqual(await fetchZeroSaleSkuRows(db, [101], { shipFrom: 'a', shipTo: null, days: 30 }), []);
|
||||
assert.equal(called, 0);
|
||||
});
|
||||
|
||||
test('fetchZeroSaleSkuRows 复用同一分档口径并关联 SKU 名称', async () => {
|
||||
let captured = null;
|
||||
const db = {
|
||||
query: async (sqlText, params) => {
|
||||
captured = { sqlText, params };
|
||||
return [
|
||||
{ wmsCoId: 101, skuId: 'SKU-A', skuName: '款式A', binName: 'A01-01-1', stockQty: 7, salesQty: 0 },
|
||||
{ wmsCoId: 101, skuId: 'SKU-A', skuName: '款式A', binName: 'A02-03-1', stockQty: 5, salesQty: 0 },
|
||||
{ wmsCoId: 101, skuId: 'SKU-B', skuName: '款式B', binName: null, stockQty: 3, salesQty: 0 },
|
||||
];
|
||||
},
|
||||
};
|
||||
|
||||
const rows = await fetchZeroSaleSkuRows(db, [101, 102], {
|
||||
shipFrom: '2026-08-28 01:00:00',
|
||||
shipTo: '2026-09-27 01:00:00',
|
||||
days: 30,
|
||||
});
|
||||
|
||||
// 每个仓位独立一行；无仓位的 SKU 保留一行（仓位为空、库存取总库存）
|
||||
assert.equal(rows.length, 3);
|
||||
assert.equal(rows[0].wmsCoId, '101');
|
||||
assert.equal(rows[0].skuName, '款式A');
|
||||
assert.equal(rows[0].binName, 'A01-01-1');
|
||||
assert.equal(rows[0].stockQty, 7);
|
||||
assert.equal(rows[1].binName, 'A02-03-1');
|
||||
assert.equal(rows[1].stockQty, 5);
|
||||
assert.equal(rows[2].binName, '');
|
||||
assert.equal(rows[2].stockQty, 3);
|
||||
|
||||
// 口径必须与 fetchGradeRows 一致：同一段 gradeCase 表达式 + 同样的「有库存或有销量」过滤
|
||||
assert.ok(captured.sqlText.includes("ELSE 'zero'"));
|
||||
assert.ok(captured.sqlText.includes("g.gradeKey = 'zero'"));
|
||||
assert.ok(captured.sqlText.includes('rpt.jst_sku'));
|
||||
assert.ok(captured.sqlText.includes("NOT IN ('Delete', 'Cancelled')"));
|
||||
assert.equal(captured.params.w0, 101);
|
||||
assert.equal(captured.params.days, 30);
|
||||
|
||||
// 系统仓位：来自 rpt.jst_packInventoryInfo，按「仓库+SKU+仓位」聚合出仓位级库存，
|
||||
// 每仓位一行（COALESCE 兜底无仓位 SKU 取总库存），排除无仓位与零库存记录
|
||||
assert.ok(captured.sqlText.includes('rpt.jst_packInventoryInfo'));
|
||||
assert.ok(captured.sqlText.includes('COALESCE(bn.binQty, g.stockQty)'));
|
||||
assert.ok(captured.sqlText.includes('binName'));
|
||||
});
|
||||
|
||||
test('fetchZeroSaleSkuRows 的仓位必须按同一仓库匹配，避免跨仓错配', async () => {
|
||||
let captured = null;
|
||||
const db = {
|
||||
query: async (sqlText) => {
|
||||
captured = sqlText;
|
||||
return [];
|
||||
},
|
||||
};
|
||||
await fetchZeroSaleSkuRows(db, [101], {
|
||||
shipFrom: '2026-08-28 01:00:00', shipTo: '2026-09-27 01:00:00', days: 30,
|
||||
});
|
||||
assert.ok(captured.includes('bn.wmsCoId = g.wmsCoId AND bn.skuId = g.skuId'));
|
||||
assert.ok(captured.includes("ISNULL(p.qty, 0) <> 0"));
|
||||
});
|
||||
|
||||
233
backend/test/warehouseShipTimeliness.test.js
Normal file
233
backend/test/warehouseShipTimeliness.test.js
Normal file
@ -0,0 +1,233 @@
|
||||
/**
|
||||
* 仓储中心 24 小时发货时效 —— 口径回归测试（2026-10-20 新增）
|
||||
*
|
||||
* 这个功能最容易出错的是「口径」，不是 SQL 语法。本文件锁定四条关键语义：
|
||||
* ① 主仓归属用**关键词最长匹配** —— 「横林二仓品牌仓」不能被「横林二仓」抢走，
|
||||
* 否则品牌仓的订单会被重复计入两个仓；
|
||||
* ② 核验轮次的时刻选择 —— 12:00 前**不做核验（返回 null）**、12:00~15:59 用
|
||||
* 7:00 窗口、16:00 后用 16:00 窗口；
|
||||
* ③ 状态排除表 —— Cancelled / Merged / Split 必须排除（Split 是拆单父单，
|
||||
* 永不发货，不排除会把一年前的单全算成超时未发）；
|
||||
* ④ 服务层分桶算术 —— pending/shippedLate/onTime 三态互斥，按时率不为负。
|
||||
*
|
||||
* ⚠️ 本文件**不连数据库**，纯逻辑测试。
|
||||
*/
|
||||
const test = require('node:test');
|
||||
const assert = require('node:assert/strict');
|
||||
|
||||
const registry = require('../src/services/warehouseShipTimelinessRegistry');
|
||||
|
||||
// ── ① 主仓归属：最长匹配 ────────────────────────────────────────────────
|
||||
test('resolveCenter 用最长关键词匹配，品牌仓不会被主仓抢走', () => {
|
||||
assert.equal(registry.resolveCenter('简尚-横林二仓品牌仓').key, 'HL2_BRAND');
|
||||
assert.equal(registry.resolveCenter('简尚-横林二仓').key, 'HL2');
|
||||
assert.equal(registry.resolveCenter('简尚-崔桥工厂品牌仓').key, 'CQ_BRAND');
|
||||
assert.equal(registry.resolveCenter('简尚-崔桥工厂仓').key, 'CQ');
|
||||
assert.equal(registry.resolveCenter('简尚-山东品牌仓').key, 'SD_BRAND');
|
||||
assert.equal(registry.resolveCenter('简尚-山东青岗集仓').key, 'SD_QGJ');
|
||||
});
|
||||
|
||||
test('resolveCenter 能捞到各种命名的子仓', () => {
|
||||
const cases = [
|
||||
['京仓-横林二仓京东云仓', 'HL2'],
|
||||
['简尚-运营C横林二仓专用仓', 'HL2'],
|
||||
['简尚-横林二仓京东仓', 'HL2'],
|
||||
['简尚-崔桥工厂仓唯品仓', 'CQ'],
|
||||
['简尚-运营C崔桥工厂仓专用仓', 'CQ'],
|
||||
['简尚-新苏北临时仓', 'XSB'],
|
||||
['简尚-分销部睢宁专用仓', 'SN'],
|
||||
['京仓-山东青岗集京东仓储', 'SD_QGJ'],
|
||||
['简尚-山东青岗集仓马来海外仓', 'SD_QGJ'],
|
||||
['简尚-河南洛宁仓', 'HN_LN'],
|
||||
];
|
||||
for (const [name, expected] of cases) {
|
||||
assert.equal(registry.resolveCenter(name)?.key, expected, name);
|
||||
}
|
||||
});
|
||||
|
||||
test('resolveCenter 不会把无关代发仓误并入（山东/河南是高频前缀）', () => {
|
||||
// 这是最容易翻车的地方：仓库表里有上百个「工厂-山东XX代发仓」，
|
||||
// 如果关键词写成「山东」就会全被并进山东品牌仓/青岗集仓。
|
||||
const shouldBeNull = [
|
||||
'工厂-山东菏泽森美代发仓',
|
||||
'工厂-山东恒思代发仓',
|
||||
'工厂-河南千优代发仓',
|
||||
'工厂-河北霸州派雅世佳代发仓',
|
||||
'简尚-苏州福利品仓',
|
||||
'简尚-崔桥工厂半成品包装项目仓',
|
||||
];
|
||||
for (const name of shouldBeNull) {
|
||||
assert.equal(registry.resolveCenter(name), null, `${name} 不应归属任何主仓`);
|
||||
}
|
||||
});
|
||||
|
||||
test('崔桥工厂半成品仓是占位仓：没有关键词、匹配不到任何仓', () => {
|
||||
const c = registry.CENTERS.find((x) => x.key === 'CQ_WIP');
|
||||
assert.ok(c, 'CQ_WIP 必须存在（业务要求列出来）');
|
||||
assert.equal(c.placeholder, true);
|
||||
assert.deepEqual(c.keywords, []);
|
||||
});
|
||||
|
||||
// ── ② 核验轮次 ──────────────────────────────────────────────────────────
|
||||
const at = (h, m) => new Date(2026, 9, 20, h, m, 0); // 2026-10-20 本机时区（东八区）
|
||||
|
||||
test('12:00 前不做核验，返回 null（不回填昨日数据）', () => {
|
||||
// 业务口径（2026-10-20 确认）：中午 12:00 才开始第一轮核验，
|
||||
// 12:00 之前页面显示「未到核验时间」，**不能用昨天的结算结果冒充今天**。
|
||||
assert.equal(registry.resolveActiveRound(at(9, 30)), null);
|
||||
assert.equal(registry.resolveActiveRound(at(11, 59)), null);
|
||||
assert.equal(registry.resolveActiveRound(at(0, 5)), null);
|
||||
});
|
||||
|
||||
test('12:00~15:59 用 12:00 轮，核验窗口是当天 00:00~07:00', () => {
|
||||
const r = registry.resolveActiveRound(at(12, 0));
|
||||
assert.ok(r);
|
||||
assert.equal(r.key, 'NOON');
|
||||
assert.equal(r.deadline, '2026-10-20 12:00:00');
|
||||
assert.equal(r.windowStart, '2026-10-20 00:00:00');
|
||||
assert.equal(r.windowEnd, '2026-10-20 07:00:00');
|
||||
});
|
||||
|
||||
test('16:00 之后用 16:00 轮，核验窗口扩到当天 00:00~16:00', () => {
|
||||
const r = registry.resolveActiveRound(at(16, 0));
|
||||
assert.equal(r.key, 'AFTERNOON');
|
||||
assert.equal(r.deadline, '2026-10-20 16:00:00');
|
||||
assert.equal(r.windowEnd, '2026-10-20 16:00:00');
|
||||
|
||||
// 深夜仍沿用当天 16:00 轮（午夜后又回到新的一天）
|
||||
assert.equal(registry.resolveActiveRound(at(23, 59)).key, 'AFTERNOON');
|
||||
});
|
||||
|
||||
// ── ③ 状态排除 ──────────────────────────────────────────────────────────
|
||||
test('排除 Cancelled / Merged / Split，但保留 Question（仍算待发）', () => {
|
||||
const sql = registry.EXCLUDED_STATUS_SQL;
|
||||
assert.match(sql, /Cancelled/);
|
||||
assert.match(sql, /Merged/);
|
||||
assert.match(sql, /Split/);
|
||||
assert.doesNotMatch(sql, /Question/);
|
||||
});
|
||||
|
||||
// ── ④ 服务层分桶（注入假查询，不连库）──
|
||||
function stubDb(dbPath, realDb) {
|
||||
const wmsId = 15038426;
|
||||
// 重构后服务层是「一趟取回窗口内全部订单，Node 侧分桶」，
|
||||
// 所以 stub 要返回**明细行**而不是聚合行。三态各造几条：
|
||||
// · 超时未发 3 单 / 合计 300
|
||||
// · 已发但超时 1 单（send_date 晚于 16:00）
|
||||
// · 按时 6 单（send_date 早于 16:00）
|
||||
const mk = (sendDate, amount, oId) => ({
|
||||
src: 'jst_Orders',
|
||||
wms_co_id: wmsId,
|
||||
o_id: oId,
|
||||
so_id: String(oId),
|
||||
shop_name: '测试店',
|
||||
order_date: '2026-10-20 01:00:00',
|
||||
send_date: sendDate,
|
||||
status: 'Delivering',
|
||||
shop_status: '',
|
||||
pay_amount: amount,
|
||||
labels: '',
|
||||
ob_io_date: null,
|
||||
});
|
||||
|
||||
const stubRows = [
|
||||
mk(null, 100, 1), mk(null, 100, 2), mk(null, 100, 3),
|
||||
mk('2026-10-20 18:30:00', 50, 4),
|
||||
mk('2026-10-20 09:00:00', 100, 5), mk('2026-10-20 09:00:00', 100, 6),
|
||||
mk('2026-10-20 10:00:00', 100, 7), mk('2026-10-20 10:00:00', 100, 8),
|
||||
mk('2026-10-20 11:00:00', 100, 9), mk('2026-10-20 11:00:00', 100, 10),
|
||||
];
|
||||
|
||||
// 记录业务表被查了几次 —— 12:00 前应当为 0
|
||||
// ⚠️ 现在是**两表并行分别查**（jst_Orders + jst_qmcust_orderlist），
|
||||
// 所以两表都返回同一批 stubRows；服务层按 o_id 去重后仍应算 10 条，
|
||||
// 这也顺带验证了「跨表同 o_id 去重」逻辑。
|
||||
const calls = { order: 0 };
|
||||
require.cache[dbPath] = {
|
||||
id: dbPath,
|
||||
filename: dbPath,
|
||||
loaded: true,
|
||||
exports: {
|
||||
query: async (sql) => {
|
||||
if (/jst_warehouseInfo/u.test(sql)) {
|
||||
return [{ Id: 1, name: '简尚-崔桥工厂仓', wms_co_id: wmsId }];
|
||||
}
|
||||
if (/jst_salesOutboundInfo/u.test(sql) && /MAX\(io_date\)/u.test(sql)) {
|
||||
// 新鲜度查询：返回空，走 stale 分支也不影响主结果
|
||||
return [{ outbound_max: '2026-10-19 23:00:00' }];
|
||||
}
|
||||
calls.order += 1;
|
||||
return stubRows;
|
||||
},
|
||||
},
|
||||
};
|
||||
void realDb;
|
||||
return { calls, wmsId };
|
||||
}
|
||||
|
||||
test('服务层三态分桶互斥，按时率不为负', async () => {
|
||||
const dbPath = require.resolve('../src/config/cloudDatabase');
|
||||
const realDb = require.cache[dbPath];
|
||||
stubDb(dbPath, realDb);
|
||||
delete require.cache[require.resolve('../src/services/warehouseShipTimelinessService')];
|
||||
delete require.cache[require.resolve('../src/services/warehouseShipTimelinessRegistry')];
|
||||
|
||||
try {
|
||||
const svc = require('../src/services/warehouseShipTimelinessService');
|
||||
const data = await svc.getShipTimeliness({ now: at(16, 5), limit: 10 });
|
||||
const c = data.centers.find((x) => x.key === 'CQ');
|
||||
assert.ok(c, '应能归到崔桥工厂仓');
|
||||
assert.equal(c.overdueUnshipped, 3);
|
||||
assert.equal(c.overdueShipped, 1);
|
||||
assert.equal(c.windowOrderCount, 10, '总数 = pending + shippedLate + onTime');
|
||||
assert.equal(c.onTimeRate, 60, '按时 6/10 = 60%（跨表重复 o_id 必须去重，否则总数会翻倍）');
|
||||
|
||||
// 汇总口径与各仓一致，且按时率不会是负数
|
||||
assert.equal(data.summary.overdueUnshipped, 3);
|
||||
assert.equal(data.summary.windowOrderCount, 10);
|
||||
assert.equal(data.summary.onTimeRate, 60);
|
||||
assert.ok(data.summary.onTimeRate >= 0);
|
||||
|
||||
// 前端要展示「核验 16:00 前」，round 必须带出 windowEnd ——
|
||||
// 只回 key/label 的话页面会渲染成「核验 前」（2026-10-20 真渲染时抓到）
|
||||
assert.ok(data.round, 'round 不能为空');
|
||||
assert.equal(data.round.key, 'AFTERNOON');
|
||||
assert.equal(data.round.cutoffHour, 16);
|
||||
assert.match(String(data.round.windowEnd), /16:00:00$/, 'round.windowEnd 必须带出截止时刻');
|
||||
} finally {
|
||||
// 还原，避免污染同进程内其它测试
|
||||
if (realDb) require.cache[dbPath] = realDb;
|
||||
else delete require.cache[dbPath];
|
||||
delete require.cache[require.resolve('../src/services/warehouseShipTimelinessService')];
|
||||
delete require.cache[require.resolve('../src/services/warehouseShipTimelinessRegistry')];
|
||||
}
|
||||
});
|
||||
|
||||
test('服务层 12:00 前返回 notStarted，且不去查订单表', async () => {
|
||||
const dbPath = require.resolve('../src/config/cloudDatabase');
|
||||
const realDb = require.cache[dbPath];
|
||||
const { calls } = stubDb(dbPath, realDb);
|
||||
delete require.cache[require.resolve('../src/services/warehouseShipTimelinessService')];
|
||||
delete require.cache[require.resolve('../src/services/warehouseShipTimelinessRegistry')];
|
||||
|
||||
try {
|
||||
const svc = require('../src/services/warehouseShipTimelinessService');
|
||||
const data = await svc.getShipTimeliness({ now: at(9, 30), limit: 10 });
|
||||
assert.equal(data.notStarted, true);
|
||||
assert.equal(data.round, null);
|
||||
assert.equal(data.orders.length, 0);
|
||||
assert.equal(data.summary.overdueUnshipped, 0);
|
||||
// 11 个主仓仍然列出来（结构不能塌），但数字全为 0
|
||||
assert.equal(data.centers.length, registry.CENTERS.length);
|
||||
assert.ok(data.centers.every((c) => c.overdueUnshipped === 0));
|
||||
assert.equal(calls.order, 0, '未到核验时间不应扫订单表');
|
||||
assert.match(data.note, /12:00/);
|
||||
// 下一轮核验时刻要告诉前端，方便提示「12:00 开始」
|
||||
assert.equal(data.nextRoundAt, '2026-10-20 12:00:00');
|
||||
} finally {
|
||||
if (realDb) require.cache[dbPath] = realDb;
|
||||
else delete require.cache[dbPath];
|
||||
delete require.cache[require.resolve('../src/services/warehouseShipTimelinessService')];
|
||||
delete require.cache[require.resolve('../src/services/warehouseShipTimelinessRegistry')];
|
||||
}
|
||||
});
|
||||
16
frontend/src/api/pickingBinSalesAnalysis.js
Normal file
16
frontend/src/api/pickingBinSalesAnalysis.js
Normal file
@ -0,0 +1,16 @@
|
||||
import request from './request';
|
||||
|
||||
const LIST_TIMEOUT_MS = 5 * 60 * 1000;
|
||||
|
||||
// 拣选库位动销分析：仓库 × 仓位 → 类型 / 库存数量 / SKU数 / 近N天销量 / 日销 / 动销等级
|
||||
// refresh=1 跳过后端 5 分钟缓存，强制重算
|
||||
export const getPickingBinSalesAnalysis = (params = {}) => request.get(
|
||||
'/api/picking-bin-sales-analysis',
|
||||
{ params, timeout: LIST_TIMEOUT_MS }
|
||||
);
|
||||
|
||||
// 导出库位明细（xlsx/csv，服务端直接返回文件流）
|
||||
export const exportPickingBinSalesAnalysis = (params = {}) => request.get(
|
||||
'/api/picking-bin-sales-analysis/export',
|
||||
{ params, responseType: 'blob', timeout: LIST_TIMEOUT_MS }
|
||||
);
|
||||
@ -8,3 +8,9 @@ export const getStockSalesAnalysis = (params = {}) => request.get(
|
||||
'/api/stock-sales-analysis',
|
||||
{ params, timeout: LIST_TIMEOUT_MS }
|
||||
);
|
||||
|
||||
// 导出「销售=0」档的 SKU 级明细（xlsx/csv，服务端直接返回文件流）
|
||||
export const exportStockSalesZeroDetail = (params = {}) => request.get(
|
||||
'/api/stock-sales-analysis/zero-sales-export',
|
||||
{ params, responseType: 'blob', timeout: LIST_TIMEOUT_MS }
|
||||
);
|
||||
|
||||
23
frontend/src/api/warehouseShipTimeliness.js
Normal file
23
frontend/src/api/warehouseShipTimeliness.js
Normal file
@ -0,0 +1,23 @@
|
||||
import request from './request';
|
||||
|
||||
// 发货时效要扫两张订单表 + 出库表（rpt 下全是视图），冷启动实测 40~50 秒，
|
||||
// 超时必须放大到 3 分钟，否则前端会先断开、后端还在跑。
|
||||
const TIMELINESS_TIMEOUT_MS = 3 * 60 * 1000;
|
||||
|
||||
// 仓储中心 24 小时发货时效：每个主仓「超时未发」数量 + 超时订单明细
|
||||
// params.round 'NOON' | 'AFTERNOON'（不传按当前时刻自动判定）
|
||||
// params.limit 明细条数上限
|
||||
export const getWarehouseShipTimeliness = (params = {}) => request.get(
|
||||
'/api/warehouse-ship-timeliness/list',
|
||||
{
|
||||
params,
|
||||
timeout: TIMELINESS_TIMEOUT_MS,
|
||||
headers: { 'Cache-Control': 'no-cache' },
|
||||
}
|
||||
);
|
||||
|
||||
// 仅取今日两轮核验的描述（不打订单表，秒回），用于顶部提示
|
||||
export const getWarehouseShipTimelinessRounds = () => request.get(
|
||||
'/api/warehouse-ship-timeliness/rounds',
|
||||
{ timeout: 30000 }
|
||||
);
|
||||
@ -41,6 +41,7 @@ export const DEFAULT_SIDEBAR_CONFIG = {
|
||||
children: [
|
||||
{ key: 'daily-inventory', label: '每日进销存', icon: 'table', routeKey: 'daily-inventory', visible: true, order: 10 },
|
||||
{ key: 'stock-sales-analysis', label: '库存动销分析', icon: 'line-chart', routeKey: 'stock-sales-analysis', visible: true, order: 20 },
|
||||
{ key: 'picking-bin-sales-analysis', label: '拣选库位动销分析', icon: 'inbox', routeKey: 'picking-bin-sales-analysis', visible: true, order: 30 },
|
||||
],
|
||||
},
|
||||
{
|
||||
|
||||
91
frontend/src/constants/pickingBinSalesAnalysis.js
Normal file
91
frontend/src/constants/pickingBinSalesAnalysis.js
Normal file
@ -0,0 +1,91 @@
|
||||
/** 拣选库位动销分析 —— 表头与口径说明（粒度：仓位 × 商品编码） */
|
||||
|
||||
export const PICKING_BIN_COLUMNS = [
|
||||
{
|
||||
key: 'warehouse',
|
||||
label: '仓库',
|
||||
tip: '聚水潭主仓（按 wms_co_id 单仓独立统计，不合并分仓）',
|
||||
},
|
||||
{
|
||||
key: 'binName',
|
||||
label: '仓位',
|
||||
tip: '系统仓位编码（jst_packInventoryInfo.bin）；同一仓位有多个商品时会出现多行',
|
||||
},
|
||||
{
|
||||
key: 'binType',
|
||||
label: '类型',
|
||||
tip: '统计范围固定为真实拣选仓位（pack_type=Bin 且 bin_group 为空）；箱记录和存货位不纳入动销分析',
|
||||
},
|
||||
{
|
||||
key: 'skuId',
|
||||
label: '商品编码',
|
||||
tip: '商品编码（jst_packInventoryInfo.sku_id），一行就是一个「仓位 × 商品」组合',
|
||||
},
|
||||
{
|
||||
key: 'stockQty',
|
||||
label: '库存数量',
|
||||
tip: '该商品在这个仓位上的当前库存件数（同一仓位多行的库存相加 = 仓位库存）',
|
||||
},
|
||||
{
|
||||
key: 'salesQty',
|
||||
label: '商品销量',
|
||||
tip: '近 N 天该商品的销量（整档，不按仓位分摊）：含线上订单、组合装子件拆分、全民客户(B2B分销)渠道；同一商品占几个仓位，这几行显示的销量都一样，所以这一列不能直接按行求和',
|
||||
},
|
||||
{
|
||||
key: 'dailySales',
|
||||
label: '商品日销',
|
||||
tip: '商品销量 ÷ 统计天数，动销等级严格按此值划分',
|
||||
},
|
||||
{
|
||||
key: 'gradeKey',
|
||||
label: '动销等级',
|
||||
tip: '按商品日销划分：爆款≥20 / 热卖款 10~20 / 平销款 3~10 / 滞销款-1 1~3 / 滞销款-2 0~1 / 销售=0',
|
||||
},
|
||||
];
|
||||
|
||||
export const PICKING_BIN_WINDOW_OPTIONS = [
|
||||
{ label: '近 7 天', value: 7 },
|
||||
{ label: '近 15 天', value: 15 },
|
||||
{ label: '近 30 天', value: 30 },
|
||||
{ label: '近 60 天', value: 60 },
|
||||
];
|
||||
|
||||
export const PICKING_BIN_DEFAULT_DAYS = 30;
|
||||
|
||||
/** 动销等级展示色（与后端 key 对应） */
|
||||
export const PICKING_BIN_GRADE_COLORS = {
|
||||
hot: 'red',
|
||||
best: 'volcano',
|
||||
normal: 'blue',
|
||||
slow1: 'gold',
|
||||
slow2: 'default',
|
||||
zero: 'default',
|
||||
};
|
||||
|
||||
export const PICKING_BIN_DESCRIPTION = [
|
||||
'【统计范围】只统计真实拣选仓位（pack_type=Bin 且 bin_group 为空）；箱记录（pack_type=Pack）和存货位不纳入动销分析。',
|
||||
'',
|
||||
'【粒度】一行 = 一个「仓位 × 商品编码」组合。一个仓位放了 N 个商品就显示 N 行，列表与导出一致。',
|
||||
' 库存数量 = 该商品在这个仓位上的实际库存（同一仓位多行相加 = 仓位总库存）。',
|
||||
'',
|
||||
'【动销等级口径】按「商品日销」分档：商品日销 = 该商品近 N 天销量 ÷ N。',
|
||||
' 爆款：日销 ≥ 20；热卖款：10 ≤ 日销 ＜ 20；平销款：3 ≤ 日销 ＜ 10；',
|
||||
' 滞销款-1：1 ≤ 日销 ＜ 3；滞销款-2：0 ＜ 日销 ＜ 1；销售=0：窗口内该商品无销量。',
|
||||
' 等级判断的是商品本身的销售速度，与其摆放位置无关 —— 同一件商品占几个仓位，等级都相同。',
|
||||
'默认统计窗口为近 30 天，可切换 7 / 15 / 30 / 60 天。',
|
||||
'',
|
||||
'【为什么不用分摊销量来定等级】',
|
||||
' 同一 SKU 常同时占用多个同类型仓位（崔桥工厂仓实测 3290 个拣选 SKU 中 1431 个跨多个拣选位，最多 24 个）。',
|
||||
' 若把销量先摊到每个仓位再定等级，一件日销 100 的爆款放在 10 个仓位就会被算成日销 10 —— 判断的是「摆法」而不是商品。',
|
||||
'',
|
||||
'【商品销量为什么不能直接按行求和】',
|
||||
' 商品销量取的是整档销量（不除以占用仓位数），同一件商品跨仓位会重复出现，所以该列相加无意义。',
|
||||
' 需要按行求和请用导出文件里的「分摊销量」列：商品销量 ÷ 其占用的同类型仓位数。',
|
||||
' 恒等式已实测校验（差异 0）：全部行分摊销量之和 = 有库位记录的那些 SKU 的销量之和。',
|
||||
' 注意它小于全仓销量：jst_packInventoryInfo 只覆盖部分 SKU 的仓位数据，无仓位记录的 SKU 销量不计入任何库位。',
|
||||
'',
|
||||
'数据来源：库存取 rpt.jst_packInventoryInfo（按仓库 + 仓位 + 商品汇总）；',
|
||||
'销量按 send_date 窗口统计 4 个来源：① jst_Orders 普通商品行；② jst_Orders 组合装行（ZH-拼接编码）按组合装映射展开为子件；',
|
||||
'③ jst_qmcust_orderlist 全民客户(B2B分销)普通行（排除拆单父单 Split）；④ qmcust 组合装行同样展开。',
|
||||
'统计范围：只取 pack_type=Bin 且 bin_group 为空的真实拣选仓位，箱记录（pack_type=Pack）和存货位（bin_group=存货）不参与统计。',
|
||||
];
|
||||
@ -6,6 +6,7 @@ export const SIDEBAR_PAGES = [
|
||||
{ pageKey: 'settings', path: '/settings', name: '系统设置', category: 'system', defaultRoles: DEFAULT_SYSTEM_ROLES },
|
||||
{ pageKey: 'daily_inventory', path: '/planning/operation/daily-inventory', name: '每日进销存', category: 'planning', defaultRoles: DEFAULT_PLANNING_ROLES },
|
||||
{ pageKey: 'stock_sales_analysis', path: '/planning/operation/stock-sales-analysis', name: '库存动销分析', category: 'planning', defaultRoles: DEFAULT_PLANNING_ROLES },
|
||||
{ pageKey: 'picking_bin_sales_analysis', path: '/planning/operation/picking-bin-sales-analysis', name: '拣选库位动销分析', category: 'planning', defaultRoles: DEFAULT_PLANNING_ROLES },
|
||||
{ pageKey: 'returns_inventory_cuiqiao', path: '/planning/operation/returns-inventory/cuiqiao', name: '崔桥工厂仓客退数据监控', category: 'planning', defaultRoles: DEFAULT_PLANNING_ROLES },
|
||||
{ pageKey: 'returns_inventory_subei', path: '/planning/operation/returns-inventory/subei', name: '苏北客退数据监控', category: 'planning', defaultRoles: DEFAULT_PLANNING_ROLES },
|
||||
{ pageKey: 'returns_inventory_suining', path: '/planning/operation/returns-inventory/suining', name: '睢宁客退数据监控', category: 'planning', defaultRoles: DEFAULT_PLANNING_ROLES },
|
||||
|
||||
@ -121,6 +121,7 @@ const SIDEBAR_ROUTE_MAP = {
|
||||
},
|
||||
'daily-inventory': { path: '/planning/operation/daily-inventory' },
|
||||
'stock-sales-analysis': { path: '/planning/operation/stock-sales-analysis' },
|
||||
'picking-bin-sales-analysis': { path: '/planning/operation/picking-bin-sales-analysis' },
|
||||
settings: { path: '/settings' },
|
||||
'online-desktop-users': { path: '/settings/online-desktop-users' },
|
||||
'desktop-execution-tasks': { path: '/settings/desktop-execution-tasks' },
|
||||
|
||||
146
frontend/src/pages/MkhBomData/WarehouseShipTimelinessSidebar.jsx
Normal file
146
frontend/src/pages/MkhBomData/WarehouseShipTimelinessSidebar.jsx
Normal file
@ -0,0 +1,146 @@
|
||||
import React from 'react';
|
||||
import { Tooltip } from 'antd';
|
||||
|
||||
/**
|
||||
* 仓储中心 24 小时发货时效 —— 列表左侧侧栏
|
||||
* ────────────────────────────────────────────────────────────────────────────
|
||||
* 业务需求（yanhui 2026-10-20）：
|
||||
* 「在列表左侧加一栏仓储中心24小时发货时效，下面列 11 个仓…只显示超时未发」
|
||||
*
|
||||
* 设计取舍：
|
||||
* · **只显示超时未发** —— 数量为 0 的仓也保留一行（浅色显示「—」），
|
||||
* 这样业务能一眼看出「这个仓今天全发完了」，比直接隐藏更有信息量；
|
||||
* · 点击某个仓 → 把该仓的超时订单筛到右侧表格里（不跳页，避免业务来回切）；
|
||||
* · 崔桥工厂半成品仓系统里没有同名仓，`placeholder=true` 时显示「未接入」。
|
||||
*
|
||||
* 数据来自 getWarehouseShipTimeliness，字段见后端 warehouseShipTimelinessService。
|
||||
*/
|
||||
function WarehouseShipTimelinessSidebar({
|
||||
data, loading, error, activeCenter, onSelectCenter, collapsed, onToggleCollapse,
|
||||
}) {
|
||||
const centers = data && Array.isArray(data.centers) ? data.centers : [];
|
||||
const summary = data && data.summary ? data.summary : null;
|
||||
const round = data && data.round ? data.round : null;
|
||||
const freshness = data && data.freshness ? data.freshness : null;
|
||||
const totalOverdue = summary ? summary.overdueUnshipped : 0;
|
||||
const hasRound = Boolean(round);
|
||||
// 12:00 之前后端返回 round=null + notStarted=true：
|
||||
// 业务口径是「12:00 才开始第一轮核验」，此时**不展示任何超时数字**，
|
||||
// 也不回填昨天的结算结果（否则会被误当成今天的超时）。
|
||||
const notStarted = Boolean(data && data.notStarted);
|
||||
// 核验截止时刻：12:00 轮截止 07:00、16:00 轮截止 16:00，直接展示，业务不用换算
|
||||
const cutoffText = String((round && round.windowEnd) || '').slice(11, 16);
|
||||
|
||||
return (
|
||||
<aside className={`mkh-ship-side${collapsed ? ' mkh-ship-side--collapsed' : ''}`}>
|
||||
<div className="mkh-ship-side__head">
|
||||
<div className="mkh-ship-side__title">
|
||||
<span>仓储中心24小时发货时效</span>
|
||||
<button
|
||||
type="button"
|
||||
className="mkh-ship-side__collapse"
|
||||
onClick={onToggleCollapse}
|
||||
title={collapsed ? '展开侧栏' : '收起侧栏'}
|
||||
>
|
||||
{collapsed ? '»' : '«'}
|
||||
</button>
|
||||
</div>
|
||||
{collapsed ? null : (
|
||||
<>
|
||||
<div className="mkh-ship-side__meta">
|
||||
{hasRound ? (
|
||||
<>
|
||||
<span className="mkh-ship-side__round">{round.label}</span>
|
||||
<span>核验 {cutoffText} 前</span>
|
||||
</>
|
||||
) : (
|
||||
<span className="mkh-ship-side__round mkh-ship-side__round--idle">未到核验时间</span>
|
||||
)}
|
||||
</div>
|
||||
<div className="mkh-ship-side__total">
|
||||
<span className="mkh-ship-side__total-num" data-zero={totalOverdue === 0}>
|
||||
{notStarted ? '—' : totalOverdue}
|
||||
</span>
|
||||
<span className="mkh-ship-side__total-label">单超时未发</span>
|
||||
</div>
|
||||
</>
|
||||
)}
|
||||
</div>
|
||||
|
||||
{collapsed ? null : (
|
||||
<>
|
||||
<ul className="mkh-ship-side__list">
|
||||
{centers.map((c) => {
|
||||
const isActive = activeCenter === c.key;
|
||||
const empty = !c.overdueUnshipped;
|
||||
// 12:00 前未开始核验时，所有仓都不能点（点了也筛不出东西，
|
||||
// 反而让人以为「这个仓没问题」）
|
||||
const disabled = c.placeholder || notStarted;
|
||||
return (
|
||||
<li key={c.key}>
|
||||
<Tooltip
|
||||
placement="right"
|
||||
title={c.placeholder
|
||||
? '系统仓库表中暂无同名仓，待上游建仓后自动接入'
|
||||
: notStarted
|
||||
? '今日 12:00 首轮核验开始后才展示超时数据'
|
||||
: `${c.name}：超时未发 ${c.overdueUnshipped} 单`
|
||||
+ `（已发但超时 ${c.overdueShipped}，窗口订单 ${c.windowOrderCount}`
|
||||
+ `${c.onTimeRate === null ? '' : `，按时率 ${c.onTimeRate}%`}）`
|
||||
+ (c.warehouseCount > 1 ? `\n含子仓 ${c.warehouseCount} 个` : '')}
|
||||
>
|
||||
<button
|
||||
type="button"
|
||||
className={[
|
||||
'mkh-ship-side__item',
|
||||
isActive ? 'is-active' : '',
|
||||
empty ? 'is-empty' : '',
|
||||
disabled ? 'is-disabled' : '',
|
||||
].filter(Boolean).join(' ')}
|
||||
onClick={() => {
|
||||
if (disabled) return;
|
||||
onSelectCenter?.(isActive ? '' : c.key);
|
||||
}}
|
||||
>
|
||||
<span className="mkh-ship-side__name">{c.name}</span>
|
||||
{c.placeholder ? (
|
||||
<span className="mkh-ship-side__badge mkh-ship-side__badge--none">未接入</span>
|
||||
) : (
|
||||
<span className={`mkh-ship-side__badge${c.overdueUnshipped > 0 ? ' is-warn' : ''}`}>
|
||||
{c.overdueUnshipped > 0 ? c.overdueUnshipped : '—'}
|
||||
</span>
|
||||
)}
|
||||
</button>
|
||||
</Tooltip>
|
||||
</li>
|
||||
);
|
||||
})}
|
||||
</ul>
|
||||
|
||||
{error ? (
|
||||
<div className="mkh-ship-side__warn" title={error}>时效数据加载失败</div>
|
||||
) : null}
|
||||
|
||||
{freshness && freshness.stale ? (
|
||||
<div className="mkh-ship-side__warn" title={freshness.hint}>
|
||||
发货数据可能滞后
|
||||
</div>
|
||||
) : null}
|
||||
|
||||
{/* 12:00 前：首轮核验还没开始，明确告诉业务「几点开始看」，
|
||||
而不是拿昨天的结算数字冒充今天。 */}
|
||||
{notStarted ? (
|
||||
<div className="mkh-ship-side__hint">
|
||||
{String(data.nextRoundAt || '').slice(11, 16) || '12:00'} 开始首轮核验
|
||||
（核验当日 07:00 前创建的订单）
|
||||
</div>
|
||||
) : null}
|
||||
|
||||
{loading ? <div className="mkh-ship-side__loading">加载中…</div> : null}
|
||||
</>
|
||||
)}
|
||||
</aside>
|
||||
);
|
||||
}
|
||||
|
||||
export default WarehouseShipTimelinessSidebar;
|
||||
@ -19,6 +19,8 @@ import {
|
||||
} from '../../api/mkhBom';
|
||||
import { createRequestGate } from '../../utils/requestGate';
|
||||
import { requireMkhFreshContract } from '../../utils/mkhFreshContract';
|
||||
import WarehouseShipTimelinessSidebar from './WarehouseShipTimelinessSidebar';
|
||||
import { getWarehouseShipTimeliness } from '../../api/warehouseShipTimeliness';
|
||||
import { exportTableFile } from '../../utils/tableExport';
|
||||
import './style.css';
|
||||
|
||||
@ -33,6 +35,16 @@ const number = (value, digits = 0) => (value === null || value === undefined ||
|
||||
|
||||
const text = (value) => (value === null || value === undefined || value === '' ? '--' : value);
|
||||
|
||||
// 超时时长（分钟）→ 「3小时20分」。业务看的是「晚了多久」，不是精确到分钟的秒数。
|
||||
const formatOverdue = (minutes) => {
|
||||
const m = Number(minutes);
|
||||
if (!Number.isFinite(m) || m <= 0) return '—';
|
||||
if (m < 60) return `${m} 分钟`;
|
||||
const h = Math.floor(m / 60);
|
||||
const rest = m % 60;
|
||||
return rest ? `${h} 小时 ${rest} 分` : `${h} 小时`;
|
||||
};
|
||||
|
||||
const EMPTY_FILTERS = { keyword: '', scope: 'warning' };
|
||||
// 页面长时打开时定期重读。后端共享 10 分钟索引缓存，因此多个页面的轮询
|
||||
// 会合并为每个缓存周期一次业务库读取，不会每 2 分钟重复扫远程大表。
|
||||
@ -300,6 +312,28 @@ const MkhBomData = () => {
|
||||
const autoRefreshPendingRef = useRef(false);
|
||||
const [bodyHeight, setBodyHeight] = useState(420);
|
||||
|
||||
// ── 仓储中心 24 小时发货时效（左侧侧栏）────────────────────────────────
|
||||
// 与 MKH 列表**完全独立**的两套数据：列表来自 BOM/库存，侧栏来自订单+出库表。
|
||||
// 所以侧栏单独 load、单独失败，不能因为发货时效查询慢就把整个列表拖住。
|
||||
const [shipTimeliness, setShipTimeliness] = useState(null);
|
||||
const [shipLoading, setShipLoading] = useState(false);
|
||||
const [shipError, setShipError] = useState('');
|
||||
const [shipCollapsed, setShipCollapsed] = useState(false);
|
||||
// 选中的主仓：非空时右侧表格只显示该仓的超时订单
|
||||
const [shipCenter, setShipCenter] = useState('');
|
||||
|
||||
// 当前选中主仓的名字 + 该仓的超时订单（从同一次快照里筛，保证与侧栏数字一致）
|
||||
const shipCenterName = useMemo(() => {
|
||||
if (!shipCenter || !shipTimeliness) return '';
|
||||
const hit = (shipTimeliness.centers || []).find((c) => c.key === shipCenter);
|
||||
return hit ? hit.name : shipCenter;
|
||||
}, [shipCenter, shipTimeliness]);
|
||||
|
||||
const shipCenterOrders = useMemo(() => {
|
||||
if (!shipCenter || !shipTimeliness) return [];
|
||||
return (shipTimeliness.orders || []).filter((o) => o.centerKey === shipCenter);
|
||||
}, [shipCenter, shipTimeliness]);
|
||||
|
||||
const beginRefreshProgress = useCallback((title) => {
|
||||
refreshProgressGenerationRef.current += 1;
|
||||
const generation = refreshProgressGenerationRef.current;
|
||||
@ -404,6 +438,33 @@ const MkhBomData = () => {
|
||||
|
||||
useEffect(() => { load(); }, [load]);
|
||||
|
||||
// ── 发货时效侧栏加载 ────────────────────────────────────────────────────
|
||||
// 注意：这个接口要扫两张订单表 + 出库表（rpt 全是视图），冷启动实测 40~50 秒，
|
||||
// 所以**不能**跟着列表每 2 分钟的轮询一起跑，否则后端一直被打。
|
||||
// 独立节奏：进页面拉一次，之后每 10 分钟一次（核验点只有 12:00 / 16:00 两个）。
|
||||
const loadShipTimeliness = useCallback(async () => {
|
||||
setShipLoading(true);
|
||||
setShipError('');
|
||||
try {
|
||||
const result = await getWarehouseShipTimeliness({ limit: 500 });
|
||||
if (!result || result.success === false) {
|
||||
setShipError(result?.error || '加载发货时效失败');
|
||||
return;
|
||||
}
|
||||
setShipTimeliness(result.data || null);
|
||||
} catch (shipLoadError) {
|
||||
setShipError(shipLoadError?.response?.data?.error || shipLoadError?.message || '加载发货时效失败');
|
||||
} finally {
|
||||
setShipLoading(false);
|
||||
}
|
||||
}, []);
|
||||
|
||||
useEffect(() => {
|
||||
loadShipTimeliness();
|
||||
const timer = window.setInterval(loadShipTimeliness, 10 * 60 * 1000);
|
||||
return () => window.clearInterval(timer);
|
||||
}, [loadShipTimeliness]);
|
||||
|
||||
// 页面保持打开时自动更新；从后台切回且数据已超过轮询周期时立即重读。
|
||||
// 后台刷新保留当前表格，不显示全页加载态；请求门仍会阻止旧响应覆盖新数据。
|
||||
useEffect(() => {
|
||||
@ -986,6 +1047,73 @@ const MkhBomData = () => {
|
||||
</div>
|
||||
</header>
|
||||
|
||||
{/*
|
||||
左侧「仓储中心24小时发货时效」侧栏 + 右侧原有内容，横向两栏。
|
||||
侧栏收起时只留一条窄条，主区自动占满 —— 用 flex，不用固定宽度判断。
|
||||
*/}
|
||||
<div className="mkh-with-side">
|
||||
<WarehouseShipTimelinessSidebar
|
||||
data={shipTimeliness}
|
||||
loading={shipLoading}
|
||||
error={shipError}
|
||||
activeCenter={shipCenter}
|
||||
onSelectCenter={setShipCenter}
|
||||
collapsed={shipCollapsed}
|
||||
onToggleCollapse={() => setShipCollapsed((v) => !v)}
|
||||
/>
|
||||
|
||||
<div className="mkh-with-side__main">
|
||||
{/*
|
||||
超时明细：只有点了左侧某个仓才展开。
|
||||
明细来自同一次 list 接口（已按 order_date 升序，最早的最急），
|
||||
不再单独请求 —— 两套数据必须同源同快照，否则数字对不上。
|
||||
*/}
|
||||
{shipCenter && shipTimeliness ? (
|
||||
<Card
|
||||
bordered={false}
|
||||
className="mkh-ship-detail"
|
||||
title={(
|
||||
<span>
|
||||
超时未发订单 · <b>{shipCenterName}</b>
|
||||
<span className="mkh-ship-detail__count">
|
||||
{shipCenterOrders.length} 单
|
||||
{shipTimeliness.truncated && shipCenterOrders.length >= (shipTimeliness.detailLimit || 0)
|
||||
? `（已截断，仅显示前 ${shipTimeliness.detailLimit} 条）`
|
||||
: ''}
|
||||
</span>
|
||||
</span>
|
||||
)}
|
||||
extra={<button type="button" className="mkh-ship-detail__clear" onClick={() => setShipCenter('')}>关闭</button>}
|
||||
>
|
||||
<Table
|
||||
rowKey={(r) => `${r.source}-${r.oId}`}
|
||||
size="small"
|
||||
pagination={{ pageSize: 10, size: 'small', showSizeChanger: false }}
|
||||
dataSource={shipCenterOrders}
|
||||
columns={[
|
||||
{ title: '订单号', dataIndex: 'oId', width: 110 },
|
||||
{ title: '店铺', dataIndex: 'shopName', width: 190, ellipsis: true },
|
||||
{ title: '发货仓', dataIndex: 'warehouseName', width: 190, ellipsis: true },
|
||||
{ title: '创建时间', dataIndex: 'orderDate', width: 150 },
|
||||
{
|
||||
title: '超时时长',
|
||||
dataIndex: 'overdueMinutes',
|
||||
width: 96,
|
||||
render: (v) => <span className="mkh-ship-detail__overdue">{formatOverdue(v)}</span>,
|
||||
},
|
||||
{
|
||||
title: '金额',
|
||||
dataIndex: 'payAmount',
|
||||
width: 96,
|
||||
align: 'right',
|
||||
render: (v) => `¥${number(v, 2)}`,
|
||||
},
|
||||
{ title: '状态', dataIndex: 'status', width: 110 },
|
||||
]}
|
||||
/>
|
||||
</Card>
|
||||
) : null}
|
||||
|
||||
<Card className="mkh-filter-card" bordered={false}>
|
||||
<Space wrap size={[12, 12]}>
|
||||
<Input
|
||||
@ -1346,6 +1474,8 @@ const MkhBomData = () => {
|
||||
</div>
|
||||
</Spin>
|
||||
</Modal>
|
||||
</div>
|
||||
</div>
|
||||
</SycmPage>
|
||||
);
|
||||
};
|
||||
|
||||
@ -391,3 +391,280 @@
|
||||
.mkh-alert-form--inner {
|
||||
margin-top: 4px;
|
||||
}
|
||||
|
||||
/* ============ 仓储中心 24 小时发货时效（左侧侧栏，2026-10-20）============ */
|
||||
/* 左侧固定宽侧栏 + 右侧自适应主区。不用 float，避免表格宽度塌陷。
|
||||
侧栏 sticky 贴顶：表格很长时侧栏跟着滚走会看不到告警数，业务要一直看得见。 */
|
||||
.mkh-with-side {
|
||||
display: flex;
|
||||
align-items: flex-start;
|
||||
gap: 14px;
|
||||
}
|
||||
|
||||
.mkh-with-side__main {
|
||||
flex: 1 1 auto;
|
||||
min-width: 0;
|
||||
}
|
||||
|
||||
.mkh-ship-side {
|
||||
flex: none;
|
||||
align-self: flex-start;
|
||||
width: 208px;
|
||||
/* 11 个仓 + 头部合计约 400px，给足高度；超出才滚动。
|
||||
不加 max-height 会被 flex 拉伸/压缩，撑开后又不该无限长（表格很长时）。 */
|
||||
max-height: calc(100vh - 40px);
|
||||
overflow-y: auto;
|
||||
padding: 12px 10px 14px;
|
||||
border: 1px solid var(--sycm-border-color);
|
||||
border-radius: 10px;
|
||||
background: var(--sycm-bg-container);
|
||||
box-sizing: border-box;
|
||||
position: sticky;
|
||||
top: 8px;
|
||||
}
|
||||
|
||||
.mkh-ship-side--collapsed {
|
||||
width: 44px;
|
||||
padding: 12px 6px;
|
||||
}
|
||||
|
||||
.mkh-ship-side__head {
|
||||
margin-bottom: 10px;
|
||||
}
|
||||
|
||||
.mkh-ship-side__title {
|
||||
display: flex;
|
||||
align-items: center;
|
||||
justify-content: space-between;
|
||||
gap: 6px;
|
||||
font-size: 13px;
|
||||
font-weight: 600;
|
||||
line-height: 1.4;
|
||||
color: var(--sycm-text-strong);
|
||||
}
|
||||
|
||||
.mkh-ship-side--collapsed .mkh-ship-side__title > span {
|
||||
writing-mode: vertical-rl;
|
||||
letter-spacing: 2px;
|
||||
font-size: 12px;
|
||||
}
|
||||
|
||||
.mkh-ship-side__collapse {
|
||||
flex: none;
|
||||
width: 18px;
|
||||
height: 18px;
|
||||
padding: 0;
|
||||
border: 1px solid var(--sycm-border-color);
|
||||
border-radius: 4px;
|
||||
background: transparent;
|
||||
color: var(--sycm-text-tertiary);
|
||||
font-size: 11px;
|
||||
line-height: 1;
|
||||
cursor: pointer;
|
||||
}
|
||||
|
||||
.mkh-ship-side__collapse:hover {
|
||||
color: var(--sycm-text-primary);
|
||||
border-color: var(--sycm-text-tertiary);
|
||||
}
|
||||
|
||||
.mkh-ship-side__meta {
|
||||
display: flex;
|
||||
flex-wrap: wrap;
|
||||
gap: 4px 8px;
|
||||
margin-top: 6px;
|
||||
font-size: 11px;
|
||||
color: var(--sycm-text-tertiary);
|
||||
}
|
||||
|
||||
.mkh-ship-side__round {
|
||||
padding: 0 5px;
|
||||
border-radius: 3px;
|
||||
background: rgba(22, 119, 255, 0.12);
|
||||
color: #0958d9;
|
||||
}
|
||||
|
||||
.mkh-ship-side__round--idle {
|
||||
background: rgba(136, 135, 128, 0.14);
|
||||
color: var(--sycm-text-tertiary);
|
||||
}
|
||||
|
||||
.mkh-ship-side__total {
|
||||
display: flex;
|
||||
align-items: baseline;
|
||||
gap: 6px;
|
||||
margin-top: 8px;
|
||||
padding-top: 8px;
|
||||
border-top: 1px dashed var(--sycm-border-color);
|
||||
}
|
||||
|
||||
.mkh-ship-side__total-num {
|
||||
font-size: 22px;
|
||||
font-weight: 700;
|
||||
line-height: 1;
|
||||
color: #cf1322;
|
||||
font-variant-numeric: tabular-nums;
|
||||
}
|
||||
|
||||
.mkh-ship-side__total-num[data-zero='true'] {
|
||||
color: var(--sycm-text-tertiary);
|
||||
}
|
||||
|
||||
.mkh-ship-side__total-label {
|
||||
font-size: 11px;
|
||||
color: var(--sycm-text-tertiary);
|
||||
}
|
||||
|
||||
.mkh-ship-side__list {
|
||||
margin: 0;
|
||||
padding: 0;
|
||||
list-style: none;
|
||||
}
|
||||
|
||||
.mkh-ship-side__item {
|
||||
display: flex;
|
||||
align-items: center;
|
||||
justify-content: space-between;
|
||||
gap: 6px;
|
||||
width: 100%;
|
||||
padding: 5px 7px;
|
||||
margin-bottom: 2px;
|
||||
border: 1px solid transparent;
|
||||
border-radius: 6px;
|
||||
background: transparent;
|
||||
font-size: 12px;
|
||||
text-align: left;
|
||||
color: var(--sycm-text-primary);
|
||||
cursor: pointer;
|
||||
transition: background 0.12s ease;
|
||||
}
|
||||
|
||||
.mkh-ship-side__item:hover {
|
||||
background: var(--sycm-bg-subtle);
|
||||
}
|
||||
|
||||
.mkh-ship-side__item.is-active {
|
||||
border-color: rgba(22, 119, 255, 0.45);
|
||||
background: rgba(22, 119, 255, 0.08);
|
||||
}
|
||||
|
||||
.mkh-ship-side__item.is-empty .mkh-ship-side__name {
|
||||
color: var(--sycm-text-tertiary);
|
||||
}
|
||||
|
||||
.mkh-ship-side__item.is-disabled {
|
||||
cursor: not-allowed;
|
||||
opacity: 0.6;
|
||||
}
|
||||
|
||||
.mkh-ship-side__name {
|
||||
overflow: hidden;
|
||||
white-space: nowrap;
|
||||
text-overflow: ellipsis;
|
||||
}
|
||||
|
||||
.mkh-ship-side__badge {
|
||||
flex: none;
|
||||
min-width: 22px;
|
||||
padding: 0 5px;
|
||||
border-radius: 9px;
|
||||
background: rgba(136, 135, 128, 0.12);
|
||||
font-size: 11px;
|
||||
font-weight: 600;
|
||||
line-height: 16px;
|
||||
text-align: center;
|
||||
color: var(--sycm-text-tertiary);
|
||||
font-variant-numeric: tabular-nums;
|
||||
}
|
||||
|
||||
.mkh-ship-side__badge.is-warn {
|
||||
background: rgba(255, 77, 79, 0.16);
|
||||
color: #cf1322;
|
||||
}
|
||||
|
||||
.mkh-ship-side__badge--none {
|
||||
font-weight: 400;
|
||||
}
|
||||
|
||||
.mkh-ship-side__warn {
|
||||
margin-top: 10px;
|
||||
padding: 6px 8px;
|
||||
border-radius: 6px;
|
||||
background: rgba(250, 173, 20, 0.14);
|
||||
font-size: 11px;
|
||||
line-height: 1.6;
|
||||
color: #ad4e00;
|
||||
cursor: help;
|
||||
}
|
||||
|
||||
.mkh-ship-side__hint {
|
||||
margin-top: 10px;
|
||||
font-size: 11px;
|
||||
line-height: 1.6;
|
||||
color: var(--sycm-text-tertiary);
|
||||
}
|
||||
|
||||
.mkh-ship-side__loading {
|
||||
margin-top: 8px;
|
||||
font-size: 11px;
|
||||
color: var(--sycm-text-tertiary);
|
||||
}
|
||||
|
||||
/* 窄屏兜底（<1100px）：此时右侧表格已经放不下，侧栏整体铺到上方。
|
||||
* ⚠️ 1100~1500px 之间**保持左侧竖栏** —— 那是业务最常用的笔记本分辨率
|
||||
* （1366×768 / 1440×900），侧栏必须还在左边，否则「列表左侧加一栏」的需求就不成立。
|
||||
* 列表始终竖向排列，绝不横排（横排后 11 个仓挤成一行完全没法读）。 */
|
||||
@media (max-width: 1100px) {
|
||||
.mkh-with-side {
|
||||
flex-direction: column;
|
||||
}
|
||||
|
||||
.mkh-ship-side,
|
||||
.mkh-ship-side--collapsed {
|
||||
position: static;
|
||||
width: 100%;
|
||||
max-height: none;
|
||||
}
|
||||
|
||||
.mkh-ship-side--collapsed .mkh-ship-side__title > span {
|
||||
writing-mode: horizontal-tb;
|
||||
}
|
||||
}
|
||||
|
||||
/* 中窄屏（1100~1500px，含 1366×768 笔记本）：侧栏仍在左侧，只是收窄 */
|
||||
@media (max-width: 1500px) and (min-width: 1101px) {
|
||||
.mkh-ship-side {
|
||||
width: 176px;
|
||||
}
|
||||
|
||||
.mkh-ship-side__name {
|
||||
font-size: 12px;
|
||||
}
|
||||
}
|
||||
|
||||
/* 超时明细卡（点左侧某个仓才出现） */
|
||||
.mkh-ship-detail {
|
||||
margin-bottom: 12px;
|
||||
}
|
||||
|
||||
.mkh-ship-detail__count {
|
||||
margin-left: 8px;
|
||||
font-size: 12px;
|
||||
font-weight: 400;
|
||||
color: var(--sycm-text-tertiary);
|
||||
}
|
||||
|
||||
.mkh-ship-detail__clear {
|
||||
padding: 0;
|
||||
border: none;
|
||||
background: transparent;
|
||||
color: #0958d9;
|
||||
font-size: 12px;
|
||||
cursor: pointer;
|
||||
}
|
||||
|
||||
.mkh-ship-detail__overdue {
|
||||
color: #cf1322;
|
||||
font-weight: 600;
|
||||
font-variant-numeric: tabular-nums;
|
||||
}
|
||||
|
||||
288
frontend/src/pages/PickingBinSalesAnalysis/index.jsx
Normal file
288
frontend/src/pages/PickingBinSalesAnalysis/index.jsx
Normal file
@ -0,0 +1,288 @@
|
||||
import React, { useCallback, useEffect, useMemo, useState } from 'react';
|
||||
import {
|
||||
Alert, Button, Card, Col, Input, Modal, Row, Select, Space, Spin, Statistic, Table, Tag, Tooltip, Typography, message,
|
||||
} from 'antd';
|
||||
import { DownloadOutlined, InfoCircleOutlined, ReloadOutlined } from '@ant-design/icons';
|
||||
import { SycmPage } from '../../components/Common';
|
||||
import {
|
||||
exportPickingBinSalesAnalysis,
|
||||
getPickingBinSalesAnalysis,
|
||||
} from '../../api/pickingBinSalesAnalysis';
|
||||
import { downloadBlobAsFile, readBlobErrorMessage } from '../../utils/tableExport';
|
||||
import {
|
||||
PICKING_BIN_COLUMNS,
|
||||
PICKING_BIN_WINDOW_OPTIONS,
|
||||
PICKING_BIN_DEFAULT_DAYS,
|
||||
PICKING_BIN_GRADE_COLORS,
|
||||
PICKING_BIN_DESCRIPTION,
|
||||
} from '../../constants/pickingBinSalesAnalysis';
|
||||
import './style.css';
|
||||
|
||||
const { Title, Text } = Typography;
|
||||
|
||||
const int = (value) => {
|
||||
if (value === null || value === undefined || value === '') return '—';
|
||||
return Number(value).toLocaleString('zh-CN', { maximumFractionDigits: 0 });
|
||||
};
|
||||
|
||||
const num = (value, digits = 2) => {
|
||||
if (value === null || value === undefined || value === '') return '—';
|
||||
return Number(value).toLocaleString('zh-CN', {
|
||||
minimumFractionDigits: digits,
|
||||
maximumFractionDigits: digits,
|
||||
});
|
||||
};
|
||||
|
||||
const TipTitle = ({ tip, children }) => (
|
||||
<Tooltip title={tip}>
|
||||
<span className="picking-bin-th-tip">{children}</span>
|
||||
</Tooltip>
|
||||
);
|
||||
|
||||
const PickingBinSalesAnalysis = () => {
|
||||
const [loading, setLoading] = useState(false);
|
||||
const [error, setError] = useState('');
|
||||
const [data, setData] = useState(null);
|
||||
const [helpOpen, setHelpOpen] = useState(false);
|
||||
const [days, setDays] = useState(PICKING_BIN_DEFAULT_DAYS);
|
||||
const [warehouse, setWarehouse] = useState('');
|
||||
const [grades, setGrades] = useState([]);
|
||||
const [keyword, setKeyword] = useState('');
|
||||
const [exporting, setExporting] = useState(false);
|
||||
|
||||
const params = useMemo(() => ({
|
||||
days,
|
||||
...(warehouse ? { wmsCoId: warehouse } : {}),
|
||||
}), [days, warehouse]);
|
||||
|
||||
const load = useCallback(async (options = {}) => {
|
||||
setLoading(true);
|
||||
setError('');
|
||||
try {
|
||||
const res = await getPickingBinSalesAnalysis({
|
||||
...(options.params ?? params),
|
||||
...(options.force ? { refresh: '1' } : {}),
|
||||
});
|
||||
setData(res?.data ?? null);
|
||||
} catch (err) {
|
||||
setError(err?.response?.data?.error || err?.message || '拣选库位动销分析数据加载失败');
|
||||
setData(null);
|
||||
} finally {
|
||||
setLoading(false);
|
||||
}
|
||||
}, [params]);
|
||||
|
||||
useEffect(() => { load(); }, [load]);
|
||||
|
||||
const items = useMemo(() => data?.items ?? [], [data]);
|
||||
const gradeMap = useMemo(() => new Map((data?.grades ?? []).map((g) => [g.key, g])), [data]);
|
||||
|
||||
const filteredRows = useMemo(() => {
|
||||
const kw = keyword.trim().toLowerCase();
|
||||
return items.filter((it) => {
|
||||
if (grades.length > 0 && !grades.includes(it.gradeKey)) return false;
|
||||
if (kw
|
||||
&& !String(it.binName).toLowerCase().includes(kw)
|
||||
&& !String(it.skuId).toLowerCase().includes(kw)) return false;
|
||||
return true;
|
||||
});
|
||||
}, [items, grades, keyword]);
|
||||
|
||||
const columns = useMemo(() => PICKING_BIN_COLUMNS.map((col) => ({
|
||||
title: <TipTitle tip={col.tip}>{col.label}</TipTitle>,
|
||||
key: col.key,
|
||||
dataIndex: col.key,
|
||||
align: ['warehouse', 'binName', 'binType', 'skuId', 'gradeKey'].includes(col.key) ? 'left' : 'right',
|
||||
width: col.key === 'warehouse' ? 240
|
||||
: (col.key === 'gradeKey' ? 170 : (col.key === 'skuId' ? 150 : 110)),
|
||||
sorter: ['stockQty', 'salesQty', 'dailySales'].includes(col.key)
|
||||
? (a, b) => (a[col.key] ?? 0) - (b[col.key] ?? 0)
|
||||
: undefined,
|
||||
render: (value) => {
|
||||
if (col.key === 'gradeKey') {
|
||||
const g = gradeMap.get(value);
|
||||
return <Tag color={PICKING_BIN_GRADE_COLORS[value] || 'default'}>{g?.label || value}</Tag>;
|
||||
}
|
||||
if (col.key === 'binType' || col.key === 'skuId') return <Text>{value || '—'}</Text>;
|
||||
if (col.key === 'dailySales') return num(value, 2);
|
||||
if (col.key === 'salesQty') return num(value, 2);
|
||||
if (col.key === 'stockQty') return int(value);
|
||||
return value || '—';
|
||||
},
|
||||
})), [gradeMap]);
|
||||
|
||||
const handleExport = useCallback(async () => {
|
||||
setExporting(true);
|
||||
try {
|
||||
const blob = await exportPickingBinSalesAnalysis({
|
||||
...params,
|
||||
...(grades.length > 0 ? { grade: grades.join(',') } : {}),
|
||||
format: 'xlsx',
|
||||
});
|
||||
const dateKey = (data?.latestBizDate || '').replace(/-/g, '') || '未知日期';
|
||||
const fileName = `拣选库位动销分析-${dateKey}.xlsx`;
|
||||
await downloadBlobAsFile(blob, fileName);
|
||||
message.success(`已开始下载：${fileName}`);
|
||||
} catch (exportError) {
|
||||
message.error(await readBlobErrorMessage(exportError, '库位动销明细导出失败'));
|
||||
} finally {
|
||||
setExporting(false);
|
||||
}
|
||||
}, [params, grades, data?.latestBizDate]);
|
||||
|
||||
const toggleGrade = useCallback((key) => {
|
||||
setGrades((prev) => (prev.includes(key) ? prev.filter((k) => k !== key) : [...prev, key]));
|
||||
}, []);
|
||||
|
||||
const summary = data?.summary ?? [];
|
||||
const total = data?.total ?? null;
|
||||
|
||||
return (
|
||||
<SycmPage className="picking-bin-page">
|
||||
<div className="picking-bin-header">
|
||||
<div className="picking-bin-header__title">
|
||||
<Title level={4} style={{ margin: 0 }}>拣选库位动销分析</Title>
|
||||
<Button
|
||||
type="text"
|
||||
icon={<InfoCircleOutlined />}
|
||||
onClick={() => setHelpOpen(true)}
|
||||
className="picking-bin-header__help"
|
||||
>
|
||||
口径说明
|
||||
</Button>
|
||||
</div>
|
||||
<div className="picking-bin-header__meta">
|
||||
<span>统计基准日：{data?.latestBizDate || '—'}</span>
|
||||
{data?.shipWindow ? (
|
||||
<span>销量窗口：{data.shipWindow.shipFrom} ~ {data.shipWindow.shipTo}（近 {data.shipWindow.days} 天）</span>
|
||||
) : null}
|
||||
{data?.takenAt ? (
|
||||
<span>仓位数据时间：{new Date(data.takenAt).toLocaleString('zh-CN', { hour12: false })}</span>
|
||||
) : null}
|
||||
</div>
|
||||
<Space wrap>
|
||||
<Space size={4}>
|
||||
<Text type="secondary">统计天数</Text>
|
||||
<Select
|
||||
value={days}
|
||||
options={PICKING_BIN_WINDOW_OPTIONS}
|
||||
onChange={setDays}
|
||||
style={{ width: 110 }}
|
||||
/>
|
||||
</Space>
|
||||
<Select
|
||||
allowClear
|
||||
value={warehouse || undefined}
|
||||
options={(data?.warehouses ?? []).map((w) => ({ label: w.warehouse, value: w.wmsCoId }))}
|
||||
onChange={(v) => setWarehouse(v || '')}
|
||||
showSearch
|
||||
optionFilterProp="label"
|
||||
style={{ minWidth: 240 }}
|
||||
placeholder="全部仓库"
|
||||
/>
|
||||
<Input.Search
|
||||
allowClear
|
||||
value={keyword}
|
||||
onChange={(e) => setKeyword(e.target.value)}
|
||||
placeholder="搜索仓位 / 商品编码"
|
||||
style={{ width: 180 }}
|
||||
/>
|
||||
<Button icon={<ReloadOutlined />} onClick={() => load()} loading={loading}>刷新</Button>
|
||||
<Tooltip title="跳过 5 分钟缓存，重新扫描订单与仓位明细重算（约 5~20 秒）">
|
||||
<Button onClick={() => load({ force: true })} loading={loading}>强制重算</Button>
|
||||
</Tooltip>
|
||||
<Tooltip title="导出当前筛选下的明细（仓库 / 仓位 / 类型 / 商品编码 / 库存数量 / 仓位SKU数 / 商品销量 / 分摊销量 / 商品日销 / 动销等级），仅含拣选位。按「仓位 × 商品编码」拆行；商品销量为整档销量（同一商品跨仓位重复，不可直接求和），需要求和请用「分摊销量」列；动销等级按商品日销判定">
|
||||
<Button icon={<DownloadOutlined />} onClick={handleExport} loading={exporting}>导出明细</Button>
|
||||
</Tooltip>
|
||||
</Space>
|
||||
</div>
|
||||
|
||||
<Alert
|
||||
type="info"
|
||||
showIcon
|
||||
message={`按「商品日均销量（商品日销）」划分 ${summary.length} 个动销等级，一行 = 一个「仓位 × 商品编码」组合`}
|
||||
description="商品日销 = 该商品近 N 天销量 ÷ N，不做仓位分摊，因此同一件商品放在几个仓位都是同一等级。商品销量跨仓位重复显示，不能直接按行求和（需要求和请导出用「分摊销量」列）。点击下方等级卡片可筛选。"
|
||||
style={{ marginBottom: 12 }}
|
||||
/>
|
||||
|
||||
{error ? <Alert type="error" showIcon message={error} style={{ marginBottom: 12 }} /> : null}
|
||||
|
||||
<Spin spinning={loading}>
|
||||
<Row gutter={[8, 8]} className="picking-bin-summary">
|
||||
{summary.map((s) => (
|
||||
<Col key={s.key} flex="1 1 150px">
|
||||
<Card
|
||||
size="small"
|
||||
hoverable
|
||||
onClick={() => toggleGrade(s.key)}
|
||||
className={grades.includes(s.key) ? 'picking-bin-card is-active' : 'picking-bin-card'}
|
||||
>
|
||||
<Tooltip title={`${s.tip}；明细 ${int(s.rowCount)} 行，库存合计 ${int(s.stockQty)} 件，日销合计 ${num(s.dailySales, 1)}（销量按去重商品计）`}>
|
||||
<Statistic
|
||||
title={s.label}
|
||||
value={s.skuCount}
|
||||
suffix="品"
|
||||
valueStyle={{ fontSize: 18 }}
|
||||
/>
|
||||
</Tooltip>
|
||||
<div className="picking-bin-card__meta">
|
||||
明细 {int(s.rowCount)} 行 · 库存 {int(s.stockQty)} · 日销 {num(s.dailySales, 1)}
|
||||
</div>
|
||||
</Card>
|
||||
</Col>
|
||||
))}
|
||||
{total ? (
|
||||
<Col flex="1 1 150px">
|
||||
<Card size="small" className="picking-bin-card is-total">
|
||||
<Tooltip title={`去重商品 ${int(total.skuCount)} 个，涉及「仓位 × 商品」组合 ${int(total.rowCount)} 行`}>
|
||||
<Statistic title={total.label} value={total.skuCount} suffix="品" valueStyle={{ fontSize: 18 }} />
|
||||
</Tooltip>
|
||||
<div className="picking-bin-card__meta">
|
||||
明细 {int(total.rowCount)} 行 · 库存 {int(total.stockQty)} · 日销 {num(total.dailySales, 1)}
|
||||
</div>
|
||||
</Card>
|
||||
</Col>
|
||||
) : null}
|
||||
</Row>
|
||||
|
||||
<Table
|
||||
className="picking-bin-table"
|
||||
columns={columns}
|
||||
dataSource={filteredRows}
|
||||
rowKey={(r) => `${r.wmsCoId}::${r.binName}::${r.skuId}`}
|
||||
size="small"
|
||||
bordered
|
||||
pagination={{
|
||||
pageSize: 50,
|
||||
showSizeChanger: true,
|
||||
pageSizeOptions: [50, 100, 200],
|
||||
showTotal: (t) => `共 ${t} 条明细`,
|
||||
}}
|
||||
scroll={{ x: 'max-content', y: 'calc(100vh - 420px)' }}
|
||||
/>
|
||||
</Spin>
|
||||
|
||||
<Text type="secondary" className="picking-bin-footer">
|
||||
当前筛选出 {filteredRows.length} 条明细
|
||||
{items.length !== filteredRows.length ? `（共 ${items.length} 条）` : ''}；
|
||||
去重商品 {int(data?.total?.skuCount ?? 0)} 个；统计范围：仅拣选位。
|
||||
</Text>
|
||||
|
||||
<Modal
|
||||
title="计算口径说明"
|
||||
open={helpOpen}
|
||||
onCancel={() => setHelpOpen(false)}
|
||||
footer={null}
|
||||
width={760}
|
||||
>
|
||||
<div className="picking-bin-help">
|
||||
{PICKING_BIN_DESCRIPTION.map((line, i) => (
|
||||
<p key={i} className={line.startsWith(' ') ? 'is-indent' : ''}>{line}</p>
|
||||
))}
|
||||
</div>
|
||||
</Modal>
|
||||
</SycmPage>
|
||||
);
|
||||
};
|
||||
|
||||
export default PickingBinSalesAnalysis;
|
||||
95
frontend/src/pages/PickingBinSalesAnalysis/style.css
Normal file
95
frontend/src/pages/PickingBinSalesAnalysis/style.css
Normal file
@ -0,0 +1,95 @@
|
||||
.picking-bin-page {
|
||||
padding: 16px 20px 24px;
|
||||
display: flex;
|
||||
flex-direction: column;
|
||||
gap: 12px;
|
||||
}
|
||||
|
||||
.picking-bin-header {
|
||||
display: flex;
|
||||
flex-wrap: wrap;
|
||||
align-items: center;
|
||||
justify-content: space-between;
|
||||
gap: 12px;
|
||||
}
|
||||
|
||||
.picking-bin-header__title {
|
||||
display: flex;
|
||||
align-items: center;
|
||||
gap: 4px;
|
||||
}
|
||||
|
||||
.picking-bin-header__help {
|
||||
font-size: 13px;
|
||||
}
|
||||
|
||||
.picking-bin-header__meta {
|
||||
display: flex;
|
||||
flex-wrap: wrap;
|
||||
gap: 16px;
|
||||
font-size: 13px;
|
||||
color: rgba(0, 0, 0, 0.55);
|
||||
}
|
||||
|
||||
.picking-bin-th-tip {
|
||||
border-bottom: 1px dashed rgba(0, 0, 0, 0.35);
|
||||
cursor: help;
|
||||
white-space: nowrap;
|
||||
}
|
||||
|
||||
.picking-bin-summary {
|
||||
margin-bottom: 12px;
|
||||
}
|
||||
|
||||
.picking-bin-card .ant-statistic-title {
|
||||
font-size: 12px;
|
||||
white-space: nowrap;
|
||||
overflow: hidden;
|
||||
text-overflow: ellipsis;
|
||||
}
|
||||
|
||||
.picking-bin-card.is-active {
|
||||
border-color: #1677ff;
|
||||
box-shadow: 0 0 0 2px rgba(22, 119, 255, 0.12);
|
||||
}
|
||||
|
||||
.picking-bin-card.is-total {
|
||||
background: rgba(22, 119, 255, 0.06);
|
||||
}
|
||||
|
||||
.picking-bin-card__meta {
|
||||
margin-top: 4px;
|
||||
font-size: 12px;
|
||||
color: rgba(0, 0, 0, 0.55);
|
||||
white-space: nowrap;
|
||||
overflow: hidden;
|
||||
text-overflow: ellipsis;
|
||||
}
|
||||
|
||||
.picking-bin-table .ant-table-thead > tr > th {
|
||||
white-space: nowrap;
|
||||
font-size: 12px;
|
||||
padding: 6px 8px;
|
||||
}
|
||||
|
||||
.picking-bin-table .ant-table-tbody > tr > td {
|
||||
padding: 5px 8px;
|
||||
font-size: 13px;
|
||||
vertical-align: middle;
|
||||
}
|
||||
|
||||
.picking-bin-footer {
|
||||
font-size: 12px;
|
||||
}
|
||||
|
||||
.picking-bin-help p {
|
||||
margin: 0 0 6px;
|
||||
line-height: 1.7;
|
||||
font-size: 13px;
|
||||
white-space: pre-wrap;
|
||||
}
|
||||
|
||||
.picking-bin-help p.is-indent {
|
||||
padding-left: 16px;
|
||||
color: rgba(0, 0, 0, 0.65);
|
||||
}
|
||||
@ -71,6 +71,25 @@ const RETURN_DEADLINE_TAGS = {
|
||||
};
|
||||
const RETURN_DEADLINE_RANK = { overdue: 3, pending: 2, done: 1, none: 0 };
|
||||
|
||||
// 供应商是否合作（后端取自简尚供应商主数据 js_supplier.coopersgn：1=合作 / 2=不合作）
|
||||
const SUPPLIER_COOP_TAGS = {
|
||||
cooperative: { color: 'green', label: '合作' },
|
||||
ended: { color: 'red', label: '不合作' },
|
||||
unknown: { color: 'default', label: '未知' },
|
||||
};
|
||||
const SUPPLIER_COOP_RANK = { ended: 2, cooperative: 1, unknown: 0 };
|
||||
const SUPPLIER_COOP_TIPS = {
|
||||
cooperative: '该商品编码的供应商在简尚供应商主数据里标记为「合作中」（js_supplier.coopersgn = 1）',
|
||||
ended: '该商品编码的供应商在简尚供应商主数据里标记为「不合作」（js_supplier.coopersgn = 2），退货处理需另行商定对接人',
|
||||
unknown: '供应商名在简尚供应商主数据（js_supplier）里没有匹配记录，或该编码没有供应商，无法判断合作状态',
|
||||
};
|
||||
|
||||
// 最晚退厂时间（工作平台「供应商不合作」）—— 2026-09-28 先上线占位：
|
||||
// 数据属于工作平台库 WorkbenchDataShared，副本账号目前没有该库访问权限（后端
|
||||
// supplierExitDateProvider.js 有完整取证），IT 开通后自动出数，页面无需再改。
|
||||
const EXIT_DATE_PENDING_TIP = '数据来源：工作平台（WorkbenchDataShared）· 供应商不合作。该库当前未授权给副本账号，待 IT 开通只读权限（或把该表同步进报表副本）后自动显示最晚退厂日期。';
|
||||
const EXIT_DATE_VALUE_TIP = '工作平台「供应商不合作」给出的最晚退厂日期：该供应商放在仓里的货最后必须拉走的日期';
|
||||
|
||||
// 预警等级过滤：主视图改成一排状态页签（全部 / 红色预警 / 关注 / 正常 / 未知），
|
||||
// 因此不再需要下拉选项；导出时仍把 warningLevel 原样传给后端（服务端 filter 同口径）。
|
||||
|
||||
@ -348,11 +367,42 @@ const COLUMNS = [
|
||||
},
|
||||
},
|
||||
{
|
||||
title: '是否出库',
|
||||
dataIndex: 'isOutbound',
|
||||
width: 90,
|
||||
title: '供应商是否合作',
|
||||
dataIndex: 'supplierCoop',
|
||||
width: 136,
|
||||
align: 'center',
|
||||
render: (value) => (value ? <Tag color="green">是</Tag> : <Tag>否</Tag>),
|
||||
sorter: (a, b) => (SUPPLIER_COOP_RANK[a.supplierCoop] || 0) - (SUPPLIER_COOP_RANK[b.supplierCoop] || 0),
|
||||
render: (value) => {
|
||||
const tag = SUPPLIER_COOP_TAGS[value] || SUPPLIER_COOP_TAGS.unknown;
|
||||
return (
|
||||
<Tooltip title={SUPPLIER_COOP_TIPS[value] || SUPPLIER_COOP_TIPS.unknown}>
|
||||
<Tag color={tag.color}>{tag.label}</Tag>
|
||||
</Tooltip>
|
||||
);
|
||||
},
|
||||
},
|
||||
{
|
||||
title: '最晚退厂时间',
|
||||
dataIndex: 'latestExitDateLabel',
|
||||
width: 132,
|
||||
align: 'center',
|
||||
sorter: (a, b) => String(a.latestExitDateLabel || '').localeCompare(String(b.latestExitDateLabel || '')),
|
||||
render: (value, record) => {
|
||||
if (value) {
|
||||
return (
|
||||
<Tooltip title={EXIT_DATE_VALUE_TIP}>
|
||||
<Tag color="orange">{value}</Tag>
|
||||
</Tooltip>
|
||||
);
|
||||
}
|
||||
// 已接入但该供应商没有退厂日期 → 显示破折号；整列未接入 → 显示「待接入」
|
||||
if (record?.latestExitDateAvailable) return <span className="rsw-exit-date-empty">—</span>;
|
||||
return (
|
||||
<Tooltip title={EXIT_DATE_PENDING_TIP}>
|
||||
<Tag color="default">待接入</Tag>
|
||||
</Tooltip>
|
||||
);
|
||||
},
|
||||
},
|
||||
{
|
||||
title: '创建时间来源',
|
||||
@ -405,6 +455,8 @@ const StockWarning = ({ warehouse }) => {
|
||||
rows: [], total: 0, summary: {}, warehouse: '', warehouseName: '', returnSlot: '',
|
||||
warehouseHasReturnStock: true, statDate: '', stockSyncedAt: '', gaps: [], import: null,
|
||||
hasImport: false,
|
||||
// 最晚退厂时间数据源状态（工作平台库未接入时为 available:false + message）
|
||||
exitDateMeta: { available: false, sourceLabel: '', message: '' },
|
||||
});
|
||||
const [loading, setLoading] = useState(false);
|
||||
const [exporting, setExporting] = useState(false);
|
||||
@ -443,6 +495,7 @@ const StockWarning = ({ warehouse }) => {
|
||||
gaps: payload.gaps || [],
|
||||
import: payload.import || null,
|
||||
hasImport: Boolean(payload.hasImport),
|
||||
exitDateMeta: payload.exitDateMeta || { available: false, sourceLabel: '', message: '' },
|
||||
});
|
||||
} catch (requestError) {
|
||||
setError(requestError?.response?.data?.error || requestError?.message || '加载客退数据监控失败');
|
||||
|
||||
@ -648,6 +648,11 @@
|
||||
font-weight: 600;
|
||||
}
|
||||
|
||||
/* 表格「最晚退厂时间」列：数据源已接入但该供应商无日期时的占位符 */
|
||||
.rsw-exit-date-empty {
|
||||
color: var(--sycm-text-quaternary, #bfbfbf);
|
||||
}
|
||||
|
||||
/* ── 退货库存预警：图表区 ─────────────────────────────────── */
|
||||
.rsw-charts {
|
||||
display: grid;
|
||||
|
||||
@ -1,10 +1,11 @@
|
||||
import React, { useCallback, useEffect, useMemo, useState } from 'react';
|
||||
import {
|
||||
Alert, Button, Modal, Select, Space, Spin, Table, Tooltip, Typography,
|
||||
Alert, Button, Modal, Select, Space, Spin, Table, Tooltip, Typography, message,
|
||||
} from 'antd';
|
||||
import { InfoCircleOutlined, ReloadOutlined } from '@ant-design/icons';
|
||||
import { DownloadOutlined, InfoCircleOutlined, ReloadOutlined } from '@ant-design/icons';
|
||||
import { SycmPage } from '../../components/Common';
|
||||
import { getStockSalesAnalysis } from '../../api/stockSalesAnalysis';
|
||||
import { exportStockSalesZeroDetail, getStockSalesAnalysis } from '../../api/stockSalesAnalysis';
|
||||
import { downloadBlobAsFile, readBlobErrorMessage } from '../../utils/tableExport';
|
||||
import {
|
||||
STOCK_SALES_COLUMNS,
|
||||
STOCK_SALES_WINDOW_OPTIONS,
|
||||
@ -43,6 +44,7 @@ const StockSalesAnalysis = () => {
|
||||
const [helpOpen, setHelpOpen] = useState(false);
|
||||
const [days, setDays] = useState(STOCK_SALES_DEFAULT_DAYS);
|
||||
const [warehouse, setWarehouse] = useState(ALL_WAREHOUSES);
|
||||
const [exporting, setExporting] = useState(false);
|
||||
|
||||
const load = useCallback(async (options = {}) => {
|
||||
setLoading(true);
|
||||
@ -71,6 +73,27 @@ const StockSalesAnalysis = () => {
|
||||
const items = useMemo(() => data?.items ?? [], [data]);
|
||||
const grand = data?.grand ?? null;
|
||||
|
||||
/** 导出「销售=0」档 SKU 明细：跟随当前统计天数与仓库筛选 */
|
||||
const handleExportZero = useCallback(async () => {
|
||||
setExporting(true);
|
||||
try {
|
||||
const wmsCoId = warehouse === ALL_WAREHOUSES
|
||||
? undefined
|
||||
: items.find((it) => it.warehouse === warehouse)?.wmsCoId;
|
||||
const params = { days, format: 'xlsx', ...(wmsCoId != null ? { wmsCoId: String(wmsCoId) } : {}) };
|
||||
const blob = await exportStockSalesZeroDetail(params);
|
||||
const dateKey = (data?.latestBizDate || '').replace(/-/g, '') || '未知日期';
|
||||
const scope = warehouse === ALL_WAREHOUSES ? '' : `-${warehouse}`;
|
||||
const fileName = `库存动销分析-销售0明细${scope}-${dateKey}.xlsx`;
|
||||
downloadBlobAsFile(blob, fileName);
|
||||
message.success(`已开始下载：${fileName}`);
|
||||
} catch (exportError) {
|
||||
message.error(await readBlobErrorMessage(exportError, '导出「销售=0」明细失败'));
|
||||
} finally {
|
||||
setExporting(false);
|
||||
}
|
||||
}, [days, warehouse, items, data?.latestBizDate]);
|
||||
|
||||
const warehouseOptions = useMemo(() => ([
|
||||
{ label: '全部仓库', value: ALL_WAREHOUSES },
|
||||
...items.map((it) => ({ label: it.warehouse, value: it.warehouse })),
|
||||
@ -186,6 +209,9 @@ const StockSalesAnalysis = () => {
|
||||
<Tooltip title="跳过 5 分钟缓存，重新扫描订单明细与库存明细重算（约 5~20 秒）">
|
||||
<Button onClick={() => load({ days, force: true })} loading={loading}>强制重算</Button>
|
||||
</Tooltip>
|
||||
<Tooltip title="导出当前筛选下「销售=0」档的 SKU 明细（仓库 / SKU编码 / SKU名称 / 系统仓位 / 库存数量），跟随统计天数与仓库筛选；每个仓位独立一行，库存数量为该仓位的实际数量，仓位数据未覆盖的 SKU 保留一行（仓位留空、库存为总量）">
|
||||
<Button icon={<DownloadOutlined />} onClick={handleExportZero} loading={exporting}>导出销售=0</Button>
|
||||
</Tooltip>
|
||||
</Space>
|
||||
</div>
|
||||
|
||||
|
||||
@ -21,6 +21,7 @@ const ModularStockMonitor = lazy(() => import('../pages/ModularStockMonitor'));
|
||||
const MkhBomData = lazy(() => import('../pages/MkhBomData'));
|
||||
const DailyInventory = lazy(() => import('../pages/DailyInventory'));
|
||||
const StockSalesAnalysis = lazy(() => import('../pages/StockSalesAnalysis'));
|
||||
const PickingBinSalesAnalysis = lazy(() => import('../pages/PickingBinSalesAnalysis'));
|
||||
const ReturnsInventoryMonitor = lazy(() => import('../pages/ReturnsInventoryWarning/Monitor'));
|
||||
|
||||
const lazyLoadingElement = <PageLoadingFallback />;
|
||||
@ -122,6 +123,10 @@ const router = createBrowserRouter([
|
||||
path: 'planning/operation/stock-sales-analysis',
|
||||
element: renderLazyComponent(StockSalesAnalysis),
|
||||
},
|
||||
{
|
||||
path: 'planning/operation/picking-bin-sales-analysis',
|
||||
element: renderLazyComponent(PickingBinSalesAnalysis),
|
||||
},
|
||||
// 客退数据监控（5 张看板共用同一个页面，靠 warehouseKey 区分取哪个仓；
|
||||
// 单一「退货库存预警」视图，原 明细/超期/汇总/差异 四个页签已下线）
|
||||
{
|
||||
|
||||
Loading…
x
Reference in New Issue
Block a user