oms/backend/test/app/lib/exportDefsOrder.test.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

163 lines
6.9 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 { assert } = require('egg-mock/bootstrap');
const { buildExportSql, cleanFilters } = require('../../../app/utils/exportDefs');
/** 集团账号(不受货主范围限制) */
const GROUP_ACCESS = { isGroup: true, ownCompanyIds: [] };
describe('test/app/lib/exportDefsOrder.test.js', () => {
it('默认导出一行一个订单:不含明细列,带序列号数量/列表(SN 由 decorateRows 补值,不进 SQL)', () => {
const { sql, columns } = buildExportSql('order', { order_stage: 'pending' }, 5, 1, GROUP_ACCESS);
const headers = columns.map(c => c.header);
assert(headers.includes('序列号数量'));
assert(headers.includes('序列号列表'));
assert(!headers.includes('行号'));
assert(!headers.includes('商家编码'));
// MySQL 5.7 兼容:不在 SQL 里做 SN 聚合(派生表 + 嵌套派生表的相关子查询会报 Unknown column 't.id')
assert(!sql.includes('JSON_ARRAYAGG'));
assert(!sql.includes('order_sn_codes'));
assert(!sql.includes('JOIN order_status_items'));
// 内层固定补齐筛选/关联要用的列,但不输出到导出表
assert(sql.includes('t.source_platform_code AS source_platform_code'));
});
it('订单含明细:JOIN 明细表,一行一个商品并带明细级序列号列', () => {
const { sql, columns, replacements } = buildExportSql(
'order',
{ order_stage: 'pending', export_mode: 'item' },
5,
1,
GROUP_ACCESS
);
const headers = columns.map(c => c.header);
assert(headers.includes('行号'));
assert(headers.includes('商品编码'));
assert(headers.includes('序列号数量'));
assert(sql.includes('JOIN order_status_items oi ON oi.order_id = o.id'));
assert(sql.includes('oi.order_line_no'));
assert(sql.includes('t.principal_id AS principal_id'), '内层需带 principal_id 供关联明细');
assert(sql.includes('ORDER BY o.id DESC'));
assert(!sql.includes('item_sn_count'), '明细级 SN 由 decorateRows 补值,不进 SQL');
assert.deepEqual(replacements, [ 5 ]);
});
it('导出选中:ids 转成订单ID过滤,与筛选条件同时生效', () => {
const { sql, replacements } = buildExportSql(
'order',
{ order_stage: 'delivery', ids: '11, 22,abc' },
5,
1,
GROUP_ACCESS
);
assert(sql.includes('t.id IN (?,?)'), '非法ID被丢弃,只保留两个有效ID');
assert.deepEqual(replacements, [ 5, 11, 22 ]);
});
it('含明细 + 条件:订单条件留在子查询内,参数顺序为订单→条件', () => {
const { sql, replacements } = buildExportSql(
'order',
{ order_stage: 'delivery', export_mode: 'item', order_no: 'A1', ids: '9' },
7,
1,
GROUP_ACCESS
);
// 条件子查询在内层 `AS t WHERE ...`,外层只做 JOIN;参数按 WHERE 中占位顺序:订单→条件→选中ID
assert(sql.includes(') AS t WHERE'));
assert(sql.includes(') AS o JOIN order_status_items oi'));
assert.deepEqual(replacements, [ 7, '%A1%', 9 ]);
});
it('所在公司权限:内层子查询追加 subsidiary_id 过滤,参数顺序保持一致', () => {
const { sql, replacements } = buildExportSql(
'order',
{ order_stage: 'pending', ids: '3' },
5,
1,
{ isGroup: false, ownCompanyIds: [ 4, 7 ] }
);
assert(sql.includes('t.subsidiary_id IN (?,?)'));
assert.deepEqual(replacements, [ 5, 4, 7, 3 ]);
});
it('订单行 + 平台筛选:内层补齐 source_platform_code,避免外层 WHERE 找不到列', () => {
const { sql } = buildExportSql('order', { platform_codes: 'tb,pdd' }, 5, 1, GROUP_ACCESS);
assert(sql.includes('t.source_platform_code AS source_platform_code'));
assert(sql.includes('t.source_platform_code IN (?,?)'));
});
it('cleanFilters 保留 export_mode / ids,丢弃未声明字段', () => {
const filters = cleanFilters('order', { export_mode: 'item', ids: '1,2', unknown: 'x', order_no: '' });
assert.deepEqual(filters, { export_mode: 'item', ids: '1,2' });
});
it('ids 标记为 hidden,不进入通用导出表单', () => {
const def = require('../../../app/utils/exportDefs').EXPORT_TYPES.order;
const idsField = def.filters.find(f => f.key === 'ids');
assert(idsField && idsField.hidden === true);
});
describe('decorateRows(序列号回填)', () => {
/**
* 造一个只返回固定 SN 行的假 ctx
* @param {Array<Object>} snRows 让 model.query 返回的 SN 行
* @return {Object} 含 calls / model 的假上下文
*/
function fakeCtx(snRows) {
const calls = [];
return {
calls,
model: {
QueryTypes: { SELECT: 'SELECT' },
query: async (sql, options) => { calls.push({ sql, options }); return snRows; },
},
};
}
it('订单行模式:按订单ID汇总 SN 数量与列表', async () => {
const { decorateOrderRows } = require('../../../app/utils/exportDefs');
const ctx = fakeCtx([
{ order_id: 1, order_line_no: '1', item_code: 'A', sn_code: 'SN1' },
{ order_id: 1, order_line_no: '2', item_code: 'B', sn_code: 'SN2' },
{ order_id: 2, order_line_no: null, item_code: 'C', sn_code: 'SN3' },
]);
const rows = [{ id: 1 }, { id: 2 }, { id: 3 }];
await decorateOrderRows(ctx, rows, { principalId: 9, filters: { export_mode: 'order' } });
assert(rows[0].sn_count === 2);
assert(rows[0].sn_codes.join(',') === 'SN1,SN2');
assert(rows[1].sn_count === 1);
assert(rows[2].sn_count === 0, '无 SN 的订单数量为 0');
assert.deepEqual(ctx.calls[0].options.replacements, [ 9, [ 1, 2, 3 ]]);
});
it('含明细模式:按行号匹配,行号为空的回传记录按商品编码兜底', async () => {
const { decorateOrderRows } = require('../../../app/utils/exportDefs');
const ctx = fakeCtx([
{ order_id: 1, order_line_no: '1', item_code: 'A', sn_code: 'SN1' },
{ order_id: 1, order_line_no: null, item_code: 'B', sn_code: 'SN2' },
]);
const rows = [
{ id: 1, order_line_no: '1', item_code: 'A' },
{ id: 1, order_line_no: '2', item_code: 'B' },
{ id: 1, order_line_no: '3', item_code: 'C' },
];
await decorateOrderRows(ctx, rows, { principalId: 9, filters: { export_mode: 'item' } });
assert(rows[0].item_sn_count === 1);
assert(rows[0].item_sn_codes[0] === 'SN1');
assert(rows[1].item_sn_count === 1, '行号为空的 SN 按商品编码挂到对应行');
assert(rows[1].item_sn_codes[0] === 'SN2');
assert(rows[2].item_sn_count === 0);
});
it('空数据 / 无订单ID时不查库', async () => {
const { decorateOrderRows } = require('../../../app/utils/exportDefs');
const ctx = fakeCtx([]);
await decorateOrderRows(ctx, [], { principalId: 9 });
await decorateOrderRows(ctx, [{ id: null }], { principalId: 9 });
assert(ctx.calls.length === 0);
});
});
});