import argparse import asyncio import os import sys import uuid from datetime import datetime, timezone import openpyxl from sqlalchemy import select # Ensure utf-8 output on Windows terminal if sys.stdout.encoding != 'utf-8': try: sys.stdout.reconfigure(encoding='utf-8') except Exception: pass # Add the root backend directory to sys.path sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))) from app.core.database import AsyncSessionLocal from app.models.yard_models import ChassisMaster, ChassisStatusLog, VehicleModelMaster from app.models.sales_models import CustomerMaster def parse_date_to_utc(val): if val is None: return datetime.now(timezone.utc) if isinstance(val, datetime): return val.replace(tzinfo=timezone.utc) if val.tzinfo is None else val.astimezone(timezone.utc) if hasattr(val, 'year') and hasattr(val, 'month') and hasattr(val, 'day'): return datetime(val.year, val.month, val.day, 12, 0, 0, tzinfo=timezone.utc) try: dt = datetime.fromisoformat(str(val).strip()) return dt.replace(tzinfo=timezone.utc) if dt.tzinfo is None else dt.astimezone(timezone.utc) except Exception: return datetime.now(timezone.utc) def parse_date_only(val): if val is None: return None if isinstance(val, datetime): return val.date() if hasattr(val, 'year') and hasattr(val, 'month') and hasattr(val, 'day'): return val try: dt = datetime.fromisoformat(str(val).strip()) return dt.date() except Exception: return None async def run_update(excel_path: str, dry_run: bool = True, inward_missing: bool = False): print("=" * 70) print("AUDI ERP - DISPATCH STATUS UPDATE TOOL") print(f"File: {excel_path}") print(f"Mode: {'DRY RUN (No database changes will be saved)' if dry_run else 'LIVE EXECUTION (Database will be updated)'}") print(f"Inward Missing Chassis: {inward_missing}") print("=" * 70) if not os.path.exists(excel_path): print(f"[ERROR] Excel file not found at {excel_path}") return wb = openpyxl.load_workbook(excel_path, data_only=True) if 'PRODUCTION REPORT' not in wb.sheetnames: print("[ERROR] 'PRODUCTION REPORT' sheet not found in Excel file.") return ws = wb['PRODUCTION REPORT'] rows = list(ws.iter_rows(values_only=True)) if len(rows) < 2: print("[ERROR] Sheet contains no data.") return # Extract chassis data report_items = [] for r_idx in range(1, len(rows)): r = rows[r_idx] chassis_raw = r[1] if chassis_raw is not None and str(chassis_raw).strip(): chassis_no = str(chassis_raw).strip() status_val = str(r[18]).strip() if r[18] is not None else '' disp_date = r[27] receive_date = r[16] model_val = str(r[12]).strip() if r[12] is not None else None customer_val = str(r[13]).strip() if r[13] is not None else None report_items.append({ 'row_num': r_idx + 1, 'chassis_no': chassis_no, 'status': status_val, 'disp_date': disp_date, 'receive_date': receive_date, 'model': model_val, 'customer': customer_val, }) print(f"\n[INFO] Extracted {len(report_items)} chassis records from 'PRODUCTION REPORT' sheet.") async with AsyncSessionLocal() as db: # Load all existing chassis res = await db.execute(select(ChassisMaster)) all_chassis = res.scalars().all() db_chassis_map = {c.chassis_no.lower().strip(): c for c in all_chassis if c.chassis_no} # Load models for possible auto-inward model_res = await db.execute(select(VehicleModelMaster)) all_models = model_res.scalars().all() model_map = {m.model_number.lower().strip(): m.model_id for m in all_models if m.model_number} matched_updates = [] missing_chassis = [] for item in report_items: c_no_key = item['chassis_no'].lower() if c_no_key in db_chassis_map: matched_updates.append((item, db_chassis_map[c_no_key])) else: missing_chassis.append(item) print(f"\n[MATCHING RESULTS]") print(f" * Matched in ChassisMaster : {len(matched_updates)} chassis") print(f" * Missing from Database : {len(missing_chassis)} chassis") # Breakdown of current status for matched chassis current_tracking_breakdown = {} for item, c in matched_updates: ts = c.tracking_status or 'NONE' current_tracking_breakdown[ts] = current_tracking_breakdown.get(ts, 0) + 1 print(f" * Current tracking_status : {current_tracking_breakdown}") print("\n[PLANNED UPDATES FOR MATCHED CHASSIS]") print(" 1. tracking_status -> 'DISPATCHED'") print(" 2. current_status -> 'DISPATCHED'") print(" 3. dispatched_at -> Date from Excel 'Dispatch Date' (converted to UTC)") print(" 4. Station WIP -> current_station_id=None, station_arrived_at=None, station_completed_at=None") print(" 5. ChassisStatusLog -> Append audit record (to_status='DISPATCHED', location='DISPATCH')") # Execute updates in transaction updated_count = 0 for item, chassis in matched_updates: disp_dt = parse_date_to_utc(item['disp_date']) old_ts = chassis.tracking_status if not dry_run: chassis.tracking_status = 'DISPATCHED' chassis.current_status = 'DISPATCHED' chassis.dispatched_at = disp_dt chassis.current_station_id = None chassis.station_arrived_at = None chassis.station_completed_at = None status_log = ChassisStatusLog( log_id=uuid.uuid4(), chassis_id=chassis.chassis_id, from_status=old_ts, to_status='DISPATCHED', changed_by=None, changed_at=disp_dt, scan_location='DISPATCH', remarks='Closed via September 2026 Closing Report', ) db.add(status_log) updated_count += 1 inwarded_count = 0 if inward_missing and missing_chassis: print(f"\n[AUTO-INWARD] Inwarding {len(missing_chassis)} missing chassis...") for item in missing_chassis: disp_dt = parse_date_to_utc(item['disp_date']) arr_d = parse_date_only(item['receive_date']) or disp_dt.date() model_id = None if item['model']: model_id = model_map.get(item['model'].lower().strip()) if not dry_run: new_chassis = ChassisMaster( chassis_id=uuid.uuid4(), chassis_no=item['chassis_no'], model_id=model_id, arrival_date=arr_d, tracking_status='DISPATCHED', current_status='DISPATCHED', dispatched_at=disp_dt, remarks=f"Auto-inwarded & closed via September 2026 Closing Report (Customer: {item['customer']})", ) db.add(new_chassis) await db.flush() status_log = ChassisStatusLog( log_id=uuid.uuid4(), chassis_id=new_chassis.chassis_id, from_status=None, to_status='DISPATCHED', changed_by=None, changed_at=disp_dt, scan_location='DISPATCH', remarks='Created & Closed via September 2026 Closing Report', ) db.add(status_log) inwarded_count += 1 if missing_chassis and not inward_missing: print("\n[NOTICE] The following 21 chassis exist in the Excel report but NOT in the database:") for idx, m in enumerate(missing_chassis, 1): print(f" {idx:2d}. Chassis: {m['chassis_no']:<10} | Model: {str(m['model']):<15} | Customer: {str(m['customer']):<25} | DispDate: {m['disp_date']}") if dry_run: print("\n" + "=" * 70) print(f"[DRY RUN COMPLETE] {updated_count} chassis would be updated to DISPATCHED.") if inward_missing: print(f"[DRY RUN COMPLETE] {inwarded_count} missing chassis would also be created and marked DISPATCHED.") print("No changes were made to the database. Run with --live to commit.") print("=" * 70) else: await db.commit() print("\n" + "=" * 70) print(f"[SUCCESS] {updated_count} chassis updated to DISPATCHED in the database.") if inward_missing: print(f"[SUCCESS] {inwarded_count} new chassis created and marked DISPATCHED.") print("All changes committed successfully!") print("=" * 70) if __name__ == '__main__': parser = argparse.ArgumentParser(description="Update chassis dispatch status from closing report") parser.add_argument('--file', default=r"D:\audi dev\Audi-ERP\docs\CLOSSING REPORT SEP 2026.xlsx", help="Path to closing report Excel") parser.add_argument('--live', action='store_true', help="Execute live database update (default is dry-run)") parser.add_argument('--inward-missing', action='store_true', help="Also create/inward the missing chassis records") args = parser.parse_args() asyncio.run(run_update(args.file, dry_run=not args.live, inward_missing=args.inward_missing))