from datetime import datetime, timedelta import pytz from django.db.models import Q from rest_framework.authentication import TokenAuthentication from rest_framework.decorators import api_view, authentication_classes, permission_classes from rest_framework.permissions import IsAuthenticated from rest_framework.response import Response from human_resource.models import StaffProfile from procurement.models import ProductPurchaseInstance, PurchaseOrder, Supplier from system_administration.models import CompanyProfile from access_control.permissions import requires_permission from warehouse_management.models import ( Category, Inventory, Product, StockReservation, StockReservationInstance, StockTransaction, StockTransactionInstance, Warehouse, ) TARGET_TZ = pytz.timezone('Africa/Nairobi') DATE_FMT = '%d/%m/%Y, %H:%M' DATE_ONLY_FMT = '%d/%m/%Y' def _parse_date(date_str): """Parse DD/MM/YYYY string to a timezone-aware datetime at start of day.""" dt = datetime.strptime(date_str, DATE_ONLY_FMT) return TARGET_TZ.localize(dt) # --------------------------------------------------------------------------- # 1. Stock Balance Report # --------------------------------------------------------------------------- @api_view(['POST']) @authentication_classes([TokenAuthentication]) @permission_classes([IsAuthenticated]) @requires_permission('reports.warehouse_reports.view') def stock_balance_report(request): """ Stock balances by item and category for a warehouse. Input: serial_number, warehouse_id, category_id ("all" or numeric id) """ company_serial_number = request.data["serial_number"] warehouse_id = request.data["warehouse_id"] category_id = request.data.get("category_id", "all") active_user = request.user payload = {} try: company_profile = CompanyProfile.objects.get(company_serial_number=company_serial_number) staff_profile = StaffProfile.objects.get(user=active_user) warehouse = Warehouse.objects.get(id=int(warehouse_id)) qs = Inventory.objects.filter( warehouse=warehouse, recycle_bin=False, product__recycle_bin=False, product__category__isnull=False, ).exclude( product__category__category_name="TRANSPORT FEE", ).select_related('product', 'product__category').order_by( 'product__category__category_name', 'product__product_name' ) if category_id != "all": qs = qs.filter(product__category_id=int(category_id)) # Lazy-expire reservations from warehouse_management.views import _expire_reservations, _get_reserved_qty _expire_reservations(warehouse) category_map = {} total_items = 0 low_stock_count = 0 out_of_stock_count = 0 for inv in qs: product = inv.product cat = product.category cat_key = str(cat.id) if cat_key not in category_map: category_map[cat_key] = { "category_id": cat_key, "category_name": cat.category_name, "product_list": [], "category_total_quantity": 0.0, } qty = float(inv.quantity) min_qty = float(inv.minimum_stock_level) reserved = _get_reserved_qty(product, warehouse) available = max(0.0, qty - reserved) if available == 0: stock_status = "out_of_stock" out_of_stock_count += 1 elif available <= min_qty: stock_status = "low_stock" low_stock_count += 1 else: stock_status = "adequate" category_map[cat_key]["product_list"].append({ "product_id": str(product.id), "product_name": product.product_name, "stock_keeping_unit": product.stock_keeping_unit, "unit_of_measurement": product.unit_of_measurement, "inventory_id": str(inv.id), "quantity": str(qty), "minimum_stock_level": str(min_qty), "reserved_quantity": str(reserved), "available_quantity": str(available), "stock_status": stock_status, }) category_map[cat_key]["category_total_quantity"] += qty total_items += 1 for cat in category_map.values(): cat["category_total_quantity"] = str(cat["category_total_quantity"]) payload["warehouse_id"] = str(warehouse.id) payload["warehouse_name"] = warehouse.warehouse_name payload["category_list"] = sorted(category_map.values(), key=lambda x: x["category_name"]) payload["summary"] = { "total_items": total_items, "low_stock_count": low_stock_count, "out_of_stock_count": out_of_stock_count, } return Response({"message": "true", "payload": payload}, status=200) except Exception as e: print(e) return Response({"message": "false", "payload": payload}, status=500) # --------------------------------------------------------------------------- # 2. Consumption Report # --------------------------------------------------------------------------- @api_view(['POST']) @authentication_classes([TokenAuthentication]) @permission_classes([IsAuthenticated]) @requires_permission('reports.warehouse_reports.view') def consumption_report(request): """ Outbound stock consumption by product line within a date range. Input: serial_number, warehouse_id, from_date (DD/MM/YYYY), to_date (DD/MM/YYYY) """ company_serial_number = request.data["serial_number"] warehouse_id = request.data["warehouse_id"] from_date_str = request.data["from_date"] to_date_str = request.data["to_date"] active_user = request.user payload = {} try: CompanyProfile.objects.get(company_serial_number=company_serial_number) warehouse = Warehouse.objects.get(id=int(warehouse_id)) from_dt = _parse_date(from_date_str) to_dt = _parse_date(to_date_str) + timedelta(days=1) outbound_txs = StockTransaction.objects.filter( source_warehouse=warehouse, transaction_type="out_bound", recycle_bin=False, created_on__gte=from_dt, created_on__lt=to_dt, ).prefetch_related('stock_transaction_instances__product__category') product_consumption = {} for tx in outbound_txs: for inst in tx.stock_transaction_instances.all(): if inst.product is None: continue prod_id = str(inst.product.id) if prod_id not in product_consumption: product_consumption[prod_id] = { "product_id": prod_id, "product_name": inst.product.product_name, "stock_keeping_unit": inst.product.stock_keeping_unit, "unit_of_measurement": inst.product.unit_of_measurement, "category_name": inst.product.category.category_name if inst.product.category else "", "total_consumed": 0.0, "transaction_count": 0, } product_consumption[prod_id]["total_consumed"] += float(inst.quantity) product_consumption[prod_id]["transaction_count"] += 1 for p in product_consumption.values(): p["total_consumed"] = str(p["total_consumed"]) consumption_list = sorted( product_consumption.values(), key=lambda x: float(x["total_consumed"]), reverse=True, ) payload["warehouse_id"] = str(warehouse.id) payload["warehouse_name"] = warehouse.warehouse_name payload["from_date"] = from_date_str payload["to_date"] = to_date_str payload["consumption_list"] = consumption_list payload["total_products_consumed"] = len(consumption_list) return Response({"message": "true", "payload": payload}, status=200) except Exception as e: print(e) return Response({"message": "false", "payload": payload}, status=500) # --------------------------------------------------------------------------- # 3. Purchase Requisition Summary Report # --------------------------------------------------------------------------- @api_view(['POST']) @authentication_classes([TokenAuthentication]) @permission_classes([IsAuthenticated]) @requires_permission('reports.procurement_reports.view') def purchase_requisition_report(request): """ Summary of purchase requisitions within a date range. Input: serial_number, warehouse_id ("all" or id), from_date, to_date """ from warehouse_management.models import PurchaseRequisition, PurchaseRequisitionInstance company_serial_number = request.data["serial_number"] warehouse_id = request.data.get("warehouse_id", "all") from_date_str = request.data["from_date"] to_date_str = request.data["to_date"] active_user = request.user payload = {} try: CompanyProfile.objects.get(company_serial_number=company_serial_number) from_dt = _parse_date(from_date_str) to_dt = _parse_date(to_date_str) + timedelta(days=1) qs = PurchaseRequisition.objects.filter( recycle_bin=False, created_on__gte=from_dt, created_on__lt=to_dt, ).select_related('warehouse', 'created_by').prefetch_related('purchase_requisition_instances__product') if warehouse_id != "all": qs = qs.filter(warehouse_id=int(warehouse_id)) pr_list = [] total_approved = 0 total_pending = 0 for pr in qs.order_by("-created_on"): instances = pr.purchase_requisition_instances.filter(recycle_bin=False) items_count = instances.count() purchased_count = instances.filter(purchase_requisition_items_purchased=True).count() status = "approved" if pr.purchase_requisition_approved else "pending" if status == "approved": total_approved += 1 else: total_pending += 1 pr_list.append({ "purchase_requisition_id": str(pr.id), "purchase_requisition_number": pr.purchase_requisition_number, "warehouse_name": pr.warehouse.warehouse_name if pr.warehouse else "", "description": pr.purchase_requisition_description, "status": status, "items_count": items_count, "items_purchased_count": purchased_count, "created_by": f'{pr.created_by.first_name} {pr.created_by.last_name}' if pr.created_by else "", "created_on": datetime.strftime( pr.created_on.astimezone(TARGET_TZ), DATE_FMT) if pr.created_on else "", }) payload["from_date"] = from_date_str payload["to_date"] = to_date_str payload["purchase_requisition_list"] = pr_list payload["summary"] = { "total": len(pr_list), "approved": total_approved, "pending": total_pending, } return Response({"message": "true", "payload": payload}, status=200) except Exception as e: print(e) return Response({"message": "false", "payload": payload}, status=500) # --------------------------------------------------------------------------- # 4. Purchase Order Summary Report # --------------------------------------------------------------------------- @api_view(['POST']) @authentication_classes([TokenAuthentication]) @permission_classes([IsAuthenticated]) @requires_permission('reports.procurement_reports.view') def purchase_order_report(request): """ Summary of purchase orders within a date range. Input: serial_number, from_date, to_date """ company_serial_number = request.data["serial_number"] from_date_str = request.data["from_date"] to_date_str = request.data["to_date"] active_user = request.user payload = {} try: company_profile = CompanyProfile.objects.get(company_serial_number=company_serial_number) from_dt = _parse_date(from_date_str) to_dt = _parse_date(to_date_str) + timedelta(days=1) qs = PurchaseOrder.objects.filter( company_profile=company_profile, recycle_bin=False, created_on__gte=from_dt, created_on__lt=to_dt, ).select_related('created_by').prefetch_related( 'purchase_order_product_instances__purchase_product', 'purchase_order_product_instances__supplier', ) po_list = [] total_value = 0.0 total_approved = 0 total_pending = 0 total_delivered = 0 for po in qs.order_by("-created_on"): instances = po.purchase_order_product_instances.filter(recycle_bin=False) items_count = instances.count() delivered_count = instances.filter(product_purchase_delivered=True).count() po_value = float(po.purchase_value_overall) total_value += po_value if po.purchase_order_approved: total_approved += 1 else: total_pending += 1 if items_count > 0 and delivered_count == items_count: total_delivered += 1 suppliers = list({inst.supplier.supplier_name for inst in instances if inst.supplier}) po_list.append({ "purchase_order_id": str(po.id), "purchase_order_number": po.purchase_order_number, "purchase_value_overall": po.purchase_value_overall, "approved": "true" if po.purchase_order_approved else "false", "items_count": items_count, "items_delivered_count": delivered_count, "suppliers": suppliers, "created_by": f'{po.created_by.first_name} {po.created_by.last_name}' if po.created_by else "", "created_on": datetime.strftime( po.created_on.astimezone(TARGET_TZ), DATE_FMT) if po.created_on else "", }) payload["from_date"] = from_date_str payload["to_date"] = to_date_str payload["purchase_order_list"] = po_list payload["summary"] = { "total": len(po_list), "approved": total_approved, "pending": total_pending, "fully_delivered": total_delivered, "total_value": str(round(total_value, 2)), } return Response({"message": "true", "payload": payload}, status=200) except Exception as e: print(e) return Response({"message": "false", "payload": payload}, status=500) # --------------------------------------------------------------------------- # 5. Supplier Performance Report # --------------------------------------------------------------------------- @api_view(['POST']) @authentication_classes([TokenAuthentication]) @permission_classes([IsAuthenticated]) @requires_permission('reports.procurement_reports.view') def supplier_performance_report(request): """ Delivery performance and lead time per supplier within a date range. Input: serial_number, from_date, to_date, supplier_id ("all" or id) """ company_serial_number = request.data["serial_number"] from_date_str = request.data["from_date"] to_date_str = request.data["to_date"] supplier_id = request.data.get("supplier_id", "all") active_user = request.user payload = {} try: company_profile = CompanyProfile.objects.get(company_serial_number=company_serial_number) from_dt = _parse_date(from_date_str) to_dt = _parse_date(to_date_str) + timedelta(days=1) instances_qs = ProductPurchaseInstance.objects.filter( recycle_bin=False, purchase_order__company_profile=company_profile, created_on__gte=from_dt, created_on__lt=to_dt, ).select_related('supplier', 'purchase_order', 'purchase_product') if supplier_id != "all": instances_qs = instances_qs.filter(supplier_id=int(supplier_id)) supplier_stats = {} for inst in instances_qs: if inst.supplier is None: continue sup_id = str(inst.supplier.id) if sup_id not in supplier_stats: supplier_stats[sup_id] = { "supplier_id": sup_id, "supplier_name": inst.supplier.supplier_name, "supplier_email": inst.supplier.supplier_email, "total_orders": 0, "delivered_orders": 0, "pending_orders": 0, "total_value": 0.0, "total_paid": 0.0, "payment_settled_count": 0, } stats = supplier_stats[sup_id] stats["total_orders"] += 1 stats["total_value"] += float(inst.purchase_value_overall) stats["total_paid"] += float(inst.purchase_amount_paid_to_supplier) if inst.product_purchase_delivered: stats["delivered_orders"] += 1 else: stats["pending_orders"] += 1 if inst.supplier_payment_settled: stats["payment_settled_count"] += 1 result_list = [] for stats in supplier_stats.values(): total = stats["total_orders"] delivery_rate = (stats["delivered_orders"] / total * 100) if total > 0 else 0 stats["delivery_rate_percent"] = str(round(delivery_rate, 1)) stats["total_value"] = str(round(stats["total_value"], 2)) stats["total_paid"] = str(round(stats["total_paid"], 2)) result_list.append(stats) result_list.sort(key=lambda x: float(x["total_value"]), reverse=True) payload["from_date"] = from_date_str payload["to_date"] = to_date_str payload["supplier_performance_list"] = result_list payload["total_suppliers"] = len(result_list) return Response({"message": "true", "payload": payload}, status=200) except Exception as e: print(e) return Response({"message": "false", "payload": payload}, status=500) # --------------------------------------------------------------------------- # 6. Supplier Statement of Account # --------------------------------------------------------------------------- @api_view(['POST']) @authentication_classes([TokenAuthentication]) @permission_classes([IsAuthenticated]) @requires_permission('reports.procurement_reports.view') def supplier_statement(request): """ Statement of account for a specific supplier within a date range. Input: serial_number, supplier_id, from_date, to_date """ company_serial_number = request.data["serial_number"] supplier_id = request.data["supplier_id"] from_date_str = request.data["from_date"] to_date_str = request.data["to_date"] active_user = request.user payload = {} try: company_profile = CompanyProfile.objects.get(company_serial_number=company_serial_number) supplier = Supplier.objects.get(id=int(supplier_id)) from_dt = _parse_date(from_date_str) to_dt = _parse_date(to_date_str) + timedelta(days=1) instances = ProductPurchaseInstance.objects.filter( supplier=supplier, recycle_bin=False, purchase_order__company_profile=company_profile, created_on__gte=from_dt, created_on__lt=to_dt, ).select_related('purchase_order', 'purchase_product').order_by('created_on') statement_lines = [] running_balance = 0.0 total_invoiced = 0.0 total_paid = 0.0 for inst in instances: invoiced = float(inst.purchase_value_overall) paid = float(inst.purchase_amount_paid_to_supplier) outstanding = invoiced - paid running_balance += outstanding total_invoiced += invoiced total_paid += paid statement_lines.append({ "purchase_order_number": inst.purchase_order.purchase_order_number if inst.purchase_order else "", "product_name": inst.purchase_product.product_name if inst.purchase_product else "", "quantity_purchased": inst.quantity_purchased, "quantity_delivered": inst.quantity_delivered, "cost_currency": inst.cost_currency, "purchase_value_per_unit": inst.purchase_value_per_unit, "purchase_value_overall": inst.purchase_value_overall, "amount_paid": inst.purchase_amount_paid_to_supplier, "outstanding": str(round(outstanding, 2)), "running_balance": str(round(running_balance, 2)), "payment_settled": "true" if inst.supplier_payment_settled else "false", "delivered": "true" if inst.product_purchase_delivered else "false", "date": datetime.strftime( inst.created_on.astimezone(TARGET_TZ), DATE_FMT) if inst.created_on else "", }) supplier_map = { "supplier_id": str(supplier.id), "supplier_name": supplier.supplier_name, "supplier_email": supplier.supplier_email, "supplier_phone": supplier.supplier_phone, "supplier_address": supplier.supplier_address, } payment_details_qs = supplier.supplier_payment_details.first() if payment_details_qs: supplier_map["bank_name"] = payment_details_qs.banking_institution_name supplier_map["bank_account_name"] = payment_details_qs.bank_account_name supplier_map["bank_account_number"] = payment_details_qs.bank_account_number payload["supplier"] = supplier_map payload["from_date"] = from_date_str payload["to_date"] = to_date_str payload["statement_lines"] = statement_lines payload["summary"] = { "total_invoiced": str(round(total_invoiced, 2)), "total_paid": str(round(total_paid, 2)), "total_outstanding": str(round(running_balance, 2)), "transactions_count": len(statement_lines), } return Response({"message": "true", "payload": payload}, status=200) except Exception as e: print(e) return Response({"message": "false", "payload": payload}, status=500)