""" Ingest Monthly Stock from JAN 2026.xlsx - Reads ALL sheets (JAN 2026, FEB 2026, etc.) - Parses 28 material groups with their sub-items - Stores category_order (1-28) to preserve Excel ordering - Uses the actual values from Excel cells (not recalculated) """ import asyncio import sys import re import pandas as pd from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy import text from decimal import Decimal, InvalidOperation import datetime import os BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))) sys.path.insert(0, os.path.join(BASE_DIR, 'backend')) from app.core.database import engine, Base from app.models.maintenance_models import MonthlyStock FILEPATH = os.path.join(BASE_DIR, 'Maintainance', 'monthly cost', 'JAN 2026.xlsx') MONTH_MAP = { 'JAN': 1, 'FEB': 2, 'MARCH': 3, 'APRILL': 4, 'MAY': 5, 'JUNE': 6, 'JULY': 7, 'AUG': 8, 'SEP': 9, 'OCT': 10, 'NOV': 11, 'DEC': 12, 'MARCH': 3, 'APRIL': 4, 'AUGUST': 8, 'SEPTEMBER': 9, 'OCTOBER': 10, 'NOVEMBER': 11, 'DECEMBER': 12 } SKIP_MATERIAL_NAMES = {'0', '0.0', 'nan', 'none', '', '-', 'nan', 'materials name', 'uom', 'cost', 'n/a'} def to_d(val): if val is None: return Decimal('0') s = str(val).strip() if s.lower() in {'nan', '', '-', 'none'}: return Decimal('0') try: return Decimal(s) except (InvalidOperation, ValueError): return Decimal('0') def parse_month_year(sheet_name): """Parse sheet name like 'JAN 2026' -> (1, 2026)""" parts = sheet_name.strip().upper().split() month = None year = None for p in parts: if p in MONTH_MAP: month = MONTH_MAP[p] elif p.isdigit() and len(p) == 4: year = int(p) return month, year def parse_sheet(df): """ Parse one month sheet into list of material entries. Returns: list of dicts with category, category_order, material_name, uom, cost, ... """ entries = [] current_category = 'UNCATEGORIZED' current_category_order = 0 for idx, row in df.iterrows(): vals = list(row) # col1 = serial/group number (vals[1]) # col2 = material name or group name (vals[2]) # col3 = uom (vals[3]) # col4 = cost (vals[4]) # col5 = opening qty (vals[5]) # col6 = open amount (vals[6]) # col7 = incoming qty (vals[7]) # col8 = incoming amount (vals[8]) # col9 = total qty (vals[9]) # col10 = total amount (vals[10]) # col11 = consumption qty (vals[11]) # col12 = consumption amount (vals[12]) # col13 = closing qty (vals[13]) # col14 = closing amount (vals[14]) col2_raw = vals[2] if len(vals) > 2 else None col3_raw = vals[3] if len(vals) > 3 else None # uom col1_raw = vals[1] if len(vals) > 1 else None if col2_raw is None or (isinstance(col2_raw, float) and pd.isna(col2_raw)): continue col2_str = str(col2_raw).strip() if not col2_str or col2_str.lower() in SKIP_MATERIAL_NAMES: continue # Skip header rows if col2_str.lower() in ('materials name',): continue # TOTAL CONSUMPTION row — skip (we compute in frontend) if 'total consumption' in col2_str.lower(): continue # GRAND TOTAL row — skip if 'grand' in col2_str.lower() and 'total' in col2_str.lower(): continue # Check if this is a category/group header row: # A category header has no UoM (NaN) AND col1 has a group number uom_raw = col3_raw uom_is_nan = uom_raw is None or (isinstance(uom_raw, float) and pd.isna(uom_raw)) or str(uom_raw).strip() in ('nan', '') # Also check if it's a header by looking at cost column cost_raw = vals[4] if len(vals) > 4 else None cost_is_nan = cost_raw is None or (isinstance(cost_raw, float) and pd.isna(cost_raw)) or str(cost_raw).strip() in ('nan', '', 'cost') if uom_is_nan and cost_is_nan: # This is a group/category header row # col1 could be the group number cat_order = 0 if col1_raw is not None and not (isinstance(col1_raw, float) and pd.isna(col1_raw)): try: cat_order = int(float(str(col1_raw).strip())) except: cat_order = current_category_order + 1 else: cat_order = current_category_order + 1 current_category = col2_str current_category_order = cat_order continue # It's a material row — skip if name is a junk value mat_name = col2_str if mat_name in SKIP_MATERIAL_NAMES or mat_name == '0': continue # Try to get numeric material name - if it's only digits, skip try: float(mat_name) continue # pure number, not a material name except: pass # Parse values from Excel (use as-is, don't recalculate) cost = to_d(vals[4] if len(vals) > 4 else None) op_qty = to_d(vals[5] if len(vals) > 5 else None) op_amt = to_d(vals[6] if len(vals) > 6 else None) inc_qty = to_d(vals[7] if len(vals) > 7 else None) inc_amt = to_d(vals[8] if len(vals) > 8 else None) tot_qty = to_d(vals[9] if len(vals) > 9 else None) tot_amt = to_d(vals[10] if len(vals) > 10 else None) cons_qty = to_d(vals[11] if len(vals) > 11 else None) cons_amt = to_d(vals[12] if len(vals) > 12 else None) clos_qty = to_d(vals[13] if len(vals) > 13 else None) clos_amt = to_d(vals[14] if len(vals) > 14 else None) # Skip rows where all numeric values are zero AND no cost defined if cost == 0 and op_qty == 0 and inc_qty == 0 and cons_qty == 0: continue entries.append({ 'category': current_category, 'category_order': current_category_order, 'material_name': mat_name[:300], 'uom': str(uom_raw).strip()[:50] if not uom_is_nan else '', 'cost': cost, 'opening_qty': op_qty, 'opening_amount': op_amt if op_amt else (op_qty * cost), 'incoming_qty': inc_qty, 'incoming_amount': inc_amt if inc_amt else (inc_qty * cost), 'total_qty': tot_qty if tot_qty else (op_qty + inc_qty), 'total_amount': tot_amt if tot_amt else ((op_qty + inc_qty) * cost), 'consumption_qty': cons_qty, 'consumption_amount': cons_amt if cons_amt else (cons_qty * cost), 'closing_qty': clos_qty if clos_qty else (op_qty + inc_qty - cons_qty), 'closing_amount': clos_amt if clos_amt else ((op_qty + inc_qty - cons_qty) * cost), }) return entries async def ingest_all(): print(f"Reading: {FILEPATH}") # Create tables async with engine.begin() as conn: await conn.run_sync(Base.metadata.create_all) wb = pd.ExcelFile(FILEPATH) print(f"Sheets: {wb.sheet_names}") async with AsyncSession(engine) as session: async with session.begin(): for sheet in wb.sheet_names: month, year = parse_month_year(sheet) if not month or not year: print(f" Skipping sheet: {sheet} (cannot parse month/year)") continue print(f"\nProcessing: {sheet} -> {month}/{year}") # Clear existing data for this month/year await session.execute( text("DELETE FROM maintenance_monthly_stock WHERE stock_month=:m AND stock_year=:y"), {'m': month, 'y': year} ) df = pd.read_excel(FILEPATH, sheet_name=sheet, skiprows=3, header=None) entries = parse_sheet(df) if not entries: print(f" No valid entries found") continue now = datetime.datetime.utcnow() for e in entries: item = MonthlyStock( stock_month=month, stock_year=year, category=e['category'], category_order=e['category_order'], material_name=e['material_name'], uom=e['uom'], cost=e['cost'], opening_qty=e['opening_qty'], opening_amount=e['opening_amount'], incoming_qty=e['incoming_qty'], incoming_amount=e['incoming_amount'], total_qty=e['total_qty'], total_amount=e['total_amount'], consumption_qty=e['consumption_qty'], consumption_amount=e['consumption_amount'], closing_qty=e['closing_qty'], closing_amount=e['closing_amount'], created_at=now, ) session.add(item) print(f" Loaded {len(entries)} items") print("\nDone!") asyncio.run(ingest_all())