54 lines
1.8 KiB
JavaScript
54 lines
1.8 KiB
JavaScript
|
import { DynamoDBClient } from "@aws-sdk/client-dynamodb";
|
||
|
|
import { DynamoDBDocumentClient, ScanCommand } from "@aws-sdk/lib-dynamodb";
|
||
|
|
import { readFileSync, writeFileSync, mkdirSync } from "fs";
|
||
|
|
import { execSync } from "child_process";
|
||
|
|
|
||
|
|
mkdirSync("output", { recursive: true });
|
||
|
|
|
||
|
|
// Get all PO numbers from DynamoDB
|
||
|
|
const client = new DynamoDBClient({ region: "us-east-1" });
|
||
|
|
const docClient = DynamoDBDocumentClient.from(client);
|
||
|
|
|
||
|
|
const dbPOs = new Set();
|
||
|
|
let lastKey = undefined;
|
||
|
|
|
||
|
|
console.log("Scanning DynamoDB for existing PO numbers...");
|
||
|
|
while (true) {
|
||
|
|
const resp = await docClient.send(
|
||
|
|
new ScanCommand({
|
||
|
|
TableName: "purchase-orders",
|
||
|
|
ProjectionExpression: "po_number",
|
||
|
|
ExclusiveStartKey: lastKey,
|
||
|
|
})
|
||
|
|
);
|
||
|
|
for (const item of resp.Items) {
|
||
|
|
dbPOs.add(item.po_number);
|
||
|
|
}
|
||
|
|
lastKey = resp.LastEvaluatedKey;
|
||
|
|
if (!lastKey) break;
|
||
|
|
}
|
||
|
|
console.log(`Found ${dbPOs.size} POs in DynamoDB`);
|
||
|
|
|
||
|
|
// Get POs from invoice spreadsheet using python helper
|
||
|
|
console.log("Extracting POs from invoice spreadsheet...");
|
||
|
|
const pyScript = [
|
||
|
|
"import openpyxl, json",
|
||
|
|
'wb = openpyxl.load_workbook("/Users/adammoussa/Documents/working-docs/plumbing-spend/invoices-2025.xlsx")',
|
||
|
|
"ws = wb.active",
|
||
|
|
"pos = set()",
|
||
|
|
"for row in ws.iter_rows(min_row=2, values_only=True):",
|
||
|
|
" if row[1]: pos.add(str(row[1]).strip())",
|
||
|
|
"print(json.dumps(sorted(list(pos))))",
|
||
|
|
].join("\n");
|
||
|
|
const invoicePOs = JSON.parse(
|
||
|
|
execSync(`python3.12 -c "${pyScript.replace(/"/g, '\\"')}"`, { encoding: "utf-8" })
|
||
|
|
);
|
||
|
|
console.log(`Found ${invoicePOs.length} unique POs in invoice file`);
|
||
|
|
|
||
|
|
// Find POs not in DB
|
||
|
|
const notInDb = invoicePOs.filter((po) => !dbPOs.has(po));
|
||
|
|
console.log(`POs not in DynamoDB: ${notInDb.length}`);
|
||
|
|
|
||
|
|
writeFileSync("output/po-list.json", JSON.stringify(notInDb, null, 2));
|
||
|
|
console.log("Saved to output/po-list.json");
|