240 lines
11 KiB
TypeScript
240 lines
11 KiB
TypeScript
|
/**
|
|||
|
|
* One-time loader — YG-1 quote SOQ078608 → stock_purchase draft.
|
|||
|
|
*
|
|||
|
|
* Source PDF: /Volumes/KIOXIA/Downloads/SOQ078608(CNL001)(YG-1 South Africa PTY LTD.)(2026-08-03).pdf
|
|||
|
|
* Supplier: YG-1 (id `udpqcpsr9bfpfgx`)
|
|||
|
|
* Customer: CNL001 (CNL Industrial Mining Supplies) — informational only;
|
|||
|
|
* stock_purchases are NOT tied to a customer order.
|
|||
|
|
* Quote date: 2026-08-03
|
|||
|
|
* Items: 16 line items (T7317249, TC187246, …, E2545140)
|
|||
|
|
* Total: R 10 651.11 incl (R 9 261.84 excl, R 1 389.27 VAT)
|
|||
|
|
*
|
|||
|
|
* ## Line items
|
|||
|
|
*
|
|||
|
|
* Parsed from the PDF by eyeballing the column layout. The "SET 2" /
|
|||
|
|
* "2SET" embed in the descriptions makes raw text extraction
|
|||
|
|
* ambiguous about quantity (the "2" is both a literal "2-tap set"
|
|||
|
|
* marker AND sometimes the quantity), so the quantities below were
|
|||
|
|
* verified against the math: `qty × unit × (1 − disc) × 1.15 == total_incl`.
|
|||
|
|
* The total also sums to the PDF's footer (R 9 261.84 excl).
|
|||
|
|
*
|
|||
|
|
* What this does:
|
|||
|
|
* 1. Validates each line's math against the PDF totals.
|
|||
|
|
* 2. Creates 16 new stock_items (none of these YG-1 SKUs exist yet —
|
|||
|
|
* verified against the running PB before the script started).
|
|||
|
|
* 3. Creates one stock_purchase with status="draft",
|
|||
|
|
* purchase_number = `PO{yy}{mm}{dd}{seq}STK` (today's first in the day).
|
|||
|
|
* 4. Creates 16 stock_purchase_items with cost_price = post-discount
|
|||
|
|
* per-unit price (this is what we'll actually pay, ignoring the
|
|||
|
|
* supplier's discount).
|
|||
|
|
*
|
|||
|
|
* Re-run safety: the script re-checks PB for an existing stock_item
|
|||
|
|
* with the same SKU before creating — if you re-run it after a partial
|
|||
|
|
* fail, the existing item rows are reused (idempotent on stock_items).
|
|||
|
|
* It does NOT dedupe against an existing draft; if you re-run after a
|
|||
|
|
* successful run you'll get a SECOND draft. Don't re-run after success.
|
|||
|
|
*
|
|||
|
|
* Run: `npx tsx scripts/2026-08-03-load-yg1-quote-soq078608.ts`
|
|||
|
|
*/
|
|||
|
|
import PocketBase from "pocketbase";
|
|||
|
|
|
|||
|
|
const PB_URL = "https://pocketbase-gnqc88suy8fqk6wyryjjdpvl.169.239.180.130.sslip.io";
|
|||
|
|
const ADMIN_EMAIL = "david.els1@outlook.com";
|
|||
|
|
const ADMIN_PASSWORD = "1234567890";
|
|||
|
|
|
|||
|
|
const YG1_SUPPLIER_ID = "udpqcpsr9bfpfgx";
|
|||
|
|
|
|||
|
|
/* ============================================================================
|
|||
|
|
* Parsed line items (verified by math, see header)
|
|||
|
|
* ========================================================================== */
|
|||
|
|
|
|||
|
|
interface ParsedLine {
|
|||
|
|
sku: string;
|
|||
|
|
description: string;
|
|||
|
|
quantity: number;
|
|||
|
|
unit_price_ex: number;
|
|||
|
|
disc_pct: number;
|
|||
|
|
pdf_total_incl: number;
|
|||
|
|
no_stock: boolean;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const LINES: ParsedLine[] = [
|
|||
|
|
{ sku: "T7317249", description: "HSS ISO529 HAND TAP SET 2 M4X0.7 6H 53.0L", quantity: 2, unit_price_ex: 551.04, disc_pct: 60, pdf_total_incl: 506.95, no_stock: false },
|
|||
|
|
{ sku: "TC187246", description: "HSS-E ISO529 SPIRAL FLUTE TAP M4X0.7 6H 53.0L", quantity: 2, unit_price_ex: 271.31, disc_pct: 60, pdf_total_incl: 249.61, no_stock: false },
|
|||
|
|
{ sku: "T7317319", description: "HSS ISO529 HAND TAP SET 2 M6X1.0 6H 66.0L", quantity: 2, unit_price_ex: 455.12, disc_pct: 60, pdf_total_incl: 418.72, no_stock: false },
|
|||
|
|
{ sku: "TC804316", description: "HSS-E COMBO DIN371 SPIRAL FLUTE TAP M6X1.0 6H 80.0L", quantity: 2, unit_price_ex: 383.59, disc_pct: 60, pdf_total_incl: 352.90, no_stock: false },
|
|||
|
|
{ sku: "TS2352902", description: "HSS ISO529 HAND TAP 2SET M8X1.0 6H 72.0L", quantity: 2, unit_price_ex: 465.18, disc_pct: 60, pdf_total_incl: 427.96, no_stock: false },
|
|||
|
|
{ sku: "T7317369", description: "HSS ISO529 HAND TAP SET 2 M8X1.25 6H 72.0L", quantity: 2, unit_price_ex: 692.74, disc_pct: 60, pdf_total_incl: 637.32, no_stock: false },
|
|||
|
|
{ sku: "T7317429", description: "HSS ISO529 HAND TAP SET 2 M10X1.5 6H 80.0L", quantity: 1, unit_price_ex: 802.20, disc_pct: 60, pdf_total_incl: 369.01, no_stock: false },
|
|||
|
|
{ sku: "TS2352932", description: "HSS ISO529 HAND TAP 2SET M10X1.25 6H 80.0L", quantity: 1, unit_price_ex: 567.84, disc_pct: 60, pdf_total_incl: 261.21, no_stock: true },
|
|||
|
|
{ sku: "T7317509", description: "HSS ISO529 HAND TAP SET 2 M12X1.75 6H 89.0L", quantity: 1, unit_price_ex: 1152.19, disc_pct: 60, pdf_total_incl: 530.01, no_stock: false },
|
|||
|
|
{ sku: "TC844556", description: "HSS-E COMBO DIN374 SPIRAL FLUTE TAP M14X1.5 6H 100.0L", quantity: 1, unit_price_ex: 1100.35, disc_pct: 60, pdf_total_incl: 506.16, no_stock: false },
|
|||
|
|
{ sku: "T7317709", description: "HSS ISO529 HAND TAP SET 2 M20X2.5 6H 112.0L", quantity: 2, unit_price_ex: 3104.01, disc_pct: 60, pdf_total_incl: 2855.69, no_stock: false },
|
|||
|
|
{ sku: "TS1551712", description: "HSS ISO529 BSP PIPE HAND TAP 2SET G1/4-19 67.0L", quantity: 2, unit_price_ex: 1061.43, disc_pct: 60, pdf_total_incl: 976.51, no_stock: false },
|
|||
|
|
{ sku: "D1205200", description: "HSS(M2) Jobber Taper Shank Drill Steam Homo DIN345 20XMT#2X140X238", quantity: 2, unit_price_ex: 1270.60, disc_pct: 70, pdf_total_incl: 876.71, no_stock: false },
|
|||
|
|
{ sku: "E2545060", description: "HSSCo8(M42) 4FL 30deg. Helix Short E/M 6X6X16X60.5", quantity: 3, unit_price_ex: 265.21, disc_pct: 60, pdf_total_incl: 365.99, no_stock: false },
|
|||
|
|
{ sku: "E2502080", description: "HSSCo8(M42) 2FL 30deg. Helix Short E/M 8X10X11X61", quantity: 4, unit_price_ex: 371.60, disc_pct: 60, pdf_total_incl: 683.74, no_stock: false },
|
|||
|
|
{ sku: "E2545140", description: "HSSCo8(M42) 4FL 30deg. Helix Short E/M 14X12X28.5X73", quantity: 2, unit_price_ex: 687.63, disc_pct: 60, pdf_total_incl: 632.62, no_stock: false },
|
|||
|
|
];
|
|||
|
|
|
|||
|
|
const PDF_TOTALS = { excl: 9261.84, vat: 1389.27, incl: 10651.11 };
|
|||
|
|
|
|||
|
|
/* ============================================================================
|
|||
|
|
* Math validation
|
|||
|
|
* ========================================================================== */
|
|||
|
|
|
|||
|
|
function validateLines(lines: ParsedLine[]): void {
|
|||
|
|
let sumExcl = 0;
|
|||
|
|
let sumVat = 0;
|
|||
|
|
let sumIncl = 0;
|
|||
|
|
for (const l of lines) {
|
|||
|
|
const excl = l.quantity * l.unit_price_ex * (1 - l.disc_pct / 100);
|
|||
|
|
const vat = excl * 0.15;
|
|||
|
|
const incl = excl + vat;
|
|||
|
|
sumExcl += excl;
|
|||
|
|
sumVat += vat;
|
|||
|
|
sumIncl += incl;
|
|||
|
|
const expectedIncl = Math.round(l.quantity * l.unit_price_ex * (1 - l.disc_pct / 100) * 1.15 * 100) / 100;
|
|||
|
|
const diff = Math.abs(expectedIncl - l.pdf_total_incl);
|
|||
|
|
if (diff > 0.05) {
|
|||
|
|
throw new Error(
|
|||
|
|
`Line ${l.sku} math mismatch: expected total ${expectedIncl.toFixed(2)}, PDF says ${l.pdf_total_incl.toFixed(2)}, diff ${diff.toFixed(2)}`,
|
|||
|
|
);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
sumExcl = Math.round(sumExcl * 100) / 100;
|
|||
|
|
sumVat = Math.round(sumVat * 100) / 100;
|
|||
|
|
sumIncl = Math.round(sumIncl * 100) / 100;
|
|||
|
|
console.log(` Sum excl: R ${sumExcl.toFixed(2)} (PDF says ${PDF_TOTALS.excl.toFixed(2)})`);
|
|||
|
|
console.log(` Sum vat: R ${sumVat.toFixed(2)} (PDF says ${PDF_TOTALS.vat.toFixed(2)})`);
|
|||
|
|
console.log(` Sum incl: R ${sumIncl.toFixed(2)} (PDF says ${PDF_TOTALS.incl.toFixed(2)})`);
|
|||
|
|
if (
|
|||
|
|
Math.abs(sumExcl - PDF_TOTALS.excl) > 0.05 ||
|
|||
|
|
Math.abs(sumVat - PDF_TOTALS.vat) > 0.05 ||
|
|||
|
|
Math.abs(sumIncl - PDF_TOTALS.incl) > 0.05
|
|||
|
|
) {
|
|||
|
|
throw new Error("Footer totals do not match parsed lines");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/* ============================================================================
|
|||
|
|
* PB utilities
|
|||
|
|
* ========================================================================== */
|
|||
|
|
|
|||
|
|
async function ensureStockItem(
|
|||
|
|
pb: PocketBase,
|
|||
|
|
sku: string,
|
|||
|
|
description: string,
|
|||
|
|
supplierId: string,
|
|||
|
|
): Promise<{ id: string; created: boolean }> {
|
|||
|
|
const existing = await pb.collection("stock_items").getList(1, 1, {
|
|||
|
|
filter: `sku = "${sku}"`,
|
|||
|
|
fields: "id",
|
|||
|
|
});
|
|||
|
|
if (existing.items.length > 0) {
|
|||
|
|
return { id: String(existing.items[0].id), created: false };
|
|||
|
|
}
|
|||
|
|
const created = await pb.collection("stock_items").create({
|
|||
|
|
sku,
|
|||
|
|
name: description,
|
|||
|
|
description,
|
|||
|
|
unit: "SET",
|
|||
|
|
current_qty: 0,
|
|||
|
|
min_qty: 0,
|
|||
|
|
target_qty: 0,
|
|||
|
|
is_active: true,
|
|||
|
|
supplier: supplierId,
|
|||
|
|
location: "",
|
|||
|
|
notes: "",
|
|||
|
|
});
|
|||
|
|
return { id: String(created.id), created: true };
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
async function generateDraftNumber(pb: PocketBase): Promise<string> {
|
|||
|
|
const now = new Date();
|
|||
|
|
const yy = String(now.getFullYear()).slice(-2);
|
|||
|
|
const mm = String(now.getMonth() + 1).padStart(2, "0");
|
|||
|
|
const dd = String(now.getDate()).padStart(2, "0");
|
|||
|
|
const prefix = `PO${yy}${mm}${dd}`;
|
|||
|
|
const rows = await pb.collection("stock_purchases").getFullList({
|
|||
|
|
filter: `purchase_number ~ "${prefix}"`,
|
|||
|
|
fields: "purchase_number",
|
|||
|
|
});
|
|||
|
|
let max = 0;
|
|||
|
|
for (const r of rows) {
|
|||
|
|
const m = String(r.purchase_number ?? "").match(/^PO\d{6}(\d{2})STK$/);
|
|||
|
|
if (m) {
|
|||
|
|
const n = parseInt(m[1], 10);
|
|||
|
|
if (Number.isFinite(n) && n > max) max = n;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
const seq = String(max + 1).padStart(2, "0");
|
|||
|
|
return `${prefix}${seq}STK`;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/* ============================================================================
|
|||
|
|
* Main
|
|||
|
|
* ========================================================================== */
|
|||
|
|
|
|||
|
|
async function main() {
|
|||
|
|
console.log("=== 1. Validating math against PDF ===");
|
|||
|
|
validateLines(LINES);
|
|||
|
|
|
|||
|
|
console.log(`\n=== 2. Connecting to PocketBase (${PB_URL}) ===`);
|
|||
|
|
const pb = new PocketBase(PB_URL);
|
|||
|
|
await pb.collection("_superusers").authWithPassword(ADMIN_EMAIL, ADMIN_PASSWORD);
|
|||
|
|
console.log(" Authenticated as admin");
|
|||
|
|
|
|||
|
|
console.log("\n=== 3. Creating stock_items ===");
|
|||
|
|
const itemIds: string[] = [];
|
|||
|
|
for (const l of LINES) {
|
|||
|
|
const { id, created } = await ensureStockItem(pb, l.sku, l.description, YG1_SUPPLIER_ID);
|
|||
|
|
console.log(` ${created ? "+" : "="} ${l.sku.padEnd(10)} → ${id} ${created ? "" : "(already existed)"}`);
|
|||
|
|
itemIds.push(id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
console.log("\n=== 4. Creating stock_purchase draft ===");
|
|||
|
|
const purchaseNumber = await generateDraftNumber(pb);
|
|||
|
|
console.log(` purchase_number: ${purchaseNumber}`);
|
|||
|
|
const header = await pb.collection("stock_purchases").create({
|
|||
|
|
purchase_number: purchaseNumber,
|
|||
|
|
supplier: YG1_SUPPLIER_ID,
|
|||
|
|
status: "draft",
|
|||
|
|
notes: `Loaded from YG-1 quote SOQ078608 dated 2026-08-03 (customer CNL001). ${LINES.length} line items.`,
|
|||
|
|
email_error: "",
|
|||
|
|
email_message_id: "",
|
|||
|
|
email_payload: null,
|
|||
|
|
});
|
|||
|
|
const purchaseId = String(header.id);
|
|||
|
|
console.log(` draft id: ${purchaseId}`);
|
|||
|
|
|
|||
|
|
console.log("\n=== 5. Creating stock_purchase_items ===");
|
|||
|
|
for (let i = 0; i < LINES.length; i++) {
|
|||
|
|
const l = LINES[i];
|
|||
|
|
const costPrice = Math.round(l.unit_price_ex * (1 - l.disc_pct / 100) * 100) / 100;
|
|||
|
|
const line = await pb.collection("stock_purchase_items").create({
|
|||
|
|
purchase: purchaseId,
|
|||
|
|
stock_item: itemIds[i],
|
|||
|
|
description: l.description,
|
|||
|
|
quantity: l.quantity,
|
|||
|
|
quantity_received: 0,
|
|||
|
|
cost_price: costPrice,
|
|||
|
|
notes: l.no_stock ? "No stock (per YG-1 quote SOQ078608)" : "",
|
|||
|
|
});
|
|||
|
|
const flag = l.no_stock ? " ← NO STOCK" : "";
|
|||
|
|
console.log(` + ${l.sku.padEnd(10)} qty=${String(l.quantity).padStart(2)} cost=R ${costPrice.toFixed(2).padStart(8)} line=${line.id}${flag}`);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
console.log("\n=== Done ===");
|
|||
|
|
const totalCost = LINES.reduce((s, l) => s + l.quantity * l.unit_price_ex * (1 - l.disc_pct / 100), 0);
|
|||
|
|
console.log(` Draft stock purchase: ${purchaseNumber} (id ${purchaseId})`);
|
|||
|
|
console.log(` Lines: ${LINES.length}`);
|
|||
|
|
console.log(` Total cost (post-discount, excl VAT): R ${totalCost.toFixed(2)}`);
|
|||
|
|
console.log(` Supplier: YG-1 (${YG1_SUPPLIER_ID})`);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
main().catch((err) => {
|
|||
|
|
console.error("FATAL:", err);
|
|||
|
|
process.exit(1);
|
|||
|
|
});
|