""" ESE_SampleData_Generator.py Generates ESE_SampleData.xlsx — a reference workbook covering all 12 scheduling scenarios. Run: python3 Docs/ESE_SampleData_Generator.py Requires: pip install openpyxl """ from openpyxl import Workbook from openpyxl.styles import (PatternFill, Font, Alignment, Border, Side, GradientFill) from openpyxl.utils import get_column_letter from openpyxl.comments import Comment import os # ── Color palette ───────────────────────────────────────────────────────────── HEADER_BG = "1F3864" # Dark navy HEADER_FG = "FFFFFF" SUBHDR_BG = "2F5496" # Section sub-header INFO_BG = "D6E4F0" # Light blue — info sheets C_SCHEDULED = "C6EFCE" # Green — Decision 0 C_SUBCON = "BDD7EE" # Blue — Decision 1 C_MONITOR = "DDEBF7" # Cyan — Decision 2 C_NOCAND = "FFC7CE" # Red — Decision 3 C_NOSLOT = "FFEB9C" # Orange — Decision 4 C_BATCH = "E2EFDA" # Yellow-green — batch rows C_INTERR = "EAD1DC" # Purple — interruptible segments C_MASTER = "F2F2F2" # Light grey — master data rows C_WHITE = "FFFFFF" SCENARIO_COLORS = { "S1": C_SCHEDULED, "S2": C_SCHEDULED, "S3": C_SUBCON, "S4": C_MONITOR, "S5": C_BATCH, "S6": C_SCHEDULED, "S7": C_INTERR, "S8": C_SCHEDULED, "S9": C_NOCAND, "S10": C_NOSLOT, "S11": C_SCHEDULED, "S12": C_SCHEDULED, } def fill(hex_color): return PatternFill("solid", fgColor=hex_color) def header_font(): return Font(bold=True, color=HEADER_FG, name="Calibri", size=10) def bold_font(): return Font(bold=True, name="Calibri", size=10) def normal_font(): return Font(name="Calibri", size=10) def center(): return Alignment(horizontal="center", vertical="center", wrap_text=True) def left(): return Alignment(horizontal="left", vertical="center", wrap_text=True) thin = Side(style="thin", color="BFBFBF") def border(): return Border(left=thin, right=thin, top=thin, bottom=thin) def write_headers(ws, headers, row=1, bg=HEADER_BG): for col, h in enumerate(headers, 1): c = ws.cell(row=row, column=col, value=h) c.fill = fill(bg) c.font = Font(bold=True, color=HEADER_FG if bg == HEADER_BG else "1F3864", name="Calibri", size=10) c.alignment = center() c.border = border() ws.row_dimensions[row].height = 28 def write_row(ws, row_idx, values, bg=C_WHITE, comment_map=None): for col, val in enumerate(values, 1): c = ws.cell(row=row_idx, column=col, value=val) c.fill = fill(bg) c.font = normal_font() c.alignment = left() c.border = border() if comment_map: for col, text in comment_map.items(): c = ws.cell(row=row_idx, column=col) cmt = Comment(text, "ESE Guide") cmt.width = 250 cmt.height = 80 c.comment = cmt ws.row_dimensions[row_idx].height = 18 def autosize(ws, min_width=10, max_width=40): for col_cells in ws.columns: length = max(len(str(c.value or "")) for c in col_cells) ws.column_dimensions[get_column_letter(col_cells[0].column)].width = \ max(min_width, min(length + 2, max_width)) def freeze(ws, cell="A2"): ws.freeze_panes = cell # ── Sheet builders ───────────────────────────────────────────────────────────── def build_readme(wb): ws = wb.create_sheet("00-README") ws.sheet_view.showGridLines = False title_cell = ws["A1"] title_cell.value = "Enterprise Scheduling Engine (ESE) — Sample Data Reference" title_cell.font = Font(bold=True, size=14, color="1F3864", name="Calibri") title_cell.fill = fill(INFO_BG) ws.row_dimensions[1].height = 30 ws.merge_cells("A1:G1") intro = [ "", "PURPOSE", "This workbook provides realistic sample data for all 12 ESE scheduling scenarios.", "Use it to: (a) understand engine input/output, (b) set up a dev database for testing,", "(c) trace why a specific job was or was not scheduled.", "", "SHEET INDEX", ] for i, line in enumerate(intro, 2): ws.cell(row=i, column=1, value=line).font = normal_font() if line in ("PURPOSE", "SHEET INDEX"): ws.cell(row=i, column=1).font = bold_font() sheets = [ ("00-README", "This sheet — overview and legend"), ("01-Scenarios", "12 scenarios: what triggers each, what the engine outputs"), ("10-MWORKCENTER", "Master — Work centers"), ("11-MMACHINE", "Master — Machines (linked to WC)"), ("12-MWORKCENTERSHIFTMAP","Master — Shift windows per WC per day"), ("13-MBATCHRULE", "Master — Batch merge rules"), ("14-MROUTINGVERSION", "Master — Routing version headers"), ("15-MROUTINGVERSIONDETAIL","Master — Routing steps with ESE fields"), ("16-MBILLOFRESOURCE", "Master — Machine-resource rows (TYPE=4); source of duration"), ("17-MPATTERN", "Master — Moulds/patterns"), ("20-TINDENT", "Transaction — Production indent headers"), ("21-TINDENTDETAIL", "Transaction — Indent detail lines (the demand)"), ("22-TNESTINGPLAN", "Transaction — Nesting plans (scenario S11)"), ("30-TSCHEDULINGRUNLOG", "Run Output — Run summary (utilization, RCCP JSON)"), ("31-TSCHEDULINGRUNDETAILLOG","Run Output — Per-EU decision log (KEY diagnostic sheet)"), ("32-TRESOURCEPLAN", "Run Output — Committed plan rows"), ("33-TTASK", "Run Output — Mount tasks for mould changeovers"), ] r = 9 write_headers(ws, ["Sheet Name", "Description"], row=r) for s, d in sheets: r += 1 write_row(ws, r, [s, d], bg=C_MASTER) r += 2 ws.cell(row=r, column=1, value="COLOR LEGEND").font = bold_font() r += 1 legend = [ (C_SCHEDULED, "Decision 0 — Scheduled (job placed on machine, TRESOURCEPLAN row exists)"), (C_SUBCON, "Decision 1 — Subcontract (no machine slot; lead time based)"), (C_MONITOR, "Decision 2 — Monitor-Only (LocationType=5; just tracked, not scheduled)"), (C_NOCAND, "Decision 3 — Failed: No Candidates (no machines configured for that WC)"), (C_NOSLOT, "Decision 4 — Failed: No Slot (machines exist but calendar fully booked)"), (C_BATCH, "Batch rows — multiple indent details merged into one EU (BCH-xxx)"), (C_INTERR, "Interruptible segments — one job split across multiple shift windows"), ] for color, desc in legend: ws.cell(row=r, column=1, value=" ").fill = fill(color) ws.cell(row=r, column=2, value=desc).font = normal_font() r += 1 r += 1 ws.cell(row=r, column=1, value="KEY FIELD CONVENTIONS").font = bold_font() r += 1 conventions = [ ("IS* TINYINT fields (ISINTERRUPTIBLE, ISMONITORONLY...)", "0 = YES (IS that state) | 1 = NO (is NOT that state). Different from typical bool!"), ("DURATIONSOURCE", "0 = From BOR (Setup + CycleTime×Qty) | 1 = Manual override | 2 = Subcontract lead time"), ("DECISION", "0=Scheduled 1=Subcontract 2=MonitorOnly 3=FailedNoCandidates 4=FailedNoSlot"), ("EXECUTIONMODE", "0=InHouse 1=Subcontract 2=Either"), ("TIMEUOM / LEADTIMEUOM", "0=Seconds 1=Minutes 2=Hours 3=Days 4=Weeks"), ("ROOTBATCHNUMBER", "SGL-xxx = singleton job | BCH-xxx = batch (multiple indent details merged)"), ("SLOTWAITMINS", "ScheduledStart − ComputedEarliestStart. High value = job queued behind other work."), ("DEADLINESLACKMINS", "Deadline − ScheduledEnd. NEGATIVE = job will be late. Check FAILUREREASON if so."), ] for field, explanation in conventions: write_row(ws, r, [field, explanation], bg=INFO_BG) r += 1 ws.column_dimensions["A"].width = 42 ws.column_dimensions["B"].width = 70 def build_scenarios(wb): ws = wb.create_sheet("01-Scenarios") ws.sheet_view.showGridLines = False ws.merge_cells("A1:F1") ws["A1"].value = "ESE Scenarios — Input Configuration → Expected Engine Output" ws["A1"].font = Font(bold=True, size=12, color="1F3864", name="Calibri") ws["A1"].fill = fill(INFO_BG) ws.row_dimensions[1].height = 26 headers = ["Scenario", "Name", "Key Input Config", "Duration Calc", "Expected Decision", "Verify In"] write_headers(ws, headers, row=2) scenarios = [ ("S1", "Simple In-House (BOR)", "WC-CAST, EXECUTIONMODE=0, BOR CYCLETIME=3min SETUPTIME=30min, Qty=100", "30 + 3×100 = 330 min (DURATIONSOURCE=0)", "Decision=0 (Scheduled)\nMachine=MCH-01\nStart=06:00 End=11:30", "31-TSCHEDULINGRUNDETAILLOG row 1\n32-TRESOURCEPLAN row 1"), ("S2", "Manual Duration Override", "Same as S1 but TINDENTDETAIL.MANUALDURATIONMINUTES=200", "200 min (DURATIONSOURCE=1, BOR ignored)", "Decision=0 (Scheduled)\nDURATIONSOURCE=1", "31-detail log row 2\n21-TINDENTDETAIL: ManualDurationMins=200"), ("S3", "Subcontract", "MROUTINGVERSIONDETAIL.EXECUTIONMODE=1, SUBCONTRACTLEADTIMEDAYS=5", "5 × 480 = 2400 min (DURATIONSOURCE=2)", "Decision=1 (Subcontract)\nNo machine, no TRESOURCEPLAN row", "31-detail log row 3\n15-MROUTINGVERSIONDETAIL: ExecMode=1"), ("S4", "Monitor-Only Step", "MROUTINGVERSIONDETAIL.LOCATIONTYPE=5 → IsMonitorOnly=0 (yes)", "No slot needed — just logged", "Decision=2 (MonitorOnly)\nMachineId=NULL", "31-detail log row 4\n15-RVD: LocationType=5"), ("S5", "Batch Merge (2 jobs → 1 EU)", "2 indent details, same WC+routing, each qty=75. MBATCHRULE max=150.", "Merged qty=150 → 30+3×150=480 min", "Decision=0 (Scheduled)\nBATCHITEMCOUNT=2\nBATCHEDDETAILIDS=[1201,1202]\nROOTBATCHNUMBER=BCH-001", "31-detail log: BATCHITEMCOUNT=2\n13-MBATCHRULE for merge rule"), ("S6", "Mould Changeover", "Job needs Pattern-2 (Camshaft mould). MCH-01 has Pattern-1 mounted.", "30+3×60=210 min job + 30 min mount task", "Decision=0, PATTERNNEWMOUNT=1\nTTASK row created (TASKNATURE=2)\nJob start = MountEnd (06:30)", "33-TTASK: mount row\n31-detail log: PatternNewMount=1"), ("S7", "Interruptible — Spans 2 Shifts", "ISINTERRUPTIBLE=0 (YES interruptible), Qty=190 → 600 min needed.\nWC-CAST has Shift-A 06-14 + Shift-B 14-22.", "30+3×190=600 min total\nSegment1: 450min (06:00-13:30)\nSegment2: 150min (14:00-16:30)", "Decision=0 (Scheduled)\n2 TRESOURCEPLAN rows, same RESOURCEPLANCOMBINEID\nISINTERRUPTIBLE=0", "32-TRESOURCEPLAN: 2 rows same CombineId\n12-shifts: verify adjacent windows"), ("S8", "Non-Interruptible — Large Merged Window", "ISINTERRUPTIBLE=1 (NOT interruptible), Qty=170 → 540 min.\nSame 2 adjacent shifts → MergeAdjacentWindows gives 900-min window.", "30+3×170=540 min\nSingle contiguous slot found", "Decision=0 (Scheduled)\n1 TRESOURCEPLAN row\nISINTERRUPTIBLE=1", "32-TRESOURCEPLAN: 1 row only\n15-RVD: IsInterruptible=1"), ("S9", "Failed — No Candidates", "Indent routed to WC-MILL (WorkCenterId=4). No machines assigned to WC-MILL.", "N/A — CandidateDAL returns empty", "Decision=3 (FailedNoCandidates)\nCANDIDATESCONSIDERED=0\nFAILUREREASON explains the gap", "31-detail log: Decision=3, read FAILUREREASON\n11-MMACHINE: no machine with WC=4"), ("S10", "Failed — No Slot", "MCH-03 in WC-MACH has Shift 08-16 (450 min net). Another job already fills entire shift.", "Job needs 270 min but 0 free minutes remain", "Decision=4 (FailedNoSlot)\nCANDIDATESJSON shows FullyBooked\nFAILUREREASON lists count+reason", "31-detail log: Decision=4\nCANDIDATESJSON: RejectReason=FullyBooked"), ("S11", "Nesting Plan", "TNESTINGPLAN: SETUPTIME=45, CYCLETIME=90, WORKCENTERID=WC-NEST", "45+90=135 min", "Decision=0 (Scheduled)\nDEMANDTYPE=7 (NestingPlan)\nMachine=MCH-04", "31-detail log: DemandType=7\n22-TNESTINGPLAN source row"), ("S12", "RCCP Overload Warning", "Total required on WC-CAST > available capacity over 5-day horizon", "Required=3800 min Available=2400 min", "Run still completes (RCCP is advisory)\nRCCPJSON shows IsOverloaded=true, OverloadPct=158%", "30-TSCHEDULINGRUNLOG: RCCPJSON field"), ] for i, row_data in enumerate(scenarios, 3): scen = row_data[0] bg = SCENARIO_COLORS.get(scen, C_WHITE) write_row(ws, i, list(row_data), bg=bg) ws.column_dimensions["A"].width = 7 ws.column_dimensions["B"].width = 28 ws.column_dimensions["C"].width = 48 ws.column_dimensions["D"].width = 32 ws.column_dimensions["E"].width = 38 ws.column_dimensions["F"].width = 38 for i in range(3, 15): ws.row_dimensions[i].height = 52 freeze(ws) def build_workcenter(wb): ws = wb.create_sheet("10-MWORKCENTER") headers = ["WorkCenterId", "WorkCenterCode", "WorkCenterName", "ProcessId", "NumberOfUnits", "WorkingMinutes", "Status", "TenantId", "Notes"] write_headers(ws, headers) rows = [ (1, "WC-CAST", "Casting", 10, 1, 450, 1, 1, "2 adjacent shifts (S7/S8). Net 450 min per shift after break."), (2, "WC-MACH", "Machining", 20, 1, 450, 1, 1, "1 shift 08-16. Used for S10 (fully booked)."), (3, "WC-NEST", "Nesting/Cutting", 30, 1, 690, 1, 1, "1 long shift 08-20. Used for S11 (nesting plan)."), (4, "WC-MILL", "Milling", 40, 0, 0, 1, 1, "NO machines assigned — triggers S9 FailedNoCandidates."), ] for i, r in enumerate(rows, 2): write_row(ws, i, list(r), bg=C_MASTER, comment_map={9: r[-1]}) autosize(ws) freeze(ws) def build_machine(wb): ws = wb.create_sheet("11-MMACHINE") headers = ["MachineId", "MachineCode", "MachineName", "WorkCenterId", "ProcessId", "Status", "TenantId", "Notes"] write_headers(ws, headers) rows = [ (1, "MCH-01", "Casting Machine 1", 1, 10, 1, 1, "Primary casting machine. Has Pattern-1 mounted at run start (S6)."), (2, "MCH-02", "Casting Machine 2", 1, 10, 1, 1, "Secondary casting machine. Used as alternative candidate in WC-CAST."), (3, "MCH-03", "CNC Lathe 1", 2, 20, 1, 1, "Fully booked in S10 — no free segment for 365-day lookahead."), (4, "MCH-04", "Nesting Machine 1", 3, 30, 1, 1, "Used for S11 nesting plan scheduling."), ] for i, r in enumerate(rows, 2): write_row(ws, i, list(r), bg=C_MASTER, comment_map={8: r[-1]}) autosize(ws) freeze(ws) def build_shiftmap(wb): ws = wb.create_sheet("12-MWORKCENTERSHIFTMAP") headers = ["ShiftMapId", "WorkCenterId", "WorkCenterCode", "DayOfWeek", "DayName", "ShiftStartTime (mins)", "ShiftEndTime (mins)", "ShiftStart (HH:MM)", "ShiftEnd (HH:MM)", "BreakStart (mins)", "BreakEnd (mins)", "NetWorkingMins", "EffectiveFrom", "Status", "TenantId", "Notes"] write_headers(ws, headers) days = [(1,"Mon"),(2,"Tue"),(3,"Wed"),(4,"Thu"),(5,"Fri"),(6,"Sat")] shift_data = [] # WC-CAST: Shift-A (06:00-14:00) and Shift-B (14:00-22:00) Mon-Sat for d, dname in days: sid_a = (d - 1) * 2 + 1 sid_b = (d - 1) * 2 + 2 note_a = "S7/S8: Adjacent shifts — MergeAdjacentWindows joins A+B into 900-min window" if d == 1 else "" note_b = "S7: Interruptible job spills into Shift-B" if d == 1 else "" shift_data.append((sid_a, 1, "WC-CAST", d, dname, 360, 840, "06:00", "14:00", 600, 630, 450, "2026-01-01", 1, 1, note_a)) shift_data.append((sid_b, 1, "WC-CAST", d, dname, 840, 1320, "14:00", "22:00", 1080, 1110, 450, "2026-01-01", 1, 1, note_b)) # WC-MACH: 08:00-16:00 Mon-Sat for idx, (d, dname) in enumerate(days): shift_data.append((13 + idx, 2, "WC-MACH", d, dname, 480, 960, "08:00", "16:00", 720, 750, 450, "2026-01-01", 1, 1, "S10: MCH-03 is fully booked — no slot despite this shift" if d == 1 else "")) # WC-NEST: 08:00-20:00 Mon-Sat for idx, (d, dname) in enumerate(days): shift_data.append((19 + idx, 3, "WC-NEST", d, dname, 480, 1200, "08:00", "20:00", 720, 750, 690, "2026-01-01", 1, 1, "S11: Nesting plan fits in single shift" if d == 1 else "")) for i, r in enumerate(shift_data, 2): bg = C_INTERR if r[2] == "WC-CAST" and r[3] == 1 else C_MASTER cmap = {16: r[-1]} if r[-1] else None write_row(ws, i, list(r), bg=bg, comment_map=cmap) autosize(ws) freeze(ws) def build_batchrule(wb): ws = wb.create_sheet("13-MBATCHRULE") headers = ["MBatchRuleId", "WorkCenterId", "WorkCenterCode", "ProcessId", "BatchBasisType", "BatchBasisTypeName", "BatchMinimum", "BatchMaximum", "PreferredBatchQty", "BatchContinuity", "Status", "TenantId", "Notes"] write_headers(ws, headers) rows = [ (1, 1, "WC-CAST", 10, 0, "Fixed", 50, 150, 150, 0, 1, 1, "S5: IndentDetails 1201(qty=75) + 1202(qty=75) merged → combined qty=150 ≤ max. Duration=30+3×150=480 min."), ] for i, r in enumerate(rows, 2): write_row(ws, i, list(r), bg=C_BATCH, comment_map={13: r[-1]}) autosize(ws) freeze(ws) def build_routing_version(wb): ws = wb.create_sheet("14-MROUTINGVERSION") headers = ["RoutingVersionId", "ItemId", "SkuId", "RoutingId", "VersionNumber", "FromDate", "ToDate", "Status", "TenantId", "Notes"] write_headers(ws, headers) rows = [ (101, 101, 201, 1, 1, "2026-01-01", None, 1, 1, "Flywheel Housing — used in S1, S2, S5, S6, S10"), (102, 105, 205, 2, 1, "2026-01-01", None, 1, 1, "Cylinder Block — used in S7 (interruptible) and S8 (non-interruptible)"), (103, 103, 203, 3, 1, "2026-01-01", None, 1, 1, "Subcontract Component — used in S3 (ExecutionMode=1)"), (104, 104, 204, 4, 1, "2026-01-01", None, 1, 1, "Monitor-only process — S4 (LocationType=5)"), (105, 101, 201, 5, 1, "2026-01-01", None, 1, 1, "Flywheel Housing v2 — used in S5 batch merge"), ] for i, r in enumerate(rows, 2): write_row(ws, i, list(r), bg=C_MASTER, comment_map={10: r[-1]}) autosize(ws) freeze(ws) def build_routing_detail(wb): ws = wb.create_sheet("15-MROUTINGVERSIONDETAIL") headers = [ "RoutingDetailId", "RoutingVersionId", "SlNo", "WorkCenterId", "WorkCenterCode", "ProcessId", "ExecutionMode", "ExecutionModeName", "IsInterruptible (0=Yes)", "MinRunMins", "MaxWaitMins", "FixedLeadTime", "LeadTimeUom", "LeadTimeUomName", "SubcontractDays", "BatchContinuity", "LocationType", "IsMonitorOnly (0=Yes)", "IsGroupOutputStep (0=Yes)", "Predecessor", "TenantId", "Notes" ] write_headers(ws, headers) rows = [ (1001, 101, 1, 1, "WC-CAST", 10, 0, "InHouse", 1, 0, 0, 60, 1, "Minutes", 0, 0, 0, 1, 1, "", 1, "S1/S2/S6/S10: InHouse. BOR row 2001 drives duration. IsInterruptible=1 means NOT interruptible."), (1002, 102, 1, 1, "WC-CAST", 10, 0, "InHouse", 0, 60, 0, 60, 1, "Minutes", 0, 0, 0, 1, 1, "", 1, "S7/S8: IsInterruptible=0 means IS interruptible. MinRunMins=60 — segments shorter than 60min skipped."), (1003, 103, 1, 1, "WC-CAST", 10, 1, "Subcontract", 1, 0, 0, 0, 1, "Minutes", 5, 0, 0, 1, 1, "", 1, "S3: ExecutionMode=1. Duration=5×480=2400 min. No machine assigned."), (1004, 104, 1, 2, "WC-MACH", 20, 0, "InHouse", 1, 0, 0, 30, 1, "Minutes", 0, 0, 5, 0, 1, "", 1, "S4: LocationType=5 → IsMonitorOnly=0 (YES monitor-only). Engine skips slot finding."), (1005, 105, 1, 1, "WC-CAST", 10, 0, "InHouse", 1, 0, 0, 60, 1, "Minutes", 0, 0, 0, 1, 0, "", 1, "S5: IsGroupOutputStep=0 (YES group output step). Batch merge combines 2 details."), ] for i, r in enumerate(rows, 2): bg = SCENARIO_COLORS.get(f"S{i-1}", C_MASTER) write_row(ws, i, list(r), bg=bg, comment_map={22: r[-1]}) # Add IS* convention note row r = len(rows) + 3 ws.cell(row=r, column=1, value="★ IS* TINYINT Convention").font = bold_font() ws.cell(row=r, column=2, value="0 = YES (the IS* condition is true) | 1 = NO (condition is false). Example: ISINTERRUPTIBLE=0 means the job IS interruptible.").font = normal_font() autosize(ws) freeze(ws) def build_bor(wb): ws = wb.create_sheet("16-MBILLOFRESOURCE") headers = [ "BillOfResourceId", "RoutingDetailId", "Type", "TypeName", "ResourceId (MachineId)", "SetupTime", "CycleTime", "TimeUom", "TimeUomName", "SlNo", "TenantId", "Duration Formula", "Notes" ] write_headers(ws, headers) rows = [ (2001, 1001, 4, "Machine Resource", 1, 30, 3, 1, "Minutes", 1, 1, "30 + 3 × Qty", "S1 (Qty=100): 330min | S2: same BOR but ManualDuration overrides | S6 (Qty=60): 210min | S10 (Qty=80): 270min"), (2002, 1002, 4, "Machine Resource", 1, 30, 3, 1, "Minutes", 1, 1, "30 + 3 × Qty", "S7 (Qty=190): 600min total across 2 shift segments | S8 (Qty=170): 540min single contiguous slot"), (2003, 1005, 4, "Machine Resource", 1, 30, 3, 1, "Minutes", 1, 1, "30 + 3 × Qty", "S5 (BatchQty=150): 30+3×150=480min — exactly fills Shift-A net capacity"), ] for i, r in enumerate(rows, 2): write_row(ws, i, list(r), bg=C_MASTER, comment_map={13: r[-1]}) r = len(rows) + 3 ws.cell(row=r, column=1, value="★ TYPE values: 1=Machine(legacy) 2=Tool 3=Die 4=Machine(ESE current) 5=Operator 6=WC-level resource").font = normal_font() ws.cell(row=r, column=1).fill = fill(INFO_BG) autosize(ws) freeze(ws) def build_pattern(wb): ws = wb.create_sheet("17-MPATTERN") headers = ["PatternId", "PatternCode", "PatternName", "NumberOfCavity", "Status", "TenantId", "Notes"] write_headers(ws, headers) rows = [ (1, "PAT-001", "Flywheel Pattern", 4, 1, 1, "Last mounted on MCH-01. S6: job requires PAT-002 → PatternAlreadyMounted=false → 30-min changeover."), (2, "PAT-002", "Camshaft Pattern", 2, 1, 1, "S6: required by IndentDetail 1105. MCH-01 must swap from PAT-001. PATTERNNEWMOUNT=1 in detail log."), ] for i, r in enumerate(rows, 2): write_row(ws, i, list(r), bg=C_MASTER, comment_map={7: r[-1]}) autosize(ws) freeze(ws) def build_indent(wb): ws = wb.create_sheet("20-TINDENT") headers = [ "IndentId", "IndentNumber", "WorkCenterId", "WorkCenterCode", "ProcessId", "OUID", "Status", "IsNPD", "ExpectedFirstDeliveryDate", "ExpectedCompletionDate", "Scenario", "Notes" ] write_headers(ws, headers) rows = [ (501, "IND-2026-001", 1, "WC-CAST", 10, 100, 1, 0, "2026-06-16", "2026-06-16 17:00", "S1", "S1: Simple in-house. Deadline 17:00 → 390 min slack after 11:30 end."), (502, "IND-2026-002", 1, "WC-CAST", 10, 100, 1, 0, "2026-06-16", "2026-06-16 14:00", "S2", "S2: Manual override 200 min. Job starts after S1 ends (11:30) → ends 14:50, misses 14:00 deadline by 50 min (DeadlineSlack=-50)."), (503, "IND-2026-003", 1, "WC-CAST", 10, 100, 1, 0, "2026-06-19", "2026-06-23 00:00", "S3", "S3: Subcontract. EXPECTEDCOMPLETIONDATE far out to allow 5-day lead time."), (504, "IND-2026-004", 2, "WC-MACH", 20, 100, 1, 0, "2026-06-16", "2026-06-16 18:00", "S4", "S4: Monitor-only step. Engine logs it; no machine scheduled."), (505, "IND-2026-005", 1, "WC-CAST", 10, 100, 1, 0, "2026-06-16", "2026-06-16 14:00", "S5a", "S5 (batch part 1): IndentDetail 1201, qty=75. Merged with indent 506 by BatchRule."), (506, "IND-2026-006", 1, "WC-CAST", 10, 100, 1, 0, "2026-06-16", "2026-06-16 14:00", "S5b", "S5 (batch part 2): IndentDetail 1202, qty=75. Same deadline triggers batch merge."), (507, "IND-2026-007", 1, "WC-CAST", 10, 100, 1, 0, "2026-06-16", "2026-06-17 16:00", "S6", "S6: Job requires Pattern-2. MCH-01 has Pattern-1 → changeover task inserted."), (508, "IND-2026-008", 1, "WC-CAST", 10, 100, 1, 0, "2026-06-16", "2026-06-17 16:00", "S7", "S7: Interruptible, 600 min needed. Adjacent shifts provide enough total capacity."), (509, "IND-2026-009", 1, "WC-CAST", 10, 100, 1, 0, "2026-06-16", "2026-06-17 22:00", "S8", "S8: Non-interruptible, 540 min. MergeAdjacentWindows gives 900-min slot."), (510, "IND-2026-010", 4, "WC-MILL", 40, 100, 1, 0, "2026-06-16", "2026-06-16 17:00", "S9", "S9: WorkCenterId=4 (WC-MILL) has no machines → FailedNoCandidates."), (511, "IND-2026-011", 2, "WC-MACH", 20, 100, 1, 0, "2026-06-16", "2026-06-16 17:00", "S10", "S10: MCH-03 exists but shift is 100% booked → FailedNoSlot."), ] for i, r in enumerate(rows, 2): scen = r[-2] bg = SCENARIO_COLORS.get(scen.split("/")[0].strip(), C_MASTER) write_row(ws, i, list(r), bg=bg, comment_map={12: r[-1]}) autosize(ws) freeze(ws) def build_indent_detail(wb): ws = wb.create_sheet("21-TINDENTDETAIL") headers = [ "IndentDetailId", "IndentId", "SlNo", "ItemId", "SkuId", "RoutingVersionDetailId", "IndentDetailQty", "PlannedQty", "ManualDurationMins", "MachineId_Override", "ScheduleStatus", "ReleasestATUS", "Scenario", "Notes" ] write_headers(ws, headers) rows = [ (1101, 501, 1, 101, 201, 1001, 100, 100, None, None, 0, 0, "S1", "S1: No manual override. Duration from BOR: 30+3×100=330 min. DURATIONSOURCE=0."), (1102, 502, 1, 101, 201, 1001, 100, 100, 200, None, 0, 0, "S2", "S2: MANUALDURATIONMINUTES=200. Engine uses 200 min; BOR ignored. DURATIONSOURCE=1."), (1103, 503, 1, 103, 203, 1003, 50, 50, None, None, 0, 0, "S3", "S3: RoutingDetail has ExecutionMode=1 (Subcontract). Duration=5×480=2400 min."), (1104, 504, 1, 104, 204, 1004, 20, 20, None, None, 0, 0, "S4", "S4: LocationType=5 on routing step → engine sets IsMonitorOnly=0. No slot needed."), (1201, 505, 1, 101, 201, 1005, 75, 75, None, None, 0, 0, "S5a", "S5a: Batch part 1. BatchRule (max=150) merges with 1202 (75+75=150). BCH-001."), (1202, 506, 1, 101, 201, 1005, 75, 75, None, None, 0, 0, "S5b", "S5b: Batch part 2. Same WC+routing as 1201. Duration for merged EU=480 min."), (1105, 507, 1, 102, 202, 1001, 60, 60, None, None, 0, 0, "S6", "S6: Routing step links to Pattern-2 (via CandidateDAL). MCH-01 has Pattern-1 → changeover."), (1106, 508, 1, 105, 205, 1002, 190, 190, None, None, 0, 0, "S7", "S7: IsInterruptible=0 on routing. 30+3×190=600 min. Spans Shift-A and Shift-B."), (1107, 509, 1, 105, 205, 1002, 170, 170, None, None, 0, 0, "S8", "S8: Same routing as S7 but different indent. 30+3×170=540 min. Non-interruptible after merge."), (1108, 510, 1, 106, 206, None, 30, 30, None, None, 0, 0, "S9", "S9: WorkCenter WC-MILL has no machines. CandidateDAL returns []. Decision=3."), (1109, 511, 1, 101, 201, 1001, 80, 80, None, None, 0, 0, "S10", "S10: MCH-03 in WC-MACH. Another job pre-fills all 450 min. No free segment ≥270 min."), ] for i, r in enumerate(rows, 2): scen = r[-2] bg = SCENARIO_COLORS.get(scen, C_MASTER) write_row(ws, i, list(r), bg=bg, comment_map={14: r[-1]}) autosize(ws) freeze(ws) def build_nesting_plan(wb): ws = wb.create_sheet("22-TNESTINGPLAN") headers = [ "NestingPlanId", "CmsId", "WorkCenterId", "WorkCenterCode", "SetupTime", "CycleTime", "NestingPlanNestingTime", "ScheduleStatus", "TenantId", "Scenario", "Notes" ] write_headers(ws, headers) rows = [ (3001, 5001, 3, "WC-NEST", 45, 90, 0, 0, 1, "S11", "S11: DurationMinutes = SETUPTIME(45) + CYCLETIME(90) = 135 min. DEMANDTYPE=7 in detail log."), ] for i, r in enumerate(rows, 2): write_row(ws, i, list(r), bg=C_SCHEDULED, comment_map={11: r[-1]}) autosize(ws) freeze(ws) def build_run_log(wb): ws = wb.create_sheet("30-TSCHEDULINGRUNLOG") headers = [ "SchedulingRunId", "TenantId", "TriggeredByUserId", "HorizonFromDate", "HorizonToDate", "MachinesScheduled", "UtilizationPct", "DurationMs", "Status", "StatusName", "StartedAt", "CompletedAt", "RCCPJson", "Notes" ] write_headers(ws, headers) rccp_json = ('[{"WorkCenterId":1,"WorkCenterName":"WC-CAST","CapacityMinutes":2400,' '"RequiredMinutes":3800,"OverloadPct":158.33,"IsOverloaded":true},' '{"WorkCenterId":2,"WorkCenterName":"WC-MACH","CapacityMinutes":2250,' '"RequiredMinutes":270,"OverloadPct":12.0,"IsOverloaded":false},' '{"WorkCenterId":3,"WorkCenterName":"WC-NEST","CapacityMinutes":3450,' '"RequiredMinutes":135,"OverloadPct":3.9,"IsOverloaded":false}]') rows = [ (1001, 1, 42, "2026-06-16", "2026-06-20", 4, 82.5, 1243, 2, "Completed", "2026-06-16 05:00:00", "2026-06-16 05:00:01", rccp_json, "S12: WC-CAST IsOverloaded=true (158%). Engine still schedules; RCCP is advisory warning only."), ] for i, r in enumerate(rows, 2): write_row(ws, i, list(r), bg=C_MASTER, comment_map={14: r[-1]}) autosize(ws) freeze(ws) def build_detail_log(wb): ws = wb.create_sheet("31-TSCHEDULINGRUNDETAILLOG") ws.sheet_view.showGridLines = True # Add explanatory note at top ws.merge_cells("A1:N1") ws["A1"].value = ("★ KEY DIAGNOSTIC SHEET — One row per EU (Execution Unit). " "Read FAILUREREASON for jobs that did not schedule. " "Check CANDIDATESJSON to understand why each machine was accepted or rejected.") ws["A1"].font = Font(bold=True, size=10, color="1F3864", name="Calibri") ws["A1"].fill = fill(INFO_BG) ws.row_dimensions[1].height = 20 headers = [ "DetailLogId", "SchedulingRunId", "TenantId", "SeqNo", "DemandType", "DemandTypeName", "SourceDocumentId", "SourceDetailId", "WorkCenterId", "ProcessId", "ItemId", "SkuId", "DurationMinutes", "DurationSource", "DurationSourceName", "OriginalQty", "ExecutionMode", "ExecutionModeName", "IsInterruptible (0=Yes)", "MinRunMins", "MaxWaitMins", "EarlyStart", "ComputedEarlyStart", "Deadline", "Priority", "RootBatchNumber", "BatchItemCount", "BatchedDetailIds", "TotalBatchQty", "Decision", "DecisionName", "MachineId", "PatternId", "PatternNewMount", "ScheduledStart", "ScheduledEnd", "SlotWaitMins", "DeadlineSlackMins", "ShelfLifeViolation", "WinningScore", "CandidatesConsidered", "CandidatesJson", "FailureReason", "Scenario" ] write_headers(ws, headers, row=2) base_dt = "2026-06-16 " cand_ok = ('[{"MachineId":1,"Score":0.92,"SlotFound":true,"RejectReason":"","PatternId":1,"PatternMounted":true},' '{"MachineId":2,"Score":0.78,"SlotFound":true,"RejectReason":"LowerScore","PatternId":1,"PatternMounted":true}]') cand_changeover = ('[{"MachineId":1,"Score":0.85,"SlotFound":true,"RejectReason":"","PatternId":2,"PatternMounted":false},' '{"MachineId":2,"Score":0.72,"SlotFound":true,"RejectReason":"LowerScore","PatternId":2,"PatternMounted":false}]') cand_noslot = '[{"MachineId":3,"Score":0.0,"SlotFound":false,"RejectReason":"FullyBooked","PatternId":null,"PatternMounted":false}]' rows = [ # (DetailLogId, RunId, Tenant, Seq, DType, DTypeName, SrcDocId, SrcDetailId, # WCID, ProcId, ItemId, SkuId, # DurMin, DurSrc, DurSrcName, OrigQty, ExecMode, ExecModeName, # IsInterr, MinRunMins, MaxWait, # EarlyStart, CompEarlyStart, Deadline, # Prio, BatchNum, BatchCnt, BatchedIds, TotalBatchQty, # Decision, DecName, MachId, PatId, NewMount, # SchedStart, SchedEnd, SlotWait, Slack, # ShelfViol, Score, CandCount, CandJson, # FailReason, Scenario) (1, 1001, 1, 1, 0, "ProductionIndent", 501, 1101, 1, 10, 101, 201, 330, 0, "BOR (Setup+CycleTime×Qty)", 100.0, 0, "InHouse", 1, 0, 0, base_dt+"06:00", base_dt+"06:00", base_dt+"17:00", 1, "SGL-001", 1, None, 100.0, 0, "Scheduled", 1, 1, 0, base_dt+"06:00", base_dt+"11:30", 0, 330, 0, 0.92, 2, cand_ok, None, "S1"), (2, 1001, 1, 2, 0, "ProductionIndent", 502, 1102, 1, 10, 101, 201, 200, 1, "ManualOverride", 100.0, 0, "InHouse", 1, 0, 0, base_dt+"06:00", base_dt+"06:00", base_dt+"14:00", 1, "SGL-002", 1, None, 100.0, 0, "Scheduled", 1, 1, 0, base_dt+"11:30", base_dt+"14:50", 690, -50, 0, 0.92, 2, cand_ok, None, "S2"), (3, 1001, 1, 3, 0, "ProductionIndent", 503, 1103, 1, 10, 103, 203, 2400, 2, "SubcontractLeadTime", 50.0, 1, "Subcontract", 1, 0, 0, base_dt+"06:00", base_dt+"06:00", "2026-06-23 00:00", 1, "SGL-003", 1, None, 50.0, 1, "Subcontract", None, None, None, None, None, None, None, 0, None, 0, None, None, "S3"), (4, 1001, 1, 4, 0, "ProductionIndent", 504, 1104, 2, 20, 104, 204, 30, 0, "BOR", 20.0, 0, "InHouse", 1, 0, 0, base_dt+"06:00", base_dt+"06:00", base_dt+"18:00", 1, "SGL-004", 1, None, 20.0, 2, "MonitorOnly", None, None, None, None, None, None, None, 0, None, 0, None, None, "S4"), (5, 1001, 1, 5, 0, "ProductionIndent", 505, 1201, 1, 10, 101, 201, 480, 0, "BOR (Setup+CycleTime×Qty)", 75.0, 0, "InHouse", 1, 0, 0, base_dt+"06:00", base_dt+"06:00", base_dt+"14:00", 1, "BCH-001", 2, "[1201,1202]", 150.0, 0, "Scheduled", 1, 1, 0, base_dt+"14:50", base_dt+"22:50", 530, -530, 0, 0.92, 2, cand_ok, None, "S5"), (6, 1001, 1, 6, 0, "ProductionIndent", 507, 1105, 1, 10, 102, 202, 210, 0, "BOR (Setup+CycleTime×Qty)", 60.0, 0, "InHouse", 1, 0, 0, base_dt+"06:00", base_dt+"06:00", "2026-06-17 16:00", 1, "SGL-005", 1, None, 60.0, 0, "Scheduled", 1, 2, 1, base_dt+"06:30", base_dt+"10:00", 30, 1800, 0, 0.85, 2, cand_changeover, None, "S6"), (7, 1001, 1, 7, 0, "ProductionIndent", 508, 1106, 1, 10, 105, 205, 600, 0, "BOR (Setup+CycleTime×Qty)", 190.0, 0, "InHouse", 0, 60, 0, base_dt+"06:00", base_dt+"06:00", "2026-06-17 16:00", 1, "SGL-006", 1, None, 190.0, 0, "Scheduled", 1, 1, 0, base_dt+"06:00", base_dt+"16:30", 0, 1170, 0, 0.92, 2, cand_ok, None, "S7"), (8, 1001, 1, 8, 0, "ProductionIndent", 509, 1107, 1, 10, 105, 205, 540, 0, "BOR (Setup+CycleTime×Qty)", 170.0, 0, "InHouse", 1, 0, 0, base_dt+"06:00", base_dt+"06:00", "2026-06-17 22:00", 1, "SGL-007", 1, None, 170.0, 0, "Scheduled", 1, 1, 0, base_dt+"06:00", base_dt+"15:00", 0, 1860, 0, 0.92, 2, cand_ok, None, "S8"), (9, 1001, 1, 9, 0, "ProductionIndent", 510, 1108, 4, 40, 106, 206, 60, 0, "BOR", 30.0, 0, "InHouse", 1, 0, 0, base_dt+"06:00", base_dt+"06:00", base_dt+"17:00", 1, "SGL-008", 1, None, 30.0, 3, "FailedNoCandidates", None, None, None, None, None, None, None, 0, None, 0, None, "No machine candidates configured for WorkCenter 4. Check machine-to-work-center assignments.", "S9"), (10, 1001, 1, 10, 0, "ProductionIndent", 511, 1109, 2, 20, 101, 201, 270, 0, "BOR (Setup+CycleTime×Qty)", 80.0, 0, "InHouse", 1, 0, 0, base_dt+"06:00", base_dt+"06:00", base_dt+"17:00", 1, "SGL-009", 1, None, 80.0, 4, "FailedNoSlot", None, None, None, None, None, None, None, 0, None, 1, cand_noslot, "No slot found within 365-day lookahead across 1 candidate machine(s). Reasons: 1×FullyBooked (no free segment ≥ duration).", "S10"), (11, 1001, 1, 11, 7, "NestingPlan", 3001, 3001, 3, 30, 0, 0, 135, 0, "BOR/Fixed", 1.0, 0, "InHouse", 1, 0, 0, base_dt+"06:00", base_dt+"06:00", base_dt+"17:00", 1, "SGL-010", 1, None, 1.0, 0, "Scheduled", 4, None, 0, base_dt+"08:00", base_dt+"10:15", 120, 405, 0, 0.95, 1, '[{"MachineId":4,"Score":0.95,"SlotFound":true,"RejectReason":"","PatternId":null,"PatternMounted":false}]', None, "S11"), ] for i, r in enumerate(rows, 3): scen = r[-1] bg = SCENARIO_COLORS.get(scen, C_WHITE) write_row(ws, i, list(r), bg=bg) autosize(ws) ws.freeze_panes = "A3" def build_resource_plan(wb): ws = wb.create_sheet("32-TRESOURCEPLAN") headers = [ "ResourcePlanId", "SchedulingRunId", "IndentDetailId", "MachineId", "WorkCenterId", "ProcessId", "PlannedQuantity", "ScheduledStartDate", "ScheduledEndDate", "ResourceLevel", "ResourceLevelName", "IsInterruptible (0=Yes)", "IsLocked", "PlanNature", "ResourcePlanCombineId", "ParallelNumber", "MountingTaskId", "TenantId", "Scenario", "Notes" ] write_headers(ws, headers) base_dt = "2026-06-16 " rows = [ # S1 (1, 1001, 1101, 1, 1, 10, 100.0, base_dt+"06:00", base_dt+"11:30", 1, "Machine", 1, 0, 0, 10010000001, 1, None, 1, "S1", "S1: Single segment. Duration=330 min (BOR-driven)."), # S2 (2, 1001, 1102, 1, 1, 10, 100.0, base_dt+"11:30", base_dt+"14:50", 1, "Machine", 1, 0, 0, 10010000002, 1, None, 1, "S2", "S2: Starts after S1 ends. ManualDuration=200 min. NOTE: ends 50 min past deadline."), # S5: 2 resource plan rows (one per source detail, combined slot) (3, 1001, 1201, 1, 1, 10, 75.0, base_dt+"14:50", base_dt+"22:50", 1, "Machine", 1, 0, 0, 10010000005, 1, None, 1, "S5", "S5a: Batch part 1 (IndentDetailId=1201, qty=75). Same slot as S5b."), (4, 1001, 1202, 1, 1, 10, 75.0, base_dt+"14:50", base_dt+"22:50", 1, "Machine", 1, 0, 0, 10010000005, 1, None, 1, "S5", "S5b: Batch part 2 (IndentDetailId=1202, qty=75). Same ResourcePlanCombineId as S5a."), # S6: job start after mount (5, 1001, 1105, 1, 1, 10, 60.0, base_dt+"06:30", base_dt+"10:00", 1, "Machine", 1, 0, 0, 10010000006, 1, 1, 1, "S6", "S6: EffectiveStart=06:30 (after 30-min mount task). MountingTaskId=1 links to TTASK."), # S7: 2 interruptible segments (6, 1001, 1106, 1, 1, 10, 190.0, base_dt+"06:00", base_dt+"13:30", 1, "Machine", 0, 0, 0, 10010000007, 1, None, 1, "S7", "S7 segment 1: 450 min (06:00-13:30, net of 30-min break). ISINTERRUPTIBLE=0 (yes)."), (7, 1001, 1106, 1, 1, 10, 190.0, base_dt+"14:00", base_dt+"16:30", 1, "Machine", 0, 0, 0, 10010000007, 1, None, 1, "S7", "S7 segment 2: 150 min (14:00-16:30). Same ResourcePlanCombineId as segment 1."), # S8: single large slot (8, 1001, 1107, 1, 1, 10, 170.0, base_dt+"06:00", base_dt+"15:00", 1, "Machine", 1, 0, 0, 10010000008, 1, None, 1, "S8", "S8: Single 540-min slot. ISINTERRUPTIBLE=1 (NOT interruptible). MergeAdjacentWindows enabled this."), # S11: nesting plan (9, 1001, 3001, 4, 3, 30, 1.0, base_dt+"08:00", base_dt+"10:15", 2, "Pattern/Nesting", 1, 0, 0, 10010000011, 1, None, 1, "S11", "S11: NestingPlan. IndentDetailId=NestingPlanId=3001. ResourceLevel=2."), ] for i, r in enumerate(rows, 2): scen = r[-2] bg = SCENARIO_COLORS.get(scen, C_MASTER) write_row(ws, i, list(r), bg=bg, comment_map={20: r[-1]}) autosize(ws) freeze(ws) def build_task(wb): ws = wb.create_sheet("33-TTASK") headers = [ "TaskId", "MachineId", "MachineCode", "TaskNature", "TaskNatureName", "Status", "TaskStartDate", "TaskEndDate", "TenantId", "Scenario", "Notes" ] write_headers(ws, headers) rows = [ (1, 1, "MCH-01", 2, "Pattern Changeover", 0, "2026-06-16 06:00", "2026-06-16 06:30", 1, "S6", "S6: 30-min mould changeover task. MCH-01 swaps from Pattern-1 to Pattern-2 before casting job starts. " "TRESOURCEPLAN.MOUNTINGTASKID=1 links the job row to this task."), ] for i, r in enumerate(rows, 2): write_row(ws, i, list(r), bg=SCENARIO_COLORS["S6"], comment_map={11: r[-1]}) autosize(ws) freeze(ws) # ── Main ────────────────────────────────────────────────────────────────────── def main(): wb = Workbook() wb.remove(wb.active) # remove default sheet build_readme(wb) build_scenarios(wb) build_workcenter(wb) build_machine(wb) build_shiftmap(wb) build_batchrule(wb) build_routing_version(wb) build_routing_detail(wb) build_bor(wb) build_pattern(wb) build_indent(wb) build_indent_detail(wb) build_nesting_plan(wb) build_run_log(wb) build_detail_log(wb) build_resource_plan(wb) build_task(wb) out_path = os.path.join(os.path.dirname(__file__), "ESE_SampleData.xlsx") wb.save(out_path) print(f"✓ Saved: {out_path}") print(f" Sheets: {[ws.title for ws in wb.worksheets]}") if __name__ == "__main__": main()