e795e8584d
- scripts/2026-08-03-load-husqvarna-cart.ts: parses two cart screenshots into a stock_purchase draft. - scripts/2026-08-03-load-yg1-quote-soq078608.ts: extracts YG-1 quote SOQ078608 into a stock_purchase draft. - scripts/2026-08-03-merge-yg1-drafts.ts: consolidates the new YG-1 draft with the existing PO26073102STK into a single PO26080301STK. Source images (img1_*.png, img2_*.png) are kept alongside the husqvarna loader so the parse step is reproducible. The stock-orders-after.png screenshot documents the page state after the recent wrap + PDF-download fixes. Co-Authored-By: Claude <noreply@anthropic.com> Co-authored-by: Orca <help@stably.ai>
239 lines
11 KiB
TypeScript
239 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);
|
||
});
|