- desktop/installer:新增 Inno Setup 的 EXE 安装包定义(YuncangTrace.iss)与 build.ps1 的 MSI/EXE 双产物支持 - backend:统计口径排除已删除/未绑定工作台的记录;扫码相关调整 - frontend:首页/工作台排行/本机软件下载等视图微调 Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
907 lines
35 KiB
JavaScript
907 lines
35 KiB
JavaScript
'use strict';
|
||
|
||
const { Service } = require('egg');
|
||
const { Op } = require('sequelize');
|
||
const { VIRTUAL_CHANNEL_PREFIX, isVirtualChannelId } = require('../lib/virtual-channel');
|
||
|
||
/** 单次上报的条数上限(防止一次请求塞太多) */
|
||
const MAX_BATCH = 500;
|
||
/** 字段长度(与 DDL 保持一致) */
|
||
const MAX_CHANNEL_ID = 64;
|
||
const MAX_CHANNEL_NAME = 128;
|
||
const MAX_BARCODE = 255;
|
||
|
||
/** 统计按天聚合用的时区偏移(与 config.sequelize.timezone 的 +08:00 保持一致) */
|
||
const STATS_TZ_OFFSET_MS = 8 * 60 * 60 * 1000;
|
||
/** 趋势图最多回溯天数(防止被传个超大值拖死数据库) */
|
||
const TREND_MAX_DAYS = 30;
|
||
|
||
/**
|
||
* 同一个运单号的**合并窗口**(现场要求:24 小时)。
|
||
*
|
||
* 现场场景:面单在扫码器上停留很久(操作员还没把面单贴到包裹上),
|
||
* 连续扫描模式会反复扫到同一个码。以前是「首次扫描即上报、24 小时内再扫直接丢弃」,
|
||
* 结果回放时间取的是**第一次**扫到的时间,包裹真正离开扫码器的时间反而丢了。
|
||
*
|
||
* 现在的规则:同一运单 24 小时内**只保留一条记录**(跨通道也算),
|
||
* 后面上报的扫描时间如果更新,就**覆盖**这条记录的 `scan_time` 与工作台快照;
|
||
* 只有比库里更旧的上报(乱序/重试)才忽略。超过 24 小时再扫,算一次新的上报。
|
||
*/
|
||
const REPEAT_WINDOW_MS = 24 * 60 * 60 * 1000;
|
||
|
||
/** 去掉首尾空白并把非字符串转成字符串 */
|
||
function text(value) {
|
||
if (value === undefined || value === null) return '';
|
||
return String(value).trim();
|
||
}
|
||
|
||
/**
|
||
* 条码清洗:去首尾空白,再削掉首尾**非字母数字**的垃圾字符。
|
||
*
|
||
* 现场踩过:设备每 ~0.7s 推一帧 `04 D0 00 00 FF 2C`,最后一个字节 0x2C 是 `,`,
|
||
* 会粘在紧接着的条码前面,落库就成了 `,6901028011778`。
|
||
* 条码本体不会以逗号/空格开头结尾,所以首尾非字母数字一律削掉(**中间的不动**,
|
||
* GS1 里 `(01)`、`-`、`/` 这些还可能出现在内部)。
|
||
*/
|
||
function cleanBarcode(value) {
|
||
const raw = text(value);
|
||
if (!raw) return '';
|
||
return raw.replace(/^[^0-9A-Za-z]+/, '').replace(/[^0-9A-Za-z]+$/, '');
|
||
}
|
||
|
||
/**
|
||
* 解析扫描时间,统一截断到秒(去重键的一部分)
|
||
*
|
||
* 支持:`2026-10-16 12:00:00` / `2026-10-16T12:00:00` / `2026/10/16 12:00:00`
|
||
* / 10 位秒级时间戳 / 13 位毫秒级时间戳 / Date
|
||
* 无时区的字符串按**服务器本地时区**解析(与 Sequelize 的 +08:00 一致)。
|
||
*
|
||
* @return {Date|null} 非法返回 null
|
||
*/
|
||
function parseScanTime(value) {
|
||
if (value === undefined || value === null || value === '') return null;
|
||
if (value instanceof Date) return Number.isNaN(value.getTime()) ? null : truncateToSecond(value);
|
||
|
||
const str = text(value);
|
||
if (!str) return null;
|
||
|
||
let date;
|
||
if (/^\d{10}$/.test(str)) {
|
||
date = new Date(Number(str) * 1000); // 秒级时间戳
|
||
} else if (/^\d{13}$/.test(str)) {
|
||
date = new Date(Number(str)); // 毫秒级时间戳
|
||
} else {
|
||
date = new Date(str.replace(/\//g, '-').replace(' ', 'T'));
|
||
}
|
||
|
||
if (!date || Number.isNaN(date.getTime())) return null;
|
||
return truncateToSecond(date);
|
||
}
|
||
|
||
function truncateToSecond(date) {
|
||
const copy = new Date(date.getTime());
|
||
copy.setMilliseconds(0);
|
||
return copy;
|
||
}
|
||
|
||
/** 唯一键冲突(重复上报)错误 */
|
||
function isDuplicateError(err) {
|
||
if (!err) return false;
|
||
if (err.name === 'SequelizeUniqueConstraintError') return true;
|
||
if (err.parent && err.parent.code === 'ER_DUP_ENTRY') return true;
|
||
return err.code === 'ER_DUP_ENTRY';
|
||
}
|
||
|
||
/** 记录去重用的内存键 */
|
||
function dedupKey(row) {
|
||
return [ row.scannerId, row.channelId, row.scanTime.getTime(), row.barcode ].join('\u0000');
|
||
}
|
||
|
||
function toInt(value) {
|
||
const num = Number(value);
|
||
return Number.isFinite(num) ? num : null;
|
||
}
|
||
|
||
/** 趋势统计的天数(默认 7,限定 1 ~ 30) */
|
||
function clampDays(days) {
|
||
return Math.min(Math.max(Number(days) || 7, 1), TREND_MAX_DAYS);
|
||
}
|
||
|
||
/** 某个时刻在 +08:00 下的日期键(YYYY-MM-DD) */
|
||
function dayKey(date) {
|
||
return new Date(date.getTime() + STATS_TZ_OFFSET_MS).toISOString().slice(0, 10);
|
||
}
|
||
|
||
/** 含「今天」在内、连续 span 天的日期键(升序) */
|
||
function dayKeys(endAt, span) {
|
||
const keys = [];
|
||
for (let i = span - 1; i >= 0; i--) {
|
||
keys.push(dayKey(new Date(endAt.getTime() - i * 86400000)));
|
||
}
|
||
return keys;
|
||
}
|
||
|
||
/** 日期键当天 00:00(+08:00)对应的时刻 */
|
||
function dayStart(key) {
|
||
return new Date(`${key}T00:00:00+08:00`);
|
||
}
|
||
|
||
/** 某个时刻在 +08:00 下的小时(0~23) */
|
||
function hourOf(date) {
|
||
return new Date(date.getTime() + STATS_TZ_OFFSET_MS).getUTCHours();
|
||
}
|
||
|
||
class ScanService extends Service {
|
||
get ScanChannel() {
|
||
return this.app.model.ScanChannel;
|
||
}
|
||
|
||
get ScanRecord() {
|
||
return this.app.model.ScanRecord;
|
||
}
|
||
|
||
get LinkScanChannel() {
|
||
return this.app.model.WorkstationScanChannel;
|
||
}
|
||
|
||
/**
|
||
* 中控上报通道(channel_id + channel_name)
|
||
*
|
||
* - 同一中控 + 同一 channel_id 视为同一个通道:存在则更新通道名与最近上报时间;
|
||
* - 曾被软删除的通道会被「复活」(deleted_at 置空),避免唯一键挡住重新启用;
|
||
* - 同一次上报里重复的 channel_id 只处理最后一次;
|
||
* - **`replace: true` = 全量对账**:中控的通道列表存在本地,推上来的就是全部;
|
||
* 本中控下不在列表里的旧通道会被软删除,并解绑工作台绑定(旧客户端不带该字段,行为不变);
|
||
* 此时允许 `channels: []`(表示「通道已全部删除」)。
|
||
*
|
||
* @param {object} scanner 中控(扫描器实例,来自 scanner_auth)
|
||
* @param {Array} channels 形如 [{ channelId, channelName }]
|
||
* @param {object} [options] { replace?: boolean }
|
||
* @return {Promise<{received:number, created:number, updated:number, invalid:number, errors:Array, channels:Array, removed:number, unbound:number}>}
|
||
*/
|
||
async reportChannels(scanner, channels, { replace = false } = {}) {
|
||
const list = Array.isArray(channels) ? channels : [];
|
||
if (!list.length && !replace) {
|
||
this.ctx.throw(422, 'channels 不能为空,body 形如 { channels: [{ channelId, channelName }] }');
|
||
}
|
||
if (list.length > MAX_BATCH) this.ctx.throw(422, `单次最多上报 ${MAX_BATCH} 个通道`);
|
||
|
||
const now = new Date();
|
||
const errors = [];
|
||
const normalized = new Map(); // channelId -> { channelId, channelName }
|
||
|
||
list.forEach((raw, index) => {
|
||
const item = raw && typeof raw === 'object' ? raw : {};
|
||
const channelId = text(item.channelId || item.channel_id);
|
||
const channelName = text(item.channelName || item.channel_name);
|
||
if (!channelId) {
|
||
errors.push({ index, message: 'channelId 不能为空' });
|
||
return;
|
||
}
|
||
if (channelId.length > MAX_CHANNEL_ID) {
|
||
errors.push({ index, message: `channelId 超长(>${MAX_CHANNEL_ID})` });
|
||
return;
|
||
}
|
||
if (channelName.length > MAX_CHANNEL_NAME) {
|
||
errors.push({ index, message: `channelName 超长(>${MAX_CHANNEL_NAME})` });
|
||
return;
|
||
}
|
||
// 旧版桌面端会多报一个「本机中控」占位通道(channel_id=DESKTOP),
|
||
// 新版已不再上报;这里直接忽略,避免老客户端把它重新写回来。
|
||
if (channelId.toUpperCase() === 'DESKTOP') return;
|
||
// 同一次上报里同 channelId 只保留最后一次
|
||
normalized.set(channelId, { channelId, channelName });
|
||
});
|
||
|
||
const rows = [ ...normalized.values() ];
|
||
const ids = rows.map(row => row.channelId);
|
||
|
||
if (!rows.length && !replace) {
|
||
return { received: list.length, created: 0, updated: 0, invalid: errors.length, errors, channels: [], removed: 0, unbound: 0 };
|
||
}
|
||
|
||
const existing = ids.length ? await this.ScanChannel.findAll({
|
||
where: { scannerId: scanner.id, channelId: { [Op.in]: ids } },
|
||
paranoid: false,
|
||
}) : [];
|
||
const existingMap = new Map(existing.map(row => [ row.channelId, row ]));
|
||
|
||
let created = 0;
|
||
let updated = 0;
|
||
const saved = [];
|
||
|
||
for (const row of rows) {
|
||
const hit = existingMap.get(row.channelId);
|
||
if (hit) {
|
||
if (hit.deletedAt) {
|
||
await this.ScanChannel.restore({ where: { id: hit.id } });
|
||
this.ctx.logger.info('[scan] 通道 %s 已重新启用 (scanner=%s)', row.channelId, scanner.sn);
|
||
}
|
||
await this.ScanChannel.update(
|
||
{ channelName: row.channelName, lastReportAt: now },
|
||
{ where: { id: hit.id }, paranoid: false },
|
||
);
|
||
updated++;
|
||
saved.push(Object.assign({ id: hit.id }, row, { lastReportAt: now }));
|
||
} else {
|
||
const instance = await this.ScanChannel.create({
|
||
scannerId: scanner.id,
|
||
subjectId: scanner.subjectId || null,
|
||
channelId: row.channelId,
|
||
channelName: row.channelName,
|
||
lastReportAt: now,
|
||
status: 1,
|
||
});
|
||
created++;
|
||
saved.push(instance.toJSON());
|
||
}
|
||
}
|
||
|
||
let removed = 0;
|
||
let unbound = 0;
|
||
|
||
// 全量对账:中控的通道列表在本地(channels.json),推上来的就是全部 →
|
||
// 本中控下不在列表里的通道 = 被删了,软删通道行并解绑工作台。
|
||
if (replace) {
|
||
const keep = new Set(ids);
|
||
const current = await this.ScanChannel.findAll({
|
||
attributes: [ 'channelId' ],
|
||
where: { scannerId: scanner.id },
|
||
});
|
||
// 虚拟通道 `CAM-`(纯摄像头工作台)由服务端维护,设备端列表里永远不会有 → 必须豁免,否则每次上报都会被删
|
||
const staleIds = current
|
||
.map(row => row.channelId)
|
||
.filter(channelId => !keep.has(channelId) && !isVirtualChannelId(channelId));
|
||
|
||
const linkWhere = { scannerId: scanner.id };
|
||
linkWhere.channelId = ids.length
|
||
? { [Op.and]: [{ [Op.notIn]: ids }, { [Op.notLike]: `${VIRTUAL_CHANNEL_PREFIX}%` }] }
|
||
: { [Op.notLike]: `${VIRTUAL_CHANNEL_PREFIX}%` };
|
||
unbound = await this.LinkScanChannel.destroy({ where: linkWhere });
|
||
|
||
if (staleIds.length) {
|
||
removed = await this.ScanChannel.destroy({
|
||
where: { scannerId: scanner.id, channelId: { [Op.in]: staleIds } },
|
||
});
|
||
this.ctx.logger.info('[scan] 通道全量对账 %s: 删除 %d / 解绑工作台 %d', scanner.sn, removed, unbound);
|
||
}
|
||
}
|
||
|
||
this.ctx.logger.info('[scan] 通道上报 %s: 新增 %d / 更新 %d / 非法 %d', scanner.sn, created, updated, errors.length);
|
||
return {
|
||
received: list.length,
|
||
created,
|
||
updated,
|
||
invalid: errors.length,
|
||
errors,
|
||
channels: saved,
|
||
removed,
|
||
unbound,
|
||
};
|
||
}
|
||
|
||
/**
|
||
* 中控上报扫码结果:条码 + 扫描时间 + 通道 id
|
||
*
|
||
* 去重分两层:
|
||
* 1. 完全相同的上报(同一中控 + 通道 + 时间(秒) + 条码)只入库一次 → 计入 `duplicated`;
|
||
* 2. **同一运单号 24 小时内只保留一条**(跨通道也算):
|
||
* - 本次扫描时间比库里那条新 → **覆盖** `scan_time` / 工作台快照 / 通道 → 计入 `updated`;
|
||
* - 本次扫描时间比库里那条旧(乱序/重试)→ 忽略 → 计入 `suppressed`。
|
||
* 重复上报不报错。
|
||
* 顺带:刷新通道的最近上报时间(未知通道自动补一条,通道名留空)、刷新中控心跳。
|
||
*
|
||
* @param {object} scanner 中控(扫描器实例)
|
||
* @param {Array} records 形如 [{ barcode, scanTime, channelId }]
|
||
* @return {Promise<object>} { received, inserted, updated, duplicated, suppressed, invalid, errors, unknownChannels }
|
||
*/
|
||
async ingest(scanner, records) {
|
||
const list = Array.isArray(records) ? records : [];
|
||
if (!list.length) this.ctx.throw(422, 'records 不能为空');
|
||
if (list.length > MAX_BATCH) this.ctx.throw(422, `单次最多上报 ${MAX_BATCH} 条`);
|
||
|
||
const now = new Date();
|
||
const errors = [];
|
||
const rows = [];
|
||
const seen = new Set();
|
||
|
||
list.forEach((raw, index) => {
|
||
const item = raw && typeof raw === 'object' ? raw : {};
|
||
const barcode = cleanBarcode(item.barcode || item.code);
|
||
const channelId = text(item.channelId || item.channel_id);
|
||
const scanTime = parseScanTime(item.scanTime || item.scan_time || item.time);
|
||
|
||
if (!channelId) {
|
||
errors.push({ index, message: 'channelId 不能为空' });
|
||
return;
|
||
}
|
||
if (channelId.length > MAX_CHANNEL_ID) {
|
||
errors.push({ index, message: `channelId 超长(>${MAX_CHANNEL_ID})` });
|
||
return;
|
||
}
|
||
if (!barcode) {
|
||
errors.push({ index, message: 'barcode 不能为空' });
|
||
return;
|
||
}
|
||
if (barcode.length > MAX_BARCODE) {
|
||
errors.push({ index, message: `barcode 超长(>${MAX_BARCODE})` });
|
||
return;
|
||
}
|
||
if (!scanTime) {
|
||
errors.push({ index, message: 'scanTime 不能为空或格式非法' });
|
||
return;
|
||
}
|
||
|
||
const row = { scannerId: scanner.id, subjectId: scanner.subjectId || null, channelId, barcode, scanTime, reportedAt: now };
|
||
// 同一批里完全相同的上报只留一条
|
||
const key = dedupKey(row);
|
||
if (seen.has(key)) return;
|
||
seen.add(key);
|
||
rows.push(row);
|
||
});
|
||
|
||
const result = {
|
||
received: list.length,
|
||
inserted: 0,
|
||
// 同一运单已有记录,本次扫描时间更新 → 覆盖那条记录
|
||
updated: 0,
|
||
// 同一批内完全相同的上报
|
||
duplicated: list.length - errors.length - rows.length,
|
||
// 比库里已有记录更旧的重复上报(乱序/重试),不覆盖
|
||
suppressed: 0,
|
||
invalid: errors.length,
|
||
errors,
|
||
unknownChannels: [],
|
||
};
|
||
if (!rows.length) return result;
|
||
|
||
let fresh = await this.filterExisting(rows);
|
||
result.duplicated += rows.length - fresh.length;
|
||
|
||
// 入库/覆盖前把「当时的工作台」冻成快照:回放要按当时的录像机/摄像头/布局/录制范围拉流,
|
||
// 工作台后来被改过、删掉了,历史运单照样能查回来。
|
||
if (fresh.length) {
|
||
try {
|
||
const pairs = fresh.map(row => ({ scannerId: row.scannerId, channelId: row.channelId }));
|
||
const snapshots = await this.ctx.service.pack.snapshotMap(pairs);
|
||
|
||
for (const row of fresh) {
|
||
const snapshot = snapshots.get(`${row.scannerId}|${row.channelId}`);
|
||
if (!snapshot) continue;
|
||
row.workstationId = snapshot.workstation.id;
|
||
row.workstationSnapshot = snapshot;
|
||
}
|
||
} catch (err) {
|
||
// 快照失败不能影响扫码入库本身(少个快照顶多查回放时提示"没有工作台信息")
|
||
this.ctx.logger.warn('[scan] 生成工作台快照失败:%s', err.message);
|
||
}
|
||
}
|
||
|
||
// 24 小时合并规则:同一运单只留一条,时间以**最后一次扫描**为准
|
||
// (活跃工作台要在合并**之前**记下来:被合并覆盖的那几条不会再出现在 `fresh` 里)
|
||
const activeWorkstations = fresh.slice();
|
||
if (fresh.length) {
|
||
const merged = await this.mergeExisting(fresh, scanner);
|
||
result.updated += merged.updated;
|
||
result.suppressed += merged.stale;
|
||
fresh = merged.kept;
|
||
}
|
||
|
||
if (fresh.length) {
|
||
try {
|
||
await this.ScanRecord.bulkCreate(fresh);
|
||
result.inserted = fresh.length;
|
||
} catch (err) {
|
||
if (!isDuplicateError(err)) throw err;
|
||
// 并发下正好撞上唯一键:退化成逐条插入,撞上的算重复
|
||
for (const row of fresh) {
|
||
try {
|
||
await this.ScanRecord.create(row);
|
||
result.inserted++;
|
||
} catch (inner) {
|
||
if (!isDuplicateError(inner)) throw inner;
|
||
}
|
||
}
|
||
result.duplicated += fresh.length - result.inserted;
|
||
}
|
||
}
|
||
|
||
const channelIds = [ ...new Set(rows.map(row => row.channelId)) ];
|
||
result.unknownChannels = await this.touchChannels(scanner, channelIds, now);
|
||
// 有数据上报即视为中控在线,刷新心跳
|
||
await this.ctx.service.scanner.heartbeat(scanner, this.ctx.ip);
|
||
// 上报了条码 = 工作台活跃:刷新「最后活跃时间」,工作台的自动下线定时任务按它计时
|
||
await this.touchWorkstations(activeWorkstations, now);
|
||
|
||
this.ctx.logger.info(
|
||
'[scan] 扫码上报 %s: 收到 %d / 入库 %d / 覆盖 %d / 去重 %d / 旧上报 %d / 非法 %d',
|
||
scanner.sn, result.received, result.inserted, result.updated, result.duplicated, result.suppressed, result.invalid,
|
||
);
|
||
if (result.unknownChannels.length) {
|
||
this.ctx.logger.warn('[scan] %s 上报了未登记的通道: %s', scanner.sn, result.unknownChannels.join(', '));
|
||
}
|
||
return result;
|
||
}
|
||
|
||
/**
|
||
* 过滤掉库里已存在的记录(一次查询,按中控 + 时间范围 + 通道)
|
||
* @return {Promise<Array>} 需要新入库的行
|
||
*/
|
||
async filterExisting(rows) {
|
||
const channelIds = [ ...new Set(rows.map(row => row.channelId)) ];
|
||
const times = rows.map(row => row.scanTime.getTime());
|
||
const from = new Date(Math.min(...times));
|
||
const to = new Date(Math.max(...times));
|
||
|
||
const existing = await this.ScanRecord.findAll({
|
||
attributes: [ 'scannerId', 'channelId', 'barcode', 'scanTime' ],
|
||
where: {
|
||
scannerId: rows[0].scannerId,
|
||
channelId: { [Op.in]: channelIds },
|
||
scanTime: { [Op.between]: [ from, to ] },
|
||
},
|
||
raw: true,
|
||
});
|
||
|
||
const existingKeys = new Set(existing.map(row => dedupKey(Object.assign({}, row, {
|
||
scanTime: new Date(row.scanTime),
|
||
}))));
|
||
|
||
return rows.filter(row => !existingKeys.has(dedupKey(row)));
|
||
}
|
||
|
||
/**
|
||
* 同一运单 24 小时内只保留一条记录,时间以**最后一次扫描**为准。
|
||
*
|
||
* 规则:
|
||
* - 同一运单号(**主体内**,跨中控/通道都算)24 小时内只留一条;
|
||
* - 库里已有记录、且本次扫描时间更新 → **覆盖** `scan_time` / 通道 / 工作台快照 → 计入 `updated`;
|
||
* - 本次扫描时间比库里那条旧(乱序上报、重试)→ 忽略,避免把时间改回过去 → 计入 `stale`;
|
||
* - 同一批里同一运单出现多次 → 只留**最晚**的一条,其余忽略;
|
||
* - 距上一条超过 24 小时 → 算新的上报,正常入库。
|
||
*
|
||
* @param {Array} rows 已通过精确去重、且已带好工作台快照的待入库行
|
||
* @param {object} scanner 当前中控
|
||
* @return {Promise<{ kept: Array, updated: number, stale: number }>}
|
||
*/
|
||
async mergeExisting(rows, scanner) {
|
||
if (!rows.length) return { kept: rows, updated: 0, stale: 0 };
|
||
|
||
const barcodes = [ ...new Set(rows.map(row => row.barcode)) ];
|
||
const times = rows.map(row => row.scanTime.getTime());
|
||
const from = new Date(Math.min(...times) - REPEAT_WINDOW_MS);
|
||
const to = new Date(Math.max(...times) + REPEAT_WINDOW_MS);
|
||
|
||
// 主体内按运单号判断;老数据没有 subject_id 时退回「同一中控」
|
||
const scope = scanner.subjectId
|
||
? { subjectId: Number(scanner.subjectId) }
|
||
: { scannerId: Number(scanner.id) };
|
||
|
||
const existing = await this.ScanRecord.findAll({
|
||
attributes: [ 'id', 'barcode', 'scanTime' ],
|
||
where: Object.assign({}, scope, {
|
||
barcode: { [Op.in]: barcodes },
|
||
scanTime: { [Op.between]: [ from, to ] },
|
||
}),
|
||
raw: true,
|
||
});
|
||
|
||
// barcode -> 库里最近的一条 { id, at }
|
||
const latest = new Map();
|
||
for (const row of existing) {
|
||
const at = new Date(row.scanTime).getTime();
|
||
const current = latest.get(row.barcode);
|
||
if (!current || at > current.at) latest.set(row.barcode, { id: Number(row.id), at });
|
||
}
|
||
|
||
// 同一批里同一运单只保留最晚的一条
|
||
const groups = new Map();
|
||
for (const row of rows) {
|
||
if (!groups.has(row.barcode)) groups.set(row.barcode, []);
|
||
groups.get(row.barcode).push(row);
|
||
}
|
||
|
||
const kept = [];
|
||
let updated = 0;
|
||
let stale = 0;
|
||
|
||
for (const [ barcode, list ] of groups) {
|
||
list.sort((a, b) => a.scanTime.getTime() - b.scanTime.getTime());
|
||
const last = list[list.length - 1];
|
||
const hit = latest.get(barcode);
|
||
|
||
if (!hit) {
|
||
// 库里没有:只入库最晚的一条,同批更早的扫描被它覆盖
|
||
kept.push(last);
|
||
stale += list.length - 1;
|
||
continue;
|
||
}
|
||
|
||
if (last.scanTime.getTime() <= hit.at) {
|
||
// 比库里已有的还旧:不回退扫描时间
|
||
stale += list.length;
|
||
continue;
|
||
}
|
||
|
||
const fields = {
|
||
scannerId: last.scannerId,
|
||
channelId: last.channelId,
|
||
scanTime: last.scanTime,
|
||
reportedAt: last.reportedAt,
|
||
};
|
||
// 快照/主体取得到才覆盖,取不到就保留库里原来的(别因为临时解绑把历史回放弄丢)
|
||
if (last.subjectId) fields.subjectId = last.subjectId;
|
||
if (last.workstationId) fields.workstationId = last.workstationId;
|
||
if (last.workstationSnapshot) fields.workstationSnapshot = last.workstationSnapshot;
|
||
|
||
try {
|
||
await this.ScanRecord.update(fields, { where: { id: hit.id } });
|
||
hit.at = last.scanTime.getTime();
|
||
updated++;
|
||
stale += list.length - 1;
|
||
} catch (err) {
|
||
if (!isDuplicateError(err)) throw err;
|
||
// 并发的另一条上报先把唯一键占住了:以已存在的那条为准
|
||
stale += list.length;
|
||
}
|
||
}
|
||
|
||
if (updated) {
|
||
this.ctx.logger.info('[scan] %s 有 %d 条扫码把已有记录覆盖为最新扫描时间', scanner.sn, updated);
|
||
}
|
||
return { kept, updated, stale };
|
||
}
|
||
|
||
/**
|
||
* 刷新通道的最近上报时间;未登记的通道自动补一条(通道名留空)
|
||
* @return {Promise<Array<string>>} 自动补建的通道 id
|
||
*/
|
||
async touchChannels(scanner, channelIds, at = new Date()) {
|
||
if (!channelIds.length) return [];
|
||
|
||
const known = await this.ScanChannel.findAll({
|
||
attributes: [ 'channelId' ],
|
||
where: { scannerId: scanner.id, channelId: { [Op.in]: channelIds } },
|
||
paranoid: false,
|
||
raw: true,
|
||
});
|
||
const knownIds = new Set(known.map(row => row.channelId));
|
||
const missing = channelIds.filter(id => !knownIds.has(id));
|
||
|
||
if (missing.length) {
|
||
await this.ScanChannel.bulkCreate(missing.map(channelId => ({
|
||
scannerId: scanner.id,
|
||
channelId,
|
||
channelName: '',
|
||
lastReportAt: at,
|
||
status: 1,
|
||
})));
|
||
}
|
||
|
||
await this.ScanChannel.update(
|
||
{ lastReportAt: at },
|
||
{ where: { scannerId: scanner.id, channelId: { [Op.in]: channelIds } }, paranoid: false },
|
||
);
|
||
|
||
return missing;
|
||
}
|
||
|
||
/**
|
||
* 刷新相关工作台的「最后活跃时间」
|
||
*
|
||
* 工作台的自动下线定时任务(`app/schedule/workstation_idle_offline.js`)按这个字段判断 inactivity;
|
||
* 只有**入库成功**(含覆盖)的记录才带 `workstationId`,所以这里直接取这批。
|
||
*
|
||
* 失败不影响扫码入库本身(顶多晚一点自动下线)。
|
||
*/
|
||
async touchWorkstations(rows, at = new Date()) {
|
||
const ids = [ ...new Set((rows || []).map(row => Number(row.workstationId)).filter(id => Number.isInteger(id) && id > 0)) ];
|
||
if (!ids.length) return 0;
|
||
|
||
try {
|
||
const [ affected ] = await this.app.model.Workstation.update(
|
||
{ lastActiveAt: at },
|
||
{ where: { id: { [Op.in]: ids } } },
|
||
);
|
||
return Number(affected) || 0;
|
||
} catch (err) {
|
||
this.ctx.logger.warn('[scan] 刷新工作台最后活跃时间失败:%s', err.message);
|
||
return 0;
|
||
}
|
||
}
|
||
|
||
/** 通道列表(分页;可按中控 / 关键字过滤),带所属中控 */
|
||
async listChannels({ page = 1, pageSize = 20, scannerId, scannerIds, subjectId, keyword, status } = {}) {
|
||
const size = Math.min(Number(pageSize) || 20, 100);
|
||
const current = Math.max(Number(page) || 1, 1);
|
||
|
||
const where = {};
|
||
if (scannerId) where.scannerId = Number(scannerId);
|
||
if (scannerIds) where.scannerId = { [Op.in]: scannerIds.map(Number) };
|
||
if (subjectId) where.subjectId = Number(subjectId);
|
||
if (status !== undefined && status !== '' && status !== null) where.status = Number(status);
|
||
if (keyword) {
|
||
where[Op.or] = [
|
||
{ channelId: { [Op.like]: `%${keyword}%` } },
|
||
{ channelName: { [Op.like]: `%${keyword}%` } },
|
||
];
|
||
}
|
||
|
||
const { rows, count } = await this.ScanChannel.findAndCountAll({
|
||
where,
|
||
include: [{ model: this.app.model.Scanner, as: 'scanner', required: false, attributes: [ 'id', 'sn', 'name' ] }],
|
||
order: [[ 'scannerId', 'ASC' ], [ 'channelId', 'ASC' ]],
|
||
limit: size,
|
||
offset: (current - 1) * size,
|
||
});
|
||
|
||
return {
|
||
list: rows.map(row => row.toJSON()),
|
||
total: count,
|
||
page: current,
|
||
pageSize: size,
|
||
};
|
||
}
|
||
|
||
/**
|
||
* 首页统计口径:只算绑定到**未删除工作台**的扫码记录
|
||
*
|
||
* 未绑定工作台的扫码(通道没挂到任何工作台上)和挂到已删除工作台上的历史记录
|
||
* 都不进首页统计(KPI / 趋势图 / 工作台榜 / 最近一次扫码)。
|
||
*
|
||
* @param {number} subjectId 当前主体
|
||
* @return {Promise<number[]>} 该主体下未删除工作台的 id
|
||
*/
|
||
async boundWorkstationIds(subjectId) {
|
||
const rows = await this.app.model.Workstation.findAll({
|
||
where: { subjectId: Number(subjectId) },
|
||
attributes: [ 'id' ],
|
||
});
|
||
return rows
|
||
.map(row => Number(row.id))
|
||
.filter(id => Number.isInteger(id) && id > 0);
|
||
}
|
||
|
||
/**
|
||
* 扫码记录列表(分页 + 通道 / 条码 / 时间范围过滤),带所属中控
|
||
* @param {object} params { page, pageSize, scannerId 单个中控, scannerIds 多个中控(用户端按「我的设备」过滤用),
|
||
* channelId, barcode, startTime, endTime, workstationIds 只取这些工作台的记录(首页统计用) }
|
||
*/
|
||
async listRecords({ page = 1, pageSize = 20, scannerId, scannerIds, subjectId, channelId, barcode, startTime, endTime, workstationIds } = {}) {
|
||
const size = Math.min(Math.max(Number(pageSize) || 20, 1), 200);
|
||
const current = Math.max(Number(page) || 1, 1);
|
||
|
||
const where = {};
|
||
if (scannerId) where.scannerId = Number(scannerId);
|
||
if (scannerIds) where.scannerId = { [Op.in]: scannerIds.map(Number) };
|
||
if (subjectId) where.subjectId = Number(subjectId);
|
||
if (channelId) where.channelId = channelId;
|
||
if (barcode) where.barcode = { [Op.like]: `%${barcode}%` };
|
||
if (workstationIds) where.workstationId = { [Op.in]: workstationIds.map(Number) };
|
||
|
||
const from = parseScanTime(startTime);
|
||
const to = parseScanTime(endTime);
|
||
if (from || to) {
|
||
where.scanTime = {};
|
||
if (from) where.scanTime[Op.gte] = from;
|
||
if (to) where.scanTime[Op.lte] = to;
|
||
}
|
||
|
||
const { rows, count } = await this.ScanRecord.findAndCountAll({
|
||
where,
|
||
include: [{ model: this.app.model.Scanner, as: 'scanner', required: false, attributes: [ 'id', 'sn', 'name' ] }],
|
||
order: [[ 'scanTime', 'DESC' ], [ 'id', 'DESC' ]],
|
||
limit: size,
|
||
offset: (current - 1) * size,
|
||
});
|
||
|
||
return {
|
||
list: rows.map(row => row.toJSON()),
|
||
total: count,
|
||
page: current,
|
||
pageSize: size,
|
||
};
|
||
}
|
||
|
||
/**
|
||
* 通道数(首页统计用)
|
||
* @param {object} where 普通条件;或 `{ scannerIds: [1,2] }` 限定这几个中控(用户端统计用)
|
||
*/
|
||
async countChannels({ scannerIds, subjectId, ...where } = {}) {
|
||
if (scannerIds) where.scannerId = { [Op.in]: scannerIds.map(Number) };
|
||
if (subjectId) where.subjectId = Number(subjectId);
|
||
return this.ScanChannel.count({ where });
|
||
}
|
||
|
||
/** 扫码记录数(首页统计用;`{ scannerIds }` 限定中控,同 countChannels) */
|
||
async countRecords({ scannerIds, subjectId, workstationIds, ...where } = {}) {
|
||
if (scannerIds) where.scannerId = { [Op.in]: scannerIds.map(Number) };
|
||
if (subjectId) {
|
||
where.subjectId = Number(subjectId);
|
||
where.workstationId = { [Op.in]: workstationIds || await this.boundWorkstationIds(subjectId) };
|
||
}
|
||
return this.ScanRecord.count({ where });
|
||
}
|
||
|
||
/**
|
||
* 按天统计扫码数(首页「近日扫描数量」趋势图)
|
||
*
|
||
* 日期口径按 +08:00 切(与 Sequelize 的 timezone 一致),没有记录的日期补 0,
|
||
* 保证前端拿到的是**连续**的 N 天,直接画图。
|
||
*
|
||
* @param {object} params { subjectId, days = 7, endAt = new Date(), workstationIds }
|
||
* @return {Promise<{ list: Array<{ date: string, count: number }>, total: number }>}
|
||
*/
|
||
async dailyCounts({ subjectId, days = 7, endAt = new Date(), workstationIds } = {}) {
|
||
const span = clampDays(days);
|
||
const keys = dayKeys(endAt, span);
|
||
const start = dayStart(keys[0]);
|
||
// 上界取「明天 0 点」,把未来时间的异常记录排除在区间外
|
||
const end = new Date(start.getTime() + span * 86400000);
|
||
const where = {
|
||
subjectId: Number(subjectId),
|
||
workstationId: { [Op.in]: workstationIds || await this.boundWorkstationIds(subjectId) },
|
||
scanTime: { [Op.gte]: start, [Op.lt]: end },
|
||
};
|
||
|
||
const sequelize = this.ScanRecord.sequelize || this.app.Sequelize;
|
||
const counts = new Map();
|
||
|
||
if (sequelize && typeof sequelize.fn === 'function') {
|
||
// ⚠️ 列名不要写成 `scan_records.xxx`:Sequelize 会给表起别名(scanRecord),
|
||
// 用表名限定会被 MySQL 当成「未知列」直接报错(首页统计 500)。单表查询用未限定列名即可。
|
||
const dayExpr = sequelize.fn('DATE_FORMAT', sequelize.col('scan_time'), '%Y-%m-%d');
|
||
const rows = await this.ScanRecord.findAll({
|
||
attributes: [[ dayExpr, 'day' ], [ sequelize.fn('COUNT', sequelize.col('id')), 'count' ]],
|
||
where,
|
||
group: [ dayExpr ],
|
||
raw: true,
|
||
});
|
||
for (const row of rows) counts.set(String(row.day), Number(row.count) || 0);
|
||
} else {
|
||
// 单测环境没有 sequelize(插件是关掉的):退化成内存聚合
|
||
const rows = await this.ScanRecord.findAll({ attributes: [ 'scanTime' ], where, raw: true });
|
||
for (const row of rows) {
|
||
const at = new Date(row.scanTime || row.scan_time);
|
||
if (Number.isNaN(at.getTime())) continue;
|
||
const key = dayKey(at);
|
||
counts.set(key, (counts.get(key) || 0) + 1);
|
||
}
|
||
}
|
||
|
||
const list = keys.map(date => ({ date, count: counts.get(date) || 0 }));
|
||
return { list, total: list.reduce((sum, item) => sum + item.count, 0) };
|
||
}
|
||
|
||
/**
|
||
* 按小时统计某一天的扫码数(首页「今日 / 昨日」维度)
|
||
*
|
||
* 日期口径同上(+08:00 切日),固定返回 0~23 点共 24 个点(没有记录的小时补 0),
|
||
* 前端直接画成时段分布图。
|
||
*
|
||
* @param {object} params { subjectId, day = new Date()(任意落在目标那天的时刻), workstationIds }
|
||
* @return {Promise<{ date: string, list: Array<{ date: string, label: string, count: number }>, total: number }>}
|
||
*/
|
||
async hourlyCounts({ subjectId, day = new Date(), workstationIds } = {}) {
|
||
const key = dayKey(day);
|
||
const start = dayStart(key);
|
||
const end = new Date(start.getTime() + 86400000);
|
||
const where = {
|
||
subjectId: Number(subjectId),
|
||
workstationId: { [Op.in]: workstationIds || await this.boundWorkstationIds(subjectId) },
|
||
scanTime: { [Op.gte]: start, [Op.lt]: end },
|
||
};
|
||
|
||
const sequelize = this.ScanRecord.sequelize || this.app.Sequelize;
|
||
const counts = new Map();
|
||
|
||
if (sequelize && typeof sequelize.fn === 'function') {
|
||
const hourExpr = sequelize.fn('HOUR', sequelize.col('scan_time'));
|
||
const rows = await this.ScanRecord.findAll({
|
||
attributes: [[ hourExpr, 'hour' ], [ sequelize.fn('COUNT', sequelize.col('id')), 'count' ]],
|
||
where,
|
||
group: [ hourExpr ],
|
||
raw: true,
|
||
});
|
||
for (const row of rows) counts.set(Number(row.hour), Number(row.count) || 0);
|
||
} else {
|
||
const rows = await this.ScanRecord.findAll({ attributes: [ 'scanTime' ], where, raw: true });
|
||
for (const row of rows) {
|
||
const at = new Date(row.scanTime || row.scan_time);
|
||
if (Number.isNaN(at.getTime())) continue;
|
||
const hour = hourOf(at);
|
||
counts.set(hour, (counts.get(hour) || 0) + 1);
|
||
}
|
||
}
|
||
|
||
const list = [];
|
||
for (let hour = 0; hour < 24; hour++) {
|
||
const label = `${String(hour).padStart(2, '0')}:00`;
|
||
list.push({ date: `${key} ${label}`, label, count: counts.get(hour) || 0 });
|
||
}
|
||
return { date: key, list, total: list.reduce((sum, item) => sum + item.count, 0) };
|
||
}
|
||
|
||
/**
|
||
* 按工作台统计扫码(打包)数量(首页「每个工作台打包数量」图)
|
||
*
|
||
* 统计口径:区间内扫码记录上冻结的 `workstation_id`。
|
||
* 只统计**未删除**的工作台:未绑定工作台的记录、挂在已删除工作台上的历史记录都不计入,
|
||
* 已删除的工作台也不出现在榜单里。
|
||
*
|
||
* @param {object} params { subjectId, days = 7, endAt = new Date() }
|
||
* @return {Promise<{ list: Array<object> }>}
|
||
*/
|
||
async workstationCounts({ subjectId, days = 7, endAt = new Date() } = {}) {
|
||
const span = clampDays(days);
|
||
const keys = dayKeys(endAt, span);
|
||
const start = dayStart(keys[0]);
|
||
const end = new Date(start.getTime() + span * 86400000);
|
||
|
||
const stations = await this.app.model.Workstation.findAll({
|
||
where: { subjectId: Number(subjectId) },
|
||
attributes: [ 'id', 'code', 'name', 'wsType', 'status' ],
|
||
order: [[ 'id', 'ASC' ]],
|
||
});
|
||
if (!stations.length) return { list: [] };
|
||
|
||
const where = {
|
||
subjectId: Number(subjectId),
|
||
workstationId: { [Op.in]: stations.map(station => Number(station.id)) },
|
||
scanTime: { [Op.gte]: start, [Op.lt]: end },
|
||
};
|
||
|
||
const sequelize = this.ScanRecord.sequelize || this.app.Sequelize;
|
||
const aggregated = Boolean(sequelize && typeof sequelize.fn === 'function');
|
||
const counts = new Map();
|
||
|
||
let rows;
|
||
if (aggregated) {
|
||
rows = await this.ScanRecord.findAll({
|
||
attributes: [
|
||
[ sequelize.col('workstation_id'), 'workstationId' ],
|
||
[ sequelize.fn('COUNT', sequelize.col('id')), 'count' ],
|
||
],
|
||
where,
|
||
group: [ sequelize.col('workstation_id') ],
|
||
raw: true,
|
||
});
|
||
} else {
|
||
rows = await this.ScanRecord.findAll({ attributes: [ 'workstationId' ], where, raw: true });
|
||
}
|
||
for (const row of rows) {
|
||
const id = Number(row.workstationId === undefined ? row.workstation_id : row.workstationId);
|
||
if (!Number.isInteger(id) || id <= 0) continue;
|
||
const count = aggregated ? (Number(row.count) || 0) : 1;
|
||
counts.set(id, (counts.get(id) || 0) + count);
|
||
}
|
||
|
||
const list = stations.map(station => {
|
||
const id = Number(station.id);
|
||
return {
|
||
id,
|
||
code: station.code,
|
||
name: station.name || '',
|
||
wsType: Number(station.wsType) || 1,
|
||
status: Number(station.status) || 0,
|
||
count: counts.get(id) || 0,
|
||
};
|
||
});
|
||
|
||
list.sort((a, b) => (b.count - a.count) || (a.id - b.id));
|
||
return { list };
|
||
}
|
||
|
||
/** 某中控的通道(上报后回显用) */
|
||
async findChannelsByScanner(scannerId) {
|
||
const rows = await this.ScanChannel.findAll({
|
||
where: { scannerId },
|
||
order: [[ 'channelId', 'ASC' ]],
|
||
});
|
||
return rows.map(row => row.toJSON());
|
||
}
|
||
}
|
||
|
||
module.exports = ScanService;
|
||
module.exports.parseScanTime = parseScanTime;
|