liufumingren 8112430c37 perf(stock.report): 库存报表导出改游标分批+ExcelJS 流式,参考订单导出架构
- 原实现:明细一条大 SQL 全量拉回(货主 1000305 单次 16.9 万行),
  SheetJS aoa_to_sheet 全内存构建 490 万单元格后一次性 XLSX.write 序列化,
  生产实测总耗时 81.7s(其中 Excel 生成约 30~50s、内存峰值约 2GB),
  且进度只有 10/100 两档,中间 80s 零反馈被误判卡死
- 改造(对齐 order_export 流式架构):
  1) 明细按主键 id 游标分批拉取(batchSize=5000,走 PRIMARY 索引,
     生产实测每批约 95ms、末批不满即止无空探测),行序由 created_at,id
     变更为 id 升序(自增序≈插入序)
  2) ExcelJS.stream.xlsx.WorkbookWriter 流式落盘(useStyles/useSharedStrings
     关闭),逐批行级 commit,内存占用从约 2GB 降到批级
  3) 富化映射(商品/货主/条码/入库单/订单/运单)改为按批加载
  4) onProgress 每批上报 current_count/total_count,进度 5→95→100 平滑
- excel.js 新增 uploadExistingFile:流式生成场景复用上传链路
  (归一化文件名/buildUploadKey/uploadFile/uploadSuccessNotify),
  供 WorkbookWriter 落盘后 createReadStream 上传
- systemMethods 层 export/exportStore 接 onProgress 并持久化
  current_count/total_count 字段(向前端兼容订单导出进度组件)
- 验证:storage_report_export.test.js 2/2 PASS(12000 行游标 3 批、
  进度序列 5/95/100、回读 Excel 校验 sheet/表头/行数/单元格值、
  空数据仅表头);生产只读探测 EXPLAIN 走 PRIMARY range、
  首尾页约 95ms;ESLint 4 文件 0 error
2026-10-08 19:33:21 +08:00

562 lines
20 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 MainService = require('../../main');
const STOCK_REPORT_EXPORT_TASK_CACHE_KEY_PREFIX = 'stock_report_export_task_';
const STOCK_REPORT_EXPORT_TASK_CACHE_TTL_SECONDS = 1800;
/**
* 系统方法层:stock.report(库存流水报表接口包装层)。
*/
class StockReportMethodService extends MainService {
constructor(ctx) {
super(ctx);
const _noNeedLogin = '';
const _noNeedRight = 'search,searchStore,export,exportStore,exportTaskProgress';
const _noNeedLog = 'search,searchStore,export,exportStore,exportTaskProgress';
const _noNeedAuth = 'search,searchStore,export,exportStore,exportTaskProgress';
this.systemInit(_noNeedLogin, _noNeedRight, _noNeedLog, _noNeedAuth);
}
/**
* 查询库存流水统计报表。
* @param {Object} data 请求体
* @param {Object} [data.where] 查询条件
* @param {Object} [data.sort] 排序参数
* @param {string} [data.sort.sortField] 排序字段(兼容:sort_field;白名单:stock_id、item_code、item_name、begin_total、inbound_total、outbound_total、end_total)
* @param {string} [data.sort.sortOrder] 排序顺序(兼容:sort_order)
* @param {string|Date} [data.where.start_time] 开始时间
* @param {string|Date} [data.where.end_time] 结束时间
* @param {boolean|string|number} [data.where.by_space=false] 是否按库位返回
* @param {Array<number|string>} [data.where.store_ids] 货主ID列表
* @param {number|number[]} [data.where.stock_id] 商品库存ID(单数)
* @param {number[]} [data.where.stock_ids] 商品库存ID列表(复数)
* @param {string} [data.where.keyword] 商品关键字(按 item_code/item_name/goods_code/barcode 模糊匹配)
* @param {Object} [data.pagination] 分页参数 { page, pageSize }(兼容 page_size);也支持顶层 data.page / data.pageSize / data.page_size(与其他 list 一致,顶层值优先);未传或非法时默认 { page: 1, pageSize: 20 },响应始终返回 data.pagination 分页信息
* @return {Promise<Object>} 统一 success 包装
*/
async search(data = {}) {
const { service } = this;
const { whereValue = {} } = this.prepareWhereContext(data);
const {
start_time: startTime,
end_time: endTime,
by_space: bySpace,
store_ids: storeIds,
stock_id: stockIdRaw,
stock_ids: stockIdsRaw,
keyword,
sort_field: whereSortField,
sortField: whereSortFieldCamel,
sort_order: whereSortOrder,
sortOrder: whereSortOrderCamel,
} = whereValue;
// 兼容 stock_id(单数,支持数组)和 stock_ids(数组)
const stockIds = stockIdsRaw !== undefined
? stockIdsRaw
: (stockIdRaw !== undefined ? (Array.isArray(stockIdRaw) ? stockIdRaw : [ stockIdRaw ]) : null);
const { sort = {} } = data;
const {
sort_field: sortFieldSnake,
sortField: sortFieldCamel,
sort_order: sortOrderSnake,
sortOrder: sortOrderCamel,
} = sort || {};
// 分页参数:与项目其他 list 接口保持一致——
// 支持 data.pagination { page, pageSize }(pageSize 兼容 page_size),
// 同时兼容顶层 data.page(分页)与 data.pageSize / data.page_size(分页大小,顶层值优先)
const { pagination: inputPagination, page: topLevelPage, pageSize: topLevelPageSize, page_size: topLevelPageSizeSnake } = data;
const pagination = {
...(inputPagination && typeof inputPagination === 'object' ? inputPagination : {}),
};
if (topLevelPage !== undefined) {
pagination.page = topLevelPage;
}
const resolvedTopLevelPageSize = topLevelPageSize !== undefined ? topLevelPageSize : topLevelPageSizeSnake;
if (resolvedTopLevelPageSize !== undefined) {
pagination.pageSize = resolvedTopLevelPageSize;
}
const result = await service.systems.storage.report.search(startTime, endTime, bySpace, storeIds, {
sortField: sortFieldCamel || sortFieldSnake || whereSortFieldCamel || whereSortField,
sortOrder: sortOrderCamel || sortOrderSnake || whereSortOrderCamel || whereSortOrder,
}, 'warehouse', stockIds, keyword, pagination);
return this.success('ok', result);
}
/**
* 货主侧库存流水统计报表。
* 以货主维度查询库存流水,只关注商品入库和订单出库,不涉及库位维度。
* @param {Object} data 请求体
* @param {Object} [data.where] 查询条件
* @param {Object} [data.sort] 排序参数
* @param {string} [data.sort.sortField] 排序字段(兼容:sort_field)
* @param {string} [data.sort.sortOrder] 排序顺序(兼容:sort_order)
* @param {string|Date} [data.where.start_time] 开始时间
* @param {string|Date} [data.where.end_time] 结束时间
* @param {Array<number|string>} [data.where.warehouse_ids] 仓库ID列表(可选过滤)
* @param {number|number[]} [data.where.stock_id] 商品库存ID(单数)
* @param {number[]} [data.where.stock_ids] 商品库存ID列表(复数)
* @param {string} [data.where.keyword] 商品关键字(按 item_code/item_name/goods_code/barcode 模糊匹配)
* @return {Promise<Object>} 统一 success 包装
*/
async searchStore(data = {}) {
const { service } = this;
const { whereValue = {} } = this.prepareWhereContext(data);
const {
start_time: startTime,
end_time: endTime,
warehouse_ids: warehouseIdsRaw,
warehouse_id: warehouseIdRaw,
stock_id: stockIdRaw,
stock_ids: stockIdsRaw,
keyword,
sort_field: whereSortField,
sortField: whereSortFieldCamel,
sort_order: whereSortOrder,
sortOrder: whereSortOrderCamel,
} = whereValue;
// 兼容 warehouse_id(单数)和 warehouse_ids(数组)
const warehouseIds = warehouseIdsRaw !== undefined
? warehouseIdsRaw
: (warehouseIdRaw !== undefined ? [ warehouseIdRaw ] : undefined);
// 兼容 stock_id(单数,支持数组)和 stock_ids(数组)
const stockIds = stockIdsRaw !== undefined
? stockIdsRaw
: (stockIdRaw !== undefined ? (Array.isArray(stockIdRaw) ? stockIdRaw : [ stockIdRaw ]) : null);
const { sort = {} } = data;
const {
sort_field: sortFieldSnake,
sortField: sortFieldCamel,
sort_order: sortOrderSnake,
sortOrder: sortOrderCamel,
} = sort || {};
// 货主侧不涉及库位维度(by_space 固定为 false)
const result = await service.systems.storage.report.search(
startTime,
endTime,
false,
warehouseIds, // store 模式下 storeIds 参数实际承载 warehouse_id 过滤
{
sortField: sortFieldCamel || sortFieldSnake || whereSortFieldCamel || whereSortField,
sortOrder: sortOrderCamel || sortOrderSnake || whereSortOrderCamel || whereSortOrder,
},
'store',
stockIds,
keyword
);
return this.success('ok', result);
}
/**
* 货主侧提交库存报表导出任务。
* 与 export 逻辑一致但使用货主维度,只关注商品入库和订单出库,不涉及库位维度。
* 异步执行,立即返回任务ID。
* @param {Object} data 请求体
* @param {Object} [data.where] 查询条件
* @param {Object} [data.sort] 排序参数
* @param {string} [data.sort.sortField] 排序字段(兼容:sort_field)
* @param {string} [data.sort.sortOrder] 排序顺序(兼容:sort_order)
* @param {string|Date} [data.where.start_time] 开始时间
* @param {string|Date} [data.where.end_time] 结束时间
* @param {Array<number|string>} [data.where.warehouse_ids] 仓库ID列表
* @param {number|number[]} [data.where.stock_id] 商品库存ID(单数)
* @param {number[]} [data.where.stock_ids] 商品库存ID列表(复数)
* @param {string} [data.where.keyword] 商品关键字(模糊匹配)
* @param {string} [data.export_mode] 导出模式:'item' 导出含锁定类型的全部变更记录
* @param {string} [data.exportMode] 导出模式(驼峰写法,同 export_mode)
* @return {Promise<Object>} 统一 success 包装(返回任务ID)
*/
async exportStore(data = {}) {
const { service } = this;
const { whereValue = {} } = this.prepareWhereContext(data);
const {
start_time: startTime,
end_time: endTime,
warehouse_ids: warehouseIdsRaw,
warehouse_id: warehouseIdRaw,
stock_id: stockIdRaw,
stock_ids: stockIdsRaw,
keyword,
sort_field: whereSortField,
sortField: whereSortFieldCamel,
sort_order: whereSortOrder,
sortOrder: whereSortOrderCamel,
} = whereValue;
// 兼容 warehouse_id(单数)和 warehouse_ids(数组)
const warehouseIds = warehouseIdsRaw !== undefined
? warehouseIdsRaw
: (warehouseIdRaw !== undefined ? [ warehouseIdRaw ] : undefined);
// 兼容 stock_id(单数,支持数组)和 stock_ids(数组)
const stockIds = stockIdsRaw !== undefined
? stockIdsRaw
: (stockIdRaw !== undefined ? (Array.isArray(stockIdRaw) ? stockIdRaw : [ stockIdRaw ]) : null);
const { sort = {} } = data;
const {
sort_field: sortFieldSnake,
sortField: sortFieldCamel,
sort_order: sortOrderSnake,
sortOrder: sortOrderCamel,
} = sort || {};
const exportMode = String(data.export_mode || data.exportMode || '').trim();
const taskId = this.generateExportTaskId();
const taskPayload = {
task_id: taskId,
status: 'submitted',
progress: 0,
message: '导出任务已提交',
result: null,
error: '',
};
await this.setExportTaskProgress(taskId, taskPayload);
const sortOption = {
sortField: sortFieldCamel || sortFieldSnake || whereSortFieldCamel || whereSortField,
sortOrder: sortOrderCamel || sortOrderSnake || whereSortOrderCamel || whereSortOrder,
};
const onProgress = async progress => {
const safeProgress = this.isPlainObject(progress) ? progress : {};
await this.setExportTaskProgress(taskId, {
task_id: taskId,
status: safeProgress.status || 'running',
progress: safeProgress.progress,
current_count: safeProgress.currentCount ?? safeProgress.current_count ?? 0,
total_count: safeProgress.totalCount ?? safeProgress.total_count ?? 0,
message: safeProgress.message || '导出任务执行中',
result: safeProgress.result || null,
error: '',
});
};
setImmediate(async () => {
try {
await this.setExportTaskProgress(taskId, {
task_id: taskId,
status: 'running',
progress: 5,
message: '导出任务执行中',
result: null,
error: '',
});
const result = await service.systems.storage.report.export(
startTime, endTime, false, warehouseIds,
sortOption, exportMode, 'store', stockIds, keyword, onProgress
);
await this.setExportTaskProgress(taskId, {
task_id: taskId,
status: 'success',
progress: 100,
message: '导出任务完成',
result,
error: '',
});
} catch (error) {
const errorMessage = error && error.message ? error.message : `${error}`;
await this.setExportTaskProgress(taskId, {
task_id: taskId,
status: 'failed',
progress: 100,
message: '导出任务失败',
result: null,
error: errorMessage,
});
}
});
return this.success('ok', {
...taskPayload,
update_time: Date.now(),
});
}
/**
* 提交库存流水统计报表导出任务(仓库侧)。
* @param {Object} data 请求体
* @param {Object} [data.where] 查询条件
* @param {Object} [data.sort] 排序参数
* @param {string} [data.sort.sortField] 排序字段(兼容:sort_field)
* @param {string} [data.sort.sortOrder] 排序顺序(兼容:sort_order)
* @param {string|Date} [data.where.start_time] 开始时间
* @param {string|Date} [data.where.end_time] 结束时间
* @param {boolean|string|number} [data.where.by_space=false] 是否按库位返回
* @param {Array<number|string>} [data.where.store_ids] 货主ID列表
* @param {number|number[]} [data.where.stock_id] 商品库存ID(单数)
* @param {number[]} [data.where.stock_ids] 商品库存ID列表(复数)
* @param {string} [data.where.keyword] 商品关键字(模糊匹配)
* @param {string} [data.export_mode] 导出模式:'item' 导出含锁定类型的全部变更记录,不传则排除锁定类型
* @param {string} [data.exportMode] 导出模式(驼峰写法,同 export_mode)
* @return {Promise<Object>} 统一 success 包装(返回任务ID)
*/
async export(data = {}) {
const { service } = this;
const { whereValue = {} } = this.prepareWhereContext(data);
const {
start_time: startTime,
end_time: endTime,
by_space: bySpace,
store_ids: storeIds,
stock_id: stockIdRaw,
stock_ids: stockIdsRaw,
keyword,
sort_field: whereSortField,
sortField: whereSortFieldCamel,
sort_order: whereSortOrder,
sortOrder: whereSortOrderCamel,
} = whereValue;
// 兼容 stock_id(单数,支持数组)和 stock_ids(数组)
const stockIds = stockIdsRaw !== undefined
? stockIdsRaw
: (stockIdRaw !== undefined ? (Array.isArray(stockIdRaw) ? stockIdRaw : [ stockIdRaw ]) : null);
const { sort = {} } = data;
const {
sort_field: sortFieldSnake,
sortField: sortFieldCamel,
sort_order: sortOrderSnake,
sortOrder: sortOrderCamel,
} = sort || {};
const exportMode = String(data.export_mode || data.exportMode || '').trim();
const taskId = this.generateExportTaskId();
const taskPayload = {
task_id: taskId,
status: 'submitted',
progress: 0,
message: '导出任务已提交',
result: null,
error: '',
};
await this.setExportTaskProgress(taskId, taskPayload);
const sortOption = {
sortField: sortFieldCamel || sortFieldSnake || whereSortFieldCamel || whereSortField,
sortOrder: sortOrderCamel || sortOrderSnake || whereSortOrderCamel || whereSortOrder,
};
const onProgress = async progress => {
const safeProgress = this.isPlainObject(progress) ? progress : {};
await this.setExportTaskProgress(taskId, {
task_id: taskId,
status: safeProgress.status || 'running',
progress: safeProgress.progress,
current_count: safeProgress.currentCount ?? safeProgress.current_count ?? 0,
total_count: safeProgress.totalCount ?? safeProgress.total_count ?? 0,
message: safeProgress.message || '导出任务执行中',
result: safeProgress.result || null,
error: '',
});
};
setImmediate(async () => {
try {
await this.setExportTaskProgress(taskId, {
task_id: taskId,
status: 'running',
progress: 5,
message: '导出任务执行中',
result: null,
error: '',
});
const result = await service.systems.storage.report.export(
startTime, endTime, bySpace, storeIds, sortOption, exportMode,
'warehouse', stockIds, keyword, onProgress
);
await this.setExportTaskProgress(taskId, {
task_id: taskId,
status: 'success',
progress: 100,
message: '导出任务完成',
result,
error: '',
});
} catch (error) {
const errorMessage = error && error.message ? error.message : `${error}`;
await this.setExportTaskProgress(taskId, {
task_id: taskId,
status: 'failed',
progress: 100,
message: '导出任务失败',
result: null,
error: errorMessage,
});
}
});
return this.success('ok', {
...taskPayload,
update_time: Date.now(),
});
}
/**
* 查询库存流水统计报表导出任务进度。
* @param {Object} data 请求体
* @param {string} [data.task_id] 任务ID(下划线写法)
* @param {string} [data.taskId] 任务ID(驼峰写法)
* @return {Promise<Object>} 统一 success 包装
*/
async exportTaskProgress(data = {}) {
const taskId = this.normalizeExportTaskId(data);
if (!taskId) {
return this.success('ok', {
task_id: '',
status: 'not_found',
progress: 0,
message: '任务不存在',
result: null,
error: '',
download_url: '',
update_time: Date.now(),
});
}
const cachedTask = await this.getExportTaskProgress(taskId);
if (!cachedTask) {
return this.success('ok', {
task_id: taskId,
status: 'not_found',
progress: 0,
message: '任务不存在',
result: null,
error: '',
download_url: '',
update_time: Date.now(),
});
}
const safeResult = this.isPlainObject(cachedTask.result) ? cachedTask.result : {};
const downloadUrl = String(
safeResult.url
|| safeResult.cndUrl
|| safeResult.cosUrl
|| safeResult.fileKey
|| ''
);
return this.success('ok', {
task_id: taskId,
...cachedTask,
download_url: downloadUrl,
});
}
/**
* 生成导出任务ID。
* @return {string} 导出任务ID
*/
generateExportTaskId() {
const warehouseId = this.ctx?.user?.store_id || 'unknown';
const timestamp = Date.now();
const randomToken = Math.random().toString(36).slice(2, 10);
return `stock_report_export_${warehouseId}_${timestamp}_${randomToken}`;
}
/**
* 规范导出任务ID。
* @param {Object} data 请求体
* @param {string} [data.task_id] 任务ID(下划线写法)
* @param {string} [data.taskId] 任务ID(驼峰写法)
* @return {string} 归一化后的任务ID
*/
normalizeExportTaskId(data = {}) {
const normalizedData = this.isPlainObject(data) ? data : {};
return String(normalizedData.task_id || normalizedData.taskId || '').trim();
}
/**
* 获取导出任务缓存键。
* @param {string} taskId 任务ID
* @return {string} 缓存键
*/
getExportTaskCacheKey(taskId = '') {
const normalizedTaskId = String(taskId || '').trim();
if (!normalizedTaskId) {
return '';
}
return `${STOCK_REPORT_EXPORT_TASK_CACHE_KEY_PREFIX}${normalizedTaskId}`;
}
/**
* 写入导出任务进度缓存。
* @param {string} taskId 任务ID
* @param {Object} payload 任务状态载荷
* @return {Promise<void>} 无返回值
*/
async setExportTaskProgress(taskId = '', payload = {}) {
const cacheKey = this.getExportTaskCacheKey(taskId);
if (!cacheKey || typeof this.app.setCacheAsync !== 'function') {
return;
}
const safePayload = this.isPlainObject(payload) ? payload : {};
const normalizeCount = value => {
const numericValue = Number(value);
return Number.isFinite(numericValue) && numericValue >= 0 ? Math.round(numericValue) : 0;
};
const progressPayload = {
task_id: String(taskId || '').trim(),
status: String(safePayload.status || 'submitted').trim() || 'submitted',
progress: Number.isFinite(Number(safePayload.progress)) ? Math.min(100, Math.max(0, Math.round(Number(safePayload.progress)))) : 0,
current_count: normalizeCount(safePayload.current_count),
total_count: normalizeCount(safePayload.total_count),
message: String(safePayload.message || ''),
result: safePayload.result ?? null,
error: String(safePayload.error || ''),
update_time: Date.now(),
};
try {
await this.app.setCacheAsync(cacheKey, progressPayload, STOCK_REPORT_EXPORT_TASK_CACHE_TTL_SECONDS);
} catch (error) {
this.ctx.logger.warn('[stock.report.export] 写入导出任务缓存失败: %s', error && error.message ? error.message : error);
}
}
/**
* 读取导出任务进度缓存。
* @param {string} taskId 任务ID
* @return {Promise<Object|null>} 任务缓存
*/
async getExportTaskProgress(taskId = '') {
const cacheKey = this.getExportTaskCacheKey(taskId);
if (!cacheKey || typeof this.app.getCacheAsync !== 'function') {
return null;
}
try {
const cached = await this.app.getCacheAsync(cacheKey);
if (!this.isPlainObject(cached)) {
return null;
}
return cached;
} catch (error) {
this.ctx.logger.warn('[stock.report.export] 读取导出任务缓存失败: %s', error && error.message ? error.message : error);
return null;
}
}
}
module.exports = StockReportMethodService;