from rest_framework.views import APIView from rest_framework.response import Response from rest_framework import status from rest_framework import viewsets from rest_framework.filters import SearchFilter, OrderingFilter from django.db.models import Q, Max, Count, Sum, Avg, F from django.db.models.functions import Coalesce from django.db.models import Value, FloatField from ledgers.ledgers_helper import get_transactional_charts, get_account_lines, get_income_statement_lines, get_balance_sheet_lines from .reports_helper import * from .savings_reports_helper import * from questbanker_api.utils import get_current_user from ledgers.models import OrganisationSubAccount, Currencies from organisations.models import Organisation from savings.serializers import FixedDepositSerializer from savings.models import FixedDeposit, SavingAccount, SavingAccountTransactions from customers.models import Customer, CustomerType from loans.models import LoanApplication from shares.models import SharesTransaction from organisations.models import OrganisationBranch from datetime import datetime from exservices.models import MemberSmsSubscription,SMSRequest,UserSms,OrganisationFreeSmsAward from rest_framework.pagination import PageNumberPagination from custom_searches.serializers import * from integrations.models import * from general.permissions import IsUNCDFPartner from rest_framework.permissions import IsAuthenticated from .models import MinistryReportSettings from .serializers import MinistryReportSettingsSerializer from .ministry_reports_helper import get_preloaded_ministry_data, invalidate_ministry_report_cache from django.shortcuts import get_object_or_404 import pandas as pd # Added for SavingsAccountStatusReportView from django.db.models.functions import Coalesce # Added for MinistryReportOverviewView from django.db.models import Value, FloatField # Added for MinistryReportOverviewView from django.core.management import call_command class SavingsBalanceCacheRefreshView(APIView): """ Trigger an on-demand cache refresh for the authenticated user's organisation. POST /api/reports/savings-cache/refresh/ """ permission_classes = [IsAuthenticated] def post(self, request): organisation_id = get_current_user(request, 'organisation_id', None) if not organisation_id: return Response({'error': 'organisation_id not found'}, status=status.HTTP_400_BAD_REQUEST) as_at = request.data.get('as_at', str(datetime.now().date())) try: call_command('refresh_savings_balance_cache', org_id=organisation_id, as_at=as_at) return Response({'status': 'refreshed', 'organisation_id': organisation_id, 'as_at': as_at}) except Exception as e: return Response({'error': str(e)}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) class AnalyticDashBoardView(APIView): def get(self, request, format=None): organisation_id = get_current_user(self.request, 'organisation_id', None) end = self.request.GET.get('e',None) start = self.request.GET.get('s',None) branch_id = self.request.GET.get('branch_id',None) summary = self.request.GET.get('summary','1') dashboard_summary = { "customer_totals":0, "genders":[], "customers":[], "all_gender_totals":0, "customer_types":[], "customer_data":[], "male_count":0, "female_count":0, "other_count":0, "currency": Currencies.objects.get(pk = Organisation.objects.get(pk = organisation_id).base_currency).currency_code } if int(summary) == 1: dashboard_summary = get_customers_by_type(dashboard_summary,{"organisation_id":organisation_id,"branch_id":branch_id,"start":start,"end":end}) dashboard_summary = get_customers_by_gender(dashboard_summary,{"organisation_id":organisation_id,"branch_id":branch_id}) dashboard_summary = get_sacco_dashboard_summary(dashboard_summary,{"organisation_id":organisation_id,"branch_id":branch_id}) if int(summary) == 2: dashboard_summary = get_sacco_dashboard_summary(dashboard_summary,{"organisation_id":organisation_id,"branch_id":branch_id}) return Response(dashboard_summary, status=status.HTTP_200_OK) # Create your views here. class GreenFinanceSummaryView(APIView): def get(self, request, format=None): organisation_id = get_current_user(self.request, 'organisation_id', None) # Organisations that subscribe to Green Finance GREEN_FINANCE_ORG_IDS = [1, 33, 24, 39, 21] # Restrict access to these organisations if organisation_id not in GREEN_FINANCE_ORG_IDS: return Response( {"detail": "Green Finance not available for this organisation."}, status=status.HTTP_403_FORBIDDEN ) end = self.request.GET.get('e', None) start = self.request.GET.get('s', None) branch_id = self.request.GET.get('branch_id', None) # Base filter for loans loan_filter = { "classification_item_loan__organisation_branch__branch_organisation__id": organisation_id, "classification_item_loan__is_deleted": False, } if branch_id and int(branch_id) > 0: loan_filter["classification_item_loan__organisation_branch_id"] = branch_id if start and end: loan_filter["classification_item_loan__loan_date__date__gte"] = start loan_filter["classification_item_loan__loan_date__date__lte"] = end # Get classification item loan records item_loans = ( ClassificationItemLoans.objects .filter(**loan_filter) .select_related( "classification_item__classification", "classification_item_loan" ) ) # Total stats total_loans = item_loans.values("classification_item_loan").distinct().count() total_volume = ( item_loans.aggregate(total=Sum("classification_item_loan__loan_amount"))["total"] or 0 ) # Breakdown by classification with volume indicators = { "adaptation": item_loans.filter( classification_item__classification__name__icontains="adaptation" ), "mitigation": item_loans.filter( classification_item__classification__name__icontains="mitigation" ), "biodiversity_conservation": item_loans.filter( classification_item__classification__name__icontains="biodiversity conservation" ), } # Prepare final counts and volumes indicator_summary = {} for key, queryset in indicators.items(): indicator_summary[key] = { "count": queryset.count(), "volume": queryset.aggregate( total=Sum("classification_item_loan__loan_amount") )["total"] or 0 } summary = { "total_loans": total_loans, "total_volume": total_volume, "adaptation_count": indicator_summary["adaptation"]["count"], "adaptation_volume": indicator_summary["adaptation"]["volume"], "mitigation_count": indicator_summary["mitigation"]["count"], "mitigation_volume": indicator_summary["mitigation"]["volume"], "biodiversity_conservation_count": indicator_summary["biodiversity_conservation"]["count"], "biodiversity_conservation_volume": indicator_summary["biodiversity_conservation"]["volume"], } return Response(summary, status=status.HTTP_200_OK) class smsDashBoardView(APIView): def get(self, request, format=None): organisation_id = get_current_user(self.request, 'organisation_id', None) end = self.request.GET.get('e',None) start = self.request.GET.get('s',None) sms_summary = {} purchases_filter = {"organisation__id": organisation_id,"status":"approved"} free_sms_filter = {"organisation__id": organisation_id} used_filter = {"branch__branch_organisation__id": organisation_id,"is_sent":True} member_filter = {"customer_branch__branch_organisation__id": organisation_id} subcribers_filter = {"org_subscription__organisation__id": organisation_id} purchases_bf_filter = {"organisation__id": organisation_id,"status":"approved"} used_bf_filter = {"branch__branch_organisation__id": organisation_id,"is_sent":True} free_bf_sms_filter = {"organisation__id": organisation_id} if start: purchases_bf_filter['date_added__date__lt'] = start purchases_filter['date_added__date__gte'] = start free_sms_filter['date_added__date__gte'] = start free_bf_sms_filter['date_added__date__lt'] = start used_filter['date_added__date__gte'] = start used_bf_filter['date_added__date__lt'] = start if end: purchases_filter['date_added__date__lte'] = end free_sms_filter['date_added__date__lte'] = end used_filter['date_added__date__lte'] = end member_filter['date_added__date__lte'] = end subcribers_filter['date_added__date__lte'] = end members = Customer.objects.filter(**member_filter).values( "id").count() if not members: members = 0 subscribers = MemberSmsSubscription.objects.filter(**subcribers_filter).values( "customer").distinct().count() if not subscribers: subscribers = 0 purchases = SMSRequest.objects.filter(**purchases_filter).aggregate(total_sum=Sum('approved_amount'))['total_sum'] if not purchases: purchases = 0 purchases_bf = SMSRequest.objects.filter(**purchases_bf_filter).aggregate(total_sum=Sum('approved_amount'))['total_sum'] if not purchases_bf: purchases_bf = 0 free_sms = OrganisationFreeSmsAward.objects.filter(**free_sms_filter).aggregate(total_sum=Sum('amount'))['total_sum'] if not free_sms: free_sms = 0 free_bf_sms = OrganisationFreeSmsAward.objects.filter(**free_bf_sms_filter).aggregate(total_sum=Sum('amount'))['total_sum'] if not free_bf_sms: free_bf_sms = 0 used_sms = UserSms.objects.filter(**used_filter).aggregate(total_sum=Sum('base_cost'))['total_sum'] if not used_sms: used_sms = 0 used_bf_sms = UserSms.objects.filter(**used_bf_filter).aggregate(total_sum=Sum('base_cost'))['total_sum'] if not used_bf_sms: used_bf_sms = 0 used_by_type = UserSms.objects.filter(**used_filter).values( "sms_type__sms_type_key", "sms_type__sms_type_name").annotate(total_sum=Sum('base_cost')) if not used_by_type: sms_summary["used_by_type"] = [] sms_summary["members"] = members sms_summary["subscribers"] = subscribers sms_summary["purchases"] = purchases sms_summary["free_sms"] = free_sms sms_summary["used_sms"] = used_sms sms_summary["un_subscribers"] = members - subscribers sms_summary["remaining"] = (purchases + purchases_bf + free_sms + free_bf_sms) - (used_sms + used_bf_sms) if sms_summary["remaining"] < 0: sms_summary["remaining"] = 0 if used_by_type: sms_summary["used_by_type"] = used_by_type sms_summary["purchases"] = purchases + free_sms return Response(sms_summary, status=status.HTTP_200_OK) class TrialBalanceView(APIView): def get(self, request, format=None): tb_data = { 'assets': [], 'liabilities': [], 'capital': [], 'income': [], 'expenses': [] } organisation_id = get_current_user(self.request, 'organisation_id', None) branch_id = self.request.GET.get('branch', None) if not branch_id: branch_id = get_current_user(self.request, 'organisation_branch_id', None) if int(branch_id) == 0: branch_id = ','.join( map(str, OrganisationBranch.objects.filter( branch_organisation_id=organisation_id ).values_list('id', flat=True)) ) # Fetch query params safely start = self.request.GET.get('s') end = self.request.GET.get('e') coverage = self.request.GET.get('coverage') # 🔒 Handle missing start/end dates gracefully (for budgeting) if not start: # Default to start of current year start = datetime.now().strftime('%Y-01-01') if not end: # Default to today end = datetime.now().strftime('%Y-%m-%d') # Fetch system charts account_lines = get_account_lines() for account_line in account_lines: accounts = OrganisationSubAccount.objects.filter( account_line=account_line, account_organisation=organisation_id, parent_id__isnull=True ).order_by('account_code').values('id', 'account_code', 'account_name') for account in accounts: line_charts = get_transactional_charts( organisation_id, account_line, account['account_code'] ) transactions = get_chart_child_transactions( line_charts, branch_id, start, end, coverage ) account['transactions'] = sorted( transactions, key=lambda x: x.get('id', None) ) tb_data[account_line].append(account) # Inject retained earnings chart = OrganisationSubAccount.objects.filter( account_code='sys-323', account_organisation=organisation_id ) if chart.exists(): chart_transactions = OrganisationSubAccountSerializer( chart[0], context={ 'branch_id': branch_id, 'start_date': start, 'end_date': end, 'type': 'balanced' } ).data # 🧩 Safely compute previous year end only if start exists start_year = int(start.split('-')[0]) prev_year_end = f"{start_year - 1}-12-31" retained_earnings = get_period_earnings( organisation_id, branch_id, '2000-01-01', prev_year_end ) tb_data = inject_retained_earnings( tb_data, retained_earnings, chart_transactions ) return Response(tb_data, status=status.HTTP_200_OK) class IncomeStatementView(APIView): def get(self, request, format=None): tb_data = { 'income': [], 'expenses': [] } organisation_id = get_current_user(self.request, 'organisation_id', None) branch_id = self.request.GET.get('branch', None) if not branch_id: branch_id = get_current_user(self.request, 'organisation_branch_id', None) if int(branch_id) == 0: branch_id = ',' . join(map(str, OrganisationBranch.objects.filter(branch_organisation_id=organisation_id).values_list('id', flat=True))) #Cash account details end = self.request.GET.get('e') start = self.request.GET.get('s') coverage = self.request.GET.get('coverage') # Fetch system charts account_lines = get_income_statement_lines() for account_line in account_lines: accounts = OrganisationSubAccount.objects.filter(account_line=account_line, account_organisation=organisation_id, parent_id__isnull=True).order_by('id').values('id', 'account_code', 'account_name') for account in accounts: line_charts = get_transactional_charts(organisation_id, account_line, account['account_code']) transactions = get_chart_child_transactions(line_charts, branch_id, start, end, coverage) account['transactions'] = sorted(transactions, key=lambda x: x.get('id', None)) tb_data[account_line].append(account) return Response(tb_data, status=status.HTTP_200_OK) class BalanceSheetView(APIView): def get(self, request, format=None): tb_data = { 'assets': [], 'capital': [], 'liabilities': [] } organisation_id = get_current_user(self.request, 'organisation_id', None) branch_id = self.request.GET.get('branch', None) if not branch_id: branch_id = get_current_user(self.request, 'organisation_branch_id', None) if int(branch_id) == 0: branch_id = ',' . join(map(str, OrganisationBranch.objects.filter(branch_organisation_id=organisation_id).values_list('id', flat=True))) #Cash account details end = self.request.GET.get('e') # Use end date for both start and end for Balance Sheet items. start = self.request.GET.get('s') coverage = 'balanced' # Fetch system charts account_lines = get_balance_sheet_lines() for account_line in account_lines: accounts = OrganisationSubAccount.objects.filter(account_line=account_line, account_organisation=organisation_id, parent_id__isnull=True).order_by('id').values('id', 'account_code', 'account_name') for account in accounts: line_charts = get_transactional_charts(organisation_id, account_line, account['account_code']) transactions = get_chart_child_transactions(line_charts, branch_id, start, end, coverage) account['transactions'] = sorted(transactions, key=lambda x: x.get('id', None)) tb_data[account_line].append(account) reference_chart = OrganisationSubAccount.objects.filter(account_code='sys-33', account_organisation=organisation_id) period_earnings = get_period_earnings(organisation_id, branch_id, start, end) if period_earnings['forward'] + period_earnings['btn'] != 0: tb_data['capital'][2]['transactions'] = [ { "id": reference_chart[0].id, "account_code": "sys-33", "account_name": "Period Net Incomes", "sub_accounts": [ { "id": reference_chart[0].id, "account_code": "sys-331", "account_name": "Current Period Earnings", "balance_bf": { "debits": 0, "credits": 0, "balance": period_earnings['forward'], "balance_raw": period_earnings['forward'] }, "balance_btn": { "debits": period_earnings['debits'], "credits": period_earnings['credits'], "balance": period_earnings['btn'], "balance_raw": period_earnings['btn'] } } ] } ] # inject retained earnings # Get manual postings chart = OrganisationSubAccount.objects.filter(account_code='sys-323', account_organisation=organisation_id) chart_transactions = OrganisationSubAccountSerializer( chart[0], context={ 'branch_id': branch_id, 'start_date': start, 'end_date': end, 'type': 'balanced' } ).data # Get automated postings end = str(int(start.split('-')[0]) - 1) + '-12-31' retained_earnings = get_period_earnings(organisation_id, branch_id, '2000-01-01', end) tb_data = inject_retained_earnings(tb_data, retained_earnings, chart_transactions) return Response(tb_data, status=status.HTTP_200_OK) class SavingsReportFiltersView(APIView): def get(self,request,format=None): organisation_id = get_current_user(self.request, 'organisation_id', None) return Response(get_savings_filters(organisation_id)) class SavingsAccountBalancesView(APIView): def get(self, request): filter_1 = [] filter_2 = [] results = [] organisation_id = get_current_user(self.request, 'organisation_id', 1) organisation_branch_id = get_current_user(self.request, 'organisation_branch_id', 1) report_type = request.GET.get('report_type', "balance") include_zero_balance = request.GET.get("include_zero_balance", "false").lower() == "true" if request.GET.get('filter_1'): filter_1 = list(request.GET.get('filter_1').split(',')) if request.GET.get('filter_2'): filter_2 = list(request.GET.get('filter_2').split(',')) filter_type = self.request.GET.get('filter') if filter_type == 'product-branch': results = filter_savings_by_product_branch({ "filter_1": filter_1, "filter_2": filter_2, "start": request.GET.get('s'), "as_at": request.GET.get('e'), "organisation_id": organisation_id, "organisation_branch_id": organisation_branch_id, "report_type": report_type }) elif filter_type == 'gender-product': results = filter_savings_by_gender_product({ "filter_1": filter_1, "filter_2": filter_2, "start": request.GET.get('s'), "as_at": request.GET.get('e'), "organisation_id": organisation_id, "organisation_branch_id": organisation_branch_id, "report_type": report_type }) elif filter_type == 'client_type-product': results = filter_savings_by_client_type_product({ "filter_1": filter_1, "filter_2": filter_2, "start": request.GET.get('s'), "as_at": request.GET.get('e'), "organisation_id": organisation_id, "organisation_branch_id": organisation_branch_id, "report_type": report_type }) elif filter_type == 'gender-branch': results = filter_savings_by_gender_branch({ "filter_1": filter_1, "filter_2": filter_2, "start": request.GET.get('s'), "as_at": request.GET.get('e'), "organisation_id": organisation_id, "organisation_branch_id": organisation_branch_id, "report_type": report_type }) # ✅ Correct zero-balance filtering inside nested structure if not include_zero_balance and results: for r in results: if "filter_2" in r: for f2 in r["filter_2"]: if "savings" in f2 and isinstance(f2["savings"], list): # Keep only accounts with positive balance f2["savings"] = [ s for s in f2["savings"] if float(s.get("balance_raw", 0)) > 0 ] # Remove empty filter_2 entries results = [ r for r in results if any(len(f2.get("savings", [])) > 0 for f2 in r.get("filter_2", [])) ] return Response({"results": results, "count": len(results)}) class SavingsAccountBalancesView_old(APIView): def get(self, request): filter_1 = [] filter_2 = [] page_size = 100 results = [] count = 0 paginator = PageNumberPagination() search = self.request.GET.get("search", None) organisation_id = get_current_user(self.request, 'organisation_id', None) savings_filter = {"customer_branch__branch_organisation__id":organisation_id} if self.request.GET.get('page_size'): page_size = self.request.GET.get('page_size') if request.GET.get('filter_1'): filter_1 = list(request.GET.get('filter_1').split(',')) if request.GET.get('filter_2'): filter_2 = list(request.GET.get('filter_2').split(',')) paginator.page_size = page_size if self.request.GET.get('filter') == 'gender-product': savings_filter["account_product__id__in"] = filter_1 savings_filter["account_customer__gender__in"] = filter_2 savings_accounts = [] if search and len(search) > 0: savings_accounts = SavingAccount.objects.filter(Q(account_customer__member_number__icontains=search) | Q(account_customer__name__icontains=search),**savings_filter) else: savings_accounts = SavingAccount.objects.filter(**savings_filter) if page_size == 999999999999: paginated_accounts = savings_accounts else: paginated_accounts = paginator.paginate_queryset(savings_accounts, request) count = len(savings_accounts) results = filter_savings_by_gender_product({"filter_1":filter_1,"filter_2":filter_2,"paginated_accounts":paginated_accounts,"as_at":request.GET.get('e')}) if self.request.GET.get('filter') == 'client_type-product': savings_filter["account_product__id__in"] = filter_1 savings_filter["account_customer__branch_customer_type__in"] = filter_2 if search and len(search) > 0: savings_accounts = SavingAccount.objects.filter(Q(account_customer__member_number__icontains=search) | Q(account_customer__name__icontains=search),**savings_filter) else: savings_accounts = SavingAccount.objects.filter(**savings_filter) if page_size == 999999999999: paginated_accounts = savings_accounts else: paginated_accounts = paginator.paginate_queryset(savings_accounts, request) count = len(savings_accounts) results = filter_savings_by_client_type_product({"filter_1":filter_1,"filter_2":filter_2,"paginated_accounts":paginated_accounts,"as_at":request.GET.get('e')}) if self.request.GET.get('filter') == 'product-branch': savings_filter["account_product__id__in"] = filter_1 savings_filter["customer_branch__id__in"] = filter_2 if search and len(search) > 0: savings_accounts = SavingAccount.objects.filter(Q(account_customer__member_number__icontains=search) | Q(account_customer__name__icontains=search),**savings_filter) else: savings_accounts = SavingAccount.objects.filter(**savings_filter) if page_size == 999999999999: paginated_accounts = savings_accounts else: paginated_accounts = paginator.paginate_queryset(savings_accounts, request) count = len(savings_accounts) results = filter_savings_by_product_branch({"filter_1":filter_1,"filter_2":filter_2,"paginated_accounts":paginated_accounts,"as_at":request.GET.get('e')}) if self.request.GET.get('filter') == 'gender-branch': savings_filter["customer_branch__id__in"] = filter_1 savings_filter["account_customer__gender__in"] = filter_2 if search and len(search) > 0: savings_accounts = SavingAccount.objects.filter(Q(account_customer__member_number__icontains=search) | Q(account_customer__name__icontains=search),**savings_filter) else: savings_accounts = SavingAccount.objects.filter(**savings_filter) if page_size == 999999999999: paginated_accounts = savings_accounts else: paginated_accounts = paginator.paginate_queryset(savings_accounts, request) count = len(savings_accounts) results = filter_savings_by_gender_branch({"filter_1":filter_1,"filter_2":filter_2,"paginated_accounts":paginated_accounts,"as_at":request.GET.get('e')}) return Response({"results":results,"count":count}) class GroupSavingsAccountBalancesView(APIView): def get(self, request): filter_1 = [] filter_2 = [] search = self.request.GET.get("search", None) organisation_id = get_current_user(self.request, 'organisation_id', None) if request.GET.get('filter_1'): filter_1 = list(request.GET.get('filter_1').split(',')) if request.GET.get('filter_2'): filter_2 = list(request.GET.get('filter_2').split(',')) if self.request.GET.get('filter') == 'gender-product': results = filter_group_savings_by_gender_product({"filter_1":filter_1,"filter_2":filter_2,"start":request.GET.get('s'),"end":request.GET.get('e'),"search":search,"organisation_id":organisation_id}) if self.request.GET.get('filter') == 'product-branch': results = filter_group_savings_by_product_branch({"filter_1":filter_1,"filter_2":filter_2,"start":request.GET.get('s'),"end":request.GET.get('e'),"search":search,"organisation_id":organisation_id}) if self.request.GET.get('filter') == 'gender-branch': results = filter_group_savings_by_gender_branch({"filter_1":filter_1,"filter_2":filter_2,"start":request.GET.get('s'),"end":request.GET.get('e'),"search":search,"organisation_id":organisation_id}) return Response({"results":results}) class FixedDepositsReportView(viewsets.ModelViewSet): serializer_class = FixedDepositSerializer filter_backends = (SearchFilter, OrderingFilter ) filterset_fields = ['saving_account__account_customer__name', 'saving_account__account_customer__member_number'] search_fields = ('saving_account__account_customer__name', ) http_method_names = ['get'] def get_queryset(self): organisation_id = get_current_user(self.request, 'organisation_id', None) if organisation_id: as_at = self.request.GET.get('as_at') branch = self.request.GET.get('branch') if int(branch) > 0: return FixedDeposit.objects.annotate( latest_instalment_date=Max( 'fixed_deposit__expected_date', filter=Q(fixed_deposit__deleted=False), ) ).filter(latest_instalment_date__date__lte=as_at, saving_account__account_customer__customer_branch__branch_organisation__id=organisation_id, status='pending', branch_id=branch) return FixedDeposit.objects.annotate( latest_instalment_date=Max( 'fixed_deposit__expected_date', filter=Q(fixed_deposit__deleted=False), ) ).filter(latest_instalment_date__date__lte=as_at, saving_account__account_customer__customer_branch__branch_organisation__id=organisation_id, status='pending') class FixedDepositsClosedReportView(viewsets.ModelViewSet): serializer_class = FixedDepositSerializer filter_backends = (SearchFilter, OrderingFilter ) filterset_fields = ['saving_account__account_customer__name', 'saving_account__account_customer__member_number'] search_fields = ('saving_account__account_customer__name', ) http_method_names = ['get'] def get_queryset(self): end = self.request.GET.get("e", None) start = self.request.GET.get("s", None) branch = self.request.GET.get("branch", None) organisation_id = get_current_user(self.request, 'organisation_id', None) if organisation_id and end and start: if int(branch) > 0: return FixedDeposit.objects.filter(branch_id=branch, closure_transaction__record_date__date__gte=start, closure_transaction__record_date__date__lte=end, saving_account__account_customer__customer_branch__branch_organisation__id=organisation_id, status='closed') return FixedDeposit.objects.filter(closure_transaction__record_date__date__gte=start, closure_transaction__record_date__date__lte=end, saving_account__account_customer__customer_branch__branch_organisation__id=organisation_id, status='closed') class FixedDepositsOutstandingReportView(viewsets.ModelViewSet): serializer_class = FixedDepositSerializer filter_backends = (SearchFilter, OrderingFilter ) filterset_fields = ['saving_account__account_customer__name', 'saving_account__account_customer__member_number'] search_fields = ('saving_account__account_customer__name', ) http_method_names = ['get'] def get_queryset(self): as_at = self.request.GET.get("as_at", None) status = self.request.GET.get("status", 'pending') branch = self.request.GET.get("branch", None) organisation_id = get_current_user(self.request, 'organisation_id', None) if organisation_id: if as_at: as_at = as_at.split(' ')[0] status = status.split(',') if int(branch) > 0: return FixedDeposit.objects.filter(Q(branch_id=branch, reference_transaction__record_date__date__lte=as_at, saving_account__account_customer__customer_branch__branch_organisation__id=organisation_id, status__in=status, closure_transaction__isnull=True) | Q(branch_id=branch, reference_transaction__record_date__date__lte=as_at, saving_account__account_customer__customer_branch__branch_organisation__id=organisation_id, status__in=status, closure_transaction__record_date__date__gt=as_at)) return FixedDeposit.objects.filter(Q(reference_transaction__record_date__date__lte=as_at, saving_account__account_customer__customer_branch__branch_organisation__id=organisation_id, status__in=status, closure_transaction__isnull=True) | Q(reference_transaction__record_date__date__lte=as_at, saving_account__account_customer__customer_branch__branch_organisation__id=organisation_id, status__in=status, closure_transaction__record_date__date__gt=as_at)) class SavingsAccountStatusReportView(APIView): # SavingsAccountSearchSerializer class CustomPagination(PageNumberPagination): page_size = 100 # default page size page_size_query_param = 'page_size' max_page_size = 1000 def get(self, request): count = 0 results = [] start = self.request.GET.get("start", None) end = self.request.GET.get("end", None) organisations = Organisation.objects.all().order_by("name") count = len(organisations) paginator = self.CustomPagination() page = paginator.paginate_queryset(organisations, request) if page is not None: organisations = page if organisations: for organisation in organisations: keys = ["active", "inactive", "dormant"] statuses = { "name": organisation.name, "email": organisation.email, "phone_number": organisation.phone_number, "active": 0, "inactive": 0, "dormant": 0, "active_gender_count": { "male": 0, "female": 0, "other": 0 } } if not end: end = datetime.now().strftime("%Y-%m-%d") filter_parameter = {"open_date__date__lte": end, "organisation_id": organisation.id} if start: filter_parameter["open_date__date__gte"] = start queryset = SavingsAccountSearch.objects.filter(**filter_parameter) if queryset: serializer = SavingsAccountSearchSerializer(queryset, many=True) accounts_data = serializer.data df = pd.DataFrame(accounts_data) if 'status' in df.columns: df_status = df.groupby('status').size().reset_index(name='count') if len(df_status) > 0: filtered_list = df_status.to_dict(orient='records') for row in filtered_list: for key in keys: if key == row["status"]: statuses[key] = row["count"] # Filter for active accounts and count genders if 'gender' in df.columns: df_active = df[df['status'] == 'active'] df_active = df_active.dropna(subset=['gender']) # Drop rows where 'gender' is NaN if not df_active.empty: # Normalize gender values df_active['gender'] = df_active['gender'].map({ 'M': 'male', 'F': 'female' }).fillna('other') gender_counts = df_active['gender'].value_counts().to_dict() # Update statuses with gender counts for gender, count in gender_counts.items(): if gender == 'male': statuses["active_gender_count"]["male"] += count elif gender == 'female': statuses["active_gender_count"]["female"] += count else: statuses["active_gender_count"]["other"] += count results.append(statuses) return paginator.get_paginated_response(results) class UNCDFDashboardView(APIView): """ Dedicated dashboard for UNCDF partners """ permission_classes = [IsAuthenticated, IsUNCDFPartner] def get(self, request, format=None): # Get Finwise organization data finwise_org_id = 139 # Get basic organization info try: from organisations.models import Organisation finwise_org = Organisation.objects.get(id=finwise_org_id) except: return Response({'error': 'Finwise organization not found'}, status=status.HTTP_404_NOT_FOUND) # Get loan data for Finwise from loans.models import LoanApplication loans = LoanApplication.objects.filter( organisation_branch__branch_organisation_id=finwise_org_id ) # Calculate summary statistics total_applications = loans.count() pending_loans = loans.filter(status='pending').count() approved_loans = loans.filter(status='approved').count() disbursed_loans = loans.filter(status='disbursed').count() rejected_loans = loans.filter(status='rejected').count() cleared_loans = loans.filter(status='cleared_off').count() # Calculate total amounts from django.db.models import Sum total_approved_amount = loans.filter( status__in=['approved', 'disbursed', 'cleared_off'] ).aggregate(total=Sum('loan_amount'))['total'] or 0 total_disbursed_amount = loans.filter( status__in=['disbursed', 'cleared_off'] ).aggregate(total=Sum('loan_amount'))['total'] or 0 # Get recent activities (last 30 days) from datetime import datetime, timedelta thirty_days_ago = datetime.now() - timedelta(days=30) recent_loans = loans.filter( date_added__gte=thirty_days_ago ).count() # Get customer data for Finwise from customers.models import Customer total_customers = Customer.objects.filter( customer_branch__branch_organisation_id=finwise_org_id ).count() # Get savings data for Finwise from savings.models import SavingAccount total_savings_accounts = SavingAccount.objects.filter( account_customer__customer_branch__branch_organisation_id=finwise_org_id, status='active' ).count() dashboard_data = { 'organisation': { 'id': finwise_org.id, 'name': finwise_org.name, 'short_name': finwise_org.short_name, 'logo_url': finwise_org.logo_url, 'city': finwise_org.city, 'address': finwise_org.address, 'phone_number': finwise_org.phone_number, }, 'summary': { 'total_customers': total_customers, 'total_savings_accounts': total_savings_accounts, 'total_applications': total_applications, 'pending_loans': pending_loans, 'approved_loans': approved_loans, 'disbursed_loans': disbursed_loans, 'rejected_loans': rejected_loans, 'cleared_loans': cleared_loans, 'total_approved_amount': float(total_approved_amount), 'total_disbursed_amount': float(total_disbursed_amount), 'recent_activities': recent_loans, }, 'user_info': { 'username': request.user.username, 'role': 'UNCDF Partner', 'access_level': 'Partner Dashboard Only', 'partner_name': 'UNCDF' }, 'last_updated': datetime.now().strftime('%Y-%m-%d %H:%M:%S') } return Response(dashboard_data, status=status.HTTP_200_OK) class MinistryReportOverviewView(APIView): """ Optimized ministry report overview endpoint - ORG-SPECIFIC. Uses caching and optimized queries for faster loading. """ permission_classes = [IsAuthenticated] def get(self, request, format=None): # Get organisation_id from query params or authenticated user organisation_id = request.GET.get('organisation_id', None) if not organisation_id: organisation_id = get_current_user(request, 'organisation_id', None) if not organisation_id: return Response( {"error": "Organisation ID is required or not found in user context"}, status=status.HTTP_400_BAD_REQUEST ) # Verify that user belongs to this organization user_org_id = get_current_user(request, 'organisation_id', None) if user_org_id and str(user_org_id) != str(organisation_id): return Response( {"error": "You do not have permission to view this organisation's report"}, status=status.HTTP_403_FORBIDDEN ) # Optional filters start_date = request.GET.get('start_date', None) end_date = request.GET.get('end_date', None) branch_id = request.GET.get('branch_id', None) # Use optimized helper function but maintain original structure try: preloaded_data = get_preloaded_ministry_data( organisation_id, start_date, end_date, branch_id ) if "error" in preloaded_data: return Response(preloaded_data, status=status.HTTP_404_NOT_FOUND) # Transform to match original response structure report_data = { "organization_summary": { "organization_name": preloaded_data["organization_info"]["name"], "total_branches": preloaded_data["organization_info"]["total_branches"], "total_organizations": 1 }, "customer_statistics": preloaded_data["customer_statistics"], "savings_statistics": preloaded_data["savings_statistics"], "savings_products": preloaded_data["savings_products"], "loan_statistics": preloaded_data["loan_statistics"], "loan_products": preloaded_data["loan_products"], "financial_performance": preloaded_data["financial_performance"], "shares_statistics": preloaded_data["shares_statistics"] } return Response(report_data, status=status.HTTP_200_OK) except Exception as e: return Response( {"error": f"Failed to generate report: {str(e)}"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR ) class MinistryReportSettingsView(APIView): """ Ministry Report Settings API NOW SUPPORTS MULTIPLE REPORTS PER ORGANISATION """ permission_classes = [IsAuthenticated] def get(self, request, pk=None): """ GET (collection) : /api/ministry-report-settings/ → returns ALL report settings for authenticated user's organisation GET (detail) : /api/ministry-report-settings/{id}/ → returns a single report settings record """ user_org_id = get_current_user(request, 'organisation_id', None) if not user_org_id: return Response( {"error": "Organisation ID not found for authenticated user"}, status=status.HTTP_400_BAD_REQUEST ) # ---- GET BY ID ---- if pk: try: settings = MinistryReportSettings.objects.get(pk=pk) except MinistryReportSettings.DoesNotExist: return Response( {"error": "Report settings not found", "id": pk}, status=status.HTTP_404_NOT_FOUND ) # Security check if settings.organisation_id != user_org_id: return Response( {"error": "You do not have permission to view this resource"}, status=status.HTTP_403_FORBIDDEN ) serializer = MinistryReportSettingsSerializer(settings) return Response(serializer.data, status=status.HTTP_200_OK) # ---- LIST ALL SETTINGS FOR THIS ORG ---- settings = MinistryReportSettings.objects.filter(organisation_id=user_org_id) serializer = MinistryReportSettingsSerializer(settings, many=True) return Response(serializer.data, status=status.HTTP_200_OK) # ---------------------------------------------------------------------- def post(self, request): """ POST /api/ministry-report-settings/ → Creates a NEW report for this organisation (multiple allowed) """ organisation_id = get_current_user(request, 'organisation_id', None) if not organisation_id: return Response( {"error": "organisation_id is required"}, status=status.HTTP_400_BAD_REQUEST ) # Validate organisation exists try: organisation = Organisation.objects.get(pk=organisation_id) except Organisation.DoesNotExist: return Response( {"error": "Organisation not found", "organisation_id": organisation_id}, status=status.HTTP_404_NOT_FOUND ) data = request.data.copy() data["organisation"] = organisation_id # enforce ownership serializer = MinistryReportSettingsSerializer(data=data) if serializer.is_valid(): serializer.save() # Invalidate cache when settings change invalidate_ministry_report_cache(organisation_id) return Response(serializer.data, status=status.HTTP_201_CREATED) return Response( {"error": "Validation error", "details": serializer.errors}, status=status.HTTP_400_BAD_REQUEST ) # ---------------------------------------------------------------------- def put(self, request, pk=None): """ PUT /api/ministry-report-settings/{id}/ → Updates an existing report """ if not pk: return Response( {"error": "Settings ID is required for update"}, status=status.HTTP_400_BAD_REQUEST ) try: settings = MinistryReportSettings.objects.get(pk=pk) except MinistryReportSettings.DoesNotExist: return Response( {"error": "Report settings not found", "id": pk}, status=status.HTTP_404_NOT_FOUND ) user_org_id = get_current_user(request, 'organisation_id', None) # Security check if settings.organisation_id != user_org_id: return Response( {"error": "You do not have permission to update these settings"}, status=status.HTTP_403_FORBIDDEN ) serializer = MinistryReportSettingsSerializer(settings, data=request.data, partial=True) if serializer.is_valid(): serializer.save() # Invalidate cache when settings change invalidate_ministry_report_cache(user_org_id) return Response(serializer.data, status=status.HTTP_200_OK) return Response( {"error": "Validation error", "details": serializer.errors}, status=status.HTTP_400_BAD_REQUEST ) # ---------------------------------------------------------------------- def delete(self, request, pk=None): """ DELETE /api/ministry-report-settings/{id}/ """ if not pk: return Response( {"error": "Settings ID is required for deletion"}, status=status.HTTP_400_BAD_REQUEST ) try: settings = MinistryReportSettings.objects.get(pk=pk) except MinistryReportSettings.DoesNotExist: return Response( {"error": "Report settings not found", "id": pk}, status=status.HTTP_404_NOT_FOUND ) user_org_id = get_current_user(request, 'organisation_id', None) if settings.organisation_id != user_org_id: return Response( {"error": "You do not have permission to delete these settings"}, status=status.HTTP_403_FORBIDDEN ) settings.delete() # Invalidate cache when settings are deleted invalidate_ministry_report_cache(user_org_id) return Response(status=status.HTTP_204_NO_CONTENT) class MinistryReportPreloadedDataView(APIView): """ Preloaded ministry report data endpoint for faster frontend loading. Returns all ministry report data and settings in a single optimized call. """ permission_classes = [IsAuthenticated] def get(self, request, format=None): organisation_id = get_current_user(request, 'organisation_id', None) if not organisation_id: return Response( {"error": "Organisation ID not found for authenticated user"}, status=status.HTTP_400_BAD_REQUEST ) # Optional filters start_date = request.GET.get('start_date', None) end_date = request.GET.get('end_date', None) branch_id = request.GET.get('branch_id', None) try: # Get preloaded data using optimized helper preloaded_data = get_preloaded_ministry_data( organisation_id, start_date, end_date, branch_id ) if "error" in preloaded_data: return Response(preloaded_data, status=status.HTTP_404_NOT_FOUND) # Add additional metadata for frontend preloaded_data["metadata"] = { "filters_applied": { "start_date": start_date, "end_date": end_date, "branch_id": branch_id }, "cache_status": "optimized", "data_freshness": "5_minutes_max" } return Response(preloaded_data, status=status.HTTP_200_OK) except Exception as e: return Response( {"error": f"Failed to load ministry report data: {str(e)}"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR ) class MinistryReportCacheManagementView(APIView): """ Cache management endpoint for ministry reports """ permission_classes = [IsAuthenticated] def get(self, request, format=None): """Get cache status""" organisation_id = get_current_user(request, 'organisation_id', None) if not organisation_id: return Response( {"error": "Organisation ID not found for authenticated user"}, status=status.HTTP_400_BAD_REQUEST ) from .cache_utils import get_cache_status status_info = get_cache_status(organisation_id) return Response(status_info, status=status.HTTP_200_OK) def post(self, request, format=None): """Warm cache""" organisation_id = get_current_user(request, 'organisation_id', None) if not organisation_id: return Response( {"error": "Organisation ID not found for authenticated user"}, status=status.HTTP_400_BAD_REQUEST ) from .cache_utils import warm_ministry_reports_cache result = warm_ministry_reports_cache([organisation_id]) return Response(result, status=status.HTTP_200_OK) def delete(self, request, format=None): """Clear cache""" organisation_id = get_current_user(request, 'organisation_id', None) if not organisation_id: return Response( {"error": "Organisation ID not found for authenticated user"}, status=status.HTTP_400_BAD_REQUEST ) from .cache_utils import clear_ministry_reports_cache result = clear_ministry_reports_cache([organisation_id]) return Response(result, status=status.HTTP_200_OK)