Compare commits
3 Commits
docs/ssr-r
...
fc54b92ddb
| Author | SHA1 | Date | |
|---|---|---|---|
| fc54b92ddb | |||
| 019a1b4cd8 | |||
| 38575aa5bd |
1
resources/.~lock.sample-yodlee-manual.tsv#
Normal file
1
resources/.~lock.sample-yodlee-manual.tsv#
Normal file
@@ -0,0 +1 @@
|
||||
,noti,pop-os,01.06.2026 21:02,file:///home/noti/.config/libreoffice/4;
|
||||
File diff suppressed because one or more lines are too long
@@ -1760,3 +1760,37 @@ Id,Sysco Category,Sysco Description,Integreat Account,Integreat Account Code,Nic
|
||||
1759,MEATS,PORK SHANK BONE KUROBUTA PR12,Beef/Pork Costs,51110,
|
||||
1760,CANNED AND DRY,SEASONING ITALIAN WHL,Food Costs,50000,
|
||||
1761,PRODUCE,MUSHROOM PORTABELLA CAP 4-5,Produce Costs,51200,
|
||||
1762,PAPER & DISP,BAG PAPER 250 CT,Paper Costs,55000,
|
||||
1763,MEATS,BEEF SHLDR TERES MAJOR SEL,Beef/Pork Costs,51110,
|
||||
1764,PAPER & DISP,BOWL PLASTIC COATING 42 OZ,Paper Costs,55000,
|
||||
1765,PAPER & DISP,BOX CATERING 21X13X4.25 LOGO,Paper Costs,55000,
|
||||
1766,CANNED AND DRY,CANDY MILK CHOC SHELLS,Food Costs,50000,
|
||||
1767,CANNED AND DRY,CHOCOLATE DUBAI PISTCHO KUNFEH,Food Costs,50000,
|
||||
1768,PAPER & DISP,CONTAINER PAPER 1/30 OZ NTG,Paper Costs,55000,
|
||||
1769,PAPER & DISP,CONTAINER PAPER 4/110OZ NTG,Paper Costs,55000,
|
||||
1770,PAPER & DISP,CUP PAPER COLD 22 OZ LOGO NTG,Paper Costs,55000,
|
||||
1771,PAPER & DISP,CUP PORTION PLAS CLR 1.50 OZ,Paper Costs,55000,
|
||||
1772,CANNED AND DRY,DESSERT CUP,Food Costs,50000,
|
||||
1773,FROZEN,DESSERT MINI PLAIN BEIGNET,Food Costs,50000,
|
||||
1774,CANNED AND DRY,DIP GARLIC TOUM,Food Costs,50000,
|
||||
1775,CANNED AND DRY,DRINK ENERGY ORANGE SPRKLNG,Soft Beverage Costs,52000,
|
||||
1776,CANNED AND DRY,DRINK ENERGY PEACH VIBE SPRKLG,Soft Beverage Costs,52000,
|
||||
1777,CANNED AND DRY,DRINK ENERGY TROPICAL VIBE,Soft Beverage Costs,52000,
|
||||
1778,PAPER & DISP,FILM PVC 18X2000 ROLL,Paper Costs,55000,
|
||||
1779,CANNED AND DRY,JUICE CONC MANDARIN CARDAMOM,Food Costs,50000,
|
||||
1780,CANNED AND DRY,JUICE CONC STRAWB DRAGON,Food Costs,50000,
|
||||
1781,PAPER & DISP,LID CLEAR PET 42 OZ,Paper Costs,55000,
|
||||
1782,PAPER & DISP,LID DOME DESSERT CUP,Paper Costs,55000,
|
||||
1783,PAPER & DISP,NAPKIN 2PLY INTR FOLD 6.3X8.26,Paper Costs,55000,
|
||||
1784,CANNED AND DRY,PASTE HERB HARISSA MOROCCAN,Food Costs,50000,
|
||||
1785,CANNED AND DRY,PASTE TAHINI DRESSING,Food Costs,50000,
|
||||
1786,FROZEN,PASTRY BEIGNET MN FLD CHOCCRML,Food Costs,50000,
|
||||
1787,CANNED AND DRY,PEPPER BANANA MILD RING,Food Costs,50000,
|
||||
1788,CANNED AND DRY,RICE MIX NICKS,Food Costs,50000,
|
||||
1789,CANNED AND DRY,SODA CHERRY VISSINADA GREEK,Soft Beverage Costs,52000,
|
||||
1790,CANNED AND DRY,SODA COLA PEPSI ZERO SUGAR,Soft Beverage Costs,52000,
|
||||
1791,CANNED AND DRY,SODA PEPSI COLA,Soft Beverage Costs,52000,
|
||||
1792,FROZEN,SPANAKOPITA SPINACH COOKED,Food Costs,50000,
|
||||
1793,PAPER & DISP,SPOON PLAS TEA PP X-HVY BLK,Paper Costs,55000,
|
||||
1794,PAPER & DISP,WRAP PAPER 14X14 LOGO VER2,Paper Costs,55000,
|
||||
1795,DAIRY PRODUCTS,YOGURT FRZN NF NICK THE GREEK,Dairy Costs,51300,
|
||||
|
||||
|
43
resources/sysco_recode/bad.csv
Normal file
43
resources/sysco_recode/bad.csv
Normal file
@@ -0,0 +1,43 @@
|
||||
,,,,,,,,,,,,,,,,,,,,,,,,,d,,,,,,,,,,,,,,,,,,,,,,,,,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,20.56,CA,20.56,Y,0,0,0,1.2,07,48,02,01,CANNED AND DRY,24,20OZ,AQUAFIN,WATER PURIFIED BTL PET LSE DW,30,33,0.75,24,,000000,,0000,,,,29115,47,,8492330,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,14.84,CA,14.84,Y,0,0,0,0.6,07,16,05,01,CANNED AND DRY,12,11.2OZ,LOUX,SODA CHERRY VISSINADA GRK PLAS,9.5,10.5,0.26,12,,000000,,0000,,,3000P,808959,01,,7189422,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,14.93,CA,14.93,Y,0,0,0,0.6,07,16,05,01,CANNED AND DRY,12,8 OZ,LOUX,SODA LEMON LEMONADA GREEK,9,11.5,0.26,12,,000000,,0000,,,3200,808959,01,,9910355,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,79.83,CA,79.83,Y,0,0,0,0,07,35,03,99,CANNED AND DRY,4,5 LB,OTHRYS,SPICE OREGANO LEAF RUBBED,20,22,2.51,4,,000000,,0000,,,62760,808959,01,,9911236,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,31.07,CA,31.07,Y,0,0,0,0,07,35,99,99,CANNED AND DRY,22,4.68OZ,HI WEST,RICE MIX NICKS,6.43,7,0.16,22,,000000,,0000,,,30-5729,345717,03,,7301949,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,2,2,0,54.84,CA,109.68,Y,0,0,0,0,07,33,01,99,CANNED AND DRY,2,20 LB,ROYAL,RICE BASMATI PABROIL SELA CS,40,40.6,1.21,2,,000000,,0000,,,91000244,26992,43,,7053293,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,75.49,CA,75.49,Y,0,0,0,0,07,34,04,99,CANNED AND DRY,4,1 GAL,NICKGRK,DRESSING VINAIGRETTE LOGO,33,35,0.87,4,,000000,,0000,,,1654,853,01,,7108399,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,29.4,CA,29.4,Y,0,0,0,0,07,36,99,99,CANNED AND DRY,8,15 OZ,HAIG'S,DIP GARLIC TOUM,7.25,8.25,0.34,8,,000000,,0000,,,8PGD16,691816,01,,7360056,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,38.85,CA,38.85,Y,0,0,0,0,07,37,02,02,CANNED AND DRY,1,35 LB,BEOCO,OIL CORN,35,36.55,0.85,1,,000000,,0000,,,,9846,02,,4823761,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,2,2,0,56.72,CA,113.44,Y,0,0,0,0,07,36,99,99,CANNED AND DRY,4,4 LB,GRECDEL,SPREAD HUMMUS TRADITIONAL,16,17,0.62,4,,000000,,0000,,,HU000083,1533,19,,7278619,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,-7.22,CA,-7.32,Y,0,0,0,0,07,86,01,99,CANNED AND DRY,1,EA,NONPROD,ALLOWANCE FOR DROP SIZE,0.01,0.01,0.01,1,,000000,,0000,,,,,01,,9477498,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,4.17,CA,4.17,Y,0,0,0,0,07,86,01,99,CANNED AND DRY,1,EA,NONPROD,CHGS FOR FUEL SURCHARGE,1,1,0,1,,000000,,0000,,,,,01,,6592893,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,2,2,0,95.98,CA,191.96,Y,0,0,0,0,02,04,99,99,DAIRY PRODUCTS,1,5 GAL,NICKGRK,SAUCE TZATZIKI,42,43.5,1.2,1,,000000,,0000,,,SA000084,1533,19,,7213639,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,2,2,0,54.82,CA,109.64,Y,0,0,0,0,02,10,01,99,DAIRY PRODUCTS,4,1 GAL,NICKGRK,YOGURT FRZN NF NICK THE GREEK,39.9,39.9,0.97,4,,000000,,0000,,,13101,379887,05,,7302646,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,172.37,CA,172.37,Y,0,0,0,0,12,08,02,03,DISPENSER BEVRG,12,32 OZ,TRACTOR,JUICE CONC STRAWB DRAGON,24,25.5,0.58,12,,000000,,0000,,,6555,693956,01,,7206974,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,62.48,CA,62.48,Y,0,0,0,0,12,08,02,03,DISPENSER BEVRG,1,2.5GAL,DR PEPR,SYRUP DR PPR DIET BIB,20.93,21.82,0.47,1,,000000,,0000,,,12115,376510,09,,7459969,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,117.3,CA,117.3,Y,0,0,0,0,12,08,02,03,DISPENSER BEVRG,1,5GAL,DR PEPR,SYRUP DR PEPPER BIB,40,54.4,0.83,1,,000000,,0000,,,12109,9562,14,,4273553,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,8,8,0,26.36,CA,210.88,Y,0,0,0,0,06,02,45,99,FROZEN,12,10 CT,KONTOS,BREAD PITA GYRO PRE-OILED 7,21,24,1.65,12,,000000,,0000,,,10005,25370,01,,5223334,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,2,2,0,69.37,CA,138.74,Y,0,0,0,0,06,01,70,99,FROZEN,36,6 OZ,HELLAS,SPANAKOPITA SPINACH COOKED,12.4,13.4,0.62,36,,000000,,0000,,,216312,32248,01,,7455027,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,2,2,0,49.53,CA,99.06,Y,0,0,0,0,06,01,60,99,FROZEN,2,24 CT,HELLAS,BAKLAVA CLASSIC 2X24,9.6,10.6,0.46,2,,000000,,0000,,,100224,32248,01,,7187055,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,57.66,CA,57.66,Y,0,0,0,0,06,01,65,99,FROZEN,140,0.7 OZ,CHICPAT,DESSERT MINI PLAIN BEIGNET,6.17,7.5,0.88,140,,000000,,0000,,,540061,1188,53,,7212299,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,97.94,CA,97.94,Y,0,0,0,0,06,02,01,99,FROZEN,4,10 LB,NICKGRK,APTZR VEG FALAFEL PUCK HALAL,40,42,2.03,4,,000000,,0000,,,FA000090,1533,05,,7274591,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,1,53.5,1,53.5,7.556,LB,404.25,Y,0,0,0,0,03,02,01,13,MEATS,5,10.5#,TWORVRS,BEEF SHLDR TERES MAJOR SEL,53,55,1.99,5,,000000,,0000,,,B83003,527004,03,,0932867,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,87.76,CA,87.76,Y,0,0,0,0,03,04,99,99,MEATS,1,20 LB,GRECDEL,PORK SLI GYRO CONE,20,21,0.77,1,,000000,,0000,,,ME000215,1533,05,,7211838,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,7,7,0,92.53,CA,647.71,Y,0,0,0,0,03,02,04,99,MEATS,1,30 LB,NICKGRK,MEAT GYRO BEEF CONE NTG,30,31,0.97,1,,000000,,0000,,,ME000071,1533,05,,9906087,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,25.41,CA,25.41,Y,0,0,0,0,08,42,64,43,PAPER & DISP,20,50 CT,KARAT,LID PLAS FLAT F/12-22 OZ,5.75,7,1.94,20,,000000,,0000,,,C-KCL90,461672,05,,7661388,00000000000000,,,260402,04671945,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,22.32,CA,22.32,Y,0,0,0,0,08,60,99,99,PAPER & DISP,24,250 CT,ELEMEN,NAPKIN 2PLY INTR FOLD 6.3X8.26,16.1,16.8,1.55,24,,000000,,0000,,,11904,613310,01,,7452585,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,26.15,CA,26.15,Y,0,0,0,0,08,21,64,62,PAPER & DISP,50,50CT,KARAT,CUP PORTION PLAS CLR 1.50 OZ,10,10,1.36,50,,000000,,0000,,,FP-P150-PP,461672,05,,4613026,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,43.99,CA,43.99,Y,0,0,0,0,08,75,03,04,PAPER & DISP,6,50 EA,NATZWAY,BOWL PLASTIC COATING 42 OZ,14.55,17.19,3.56,6,,000000,,0000,,,10205,773772,01,,7408008,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,41.25,CA,41.25,Y,0,0,0,0,08,42,99,99,PAPER & DISP,6,50CT,NATZWAY,LID CLEAR PET 42 OZ,8.59,10.47,2.01,6,,000000,,0000,,,10206,773772,01,,7408215,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,47.6,CA,47.6,Y,0,0,0,0,08,21,56,99,PAPER & DISP,1000,22 OZ,NICKGRK,CUP PAPER COLD 22 OZ LOGO NTG,31.96,34.62,3.78,1000,,000000,,0000,,,810161542703,461672,05,,7354127,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,30.6,CA,30.6,Y,0,0,0,0,08,18,56,99,PAPER & DISP,1,450 CT,NICKGRK,CONTAINER PAPER 1/30 OZ NTG,23,25,3.25,1,,000000,,0000,,,810161542673,461672,05,,7354120,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,27.6,CA,27.6,Y,0,0,0,0,08,18,56,99,PAPER & DISP,1,160 CT,NICKGRK,CONTAINER PAPER 4/110OZ NTG,19.6,21.4,3.59,1,,000000,,0000,,,810161542680,461672,05,,7354119,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,3,3,0,37.6,CA,112.8,Y,0,0,0,0,08,18,02,12,PAPER & DISP,2,100CT,NATZWAY,CONTAINER PAPER MLD FBR 9X6,18,18,1.28,2,,000000,,0000,,,10042,773772,01,,7250678,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,2,2,0,46.07,CA,92.14,Y,0,0,0,0,08,09,56,99,PAPER & DISP,1,250BAG,NICKGRK,BAG PAPER 250 CT,14.5,15,2.18,1,,000000,,0000,,,,773772,01,,7417242,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,1,,1,1,0,24.46,EA,24.46,Y,0,0,0,0,08,36,56,79,PAPER & DISP,5,1000,BAGCRFT,WRAP DELI WHT 12X12 GRS RESIST,37,37,1.07,5,,000000,,0000,,,P057012,276,01,,5723808,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,1,1,0,39.41,CA,39.41,Y,3.85,0,0,0,08,06,25,10,PAPER & DISP,10,100CT,DHGPROF,GLOVE NITRILE BLK PEDRFREE LRG,12.21,12.21,0.66,10,,000000,,0000,,,DNGB-L,613310,01,,7296407,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,3,3,0,89.32,CA,267.96,Y,0,0,0,0,05,01,01,07,POULTRY,4,10 LB,SYS CLS,CHICKEN CVP THIGH BNLS SKLS,40,42,1.04,4,,000000,,0000,,,14301,3254,21,,7792187,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,9,9,0,86.97,CA,782.73,Y,0,0,0,0,05,02,01,99,POULTRY,1,20LB,GRECDEL,GYRO CHICKEN SHAWARMA CONE,20,21,0.77,1,,000000,,0000,,,ME000102,1533,05,,7124188,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,DET,,,,,5,5,0,23.65,CA,118.25,Y,0,0,0,0,11,02,23,01,PRODUCE,1,50 LB,PACKER,POTATO KENNEBEC FRESH,50,52,2,1,,000000,,0000,,,,696760,01,,2039220,00000000000000,,,260402,04672959,
|
||||
EEK,,050,00175469,850081745,HDR,,,CKC CONCORD INC,,260402,,,,Rolling 8,,NICK THE GREEK CONCORD,2075 DIAMOND BLVD,STE H-103,CONCORD,CA,94520-582,408593,000000000,,,,,BBNKG,0,050,SYSCO SAN FRANCISCO,5900 STEWART AVENU,,FREMONT,CA,94538,,,,,,,1372486,4024,004,0000000,00000000,20260529,6.25,CRO8
|
||||
EEK,,050,00175469,850081745,SUM,,,40,0,0,74,0,4625.36,6.25,00000463161,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
|
||||
|
271
scratch-sessions/sysco_recode_scratch.clj
Normal file
271
scratch-sessions/sysco_recode_scratch.clj
Normal file
@@ -0,0 +1,271 @@
|
||||
;; =====================================================================
|
||||
;; ONE-OFF SCRATCH — re-code already-imported Sysco invoices after fixing
|
||||
;; resources/sysco_line_item_mapping.csv.
|
||||
;;
|
||||
;; Context: the Sysco importer codes each line item by EXACT description
|
||||
;; match against sysco_line_item_mapping.csv, defaulting to GL 50000 when a
|
||||
;; description is missing (auto-ap.jobs.sysco/get-line-account). Missing
|
||||
;; PAPER & DISP (and other) descriptions landed in 50000 (Food Costs)
|
||||
;; instead of their real accounts (e.g. 55000 Paper Costs). The mapping is
|
||||
;; now fixed; this re-derives the correct split from each invoice's source
|
||||
;; CSV and rewrites :invoice/expense-accounts.
|
||||
;;
|
||||
;; Design:
|
||||
;; - Recode EVERY invoice found in the CSV resource (a Sysco file may batch
|
||||
;; several invoices; they're grouped by InvoiceNumber).
|
||||
;; - Build ONE transaction covering every invoice, emitting only the datoms
|
||||
;; that actually change:
|
||||
;; * reuse an existing invoice-expense-account when its account (and
|
||||
;; location) already match, updating just :amount when it differs;
|
||||
;; * add a child for an account that has no row yet;
|
||||
;; * retract a child whose account is no longer in the corrected split;
|
||||
;; * emit nothing for rows already correct.
|
||||
;; - Validate with (dc/with db changes): apply the tx to an in-memory db
|
||||
;; value and assert every affected invoice's expense-account amounts sum
|
||||
;; to its :invoice/total BEFORE committing for real.
|
||||
;; - After committing, touch the ledger for every affected invoice. This is
|
||||
;; a SEPARATE transaction on purpose: :upsert-invoice rebuilds the journal
|
||||
;; entry from the invoice's expense-accounts as seen in db-before, so it
|
||||
;; must run after the recode is committed.
|
||||
;;
|
||||
;; DO NOT load/evaluate this whole file. Step through the (comment ...) forms
|
||||
;; one at a time in a connected REPL; the commit + ledger steps are gated #_.
|
||||
;;
|
||||
;; PRECONDITIONS
|
||||
;; - The deployed artifact ships the fixed sysco_line_item_mapping.csv AND
|
||||
;; the invoice CSV at resources/sysco_recode/<file>.csv (io/resource).
|
||||
;; - You are connected to the DB you intend to mutate (prod conn!).
|
||||
;; =====================================================================
|
||||
|
||||
(comment
|
||||
|
||||
(require '[auto-ap.jobs.sysco :as sysco]
|
||||
'[auto-ap.datomic :refer [conn audit-transact random-tempid]]
|
||||
'[auto-ap.utils :refer [dollars=]]
|
||||
'[auto-ap.time :as t]
|
||||
'[clj-time.coerce :as coerce]
|
||||
'[clojure.data.csv :as csv]
|
||||
'[clojure.java.io :as io]
|
||||
'[datomic.api :as dc])
|
||||
|
||||
;; ------------------------------------------------------------------
|
||||
;; STEP 0 — reload the mapping cache so the corrected CSV is in effect.
|
||||
;; ------------------------------------------------------------------
|
||||
(reset! sysco/sysco-name->line nil)
|
||||
(count (sysco/get-sysco->line))
|
||||
|
||||
;; sanity: a previously-missing paper description now resolves to 55000.
|
||||
(dc/pull (dc/db conn) [:account/numeric-code :account/name]
|
||||
(sysco/get-line-account "BAG PAPER 250 CT")) ; => 55000
|
||||
|
||||
;; ------------------------------------------------------------------
|
||||
;; Helpers
|
||||
;; ------------------------------------------------------------------
|
||||
(defn read-csv-rows
|
||||
"Reads the invoice CSV from the classpath (so it ships with the deploy).
|
||||
`resource-path` is relative to a resources/ root, e.g. \"sysco_recode/bad.csv\"."
|
||||
[resource-path]
|
||||
(with-open [r (io/reader (or (io/resource resource-path)
|
||||
(throw (ex-info "CSV not found on classpath"
|
||||
{:resource-path resource-path}))))]
|
||||
(doall (csv/read-csv r))))
|
||||
|
||||
(defn parse-date
|
||||
"Sysco yyMMdd string -> java.util.Date, the same way the importer stores
|
||||
:invoice/date (auto-ap.jobs.sysco/extract-invoice-details)."
|
||||
[yymmdd]
|
||||
(coerce/to-date (t/parse yymmdd "yyMMdd")))
|
||||
|
||||
(defn group-invoices
|
||||
"Split a (possibly multi-invoice) Sysco CSV into one entry per invoice.
|
||||
Groups DET/HDR/SUM rows by InvoiceNumber (index 4); date comes from the
|
||||
group's HDR row InvoiceDate (index 10)."
|
||||
[rows]
|
||||
(->> rows
|
||||
(filter #(contains? #{"DET" "HDR" "SUM"} (nth % 5 nil)))
|
||||
(group-by #(nth % 4))
|
||||
(mapv (fn [[number grp]]
|
||||
(let [hdr (first (filter #(= "HDR" (nth % 5)) grp))]
|
||||
{:invoice-number number
|
||||
:date-str (some-> hdr (nth 10))
|
||||
:rows grp})))))
|
||||
|
||||
(defn desired-split
|
||||
"Rows of one Sysco invoice -> {account-eid -> amount-double}, using the
|
||||
CURRENT (fixed) mapping. DET rows only (record-type at index 5); tax
|
||||
(SUM row, TotalTaxAmount index 14) added to the same account the
|
||||
importer uses for \"TAX\". Mirrors auto-ap.jobs.sysco/code-individual-items."
|
||||
[rows]
|
||||
(let [det (filter #(= "DET" (nth % 5)) rows)
|
||||
sum-row (first (filter #(= "SUM" (nth % 5)) rows))
|
||||
tax (some-> sum-row (nth 14) Double/parseDouble)
|
||||
by-acct (reduce
|
||||
(fn [acc row]
|
||||
(update acc
|
||||
(sysco/get-line-account (nth row sysco/item-name-index))
|
||||
(fnil + 0.0)
|
||||
(Double/parseDouble (nth row sysco/item-price-index))))
|
||||
{}
|
||||
det)]
|
||||
(cond-> by-acct
|
||||
(and tax (not (zero? tax)))
|
||||
(update (sysco/get-line-account "TAX") (fnil + 0.0) tax))))
|
||||
|
||||
(defn resolve-eid
|
||||
"Match on invoice-number AND date (belt-and-suspenders). Asserts a unique
|
||||
hit so we never recode the wrong invoice."
|
||||
[invoice-number date]
|
||||
(let [ids (mapv first (dc/q '[:find ?i :in $ ?n ?d
|
||||
:where
|
||||
[?i :invoice/invoice-number ?n]
|
||||
[?i :invoice/date ?d]]
|
||||
(dc/db conn) invoice-number date))]
|
||||
(assert (>= 1 (count ids))
|
||||
(str "multiple invoices match " invoice-number " / " date ": " ids))
|
||||
(first ids)))
|
||||
|
||||
(defn invoice-change-datoms
|
||||
"Minimal tx-data to make invoice `eid`'s expense-account split equal
|
||||
`desired` ({account-eid -> amount}). Returns [] when already correct."
|
||||
[db eid desired]
|
||||
(let [existing (:invoice/expense-accounts
|
||||
(dc/pull db [{:invoice/expense-accounts
|
||||
[:db/id :invoice-expense-account/amount
|
||||
:invoice-expense-account/location
|
||||
{:invoice-expense-account/account [:db/id]}]}]
|
||||
eid))
|
||||
loc (or (some :invoice-expense-account/location existing) "HQ")
|
||||
;; one child per account expected; index by account, retract any dupes
|
||||
by-acct (group-by #(get-in % [:invoice-expense-account/account :db/id]) existing)
|
||||
one (into {} (map (fn [[a cs]] [a (first cs)])) by-acct)
|
||||
dupes (mapcat (fn [[_ cs]] (map :db/id (rest cs))) by-acct)
|
||||
wanted (set (keys desired))
|
||||
upserts (keep (fn [[acct amt]]
|
||||
(let [child (get one acct)]
|
||||
(cond
|
||||
;; new account -> accrete a child under the invoice
|
||||
(nil? child)
|
||||
{:db/id eid
|
||||
:invoice/expense-accounts
|
||||
[#:invoice-expense-account{:db/id (random-tempid)
|
||||
:account acct
|
||||
:location loc
|
||||
:amount amt}]}
|
||||
;; right account, wrong value -> reuse, set amount
|
||||
;; (and fix location if it drifted)
|
||||
(or (not (dollars= (:invoice-expense-account/amount child) amt))
|
||||
(not= (:invoice-expense-account/location child) loc))
|
||||
(cond-> {:db/id (:db/id child)
|
||||
:invoice-expense-account/amount amt}
|
||||
(not= (:invoice-expense-account/location child) loc)
|
||||
(assoc :invoice-expense-account/location loc))
|
||||
;; already correct -> nothing
|
||||
:else nil)))
|
||||
desired)
|
||||
retracts (for [[acct child] one :when (not (wanted acct))]
|
||||
[:db/retractEntity (:db/id child)])]
|
||||
(vec (concat upserts
|
||||
retracts
|
||||
(map (fn [id] [:db/retractEntity id]) dupes)))))
|
||||
|
||||
;; ------------------------------------------------------------------
|
||||
;; STEP 1 — point at the CSV. EVERY invoice in this file gets recoded.
|
||||
;; Place the file under resources/ (e.g. resources/sysco_recode/bad.csv)
|
||||
;; and commit it so it's on the classpath of the deployed artifact.
|
||||
;; ------------------------------------------------------------------
|
||||
(def csv-path "sysco_recode/bad.csv")
|
||||
(def rows (read-csv-rows csv-path))
|
||||
|
||||
(def invoices (group-invoices rows))
|
||||
(mapv (juxt :invoice-number :date-str) invoices) ;; what we found in the file
|
||||
|
||||
;; ------------------------------------------------------------------
|
||||
;; STEP 2 — resolve each invoice (number + date) and compute its split.
|
||||
;; ------------------------------------------------------------------
|
||||
(def plan
|
||||
(mapv (fn [{:keys [invoice-number date-str rows]}]
|
||||
(let [date (parse-date date-str)]
|
||||
{:invoice-number invoice-number
|
||||
:date date
|
||||
:eid (resolve-eid invoice-number date)
|
||||
:desired (desired-split rows)}))
|
||||
invoices))
|
||||
|
||||
;; bail if any invoice number didn't resolve
|
||||
(assert (every? :eid plan)
|
||||
(str "unresolved invoices: "
|
||||
(mapv (juxt :invoice-number :date) (remove :eid plan))))
|
||||
|
||||
;; ------------------------------------------------------------------
|
||||
;; STEP 3 — build the SINGLE changes-only transaction across all invoices.
|
||||
;; ------------------------------------------------------------------
|
||||
(def changes
|
||||
(let [db (dc/db conn)]
|
||||
(vec (mapcat (fn [{:keys [eid desired]}] (invoice-change-datoms db eid desired))
|
||||
plan))))
|
||||
|
||||
(count changes) ;; how many datoms we're actually changing
|
||||
changes ;; inspect the full minimal tx
|
||||
|
||||
;; ------------------------------------------------------------------
|
||||
;; STEP 4 — validate with dc/with: apply the tx to an in-memory db value
|
||||
;; and confirm every affected invoice still balances (sum of expense
|
||||
;; account amounts == :invoice/total).
|
||||
;; ------------------------------------------------------------------
|
||||
(def preview (dc/with (dc/db conn) changes))
|
||||
|
||||
(def balance-report
|
||||
(let [db-after (:db-after preview)]
|
||||
(mapv (fn [{:keys [eid invoice-number]}]
|
||||
(let [inv (dc/pull db-after
|
||||
[:invoice/total
|
||||
{:invoice/expense-accounts [:invoice-expense-account/amount]}]
|
||||
eid)
|
||||
s (reduce + 0.0 (map :invoice-expense-account/amount
|
||||
(:invoice/expense-accounts inv)))]
|
||||
{:invoice-number invoice-number
|
||||
:total (:invoice/total inv)
|
||||
:ea-sum s
|
||||
:ok? (dollars= s (:invoice/total inv))}))
|
||||
plan)))
|
||||
balance-report
|
||||
|
||||
;; HARD GATE — do not continue unless every invoice balances post-change.
|
||||
(assert (every? :ok? balance-report)
|
||||
(str "unbalanced after change: "
|
||||
(filterv (complement :ok?) balance-report)))
|
||||
|
||||
;; ------------------------------------------------------------------
|
||||
;; STEP 5 — COMMIT the recode (gated). One transaction, changes only.
|
||||
;; ------------------------------------------------------------------
|
||||
#_(audit-transact changes
|
||||
{:user/name "sysco recode (missing GL mappings fix)"
|
||||
:user/role "admin"})
|
||||
|
||||
;; ------------------------------------------------------------------
|
||||
;; STEP 6 — touch the ledger for every affected invoice (separate tx;
|
||||
;; :upsert-invoice rebuilds the journal entry from the now-committed
|
||||
;; expense-accounts). Gated.
|
||||
;; ------------------------------------------------------------------
|
||||
#_(audit-transact (mapv (fn [{:keys [eid]}] [:upsert-invoice {:db/id eid}]) plan)
|
||||
{:user/name "sysco recode ledger touch"
|
||||
:user/role "admin"})
|
||||
|
||||
;; ------------------------------------------------------------------
|
||||
;; STEP 7 — verify committed result.
|
||||
;; ------------------------------------------------------------------
|
||||
#_(let [db (dc/db conn)]
|
||||
(mapv (fn [{:keys [eid invoice-number]}]
|
||||
{:invoice-number invoice-number
|
||||
:accounts
|
||||
(->> (dc/pull db
|
||||
[{:invoice/expense-accounts
|
||||
[:invoice-expense-account/amount
|
||||
{:invoice-expense-account/account [:account/numeric-code]}]}]
|
||||
eid)
|
||||
:invoice/expense-accounts
|
||||
(map (juxt #(get-in % [:invoice-expense-account/account :account/numeric-code])
|
||||
:invoice-expense-account/amount))
|
||||
(sort-by first)
|
||||
vec)})
|
||||
plan)))
|
||||
Reference in New Issue
Block a user