oms/backend/app/service/exportTask.js
liufumingren dbca49c3fa fix(export): 序列号改为导出后按 chunk 补齐,兼容 MySQL 5.7
生产 MySQL 5.7 下订单导出报 Unknown column 't.id' in 'where clause':
根因是 SN 聚合用了「派生表 + 相关子查询 + 嵌套派生表(JSON_ARRAYAGG)」,
5.7 在派生表合并后会丢失外层别名 t。

修复:
- SQL 不再查 order_sn_codes(去掉 JSON_ARRAYAGG / 相关子查询),避免方言差异与 GROUP_CONCAT 截断
- 新增 EXPORT_TYPES.order.decorateRows:每个 chunk 查完后按订单ID批量取 SN,
  订单行模式填 sn_count/sn_codes,含明细模式填 item_sn_count/item_sn_codes(行号匹配,行号空按商品编码兜底)
- buildResult 支持 selectColumns(导出列可多于 SQL 查询列)
- 单测覆盖 decorateRows 两种模式;已用生产库(5.7.44)只读验证 5 种 SQL 组合均可执行

Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
2026-11-14 01:14:18 +08:00

231 lines
8.3 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 fs = require('fs');
const path = require('path');
const { Service } = require('egg');
const ExcelJS = require('exceljs');
const { buildExportSql, STATUS_LABELS, TYPE_LABELS, formatDate, EXPORT_TYPES } = require('../utils/exportDefs');
/** 任务状态:0待处理 1处理中 2已完成 3失败 */
const STATUS = { PENDING: 0, PROCESSING: 1, COMPLETED: 2, FAILED: 3 };
/** 单批次查询行数(边查边写,控制内存占用并上报进度) */
const CHUNK = 2000;
/**
* 解析任务参数(params 以 JSON 字符串存储)
* @param {string|Object} s 参数
* @return {Object} 解析后的筛选对象
*/
function safeParse(s) {
if (!s) return {};
if (typeof s === 'object') return s;
try { return JSON.parse(s); } catch { return {}; }
}
/**
* 给任务对象补上中文标签(避免前后端枚举漂移)
* @param {Object} item 任务纯对象
* @return {Object} 带 label 的任务
*/
function withLabels(item) {
item.type_label = TYPE_LABELS[item.type] || item.type;
item.status_label = STATUS_LABELS[item.status] != null ? STATUS_LABELS[item.status] : String(item.status);
return item;
}
class ExportTaskService extends Service {
/**
* 提交导出任务:仅构造并落库一条「待处理」记录,立即返回任务ID(实际导出由定时任务处理)
* @param {string} type 导出类型
* @param {Object} filters 已白名单过滤的筛选条件
* @param {number} principalId 主体ID
* @param {number} userId 提交人ID
* @return {Promise<Object>} 任务实例
*/
async submit(type, filters, principalId, userId) {
const access = await this.ctx.service.access.resolve(userId, principalId);
const { sql } = buildExportSql(type, filters, principalId, userId, access);
return this.ctx.model.ExportTask.create({
principal_id: principalId,
type,
status: STATUS.PENDING,
sql,
params: JSON.stringify(filters || {}),
created_by: userId || null,
});
}
/**
* 分页查询当前主体的导出任务
* @param {number} principalId 主体ID
* @param {Object} opts page/pageSize/type/status
* @return {Promise<{list: Array, total: number}>}
*/
async list(principalId, opts) {
const { page, pageSize, type, status } = opts;
const where = { principal_id: principalId };
if (type) where.type = type;
if (status !== undefined && status !== null && status !== '') where.status = Number(status);
const { count, rows } = await this.ctx.model.ExportTask.findAndCountAll({
where,
offset: (Number(page) - 1) * Number(pageSize),
limit: Number(pageSize),
order: [[ 'id', 'DESC' ]],
});
const list = rows.map(r => withLabels(r.get({ plain: true })));
return { list, total: count };
}
/**
* 查询单个任务(带中文标签)
* @param {number} id 任务ID
* @return {Promise<Object|null>}
*/
async progress(id) {
const task = await this.ctx.model.ExportTask.findByPk(id);
if (!task) return null;
return withLabels(task.get({ plain: true }));
}
/**
* 认领并处理一条「待处理」任务(全局单_worker队列,靠行锁保证集群下不重复认领)
* 业务流程:事务内 FOR UPDATE 锁定最旧待处理任务 → 置为处理中 → 提交;随后分块查询并写 XLSX;
* 完成时置已完成;异常时置失败并记录原因。
* @return {Promise<void>}
*/
async processPending() {
const ctx = this.ctx;
const Model = ctx.model.ExportTask;
const transaction = await ctx.model.transaction();
let task = null;
try {
task = await Model.findOne({
where: { status: STATUS.PENDING },
order: [[ 'id', 'ASC' ]],
lock: true,
transaction,
});
if (!task) { await transaction.rollback(); return; }
task.status = STATUS.PROCESSING;
await task.save({ transaction });
await transaction.commit();
} catch (e) {
await transaction.rollback().catch(() => {});
ctx.logger.error('[export-worker] 认领任务失败', e);
return;
}
try {
await this.run(task, ctx);
} catch (e) {
ctx.logger.error('[export-worker] 处理任务失败 id=' + task.id, e);
}
}
/**
* 执行导出:分块查询 + 写 XLSX + 实时更新进度,文件按 年/月/日 分目录
* @param {Object} task 任务实例
* @param {Egg.Context} ctx egg 上下文(含 model / app)
* @return {Promise<void>}
*/
async run(task, ctx) {
const Model = ctx.model.ExportTask;
const QueryTypes = ctx.model.QueryTypes;
const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet(TYPE_LABELS[task.type] || 'export');
try {
const filters = safeParse(task.params);
const { sql, countSql, columns, replacements } = buildExportSql(task.type, filters, task.principal_id, task.created_by);
const [ countRow ] = await ctx.model.query(countSql, { replacements, type: QueryTypes.SELECT });
const total = Number(countRow && countRow.cnt) || 0;
const uploadDir = ctx.app.config.upload.dir;
const now = new Date();
const y = String(now.getFullYear());
const m = String(now.getMonth() + 1).padStart(2, '0');
const d = String(now.getDate()).padStart(2, '0');
const relDir = [ 'exports', y, m, d ].join('/');
fs.mkdirSync(path.join(uploadDir, relDir), { recursive: true });
const fileName = `export_${task.type}_${task.id}_${now.getTime()}.xlsx`;
const relPath = relDir + '/' + fileName;
const absPath = path.join(uploadDir, relPath);
sheet.columns = columns.map(c => ({ header: c.header, key: c.key, width: 18 }));
sheet.getRow(1).font = { bold: true };
sheet.views = [{ state: 'frozen', ySplit: 1 }];
let offset = 0;
let processed = 0;
if (total > 0) {
// 循环分块:offset/CHUNK 为整数,直接内联拼接,无注入风险
let finished = false;
while (!finished) {
const rows = await ctx.model.query(`${sql} LIMIT ${offset}, ${CHUNK}`, {
replacements,
type: QueryTypes.SELECT,
});
// 类型自定义的补充列(如订单的序列号数量/列表):按当前 chunk 批量补齐,避免跨库方言差异
const typeDef = EXPORT_TYPES[task.type];
if (typeDef && typeof typeDef.decorateRows === 'function' && rows.length) {
await typeDef.decorateRows(ctx, rows, { principalId: task.principal_id, filters });
}
for (const row of rows) {
const vals = {};
for (const c of columns) {
let v = c.format ? c.format(row[c.key], row) : row[c.key];
if (v instanceof Date) v = formatDate(v);
vals[c.key] = v;
}
sheet.addRow(vals);
}
processed += rows.length;
offset += CHUNK;
const progress = Math.min(99, Math.floor(processed / total * 100));
await Model.update(
{ processed_count: processed, progress, file_path: relPath, file_name: fileName },
{ where: { id: task.id } }
);
if (rows.length < CHUNK) finished = true;
}
}
await workbook.xlsx.writeFile(absPath);
await Model.update(
{
status: STATUS.COMPLETED,
total_count: total,
processed_count: total,
progress: 100,
file_path: relPath,
file_name: fileName,
},
{ where: { id: task.id } }
);
ctx.logger.info(`[export-worker] 任务完成 id=${task.id} type=${task.type} rows=${total}`);
} catch (e) {
const msg = (e && e.message) ? e.message : String(e);
await Model.update(
{ status: STATUS.FAILED, error_message: msg.slice(0, 500) },
{ where: { id: task.id } }
).catch(() => {});
ctx.logger.error('[export-worker] 任务失败 id=' + task.id, e);
throw e;
}
}
/**
* 解析任务对应的导出文件绝对路径(校验防止目录穿越)
* @param {Object} task 任务实例
* @return {Promise<string|null>} 文件绝对路径或 null
*/
async download(task) {
const rel = task.file_path;
if (!rel || rel.includes('..') || !/^exports\//.test(rel)) return null;
const abs = path.join(this.ctx.app.config.upload.dir, rel);
if (!fs.existsSync(abs)) return null;
return abs;
}
}
module.exports = ExportTaskService;