WMS_MAIN/app/service/systems/deliveryOrderSummary.js
liufumingren 861c384917 feat(order): 新增配送订单每日汇总 order.deliveryOrder.summary
- 新增 order.deliveryOrder.summary:以自然日为维度汇总订单量、迁入配送表订单量、
  发货量(订单数/商品件数/金额)、拦截数量、库内确定拦截数量与取消数量
- 数据源为订单常用表 + 配送表分表(history/outsourced 不参与),日期分组按
  CONVERT_TZ 换算到服务器本地时区,避免 UTC 存储导致自然日偏移
- 缺省最近 7 个自然日、单次最多 90 天,区间内无数据日期补 0;可选 store_id 过滤
- 同步 methods/system.json(0.3.302 → 0.3.303)、变更记录与 AI_METHOD_ROUTING.md

Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
2026-10-21 17:13:40 +08:00

254 lines
9.2 KiB
JavaScript
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

'use strict';
const Service = require('egg').Service;
const {
resolveSummaryRange,
buildDateKeys,
buildDailyMap,
addCountRows,
addShippedRows,
finalizeDailyRows,
summarizeRows,
} = require('../../lib/delivery_order_summary');
/** 不参与发货/订单量统计的订单状态。 */
const CANCELLED_ORDER_STATUS = 'cancel';
/** 库内确定拦截状态(与 report/queries/interceptOrder 口径一致)。 */
const INTERCEPTED_AT_WAREHOUSE_STATUS = 'intercepted_at_warehouse';
/** 未进入拦截流程的拦截状态。 */
const INTERCEPT_STATUS_NONE = 'none';
/**
* 系统层:配送订单每日汇总。
*
* 数据源为订单常用表与配送表(订单出库后迁移到配送表,迁移时删除源行,两表无重叠,
* 直接相加不会重复计数);归档表 history / 委托外仓表 outsourced 不参与统计。
*
* 每日指标口径(各指标按自身时间字段落入统计日):
* - order_count:当日创建订单数(create_at,排除取消单);
* - dispatch_count:当日迁入配送表的订单数(配送表 dispath_time);
* - shipped_count:当日发货订单数(delivery_time,排除取消单);
* - shipped_items_total:当日发货商品件数(发货订单 items_total 合计);
* - shipped_amount:当日发货订单金额(发货订单 total_amount 合计,两位小数);
* - intercepted_count:当日拦截订单数(intercept_status != 'none' 且 intercept_time 落入当日);
* - intercepted_at_warehouse_count:当日库内确定拦截订单数(配送表 dispath_time 落入当日,
* intercept_status = 'intercepted_at_warehouse');
* - cancelled_count:当日取消订单数(status = 'cancel' 且 updated_at 落入当日,取消操作会刷新 updated_at)。
*/
class DeliveryOrderSummaryService extends Service {
/**
* 查询配送订单每日汇总。
*
* @param {Object} options 查询参数。
* @param {number} options.warehouseId 仓库ID(必填)。
* @param {number|null} [options.storeId] 货主ID(可选,>0 时按货主过滤)。
* @param {*} [options.startDate] 开始日期(yyyy-MM-dd,缺省为结束日往前 6 天)。
* @param {*} [options.endDate] 结束日期(yyyy-MM-dd,缺省为今天)。
* @return {Promise<Object>} { warehouse_id, store_id, start_date, end_date, day_count, list, summary }
*/
async queryDailySummary({ warehouseId, storeId = null, startDate = null, endDate = null } = {}) {
const normalizedWarehouseId = Number(warehouseId) || 0;
if (normalizedWarehouseId <= 0) {
const error = new Error('配送订单汇总缺少仓库上下文');
error.code = 'INVALID_SUMMARY_REQUEST';
throw error;
}
const range = resolveSummaryRange({ startDate, endDate });
const dateKeys = buildDateKeys(range.start, range.end);
const dailyMap = buildDailyMap(dateKeys);
const factory = this.ctx.model && this.ctx.model.OrderDeliveryFactory;
if (!factory) {
throw new Error('订单配送模型工厂不可用');
}
const [ currentModel, dispatchModel ] = await Promise.all([
factory.getCurrentModel(normalizedWarehouseId),
factory.getDispatchModel(normalizedWarehouseId),
]);
const { Op } = this.app.Sequelize;
const normalizedStoreId = Number(storeId) || 0;
const storeScope = normalizedStoreId > 0 ? { store_id: normalizedStoreId } : {};
const notCancelled = { [Op.ne]: CANCELLED_ORDER_STATUS };
const timeWindow = () => ({ [Op.gte]: range.start, [Op.lt]: range.endExclusive });
const [
currentCreatedRows,
dispatchCreatedRows,
currentShippedRows,
dispatchShippedRows,
currentInterceptedRows,
dispatchInterceptedRows,
dispatchConfirmedRows,
dispatchMigratedRows,
currentCancelledRows,
dispatchCancelledRows,
] = await Promise.all([
this.queryDailyCount(currentModel, 'create_at', {
...storeScope,
status: notCancelled,
create_at: timeWindow(),
}),
this.queryDailyCount(dispatchModel, 'create_at', {
...storeScope,
status: notCancelled,
create_at: timeWindow(),
}),
this.queryDailyShipped(currentModel, {
...storeScope,
status: notCancelled,
delivery_time: timeWindow(),
}),
this.queryDailyShipped(dispatchModel, {
...storeScope,
status: notCancelled,
delivery_time: timeWindow(),
}),
this.queryDailyCount(currentModel, 'intercept_time', {
...storeScope,
intercept_status: { [Op.ne]: INTERCEPT_STATUS_NONE },
intercept_time: timeWindow(),
}),
this.queryDailyCount(dispatchModel, 'intercept_time', {
...storeScope,
intercept_status: { [Op.ne]: INTERCEPT_STATUS_NONE },
intercept_time: timeWindow(),
}),
this.queryDailyCount(dispatchModel, 'dispath_time', {
...storeScope,
intercept_status: INTERCEPTED_AT_WAREHOUSE_STATUS,
dispath_time: timeWindow(),
}),
this.queryDailyCount(dispatchModel, 'dispath_time', {
...storeScope,
dispath_time: timeWindow(),
}),
this.queryDailyCount(currentModel, 'updated_at', {
...storeScope,
status: CANCELLED_ORDER_STATUS,
updated_at: timeWindow(),
}),
this.queryDailyCount(dispatchModel, 'updated_at', {
...storeScope,
status: CANCELLED_ORDER_STATUS,
updated_at: timeWindow(),
}),
]);
addCountRows(dailyMap, currentCreatedRows, 'order_count');
addCountRows(dailyMap, dispatchCreatedRows, 'order_count');
addCountRows(dailyMap, dispatchMigratedRows, 'dispatch_count');
addCountRows(dailyMap, dispatchConfirmedRows, 'intercepted_at_warehouse_count');
addShippedRows(dailyMap, currentShippedRows);
addShippedRows(dailyMap, dispatchShippedRows);
addCountRows(dailyMap, currentInterceptedRows, 'intercepted_count');
addCountRows(dailyMap, dispatchInterceptedRows, 'intercepted_count');
addCountRows(dailyMap, currentCancelledRows, 'cancelled_count');
addCountRows(dailyMap, dispatchCancelledRows, 'cancelled_count');
const list = finalizeDailyRows(dateKeys, dailyMap);
return {
warehouse_id: normalizedWarehouseId,
store_id: normalizedStoreId > 0 ? normalizedStoreId : null,
start_date: range.startDate,
end_date: range.endDate,
day_count: range.dayCount,
list,
summary: summarizeRows(list),
};
}
/**
* 按日期分组计数。
*
* 分组日期按「数据库时区 → 服务器本地时区」换算后再取 DATE,
* 与 JS 侧 new Date(y, m, d) 构造的自然日边界保持一致。
*
* @param {Object} model 订单分表模型。
* @param {string} dateField 时间字段。
* @param {Object} where 查询条件。
* @return {Promise<Array<Object>>} 聚合行(date / count)。
*/
async queryDailyCount(model, dateField, where) {
const { fn, col } = this.app.Sequelize;
const dateColumn = this.buildLocalDateColumn(dateField);
return await model.findAll({
attributes: [
[ dateColumn, 'date' ],
[ fn('COUNT', col('id')), 'count' ],
],
where,
group: [ dateColumn ],
raw: true,
});
}
/**
* 按发货日期分组统计发货订单数、商品件数与订单金额。
*
* @param {Object} model 订单分表模型。
* @param {Object} where 查询条件。
* @return {Promise<Array<Object>>} 聚合行(date / count / items_total / amount)。
*/
async queryDailyShipped(model, where) {
const { fn, col } = this.app.Sequelize;
const dateColumn = this.buildLocalDateColumn('delivery_time');
return await model.findAll({
attributes: [
[ dateColumn, 'date' ],
[ fn('COUNT', col('id')), 'count' ],
[ fn('SUM', col('items_total')), 'items_total' ],
[ fn('SUM', col('total_amount')), 'amount' ],
],
where,
group: [ dateColumn ],
raw: true,
});
}
/**
* 构造按服务器本地自然日归一化的日期表达式。
*
* @param {string} dateField 时间字段。
* @return {Object} Sequelize 表达式 DATE(CONVERT_TZ(field, dbTz, localTz))。
*/
buildLocalDateColumn(dateField) {
const { fn, col } = this.app.Sequelize;
return fn(
'DATE',
fn('CONVERT_TZ', col(dateField), this.resolveDbTimezone(), this.resolveLocalTimezone())
);
}
/**
* 数据库连接时区(Sequelize options.timezone,缺省 +00:00)。
*
* @return {string} 形如 +00:00 / +08:00。
*/
resolveDbTimezone() {
const sequelize = this.ctx.model && this.ctx.model.options ? this.ctx.model : null;
return (sequelize && sequelize.options && sequelize.options.timezone) || '+00:00';
}
/**
* 服务器本地时区偏移(形如 +08:00)。
*
* @return {string} 时区偏移。
*/
resolveLocalTimezone() {
const offsetMinutes = -new Date().getTimezoneOffset();
const sign = offsetMinutes < 0 ? '-' : '+';
const absMinutes = Math.abs(offsetMinutes);
const hours = String(Math.floor(absMinutes / 60)).padStart(2, '0');
const minutes = String(absMinutes % 60).padStart(2, '0');
return `${sign}${hours}:${minutes}`;
}
}
module.exports = DeliveryOrderSummaryService;