284 lines
9.1 KiB
JavaScript
284 lines
9.1 KiB
JavaScript
const XLSX = require('xlsx');
|
||
const fs = require('fs');
|
||
|
||
/**
|
||
* 功能：读取Excel文件并解析为JSON
|
||
* 参数：filePath - 文件路径
|
||
* 返回值：解析后的工作簿对象
|
||
*/
|
||
function readExcel(filePath) {
|
||
return XLSX.readFile(filePath);
|
||
}
|
||
|
||
/**
|
||
* 功能：判断是否为有效订单号（FR开头的订单号）
|
||
* 参数：orderNo - 订单号
|
||
* 返回值：是否有效
|
||
*/
|
||
function isValidOrderNo(orderNo) {
|
||
if (!orderNo) return false;
|
||
const str = String(orderNo).trim();
|
||
if (!str) return false;
|
||
if (str === '总计' || str === '合计' || str === '汇总') return false;
|
||
if (str.startsWith('FR')) return true;
|
||
return /^\d+$/.test(str);
|
||
}
|
||
|
||
/**
|
||
* 功能：从酒店账单提取订单号和金额
|
||
* 参数：workbook - Excel工作簿对象
|
||
* 返回值：订单Map {订单号: {金额, 入住人, 入住时间, 离店时间}}
|
||
*/
|
||
function extractHotelOrders(workbook) {
|
||
const sheet = workbook.Sheets['.'];
|
||
const data = XLSX.utils.sheet_to_json(sheet);
|
||
const orders = new Map();
|
||
|
||
data.forEach(row => {
|
||
const orderNo = row['吉利订单号'];
|
||
if (isValidOrderNo(orderNo)) {
|
||
orders.set(String(orderNo).trim(), {
|
||
结算金额: parseFloat(row['结算金额']) || 0,
|
||
系统金额: parseFloat(row['系统金额']) || 0,
|
||
差异: parseFloat(row['差异']) || 0,
|
||
入住人: row['入住人姓名'] || '',
|
||
入住时间: row['入住时间'] || '',
|
||
离店时间: row['离店时间'] || '',
|
||
房型: row['房型'] || '',
|
||
房号: row['房号'] || '',
|
||
房间数: row['房间数'] || 0,
|
||
间夜数: row['间夜数'] || 0
|
||
});
|
||
}
|
||
});
|
||
|
||
return orders;
|
||
}
|
||
|
||
/**
|
||
* 功能：从商旅账单Export表提取订单号和金额（合并同一订单的正负金额）
|
||
* 参数：workbook - Excel工作簿对象
|
||
* 返回值：订单Map {订单号: 金额对象}
|
||
*/
|
||
function extractGeelyExportOrders(workbook) {
|
||
const sheet = workbook.Sheets['Export'];
|
||
const data = XLSX.utils.sheet_to_json(sheet, { header: 1 }); // 使用数组索引访问
|
||
const orders = new Map();
|
||
|
||
// 从第2行开始（第0行是标题，第1行是表头）
|
||
for (let i = 2; i < data.length; i++) {
|
||
const row = data[i];
|
||
const orderNo = row[5]; // 供应商订单号在第5列
|
||
|
||
if (isValidOrderNo(orderNo)) {
|
||
// 金额在第11列，纯数字，已包含正负值
|
||
const amount = parseFloat(row[11]) || 0;
|
||
const key = String(orderNo).trim();
|
||
|
||
// 合并同一订单号的金额
|
||
if (orders.has(key)) {
|
||
const existing = orders.get(key);
|
||
existing.金额 += amount;
|
||
// 保留最后一条记录的其他信息
|
||
existing.对账明细名称 = row[6] || existing.对账明细名称;
|
||
existing.结算状态 = row[9] || existing.结算状态;
|
||
existing.对账单号 = row[0] || existing.对账单号;
|
||
} else {
|
||
orders.set(key, {
|
||
金额: amount,
|
||
对账明细名称: row[6] || '',
|
||
结算状态: row[9] || '',
|
||
对账单号: row[0] || '',
|
||
订单号: row[4] || ''
|
||
});
|
||
}
|
||
}
|
||
}
|
||
|
||
return orders;
|
||
}
|
||
|
||
/**
|
||
* 功能：对比两个订单集合的差异
|
||
* 参数：hotelOrders - 酒店方订单Map, geelyOrders - 商旅方订单Map
|
||
* 返回值：差异结果对象
|
||
*/
|
||
function compareOrders(hotelOrders, geelyOrders) {
|
||
const hotelOnly = [];
|
||
const geelyOnly = [];
|
||
const amountDiff = [];
|
||
const matched = [];
|
||
|
||
// 检查酒店有但商旅没有的订单
|
||
for (const [orderNo, hotelInfo] of hotelOrders) {
|
||
if (!geelyOrders.has(orderNo)) {
|
||
hotelOnly.push({ 订单号: orderNo, ...hotelInfo });
|
||
} else {
|
||
const geelyInfo = geelyOrders.get(orderNo);
|
||
const hotelAmount = hotelInfo.结算金额;
|
||
const geelyAmount = geelyInfo.金额;
|
||
|
||
if (Math.abs(hotelAmount - geelyAmount) > 0.01) {
|
||
amountDiff.push({
|
||
订单号: orderNo,
|
||
酒店金额: hotelAmount,
|
||
商旅金额: geelyAmount,
|
||
差额: hotelAmount - geelyAmount,
|
||
入住人: hotelInfo.入住人,
|
||
酒店明细: hotelInfo,
|
||
商旅明细: geelyInfo
|
||
});
|
||
} else {
|
||
matched.push({
|
||
订单号: orderNo,
|
||
金额: hotelAmount,
|
||
入住人: hotelInfo.入住人
|
||
});
|
||
}
|
||
}
|
||
}
|
||
|
||
// 检查商旅有但酒店没有的订单
|
||
for (const [orderNo, geelyInfo] of geelyOrders) {
|
||
if (!hotelOrders.has(orderNo)) {
|
||
geelyOnly.push({ 订单号: orderNo, ...geelyInfo });
|
||
}
|
||
}
|
||
|
||
return { hotelOnly, geelyOnly, amountDiff, matched };
|
||
}
|
||
|
||
/**
|
||
* 功能：生成差异报告并输出
|
||
* 参数：result - 对比结果对象
|
||
*/
|
||
function generateReport(result, hotelOrdersCount, geelyOrdersCount) {
|
||
console.log('\n' + '='.repeat(70));
|
||
console.log(' 吉利商旅 vs 酒店账单 差异报告');
|
||
console.log(' (仅使用Export表，已合并同一订单的正负金额)');
|
||
console.log('='.repeat(70));
|
||
|
||
console.log(`\n【统计概览】`);
|
||
console.log(` 酒店账单有效订单数: ${hotelOrdersCount}`);
|
||
console.log(` 商旅账单有效订单数: ${geelyOrdersCount}`);
|
||
console.log(` ✓ 匹配成功订单数: ${result.matched.length}`);
|
||
console.log(` ⚠ 金额差异订单数: ${result.amountDiff.length}`);
|
||
console.log(` ✗ 酒店有-商旅无: ${result.hotelOnly.length}`);
|
||
console.log(` ? 商旅有-酒店无: ${result.geelyOnly.length}`);
|
||
|
||
if (result.hotelOnly.length > 0) {
|
||
console.log(`\n【酒店有但商旅没有的订单 - 共${result.hotelOnly.length}条】`);
|
||
console.log('-'.repeat(70));
|
||
result.hotelOnly.forEach((item, i) => {
|
||
console.log(`${i+1}. ${item.订单号} | ${item.入住人.padEnd(6)} | ¥${String(item.结算金额).padStart(6)} | ${item.房型}`);
|
||
});
|
||
}
|
||
|
||
if (result.geelyOnly.length > 0) {
|
||
console.log(`\n【商旅有但酒店没有的订单 - 共${result.geelyOnly.length}条】`);
|
||
console.log('-'.repeat(70));
|
||
result.geelyOnly.forEach((item, i) => {
|
||
console.log(`${i+1}. ${item.订单号} | ¥${String(item.金额).padStart(6)} | ${item.对账明细名称 || ''}`);
|
||
});
|
||
}
|
||
|
||
if (result.amountDiff.length > 0) {
|
||
console.log(`\n【金额存在差异的订单 - 共${result.amountDiff.length}条】`);
|
||
console.log('-'.repeat(70));
|
||
result.amountDiff.forEach((item, i) => {
|
||
console.log(`${i+1}. ${item.订单号} | ${item.入住人}`);
|
||
console.log(` 酒店: ¥${item.酒店金额} | 商旅: ¥${item.商旅金额} | 差额: ¥${item.差额}`);
|
||
});
|
||
}
|
||
|
||
console.log('\n' + '='.repeat(70));
|
||
|
||
// 计算总金额
|
||
let hotelTotal = 0;
|
||
let geelyTotal = 0;
|
||
|
||
for (const item of result.matched) {
|
||
hotelTotal += item.金额;
|
||
geelyTotal += item.金额;
|
||
}
|
||
for (const item of result.amountDiff) {
|
||
hotelTotal += item.酒店金额;
|
||
geelyTotal += item.商旅金额;
|
||
}
|
||
for (const item of result.hotelOnly) {
|
||
hotelTotal += item.结算金额;
|
||
}
|
||
for (const item of result.geelyOnly) {
|
||
geelyTotal += item.金额;
|
||
}
|
||
|
||
console.log(`\n【金额汇总】`);
|
||
console.log(` 酒店账单总金额: ¥${hotelTotal}`);
|
||
console.log(` 商旅账单总金额: ¥${geelyTotal}`);
|
||
console.log(` 总差额: ¥${hotelTotal - geelyTotal}`);
|
||
console.log('');
|
||
|
||
return {
|
||
统计: {
|
||
酒店订单总数: hotelOrdersCount,
|
||
商旅订单总数: geelyOrdersCount,
|
||
匹配成功: result.matched.length,
|
||
金额差异: result.amountDiff.length,
|
||
酒店有商旅无: result.hotelOnly.length,
|
||
商旅有酒店无: result.geelyOnly.length,
|
||
酒店总金额: hotelTotal,
|
||
商旅总金额: geelyTotal,
|
||
总差额: hotelTotal - geelyTotal
|
||
},
|
||
酒店有商旅无: result.hotelOnly,
|
||
商旅有酒店无: result.geelyOnly,
|
||
金额差异: result.amountDiff,
|
||
匹配成功: result.matched
|
||
};
|
||
}
|
||
|
||
/**
|
||
* 功能：将结果保存为JSON文件
|
||
* 参数：report - 报告对象, filename - 文件名
|
||
*/
|
||
function saveReportToJson(report, filename) {
|
||
const jsonReport = JSON.parse(JSON.stringify(report, (key, value) => {
|
||
if (typeof value === 'bigint') return value.toString();
|
||
return value;
|
||
}));
|
||
|
||
fs.writeFileSync(filename, JSON.stringify(jsonReport, null, 2), 'utf8');
|
||
console.log(`详细结果已保存到: ${filename}`);
|
||
}
|
||
|
||
// 主函数
|
||
function main() {
|
||
const geelyFile = '杭州碧丽酒店管理有限公司 2026年06月 对账单(0601-0630).xlsx';
|
||
const hotelFile = '杭州滨江希尔顿欢朋酒店-吉利商旅6月账单.xlsx';
|
||
|
||
console.log('正在读取Excel文件...');
|
||
|
||
const geelyWorkbook = readExcel(geelyFile);
|
||
const hotelWorkbook = readExcel(hotelFile);
|
||
|
||
console.log('正在提取订单数据...');
|
||
|
||
const hotelOrders = extractHotelOrders(hotelWorkbook);
|
||
const geelyOrders = extractGeelyExportOrders(geelyWorkbook);
|
||
|
||
console.log(`酒店有效订单数: ${hotelOrders.size}`);
|
||
console.log(`商旅Export表有效订单数(合并后): ${geelyOrders.size}`);
|
||
|
||
console.log('\n正在对比订单...');
|
||
|
||
const result = compareOrders(hotelOrders, geelyOrders);
|
||
|
||
const report = generateReport(result, hotelOrders.size, geelyOrders.size);
|
||
|
||
saveReportToJson(report, '账单差异报告.json');
|
||
|
||
console.log('\n对比完成！');
|
||
}
|
||
|
||
main();
|