"""Build 04_Gaps/Binzagr_Data_Request_Templates.xlsx - fill-in templates for the data research cannot provide. Conventions (explained on the 'Read me' sheet): yellow cells = please fill in grey italic row 3 = example (ignored on import) light grey = pre-filled from your data (do not edit) dark header row 2 = column names (do not rename) """ import json import os import pandas as pd from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.worksheet.datavalidation import DataValidation from openpyxl.utils import get_column_letter _HERE = os.path.dirname(os.path.abspath(__file__)) ROOT = os.environ.get('BZ_ROOT', os.path.normpath(os.path.join(_HERE, '..', '..'))) # Binzagr_Data/ (script lives in 04_Gaps/scripts) OUT = os.path.join(ROOT, '04_Gaps') C = os.path.join(ROOT, '02_Cleaned', 'data') + os.sep S = os.path.join(ROOT, '03_Simulated', 'data') + os.sep CPU8 = json.load(open(os.path.join(ROOT, '03_Simulated', 'audit', 'stats_pnl.json')))['cost_per_unit_8m'] F = 'Arial' HDR = PatternFill('solid', start_color='1F3864') YEL = PatternFill('solid', start_color='FFFF00') YEL_L = PatternFill('solid', start_color='FFF9C4') GRY = PatternFill('solid', start_color='EDEDED') EXM = PatternFill('solid', start_color='D9D9D9') thin = Side(style='thin', color='BFBFBF') BOX = Border(left=thin, right=thin, top=thin, bottom=thin) INPUT_ROWS = 300 LISTS = { 'line_item': ['Handling revenue', 'Storage revenue', 'VAS revenue', 'Other revenue', 'Direct labour - own staff', 'Direct labour - agency', 'Overtime', 'Equipment / MHE', 'Rent / facility', 'Utilities', 'Supervision & admin', 'IT / systems', 'Consumables', 'Other opex'], 'role': ['Tally clerk', 'Storekeeper', 'Warehouse manager', 'Inventory control', 'Picker', 'Forklift / reach-truck operator', 'Loader', 'Checker / auditor', 'Receiver', 'Supervisor', 'Clerk / admin', 'Driver', 'Other'], 'login_kind': ['Person (own login)', 'Shared team login', 'System / interface', 'Device / terminal login', 'Other'], 'employment_type': ['Own employee', 'Agency / outsourced', 'Temporary', 'Other'], 'nationality_group': ['Saudi', 'Non-Saudi'], 'temperature': ['Ambient', 'Chilled', 'Frozen', 'Mixed'], 'site_status': ['Active', 'Closed', 'Moved', 'Merged', 'Other'], 'client_type': ['Binzagr business unit', 'External 3PL client', 'Other'], 'billing_basis': ['Internal recharge', 'External invoice', 'Not billed', 'Other'], 'yn': ['Y', 'N'], 'channel': ['Baqala / small grocery', 'Tamweenat', 'Mini / supermarket', 'Key account / hypermarket', 'HoReCa', 'Pharmacy', 'Perfumery / cosmetics', 'Wholesale / trading', 'Fuel station', 'E-commerce', 'Other'], 'service': ['153 Receipt of Shipment', '183 Receipt Damaged', '391 Unload / Unpick', '400 Case Pick', '410 Pallet Pick', '350 Consolidation', '204 Re-palletisation', '206 Pallet split', '172 Returns put-away', '110 Received HUs', '120 Received cases (floor-loaded)', '130 Picked UOS', '210 Repacked HUs', '220 Repacked cases', '610 Customer returns HUs', '710 Shipped HUs', 'Storage (per pallet per day)', 'Storage (per pallet per month)', 'Labelling / VAS', 'Other'], 'status': ['Will send', 'Already sent', 'Not available', 'Need clarification'], } def font(**kw): return Font(name=F, size=kw.pop('size', 10), **kw) def title(ws, text, sub, ncol): ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max(ncol, 2)) c = ws.cell(row=1, column=1, value=f'{text} — {sub}') c.font = font(bold=True, size=11, color='1F3864'); c.alignment = Alignment(wrap_text=True, vertical='center') ws.row_dimensions[1].height = 42 def header(ws, cols, widths, text_cols=()): for j, (c, w) in enumerate(zip(cols, widths), start=1): cell = ws.cell(row=2, column=j, value=c) cell.font = font(bold=True, color='FFFFFF'); cell.fill = HDR; cell.border = BOX cell.alignment = Alignment(wrap_text=True, vertical='center') ws.column_dimensions[get_column_letter(j)].width = w ws.row_dimensions[2].height = 30 ws.freeze_panes = 'A3' def add_dv(ws, listname, col, first, last): dv = DataValidation(type='list', formula1='"' + ','.join(LISTS[listname]) + '"', allow_blank=True) dv.error = 'Please choose a value from the list (or leave blank).'; dv.errorTitle = 'Value not in list' ws.add_data_validation(dv) dv.add(f'{get_column_letter(col)}{first}:{get_column_letter(col)}{last}') def blank_sheet(wb, name, sub, cols, widths, example, text_cols=(), dvs=None, number_cols=None): ws = wb.create_sheet(name) title(ws, name, sub, len(cols)) header(ws, cols, widths) for j, v in enumerate(example, start=1): c = ws.cell(row=3, column=j, value=v) c.font = font(italic=True, color='595959'); c.fill = EXM; c.border = BOX for r in range(4, 4 + INPUT_ROWS): for j in range(1, len(cols) + 1): c = ws.cell(row=r, column=j); c.fill = YEL_L; c.border = BOX; c.font = font() if cols[j - 1] in text_cols: c.number_format = '@' # keep codes as text (avoid Excel number corruption) elif number_cols and cols[j - 1] in number_cols: c.number_format = number_cols[cols[j - 1]] for col, ln in (dvs or {}).items(): add_dv(ws, ln, cols.index(col) + 1, 4, 3 + INPUT_ROWS) return ws def prefilled_sheet(wb, name, sub, df, fixed_cols, input_cols, widths, dvs=None, text_cols=()): ws = wb.create_sheet(name) cols = fixed_cols + input_cols title(ws, name, sub, len(cols)) header(ws, cols, widths) for i, row in enumerate(df[fixed_cols].itertuples(index=False), start=3): for j, v in enumerate(row, start=1): v = None if (isinstance(v, float) and pd.isna(v)) else (v.item() if hasattr(v, 'item') else v) c = ws.cell(row=i, column=j, value=v); c.fill = GRY; c.font = font(); c.border = BOX if fixed_cols[j - 1] in text_cols: c.number_format = '@' for j in range(len(fixed_cols) + 1, len(cols) + 1): c = ws.cell(row=i, column=j); c.fill = YEL; c.font = font(); c.border = BOX for col, ln in (dvs or {}).items(): add_dv(ws, ln, cols.index(col) + 1, 3, 2 + len(df)) return ws def main(): os.makedirs(OUT, exist_ok=True) wb = Workbook() rm = wb.active; rm.title = 'Read me' # ---------------- 1 P&L blank_sheet(wb, '1 Jeddah P&L', 'Actual monthly P&L of the Jeddah warehouse operation, Feb–Sep 2026 (one row per month x site x line item; client optional)', ['month (YYYY-MM)', 'site / wh_id', 'line_item', 'client_code (optional)', 'amount_sar (excl. VAT)', 'notes'], [16, 14, 28, 18, 20, 44], ['2026-04', '2100-04', 'Direct labour - agency', '', 412500, 'Example only: agency invoice, 96 workers'], text_cols=('month (YYYY-MM)', 'site / wh_id', 'client_code (optional)'), dvs={'line_item': 'line_item'}, number_cols={'amount_sar (excl. VAT)': '#,##0.00'}) # ---------------- 2 Attendance blank_sheet(wb, '2 Attendance', 'Time-attendance export: one row per person per shift, keyed by the payroll number (the same number as in your WMS user list). CSV is fine for large volumes', ['payroll_number (as in the WMS user list)', 'employee_id (WMS badge, optional)', 'date (YYYY-MM-DD)', 'shift_code', 'clock_in (YYYY-MM-DD HH:MM)', 'clock_out (YYYY-MM-DD HH:MM)', 'role', 'employment_type', 'home_wh_id', 'nationality_group', 'notes'], [20, 18, 16, 11, 22, 22, 22, 20, 12, 16, 30], ['40151', '', '2026-04-12', 'A', '2026-04-12 06:58', '2026-04-12 15:31', 'Tally clerk', 'Agency / outsourced', '2100-01', 'Non-Saudi', 'Example only'], text_cols=('payroll_number (as in the WMS user list)', 'employee_id (WMS badge, optional)', 'date (YYYY-MM-DD)', 'shift_code', 'clock_in (YYYY-MM-DD HH:MM)', 'clock_out (YYYY-MM-DD HH:MM)', 'home_wh_id'), dvs={'role': 'role', 'employment_type': 'employment_type', 'nationality_group': 'nationality_group'}) # ---------------- 3 Labour cost blank_sheet(wb, '3 Labour cost', 'Monthly headcount and all-in labour cost by site and role (salary, allowances, GOSI, levy, iqama, insurance, agency fees, overtime)', ['month (YYYY-MM)', 'site / wh_id', 'role', 'employment_type', 'nationality_group', 'headcount (month end)', 'total_cost_sar (all-in)', 'overtime_hours', 'notes'], [16, 14, 26, 20, 16, 16, 20, 14, 36], ['2026-04', '2100-04', 'Tally clerk', 'Agency / outsourced', 'Non-Saudi', 48, 196800, 1240, 'Example only: agency rate ~SAR 4,100/head'], text_cols=('month (YYYY-MM)', 'site / wh_id'), dvs={'role': 'role', 'employment_type': 'employment_type', 'nationality_group': 'nationality_group'}, number_cols={'total_cost_sar (all-in)': '#,##0', 'headcount (month end)': '0', 'overtime_hours': '#,##0'}) # ---------------- 4 Rate card blank_sheet(wb, '4 Rate card', 'Contracted activity rates per client (internal recharges and external 3PL clients). Services = your Revenue Generation codes', ['client_code', 'client_name', 'service', 'unit', 'rate_sar', 'min_charge_sar', 'effective_from (YYYY-MM-DD)', 'effective_to (YYYY-MM-DD)', 'contract_type', 'notes'], [12, 26, 30, 18, 12, 14, 18, 18, 22, 30], ['3069', 'Example client', '400 Case Pick', 'per case', 0.80, '', '2026-01-01', '2026-12-31', 'External 3PL client', 'Example only (Badr: the same case activity is SAR 0.80 for one client, 0.50 for another)'], text_cols=('client_code', 'effective_from (YYYY-MM-DD)', 'effective_to (YYYY-MM-DD)'), dvs={'service': 'service', 'contract_type': 'client_type'}, number_cols={'rate_sar': '#,##0.00', 'min_charge_sar': '#,##0.00'}) # ---------------- 5 Sites (pre-filled) wp = pd.read_parquet(S + 'inf_warehouse_profile.parquet') wp = wp.assign(rows_total=wp.rows_total.astype(int), our_inferred_temperature=wp.temperature_regime_inferred, changes_seen_in_data=wp.structural_changes.replace('', 'none')).rename(columns={'rows_total': 'wms_rows_feb_sep'}) prefilled_sheet(wb, '5 Sites', 'Site register. Grey = what we see in your WMS data; please fill the yellow columns (especially 2100-12 and the 2100-01 -> 2100-07 change)', wp, ['wh_id', 'wms_rows_feb_sep', 'first_month', 'last_month', 'changes_seen_in_data', 'our_inferred_temperature'], ['site_name', 'city / area', 'temperature_regime', 'area_sqm', 'pallet_positions', 'operating_pattern (shifts/days)', 'status', 'change_date (YYYY-MM-DD)', 'what changed and why', 'notes'], [10, 14, 11, 11, 34, 16, 22, 14, 16, 11, 13, 20, 12, 16, 34, 24], dvs={'temperature_regime': 'temperature', 'status': 'site_status'}, text_cols=('wh_id', 'first_month', 'last_month')) # ---------------- 6 Clients (pre-filled) cp = pd.read_parquet(S + 'inf_client_principal_map.parquet') cp = cp.assign(our_inferred_type=cp.client_type_inferred.str.replace('_', ' '), principals_we_see=cp.principals_top3.replace('', 'own item codes (not in product master)')) prefilled_sheet(wb, '6 Clients', 'Warehouse client codes. Grey = our inference from item codes; please give the client name and confirm type / billing', cp, ['client_code', 'sku_events', 'our_inferred_type', 'principals_we_see'], ['client_name', 'client_type (confirm)', 'billing_basis', 'notes'], [11, 12, 22, 60, 30, 22, 18, 30], dvs={'client_type (confirm)': 'client_type', 'billing_basis': 'billing_basis'}, text_cols=('client_code',)) # ---------------- 7 Badges (pre-filled): logins your user list does not explain + flagged badges bd = pd.read_parquet(C + 'jeddah_badges.parquet') rk = pd.read_parquet(S + 'inf_badge_session_risk.parquet') rk = rk[rk.badge_sharing_risk.isin(['high', 'medium'])].assign(badge=rk.badges.str.split('|')).explode('badge') hr = rk.groupby('badge').size() bd['high_or_medium_risk_sessions'] = bd.employee_id_norm.map(hr).fillna(0).astype(int) ss = pd.read_parquet(C + 'jeddah_badge_sessions.parquet', columns=['employee_id_norm', 'n_events', 'same_second_share']) bd['same_second_share'] = bd.employee_id_norm.map((ss.same_second_share * ss.n_events).groupby(ss.employee_id_norm).sum() / ss.groupby('employee_id_norm').n_events.sum()).round(2) why = pd.Series('', index=bd.index) why = why.mask(~bd.in_users_list.astype(bool), 'not in the WMS user list') why = why.mask(bd.employee_id_norm.eq('BHAS210004'), 'storekeeper login posting confirmations in batches (~2,140 scans a day)') why = why.mask(bd.months_scanning_while_inactive.fillna(0) > 0, why.where(why == '', why + '; ') + 'scanned while marked inactive') why = why.mask(bd.payroll_numbers.fillna('').str.contains('|', regex=False), why.where(why == '', why + '; ') + 'linked to two payroll numbers over time') bd['why_listed'] = why bd = bd[bd.why_listed != ''].copy() bd['first_seen'] = bd.first_seen.dt.strftime('%Y-%m-%d'); bd['last_seen'] = bd.last_seen.dt.strftime('%Y-%m-%d') bd['events_per_active_day'] = bd.events_per_active_day.round(0).astype(int) bd['possible_alias_group'] = bd.possible_alias_group.fillna('') bd = bd.sort_values('n_events', ascending=False) prefilled_sheet(wb, '7 Badges', f'{len(bd)} WMS logins your user list does not explain, or that look unusual. Grey = what the scans show; please tell us what each one is (no names needed)', bd, ['employee_id_norm', 'why_listed', 'badge_type', 'main_wh', 'first_seen', 'last_seen', 'n_events', 'n_active_days', 'events_per_active_day', 'same_second_share', 'sessions_gt_12h', 'high_or_medium_risk_sessions', 'possible_alias_group'], ['what it is', 'payroll_number (if a person)', 'role', 'employment_type', 'same_person_as (other badge id)', 'notes'], [16, 34, 12, 10, 12, 12, 11, 11, 12, 11, 11, 13, 13, 22, 18, 18, 18, 18, 30], dvs={'what it is': 'login_kind', 'role': 'role', 'employment_type': 'employment_type'}, text_cols=('employee_id_norm', 'main_wh', 'first_seen', 'last_seen')) n_badges_sheet = len(bd) # ---------------- 8 Receivables blank_sheet(wb, '8 Receivables', 'Invoice-level receivables with payments (open items at a snapshot date + payment history). CSV export is fine', ['dist_cd', 'cust_cd', 'doc_no', 'doc_date (YYYY-MM-DD)', 'due_date (YYYY-MM-DD)', 'invoice_amount_sar', 'paid_amount_sar', 'last_payment_date (YYYY-MM-DD)', 'open_amount_sar', 'snapshot_date (YYYY-MM-DD)', 'notes'], [10, 12, 18, 16, 16, 16, 16, 18, 16, 16, 26], ['1000', '739230', 'CSV10267003023', '2026-03-03', '2026-04-30', 1250.40, 1250.40, '2026-05-06', 0, '2026-10-20', 'Example only'], text_cols=('dist_cd', 'cust_cd', 'doc_no', 'doc_date (YYYY-MM-DD)', 'due_date (YYYY-MM-DD)', 'last_payment_date (YYYY-MM-DD)', 'snapshot_date (YYYY-MM-DD)'), number_cols={'invoice_amount_sar': '#,##0.00', 'paid_amount_sar': '#,##0.00', 'open_amount_sar': '#,##0.00'}) # ---------------- 9 Channel codes (pre-filled) ch = pd.read_parquet(S + 'inf_customer_channel_map.parquet') ch = ch.sort_values(['level', 'customers'], ascending=[True, False]) ch = ch.assign(sales_sar_mar_oct_2026=ch.sales_sar_mar_oct_2026.round(0).astype(int), our_inferred_channel=ch.inferred_channel, example_customer_names=ch.example_names.str.slice(0, 160)) prefilled_sheet(wb, '9 Channel codes', 'Customer hierarchy codes (CUST_HIER1/2/3). Grey = our guess from customer names; please give the official name and channel', ch, ['level', 'code', 'customers', 'sales_sar_mar_oct_2026', 'our_inferred_channel', 'example_customer_names'], ['official_name', 'channel', 'notes'], [11, 11, 11, 16, 24, 70, 28, 24, 28], dvs={'channel': 'channel'}, text_cols=('code',)) # ---------------- 10 Code meanings (pre-filled) codes = pd.DataFrame([ ('Transaction mapping', 'Code 080 Cycle Count', 'Listed twice: once "In House", once "Duplicate". Which one applies?'), ('Transaction mapping', 'Code 100', 'Mapping: "Picked Volume" (Duplicate, Revenue). Log: "Item Import" (1,594 rows). Which is right? Not billed until confirmed'), ('Transaction mapping', 'Codes 110-920 not in the log', "18 codes (Received HU's, Received cases, Picked UOS, Shipped HU's, Customer returns HU's ...) never appear in the WMS log. A separate billing-volume table?"), ('Transaction mapping', 'Type of Movement = Duplicate', 'Means "billed under another code" (e.g. Picking (Pick) is billed as Case Pick / Pallet Pick; the three Per Pallet (Billing) rows echo loading / consolidation / audit)?'), ('Transaction mapping', 'Loading, LP audit, QC = Cost', 'So loading is not billed separately - is it included in the pick or shipped-HU rate?'), ('Users mapping', 'menu_level', 'WMS access menu (Tally clerk / Storekeeper / Warehouse manager). Is it the job title, or do pickers, forklift drivers, loaders share these menus?'), ('Users mapping', 'ANNUALCOUNT (Jan 2026)', 'Temporary menu for the annual stock count? (we use the job menu of the surrounding months)'), ('Users mapping', 'status I with scans', '3 badges scanned in months the list marks Inactive (839 scans) - late deactivation, or someone else using the login?'), ('Users mapping', 'Badge MAA210007', 'Linked to two payroll numbers over time - are badge ids re-used?'), ('Users mapping', 'Logins not in the list', 'WA.AD.nnn, plain numbers (751, 754 ...), HM1008, AMR2100, NB99 ... (6% of scans) - desktop, device or agency logins? (sheet 7)'), ('Users mapping', 'Active people vs people who scan', '~269 people have an active login at the Jeddah log sites each month, but only ~138 payroll people (plus ~65 other logins) scan in a month. Are the others office staff, drivers, on leave, or accounts never closed?'), ('Conversion factors', 'PAL / CS', 'PAL = units of sale per pallet, CS = units of sale per case? (fits the data: the median receipt is exactly one pallet)'), ('Conversion factors', 'Inconsistent factors', '154 items have PAL < CS, 1,155 have no PAL, 163 no CS; item 3008-589018 is listed with PAL 90 and 60. Correct values?'), ('WMS', 'Badge BHAS210004', 'Registered storekeeper at 2100-04; ~2,140 scans a day, mostly posted in batches in the same second. What is this login used for?'), ('WMS', 'CONTROL_NUMBER', 'Inbound / outbound order number or internal reference; 6/7-digit numbers = inbound (Badr, 21 Oct). Prefix NNNN- = order-owning sales org (matches client 48%)?'), ('WMS', 'TRAN_TYPE 970 / 971 / 972 (Underload)', 'What does "Underload" record? (Binzagr groups it under Pallets)'), ('WMS', 'WH_ID 2100 (no suffix), 2100-02, 2100-16', 'Very low volume - what are these?'), ('WMS', 'Badges 838 vs WA.AD.838 (38 pairs)', 'Same person under two id formats?'), ('WMS', 'Site 2100-12', 'No activity after April 2026 - closed or merged?'), ('WMS', 'Sites 2100-01 -> 2100-07', 'Activity moved in August 2026 - relocation?'), ('WMS', 'Shift pattern', 'Scans show 24/7 operation with Friday off - what are the official shifts?'), ('Customer master', 'DIST_CD 1000 / 2000 / 8000', '1000 = main Riyadh distributor (94% of sales lines); 2000 only in the master (4,315 customers, no sales); 8000 = second code (6%)'), ('Salesman master', 'BRANCH_CD 2300 / 2301', '2300 = Riyadh (116 reps); 2301 = 3 reps - what is it?'), ('Customer master', 'CUST_TYPE Q / C', 'Q = credit (all have a payment term), C = cash (none have one)'), ('Customer master', 'CUST_CRDLMT = 9,999,999', 'Placeholder meaning "no limit"? (10,755 of 11,068 credit customers)'), ('Customer master', 'CUST_HIER1 10/20/30/40/50/60', 'Top customer hierarchy level - names unknown'), ('Payment Term.xlsx', 'INVTERM ZC12', 'No description provided; not used by any customer'), ('Product master', 'STATUS A / B', 'Meaning? (8,986 A / 14,659 B)'), ('Product master', 'SHELF_LIFE', 'Empty for every product - is shelf life held elsewhere?'), ('Sales lines', 'PRD_SLSTYPE = FOC', 'Free goods (6% of lines) - promotion, trade spend or sample?'), ('Sales lines', 'UOM UOS vs EA', 'UOS = selling unit (pack/case), EA = each?'), ], columns=['source', 'code / field', 'our current understanding']) prefilled_sheet(wb, '10 Code meanings', 'Codes and fields we interpreted from the data. Please correct or confirm', codes, ['source', 'code / field', 'our current understanding'], ['correct meaning / confirmation', 'notes'], [16, 34, 70, 50, 30]) # ---------------- 11 Re-exports & history (pre-filled) rx = pd.DataFrame([ (1, 'Facility cost per case / per pallet', 'Feb-Sep 2026, monthly', 'Your current costing: cost per case and per pallet by site (as used today)', f"Validates our simulated labour per loose case picked (SAR {CPU8['case_pick_worked_time_labour_per_loose_case_sar']:.2f} on worked time; " f"{CPU8['case_pick_labour_per_loose_case_sar']:.2f} incl. paid days without a scan), per pallet picked " f"({CPU8['pallet_pick_worked_time_labour_per_pallet_sar']:.2f}; {CPU8['pallet_pick_labour_per_pallet_sar']:.2f}) and per pallet received " f"({CPU8['receipt_worked_time_labour_per_pallet_received_sar']:.2f}; {CPU8['receipt_labour_per_pallet_received_sar']:.2f})"), (1, 'Sample client activity rates', 'current', 'A few clients: rate per activity and unit (promised on 21 Oct)', 'Replaces the cost-recovery rate card for those clients'), (2, 'WMS transaction log re-export', 'Jan 2026 onwards', 'Same 19 columns + unique transaction id (TRAN_LOG_ID); HU_ID as TEXT (as in the 22 Oct sample); client on billing rows; include January 2026', 'The sample proved Excel kept only 6 of the 12 pallet-id digits; January is missing from the extract'), (2, 'Billing volumes (codes 110-920)', 'Feb 2026 onwards', "Per client per month: Received HU's, Received cases, Picked UOS, Shipped HU's, Customer returns HU's, storage", 'Actual billed volumes per client - the revenue side of the P&L'), (2, 'HR job titles by payroll number', 'current', 'payroll_number, job title (picker, forklift driver, loader ...), own / agency, nationality group', 'Wage rates by job (WMS menu levels are not job titles)'), (2, 'Time-and-motion study results', 'latest', 'Measured time per activity (case picking etc.) from the study Badr mentioned', 'Engineered standards to anchor our data-derived ones (G11)'), (2, 'Product hierarchy as text', 'current', 'Product Grouping + PRDCAT_KEY exported as text (or CSV)', 'Excel cut 16-18 digit codes; category names are ambiguous for 10,035 (L5) and 18,206 (L6) products'), (2, 'Sales & returns history', '24+ months', 'Same layout as Sales_Return - Riyadh', 'Learn Ramadan/Eid seasonality (only one Ramadan in the extract)'), (3, 'WMS history', '24 months', 'Same layout as the monthly Jeddah files', 'Workload seasonality for staffing forecasts'), (3, 'Storage occupancy', 'Feb 2026 onwards', 'Pallet positions occupied per client per day (or week)', 'Storage revenue in the P&L (not modelled today)'), (3, 'Van loads', 'Mar 2026 onwards', 'Per rep per day: SKU, qty loaded, qty returned to warehouse', 'Van-load optimisation (sales show only what was sold)'), (3, 'Route / journey plans + visit logs', 'Mar 2026 onwards', 'Planned customers per rep per day, visited Y/N, no-sale reason', 'Route optimisation, missed-visit analysis'), (3, 'Outlet closure confirmations', 'any', 'cust_cd, closed Y/N, closure date', 'Labels for the outlet churn model'), (3, 'Other clusters masters', 'current', 'Customer + product masters of the other clusters / cities', 'Cross-cluster harmonisation use case'), (3, 'Shelf life master', 'current', 'prd_cd, shelf life (days), min remaining life accepted by retailers', 'Only if the shelf-life use case comes back into scope'), ], columns=['priority', 'dataset', 'period', 'what exactly', 'why we need it']) prefilled_sheet(wb, '11 Re-exports & history', 'Larger extracts and re-exports (send as CSV/zip; no need to paste here - just tell us the status)', rx, ['priority', 'dataset', 'period', 'what exactly', 'why we need it'], ['status', 'expected_date (YYYY-MM-DD)', 'owner', 'notes'], [9, 30, 16, 60, 60, 16, 18, 16, 30], dvs={'status': 'status'}) n_rx, n_codes = len(rx), len(codes) # ---------------- Read me (written last so formulas reference existing sheets) rm['A1'] = 'Binzagr PoV — data request templates'; rm['A1'].font = font(bold=True, size=14, color='1F3864') rm['A2'] = ('These sheets cover the data we cannot get from public research. Everything else has been cleaned or simulated already. ' 'Each sheet replaces a simulated or inferred table in our model one-for-one, so your answers flow straight into the PoV.') rm['A2'].font = font(); rm['A2'].alignment = Alignment(wrap_text=True); rm.merge_cells('A2:G2'); rm.row_dimensions[2].height = 45 rm['A4'] = 'How to fill in'; rm['A4'].font = font(bold=True, size=11) legend = [(YEL, 'Yellow / light-yellow cell', 'Please fill in'), (EXM, 'Grey italic row 3', 'Example of the expected format - ignored on import'), (GRY, 'Light-grey columns', 'Pre-filled from your data - please do not edit'), (HDR, 'Dark header row', 'Column names - please do not rename')] for i, (fill, a, b) in enumerate(legend, start=5): rm.cell(row=i, column=1, value=a).fill = fill rm.cell(row=i, column=1).font = font(color='FFFFFF' if fill is HDR else '000000', bold=fill is HDR) rm.cell(row=i, column=2, value=b).font = font() rules = ['Dates YYYY-MM-DD; timestamps YYYY-MM-DD HH:MM (24h, Saudi local time).', 'Amounts in SAR excluding VAT.', 'Codes exactly as in your systems; code columns are formatted as text so Excel keeps long codes intact.', 'Large tables (attendance, receivables, re-exports): a CSV/zip with the same column names is better than pasting.', 'People are identified by payroll number (as in your WMS user list) - we never need names.'] rm['A10'] = 'Rules'; rm['A10'].font = font(bold=True, size=11) for i, t in enumerate(rules, start=11): rm.cell(row=i, column=1, value='• ' + t).font = font() rm.merge_cells(start_row=i, start_column=1, end_row=i, end_column=7) r0 = 17 rm.cell(row=r0, column=1, value='Sheets').font = font(bold=True, size=11) hdrs = ['Sheet', 'What we need', 'What it unlocks / replaces', 'Suggested owner', 'Priority', 'Rows filled', 'Status'] for j, h in enumerate(hdrs, start=1): c = rm.cell(row=r0 + 1, column=j, value=h); c.font = font(bold=True, color='FFFFFF'); c.fill = HDR; c.border = BOX idx = [ (1, '1 Jeddah P&L', 'Actual monthly P&L, Feb–Sep 2026', 'Replaces the simulated baseline P&L - the comparison Salman asked for', 'Salman / Finance', 1, "=COUNTA('1 Jeddah P&L'!A4:A303)"), (2, '2 Attendance', 'Clock-in / roster per person per shift, keyed by payroll number', 'Measures the ~59% of paid time the scans cannot see; confirms head count and badge sharing', 'Badr / HR', 1, "=COUNTA('2 Attendance'!A4:A303)"), (3, '3 Labour cost', 'Headcount and all-in cost by site, role, month', 'Replaces the simulated loaded labour cost (SAR ~4,100 per expat per month; role tiers)', 'Finance / HR', 1, "=COUNTA('3 Labour cost'!A4:A303)"), (4, '4 Rate card', 'Contracted rate per client and activity (your Revenue Generation codes)', 'Replaces the simulated cost-recovery rate card and revenue', 'Finance / Commercial', 1, "=COUNTA('4 Rate card'!A4:A303)"), (5, '5 Sites', 'Site names, type, size, status; the 2100-12 and 2100-01 -> 2100-07 changes', 'Fair site-to-site benchmarking', 'Badr / Operations', 1, "=COUNTA('5 Sites'!G3:G11)"), (6, '6 Clients', 'Client names, type, billing basis (31 codes pre-filled)', 'Confirms the inferred client -> principal map', 'Badr / Finance', 2, "=COUNTA('6 Clients'!E3:E33)"), (7, '7 Badges', f'What the {n_badges_sheet} logins not explained by your user list are', 'Separates people, shared, device and system logins', 'Badr / HR', 2, f"=COUNTA('7 Badges'!N3:N{2 + n_badges_sheet})"), (8, '8 Receivables', 'Invoice-level open items and payments', 'Replaces the simulated FIFO ageing', 'Neeraj / Finance', 2, "=COUNTA('8 Receivables'!A4:A303)"), (9, '9 Channel codes', 'Official names of customer hierarchy codes (65 pre-filled)', 'Confirms the inferred channel labels', 'Neeraj', 2, "=COUNTA('9 Channel codes'!G3:G67)"), (10, '10 Code meanings', f'Confirm / correct {n_codes} interpretations (incl. 13 from your 22 Oct mapping files)', 'Removes guesswork from the cleaning and model', 'Neeraj / Badr', 2, f"=COUNTA('10 Code meanings'!D3:D{2 + n_codes})"), (11, '11 Re-exports & history', f'Status of {n_rx} extracts (incl. cost per case / pallet and sample client rates)', 'Validation, billed volumes, seasonality, storage revenue', 'Badr / Neeraj', 2, f"=COUNTA('11 Re-exports & history'!F3:F{2 + n_rx})"), ] for i, row in enumerate(idx, start=r0 + 2): for j, v in enumerate(row[1:], start=1): c = rm.cell(row=i, column=j, value=v); c.font = font(); c.border = BOX; c.alignment = Alignment(wrap_text=True, vertical='top') c = rm.cell(row=i, column=7); c.fill = YEL; c.border = BOX add_dv(rm, 'status', 7, r0 + 2, r0 + 1 + len(idx)) rm.cell(row=r0 + 3 + len(idx), column=1, value='Rows filled counts entries below the example row (or filled yellow cells on pre-filled sheets). ' 'Priority 1 = needed for the client-facing P&L comparison; 2 = replaces an inference; 3 = future scope.').font = font(italic=True, color='595959') for col, w in zip('ABCDEFG', [26, 46, 56, 20, 9, 11, 18]): rm.column_dimensions[col].width = w for ws in wb.worksheets: # print nicely: landscape, fit all columns on one page width ws.page_setup.orientation = 'landscape' ws.sheet_properties.pageSetUpPr.fitToPage = True ws.page_setup.fitToWidth = 1 ws.page_setup.fitToHeight = 0 ws.print_title_rows = '2:2' if ws.title != 'Read me' else None path = os.path.join(OUT, 'Binzagr_Data_Request_Templates.xlsx') wb.save(path) print('saved', path) if __name__ == '__main__': main()