oms/backend/app/service/shopDatabaseSync.js
liufumingren ba0d03414a feat(order): 电商平台筛选可遍历实际平台,数据库店铺与手工单默认淘宝
- 订单筛选项新增 platforms:系统预设 ∪ 当前主体订单里实际出现过的编码(带数量,
  未收录进预设的标记 preset=false),下拉按用量排序,店管家新增平台类型时不会「筛不到」
- 手工单默认 source_platform_code=TB(淘宝),新建/编辑弹窗新增「电商平台」下拉;
  编辑改平台时按系统预设同步名称;外部打单平台拉取的订单该字段置灰不可改
- 数据库店铺拉单按店铺配置的平台落库(未配置时默认淘宝),待审核订单可见可筛
- 迁移 20261107010000:把「数据库店铺订单(extend_props.source=shop_database)」与
  「手工单(extend_props.labels 含手工单)」里为空的电商平台回填为 TB/淘宝

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

678 lines
27 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');
const { ORDER_STATUS } = require('../lib/orderStatus');
const { resolvePlatform } = require('../lib/platformCode');
const { buildSnapshot } = require('../lib/orderItemExpand');
const { DEFAULT_DB_TYPE, DEFAULT_PORT, DATASETS, CALLBACK_MODE_FIELD, CALLBACK_MODE_ROW, DEFAULT_CALLBACK_SEPARATOR } = require('../lib/shopDatabaseFields');
const {
SAFE_IDENTIFIER,
normalizeParams,
validateParams,
openConnection,
closeQuietly,
} = require('../lib/shopDatabaseClient');
/** 单次拉取的订单上限(避免客户库数据量过大拖垮进程) */
const MAX_ORDERS_PER_PULL = 2000;
/**
* 截断字符串,避免超出表字段长度
* @param {*} value 原始值
* @param {number} max 最大长度
* @return {string} 截断后的字符串
*/
function truncateText(value, max) {
return String(value == null ? '' : value).slice(0, max);
}
/**
* 明细行转列表摘要快照(与订单页 product_info 口径一致)
* @param {Array} lines 明细
* @return {Array|null} 快照
*/
function buildProductInfo(lines) {
const list = Array.isArray(lines) ? lines : [];
if (!list.length) return null;
return list.map(line => ({
item_code: line.sku_code || line.outer_item_id || null,
item_name: line.name || null,
quantity: line.quantity,
price: line.price,
}));
}
/**
* 回传时间格式化(客户库多为 datetime 列,统一写 'YYYY-MM-DD HH:mm:ss')
* @param {Date} date 时间
* @return {string} 本地时间字符串
*/
function formatLocalDateTime(date) {
const d = date instanceof Date ? date : new Date(date);
const pad = n => String(n).padStart(2, '0');
return `${d.getFullYear()}-${pad(d.getMonth() + 1)}-${pad(d.getDate())} `
+ `${pad(d.getHours())}:${pad(d.getMinutes())}:${pad(d.getSeconds())}`;
}
/**
* 数据库店铺(shops.type=3)拉取服务
*
* 直接用配置里的「商品表 / 订单表 / 订单明细表 + 字段映射」读取客户自有库的明文数据:
* 1. 商品:按商品表映射读全量,按「店铺 + 商品编码 + 规格」写入店铺商品(shop_products);
* 2. 订单:按订单表映射读订单、按订单明细表映射读明细(用映射的订单号列关联),
* 写入 orders / order_index / order_status_pending / order_status_items,
* 再交给 orderItemResolve 按店铺商品绑定关系展开,口径与打单平台拉单一致。
*/
class ShopDatabaseSyncService extends Service {
/**
* 取店铺的数据库配置,并校验连接与表映射是否完整
* @param {Object} shop 店铺实例(需含 id / principal_id / subsidiary_id / name)
* @return {Promise<Object>} 配置实例
*/
async requireConfig(shop) {
const { ctx } = this;
const config = await ctx.model.ShopDatabaseConfig.findOne({ where: { shop_id: shop.id } });
if (!config) {
const err = new Error('该数据库店铺尚未配置数据库连接,请先在「配置」中完成设置');
err.status = 400;
throw err;
}
const params = this.paramsOf(config);
const paramsError = validateParams(params);
if (paramsError) {
const err = new Error(`数据库配置不完整:${paramsError}`);
err.status = 400;
throw err;
}
for (const dataset of Object.values(DATASETS)) {
if (!config[dataset.tableColumn]) {
const err = new Error(`数据库配置不完整:未选择${dataset.label}`);
err.status = 400;
throw err;
}
}
return config;
}
/**
* 配置转连接参数
* @param {Object} config 配置实例
* @return {Object} 连接参数
*/
paramsOf(config) {
return normalizeParams({
db_type: config.db_type || DEFAULT_DB_TYPE,
host: config.host,
port: config.port == null ? DEFAULT_PORT : config.port,
database_name: config.database_name,
username: config.username,
password: config.password,
});
}
/**
* 按字段映射拼 SELECT:OMS 字段 → 源列(缺映射的字段不查)
* @param {Object} conn 连接
* @param {Object} table 表名
* @param {Object} mapping 字段映射
* @param {Object} dataset DATASETS 中的定义
* @param {string} [where] 附加 WHERE 语句(已参数化)
* @param {Array} [values] WHERE 参数
* @return {Promise<Array>} 行数据(键为 OMS 字段名)
*/
async selectMapped(conn, table, mapping, dataset, where, values) {
const map = mapping || {};
const cols = dataset.fields
.map(field => field.key)
.filter(key => map[key])
.map(key => `${conn.escapeId(map[key])} AS ${conn.escapeId(key)}`);
if (!cols.length) return [];
const sql = `SELECT ${cols.join(', ')} FROM ${conn.escapeId(table)}${where ? ` WHERE ${where}` : ''}`;
const [ rows ] = await conn.query(sql, values || []);
return rows;
}
/**
* 拉取过滤条件转 WHERE 片段(状态列 + 多个状态值)
* @param {Object} conn 连接
* @param {Object} config 配置实例
* @param {Object} dataset DATASETS 中的定义
* @param {Array<string>} [wheres] 已有条件(会被追加)
* @param {Array} [values] 已有参数
* @return {{wheres: Array<string>, values: Array}} 过滤条件与参数
*/
buildFilter(conn, config, dataset, wheres, values) {
const list = (wheres || []).slice();
const params = (values || []).slice();
if (!dataset.filter) return { wheres: list, values: params };
const column = config[dataset.filter.columnField];
const raw = config[dataset.filter.valuesField];
const filterValues = (Array.isArray(raw) ? raw : [])
.map(value => String(value == null ? '' : value).trim())
.filter(Boolean);
if (!column || !filterValues.length) return { wheres: list, values: params };
list.push(`${conn.escapeId(column)} IN (${filterValues.map(() => '?').join(', ')})`);
params.push(...filterValues);
return { wheres: list, values: params };
}
/**
* 拉取店铺商品:按商品表映射全量读取,写入 / 更新店铺商品
* @param {Object} shop 店铺实例
* @return {Promise<{total: number, created: number, updated: number, filtered: boolean}>} 拉取统计
*/
async pullProducts(shop) {
const { ctx } = this;
const config = await this.requireConfig(shop);
const params = this.paramsOf(config);
let conn = null;
try {
conn = await openConnection(params);
const dataset = DATASETS.product;
const filter = this.buildFilter(conn, config, dataset);
const rows = await this.selectMapped(
conn,
config.product_table,
config.product_fields,
dataset,
filter.wheres.length ? filter.wheres.join(' AND ') : '',
filter.values
);
const existRows = await ctx.model.ShopProduct.findAll({
where: { principal_id: shop.principal_id, shop_id: shop.id },
attributes: [ 'id', 'outer_item_id', 'outer_sku_id' ],
raw: true,
});
const existMap = new Map(existRows.map(row => [
`${row.outer_item_id}||${row.outer_sku_id || ''}`,
Number(row.id),
]));
const now = new Date();
let created = 0;
let updated = 0;
for (const row of rows) {
const itemCode = truncateText(row.item_code, 100);
if (!itemCode) continue;
const spec = row.sku_property == null ? '' : truncateText(row.sku_property, 255);
const key = `${itemCode}||${spec}`;
const fields = {
principal_id: shop.principal_id,
subsidiary_id: shop.subsidiary_id == null ? null : Number(shop.subsidiary_id),
shop_id: shop.id,
outer_item_id: itemCode,
outer_sku_id: spec || null,
title: truncateText(row.item_name, 255) || null,
outer_code: itemCode,
spec: spec || null,
price: row.price == null || row.price === '' ? null : Number(row.price),
status: 1,
raw: row,
synced_at: now,
};
const existId = existMap.get(key);
if (existId) {
await ctx.model.ShopProduct.update(fields, { where: { id: existId } });
updated++;
} else {
await ctx.model.ShopProduct.create(fields);
created++;
}
}
await ctx.service.cache.invalidateShop(shop.id);
return { total: rows.length, created, updated, filtered: filter.wheres.length > 0 };
} finally {
await closeQuietly(conn);
}
}
/**
* 拉取店铺订单:读订单表 + 明细表,按订单号分组后写入订单与待审核状态表
* @param {Object} shop 店铺实例
* @param {Object} options 时间窗
* @param {Date} [options.startTime] 下单时间起
* @param {Date} [options.endTime] 下单时间止
* @return {Promise<Object>} 拉取统计
*/
async pullOrders(shop, options = {}) {
const config = await this.requireConfig(shop);
const params = this.paramsOf(config);
const orderDataset = DATASETS.order;
const itemDataset = DATASETS.order_item;
let conn = null;
try {
conn = await openConnection(params);
// 下单时间可映射时按时间窗过滤,减少读取量;订单状态按配置的状态列过滤(可多个值)
const timeColumn = (config.order_fields || {}).place_order_time;
const wheres = [];
const values = [];
if (timeColumn && options.startTime && options.endTime) {
wheres.push(`${conn.escapeId(timeColumn)} >= ? AND ${conn.escapeId(timeColumn)} <= ?`);
values.push(options.startTime, options.endTime);
}
const filter = this.buildFilter(conn, config, orderDataset, wheres, values);
const orderRows = await this.selectMapped(
conn,
config.order_table,
config.order_fields,
orderDataset,
filter.wheres.length ? filter.wheres.join(' AND ') : '',
filter.values
);
const itemRows = await this.selectMapped(
conn,
config.order_item_table,
config.order_item_fields,
itemDataset
);
// 明细按订单号归组(映射的订单号列做关联键)
const itemsByOrder = new Map();
for (const row of itemRows) {
const key = String(row.order_no == null ? '' : row.order_no).trim();
if (!key) continue;
if (!itemsByOrder.has(key)) itemsByOrder.set(key, []);
itemsByOrder.get(key).push(row);
}
const result = { orders: 0, created: 0, updated: 0, items: 0, skipped: 0 };
const seen = new Set();
for (const orderRow of orderRows) {
const sourceOrderNo = String(orderRow.order_no == null ? '' : orderRow.order_no).trim();
if (!sourceOrderNo || seen.has(sourceOrderNo)) continue;
seen.add(sourceOrderNo);
if (seen.size > MAX_ORDERS_PER_PULL) break;
// 只处理有明细的订单:无明细无法生成可发货的单据
const lines = itemsByOrder.get(sourceOrderNo) || [];
if (!lines.length) {
result.skipped++;
continue;
}
const written = await this.saveOrder(shop, orderRow, lines, sourceOrderNo);
result.orders++;
if (written.created) result.created++;
else result.updated++;
result.items += written.items;
}
return result;
} finally {
await closeQuietly(conn);
}
}
/**
* 命中店铺商品(按商品编码 + 规格,规格为空时按编码命中任意规格)
* @param {Object} shop 店铺实例
* @param {Object} line 明细行
* @return {Promise<Object|null>} 店铺商品
*/
async matchShopProduct(shop, line) {
const { ctx } = this;
const itemCode = String(line.item_code || '').trim();
if (!itemCode) return null;
const spec = String(line.sku_property || '').trim();
const rows = await ctx.model.ShopProduct.findAll({
where: { principal_id: shop.principal_id, shop_id: shop.id, outer_item_id: itemCode },
order: [[ 'id', 'ASC' ]],
});
if (!rows.length) return null;
if (spec) {
const hit = rows.find(row => String(row.outer_sku_id || '') === spec);
if (hit) return hit;
}
return rows.find(row => !row.outer_sku_id) || rows[0];
}
/**
* 写入单个订单(orders + order_index + order_status_pending + order_status_items)
* @param {Object} shop 店铺实例
* @param {Object} orderRow 订单表映射行
* @param {Array} lines 明细行
* @param {string} sourceOrderNo 源订单号
* @return {Promise<{created: boolean, items: number}>} 写入结果
*/
async saveOrder(shop, orderRow, lines, sourceOrderNo) {
const { ctx } = this;
const now = new Date();
const orderNo = truncateText(`DB${shop.id}-${sourceOrderNo}`, 50);
const subsidiaryId = shop.subsidiary_id == null ? null : Number(shop.subsidiary_id);
// 明细先解析出店铺商品与绑定的系统商品/组合商品
const resolved = [];
let itemTotal = 0;
let itemAmount = 0;
for (const line of lines) {
const quantity = Number(line.quantity) || 0;
const price = line.price == null || line.price === '' ? 0 : Number(line.price);
const matched = await this.matchShopProduct(shop, line);
const bound = this.resolveBindings(matched);
itemTotal += quantity;
itemAmount += quantity * price;
resolved.push({ line, quantity, price, matched, bound });
}
const receiver = {
name: truncateText(orderRow.receiver, 50),
mobile: truncateText(orderRow.mobile, 50),
tel: truncateText(orderRow.tel, 50),
province: truncateText(orderRow.province, 100),
city: truncateText(orderRow.city, 100),
area: truncateText(orderRow.area, 100),
town: truncateText(orderRow.town, 100),
address: truncateText(orderRow.detail_address, 500),
zipCode: truncateText(orderRow.zip_code, 20),
};
const placeOrderTime = orderRow.place_order_time ? new Date(orderRow.place_order_time) : now;
const totalAmount = orderRow.total_amount == null || orderRow.total_amount === ''
? Number(itemAmount.toFixed(2))
: Number(orderRow.total_amount);
// 店铺配置了默认发货仓库时,新拉进来的待审核订单直接落到该仓库
const defaultWarehouse = await ctx.service.shopWarehouse.resolveDefault(shop);
const defaultWarehouseFields = defaultWarehouse
? { warehouse_id: defaultWarehouse.id, warehouse_code: defaultWarehouse.code }
: {};
// 电商平台:数据库店铺优先用店铺上配置的平台,未配置时按淘宝(数据库店铺通常对应淘宝店铺)
const platform = resolvePlatform(shop.platform || 'TB');
const transaction = await ctx.model.transaction();
try {
// 1) 订单主表(页面「订单」列表走状态表,这里保留一份与原打单平台拉单一致的结构)
const orderFields = {
principal_id: shop.principal_id,
shop_id: shop.id,
order_no: orderNo,
order_type: 1,
total_amount: totalAmount,
receiver: receiver.name || null,
remark: truncateText(orderRow.buyer_message, 255) || null,
external_order_id: truncateText(sourceOrderNo, 100),
platform_type: 'DATABASE',
external_created_at: placeOrderTime,
synced_at: now,
external_data: orderRow,
};
let order = await ctx.model.Order.findOne({
where: { principal_id: shop.principal_id, order_no: orderNo },
transaction,
});
const created = !order;
if (order) await order.update(orderFields, { transaction });
else order = await ctx.model.Order.create(orderFields, { transaction });
// 2) 已审核流转出待审核的订单只刷新平台侧数据,不覆盖仓内流程
const indexRow = await ctx.model.OrderIndex.findOne({
where: { principal_id: shop.principal_id, parent_order_no: sourceOrderNo },
attributes: [ 'id', 'current_status' ],
raw: true,
transaction,
});
const headFields = {
shop_id: shop.id,
subsidiary_id: subsidiaryId,
shop_nick: truncateText(shop.name, 200),
// 电商平台:数据库店铺没有平台回传,按店铺配置(默认淘宝)落库,订单页可直接筛选
source_platform_code: platform.code,
source_platform_name: platform.name,
product_info: buildProductInfo(resolved.map(row => ({
sku_code: row.line.item_code,
outer_item_id: row.line.item_code,
name: row.line.item_name,
quantity: row.quantity,
price: row.price,
}))),
receiver: receiver.name,
mobile: receiver.mobile,
tel: receiver.tel || receiver.mobile,
province: receiver.province,
city: receiver.city,
area: receiver.area,
town: receiver.town,
detail_address: receiver.address,
address: truncateText([ receiver.province, receiver.city, receiver.area, receiver.town, receiver.address ]
.filter(Boolean).join(''), 255),
zip_code: receiver.zipCode || null,
total_amount: totalAmount,
freight: Number(orderRow.freight) || 0,
item_amount: Number(itemAmount.toFixed(2)),
item_total: Number(itemTotal.toFixed(3)),
item_kind: resolved.length,
buyer_message: truncateText(orderRow.buyer_message, 500),
seller_message: truncateText(orderRow.seller_message, 500),
place_order_time: placeOrderTime,
order_type: 'JYCK',
extend_props: { source: 'shop_database', source_order_no: sourceOrderNo },
};
let orderId;
if (indexRow && indexRow.current_status !== ORDER_STATUS.PENDING) {
await transaction.commit();
return { created, items: resolved.length };
}
if (indexRow) {
orderId = indexRow.id;
const pending = await ctx.model.OrderStatusPending.findByPk(orderId, { transaction });
// 已有发货仓库的订单不覆盖,保留用户手工设置
const keepWarehouse = !!pending && pending.warehouse_id != null && Number(pending.warehouse_id) > 0;
await ctx.model.OrderStatusPending.update(
{ ...headFields, ...(keepWarehouse ? {} : defaultWarehouseFields) },
{ where: { id: orderId }, transaction }
);
} else {
const index = await ctx.model.OrderIndex.create({
order_no: orderNo,
parent_order_no: sourceOrderNo,
principal_id: shop.principal_id,
subsidiary_id: subsidiaryId,
current_table: ORDER_STATUS.PENDING,
current_status: ORDER_STATUS.PENDING,
}, { transaction });
orderId = index.id;
await ctx.model.OrderStatusPending.create({
...headFields,
...defaultWarehouseFields,
id: orderId,
order_no: orderNo,
parent_order_no: sourceOrderNo,
principal_id: shop.principal_id,
subsidiary_id: subsidiaryId,
status: ORDER_STATUS.PENDING,
}, { transaction });
}
// 3) 明细整体替换后按绑定关系重建(组合商品会展开为子商品行)
await ctx.model.OrderStatusItem.destroy({ where: { order_id: orderId }, transaction });
const rows = resolved.map(({ line, quantity, price, matched, bound }, idx) => {
const outerItemId = truncateText(line.item_code, 100);
const snapshotLine = {
outer_item_id: outerItemId,
outer_sku_id: line.sku_property || null,
sku_code: outerItemId,
name: line.item_name || null,
spec: line.sku_property || null,
quantity,
price,
total_amount: Number((quantity * price).toFixed(2)),
raw: line,
};
return {
principal_id: shop.principal_id,
order_id: orderId,
shop_id: shop.id,
order_line_no: String(idx + 1),
item_code: outerItemId,
item_name: truncateText(line.item_name, 255),
plan_qty: quantity,
actual_price: price,
retail_price: price,
ext_code: outerItemId,
inventory_type: 'ZP',
source_order_code: truncateText(sourceOrderNo, 50),
sub_source_order_code: truncateText(`${outerItemId}-${idx + 1}`, 50),
shop_product_id: matched ? matched.id : null,
product_id: bound.product_id,
combo_product_id: bound.combo_product_id,
item_type: bound.item_type,
parse_status: bound.parse_status,
parse_msg: bound.parse_msg,
outer_item_id: outerItemId,
outer_sku_id: line.sku_property || null,
sku_code: outerItemId,
spec: truncateText(line.sku_property, 255),
quantity,
price,
total_amount: Number((quantity * price).toFixed(2)),
raw: line,
platform_snapshot: buildSnapshot(snapshotLine, idx + 1),
};
});
if (rows.length) await ctx.model.OrderStatusItem.bulkCreate(rows, { transaction });
await ctx.service.orderItemResolve.rebuildOrderItems([ orderId ], { transaction });
await transaction.commit();
return { created, items: resolved.length };
} catch (err) {
await transaction.rollback();
throw err;
}
}
/**
* 店铺商品绑定结果:已绑定系统商品/组合商品时直接落到对应字段
* @param {Object|null} matched 店铺商品
* @return {Object} { product_id, combo_product_id, item_type, parse_status, parse_msg }
*/
resolveBindings(matched) {
if (matched && Number(matched.bind_type) === 1 && matched.product_id) {
return { product_id: Number(matched.product_id), combo_product_id: null, item_type: 1, parse_status: 1, parse_msg: null };
}
if (matched && Number(matched.bind_type) === 2 && matched.combo_product_id) {
return { product_id: null, combo_product_id: Number(matched.combo_product_id), item_type: 2, parse_status: 1, parse_msg: null };
}
return {
product_id: null,
combo_product_id: null,
item_type: 1,
parse_status: 2,
parse_msg: matched ? '店铺商品未绑定系统商品' : '未匹配到店铺商品',
};
}
/**
* 回传单号:把发货确认拿到的运单号写回客户自有库
*
* 支持两种方式(见 CALLBACK_MODES):
* 1 写回字段:UPDATE 目标表 SET 运单号字段 = '单号1,单号2' WHERE 定位字段 = 订单号;
* 2 写回行:按订单号先删后插,多包裹每个运单号一行(重复回传不会产生重复行)。
* 未开启回传、缺少表/列配置、订单号或运单号为空时直接返回未写入。
* @param {Object} shop 店铺实例(需含 id / principal_id)
* @param {Object} payload 回传数据
* @param {string} payload.orderNo 源订单号(拉单时写入的 external_order_id)
* @param {Array<string>} payload.waybills 运单号集合
* @param {string} [payload.expressCode] 快递公司编码
* @param {string} [payload.expressName] 快递公司名称
* @param {Date} [payload.syncedAt] 回传时间(默认当前时间)
* @return {Promise<{written: boolean, mode?: number, reason?: string, affected?: number, rows?: number}>} 写入结果
*/
async writeBackWaybills(shop, payload = {}) {
const { ctx } = this;
const orderNo = String(payload.orderNo == null ? '' : payload.orderNo).trim();
const waybills = (payload.waybills || [])
.map(item => String(item == null ? '' : item).trim())
.filter(Boolean);
if (!orderNo || !waybills.length) {
return { written: false, reason: '缺少订单号或运单号' };
}
const config = await ctx.model.ShopDatabaseConfig.findOne({ where: { shop_id: shop.id } });
const mode = config ? Number(config.callback_mode || 0) : 0;
if (!config || !mode) return { written: false, reason: '未开启回传单号' };
if (!config.callback_table || !SAFE_IDENTIFIER.test(config.callback_table)) {
return { written: false, reason: '回传单号:目标表未配置' };
}
const params = this.paramsOf(config);
const paramsError = validateParams(params);
if (paramsError) return { written: false, reason: `数据库配置不完整:${paramsError}` };
const expressCode = truncateText(payload.expressCode, 50);
const expressName = truncateText(payload.expressName, 200);
const syncedAt = payload.syncedAt ? new Date(payload.syncedAt) : new Date();
let conn = null;
try {
conn = await openConnection(params);
const table = conn.escapeId(config.callback_table);
if (mode === CALLBACK_MODE_FIELD) {
const field = config.callback_field;
const keyField = config.callback_key_field;
if (!field || !keyField || !SAFE_IDENTIFIER.test(field) || !SAFE_IDENTIFIER.test(keyField)) {
return { written: false, reason: '回传单号:运单号字段或定位字段未配置' };
}
const separator = config.callback_separator || DEFAULT_CALLBACK_SEPARATOR;
const [ res ] = await conn.query(
`UPDATE ${table} SET ${conn.escapeId(field)} = ? WHERE ${conn.escapeId(keyField)} = ?`,
[ waybills.join(separator), orderNo ]
);
if (!res.affectedRows) {
ctx.logger.warn(`[shop-database] 店铺 ${shop.id} 回传单号未命中订单行:${orderNo}`);
}
return { written: true, mode, affected: res.affectedRows || 0 };
}
if (mode !== CALLBACK_MODE_ROW) return { written: false, reason: `不支持的回传方式:${mode}` };
const mapping = config.callback_fields && typeof config.callback_fields === 'object' ? config.callback_fields : {};
const keyField = mapping.order_no;
if (!keyField || !SAFE_IDENTIFIER.test(keyField)) {
return { written: false, reason: '回传单号:未配置订单号列' };
}
const values = waybill => ({
order_no: orderNo,
waybill_no: waybill,
express_code: expressCode,
express_name: expressName,
synced_at: formatLocalDateTime(syncedAt),
});
const keys = Object.keys(mapping).filter(key => SAFE_IDENTIFIER.test(mapping[key]));
if (!keys.length) return { written: false, reason: '回传单号:未配置任何回传列' };
// 先删后插:同一订单重复回传(含包裹数变化)不会留下重复行
await conn.query(`DELETE FROM ${table} WHERE ${conn.escapeId(keyField)} = ?`, [ orderNo ]);
const columns = keys.map(key => conn.escapeId(mapping[key])).join(', ');
const placeholders = keys.map(() => '?').join(', ');
for (const waybill of waybills) {
const row = values(waybill);
await conn.query(
`INSERT INTO ${table} (${columns}) VALUES (${placeholders})`,
keys.map(key => row[key])
);
}
return { written: true, mode, rows: waybills.length };
} finally {
await closeQuietly(conn);
}
}
/**
* 回写店铺最近拉单时间与结果
* @param {Object} shop 店铺实例
* @param {Date} syncedAt 拉取时间
* @param {string} message 结果说明
* @return {Promise<void>} 无返回值
*/
async markShopSynced(shop, syncedAt, message) {
const { ctx } = this;
await ctx.model.Shop.update(
{ last_order_sync_at: syncedAt, last_order_sync_msg: truncateText(message, 255) },
{ where: { id: shop.id } }
);
}
}
module.exports = ShopDatabaseSyncService;