from django.db.models import Q, Count, Sum, Avg, F, Value, FloatField from django.db.models.functions import Coalesce from django.core.cache import cache from datetime import datetime, timedelta from .models import MinistryReportSettings from organisations.models import Organisation, OrganisationBranch from customers.models import Customer, CustomerType from savings.models import SavingAccount, SavingsProduct, SavingAccountTransactions from loans.models import LoanApplication, LoanProduct from shares.models import SharesTransaction from ledgers.models import SystemTransactions from savings.savings_bal_helper import get_account_balance def get_ministry_report_cache_key(organisation_id, report_type='overview'): """Generate cache key for ministry reports""" return f"ministry_report_{report_type}_{organisation_id}" def get_preloaded_ministry_data(organisation_id, start_date=None, end_date=None, branch_id=None): """ Preload all ministry report data for faster frontend loading """ cache_key = get_ministry_report_cache_key(organisation_id, 'preloaded') # Try to get from cache first (cache for 5 minutes) cached_data = cache.get(cache_key) if cached_data: return cached_data try: organisation = Organisation.objects.get(id=organisation_id) except Organisation.DoesNotExist: return {"error": "Organization not found"} # Get branches branches_qs = OrganisationBranch.objects.filter(branch_organisation_id=organisation_id) if branch_id and int(branch_id) > 0: branches_qs = branches_qs.filter(id=branch_id) branch_ids = list(branches_qs.values_list('id', flat=True)) # Organization basic info org_data = { "id": organisation.id, "name": organisation.name, "short_name": organisation.short_name, "email": organisation.email, "phone_number": organisation.phone_number, "address": organisation.address, "city": organisation.city, "total_branches": len(branch_ids) } # Get ministry report settings - use full serializer to maintain compatibility ministry_settings = MinistryReportSettings.objects.filter(organisation_id=organisation_id).first() settings_data = None if ministry_settings: from .serializers import MinistryReportSettingsSerializer settings_data = MinistryReportSettingsSerializer(ministry_settings).data # Customer statistics with optimized queries customers_qs = Customer.objects.filter(is_deleted=False) if branch_ids: customers_qs = customers_qs.filter(customer_branch_id__in=branch_ids) else: customers_qs = customers_qs.filter(customer_branch__branch_organisation_id=organisation_id) if start_date: customers_qs = customers_qs.filter(date_added__date__gte=start_date) if end_date: customers_qs = customers_qs.filter(date_added__date__lte=end_date) # Single query for customer stats customer_stats = customers_qs.aggregate( total_customers=Count('id'), male=Count('id', filter=Q(gender='M')), female=Count('id', filter=Q(gender='F')), other=Count('id', filter=~Q(gender__in=['M', 'F'])) ) # Customer types customer_types = list(customers_qs.values('branch_customer_type__customer_type').annotate( count=Count('id') ).order_by('-count')) # Savings statistics with optimized queries savings_qs = SavingAccount.objects.filter(deleted=False) if branch_ids: savings_qs = savings_qs.filter(customer_branch_id__in=branch_ids) else: savings_qs = savings_qs.filter(customer_branch__branch_organisation_id=organisation_id) savings_stats = savings_qs.aggregate( total_accounts=Count('id'), active_accounts=Count('id', filter=Q(status='active')) ) # Savings transactions deposits_qs = SavingAccountTransactions.objects.filter(transaction_type='deposit') withdrawals_qs = SavingAccountTransactions.objects.filter(transaction_type='withdrawal') if branch_ids: deposits_qs = deposits_qs.filter(customer_account__customer_branch_id__in=branch_ids) withdrawals_qs = withdrawals_qs.filter(customer_account__customer_branch_id__in=branch_ids) else: deposits_qs = deposits_qs.filter(customer_account__customer_branch__branch_organisation_id=organisation_id) withdrawals_qs = withdrawals_qs.filter(customer_account__customer_branch__branch_organisation_id=organisation_id) if start_date: deposits_qs = deposits_qs.filter(transaction__record_date__date__gte=start_date) withdrawals_qs = withdrawals_qs.filter(transaction__record_date__date__gte=start_date) if end_date: deposits_qs = deposits_qs.filter(transaction__record_date__date__lte=end_date) withdrawals_qs = withdrawals_qs.filter(transaction__record_date__date__lte=end_date) savings_transactions = { 'total_deposits': deposits_qs.aggregate(total=Coalesce(Sum('transaction__amount'), Value(0.0)))['total'], 'total_withdrawals': withdrawals_qs.aggregate(total=Coalesce(Sum('transaction__amount'), Value(0.0)))['total'] } savings_transactions['net_savings'] = float(savings_transactions['total_deposits'] or 0) - float(savings_transactions['total_withdrawals'] or 0) # Savings products with balances savings_products = [] for product in SavingsProduct.objects.filter(saving_product_org_id=organisation_id): accounts = product.saving_account_account_product.filter(status='active') if branch_ids: accounts = accounts.filter(customer_branch_id__in=branch_ids) total_members = accounts.values('account_customer').distinct().count() total_accounts = accounts.count() # Calculate total balance efficiently total_balance = 0 for acc in accounts[:100]: # Limit to prevent timeout try: balance = get_account_balance(acc)['balance_raw'] total_balance += balance except: continue savings_products.append({ 'id': product.id, 'product_name': product.product_name, 'min_balance': product.min_balance, 'total_accounts': total_accounts, 'total_members': total_members, 'total_balance': total_balance }) # Loan statistics loans_qs = LoanApplication.objects.all() if branch_ids: loans_qs = loans_qs.filter(organisation_branch_id__in=branch_ids) else: loans_qs = loans_qs.filter(organisation_branch__branch_organisation_id=organisation_id) if start_date: loans_qs = loans_qs.filter(loan_date__date__gte=start_date) if end_date: loans_qs = loans_qs.filter(loan_date__date__lte=end_date) loan_stats = loans_qs.aggregate( total_applications=Count('id'), pending=Count('id', filter=Q(status='pending')), approved=Count('id', filter=Q(status='approved')), disbursed=Count('id', filter=Q(status='disbursed')), rejected=Count('id', filter=Q(status='rejected')), cleared=Count('id', filter=Q(status='cleared_off')), total_loan_amount=Coalesce(Sum('loan_amount'), Value(0.0)), total_disbursed=Coalesce(Sum('loan_amount', filter=Q(status__in=['disbursed','cleared_off'])), Value(0.0)) ) # Loan products loan_products = list(LoanProduct.objects.filter(organisation_id=organisation_id).values( 'id','product_name','int_rate' ).annotate( total_issued=Count( 'loan_application_product', filter=Q(loan_application_product__status__in=['disbursed','cleared_off']) ), total_amount=Coalesce( Sum('loan_application_product__loan_amount', filter=Q(loan_application_product__status__in=['disbursed','cleared_off'])), Value(0.0) ) )) # Shares statistics shares_qs = SharesTransaction.objects.all() if branch_ids: shares_qs = shares_qs.filter(shareholder__customer__customer_branch_id__in=branch_ids) else: shares_qs = shares_qs.filter(shareholder__customer__customer_branch__branch_organisation_id=organisation_id) if start_date: shares_qs = shares_qs.filter(system_transaction__record_date__date__gte=start_date) if end_date: shares_qs = shares_qs.filter(system_transaction__record_date__date__lte=end_date) shares_stats = shares_qs.aggregate( total_purchases=Coalesce(Sum('system_transaction__amount', filter=Q(transaction_type='purchase')), Value(0.0)), total_withdrawals=Coalesce(Sum('system_transaction__amount', filter=Q(transaction_type='withdrawal')), Value(0.0)), total_transactions=Count('id') ) # Financial performance income_total = SystemTransactions.objects.filter( credit_chart__account_organisation_id=organisation_id, credit_chart__account_line='income', deleted=False ) expense_total = SystemTransactions.objects.filter( debit_chart__account_organisation_id=organisation_id, debit_chart__account_line='expenses', deleted=False ) if start_date: income_total = income_total.filter(record_date__date__gte=start_date) expense_total = expense_total.filter(record_date__date__gte=start_date) if end_date: income_total = income_total.filter(record_date__date__lte=end_date) expense_total = expense_total.filter(record_date__date__lte=end_date) financial_stats = { 'total_income': float(income_total.aggregate(total=Coalesce(Sum('amount'), Value(0.0)))['total'] or 0), 'total_expenses': float(expense_total.aggregate(total=Coalesce(Sum('amount'), Value(0.0)))['total'] or 0) } financial_stats['net_income'] = financial_stats['total_income'] - financial_stats['total_expenses'] # Compile all data preloaded_data = { "organization_info": org_data, "ministry_settings": settings_data, "customer_statistics": { "total_customers": customer_stats['total_customers'], "gender_breakdown": { "male": customer_stats['male'], "female": customer_stats['female'], "other": customer_stats['other'] }, "customer_types": customer_types }, "savings_statistics": { "total_accounts": savings_stats['total_accounts'], "active_accounts": savings_stats['active_accounts'], "total_deposits": float(savings_transactions['total_deposits'] or 0), "total_withdrawals": float(savings_transactions['total_withdrawals'] or 0), "net_savings": savings_transactions['net_savings'] }, "savings_products": savings_products, "loan_statistics": { "total_applications": loan_stats['total_applications'], "status_breakdown": { "pending": loan_stats['pending'], "approved": loan_stats['approved'], "disbursed": loan_stats['disbursed'], "rejected": loan_stats['rejected'], "cleared": loan_stats['cleared'] }, "total_loan_amount": float(loan_stats['total_loan_amount'] or 0), "total_disbursed_amount": float(loan_stats['total_disbursed'] or 0) }, "loan_products": loan_products, "shares_statistics": { "total_purchases": float(shares_stats['total_purchases'] or 0), "total_withdrawals": float(shares_stats['total_withdrawals'] or 0), "net_shares": float((shares_stats['total_purchases'] or 0) - (shares_stats['total_withdrawals'] or 0)), "total_transactions": shares_stats['total_transactions'] }, "financial_performance": financial_stats, "last_updated": datetime.now().isoformat() } # Cache for 5 minutes cache.set(cache_key, preloaded_data, 300) return preloaded_data def invalidate_ministry_report_cache(organisation_id): """Invalidate ministry report cache when data changes""" cache_keys = [ get_ministry_report_cache_key(organisation_id, 'overview'), get_ministry_report_cache_key(organisation_id, 'preloaded') ] cache.delete_many(cache_keys)