[{"_1":2,"_180":-5,"_181":-5},"loaderData",{"_3":4,"_95":96},"root",{"_5":6,"_36":37,"_68":69},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesChatPanelState","notificationCount",0,"flagDenols","isBusinessOrgMember","ENV",{"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63,"_64":65,"_66":67},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","f33c6f02e3b9304fe5cb194294cedf6a64b697a5","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","API_URL","https://api.val.town","clerkState",{"_70":71},"__internal_clerk_state",{"_72":73,"_74":47,"_75":76,"_77":76,"_78":20,"_79":76,"_80":76,"_81":76,"_82":76,"_83":84,"_93":20,"_94":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_85":20,"_19":20,"_86":76,"_87":88,"_89":76,"_90":47,"_91":20,"_92":76},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_97":98,"_99":100,"_101":102,"_103":104,"_157":158,"_177":178,"_179":-5},"handle","abhishekplating","name","PlatingPro","path","ppSales.ts","project",{"_105":106,"_99":100,"_107":108,"_109":-5,"_110":20,"_111":112,"_113":114,"_115":-5,"_12":116,"_117":-5,"_118":20,"_119":-5,"_120":-5,"_121":33,"_14":122,"_123":-5,"_124":-5,"_125":-5,"_126":127,"_128":127,"_129":130,"_131":130,"_132":130,"_133":127,"_134":127,"_23":135,"_138":139,"_143":144,"_156":20},"id","1fffa610-245e-11f1-b73d-42dde27851f2","createdAt",["D",1774012210756],"deletedAt","legacyEnvVars","privacy","public","description","V1","imageUrl","019ca7d6-bbec-76d6-822b-6e2c39ed1dbd","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","019ca7d6-bbec-76d6-822b-72f395b7c435","tursoName","tursoHostname","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_97":98,"_136":137},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzNBS1hsRkxLQ2d3eHdUMVYzU3p3YTNmakRRbSJ9","org",{"_97":98,"_140":137,"_12":116,"_141":142},"avatar","tier","free","branch",{"_145":146,"_99":45,"_105":147,"_148":106,"_149":-5,"_150":-5,"_151":-5,"_152":-5,"_153":-5,"_154":100,"_155":98},"version",104,"20008210-245e-11f1-b73d-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_105":159,"_99":102,"_160":161,"_162":-5,"_163":147,"_164":165,"_166":167,"_107":168,"_145":169,"_170":171,"_172":173,"_174":20,"_175":-5,"_101":176},"d18d6812-29f5-11f1-9157-42dde27851f2","fileId","0f23d982-2810-11f1-ae9d-42dde27851f2","parentId","branchId","branchVersion",94,"latest",true,["D",1774627118446],12,"fileType","script","content","// PlatingPro — Sales Core Module (V20)\n// Customers, Invoices, Dashboard, KG metrics\nimport { err, ok, sql, audit, isValidDate, requireFields } from \"./ppShared.ts\";\n\nexport async function salesPing(req?: Request): Promise {\n return ok({ sales: \"ok\", v: 20 }, req);\n}\n\nexport async function migrateSales(req?: Request): Promise {\n try {\n await sql`CREATE TABLE IF NOT EXISTS pp_sales_customers(id SERIAL PRIMARY KEY,name TEXT UNIQUE NOT NULL,gstin TEXT,factory TEXT,address TEXT,phone TEXT,created_by TEXT,created_at TIMESTAMPTZ DEFAULT NOW(),updated_at TIMESTAMPTZ DEFAULT NOW())`;\n await sql`CREATE TABLE IF NOT EXISTS pp_sales_invoices(id SERIAL PRIMARY KEY,inv_no TEXT UNIQUE NOT NULL,inv_date DATE NOT NULL,customer_id INT,customer_name TEXT NOT NULL,factory TEXT,taxable NUMERIC(12,2) DEFAULT 0,cgst NUMERIC(12,2) DEFAULT 0,sgst NUMERIC(12,2) DEFAULT 0,igst NUMERIC(12,2) DEFAULT 0,total NUMERIC(12,2) DEFAULT 0,source TEXT DEFAULT 'manual',notes TEXT,created_by TEXT,created_at TIMESTAMPTZ DEFAULT NOW())`;\n await sql`CREATE TABLE IF NOT EXISTS pp_sales_items(id SERIAL PRIMARY KEY,invoice_id INT,product_name TEXT NOT NULL,hsn TEXT,qty NUMERIC(12,2) DEFAULT 0,unit TEXT DEFAULT 'PCS',rate NUMERIC(12,4) DEFAULT 0,amount NUMERIC(12,2) DEFAULT 0,color TEXT,description TEXT)`;\n await sql`CREATE TABLE IF NOT EXISTS pp_gmail_sync(id SERIAL PRIMARY KEY,gmail_msg_id TEXT UNIQUE NOT NULL,gmail_subject TEXT,gmail_date TEXT,attachment_name TEXT,inv_no TEXT,status TEXT DEFAULT 'pending',error TEXT,invoice_id INT,scanned_data JSONB,created_at TIMESTAMPTZ DEFAULT NOW())`;\n return ok({\n success: true,\n msg: \"Sales tables ready (V20 with Gmail sync + KG)\",\n }, req);\n } catch (e: any) {\n return err(\"Migration failed: \" + e.message, 500, req);\n }\n}\n\nexport async function getSalesCustomers(req?: Request): Promise {\n const r =\n await sql`SELECT c.id,c.name,c.gstin,c.factory,c.address,c.phone,c.created_at,c.updated_at,COALESCE((SELECT SUM(i.total)::float FROM pp_sales_invoices i WHERE i.customer_id=c.id),0)as total_billing,COALESCE((SELECT COUNT(*)::int FROM pp_sales_invoices i WHERE i.customer_id=c.id),0)as inv_count FROM pp_sales_customers c WHERE COALESCE(c.is_deleted,false)=false ORDER BY total_billing DESC`;\n return ok({ customers: r }, req);\n}\n\nexport async function upsertSalesCustomer(b: any, req?: Request): Promise {\n if (!b.name) return err(\"name required\", 400, req);\n var name = b.name.trim();\n var gstin = (b.gstin || \"\").trim().toUpperCase();\n var factory = b.factory || null;\n if (b.id) {\n await sql`UPDATE pp_sales_customers SET name=${name},gstin=${gstin},factory=${factory},address=${\n b.address || null\n },phone=${b.phone || null},updated_at=NOW() WHERE id=${b.id}`;\n if (factory) {\n await sql`UPDATE pp_sales_invoices SET factory=${factory} WHERE customer_id=${b.id}`;\n }\n return ok({ success: true }, req);\n }\n var ex =\n await sql`SELECT id FROM pp_sales_customers WHERE LOWER(TRIM(name))=LOWER(${name})`;\n if (ex.length > 0) {\n await sql`UPDATE pp_sales_customers SET gstin=COALESCE(NULLIF(${gstin},''),gstin),factory=COALESCE(${factory},factory),updated_at=NOW() WHERE id=${\n ex[0].id\n }`;\n if (factory) {\n await sql`UPDATE pp_sales_invoices SET factory=${factory} WHERE customer_id=${\n ex[0].id\n }`;\n }\n return ok({ success: true, id: ex[0].id, existed: true }, req);\n }\n var r =\n await sql`INSERT INTO pp_sales_customers(name,gstin,factory,created_by)VALUES(${name},${gstin},${factory},${\n b.created_by || \"system\"\n })RETURNING id`;\n return ok({ success: true, id: r[0].id }, req);\n}\n\nexport async function setSalesCustomerFactory(b: any, req?: Request): Promise {\n if (!b.id || !b.factory) return err(\"id and factory required\", 400, req);\n await sql`UPDATE pp_sales_customers SET factory=${b.factory},updated_at=NOW() WHERE id=${b.id}`;\n await sql`UPDATE pp_sales_invoices SET factory=${b.factory} WHERE customer_id=${b.id}`;\n return ok({ success: true }, req);\n}\n\nexport async function deleteSalesCustomer(b: any, req?: Request): Promise {\n if (!b.id) return err(\"id required\", 400, req);\n // Prevent delete if customer has invoices\n const linked = await sql`SELECT COUNT(*)::int as cnt FROM pp_sales_invoices WHERE customer_id=${b.id}`;\n if (linked[0]?.cnt > 0) return err(\"Cannot delete: customer has \" + linked[0].cnt + \" invoice(s). Remove invoices first.\", 409, req);\n // Soft delete\n await sql`UPDATE pp_sales_customers SET is_deleted=true WHERE id=${b.id}`;\n return ok({ success: true }, req);\n}\n\nexport async function saveSalesInvoice(b: any, req?: Request): Promise {\n // FIX 9: Input validation\n if (!b.inv_no || !b.inv_date || !b.customer_name)\n return err(\"inv_no, inv_date, customer_name required\", 400, req);\n if (!isValidDate(b.inv_date)) return err(\"inv_date must be YYYY-MM-DD\", 400, req);\n\n const custName = b.customer_name.trim().replace(/\\s+/g, \" \");\n const custNameNorm = custName.toLowerCase();\n const invNo = b.inv_no.trim().toUpperCase();\n\n // FIX 3: Real DB transaction using BEGIN/COMMIT/ROLLBACK\n try {\n await sql`BEGIN`;\n try {\n // Resolve or create customer (inside transaction)\n let custId = 0;\n const ex = await sql`SELECT id,factory FROM pp_sales_customers WHERE LOWER(TRIM(name))=${custNameNorm} AND COALESCE(is_deleted,false)=false`;\n if (ex.length > 0) {\n custId = ex[0].id;\n if (!b.factory) b.factory = ex[0].factory;\n } else {\n const cr = await sql`INSERT INTO pp_sales_customers(name,gstin,factory,created_by) VALUES(${custName},${(b.gstin||\"\").toUpperCase()},${b.factory||null},${\"system\"}) RETURNING id`;\n custId = cr[0].id;\n }\n\n // Check duplicate invoice number\n const dup = await sql`SELECT id FROM pp_sales_invoices WHERE inv_no=${invNo}`;\n if (dup.length > 0) {\n await sql`ROLLBACK`;\n return err(\"Invoice \" + invNo + \" already exists\", 409, req);\n }\n\n // Compute totals\n let taxable = Number(b.taxable) || 0;\n if (b.items && Array.isArray(b.items) && b.items.length > 0 && taxable === 0) {\n for (const it of b.items) taxable += Number(it.amount || 0);\n }\n const cgst = Number(b.cgst)||0, sgst = Number(b.sgst)||0, igst = Number(b.igst)||0;\n const total = Number(b.total) || (taxable + cgst + sgst + igst);\n\n // Insert invoice header\n const inv = await sql`INSERT INTO pp_sales_invoices(inv_no,inv_date,customer_id,customer_name,factory,taxable,cgst,sgst,igst,total,source,notes,created_by) VALUES(${invNo},${b.inv_date},${custId},${custName},${b.factory||null},${taxable},${cgst},${sgst},${igst},${total},${b.source||\"manual\"},${b.notes||null},${b.created_by||\"system\"}) RETURNING id`;\n const invId = inv[0].id;\n\n // Insert line items\n if (b.items && Array.isArray(b.items)) {\n for (const it of b.items) {\n const amt = Number(it.amount) || (Number(it.qty||0) * Number(it.rate||0));\n await sql`INSERT INTO pp_sales_items(invoice_id,product_name,hsn,qty,unit,rate,amount,color,description) VALUES(${invId},${it.product_name||it.name||\"\"},${it.hsn||\"\"},${Number(it.qty)||0},${it.unit||\"PCS\"},${Number(it.rate)||0},${amt},${it.color||\"\"},${it.description||\"\"})`;\n }\n }\n\n await sql`COMMIT`;\n return ok({ success: true, id: invId, customer_id: custId }, req);\n } catch (inner: any) {\n await sql`ROLLBACK`;\n return err(\"Failed to save invoice: \" + inner.message, 500, req);\n }\n } catch (outer: any) {\n return err(\"Transaction error: \" + outer.message, 500, req);\n }\n}\n\n// V20: includes total_qty (kg) per invoice via subquery join on pp_sales_items\nexport async function getSalesInvoicesByDate(p: URLSearchParams, req?: Request): Promise {\n var date = p.get(\"date\") || \"\";\n var month = p.get(\"month\") || \"\";\n var factory = p.get(\"factory\") || \"\";\n if (date && factory && factory !== \"all\") {\n return ok({\n invoices:\n await sql`SELECT i.*,c.gstin,COALESCE(iq.total_qty,0)::float as total_qty FROM pp_sales_invoices i LEFT JOIN pp_sales_customers c ON i.customer_id=c.id LEFT JOIN (SELECT invoice_id,SUM(qty)::float as total_qty FROM pp_sales_items WHERE LOWER(unit) IN ('kg','kgs','kilo','kilos','kilogram','kilograms') GROUP BY invoice_id) iq ON iq.invoice_id=i.id WHERE TO_CHAR(i.inv_date,'YYYY-MM-DD')=${date} AND i.factory=${factory} ORDER BY i.total DESC`,\n }, req);\n }\n if (date) {\n return ok({\n invoices:\n await sql`SELECT i.*,c.gstin,COALESCE(iq.total_qty,0)::float as total_qty FROM pp_sales_invoices i LEFT JOIN pp_sales_customers c ON i.customer_id=c.id LEFT JOIN (SELECT invoice_id,SUM(qty)::float as total_qty FROM pp_sales_items WHERE LOWER(unit) IN ('kg','kgs','kilo','kilos','kilogram','kilograms') GROUP BY invoice_id) iq ON iq.invoice_id=i.id WHERE TO_CHAR(i.inv_date,'YYYY-MM-DD')=${date} ORDER BY i.total DESC`,\n }, req);\n }\n if (month && factory && factory !== \"all\") {\n return ok({\n invoices:\n await sql`SELECT i.*,c.gstin,COALESCE(iq.total_qty,0)::float as total_qty FROM pp_sales_invoices i LEFT JOIN pp_sales_customers c ON i.customer_id=c.id LEFT JOIN (SELECT invoice_id,SUM(qty)::float as total_qty FROM pp_sales_items WHERE LOWER(unit) IN ('kg','kgs','kilo','kilos','kilogram','kilograms') GROUP BY invoice_id) iq ON iq.invoice_id=i.id WHERE TO_CHAR(i.inv_date,'YYYY-MM')=${month} AND i.factory=${factory} ORDER BY i.inv_date DESC,i.total DESC`,\n }, req);\n }\n if (month) {\n return ok({\n invoices:\n await sql`SELECT i.*,c.gstin,COALESCE(iq.total_qty,0)::float as total_qty FROM pp_sales_invoices i LEFT JOIN pp_sales_customers c ON i.customer_id=c.id LEFT JOIN (SELECT invoice_id,SUM(qty)::float as total_qty FROM pp_sales_items WHERE LOWER(unit) IN ('kg','kgs','kilo','kilos','kilogram','kilograms') GROUP BY invoice_id) iq ON iq.invoice_id=i.id WHERE TO_CHAR(i.inv_date,'YYYY-MM')=${month} ORDER BY i.inv_date DESC,i.total DESC`,\n }, req);\n }\n if (factory && factory !== \"all\") {\n return ok({\n invoices:\n await sql`SELECT i.*,c.gstin,COALESCE(iq.total_qty,0)::float as total_qty FROM pp_sales_invoices i LEFT JOIN pp_sales_customers c ON i.customer_id=c.id LEFT JOIN (SELECT invoice_id,SUM(qty)::float as total_qty FROM pp_sales_items WHERE LOWER(unit) IN ('kg','kgs','kilo','kilos','kilogram','kilograms') GROUP BY invoice_id) iq ON iq.invoice_id=i.id WHERE i.factory=${factory} ORDER BY i.inv_date DESC LIMIT 100`,\n }, req);\n }\n return ok({\n invoices:\n await sql`SELECT i.*,c.gstin,COALESCE(iq.total_qty,0)::float as total_qty FROM pp_sales_invoices i LEFT JOIN pp_sales_customers c ON i.customer_id=c.id LEFT JOIN (SELECT invoice_id,SUM(qty)::float as total_qty FROM pp_sales_items WHERE LOWER(unit) IN ('kg','kgs','kilo','kilos','kilogram','kilograms') GROUP BY invoice_id) iq ON iq.invoice_id=i.id ORDER BY i.inv_date DESC LIMIT 100`,\n }, req);\n}\n\nexport async function getSalesInvoiceDetail(p: URLSearchParams, req?: Request): Promise {\n var id = Number(p.get(\"id\"));\n if (!id) return err(\"id required\", 400, req);\n var inv =\n await sql`SELECT i.*,c.gstin,c.address,c.phone FROM pp_sales_invoices i LEFT JOIN pp_sales_customers c ON i.customer_id=c.id WHERE i.id=${id}`;\n var items =\n await sql`SELECT*FROM pp_sales_items WHERE invoice_id=${id} ORDER BY amount DESC`;\n if (!inv || inv.length === 0) return err(\"Not found\", 404, req);\n return ok({ invoice: inv[0], items: items }, req);\n}\n\nexport async function deleteSalesInvoice(b: any, callerName = \"unknown\", req?: Request): Promise {\n // FIX 6: Require explicit confirmation flag\n if (!b.id) return err(\"id required\", 400, req);\n if (b.confirmDelete !== true) return err(\"confirmDelete: true required to delete an invoice\", 400, req);\n\n // FIX 3: Delete items + header atomically\n try {\n await sql`BEGIN`;\n try {\n const inv = await sql`SELECT inv_no,total FROM pp_sales_invoices WHERE id=${b.id}`;\n if (inv.length === 0) { await sql`ROLLBACK`; return err(\"Invoice not found\", 404, req); }\n await sql`DELETE FROM pp_sales_items WHERE invoice_id=${b.id}`;\n await sql`DELETE FROM pp_sales_invoices WHERE id=${b.id}`;\n await sql`COMMIT`;\n await audit(callerName, \"DELETE_INVOICE\", \"sales\", { id: b.id, inv_no: inv[0].inv_no, total: inv[0].total });\n return ok({ success: true }, req);\n } catch (inner: any) {\n await sql`ROLLBACK`;\n return err(\"Delete failed: \" + inner.message, 500, req);\n }\n } catch (outer: any) {\n return err(\"Transaction error: \" + outer.message, 500, req);\n }\n}\n\nexport async function updateSalesInvoice(b: any, req?: Request): Promise {\n if (!b.id) return err(\"id required\", 400, req);\n var taxable = Number(b.taxable) || 0;\n var cgst = Number(b.cgst) || 0;\n var sgst = Number(b.sgst) || 0;\n var igst = Number(b.igst) || 0;\n var total = Number(b.total) || (taxable + cgst + sgst + igst);\n var custName = (b.customer_name || \"\").trim();\n var factory = b.factory || null;\n var invDate = b.inv_date || null;\n // Update header\n await sql`UPDATE pp_sales_invoices SET\n inv_date=COALESCE(${invDate}::date, inv_date),\n customer_name=COALESCE(NULLIF(${custName},''), customer_name),\n factory=COALESCE(${factory}, factory),\n taxable=${taxable}, cgst=${cgst}, sgst=${sgst}, igst=${igst}, total=${total}\n WHERE id=${b.id}`;\n // Sync customer factory if changed\n if (factory) {\n var cx =\n await sql`SELECT id FROM pp_sales_customers WHERE LOWER(TRIM(name))=LOWER(${custName})`;\n if (cx.length > 0) {\n await sql`UPDATE pp_sales_customers SET factory=${factory},updated_at=NOW() WHERE id=${\n cx[0].id\n }`;\n }\n }\n // Replace all items\n if (b.items && Array.isArray(b.items)) {\n await sql`DELETE FROM pp_sales_items WHERE invoice_id=${b.id}`;\n for (var j = 0; j < b.items.length; j++) {\n var it = b.items[j];\n var amt = Number(it.amount) ||\n (Number(it.qty || 0) * Number(it.rate || 0));\n await sql`INSERT INTO pp_sales_items(invoice_id,product_name,hsn,qty,unit,rate,amount,color,description)VALUES(${b.id},${\n it.product_name || \"\"\n },${it.hsn || \"\"},${Number(it.qty) || 0},${it.unit || \"KG\"},${\n Number(it.rate) || 0\n },${amt},${it.color || \"\"},${it.description || \"\"})`;\n }\n }\n return ok({ success: true }, req);\n}\n\n// BUG6 FIX: Parallelized — all 5 queries run simultaneously via Promise.all (~5x faster)\nexport async function getSalesDashboard(p: URLSearchParams, req?: Request): Promise {\n var date = p.get(\"date\") || \"\";\n var month = p.get(\"month\") || \"\";\n var factory = p.get(\"factory\") || \"\";\n var m = month || (date ? date.slice(0, 7) : \"\");\n var fac = (factory && factory !== \"all\") ? factory : \"\";\n\n var dayQ = !date\n ? Promise.resolve([{\n inv_count: 0,\n taxable: 0,\n cgst: 0,\n sgst: 0,\n igst: 0,\n total: 0,\n cust_count: 0,\n }])\n : fac\n ? sql`SELECT COUNT(*)::int as inv_count,COALESCE(SUM(taxable),0)::float as taxable,COALESCE(SUM(cgst),0)::float as cgst,COALESCE(SUM(sgst),0)::float as sgst,COALESCE(SUM(igst),0)::float as igst,COALESCE(SUM(total),0)::float as total,COUNT(DISTINCT customer_name)::int as cust_count FROM pp_sales_invoices WHERE TO_CHAR(inv_date,'YYYY-MM-DD')=${date} AND factory=${fac}`\n : sql`SELECT COUNT(*)::int as inv_count,COALESCE(SUM(taxable),0)::float as taxable,COALESCE(SUM(cgst),0)::float as cgst,COALESCE(SUM(sgst),0)::float as sgst,COALESCE(SUM(igst),0)::float as igst,COALESCE(SUM(total),0)::float as total,COUNT(DISTINCT customer_name)::int as cust_count FROM pp_sales_invoices WHERE TO_CHAR(inv_date,'YYYY-MM-DD')=${date}`;\n\n var mtdQ = !m\n ? Promise.resolve([{ inv_count: 0, total: 0, cust_count: 0 }])\n : fac\n ? sql`SELECT COUNT(*)::int as inv_count,COALESCE(SUM(total),0)::float as total,COUNT(DISTINCT customer_name)::int as cust_count FROM pp_sales_invoices WHERE TO_CHAR(inv_date,'YYYY-MM')=${m} AND factory=${fac}`\n : sql`SELECT COUNT(*)::int as inv_count,COALESCE(SUM(total),0)::float as total,COUNT(DISTINCT customer_name)::int as cust_count FROM pp_sales_invoices WHERE TO_CHAR(inv_date,'YYYY-MM')=${m}`;\n\n var facQ = date\n ? sql`SELECT factory,COUNT(*)::int as inv_count,COALESCE(SUM(total),0)::float as total FROM pp_sales_invoices WHERE TO_CHAR(inv_date,'YYYY-MM-DD')=${date} AND factory IS NOT NULL GROUP BY factory`\n : Promise.resolve([]);\n\n var trendQ = !m\n ? Promise.resolve([])\n : fac\n ? sql`SELECT TO_CHAR(inv_date,'YYYY-MM-DD')as date,COUNT(*)::int as inv_count,SUM(total)::float as total FROM pp_sales_invoices WHERE TO_CHAR(inv_date,'YYYY-MM')=${m} AND factory=${fac} GROUP BY inv_date ORDER BY inv_date DESC LIMIT 31`\n : sql`SELECT TO_CHAR(inv_date,'YYYY-MM-DD')as date,COUNT(*)::int as inv_count,SUM(total)::float as total FROM pp_sales_invoices WHERE TO_CHAR(inv_date,'YYYY-MM')=${m} GROUP BY inv_date ORDER BY inv_date DESC LIMIT 31`;\n\n var mtdFacQ = m\n ? sql`SELECT factory,COUNT(*)::int as inv_count,COALESCE(SUM(total),0)::float as total FROM pp_sales_invoices WHERE TO_CHAR(inv_date,'YYYY-MM')=${m} AND factory IS NOT NULL GROUP BY factory`\n : Promise.resolve([]);\n\n const [dayArr, mtdArr, facArr, trendArr, mtdFacArr] = await Promise.all([\n dayQ,\n mtdQ,\n facQ,\n trendQ,\n mtdFacQ,\n ]);\n return ok({\n day: dayArr[0],\n mtd: mtdArr[0],\n facSplit: facArr,\n trend: trendArr,\n mtdFacSplit: mtdFacArr,\n }, req);\n}\n\n// OroBeads Match — Bhiwandi (OroBeads) vs Lambodhar daily SALES comparison\n// Shows: daily KG (from items with KG-like units only), taxable Amount, and Avg Rate per factory\nexport async function getOroBeadsMatch(p: URLSearchParams, req?: Request): Promise {\n var month = p.get(\"month\") || \"\";\n if (!month || !/^\\d{4}-\\d{2}$/.test(month)) {\n return err(\"month required (YYYY-MM)\");\n }\n try {\n // Parallel queries: OroBeads daily sales + Lambodhar daily sales\n // KG = only items where unit is KG/Kgs/kg (excludes PCS, NOS, EA, Ltrs, Gross, SET)\n // Amount = invoice taxable (pre-GST) from invoice headers\n var [oroRows, lamRows] = await Promise.all([\n sql`SELECT TO_CHAR(i.inv_date,'YYYY-MM-DD') as date,\n SUM(i.taxable)::float as amount,\n COALESCE(SUM(iq.kg_qty),0)::float as kg\n FROM pp_sales_invoices i\n LEFT JOIN (\n SELECT invoice_id, SUM(qty)::float as kg_qty\n FROM pp_sales_items\n WHERE LOWER(unit) IN ('kg','kgs','kilo','kilos','kilogram','kilograms')\n GROUP BY invoice_id\n ) iq ON iq.invoice_id=i.id\n WHERE TO_CHAR(i.inv_date,'YYYY-MM') = ${month}\n AND i.factory='orobeads'\n GROUP BY TO_CHAR(i.inv_date,'YYYY-MM-DD')\n ORDER BY date`,\n sql`SELECT TO_CHAR(i.inv_date,'YYYY-MM-DD') as date,\n SUM(i.taxable)::float as amount,\n COALESCE(SUM(iq.kg_qty),0)::float as kg\n FROM pp_sales_invoices i\n LEFT JOIN (\n SELECT invoice_id, SUM(qty)::float as kg_qty\n FROM pp_sales_items\n WHERE LOWER(unit) IN ('kg','kgs','kilo','kilos','kilogram','kilograms')\n GROUP BY invoice_id\n ) iq ON iq.invoice_id=i.id\n WHERE TO_CHAR(i.inv_date,'YYYY-MM') = ${month}\n AND i.factory='lambodhar'\n GROUP BY TO_CHAR(i.inv_date,'YYYY-MM-DD')\n ORDER BY date`,\n ]);\n var oroMap: Record = {};\n var lamMap: Record = {};\n for (var r of oroRows) {\n oroMap[r.date] = { kg: Number(r.kg) || 0, amount: Number(r.amount) || 0 };\n }\n for (var r of lamRows) {\n lamMap[r.date] = { kg: Number(r.kg) || 0, amount: Number(r.amount) || 0 };\n }\n var allDates = Array.from(\n new Set([...Object.keys(oroMap), ...Object.keys(lamMap)]),\n ).sort();\n var days = allDates.map(function (d) {\n var o = oroMap[d] || { kg: 0, amount: 0 };\n var l = lamMap[d] || { kg: 0, amount: 0 };\n return {\n date: d,\n oro_kg: o.kg,\n oro_amount: o.amount,\n oro_rate: o.kg > 0 ? Math.round(o.amount / o.kg) : 0,\n lam_kg: l.kg,\n lam_amount: l.amount,\n lam_rate: l.kg > 0 ? Math.round(l.amount / l.kg) : 0,\n };\n });\n var totOroKg = days.reduce(function (s: number, d: any) {\n return s + d.oro_kg;\n }, 0);\n var totOroAmt = days.reduce(function (s: number, d: any) {\n return s + d.oro_amount;\n }, 0);\n var totLamKg = days.reduce(function (s: number, d: any) {\n return s + d.lam_kg;\n }, 0);\n var totLamAmt = days.reduce(function (s: number, d: any) {\n return s + d.lam_amount;\n }, 0);\n return ok({\n month,\n days,\n totals: {\n oro_kg: totOroKg,\n oro_amount: totOroAmt,\n oro_rate: totOroKg > 0 ? Math.round(totOroAmt / totOroKg) : 0,\n lam_kg: totLamKg,\n lam_amount: totLamAmt,\n lam_rate: totLamKg > 0 ? Math.round(totLamAmt / totLamKg) : 0,\n grand_kg: totOroKg + totLamKg,\n grand_amount: totOroAmt + totLamAmt,\n },\n }, req);\n } catch (e: any) {\n return ok({\n month,\n days: [],\n totals: {\n oro_kg: 0,\n oro_amount: 0,\n oro_rate: 0,\n lam_kg: 0,\n lam_amount: 0,\n lam_rate: 0,\n grand_kg: 0,\n grand_amount: 0,\n },\n error: String(e?.message || e),\n }, req);\n }\n}\n\nexport async function getSalesDates(p: URLSearchParams, req?: Request): Promise {\n var m = p.get(\"month\") || \"\";\n if (!m) return ok({ dates: [] }, req);\n var r =\n await sql`SELECT DISTINCT TO_CHAR(inv_date,'YYYY-MM-DD')as date FROM pp_sales_invoices WHERE TO_CHAR(inv_date,'YYYY-MM')=${m} ORDER BY date DESC`;\n return ok({ dates: r.map((x: any) => x.date) }, req);\n}\n\nexport async function getSalesMonths(req?: Request): Promise {\n var r =\n await sql`SELECT DISTINCT TO_CHAR(inv_date,'YYYY-MM')as month FROM pp_sales_invoices ORDER BY month DESC`;\n return ok({ months: r.map((x: any) => x.month) }, req);\n}\n\n// V21: total_qty counts only KG-unit items (excludes PCS, NOS, EA, Gross, Ltrs, SET)\nexport async function getSalesCustomerSummary(p: URLSearchParams, req?: Request): Promise {\n var month = p.get(\"month\") || \"\";\n var factory = p.get(\"factory\") || \"\";\n // KG-only subquery: only sum qty where unit is KG-like\n var kgSub =\n `(SELECT invoice_id,SUM(qty)::float as total_qty FROM pp_sales_items WHERE LOWER(unit) IN ('kg','kgs','kilo','kilos','kilogram','kilograms') GROUP BY invoice_id)`;\n if (month && factory && factory !== \"all\") {\n return ok({\n customers:\n await sql`SELECT i.customer_name,i.factory,c.gstin,COUNT(DISTINCT i.id)::int as inv_count,SUM(i.taxable)::float as taxable,SUM(i.total)::float as total,COALESCE(SUM(iq.total_qty),0)::float as total_qty FROM pp_sales_invoices i LEFT JOIN pp_sales_customers c ON i.customer_id=c.id LEFT JOIN (SELECT invoice_id,SUM(qty)::float as total_qty FROM pp_sales_items WHERE LOWER(unit) IN ('kg','kgs','kilo','kilos','kilogram','kilograms') GROUP BY invoice_id) iq ON iq.invoice_id=i.id WHERE TO_CHAR(i.inv_date,'YYYY-MM')=${month} AND i.factory=${factory} GROUP BY i.customer_name,i.factory,c.gstin ORDER BY SUM(i.total)DESC`,\n }, req);\n }\n if (month) {\n return ok({\n customers:\n await sql`SELECT i.customer_name,i.factory,c.gstin,COUNT(DISTINCT i.id)::int as inv_count,SUM(i.taxable)::float as taxable,SUM(i.total)::float as total,COALESCE(SUM(iq.total_qty),0)::float as total_qty FROM pp_sales_invoices i LEFT JOIN pp_sales_customers c ON i.customer_id=c.id LEFT JOIN (SELECT invoice_id,SUM(qty)::float as total_qty FROM pp_sales_items WHERE LOWER(unit) IN ('kg','kgs','kilo','kilos','kilogram','kilograms') GROUP BY invoice_id) iq ON iq.invoice_id=i.id WHERE TO_CHAR(i.inv_date,'YYYY-MM')=${month} GROUP BY i.customer_name,i.factory,c.gstin ORDER BY SUM(i.total)DESC`,\n }, req);\n }\n return ok({\n customers:\n await sql`SELECT i.customer_name,i.factory,c.gstin,COUNT(DISTINCT i.id)::int as inv_count,SUM(i.taxable)::float as taxable,SUM(i.total)::float as total,COALESCE(SUM(iq.total_qty),0)::float as total_qty FROM pp_sales_invoices i LEFT JOIN pp_sales_customers c ON i.customer_id=c.id LEFT JOIN (SELECT invoice_id,SUM(qty)::float as total_qty FROM pp_sales_items WHERE LOWER(unit) IN ('kg','kgs','kilo','kilos','kilogram','kilograms') GROUP BY invoice_id) iq ON iq.invoice_id=i.id GROUP BY i.customer_name,i.factory,c.gstin ORDER BY SUM(i.total)DESC`,\n }, req);\n}","deleted","label",[102],"permalink","https://www.val.town/x/abhishekplating/PlatingPro/code/ppSales.ts","extension","actionData","errors"]