380 lines
16 KiB
JavaScript
380 lines
16 KiB
JavaScript
const XLSX = require('xlsx');
|
||
const path = require('path');
|
||
|
||
// ========== 配置 ==========
|
||
const EXCEL_FILE = path.join(__dirname, '新建 XLSX 工作表.xlsx');
|
||
const SHEET_NAME = '2026';
|
||
|
||
const BASE_URL = 'https://superstar.geelytravel.com';
|
||
const AUTH_TOKEN = 'Bearer eyJhbGciOiJIUzI1NiJ9.eyJzdWIiOiJKV1QtRC1TRVFfVVNFUl9UTUNfMDAwMDAxODMiLCJ1c2VyX2NvZGUiOiJTRVFfVVNFUl9UTUNfMDAwMDAxODMiLCJyb2xlcyI6WyLnu5PnrpfkuJPlkZgiLCLnu5PnrpfkuJPlkZgiXSwiZXhwIjoxNzg2NTMzNTUxLCJpYXQiOjE3ODM5NDE1NTF9.1Mcmf5IM07Scrry4FzPs6TAcfUu0Nqtjre_2iJlNd5c';
|
||
|
||
const COMMON_HEADERS = {
|
||
'accept': 'application/json, text/plain, */*',
|
||
'accept-language': 'zh-CN,zh;q=0.9',
|
||
'authorization': AUTH_TOKEN,
|
||
'cache-control': 'no-cache',
|
||
'pragma': 'no-cache',
|
||
'requestsource': 'D',
|
||
'sec-fetch-dest': 'empty',
|
||
'sec-fetch-mode': 'cors',
|
||
'sec-fetch-site': 'same-origin',
|
||
'usercode': 'SEQ_USER_TMC_00000183',
|
||
'cookie': '_c_WBKFRo=CefecIUfnnuqejh60diYtytjDPwI9Qptslcwakc4; acw_tc=731dc87617840296932491914e815aef28b42e276e1a01eb53ed0f548bd5e3',
|
||
};
|
||
|
||
const INVOICE_APPLY_TITLE = '浙江吉利商务服务有限公司';
|
||
const INVOICE_TITLE = '吉智（杭州）文化创意有限公司';
|
||
const TAXPAYER_ID = '91330109MA2GM71J64';
|
||
const ADDRESS = '杭州市萧山区经纪技术开发区启迪路198号A-B102-126B室';
|
||
const TELEPHONE = '0571-85321518';
|
||
const DEPOSIT_BANK = '中国民生银行股份有限公司杭州分行';
|
||
const BANK_ACCOUNT = '631101003';
|
||
|
||
// ========== 1. 读取 Excel ==========
|
||
function readTeamIds() {
|
||
const workbook = XLSX.readFile(EXCEL_FILE);
|
||
const sheet = workbook.Sheets[SHEET_NAME];
|
||
if (!sheet) {
|
||
console.error('Sheet "' + SHEET_NAME + '" 不存在');
|
||
process.exit(1);
|
||
}
|
||
const rows = XLSX.utils.sheet_to_json(sheet, { defval: null });
|
||
const teamIds = [];
|
||
for (const row of rows) {
|
||
const val = row['团号'];
|
||
if (val && /^TEAM-\d+$/.test(String(val).trim())) {
|
||
teamIds.push(String(val).trim());
|
||
}
|
||
}
|
||
console.log('[Excel] 找到 ' + teamIds.length + ' 个 TEAM ID: ' + teamIds.join(', ') + '\n');
|
||
return teamIds;
|
||
}
|
||
|
||
// ========== 2. 查询 settlement account ==========
|
||
async function querySettlementAccountIds(teamId) {
|
||
const url = BASE_URL + '/api/tmc/tmc/customerAccount/settlement/querySettlementAccountIds?customerSettlementAccountName=' + teamId;
|
||
const resp = await fetch(url, { headers: COMMON_HEADERS, method: 'GET' });
|
||
return resp.json();
|
||
}
|
||
|
||
// ========== 3. 查询 bill list ==========
|
||
async function queryBillList(settlementAccountId) {
|
||
const url = BASE_URL + '/api/tmc/tmc/settlement/receiveBill/queryBillList?settlementAccountId=' + settlementAccountId + '&pageIndex=1&pageSize=10';
|
||
const resp = await fetch(url, { headers: COMMON_HEADERS, method: 'GET' });
|
||
return resp.json();
|
||
}
|
||
|
||
// ========== 4. 批量调整发票类型 ==========
|
||
async function batchAdjustInvoiceOrderPrice(settlementSeq, receiveBillId, originInvoiceType, targetInvoiceType) {
|
||
const url = BASE_URL + '/api/tmc/tmc/settlement/receiveInvoice/batchAdjustInvoiceOrderPrice';
|
||
const headers = {
|
||
...COMMON_HEADERS,
|
||
'content-type': 'application/json;charset=UTF-8',
|
||
'Referer': BASE_URL + '/new-tmc/finance/settlement/pay/invoice/billing/' + receiveBillId,
|
||
};
|
||
const body = JSON.stringify({
|
||
settlementSeq: settlementSeq,
|
||
orderMode: '1',
|
||
originInvoiceType: originInvoiceType,
|
||
targetInvoiceType: targetInvoiceType,
|
||
});
|
||
const resp = await fetch(url, { headers: headers, method: 'POST', body: body });
|
||
return resp.json();
|
||
}
|
||
|
||
// ========== 5. 确认开票 ==========
|
||
async function confirmInvoiceOrder(settlementSeq, receiveBillId) {
|
||
const url = BASE_URL + '/api/tmc/tmc/settlement/receiveInvoice/confirmInvoiceOrder';
|
||
const headers = {
|
||
...COMMON_HEADERS,
|
||
'content-type': 'application/json;charset=UTF-8',
|
||
'Referer': BASE_URL + '/new-tmc/finance/settlement/pay/invoice/billing/' + receiveBillId,
|
||
};
|
||
const body = JSON.stringify({ settlementSeq: settlementSeq, orderMode: '1' });
|
||
const resp = await fetch(url, { headers: headers, method: 'POST', body: body });
|
||
return resp.json();
|
||
}
|
||
|
||
// ========== 6. 提交开票 ==========
|
||
async function submitInvoiceApply(confirmInvoiceKey, invoiceInfoList, receiveBillId) {
|
||
const url = BASE_URL + '/api/tmc/tmc/settlement/receiveInvoice/submitInvoiceApply';
|
||
const headers = {
|
||
...COMMON_HEADERS,
|
||
'content-type': 'application/json;charset=UTF-8',
|
||
'Referer': BASE_URL + '/new-tmc/finance/settlement/pay/invoice/billing/' + receiveBillId,
|
||
};
|
||
const body = JSON.stringify({
|
||
confirmInvoiceKey: confirmInvoiceKey,
|
||
invoiceInfoList: invoiceInfoList,
|
||
});
|
||
const resp = await fetch(url, { headers: headers, method: 'POST', body: body });
|
||
return resp.json();
|
||
}
|
||
|
||
// ========== 7. 填充公司信息 ==========
|
||
function fillCompanyInfo(item) {
|
||
return {
|
||
...item,
|
||
invoiceApplyTitle: INVOICE_APPLY_TITLE,
|
||
invoiceTitle: INVOICE_TITLE,
|
||
taxpayerIdentityNumber: TAXPAYER_ID,
|
||
address: ADDRESS,
|
||
telephone: TELEPHONE,
|
||
depositBank: DEPOSIT_BANK,
|
||
bankAccount: BANK_ACCOUNT,
|
||
};
|
||
}
|
||
|
||
// ========== 8. 映射火车票开票项 ==========
|
||
function mapTrainInvoice(serverItem) {
|
||
const invoiceType = serverItem.invoiceType || '';
|
||
const item = {
|
||
invoiceSerialNum: serverItem.invoiceSerialNum,
|
||
invoiceType: invoiceType,
|
||
invoicePrice: serverItem.invoicePrice,
|
||
taxRate: invoiceType === '13' ? 6 : 0,
|
||
invoiceSettlementDetail: serverItem.settlementDetails || '',
|
||
invoiceQuantity: serverItem.invoiceQuantity || 0,
|
||
ticketList: serverItem.ticketList || null,
|
||
};
|
||
if (invoiceType === '13') {
|
||
item.invoiceContent = '*生产生活服务*代订火车票服务费';
|
||
}
|
||
return fillCompanyInfo(item);
|
||
}
|
||
|
||
// ========== 9. 映射酒店开票项 ==========
|
||
function mapHotelInvoice(serverItem) {
|
||
const invoiceType = serverItem.invoiceType || '';
|
||
const item = {
|
||
invoiceSerialNum: serverItem.invoiceSerialNum,
|
||
invoiceType: invoiceType,
|
||
invoicePrice: serverItem.invoicePrice,
|
||
taxRate: 6,
|
||
invoiceSettlementDetail: serverItem.settlementDetails || '',
|
||
invoiceQuantity: serverItem.invoiceQuantity || 0,
|
||
ticketList: serverItem.ticketList || null,
|
||
};
|
||
if (invoiceType === '2' || invoiceType === '1') {
|
||
item.invoiceContent = '*生产生活服务*代订住宿费';
|
||
} else if (invoiceType === '13') {
|
||
item.invoiceContent = '*生产生活服务*代订住宿服务费';
|
||
}
|
||
return fillCompanyInfo(item);
|
||
}
|
||
|
||
// ========== 10. 映射机票开票项（国内+国际通用） ==========
|
||
function mapFlightInvoice(serverItem) {
|
||
const invoiceType = serverItem.invoiceType || '';
|
||
const item = {
|
||
invoiceSerialNum: serverItem.invoiceSerialNum,
|
||
invoiceType: invoiceType,
|
||
invoicePrice: serverItem.invoicePrice,
|
||
taxRate: 6,
|
||
invoiceSettlementDetail: serverItem.settlementDetails || '',
|
||
invoiceQuantity: serverItem.invoiceQuantity || 0,
|
||
ticketList: serverItem.ticketList || null,
|
||
};
|
||
if (invoiceType === '2') {
|
||
item.invoiceContent = '*生产生活服务*代订机票';
|
||
} else if (invoiceType === '13') {
|
||
item.invoiceContent = '*生产生活服务*代订机票服务费';
|
||
}
|
||
return fillCompanyInfo(item);
|
||
}
|
||
|
||
// ========== 11. 处理火车票 ==========
|
||
async function processTrainBill(bill, billIndex, totalBills) {
|
||
const settlementSeq = bill.settlementSeq;
|
||
const receiveBillId = bill.receiveBillId;
|
||
|
||
console.log(' --- 火车票 [' + billIndex + '/' + totalBills + '] ' + bill.statementName + ' ---');
|
||
|
||
const confirmResp = await confirmInvoiceOrder(settlementSeq, receiveBillId);
|
||
console.log(' -> confirmInvoiceOrder:', JSON.stringify(confirmResp));
|
||
|
||
const confirmResult = confirmResp?.result;
|
||
if (!confirmResult || !confirmResult.invoiceAble) {
|
||
console.log(' ⚠ 跳过: ' + (confirmResp?.message || '') + '\n');
|
||
return;
|
||
}
|
||
|
||
const confirmInvoiceKey = confirmResult.confirmInvoiceKey;
|
||
const submitList = (confirmResult.invoiceInfoList || []).map(mapTrainInvoice);
|
||
|
||
const submitResp = await submitInvoiceApply(confirmInvoiceKey, submitList, receiveBillId);
|
||
console.log(' -> submitInvoiceApply:', JSON.stringify(submitResp));
|
||
console.log('');
|
||
}
|
||
|
||
// ========== 12. 处理酒店 ==========
|
||
async function processHotelBill(bill, billIndex, totalBills) {
|
||
const settlementSeq = bill.settlementSeq;
|
||
const receiveBillId = bill.receiveBillId;
|
||
|
||
console.log(' --- 酒店 [' + billIndex + '/' + totalBills + '] ' + bill.statementName + ' ---');
|
||
|
||
const adjustResp = await batchAdjustInvoiceOrderPrice(settlementSeq, receiveBillId, '1', '2');
|
||
console.log(' -> batchAdjust (1→2):', JSON.stringify(adjustResp));
|
||
if (adjustResp?.code !== 'Z000') {
|
||
console.log(' ⚠ 调整失败: ' + (adjustResp?.message || '') + '\n');
|
||
return;
|
||
}
|
||
|
||
const confirmResp = await confirmInvoiceOrder(settlementSeq, receiveBillId);
|
||
console.log(' -> confirmInvoiceOrder:', JSON.stringify(confirmResp));
|
||
|
||
const confirmResult = confirmResp?.result;
|
||
if (!confirmResult || !confirmResult.invoiceAble) {
|
||
console.log(' ⚠ 跳过: ' + (confirmResp?.message || '') + '\n');
|
||
return;
|
||
}
|
||
|
||
const confirmInvoiceKey = confirmResult.confirmInvoiceKey;
|
||
const submitList = (confirmResult.invoiceInfoList || []).map(mapHotelInvoice);
|
||
|
||
const submitResp = await submitInvoiceApply(confirmInvoiceKey, submitList, receiveBillId);
|
||
console.log(' -> submitInvoiceApply:', JSON.stringify(submitResp));
|
||
console.log('');
|
||
}
|
||
|
||
// ========== 13. 处理国内机票 ==========
|
||
async function processFlightBill(bill, billIndex, totalBills) {
|
||
const settlementSeq = bill.settlementSeq;
|
||
const receiveBillId = bill.receiveBillId;
|
||
|
||
console.log(' --- 国内机票 [' + billIndex + '/' + totalBills + '] ' + bill.statementName + ' ---');
|
||
|
||
const adjust1Resp = await batchAdjustInvoiceOrderPrice(settlementSeq, receiveBillId, '4', '2');
|
||
console.log(' -> batchAdjust (4→2):', JSON.stringify(adjust1Resp));
|
||
if (adjust1Resp?.code !== 'Z000') {
|
||
console.log(' ⚠ 调整失败: ' + (adjust1Resp?.message || '') + '\n');
|
||
return;
|
||
}
|
||
|
||
const adjust2Resp = await batchAdjustInvoiceOrderPrice(settlementSeq, receiveBillId, '30', '2');
|
||
console.log(' -> batchAdjust (30→2):', JSON.stringify(adjust2Resp));
|
||
if (adjust2Resp?.code !== 'Z000') {
|
||
console.log(' ⚠ 调整失败: ' + (adjust2Resp?.message || '') + '\n');
|
||
return;
|
||
}
|
||
|
||
const confirmResp = await confirmInvoiceOrder(settlementSeq, receiveBillId);
|
||
console.log(' -> confirmInvoiceOrder:', JSON.stringify(confirmResp));
|
||
|
||
const confirmResult = confirmResp?.result;
|
||
if (!confirmResult || !confirmResult.invoiceAble) {
|
||
console.log(' ⚠ 跳过: ' + (confirmResp?.message || '') + '\n');
|
||
return;
|
||
}
|
||
|
||
const confirmInvoiceKey = confirmResult.confirmInvoiceKey;
|
||
const submitList = (confirmResult.invoiceInfoList || []).map(mapFlightInvoice);
|
||
|
||
const submitResp = await submitInvoiceApply(confirmInvoiceKey, submitList, receiveBillId);
|
||
console.log(' -> submitInvoiceApply:', JSON.stringify(submitResp));
|
||
console.log('');
|
||
}
|
||
|
||
// ========== 14. 处理国际机票 ==========
|
||
async function processInternationalFlightBill(bill, billIndex, totalBills) {
|
||
const settlementSeq = bill.settlementSeq;
|
||
const receiveBillId = bill.receiveBillId;
|
||
|
||
console.log(' --- 国际机票 [' + billIndex + '/' + totalBills + '] ' + bill.statementName + ' ---');
|
||
|
||
const confirmResp = await confirmInvoiceOrder(settlementSeq, receiveBillId);
|
||
console.log(' -> confirmInvoiceOrder:', JSON.stringify(confirmResp));
|
||
|
||
const confirmResult = confirmResp?.result;
|
||
if (!confirmResult || !confirmResult.invoiceAble) {
|
||
console.log(' ⚠ 跳过: ' + (confirmResp?.message || '') + '\n');
|
||
return;
|
||
}
|
||
|
||
const confirmInvoiceKey = confirmResult.confirmInvoiceKey;
|
||
const submitList = (confirmResult.invoiceInfoList || []).map(mapFlightInvoice);
|
||
|
||
const submitResp = await submitInvoiceApply(confirmInvoiceKey, submitList, receiveBillId);
|
||
console.log(' -> submitInvoiceApply:', JSON.stringify(submitResp));
|
||
console.log('');
|
||
}
|
||
|
||
// ========== 主流程 ==========
|
||
async function main() {
|
||
const teamIds = readTeamIds();
|
||
if (teamIds.length === 0) {
|
||
console.log('没有找到 TEAM ID，退出');
|
||
return;
|
||
}
|
||
|
||
for (let i = 0; i < teamIds.length; i++) {
|
||
const teamId = teamIds[i];
|
||
console.log('[' + (i + 1) + '/' + teamIds.length + '] 处理 ' + teamId + ' ...');
|
||
|
||
try {
|
||
const settlementResp = await querySettlementAccountIds(teamId);
|
||
const resultList = settlementResp?.result || [];
|
||
if (resultList.length === 0) {
|
||
console.log(' ⚠ ' + teamId + ' 没有 settlement account，跳过\n');
|
||
continue;
|
||
}
|
||
const settlementAccountId = resultList[0]?.id;
|
||
console.log(' -> settlementAccountId: ' + settlementAccountId);
|
||
|
||
const billResp = await queryBillList(settlementAccountId);
|
||
const allBills = billResp?.result?.list || [];
|
||
const total = billResp?.result?.total || 0;
|
||
console.log(' -> 共 ' + total + ' 条对账单');
|
||
|
||
const skippedBills = allBills.filter(function (b) { return String(b.settlementStatus) === '3'; });
|
||
skippedBills.forEach(function (b) {
|
||
console.log(' ⚠ 跳过 settlementStatus=3 的对账单: ' + (b.statementName || b.receiveBillId || '') + '\n');
|
||
});
|
||
|
||
const bills = allBills.filter(function (b) { return String(b.settlementStatus) !== '3'; });
|
||
console.log(' -> 实际处理 ' + bills.length + ' 条对账单\n');
|
||
|
||
const trainBills = bills.filter(function (b) { return (b.statementName || '').includes('火车票'); });
|
||
const hotelBills = bills.filter(function (b) { return (b.statementName || '').includes('酒店'); });
|
||
const flightBills = bills.filter(function (b) { return (b.statementName || '').includes('国内机票'); });
|
||
const intlFlightBills = bills.filter(function (b) { return (b.statementName || '').includes('国际机票'); });
|
||
const otherBills = bills.filter(function (b) {
|
||
const name = b.statementName || '';
|
||
return !name.includes('火车票') && !name.includes('酒店') && !name.includes('国内机票') && !name.includes('国际机票');
|
||
});
|
||
|
||
console.log(' -> 火车票: ' + trainBills.length + ', 酒店: ' + hotelBills.length + ', 国内机票: ' + flightBills.length + ', 国际机票: ' + intlFlightBills.length + ', 其他: ' + otherBills.length + '\n');
|
||
|
||
for (let j = 0; j < trainBills.length; j++) {
|
||
await processTrainBill(trainBills[j], j + 1, trainBills.length);
|
||
}
|
||
for (let j = 0; j < hotelBills.length; j++) {
|
||
await processHotelBill(hotelBills[j], j + 1, hotelBills.length);
|
||
}
|
||
for (let j = 0; j < flightBills.length; j++) {
|
||
await processFlightBill(flightBills[j], j + 1, flightBills.length);
|
||
}
|
||
for (let j = 0; j < intlFlightBills.length; j++) {
|
||
await processInternationalFlightBill(intlFlightBills[j], j + 1, intlFlightBills.length);
|
||
}
|
||
|
||
if (otherBills.length > 0) {
|
||
console.log(' === 以下对账单未处理（未知类型） ===');
|
||
otherBills.forEach(function (b) { console.log(' - ' + b.statementName); });
|
||
console.log('');
|
||
}
|
||
} catch (err) {
|
||
console.error(' ✗ ' + teamId + ' 出错:', err.message);
|
||
}
|
||
console.log('');
|
||
}
|
||
|
||
console.log('========== 完成 ==========');
|
||
}
|
||
|
||
main().catch(err => {
|
||
console.error('Fatal error:', err);
|
||
process.exit(1);
|
||
});
|