# budgets/views.py from rest_framework import viewsets from rest_framework.permissions import IsAuthenticated from rest_framework.response import Response from rest_framework import status from rest_framework.decorators import action from django.db.models import Sum from django.utils import timezone from .models import Budget from .serializers import BudgetSerializer, BudgetAccountStructureSerializer, BudgetAccountSerializer from ledgers.models import OrganisationSubAccount, SystemTransactions from questbanker_api.utils import get_current_user import calendar from datetime import date from collections import defaultdict def _build_analysis(budget, branch_id, organisation_id): """ Compute expected/actual/variance for every row+cell in one pass. Uses two bulk queries per row (all debits and all credits across all months) instead of two queries per cell, reducing DB round-trips from O(rows*months) to O(rows). """ # Pre-fetch all account codes we need in a single query account_codes = list(budget.rows.values_list('account_code', flat=True).distinct()) charts_by_code = { c.account_code: c for c in OrganisationSubAccount.objects.filter( account_code__in=account_codes, account_organisation_id=organisation_id, deleted=False, ) } rows_analysis = [] for row in budget.rows.prefetch_related('cells').order_by('id'): chart = charts_by_code.get(row.account_code) cells = list(row.cells.order_by('month')) if not cells: rows_analysis.append({ 'id': row.id, 'account_code': row.account_code, 'account_name': row.account_name, 'row_type': row.row_type, 'cells': [], }) continue period_start = cells[0].month last_cell = cells[-1].month last_day = calendar.monthrange(last_cell.year, last_cell.month)[1] period_end = date(last_cell.year, last_cell.month, last_day) # Default: no actuals debits_by_month = defaultdict(float) credits_by_month = defaultdict(float) if chart: # One query for all debits across the full period debit_qs = ( SystemTransactions.objects .filter( transaction_type='normal', deleted=False, branch_id=branch_id, debit_chart=chart, record_date__date__gte=period_start, record_date__date__lte=period_end, ) .values('record_date__year', 'record_date__month') .annotate(total=Sum('amount')) ) for entry in debit_qs: key = (entry['record_date__year'], entry['record_date__month']) debits_by_month[key] = round(entry['total'] or 0, 3) # One query for all credits across the full period credit_qs = ( SystemTransactions.objects .filter( transaction_type='normal', deleted=False, branch_id=branch_id, credit_chart=chart, record_date__date__gte=period_start, record_date__date__lte=period_end, ) .values('record_date__year', 'record_date__month') .annotate(total=Sum('amount')) ) for entry in credit_qs: key = (entry['record_date__year'], entry['record_date__month']) credits_by_month[key] = round(entry['total'] or 0, 3) cells_analysis = [] for cell in cells: key = (cell.month.year, cell.month.month) d = debits_by_month[key] c = credits_by_month[key] if chart and chart.account_line in ['income', 'liabilities', 'capital']: actual = round(c - d, 2) else: actual = round(d - c, 2) expected = float(cell.value) cells_analysis.append({ 'month': str(cell.month), 'expected': expected, 'actual': actual, 'variance': round(actual - expected, 2), }) rows_analysis.append({ 'id': row.id, 'account_code': row.account_code, 'account_name': row.account_name, 'row_type': row.row_type, 'cells': cells_analysis, }) return rows_analysis class BudgetViewSet(viewsets.ModelViewSet): queryset = Budget.objects.all().order_by('-created_at') serializer_class = BudgetSerializer permission_classes = [IsAuthenticated] def get_queryset(self): # Budgets are strictly branch-scoped — only show budgets for the # user's current session branch, regardless of who created them. branch_id = get_current_user(self.request, 'organisation_branch_id', None) return self.queryset.filter(branch_id=branch_id) def perform_create(self, serializer): branch_id = get_current_user(self.request, 'organisation_branch_id', None) serializer.save(user=self.request.user, branch_id=branch_id) @action(detail=True, methods=['post']) def publish(self, request, pk=None): budget = self.get_object() if budget.status == Budget.PUBLISHED: return Response({'error': 'Budget is already published'}, status=status.HTTP_400_BAD_REQUEST) budget.status = Budget.PUBLISHED budget.save() return Response(self.get_serializer(budget).data) @action(detail=True, methods=['post']) def revert_to_draft(self, request, pk=None): budget = self.get_object() if budget.status == Budget.DRAFT: return Response({'error': 'Budget is already a draft'}, status=status.HTTP_400_BAD_REQUEST) # Clear cached analysis when reverting — figures may change after edits budget.status = Budget.DRAFT budget.analysis_cache = None budget.analysis_cached_at = None budget.save() return Response(self.get_serializer(budget).data) @action(detail=True, methods=['get']) def analyse(self, request, pk=None): budget = self.get_object() if budget.status != Budget.PUBLISHED: return Response({'error': 'Only published budgets can be analysed'}, status=status.HTTP_400_BAD_REQUEST) force_refresh = request.GET.get('refresh') == '1' # Return cached result if available and refresh not requested if budget.analysis_cache and not force_refresh: return Response(budget.analysis_cache) branch_id = budget.branch_id if not branch_id: branch_id = get_current_user(request, 'organisation_branch_id', None) organisation_id = get_current_user(request, 'organisation_id', None) rows_analysis = _build_analysis(budget, branch_id, organisation_id) result = { 'budget_id': budget.id, 'financial_year': budget.financial_year, 'budget_period': budget.budget_period, 'branch_id': branch_id, 'cached_at': timezone.now().isoformat(), 'rows': rows_analysis, } # Persist the result so subsequent calls are instant budget.analysis_cache = result budget.analysis_cached_at = timezone.now() budget.save(update_fields=['analysis_cache', 'analysis_cached_at']) return Response(result) @action(detail=True, methods=['post']) def refresh_analysis(self, request, pk=None): """Force-clear the cached analysis so the next analyse call recomputes it.""" budget = self.get_object() budget.analysis_cache = None budget.analysis_cached_at = None budget.save(update_fields=['analysis_cache', 'analysis_cached_at']) return Response({'status': 'Analysis cache cleared. Call analyse to regenerate.'}) class BudgetAccountView(viewsets.ViewSet): """ API endpoint for retrieving account structures for budgeting. Returns ALL accounts regardless of whether they have transactions. """ @action(detail=False, methods=['get'], url_path='structure') def get_budget_structure(self, request): """ GET /api/budget-accounts/structure/ Returns the complete account hierarchy for income and expenses, including all accounts regardless of transactions. Query params: - organisation_id: The organisation ID (optional if using get_current_user) """ # Get organisation from request # organisation_id = get_current_user(request, 'organisation_id', None) organisation_id = request.GET.get('organisation_id') if not organisation_id: return Response( {'error': 'Organisation ID is required'}, status=status.HTTP_400_BAD_REQUEST ) # Create context for serializer context = { 'organisation_id': organisation_id, 'request': request } # Serialize the structure serializer = BudgetAccountStructureSerializer( {}, # Empty dict as we're using SerializerMethodField context=context ) return Response(serializer.data, status=status.HTTP_200_OK) @action(detail=False, methods=['get'], url_path='flat-list') def get_flat_list(self, request): """ GET /api/budget-accounts/flat-list/ Returns a flat list of all leaf accounts (accounts with no children) for the specified account lines. Useful for budget data entry. Query params: - organisation_id: The organisation ID - account_lines: Comma-separated list (e.g., 'income,expenses') """ # organisation_id = get_current_user(request, 'organisation_id', None) organisation_id = request.GET.get('organisation_id') account_lines = request.GET.get('account_lines', 'income,expenses') if not organisation_id: return Response( {'error': 'Organisation ID is required'}, status=status.HTTP_400_BAD_REQUEST ) # Parse account lines lines = [line.strip() for line in account_lines.split(',')] # Get all accounts for the specified lines all_accounts = OrganisationSubAccount.objects.filter( account_line__in=lines, account_organisation_id=organisation_id, deleted=False ).order_by('account_line', 'account_code') # Filter to only leaf accounts (accounts with no children) leaf_accounts = [] for account in all_accounts: has_children = OrganisationSubAccount.objects.filter( parent_id=account.id, deleted=False ).exists() if not has_children: leaf_accounts.append(account) # Serialize context = {'organisation_id': organisation_id, 'request': request} serializer = BudgetAccountSerializer( leaf_accounts, many=True, context=context ) return Response({ 'count': len(leaf_accounts), 'accounts': serializer.data }, status=status.HTTP_200_OK)