316 lines
12 KiB
Python
316 lines
12 KiB
Python
|
"""
|
||
|
|
Replenishment Plan Repository
|
||
|
|
|
||
|
|
Provides database operations for replenishment planning, inventory projections,
|
||
|
|
and supplier allocations.
|
||
|
|
"""
|
||
|
|
|
||
|
|
from typing import List, Optional, Dict, Any
|
||
|
|
from datetime import date
|
||
|
|
from uuid import UUID
|
||
|
|
from sqlalchemy.ext.asyncio import AsyncSession
|
||
|
|
from sqlalchemy import select, and_, func
|
||
|
|
from sqlalchemy.orm import selectinload
|
||
|
|
|
||
|
|
from app.models.replenishment import (
|
||
|
|
ReplenishmentPlan,
|
||
|
|
ReplenishmentPlanItem,
|
||
|
|
InventoryProjection,
|
||
|
|
SupplierAllocation
|
||
|
|
)
|
||
|
|
from app.repositories.base_repository import BaseRepository
|
||
|
|
import structlog
|
||
|
|
|
||
|
|
logger = structlog.get_logger()
|
||
|
|
|
||
|
|
|
||
|
|
class ReplenishmentPlanRepository(BaseRepository[ReplenishmentPlan]):
|
||
|
|
"""Repository for replenishment plan operations"""
|
||
|
|
|
||
|
|
def __init__(self):
|
||
|
|
super().__init__(ReplenishmentPlan)
|
||
|
|
|
||
|
|
async def list_plans(
|
||
|
|
self,
|
||
|
|
db: AsyncSession,
|
||
|
|
tenant_id: UUID,
|
||
|
|
skip: int = 0,
|
||
|
|
limit: int = 100,
|
||
|
|
status: Optional[str] = None
|
||
|
|
) -> List[Dict[str, Any]]:
|
||
|
|
"""List replenishment plans for a tenant"""
|
||
|
|
try:
|
||
|
|
query = select(ReplenishmentPlan).where(
|
||
|
|
ReplenishmentPlan.tenant_id == tenant_id
|
||
|
|
)
|
||
|
|
|
||
|
|
if status:
|
||
|
|
query = query.where(ReplenishmentPlan.status == status)
|
||
|
|
|
||
|
|
query = query.offset(skip).limit(limit).order_by(
|
||
|
|
ReplenishmentPlan.created_at.desc()
|
||
|
|
)
|
||
|
|
|
||
|
|
result = await db.execute(query)
|
||
|
|
plans = result.scalars().all()
|
||
|
|
|
||
|
|
return [
|
||
|
|
{
|
||
|
|
"id": str(plan.id),
|
||
|
|
"tenant_id": str(plan.tenant_id),
|
||
|
|
"planning_date": plan.planning_date,
|
||
|
|
"projection_horizon_days": plan.projection_horizon_days,
|
||
|
|
"total_items": plan.total_items,
|
||
|
|
"urgent_items": plan.urgent_items,
|
||
|
|
"high_risk_items": plan.high_risk_items,
|
||
|
|
"total_estimated_cost": float(plan.total_estimated_cost),
|
||
|
|
"status": plan.status,
|
||
|
|
"created_at": plan.created_at,
|
||
|
|
"updated_at": plan.updated_at
|
||
|
|
}
|
||
|
|
for plan in plans
|
||
|
|
]
|
||
|
|
|
||
|
|
except Exception as e:
|
||
|
|
logger.error("Failed to list replenishment plans", error=str(e), tenant_id=tenant_id)
|
||
|
|
raise
|
||
|
|
|
||
|
|
async def get_plan_by_id(
|
||
|
|
self,
|
||
|
|
db: AsyncSession,
|
||
|
|
plan_id: UUID,
|
||
|
|
tenant_id: UUID
|
||
|
|
) -> Optional[Dict[str, Any]]:
|
||
|
|
"""Get a specific replenishment plan with items"""
|
||
|
|
try:
|
||
|
|
query = select(ReplenishmentPlan).where(
|
||
|
|
and_(
|
||
|
|
ReplenishmentPlan.id == plan_id,
|
||
|
|
ReplenishmentPlan.tenant_id == tenant_id
|
||
|
|
)
|
||
|
|
).options(selectinload(ReplenishmentPlan.items))
|
||
|
|
|
||
|
|
result = await db.execute(query)
|
||
|
|
plan = result.scalar_one_or_none()
|
||
|
|
|
||
|
|
if not plan:
|
||
|
|
return None
|
||
|
|
|
||
|
|
return {
|
||
|
|
"id": str(plan.id),
|
||
|
|
"tenant_id": str(plan.tenant_id),
|
||
|
|
"planning_date": plan.planning_date,
|
||
|
|
"projection_horizon_days": plan.projection_horizon_days,
|
||
|
|
"forecast_id": str(plan.forecast_id) if plan.forecast_id else None,
|
||
|
|
"production_schedule_id": str(plan.production_schedule_id) if plan.production_schedule_id else None,
|
||
|
|
"total_items": plan.total_items,
|
||
|
|
"urgent_items": plan.urgent_items,
|
||
|
|
"high_risk_items": plan.high_risk_items,
|
||
|
|
"total_estimated_cost": float(plan.total_estimated_cost),
|
||
|
|
"status": plan.status,
|
||
|
|
"created_at": plan.created_at,
|
||
|
|
"updated_at": plan.updated_at,
|
||
|
|
"executed_at": plan.executed_at,
|
||
|
|
"items": [
|
||
|
|
{
|
||
|
|
"id": str(item.id),
|
||
|
|
"ingredient_id": str(item.ingredient_id),
|
||
|
|
"ingredient_name": item.ingredient_name,
|
||
|
|
"unit_of_measure": item.unit_of_measure,
|
||
|
|
"base_quantity": float(item.base_quantity),
|
||
|
|
"safety_stock_quantity": float(item.safety_stock_quantity),
|
||
|
|
"final_order_quantity": float(item.final_order_quantity),
|
||
|
|
"order_date": item.order_date,
|
||
|
|
"delivery_date": item.delivery_date,
|
||
|
|
"required_by_date": item.required_by_date,
|
||
|
|
"lead_time_days": item.lead_time_days,
|
||
|
|
"is_urgent": item.is_urgent,
|
||
|
|
"urgency_reason": item.urgency_reason,
|
||
|
|
"waste_risk": item.waste_risk,
|
||
|
|
"stockout_risk": item.stockout_risk,
|
||
|
|
"supplier_id": str(item.supplier_id) if item.supplier_id else None
|
||
|
|
}
|
||
|
|
for item in plan.items
|
||
|
|
]
|
||
|
|
}
|
||
|
|
|
||
|
|
except Exception as e:
|
||
|
|
logger.error("Failed to get replenishment plan", error=str(e), plan_id=plan_id)
|
||
|
|
raise
|
||
|
|
|
||
|
|
|
||
|
|
class InventoryProjectionRepository(BaseRepository[InventoryProjection]):
|
||
|
|
"""Repository for inventory projection operations"""
|
||
|
|
|
||
|
|
def __init__(self):
|
||
|
|
super().__init__(InventoryProjection)
|
||
|
|
|
||
|
|
async def list_projections(
|
||
|
|
self,
|
||
|
|
db: AsyncSession,
|
||
|
|
tenant_id: UUID,
|
||
|
|
ingredient_id: Optional[UUID] = None,
|
||
|
|
projection_date: Optional[date] = None,
|
||
|
|
stockout_only: bool = False,
|
||
|
|
skip: int = 0,
|
||
|
|
limit: int = 100
|
||
|
|
) -> List[Dict[str, Any]]:
|
||
|
|
"""List inventory projections"""
|
||
|
|
try:
|
||
|
|
query = select(InventoryProjection).where(
|
||
|
|
InventoryProjection.tenant_id == tenant_id
|
||
|
|
)
|
||
|
|
|
||
|
|
if ingredient_id:
|
||
|
|
query = query.where(InventoryProjection.ingredient_id == ingredient_id)
|
||
|
|
|
||
|
|
if projection_date:
|
||
|
|
query = query.where(InventoryProjection.projection_date == projection_date)
|
||
|
|
|
||
|
|
if stockout_only:
|
||
|
|
query = query.where(InventoryProjection.is_stockout == True)
|
||
|
|
|
||
|
|
query = query.offset(skip).limit(limit).order_by(
|
||
|
|
InventoryProjection.projection_date.asc()
|
||
|
|
)
|
||
|
|
|
||
|
|
result = await db.execute(query)
|
||
|
|
projections = result.scalars().all()
|
||
|
|
|
||
|
|
return [
|
||
|
|
{
|
||
|
|
"id": str(proj.id),
|
||
|
|
"tenant_id": str(proj.tenant_id),
|
||
|
|
"ingredient_id": str(proj.ingredient_id),
|
||
|
|
"ingredient_name": proj.ingredient_name,
|
||
|
|
"projection_date": proj.projection_date,
|
||
|
|
"starting_stock": float(proj.starting_stock),
|
||
|
|
"forecasted_consumption": float(proj.forecasted_consumption),
|
||
|
|
"scheduled_receipts": float(proj.scheduled_receipts),
|
||
|
|
"projected_ending_stock": float(proj.projected_ending_stock),
|
||
|
|
"is_stockout": proj.is_stockout,
|
||
|
|
"coverage_gap": float(proj.coverage_gap),
|
||
|
|
"created_at": proj.created_at
|
||
|
|
}
|
||
|
|
for proj in projections
|
||
|
|
]
|
||
|
|
|
||
|
|
except Exception as e:
|
||
|
|
logger.error("Failed to list inventory projections", error=str(e), tenant_id=tenant_id)
|
||
|
|
raise
|
||
|
|
|
||
|
|
|
||
|
|
class SupplierAllocationRepository(BaseRepository[SupplierAllocation]):
|
||
|
|
"""Repository for supplier allocation operations"""
|
||
|
|
|
||
|
|
def __init__(self):
|
||
|
|
super().__init__(SupplierAllocation)
|
||
|
|
|
||
|
|
async def list_allocations(
|
||
|
|
self,
|
||
|
|
db: AsyncSession,
|
||
|
|
tenant_id: UUID,
|
||
|
|
requirement_id: Optional[UUID] = None,
|
||
|
|
supplier_id: Optional[UUID] = None,
|
||
|
|
skip: int = 0,
|
||
|
|
limit: int = 100
|
||
|
|
) -> List[Dict[str, Any]]:
|
||
|
|
"""List supplier allocations
|
||
|
|
|
||
|
|
Note: SupplierAllocation model doesn't have tenant_id, so we filter by requirements
|
||
|
|
"""
|
||
|
|
try:
|
||
|
|
# Build base query - no tenant_id filter since model doesn't have it
|
||
|
|
query = select(SupplierAllocation)
|
||
|
|
|
||
|
|
if requirement_id:
|
||
|
|
query = query.where(SupplierAllocation.requirement_id == requirement_id)
|
||
|
|
|
||
|
|
if supplier_id:
|
||
|
|
query = query.where(SupplierAllocation.supplier_id == supplier_id)
|
||
|
|
|
||
|
|
query = query.offset(skip).limit(limit).order_by(
|
||
|
|
SupplierAllocation.created_at.desc()
|
||
|
|
)
|
||
|
|
|
||
|
|
result = await db.execute(query)
|
||
|
|
allocations = result.scalars().all()
|
||
|
|
|
||
|
|
return [
|
||
|
|
{
|
||
|
|
"id": str(alloc.id),
|
||
|
|
"requirement_id": str(alloc.requirement_id) if alloc.requirement_id else None,
|
||
|
|
"replenishment_plan_item_id": str(alloc.replenishment_plan_item_id) if alloc.replenishment_plan_item_id else None,
|
||
|
|
"supplier_id": str(alloc.supplier_id),
|
||
|
|
"supplier_name": alloc.supplier_name,
|
||
|
|
"allocation_type": alloc.allocation_type,
|
||
|
|
"allocated_quantity": float(alloc.allocated_quantity),
|
||
|
|
"allocation_percentage": float(alloc.allocation_percentage),
|
||
|
|
"unit_price": float(alloc.unit_price),
|
||
|
|
"total_cost": float(alloc.total_cost),
|
||
|
|
"lead_time_days": alloc.lead_time_days,
|
||
|
|
"supplier_score": float(alloc.supplier_score),
|
||
|
|
"allocation_reason": alloc.allocation_reason,
|
||
|
|
"created_at": alloc.created_at
|
||
|
|
}
|
||
|
|
for alloc in allocations
|
||
|
|
]
|
||
|
|
|
||
|
|
except Exception as e:
|
||
|
|
logger.error("Failed to list supplier allocations", error=str(e))
|
||
|
|
raise
|
||
|
|
|
||
|
|
|
||
|
|
class ReplenishmentAnalyticsRepository:
|
||
|
|
"""Repository for replenishment analytics"""
|
||
|
|
|
||
|
|
async def get_analytics(
|
||
|
|
self,
|
||
|
|
db: AsyncSession,
|
||
|
|
tenant_id: UUID,
|
||
|
|
start_date: Optional[date] = None,
|
||
|
|
end_date: Optional[date] = None
|
||
|
|
) -> Dict[str, Any]:
|
||
|
|
"""Get replenishment planning analytics"""
|
||
|
|
try:
|
||
|
|
# Build base query
|
||
|
|
query = select(ReplenishmentPlan).where(
|
||
|
|
ReplenishmentPlan.tenant_id == tenant_id
|
||
|
|
)
|
||
|
|
|
||
|
|
if start_date:
|
||
|
|
query = query.where(ReplenishmentPlan.planning_date >= start_date)
|
||
|
|
|
||
|
|
if end_date:
|
||
|
|
query = query.where(ReplenishmentPlan.planning_date <= end_date)
|
||
|
|
|
||
|
|
result = await db.execute(query)
|
||
|
|
plans = result.scalars().all()
|
||
|
|
|
||
|
|
# Calculate analytics
|
||
|
|
total_plans = len(plans)
|
||
|
|
total_items = sum(plan.total_items for plan in plans)
|
||
|
|
total_urgent = sum(plan.urgent_items for plan in plans)
|
||
|
|
total_high_risk = sum(plan.high_risk_items for plan in plans)
|
||
|
|
total_cost = sum(plan.total_estimated_cost for plan in plans)
|
||
|
|
|
||
|
|
# Status breakdown
|
||
|
|
status_counts = {}
|
||
|
|
for plan in plans:
|
||
|
|
status_counts[plan.status] = status_counts.get(plan.status, 0) + 1
|
||
|
|
|
||
|
|
return {
|
||
|
|
"total_plans": total_plans,
|
||
|
|
"total_items": total_items,
|
||
|
|
"total_urgent_items": total_urgent,
|
||
|
|
"total_high_risk_items": total_high_risk,
|
||
|
|
"total_estimated_cost": float(total_cost),
|
||
|
|
"status_breakdown": status_counts,
|
||
|
|
"average_items_per_plan": total_items / total_plans if total_plans > 0 else 0,
|
||
|
|
"urgent_item_percentage": (total_urgent / total_items * 100) if total_items > 0 else 0
|
||
|
|
}
|
||
|
|
|
||
|
|
except Exception as e:
|
||
|
|
logger.error("Failed to get replenishment analytics", error=str(e), tenant_id=tenant_id)
|
||
|
|
raise
|