oms/backend/app/controller/customExpressCode.js
liufumingren 426e6e9e7b fix(express): 自定义快递编码列表因字符集排序规则不一致导致 500
- 列表/详情不再用 Sequelize include 关联 express_companies(该 JOIN 在两边排序规则不同时报
  ER_CANT_AGGREGATE_2COLLATIONS),改为一次 IN 查询后在内存组装,响应仍保留 expressCompany/express_name
- 新增迁移把未显式指定字符集的字典/任务表统一为 utf8mb4 / utf8mb4_unicode_ci
  (custom_express_codes、external_print_express_interfaces、warehouse_express_mappings、
   waybill_fetch_records、order_sync_tasks、shop_database_configs、operation_logs、openapi_request_idempotency)

Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
2026-11-05 17:33:27 +08:00

297 lines
11 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';
/**
* @controller CustomExpressCodeController 自定义快递编码
* 全局字典(不区分主体):自定义编码 + 名称 + 对应的系统快递编码
*/
const { Controller } = require('egg');
const { parsePagination, buildPageResult } = require('../lib/pagination');
/**
* 404 响应(资源不存在)
* @param {string} message 提示信息
* @return {Error} 带 status 的异常
*/
function notFound(message) {
const err = new Error(message);
err.status = 404;
return err;
}
/**
* 400 响应(参数校验失败)
* @param {string} message 提示信息
* @return {Error} 带 status 的异常
*/
function badRequest(message) {
const err = new Error(message);
err.status = 400;
return err;
}
class CustomExpressCodeController extends Controller {
/**
* @summary 自定义快递编码列表
* @description 分页查询自定义快递编码,支持按编码 / 名称 / 对应系统编码 / 状态过滤
* @router get /api/custom-express-codes
* @apikey
* @request query integer page 页码,默认 1
* @request query integer pageSize 每页条数,默认 20
* @request query string keyword 关键词,匹配自定义编码 / 名称
* @request query string express_code 对应的系统快递编码
* @request query integer status 状态:1启用 0禁用
* @response 200 baseResponse success
*/
async index() {
const { ctx } = this;
const Op = this.app.Sequelize.Op;
const { page, pageSize, offset, limit } = parsePagination(ctx.query);
const { keyword, express_code: expressCode, status } = ctx.query;
const where = {};
const text = String(keyword || '').trim();
if (text) {
where[Op.or] = [
{ code: { [Op.like]: `%${text}%` } },
{ name: { [Op.like]: `%${text}%` } },
];
}
if (expressCode) where.express_code = String(expressCode).trim();
if (status !== undefined && status !== '') where.status = Number(status) === 0 ? 0 : 1;
const { count, rows } = await ctx.model.CustomExpressCode.findAndCountAll({
where,
offset,
limit,
order: [[ 'sort', 'ASC' ], [ 'id', 'ASC' ]],
raw: true,
});
ctx.body = {
code: 0,
message: 'success',
data: buildPageResult(await this._attachExpressCompany(rows), count, { page, pageSize }),
};
}
/**
* 给记录补上对应的系统快递(id/code/name)
*
* 不使用 Sequelize include(LEFT JOIN):custom_express_codes 与 express_companies 若字符集/排序规则
* 不一致(历史环境常见),JOIN 会直接抛 ER_CANT_AGGREGATE_2COLLATIONS 导致列表 500;
* 这里改成一次 IN 查询后在内存里组装,既避免排序规则冲突,也少一次 JOIN。
* @param {Array<Object>} rows 记录(已转 plain / raw)
* @return {Promise<Array<Object>>} 带 expressCompany / express_name 的记录
*/
async _attachExpressCompany(rows) {
const { ctx } = this;
const Op = this.app.Sequelize.Op;
const list = rows || [];
const codes = [ ...new Set(list.map(row => row.express_code).filter(Boolean)) ];
const companies = codes.length
? await ctx.model.ExpressCompany.findAll({
where: { code: { [Op.in]: codes } },
attributes: [ 'id', 'code', 'name' ],
raw: true,
})
: [];
const byCode = new Map(companies.map(company => [ company.code, company ]));
return list.map(row => {
const company = byCode.get(row.express_code) || null;
return { ...row, expressCompany: company, express_name: company ? company.name : '' };
});
}
/**
* @summary 快递编码下拉(自定义 + 系统)
* @description 返回启用的自定义快递编码与系统快递公司,供订单选择物流公司、快递接口绑定使用;
* source=custom 表示自定义(express_code 为其对应的系统编码),source=system 表示系统快递
* @router get /api/custom-express-codes/options
* @apikey
* @response 200 baseResponse success
*/
async options() {
const { ctx } = this;
const [ customs, systems ] = await Promise.all([
ctx.model.CustomExpressCode.findAll({
where: { status: 1 },
attributes: [ 'code', 'name', 'express_code', 'sort' ],
order: [[ 'sort', 'ASC' ], [ 'id', 'ASC' ]],
raw: true,
}),
ctx.model.ExpressCompany.findAll({
where: { status: 1 },
attributes: [ 'id', 'code', 'name', 'sort' ],
order: [[ 'sort', 'ASC' ], [ 'id', 'ASC' ]],
raw: true,
}),
]);
const systemNameByCode = new Map(systems.map(row => [ row.code, row.name ]));
const customRows = customs.map(row => ({
code: row.code,
name: row.name,
source: 'custom',
express_code: row.express_code,
express_name: systemNameByCode.get(row.express_code) || row.express_code,
sort: Number(row.sort) || 0,
}));
const systemRows = systems.map(row => ({
code: row.code,
name: row.name,
source: 'system',
express_code: row.code,
express_name: row.name,
sort: Number(row.sort) || 0,
}));
ctx.body = { code: 0, message: 'success', data: [ ...customRows, ...systemRows ] };
}
/**
* @summary 自定义快递编码详情
* @router get /api/custom-express-codes/{id}
* @apikey
* @request path integer *id 主键ID
* @response 200 baseResponse success
*/
async show() {
const { ctx } = this;
const item = await ctx.model.CustomExpressCode.findByPk(ctx.params.id, { raw: true });
if (!item) {
this._fail(ctx, notFound('自定义快递编码不存在'), '自定义快递编码不存在');
return;
}
const [ data ] = await this._attachExpressCompany([ item ]);
ctx.body = { code: 0, message: 'success', data };
}
/**
* @summary 新增自定义快递编码
* @description 自定义编码不能与系统快递编码重复;对应的系统快递编码必须存在
* @router post /api/custom-express-codes
* @apikey
* @request body createCustomExpressCodeRequest *body
* @response 200 baseResponse success
*/
async create() {
const { ctx } = this;
try {
const payload = await this._validate(ctx.request.body || {});
const item = await ctx.model.CustomExpressCode.create(payload);
ctx.body = { code: 0, message: 'success', data: { id: item.id } };
} catch (err) {
this._fail(ctx, err, '新增自定义快递编码失败');
}
}
/**
* @summary 修改自定义快递编码
* @description 未传字段保持原值;编码变更同样校验唯一性与系统编码冲突
* @router put /api/custom-express-codes/{id}
* @apikey
* @request path integer *id 主键ID
* @request body updateCustomExpressCodeRequest *body
* @response 200 baseResponse success
*/
async update() {
const { ctx } = this;
const item = await ctx.model.CustomExpressCode.findByPk(ctx.params.id);
if (!item) {
this._fail(ctx, notFound('自定义快递编码不存在'), '自定义快递编码不存在');
return;
}
try {
const merged = { ...item.get({ plain: true }), ...(ctx.request.body || {}) };
const payload = await this._validate(merged, item.id);
await item.update(payload);
ctx.body = { code: 0, message: 'success', data: { id: item.id } };
} catch (err) {
this._fail(ctx, err, '修改自定义快递编码失败');
}
}
/**
* @summary 删除自定义快递编码
* @description 历史订单已保存编码与名称快照,删除字典不影响已创建的订单
* @router delete /api/custom-express-codes/{id}
* @apikey
* @request path integer *id 主键ID
* @response 200 baseResponse success
*/
async destroy() {
const { ctx } = this;
const item = await ctx.model.CustomExpressCode.findByPk(ctx.params.id);
if (!item) {
this._fail(ctx, notFound('自定义快递编码不存在'), '自定义快递编码不存在');
return;
}
await item.destroy();
ctx.body = { code: 0, message: 'success', data: { id: Number(ctx.params.id) } };
}
/**
* 校验并归一化入参
* @param {Object} body 入参
* @param {number|null} id 修改时的记录ID(用于唯一性排除自身)
* @return {Promise<Object>} 归一化后的字段
*/
async _validate(body = {}, id = null) {
const { ctx } = this;
const Op = this.app.Sequelize.Op;
const code = String(body.code == null ? '' : body.code).trim();
const name = String(body.name == null ? '' : body.name).trim();
const expressCode = String(body.express_code == null ? '' : body.express_code).trim();
if (!code) throw badRequest('code(自定义快递编码)为必填项');
if (code.length > 50) throw badRequest('自定义快递编码不能超过 50 个字符');
if (!name) throw badRequest('name(自定义快递名称)为必填项');
if (name.length > 100) throw badRequest('自定义快递名称不能超过 100 个字符');
if (!expressCode) throw badRequest('express_code(对应的系统快递编码)为必填项');
const systemCompany = await ctx.model.ExpressCompany.findOne({
where: { code: expressCode },
attributes: [ 'id' ],
});
if (!systemCompany) throw badRequest(`系统快递编码 ${expressCode} 不存在,请先在快递公司字典中维护`);
const systemSameCode = await ctx.model.ExpressCompany.findOne({
where: { code },
attributes: [ 'id' ],
});
if (systemSameCode) throw badRequest(`自定义编码 ${code} 与系统快递编码重复,请换一个编码`);
const where = { code };
if (id != null) where.id = { [Op.ne]: id };
const exist = await ctx.model.CustomExpressCode.findOne({ where, attributes: [ 'id' ] });
if (exist) throw badRequest(`自定义编码 ${code} 已存在`);
return {
code,
name,
express_code: expressCode,
sort: Number.isFinite(Number(body.sort)) ? Number(body.sort) : 0,
status: body.status === 0 || body.status === '0' || body.status === false ? 0 : 1,
remark: String(body.remark == null ? '' : body.remark).trim().slice(0, 255) || null,
};
}
/**
* 统一错误响应
* @param {Object} ctx 上下文
* @param {Error} err 异常(可带 status)
* @param {string} fallbackMessage 兜底提示
* @return {void} 无
*/
_fail(ctx, err, fallbackMessage) {
const status = Number(err && err.status) || 500;
ctx.status = status;
ctx.body = { code: status, message: (err && err.message) || fallbackMessage };
if (status >= 500) ctx.logger.error('[customExpressCode] %s', (err && err.message) || fallbackMessage, err);
}
}
module.exports = CustomExpressCodeController;