# app/modules/rm_master/routes.py from fastapi import APIRouter, Depends, HTTPException from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy import select from app.core.database import get_db from app.core.security import require_permission from app.models.rm_models import RmMaster, MaterialTypeMaster, ProcurementSourceMaster from .schemas import RmMasterCreate, RmMasterUpdate, RmMasterResponse from uuid import UUID from typing import List, Optional router = APIRouter() @router.get('/material-types') async def list_material_types(db: AsyncSession = Depends(get_db), current_user=Depends(require_permission('RM_MASTER', 'view'))): result = await db.execute(select(MaterialTypeMaster).where(MaterialTypeMaster.is_active == True)) return list(result.scalars().all()) @router.get('/procurement-sources') async def list_procurement_sources(db: AsyncSession = Depends(get_db), current_user=Depends(require_permission('RM_MASTER', 'view'))): result = await db.execute(select(ProcurementSourceMaster).where(ProcurementSourceMaster.is_active == True)) return list(result.scalars().all()) @router.get('/', response_model=List[RmMasterResponse]) async def list_rm( skip: int = 0, limit: Optional[int] = None, is_active: Optional[str] = None, vendor_id: Optional[UUID] = None, db: AsyncSession = Depends(get_db), _=Depends(require_permission('RM_MASTER', 'view')) ): from app.models.rm_models import RmVendorMapping, RmPoDetail, RmPurchaseOrder from sqlalchemy import func po_subq = ( select(RmPoDetail.unit_price) .join(RmPurchaseOrder, RmPoDetail.po_id == RmPurchaseOrder.po_id) .where(RmPoDetail.rm_id == RmMaster.rm_id) .order_by(RmPurchaseOrder.order_date.desc()) .limit(1) .correlate(RmMaster) .scalar_subquery() ) vendor_cost_subq = ( select(RmVendorMapping.standard_cost) .where(RmVendorMapping.rm_id == RmMaster.rm_id) .order_by(RmVendorMapping.created_at.desc()) .limit(1) .correlate(RmMaster) .scalar_subquery() ) price_col = func.coalesce(po_subq, vendor_cost_subq, 0.0).label('standard_cost') stmt = select(RmMaster, price_col) if is_active is not None and is_active.lower() not in ('null', 'none', ''): active_val = is_active.lower() in ('true', '1', 'yes') stmt = stmt.where(RmMaster.is_active == active_val) if vendor_id is not None: stmt = stmt.join(RmVendorMapping, RmMaster.rm_id == RmVendorMapping.rm_id).where(RmVendorMapping.vendor_id == vendor_id) if skip > 0: stmt = stmt.offset(skip) if limit is not None: stmt = stmt.limit(limit) result = await db.execute(stmt) rows = result.all() # Extract RM IDs to fetch vendors rm_ids = [rm.rm_id for rm, _ in rows] vendors_by_rm = {} if rm_ids: from app.models.rm_models import VendorMaster v_stmt = select(RmVendorMapping.rm_id, VendorMaster.vendor_id, VendorMaster.name)\ .join(VendorMaster, RmVendorMapping.vendor_id == VendorMaster.vendor_id)\ .where(RmVendorMapping.rm_id.in_(rm_ids)) v_res = await db.execute(v_stmt) for rmid, vid, vname in v_res: if rmid not in vendors_by_rm: vendors_by_rm[rmid] = [] vendors_by_rm[rmid].append({"vendor_id": str(vid), "name": vname}) # Fetch inventory for RMs inventory_by_rm = {} if rm_ids: from app.models.rm_models import RmInventory inv_stmt = select(RmInventory).where(RmInventory.rm_id.in_(rm_ids)) inv_res = await db.execute(inv_stmt) for inv in inv_res.scalars().all(): t_qty = float(inv.current_qty) if inv.current_qty else 0.0 r_qty = float(inv.reserved_qty) if inv.reserved_qty else 0.0 inventory_by_rm[inv.rm_id] = { "total_qty": t_qty, "reserved_qty": r_qty, "available_qty": max(0.0, t_qty - r_qty) } items = [] for rm, cost in rows: rm_dict = {c.name: getattr(rm, c.name) for c in rm.__table__.columns} rm_dict['standard_cost'] = float(cost) if cost is not None else 0.0 rm_dict['vendors'] = vendors_by_rm.get(rm.rm_id, []) inv = inventory_by_rm.get(rm.rm_id, {"total_qty": 0.0, "reserved_qty": 0.0, "available_qty": 0.0}) rm_dict['total_qty'] = inv['total_qty'] rm_dict['reserved_qty'] = inv['reserved_qty'] rm_dict['available_qty'] = inv['available_qty'] items.append(rm_dict) return items @router.post('/', response_model=RmMasterResponse, status_code=201) async def create_rm( data: RmMasterCreate, db: AsyncSession = Depends(get_db), _=Depends(require_permission('RM_MASTER', 'create')) ): rm = RmMaster(**data.model_dump(exclude_none=True)) db.add(rm) await db.flush() await db.refresh(rm) return await get_rm(rm.rm_id, db) @router.get('/{rm_id}', response_model=RmMasterResponse) async def get_rm( rm_id: UUID, db: AsyncSession = Depends(get_db), _=Depends(require_permission('RM_MASTER', 'view')) ): rm = await db.get(RmMaster, rm_id) if not rm: raise HTTPException(404, 'RM not found') rm_dict = {c.name: getattr(rm, c.name) for c in rm.__table__.columns} # Fetch Vendors from app.models.rm_models import RmVendorMapping, VendorMaster, RmInventory v_stmt = select(VendorMaster.vendor_id, VendorMaster.name, RmVendorMapping.standard_cost)\ .join(RmVendorMapping, RmVendorMapping.vendor_id == VendorMaster.vendor_id)\ .where(RmVendorMapping.rm_id == rm_id) v_res = await db.execute(v_stmt) rm_dict['vendors'] = [{"vendor_id": str(r[0]), "vendor_name": r[1], "standard_cost": float(r[2]) if r[2] else 0.0} for r in v_res.all()] # Fetch Inventory inv_stmt = select(RmInventory).where(RmInventory.rm_id == rm_id) inv_res = await db.execute(inv_stmt) inv = inv_res.scalar_one_or_none() t_qty = float(inv.current_qty) if inv and inv.current_qty else 0.0 r_qty = float(inv.reserved_qty) if inv and inv.reserved_qty else 0.0 rm_dict['total_qty'] = t_qty rm_dict['reserved_qty'] = r_qty rm_dict['available_qty'] = max(0.0, t_qty - r_qty) return rm_dict @router.put('/{rm_id}', response_model=RmMasterResponse) async def update_rm( rm_id: UUID, data: RmMasterUpdate, db: AsyncSession = Depends(get_db), _=Depends(require_permission('RM_MASTER', 'edit')) ): rm = await db.get(RmMaster, rm_id) if not rm: raise HTTPException(404, 'RM not found') update_data = data.model_dump(exclude_none=True) for key, val in update_data.items(): setattr(rm, key, val) await db.flush() await db.refresh(rm) return await get_rm(rm_id, db) @router.delete('/{rm_id}', status_code=204) async def delete_rm( rm_id: UUID, db: AsyncSession = Depends(get_db), _=Depends(require_permission('RM_MASTER', 'delete')) ): rm = await db.get(RmMaster, rm_id) if not rm: raise HTTPException(404, 'RM not found') rm.is_active = False await db.flush()