atulyamann/flexup-api
0
1"""2core.py — Pure business logic extracted from stitched.py.3No file-system paths, no print statements in the hot path.4Call run_pipeline(file_like) -> bytes to get the output workbook.5"""6 7import io8import logging9from datetime import datetime10 11import numpy as np12import pandas as pd13 14logger = logging.getLogger(__name__)15 16# ---------------------------------------------------------------------------17# Constants18# ---------------------------------------------------------------------------19START_DATE = pd.to_datetime("06/28/2026", format="%m/%d/%Y")20END_DATE = pd.to_datetime("10/05/2026", format="%m/%d/%Y")21 22METRICS = [23 "FC Shipments Planned Cap",24 "Daily MMV",25 "Daily Delta",26 "Max of % to DCM or RSP",27 "SETWEEKP",28 "MaxWeekCap",29 "METQS hrs",30 "Flex Up from SET up to 55",31 "LaborShareMax",32 "LaborSharePlan",33 "Rate",34 "Flex Up from LS gap from max",35 "RR",36 "Hires",37 "Flex Up from Hires",38 "VTO Hours",39 "Flex Up from VTO Removal",40 "VET hours",41 "Flex up from VET addition",42 "Show hours",43 "VET Ideal hours",44 "VET Flex up",45]46 47REQUIRED_SHEETS = [48 "PC", "MMV", "LS", "MAX LS", "SU", "VTO", "VET",49 "Show", "MET hrs", "METQS hrs", "Rate", "Hires", "RR",50]51 52# ---------------------------------------------------------------------------53# Site / region data (unchanged from stitched.py)54# ---------------------------------------------------------------------------55ALL_SITES = [56 # AR Sortable57 "ABQ1","ACY1","AGS1","AGS2","AKC1","ATL2","AUS2","AUS3","BDL2","BDL3","BDL4",58 "BFI4","BFL1","BHM1","BOI2","BOS3","BTR1","BWI2","CLE2","CLE3","CLT4","CMH1","CMH4","DAB2","DAL3",59 "DCA1","DEN3","DEN4","DET3","DET6","DFW7","DSM5","DTW1","ELP1","EWR4","EWR9","FAT1","FSD1","FTW6",60 "FWA6","GEG1","GRR1","GYR1","HOU2","HOU6","IGQ1","JAN1","JAX2","JFK8","LAS7","LGA9","LGB3","LGB7",61 "LIT1","LUK2","MCO1","MDW7","MEM4","MIA1","MKC6","MKE1","MKE2","MLI1","MQY1","MSP1","MTN1","OAK4",62 "OKC1","OMA2","ORD5","ORF3","ORF4","ORH3","OXR1","PAE2","PCW1","PDX8","PDX9","PSP1","PVD2","RDU1",63 "RIC4","ROC1","SAN3","SAT2","SAT3","SAV4","SBD6","SBN1","SCK6","SHV1",64 "SLC1","SMF1","STL8","SYR1","TLH2","TPA1","TPA4","TUL2","TUS2","TYS1","VGT1",65 # NonSortable66 "ABE4","ACY2","AKR1","ALB1","AMA1","BFI3","BNA2","BOS7","BWI4","CHA2",67 "CHO1","CLT3","CMH2","CMH3","DCA6","DEN8","DET1","DET2","DFW6","FAT2","FOE1","FTW5",68 "GEG2","GSO1","HOU8","HSV1","ICT2","IGQ2","ILG1","IND5","JAX3","JVL1","LAS6","LFT1",69 "LGB4","LGB6","LIT2","MCE1","MCO2","MDT1","MDT4","MDW6","MEM6","MGE3","MKC4","OAK3",70 "OKC2","ORD2","PDX7","PHL4","PHL5","PHL6","PHX5","PHX7","PIT2","RIC1","RNO4","SAT1",71 "SAT4","SAV3","SBD2","SCK1","SJC7","SLC2","SMF6","SNA4","STL3","STL4","SWF1","TEB3",72 "TEB4","TEB6","TPA2","TPA3",73 # Canada74 "YEG1","YOO1","YOW1","YVR3","YYZ2","YYZ3","YYZ9","YYZ1","YHM1","YOW3","YUL2","YXU1",75 "YYZ4","YYZ7","YEG2","YVR2","YVR4","YXX2","YYC1","YYC4",76 # SDC77 "ATL7","AVP8","HGR5","KRB1","KRB2","KRB3","KRB4","KRB6","QXX6","SAV7",78 # TSSL79 "ABE2","CAE1","IND1","PHL7","RIC2","AFW1","BFL2","BNA3","CSG1","DAL2",80 "JAX7","MDW4","ONT2","ONT6","PHX3","RDG1","SDF2","SDF8","ABE3","FTW9","IND4","MGE1",81 # IXD82 "ABQ2","ABS4","GEU2","GEU3","GEU5","HEA2","HGR6","HIA1","HLI2","LBE1",83 "MIT2","PBI3","POC1","POC2","POC3","PPO4","PSC2","QXY8","RYY2","TCY1","TCY2","TMB8",84 "WBW2","ABE8","AVP1","BNA6","CLT2","FTW1","FWA4","GYR2","GYR3","IAH3","IND9","LAN2",85 "LAS1","LAX9","LGB8","MCC1","MDW2","MEM1","MQJ1","ONT8","ORF2","PSP3","RDU2","RDU4",86 "RFD2","RMN3","SBD1","SCK4","SMF3","SWF2","TEB9","VGT2",87 # DG88 "BFI9","BWI1","CMH7","MEM2","MLB1",89 # IXD-NonSort90 "OLM1","PCA2","QXY4","SBD3","SCK8",91]92 93# ---------------------------------------------------------------------------94# Helper: date-column conversion (unchanged logic)95# ---------------------------------------------------------------------------96def _apply_date_conversion(df: pd.DataFrame, sheet_name: str):97 converted, new_cols = [], {}98 for col in df.columns:99 if isinstance(col, (pd.Timestamp, datetime)):100 converted.append(col)101 continue102 if isinstance(col, str):103 for fmt in ("%m/%d/%Y", "%Y-%m-%d", "%m/%d/%y", "%m-%d-%Y", "%B %d, %Y"):104 try:105 ts = pd.to_datetime(col, format=fmt)106 new_cols[col] = ts107 converted.append(ts)108 break109 except (ValueError, TypeError):110 continue111 elif isinstance(col, (int, float)) and 40000 < col < 60000:112 try:113 ts = pd.Timestamp("1899-12-30") + pd.Timedelta(days=int(col))114 new_cols[col] = ts115 converted.append(ts)116 except Exception:117 pass118 if new_cols:119 df = df.rename(columns=new_cols)120 logger.debug("%s: converted %d date columns", sheet_name, len(converted))121 return df, converted122 123 124# ---------------------------------------------------------------------------125# Helper: network / region mapping (unchanged logic)126# ---------------------------------------------------------------------------127_AR_SORT = {128 "ABQ1","ACY1","AGS1","AGS2","AKC1","ATL2","AUS2","AUS3","BDL2","BDL3","BDL4",129 "BFI4","BFL1","BHM1","BOI2","BOS3","BTR1","BWI2","CLE2","CLE3","CLT4","CMH1","CMH4","DAB2","DAL3",130 "DCA1","DEN3","DEN4","DET3","DET6","DFW7","DSM5","DTW1","ELP1","EWR4","EWR9","FAT1","FSD1","FTW6",131 "FWA6","GEG1","GRR1","GYR1","HOU2","HOU6","IGQ1","JAN1","JAX2","JFK8","LAS7","LGA9","LGB3","LGB7",132 "LIT1","LUK2","MCO1","MDW7","MEM4","MIA1","MKC6","MKE1","MKE2","MLI1","MQY1","MSP1","MTN1","OAK4",133 "OKC1","OMA2","ORD5","ORF3","ORF4","ORH3","OXR1","PAE2","PCW1","PDX8","PDX9","PSP1","PVD2","RDU1",134 "RIC4","ROC1","SAN3","SAT2","SAT3","SAV4","SBD6","SBN1","SCK6","SHV1",135 "SLC1","SMF1","STL8","SYR1","TLH2","TPA1","TPA4","TUL2","TUS2","TYS1","VGT1",136}137_NONSORT = {138 "ABE4","ACY2","AKR1","ALB1","AMA1","BFI3","BNA2","BOS7","BWI4","CHA2","CHO1","CLT3","CMH2","CMH3",139 "DCA6","DEN8","DET1","DET2","DFW6","FAT2","FOE1","FTW5","GEG2","GSO1","HOU8","HSV1","ICT2","IGQ2",140 "ILG1","IND5","JAX3","JVL1","LAS6","LFT1","LGB4","LGB6","LIT2","MCE1","MCO2","MDT1","MDT4","MDW6",141 "MEM6","MGE3","MKC4","OAK3","OKC2","ORD2","PDX7","PHL4","PHL5","PHL6","PHX5","PHX7","PIT2","RIC1",142 "RNO4","SAT1","SAT4","SAV3","SBD2","SCK1","SJC7","SLC2","SMF6","SNA4","STL3","STL4","SWF1","TEB3",143 "TEB4","TEB6","TPA2","TPA3",144}145_CANADA = {146 "YEG1","YOO1","YOW1","YVR3","YYZ2","YYZ3","YYZ9","YYZ1","YHM1","YOW3","YUL2","YXU1",147 "YYZ4","YYZ7","YEG2","YVR2","YVR4","YXX2","YYC1","YYC4",148}149_SDC = {"ATL7","AVP8","HGR5","KRB1","KRB2","KRB3","KRB4","KRB6","QXX6","SAV7"}150_TSSL = {151 "ABE2","CAE1","IND1","PHL7","RIC2","AFW1","BFL2","BNA3","CSG1","DAL2",152 "JAX7","MDW4","ONT2","ONT6","PHX3","RDG1","SDF2","SDF8","ABE3","FTW9","IND4","MGE1",153}154_IXD = {155 "ABQ2","ABS4","GEU2","GEU3","GEU5","HEA2","HGR6","HIA1","HLI2","LBE1","MIT2","PBI3",156 "POC1","POC2","POC3","PPO4","PSC2","QXY8","RYY2","TCY1","TCY2","TMB8","WBW2","ABE8",157 "AVP1","BNA6","CLT2","FTW1","FWA4","GYR2","GYR3","IAH3","IND9","LAN2","LAS1","LAX9",158 "LGB8","MCC1","MDW2","MEM1","MQJ1","ONT8","ORF2","PSP3","RDU2","RDU4","RFD2","RMN3",159 "SBD1","SCK4","SMF3","SWF2","TEB9","VGT2",160}161_NIXD = {162 "ABQ2","ABS4","GEU2","GEU3","GEU5","HEA2","HGR6","HIA1","HLI2","LBE1","MIT2","PBI3",163 "POC1","POC2","POC3","PPO4","PSC2","QXY8","RYY2","TCY1","TCY2","TMB8","WBW2",164}165_DG = {"BFI9","BWI1","CMH7","MEM2","MLB1"}166_IXDNS = {"OLM1","PCA2","QXY4","SBD3","SCK8"}167 168_CA_WEST = {"YEG2","YVR2","YVR4","YXX2","YYC1","YYC4"}169_CA_EAST = {"YHM1","YOW3","YUL2","YXU1","YYZ4","YYZ7"}170 171_NORTHWEST = {"BFI9","BFI3","DEN8","GEG2","PDX7","SLC2","BFI1","BFI4","BOI2","DEN3","DEN4","GEG1","PAE2","PDX8","PDX9","SLC1","BFI7","DEN7","PDX6","SLC3"}172_SOUTHWEST = {"FAT2","LAS6","LGB4","LGB6","MCE1","OAK3","PHX5","PHX7","RNO4","SBD2","SCK1","SJC7","SMF6","SNA4","KRB1","KRB3","KRB4","BFL1","FAT1","GYR1","LAS7","LGB3","LGB7","OAK4","OXR1","PSP1","SAN3","SBD6","SCK6","SMF1","TUS2","VGT1","BFL2","ONT2","ONT6","PHX3"}173_MIDWEST = {"CMH7","AKR1","CMH2","CMH3","DET1","DET2","IND5","PIT2","CLE2","CLE3","CMH1","CMH4","DET3","DET6","DTW1","FWA6","GRR1","LUK2","PCW1","IND1","SDF1","SDF2","SDF8","IND4"}174_GREATLAKES = {"FOE1","IGQ2","JVL1","MDW6","MKC4","ORD2","STL3","STL4","DSM5","FSD1","IGQ1","MDW7","MKC6","MKE1","MKE2","MLI1","MSP1","OMA2","ORD5","SBN1","STL8","MDW4"}175_MIDSOUTH = {"MEM2","BNA2","CHA2","HSV1","LFT1","LIT2","MEM6","MGE3","AGS2","ATL2","BHM1","BTR1","JAN1","LIT1","MEM4","MQY1","SHV1","BNA3","CSG1","MGE1"}176_TEXAS = {"AMA1","DFW6","FTW5","HOU8","ICT2","OKC2","SAT1","SAT4","ABQ1","AUS2","AUS3","DAL3","DFW7","ELP1","FTW6","HOU2","HOU6","OKC1","SAT2","SAT3","TUL2","AFW1","DAL2","FTW9"}177_NORTHEAST = {"ABE4","ACY2","ALB1","BOS7","PHL4","PHL5","SWF1","TEB3","TEB4","TEB6","AVP8","HGR5","QXX6","BDL2","BDL3","BDL4","BOS3","EWR4","EWR9","JFK8","LGA9","ORH3","PVD2","ROC1","SYR1","ABE2","RDG1","ABE3"}178_MIDATLANTIC = {"BWI1","BWI4","CHO1","CLT3","DCA6","GSO1","ILG1","MDT1","MDT4","PHL6","RIC1","KRB2","ACY1","AKC1","BWI2","CLT4","DCA1","MTN1","ORF3","ORF4","RDU1","RIC4","TYS1","CAE1","PHL7","RIC2"}179_FLORIDA = {"MLB1","JAX3","MCO2","SAV3","TPA2","TPA3","ATL7","SAV7","AGS1","DAB2","JAX2","MCO1","MIA1","SAV4","TLH2","TPA1","TPA4","JAX7"}180 181 182def get_network_region(site: str):183 if site in _AR_SORT:184 network = "AR Sortable"185 if site in _NORTHWEST: region = "NorthWest"186 elif site in _SOUTHWEST: region = "SouthWest"187 elif site in _MIDWEST: region = "MidWest"188 elif site in _GREATLAKES:region = "GreatLakes"189 elif site in _MIDSOUTH: region = "MidSouth"190 elif site in _TEXAS: region = "Texas"191 elif site in _NORTHEAST: region = "NorthEast"192 elif site in _MIDATLANTIC: region = "MidAtlantic"193 elif site in _FLORIDA: region = "Florida"194 else: region = "UnMapped"195 elif site in _NONSORT:196 network, region = "NonSortable", "NonSortable"197 elif site in _CANADA:198 network = "CANADA"199 if site in _CA_WEST: region = "Sortable-West"200 elif site in _CA_EAST: region = "Sortable-East"201 elif site == "YYZ1": region = "CA-Returns"202 else: region = "NonSortable"203 elif site in _SDC:204 network, region = "SDC", "SDC"205 elif site in _TSSL:206 network, region = "TSSL", "TSSL"207 elif site in _IXD:208 network = "IXD"209 region = "NIXD" if site in _NIXD else "RIXD"210 elif site in _DG:211 network, region = "DG", "DG"212 elif site in _IXDNS:213 network, region = "IXD-NonSort", "IXD-NonSort"214 else:215 network, region = "UnMapped", "UnMapped"216 return network, region217 218 219# ---------------------------------------------------------------------------220# Main pipeline221# ---------------------------------------------------------------------------222 223def _load_sheets(file_like) -> dict:224 """Load and validate all required sheets from the workbook."""225 # Read all bytes once so we can do multiple read_excel passes226 if hasattr(file_like, "read"):227 content = file_like.read()228 else:229 with open(file_like, "rb") as f:230 content = f.read()231 232 # First check sheet names exist233 xl = pd.ExcelFile(io.BytesIO(content))234 missing = [s for s in REQUIRED_SHEETS if s not in xl.sheet_names]235 if missing:236 raise ValueError(f"Workbook is missing required sheets: {missing}")237 238 # Use pd.read_excel per-sheet to match stitched.py behavior exactly239 return {s: pd.read_excel(io.BytesIO(content), sheet_name=s) for s in REQUIRED_SHEETS}240 241 242def run_pipeline(file_like) -> bytes:243 """244 Execute the full FlexUp metrics pipeline.245 246 Parameters247 ----------248 file_like : file-like object (BytesIO or path)249 Source Excel workbook.250 251 Returns252 -------253 bytes254 Output Excel workbook as raw bytes.255 """256 sheets = _load_sheets(file_like)257 258 pc_df = sheets["PC"]259 mmv_df = sheets["MMV"]260 ls_df = sheets["LS"]261 max_ls_df = sheets["MAX LS"]262 su_df = sheets["SU"]263 vto_df = sheets["VTO"]264 vet_df = sheets["VET"]265 show_df = sheets["Show"]266 met_hrs_df = sheets["MET hrs"]267 metqs_df = sheets["METQS hrs"]268 rate_df = sheets["Rate"]269 hires_df = sheets["Hires"]270 rr_raw_df = sheets["RR"]271 272 # Drop irrelevant columns273 for col in ["Concept", "Shift", "Flow"]:274 if col in metqs_df.columns:275 metqs_df = metqs_df.drop(columns=[col])276 for col in ["Concept", "Aspect", "Flow", "Shift", "concept", "aspect", "flow", "shift"]:277 if col in met_hrs_df.columns:278 met_hrs_df = met_hrs_df.drop(columns=[col])279 280 # Normalise date columns281 pc_df, _ = _apply_date_conversion(pc_df, "PC")282 ls_df, _ = _apply_date_conversion(ls_df, "LS")283 vto_df, _ = _apply_date_conversion(vto_df, "VTO")284 vet_df, _ = _apply_date_conversion(vet_df, "VET")285 show_df, _ = _apply_date_conversion(show_df, "Show")286 met_hrs_df, _ = _apply_date_conversion(met_hrs_df, "MET hrs")287 metqs_df, _ = _apply_date_conversion(metqs_df, "METQS hrs")288 rate_df, _ = _apply_date_conversion(rate_df, "Rate")289 hires_df, _ = _apply_date_conversion(hires_df, "Hires")290 su_df, _ = _apply_date_conversion(su_df, "SU")291 292 # Build date range293 date_range = pd.date_range(start=START_DATE, end=END_DATE, freq="D")294 dates = [f"{dt.month}/{dt.day}" for dt in date_range]295 296 # Build date mapping from PC sheet — restrict to dates within START_DATE..END_DATE297 # to avoid collisions when month/day repeats across years298 date_cols_pc = [299 c for c in pc_df.columns300 if isinstance(c, (pd.Timestamp, datetime))301 and START_DATE <= pd.Timestamp(c) <= END_DATE302 ]303 date_mapping = {f"{c.month}/{c.day}": c for c in date_cols_pc}304 dates = [d for d in dates if d in date_mapping]305 logger.info("Processing %d dates for %d sites", len(dates), len(ALL_SITES))306 307 # Lookups308 pc_lookup = pc_df.set_index("site")309 ls_lookup = ls_df.set_index("site")310 vto_lookup = vto_df.set_index("site")311 vet_lookup = vet_df.set_index("site")312 show_lookup = show_df.set_index("site")313 met_hrs_lookup = met_hrs_df.set_index("site")314 metqs_lookup = metqs_df.set_index("site")315 rate_lookup = rate_df.set_index("site")316 hires_lookup = hires_df.set_index("site")317 su_lookup = su_df.set_index("site")318 319 # Process RR: daily = weekly / 7320 rr_raw_df["Run Week"] = pd.to_datetime(rr_raw_df["Run Week"], format="%m/%d/%Y")321 rr_expanded = []322 for _, row in rr_raw_df.iterrows():323 week_start = row["Run Week"]324 daily_rr = row["Run Rate"] / 7325 site = row["Site"]326 for day_offset in range(7):327 rr_expanded.append({328 "site": site,329 "date": week_start + pd.Timedelta(days=day_offset),330 "daily_rr": daily_rr,331 })332 rr_daily_df = pd.DataFrame(rr_expanded)333 rr_lookup = rr_daily_df.pivot_table(334 index="site", columns="date", values="daily_rr", aggfunc="sum"335 ).fillna(0)336 337 # Build output338 # Pre-group dataframes by site to avoid repeated filtering339 max_ls_grouped = {site: df for site, df in max_ls_df.groupby("site")}340 rr_raw_grouped = {site: df for site, df in rr_raw_df.groupby("Site")}341 342 all_sites_data = []343 for site_code in ALL_SITES:344 site_df = _create_site_metrics(345 site_code, dates, date_mapping,346 pc_lookup, mmv_df, ls_lookup, max_ls_df, su_lookup,347 vto_lookup, vet_lookup, show_lookup, met_hrs_lookup,348 metqs_lookup, rate_lookup, hires_lookup, rr_lookup, rr_raw_df,349 max_ls_grouped=max_ls_grouped, rr_raw_grouped=rr_raw_grouped,350 )351 all_sites_data.append(site_df)352 353 combined_df = pd.concat(all_sites_data, ignore_index=True)354 355 # Serialise to bytes356 buf = io.BytesIO()357 with pd.ExcelWriter(buf, engine="openpyxl") as writer:358 combined_df.to_excel(writer, sheet_name="SiteLevel(2)", index=False)359 buf.seek(0)360 return buf.read()361 362 363# ---------------------------------------------------------------------------364# Per-site metric calculation (ported from stitched.py, no I/O)365# ---------------------------------------------------------------------------366 367def _row_as_dict(lookup_df, site_code):368 """Extract a single site's row as a dict {column: value}. Returns empty dict if missing."""369 if site_code not in lookup_df.index:370 return {}371 row = lookup_df.loc[site_code]372 if isinstance(row, pd.DataFrame): # duplicate rows; take first373 row = row.iloc[0]374 return row.to_dict()375 376 377def _create_site_metrics(378 site_code, dates, date_mapping,379 pc_lookup, mmv_df, ls_lookup, max_ls_df, su_lookup,380 vto_lookup, vet_lookup, show_lookup, met_hrs_lookup,381 metqs_lookup, rate_lookup, hires_lookup, rr_lookup, rr_raw_df,382 # Pre-computed structures383 max_ls_grouped=None, rr_raw_grouped=None,384):385 network, region = get_network_region(site_code)386 387 # Extract each site's row ONCE as a dict — replaces thousands of .loc lookups388 pc_row = _row_as_dict(pc_lookup, site_code)389 ls_row = _row_as_dict(ls_lookup, site_code)390 vto_row = _row_as_dict(vto_lookup, site_code)391 vet_row = _row_as_dict(vet_lookup, site_code)392 show_row = _row_as_dict(show_lookup, site_code)393 met_hrs_row = _row_as_dict(met_hrs_lookup, site_code)394 metqs_row = _row_as_dict(metqs_lookup, site_code)395 rate_row = _row_as_dict(rate_lookup, site_code)396 hires_row = _row_as_dict(hires_lookup, site_code)397 rr_row = _row_as_dict(rr_lookup, site_code)398 su_row = _row_as_dict(su_lookup, site_code)399 400 site_in_pc = bool(pc_row)401 site_in_ls = bool(ls_row)402 site_in_vto = bool(vto_row)403 site_in_vet = bool(vet_row)404 site_in_show = bool(show_row)405 site_in_met = bool(met_hrs_row)406 site_in_metqs = bool(metqs_row)407 site_in_rate = bool(rate_row)408 site_in_hires = bool(hires_row)409 site_in_rr = bool(rr_row)410 site_in_su = bool(su_row)411 412 # Pre-compute hires column dates and values for fast weekly summation413 hires_cols_dates = []414 for col in hires_row.keys():415 try:416 hires_cols_dates.append((col, pd.to_datetime(col)))417 except Exception:418 continue419 420 # Pre-build MMV weekly dict421 mmv_site_data = mmv_df[mmv_df["FC"] == site_code]422 mmv_weekly = {}423 if len(mmv_site_data) > 0:424 for col in mmv_site_data.columns:425 if col == "FC":426 continue427 try:428 col_date = pd.to_datetime(col)429 val = mmv_site_data.iloc[0][col]430 if pd.notna(val) and val != "":431 mmv_weekly[col_date] = float(val) / 7432 except Exception:433 continue434 435 # MMV cache by date_str so we don't re-iterate every metric436 daily_mmv_cache = {}437 438 def get_daily_mmv(dt_col):439 cached = daily_mmv_cache.get(dt_col)440 if cached is not None:441 return cached442 dt_date = pd.to_datetime(dt_col)443 for week_start, daily_val in mmv_weekly.items():444 if week_start <= dt_date <= week_start + pd.Timedelta(days=6):445 daily_mmv_cache[dt_col] = daily_val446 return daily_val447 daily_mmv_cache[dt_col] = 0448 return 0449 450 # Pre-filtered max_ls / rr_raw for this site451 if max_ls_grouped is not None:452 max_ls_site = max_ls_grouped.get(site_code)453 else:454 max_ls_site = max_ls_df[max_ls_df["site"] == site_code]455 if rr_raw_grouped is not None:456 rr_raw_site = rr_raw_grouped.get(site_code)457 else:458 rr_raw_site = rr_raw_df[rr_raw_df["Site"] == site_code]459 460 site_data = []461 for metric in METRICS:462 row = {"Network": network, "Region": region, "Site": site_code, "Metric": metric}463 for date_str in dates:464 try:465 dt_col = date_mapping.get(date_str)466 if dt_col is None:467 value = ""468 elif metric == "FC Shipments Planned Cap":469 value = pc_row.get(dt_col, "") if site_in_pc else ""470 elif metric == "Daily MMV":471 v = get_daily_mmv(dt_col)472 value = v if v > 0 else ""473 elif metric == "Daily Delta":474 if site_in_pc and get_daily_mmv(dt_col) > 0:475 value = get_daily_mmv(dt_col) - pc_row[dt_col]476 else:477 value = ""478 elif metric == "Max of % to DCM or RSP":479 if site_in_pc and get_daily_mmv(dt_col) > 0:480 fc_val = pc_row[dt_col]481 dcm_ratio = fc_val / get_daily_mmv(dt_col) if get_daily_mmv(dt_col) != 0 else 0482 rsp_val = su_row.get(dt_col)483 if rsp_val is not None and pd.notna(rsp_val) and rsp_val != "":484 rsp_ratio = rsp_val / 100485 else:486 rsp_ratio = 0487 value = max(dcm_ratio, rsp_ratio)488 else:489 value = ""490 elif metric == "SETWEEKP":491 value = met_hrs_row.get(dt_col, 0) if site_in_met else 0492 elif metric == "MaxWeekCap":493 value = 55494 elif metric == "METQS hrs":495 value = metqs_row.get(dt_col, "") if site_in_metqs else ""496 elif metric == "Flex Up from SET up to 55":497 if site_in_pc and get_daily_mmv(dt_col) > 0:498 fc_val = pc_row[dt_col]499 delta = get_daily_mmv(dt_col) - fc_val500 met_val = metqs_row.get(dt_col, 40) if site_in_metqs else 40501 if met_val == "" or (isinstance(met_val, float) and pd.isna(met_val)):502 met_val = 40503 met_val = float(met_val)504 max_flex = (55 / met_val) * fc_val - fc_val if met_val != 0 else 0505 value = max(0, min(delta, max_flex))506 else:507 value = ""508 elif metric == "LaborShareMax":509 try:510 current_date = pd.to_datetime(f"{date_str}/2026", format="%m/%d/%Y")511 matching = max_ls_site[max_ls_site["start_date"] == current_date]512 value = matching["input_value"].sum() or ""513 except Exception:514 value = ""515 elif metric == "LaborSharePlan":516 value = ls_row.get(dt_col, "") if site_in_ls else ""517 elif metric == "Rate":518 value = rate_row.get(dt_col, "") if site_in_rate else ""519 elif metric == "Flex Up from LS gap from max":520 if site_in_pc and site_in_ls and get_daily_mmv(dt_col) > 0:521 fc_val = pc_row[dt_col]522 delta = get_daily_mmv(dt_col) - fc_val523 try:524 current_date = pd.to_datetime(f"{date_str}/2026", format="%m/%d/%Y")525 matching = max_ls_site[max_ls_site["start_date"] == current_date]526 ls_max = matching["input_value"].sum()527 except Exception:528 ls_max = 0529 ls_plan = ls_row.get(dt_col)530 if site_in_show:531 show_val = show_row.get(dt_col)532 rate = (fc_val / show_val533 if show_val not in ("", None) and not pd.isna(show_val) and show_val != 0534 else 0)535 else:536 rate = 0537 ls_gap_flex = (ls_max - ls_plan) * rate538 value = max(0, min(delta, ls_gap_flex)) if ls_max > 0 else ""539 else:540 value = ""541 elif metric == "RR":542 value = rr_row.get(dt_col, "") if site_in_rr else ""543 elif metric == "Hires":544 value = hires_row.get(dt_col, "") if site_in_hires else ""545 elif metric == "Flex Up from Hires":546 if site_in_pc and site_in_hires and site_in_rate and site_in_rr and get_daily_mmv(dt_col) > 0:547 fc_val = pc_row[dt_col]548 delta = get_daily_mmv(dt_col) - fc_val549 rate = rate_row.get(dt_col)550 dt_date = pd.to_datetime(dt_col)551 dow = dt_date.dayofweek552 week_start = dt_date - pd.Timedelta(days=(dow + 1) % 7)553 week_end = week_start + pd.Timedelta(days=6)554 555 # Sum Hires across the whole week using pre-computed column dates556 weekly_hires = 0557 for wc, wc_date in hires_cols_dates:558 if week_start <= wc_date <= week_end:559 h = hires_row.get(wc)560 if h is not None and pd.notna(h) and h != "":561 try:562 weekly_hires += float(h)563 except Exception:564 continue565 566 rr_match = rr_raw_site[567 (rr_raw_site["Run Week"] <= dt_date) &568 (rr_raw_site["Run Week"] + pd.Timedelta(days=6) >= dt_date)569 ]570 rr_val = float(rr_match["Run Rate"].iloc[0]) if len(rr_match) > 0 else 0571 if rate in ("", None) or (isinstance(rate, float) and pd.isna(rate)):572 rate = 0573 rate = float(rate)574 flex_hires = (rr_val - weekly_hires) * rate / 7575 value = max(0, min(delta, flex_hires))576 else:577 value = ""578 elif metric == "VTO Hours":579 value = vto_row.get(dt_col, "") if site_in_vto else ""580 elif metric == "Flex Up from VTO Removal":581 if site_in_pc and site_in_ls and site_in_vto and get_daily_mmv(dt_col) > 0:582 fc_val = pc_row[dt_col]583 delta = get_daily_mmv(dt_col) - fc_val584 vto_val = vto_row.get(dt_col)585 rate = rate_row.get(dt_col)586 if vto_val in ("", None) or (isinstance(vto_val, float) and pd.isna(vto_val)):587 vto_val = 0588 if rate in ("", None) or (isinstance(rate, float) and pd.isna(rate)):589 rate = 0590 value = max(0, min(delta, float(vto_val) * float(rate)))591 else:592 value = ""593 elif metric == "VET hours":594 value = vet_row.get(dt_col, "") if site_in_vet else ""595 elif metric == "Flex up from VET addition":596 if site_in_pc and site_in_show and site_in_vet and get_daily_mmv(dt_col) > 0:597 setweekp = met_hrs_row.get(dt_col, 0) if site_in_met else 0598 if setweekp in ("", None) or (isinstance(setweekp, float) and pd.isna(setweekp)):599 setweekp = 0600 setweekp = float(setweekp)601 fc_val = pc_row[dt_col]602 delta = get_daily_mmv(dt_col) - fc_val603 vet_val = vet_row.get(dt_col)604 show_val = show_row.get(dt_col)605 vet_ideal = (0 if setweekp > 0606 else (show_val * 0.1 if show_val not in ("", None) and not pd.isna(show_val) else 0))607 vet_flex_up = (max(0, vet_ideal - float(vet_val))608 if vet_val not in ("", None) and not pd.isna(vet_val)609 else vet_ideal)610 rate = rate_row.get(dt_col)611 value = max(0, min(delta, vet_flex_up * float(rate) if rate not in ("", None) and not pd.isna(rate) else 0))612 else:613 value = ""614 elif metric == "Show hours":615 value = show_row.get(dt_col, "") if site_in_show else ""616 elif metric == "VET Ideal hours":617 if site_in_show:618 show_val = show_row.get(dt_col)619 setweekp = met_hrs_row.get(dt_col, 0) if site_in_met else 0620 if setweekp in ("", None) or (isinstance(setweekp, float) and pd.isna(setweekp)):621 setweekp = 0622 setweekp = float(setweekp)623 value = (0 if setweekp > 0624 else (show_val * 0.1 if show_val not in ("", None) and not pd.isna(show_val) else ""))625 else:626 value = ""627 elif metric == "VET Flex up":628 if site_in_vet and site_in_show:629 setweekp = met_hrs_row.get(dt_col, 0) if site_in_met else 0630 if setweekp in ("", None) or (isinstance(setweekp, float) and pd.isna(setweekp)):631 setweekp = 0632 setweekp = float(setweekp)633 vet_val = vet_row.get(dt_col)634 show_val = show_row.get(dt_col)635 if (show_val not in ("", None) and not pd.isna(show_val)636 and vet_val not in ("", None) and not pd.isna(vet_val)):637 vet_ideal = 0 if setweekp > 0 else show_val * 0.1638 value = 0 if float(vet_val) > vet_ideal else vet_ideal - float(vet_val)639 else:640 value = ""641 else:642 value = ""643 else:644 value = ""645 except Exception:646 value = ""647 row[date_str] = value648 site_data.append(row)649 return pd.DataFrame(site_data)650