lx_fetch_amazon_orders.js

#!/usr/bin/env node
const crypto = require('crypto');
const https = require('https');
const fs = require('fs');
const path = require('path');
const { execSync } = require('child_process');

function loadEnv() {
    const envPath = path.join(__dirname, '.env');
    if (!fs.existsSync(envPath)) {
        console.error('.env 文件不存在，请先运行: cp .env.example .env');
        console.error('然后填入 APP_ID 和 APP_SECRET');
        process.exit(1);
    }
    const env = {};
    fs.readFileSync(envPath, 'utf8')
        .split('\n')
        .forEach(line => {
            const [key, ...values] = line.split('=');
            if (key) env[key.trim()] = values.join('=').trim();
        });
    return env;
}

let env = loadEnv();
let config = {
    accessToken: env.ACCESS_TOKEN,
    appId: env.APP_ID,
    domainName: env.DOMAIN_NAME || 'https://openapi.lingxing.com'
};

function reloadConfig() {
    env = loadEnv();
    config = {
        accessToken: env.ACCESS_TOKEN,
        appId: env.APP_ID,
        domainName: env.DOMAIN_NAME || 'https://openapi.lingxing.com'
    };
}

function checkAndRefreshToken() {
    if (!env.ACCESS_TOKEN) {
        console.log('未找到 Token，正在获取...');
        return refreshToken();
    }
    
    if (env.EXPIRES_AT) {
        const expiresAt = new Date(env.EXPIRES_AT);
        const now = new Date();
        if (now >= expiresAt) {
            console.log('Token 已过期，正在刷新...');
            return refreshToken();
        }
    }
    
    return true;
}

function refreshToken() {
    console.log('Token 已失效，正在重新获取...');
    try {
        execSync('node lx_auth.js', { cwd: __dirname, stdio: 'inherit' });
        reloadConfig();
        console.log('Token 刷新成功\n');
        return true;
    } catch (err) {
        console.error('Token 刷新失败:', err.message);
        return false;
    }
}

function httpsPost(url, body, headers) {
    return new Promise((resolve, reject) => {
        const urlObj = new URL(url);
        const options = {
            hostname: urlObj.hostname,
            port: urlObj.port || 443,
            path: urlObj.pathname + urlObj.search,
            method: 'POST',
            headers: headers,
            timeout: 30000
        };

        const req = https.request(options, (res) => {
            let data = '';
            res.on('data', (chunk) => data += chunk);
            res.on('end', () => resolve({ code: res.statusCode, body: data }));
        });

        req.on('timeout', () => {
            req.destroy();
            reject(new Error('TIMEOUT'));
        });

        req.on('error', reject);
        req.write(body);
        req.end();
    });
}

function md5(str) {
    return crypto.createHash('md5').update(str).digest('hex').toUpperCase();
}

function aesEncrypt(text, key) {
    const cipher = crypto.createCipheriv('aes-128-ecb', Buffer.from(key, 'utf8'), null);
    let encrypted = cipher.update(text, 'utf8', 'base64');
    encrypted += cipher.final('base64');
    return encrypted;
}

function sortAscii(obj) {
    return Object.keys(obj)
        .sort()
        .map(k => `${k}=${obj[k]}`)
        .join('&');
}

function serializeURL(obj) {
    return Object.keys(obj)
        .map(k => `${encodeURIComponent(k)}=${encodeURIComponent(obj[k])}`)
        .join('&');
}

function getDayTimes(daysAgo) {
    const date = new Date();
    date.setDate(date.getDate() - daysAgo);
    const start = new Date(date.setHours(0, 0, 0, 0));
    const end = new Date(date.setHours(23, 59, 59, 999));
    return [start, end];
}

function formatDateTime(date) {
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, '0');
    const day = String(date.getDate()).padStart(2, '0');
    const hour = String(date.getHours()).padStart(2, '0');
    const minute = String(date.getMinutes()).padStart(2, '0');
    const second = String(date.getSeconds()).padStart(2, '0');
    return `${year}-${month}-${day} ${hour}:${minute}:${second}`;
}

// 站点到时区的映射
const siteTimezones = {
    '美国': 'America/Los_Angeles',   // 太平洋时间（亚马逊美国站）
    '加拿大': 'America/Los_Angeles', // 加拿大（太平洋时间）
    '墨西哥': 'America/Mexico_City', // 墨西哥
    '巴西': 'America/Sao_Paulo',     // 巴西
    '英国': 'Europe/London',         // 英国
    '德国': 'Europe/Berlin',         // 德国（中欧时间）
    '法国': 'Europe/Paris',          // 法国（中欧时间）
    '意大利': 'Europe/Rome',         // 意大利（中欧时间）
    '西班牙': 'Europe/Madrid',       // 西班牙（中欧时间）
    '瑞典': 'Europe/Stockholm',      // 瑞典（中欧时间）
    '日本': 'Asia/Tokyo'             // 日本
};

// 将北京时间转换为站点本地时间
function convertToSiteTime(beijingDate, timezone) {
    // 使用 toLocaleString 转换时区
    const siteTimeStr = beijingDate.toLocaleString('en-US', { 
        timeZone: timezone,
        year: 'numeric',
        month: '2-digit',
        day: '2-digit',
        hour: '2-digit',
        minute: '2-digit',
        second: '2-digit',
        hour12: false
    });
    
    // 解析并格式化为 YYYY-MM-DD HH:mm:ss
    const parts = siteTimeStr.match(/(\d+)\/(\d+)\/(\d+),\s*(\d+):(\d+):(\d+)/);
    if (parts) {
        const [, month, day, year, hour, minute, second] = parts;
        return `${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')} ${hour.padStart(2, '0')}:${minute.padStart(2, '0')}:${second.padStart(2, '0')}`;
    }
    return formatDateTime(beijingDate);
}

// 按站点分组店铺
function groupStoresBySite(stores) {
    const groups = {};
    stores.forEach(store => {
        const country = store.country;
        if (!groups[country]) {
            groups[country] = [];
        }
        groups[country].push(store.sid);
    });
    return groups;
}

async function sendRequest(reqData) {
    const timestamp = Math.floor(Date.now() / 1000);
    
    const input = {
        access_token: config.accessToken,
        app_key: config.appId,
        timestamp: timestamp,
        ...reqData
    };
    
    Object.keys(input).forEach(key => {
        if (typeof input[key] === 'object') {
            input[key] = JSON.stringify(input[key]);
        }
    });
    
    const sortedData = sortAscii(input);
    const sign = md5(sortedData);
    const encrypted = aesEncrypt(sign, config.appId);
    
    const params = {
        access_token: config.accessToken,
        app_key: config.appId,
        sign: encrypted,
        timestamp: timestamp
    };
    
    const apiPath = '/erp/sc/data/mws/orders';
    const url = config.domainName + apiPath + '?' + serializeURL(params);
    
    const headers = {
        'Content-Type': 'application/json;charset=UTF-8',
        'Accept': '*/*'
    };
    
    const response = await httpsPost(url, JSON.stringify(reqData), headers);
    return JSON.parse(response.body);
}

async function fetchOrders(startDate, endDate, sidList) {
    const allOrders = [];
    let offset = 0;
    const length = 1000;
    let retries = 0;
    const maxRetries = 3;
    let tokenRefreshed = false;
    
    const progressFile = path.join(__dirname, 'output', '.fetch_amazon_progress.json');
    
    // 尝试恢复进度
    if (fs.existsSync(progressFile)) {
        try {
            const progress = JSON.parse(fs.readFileSync(progressFile, 'utf8'));
            if (progress.startDate === startDate && 
                progress.endDate === endDate &&
                JSON.stringify(progress.sidList) === JSON.stringify(sidList)) {
                offset = progress.offset;
                allOrders.push(...progress.orders);
                console.log(`从偏移量 ${offset} 继续获取（已有 ${allOrders.length} 条订单）`);
            } else {
                console.log('查询条件已变化，从头开始');
            }
        } catch (err) {
            console.log('无法恢复进度，从头开始');
        }
    }
    
    console.log(`获取订单: ${startDate} 到 ${endDate}`);
    
    const saveProgress = () => {
        const progress = {
            startDate,
            endDate,
            sidList,
            offset,
            orders: allOrders,
            timestamp: Date.now()
        };
        fs.writeFileSync(progressFile, JSON.stringify(progress));
    };
    
    while (retries < maxRetries) {
        const data = {
            sid_list: sidList,
            start_date: startDate,
            end_date: endDate,
            date_type: 1, // 订购时间【站点时间】
            order_status: ['Shipped', 'PartiallyShipped'], // 已发货、部分发货
            offset: offset,
            length: length
        };
        
        try {
            console.log(`正在获取偏移量 ${offset}...`);
            const result = await sendRequest(data);
            
            if (result.code == 0) {
                if (result.data.length == 0) break;
                
                allOrders.push(...result.data);
                console.log(`已获取 ${allOrders.length} / ${result.total} 条订单`);
                
                saveProgress();
                
                if (allOrders.length >= result.total) break;
                
                offset += length;
                retries = 0;
                tokenRefreshed = false;
            } else if ((result.code == 2001003 || result.code == 2001005) && !tokenRefreshed) {
                console.log('Token 失效，正在刷新...');
                if (refreshToken()) {
                    tokenRefreshed = true;
                    retries = 0;
                    continue;
                } else {
                    console.error('Token 刷新失败，进度已保存，可稍后重试');
                    saveProgress();
                    throw new Error('Token 刷新失败，请检查配置');
                }
            } else {
                retries++;
                console.error(`请求失败 (${retries}/${maxRetries}): ${result.message || result.code}`);
                if (retries >= maxRetries) {
                    console.error('达到最大重试次数，进度已保存');
                    saveProgress();
                }
            }
        } catch (err) {
            retries++;
            console.error(`请求异常 (${retries}/${maxRetries}): ${err.message}`);
            if (retries >= maxRetries) {
                console.error('达到最大重试次数，进度已保存');
                saveProgress();
                throw err;
            }
        }
    }
    
    if (fs.existsSync(progressFile)) {
        fs.unlinkSync(progressFile);
    }
    
    return allOrders;
}

async function fetchOrdersByDateType(startDate, endDate, sidList, dateType, dateTypeName) {
    console.log(`\n=== 按${dateTypeName}查询 ===`);
    const allOrders = [];
    
    for (let i = 0; i < sidList.length; i += 20) {
        const batch = sidList.slice(i, i + 20);
        console.log(`正在获取第 ${Math.floor(i / 20) + 1} 批店铺（店铺 ${i + 1}-${Math.min(i + 20, sidList.length)}）`);
        
        let offset = 0;
        const length = 1000;
        let hasMore = true;
        
        while (hasMore) {
            const data = {
                sid_list: batch,
                start_date: startDate,
                end_date: endDate,
                date_type: dateType,
                order_status: ['Shipped', 'PartiallyShipped'],
                offset: offset,
                length: length
            };
            
            try {
                console.log(`正在获取偏移量 ${offset}...`);
                const result = await sendRequest(data);
                
                if (result.code == 0) {
                    if (result.data.length == 0) {
                        hasMore = false;
                        break;
                    }
                    
                    allOrders.push(...result.data);
                    console.log(`已获取 ${allOrders.length} 条订单`);
                    
                    if (result.data.length < length) {
                        hasMore = false;
                    } else {
                        offset += length;
                    }
                } else {
                    console.error(`请求失败: ${result.message || result.code}`);
                    hasMore = false;
                }
            } catch (err) {
                console.error(`请求异常: ${err.message}`);
                hasMore = false;
            }
        }
    }
    
    return allOrders;
}

function escapeCSV(value) {
    if (value == null) return '';
    const str = String(value);
    if (str.includes(',') || str.includes('"') || str.includes('\n') || str.includes('\r')) {
        return `"${str.replace(/"/g, '""')}"`;
    }
    return str;
}

function ordersToCSV(orders) {
    const rows = [];
    
    rows.push([
        '店铺名称',
        '亚马逊订单号',
        '订单状态',
        '配送方式',
        '订单金额',
        '币种',
        '销售渠道',
        '邮编',
        '物流单号',
        '退款金额',
        'ASIN',
        'MSKU',
        '本地SKU',
        '本地品名',
        '数量',
        '订购时间(站点)',
        '发货时间(站点)',
        '订单修改时间'
    ].join(','));
    
    orders.forEach(order => {
        const items = order.item_list || [];
        if (items.length === 0) {
            rows.push([
                escapeCSV(order.seller_name),
                escapeCSV(order.amazon_order_id),
                escapeCSV(order.order_status),
                escapeCSV(order.fulfillment_channel),
                escapeCSV(order.order_total_amount),
                escapeCSV(order.order_total_currency_code),
                escapeCSV(order.sales_channel),
                escapeCSV(order.postal_code),
                escapeCSV(order.tracking_number),
                escapeCSV(order.refund_amount),
                '', '', '', '', '',
                escapeCSV(order.purchase_date_local),
                escapeCSV(order.shipment_date_local),
                escapeCSV(order.gmt_modified)
            ].join(','));
        } else {
            items.forEach(item => {
                rows.push([
                    escapeCSV(order.seller_name),
                    escapeCSV(order.amazon_order_id),
                    escapeCSV(order.order_status),
                    escapeCSV(order.fulfillment_channel),
                    escapeCSV(order.order_total_amount),
                    escapeCSV(order.order_total_currency_code),
                    escapeCSV(order.sales_channel),
                    escapeCSV(order.postal_code),
                    escapeCSV(order.tracking_number),
                    escapeCSV(order.refund_amount),
                    escapeCSV(item.asin),
                    escapeCSV(item.seller_sku),
                    escapeCSV(item.local_sku),
                    escapeCSV(item.local_name),
                    escapeCSV(item.quantity_ordered),
                    escapeCSV(order.purchase_date_local),
                    escapeCSV(order.shipment_date_local),
                    escapeCSV(order.gmt_modified)
                ].join(','));
            });
        }
    });
    
    return rows.join('\n');
}

async function main() {
    if (!checkAndRefreshToken()) {
        console.error('无法获取有效的 Token，请检查配置');
        process.exit(1);
    }
    
    console.log('正在刷新店铺列表...\n');
    try {
        execSync('node lx_get_stores.js', { cwd: __dirname, stdio: 'inherit' });
        console.log('');
    } catch (err) {
        console.error('获取店铺列表失败:', err.message);
        process.exit(1);
    }
    
    let sidList = [];
    let stores = [];
    const storesPath = path.join(__dirname, 'output', 'amazon_seller_stores.json');
    
    try {
        stores = JSON.parse(fs.readFileSync(storesPath, 'utf8')).filter(s => s.status == 1);
        sidList = stores.map(s => s.sid);
        console.log(`加载了 ${sidList.length} 个正常状态的店铺\n`);
    } catch (err) {
        console.log('读取店铺列表失败:', err.message);
        process.exit(1);
    }
    
    const [startDate] = getDayTimes(1);
    const [, endDate] = getDayTimes(0);
    
    console.log(`查询时间范围: ${formatDateTime(startDate)} 到 ${formatDateTime(endDate)} (北京时间)\n`);
    
    // 按站点分组
    const siteGroups = groupStoresBySite(stores);
    
    try {
        const allOrders = [];
        
        // 按订购时间查询（各站点本地时间）
        console.log('=== 按订购时间查询（站点本地时间） ===\n');
        for (const [country, sids] of Object.entries(siteGroups)) {
            const timezone = siteTimezones[country] || 'Asia/Shanghai';
            const siteStartDate = convertToSiteTime(startDate, timezone);
            const siteEndDate = convertToSiteTime(endDate, timezone);
            
            console.log(`${country} 站点 (${sids.length}个店铺):`);
            console.log(`  时区: ${timezone}`);
            console.log(`  查询时间: ${siteStartDate} 到 ${siteEndDate}`);
            
            const orders = await fetchOrdersByDateType(siteStartDate, siteEndDate, sids, 1, `订购时间-${country}站`);
            allOrders.push(...orders);
        }
        
        // 去重
        const orderMap = new Map();
        allOrders.forEach(order => {
            orderMap.set(order.amazon_order_id, order);
        });
        
        const orders = Array.from(orderMap.values());
        console.log(`\n=== 结果 ===`);
        console.log(`总计: ${orders.length} 条订单`);
        
        if (orders.length > 0) {
            const csv = ordersToCSV(orders);
            
            const outputDir = path.join(__dirname, 'output');
            if (!fs.existsSync(outputDir)) {
                fs.mkdirSync(outputDir);
            }
            
            const beijingDate = new Date().toLocaleString('zh-CN', {
                timeZone: 'Asia/Shanghai',
                year: 'numeric',
                month: '2-digit',
                day: '2-digit'
            }).replace(/\//g, '-');
            const filename = path.join(outputDir, `amazon_orders_${beijingDate}.csv`);
            fs.writeFileSync(filename, '\ufeff' + csv);
            console.log(`\n成功导出 ${orders.length} 条订单到: ${filename}`);
        } else {
            console.log('\n未找到订单');
        }
    } catch (err) {
        console.error('\n获取订单失败:', err.message);
        console.error('进度已保存，可以重新运行脚本继续获取');
        process.exit(1);
    }
}

main().catch(err => {
    console.error('错误:', err.message);
    process.exit(1);
});