5jJ$PdZddlZddlZddlZddlZddlZddlZddlmZ ddlmZddl m Z e ejjde ejj dz Zej$ZdZd d d dZd Zd ZdZdZdZdddddddZej8dZdZdZdZ dZ!dZ"dZ#dZ$dZ%dZ&d Z'd!Z(d"Z)d#Z*dd$Z+d%Z,dd&Z-d'Z.dd(Z/d)Z0dd*Z1d+Z2d,Z3d-Z4d.Z5d/Z6dd0Z7dd1Z8d2Z9d3Z:dd4Z;d5Zd8Z?d9Z@dd:ZAdd;ZBdd<ZCd=ZDd>ZEdd?ZFd@ZGddAZHddBZIdCZJdDZKdEZLddFZMdGZNdHZOdIZPdJZQdKZRdLZSdMZTddNZUddOZVddPZWddQZXddRZYdSZZddTZ[dUZ\dVZ]dWZ^dXZ_dYZ`ddZZad[Zbd\Zcd]Zddd^Zed_Zfd`ZgehdaegDZidbZjdcZkddZldeZmdfZndgZodhZpdiZqdjZrdkZsdlZtdmZudnZvdoZwddpZxdqZydrZzdsZ{dtZ|duZ}dvZ~dwZdxZdyZdzZd{Zd|Zd}Zd~ZehdeDZej8dZej8dZdZdZdZdZdZdZdZdZdZdZdZdZddZdZdZdZdZdZdZdZdddddZdZdZdZdZdZdZdZdZdZddZdZdZdZdZdZdZdZdZddZy)u qc_store.py — SQLite Quality Control store (DVI-1326 P1, board-approved plan). Backs the Togen QC tool's Batch Tickets capture: an administrable Materials list, versioned Mixture recipes with per-yd3 targets, and per-batch capture rows (one batch = one handwritten batch ticket page today; PLC/SCADA and spreadsheet imports arrive in later phases via the same `source` field). Flask-independent (mirrors assets_store.py / scada_store.py) so app.py, scripts, and tests can all import it. qc.db lives alongside the app's other state files. Design decisions (from the approved DVI-1326 plan + board answers 2026-08-08): - Quantities are stored in each material's CAPTURE unit (lb / oz / gal — what the batch ticket shows); consumption reporting converts to the material's CONSUMPTION unit (ton / lb / gal) with a tunable per-material decimal rounding, so "capture in oz, order by the lb" and "capture in lb, order by the ton" are both display-time conversions, never lossy storage. - Mixture targets are per-yd3 dosing amounts in the capture unit with -/+ tolerances (the scanned ticket's -/Target/+ columns); the batch form scales them by the batch's yards. Out-of-range NEVER blocks a save — it is flagged. - Mixtures are versioned: a targets change appends a mixture_versions row and bumps current_version; a name/active-only change does not. Batch lines snapshot the material name/unit and the scaled target/min/max at capture time, so recipe edits never rewrite captured history. - Materials are administrable (board answer #2): hard delete only when a material is unreferenced by any mixture target or batch line; otherwise deactivate (kept for history, hidden from new capture forms). Schema ------ materials(name UNIQUE, capture_unit, consumption_unit, consumption_decimals, sort, active) — the trackable materials/chemicals/VMAs. mixtures(name, segment, active, current_version) — named recipes per production segment (wet_cast / pipe / block). mixture_versions(mixture_id, version, created_at, created_by, note) — append-only recipe history. mixture_targets(version_id, material_id, target, minus, plus) — per-yd3 dosing in the material's capture unit; minus/plus are tolerance amounts below/above target. batches(batch_date, segment, batch_number, mixture_id, mixture_version, mixture_name, yards, rock_moisture, sand_moisture, initials, notes, source manual|import|plc|ocr, created_by/at, updated_by/at, deleted) — one captured batch ticket (soft-deleted so ids stay stable). batch_lines(batch_id, material_id, material_name, unit, target, min_ok, max_ok, actual, out_of_range) — snapshot per material per batch. batch_custom_values(batch_id, field_id, value) — flexible per-batch custom field values (DVI-1452 P2), id-keyed so a label rename never orphans. meta(key, value) — bookkeeping (incl. the admin-managed 'segments', 'custom_fields', 'batch_ticket_fields', … registries). N)date)datetime)Path QC_DB_FILEzqc.db)wet_castpipeblockzWet Castz Pipe Plantz Block Plant)manualimportplcocrdaily_production)lbozgal)bothaggregate_only{Gzt?gư>?gMb@?g?g?))rr)rton)rr)rr)rr)rrz^\d{4}-\d{2}-\d{2}$) )Fly Ashrr)Cementrr)Sandrr)Rockrr)Airrr)z2000 SCCrr)Retarderrr) Acceleratorrr)z Water 80%rr)z Water 20%rr)GREYREGHEHHEBRIXIPEXctjttd}|j d|j dtj |_|S)N)timeoutzPRAGMA journal_mode=WALzPRAGMA busy_timeout=10000)sqlite3connectstrrexecuteRow row_factoryconns /var/www/html/togen/qc_store.py_connectr0sA ??3z?B 7DLL*+LL,-{{D KcHtjjdS)Nz%Y-%m-%d %H:%M:%S)rutcnowstrftimer1r/_nowr6s ??  % %&9 ::r1c t5t5}|jd|jd|jd|jd|jd|jd|jd|jd|jd |jd |jd |jd |jd jDchc]}|d }}d|vr|jd|jd|jdjDchc]}|d }}d|vr|jdd|vr|jd|jdjDchc]}|d }}d|vr|jdd|vr|jdd|vr|jdt |t ,t |t tjtt||jddddddycc}wcc}wcc}w#1swY!xYw#1swYyxYw)z?Create tables/indexes if missing and seed defaults. Idempotent.aCREATE TABLE IF NOT EXISTS materials ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE COLLATE NOCASE, capture_unit TEXT NOT NULL, consumption_unit TEXT NOT NULL, consumption_decimals INTEGER NOT NULL DEFAULT 0, sort INTEGER NOT NULL DEFAULT 0, active INTEGER NOT NULL DEFAULT 1, created_at TEXT, updated_at TEXT)aYCREATE TABLE IF NOT EXISTS mixtures ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, segment TEXT NOT NULL, active INTEGER NOT NULL DEFAULT 1, current_version INTEGER NOT NULL DEFAULT 1, created_at TEXT, updated_at TEXT)zpCREATE UNIQUE INDEX IF NOT EXISTS idx_mixtures_name ON mixtures(segment, name COLLATE NOCASE)a3CREATE TABLE IF NOT EXISTS mixture_versions ( id INTEGER PRIMARY KEY AUTOINCREMENT, mixture_id INTEGER NOT NULL, version INTEGER NOT NULL, created_at TEXT, created_by TEXT, note TEXT, UNIQUE(mixture_id, version))aCREATE TABLE IF NOT EXISTS mixture_targets ( id INTEGER PRIMARY KEY AUTOINCREMENT, version_id INTEGER NOT NULL, material_id INTEGER NOT NULL, target REAL NOT NULL DEFAULT 0, minus REAL NOT NULL DEFAULT 0, plus REAL NOT NULL DEFAULT 0, UNIQUE(version_id, material_id))a?CREATE TABLE IF NOT EXISTS batches ( id INTEGER PRIMARY KEY AUTOINCREMENT, batch_date TEXT NOT NULL, segment TEXT NOT NULL, batch_number TEXT NOT NULL DEFAULT '', mixture_id INTEGER, mixture_version INTEGER, mixture_name TEXT NOT NULL DEFAULT '', yards REAL NOT NULL DEFAULT 0, rock_moisture REAL, sand_moisture REAL, initials TEXT NOT NULL DEFAULT '', notes TEXT NOT NULL DEFAULT '', source TEXT NOT NULL DEFAULT 'manual', created_by TEXT, created_at TEXT, updated_by TEXT, updated_at TEXT, deleted INTEGER NOT NULL DEFAULT 0)zgCREATE INDEX IF NOT EXISTS idx_batches_date ON batches(batch_date, segment, deleted)aCREATE TABLE IF NOT EXISTS batch_lines ( id INTEGER PRIMARY KEY AUTOINCREMENT, batch_id INTEGER NOT NULL, material_id INTEGER NOT NULL, material_name TEXT NOT NULL DEFAULT '', unit TEXT NOT NULL DEFAULT '', target REAL, min_ok REAL, max_ok REAL, actual REAL, out_of_range INTEGER NOT NULL DEFAULT 0, UNIQUE(batch_id, material_id))z\CREATE INDEX IF NOT EXISTS idx_batch_lines_batch ON batch_lines(batch_id)zCREATE TABLE IF NOT EXISTS batch_custom_values ( batch_id INTEGER NOT NULL, field_id TEXT NOT NULL, value TEXT NOT NULL DEFAULT '', UNIQUE(batch_id, field_id))zlCREATE INDEX IF NOT EXISTS idx_batch_custom_values_batch ON batch_custom_values(batch_id)zVCREATE TABLE IF NOT EXISTS meta ( key TEXT PRIMARY KEY, value TEXT)zPRAGMA table_info(batches)name import_keyzBALTER TABLE batches ADD COLUMN import_key TEXT NOT NULL DEFAULT ''zxCREATE INDEX IF NOT EXISTS idx_batches_import_key ON batches(batch_date, segment, source, import_key)zPRAGMA table_info(materials)aggregate_childrenzLALTER TABLE materials ADD COLUMN aggregate_children TEXT NOT NULL DEFAULT ''aggregate_report_modezSALTER TABLE materials ADD COLUMN aggregate_report_mode TEXT NOT NULL DEFAULT 'both'zPRAGMA table_info(mixtures)mix_codezAALTER TABLE mixtures ADD COLUMN mix_code TEXT NOT NULL DEFAULT ''included_materialszKALTER TABLE mixtures ADD COLUMN included_materials TEXT NOT NULL DEFAULT ''aggregate_capturezJALTER TABLE mixtures ADD COLUMN aggregate_capture TEXT NOT NULL DEFAULT ''N) _write_lockr0r*fetchall _get_meta_SEGMENTS_META_KEY _set_metajsondumps_seed_segments_seed_defaultscommit)r.rcolsmcolsmxcolss r/ init_qc_dbrMs UhjUD  8 9  8 9  @ A  3 4  7 8  : ;$  ? @  5 6  / 0  2 3  7 8  8 9$(<< ($**2(*6a& 66 t # LL   J K %)LL *%,,4HJ8q688 u , LL& ' #% / LL* + &*\\ )&++38:7!F)77 V # LL  v - LL& ' f , LL& ' T- . 6 d. >;K0L Mt kUUUx687kUUUUsP I>C.I2 I# A I2 I(!A I2. I-:BI2I>#I22I; 7I>>Jc t}|jdjddk(r9ttD]'\}\}}}|jd||||dzdz||f)|jdjddk(rBt D]8}|jd|d||f}|jd |j |d d f:y y ) zjSeed materials + mixture names only into EMPTY tables (never re-adds an admin-deleted row on restart).zSELECT COUNT(*) FROM materialsrzINSERT INTO materials(name, capture_unit, consumption_unit, consumption_decimals, sort, active, created_at, updated_at) VALUES(?,?,?,0,?,1,?,?) zSELECT COUNT(*) FROM mixtureszINSERT INTO mixtures(name, segment, active, current_version, created_at, updated_at) VALUES(?,?,1,1,?,?)rxINSERT INTO mixture_versions(mixture_id, version, created_at, created_by, note) VALUES(?,1,?,?,?)seedu"Seeded mixture — targets pendingN)r6r*fetchone enumerate_SEED_MATERIALS_SEED_MIXTURES lastrowid)r.nowir8capconscurs r/rGrG+s &C ||45>>@CqH$-o$> ; A c4 LL.sD1q5B,S9  ; ;  ||34==?BaG" TD,,Fz3,.C LL>V-QR T  THr1cDtj||f}||y||zS)zDCapture-unit value -> consumption-unit value (None on unknown pair).N) _UNIT_FACTORSget)value capture_unitconsumption_unitfactors r/convert_amountrdFs.    .>? @F ~ 6>r1c||ftvSN)r^)rarbs r/valid_unit_pairrgNs * +} < []. rDloads TypeError ValueError isinstancelistsetintaddappendrawvalsoutseenvrYs r/_parse_childrenryVs  zz# dD ! CEC  AA D= HHQK JJqM J z " :&   s"A5 B 5B B  BBcD||dk(ry tj|}t |t sygt }}|D]5} t|}||vs|j||j|7|S#ttf$rYywxYw#ttf$rY`wxYw)u,Parse a stored included_materials value (DVI-1440 P2). '' / None -> None, meaning UNSET — the D4-A legacy default applies (membership = materials that currently have targets). A valid JSON list -> de-duplicated list of ints (possibly []); bad JSON / non-list -> None (treated as unset).Nrirss r/_parse_includedr|ms  {cRizz# dD !CEC  AA D= HHQK JJqM J z ":&   s#A8 B 8B  B  BBcT| t|S|xsgDchc]}|d c}Scc}w)uuThe mixture's effective capture membership as a set of material ids. Explicit list wins (D3-A); an unset list (None) falls back to the D4-A legacy default = materials that currently have targets. A legacy mixture with NO targets resolves to the empty set — callers that must still capture (batch/scan/daily) treat that empty legacy set as 'all materials'. material_id)ro)includedtargetsts r/_effective_included_idsrs1 8}'.}" 6Am  66 6s %c t|xsgS)zParse a stored JSON id list where '' / None / bad JSON all mean the empty list (unlike _parse_included, whose None means 'unset / legacy default'). Used for aggregate_capture (DVI-1440 review #2).)r|)rts r/_parse_id_listrs 3  %2%r1c tt}}|xsgD]d}|j|}|s|jds)|j||jdxsgD]}|j|f||fS)uResolve a mixture's aggregate capture modes into a (direct, suppressed) pair used by every capture surface (batch / scan / daily). ``direct`` = aggregate ids captured DIRECTLY (they become a capture line; the operator keys the aggregate total). ``suppressed`` = the child leaf ids of those direct aggregates — they are NOT captured on this mixture (the aggregate stands in for them). An aggregate not in ``direct_agg_ids`` keeps the DVI-1423 report-only default (summed from its captured constituents), so its children stay capturable. ``mats_by_id`` maps id -> a _material_row-shaped dict (needs is_aggregate + aggregate_children). is_aggregater:)ror_rq)direct_agg_ids mats_by_iddirect suppressedaidmcids r/ _capture_planrsJF$"$ NN3  ~& JJsO239r $s# $ $ : r1c4||jvr||S|S)zsqlite3.Row has no .get(); read a possibly-absent column safely (rows read before the DVI-1423 migration lack the aggregate columns in tests).keys)rowkeydefaults r/_row_getrs chhj(3s85g5r1c tt|dd}t|ddxsd}|tvrd}|d|d|d|d|d |d t|d ||t|d S) Nr:r{r;ridr8rarbconsumption_decimalssortactive) rr8rarbrrrr:r;r)ryrAGGREGATE_REPORT_MODESbool)rchildrenmodes r/ _material_rowrsx-A2FGH S16 : DfD ))$iVN+ 23 #$: ;F tCM':&!%X  r1c t5}d}|s|dz }|dz }|j|jDcgc] }t|}}ddd|sDcgc] }|dr | }}Scc}w#1swY'xYwcc}w)uList materials. include_aggregates=False drops report-only aggregators (DVI-1423) — used by the capture-facing surfaces (batch entry, mixture target grids, imports) where aggregators must never appear.SELECT * FROM materialsz WHERE active=1z ORDER BY sort, nameNr)r0r*r@r)include_inactiveinclude_aggregatesr.sqlrIrowss r/list_materialsrs Ht' $ $C %%*.,,s*;*D*D*FGQ a GG H 9aq'899 KH HH:s(0A5A0 A5 B(B0A55A>ct5}|jd|fj}|r t|ndcdddS#1swYyxYw)N"SELECT * FROM materials WHERE id=?)r0r*rSr)r~r.rs r/ get_materialrsJ 3tll?'>++38: %(}S!d333s 0AAc t5}|jdjDcic]}|dt|}}dddg}j D]}}|ds |s|dsg}|dD]8}|j |}||j |d|d|d|d d :|j |d|d|d|d |d |d |d |Scc}w#1swYxYw)aDVI-1433: report-only aggregators, each with its leaf children resolved to {id, name, capture_unit, consumption_unit}. Feeds the Mixtures editor's 'Aggregated totals' section (which materials each aggregate totals + the aggregate's own target). Distinct from list_materials(include_aggregates=False), which the capture surfaces read and which never includes aggregators.+SELECT * FROM materials ORDER BY sort, namerNrrr:r8rarb)rr8rarbrr;)rr8rarbrr;r)r0r*r@rvaluesr_rr) rr.rImatsrvrrrcs r/list_aggregatesrsH Gt37<< 94;;C8:Ga$q))GGG C [[]  ( )* IC Ay OO1T7AfI-.~->123E1FH I  I D'1V9n- !"4 5$%&<$=%&'>%?    * J1GGGs"C&C!C&!C&&C/c ti}|xsgD]I} t|jd} t dt |jdxsd||<Kg}|j D]\}}|j|}|r|jdr|dkr1t||d|d}|Gd} d} |dD]N} |j| d} | dkr|j| } | s/t| | d| d}|E| |z } | dz } P| dk(rt|| z }t ttt || z}||kDs|d }|j||d |dt||t| |t||d |S#ttf$rYwxYw#ttf$r d||<YwxYw) aPure: given a mixture's target rows ({material_id, target, ...}, values in each material's CAPTURE unit) and a {id: material} map, return the list of aggregate-vs-parts conflicts. A conflict exists for aggregate A when A has a target > 0 AND >=1 child has a target > 0 AND the aggregate target and the summed child targets disagree once converted to the shared consumption unit: |conv(target_A) - sum conv(target_Ci)| > max(1e-6, 0.5% * larger). Converting to the consumption unit is exact because children share A's consumption unit (validated at material save). Setting only the whole, or only the children, is never a conflict. Each conflict dict carries the converted whole/parts values + delta + unit for display.r~targetrr:rarbrOrr8) aggregate_idaggregate_nameunitwholepartsdelta) rpr_rkrlmaxfloatitemsrdabsCONFLICT_ABS_TOLCONFLICT_REL_TOLrrround)rrtgetrmidrv whole_capmatrr n_childrenr child_capchildconvrtoldecs r/mixture_target_conflictsrs Dm aeeM*+C CquuX';!!<=DI  C**,$YnnS!#''"679> y#n*="#568 =  +, Cc*IA~NN3'E!)U>-B"'(:";=D| TME !OJ  ? EEM""$4s5%7H$HI 3;,-C JJ #"%f+./uc*uc*uc*  ;$J JY:&   :& DI s"F +F FFF76F7c t|jdxsdjdd}|syt|jdxsdjjt|jdxsdjj}tvrddd j tzfSt |s2tfd tD}dd d d j |d fS tdtdt|jdxsd} t|jdxsd}|jddrdnd}g}t} |jdxsgD]5} t| } | | vs| j| |j!| 7t|jdxsdjj} | t"vrd} t%} t&5t)5}|j+d|fj-}|r#||d|k7rdd|zfcdddcdddS|3|j+d|fj-s ddddddy|r||Dcgc] }||k7s | }}|s ddddddy|j+dj/Dcic]}|d| }}|D]}|j|}|ddddddyt1t3|ddrdd |dzfccdddcdddS|d|k7sedd!|d"|dd#|dd fccdddcdddS||j+d$|fj-d}||j+d%|fj-dz }|r ddddddy&|j5D]A}|d|k(r |t1t3|ddvs%dd'|dzfccdddcdddS|rt7j8|nd}|r| nd}|)|j+d(|||||||| | f }|j:}n|j+d)|||||||| |f |j=ddddddt?|dfS#ttf$rd}YwxYw#ttf$rd}YwxYw#ttf$rYwxYwcc}wcc}w#1swYnxYw#1swYrxYw)*z7Create or update a material. Returns (material, error).r8r{NP)NzMaterial name is required.rarbz Capture unit must be one of: %s., c34K|]\}}|k(s |ywrfr5).0krrZs r/ z save_material..HsCv1!s(C zConsumption unit for z must be one of: .rrrrTrOr:r;rz4SELECT id FROM materials WHERE name=? COLLATE NOCASErz%A material named '%s' already exists.#SELECT id FROM materials WHERE id=?NMaterial not found.)Nz8An aggregate must reference at least one other material.r)Nz-An aggregate child material no longer exists.ubAggregates can only contain leaf materials (one level of nesting) — '%s' is itself an aggregate.z&Every aggregate child must consume in u to match this material — 'z' consumes in 4SELECT COUNT(*) FROM batch_lines WHERE material_id=?8SELECT COUNT(*) FROM mixture_targets WHERE material_id=?)NuThis material has captured batches or recipe targets and can't become a report-only aggregate — create a new material instead.zaThis material is a child of aggregate '%s' and can't also be an aggregate (one level of nesting).zINSERT INTO materials(name, capture_unit, consumption_unit, consumption_decimals, sort, active, aggregate_children, aggregate_report_mode, created_at, updated_at) VALUES(?,?,?,?,?,?,?,?,?,?)zUPDATE materials SET name=?, capture_unit=?, consumption_unit=?, consumption_decimals=?, sort=?, active=?, aggregate_children=?, aggregate_report_mode=?, updated_at=? WHERE id=?) r)r_striplower CAPTURE_UNITSjoinrgsortedr^rminrprkrlrorqrrrr6r?r0r*rSr@ryrrrDrErWrHr)fieldsr~r8r[alloweddecimalsrrrrwrxrYrrXr.duprrIall_rowsrcrowrefs children_json report_moder\rZs @r/ save_materialr>s vzz&!'R ( . . 0" 5D 1 fjj(.B / 5 5 7 = = ?C vzz,-3 4 : : < B B DD -7$))M:RRRR 3 %CCC 7#%% %q#aVZZ0F%G%L1!MNO6::f%*+**Xt,Q!FH 5Djj-.4" AA D= HHQK OOA  vzz12?HHJLq$ LHL U||C(<P'BBB(#8D2F#KL#W$(L#122+BBB0*+t3&*DL$?Q:R#TUU3BBB U&||J N$$,HJq2 N N$$,HJq22HIBBBN"*?Aw+- "o$Q( ??YBBBN?19 8,b &dF  ,,2sD(D&-c3( )C--K LL!sD(D&-c;0  1 EBBF  $d **s z " z ":&   (ELBBBBs 2S<S2 T  T6(4T* T6/$T*T6$ T*- T 8T <T*T6!T*3 T%T*T6,!T* T6 T*)T*> T6AT*"T634T*( T*3 T6A7T*=T6S/.S/2TT TT T**T3 /T66T?ct5t5}|jd|fjs ddddddy|jd|fjd}||jd|fjdz }|r ddddddy|jd|fj D].}|t |d vsd |d zccdddcdddS|jd |f|j ddddddy#1swYxYw#1swYyxYw) zxHard delete only when unreferenced (board answer #2); returns error text otherwise so the admin deactivates instead.rNrrrrudMaterial is referenced by captured batches or mixture recipes — deactivate it instead of deleting.z:SELECT name, aggregate_children FROM materials WHERE id!=?r:uWMaterial is a child of aggregate '%s' — remove it from the aggregate before deleting.r8z DELETE FROM materials WHERE id=?)r?r0r*rSr@ryrH)r~r.rrIs r/delete_materialrs^ hjD||A(N,,4HJ7(|| B N$HJq*  F N$HJq* * EL (  FAoa0D.EFF9; mixture mix_code). Case-insensitive, whitespace-trimmed; '' when blank.r{)r)rrr`s r/_norm_mix_coders( )ur 2 8 8 : @ @ BBr1c|jdjDcic]}|dt|c}Scc}w)u{material_id: material} over ALL materials — needed for the DVI-1433 conflict math which spans an aggregate and its leaf children.rr)r*r@r)r.rIs r/ _mats_by_idrsI\\";<EEG I dG]1% % II Is;cN|d|d|dt|d|dt|ddxsdtt|ddtt|d d|d d }|rDt ||d|d}|r t ||ng}||d <| t |}t|||d <|S)Nrr8segmentrcurrent_versionr<r{r=r> updated_at) rr8rrrr<r=r>rr conflicts)rrr|rrrrr)r.r with_targetsrrvvidrs r/ _mixture_rowrs$iVYs8}%#6G2HS*b17R- S. 35, S-r 24,' CdCIs3D/EF58&tS1b I  $T*J3GZHK Jr1c t5}dg}}g}|r"|jd|j||s|jd|r|ddj|zz }|dz }|r t|nd}|j ||j Dcgc]}t ||||c}cdddScc}w#1swYyxYw)NzSELECT * FROM mixtures segment=?zactive=1z WHERE  AND z ORDER BY name COLLATE NOCASE)r0rrrrr*r@r) rrrr.rargscondsrrIs r/ list_mixturesr s  =t,bT  LL % KK  LL $  9w||E22 2C ..*6[&D c4099;=T1lJ?= = == = =sB B>B9-B>9B>>Cct5}|jd|fj}|r t||ndcdddS#1swYyxYw)N!SELECT * FROM mixtures WHERE id=?)r0r*rSr)rr.rs r/ get_mixturer sL 8tll>&=**2(* *-|D#&4888s 1AAcgt}}|xsgD]}t|ts t|j d}i}dD]0} t|j |xsd}td|||<2||vrk|j||ddk(r|ddk(r |ddk(r|j||d|d|dd|d fS#t t f$rYwxYw#t t f$rd}YwxYw) zQNormalize a targets list; drops all-zero/unknown rows. Returns (rows, error).r~)rrrrrrrrrN) rormdictrpr_rkrlrrrqrr)rrvrwrtrrurrxs r/_sanitize_targetsrs/CEC 2C#t$  cggm,-C. $C #''#,+!,C DI  $ $;   >Q 4=A#5$v,!:K  3$x.!']DLB C'C* 9!:&    z*  s#C  C" CC"C65C6c  t|jdxsdjdd}|syt|jdxsdj}t|sy|jd d rd nd }t|jd xsdjdd}t |jd\}}|rd|fS|jd} d} | Xgt } } t | tr| ngD]5} t| } | | vs| j| | j| 7|jd}d}|Xgt }}t |tr|ngD]5} t| } | |vs|j| |j| 7t}t5t5}|j!dj#}|Dchc]}|d }}|Dcgc] }|d|vs |}}|r ddddddyd}d}| K| D cgc] } | |vs|  } } t%j&| }t | }|Dcgc] }|d|vs |}}d}|Nt)|}|D cgc]#} |j| r|| dr|| |vr| %}} t%j&|}|j!d||fj+}|r1||d|k7r'dd|dt-||dfcdddcdddS|r[|j!d||fj+}|r7||d|k7r-dd|d|ddt-||dfcdddcdddS|||j!d||||||nd||nd||f}|j.}|j!d |||d!f}|j.}|D]%}|j!d"||d|d#|d$|d%f'n|j!d&|fj+} | s ddddddy't1||| d(}!|!r t3||!ng}"t5d)|Dt5d*|"Dk7}#| d(}$|#rb| d(d z}$|j!d+||$||d,f}|j.}|D]%}|j!d-||d|d#|d$|d%f't7|||$gd.}%|||$||g}&|$|%j9d/d0|&j9d/||<|%j9t;|%d z d1|&j9t;|&d z ||&j||j!d2d3j=|%zd4z|&|j?ddddddtA|dfS#ttf$rYwxYw#ttf$rYbwxYwcc}wcc}wcc} wcc}wcc} w#1swYcxYw#1swYgxYw)5zCreate or update a mixture. A targets change appends a new version; a name/active-only change does not. Returns (mixture, error).r8r{Nr)NzMixture name is required.rrNUnknown segment.rTrOrr<(rr=r>SELECT id FROM materialsrr~)NzUnknown material in targets.rzASELECT id FROM mixtures WHERE segment=? AND name=? COLLATE NOCASEzA mixture named 'z' already exists for rzKSELECT id, name FROM mixtures WHERE segment=? AND mix_code=? COLLATE NOCASEz Mix code 'z' is already used by 'z' for zINSERT INTO mixtures(name, segment, active, current_version, mix_code, included_materials, aggregate_capture, created_at, updated_at) VALUES(?,?,?,1,?,?,?,?,?)rQCreatedzvINSERT INTO mixture_targets(version_id, material_id, target, minus, plus) VALUES(?,?,?,?,?)rrrr )NMixture not found.rc3@K|]}|d|d|d|dfywr~rrrNr5rrs r/rzsave_mixture..s2)-(!H+qz1V9M)c3@K|]}|d|d|d|dfywrr5rs r/rzsave_mixture..s2-]+Qx[!G*aiP-rz|INSERT INTO mixture_versions(mixture_id, version, created_at, created_by, note) VALUES(?,?,?,?,?)zTargets updatedzzINSERT INTO mixture_targets(version_id, material_id, target, minus, plus) VALUES(?,?,?,?,?))zname=?zactive=?zcurrent_version=?z mix_code=?z updated_at=?zincluded_materials=?zaggregate_capture=?zUPDATE mixtures SET rz WHERE id=?)!r)r_r is_segmentrrormrnrprkrlrqrrr6r?r0r*r@rDrErrS_segment_label_connrWrrr"_apply_targets_to_daily_productioninsertlenrrHr )'rractorr8rrr<rerrraw_incl included_idsseen_irxrYraw_aggagg_capture_idsseen_arXr.mat_rowsrIknownrbad included_jsonincl_setagg_capture_jsonrrcode_dupr\rrcur_videxistingchanged new_versionset_colsset_argss' r/ save_mixturer83s vzz&!'R ( . . 0" 5D 0&**Y'5:6<<>G g '**Xt,Q!F6::j)/R0668"=H$VZZ %:;LGS Syzz./HL!35f (48(b 'A F 1 ##A& 'jj,-GO"$ce'6'B *A F 1 &&q) * &C mhjmD<< :;DDF"*+Q4++!CQQ}%5U%BqCC 7 mmm    # (4B!qEzABLB JJ|4M<(H")JQQ}-=-IqJGJ   &$T*J*9>>!$A~)F%h9O9 $zz/: ll O dO%XZ  J&#d)z*A)$8::GmmmJ ||02981DFFNhj Z/8D>Z3Ohv.'g689UmmmZ  ,,0w"/";%5%A!rc CJ,,>S%35C--C Q E!M*AhK7QvYOQ Q ,,B *}..6hj 1MmmmN&dJE+D':2H) '))-#+---  /0K!"34q8 llNc5:KLNmm UALLIa .( QwZ6SUU34[Q(Hfk8SAH(#9:=1+H  13HIH  13CD OOJ ' LL&8)<<}L  [mm\ z "D ((Ez*  z*  ,CC K95mmmms V/9 W= X$W4, W8W4> W  W W4X% W4/ W%9W%=&W4# W*0W*4W4 (W/1AW4 XA W4' X:B"W4X-E%W4X/WWWWW44W= 9XX c t5}|jd|fj}|Dcgc]#}|d|d|d|dt||dd%c}cdddScc}w#1swYyxYw)NzZSELECT * FROM mixture_versions WHERE mixture_id=? ORDER BY version DESCr created_at created_bynoter)rr:r;r<r)r0r*r@r)rr.rrIs r/mixture_versionsr=s Pt|| ,.8]<>$ 5E,&**Y'5:6<<>G g 'E) > : @bAGGI#2NE./S% 7(;(@q"ABg2:jjo * E#J :"3Zc :FJJz28b9??A#2FE*G,2399;ETBE'N H%1 2 8 8 :F &- 7fXE(O **\ "C#*-Z*?c#hTl $;) z "/./z* :3c999 : z "#"l $;#s6:+GG4HG10G14HHH,+H,c \d\}}d}|d1|jd|dfj}|sy|d|d}}|rt||d||dni}|jd Dcic]}|d | }}|jD cic]\} }| t |} } }t |rt t|d d ng| \} } gt}} |xsgD]}t|ts t|jd }|j|}|||vrH| |dr|| vrU|| vrZ|j||jd}|dvrd}n t!dt#|}|j|}d}|r!||d|d|d|cxkr|dksnd}| j%||d|d|r|dnd|r|dnd|r|dnd||d| ||dfScc}wcc}} w#ttf$rY*wxYw#ttf$rdddd|dzfcYcSwxYw)zSnapshot batch lines from the mixture's CURRENT version targets scaled by yards. Returns (line_rows, mixture_name, mixture_version, error).r{NNrr )NNNrr8rrArrr>r{r~rrErRrzActual for %s must be a number.rr?r@rOrarr~ material_namerrr?r@rE out_of_range)r*rSrCrrrrrrormrrpr_rkrlrqrrrr)r.r[linesmix_name mix_versionmrowrHrI materialsrY mats_metarrrvrwrtrr actual_rawrErKrLs r/ _build_linesriIs%Hk D \&||?"<0244?RFJCEC ) #t$  cggm,-CmmC  ;#+  S>. )&  J    WWX&  #F YS% "34zz# V'DN,FN.h6CT(^CC V'(,d8n$(,d8n$(,d8n$c   E) T +t ++eND:&   (z* YT4)JSQW[)XXX Ys0) G* G/3G5!H 5HH H+*H+ct|\}}|rd|fS|#t|tr|jdnd}t }t 5t 5}t|||\}} } }|rd|fcdddcdddSt||} |jd|d|d|d|d| | |d|d |d |d |d |d ||||f} | j} |D]5}|jd| |d|d|d|d|d|d|d|df 7t|| | |jddddddt dfS#1swYxYw#1swY"xYw)zCapture one batch ticket. Returns (batch, error). ``custom_values`` is an optional {field_id: value} map for admin-defined custom fields (DVI-1452 P2); values are sanitized to the field type and never block the save.N custom_valuesaJINSERT INTO batches(batch_date, segment, batch_number, mixture_id, mixture_version, mixture_name, yards, rock_moisture, sand_moisture, initials, notes, source, created_by, created_at, updated_by, updated_at, deleted) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0)rNrrOrrArPrQrSrUrWINSERT INTO batch_lines(batch_id, material_id, material_name, unit, target, min_ok, max_ok, actual, out_of_range) VALUES(?,?,?,?,?,?,?,?,?)r~r`rrr?r@rEra)r\rmrr_r6r?r0ri_sanitize_custom_valuesr*rW_write_custom_valuesrH get_batch)rrbr#rkr[r$rXr. line_rowsrcrdcvalsr\batch_idrJs r/ create_batchrss(/JE3 Sy7A&$7O ?3UY &C hjD0 #13~3F  c,'  301  N+  .5c'l  _-  _-  C O &-c'l  #h-  LCHHJ,Fs<(B  c,' *6s<7H  c,' *6s<7H  r1ct5}|jd|fj}|s dddyt|}|jd|fj Dcgc].}|d|d|d|d|d|d|d t |d d 0c}|d <t |||d <|cdddScc}w#1swYyxYw)NruzMSELECT * FROM batch_lines WHERE batch_id=? ORDER BY idr~r`rrr?r@rErar_rbrk)r0r*rSrr@r_load_custom_values)rrr.rrvrIs r/roros tllK$;((0    C \\&(0{44 from the earliest captured batch), optionally scoped to one segment, plus converted+rounded consumption values (the ordering/usage view — conversion uses each material's CONSUMPTION unit with its administered rounding decimals). Includes every active material (zero rows included so the report shape is stable) plus any inactive material the window's batches actually used. Shared by daily_totals (start == end) and range_totals. Returns {batch_count, total_yards, materials}.z b.deleted=0zl.actual IS NOT NULLNzb.batch_date>=?zb.batch_date<=?z b.segment=?zSELECT l.material_id, SUM(l.actual) AS total, COUNT(l.actual) AS n, SUM(l.out_of_range) AS oor FROM batch_lines l JOIN batches b ON b.id = l.batch_id WHERE rz GROUP BY l.material_idr~z deleted=0z batch_date>=?z batch_date<=?rz:SELECT COUNT(*) AS n, SUM(yards) AS yd FROM batches WHERE rrrrtotalrrarbr8rrrrLF) r~r8racaptured_totalrbconsumption_totalr n_capturedrrr:rTr;) r~r8rarrbrrrrrr:r;ryd) batch_count total_yardsrf) rrrr*r@rSrr_rdrrornupdate)r. date_startdate_endrr rrrIsumsbcondsbargsbrowr leaf_rowsrrsrowrr[agg_rowshidden_childrenown_captown_consrrrs r/_totals_for_windowr+sk2 3E D &' J LL"#KK ]# G ll5) *-F FC*.c4)@)I)I)K LAAm a  LD L]F E o& Z  MM/" LL k" W <<D ,,v   % ''/xz '+ll5'77?xz CM!  CD C I  ~  $i8}D xx}*.g%#CeS%8!"457F /# #$6 7%)%5#(c2H.I"J;?$'(>$?.249>48tE{/aa!  # :HeO >>"#h-  xxD "-1DM(Ss!(C,?"%&8"9;B>A +, 4CMM#&E23?233 4 t9c&k/+/hD #$6 7!&uc2H.I!J$'(>$?.249>48tE{/aa "&s+?'@"A%()@%A T & '+; ;  " "3';#< =A >J D($i ~ h HSM*  'C9,< KK # ' ( 9>:, E M  Cs * M;;Nct5}t||||}ddd||xsdd|d|ddS#1swYxYw)zPer-material captured + converted/rounded consumption totals for one day (see _totals_for_window). Stable-shape single-day grid.Nr{rrrf)rNrrrrf)r0r)rNrr.rs r/ daily_totalsrs_ Ft tZW EF!W]'-8H{^ FFs 7Ac 2t5}t||||}g}|sLt|D]>}|d}t||||}|ds|j||d|d|d|dd@ddd|xsd||xsdd|d|dd S#1swY$xYw) uPer-material captured + converted/rounded consumption totals over an inclusive [start_date, end_date] window (start_date None => from the first captured batch) — the date-range ordering view (DVI-1326 P2). When no segment filter is given, also returns a per-plant (segment) breakdown so consumption can be split by production line (Wet Cast today; Pipe/Block appear here automatically once their sources land). Segments with no batches in the window are omitted.rrlabelrrf)rrrrrfNr{) start_dateend_daterrrrfsegments)r0r_read_segmentsrr) rrrr.overallrsegssts r/ range_totalsrs  t$T:xI%d+ I'j(AFm$OO#$s7|')-'8')-'8%' _ %  !&BH=b}-}-[)    s9B %B  Bct5}|jd|fj}dddrt|dxsdSdS#1swYxYw)zuNumber of distinct segments with a Daily Production entry captured on ``batch_date`` (source='daily_production').zoSELECT COUNT(DISTINCT segment) AS n FROM batches WHERE batch_date=? AND source='daily_production' AND deleted=0Nrrr0r*rSrp)rNr.rs r/ daily_production_submitted_countrsa &tll M M$8: & "%3s3x}1 +!+ &&s "A  Act5}|jdj}|r|ds dddy|d}|jd|fjd}dddtxsddS#1swYxYw)zThe most recent Pipe/PLC import (source='import') as {batch_date, count} over that latest date, or None when nothing imported.zLSELECT MAX(batch_date) AS d FROM batches WHERE source='import' AND deleted=0dNzVSELECT COUNT(*) AS n FROM batches WHERE source='import' AND batch_date=? AND deleted=0rr)rNcountr)r.rrrs r/ last_importrs  "tll 233;8: #c( " " H LL C D (*S " "c!&qk 22 " "s(A?)A??Bct5}|jdj}dddr |dr|dSdS#1swYxYw)uThe latest captured (non-deleted) batch date across all sources, or None — the data-span anchor for the Reports tile summary.z8SELECT MAX(batch_date) AS d FROM batches WHERE deleted=0Nr)r0r*rS)r.rs r/last_batch_datersY Stll FHHP  Ss3x3s81T1SSs AA cV|jd|fj}|r|dS|S)Nz"SELECT value FROM meta WHERE key=?r`r)r.rrrs r/rArAs/ ,,;cV D M M OC3w<+G+r1c,|jd||fy)Nz\INSERT INTO meta(key, value) VALUES(?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value)r*)r.rr`s r/rCrC sLL >@CU|Mr1rctjddt|xsdjj jdddS)Nz [^a-z0-9]+_r{r)resubr)rr)rs r/_slugify_segmentr)sD 66-c%+2&6&<&<&>&D&D&F G M Mc RSVTV WWr1c gt}}t|trt|D]\}}t|tst j ddt|jdxsdjj}|r||vrmt|jdxsdjxs|} t|jd}|j||j||t!|jdd |d |j#d |S#ttf$r |dzdz}YpwxYw) zNormalize a raw segments list -> [{id,label,active,sort}] sorted by sort. Drops entries without a usable id; de-dupes ids (first wins); self-healing. [^a-z0-9_]+r{rrrrOrPrTrrrrc|d|dfSNrrr5)rs r/z"_coerce_segments..AAfIqw/r1r)rormrnrTrrrr)r_rrrprkrlrqrrrr)datarvrwrYentsidrrs r/_coerce_segmentsr-s4CEC$o PFAsc4(&&SWWT]0b1779??ACC#+(.B/557>3E $3776?+ HHSM JJcE"&swwx'>"?O P PHH/H0 J z* $A| $sD88EEc ttDcgc]&\}}|tj||d|dzdzd(c}}Scc}}w)NTrOrPr)rTSEGMENTSSEGMENT_LABELSr_)rYrs r/rFrFEsP/8/B D'+q!~11!Q74!er\ # DD Ds+Act|t} |rtj|nd}t |r|StS#tt f$rd}Y.wxYw)zThe current segment registry for an open connection (self-healing; falls back to the legacy constants if the meta key is somehow absent).N)rArBrDrjrkrlrrFr.rtrs r/rrJs\ D, -C"%tzz#4 DD ??n.> ?? z "sAAAcDt|D]}|d|k(s |dcS|S)Nrr)r)r. segment_idrs r/rrUs4 D ! T7j W:  r1ct5}t|}ddd|rSDcgc] }|ds | c}S#1swY#xYwcc}w)zySegment registry [{id,label,active,sort}], sorted. Active-only by default (the set new-capture pickers should offer).Nr)r0r)rr.segsrs r/ list_segmentsr\sJ $td#$#4IT)IQx[!)II$$)Is 9 AAAcFt|Dcgc]}|d c}Scc}w)Nrrrrrs r/ segment_idsrds *.s,!qw#~,N)Nz,A segment with a matching id already exists.TrrrrPr)r)rrr?r0ranyrrrrCrBrDrErrH)rr.rrrs @r/ add_segmentr|s    " " $E 0 5 !C D hjDd# ,t, ,G 3$ T!:!F)!:AFKM N$*DJJ7G7M,NO  9";  9sB D !C>"D 3C> C9AC>%D 9C>>D D  Dc t5t5}t|}tfd|Dd}|s ddddddy|2t |j }|s ddddddy||d<|t ||d<t|}t|ttj||jddddddtfdDddfS#1swY(xYw#1swY,xYw)zgRename (label) and/or activate/deactivate a segment. The id is immutable. Returns (segment, error).c34K|]}|dk(s |ywrr5rrrs r/rz!update_segment..s@Q!D'Z*?q@rNrrrrc34K|]}|dk(s |ywrr5rs r/rz!update_segment..s:qAdGz$9:r) r?r0rnextr)rrrrCrBrDrErH)rrrr.rrlbls` r/update_segmentrs hjDd#@$@$G+   e*""$C8"F7O  #F|F8 %$*DJJt,<=  :D:D A4 GGs: C>$C2C>C2&C>7AC2 C>2C; 7C>>Dc t5t5}t|}|Dcic]}|d| }}g}|xsgD]#}||vs|||vs|j||%|D]}||vs|j|t |dD] \}}|dz|d<t |}t |ttj||jddddddScc}w#1swYxYw#1swYSxYw)zReorder segments to match ``order_ids`` (unlisted ids keep their relative order at the end). Returns the reordered registry.rrOstartrPrN) r?r0rrrrTrrCrBrDrErH) order_idsr.rrby_idorderedrrs r/reorder_segmentsrs hjDd#%)*4!**O +Ce|c ' 9uSz* + "Aq! "gQ/ DAqBAfI ($*DJJt,<=  K+ KE C;C/ C*C/C/ C/)A/C/C;*C//C8 4C;;Dct5}|jd|fjr dddyt|jd|fjcdddS#1swYyxYw)z4True if any batch or mixture references the segment.z-SELECT 1 FROM batches WHERE segment=? LIMIT 1NTz.SELECT 1 FROM mixtures WHERE segment=? LIMIT 1)r0r*rSr)rr.s r/segment_in_userso 't <<G#  ''/xz 2''DLL < M#8:' '''s#A*)A**A3c tryt5t5}tfdt |Ds ddddddyt |Dcgc] }|dk7s |}}t |t tjt||jddddddycc}w#1swYxYw#1swYyxYw)zHard-delete a segment only when unreferenced; otherwise the caller should deactivate it (mirrors the materials rule). Returns (ok, error).)FuGSegment is referenced by batches or mixtures — deactivate it instead.c3.K|] }|dk(ywrr5rs r/rz!delete_segment..sGQ1T7j(GrN)FrrTN) rr?r0rrrCrBrDrErrH)rr.rrs` r/delete_segmentrsj!1 hjDG.2FGG,*$/Ia1T7j3HII$*DJJ7G7M,NO  J sE CCC C B>*B>.?C-C>CC CCreport_optionsc gddS)NF)exclude_material_idsshow_zero_capturer5r5r1r/_default_report_optionsrs $&U CCr1clt}t|ts|Sgt}}|j dxsgD]<} t |}|||vr||vs|j||j|>||d<t|j d|d<|S#t tf$rYtwxYw)zNormalize a raw report_options blob. Drops excluded ids that aren't ints (or, when valid_ids is given, that no longer name a material) so a deleted material never lingers in the exclude list.rr) rrmrror_rprkrlrqrrr)r valid_idsoptsexrwrxrYs r/_coerce_report_optionsrs # $D dD ! 35Bhh-.4"  AA  Qi%7  D= HHQK IIaL $&D  $TXX.A%B CD  K:&   s B!!B32B3cBt5}t|t} |rtj|ni}|jdjDchc]}|d }}dddtS#t t f$ri}YVwxYwcc}w#1swY1xYw)uhCurrent report-content options (self-healing — drops excluded ids that no longer name a material).rrN) r0rA_REPORT_OPTIONS_META_KEYrDrjrkrlr*r@r)r.rtrrIvalids r/get_report_optionsrs Ft67 &)4::c?rD89BBDFQ4FF F "$ .. :& D F FFs9BA9!B B#B9B  B B  BBc t5}|jdjDchc]}|d }}dddt|}t5t5}t |t tj||jdddddd|Scc}w#1swYrxYw#1swY$xYw#1swY|SxYw)zFPersist report-content options; returns the normalized stored options.rrN) r0r*r@rr?rCrrDrErH)optionsr.rIrr[s r/save_report_optionsr s Ft89BBDFQ4FFF #7E 2E hjD$0$**U2CD  L FFF Ls?"B, B'B, C 5B8C'B,,B58C =CCc Bt|xsdjj}|tvrdddj tzfSt }t 5t5}|jd|fj}|s ddddddytt|dds ddddddy |jd |||f|jddddddt|dfS#1swYxYw#1swY"xYw) uUpdate just an aggregate material's report display mode — the Reporting sub-tab (DVI-1424) surfaces the P6 aggregate_report_mode alongside the material editor. Returns (material, error); only aggregates carry a mode.rNzReport mode must be one of: %s.rrrr:r{)Nz5Only an aggregate material has a report display mode.zEUPDATE materials SET aggregate_report_mode=?, updated_at=? WHERE id=?)r)rrrrr6r?r0r*rSryrrHr)r~rrXr.rs r/set_report_moder s t~v  $ $ & , , .D ))7 "89:; ; &C  hj Dll?'>++38: .    x-A2FGP     k2 4     $d **    s< D)%D DD 6D%D ,D D DDc Ft||ni}t|d|dfdfd}t|}||jd|d<|jdxsgDcgc]$}t|||jd&c}|d<|Scc}w)aXReturn a COPY of a range_totals result with the report-content options applied to its material rows (top-level + per-plant sections). Grand totals (batch_count / total_yards) are batch-level and left untouched. Default options reproduce the pre-DVI-1424 ordering-sheet behavior: drop zero-capture leaves and zero-sum aggregates.rrc|jdvryry|jdrt|jdSt|jdS)Nr~FTrrr)r_r)rexclude show_zeros r/keepz!filter_range_report..keep>sR 55 7 *  55 123 3AEE,'((r1cH|xsgDcgc] }|s |c}Scc}wrfr5)rfrrs r/filtz!filter_range_report..filtGs!%O8aQ888s rfr)rf)rrorr_) reprrrrvrrrrs @@@r/filter_range_reportr4s "W-@'b ID$-./G()I)9 s)CCGGK01C !$!4!:=AaeeK.@)AB=C O J=s-)Bbatch_ticket_fields)rOzBatch #rPzRock moisture %rQzSand moisture %rSInitialsrUNotesc#&K|] \}}| ywrfr5rrrs r/rrjs Ktq! Kc t|tsi}i}tdD]X}|j|}t|tr|ni}tDcic]}|t |j|d c}||<Z|Scc}w)aNormalize a raw batch_ticket_fields blob into a stable {segment: {field: bool}} map covering every SEGMENT x tunable field. A missing segment/field defaults to True (enabled) so the capture forms show it (zero behavior change); unknown segments/fields are dropped.Tr)rmrrr__BATCH_TICKET_FIELD_KEYSr)rrvrrrs r/_coerce_batch_ticket_fieldsr"ms dD ! CD1QhhsmT*c7OP!AtCGGAt,--PCQ JQs#A>ct5}t|t}ddd rtj|ni}t|S#1swY.xYw#t t f$ri}Yt|SwxYw)uCurrent per-segment Batch Ticket field visibility (self-healing — every segment x tunable field present, default enabled).N)r0rA_BATCH_TICKET_FIELDS_META_KEYrDrjrkrlr"rs r/get_batch_ticket_fieldsr%|su =t;<="%tzz#2 't ,, == z " &t ,,A A AA32A3c t|}t5t5}t|tt j ||jdddddd|S#1swYxYw#1swY|SxYw)zPersist per-segment Batch Ticket field visibility; returns the normalized stored config (a stable all-segment/all-field map).N)r"r?r0rCr$rDrErHconfigr[r.s r/save_batch_ticket_fieldsr*si ( /E hjD$5tzz%7HI  L L" A05A$A0$A- )A00A: scan_settingsdi@c 8t|tsi} ttt |j dt }tttt|}|t|j dddS#ttf$r t }YSwxYw)zNormalize a raw scan_settings blob into {thumb_size:int, auto_ocr:bool}. thumb_size is clamped to [_SCAN_THUMB_MIN, _SCAN_THUMB_MAX]; a bad value falls back to the default. auto_ocr defaults True (recommended default). thumb_sizeauto_ocrT)r0r1)rmrrprrr__SCAN_THUMB_DEFAULTrkrlr_SCAN_THUMB_MINr_SCAN_THUMB_MAXr)rtss r/_coerce_scan_settingsr6s dD !! uTXXl4GHIJ K _c/26 7B$txx D/I*J KK z "! !s1BBBct5}t|t}ddd rtj|ni}t|S#1swY.xYw#t t f$ri}Yt|SwxYw)z@Current Scan Tickets settings (self-healing; defaults if unset).N)r0rA_SCAN_SETTINGS_META_KEYrDrjrkrlr6rs r/get_scan_settingsr9ss 7t567"%tzz#2 ! && 77 z "  &&r&c t|}t5t5}t|tt j ||jdddddd|S#1swYxYw#1swY|SxYw)zDPersist Scan Tickets settings; returns the normalized stored config.N)r6r?r0rCr8rDrErHr(s r/save_scan_settingsr;sg !& )E hjD$/E1BC  L Lr+ custom_fields)textnumberrdropdowncheckboxc:gt}}ttd}t|tr8t |D])\}}t|t st jddt|jdxsdjj}|r||vrnt|jdxsdj}|st|jdxsdjj}|tvrd}g} |d k(rc|jd } t| ttfr<| D]7} t| jd d } | s"| | vs'| j| 9g} |jd } t| ttfr;| D]6}t|j}||vs!|| vs&| j|8 t|jd}|j%||j||d d || t'|jdd| |t'|jddd,|j)d|S#t t"f$r |dzdz}YwxYw)zNormalize a raw custom_fields blob -> stable [{...}] sorted by sort. Drops entries without a usable id/label; de-dupes ids; scopes segments to known ids (an unknown/dropped segment reverts the field to all-segments); self-healing.Trrr{rrtyper=r?choicesNxrrrOrPrequiredFrrrrBrCrErrrc|d|dfSrr5)fs r/rz'_coerce_custom_fields.. rr1r)rorrmrnrTrrrr)r_rrCUSTOM_FIELD_TYPEStuplerrrprkrlrqrr)rrvrwr,rYrfidrftyperC raw_choicesrrraw_segsrrs r/_coerce_custom_fieldsrOsW CEC  T2 3E$o% :FAsc4(&&SWWT]0b1779??ACC#+(.B/557E16288:@@BE..G "!ggi0 kD%=9(.FLLN4C0'!1#NN1-.Dwwz*H(T5M2!'AA AEzatm A' $3776?+ HHSM JJE$3K"SWWZ5O0P $swwx67 9 :C% :LHH/H0 Jz* $A| $s.JJJct|t} |rtj|ng}t |S#tt f$rg}Yt |SwxYwrf)rA_CUSTOM_FIELDS_META_KEYrDrjrkrlrOrs r/_read_custom_fieldsrRsX D1 2C"%tzz#2 ! && z "  &&s6AAct5}t|}ddd|sDcgc] }|ds | }}|Dcgc]}|dr||dvr|}}S#1swY?xYwcc}wcc}w)zCustom field registry, sorted. Active-only by default (the set capture forms render). ``segment`` filters to fields scoped to that segment (a field with an empty ``segments`` list applies to every segment).Nrr)r0rR)rrr.rrHs r/list_custom_fieldsrTs +t$T*+ #3q{!33#D:'Qz]*BDD M++4Ds A A!A!A&Ac>tdD]}|d|k(s |cSy)NTrr)rT)field_idrHs r/get_custom_fieldrW(s,  6 T7h H r1cZ dtjjddz}||vr|S+)NcfrP)uuiduuid4hex) existing_idsrKs r/_mint_custom_field_idr^/s4 TZZ\%%cr** l "J r1cpt|xsijdxsdj}|syt|xsijdxsdjj}|tvryt 5t 5}t|}t|Dchc]}|d c}}||||xsijdxsgt|xsijd d |xsijd xsgt|Dcgc]}|d  c}d dzdd}|j|t|}t|ttj ||j#ddddddt%dfScc}wcc}w#1swY(xYw#1swY,xYw)zCreate a custom field. ``fields`` = {label, type, choices?, required?, segments?}. The id is minted once and immutable. Returns (field, error).rr{NzField name is required.rBr=NzUnknown field type.rrCrEFrrrrrPTrFN)r)r_rrrIr?r0rRr^rrrrrOrCrQrDrErHrW) rrrLr.defsrHrKrr[s r/add_custom_fieldrc6s 2""7+1r 2 8 8 :E . 2""6*4f 5 ; ; = C C EE &&*  hj D"4(#d$;QtW$;<5%!.Us>Q!D'X*=q>rN)NUnknown field.rr{r`rBrarCrErr)r?r0rRrr)r_rrrIrrOrCrQrDrErHrW)rVrr.rbrrrLr[s` r/update_custom_fieldrhOs\rF hjD"4(>$>E)  f  7+1r288:E6$F7O V  6*0b1779??AE..2#F6N   & 9 5 ;F9   !%fjj&G -G G GGc t5t5}t|}|Dcic]}|d| }}g}|xsgD]#}||vs|||vs|j||%|D]}||vs|j|t |dD] \}}|dz|d<t |}t |ttj||jddddddScc}w#1swYxYw#1swYSxYw)zzReorder fields to match ``order_ids`` (unlisted keep their relative order at the end). Returns the reordered registry.rrOrrPrN) r?r0rRrrrTrOrCrQrDrErH) rr.rbrHrrrKrr[s r/reorder_custom_fieldsrjps hjD"4(%)*4!**O +Ce|c ' 9uSz* + "Aq! "gQ/ DAqBAfI %g.$/E1BC  L+ Lrct5}t|jd|fjcdddS#1swYyxYw)z6True if any batch has captured a value for this field.z:SELECT 1 FROM batch_custom_values WHERE field_id=? LIMIT 1N)r0rr*rS)rVr.s r/custom_field_in_userls@ %tDLL H K!%%%%s *?Ac tryt5t5}t|}t fd|Ds ddddddy|Dcgc] }|dk7s |}}t |t tjt||jddddddycc}w#1swYxYw#1swYyxYw)zHard-delete a field only when unreferenced; otherwise the caller should deactivate it (a referenced field keeps its captured values, which still resolve a label). Returns (ok, error).)Fu4Field has captured values — deactivate it instead.c3.K|] }|dk(ywrr5rfs r/rz&delete_custom_field..s511T7h&5rN)Frgrr) rlr?r0rRrrCrQrDrErOrH)rVr.rbrHs` r/delete_custom_fieldros8$N hjD"4(555* 7a1T7h#677$/**2489 ;   8  sF C!B<CB< B7#B7'?B<&C7B<<C CCc|y|d}|dk(r|dvrdSdS|dk(r9 t|}|t|k(rt t|St |S|dk(r2t |j }tj|r|SdS|d k(r)t |j }|d r||d vr|SdSt |j dd S#ttf$rYywxYw) uCoerce a raw submitted value to its stored string form per field type. Never raises / never blocks (required is a soft flag) — a bad value stores as ''. Returns the normalized string.Nr{rBr@)Fr{0rfalseFalseN1r>rr?rCi) rrkrlrpr)reprrrXrY)fieldrtrLrHrs r/_coerce_custom_valuerws { &ME !LLsTRTT  c A 3q6ks3q6{6tAw6  HNN NN1%q-2-  HNN y)Q% 2B-BqKK s8>> DS !!:&  s CC'&C'c t|tsiSt|Dcic]}|ds |d|}}i}|jD]4\}}|j t |}|s#t ||||d<6|Scc}w)zMap {field_id: value} -> {field_id: normalized_str} keeping only KNOWN, ACTIVE fields (a value for an unknown/inactive/absent field is dropped).rr)rmrrRrr_r)rw)r. raw_valuesrHrrvrKvalrvs r/rmrms j$ ' !4T!: JAakQtWaZ JE J C$$&<S #c(# /s;E$K < J Ks BBc|jd|f|xsijD]\}}|jd|||fy)z|Replace a batch's custom values (values already sanitized). A '' value is still stored so an explicit clear round-trips.z0DELETE FROM batch_custom_values WHERE batch_id=?zHINSERT INTO batch_custom_values(batch_id, field_id, value) VALUES(?,?,?)N)r*r)r.rrrrKrzs r/rnrnsP LLCh[Q\r((*3S  &S1 33r1cx|jd|fjDcic] }|d|d c}Scc}w)Nz@SELECT field_id, value FROM batch_custom_values WHERE batch_id=?rVr`r)r.rrrIs r/rrsC/3||J  0XZ !!AjM1W: % !! !s7ocr_region_templatesocr_template_assignments))rNDater)mixtureMixture)rAYardsrrrrc#&K|] \}}| ywrfr5rs r/rrs&PTQq&Prz ^mat:(\d+)$z^custom:([a-z0-9_]+)$c 0t|tsy t|jdt|jd}}t|jdt|jd}}t tdtd|tdtd|f\}}t tdtd|tdtd|f\}}||z dks||z dkry||||d S#tt f$rYywxYw) zNormalize a raw rect into {x0,y0,x1,y1} clamped to [0,1] and ordered, or None if it isn't a usable rect (non-numeric or too small to OCR).Nx0y0x1y1rrr)rrrr) rmrrr_rkrlrrr)rtrrrrs r/ _coerce_rectr s c4 swwt}%uSWWT]';Bswwt}%uSWWT]';BSc#rl+Sc#rl-CD EFB Sc#rl+Sc#rl-CD EFB R5R"W-Bb 33 z "sA(DDDc|tvxsFttj|xsdxs"ttj|xsdSrR)OCR_TEMPLATE_HEADER_FIELD_KEYSr_OCR_MAT_FIELD_RErY_OCR_CUSTOM_FIELD_RErs r/_is_valid_ocr_field_keyr sJ 1 1 ;%++CI267 ;(..syb9:" field key, else None.r{rON)rrYrpgrouprrs r/ocr_field_material_idr s/ r*A3qwwqz?)T)r1c^tj|xsd}|r|jdSdS)zVReturn the custom-field id for a "custom:" field key, else None (DVI-1452 P3).r{rON)rrYrrs r/ocr_field_custom_idr# s- ""39"-A1771:$$r1ci}t|tr7|jD]$\}}t|st |}| |||<&|S)z4Keep only valid field keys that carry a usable rect.)rmrrrr)rtrvrrxrects r/_coerce_ocr_fieldsr* sQ C#tIIK "DAq&q)#A#!CF  " Jr1c t|tsyt|jdxsdj }t|jdxsdj dd}|r|sy||t |jddS)zENormalize one stored template dict, or None if unusable (no id/name).Nrr{r8rDrrr8r)rmrr)r_rr)rttidr8s r/_coerce_ocr_templater6 sz c4  cggdm!r " ( ( *C swwv$" % + + -ds 3D dt/A#''(BS/T UUr1cDt|t} |rtj|ng}gt }}t|trB|D]=}t|}|s|d|vs|j|d|j|?|S#tt f$rg}YswxYwNr) rA_OCR_TEMPLATES_META_KEYrDrjrkrlrormrnrrqrr)r.rtrrvrwrrs r/_read_ocr_templatesrA s D1 2C"%tzz#2CEC$ C$S)AQtWD(4! 1   J z "sB BBcXt5}t|cdddS#1swYyxYw)uKAll OCR region templates (self-healing — unusable entries/rects dropped).N)r0rr-s r/list_ocr_templatesrQ s& )t"4())) )c:tD]}|d|k(s |cSyr)r) template_idrs r/get_ocr_templaterW s*  ! T7k !H r1c|Dchc]}|d }} dtjdjz}||vr|S-cc}w)Nrtpl_)osurandomr\)r3ridsrs r/_mint_ocr_template_idr^ sM$ %q1T7 %C % rzz!}((** c>J  &s Ac t|tsyt|jdxsdj dd}|syt |jd}t 5t5}t|}t|jdxsdj r1tfd |Dr|D]}|dk(s ||c|d<|d<n&n$xs t||j||d t|ttj||j!t#fd |Dd}dddddddfS#1swYxYw#1swYdfSxYw) zCreate or update a template. A blank/absent id mints a new one; an existing id updates in place. Returns (template, error).)NzInvalid template.r8r{NrD)NzTemplate name is required.rrc3.K|] }|dk(ywrr5rrrs r/rz$save_ocr_template..r s9!qw#~9rrc34K|]}|dk(s |ywrr5rs r/rz$save_ocr_template..| s>Qqw#~q>r)rmrr)r_rrr?r0rrrrrrCrrDrErHr)templater8rr. templatesrresultrs @r/save_ocr_templaterf sd h %( x||F#)r * 0 0 24C 8D 1  X 6 7F  Fhj FD'- (,,t$*+113 39y99 T7c>-16*AfIq{  9.y9C   CH I$/I1FG >)>E F F 4< F F F 4<s,% E(0AE AA$> %+//#.4"5;;=3%>r1ct|}|dk(ry|dk(ryd|vsd|vrd|vryd|vsd|vrd|vry d |vry d |vsd |vry d|vsd|vryy)uClassify a Mix Log column by its (normalized) header. Everything that isn't an identified date/time/moisture/recipe/meta column is treated as a material candidate — so an unrecognized quantity column surfaces to the admin instead of vanishing.rtime sandmoisturesandmoisturerQ rockmoisturerockrPrecipeyardydsrAmixtimemetamaterial)r)rrs r/_pipe_column_roler s 6AF{F{v{zQv{zQ1}{eqjQ)q. r1cpt|tr|jdSt|tr|jdSt |xsdj }|syt j|r|SdD])} tj||jdcSy#t$rY8wxYw)z 'YYYY-MM-DD' (None when unparseable).z%Y-%m-%dr{N)z%m/%d/%Yz%m/%d/%yz%Y/%m/%dz%m.%d.%Yz%m.%d.%yz%m-%d-%Yz%m-%d-%y) rmrr4 _date_clsr)rrXrYstrptimerl)r`rfmts r/_coerce_import_dater s%"~~j))%#~~j)) EKR A ~~a( $$Q,55jA A    s$B)) B54B5cN|dvry t|S#ttf$rYywxYw)NrR)rrkrlrs r/_coerce_import_numr s3 U| z "s $$c ddl} |j|dd} |jr|jdnd}|dd dggd|j Sgig}}}|j d } t |} t| D]^\} } | t| jnd} | sd | dzz} | |vr|| xxdz cc<d| || fz} nd|| <|j| `|D]a} | t| } td| Dr$i}t|D]\}}|t| kr| |nd||<|j|cdd|j||d|j S#t$r}dd|zdggdcYd}~Sd}~wwxYw#t$r}dd |zdggdcYd}~Sd}~wwxYw#t$rd } YDwxYw#|j wxYw)aParse a PLC Mix Log .xlsx into a normalized row grid. `source` is a path or a file-like object (an uploaded file's stream). Reusable for any single- sheet header+rows export; Pipe-specific meaning is applied by the caller. Returns {ok, error, sheet, columns:[stripped headers in order], rows:[{header: cell_value}]}. Rows that are entirely blank are dropped.rNFz2openpyxl is required to read the spreadsheet (%s).r{)okerrorsheetcolumnsrT) data_only read_onlyz"Could not open the spreadsheet: %szThe workbook has no worksheets.) values_onlyr5zcol%drOz%s_%dc3nK|]-}|duxs#t|txr|j /ywrf)rmr)r)rrxs r/rz parse_mix_log..O s8$9FAs!3!EAGGI F$s35)openpyxl Exception load_workbook worksheetsclose iter_rowsr StopIterationrTr)rrrrnallr"title)rWrexcwbwsheadersrwrit header_rowidxrtr8rrrYrs r/ parse_mix_logr" s1 8  # #Fdd # K$!#R]]1 D :*KB>#BD#'*t|T a $T !33T NN4  ! C{#YF$"$$C&w/ E 6+,s6{?fQiF  E KK  TBHH"D2  [ '$&)'*462  8&JS&PB8 88 J 6  spE+F &F?F?, F-7C#F?+ F 4 F>F F  F* F%F*%F*- F<8F?;F<<F??Gct5}t|t}dddsiS tj|}i}t|trh|jD]U\}}|tk(rt|t|<"|tk(rt|t|<> t||t|<W|S#1swYxYw#t t f$ricYSwxYw#t t f$rYwxYw)z_Admin column->material overrides for the Pipe import: {header: material_id | '__ignore__'}.N)r0rA_PIPE_MAP_META_KEYrDrjrkrlrmrr _PIPE_IGNOREr) _PIPE_YARDSrp)r.rtrrvrrxs r/get_pipe_material_mapr\ s 2t012  zz# C$JJL DAqL *CF k!)CF "%a&CAK  J)22 z " ":.s.B:CC:CCCC/.C/c i}t|trr|jD]_\}}t|xsdj }|s&|dvr+|t k(r t ||<>|t k(r t ||<Q t|||<at5t5}t|ttj||j!dddddd|S#ttf$rYwxYw#1swY(xYw#1swY|SxYw)zPersist the admin column->material overrides. Values are a material id, the string '__ignore__', or falsy (which clears that column back to the default/auto behavior). Returns the normalized stored map.r{)Nr{autorN)rmrrr)rrrrprkrlr?r0rCrrDrErH)mappingr[rrxrr.s r/save_pipe_material_maprv s E'4 MMO DAqag2,$$&C11L )c K(c   Vc   hjD$*DJJu,=>  L z*   Ls65C C>5C2 C>C/.C/2C; 7C>>DcF|jdjDcgc] }t|}}|Dcic]}|d| }}|Dcic]}|dj|}}i}|D]} | |vrA|| } | tk(rddi|| <|j | } | rd| d| dd|| <nddi|| <Ht j t| } | r|j | jnd } | rd | d| dd|| <ddi|| <|Scc}wcc}wcc}w) zResolve each material-candidate column to a material (or ignored / unmapped). Returns {header: {status, material_id?, material_name?}} where status in mapped_admin | mapped_default | ignored | unmapped.rrr8statusignored mapped_admin)rr~r`unmappedNmapped_default)r*r@rrrr__PIPE_DEFAULT_MATERIAL_BY_NORMr) r. material_cols admin_maprIrrrby_namervcolrzrr8s r/_resolve_pipe_columnsr s['+ll!'##+8: /M!  /D /!% &AQtWaZ &E &-12qy !#2G2 C. ) C.Cl"$i0C))C.C&4SY-0[:C%j1C -11*S/B+/gkk$**,'T "23t9),V6CH!*-CH'.( J3 / &2sD D DcB|D]}|j||k(s|cSyrfr_)rroleswantrs r/ _first_colr s+  99Q<4 H r1c 1t|}|dsd|ddS|d}|d}|Dcic]}|t|}}t1t1fd|Dd}| t ||d }|Dcgc]*}||d k(s ||k7r1j |t k7r|,} }t ||d } t ||d } t ||d } t ||d} t ||d}|Dcgc] }||dk(s |}}| dddS| sdddSt}idddddt|ddddddd| dgdgdgddd |d!dd"d#d$|d%dd&gd't|i}g}i}t5t5}t|| 1}i}|jd(tfjD]%}t!|d)}|s|j#||'i}|D]F}| D]?}||vst%|j |#t%|j |||<AH| D]v}||}|d*d+vr#|dj'||d,|d-|d*d.2|d*dk(r|dj'd/|iQ|dj'||j |d0xt)|D]\}}t+|j | }|s|dxxd1z cc<1||vr|j'|| r,t-|j | xsdj/nd}|xsd2|d1zz} d#}!|1t%|j |}"|"|"dkr|d!xxd1z cc<n|"}!|d"xx|!z cc<d}#||j |nd}$t!|$}%|%rW|j |%}&|&r|&d3}#|d%xxd1z cc<n1t-|$j/}'|j |'dd1z||'<|t|xsd4|d1zz|!| rt%|j | nd| rt%|j | ndddd5|#d6 }(g})|6|j |d7vr#|)j'd8|j |z|D].}|j |}*|*d7vs|)j'|d9|*0d:j1|)dd;|(d<<g}+| D]C}||}|d*d+vrt%|j |},|,-|+j'|d,|,d=E|r\|jd>|t| fj3}-|-r|dxxd1z cc<n |dxxd1z cc<|dxxt|+z cc<t5||(|+| ||\}.}/|/rd|/dccdddcdddS||.xxd1z cc<|dxxt|+z cc<|s|j7dddddd|r|dnd|d<||d?<t9|d"d@|d"<t;|j=D0cgc] \}}0||0dA c}0}|d&<|Scc}wcc}wcc}w#1swYwxYw#1swY{xYwcc}0}w)BakImport a daily Pipe Mix Log .xlsx: one row -> one `pipe` batch with source='import'. Idempotent per (batch_date, import_key=batch time), so a same-day re-upload UPDATES matching batches (re-snapshotting from the file) instead of duplicating them. Material columns are mapped onto the administrable Materials list; unmapped columns are surfaced in the report, never silently dropped. dry_run analyzes + reports without writing. Returns a report dict: {ok, error?, batch_date, total_rows, imported, updated, skipped_no_date, material_columns, mapped[], unmapped[], ignored[], line_count}.rFr)rrrrc3TK|]}j|tk(s|!ywrf)r_r)rrr s r/rz&import_pipe_mix_log.. s!LAIMM!,< ,KaLs((NrArrrrQrPrruANo Date column found in the Mix Log — expected a 'Date' column.z2No material quantity columns found in the Mix Log.TrNr{ total_rowsimportedrupdatedskipped_no_datematerial_columnsmappedrr line_count yards_column yards_blank yards_totalrmix_code_columnmixtures_matchedunmatched_mix_codesdry_runzDSELECT id, name, mix_code FROM mixtures WHERE segment=? AND active=1r<r)rr r~r`)columnr~r`rWr%)r%samplerOzrow%drz#%dr ) rNrrOrArPrQrSrUrWrrRz Recipe %sz: z; rVrU)r~rEzjSELECT id FROM batches WHERE batch_date=? AND segment=? AND source='import' AND import_key=? AND deleted=0datesr)coder)rrrrrr_rr6r"rr?r0rr* PIPE_SEGMENTr@r setdefaultrrrrTrr)rrrS_upsert_import_batchrHrrr)2rWr#r$parsedrrrr yards_colr date_coltime_colsand_colrock_col recipe_col meta_colsrXreport dates_seenunmatched_codesr. resolutionpipe_mix_by_coderer(r&rIrresrbdatetime_valr9rAyvalr recipe_raw code_normrrr[ note_bitsrxrbrzr3actionr$rr s2 @r/import_pipe_mix_logrA s6 "F $<fWo66YG &>D.5 6Q!!$ $ 6E 6%'ILLdSIww7 'M158z+AiIMM!,< ,KMMM'5&1H'5&1H'5/:H'5/:HGUH5J#:quQx6'9:I:',- - '   &C  d  " &2CI A  ! %6  M  " !" '0 6B1    $1!  6CC  : (:1  r  4= FJO thjtD*4 J LL/1=AAI 8D"$z"23D ++D$7  8 =A" =F?'9!%%('C'O 21558 @ @ oS /FC'h8E()Q.)J&!!%(=Es155?0b17792H!:gq&9JE$)!%% *:;<4!8=)Q.) E = !U * !J.8.Dz*$J&z2I$((3!"4J-.!3. O113E-<-@-@-JQ-NOE*$ ( ?UcAg->"4AEE(O!D!%"4AEE(O!D!%x( EI%!%% *;:*M  quuZ/@!@A 8EE!HJ&$$A%67 8"YYy1%48E'NE$ Q ox=(JJ(s4; S-?3OP Q<<IL*578@xz9%*%:&!+&|$E 2$.eUJsInsert or (idempotently) update one keyed batch on (batch_date, segment, source, import_key) — the source comes from ``clean['source']`` so both the Pipe import ('import') and Daily Production ('daily_production', DVI-1410 P3) share this upsert. Returns ('imported'|'updated', None) or (None, error).NzcSELECT id FROM batches WHERE batch_date=? AND segment=? AND source=? AND import_key=? AND deleted=0rNrrWrzUPDATE batches SET batch_number=?, mixture_id=?, mixture_version=?, mixture_name=?, yards=?, rock_moisture=?, sand_moisture=?, initials=?, notes=?, updated_by=?, updated_at=? WHERE id=?rOrrArPrQrSrUrwrakINSERT INTO batches(batch_date, segment, batch_number, mixture_id, mixture_version, mixture_name, yards, rock_moisture, sand_moisture, initials, notes, source, import_key, created_by, created_at, updated_by, updated_at, deleted) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0)rzINSERT INTO batch_lines(batch_id, material_id, material_name, unit, target, min_ok, max_ok, actual, out_of_range) VALUES(?,?,?,?,?,?,?,?,?)r~r`rrr?r@rEra)rir*rSrW)r.r[rbr9r#rXrprcrdr$r3rrr@r\rJs r/r+r+o s -9ue,L)Ixc Sy|| 6 | eI.h  hj  D>  .> "E,$7h 7^U?3U?5K : gsH F  G ?(Mll > < % "2E.4I < +xw ? #U?%;U:=N 7^U8_j%e    =="  ,r-("_*=r&z \2h<Hr(|   !  "" 4<r1daily_production_configcd|d|S)Nzdaily::r5)rrNs r/ _daily_keyrF s #Z 00r1ct5}t|t} |rtj|ni}|jdjDcic] }|d|d }}t|Dchc]}|d }}dddi}ttr|jD]u\}} |vst| ts| jd} | dvr t| nd} | j| |k7rd} t| jd| d||<w|S#t t f$ri}YwxYwcc}wcc}w#1swYxYw#t t f$rd} Y}wxYw) u{segment: {enabled: bool, mixture_id: int|None}} — which segments capture Daily Production and each one's assigned Mixture. Self-heals: drops unknown segments and mixture ids that no longer exist / don't belong to the segment. SELECT id, segment FROM mixturesrrNrrRenabledrIr)r0rA_DAILY_PROD_META_KEYrDrjrkrlr*r@rrmrrr_rpr) r.rtrrI valid_mixr valid_segsrvrrrs r/get_daily_production_configrN s~ =t23 &)4::c?rD\\"DENNPRqQtWa l*R R'5d';hj  JJ3g&),&78<V "F G G G JE G Js!C C C B C CCc |xsgDcic]}|d| c}t|\  fd}g}|vt|}jDcgc]}|d|vr|ds|dvr|} }| jd| D]!}||d} | s|j | #|S|r(|D]!}||d} | s|j | #|SjDcgc]}|dr|dr|d vr|} }| jd| D]!}||d} | s|j | #|Scc}wcc}wcc}w) uDVI-1439 P1 (D1-A) / DVI-1440 P2 row source for the Daily Production form. ``included`` is the mixture's per-material membership (DVI-1440): an explicit list of leaf material ids, or None when unset (legacy). Precedence: * membership set -> exactly those leaf materials (in sort order), each with its per-yd3 target/-/+ when it has one, else target-less; * no membership + targets -> one row per target (DVI-1439 behavior); * no membership + no targets -> every ACTIVE leaf material target-less so the day can still be captured (DVI-1439 capture-without-targets). ``direct_aggs`` (DVI-1440 review #2) is the mixture's list of aggregate ids captured DIRECTLY: each becomes a capture line (in place of its constituent materials, which are suppressed). An aggregate not in ``direct_aggs`` stays report-only (summed from constituents) and never a capture line — matching ``_build_lines``. Target/range cells show '—' for a target-less row and nothing flags out of range.r~cj|}|sy|dr|vry|vryj|}||d|d|r|dnd|r|dnd|r|ddSddS)Nrr8rarrr)r~r8rrrrr)rrrrrrtmaps r/_mkz#_daily_ingredient_rows.._mk s HHSM ^ &  J  HHSM"AfIq?P)*!H+'(7d%&& 2 2-12 2r1rrc:|d|dxsdjfSNrr8r{rrs r/rz(_daily_ingredient_rows../ s 1V9qyB.E.E.G"Hr1rrc:|d|dxsdjfSrZr[r\s r/rz(_daily_ingredient_rows..= s qy1V9?*A*A*CDr1)rrorrrr)r direct_aggsrrrrXrinclrwantedrIleavesrrrWs ` @@@r/_daily_ingredient_rowsrb s$+2-R 9AAm a  9D&{D9FJ 2 D8} "[[]KtW_!H+4DKK H I AAdG A A   AAm$%A A  OA8a&71T7f;LOFO KKDKE  $L KKN K] :2KOs EE*E c Rtj|xsi}||t|jdddgdd}|jd}d}t5}|jdr|r|j d|fj }|rt |||d}|r t||ng} |j djD cic]} | d t| } } tt|d d } tt|d d } |d |d |dd|d<t| |d<t| | | | |d<|j d||t||fj }|r|d }ddd|rt||d<|Scc} w#1swY xYw)uForm context for one (segment, date): the segment's Daily Production config, the ingredient rows to capture, and any already-captured entry for that day (so re-opening edits it in place). ``ingredients`` drives the form — one input per ingredient + Total Yards. ``mixture`` is None only when no mixture is assigned to the segment (the SPA then shows the Settings admin-pointer). ``has_targets`` is False when a mixture IS assigned but has no per-yd3 targets yet — the form still captures (DVI-1439 P1 item 2), and the SPA shows a Mixtures-tab NOTICE, not a blocker. Empty ``ingredients`` means there are no active leaf materials.rINF)rrNrIr has_targets ingredientsentryrr rrrr=r{r>r8)rr8rrrdreztSELECT id FROM batches WHERE batch_date=? AND segment=? AND source='daily_production' AND import_key=? AND deleted=0rf)rNr_rr0r*rSrrr@rr|rrrbrFro)rrNrRrvrentry_idr.rerrrIrrr^rs r/daily_production_viewrhE s & ' + +G 4 :CZ3779-.5 C '', CH !t 779 #<< C!$((0  %dC6G1HI=@.tS9b!%.G!H!Q!Q!SU$q!11UU*T#7<>,T#6;= (,T DL-12C-D"FI%)']M"%;k7D&:M"ll K *Wj"A BDEMHJ  4yH/!0 *G J'U!!s A8FF+BFFF&c t|sytjt|xsdsyt j |xsi}|j dr|j dsyt |xsi}||d<||d<d |d <|d|d<t|\}}|rd |fSt}t||}t5t5} t| ||j d |||\} }|rd |fcd d d cd d d S| jd d d d d d t||d fS#1swYxYw#1swY#xYw) aiCapture (or correct in place) the day's Daily Production entry for a segment. One entry per (segment, date), stored as a source='daily_production' batch keyed import_key='daily::' with batch_lines snapshotting the assigned mixture's recipe scaled by the entered yards. Out-of-range is flagged, never blocks. Returns (view, error).rr{)Nzdate must be YYYY-MM-DD.rIr)Nz1Daily Production is not enabled for this segment.rNrrrWNrb)rrXrYr)rNr_rr\r6rFr?r0r+rHrh) rrNrr#rRr[r$rXrr.r@s r/upsert_daily_productionrjt sR g ' >>#j.B/ 0/ % ' + +G 4 :C 779 SWW\%:H &,B F%F<F9)F8|,F<'/JE3 Sy &C Wj )C hjD*4 78K+.s<  9   !* 5t ;; s0 E(D:9 E D:E:E ?EErf)FT)Fr)NFTrRr^)r{)T)NN)NF)r{F)__doc__rDrrr' threadingrZrrrpathlibrenvironr___file__resolveparentrLockr?rrrZrrrrr^compilerXrUrVr0r6rMrGrdrgryr|rrrrrrrrrrrrrrrrr r rr8r=rCr r\rirsrxrzrrorrrrrrrrArCrBrrrFrrrrrrrrrrrrrrrrr r rr$BATCH_TICKET_TUNABLE_FIELDSrJr!r"r%r*r8r3r4r2r6r9r;rQrIrOrRrTrWr^rcrhrjrlrorwrmrnrrrOCR_TEMPLATE_HEADER_FIELDSrrrrrrrrrrrrrrrrrrrr)rrrr rrrrrrrrrrAr+rKrFrNrPrTrbrhrjr5r1r/rvsM2h  & JJNN<h!7!7!9!@!@7!JKM inn ((,WF # 4    2::, - ">;WtT6=.27&06 $ 3D;|h+V@D&C I2="88\)~P6&-R"J?,D$%N'%T "(8|~ N,3"2, M6 X0D @JOG 6H(H**' @,D. / +.X!6! K/J KK  -(* L'@*G/d' '2,B*%""2 3!816 "'&P5O&P!PBJJ~. "rzz":;4"< * % V  ) 4 "+  : )   "?2(7t48@sl1z11<<&@F,^