import os import uuid from datetime import datetime, date, timezone from typing import List, Optional from fastapi import HTTPException from sqlalchemy import select, func, or_, and_ from sqlalchemy.ext.asyncio import AsyncSession from app.models.yard_models import ( YardCheckpointMaster, MovementChecklistItemMaster, YardQaChecklistItemMaster, VehicleModelMaster, ChassisMaster, ChassisMovementLog, ChassisMovementChecklistResult, ChassisQaChecklistResult, StoreHandoverPartMaster, ChassisStoreHandover, ChassisStoreHandoverPart, ChassisStatusLog, ) from app.models.production_models import ChassisStationLog from app.models.sales_models import TransportMaster, CustomerMaster from app.models.rm_models import StationMaster from app.models.core_models import LoginUsers from app.modules.yard import schemas from app.modules.chassis_tracker.order_spec_service import resolve_chassis_order_spec def fuel_qty_unit(fuel_type: str) -> str: return 'BAR' if (fuel_type or '').upper() == 'CNG' else 'L' def _fmt_minutes(mins: Optional[int]) -> str: if mins is None or mins < 0: return "0m" hours = mins // 60 rem = mins % 60 if hours > 0: return f"{hours}h {rem}m" return f"{rem}m" import pytz IST = pytz.timezone('Asia/Kolkata') def format_ist_time(dt: Optional[datetime]) -> str: """Format UTC datetime into Indian Standard Time (IST) 12-hour AM/PM format.""" if not dt: return "" if dt.tzinfo is None: dt = pytz.utc.localize(dt) return dt.astimezone(IST).strftime("%I:%M %p") FRIENDLY_TRACKING_STATUS = { 'CHASSIS_IN': 'Chassis In', 'WIP': 'In Production (WIP)', 'READY_FOR_DISPATCH': 'Ready for Dispatch (FVI)', 'DISPATCHED': 'Dispatched', } def remove_upload_file(file_url: str | None) -> None: if not file_url or not file_url.startswith('/uploads/'): return file_path = os.path.abspath(os.path.join(os.getcwd(), file_url.lstrip('/'))) uploads_root = os.path.abspath(os.path.join(os.getcwd(), 'uploads')) if file_path.startswith(uploads_root + os.sep) and os.path.isfile(file_path): os.remove(file_path) async def get_masters(db: AsyncSession) -> schemas.YardMastersOut: checkpoints = (await db.execute( select(YardCheckpointMaster).where(YardCheckpointMaster.is_active == True) .order_by(YardCheckpointMaster.sequence_no) )).scalars().all() movement_items = (await db.execute( select(MovementChecklistItemMaster).where(MovementChecklistItemMaster.is_active == True) .order_by(MovementChecklistItemMaster.sequence_no) )).scalars().all() qa_items = (await db.execute( select(YardQaChecklistItemMaster).where(YardQaChecklistItemMaster.is_active == True) .order_by(YardQaChecklistItemMaster.sequence_no) )).scalars().all() models = (await db.execute( select(VehicleModelMaster).where(VehicleModelMaster.is_active == True) .order_by(VehicleModelMaster.model_number) )).scalars().all() handover_parts = (await db.execute( select(StoreHandoverPartMaster).where(StoreHandoverPartMaster.is_active == True) .order_by(StoreHandoverPartMaster.sr_no) )).scalars().all() # Ensure "Battery Serial No." default part exists has_battery_sn = any(p.part_name.lower().strip() in ('battery serial no.', 'battery serial no') for p in handover_parts) if not has_battery_sn: max_sr = max((p.sr_no or 0 for p in handover_parts), default=0) battery_part = StoreHandoverPartMaster( part_id=uuid.uuid4(), sr_no=max_sr + 1, part_name="Battery Serial No.", fuel_applicability="ALL", is_active=True, ) db.add(battery_part) await db.commit() handover_parts = (await db.execute( select(StoreHandoverPartMaster).where(StoreHandoverPartMaster.is_active == True) .order_by(StoreHandoverPartMaster.sr_no) )).scalars().all() return schemas.YardMastersOut( checkpoints=checkpoints, movement_items=movement_items, qa_items=qa_items, models=models, handover_parts=handover_parts, ) async def list_chassis(db: AsyncSession, status: str | None = None): from app.models.yard_models import CustomerSuppliedPart, ChassisArrivalDocument from app.models.sales_models import ClientMaster stmt = ( select( ChassisMaster.chassis_id, ChassisMaster.chassis_no, ChassisMaster.fuel_type, ChassisMaster.yard_location, ChassisMaster.arrival_date, ChassisMaster.arrival_remark, ChassisMaster.da_lite_status, ChassisMaster.da_lite_photo_url, ChassisMaster.current_status, ChassisMaster.tracking_status, ChassisMaster.station_completed_at, StationMaster.station_name.label('current_station_name'), VehicleModelMaster.model_number.label('model_name'), CustomerMaster.name.label('dealer_name'), ClientMaster.name.label('client_name'), TransportMaster.name.label('transport_name'), func.count(func.distinct(CustomerSuppliedPart.csp_id)).label('csp_count'), func.count(func.distinct(ChassisArrivalDocument.doc_id)).label('doc_count'), ) .outerjoin(VehicleModelMaster, ChassisMaster.model_id == VehicleModelMaster.model_id) .outerjoin(StationMaster, ChassisMaster.current_station_id == StationMaster.station_id) .outerjoin(CustomerMaster, ChassisMaster.dealer_id == CustomerMaster.customer_id) .outerjoin(ClientMaster, ChassisMaster.client_id == ClientMaster.client_id) .outerjoin(TransportMaster, ChassisMaster.transport_id == TransportMaster.transport_id) .outerjoin(CustomerSuppliedPart, ChassisMaster.chassis_id == CustomerSuppliedPart.chassis_id) .outerjoin(ChassisArrivalDocument, ChassisMaster.chassis_id == ChassisArrivalDocument.chassis_id) .group_by( ChassisMaster.chassis_id, StationMaster.station_name, VehicleModelMaster.model_number, CustomerMaster.name, ClientMaster.name, TransportMaster.name ) .order_by(ChassisMaster.arrival_date.desc(), ChassisMaster.created_at.desc()) ) if status: stmt = stmt.where(ChassisMaster.current_status == status) result = await db.execute(stmt) return [schemas.ChassisListItem(**dict(row)) for row in result.mappings().all()] async def create_chassis(db: AsyncSession, payload: schemas.ChassisCreate, user_id: uuid.UUID) -> ChassisMaster: chassis_no = (payload.chassis_no or '').strip().upper() if not chassis_no: raise HTTPException(status_code=400, detail="Chassis number is required") if len(chassis_no) != 6 or not chassis_no.isalnum(): raise HTTPException(status_code=400, detail="Chassis number must be exactly 6 alphanumeric characters") existing = (await db.execute( select(ChassisMaster).where(func.lower(ChassisMaster.chassis_no) == chassis_no.lower()) )).scalar_one_or_none() if existing: raise HTTPException(status_code=400, detail="Chassis with this number is already in the yard") chassis = ChassisMaster( chassis_id=uuid.uuid4(), chassis_no=chassis_no, model_id=payload.model_id, fuel_type=payload.fuel_type, light_type=payload.light_type, voltage=payload.voltage, yard_location=payload.yard_location, transport_id=payload.transport_id, dealer_id=payload.dealer_id, client_id=payload.client_id, source_type=payload.source_type, build_order_id=payload.build_order_id, fuel_receiving_qty=payload.fuel_receiving_qty, arrival_date=payload.arrival_date, remarks=payload.remarks, tracking_status='CHASSIS_IN', created_by=user_id, ) db.add(chassis) await db.flush() status_log = ChassisStatusLog( log_id=uuid.uuid4(), chassis_id=chassis.chassis_id, from_status=None, to_status='CHASSIS_IN', changed_by=user_id, scan_location='YARD', remarks='Chassis Inward Received', ) db.add(status_log) await db.commit() await db.refresh(chassis) chassis.fuel_qty_unit = fuel_qty_unit(chassis.fuel_type) return chassis async def update_arrival(db: AsyncSession, chassis_id: uuid.UUID, payload: schemas.UpdateArrivalRequest) -> schemas.ChassisOut: chassis = await db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(status_code=404, detail="Chassis not found") chassis.arrival_date = payload.arrival_date chassis.arrival_remark = payload.remark if payload.yard_location is not None: chassis.yard_location = payload.yard_location await db.commit() await db.refresh(chassis) chassis.fuel_qty_unit = fuel_qty_unit(chassis.fuel_type) return chassis async def get_chassis_detail(db: AsyncSession, chassis_id: uuid.UUID) -> dict: from app.models.yard_models import CustomerSuppliedPart, ChassisArrivalDocument chassis = await db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(status_code=404, detail="Chassis not found") chassis.fuel_qty_unit = fuel_qty_unit(chassis.fuel_type) if chassis.model_id: chassis.model_name = (await db.execute( select(VehicleModelMaster.model_number).where(VehicleModelMaster.model_id == chassis.model_id) )).scalar_one_or_none() qa_results = (await db.execute( select(ChassisQaChecklistResult).where(ChassisQaChecklistResult.chassis_id == chassis_id) )).scalars().all() movements = (await db.execute( select(ChassisMovementLog).where(ChassisMovementLog.chassis_id == chassis_id) .order_by(ChassisMovementLog.created_at) )).scalars().all() handover = (await db.execute( select(ChassisStoreHandover).where(ChassisStoreHandover.chassis_id == chassis_id) )).scalar_one_or_none() if handover: handover.parts = (await db.execute( select(ChassisStoreHandoverPart).where(ChassisStoreHandoverPart.handover_id == handover.handover_id) )).scalars().all() # Attach names dynamically if handover.store_manager_approved_by: handover.store_manager_approved_by_name = (await db.execute( select(LoginUsers.name).where(LoginUsers.user_id == handover.store_manager_approved_by) )).scalar_one_or_none() if handover.yard_supervisor_approved_by: handover.yard_supervisor_approved_by_name = (await db.execute( select(LoginUsers.name).where(LoginUsers.user_id == handover.yard_supervisor_approved_by) )).scalar_one_or_none() documents = (await db.execute( select(ChassisArrivalDocument).where(ChassisArrivalDocument.chassis_id == chassis_id).order_by(ChassisArrivalDocument.created_at) )).scalars().all() csps = (await db.execute( select(CustomerSuppliedPart).where(CustomerSuppliedPart.chassis_id == chassis_id).order_by(CustomerSuppliedPart.created_at) )).scalars().all() return { "chassis": chassis, "qa_results": qa_results, "movements": movements, "store_handover": handover, "documents": documents, "csp": csps } async def update_da_lite( db: AsyncSession, chassis_id: uuid.UUID, status: str | None, da_lite_remarks: str | None, user_id: uuid.UUID, da_lite_photo_url: str | None = None ) -> ChassisMaster: chassis = await db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(status_code=404, detail="Chassis not found") if status: status = status.upper().strip() if status not in ('OK', 'HOLD', 'PENDING'): raise HTTPException(status_code=400, detail="da_lite_status must be OK, HOLD, or PENDING") chassis.da_lite_status = status elif not chassis.da_lite_status: chassis.da_lite_status = 'OK' if da_lite_remarks is not None: chassis.da_lite_remarks = da_lite_remarks if da_lite_photo_url is not None: if chassis.da_lite_photo_url and chassis.tracking_status in ('WIP', 'READY_FOR_DISPATCH', 'DISPATCHED'): chassis.dispatch_photo_url = da_lite_photo_url else: chassis.da_lite_photo_url = da_lite_photo_url if chassis.tracking_status in ('WIP', 'READY_FOR_DISPATCH', 'DISPATCHED'): chassis.dispatch_photo_url = da_lite_photo_url chassis.da_lite_checked_at = datetime.now(timezone.utc) chassis.da_lite_checked_by = user_id await db.commit() await db.refresh(chassis) chassis.fuel_qty_unit = fuel_qty_unit(chassis.fuel_type) return chassis async def delete_da_lite_photo( db: AsyncSession, chassis_id: uuid.UUID, user_id: uuid.UUID ) -> ChassisMaster: chassis = await db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(status_code=404, detail="Chassis not found") photo_urls = {chassis.da_lite_photo_url} if chassis.dispatch_photo_url == chassis.da_lite_photo_url: chassis.dispatch_photo_url = None chassis.da_lite_photo_url = None chassis.da_lite_checked_at = datetime.now(timezone.utc) chassis.da_lite_checked_by = user_id await db.commit() for photo_url in photo_urls: remove_upload_file(photo_url) await db.refresh(chassis) chassis.fuel_qty_unit = fuel_qty_unit(chassis.fuel_type) return chassis async def upsert_qa_checklist(db: AsyncSession, chassis_id: uuid.UUID, entries: list[schemas.QaChecklistEntry]): chassis = await db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(status_code=404, detail="Chassis not found") for entry in entries: rows = (await db.execute( select(ChassisQaChecklistResult).where( ChassisQaChecklistResult.chassis_id == chassis_id, ChassisQaChecklistResult.item_id == entry.item_id, ) )).scalars().all() row = rows[0] if rows else None if len(rows) > 1: for extra_row in rows[1:]: await db.delete(extra_row) if row is None: row = ChassisQaChecklistResult(result_id=uuid.uuid4(), chassis_id=chassis_id, item_id=entry.item_id) db.add(row) for field in ( 'yard_ok', 'yard_remark', 'bhagirath_ok', 'bhagirath_remark', 'fvi_ok', 'fvi_remark', 'fvi_rework_ok', 'fvi_rework_remark', 'fvi_rework_pending_ok', 'fvi_rework_pending_remark', ): value = getattr(entry, field) if value is not None: setattr(row, field, value) # Once every checklist item has been ticked OK or given a yard remark, it's ready to hand over. total_items = (await db.execute(select(func.count(YardQaChecklistItemMaster.item_id)).where(YardQaChecklistItemMaster.is_active == True))).scalar_one() await db.flush() filled = (await db.execute( select(func.count(ChassisQaChecklistResult.result_id)).where( ChassisQaChecklistResult.chassis_id == chassis_id, or_( ChassisQaChecklistResult.yard_ok.isnot(None), and_(ChassisQaChecklistResult.yard_remark.isnot(None), ChassisQaChecklistResult.yard_remark != ''), ), ) )).scalar_one() if chassis.current_status == 'YARD_PENDING_CHECK' and total_items > 0 and filled >= total_items: chassis.current_status = 'YARD_CHECKED' await db.commit() async def add_movement_log(db: AsyncSession, chassis_id: uuid.UUID, payload: schemas.MovementLogCreate) -> ChassisMovementLog: chassis = await db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(status_code=404, detail="Chassis not found") now = datetime.now() log_entry = ChassisMovementLog( movement_id=uuid.uuid4(), chassis_id=chassis_id, checkpoint_id=payload.checkpoint_id, responsible_person=payload.responsible_person, movement_date=now.date(), in_time=now.time(), fuel_qty=payload.fuel_qty, remark=payload.remark, signature_name=payload.signature_name, ) db.add(log_entry) await db.flush() for item in payload.checklist_items: db.add(ChassisMovementChecklistResult( result_id=uuid.uuid4(), movement_id=log_entry.movement_id, item_id=item.item_id, is_ok=item.is_ok, value_text=item.value_text, )) await db.commit() await db.refresh(log_entry) return log_entry async def upsert_store_handover(db: AsyncSession, chassis_id: uuid.UUID, payload: schemas.StoreHandoverUpdate) -> ChassisStoreHandover: chassis = await db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(status_code=404, detail="Chassis not found") handover = (await db.execute( select(ChassisStoreHandover).where(ChassisStoreHandover.chassis_id == chassis_id) )).scalar_one_or_none() if handover is None: handover = ChassisStoreHandover(handover_id=uuid.uuid4(), chassis_id=chassis_id) db.add(handover) header_fields = payload.model_dump(exclude={'parts'}) for field, value in header_fields.items(): setattr(handover, field, value) await db.flush() for part in payload.parts: row = (await db.execute( select(ChassisStoreHandoverPart).where( ChassisStoreHandoverPart.handover_id == handover.handover_id, ChassisStoreHandoverPart.part_id == part.part_id, ) )).scalar_one_or_none() if row is None: row = ChassisStoreHandoverPart(result_id=uuid.uuid4(), handover_id=handover.handover_id, part_id=part.part_id) db.add(row) row.status = part.status row.qty = part.qty await db.commit() await db.refresh(handover) handover.parts = (await db.execute( select(ChassisStoreHandoverPart).where(ChassisStoreHandoverPart.handover_id == handover.handover_id) )).scalars().all() return handover async def approve_store_handover(db: AsyncSession, chassis_id: uuid.UUID, role: str, user_id: uuid.UUID) -> dict: handover = (await db.execute(select(ChassisStoreHandover).where(ChassisStoreHandover.chassis_id == chassis_id))).scalar_one_or_none() if not handover: raise HTTPException(status_code=404, detail="Store handover not found for this chassis. Please save the handover details first.") now = datetime.now(timezone.utc) if role == 'YARD_SUPERVISOR': handover.yard_supervisor_approved_by = user_id handover.yard_supervisor_approved_at = now elif role == 'STORE_MGR': handover.store_manager_approved_by = user_id handover.store_manager_approved_at = now elif role in ('ADMIN', 'CLIENT_ADMIN'): handover.yard_supervisor_approved_by = user_id handover.yard_supervisor_approved_at = now handover.store_manager_approved_by = user_id handover.store_manager_approved_at = now else: raise HTTPException(status_code=400, detail="Invalid approval role.") await db.commit() return {"message": "Approved successfully"} async def get_pending_store_handovers(db: AsyncSession, user: LoginUsers) -> dict: role = user.role_code if not role: return {"count": 0, "handovers": []} role = role.upper() stmt = ( select( ChassisStoreHandover.chassis_id, ChassisMaster.chassis_no, ChassisMaster.arrival_date, ChassisMaster.fuel_type, VehicleModelMaster.model_number.label('model_name') ) .join(ChassisMaster, ChassisStoreHandover.chassis_id == ChassisMaster.chassis_id) .outerjoin(VehicleModelMaster, ChassisMaster.model_id == VehicleModelMaster.model_id) ) if role == 'YARD_SUPERVISOR': stmt = stmt.where(ChassisStoreHandover.yard_supervisor_approved_at.is_(None)) elif role in ('STORE_MGR', 'STORE_KEEPER'): stmt = stmt.where(ChassisStoreHandover.store_manager_approved_at.is_(None)) elif role in ('ADMIN', 'CLIENT_ADMIN'): from sqlalchemy import or_ stmt = stmt.where( or_( ChassisStoreHandover.yard_supervisor_approved_at.is_(None), ChassisStoreHandover.store_manager_approved_at.is_(None) ) ) else: return {"count": 0, "handovers": []} result = await db.execute(stmt) rows = [dict(r) for r in result.mappings().all()] return {"count": len(rows), "handovers": rows} async def get_history_store_handovers(db: AsyncSession, user: LoginUsers) -> dict: role = user.role_code if not role: return {"count": 0, "handovers": []} role = role.upper() stmt = ( select( ChassisStoreHandover.chassis_id, ChassisMaster.chassis_no, ChassisMaster.arrival_date, ChassisMaster.fuel_type, VehicleModelMaster.model_number.label('model_name'), ChassisStoreHandover.yard_supervisor_approved_at, ChassisStoreHandover.store_manager_approved_at ) .join(ChassisMaster, ChassisStoreHandover.chassis_id == ChassisMaster.chassis_id) .outerjoin(VehicleModelMaster, ChassisMaster.model_id == VehicleModelMaster.model_id) ) if role == 'YARD_SUPERVISOR': stmt = stmt.where(ChassisStoreHandover.yard_supervisor_approved_by == user.user_id) elif role in ('STORE_MGR', 'STORE_KEEPER'): stmt = stmt.where(ChassisStoreHandover.store_manager_approved_by == user.user_id) elif role in ('ADMIN', 'CLIENT_ADMIN'): from sqlalchemy import or_ stmt = stmt.where( or_( ChassisStoreHandover.yard_supervisor_approved_by == user.user_id, ChassisStoreHandover.store_manager_approved_by == user.user_id ) ) else: return {"count": 0, "handovers": []} stmt = stmt.order_by(ChassisStoreHandover.handover_date.desc().nullslast()) result = await db.execute(stmt) rows = [dict(r) for r in result.mappings().all()] return {"count": len(rows), "handovers": rows} async def handover_to_production(db: AsyncSession, chassis_id: uuid.UUID, user_id: uuid.UUID) -> ChassisMaster: chassis = await db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(status_code=404, detail="Chassis not found") if chassis.current_status == 'HANDED_TO_PRODUCTION': raise HTTPException(status_code=400, detail="Already handed over to production") if chassis.current_status == 'YARD_PENDING_CHECK': raise HTTPException(status_code=400, detail="Yard QA checklist is not fully filled yet") if not chassis.da_lite_photo_url: raise HTTPException( status_code=400, detail="DA-Lite receiving photo is mandatory before handing over to production. Please upload the receiving photo first." ) chassis.current_status = 'HANDED_TO_PRODUCTION' chassis.handed_to_production_at = datetime.now(timezone.utc) chassis.handed_to_production_by = user_id # Stamp the out-time on whichever movement leg is still open, marking when # the chassis actually left the yard for production. open_leg = (await db.execute( select(ChassisMovementLog) .where(ChassisMovementLog.chassis_id == chassis_id, ChassisMovementLog.out_time.is_(None)) .order_by(ChassisMovementLog.created_at.desc()) .limit(1) )).scalar_one_or_none() if open_leg: open_leg.out_time = datetime.now().time() await db.commit() await db.refresh(chassis) chassis.fuel_qty_unit = fuel_qty_unit(chassis.fuel_type) return chassis async def daily_report(db: AsyncSession, report_date: date): stmt = ( select( ChassisMaster.chassis_no, VehicleModelMaster.model_number.label('model_name'), ChassisMaster.fuel_type, ChassisMaster.light_type, ChassisMaster.yard_location, ChassisMaster.fuel_receiving_qty.label('fuel_receiving'), CustomerMaster.name.label('dealer_name'), ChassisMaster.remarks, TransportMaster.name.label('transport_name'), ChassisMaster.da_lite_status, ChassisMaster.voltage, ) .outerjoin(VehicleModelMaster, ChassisMaster.model_id == VehicleModelMaster.model_id) .outerjoin(CustomerMaster, ChassisMaster.dealer_id == CustomerMaster.customer_id) .outerjoin(TransportMaster, ChassisMaster.transport_id == TransportMaster.transport_id) .where(ChassisMaster.arrival_date == report_date) .order_by(ChassisMaster.created_at) ) result = await db.execute(stmt) return [dict(row) for row in result.mappings().all()] async def period_summary(db: AsyncSession, start: date, end: date, group_by: str = 'day'): date_trunc = {'day': 'day', 'week': 'week', 'month': 'month'}.get(group_by, 'day') bucket = func.date_trunc(date_trunc, ChassisMaster.arrival_date).label('period') totals_stmt = ( select( bucket, func.count(ChassisMaster.chassis_id).label('total_inwarded'), func.count().filter(ChassisMaster.da_lite_status == 'OK').label('da_lite_ok'), func.count().filter(ChassisMaster.da_lite_status == 'HOLD').label('da_lite_hold'), func.count().filter(ChassisMaster.current_status == 'HANDED_TO_PRODUCTION').label('handed_to_production'), ) .where(ChassisMaster.arrival_date >= start, ChassisMaster.arrival_date <= end) .group_by(bucket) .order_by(bucket) ) rows = (await db.execute(totals_stmt)).mappings().all() # A chassis "straight passes" FVI if no checklist item on it was flagged for rework. rework_stmt = ( select(ChassisQaChecklistResult.chassis_id) .where(or_( ChassisQaChecklistResult.fvi_rework_ok == False, # noqa: E712 and_(ChassisQaChecklistResult.fvi_rework_remark.isnot(None), ChassisQaChecklistResult.fvi_rework_remark != ''), )) .distinct() ) rework_chassis_ids = {row[0] for row in (await db.execute(rework_stmt)).all()} output = [] for row in rows: d = dict(row) # count how many chassis in this bucket are in the rework set bucket_chassis_stmt = select(ChassisMaster.chassis_id).where( func.date_trunc(date_trunc, ChassisMaster.arrival_date) == d['period'] ) bucket_ids = {r[0] for r in (await db.execute(bucket_chassis_stmt)).all()} rework_count = len(bucket_ids & rework_chassis_ids) total = d['total_inwarded'] or 0 d['rework_count'] = rework_count d['straight_pass_count'] = total - rework_count d['straight_pass_pct'] = round((d['straight_pass_count'] / total) * 100, 2) if total else 0.0 output.append(d) return output async def lookup_chassis(db: AsyncSession, chassis_no: str) -> schemas.ChassisLookupOut: """ Presence + QA summary for a chassis number, used by the Dealer Portal (and Sales) when a chassis is keyed against an order line — deliberately does not surface station-by-station movement, just: do we have it, and is the yard QA clean. """ chassis_no = (chassis_no or '').strip() chassis = (await db.execute( select(ChassisMaster).where(func.lower(ChassisMaster.chassis_no) == chassis_no.lower()) )).scalar_one_or_none() if not chassis: return schemas.ChassisLookupOut(found=False, chassis_no=chassis_no, qa_status='NOT_FOUND') model_number = None if chassis.model_id: model_number = (await db.execute( select(VehicleModelMaster.model_number).where(VehicleModelMaster.model_id == chassis.model_id) )).scalar_one_or_none() qa_rows = (await db.execute( select( YardQaChecklistItemMaster.zone_name, YardQaChecklistItemMaster.checkpoint_text, ChassisQaChecklistResult.yard_ok, ChassisQaChecklistResult.yard_remark, ) .join(ChassisQaChecklistResult, ChassisQaChecklistResult.item_id == YardQaChecklistItemMaster.item_id) .where(ChassisQaChecklistResult.chassis_id == chassis.chassis_id) )).all() if not qa_rows: qa_status = 'PENDING' remarks = [] else: remarks = [ schemas.ChassisQaRemarkOut(zone_name=r.zone_name, checkpoint_text=r.checkpoint_text, remark=r.yard_remark) for r in qa_rows if r.yard_ok is False or (r.yard_remark and r.yard_remark.strip()) ] qa_status = 'REMARKS' if remarks else 'OK' return schemas.ChassisLookupOut( found=True, chassis_no=chassis.chassis_no, model_number=model_number, da_lite_status=chassis.da_lite_status, da_lite_photo_url=chassis.da_lite_photo_url, qa_status=qa_status, tracking_status=chassis.tracking_status, qa_remarks=remarks, ) async def get_tracking_info(db: AsyncSession, chassis_no: str) -> schemas.ChassisTrackingOut: chassis_no = (chassis_no or '').strip() chassis = (await db.execute( select(ChassisMaster).where(func.lower(ChassisMaster.chassis_no) == chassis_no.lower()) )).scalar_one_or_none() if not chassis: return schemas.ChassisTrackingOut(found=False, chassis_no=chassis_no) model_name = None if chassis.model_id: model_name = (await db.execute( select(VehicleModelMaster.model_number).where(VehicleModelMaster.model_id == chassis.model_id) )).scalar_one_or_none() logs_result = await db.execute( select( ChassisStatusLog.log_id, ChassisStatusLog.from_status, ChassisStatusLog.to_status, ChassisStatusLog.station_id, ChassisStatusLog.station_code, StationMaster.station_name.label('station_name'), ChassisStatusLog.scan_mode, ChassisStatusLog.changed_at, ChassisStatusLog.scan_location, ChassisStatusLog.remarks, LoginUsers.name.label('changed_by_name'), ) .outerjoin(LoginUsers, ChassisStatusLog.changed_by == LoginUsers.user_id) .outerjoin(StationMaster, ChassisStatusLog.station_id == StationMaster.station_id) .where(ChassisStatusLog.chassis_id == chassis.chassis_id) .order_by(ChassisStatusLog.changed_at.asc()) ) timeline = [schemas.TrackingLogOut(**dict(row)) for row in logs_result.mappings().all()] current_station_name = None next_station_name = None station_status_label = None station_state = "NOT_STARTED" dwell_minutes = None dwell_formatted = None if chassis.current_station_id: curr_st = await db.get(StationMaster, chassis.current_station_id) if curr_st: current_station_name = curr_st.station_name now_t = datetime.now(timezone.utc) if chassis.station_arrived_at and not chassis.station_completed_at: station_state = "IN" station_status_label = f"{curr_st.station_name} IN" dwell_minutes = max(0, int((now_t - chassis.station_arrived_at).total_seconds() / 60)) dwell_formatted = _fmt_minutes(dwell_minutes) elif chassis.station_completed_at: station_state = "OUT" station_status_label = f"{curr_st.station_name} OUT" arr = chassis.station_arrived_at or chassis.station_completed_at dwell_minutes = max(0, int((chassis.station_completed_at - arr).total_seconds() / 60)) dwell_formatted = _fmt_minutes(dwell_minutes) next_st = (await db.execute( select(StationMaster) .where(StationMaster.is_active == True, StationMaster.sequence_no > (curr_st.sequence_no or 0)) .order_by(StationMaster.sequence_no) .limit(1) )).scalar_one_or_none() if next_st: next_station_name = next_st.station_name else: next_station_name = "Ready for Dispatch" elif chassis.tracking_status == 'READY_FOR_DISPATCH': station_status_label = "Ready for Dispatch" station_state = "READY" elif chassis.tracking_status == 'DISPATCHED': station_status_label = "Dispatched" station_state = "DISPATCHED" else: station_status_label = "Awaiting Station 1" station_state = "PENDING" friendly_status = station_status_label or FRIENDLY_TRACKING_STATUS.get(chassis.tracking_status, chassis.tracking_status) qa_required = False qa_cleared = True qa_status = "NOT_REQUIRED" qa_reason = None if chassis.current_station_id and curr_st and curr_st.requires_qa and station_state == "IN": qa_required = True from app.modules.station_work import flow qa_cleared, qa_reason = await flow.is_qa_cleared_at_station(db, chassis.chassis_id, chassis.current_station_id) qa_status = "PASSED" if qa_cleared else "PENDING" return schemas.ChassisTrackingOut( found=True, chassis_id=chassis.chassis_id, chassis_no=chassis.chassis_no, model_name=model_name, fuel_type=chassis.fuel_type, arrival_date=chassis.arrival_date, da_lite_status=chassis.da_lite_status, tracking_status=chassis.tracking_status, friendly_status=friendly_status, current_station_id=chassis.current_station_id, current_station_name=current_station_name, current_station_status=station_status_label, station_state=station_state, dwell_minutes=dwell_minutes, dwell_formatted=dwell_formatted, next_station_name=next_station_name, station_arrived_at=chassis.station_arrived_at, station_completed_at=chassis.station_completed_at, timeline=timeline, qa_required=qa_required, qa_cleared=qa_cleared, qa_status=qa_status, qa_reason=qa_reason, order_spec=(await resolve_chassis_order_spec(db, chassis)).model_dump() if chassis else None, ) async def supervisor_station_scan( db: AsyncSession, payload: schemas.SupervisorScanInput, user_id: uuid.UUID ) -> schemas.SupervisorScanResponse: chassis_no = (payload.chassis_no or '').strip() if '/track/' in chassis_no: chassis_no = chassis_no.split('/track/')[-1].split('?')[0].split('#')[0] chassis = (await db.execute( select(ChassisMaster).where(func.lower(ChassisMaster.chassis_no) == chassis_no.lower()) )).scalar_one_or_none() if not chassis: raise HTTPException(status_code=404, detail=f"Chassis '{chassis_no}' not found in database.") if chassis.tracking_status == 'DISPATCHED': raise HTTPException(status_code=400, detail=f"Chassis '{chassis.chassis_no}' is already DISPATCHED.") now = datetime.now(timezone.utc) stations = (await db.execute( select(StationMaster).where(StationMaster.is_active == True).order_by(StationMaster.sequence_no) )).scalars().all() if not stations: raise HTTPException(status_code=400, detail="No active stations configured in system.") station_map = {s.station_id: s for s in stations} station_ids = [s.station_id for s in stations] target_station = None if payload.station_id: target_station = station_map.get(payload.station_id) if not target_station: raise HTTPException(status_code=404, detail="Selected station not found or inactive.") else: if chassis.current_station_id and chassis.current_station_id in station_map and chassis.station_completed_at is None: # Currently inside a station -> Auto target is that station (to SCAN OUT) target_station = station_map[chassis.current_station_id] elif chassis.current_station_id and chassis.current_station_id in station_map and chassis.station_completed_at is not None: # Completed previous station -> Auto target is next station (to SCAN IN) cur_idx = station_ids.index(chassis.current_station_id) if cur_idx + 1 < len(stations): target_station = stations[cur_idx + 1] else: raise HTTPException( status_code=400, detail=f"Chassis #{chassis.chassis_no} has already completed final station ({station_map[chassis.current_station_id].station_name}) and is READY FOR DISPATCH." ) else: # Never started any station -> Target is first station target_station = stations[0] model_name = None if chassis.model_id: model_name = (await db.execute( select(VehicleModelMaster.model_number).where(VehicleModelMaster.model_id == chassis.model_id) )).scalar_one_or_none() target_idx = station_ids.index(target_station.station_id) prev_station = stations[target_idx - 1] if target_idx > 0 else None next_station = stations[target_idx + 1] if target_idx + 1 < len(stations) else None # ── VALIDATION: Prevent Double Scan at already completed station ── if chassis.current_station_id == target_station.station_id and chassis.station_completed_at is not None: next_name = next_station.station_name if next_station else "Ready for Dispatch" time_str = format_ist_time(chassis.station_completed_at) raise HTTPException( status_code=400, detail=f"Chassis #{chassis.chassis_no} already completed {target_station.station_name} at {time_str}. Next station is {next_name}." ) is_currently_in_at_target = ( chassis.current_station_id == target_station.station_id and chassis.station_arrived_at is not None and chassis.station_completed_at is None ) open_log = (await db.execute( select(ChassisStationLog).where( ChassisStationLog.chassis_id == chassis.chassis_id, ChassisStationLog.station_id == target_station.station_id, ChassisStationLog.event_type == 'ARRIVAL', ChassisStationLog.status == 'AT_STATION', ) )).scalars().first() is_currently_in_at_target = ( open_log is not None or ( chassis.current_station_id == target_station.station_id and chassis.station_arrived_at is not None and chassis.station_completed_at is None ) ) if not is_currently_in_at_target and open_log is None: # Check 1: Currently inside another station (IN) without completing OUT if chassis.current_station_id and chassis.current_station_id != target_station.station_id and chassis.station_completed_at is None: curr_st = station_map.get(chassis.current_station_id) curr_name = curr_st.station_name if curr_st else "active station" raise HTTPException( status_code=400, detail=f"Chassis #{chassis.chassis_no} is currently inside {curr_name} (IN). Please mark OUT of {curr_name} before scanning into {target_station.station_name}." ) # Check 2: Prevent double scan at already completed station completed_log = (await db.execute( select(ChassisStationLog).where( ChassisStationLog.chassis_id == chassis.chassis_id, ChassisStationLog.station_id == target_station.station_id, ChassisStationLog.event_type == 'COMPLETION', ).order_by(ChassisStationLog.scanned_at.desc()).limit(1) )).scalars().first() is_already_completed = ( completed_log is not None or (chassis.current_station_id == target_station.station_id and chassis.station_completed_at is not None) ) if is_already_completed: comp_dt = ( chassis.station_completed_at if (chassis.current_station_id == target_station.station_id and chassis.station_completed_at) else (completed_log.scanned_at if completed_log else None) ) time_str = f" at {format_ist_time(comp_dt)}" if comp_dt else "" raise HTTPException( status_code=400, detail=f"Chassis #{chassis.chassis_no} already completed {target_station.station_name}{time_str}." ) if is_currently_in_at_target or open_log is not None: # ── SCAN OUT (COMPLETION) ── if target_station.requires_qa: from app.modules.station_work import flow cleared, reason = await flow.is_qa_cleared_at_station(db, chassis.chassis_id, target_station.station_id) if not cleared: raise HTTPException( status_code=400, detail=f"Cannot mark Chassis Out of {target_station.station_name}: QA inspection is pending. 'Chassis Out' will only be enabled after QA approval." ) arrived_at = chassis.station_arrived_at or (open_log.scanned_at if open_log else now) elapsed_minutes = max(0, int((now - arrived_at).total_seconds() / 60)) elapsed_display = _fmt_minutes(elapsed_minutes) if open_log: open_log.status = 'COMPLETED' open_log.elapsed_minutes = elapsed_minutes comp_log = ChassisStationLog( log_id=uuid.uuid4(), chassis_id=chassis.chassis_id, station_id=target_station.station_id, event_type='COMPLETION', scanned_at=now, scanned_by=user_id, scan_mode=payload.scan_mode or 'QR_SCAN', status='COMPLETED', elapsed_minutes=elapsed_minutes, remarks=payload.remarks or f"Station OUT: {elapsed_display}", ) db.add(comp_log) chassis.station_completed_at = now curr_seq_idx = station_ids.index(target_station.station_id) is_final_station = (curr_seq_idx == len(stations) - 1) if is_final_station: chassis.tracking_status = 'READY_FOR_DISPATCH' status_text = 'READY_FOR_DISPATCH' next_station_name = 'Ready for Dispatch' msg = f"Final station '{target_station.station_name}' completed in {elapsed_display}. Status: READY FOR DISPATCH!" else: chassis.tracking_status = 'WIP' next_st = stations[curr_seq_idx + 1] next_station_name = next_st.station_name status_text = f"{target_station.station_name} OUT" msg = f"Completed at {target_station.station_name} in {elapsed_display}. Status: {status_text} (Next: {next_station_name})" status_log = ChassisStatusLog( log_id=uuid.uuid4(), chassis_id=chassis.chassis_id, station_id=target_station.station_id, station_code=target_station.station_code, scan_mode=payload.scan_mode or 'QR_SCAN', from_status=f"{target_station.station_name} IN", to_status=status_text, changed_by=user_id, scan_location=target_station.station_name, remarks=payload.remarks or f"Supervisor Scan OUT ({elapsed_display})", ) db.add(status_log) await db.commit() return schemas.SupervisorScanResponse( action="SCAN_OUT", chassis_id=chassis.chassis_id, chassis_no=chassis.chassis_no, model_name=model_name, station_id=target_station.station_id, station_name=target_station.station_name, status=status_text, elapsed_minutes=elapsed_minutes, elapsed_display=elapsed_display, next_station_name=next_station_name, is_final_station=is_final_station, message=msg, ) else: # ── SCAN IN (ARRIVAL) ── if prev_station and prev_station.requires_qa: from app.modules.station_work import flow cleared, reason = await flow.is_qa_cleared_at_station(db, chassis.chassis_id, prev_station.station_id) if not cleared: raise HTTPException( status_code=400, detail=f"Chassis #{chassis.chassis_no} has not arrived at your station. It is awaiting QA clearance at {prev_station.station_name}." ) previous_open_logs = (await db.execute( select(ChassisStationLog).where( ChassisStationLog.chassis_id == chassis.chassis_id, ChassisStationLog.event_type == 'ARRIVAL', ChassisStationLog.status == 'AT_STATION', ) )).scalars().all() for prev in previous_open_logs: prev.status = 'COMPLETED' prev.elapsed_minutes = max(0, int((now - prev.scanned_at).total_seconds() / 60)) arrival_log = ChassisStationLog( log_id=uuid.uuid4(), chassis_id=chassis.chassis_id, station_id=target_station.station_id, event_type='ARRIVAL', scanned_at=now, scanned_by=user_id, scan_mode=payload.scan_mode or 'QR_SCAN', status='AT_STATION', remarks=payload.remarks or "Supervisor Scan IN", ) db.add(arrival_log) chassis.current_station_id = target_station.station_id chassis.station_arrived_at = now chassis.station_completed_at = None chassis.tracking_status = 'WIP' curr_seq_idx = station_ids.index(target_station.station_id) is_final_station = (curr_seq_idx == len(stations) - 1) next_station_name = stations[curr_seq_idx + 1].station_name if not is_final_station else "Ready for Dispatch" status_log = ChassisStatusLog( log_id=uuid.uuid4(), chassis_id=chassis.chassis_id, station_id=target_station.station_id, station_code=target_station.station_code, scan_mode=payload.scan_mode or 'QR_SCAN', from_status=chassis.tracking_status, to_status=f"{target_station.station_name} IN", changed_by=user_id, scan_location=target_station.station_name, remarks=payload.remarks or "Supervisor Scan IN", ) db.add(status_log) await db.commit() return schemas.SupervisorScanResponse( action="SCAN_IN", chassis_id=chassis.chassis_id, chassis_no=chassis.chassis_no, model_name=model_name, station_id=target_station.station_id, station_name=target_station.station_name, status=f"{target_station.station_name} IN", elapsed_minutes=0, elapsed_display="0m", next_station_name=next_station_name, is_final_station=is_final_station, message=f"Chassis #{chassis.chassis_no} checked IN at {target_station.station_name}. Dwell counter started.", ) async def get_supervisor_stations(db: AsyncSession) -> List[schemas.SupervisorStationSummary]: stations = (await db.execute( select(StationMaster).where(StationMaster.is_active == True).order_by(StationMaster.sequence_no) )).scalars().all() result = [] for st in stations: in_count = (await db.execute( select(func.count(ChassisMaster.chassis_id)).where( ChassisMaster.current_station_id == st.station_id, ChassisMaster.station_arrived_at.isnot(None), ChassisMaster.station_completed_at.is_(None), ChassisMaster.tracking_status == 'WIP', ) )).scalar() or 0 out_count = (await db.execute( select(func.count(ChassisMaster.chassis_id)).where( ChassisMaster.current_station_id == st.station_id, ChassisMaster.station_completed_at.isnot(None), ChassisMaster.tracking_status == 'WIP', ) )).scalar() or 0 result.append(schemas.SupervisorStationSummary( station_id=st.station_id, station_name=st.station_name, station_code=st.station_code, sequence_no=st.sequence_no or 0, in_count=in_count, out_count=out_count, total_count=in_count + out_count, )) return result async def get_supervisor_station_chassis( db: AsyncSession, station_id: uuid.UUID ) -> List[schemas.SupervisorStationChassisItem]: station = await db.get(StationMaster, station_id) if not station: raise HTTPException(status_code=404, detail="Station not found") stations = (await db.execute( select(StationMaster).where(StationMaster.is_active == True).order_by(StationMaster.sequence_no) )).scalars().all() station_ids = [s.station_id for s in stations] curr_idx = station_ids.index(station.station_id) if station.station_id in station_ids else -1 next_st_name = "Ready for Dispatch" if curr_idx != -1 and curr_idx + 1 < len(stations): next_st_name = stations[curr_idx + 1].station_name rows = (await db.execute( select(ChassisMaster, VehicleModelMaster.model_number) .outerjoin(VehicleModelMaster, ChassisMaster.model_id == VehicleModelMaster.model_id) .where( ChassisMaster.current_station_id == station_id, ChassisMaster.tracking_status == 'WIP', ) .order_by(ChassisMaster.station_arrived_at.desc()) )).all() now = datetime.now(timezone.utc) items = [] for cm, model_name in rows: arr_time = cm.station_arrived_at if not arr_time: log_arr = (await db.execute( select(ChassisStationLog.scanned_at).where( ChassisStationLog.chassis_id == cm.chassis_id, ChassisStationLog.station_id == station_id, ChassisStationLog.event_type == 'ARRIVAL', ).order_by(ChassisStationLog.scanned_at.desc()).limit(1) )).scalar_one_or_none() arr_time = log_arr or cm.created_at or now comp_time = cm.station_completed_at if not comp_time: log_comp = (await db.execute( select(ChassisStationLog.scanned_at, ChassisStationLog.elapsed_minutes).where( ChassisStationLog.chassis_id == cm.chassis_id, ChassisStationLog.station_id == station_id, ChassisStationLog.event_type == 'COMPLETION', ).order_by(ChassisStationLog.scanned_at.desc()).limit(1) )).first() if log_comp: comp_time = log_comp.scanned_at if not comp_time: state = "IN" st_status = f"{station.station_name} IN" elapsed = max(0, int((now - arr_time).total_seconds() / 60)) else: state = "OUT" st_status = f"{station.station_name} OUT" elapsed = max(0, int((comp_time - arr_time).total_seconds() / 60)) qa_req = bool(station.requires_qa) qa_clr = True qa_stat = "NOT_REQUIRED" qa_rsn = None if qa_req and state == "IN": from app.modules.station_work import flow qa_clr, qa_rsn = await flow.is_qa_cleared_at_station(db, cm.chassis_id, station_id) qa_stat = "PASSED" if qa_clr else "PENDING" items.append(schemas.SupervisorStationChassisItem( chassis_id=cm.chassis_id, chassis_no=cm.chassis_no, model_name=model_name, fuel_type=cm.fuel_type, station_state=state, station_status=st_status, arrived_at=arr_time, completed_at=comp_time, elapsed_minutes=elapsed, elapsed_display=_fmt_minutes(elapsed), da_lite_status=cm.da_lite_status, next_station_name=next_st_name, server_time=now, qa_required=qa_req, qa_cleared=qa_clr, qa_status=qa_stat, qa_reason=qa_rsn, )) return items async def update_tracking_status( db: AsyncSession, chassis_no: str, payload: schemas.TrackingScanInput, user_id: uuid.UUID ) -> schemas.ChassisTrackingOut: chassis_no = (chassis_no or '').strip() chassis = (await db.execute( select(ChassisMaster).where(func.lower(ChassisMaster.chassis_no) == chassis_no.lower()) )).scalar_one_or_none() if not chassis: raise HTTPException(status_code=404, detail=f"Chassis '{chassis_no}' not found") new_status = (payload.status or '').upper() if new_status not in ('WIP', 'DISPATCHED'): raise HTTPException(status_code=400, detail="Invalid target status. Must be WIP or DISPATCHED") current_status = chassis.tracking_status if current_status == new_status: raise HTTPException(status_code=400, detail=f"Chassis is already marked as {FRIENDLY_TRACKING_STATUS.get(new_status, new_status)}") # State machine check if new_status == 'WIP': if current_status != 'CHASSIS_IN': raise HTTPException(status_code=400, detail=f"Cannot transition to WIP from '{current_status}'. Expected CHASSIS_IN.") if new_status == 'DISPATCHED' and current_status != 'WIP': raise HTTPException(status_code=400, detail=f"Cannot transition to DISPATCHED from '{current_status}'. Must be in WIP status first.") old_status = chassis.tracking_status chassis.tracking_status = new_status if new_status == 'DISPATCHED': chassis.dispatched_at = datetime.now(timezone.utc) if payload.dispatch_photo_url: chassis.dispatch_photo_url = payload.dispatch_photo_url status_log = ChassisStatusLog( log_id=uuid.uuid4(), chassis_id=chassis.chassis_id, from_status=old_status, to_status=new_status, changed_by=user_id, scan_location=payload.location or ('PRODUCTION' if new_status == 'WIP' else 'DISPATCH'), remarks=payload.remarks or f"Scanned at {payload.location or new_status}", ) db.add(status_log) await db.commit() return await get_tracking_info(db, chassis_no) async def pod_report(db: AsyncSession, start: date | None = None, end: date | None = None, month: int | None = None, year: int | None = None): if not end: end = date.today() if not start: start = date(end.year, 1, 1) # Chassis with completed/ok rework rework_stmt = ( select(ChassisQaChecklistResult.chassis_id) .where(or_( ChassisQaChecklistResult.fvi_rework_ok == True, # noqa: E712 and_(ChassisQaChecklistResult.fvi_rework_remark.isnot(None), ChassisQaChecklistResult.fvi_rework_remark != ''), )) .distinct() ) rework_chassis_ids = {row[0] for row in (await db.execute(rework_stmt)).all()} # 1. Monthly Summary month_bucket = func.date_trunc('month', ChassisMaster.arrival_date).label('period') monthly_stmt = ( select( month_bucket, func.count(ChassisMaster.chassis_id).label('total_checked_cowl'), func.count().filter(ChassisMaster.da_lite_status == 'HOLD').label('da_lite_hold_count'), ) .where(ChassisMaster.arrival_date >= start, ChassisMaster.arrival_date <= end) .group_by(month_bucket) .order_by(month_bucket) ) monthly_rows = (await db.execute(monthly_stmt)).mappings().all() monthly_summary = [] for r in monthly_rows: period_dt = r['period'] month_label = period_dt.strftime('%b-%y') if hasattr(period_dt, 'strftime') else str(period_dt)[:7] total_checked = r['total_checked_cowl'] or 0 chassis_in_month_stmt = select(ChassisMaster.chassis_id, ChassisMaster.da_lite_status).where( func.date_trunc('month', ChassisMaster.arrival_date) == period_dt ) chassis_month_res = (await db.execute(chassis_in_month_stmt)).all() month_chassis_ids = {c[0] for c in chassis_month_res} hold_chassis_ids = {c[0] for c in chassis_month_res if c[1] == 'HOLD'} stopped_cowl = len(hold_chassis_ids) if hold_chassis_ids else (r['da_lite_hold_count'] or 0) rework_status = len(month_chassis_ids & rework_chassis_ids) if rework_status > stopped_cowl: rework_status = stopped_cowl total_hold_pending = max(0, stopped_cowl - rework_status) loss_pct = round((stopped_cowl / total_checked) * 100, 2) if total_checked > 0 else 0.0 straight_pass_pct = round(100.0 - loss_pct, 2) if total_checked > 0 else 100.0 monthly_summary.append({ 'period': str(period_dt)[:10], 'month_label': month_label, 'total_checked_cowl': total_checked, 'loss_pct': loss_pct, 'straight_pass_pct': straight_pass_pct, 'stopped_cowl': stopped_cowl, 'rework_status': rework_status, 'total_hold_pending': total_hold_pending, }) # 2. Dealer / Location Breakdown dealer_stmt = ( select( CustomerMaster.name.label('dealer_name'), ChassisMaster.arrival_date, func.count(ChassisMaster.chassis_id).label('total_checked_cowl'), func.count().filter(ChassisMaster.da_lite_status == 'HOLD').label('stopped_cowl'), ) .outerjoin(CustomerMaster, ChassisMaster.dealer_id == CustomerMaster.customer_id) .where(ChassisMaster.arrival_date >= start, ChassisMaster.arrival_date <= end) .group_by(CustomerMaster.name, ChassisMaster.arrival_date) .order_by(CustomerMaster.name, ChassisMaster.arrival_date.desc()) ) dealer_rows = (await db.execute(dealer_stmt)).mappings().all() dealer_groups = {} for dr in dealer_rows: dname = dr['dealer_name'] or 'Spare stock' if dname not in dealer_groups: dealer_groups[dname] = [] tot = dr['total_checked_cowl'] or 0 stopped = dr['stopped_cowl'] or 0 dealer_date_chassis_stmt = select(ChassisMaster.chassis_id).outerjoin(CustomerMaster, ChassisMaster.dealer_id == CustomerMaster.customer_id).where( (CustomerMaster.name == dr['dealer_name']) if dr['dealer_name'] else CustomerMaster.name.is_(None), ChassisMaster.arrival_date == dr['arrival_date'] ) dealer_date_ids = {c[0] for c in (await db.execute(dealer_date_chassis_stmt)).all()} rework_status = len(dealer_date_ids & rework_chassis_ids) if rework_status > stopped: rework_status = stopped hold_pending = max(0, stopped - rework_status) loss_pct = round((stopped / tot) * 100, 2) if tot > 0 else 0.0 straight_pass_pct = round(100.0 - loss_pct, 2) if tot > 0 else 100.0 dealer_groups[dname].append({ 'date': str(dr['arrival_date']), 'total_checked_cowl': tot, 'loss_pct': loss_pct, 'straight_pass_pct': straight_pass_pct, 'stopped_cowl': stopped, 'rework_status': rework_status, 'total_hold_pending': hold_pending, }) dealer_breakdown = [] for dname, rows_list in dealer_groups.items(): tot_checked = sum(r['total_checked_cowl'] for r in rows_list) tot_stopped = sum(r['stopped_cowl'] for r in rows_list) tot_rework = sum(r['rework_status'] for r in rows_list) tot_hold_pending = sum(r['total_hold_pending'] for r in rows_list) overall_loss_pct = round((tot_stopped / tot_checked) * 100, 2) if tot_checked > 0 else 0.0 overall_straight_pass_pct = round(100.0 - overall_loss_pct, 2) if tot_checked > 0 else 100.0 dealer_breakdown.append({ 'dealer_name': dname, 'summary': { 'total_checked_cowl': tot_checked, 'loss_pct': overall_loss_pct, 'straight_pass_pct': overall_straight_pass_pct, 'stopped_cowl': tot_stopped, 'rework_status': tot_rework, 'total_hold_pending': tot_hold_pending, }, 'rows': rows_list, }) return { 'monthly_summary': monthly_summary, 'dealer_breakdown': dealer_breakdown, 'start_date': str(start), 'end_date': str(end), } async def create_qa_item(db: AsyncSession, payload: schemas.QaItemCreate) -> YardQaChecklistItemMaster: seq = payload.sequence_no or 0 if seq == 0: max_seq = (await db.execute(select(func.max(YardQaChecklistItemMaster.sequence_no)))).scalar_one_or_none() seq = (max_seq or 0) + 1 item = YardQaChecklistItemMaster( item_id=uuid.uuid4(), zone_name=payload.zone_name.strip(), checkpoint_text=payload.checkpoint_text.strip(), sequence_no=seq, is_active=True, ) db.add(item) await db.commit() return item async def update_qa_item(db: AsyncSession, item_id: uuid.UUID, payload: schemas.QaItemUpdate) -> YardQaChecklistItemMaster: from fastapi import HTTPException item = await db.get(YardQaChecklistItemMaster, item_id) if not item or not item.is_active: raise HTTPException(status_code=404, detail="QA item not found") if payload.zone_name is not None: item.zone_name = payload.zone_name.strip() if payload.checkpoint_text is not None: item.checkpoint_text = payload.checkpoint_text.strip() if payload.sequence_no is not None: item.sequence_no = payload.sequence_no await db.commit() return item async def delete_qa_item(db: AsyncSession, item_id: uuid.UUID): from fastapi import HTTPException item = await db.get(YardQaChecklistItemMaster, item_id) if not item or not item.is_active: raise HTTPException(status_code=404, detail="QA item not found") item.is_active = False await db.commit() return {"message": "QA item deleted"} async def create_handover_part(db: AsyncSession, payload: schemas.HandoverPartCreate) -> StoreHandoverPartMaster: max_sr = (await db.execute(select(func.max(StoreHandoverPartMaster.sr_no)))).scalar_one_or_none() sr = (max_sr or 0) + 1 part = StoreHandoverPartMaster( part_id=uuid.uuid4(), sr_no=sr, part_name=payload.part_name.strip(), fuel_applicability=(payload.fuel_applicability or 'ALL').upper(), is_active=True, ) db.add(part) await db.commit() return part # ── DOCUMENTS ───────────────────────────────────────── async def upload_document( db: AsyncSession, chassis_id: uuid.UUID, document_type: str, document_number, issue_date, valid_until, issuing_authority, file, user_id, ): import os, shutil, time as _time from app.models.yard_models import ChassisArrivalDocument chassis = await db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(status_code=404, detail='Chassis not found') file_path = None if file and file.filename: ext = os.path.splitext(file.filename)[1] or '.png' if ext.lower() == '.pdf': ext = '.png' # Force to png per user requirement if needed, or just change fallback safe_chassis_no = str(chassis.chassis_no).strip().replace(" ", "_").replace("/", "_") safe_doc_type = document_type.strip().replace(" ", "_").replace("/", "_") from datetime import datetime now = datetime.now() year_str, month_str = now.strftime("%Y"), now.strftime("%m") # Directory structure: uploads/chassis_docs/{document_type}/{year}/{month}/ dir_path = os.path.join("uploads", "chassis_docs", safe_doc_type, year_str, month_str) os.makedirs(dir_path, exist_ok=True) fname = f"{safe_chassis_no}_{int(_time.time())}{ext}" save_path = os.path.join(dir_path, fname) with open(save_path, "wb") as buf: shutil.copyfileobj(file.file, buf) # Web path uses forward slashes file_path = f"/uploads/chassis_docs/{safe_doc_type}/{year_str}/{month_str}/{fname}" doc = ChassisArrivalDocument( doc_id=uuid.uuid4(), chassis_id=chassis_id, document_type=document_type, document_number=document_number, issue_date=issue_date if issue_date else None, valid_until=valid_until if valid_until else None, issuing_authority=issuing_authority, file_path=file_path, verification_status='RECEIVED', ) db.add(doc) await db.flush() return doc async def delete_document(db: AsyncSession, doc_id: uuid.UUID): from app.models.yard_models import ChassisArrivalDocument doc = await db.get(ChassisArrivalDocument, doc_id) if not doc: raise HTTPException(status_code=404, detail='Document not found') file_url = doc.file_path await db.delete(doc) await db.commit() remove_upload_file(file_url) return {"message": "Document deleted", "doc_id": str(doc_id)} async def verify_document(db: AsyncSession, doc_id: uuid.UUID, payload, user_id): from app.models.yard_models import ChassisArrivalDocument doc = await db.get(ChassisArrivalDocument, doc_id) if not doc: raise HTTPException(status_code=404, detail='Document not found') if payload.verification_status not in ('VERIFIED', 'REJECTED'): raise HTTPException(status_code=400, detail='Status must be VERIFIED or REJECTED') if payload.verification_status == 'REJECTED' and not payload.rejection_reason: raise HTTPException(status_code=400, detail='Rejection reason is required') doc.verification_status = payload.verification_status doc.rejection_reason = payload.rejection_reason doc.verified_by = user_id doc.verified_at = datetime.now(timezone.utc) await db.flush() return doc # ── CUSTOMER-SUPPLIED PARTS ─────────────────────────── async def create_csp(db: AsyncSession, chassis_id: uuid.UUID, payload, user_id): from app.models.yard_models import CustomerSuppliedPart chassis = await db.get(ChassisMaster, chassis_id) if not chassis: raise HTTPException(status_code=404, detail='Chassis not found') csp = CustomerSuppliedPart( csp_id=uuid.uuid4(), chassis_id=chassis_id, rm_id=payload.rm_id, item_description=payload.item_description, item_code=payload.item_code, quantity_received=payload.quantity_received, unit_of_measurement=payload.unit_of_measurement, target_station=payload.target_station, storage_location=payload.storage_location, status='RECEIVED', received_by=user_id, received_at=datetime.now(timezone.utc), notes=payload.notes, ) db.add(csp) await db.flush() return csp async def update_csp(db: AsyncSession, csp_id: uuid.UUID, payload, user_id): from app.models.yard_models import CustomerSuppliedPart csp = await db.get(CustomerSuppliedPart, csp_id) if not csp: raise HTTPException(status_code=404, detail='Customer-supplied part not found') valid_statuses = ('VERIFIED', 'STORED', 'ISSUED', 'RETURNED', 'DAMAGED') if payload.status not in valid_statuses: raise HTTPException(status_code=400, detail=f'Status must be one of: {valid_statuses}') csp.status = payload.status if payload.quantity_verified is not None: csp.quantity_verified = payload.quantity_verified if payload.storage_location is not None: csp.storage_location = payload.storage_location if payload.issued_to_station is not None: csp.issued_to_station = payload.issued_to_station if payload.damaged_qty is not None: csp.damaged_qty = payload.damaged_qty if payload.damage_description is not None: csp.damage_description = payload.damage_description if payload.notes is not None: csp.notes = payload.notes if payload.status == 'ISSUED': csp.issued_by = user_id csp.issued_at = datetime.now(timezone.utc) await db.flush() return csp # ── FACTORY LOCATION & STATION MANAGEMENT SERVICES ───── async def get_locations(db: AsyncSession): from app.models.location_models import ChassisLocationMaster, StationQaChecklistItemMaster from app.models.rm_models import StationMaster locations = (await db.execute( select(ChassisLocationMaster).order_by(ChassisLocationMaster.sequence_no) )).scalars().all() result = [] for loc in locations: stations = (await db.execute( select(StationMaster) .where(StationMaster.location_id == loc.location_id) .order_by(StationMaster.sequence_no) )).scalars().all() stn_list = [] for stn in stations: qa_count = (await db.execute( select(func.count()) .select_from(StationQaChecklistItemMaster) .where(StationQaChecklistItemMaster.station_id == stn.station_id) )).scalar_one() or 0 stn_list.append(schemas.StationDetailOut( station_id=stn.station_id, location_id=stn.location_id, location_name=loc.location_name, station_code=stn.station_code, station_name=stn.station_name, sequence_no=stn.sequence_no or 0, requires_qa=stn.requires_qa, is_rework_station=stn.is_rework_station, is_active=stn.is_active, qa_items_count=qa_count )) result.append(schemas.LocationOut( location_id=loc.location_id, location_code=loc.location_code, location_name=loc.location_name, sequence_no=loc.sequence_no or 0, description=loc.description, is_active=loc.is_active, created_at=loc.created_at, stations=stn_list )) return result async def create_location(db: AsyncSession, payload: schemas.LocationCreate): from app.models.location_models import ChassisLocationMaster existing = (await db.execute( select(ChassisLocationMaster).where(ChassisLocationMaster.location_code == payload.location_code.upper().strip()) )).scalar_one_or_none() if existing: raise HTTPException(status_code=400, detail=f"Location code '{payload.location_code}' already exists") location = ChassisLocationMaster( location_id=uuid.uuid4(), location_code=payload.location_code.upper().strip(), location_name=payload.location_name.strip(), sequence_no=payload.sequence_no or 0, description=payload.description, is_active=payload.is_active if payload.is_active is not None else True ) db.add(location) await db.flush() return schemas.LocationOut( location_id=location.location_id, location_code=location.location_code, location_name=location.location_name, sequence_no=location.sequence_no, description=location.description, is_active=location.is_active, created_at=location.created_at, stations=[] ) async def update_location(db: AsyncSession, location_id: uuid.UUID, payload: schemas.LocationUpdate): from app.models.location_models import ChassisLocationMaster location = await db.get(ChassisLocationMaster, location_id) if not location: raise HTTPException(status_code=404, detail="Location not found") if payload.location_name is not None: location.location_name = payload.location_name.strip() if payload.sequence_no is not None: location.sequence_no = payload.sequence_no if payload.description is not None: location.description = payload.description if payload.is_active is not None: location.is_active = payload.is_active await db.flush() return schemas.LocationOut( location_id=location.location_id, location_code=location.location_code, location_name=location.location_name, sequence_no=location.sequence_no, description=location.description, is_active=location.is_active, created_at=location.created_at, stations=[] ) async def get_all_stations(db: AsyncSession): from app.models.rm_models import StationMaster from app.models.location_models import StationQaChecklistItemMaster stations = (await db.execute( select(StationMaster).order_by(StationMaster.sequence_no.asc().nulls_last(), StationMaster.station_name.asc()) )).scalars().all() stn_list = [] for stn in stations: qa_count = (await db.execute( select(func.count()) .select_from(StationQaChecklistItemMaster) .where(StationQaChecklistItemMaster.station_id == stn.station_id) )).scalar_one() or 0 stn_list.append(schemas.StationDetailOut( station_id=stn.station_id, location_id=stn.location_id, location_name=None, station_code=stn.station_code, station_name=stn.station_name, sequence_no=stn.sequence_no or 0, requires_qa=stn.requires_qa, is_rework_station=stn.is_rework_station, is_active=stn.is_active, standard_cycle_time_min=stn.standard_cycle_time_min, qa_items_count=qa_count )) return stn_list async def create_station(db: AsyncSession, payload: schemas.StationCreate): import re from app.models.location_models import ChassisLocationMaster from app.models.rm_models import StationMaster location_name = None if payload.location_id: location = await db.get(ChassisLocationMaster, payload.location_id) if location: location_name = location.location_name station_name = payload.station_name.strip() # Auto-generate clean unique station_code if not supplied if payload.station_code and payload.station_code.strip(): base_code = payload.station_code.upper().strip() else: clean_name = re.sub(r'[^A-Z0-9]+', '_', station_name.upper()).strip('_') if not clean_name: clean_name = f"STN_{uuid.uuid4().hex[:6].upper()}" base_code = clean_name[:40] candidate = base_code suffix = 1 while (await db.execute( select(StationMaster.station_id).where(StationMaster.station_code == candidate) )).scalar_one_or_none(): candidate = f"{base_code[:35]}_{suffix}" suffix += 1 station_code = candidate # Sequence reordering for insertion all_stations = (await db.execute( select(StationMaster).order_by(StationMaster.sequence_no.asc().nulls_last(), StationMaster.station_name.asc()) )).scalars().all() target_seq = payload.sequence_no if (payload.sequence_no and payload.sequence_no > 0) else (len(all_stations) + 1) target_idx = max(0, min(target_seq - 1, len(all_stations))) cycle_min = payload.standard_cycle_time_min if (payload.standard_cycle_time_min and payload.standard_cycle_time_min > 0) else None station = StationMaster( station_id=uuid.uuid4(), location_id=payload.location_id, station_code=station_code, station_name=station_name, sequence_no=target_idx + 1, requires_qa=payload.requires_qa if payload.requires_qa is not None else True, is_rework_station=payload.is_rework_station if payload.is_rework_station is not None else True, is_active=payload.is_active if payload.is_active is not None else True, standard_cycle_time_min=cycle_min, ) db.add(station) # Shift subsequent stations and re-index 1..N all_stations.insert(target_idx, station) for i, s in enumerate(all_stations, start=1): s.sequence_no = i await db.commit() await db.refresh(station) return schemas.StationDetailOut( station_id=station.station_id, location_id=station.location_id, location_name=location_name, station_code=station.station_code, station_name=station.station_name, sequence_no=station.sequence_no, requires_qa=station.requires_qa, is_rework_station=station.is_rework_station, is_active=station.is_active, standard_cycle_time_min=station.standard_cycle_time_min, qa_items_count=0 ) async def update_station(db: AsyncSession, station_id: uuid.UUID, payload: schemas.StationUpdate): from app.models.rm_models import StationMaster from app.models.location_models import ChassisLocationMaster station = await db.get(StationMaster, station_id) if not station: raise HTTPException(status_code=404, detail="Station not found") if payload.location_id is not None: station.location_id = payload.location_id if payload.station_name is not None: station.station_name = payload.station_name.strip() if payload.requires_qa is not None: station.requires_qa = payload.requires_qa if payload.is_rework_station is not None: station.is_rework_station = payload.is_rework_station if payload.is_active is not None: station.is_active = payload.is_active if "standard_cycle_time_min" in payload.model_fields_set: val = payload.standard_cycle_time_min station.standard_cycle_time_min = val if (val is not None and val > 0) else None # Rearrange sequences cleanly if sequence_no changed if payload.sequence_no is not None and payload.sequence_no != station.sequence_no: all_stations = (await db.execute( select(StationMaster).order_by(StationMaster.sequence_no.asc().nulls_last(), StationMaster.station_name.asc()) )).scalars().all() others = [s for s in all_stations if s.station_id != station_id] new_target = max(1, min(payload.sequence_no, len(others) + 1)) target_idx = new_target - 1 others.insert(target_idx, station) for i, s in enumerate(others, start=1): s.sequence_no = i await db.commit() await db.refresh(station) loc_name = None if station.location_id: loc = await db.get(ChassisLocationMaster, station.location_id) if loc: loc_name = loc.location_name return schemas.StationDetailOut( station_id=station.station_id, location_id=station.location_id, location_name=loc_name, station_code=station.station_code, station_name=station.station_name, sequence_no=station.sequence_no, requires_qa=station.requires_qa, is_rework_station=station.is_rework_station, is_active=station.is_active, standard_cycle_time_min=station.standard_cycle_time_min, qa_items_count=0 ) async def delete_station(db: AsyncSession, station_id: uuid.UUID): from app.models.rm_models import StationMaster, RmMaster from app.models.location_models import StationQaChecklistItemMaster, ChassisStationQaResult from app.models.qa_models import ( QaStationTemplateMap, QaChecksheetTemplate, QaChecksheetInstance, QaCheckpointResponse, QaChecksheetSignoff, QaShortageLog, ) from app.models.production_models import ChassisStationLog, ProductionHold from app.models.yard_models import ChassisMaster from sqlalchemy import delete, update, select station = await db.get(StationMaster, station_id) if not station: raise HTTPException(status_code=404, detail="Station not found") # 1. Clear station references on nullable foreign key tables await db.execute( update(QaChecksheetTemplate) .where(QaChecksheetTemplate.station_id == station_id) .values(station_id=None) ) await db.execute( update(RmMaster) .where(RmMaster.station_id == station_id) .values(station_id=None) ) await db.execute( update(ChassisMaster) .where(ChassisMaster.current_station_id == station_id) .values(current_station_id=None) ) await db.execute( update(ProductionHold) .where(ProductionHold.station_id == station_id) .values(station_id=None) ) # 2. Delete configuration mappings await db.execute( delete(QaStationTemplateMap).where(QaStationTemplateMap.station_id == station_id) ) await db.execute( delete(StationQaChecklistItemMaster).where(StationQaChecklistItemMaster.station_id == station_id) ) # 3. Clean up any related operational logs for this station await db.execute( delete(ChassisStationQaResult).where(ChassisStationQaResult.station_id == station_id) ) await db.execute( delete(QaShortageLog).where(QaShortageLog.station_id == station_id) ) await db.execute( delete(ChassisStationLog).where(ChassisStationLog.station_id == station_id) ) # Clean up QA checksheet instances for this station instance_ids = (await db.execute( select(QaChecksheetInstance.instance_id).where(QaChecksheetInstance.station_id == station_id) )).scalars().all() if instance_ids: await db.execute(delete(QaChecksheetSignoff).where(QaChecksheetSignoff.instance_id.in_(instance_ids))) await db.execute(delete(QaCheckpointResponse).where(QaCheckpointResponse.instance_id.in_(instance_ids))) await db.execute(delete(QaChecksheetInstance).where(QaChecksheetInstance.instance_id.in_(instance_ids))) # 4. Permanently delete station await db.delete(station) await db.flush() # Re-normalize remaining stations sequentially remaining = (await db.execute( select(StationMaster).order_by(StationMaster.sequence_no.asc().nulls_last(), StationMaster.station_name.asc()) )).scalars().all() for i, s in enumerate(remaining, start=1): s.sequence_no = i await db.commit() return {"message": "Station deleted successfully", "station_id": str(station_id)} async def get_station_qa_items(db: AsyncSession, station_id: uuid.UUID): from app.models.location_models import StationQaChecklistItemMaster items = (await db.execute( select(StationQaChecklistItemMaster) .where(StationQaChecklistItemMaster.station_id == station_id) .order_by(StationQaChecklistItemMaster.sequence_no) )).scalars().all() return items async def create_station_qa_item(db: AsyncSession, payload: schemas.StationQaItemCreate): from app.models.location_models import StationQaChecklistItemMaster item = StationQaChecklistItemMaster( item_id=uuid.uuid4(), station_id=payload.station_id, zone_name=(payload.zone_name or "General").strip(), checkpoint_text=payload.checkpoint_text.strip(), specification=payload.specification, sequence_no=payload.sequence_no or 0, is_active=payload.is_active if payload.is_active is not None else True ) db.add(item) await db.flush() return item