Batik PO — Calculation Logic (Web App Spec)

Purpose: Rebuild the batik PO generator in the RBD Ordering web app, dynamically — precuts and kits as data rows, not the sheet's fixed positional matrix. The current Google Sheet (Batik Collections Template → tab Template (NEW)) is a rigid 24-column rotation matrix that can't grow/shrink precut rows; the web app should replace that entirely. Last updated: 2026-08-11. Companion to cotton-po-web-app-logic.md.

0. Why this is a rebuild, not a port

The sheet hard-wires precuts into a fixed 24-column breakout (6 precut types × 4 slots), each column bound to a precut row (C110=A43), with divisors and stacking-grid column letters baked into formulas. You cannot add/remove precut rows without breaking it. In code, model precuts/kits as lists, size them per collection, and none of the positional machinery is needed.

1. SKU classification — CLASS-based (cleaner than cotton)

Use Data col D "Class", not SKU prefix: | Class value | Tier | |---|---| | Batik - Collection | Main yardage | | Precut Batik | Precut (covers FQ-, RP-, 5-, 10-, ASY-, 25-) | | Casepack | SKU prefix CP* | | Kit | SKU prefix KTBT- (see §6 — data gap) |

Batik SKU prefix is BT… (rayon variant BTR…). 8-yard D/R bolts (cotton is 15/10). No dropships. Mills: Chakra, Haryan, Pria Tampan, Batik Agung Pamungkas.

2. Main yardage

• D&R Yds per fabric = Data col AH "D/R or Units" (the authoritative quantity — everything derives from AH, never from Suggested QTY). See the AH-quantity-truth note.
• Total Yds = D&R + Casepack + Precut yardage (§4 rollup).
• 8-yard bolts; # of bolts = Total ÷ 8.

3. Precut model — the core dynamic piece

A precut (FQ, RP, 5", 10", ASY, 25") is built from the collection's fabrics per a stacking order: a list saying, for the precut, how many pieces of each fabric go in it.

Two quantities drive it:

• piecesPerBundle(fabric) = how many pieces of that fabric go into one bundle's stacking recipe. (Sheet: COUNTIF(stacking_column, fabric). Code: stacking[precut].filter(f => f === fabric).length.)

• totalUnits(precut) = number of bundles ordered = the precut's "TOTAL UNITS" = Data col AH "D/R or Units".

Confirmed conversion (validated on live ARBORGRACE data 2026-08-11):

Yardage(fabric) = piecesPerBundle(fabric) × totalUnits(precut) ÷ divisor(precutType)

i.e. total pieces of that fabric across the whole order ÷ pieces-per-yard. The sheet writes this as COUNTIF(stacking, fabric) × (precut-section TOTAL UNITS / divisor).

Worked proofs (COLE TEST 3, ARBORGRACE):

• FQ-A (FQ-BT23888A-15, TOTAL UNITS=126): each of 15 fabrics appears 1× in the recipe → 1 × 126 / 4 = 31.50 yd per fabric. TOTAL YDS = 15 × 31.50 = 472.50. ✓

• RP (RP-BT23888-40, TOTAL UNITS=108): each of 20 fabrics appears 2× (a "40 Pcs" roll = 20 fabrics × 2 strips) → 2 × 108 / 14 = 15.43 yd per fabric. TOTAL YDS = 20 × 15.43 = 308.57. ✓

• Total precut yardage per fabric = Σ over all precut types of Yardage(fabric). (Sheet: col AA rollup → feeds main-row "Precut Yds".)

• Total pieces per bundle ("pcs" summary, sheet row 111) = Σ over fabrics of piecesPerBundle = SUMPRODUCT(COUNTIF(stacking, fabrics)). This is the correct count — it gives 40 for RP-40 (20 fabrics × 2 strips). The old tab convention COUNTIF(body>0) counted only distinct fabrics (20) and undercounts any bundle with >1 piece per fabric; row 111 was fixed 2026-08-11 to the SUMPRODUCT form. See §9 gotcha.

Blended precut price-per-piece

For a precut, the fabric cost of one piece = total fabric cost ÷ total pieces, carrying each fabric at its own rate (so mixed-rate collections blend correctly): pricePerPiece = round( Σ(pieces_fabric × rate_fabric) / Σ(pieces_fabric) / divisor , 2) Precut line total = pieces × pricePerPiece + labor (labor = precut SKU's own rate from Data).

• Piece-weighted: a fabric with 2–3 pieces at a different rate counts 2–3× in the blend (pieces = its multiplicity in the stacking recipe). Verified correct on live data.
• Round to the cent (matches the buyer's manual reference; e.g. FQ blend 0.575 → 0.58 → rate $10.20).
• In the sheet this was fragile: the old formula paired the breakout range with the main-rate range by ROW POSITION and blanked out (SUMPRODUCT length mismatch) whenever the row adjuster resized the sheet. Fixed 2026-08-12 by looking up each rate by SKU (VLOOKUP), removing the length dependence. In code this is a non-issue — you iterate the precut's fabric list and multiply by each fabric's own rate; there's no positional coupling to worry about.

4. Casepack

Casepacks (CP*) are their own SKUs; qty/rate come from Data (AH units, BC rate). Casepack yardage = casepack count × per-member yardage; the member list comes from the stacking grid (the CP SKU typed into the grid header must exactly match the real NetSuite CP SKU — a past bug source, e.g. missing the R in a BTR rayon SKU).

5. Rate & total

• Rate = flat per-yard by mill (Simple Pricing tab), keyed by vendor × SKU prefix.
• Precut surcharge = per-yd by mill (Data col BB) for class Precut Batik. (Note: the sheet's updateBatiksSurchargeRates isn't auto-run — a known gap; in the web app, always apply it.)
• Line total = yardage × rate (+ precut labor, + surcharge).
• PO total = Σ main + precut + casepack + kit.

6. Kits — ⚠️ data gap

Kit prefix = KTBT-. Kit SKUs are NOT in the Data tab (NetSuite search 3314 doesn't return them), so today kits can't be detected or priced from Data. Before the web app can do kits, resolve the source: include KTBT- in the search, add a separate kit source, or flag off the PO name. Detect a kit product as the KIT-section row whose "Total Kits" qty is populated (components have qty in "Quantity of Pcs").

7. Vendor / PO metadata (for NetSuite import)

• Vendor resolves from Data AY (=Preferred Vendor, often "- None -") → falls back to Collections tab vendor (col E by PO#).
• Import = Cotton's 10-col NetSuite format: PO Name | Vendor | Ship Date | Item | QTY | Rate | Amount | Internal ID | External ID | Location. Main qty = D&R Yds only (casepack/precut yardage NOT rolled into the main line). External ID = "1" + PO#.
• Batik vendor initials/numbers are missing in the current script (Chakra/Haryan/Pria Tampan) — supply real ones for the exID prefix.

8. What the web app removes (all the sheet pain)

Fixed 4-slots-per-precut-type · the 24-column rotation matrix · divisor-per-column formulas · ADDRESS/MATCH stacking-grid-letter resolution · row-drift between tabs · #REF! overflow when a collection has too many fabrics. All of it becomes: size the precut/fabric lists to the collection, loop, compute.

9. Notes for the port

• Precut yardage conversion factor (§3) — confirmed 2026-08-11 on live ARBORGRACE data: Yardage = piecesPerBundle × totalUnits(precut) / divisor. No hidden extra factor.
• Google-Sheets gotcha that bit us: SUM(COUNTIFS(range, array)) does not array-expand (counts only the first element); use SUMPRODUCT(COUNTIF(range, array)). Irrelevant in code, but explains the sheet formulas.
• Kits (§6) and batik vendor initials (§7) are unresolved data items — surface them, don't silently zero them.
• Reference memories: reference_batik_po_template, reference_batik_ah_quantity_truth.