""" Views for consolidated financial reports: /reports/financial/sasapay-reconciliation/ /reports/financial/profit-loss/ /reports/financial/statements/ /reports/financial/loan-aging/ /reports/financial/loan-aging/export/ (CSV) """ from datetime import date, datetime, timedelta from decimal import Decimal from django.contrib.auth.decorators import login_required from django.db.models import Q from django.http import HttpResponse, JsonResponse from django.shortcuts import render, redirect from django.contrib import messages from django.utils import timezone from users.decorators import staff_required from users.models import Branch from loans.models import LoanProduct @login_required @staff_required def financial_reports_index(request): """Landing page listing all consolidated financial reports.""" return render(request, 'reports/financial/index.html', { 'report_title': 'Consolidated Financial Reports' }) from .financial_reports_service import ( get_sasapay_reconciliation, get_profit_and_loss, get_financial_statements, get_loan_aging, ) # ── helpers ──────────────────────────────────────────────────────────────── def _parse_date(value): """Parse a YYYY-MM-DD string, return None on failure.""" if not value: return None try: return datetime.strptime(value, '%Y-%m-%d').date() except ValueError: return None def _default_period(): """Return first day of current month and today.""" today = date.today() return today.replace(day=1), today # ── 1. SasaPay Reconciliation ────────────────────────────────────────────── @login_required @staff_required def sasapay_reconciliation(request): today = date.today() default_start = today.replace(day=1) start_date = _parse_date(request.GET.get('start_date')) or default_start end_date = _parse_date(request.GET.get('end_date')) or today data = get_sasapay_reconciliation(start_date, end_date) # Paginate IPN detail from django.core.paginator import Paginator ipn_page = Paginator(data['ipn_detail_qs'], 25).get_page(request.GET.get('ipn_page', 1)) disb_page = Paginator(data['disbursement_detail_qs'], 25).get_page(request.GET.get('disb_page', 1)) unk_page = Paginator(data['unknown_detail_qs'], 25).get_page(request.GET.get('unk_page', 1)) context = { **data, 'ipn_page': ipn_page, 'disb_page': disb_page, 'unk_page': unk_page, 'start_date': start_date, 'end_date': end_date, 'report_title': 'SasaPay Reconciliation Report', } return render(request, 'reports/financial/sasapay_reconciliation.html', context) # ── 2. Profit & Loss ─────────────────────────────────────────────────────── @login_required @staff_required def profit_and_loss(request): today = date.today() default_start = today.replace(day=1) start_date = _parse_date(request.GET.get('start_date')) or default_start end_date = _parse_date(request.GET.get('end_date')) or today branch_id = request.GET.get('branch') or None branches = Branch.objects.filter(is_active=True).order_by('name') data = get_profit_and_loss(start_date, end_date, branch_id) context = { **data, 'start_date': start_date, 'end_date': end_date, 'branches': branches, 'selected_branch': branch_id, 'report_title': 'Profit & Loss Statement', } return render(request, 'reports/financial/profit_and_loss.html', context) # ── 3. Financial Statements ──────────────────────────────────────────────── @login_required @staff_required def financial_statements(request): as_of_date = _parse_date(request.GET.get('as_of')) or date.today() branch_id = request.GET.get('branch') or None branches = Branch.objects.filter(is_active=True).order_by('name') data = get_financial_statements(as_of_date, branch_id) context = { **data, 'branches': branches, 'selected_branch': branch_id, 'report_title': 'Financial Statements', } return render(request, 'reports/financial/financial_statements.html', context) # ── 4. Loan Aging ────────────────────────────────────────────────────────── @login_required @staff_required def loan_aging(request): branch_id = request.GET.get('branch') or None product_id = request.GET.get('product') or None branches = Branch.objects.filter(is_active=True).order_by('name') products = LoanProduct.objects.filter(is_active=True).order_by('name') data = get_loan_aging(branch_id, product_id, include_detail=True) context = { **data, 'branches': branches, 'products': products, 'selected_branch': branch_id, 'selected_product': product_id, 'report_title': 'Loan Aging Report', } return render(request, 'reports/financial/loan_aging.html', context) @login_required @staff_required def loan_aging_export_csv(request): """Export loan aging detail as CSV.""" branch_id = request.GET.get('branch') or None product_id = request.GET.get('product') or None data = get_loan_aging(branch_id, product_id, include_detail=True) import csv response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = ( f'attachment; filename="loan_aging_{data["as_of"]}.csv"' ) writer = csv.writer(response) writer.writerow([ 'Loan Number', 'Borrower', 'Phone', 'Product', 'Principal (KES)', 'Outstanding (KES)', 'Due Date', 'Days Past Due', 'Aging Bucket' ]) for bucket in data['buckets']: for loan in bucket['loans']: writer.writerow([ loan['loan_number'], loan['borrower_name'], loan['borrower_phone'], loan['product'], loan['principal'], loan['outstanding'], loan['due_date'], loan['days_past_due'], bucket['label'], ]) return response # ── 5. Chart of Accounts ────────────────────────────────────────────────── @login_required @staff_required def chart_of_accounts(request): """Display complete Chart of Accounts with balances, date and type filtering.""" from decimal import Decimal # Parse filters as_of_date = date.today() if request.GET.get('as_of'): try: as_of_date = datetime.strptime(request.GET.get('as_of'), '%Y-%m-%d').date() except ValueError: pass type_filter = request.GET.get('type_filter', 'all') status_filter = request.GET.get('status_filter', 'active') try: from accounting.models import Account from accounting.services.accounting_service import AccountingService accounting_service = AccountingService() # Build base queryset qs = Account.objects.all().order_by('code') if status_filter == 'active': qs = qs.filter(is_active=True) # Get all accounts grouped by type asset_accounts = [] liability_accounts = [] equity_accounts = [] income_accounts = [] expense_accounts = [] type_map = { 'asset': asset_accounts, 'liability': liability_accounts, 'equity': equity_accounts, 'income': income_accounts, 'expense': expense_accounts, } # Only load the requested type(s) if type_filter != 'all': qs = qs.filter(account_type=type_filter) for account in qs: try: balance = accounting_service.calculate_account_balance( account=account, as_of_date=as_of_date, branch=None ) except Exception: balance = Decimal('0.00') account_data = { 'code': account.code, 'name': account.name, 'description': account.description or '', 'subtype': account.subtype or '', 'is_system_account': account.is_system_account, 'is_active': account.is_active, 'balance': balance, } bucket = type_map.get(account.account_type) if bucket is not None: bucket.append(account_data) total_qs = Account.objects.all() active_qs = Account.objects.filter(is_active=True) context = { 'asset_accounts': asset_accounts, 'liability_accounts': liability_accounts, 'equity_accounts': equity_accounts, 'income_accounts': income_accounts, 'expense_accounts': expense_accounts, 'asset_count': len(asset_accounts), 'liability_count': len(liability_accounts), 'equity_count': len(equity_accounts), 'income_count': len(income_accounts), 'expense_count': len(expense_accounts), 'total_accounts': total_qs.count(), 'active_accounts': active_qs.count(), 'system_accounts': total_qs.filter(is_system_account=True).count(), 'account_types': 5, 'as_of': as_of_date, 'today': date.today(), 'type_filter': type_filter, 'status_filter': status_filter, 'report_title': 'Chart of Accounts', } return render(request, 'reports/financial/chart_of_accounts.html', context) except ImportError: context = { 'asset_accounts': [], 'liability_accounts': [], 'equity_accounts': [], 'income_accounts': [], 'expense_accounts': [], 'asset_count': 0, 'liability_count': 0, 'equity_count': 0, 'income_count': 0, 'expense_count': 0, 'total_accounts': 0, 'active_accounts': 0, 'system_accounts': 0, 'account_types': 5, 'as_of': date.today(), 'today': date.today(), 'type_filter': 'all', 'status_filter': 'active', 'report_title': 'Chart of Accounts', 'error_message': 'Accounting system not configured.', } return render(request, 'reports/financial/chart_of_accounts.html', context) # ── 6. Account Ledger (Drill-Down) ──────────────────────────────────────── @login_required @staff_required def account_ledger(request, account_code): """Display detailed general ledger entries for a specific account.""" from datetime import timedelta from decimal import Decimal from django.core.paginator import Paginator try: from accounting.models import Account, GeneralLedger from accounting.services.accounting_service import AccountingService # Get the account account = Account.objects.get(code=account_code) accounting_service = AccountingService() # Get date filters end_date = date.today() start_date = end_date - timedelta(days=90) # Default to last 90 days if request.GET.get('start_date'): try: start_date = datetime.strptime(request.GET.get('start_date'), '%Y-%m-%d').date() except ValueError: pass if request.GET.get('end_date'): try: end_date = datetime.strptime(request.GET.get('end_date'), '%Y-%m-%d').date() except ValueError: pass # Get search filter search_reference = request.GET.get('reference', '') # Get opening balance (day before start date) opening_balance = Decimal('0.00') if start_date > date(2020, 1, 1): # Avoid going too far back opening_balance = accounting_service.calculate_account_balance( account=account, as_of_date=start_date - timedelta(days=1), branch=None ) # Get transactions in period transactions_qs = GeneralLedger.objects.filter( account=account, transaction_date__gte=start_date, transaction_date__lte=end_date ).select_related('journal_entry', 'posted_by').order_by('transaction_date', 'posted_at') # Apply search filter if search_reference: transactions_qs = transactions_qs.filter(reference_number__icontains=search_reference) # Calculate totals total_debits = Decimal('0.00') total_credits = Decimal('0.00') # Paginate paginator = Paginator(transactions_qs, 50) # 50 per page page_number = request.GET.get('page', 1) page_obj = paginator.get_page(page_number) # Prepare transaction data with posted_by username transactions = [] for entry in page_obj: transactions.append({ 'transaction_date': entry.transaction_date, 'reference_number': entry.reference_number, 'description': entry.description, 'debit_amount': entry.debit_amount, 'credit_amount': entry.credit_amount, 'balance': entry.balance, 'posted_by': entry.posted_by.get_full_name() if entry.posted_by else 'System', 'journal_entry_id': entry.journal_entry.id if entry.journal_entry else None, }) total_debits += entry.debit_amount total_credits += entry.credit_amount # Calculate net change and current balance net_change = total_debits - total_credits if account.account_type in ['asset', 'expense'] else total_credits - total_debits current_balance = accounting_service.calculate_account_balance( account=account, as_of_date=end_date, branch=None ) # Handle export export_format = request.GET.get('export') if export_format == 'pdf': return _export_account_ledger_pdf(account, transactions, opening_balance, current_balance, start_date, end_date) elif export_format == 'excel': return _export_account_ledger_excel(account, transactions, opening_balance, current_balance, start_date, end_date) context = { 'account': account, 'transactions': transactions, 'opening_balance': opening_balance, 'current_balance': current_balance, 'total_debits': total_debits, 'total_credits': total_credits, 'net_change': net_change, 'total_transactions': transactions_qs.count(), 'start_date': start_date, 'end_date': end_date, 'search_reference': search_reference, 'page_obj': page_obj, 'is_paginated': page_obj.has_other_pages(), 'report_title': f'Account Ledger: {account.code} - {account.name}', } return render(request, 'reports/financial/account_ledger.html', context) except Account.DoesNotExist: from django.contrib import messages messages.error(request, f'Account {account_code} not found.') return redirect('reports:chart_of_accounts') except ImportError: from django.contrib import messages messages.error(request, 'Accounting system not configured.') return redirect('reports:chart_of_accounts') def _export_account_ledger_pdf(account, transactions, opening_balance, current_balance, start_date, end_date): """Export account ledger to PDF.""" # TODO: Implement PDF export from django.http import HttpResponse response = HttpResponse(content_type='application/pdf') response['Content-Disposition'] = f'attachment; filename="account_ledger_{account.code}_{start_date}.pdf"' response.write(b'PDF export coming soon!') return response def _export_account_ledger_excel(account, transactions, opening_balance, current_balance, start_date, end_date): """Export account ledger to Excel.""" import csv from django.http import HttpResponse response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = f'attachment; filename="account_ledger_{account.code}_{start_date}.csv"' writer = csv.writer(response) writer.writerow(['Account Ledger Report']) writer.writerow([f'Account: {account.code} - {account.name}']) writer.writerow([f'Period: {start_date} to {end_date}']) writer.writerow([]) writer.writerow(['Date', 'Reference', 'Description', 'Debit', 'Credit', 'Balance', 'Posted By']) # Opening balance writer.writerow([start_date, '', 'Opening Balance', '', '', opening_balance, '']) # Transactions for entry in transactions: writer.writerow([ entry['transaction_date'], entry['reference_number'], entry['description'], entry['debit_amount'] if entry['debit_amount'] > 0 else '', entry['credit_amount'] if entry['credit_amount'] > 0 else '', entry['balance'], entry['posted_by'], ]) # Closing balance writer.writerow([end_date, '', 'Closing Balance', '', '', current_balance, '']) return response # ── 7. Journal Entry Detail ─────────────────────────────────────────────── @login_required @staff_required def journal_entry_detail(request, entry_id): """Display detailed view of a journal entry.""" try: from accounting.models import JournalEntry entry = JournalEntry.objects.get(id=entry_id) lines = entry.lines.all().select_related('account').order_by('line_number') total_debits = sum(line.debit_amount for line in lines) total_credits = sum(line.credit_amount for line in lines) balanced = total_debits == total_credits context = { 'entry': entry, 'lines': lines, 'total_debits': total_debits, 'total_credits': total_credits, 'balanced': balanced, 'report_title': f'Journal Entry: {entry.reference_number}', } return render(request, 'reports/financial/journal_entry_detail.html', context) except Exception as e: from django.contrib import messages messages.error(request, f'Error loading journal entry: {e}') return redirect('reports:reports_dashboard') # ══════════════════════════════════════════════════════════════════════════ # PDF EXPORT VIEWS # ══════════════════════════════════════════════════════════════════════════ @login_required @staff_required def chart_of_accounts_pdf(request): """Export Chart of Accounts as PDF with optional date filter.""" try: from accounting.models import Account from accounting.services.accounting_service import AccountingService from accounting.pdf_reports import AccountingPDFGenerator accounting_service = AccountingService() as_of_date = date.today() if request.GET.get('as_of'): try: as_of_date = datetime.strptime(request.GET.get('as_of'), '%Y-%m-%d').date() except ValueError: pass # Get all active accounts grouped by type accounts_by_type = { 'asset': [], 'liability': [], 'equity': [], 'income': [], 'expense': [] } for account in Account.objects.filter(is_active=True).order_by('code'): try: balance = accounting_service.calculate_account_balance( account=account, as_of_date=as_of_date, branch=None ) except Exception: balance = Decimal('0.00') account_data = { 'code': account.code, 'name': account.name, 'description': account.description or '', 'subtype': account.subtype or '', 'balance': balance, } if account.account_type in accounts_by_type: accounts_by_type[account.account_type].append(account_data) pdf_generator = AccountingPDFGenerator() pdf_buffer = pdf_generator.generate_chart_of_accounts_pdf( accounts_by_type=accounts_by_type, as_of_date=as_of_date, include_balances=True ) response = HttpResponse(pdf_buffer, content_type='application/pdf') response['Content-Disposition'] = ( f'inline; filename="chart_of_accounts_{as_of_date}.pdf"' ) return response except ImportError: messages.error(request, 'Accounting system not configured.') return redirect('reports:chart_of_accounts') except Exception as e: messages.error(request, f'Error generating PDF: {str(e)}') return redirect('reports:chart_of_accounts') @login_required @staff_required def account_ledger_pdf(request, account_code): """Export Account Ledger as PDF.""" from datetime import timedelta try: from accounting.models import Account, GeneralLedger from accounting.services.accounting_service import AccountingService from accounting.pdf_reports import AccountingPDFGenerator # Get the account account = Account.objects.get(code=account_code) accounting_service = AccountingService() # Get date filters end_date = date.today() start_date = end_date - timedelta(days=90) # Default to last 90 days if request.GET.get('start_date'): try: start_date = datetime.strptime(request.GET.get('start_date'), '%Y-%m-%d').date() except ValueError: pass if request.GET.get('end_date'): try: end_date = datetime.strptime(request.GET.get('end_date'), '%Y-%m-%d').date() except ValueError: pass # Get transactions in period transactions_qs = GeneralLedger.objects.filter( account=account, transaction_date__gte=start_date, transaction_date__lte=end_date ).select_related('journal_entry', 'posted_by').order_by('transaction_date', 'posted_at') # Prepare transaction data transactions = [] for entry in transactions_qs: transactions.append({ 'date': entry.transaction_date, 'description': entry.description, 'debit': entry.debit_amount if entry.debit_amount > 0 else None, 'credit': entry.credit_amount if entry.credit_amount > 0 else None, 'balance': entry.balance, }) # Account info account_info = { 'code': account.code, 'name': account.name, 'type': account.account_type, 'subtype': account.subtype } # Opening balance (day before start) opening_balance = Decimal('0.00') try: from accounting.services.accounting_service import AccountingService _svc = AccountingService() opening_balance = _svc.calculate_account_balance( account=account, as_of_date=start_date - timedelta(days=1), branch=None ) except Exception: pass # Generate PDF pdf_generator = AccountingPDFGenerator() pdf_buffer = pdf_generator.generate_account_ledger_pdf( account=account_info, transactions=transactions, start_date=start_date, end_date=end_date, opening_balance=opening_balance, ) # Return PDF response — inline so it opens in browser tab response = HttpResponse(pdf_buffer, content_type='application/pdf') response['Content-Disposition'] = ( f'inline; filename="account_ledger_{account_code}' f'_{start_date}_to_{end_date}.pdf"' ) return response except Account.DoesNotExist: messages.error(request, f'Account {account_code} not found.') return redirect('reports:chart_of_accounts') except Exception as e: messages.error(request, f'Error generating PDF: {str(e)}') return redirect('reports:chart_of_accounts') @login_required @staff_required def trial_balance_pdf(request): """Export Trial Balance as PDF with optional date filter.""" try: from accounting.models import Account from accounting.services.accounting_service import AccountingService from accounting.pdf_reports import AccountingPDFGenerator accounting_service = AccountingService() as_of_date = date.today() if request.GET.get('as_of'): try: as_of_date = datetime.strptime(request.GET.get('as_of'), '%Y-%m-%d').date() except ValueError: pass trial_balance_data = accounting_service.get_trial_balance( as_of_date=as_of_date, branch=None ) pdf_generator = AccountingPDFGenerator() pdf_buffer = pdf_generator.generate_trial_balance_pdf( trial_balance_data=trial_balance_data, as_of_date=as_of_date ) response = HttpResponse(pdf_buffer, content_type='application/pdf') response['Content-Disposition'] = ( f'inline; filename="trial_balance_{as_of_date}.pdf"' ) return response except Exception as e: messages.error(request, f'Error generating Trial Balance PDF: {str(e)}') return redirect('reports:chart_of_accounts') @login_required @staff_required def income_statement_pdf(request): """Export Income Statement (Profit & Loss) as PDF with date range filter.""" from datetime import timedelta try: from accounting.models import Account from accounting.services.accounting_service import AccountingService from accounting.pdf_reports import AccountingPDFGenerator accounting_service = AccountingService() # Get date range end_date = date.today() start_date = end_date.replace(day=1) # First day of current month if request.GET.get('start_date'): try: start_date = datetime.strptime(request.GET.get('start_date'), '%Y-%m-%d').date() except ValueError: pass if request.GET.get('end_date'): try: end_date = datetime.strptime(request.GET.get('end_date'), '%Y-%m-%d').date() except ValueError: pass # Get income accounts - period activity income_accounts = Account.objects.filter(account_type='income', is_active=True).order_by('code') income_items = [] for account in income_accounts: balance = accounting_service.calculate_account_balance(account, end_date, None) opening = accounting_service.calculate_account_balance(account, start_date - timedelta(days=1), None) if start_date > date(2020, 1, 1) else Decimal('0.00') period_activity = balance - opening if period_activity != Decimal('0.00'): income_items.append({'code': account.code, 'name': account.name, 'amount': period_activity}) # Get expense accounts - period activity expense_accounts = Account.objects.filter(account_type='expense', is_active=True).order_by('code') expense_items = [] for account in expense_accounts: balance = accounting_service.calculate_account_balance(account, end_date, None) opening = accounting_service.calculate_account_balance(account, start_date - timedelta(days=1), None) if start_date > date(2020, 1, 1) else Decimal('0.00') period_activity = balance - opening if period_activity != Decimal('0.00'): expense_items.append({'code': account.code, 'name': account.name, 'amount': period_activity}) income_data = {'income': income_items, 'expenses': expense_items} pdf_generator = AccountingPDFGenerator() pdf_buffer = pdf_generator.generate_income_statement_pdf( income_data=income_data, start_date=start_date, end_date=end_date ) response = HttpResponse(pdf_buffer, content_type='application/pdf') response['Content-Disposition'] = ( f'inline; filename="income_statement_{start_date}_to_{end_date}.pdf"' ) return response except Exception as e: messages.error(request, f'Error generating Income Statement PDF: {str(e)}') return redirect('reports:income_statement') @login_required @staff_required def balance_sheet_pdf(request): """Export Balance Sheet as PDF with optional date filter.""" try: from accounting.models import Account from accounting.services.accounting_service import AccountingService from accounting.pdf_reports import AccountingPDFGenerator accounting_service = AccountingService() as_of_date = date.today() if request.GET.get('as_of'): try: as_of_date = datetime.strptime(request.GET.get('as_of'), '%Y-%m-%d').date() except ValueError: pass # Get asset accounts asset_accounts = Account.objects.filter(account_type='asset', is_active=True).order_by('code') asset_items = [] for account in asset_accounts: balance = accounting_service.calculate_account_balance(account, as_of_date, None) if balance != Decimal('0.00'): asset_items.append({'code': account.code, 'name': account.name, 'amount': balance}) # Get liability accounts liability_accounts = Account.objects.filter(account_type='liability', is_active=True).order_by('code') liability_items = [] for account in liability_accounts: balance = accounting_service.calculate_account_balance(account, as_of_date, None) if balance != Decimal('0.00'): liability_items.append({'code': account.code, 'name': account.name, 'amount': balance}) # Get equity accounts equity_accounts = Account.objects.filter(account_type='equity', is_active=True).order_by('code') equity_items = [] for account in equity_accounts: balance = accounting_service.calculate_account_balance(account, as_of_date, None) if balance != Decimal('0.00'): equity_items.append({'code': account.code, 'name': account.name, 'amount': balance}) balance_data = { 'assets': asset_items, 'liabilities': liability_items, 'equity': equity_items } pdf_generator = AccountingPDFGenerator() pdf_buffer = pdf_generator.generate_balance_sheet_pdf( balance_data=balance_data, as_of_date=as_of_date ) response = HttpResponse(pdf_buffer, content_type='application/pdf') response['Content-Disposition'] = ( f'inline; filename="balance_sheet_{as_of_date}.pdf"' ) return response except Exception as e: messages.error(request, f'Error generating Balance Sheet PDF: {str(e)}') return redirect('reports:balance_sheet') # ══════════════════════════════════════════════════════════════════════════ # INTERACTIVE HTML REPORT VIEWS (with date filtering) # ══════════════════════════════════════════════════════════════════════════ @login_required @staff_required def trial_balance(request): """Display Trial Balance with date filtering.""" from decimal import Decimal try: from accounting.models import Account from accounting.services.accounting_service import AccountingService accounting_service = AccountingService() # Get date filter as_of_date = date.today() if request.GET.get('as_of'): try: as_of_date = datetime.strptime(request.GET.get('as_of'), '%Y-%m-%d').date() except ValueError: pass # Handle export requests export_format = request.GET.get('format') if export_format == 'pdf': return trial_balance_pdf(request) elif export_format == 'excel': return _export_trial_balance_excel(as_of_date) # Generate trial balance data trial_balance_data = accounting_service.get_trial_balance( as_of_date=as_of_date, branch=None ) accounts = trial_balance_data.get('accounts', []) total_debits = Decimal('0.00') total_credits = Decimal('0.00') for account in accounts: total_debits += account.get('debit_balance', Decimal('0.00')) total_credits += account.get('credit_balance', Decimal('0.00')) difference = abs(total_debits - total_credits) is_balanced = difference < Decimal('0.01') context = { 'accounts': accounts, 'total_debits': total_debits, 'total_credits': total_credits, 'difference': difference, 'is_balanced': is_balanced, 'balance_status': 'balanced' if is_balanced else 'unbalanced', 'account_count': len(accounts), 'as_of_date': as_of_date, 'today': date.today(), 'report_title': 'Trial Balance', } return render(request, 'reports/financial/trial_balance.html', context) except ImportError: messages.error(request, 'Accounting system not configured.') return redirect('reports:financial_reports_index') except Exception as e: messages.error(request, f'Error generating trial balance: {str(e)}') return redirect('reports:financial_reports_index') def _export_trial_balance_excel(as_of_date): """Export trial balance to Excel/CSV.""" import csv from django.http import HttpResponse from accounting.models import Account from accounting.services.accounting_service import AccountingService accounting_service = AccountingService() trial_balance_data = accounting_service.get_trial_balance( as_of_date=as_of_date, branch=None ) response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = f'attachment; filename="trial_balance_{as_of_date}.csv"' writer = csv.writer(response) writer.writerow(['Trial Balance Report']) writer.writerow([f'As of: {as_of_date}']) writer.writerow([]) writer.writerow(['Code', 'Account Name', 'Debit (KES)', 'Credit (KES)']) total_debit = Decimal('0.00') total_credit = Decimal('0.00') for account in trial_balance_data.get('accounts', []): debit = account.get('debit_balance', Decimal('0.00')) credit = account.get('credit_balance', Decimal('0.00')) total_debit += debit total_credit += credit writer.writerow([ account['code'], account['name'], f"{debit:.2f}" if debit > 0 else '', f"{credit:.2f}" if credit > 0 else '' ]) writer.writerow([]) writer.writerow(['TOTAL', '', f"{total_debit:.2f}", f"{total_credit:.2f}"]) return response @login_required @staff_required def income_statement(request): """Display Income Statement (Profit & Loss) with date range filtering.""" from decimal import Decimal try: from accounting.models import Account from accounting.services.accounting_service import AccountingService accounting_service = AccountingService() # Get date range end_date = date.today() start_date = end_date.replace(day=1) # First day of current month if request.GET.get('start_date'): try: start_date = datetime.strptime(request.GET.get('start_date'), '%Y-%m-%d').date() except ValueError: pass if request.GET.get('end_date'): try: end_date = datetime.strptime(request.GET.get('end_date'), '%Y-%m-%d').date() except ValueError: pass # Handle export requests export_format = request.GET.get('format') if export_format == 'pdf': return income_statement_pdf(request) elif export_format == 'excel': return _export_income_statement_excel(start_date, end_date) # Get income accounts with period activity income_accounts = Account.objects.filter(account_type='income', is_active=True).order_by('code') income_items = [] total_income = Decimal('0.00') for account in income_accounts: balance = accounting_service.calculate_account_balance(account, end_date, None) opening_balance = accounting_service.calculate_account_balance(account, start_date - timedelta(days=1), None) if start_date > date(2020, 1, 1) else Decimal('0.00') period_activity = balance - opening_balance if period_activity != Decimal('0.00'): income_items.append({ 'code': account.code, 'name': account.name, 'amount': period_activity, 'cumulative': balance }) total_income += period_activity # Get expense accounts with period activity expense_accounts = Account.objects.filter(account_type='expense', is_active=True).order_by('code') expense_items = [] total_expenses = Decimal('0.00') for account in expense_accounts: balance = accounting_service.calculate_account_balance(account, end_date, None) opening_balance = accounting_service.calculate_account_balance(account, start_date - timedelta(days=1), None) if start_date > date(2020, 1, 1) else Decimal('0.00') period_activity = balance - opening_balance if period_activity != Decimal('0.00'): expense_items.append({ 'code': account.code, 'name': account.name, 'amount': period_activity, 'cumulative': balance }) total_expenses += period_activity # Calculate net income net_income = total_income - total_expenses net_income_status = 'profit' if net_income >= 0 else 'loss' # Calculate key ratios gross_margin = (total_income / total_income * 100) if total_income > 0 else Decimal('0.00') expense_ratio = (total_expenses / total_income * 100) if total_income > 0 else Decimal('0.00') profit_margin = (net_income / total_income * 100) if total_income > 0 else Decimal('0.00') context = { 'income_items': income_items, 'expense_items': expense_items, 'total_income': total_income, 'total_expenses': total_expenses, 'net_income': net_income, 'net_income_status': net_income_status, 'gross_margin': gross_margin, 'expense_ratio': expense_ratio, 'profit_margin': profit_margin, 'start_date': start_date, 'end_date': end_date, 'today': date.today(), 'report_title': 'Income Statement (Profit & Loss)', } return render(request, 'reports/financial/income_statement.html', context) except ImportError: messages.error(request, 'Accounting system not configured.') return redirect('reports:financial_reports_index') except Exception as e: messages.error(request, f'Error generating income statement: {str(e)}') return redirect('reports:financial_reports_index') def _export_income_statement_excel(start_date, end_date): """Export income statement to Excel/CSV.""" import csv from django.http import HttpResponse from accounting.models import Account from accounting.services.accounting_service import AccountingService from decimal import Decimal accounting_service = AccountingService() response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = f'attachment; filename="income_statement_{start_date}_to_{end_date}.csv"' writer = csv.writer(response) writer.writerow(['Income Statement (Profit & Loss)']) writer.writerow([f'Period: {start_date} to {end_date}']) writer.writerow([]) # Income section writer.writerow(['REVENUE & INCOME']) writer.writerow(['Code', 'Account Name', 'Amount (KES)']) income_accounts = Account.objects.filter(account_type='income', is_active=True).order_by('code') total_income = Decimal('0.00') for account in income_accounts: balance = accounting_service.calculate_account_balance(account, end_date, None) opening_balance = accounting_service.calculate_account_balance(account, start_date - timedelta(days=1), None) if start_date > date(2020, 1, 1) else Decimal('0.00') period_activity = balance - opening_balance if period_activity != Decimal('0.00'): writer.writerow([account.code, account.name, f"{period_activity:.2f}"]) total_income += period_activity writer.writerow(['', 'TOTAL INCOME', f"{total_income:.2f}"]) writer.writerow([]) # Expenses section writer.writerow(['EXPENSES']) writer.writerow(['Code', 'Account Name', 'Amount (KES)']) expense_accounts = Account.objects.filter(account_type='expense', is_active=True).order_by('code') total_expenses = Decimal('0.00') for account in expense_accounts: balance = accounting_service.calculate_account_balance(account, end_date, None) opening_balance = accounting_service.calculate_account_balance(account, start_date - timedelta(days=1), None) if start_date > date(2020, 1, 1) else Decimal('0.00') period_activity = balance - opening_balance if period_activity != Decimal('0.00'): writer.writerow([account.code, account.name, f"{period_activity:.2f}"]) total_expenses += period_activity writer.writerow(['', 'TOTAL EXPENSES', f"{total_expenses:.2f}"]) writer.writerow([]) # Net income net_income = total_income - total_expenses writer.writerow(['', 'NET INCOME (LOSS)', f"{net_income:.2f}"]) return response @login_required @staff_required def balance_sheet(request): """Display Balance Sheet with date filtering.""" from decimal import Decimal try: from accounting.models import Account from accounting.services.accounting_service import AccountingService accounting_service = AccountingService() # Get date filter as_of_date = date.today() if request.GET.get('as_of'): try: as_of_date = datetime.strptime(request.GET.get('as_of'), '%Y-%m-%d').date() except ValueError: pass # Handle export requests export_format = request.GET.get('format') if export_format == 'pdf': return balance_sheet_pdf(request) elif export_format == 'excel': return _export_balance_sheet_excel(as_of_date) # Get asset accounts asset_accounts = Account.objects.filter(account_type='asset', is_active=True).order_by('code') asset_items = [] total_assets = Decimal('0.00') for account in asset_accounts: balance = accounting_service.calculate_account_balance(account, as_of_date, None) if balance != Decimal('0.00'): asset_items.append({ 'code': account.code, 'name': account.name, 'subtype': account.subtype or 'asset', 'amount': balance }) total_assets += balance # Get liability accounts liability_accounts = Account.objects.filter(account_type='liability', is_active=True).order_by('code') liability_items = [] total_liabilities = Decimal('0.00') for account in liability_accounts: balance = accounting_service.calculate_account_balance(account, as_of_date, None) if balance != Decimal('0.00'): liability_items.append({ 'code': account.code, 'name': account.name, 'subtype': account.subtype or 'liability', 'amount': balance }) total_liabilities += balance # Get equity accounts equity_accounts = Account.objects.filter(account_type='equity', is_active=True).order_by('code') equity_items = [] total_equity = Decimal('0.00') for account in equity_accounts: balance = accounting_service.calculate_account_balance(account, as_of_date, None) if balance != Decimal('0.00'): equity_items.append({ 'code': account.code, 'name': account.name, 'subtype': account.subtype or 'equity', 'amount': balance }) total_equity += balance # Calculate totals and check balance total_liabilities_equity = total_liabilities + total_equity difference = abs(total_assets - total_liabilities_equity) is_balanced = difference < Decimal('0.01') # Calculate key ratios current_ratio = Decimal('0.00') debt_to_equity = Decimal('0.00') equity_ratio = Decimal('0.00') if total_assets > 0: equity_ratio = (total_equity / total_assets * 100) if total_equity > 0: debt_to_equity = (total_liabilities / total_equity) context = { 'asset_items': asset_items, 'liability_items': liability_items, 'equity_items': equity_items, 'total_assets': total_assets, 'total_liabilities': total_liabilities, 'total_equity': total_equity, 'total_liabilities_equity': total_liabilities_equity, 'difference': difference, 'is_balanced': is_balanced, 'balance_status': 'balanced' if is_balanced else 'unbalanced', 'equity_ratio': equity_ratio, 'debt_to_equity': debt_to_equity, 'as_of_date': as_of_date, 'today': date.today(), 'report_title': 'Balance Sheet', } return render(request, 'reports/financial/balance_sheet.html', context) except ImportError: messages.error(request, 'Accounting system not configured.') return redirect('reports:financial_reports_index') except Exception as e: messages.error(request, f'Error generating balance sheet: {str(e)}') return redirect('reports:financial_reports_index') def _export_balance_sheet_excel(as_of_date): """Export balance sheet to Excel/CSV.""" import csv from django.http import HttpResponse from accounting.models import Account from accounting.services.accounting_service import AccountingService from decimal import Decimal accounting_service = AccountingService() response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = f'attachment; filename="balance_sheet_{as_of_date}.csv"' writer = csv.writer(response) writer.writerow(['Balance Sheet']) writer.writerow([f'As of: {as_of_date}']) writer.writerow([]) # Assets writer.writerow(['ASSETS']) writer.writerow(['Code', 'Account Name', 'Amount (KES)']) asset_accounts = Account.objects.filter(account_type='asset', is_active=True).order_by('code') total_assets = Decimal('0.00') for account in asset_accounts: balance = accounting_service.calculate_account_balance(account, as_of_date, None) if balance != Decimal('0.00'): writer.writerow([account.code, account.name, f"{balance:.2f}"]) total_assets += balance writer.writerow(['', 'TOTAL ASSETS', f"{total_assets:.2f}"]) writer.writerow([]) # Liabilities writer.writerow(['LIABILITIES']) writer.writerow(['Code', 'Account Name', 'Amount (KES)']) liability_accounts = Account.objects.filter(account_type='liability', is_active=True).order_by('code') total_liabilities = Decimal('0.00') for account in liability_accounts: balance = accounting_service.calculate_account_balance(account, as_of_date, None) if balance != Decimal('0.00'): writer.writerow([account.code, account.name, f"{balance:.2f}"]) total_liabilities += balance writer.writerow(['', 'TOTAL LIABILITIES', f"{total_liabilities:.2f}"]) writer.writerow([]) # Equity writer.writerow(['EQUITY']) writer.writerow(['Code', 'Account Name', 'Amount (KES)']) equity_accounts = Account.objects.filter(account_type='equity', is_active=True).order_by('code') total_equity = Decimal('0.00') for account in equity_accounts: balance = accounting_service.calculate_account_balance(account, as_of_date, None) if balance != Decimal('0.00'): writer.writerow([account.code, account.name, f"{balance:.2f}"]) total_equity += balance writer.writerow(['', 'TOTAL EQUITY', f"{total_equity:.2f}"]) writer.writerow([]) writer.writerow(['', 'TOTAL LIABILITIES & EQUITY', f"{total_liabilities + total_equity:.2f}"]) return response @login_required @staff_required def cash_flow_statement(request): """Display Cash Flow Statement with date range filtering.""" from decimal import Decimal from datetime import timedelta try: from accounting.models import Account, GeneralLedger from accounting.services.accounting_service import AccountingService accounting_service = AccountingService() # Get date range end_date = date.today() start_date = end_date.replace(day=1) # First day of current month if request.GET.get('start_date'): try: start_date = datetime.strptime(request.GET.get('start_date'), '%Y-%m-%d').date() except ValueError: pass if request.GET.get('end_date'): try: end_date = datetime.strptime(request.GET.get('end_date'), '%Y-%m-%d').date() except ValueError: pass # Handle export requests export_format = request.GET.get('format') if export_format == 'pdf': return cash_flow_statement_pdf(request) elif export_format == 'excel': return _export_cash_flow_excel(start_date, end_date) # Start with net income from income statement total_income = Decimal('0.00') total_expenses = Decimal('0.00') income_accounts = Account.objects.filter(account_type='income', is_active=True) for account in income_accounts: balance = accounting_service.calculate_account_balance(account, end_date, None) opening = accounting_service.calculate_account_balance(account, start_date - timedelta(days=1), None) if start_date > date(2020, 1, 1) else Decimal('0.00') total_income += (balance - opening) expense_accounts = Account.objects.filter(account_type='expense', is_active=True) for account in expense_accounts: balance = accounting_service.calculate_account_balance(account, end_date, None) opening = accounting_service.calculate_account_balance(account, start_date - timedelta(days=1), None) if start_date > date(2020, 1, 1) else Decimal('0.00') total_expenses += (balance - opening) net_income = total_income - total_expenses # Operating activities adjustments operating_adjustments = [] # Add back depreciation (non-cash expense) depreciation_accounts = Account.objects.filter( account_type='expense', name__icontains='depreciation', is_active=True ) total_depreciation = Decimal('0.00') for account in depreciation_accounts: balance = accounting_service.calculate_account_balance(account, end_date, None) opening = accounting_service.calculate_account_balance(account, start_date - timedelta(days=1), None) if start_date > date(2020, 1, 1) else Decimal('0.00') period_depreciation = balance - opening if period_depreciation > 0: operating_adjustments.append({ 'description': f'Add: {account.name}', 'amount': period_depreciation }) total_depreciation += period_depreciation # Changes in current assets/liabilities # (Simplified - would need more detailed tracking in production) net_cash_from_operations = net_income + total_depreciation # Investing activities (changes in fixed assets) investing_activities = [] fixed_asset_accounts = Account.objects.filter( account_type='asset', subtype='fixed_asset', is_active=True ).exclude(name__icontains='accumulated depreciation') net_investing = Decimal('0.00') for account in fixed_asset_accounts: balance = accounting_service.calculate_account_balance(account, end_date, None) opening = accounting_service.calculate_account_balance(account, start_date - timedelta(days=1), None) if start_date > date(2020, 1, 1) else Decimal('0.00') change = balance - opening if change != Decimal('0.00'): investing_activities.append({ 'description': f'Purchase of {account.name}' if change > 0 else f'Sale of {account.name}', 'amount': -change # Negative for purchases, positive for sales }) net_investing += -change # Financing activities (changes in loans and equity) financing_activities = [] loan_accounts = Account.objects.filter( account_type='liability', subtype='borrowings', is_active=True ) net_financing = Decimal('0.00') for account in loan_accounts: balance = accounting_service.calculate_account_balance(account, end_date, None) opening = accounting_service.calculate_account_balance(account, start_date - timedelta(days=1), None) if start_date > date(2020, 1, 1) else Decimal('0.00') change = balance - opening if change != Decimal('0.00'): financing_activities.append({ 'description': f'Proceeds from {account.name}' if change > 0 else f'Repayment of {account.name}', 'amount': change }) net_financing += change # Net change in cash net_cash_change = net_cash_from_operations + net_investing + net_financing # Cash beginning and ending balances cash_accounts = Account.objects.filter( Q(name__icontains='cash') | Q(name__icontains='bank') | Q(name__icontains='mpesa'), account_type='asset', is_active=True ) cash_beginning = Decimal('0.00') cash_ending = Decimal('0.00') for account in cash_accounts: opening = accounting_service.calculate_account_balance(account, start_date - timedelta(days=1), None) if start_date > date(2020, 1, 1) else Decimal('0.00') ending = accounting_service.calculate_account_balance(account, end_date, None) cash_beginning += opening cash_ending += ending context = { 'net_income': net_income, 'operating_adjustments': operating_adjustments, 'net_cash_from_operations': net_cash_from_operations, 'investing_activities': investing_activities, 'net_investing': net_investing, 'financing_activities': financing_activities, 'net_financing': net_financing, 'net_cash_change': net_cash_change, 'cash_beginning': cash_beginning, 'cash_ending': cash_ending, 'start_date': start_date, 'end_date': end_date, 'today': date.today(), 'report_title': 'Cash Flow Statement', } return render(request, 'reports/financial/cash_flow_statement.html', context) except ImportError: messages.error(request, 'Accounting system not configured.') return redirect('reports:financial_reports_index') except Exception as e: messages.error(request, f'Error generating cash flow statement: {str(e)}') return redirect('reports:financial_reports_index') def _export_cash_flow_excel(start_date, end_date): """Export cash flow statement to Excel/CSV.""" import csv from django.http import HttpResponse response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = f'attachment; filename="cash_flow_{start_date}_to_{end_date}.csv"' writer = csv.writer(response) writer.writerow(['Cash Flow Statement']) writer.writerow([f'Period: {start_date} to {end_date}']) writer.writerow([]) writer.writerow(['Section', 'Description', 'Amount (KES)']) # Note: Full implementation would mirror the cash_flow_statement view logic writer.writerow(['Operating Activities', 'Net Income', '0.00']) writer.writerow([]) writer.writerow(['NET CASH FROM OPERATIONS', '', '0.00']) return response @login_required @staff_required def cash_flow_statement_pdf(request): """Export Cash Flow Statement as PDF - placeholder.""" messages.info(request, 'Cash Flow PDF generation coming soon!') return redirect('reports:cash_flow_statement')