240 lines
10 KiB
JavaScript
240 lines
10 KiB
JavaScript
/**
|
||
* import-invoices-2025.js
|
||
*
|
||
* Imports all 2025 invoices (with line items) from QBO into the local system.
|
||
* Idempotent — safe to re-run. Does not modify existing invoices.
|
||
*
|
||
* Usage:
|
||
* node import-invoices-2025.js --dry-run # preview only, no writes
|
||
* node import-invoices-2025.js # actual import
|
||
*/
|
||
require('dotenv').config();
|
||
const { getOAuthClient, makeQboApiCall } = require('./qbo_helper');
|
||
const { getQboBaseUrl } = require('./src/config/qbo');
|
||
const { pool } = require('./src/config/database');
|
||
|
||
const DRY_RUN = process.argv.includes('--dry-run');
|
||
|
||
function argVal(flag) {
|
||
const arg = process.argv.find(a => a.startsWith(`--${flag}=`));
|
||
return arg ? arg.split('=')[1] : null;
|
||
}
|
||
const START_DATE = argVal('start') || '2025-01-01';
|
||
const END_DATE = argVal('end') || '2025-12-31';
|
||
const PAGE_SIZE = 500;
|
||
|
||
// ─── Helpers ──────────────────────────────────────────────────────────
|
||
|
||
function getCompanyId() {
|
||
return getOAuthClient().getToken().realmId;
|
||
}
|
||
|
||
function parseNum(s) { const n = parseFloat(String(s).replace(/[^0-9.]/g, '')); return isFinite(n) ? n : 0; }
|
||
|
||
function fmtMoney(n) { return '$' + Number(n).toFixed(2); }
|
||
|
||
function getJson(response) {
|
||
return response.getJson ? response.getJson() : response.json;
|
||
}
|
||
|
||
// ─── QBO Paginated Query ─────────────────────────────────────────────
|
||
|
||
async function queryAll(entity, where) {
|
||
const companyId = getCompanyId();
|
||
const baseUrl = getQboBaseUrl();
|
||
const wherePart = ` WHERE ${where}`;
|
||
const all = [];
|
||
let start = 1;
|
||
while (true) {
|
||
const sql = `SELECT * FROM ${entity}${wherePart} STARTPOSITION ${start} MAXRESULTS ${PAGE_SIZE}`;
|
||
const url = `${baseUrl}/v3/company/${companyId}/query?query=${encodeURIComponent(sql)}&minorversion=75`;
|
||
const resp = await makeQboApiCall({ url, method: 'GET' });
|
||
const data = getJson(resp);
|
||
if (data.Fault && data.Fault.Error) {
|
||
const msg = data.Fault.Error.map(e => `${e.code}: ${e.Message}`).join('; ');
|
||
throw new Error(`QBO ${entity} query failed: ${msg}`);
|
||
}
|
||
const list = (data.QueryResponse && data.QueryResponse[entity]) || [];
|
||
all.push(...list);
|
||
if (list.length < PAGE_SIZE) break;
|
||
start += list.length;
|
||
console.log(` │ ... fetched ${all.length} ${entity}s so far (page ${Math.ceil(start / PAGE_SIZE)})`);
|
||
}
|
||
return all;
|
||
}
|
||
|
||
// ─── Main ─────────────────────────────────────────────────────────────
|
||
|
||
(async () => {
|
||
console.log(DRY_RUN
|
||
? `🔍 DRY RUN — ${START_DATE} to ${END_DATE} — no writes will be made\n`
|
||
: `📝 LIVE IMPORT — ${START_DATE} to ${END_DATE} — writing to database\n`);
|
||
|
||
// 1. Load customer map (QBO customer ID → local customer ID)
|
||
const custRes = await pool.query('SELECT id, qbo_id, name FROM customers WHERE qbo_id IS NOT NULL');
|
||
const customerMap = new Map(); // qbo_id → { id, name }
|
||
for (const c of custRes.rows) customerMap.set(c.qbo_id, { id: c.id, name: c.name });
|
||
console.log(`👥 ${customerMap.size} customers with QBO IDs loaded`);
|
||
|
||
// 2. Load existing invoice IDs for deduplication
|
||
const existRes = await pool.query(
|
||
'SELECT id, invoice_number, qbo_id FROM invoices'
|
||
);
|
||
const existByNumber = new Map(); // invoice_number → id
|
||
const existByQboId = new Map(); // qbo_id → id
|
||
for (const r of existRes.rows) {
|
||
if (r.invoice_number) existByNumber.set(String(r.invoice_number).trim(), r.id);
|
||
if (r.qbo_id) existByQboId.set(String(r.qbo_id), r.id);
|
||
}
|
||
console.log(`📋 ${existRes.rows.length} existing invoices in local DB`);
|
||
|
||
// 3. Fetch all 2025 invoices from QBO
|
||
console.log(`\n📡 Fetching QBO Invoices ${START_DATE} – ${END_DATE}...`);
|
||
const qboInvoices = await queryAll('Invoice',
|
||
`TxnDate >= '${START_DATE}' AND TxnDate <= '${END_DATE}'`
|
||
);
|
||
console.log(` ← ${qboInvoices.length} invoices returned by QBO\n`);
|
||
|
||
// 4. Process each invoice
|
||
let stats = { imported: 0, skippedDuplicate: 0, skippedNoCustomer: 0, error: 0, lineItems: 0 };
|
||
|
||
for (const inv of qboInvoices) {
|
||
const docNumber = String(inv.DocNumber || '').trim();
|
||
|
||
// ── Dedup checks ──
|
||
if (docNumber && existByNumber.has(docNumber)) {
|
||
console.log(`SKIP (dup by DocNumber): #${docNumber} ${inv.CustomerRef?.name || '?'} ${fmtMoney(parseNum(inv.TotalAmt))} ${inv.TxnDate}`);
|
||
stats.skippedDuplicate++;
|
||
continue;
|
||
}
|
||
const qboId = String(inv.Id || '');
|
||
if (qboId && existByQboId.has(qboId)) {
|
||
console.log(`SKIP (dup by QBO ID): #${docNumber} ${inv.CustomerRef?.name || '?'} ${fmtMoney(parseNum(inv.TotalAmt))} ${inv.TxnDate}`);
|
||
stats.skippedDuplicate++;
|
||
continue;
|
||
}
|
||
|
||
// ── Customer match ──
|
||
const custQboId = inv.CustomerRef?.value;
|
||
const cust = customerMap.get(String(custQboId));
|
||
if (!cust) {
|
||
console.log(`SKIP (no customer): #${docNumber} "${inv.CustomerRef?.name || '?'}" (QBO ID ${custQboId}) ${fmtMoney(parseNum(inv.TotalAmt))}`);
|
||
stats.skippedNoCustomer++;
|
||
continue;
|
||
}
|
||
|
||
// ── Tax ──
|
||
const totalTax = parseNum(inv.TxnTaxDetail?.TotalTax);
|
||
const totalAmt = parseNum(inv.TotalAmt);
|
||
const subtotal = totalAmt - totalTax;
|
||
const taxExempt = totalTax === 0;
|
||
const taxRate = subtotal > 0 ? (totalTax / subtotal * 100) : 8.25;
|
||
|
||
// ── Payment status ──
|
||
const balance = parseNum(inv.Balance);
|
||
let paidDate = null, paymentStatus = null;
|
||
if (Math.abs(balance) < 0.005) {
|
||
paidDate = inv.TxnDate; // use invoice date as paid-date proxy
|
||
paymentStatus = 'Paid';
|
||
} else if (balance < totalAmt - 0.005) {
|
||
paymentStatus = 'Partial';
|
||
}
|
||
|
||
// ── Line items ──
|
||
const lines = inv.Line || [];
|
||
const saleLines = lines.filter(l => l.DetailType === 'SalesItemLineDetail');
|
||
|
||
if (DRY_RUN) {
|
||
console.log(`IMPORT: #${docNumber} ${cust.name} ${fmtMoney(totalAmt)} ${inv.TxnDate} ${saleLines.length} items`);
|
||
stats.imported++;
|
||
stats.lineItems += saleLines.length;
|
||
continue;
|
||
}
|
||
|
||
// ── INSERT ──
|
||
const pgClient = await pool.connect();
|
||
try {
|
||
await pgClient.query('BEGIN');
|
||
|
||
const invRes = await pgClient.query(
|
||
`INSERT INTO invoices
|
||
(invoice_number, customer_id, invoice_date, terms, auth_code,
|
||
tax_exempt, tax_rate, subtotal, tax_amount, total,
|
||
qbo_id, qbo_sync_token, qbo_doc_number,
|
||
paid_date, payment_status, source)
|
||
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)
|
||
RETURNING id`,
|
||
[
|
||
docNumber || null,
|
||
cust.id,
|
||
inv.TxnDate,
|
||
inv.SalesTermRef?.name || 'Net 14',
|
||
inv.CustomerMemo?.value || null,
|
||
taxExempt,
|
||
taxRate,
|
||
subtotal,
|
||
totalTax,
|
||
totalAmt,
|
||
inv.Id,
|
||
inv.SyncToken,
|
||
docNumber || null,
|
||
paidDate,
|
||
paymentStatus,
|
||
'imported'
|
||
]
|
||
);
|
||
const invoiceId = invRes.rows[0].id;
|
||
|
||
for (let i = 0; i < saleLines.length; i++) {
|
||
const line = saleLines[i];
|
||
const detail = line.SalesItemLineDetail || {};
|
||
const itemRefValue = String(detail.ItemRef?.value || '');
|
||
let qboItemId = '9';
|
||
if (itemRefValue === '5') qboItemId = '5';
|
||
if (itemRefValue === '115') qboItemId = '115';
|
||
|
||
await pgClient.query(
|
||
`INSERT INTO invoice_items
|
||
(invoice_id, quantity, description, rate, amount, item_order, qbo_item_id)
|
||
VALUES ($1,$2,$3,$4,$5,$6,$7)`,
|
||
[
|
||
invoiceId,
|
||
String(detail.Qty || 1),
|
||
line.Description || '',
|
||
String(detail.UnitPrice || 0),
|
||
String(line.Amount || 0),
|
||
i,
|
||
qboItemId
|
||
]
|
||
);
|
||
}
|
||
|
||
await pgClient.query('COMMIT');
|
||
console.log(`IMPORT: #${docNumber} ${cust.name} ${fmtMoney(totalAmt)} ${inv.TxnDate} ${saleLines.length} items`);
|
||
stats.imported++;
|
||
stats.lineItems += saleLines.length;
|
||
} catch (e) {
|
||
await pgClient.query('ROLLBACK').catch(() => {});
|
||
console.error(`ERROR: #${docNumber} — ${e.message}`);
|
||
stats.error++;
|
||
} finally {
|
||
pgClient.release();
|
||
}
|
||
}
|
||
|
||
// ── Summary ──
|
||
console.log(`\n=== IMPORT ${DRY_RUN ? '(DRY RUN)' : ''} SUMMARY — ${START_DATE} to ${END_DATE} ===`);
|
||
console.log(`Total QBO invoices queried: ${qboInvoices.length}`);
|
||
console.log(`Imported: ${stats.imported} (${stats.lineItems} line items)`);
|
||
console.log(`Skipped (duplicate): ${stats.skippedDuplicate}`);
|
||
console.log(`Skipped (no customer): ${stats.skippedNoCustomer}`);
|
||
console.log(`Errors: ${stats.error}`);
|
||
if (DRY_RUN) console.log('\n🔍 No data written — run without --dry-run to import.');
|
||
|
||
await pool.end();
|
||
process.exit(0);
|
||
})().catch(err => {
|
||
console.error('FATAL:', err.message);
|
||
process.exit(1);
|
||
});
|