from __future__ import annotations from calendar import monthrange from datetime import date, datetime, time, timedelta from typing import Annotated from fastapi import APIRouter, Depends, HTTPException, Query from sqlalchemy import desc, func from sqlalchemy.orm import Query as SQLAlchemyQuery, Session from app.api.deps import require_access from app.db.session import get_db from app.models.procurement import ( DeliveryAcceptance, ETenderCase, EMD, FinancialEvaluation, GeMProcurementCase, GlobalTenderCase, LPCProcurementCase, OpenMarketCase, PaymentTracking, PerformanceSecurity, ProcurementCase, PurchaseOrder, Requisition, SampleRegister, TechnicalEvaluation, TenderBid, ) from app.models.enums import ProcurementStatus router = APIRouter(dependencies=[Depends(require_access("dashboard"))]) ACTIVE_PROCUREMENT_MODES = ("GEM", "E_TENDER", "GLOBAL_TENDER", "LPC", "OPEN_MARKET") PROCUREMENT_TYPE_LABELS = { "GEM": "GeM Procurement", "E_TENDER": "e-Tendering", "GLOBAL_TENDER": "Global Tender", "LPC": "Local Purchase Committee", "OPEN_MARKET": "Open Market Procurement", } PROCUREMENT_TYPE_MODELS = { "GEM": GeMProcurementCase, "E_TENDER": ETenderCase, "GLOBAL_TENDER": GlobalTenderCase, "LPC": LPCProcurementCase, "OPEN_MARKET": OpenMarketCase, } MODULES = [ ("requisitions", "Requisition Register", Requisition, "/lifecycle/requisitions"), ("procurement-cases", "Procurement Case Register", ProcurementCase, "/lifecycle/procurement-cases"), ("gem-cases", "GeM Procurement", GeMProcurementCase, "/lifecycle/gem-cases"), ("e-tender-cases", "e-Tendering", ETenderCase, "/lifecycle/e-tender-cases"), ("global-tender-cases", "Global Tender", GlobalTenderCase, "/lifecycle/global-tender-cases"), ("lpc-cases", "Local Purchase Committee", LPCProcurementCase, "/lifecycle/lpc-cases"), ("open-market-cases", "Open Market Procurement", OpenMarketCase, "/lifecycle/open-market-cases"), ("tenders", "Tender/Bid Register", TenderBid, "/lifecycle/tenders"), ("emds", "EMD Register", EMD, "/lifecycle/emds"), ("samples", "Sample Register", SampleRegister, "/lifecycle/samples"), ("technical-evaluations", "Technical Evaluation", TechnicalEvaluation, "/lifecycle/technical-evaluations"), ("financial-evaluations", "Financial Evaluation", FinancialEvaluation, "/lifecycle/financial-evaluations"), ("purchase-orders", "Purchase Order", PurchaseOrder, "/lifecycle/purchase-orders"), ("performance-securities", "Performance Security", PerformanceSecurity, "/lifecycle/performance-securities"), ("delivery-acceptance", "Delivery/Acceptance", DeliveryAcceptance, "/lifecycle/delivery-acceptance"), ("payments", "Payment Tracker", PaymentTracking, "/lifecycle/payments"), ] MODULE_CONFIG = { "requisitions": { "date_column": Requisition.requisition_date, "distribution_title": "Distribution as per Establishments", "distribution_column": Requisition.received_from, "status_title": "Approval Status", "status_column": Requisition.approval_status, "metrics": [ ("Estimated Cost", func.coalesce(Requisition.estimated_cost, 0), "currency", "sum"), ], }, "procurement-cases": { "date_column": ProcurementCase.mode_selection_date, "distribution_title": "Procurement Mode Distribution", "distribution_column": ProcurementCase.procurement_mode, "status_title": "Case Status", "status_column": ProcurementCase.case_status, "metrics": [ ("Approved Estimated Cost", func.coalesce(ProcurementCase.approved_estimated_cost, 0), "currency", "sum"), ("Approved Quantity", func.coalesce(ProcurementCase.approved_quantity, 0), "number", "sum"), ], }, "gem-cases": { "date_column": GeMProcurementCase.gem_bid_date, "distribution_title": "GeM Type Distribution", "distribution_column": GeMProcurementCase.gem_procurement_type, "status_title": "GeM Status", "status_column": GeMProcurementCase.status, "metrics": [ ("GeM Order Value", func.coalesce(GeMProcurementCase.gem_order_value, 0), "currency", "sum"), ], }, "e-tender-cases": { "date_column": ETenderCase.tender_publication_date, "distribution_title": "Technical Evaluation Status", "distribution_column": ETenderCase.technical_evaluation_status, "status_title": "Tender Status", "status_column": ETenderCase.status, "metrics": [ ("Estimated Cost", func.coalesce(ETenderCase.estimated_cost, 0), "currency", "sum"), ], }, "global-tender-cases": { "date_column": GlobalTenderCase.approval_date, "distribution_title": "Vendor Country Distribution", "distribution_column": GlobalTenderCase.country_of_vendor, "status_title": "Global Tender Status", "status_column": GlobalTenderCase.status, "metrics": [ ("Bid Value INR", func.coalesce(GlobalTenderCase.bid_value_inr, 0), "currency", "sum"), ], }, "lpc-cases": { "date_column": LPCProcurementCase.lpc_meeting_date, "distribution_title": "Quotation Count Distribution", "distribution_column": LPCProcurementCase.quotation_count, "status_title": "LPC Status", "status_column": LPCProcurementCase.status, "metrics": [ ("Quotations Received", func.coalesce(LPCProcurementCase.quotation_count, 0), "number", "sum"), ], }, "open-market-cases": { "date_column": OpenMarketCase.market_survey_date, "distribution_title": "Quotation Count Distribution", "distribution_column": OpenMarketCase.quotation_count, "status_title": "Open Market Status", "status_column": OpenMarketCase.status, "metrics": [ ("Quotations Received", func.coalesce(OpenMarketCase.quotation_count, 0), "number", "sum"), ], }, "tenders": { "date_column": TenderBid.tender_date, "distribution_title": "Tender Type Distribution", "distribution_column": TenderBid.bid_type, "status_title": "Tender Status", "status_column": TenderBid.status, "metrics": [ ("Tender Value", func.coalesce(TenderBid.amount, TenderBid.estimated_cost, 0), "currency", "sum"), ], }, "emds": { "date_column": EMD.entry_date, "distribution_title": "EMD Form Distribution", "distribution_column": EMD.emd_form, "status_title": "Refund Status", "status_column": EMD.refund_status, "metrics": [ ("EMD Amount", func.coalesce(EMD.amount, 0), "currency", "sum"), ], }, "samples": { "date_column": SampleRegister.receipt_date, "distribution_title": "Bidder Distribution", "distribution_column": SampleRegister.bidder_name, "status_title": "Evaluation Status", "status_column": SampleRegister.evaluation_status, "metrics": [ ("Sample Quantity", func.coalesce(SampleRegister.quantity, 0), "number", "sum"), ], }, "technical-evaluations": { "distribution_title": "Evaluator Distribution", "distribution_column": TechnicalEvaluation.evaluator, "status_title": "Result Status", "status_column": TechnicalEvaluation.result, "metrics": [ ("Average Score", TechnicalEvaluation.score, "decimal", "avg"), ], }, "financial-evaluations": { "distribution_title": "Bidder Distribution", "distribution_column": FinancialEvaluation.bidder_name, "status_title": "Result Status", "status_column": FinancialEvaluation.result, "metrics": [ ("Quoted Amount", FinancialEvaluation.quoted_amount, "currency", "sum"), ], }, "purchase-orders": { "date_column": PurchaseOrder.po_date, "distribution_title": "Vendor Distribution", "distribution_column": PurchaseOrder.vendor_name, "status_title": "Order Status", "status_column": PurchaseOrder.status, "metrics": [ ("PO Value", func.coalesce(PurchaseOrder.po_value, PurchaseOrder.estimated_cost, 0), "currency", "sum"), ], }, "performance-securities": { "date_column": PerformanceSecurity.entry_date, "distribution_title": "Security Form Distribution", "distribution_column": PerformanceSecurity.security_form, "status_title": "Release Status", "status_column": PerformanceSecurity.release_status, "metrics": [ ("Security Amount", func.coalesce(PerformanceSecurity.amount, 0), "currency", "sum"), ], }, "delivery-acceptance": { "date_column": DeliveryAcceptance.delivery_date, "distribution_title": "Delivery Status Distribution", "distribution_column": DeliveryAcceptance.acceptance_status, "status_title": "Acceptance Status", "status_column": DeliveryAcceptance.acceptance_status, "metrics": [ ("Accepted Quantity", DeliveryAcceptance.accepted_quantity, "number", "sum"), ], }, "payments": { "date_column": PaymentTracking.invoice_date, "distribution_title": "Invoice Status Distribution", "distribution_column": PaymentTracking.payment_status, "status_title": "Payment Status", "status_column": PaymentTracking.payment_status, "metrics": [ ("Invoice Amount", PaymentTracking.invoice_amount, "currency", "sum"), ("Paid Amount", PaymentTracking.paid_amount, "currency", "sum"), ], }, } def _value(value): return value.value if hasattr(value, "value") else value def _range_query(db: Session, model, start_date: date | None, end_date: date | None): query = db.query(model) if start_date: query = query.filter(model.created_at >= datetime.combine(start_date, time.min)) if end_date: query = query.filter(model.created_at <= datetime.combine(end_date, time.max)) return query def _module_details(module_key: str): module = next((module for module in MODULES if module[0] == module_key), None) if not module: raise HTTPException(status_code=404, detail="Unknown dashboard module") return module, MODULE_CONFIG[module_key] def _analytics_period( period: str, start_date: date | None, end_date: date | None, month: int | None, year: int | None, ) -> tuple[date | None, date | None, str]: today = date.today() if period == "all": return None, None, "All time" if period == "range": if not start_date and not end_date: raise HTTPException(status_code=400, detail="Choose a From or To date for a date range.") if start_date and end_date and start_date > end_date: raise HTTPException(status_code=400, detail="From date cannot be later than To date.") if start_date and end_date: return start_date, end_date, f"{start_date:%d %b %Y} - {end_date:%d %b %Y}" if start_date: return start_date, None, f"From {start_date:%d %b %Y}" return None, end_date, f"Until {end_date:%d %b %Y}" if period == "year": selected_year = year or today.year return date(selected_year, 1, 1), date(selected_year, 12, 31), str(selected_year) if period in {"month", "current_month"}: selected_month = today.month if period == "current_month" else (month or today.month) selected_year = today.year if period == "current_month" else (year or today.year) last_day = monthrange(selected_year, selected_month)[1] start = date(selected_year, selected_month, 1) return start, date(selected_year, selected_month, last_day), start.strftime("%B %Y") raise HTTPException(status_code=400, detail="Unknown dashboard filter period.") def _analytics_query( db: Session, model, config: dict, start_date: date | None, end_date: date | None, ): query = db.query(model) if model is ProcurementCase: query = query.filter(ProcurementCase.procurement_mode.in_(ACTIVE_PROCUREMENT_MODES)) date_column = config.get("date_column") event_date = func.coalesce(date_column, func.date(model.created_at)) if date_column is not None else func.date(model.created_at) if start_date: query = query.filter(event_date >= start_date) if end_date: query = query.filter(event_date <= end_date) return query def _metric(query: SQLAlchemyQuery, label: str, expression, kind: str, aggregate: str): if aggregate == "count": calculator = func.count(expression) else: calculator = func.avg(expression) if aggregate == "avg" else func.sum(expression) value = query.with_entities(func.coalesce(calculator, 0)).scalar() return {"label": label, "value": float(value or 0), "kind": kind} def _breakdown(query: SQLAlchemyQuery, model, column, limit: int | None = None): rows = ( query.with_entities(column, func.count(model.id)) .group_by(column) .order_by(func.count(model.id).desc()) .limit(limit) .all() ) return [ {"label": str(_value(label) or "Not set"), "count": count} for label, count in rows ] def _latest(query: SQLAlchemyQuery, label: str, route: str, primary: str, secondary: str | None = None): entries = [] model = query.column_descriptions[0]["entity"] for row in query.order_by(desc(model.updated_at)).limit(5).all(): title = getattr(row, primary, None) or f"{label} #{row.id}" subtitle = _value(getattr(row, secondary, None)) if secondary else None entries.append( { "module": label, "title": str(title), "subtitle": str(subtitle or ""), "route": route, "updated_at": row.updated_at.isoformat(), } ) return entries def _iso(value): return value.isoformat() if value else None def _status_text(value): return str(_value(value) or "Not Recorded") def _linked_latest(db: Session, model, procurement_case_id: int): return ( db.query(model) .filter(model.procurement_case_id == procurement_case_id) .order_by(desc(model.updated_at)) .first() ) def _publication_date(case, tender, type_record): if tender: date_value = tender.bid_published_date or tender.publication_date or tender.tender_date if date_value: return date_value if isinstance(type_record, ETenderCase): return type_record.tender_publication_date if isinstance(type_record, GeMProcurementCase): return type_record.gem_bid_date if isinstance(type_record, GlobalTenderCase): return type_record.approval_date if isinstance(type_record, LPCProcurementCase): return type_record.recommendation_date or type_record.lpc_meeting_date if isinstance(type_record, OpenMarketCase): return type_record.market_survey_date or type_record.approval_date return case.mode_selection_date def _submission_date(tender, type_record): if tender: date_value = tender.bid_closed_date or tender.closing_date or tender.technical_bid_opening_date if date_value: return date_value if isinstance(type_record, ETenderCase): return type_record.bid_submission_end_date return None def _bid_status(case, tender, type_record, publication_date, submission_date): today = date.today() raw_status = _status_text(getattr(tender, "status", None) or getattr(type_record, "status", None) or case.case_status) normalized = raw_status.lower().replace("_", " ") if normalized in {"closed", "completed", "cancelled", "re-tendered", "re tendered"}: return "Closed" if submission_date and submission_date < today: return "Closed" if normalized in {"in tendering process", "in progress", "submitted", "approval pending", "mode selection"}: return "In Evaluation" if publication_date: return "Published" return "Not Published" def _emd_status(case, emd): if not case.emd_applicable: return "Exempted" if emd and (emd.emd_no or emd.emd_unique_id or emd.emd_date or emd.amount): return "Submitted" return "Not Submitted" def _performance_security_status(case, security): if not case.pbg_applicable: return "Exempted" if security and (security.security_number or security.security_date or security.amount): return _status_text(security.release_status) if security.release_status else "Submitted" return "Not Submitted" def _increment(counter: dict[str, int], key: str) -> None: counter[key] = counter.get(key, 0) + 1 @router.get("/requisitions") def requisition_dashboard( db: Annotated[Session, Depends(get_db)], ): query = db.query(Requisition) total = query.count() approved = query.filter(Requisition.approval_status == ProcurementStatus.approved).count() rejected = query.filter(Requisition.approval_status == ProcurementStatus.rejected).count() pending = max(total - approved - rejected, 0) total_amount = query.with_entities(func.coalesce(func.sum(func.coalesce(Requisition.estimated_cost, 0)), 0)).scalar() budget_rows = ( query.with_entities( func.coalesce(Requisition.budget_head, "Not set").label("budget_head"), func.count(Requisition.id).label("count"), func.coalesce(func.sum(func.coalesce(Requisition.estimated_cost, 0)), 0).label("amount"), ) .group_by(func.coalesce(Requisition.budget_head, "Not set")) .order_by(func.coalesce(func.sum(func.coalesce(Requisition.estimated_cost, 0)), 0).desc()) .all() ) vendor_payment_rows = ( db.query(PurchaseOrder, PaymentTracking) .outerjoin(PaymentTracking, PaymentTracking.po_id == PurchaseOrder.id) .order_by(desc(func.coalesce(PaymentTracking.invoice_date, PurchaseOrder.po_date, PurchaseOrder.created_at))) .limit(10) .all() ) vendor_payment_status = [] for order, payment in vendor_payment_rows: invoiced = float(payment.invoice_amount or 0) if payment else 0 paid = float(payment.paid_amount or 0) if payment else 0 vendor_payment_status.append( { "vendor_name": order.vendor_name, "po_no": order.po_no, "invoice_no": payment.invoice_no if payment else "Not Submitted", "invoice_date": payment.invoice_date.isoformat() if payment and payment.invoice_date else None, "invoice_amount": invoiced, "paid_amount": paid, "outstanding_amount": max(invoiced - paid, 0), "payment_status": payment.payment_status if payment else "Invoice Pending", } ) return { "summary": { "received": total, "approved": approved, "rejected": rejected, "pending": pending, "total_amount": float(total_amount or 0), }, "status": [ {"key": "received", "label": "Total Requisitions Received", "count": total, "description": "All requisitions entered in the register."}, {"key": "approved", "label": "Approved Requisitions", "count": approved, "description": "Requisitions cleared for procurement action."}, {"key": "rejected", "label": "Rejected Requisitions", "count": rejected, "description": "Requisitions returned or rejected during scrutiny."}, {"key": "pending", "label": "Pending Requisitions", "count": pending, "description": "Requisitions still moving through review."}, ], "budget_heads": [ { "budget_head": str(budget_head), "count": count, "amount": float(amount or 0), } for budget_head, count, amount in budget_rows ], "vendor_payment_status": vendor_payment_status, "recent": [ { "id": row.id, "requisition_no": row.requisition_no, "received_from": row.received_from or "", "budget_head": row.budget_head or "Not set", "amount": float(row.estimated_cost or 0), "approval_status": _value(row.approval_status), "updated_at": row.updated_at.isoformat(), } for row in query.order_by(desc(Requisition.updated_at)).limit(8).all() ], } @router.get("/procurement-types") def procurement_type_dashboards( db: Annotated[Session, Depends(get_db)], ): dashboards = [] for mode in ACTIVE_PROCUREMENT_MODES: cases = ( db.query(ProcurementCase) .filter(ProcurementCase.procurement_mode == mode) .order_by(desc(ProcurementCase.updated_at)) .all() ) bid_status_counts: dict[str, int] = {"Published": 0, "In Evaluation": 0, "Closed": 0, "Not Published": 0} emd_status_counts: dict[str, int] = {"Submitted": 0, "Not Submitted": 0, "Exempted": 0} po_status_counts: dict[str, int] = {"Issued": 0, "Not Issued": 0} delivery_status_counts: dict[str, int] = {} performance_security_counts: dict[str, int] = {"Submitted": 0, "Not Submitted": 0, "Exempted": 0} rows = [] type_model = PROCUREMENT_TYPE_MODELS[mode] for case in cases: type_record = _linked_latest(db, type_model, case.id) tender = _linked_latest(db, TenderBid, case.id) emd = _linked_latest(db, EMD, case.id) purchase_order = _linked_latest(db, PurchaseOrder, case.id) delivery = _linked_latest(db, DeliveryAcceptance, case.id) security = _linked_latest(db, PerformanceSecurity, case.id) publication_date = _publication_date(case, tender, type_record) submission_date = _submission_date(tender, type_record) bid_status = _bid_status(case, tender, type_record, publication_date, submission_date) emd_status = _emd_status(case, emd) po_status = "Issued" if purchase_order else "Not Issued" delivery_status = _status_text(delivery.acceptance_status) if delivery else "Not Recorded" performance_status = _performance_security_status(case, security) _increment(bid_status_counts, bid_status) _increment(emd_status_counts, emd_status) _increment(po_status_counts, po_status) _increment(delivery_status_counts, delivery_status) _increment(performance_security_counts, performance_status if performance_status in performance_security_counts else "Submitted") rows.append( { "case_id": case.id, "case_no": case.procurement_case_no, "case_status": case.case_status, "bid_publication_date": _iso(publication_date), "submission_date": _iso(submission_date), "emd_status": emd_status, "bid_status": bid_status, "purchase_order_status": po_status, "purchase_order_no": purchase_order.po_no if purchase_order else None, "delivery_acceptance_status": delivery_status, "performance_security_status": performance_status, "updated_at": case.updated_at.isoformat(), } ) dashboards.append( { "key": mode, "label": PROCUREMENT_TYPE_LABELS[mode], "total_cases": len(cases), "bid_status": [{"label": key, "count": value} for key, value in bid_status_counts.items()], "emd_status": [{"label": key, "count": value} for key, value in emd_status_counts.items()], "purchase_order_status": [{"label": key, "count": value} for key, value in po_status_counts.items()], "delivery_acceptance_status": [ {"label": key, "count": value} for key, value in sorted(delivery_status_counts.items(), key=lambda item: (-item[1], item[0])) ], "performance_security_status": [{"label": key, "count": value} for key, value in performance_security_counts.items()], "cases": rows, } ) return {"types": dashboards} @router.get("/module/{module_key}") def module_analytics( module_key: str, db: Annotated[Session, Depends(get_db)], period: str = Query(default="current_month"), start_date: date | None = Query(default=None), end_date: date | None = Query(default=None), month: int | None = Query(default=None, ge=1, le=12), year: int | None = Query(default=None, ge=2000, le=2100), ): (key, label, model, route), config = _module_details(module_key) filtered_start, filtered_end, period_label = _analytics_period(period, start_date, end_date, month, year) query = _analytics_query(db, model, config, filtered_start, filtered_end) return { "key": key, "label": label, "route": route, "period_label": period_label, "count": query.count(), "distribution_title": config["distribution_title"], "distribution": _breakdown(query, model, config["distribution_column"], 8), "status_title": config["status_title"], "status": _breakdown(query, model, config["status_column"]), "metrics": [_metric(query, *metric) for metric in config["metrics"]], } @router.get("") def dashboard( db: Annotated[Session, Depends(get_db)], start_date: date | None = Query(default=None), end_date: date | None = Query(default=None), ): queries = { key: _range_query(db, model, start_date, end_date) for key, _, model, _ in MODULES } po_query = queries["purchase-orders"] payment_query = queries["payments"] po_value = po_query.with_entities( func.coalesce(func.sum(func.coalesce(PurchaseOrder.po_value, PurchaseOrder.estimated_cost)), 0) ).scalar() invoice_total = payment_query.with_entities(func.coalesce(func.sum(PaymentTracking.invoice_amount), 0)).scalar() paid_total = payment_query.with_entities(func.coalesce(func.sum(PaymentTracking.paid_amount), 0)).scalar() today = date.today() next_30_days = today + timedelta(days=30) requisition_query = queries["requisitions"] procurement_case_query = queries["procurement-cases"].filter(ProcurementCase.procurement_mode.in_(ACTIVE_PROCUREMENT_MODES)) pbg_query = queries["performance-securities"] return { "counts": { "requisitions": queries["requisitions"].count(), "procurement_cases": procurement_case_query.count(), "tenders": queries["tenders"].count(), "purchase_orders": po_query.count(), "payments": payment_query.count(), }, "requisition_dashboard": [ {"label": "Total Requisitions", "value": requisition_query.count()}, {"label": "Approved Requisitions", "value": requisition_query.filter(Requisition.approval_status == ProcurementStatus.approved).count()}, {"label": "Rejected Requisitions", "value": requisition_query.filter(Requisition.approval_status == ProcurementStatus.rejected).count()}, {"label": "Pending Requisitions", "value": requisition_query.filter(Requisition.approval_status.notin_([ProcurementStatus.approved, ProcurementStatus.rejected])).count()}, ], "procurement_dashboard": [ {"label": "Total Procurement Cases", "value": procurement_case_query.count()}, {"label": "GeM Cases", "value": procurement_case_query.filter(ProcurementCase.procurement_mode == "GEM").count()}, {"label": "e-Tender Cases", "value": procurement_case_query.filter(ProcurementCase.procurement_mode == "E_TENDER").count()}, {"label": "Global Tender Cases", "value": procurement_case_query.filter(ProcurementCase.procurement_mode == "GLOBAL_TENDER").count()}, {"label": "LPC Cases", "value": procurement_case_query.filter(ProcurementCase.procurement_mode == "LPC").count()}, {"label": "Open Market Cases", "value": procurement_case_query.filter(ProcurementCase.procurement_mode == "OPEN_MARKET").count()}, ], "compliance_dashboard": [ {"label": "EMD Pending Return", "value": queries["emds"].filter(EMD.refund_status.in_(["Pending", "Pending Return", "Held"])).count()}, {"label": "EMD Forfeited", "value": queries["emds"].filter(EMD.refund_status == "Forfeited").count()}, {"label": "PBG Expiring in 30 Days", "value": pbg_query.filter(PerformanceSecurity.expiry_date >= today, PerformanceSecurity.expiry_date <= next_30_days).count()}, {"label": "PBG Expired", "value": pbg_query.filter(PerformanceSecurity.expiry_date < today).count()}, {"label": "Contracts Near Completion", "value": po_query.filter(PurchaseOrder.delivery_due_date >= today, PurchaseOrder.delivery_due_date <= next_30_days).count()}, ], "modules": [ { "key": key, "label": label, "count": queries[key].filter(ProcurementCase.procurement_mode.in_(ACTIVE_PROCUREMENT_MODES)).count() if key == "procurement-cases" else queries[key].count(), "route": route, } for key, label, model, route in MODULES ], "financials": { "purchase_order_value": float(po_value), "invoice_total": float(invoice_total), "paid_total": float(paid_total), "outstanding": float(invoice_total - paid_total), }, "status_analytics": [ {"module": "Requisition Register", "items": _breakdown(queries["requisitions"], Requisition, Requisition.approval_status)}, {"module": "Sample Register", "items": _breakdown(queries["samples"], SampleRegister, SampleRegister.evaluation_status)}, {"module": "Tender/Bid Register", "items": _breakdown(queries["tenders"], TenderBid, TenderBid.status)}, {"module": "Purchase Order", "items": _breakdown(po_query, PurchaseOrder, PurchaseOrder.status)}, {"module": "Delivery/Acceptance", "items": _breakdown(queries["delivery-acceptance"], DeliveryAcceptance, DeliveryAcceptance.acceptance_status)}, ], "payment_status": _breakdown(payment_query, PaymentTracking, PaymentTracking.payment_status), "technical_results": _breakdown(queries["technical-evaluations"], TechnicalEvaluation, TechnicalEvaluation.result), "financial_results": _breakdown(queries["financial-evaluations"], FinancialEvaluation, FinancialEvaluation.result), "procurement_modes": _breakdown(procurement_case_query, ProcurementCase, ProcurementCase.procurement_mode, 8), "range_requisitions": _breakdown(queries["requisitions"], Requisition, Requisition.received_from_range, 8), "recent_tenders": [ { "id": t.id, "tender_no": t.tender_no, "title": t.title or t.item_name or "", "status": t.status.value, } for t in queries["tenders"].order_by(TenderBid.id.desc()).limit(5).all() ], "recent_activity": sorted( [ *_latest(queries["requisitions"], "Requisition Register", "/lifecycle/requisitions", "requisition_no", "received_from"), *_latest(queries["samples"], "Sample Register", "/lifecycle/samples", "tender_number", "bidder_name"), *_latest(queries["tenders"], "Tender/Bid Register", "/lifecycle/tenders", "tender_no", "status"), *_latest(po_query, "Purchase Order", "/lifecycle/purchase-orders", "po_no", "vendor_name"), *_latest(payment_query, "Payment Tracker", "/lifecycle/payments", "invoice_no", "payment_status"), ], key=lambda item: item["updated_at"], reverse=True, )[:10], }