""" Financial Reports Service Provides data for: 1. SasaPay Reconciliation (Money In / Money Out / Control Account) 2. Profit & Loss Statement (integrated with Chart of Accounts) 3. Financial Statements (Balance Sheet style with GL integration) 4. Loan Aging Report """ from decimal import Decimal from datetime import date, timedelta from django.db.models import Sum, Count, Q from django.utils import timezone from django.core.cache import cache # --------------------------------------------------------------------------- # Helpers # --------------------------------------------------------------------------- def _d(value): """Coerce value to Decimal, defaulting to 0.""" if value is None: return Decimal('0.00') return Decimal(str(value)).quantize(Decimal('0.01')) def _date_range_filter(start_date=None, end_date=None): """Return a dict suitable for ** unpacking into a queryset filter.""" f = {} if start_date: f['__gte'] = start_date if end_date: f['__lte'] = end_date return f # --------------------------------------------------------------------------- # 1. SasaPay Reconciliation # --------------------------------------------------------------------------- def get_sasapay_reconciliation(start_date=None, end_date=None): """ Returns a reconciliation dict with: - money_in: all processed IPN payments (C2B repayments via SasaPay) - money_out: all successful B2C disbursements via SasaPay - ipn_logs: breakdown by processing_status - unknown_payments: unmatched payments - stk_results: STK push results - control_account: running balance summary """ from payments.sasapay_models import ( SasaPayIPNLog, SasaPayDisbursementResult, SasaPayUnknownPayment, SasaPaySTKResult ) from loans.models import Repayment # ------ Money IN (C2B repayments linked to SasaPay IPN) ------ repayments_qs = Repayment.objects.filter(payment_source='automatic') if start_date: repayments_qs = repayments_qs.filter(payment_date__date__gte=start_date) if end_date: repayments_qs = repayments_qs.filter(payment_date__date__lte=end_date) money_in_total = _d(repayments_qs.aggregate(t=Sum('amount'))['t']) money_in_count = repayments_qs.count() # Also aggregate from IPN logs (processed only) as cross-check ipn_qs = SasaPayIPNLog.objects.all() if start_date: ipn_qs = ipn_qs.filter(created_at__date__gte=start_date) if end_date: ipn_qs = ipn_qs.filter(created_at__date__lte=end_date) ipn_by_status = {} for row in ipn_qs.values('processing_status').annotate(cnt=Count('id')): ipn_by_status[row['processing_status']] = row['cnt'] ipn_total = ipn_qs.count() ipn_processed = ipn_by_status.get('processed', 0) ipn_no_match = ipn_by_status.get('no_match', 0) ipn_duplicate = ipn_by_status.get('duplicate', 0) ipn_failed = ipn_by_status.get('failed', 0) ipn_pending = ipn_by_status.get('pending', 0) # ------ Money OUT (B2C disbursements) ------ disb_qs = SasaPayDisbursementResult.objects.all() if start_date: disb_qs = disb_qs.filter(created_at__date__gte=start_date) if end_date: disb_qs = disb_qs.filter(created_at__date__lte=end_date) disb_success_qs = disb_qs.filter(result_code='0') money_out_total = _d(disb_success_qs.aggregate(t=Sum('trans_amount'))['t']) money_out_count = disb_success_qs.count() money_out_failed = disb_qs.exclude(result_code='0').count() last_merchant_balance = _d( disb_success_qs.order_by('-created_at').values_list('merchant_balance', flat=True).first() ) # ------ Unknown / Unmatched payments ------ unknown_qs = SasaPayUnknownPayment.objects.all() if start_date: unknown_qs = unknown_qs.filter(created_at__date__gte=start_date) if end_date: unknown_qs = unknown_qs.filter(created_at__date__lte=end_date) unknown_total = _d(unknown_qs.aggregate(t=Sum('amount'))['t']) unknown_count = unknown_qs.count() unknown_unresolved = unknown_qs.filter(resolved=False).count() # ------ STK Push results ------ stk_qs = SasaPaySTKResult.objects.all() if start_date: stk_qs = stk_qs.filter(created_at__date__gte=start_date) if end_date: stk_qs = stk_qs.filter(created_at__date__lte=end_date) stk_success = stk_qs.filter(result_code='0') stk_in_total = _d(stk_success.aggregate(t=Sum('trans_amount'))['t']) stk_in_count = stk_success.count() stk_failed_count = stk_qs.exclude(result_code='0').count() # ------ Control Account Balance ------ # Opening = money that came in (repayments) - money that went out (disbursements) net_position = money_in_total + stk_in_total - money_out_total return { 'period': {'start': start_date, 'end': end_date}, 'money_in': { 'repayments_total': money_in_total, 'repayments_count': money_in_count, 'stk_total': stk_in_total, 'stk_count': stk_in_count, 'grand_total': money_in_total + stk_in_total, }, 'money_out': { 'disbursements_total': money_out_total, 'disbursements_count': money_out_count, 'disbursements_failed': money_out_failed, }, 'control_account': { 'net_position': net_position, 'last_merchant_balance': last_merchant_balance, }, 'ipn_summary': { 'total': ipn_total, 'processed': ipn_processed, 'no_match': ipn_no_match, 'duplicate': ipn_duplicate, 'failed': ipn_failed, 'pending': ipn_pending, }, 'unknown_payments': { 'total_amount': unknown_total, 'count': unknown_count, 'unresolved': unknown_unresolved, }, 'stk_summary': { 'success_count': stk_in_count, 'failed_count': stk_failed_count, }, 'ipn_detail_qs': ipn_qs.order_by('-created_at'), 'disbursement_detail_qs': disb_qs.order_by('-created_at'), 'unknown_detail_qs': unknown_qs.order_by('-created_at'), } # --------------------------------------------------------------------------- # 2. Profit & Loss Statement (integrated with Chart of Accounts) # --------------------------------------------------------------------------- def get_profit_and_loss(start_date=None, end_date=None, branch_id=None): """ Returns a P&L dict integrated with the Chart of Accounts: INCOME (from GL or fallback to loan calculations) - Interest income (from GL account 41001 or calculated) - Processing fee income (from GL account 41002 or calculated) - Registration fee income (from GL account 41004 or calculated) - Penalty income (from GL account 41003 or calculated) EXPENSES (from GL or fallback to expense records) - Operational expenses by category (from GL expense accounts or Expense model) NET = Total Income - Total Expenses Uses accounting system journal entries when available for accuracy, falls back to direct loan/expense calculations for backwards compatibility. """ from loans.models import Loan, Repayment from expenses.models import Expense # Try to use accounting system first use_accounting_system = False try: from accounting.models import GeneralLedger, Account from accounting.services.report_service import ReportService # Check if we have GL data if GeneralLedger.objects.exists(): use_accounting_system = True report_service = ReportService() except ImportError: pass if use_accounting_system and start_date and end_date: # Use accounting system for accurate P&L from posted journal entries try: income_statement = report_service.generate_income_statement( start_date=start_date, end_date=end_date, branch=None # TODO: Map branch_id to Branch object if needed ) # Extract data from accounting report income_groups = income_statement.get('income_groups', {}) expense_groups = income_statement.get('expense_groups', {}) # Aggregate income interest_income = income_groups.get('interest', {}).get('total', Decimal('0.00')) fee_income = income_groups.get('fees', {}).get('total', Decimal('0.00')) penalty_income = income_groups.get('penalties', {}).get('total', Decimal('0.00')) other_income = income_groups.get('other', {}).get('total', Decimal('0.00')) # Split fee income (processing + registration) # TODO: Refine if we track these separately in GL processing_fee_income = fee_income * Decimal('0.7') # Estimate registration_fee_income = fee_income * Decimal('0.3') # Estimate total_income = income_statement.get('gross_income', Decimal('0.00')) # Aggregate expenses total_expenses_operational = income_statement.get('total_expenses', Decimal('0.00')) expense_by_category = [ {'category': group_name, 'total': group_data['total']} for group_name, group_data in expense_groups.items() ] net_profit = income_statement.get('net_profit', Decimal('0.00')) # Get cash flow data from loans (not in GL yet) loan_qs = Loan.objects.filter(is_deleted=False) if branch_id: loan_qs = loan_qs.filter(borrower__branch_id=branch_id) if start_date: loan_qs = loan_qs.filter(disbursement_date__date__gte=start_date) if end_date: loan_qs = loan_qs.filter(disbursement_date__date__lte=end_date) loans_disbursed = _d(loan_qs.aggregate(principal=Sum('principal_amount'))['principal']) total_loans_count = loan_qs.count() rep_qs_period = Repayment.objects.all() if start_date: rep_qs_period = rep_qs_period.filter(payment_date__date__gte=start_date) if end_date: rep_qs_period = rep_qs_period.filter(payment_date__date__lte=end_date) if branch_id: rep_qs_period = rep_qs_period.filter(loan__borrower__branch_id=branch_id) total_repayments_collected = _d(rep_qs_period.aggregate(t=Sum('amount'))['t']) return { 'period': {'start': start_date, 'end': end_date}, 'source': 'accounting_system', 'income': { 'interest_income': interest_income, 'processing_fee_income': processing_fee_income, 'registration_fee_income': registration_fee_income, 'penalty_income': penalty_income, 'other_income': other_income, 'total': total_income, }, 'expenses': { 'by_category': expense_by_category, 'total_operational': total_expenses_operational, }, 'net_profit': net_profit, 'cash_flow': { 'loans_disbursed': loans_disbursed, 'repayments_collected': total_repayments_collected, 'net_cash': total_repayments_collected - loans_disbursed, }, 'loan_summary': { 'count': total_loans_count, 'disbursed': loans_disbursed, }, } except Exception as e: # Fall back to direct calculation if accounting system fails import logging logger = logging.getLogger(__name__) logger.warning(f'Failed to use accounting system for P&L: {e}, falling back to direct calculation') # Fallback: Direct calculation from loan and expense models loan_qs = Loan.objects.filter(is_deleted=False) if branch_id: loan_qs = loan_qs.filter(borrower__branch_id=branch_id) if start_date: loan_qs = loan_qs.filter(disbursement_date__date__gte=start_date) if end_date: loan_qs = loan_qs.filter(disbursement_date__date__lte=end_date) loan_agg = loan_qs.aggregate( interest=Sum('interest_amount'), processing=Sum('processing_fee'), registration=Sum('registration_fee'), principal=Sum('principal_amount'), total_loans=Count('id'), ) interest_income = _d(loan_agg['interest']) processing_fee_income = _d(loan_agg['processing']) registration_fee_income = _d(loan_agg['registration']) loans_disbursed = _d(loan_agg['principal']) total_loans_count = loan_agg['total_loans'] or 0 # ---- Penalty income (LoanPenalty model if it exists) ---- penalty_income = Decimal('0.00') try: from loans.models import LoanPenalty pen_qs = LoanPenalty.objects.all() if start_date: pen_qs = pen_qs.filter(applied_date__date__gte=start_date) if end_date: pen_qs = pen_qs.filter(applied_date__date__lte=end_date) if branch_id: pen_qs = pen_qs.filter(loan__borrower__branch_id=branch_id) penalty_income = _d(pen_qs.aggregate(t=Sum('amount'))['t']) except Exception: pass total_income = interest_income + processing_fee_income + registration_fee_income + penalty_income # ---- Expenses ---- exp_qs = Expense.objects.filter(status='approved') if start_date: exp_qs = exp_qs.filter(expense_date__gte=start_date) if end_date: exp_qs = exp_qs.filter(expense_date__lte=end_date) if branch_id: exp_qs = exp_qs.filter(branch_id=branch_id) expense_by_category = list( exp_qs.values('category').annotate(total=Sum('amount')).order_by('-total') ) total_expenses_operational = _d(exp_qs.aggregate(t=Sum('amount'))['t']) # ---- Repayments collected (cash in) ---- rep_qs_period = Repayment.objects.all() if start_date: rep_qs_period = rep_qs_period.filter(payment_date__date__gte=start_date) if end_date: rep_qs_period = rep_qs_period.filter(payment_date__date__lte=end_date) if branch_id: rep_qs_period = rep_qs_period.filter(loan__borrower__branch_id=branch_id) total_repayments_collected = _d(rep_qs_period.aggregate(t=Sum('amount'))['t']) net_profit = total_income - total_expenses_operational return { 'period': {'start': start_date, 'end': end_date}, 'source': 'direct_calculation', 'income': { 'interest_income': interest_income, 'processing_fee_income': processing_fee_income, 'registration_fee_income': registration_fee_income, 'penalty_income': penalty_income, 'total': total_income, }, 'expenses': { 'by_category': expense_by_category, 'total_operational': total_expenses_operational, }, 'net_profit': net_profit, 'cash_flow': { 'loans_disbursed': loans_disbursed, 'repayments_collected': total_repayments_collected, 'net_cash': total_repayments_collected - loans_disbursed, }, 'loan_summary': { 'count': total_loans_count, 'disbursed': loans_disbursed, }, } # --------------------------------------------------------------------------- # 3. Financial Statements (Balance Sheet integrated with Chart of Accounts) # --------------------------------------------------------------------------- def get_financial_statements(as_of_date=None, branch_id=None): """ Returns a financial statements dict (Balance Sheet) integrated with Chart of Accounts: ASSETS (from GL or fallback to loan calculations) - Current Assets (Cash, Bank, M-Pesa from GL accounts) - Loan Portfolio (outstanding principal from GL account 11002 or calculated) - Accrued Interest Receivable (from GL account 11006) - Non-Performing Loans LIABILITIES (from GL liability accounts) - Client Savings - Borrowings - Other Liabilities EQUITY / NET WORTH - Retained Earnings (Income - Expenses from GL or calculated) Uses accounting system when available for accuracy, falls back to direct calculations. """ from loans.models import Loan, Repayment from expenses.models import Expense as_of = as_of_date or date.today() # Try to use accounting system first use_accounting_system = False try: from accounting.models import Account from accounting.services.accounting_service import AccountingService # Check if we have accounting data if Account.objects.exists(): use_accounting_system = True accounting_service = AccountingService() except ImportError: pass if use_accounting_system: try: # Get account balances from GL using organizational account codes # Assets cash_account = _get_account_balance(accounting_service, '202003', as_of, branch_id) # Cash - Main Branch bank_account = _get_account_balance(accounting_service, '202004', as_of, branch_id) # Bank - Main Account mpesa_account = _get_account_balance(accounting_service, '202005', as_of, branch_id) # M-Pesa Account loan_portfolio_account = _get_account_balance(accounting_service, '202001', as_of, branch_id) # Loan Portfolio accrued_interest_account = _get_account_balance(accounting_service, '202002', as_of, branch_id) # Accrued Interest Receivable # Liabilities client_savings_account = _get_account_balance(accounting_service, '301001', as_of, branch_id) # Client Savings Deposits # Note: Borrowings account not in current chart, use 0 borrowings_account = Decimal('0.00') # Calculate total assets current_assets = cash_account + bank_account + mpesa_account total_assets = current_assets + loan_portfolio_account + accrued_interest_account # Calculate total liabilities total_liabilities = client_savings_account + borrowings_account # Equity = Assets - Liabilities equity = total_assets - total_liabilities # Get loan statistics for additional context base_qs = Loan.objects.filter(is_deleted=False) if branch_id: base_qs = base_qs.filter(borrower__branch_id=branch_id) active_qs = base_qs.filter(status='active', disbursement_date__date__lte=as_of) active_count = active_qs.count() npl_qs = base_qs.filter(status__in=['defaulted', 'written_off'], disbursement_date__date__lte=as_of) npl_count = npl_qs.count() npl_amount = _d(npl_qs.aggregate(principal=Sum('principal_amount'))['principal']) paid_qs = base_qs.filter(status='paid', disbursement_date__date__lte=as_of) paid_count = paid_qs.count() return { 'as_of': as_of, 'source': 'accounting_system', 'assets': { 'current_assets': { 'cash': cash_account, 'bank': bank_account, 'mpesa': mpesa_account, 'total': current_assets, }, 'loan_portfolio': { 'gross': loan_portfolio_account, 'net': loan_portfolio_account - npl_amount, 'accrued_interest': accrued_interest_account, }, 'active_loans_count': active_count, 'npl_amount': npl_amount, 'npl_count': npl_count, 'paid_loans_count': paid_count, 'total_assets': total_assets, }, 'liabilities': { 'client_savings': client_savings_account, 'borrowings': borrowings_account, 'total_liabilities': total_liabilities, }, 'equity': { 'retained_earnings': equity, 'total_equity': equity, }, 'npl_ratio': ( (npl_amount / loan_portfolio_account * 100) if loan_portfolio_account > 0 else Decimal('0.00') ), } except Exception as e: # Fall back to direct calculation import logging logger = logging.getLogger(__name__) logger.warning(f'Failed to use accounting system for financial statements: {e}, falling back') # Fallback: Direct calculation from loan and expense models base_qs = Loan.objects.filter(is_deleted=False) if branch_id: base_qs = base_qs.filter(borrower__branch_id=branch_id) # ---- Active loan portfolio ---- active_qs = base_qs.filter(status='active', disbursement_date__date__lte=as_of) active_agg = active_qs.aggregate( count=Count('id'), principal=Sum('principal_amount'), interest=Sum('interest_amount'), processing=Sum('processing_fee'), registration=Sum('registration_fee'), ) active_portfolio = _d(active_agg['principal']) active_count = active_agg['count'] or 0 # Repayments on active loans (reduces outstanding) active_paid = _d( Repayment.objects.filter(loan__in=active_qs) .aggregate(t=Sum('amount'))['t'] ) outstanding_portfolio = active_portfolio - active_paid # ---- Paid loans ---- paid_qs = base_qs.filter(status='paid', disbursement_date__date__lte=as_of) paid_agg = paid_qs.aggregate(count=Count('id'), principal=Sum('principal_amount')) paid_count = paid_agg['count'] or 0 paid_principal = _d(paid_agg['principal']) # ---- Non-performing (defaulted + written-off) ---- npl_qs = base_qs.filter(status__in=['defaulted', 'written_off'], disbursement_date__date__lte=as_of) npl_agg = npl_qs.aggregate(count=Count('id'), principal=Sum('principal_amount')) npl_count = npl_agg['count'] or 0 npl_amount = _d(npl_agg['principal']) # ---- Total repayments collected (all time up to as_of) ---- rep_qs = Repayment.objects.filter(payment_date__date__lte=as_of) if branch_id: rep_qs = rep_qs.filter(loan__borrower__branch_id=branch_id) total_collected = _d(rep_qs.aggregate(t=Sum('amount'))['t']) # ---- All-time income (fees + interest on all disbursed loans up to as_of) ---- all_loans_qs = base_qs.filter(disbursement_date__date__lte=as_of) income_agg = all_loans_qs.aggregate( interest=Sum('interest_amount'), processing=Sum('processing_fee'), registration=Sum('registration_fee'), ) total_income_earned = ( _d(income_agg['interest']) + _d(income_agg['processing']) + _d(income_agg['registration']) ) # ---- All-time expenses ---- exp_qs = Expense.objects.filter(status='approved', expense_date__lte=as_of) if branch_id: exp_qs = exp_qs.filter(branch_id=branch_id) total_expenses = _d(exp_qs.aggregate(t=Sum('amount'))['t']) net_equity = total_income_earned - total_expenses return { 'as_of': as_of, 'source': 'direct_calculation', 'assets': { 'active_loan_portfolio_gross': active_portfolio, 'active_loan_portfolio_net': outstanding_portfolio, # gross - collected 'active_loans_count': active_count, 'paid_loans_principal': paid_principal, 'paid_loans_count': paid_count, 'npl_amount': npl_amount, 'npl_count': npl_count, 'total_repayments_collected': total_collected, }, 'income_earned': { 'interest': _d(income_agg['interest']), 'processing_fees': _d(income_agg['processing']), 'registration_fees': _d(income_agg['registration']), 'total': total_income_earned, }, 'total_expenses': total_expenses, 'net_equity': net_equity, 'npl_ratio': ( (npl_amount / active_portfolio * 100) if active_portfolio else Decimal('0.00') ), } def _get_account_balance(accounting_service, account_code, as_of_date, branch_id=None): """ Helper to get account balance from accounting service. Returns Decimal balance or 0.00 if account not found. """ try: from accounting.models import Account account = Account.objects.get(code=account_code, is_active=True) # calculate_account_balance returns a Decimal directly, not a dict balance = accounting_service.calculate_account_balance( account=account, as_of_date=as_of_date, branch=None # TODO: Map branch_id to Branch object if needed ) return _d(balance) except Exception as e: # Log the error for debugging import logging logger = logging.getLogger(__name__) logger.warning(f'Could not get balance for account {account_code}: {e}') return Decimal('0.00') # --------------------------------------------------------------------------- # 4. Loan Aging Report # --------------------------------------------------------------------------- AGING_BUCKETS = [ ('Current (not overdue)', 0, 0), ('1 – 30 days', 1, 30), ('31 – 60 days', 31, 60), ('61 – 90 days', 61, 90), ('91 – 180 days', 91, 180), ('181 – 365 days', 181, 365), ('Over 365 days', 366, None), ] def get_loan_aging(branch_id=None, product_id=None, include_detail=True): """ Returns an aging report dict with buckets and optional per-loan detail. Each bucket has: - label, days_min, days_max - count, principal, outstanding, percentage_of_portfolio """ from loans.models import Loan, Repayment today = date.today() active_qs = Loan.objects.filter(status='active', is_deleted=False) if branch_id: active_qs = active_qs.filter(borrower__branch_id=branch_id) if product_id: active_qs = active_qs.filter(application__loan_product_id=product_id) # Compute days past due for each loan in Python (avoids DB-level date math issues # across MySQL/SQLite; the queryset is normally small for active loans) loans_data = list( active_qs.select_related('borrower', 'application__loan_product') .values( 'id', 'loan_number', 'principal_amount', 'due_date', 'borrower__first_name', 'borrower__last_name', 'borrower__phone_number', 'application__loan_product__name', '_amount_paid_cache', ) ) # Fetch all repayment sums for these loans in one query loan_ids = [r['id'] for r in loans_data] paid_map = {} for row in ( Repayment.objects.filter(loan_id__in=loan_ids) .values('loan_id') .annotate(paid=Sum('amount')) ): paid_map[row['loan_id']] = _d(row['paid']) total_portfolio = Decimal('0.00') augmented = [] for r in loans_data: due = r['due_date'] if hasattr(due, 'date'): due = due.date() days_past_due = max(0, (today - due).days) if due else 0 outstanding = _d(r['principal_amount']) - paid_map.get(r['id'], Decimal('0.00')) outstanding = max(Decimal('0.00'), outstanding) total_portfolio += outstanding augmented.append({ 'id': r['id'], 'loan_number': r['loan_number'], 'borrower_name': f"{r['borrower__first_name']} {r['borrower__last_name']}".strip(), 'borrower_phone': r['borrower__phone_number'], 'product': r['application__loan_product__name'], 'principal': _d(r['principal_amount']), 'outstanding': outstanding, 'due_date': due, 'days_past_due': days_past_due, }) # Build buckets buckets = [] for label, dmin, dmax in AGING_BUCKETS: if dmin == 0 and dmax == 0: bucket_loans = [l for l in augmented if l['days_past_due'] == 0] elif dmax is None: bucket_loans = [l for l in augmented if l['days_past_due'] >= dmin] else: bucket_loans = [l for l in augmented if dmin <= l['days_past_due'] <= dmax] bucket_outstanding = sum((l['outstanding'] for l in bucket_loans), Decimal('0.00')) pct = ( (bucket_outstanding / total_portfolio * 100) if total_portfolio else Decimal('0.00') ) buckets.append({ 'label': label, 'days_min': dmin, 'days_max': dmax, 'count': len(bucket_loans), 'outstanding': bucket_outstanding, 'percentage': round(float(pct), 1), 'loans': bucket_loans if include_detail else [], }) overdue_buckets = buckets[1:] # exclude current total_overdue = sum((b['outstanding'] for b in overdue_buckets), Decimal('0.00')) overdue_count = sum(b['count'] for b in overdue_buckets) return { 'as_of': today, 'total_portfolio': total_portfolio, 'total_active_loans': len(augmented), 'total_overdue': total_overdue, 'overdue_count': overdue_count, 'par_ratio': round(float(total_overdue / total_portfolio * 100) if total_portfolio else 0, 2), 'buckets': buckets, 'all_loans': augmented, # for export / detail view }