import json import logging from django.core.serializers.json import DjangoJSONEncoder logger = logging.getLogger(__name__) from django.shortcuts import render, get_object_or_404, redirect from django.contrib.auth.decorators import login_required from users.decorators import admin_required, staff_required, portfolio_access_required from .decorators import admin_only, admin_only_ajax from django.contrib import messages from django.db.models import Sum, Count, Q, F, Avg from django.utils import timezone from utils.datetime_utils import get_current_datetime, make_datetime_compatible from datetime import datetime, timedelta from decimal import Decimal, DecimalException from .models import ( LoanProduct, LoanApplication, Loan, Repayment, RolloverRequest, MpesaTransaction ) from utils.filtering import ( apply_branch_and_portfolio_filters, get_filtered_clients, get_filtered_loans, get_filtered_applications, get_filtered_repayments ) from users.models import CustomUser from reports.models import LoanScoring from utils.models import AuditLog, SystemSetting, Notification from django.core.paginator import Paginator, PageNotAnInteger, EmptyPage from django.http import JsonResponse, HttpResponse from django.db import models, IntegrityError import math import os # Import PDF generation functions from .minimal_analytics import ( generate_loans_dashboard_pdf, generate_clients_dashboard_pdf, generate_payments_dashboard_pdf, generate_reports_dashboard_pdf, generate_portfolio_dashboard_pdf, generate_custom_analytics_pdf, analytics_dashboard ) def generate_enhanced_dashboard_data(branch_id=None): """Generate comprehensive dashboard data with enhanced analytics""" today = timezone.now().date() current_month_start = today.replace(day=1) # Base querysets with branch filtering loans_qs = Loan.objects.all() repayments_qs = Repayment.objects.all() users_qs = CustomUser.objects.filter(role='borrower') if branch_id: loans_qs = loans_qs.filter(borrower__branch_id=branch_id) repayments_qs = repayments_qs.filter(loan__borrower__branch_id=branch_id) users_qs = users_qs.filter(branch_id=branch_id) # Enhanced Loan Performance Analytics loan_performance = { 'total_loans': loans_qs.count(), 'active_loans': loans_qs.filter(status='active').count(), 'paid_loans': loans_qs.filter(status='paid').count(), 'defaulted_loans': loans_qs.filter(status='defaulted').count(), 'total_disbursed': loans_qs.aggregate(total=Sum('principal_amount'))['total'] or 0, 'total_collected': repayments_qs.aggregate(total=Sum('amount'))['total'] or 0, } # Calculate performance metrics loan_performance['outstanding_amount'] = loan_performance['total_disbursed'] - loan_performance['total_collected'] loan_performance['collection_rate'] = ( (loan_performance['total_collected'] / loan_performance['total_disbursed'] * 100) if loan_performance['total_disbursed'] > 0 else 0 ) loan_performance['default_rate'] = ( (loan_performance['defaulted_loans'] / loan_performance['total_loans'] * 100) if loan_performance['total_loans'] > 0 else 0 ) # Enhanced Loan Distribution Analytics loan_distribution = loans_qs.values('status').annotate( count=Count('id'), total_amount=Sum('principal_amount') ).order_by('status') distribution_data = { 'labels': [], 'counts': [], 'amounts': [], 'colors': [] } color_map = { 'active': '#10B981', # Green 'paid': '#3B82F6', # Blue 'defaulted': '#EF4444', # Red 'rolled_over': '#F59E0B', # Yellow 'pending': '#8B5CF6' # Purple } for item in loan_distribution: distribution_data['labels'].append(item['status'].title()) distribution_data['counts'].append(item['count']) distribution_data['amounts'].append(float(item['total_amount'] or 0)) distribution_data['colors'].append(color_map.get(item['status'], '#6B7280')) # Monthly Performance Trends monthly_performance = [] monthly_labels = [] for i in range(12): month_start = current_month_start - timedelta(days=30*i) month_end = (month_start + timedelta(days=32)).replace(day=1) month_loans = loans_qs.filter( created_at__gte=month_start, created_at__lt=month_end ) month_repayments = repayments_qs.filter( payment_date__gte=month_start, payment_date__lt=month_end ) disbursed = month_loans.aggregate(total=Sum('principal_amount'))['total'] or 0 collected = month_repayments.aggregate(total=Sum('amount'))['total'] or 0 monthly_performance.insert(0, { 'month': month_start.strftime('%b %Y'), 'disbursed': float(disbursed), 'collected': float(collected), 'net_flow': float(disbursed - collected), 'loans_count': month_loans.count() }) monthly_labels.insert(0, month_start.strftime('%b %Y')) # Client Growth Analytics client_growth_data = [] for i in range(12): month_start = current_month_start - timedelta(days=30*i) month_end = (month_start + timedelta(days=32)).replace(day=1) new_clients = users_qs.filter( date_joined__gte=month_start, date_joined__lt=month_end ).count() client_growth_data.insert(0, { 'month': month_start.strftime('%b %Y'), 'new_clients': new_clients }) # Portfolio Quality Analytics overdue_loans = loans_qs.filter( status='active', due_date__lt=today ) portfolio_quality = { 'total_portfolio': loan_performance['total_disbursed'], 'overdue_count': overdue_loans.count(), 'overdue_amount': overdue_loans.aggregate(total=Sum('principal_amount'))['total'] or 0, 'current_loans': loans_qs.filter(status='active', due_date__gte=today).count(), } portfolio_quality['overdue_rate'] = ( (portfolio_quality['overdue_count'] / loan_performance['active_loans'] * 100) if loan_performance['active_loans'] > 0 else 0 ) # Risk Analytics risk_categories = { 'low_risk': overdue_loans.filter( due_date__gte=today - timedelta(days=30) ).count(), 'medium_risk': overdue_loans.filter( due_date__lt=today - timedelta(days=30), due_date__gte=today - timedelta(days=90) ).count(), 'high_risk': overdue_loans.filter( due_date__lt=today - timedelta(days=90) ).count(), } return { 'loan_performance': loan_performance, 'loan_distribution': distribution_data, 'client_growth': client_growth_data, 'monthly_performance': monthly_performance, 'portfolio_quality': portfolio_quality, 'risk_categories': risk_categories, 'monthly_labels': monthly_labels, 'generated_at': timezone.now().isoformat(), } @login_required @portfolio_access_required def filtered_loans(request): """View for filtered loans based on status""" status = request.GET.get('status', 'active') selected_branch_id = request.session.get('selected_branch_id') # Base queryset loans_qs = Loan.objects.all() # Apply portfolio-based access control for staff members if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser: # Staff members can only see loans from their assigned borrowers loans_qs = loans_qs.filter(borrower__portfolio_manager=request.user) elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser: # Secretaries and auditors can only see loans from borrowers in their branch if request.user.branch: loans_qs = loans_qs.filter(borrower__branch=request.user.branch) # Apply branch filtering if a branch is selected if selected_branch_id: loans_qs = loans_qs.filter(borrower__branch_id=selected_branch_id) # Apply status filtering - EXCLUDE rolled over loans from active if status == 'active': loans_qs = loans_qs.filter(status='active') # Only truly active loans elif status == 'overdue': # Dynamic overdue filtering: due_date < current_date AND outstanding_amount > 0 AND status = 'active' # outstanding_amount is a @property so we use DB fields: total_amount > _amount_paid_cache # Validates: Requirements 5.3, 5.4, 5.5, 5.6 from django.db.models import F loans_qs = loans_qs.filter( status='active', due_date__lt=timezone.now().date(), total_amount__gt=F('_amount_paid_cache') ) elif status == 'defaulted': loans_qs = loans_qs.filter(status='defaulted') elif status == 'overdue_1_30': from datetime import timedelta thirty_days_ago = timezone.now() - timedelta(days=30) loans_qs = loans_qs.filter(status='active', due_date__lt=timezone.now(), due_date__gte=thirty_days_ago) elif status == 'overdue_31_60': from datetime import timedelta sixty_days_ago = timezone.now() - timedelta(days=60) thirty_days_ago = timezone.now() - timedelta(days=30) loans_qs = loans_qs.filter(status='active', due_date__lt=thirty_days_ago, due_date__gte=sixty_days_ago) elif status == 'overdue_60_plus': from datetime import timedelta sixty_days_ago = timezone.now() - timedelta(days=60) loans_qs = loans_qs.filter(status='active', due_date__lt=sixty_days_ago) elif status == 'processed_this_month': from datetime import datetime current_month = datetime.now().replace(day=1, hour=0, minute=0, second=0, microsecond=0) loans_qs = loans_qs.filter(created_at__gte=current_month) elif status == 'interest_bearing': loans_qs = loans_qs.filter(interest_rate__gt=0) else: loans_qs = loans_qs.filter(status=status) # Order by most recent loans_qs = loans_qs.order_by('-created_at') # Pagination paginator = Paginator(loans_qs, 25) page = request.GET.get('page') try: loans = paginator.page(page) except PageNotAnInteger: loans = paginator.page(1) except EmptyPage: loans = paginator.page(paginator.num_pages) context = { 'loans': loans, 'status_filter': status, 'title': f'{status.title()} Loans', 'total_count': loans_qs.count(), 'total_amount': loans_qs.aggregate(total=Sum('principal_amount'))['total'] or 0, 'today': timezone.now().date(), } return render(request, 'loans/filtered_loans.html', context) @login_required def rolled_over_loans(request): """View for rolled over loans only""" selected_branch_id = request.session.get('selected_branch_id') # Base queryset for rolled over loans loans_qs = Loan.objects.filter(status='rolled_over') # Apply branch filtering if a branch is selected if selected_branch_id: loans_qs = loans_qs.filter(borrower__branch_id=selected_branch_id) # Order by most recent loans_qs = loans_qs.order_by('-created_at') # Pagination paginator = Paginator(loans_qs, 25) page = request.GET.get('page') try: loans = paginator.page(page) except PageNotAnInteger: loans = paginator.page(1) except EmptyPage: loans = paginator.page(paginator.num_pages) context = { 'loans': loans, 'status_filter': 'rolled_over', 'title': 'Rolled Over Loans', 'total_count': loans_qs.count(), 'total_amount': loans_qs.aggregate(total=Sum('principal_amount'))['total'] or 0, 'today': timezone.now().date(), } return render(request, 'loans/rolled_over_loans.html', context) @login_required def filtered_applications(request): """View for filtered loan applications based on status""" status = request.GET.get('status', 'pending') selected_branch_id = request.session.get('selected_branch_id') # Base queryset applications_qs = LoanApplication.objects.all() # Apply branch filtering if a branch is selected if selected_branch_id: applications_qs = applications_qs.filter(borrower__branch_id=selected_branch_id) # Apply status filtering applications_qs = applications_qs.filter(status=status) # Order by most recent applications_qs = applications_qs.order_by('-submitted_at') # Pagination paginator = Paginator(applications_qs, 25) page = request.GET.get('page') try: applications = paginator.page(page) except PageNotAnInteger: applications = paginator.page(1) except EmptyPage: applications = paginator.page(paginator.num_pages) context = { 'applications': applications, 'status_filter': status, 'title': f'{status.title()} Applications', 'total_count': applications_qs.count(), 'total_amount': applications_qs.aggregate(total=Sum('requested_amount'))['total'] or 0, } return render(request, 'loans/filtered_applications.html', context) @login_required @portfolio_access_required def dashboard(request): """Main dashboard view with statistics and charts""" # Get selected branch from session selected_branch_id = request.session.get('selected_branch_id') # Apply portfolio-based filtering for non-admin users if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser: # Staff members can only see data related to their assigned borrowers users_qs = CustomUser.objects.filter(role='borrower', portfolio_manager=request.user) loans_qs = Loan.objects.filter(borrower__portfolio_manager=request.user) applications_qs = LoanApplication.objects.filter(borrower__portfolio_manager=request.user) repayments_qs = Repayment.objects.filter(loan__borrower__portfolio_manager=request.user) elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser: # Secretaries and auditors can only see data related to borrowers in their branch if request.user.branch: users_qs = CustomUser.objects.filter(role='borrower', branch=request.user.branch) loans_qs = Loan.objects.filter(borrower__branch=request.user.branch) applications_qs = LoanApplication.objects.filter(borrower__branch=request.user.branch) repayments_qs = Repayment.objects.filter(loan__borrower__branch=request.user.branch) else: users_qs = CustomUser.objects.filter(role='borrower') loans_qs = Loan.objects.all() applications_qs = LoanApplication.objects.all() repayments_qs = Repayment.objects.all() else: # Admin and superuser can see all data users_qs = CustomUser.objects.filter(role='borrower') loans_qs = Loan.objects.all() applications_qs = LoanApplication.objects.all() repayments_qs = Repayment.objects.all() # Get current date today = timezone.now().date() # Calculate date ranges six_months_ago = today - timedelta(days=180) one_year_ago = today - timedelta(days=365) # Convert dates to compatible datetimes using datetime_utils start_of_today = make_datetime_compatible(datetime.combine(today, datetime.min.time())) start_of_six_months = make_datetime_compatible(datetime.combine(six_months_ago, datetime.min.time())) start_of_year = make_datetime_compatible(datetime.combine(one_year_ago, datetime.min.time())) # Apply branch filtering if a branch is selected (for admin users) if selected_branch_id and (request.user.is_superuser or request.user.role == 'admin'): users_qs = users_qs.filter(branch_id=selected_branch_id) loans_qs = loans_qs.filter(borrower__branch_id=selected_branch_id) applications_qs = applications_qs.filter(borrower__branch_id=selected_branch_id) repayments_qs = repayments_qs.filter(loan__borrower__branch_id=selected_branch_id) # Use filtered querysets for statistics total_users = users_qs.count() active_users = users_qs.filter(is_active=True).count() dormant_users = users_qs.filter(last_login__lt=start_of_six_months).count() blacklisted_users = users_qs.filter(status='blacklisted').count() # Loan statistics with branch filtering total_loans = loans_qs.count() # Only count truly active loans (exclude rolled over loans) active_loans = loans_qs.filter(status='active').count() paid_loans = loans_qs.filter(status='paid').count() defaulted_loans = loans_qs.filter(status='defaulted').count() rolled_over_loans = loans_qs.filter(status='rolled_over').count() # Applications with branch filtering pending_applications = applications_qs.filter(status='pending').count() approved_applications = applications_qs.filter(status='approved').count() rejected_applications = applications_qs.filter(status='rejected').count() # Financial metrics with branch filtering total_disbursed = loans_qs.aggregate(total=Sum('principal_amount'))['total'] or 0 total_collected = repayments_qs.aggregate(total=Sum('amount'))['total'] or 0 outstanding_amount = total_disbursed - total_collected # Monthly metrics with branch filtering monthly_disbursements = loans_qs.filter( created_at__gte=start_of_year ).aggregate(total=Sum('principal_amount'))['total'] or 0 monthly_collections = repayments_qs.filter( payment_date__gte=start_of_year ).aggregate(total=Sum('amount'))['total'] or 0 # Calculate rates and changes with branch filtering total_loans_count = loans_qs.count() defaulted_loans_count = loans_qs.filter(status='defaulted').count() defaulted_rate = (defaulted_loans_count / total_loans_count * 100) if total_loans_count > 0 else 0 # Previous month comparisons with branch filtering prev_month_start = start_of_year - timedelta(days=30) prev_active_loans = loans_qs.filter(status='active', created_at__lt=start_of_year).count() prev_active_users = users_qs.filter(is_active=True, date_joined__lt=start_of_year).count() prev_pending_applications = applications_qs.filter(status='pending', submitted_at__lt=start_of_year).count() prev_defaulted_loans = loans_qs.filter(status='defaulted', created_at__lt=start_of_year).count() prev_total_loans = loans_qs.filter(created_at__lt=start_of_year).count() prev_default_rate = (prev_defaulted_loans / prev_total_loans * 100) if prev_total_loans > 0 else 0 # Calculate changes active_loans_change = ((active_loans - prev_active_loans) / prev_active_loans * 100) if prev_active_loans > 0 else 0 active_users_change = ((active_users - prev_active_users) / prev_active_users * 100) if prev_active_users > 0 else 0 pending_applications_change = ((pending_applications - prev_pending_applications) / prev_pending_applications * 100) if prev_pending_applications > 0 else 0 default_rate_change = defaulted_rate - prev_default_rate # Recent activity with branch filtering recent_activity_qs = AuditLog.objects.select_related('user').order_by('-created_at') if selected_branch_id: # Filter audit logs for branch-related activities BEFORE slicing recent_activity_qs = recent_activity_qs.filter( Q(user__branch_id=selected_branch_id) | Q(user__isnull=True) # Include system activities ) # Apply slice AFTER filtering recent_activity = recent_activity_qs[:10] # Monthly data for charts with naive datetime queries and branch filtering monthly_data = [] for i in range(6): month_start = datetime.now().replace(day=1) - timedelta(days=30*i) month_start = datetime.combine(month_start.date(), datetime.min.time()) month_end = (month_start + timedelta(days=32)).replace(day=1) # Apply branch filtering to monthly chart data disbursed_qs = Loan.objects.filter( created_at__gte=month_start, created_at__lt=month_end ) repaid_qs = Repayment.objects.filter( payment_date__gte=month_start, payment_date__lt=month_end ) if selected_branch_id: disbursed_qs = disbursed_qs.filter(borrower__branch_id=selected_branch_id) repaid_qs = repaid_qs.filter(loan__borrower__branch_id=selected_branch_id) disbursed = disbursed_qs.aggregate(total=Sum('principal_amount'))['total'] or 0 repaid = repaid_qs.aggregate(total=Sum('amount'))['total'] or 0 monthly_data.append({ 'month': month_start.strftime('%B %Y'), 'disbursed': str(disbursed), 'repaid': str(repaid) }) # Distribution data with branch filtering loan_statuses_qs = Loan.objects.values('status').annotate(count=Count('id')) if selected_branch_id: loan_statuses_qs = loan_statuses_qs.filter(borrower__branch_id=selected_branch_id) loan_statuses = loan_statuses_qs distribution_data = { 'labels': [status['status'].title() for status in loan_statuses], 'data': [str(status['count']) for status in loan_statuses], 'colors': ['#3C9EBD', '#0A3D62', '#2ECC71', '#E74C3C'] } # Client growth data with naive datetime queries and branch filtering client_growth = [] for i in range(6): month_start = datetime.now().replace(day=1) - timedelta(days=30*i) month_start = datetime.combine(month_start.date(), datetime.min.time()) month_end = (month_start + timedelta(days=32)).replace(day=1) new_clients_query = CustomUser.objects.filter( role='borrower', date_joined__gte=month_start, date_joined__lt=month_end ) if selected_branch_id: new_clients_query = new_clients_query.filter(branch_id=selected_branch_id) new_clients = new_clients_query.count() client_growth.append({ 'month': month_start.strftime('%B %Y'), 'new_clients': str(new_clients) }) # Generate enhanced dashboard data try: enhanced_data = generate_enhanced_dashboard_data(branch_id=selected_branch_id) # Prepare dashboard data with enhanced analytics dashboard_data = { 'monthlyData': monthly_data, 'distributionData': distribution_data, 'clientGrowth': client_growth, 'enhanced': enhanced_data } except Exception as e: # Fallback to basic data if enhanced analytics fail dashboard_data = { 'monthlyData': monthly_data, 'distributionData': distribution_data, 'clientGrowth': client_growth } context = { 'total_users': total_users, 'active_users': active_users, 'dormant_users': dormant_users, 'blacklisted_users': blacklisted_users, 'total_loans': total_loans, 'active_loans': active_loans, 'paid_loans': paid_loans, 'defaulted_loans': defaulted_loans, 'rolled_over_loans': rolled_over_loans, 'pending_applications': pending_applications, 'approved_applications': approved_applications, 'rejected_applications': rejected_applications, 'total_disbursed': total_disbursed, 'total_collected': total_collected, 'outstanding_amount': outstanding_amount, 'monthly_disbursements': monthly_disbursements, 'monthly_collections': monthly_collections, 'default_rate': round(defaulted_rate, 2), 'recent_activity': recent_activity, 'active_loans_change': round(active_loans_change, 2), 'active_users_change': round(active_users_change, 2), 'pending_applications_change': round(pending_applications_change, 2), 'default_rate_change': round(default_rate_change, 2), 'dashboard_data': dashboard_data, } return render(request, 'loans/dashboard.html', context) @login_required def loan_products(request): """List all loan products""" products = LoanProduct.objects.filter(is_active=True) return render(request, 'loans/loan_products.html', {'products': products}) @login_required def loan_product_detail(request, pk): """Detail view for a loan product""" product = get_object_or_404(LoanProduct, pk=pk) return render(request, 'loans/loan_product_detail.html', {'product': product}) @login_required def loan_applications(request): """List all loan applications""" # Check if user has permission to access loans if not request.user.has_permission('loans', 'access'): messages.error(request, 'You do not have permission to view loan applications.') return redirect('loans:loans') # Get selected branch from session selected_branch_id = request.session.get('selected_branch_id') # Get filter parameters status = request.GET.get('status') product = request.GET.get('product') client = request.GET.get('client') date_from = request.GET.get('date_from') date_to = request.GET.get('date_to') # Base queryset with branch filtering applications = LoanApplication.objects.select_related( 'borrower', 'loan_product', 'reviewed_by' ) # Apply branch filtering if a branch is selected if selected_branch_id: applications = applications.filter(borrower__branch_id=selected_branch_id) applications = applications.order_by('-submitted_at') # Apply filters if status: applications = applications.filter(status=status) if product: applications = applications.filter(loan_product__product_type=product) if client: applications = applications.filter(borrower__id=client) if date_from: applications = applications.filter(submitted_at__date__gte=date_from) if date_to: applications = applications.filter(submitted_at__date__lte=date_to) # Get filter options (also filtered by branch) loan_products = LoanProduct.objects.filter(is_active=True) clients_qs = CustomUser.objects.filter(role='borrower', status='active') if selected_branch_id: clients_qs = clients_qs.filter(branch_id=selected_branch_id) clients = clients_qs context = { 'applications': applications, 'loan_products': loan_products, 'clients': clients, 'statuses': LoanApplication.STATUS_CHOICES, 'filters': { 'status': status, 'product': product, 'client': client, 'date_from': date_from, 'date_to': date_to } } return render(request, 'loans/applications.html', context) @login_required def new_application(request): """Create a new loan application with improved client handling""" # Check if user has permission to create loans if not request.user.has_permission('loans', 'create'): messages.error(request, 'You do not have permission to create loan applications.') return redirect('loans:loans') try: # Get client if specified client_id = request.GET.get('client_id') client = None if client_id: try: client = get_object_or_404(CustomUser, pk=client_id, role='borrower') except: client = None # Get all active loan products loan_products = LoanProduct.objects.filter(is_active=True).order_by( models.Case( models.When(product_type='biashara', then=models.Value(1)), models.When(product_type='logbook', then=models.Value(2)), default=models.Value(3) ), 'name' ) if request.method == 'POST': try: # Get form data with detailed validation client_id = request.POST.get('client_id') print(f"DEBUG: Received client_id: {client_id}") # Debug logging if not client_id or client_id == '': return JsonResponse({ 'status': 'error', 'message': 'Please select a client from the dropdown' }, status=400) loan_product_id = request.POST.get('loan_product') if not loan_product_id: return JsonResponse({ 'status': 'error', 'message': 'Please select a loan product' }, status=400) try: requested_amount = Decimal(request.POST.get('amount', '0')) if requested_amount <= 0: return JsonResponse({ 'status': 'error', 'message': 'Please enter a valid loan amount greater than 0' }, status=400) except (TypeError, ValueError, DecimalException): return JsonResponse({ 'status': 'error', 'message': 'Please enter a valid loan amount' }, status=400) try: requested_duration = int(request.POST.get('duration', '0')) if requested_duration <= 0: return JsonResponse({ 'status': 'error', 'message': 'Please enter a valid duration greater than 0' }, status=400) except (TypeError, ValueError): return JsonResponse({ 'status': 'error', 'message': 'Please enter a valid duration' }, status=400) purpose = request.POST.get('purpose', '').strip() if not purpose: return JsonResponse({ 'status': 'error', 'message': 'Please enter the loan purpose' }, status=400) repayment_method = request.POST.get('repayment_method') if not repayment_method: return JsonResponse({ 'status': 'error', 'message': 'Please select a repayment method' }, status=400) # Handle disbursement date disbursement_date_str = request.POST.get('disbursement_date') if disbursement_date_str: try: disbursement_date = datetime.strptime(disbursement_date_str, '%Y-%m-%d') # Check if user is trying to set a past date if disbursement_date.date() < datetime.now().date(): if not request.user.is_staff and not request.user.is_superuser: return JsonResponse({ 'status': 'error', 'message': 'Only administrators can set past disbursement dates' }, status=403) except ValueError: return JsonResponse({ 'status': 'error', 'message': 'Invalid disbursement date format' }, status=400) else: disbursement_date = datetime.now() # Get loan product and client with better error handling try: loan_product = LoanProduct.objects.get(pk=loan_product_id) except LoanProduct.DoesNotExist: return JsonResponse({ 'status': 'error', 'message': 'Selected loan product not found' }, status=400) try: client = CustomUser.objects.get(pk=client_id, role='borrower') print(f"DEBUG: Found client: {client.get_full_name()}") # Debug logging except CustomUser.DoesNotExist: return JsonResponse({ 'status': 'error', 'message': 'Selected client not found or is not a borrower' }, status=400) # Check client status if hasattr(client, 'status'): if client.status == 'blacklisted': return JsonResponse({ 'status': 'error', 'message': f'Cannot create loan application for blacklisted client: {client.get_full_name()}' }, status=403) if client.status == 'suspended': return JsonResponse({ 'status': 'error', 'message': f'Cannot create loan application for suspended client: {client.get_full_name()}' }, status=403) if client.status == 'inactive': return JsonResponse({ 'status': 'error', 'message': f'Cannot create loan application for inactive client: {client.get_full_name()}' }, status=403) # Validate amount, duration, and repayment method try: loan_product.validate_amount(requested_amount) except ValueError as e: return JsonResponse({'status': 'error', 'message': str(e)}, status=400) try: loan_product.validate_duration(requested_duration) except ValueError as e: return JsonResponse({'status': 'error', 'message': str(e)}, status=400) try: loan_product.validate_repayment_method(repayment_method) except ValueError as e: return JsonResponse({'status': 'error', 'message': str(e)}, status=400) # Calculate amounts # Convert days to months - use exact calculation, not ceiling # This ensures accurate interest: 90 days = 3 months, 60 days = 2 months, etc. months = Decimal(str(requested_duration)) / Decimal('30') months = max(Decimal('1'), months) # Minimum 1 month interest_amount = loan_product.calculate_interest(requested_amount, months) processing_fee_amount = loan_product.calculate_processing_fee(requested_amount, months) total_amount = requested_amount + interest_amount + processing_fee_amount # Create loan application application = LoanApplication.objects.create( borrower=client, loan_product=loan_product, requested_amount=requested_amount, requested_duration=requested_duration, purpose=purpose, repayment_method=repayment_method, interest_amount=interest_amount, processing_fee_amount=processing_fee_amount, total_amount=total_amount ) print(f"DEBUG: Created application {application.application_number} for {client.get_full_name()}") # Auto-calculate credit score and handle auto-approval try: # Try to get existing credit score existing_score = LoanScoring.objects.filter(user=client).first() if existing_score: application.credit_score = existing_score.total_score else: application.credit_score = 0 # Auto-approve if conditions are met auto_approval_enabled = SystemSetting.get_bool('auto_approval_enabled', True) auto_approval_min_score = SystemSetting.get_int('auto_approval_min_score', 80) auto_approval_max_amount = Decimal(str(SystemSetting.get_float('auto_approval_max_amount', 25000))) if (auto_approval_enabled and application.credit_score >= auto_approval_min_score and requested_amount <= auto_approval_max_amount): application.auto_approved = True application.status = 'approved' application.reviewed_by = None application.reviewed_at = datetime.now() application.approval_notes = 'Auto-approved based on credit score' # Create loan record loan = Loan.objects.create( application=application, borrower=client, principal_amount=requested_amount, interest_amount=interest_amount, processing_fee=processing_fee_amount, total_amount=total_amount, disbursement_date=disbursement_date, due_date=disbursement_date + timedelta(days=requested_duration), duration_days=requested_duration, status='active' ) messages.success(request, f'{loan_product.name} loan has been recorded and auto-approved!') else: messages.success(request, f'{loan_product.name} loan has been recorded and is under review.') except Exception as e: print(f"DEBUG: Credit score error: {e}") messages.success(request, f'{loan_product.name} loan has been recorded and is under review.') application.save() # Create audit log AuditLog.objects.create( user=request.user, action='create', model_name='LoanApplication', object_id=str(application.id), description=f'Added {loan_product.name} loan for {client.get_full_name()}' ) # Create notification for admins if not application.auto_approved and SystemSetting.get_bool('email_notifications_enabled', True): from django.urls import reverse Notification.objects.create( title=f'New {loan_product.name} Loan Application', message=f'A new {loan_product.name} loan application has been submitted by {client.get_full_name()}', priority='medium', notification_type='application_submitted', action_url=reverse('loans:application_detail', kwargs={'pk': application.id}), loan_app=application ) return JsonResponse({ 'status': 'success', 'message': 'Loan application submitted successfully', 'application_id': str(application.id), 'application_number': application.application_number }) except Exception as e: import traceback print("Error in loan application:", str(e)) print(traceback.format_exc()) return JsonResponse({ 'status': 'error', 'message': f'An error occurred while processing your application: {str(e)}' }, status=500) # GET request - show the form # Get all active borrowers with proper status filtering clients = CustomUser.objects.filter( role='borrower' ).exclude( status__in=['deleted', 'archived'] # Exclude deleted/archived but allow inactive for admin review ).select_related().order_by('first_name', 'last_name') # Convert clients to a safe format for JavaScript clients_data = [] for c in clients: client_data = { 'id': str(c.id), 'full_name': c.get_full_name(), 'first_name': c.first_name or '', 'last_name': c.last_name or '', 'email': c.email or 'No email', 'phone_number': str(c.phone_number) if c.phone_number else 'No phone', 'business_name': getattr(c, 'business_name', '') or '', 'id_number': getattr(c, 'id_number', '') or '', 'status': getattr(c, 'status', 'active') } clients_data.append(client_data) # Convert loan products to dict with current settings loan_products_data = [] for product in loan_products: try: product_data = product.to_dict() loan_products_data.append(product_data) except Exception as e: print(f"Error converting product {product.name}: {e}") # Fallback data - use system settings instead of hardcoded values loan_products_data.append({ 'id': str(product.id), 'name': product.name, 'product_type': product.product_type, 'description': product.description, 'min_amount': float(product.get_min_amount()), 'max_amount': float(product.get_max_amount()), 'interest_rate': float(product.get_interest_rate()), 'processing_fee': float(product.get_processing_fee()), 'late_payment_penalty': float(product.get_late_payment_penalty()), 'duration_months': product.duration_months, 'min_duration': product.get_min_duration(), 'max_duration': product.get_max_duration(), 'available_repayment_methods': product.available_repayment_methods or ['monthly'], 'requires_guarantor': product.requires_guarantor, 'requires_collateral': product.requires_collateral, 'is_active': product.is_active }) context = { 'loan_products': loan_products, 'loan_products_json': json.dumps(loan_products_data, cls=DjangoJSONEncoder), 'clients': clients, 'clients_json': json.dumps(clients_data, cls=DjangoJSONEncoder), 'selected_client': client, 'today': datetime.now().date() } return render(request, 'loans/new_application.html', context) except Exception as e: import traceback print("Error in loan application view:", str(e)) print(traceback.format_exc()) messages.error(request, f'An error occurred: {str(e)}') return redirect('loans:loans') @login_required def application_detail(request, pk): """Detail view for a loan application""" # Check if user has permission to access loans if not request.user.has_permission('loans', 'access'): messages.error(request, 'You do not have permission to view loan application details.') return redirect('loans:loans') application = get_object_or_404(LoanApplication, pk=pk) return render(request, 'loans/application_detail.html', {'application': application}) @login_required def approve_application(request, pk): """Approve a loan application""" # Check if user has permission to approve loans if not request.user.has_permission('loans', 'approve'): messages.error(request, 'You do not have permission to approve loan applications.') return redirect('loans:loans') application = get_object_or_404(LoanApplication, pk=pk) # Check if borrower is blacklisted if application.borrower.status == 'blacklisted': messages.error(request, 'Cannot approve loan application for blacklisted client.') return redirect('loans:application_detail', pk=pk) # Check if borrower is suspended if application.borrower.status == 'suspended': messages.error(request, 'Cannot approve loan application for suspended client.') return redirect('loans:application_detail', pk=pk) # Check if borrower is inactive if application.borrower.status == 'inactive': messages.error(request, 'Cannot approve loan application for inactive client.') return redirect('loans:application_detail', pk=pk) # Get loan statistics defaulted_loans = application.borrower.loans.filter(status='defaulted').count() paid_loans = application.borrower.loans.filter(status='paid').count() # Get previous loans and repayment history from decimal import Decimal previous_loans = Loan.objects.filter( borrower=application.borrower ).select_related('borrower').order_by('-disbursement_date') # Calculate statistics and add repayment percentage to each loan loan_list = [] total_borrowed = Decimal('0') total_repaid = Decimal('0') for loan in previous_loans[:10]: # Show last 10 loans # Calculate repayment percentage if loan.total_amount and loan.total_amount > 0: repayment_pct = (loan.amount_paid / loan.total_amount * 100) loan.repayment_percentage = int(repayment_pct) else: loan.repayment_percentage = 0 loan_list.append(loan) total_borrowed += loan.principal_amount total_repaid += loan.amount_paid active_loans_count = previous_loans.filter(status='active').count() completed_loans_count = previous_loans.filter(status='paid').count() if request.method == 'POST': # Handle disbursement date (admin only can set past dates) disbursement_date_str = request.POST.get('disbursement_date') disbursement_date = None if disbursement_date_str: try: disbursement_date = datetime.strptime(disbursement_date_str, '%Y-%m-%d') # Check if user is trying to set a past date if disbursement_date.date() < datetime.now().date(): if not request.user.is_admin(): messages.error(request, 'Only administrators can set past disbursement dates.') return redirect('loans:approve_application', pk=pk) except ValueError: messages.error(request, 'Invalid disbursement date format.') return redirect('loans:approve_application', pk=pk) # Get edited loan details approved_amount = request.POST.get('approved_amount') approved_duration = request.POST.get('approved_duration') approved_purpose = request.POST.get('approved_purpose') edit_interest_rate = request.POST.get('edit_interest_rate') edit_processing_fee_rate = request.POST.get('edit_processing_fee_rate') edit_repayment_method = request.POST.get('edit_repayment_method') # Update application with edited values if provided if approved_amount: try: approved_amount_decimal = Decimal(approved_amount) # Validate amount is within product limits if approved_amount_decimal < application.loan_product.min_amount: messages.error(request, f'Loan amount cannot be less than KES {application.loan_product.min_amount}') return redirect('loans:approve_application', pk=pk) if approved_amount_decimal > application.loan_product.max_amount: messages.error(request, f'Loan amount cannot exceed KES {application.loan_product.max_amount}') return redirect('loans:approve_application', pk=pk) application.requested_amount = approved_amount_decimal except (ValueError, DecimalException): messages.error(request, 'Invalid loan amount provided.') return redirect('loans:approve_application', pk=pk) if approved_duration: try: approved_duration_int = int(approved_duration) # Validate duration is within product limits if approved_duration_int < application.loan_product.min_duration: messages.error(request, f'Duration cannot be less than {application.loan_product.min_duration} days') return redirect('loans:approve_application', pk=pk) if approved_duration_int > application.loan_product.max_duration: messages.error(request, f'Duration cannot exceed {application.loan_product.max_duration} days') return redirect('loans:approve_application', pk=pk) application.requested_duration = approved_duration_int except ValueError: messages.error(request, 'Invalid duration provided.') return redirect('loans:approve_application', pk=pk) if approved_purpose: application.purpose = approved_purpose # Handle custom interest rate if edit_interest_rate: try: custom_interest_rate = Decimal(edit_interest_rate) if custom_interest_rate < 0 or custom_interest_rate > 100: messages.error(request, 'Interest rate must be between 0 and 100%') return redirect('loans:approve_application', pk=pk) # Store custom rate for loan creation application.custom_interest_rate = custom_interest_rate except (ValueError, DecimalException): messages.error(request, 'Invalid interest rate provided.') return redirect('loans:approve_application', pk=pk) # Handle custom processing fee rate if edit_processing_fee_rate: try: custom_processing_fee = Decimal(edit_processing_fee_rate) if custom_processing_fee < 0 or custom_processing_fee > 100: messages.error(request, 'Processing fee must be between 0 and 100%') return redirect('loans:approve_application', pk=pk) # Store custom rate for loan creation application.custom_processing_fee = custom_processing_fee except (ValueError, DecimalException): messages.error(request, 'Invalid processing fee provided.') return redirect('loans:approve_application', pk=pk) # Handle repayment method if edit_repayment_method: if edit_repayment_method in application.loan_product.available_repayment_methods: application.repayment_method = edit_repayment_method else: messages.error(request, 'Invalid repayment method selected.') return redirect('loans:approve_application', pk=pk) # Recalculate loan amounts based on edited values application.calculate_loan_amounts() application.save() try: # Approve application and create loan loan = application.approve(request.user, request.POST.get('notes', ''), disbursement_date) # Create audit log AuditLog.objects.create( user=request.user, action='create', model_name='Loan', object_id=str(loan.id), description=f'Created loan {loan.loan_number} for {loan.borrower.get_full_name()}' ) # Send loan approval SMS notification try: from payments.sms_service import send_loan_approval_sms send_loan_approval_sms( borrower_name=loan.borrower.get_full_name(), amount=float(loan.principal_amount), loan_number=loan.loan_number, customer_phone=loan.borrower.phone_number, ) except Exception as sms_err: logger.warning(f"Loan approval SMS failed for {loan.loan_number}: {sms_err}") messages.success(request, f'Application approved and loan {loan.loan_number} created successfully.') return redirect('loans:loan_detail', pk=loan.pk) # Redirect to loan detail page except IntegrityError as e: messages.error(request, 'This application has already been approved and a loan exists.') return redirect('loans:application_detail', pk=pk) except Exception as e: messages.error(request, f'Error approving application: {str(e)}') return redirect('loans:application_detail', pk=pk) return render(request, 'loans/approve_application.html', { 'application': application, 'defaulted_loans': defaulted_loans, 'paid_loans': paid_loans, 'previous_loans': loan_list, 'total_borrowed': total_borrowed, 'total_repaid': total_repaid, 'active_loans_count': active_loans_count, 'completed_loans_count': completed_loans_count, 'today': datetime.now().date() }) @login_required def reject_application(request, pk): """Reject a loan application""" # Check if user has permission to reject loans if not request.user.has_permission('loans', 'reject'): messages.error(request, 'You do not have permission to reject loan applications.') return redirect('loans:loans') application = get_object_or_404(LoanApplication, pk=pk) if request.method == 'POST': application.reject(request.user, request.POST.get('notes', '')) messages.success(request, 'Application rejected.') return redirect('loans:application_detail', pk=pk) return render(request, 'loans/reject_application.html', {'application': application}) @login_required @portfolio_access_required def loans(request): """View for managing all loans with filtering, pagination, and reporting""" # Check if user has permission to access loans if not request.user.has_permission('loans', 'access'): messages.error(request, 'You do not have permission to view loans.') return redirect('dashboard') # Get selected branch from session selected_branch_id = request.session.get('selected_branch_id') # Get all loan applications with related data, excluding deleted loans loans_list = LoanApplication.objects.all().select_related( 'borrower', 'loan_product', 'loan' ).prefetch_related( 'loan__repayments' ).filter( Q(loan__isnull=True) | Q(loan__is_deleted=False) # Include applications without loans or with non-deleted loans ) # Apply portfolio-based access control for staff members if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser: # Staff members can only see loans from their assigned borrowers loans_list = loans_list.filter(borrower__portfolio_manager=request.user) elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser: # Secretaries and auditors can only see loans from borrowers in their branch if request.user.branch: loans_list = loans_list.filter(borrower__branch=request.user.branch) # Apply branch filtering if a branch is selected if selected_branch_id: loans_list = loans_list.filter(borrower__branch_id=selected_branch_id) loans_list = loans_list.order_by('-submitted_at') # Apply filters status = request.GET.get('status') product_type = request.GET.get('product_type') search = request.GET.get('search') date_from = request.GET.get('date_from') date_to = request.GET.get('date_to') overdue = request.GET.get('overdue') # Redirect old overdue URL pattern to new cleaner pattern if overdue == 'true' and status in ['active', '', None]: # Build new URL with status=overdue instead of status=active&overdue=true from django.http import QueryDict new_params = request.GET.copy() new_params['status'] = 'overdue' if 'overdue' in new_params: del new_params['overdue'] return redirect(f"{request.path}?{new_params.urlencode()}") if status: if status in ['active', 'paid', 'defaulted', 'rolled_over', 'written_off', 'overdue']: if status == 'active': # Only include active loans, not rolled over loans loans_list = loans_list.filter(loan__status='active') elif status == 'overdue': # Show overdue loans (active loans past their due date with outstanding amount > 0) # outstanding_amount is a @property, use DB fields: loan__total_amount > loan___amount_paid_cache from django.db.models import F loans_list = loans_list.filter( loan__status='active', loan__due_date__lt=timezone.now().date(), loan__total_amount__gt=F('loan___amount_paid_cache') ) else: loans_list = loans_list.filter(loan__status=status) elif status in ['pending', 'under_review', 'approved', 'rejected']: loans_list = loans_list.filter(status=status) else: # By default, exclude rolled over loans from the main loans view # Users can specifically view rolled over loans at /loans/rolled-over/ loans_list = loans_list.exclude(loan__status='rolled_over') # Handle overdue parameter separately (for backward compatibility) # Only apply if status is not already 'overdue' to avoid duplicate filtering if overdue == 'true' and status not in ['overdue', 'paid', 'defaulted', 'rolled_over', 'written_off']: # When overdue=true is present, filter to show only overdue loans loans_list = loans_list.filter( loan__status='active', loan__due_date__lt=datetime.now() ) if product_type: loans_list = loans_list.filter(loan_product__product_type=product_type) if search: loans_list = loans_list.filter( Q(application_number__icontains=search) | Q(loan__loan_number__icontains=search) | Q(borrower__first_name__icontains=search) | Q(borrower__last_name__icontains=search) | Q(borrower__email__icontains=search) | Q(borrower__phone_number__icontains=search) | Q(borrower__business_name__icontains=search) ) if date_from: try: date_from = datetime.strptime(date_from, '%Y-%m-%d') loans_list = loans_list.filter( Q(submitted_at__date__gte=date_from) | Q(loan__disbursement_date__date__gte=date_from) ) except ValueError: pass if date_to: try: date_to = datetime.strptime(date_to, '%Y-%m-%d') loans_list = loans_list.filter( Q(submitted_at__date__lte=date_to) | Q(loan__disbursement_date__date__lte=date_to) ) except ValueError: pass # Get summary statistics loans_with_loan = loans_list.filter(loan__isnull=False) total_loans = loans_with_loan.count() # Only count truly active loans (exclude rolled over loans) active_loans = loans_with_loan.filter(loan__status='active').count() total_disbursed = loans_with_loan.aggregate( total=Sum('loan__principal_amount') )['total'] or 0 total_collected = Repayment.objects.filter( loan__application__in=loans_with_loan ).aggregate(total=Sum('amount'))['total'] or 0 # Advanced statistics advanced_stats = { 'overdue_loans': loans_with_loan.filter( loan__status='active', # Only truly active loans can be overdue loan__due_date__lt=datetime.now() ).count(), 'default_rate': ( loans_with_loan.filter(loan__status='defaulted').count() / total_loans * 100 ) if total_loans > 0 else 0, 'avg_loan_amount': loans_with_loan.aggregate( avg=Avg('loan__principal_amount') )['avg'] or 0, 'loans_by_product': loans_with_loan.values( 'loan_product__product_type', 'loan_product__name' ).annotate( count=Count('id'), total_amount=Sum('loan__principal_amount') ).order_by('loan_product__product_type'), 'loans_by_status': loans_with_loan.values( 'loan__status' ).annotate( count=Count('id'), total_amount=Sum('loan__principal_amount') ).order_by('loan__status'), 'client_summary': loans_with_loan.values( 'borrower__first_name', 'borrower__last_name' ).annotate( total_loans=Count('id'), total_amount=Sum('loan__principal_amount'), active_loans=Count('loan', filter=Q(loan__status='active')) ).order_by('-total_loans')[:5] } # Handle report generation - TEMPORARILY DISABLED DUE TO RECURSION ERROR if False and request.GET.get('generate_report') == 'true': from .reports import generate_loan_statistics, generate_enhanced_charts, generate_enhanced_pdf_report # Get filter parameters for report product_type = request.GET.get('product_type') loan_product = None if product_type: loan_product = LoanProduct.objects.filter(product_type=product_type).first() borrower_id = request.GET.get('borrower') borrower = None if borrower_id: borrower = CustomUser.objects.filter(id=borrower_id).first() # Generate statistics and charts stats = generate_loan_statistics( start_date=date_from, end_date=date_to, borrower=borrower, loan_product=loan_product ) charts = generate_enhanced_charts(stats) # Generate PDF pdf = generate_enhanced_pdf_report(stats, charts, report_type='general') # Return PDF response filename = "loan_report" if borrower: filename = f"loans_{borrower.get_full_name()}" if loan_product: filename += f"_{loan_product.name}" if date_from: filename += f"_from_{date_from}" if date_to: filename += f"_to_{date_to}" response = HttpResponse(pdf, content_type='application/pdf') response['Content-Disposition'] = f'attachment; filename="{filename}.pdf"' return response # Pagination paginator = Paginator(loans_list, 10) page = request.GET.get('page') try: loans = paginator.page(page) except PageNotAnInteger: loans = paginator.page(1) except EmptyPage: loans = paginator.page(paginator.num_pages) context = { 'loans': loans, 'total_loans': total_loans, 'active_loans': active_loans, 'total_disbursed': total_disbursed, 'total_collected': total_collected, 'advanced_stats': advanced_stats, 'selected_status': status, 'selected_product_type': product_type, 'search_query': search, 'date_from': date_from, 'date_to': date_to, 'loan_statuses': [ ('pending', 'Pending'), ('under_review', 'Under Review'), ('approved', 'Approved'), ('rejected', 'Rejected'), ('active', 'Active'), ('paid', 'Paid'), ('defaulted', 'Defaulted'), ('written_off', 'Written Off'), ('rolled_over', 'Rolled Over') ], 'product_types': [ ('personal', 'Personal Loan'), ('business', 'Business Advance'), ('salary', 'Salary Loan'), ('emergency', 'Emergency Loan'), ('asset', 'Asset Finance') ] } return render(request, 'loans/loans.html', context) @login_required def update_loan_status(request, pk): """Update loan status (admin only)""" # Check if user has permission to edit loans if not request.user.has_permission('loans', 'edit'): messages.error(request, 'You do not have permission to update loan status.') return redirect('loans:loans') if not request.user.is_admin(): messages.error(request, 'Only administrators can update loan status.') return redirect('loans:loans') loan = get_object_or_404(Loan, pk=pk) if request.method == 'POST': new_status = request.POST.get('status') reason = request.POST.get('reason', '') if not new_status: messages.error(request, 'Please select a status.') return redirect('loans:loans') try: # Validate status transition loan.validate_status_transition(new_status) old_status = loan.status # Handle specific status changes if new_status == 'defaulted': loan.mark_as_defaulted(reason) elif new_status == 'written_off': loan.write_off(reason) else: loan.status = new_status loan.save() # Create audit log AuditLog.objects.create( user=request.user, action='status_change', model_name='Loan', object_id=str(loan.id), description=f'Changed loan {loan.loan_number} status from {old_status} to {new_status}. Reason: {reason}' ) messages.success(request, f'Loan status updated to {new_status}.') except ValueError as e: messages.error(request, str(e)) except Exception as e: messages.error(request, f'Error updating loan status: {str(e)}') return redirect('loans:loans') @login_required def loan_detail(request, pk): """Detailed view of a loan with all related information and report generation""" # Check if user has permission to access loans if not request.user.has_permission('loans', 'access'): messages.error(request, 'You do not have permission to view loan details.') return redirect('loans:loans') try: loan = get_object_or_404(Loan.objects.select_related( 'borrower', 'application', 'application__loan_product' ), pk=pk) except Http404: # Try to find the loan in rolled-over status loan = get_object_or_404(Loan.objects.select_related( 'borrower', 'application', 'application__loan_product' ).filter(status='rolled_over'), pk=pk) # Get all repayments repayments = Repayment.objects.filter(loan=loan).order_by('-payment_date') # Get penalty charges penalty_charges = loan.penalty_charges.all().order_by('-applied_date') total_penalties = loan.total_penalties # Get rollover requests rollovers = RolloverRequest.objects.filter(loan=loan).order_by('-requested_at') # Calculate loan statistics total_paid = repayments.aggregate(total=Sum('amount'))['total'] or 0 remaining_amount = loan.outstanding_amount # This now includes penalties total_amount_with_penalties = loan.total_amount + total_penalties payment_progress = (total_paid / total_amount_with_penalties * 100) if total_amount_with_penalties > 0 else 0 # Calculate current penalty if overdue current_penalty = loan.calculate_penalty() if loan.is_overdue else Decimal('0.00') # Calculate late payments late_payments = repayments.filter( payment_date__gt=loan.due_date ).count() # Get payment schedule using repayment scheduler payment_schedule = loan.get_payment_schedule() # Get arrears summary arrears_summary = loan.get_arrears_summary() # Get missed payment periods missed_periods = loan.get_missed_payment_periods() context = { 'loan': loan, 'repayments': repayments, 'penalty_charges': penalty_charges, 'total_penalties': total_penalties, 'current_penalty': current_penalty, 'rollovers': rollovers, 'total_paid': total_paid, 'remaining_amount': remaining_amount, 'payment_progress': payment_progress, 'payment_schedule': payment_schedule, 'arrears_summary': arrears_summary, 'missed_periods': missed_periods, 'is_overdue': loan.is_overdue, 'days_overdue': loan.days_overdue if loan.is_overdue else 0, 'days_missed': loan.days_missed if loan.is_overdue else 0, 'late_payments': late_payments, 'penalty_rate': loan.application.loan_product.get_late_payment_penalty(), 'repayment_method': loan.repayment_method, 'expected_payment_amount': loan.expected_payment_amount, 'payment_frequency_days': loan.payment_frequency_days, 'next_payment_due_date': loan.next_payment_due_date, 'payment_status': loan.payment_status, } # Handle report generation if request.GET.get('generate_report') == 'true': from .reports import generate_loan_statistics, generate_enhanced_charts, generate_enhanced_pdf_report # Generate statistics and charts stats = generate_loan_statistics(borrower=loan.borrower, loan_product=loan.application.loan_product) charts = generate_enhanced_charts(stats) # Generate PDF pdf = generate_enhanced_pdf_report(stats, charts, report_type='borrower', borrower=loan.borrower) # Return PDF response response = HttpResponse(pdf, content_type='application/pdf') response['Content-Disposition'] = f'attachment; filename="loan_{loan.loan_number}_report.pdf"' return response return render(request, 'loans/loan_detail.html', context) @login_required def disburse_loan(request, pk): """Disburse a loan""" # Check if user has permission to process loans if not request.user.has_permission('loans', 'process'): messages.error(request, 'You do not have permission to disburse loans.') return redirect('loans:loans') loan = get_object_or_404(Loan, pk=pk) if request.method == 'POST': # Handle disbursement logic messages.success(request, 'Loan disbursed successfully.') return redirect('loans:loan_detail', pk=pk) return render(request, 'loans/disburse_loan.html', {'loan': loan}) @login_required @portfolio_access_required def repayments(request): """List all repayments with filtering and search""" # Check if user has permission to access repayments if not request.user.has_permission('repayments', 'access'): messages.error(request, 'You do not have permission to view repayments.') return redirect('dashboard') # Get selected branch from session selected_branch_id = request.session.get('selected_branch_id') # Base queryset with branch filtering repayments_list = Repayment.objects.select_related( 'loan', 'loan__borrower', 'loan__application__loan_product' ) # Apply portfolio-based access control for staff members if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser: # Staff members can only see repayments for their assigned borrowers repayments_list = repayments_list.filter(loan__borrower__portfolio_manager=request.user) elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser: # Secretaries and auditors can only see repayments from borrowers in their branch if request.user.branch: repayments_list = repayments_list.filter(loan__borrower__branch=request.user.branch) elif selected_branch_id: # Admins and managers can filter by selected branch repayments_list = repayments_list.filter(loan__borrower__branch_id=selected_branch_id) repayments_list = repayments_list.order_by('-payment_date') # Search functionality search_query = request.GET.get('search', '') if search_query: repayments_list = repayments_list.filter( Q(loan__borrower__first_name__icontains=search_query) | Q(loan__borrower__last_name__icontains=search_query) | Q(loan__borrower__phone_number__icontains=search_query) | Q(loan__loan_number__icontains=search_query) | Q(payment_method__icontains=search_query) ) # Date filtering start_date = request.GET.get('start_date') end_date = request.GET.get('end_date') if start_date: repayments_list = repayments_list.filter(payment_date__gte=start_date) if end_date: repayments_list = repayments_list.filter(payment_date__lte=end_date) # Payment method filtering payment_method = request.GET.get('payment_method') if payment_method: repayments_list = repayments_list.filter(payment_method=payment_method) # Payment source filtering (automatic vs manual) payment_source = request.GET.get('payment_source') if payment_source: repayments_list = repayments_list.filter(payment_source=payment_source) # Loan status filtering loan_status = request.GET.get('loan_status') if loan_status: repayments_list = repayments_list.filter(loan__status=loan_status) # Receipt status filtering receipt_status = request.GET.get('receipt_status') if receipt_status == 'with_receipt': repayments_list = repayments_list.filter(receipt__isnull=False) elif receipt_status == 'without_receipt': repayments_list = repayments_list.filter(receipt__isnull=True) # Pagination paginator = Paginator(repayments_list, 25) # Show 25 repayments per page page = request.GET.get('page') try: repayments = paginator.page(page) except PageNotAnInteger: repayments = paginator.page(1) except EmptyPage: repayments = paginator.page(paginator.num_pages) # Get filter options for dropdowns — use static model choices, not DB queries # (DB queries return one row per repayment causing thousands of duplicate options) payment_methods = Repayment.PAYMENT_METHODS # list of (value, label) tuples loan_statuses = Loan.objects.values_list('status', flat=True).distinct().order_by('status') context = { 'repayments': repayments, 'search_query': search_query, 'start_date': start_date, 'end_date': end_date, 'payment_method': payment_method, 'payment_source': payment_source, 'loan_status': loan_status, 'receipt_status': receipt_status, 'payment_methods': payment_methods, 'payment_sources': Repayment.PAYMENT_SOURCES, 'loan_statuses': loan_statuses, 'total_repayments': repayments_list.count(), 'total_amount': repayments_list.aggregate(Sum('amount'))['amount__sum'] or 0, } return render(request, 'loans/repayments.html', context) @login_required def export_repayments_excel(request): """Export repayments to Excel""" # Check if user has permission to export repayments if not request.user.has_permission('repayments', 'export'): messages.error(request, 'You do not have permission to export repayment data.') return redirect('loans:repayments') try: from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment from django.http import HttpResponse import io # Get selected branch from session selected_branch_id = request.session.get('selected_branch_id') # Base queryset with branch filtering repayments_list = Repayment.objects.select_related( 'loan', 'loan__borrower', 'loan__application__loan_product' ) # Apply branch filtering if a branch is selected if selected_branch_id: repayments_list = repayments_list.filter(loan__borrower__branch_id=selected_branch_id) # Apply filters from request search_query = request.GET.get('search', '') if search_query: repayments_list = repayments_list.filter( Q(loan__borrower__first_name__icontains=search_query) | Q(loan__borrower__last_name__icontains=search_query) | Q(loan__borrower__phone_number__icontains=search_query) | Q(loan__loan_number__icontains=search_query) | Q(payment_method__icontains=search_query) ) start_date = request.GET.get('start_date') end_date = request.GET.get('end_date') if start_date: repayments_list = repayments_list.filter(payment_date__gte=start_date) if end_date: repayments_list = repayments_list.filter(payment_date__lte=end_date) payment_method = request.GET.get('payment_method') if payment_method: repayments_list = repayments_list.filter(payment_method=payment_method) loan_status = request.GET.get('loan_status') if loan_status: repayments_list = repayments_list.filter(loan__status=loan_status) receipt_status = request.GET.get('receipt_status') if receipt_status == 'with_receipt': repayments_list = repayments_list.filter(receipt__isnull=False) elif receipt_status == 'without_receipt': repayments_list = repayments_list.filter(receipt__isnull=True) repayments_list = repayments_list.order_by('-payment_date') # Create workbook wb = Workbook() ws = wb.active ws.title = "Repayments Report" # Define styles header_font = Font(bold=True, color="FFFFFF") header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid") center_alignment = Alignment(horizontal="center", vertical="center") # Headers headers = [ 'Payment Date', 'Borrower Name', 'Phone Number', 'Loan Number', 'Product', 'Amount (KSh)', 'Payment Method', 'Loan Status', 'Receipt Status', 'M-Pesa Transaction ID' ] for col, header in enumerate(headers, 1): cell = ws.cell(row=1, column=col, value=header) cell.font = header_font cell.fill = header_fill cell.alignment = center_alignment # Data for row, repayment in enumerate(repayments_list, 2): try: # Safe access to related fields with comprehensive error handling payment_date = 'N/A' if repayment.payment_date: try: payment_date = repayment.payment_date.strftime('%Y-%m-%d %H:%M') except: payment_date = str(repayment.payment_date) borrower_name = 'N/A' if repayment.loan and repayment.loan.borrower: try: borrower_name = repayment.loan.borrower.get_full_name() except: borrower_name = f"{repayment.loan.borrower.first_name or ''} {repayment.loan.borrower.last_name or ''}".strip() or 'N/A' phone_number = 'N/A' if repayment.loan and repayment.loan.borrower: phone_number = repayment.loan.borrower.phone_number or 'N/A' loan_number = 'N/A' if repayment.loan: loan_number = repayment.loan.loan_number or 'N/A' # Safe access to loan product product_name = 'N/A' try: if (repayment.loan and hasattr(repayment.loan, 'application') and repayment.loan.application and hasattr(repayment.loan.application, 'loan_product') and repayment.loan.application.loan_product): product_name = repayment.loan.application.loan_product.name or 'N/A' except: product_name = 'N/A' amount = 0.0 try: amount = float(repayment.amount) if repayment.amount else 0.0 except: amount = 0.0 payment_method = repayment.payment_method or 'N/A' loan_status = 'N/A' try: if repayment.loan and repayment.loan.status: loan_status = repayment.loan.status.title() except: loan_status = 'N/A' # Safe receipt status check - handle both receipt and receipt_set relationships receipt_status = 'No' try: # Check multiple possible receipt relationships if hasattr(repayment, 'receipt') and repayment.receipt: receipt_status = 'Yes' elif hasattr(repayment, 'receipt_set') and repayment.receipt_set.exists(): receipt_status = 'Yes' elif hasattr(repayment, 'receipts') and repayment.receipts.exists(): receipt_status = 'Yes' except: receipt_status = 'No' mpesa_id = repayment.mpesa_transaction_id or '' # Write data to Excel ws.cell(row=row, column=1, value=payment_date) ws.cell(row=row, column=2, value=borrower_name) ws.cell(row=row, column=3, value=phone_number) ws.cell(row=row, column=4, value=loan_number) ws.cell(row=row, column=5, value=product_name) ws.cell(row=row, column=6, value=amount) ws.cell(row=row, column=7, value=payment_method) ws.cell(row=row, column=8, value=loan_status) ws.cell(row=row, column=9, value=receipt_status) ws.cell(row=row, column=10, value=mpesa_id) except Exception as e: # Log the error but continue processing print(f"Skipping row {row} due to error: {e}") # Still write basic data even if there's an error try: ws.cell(row=row, column=1, value='Error') ws.cell(row=row, column=2, value=f'Repayment ID: {repayment.pk}') ws.cell(row=row, column=3, value='Error') ws.cell(row=row, column=4, value='Error') ws.cell(row=row, column=5, value='Error') ws.cell(row=row, column=6, value=0.0) ws.cell(row=row, column=7, value='Error') ws.cell(row=row, column=8, value='Error') ws.cell(row=row, column=9, value='Error') ws.cell(row=row, column=10, value='') except: pass continue # Auto-adjust column widths for column in ws.columns: max_length = 0 column_letter = column[0].column_letter for cell in column: try: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = min(max_length + 2, 50) ws.column_dimensions[column_letter].width = adjusted_width # Save to response response = HttpResponse( content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response['Content-Disposition'] = f'attachment; filename="repayments_export_{timezone.now().strftime("%Y%m%d")}.xlsx"' wb.save(response) return response except Exception as e: return HttpResponse(f"Error generating Excel file: {str(e)}", status=500) @login_required def export_repayments_pdf(request): """Export repayments to PDF with comprehensive analysis and charts""" # Check if user has permission to export repayments if not request.user.has_permission('repayments', 'export'): messages.error(request, 'You do not have permission to export repayment data.') return redirect('loans:repayments') try: from django.http import HttpResponse from django.template.loader import get_template from django.template import Context import io from reportlab.lib.pagesizes import letter, A4 from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, PageBreak from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle from reportlab.lib import colors from reportlab.lib.units import inch from reportlab.graphics.shapes import Drawing, Rect, String from reportlab.graphics.charts.barcharts import VerticalBarChart from reportlab.graphics.charts.piecharts import Pie from reportlab.graphics.charts.linecharts import HorizontalLineChart from reportlab.graphics import renderPDF from collections import defaultdict, Counter from datetime import datetime, timedelta import matplotlib.pyplot as plt import matplotlib matplotlib.use('Agg') # Use non-interactive backend import base64 from io import BytesIO # Get selected branch from session selected_branch_id = request.session.get('selected_branch_id') # Base queryset with branch filtering repayments_list = Repayment.objects.select_related( 'loan', 'loan__borrower', 'loan__application__loan_product' ) # Apply branch filtering if a branch is selected if selected_branch_id: repayments_list = repayments_list.filter(loan__borrower__branch_id=selected_branch_id) # Apply filters from request search_query = request.GET.get('search', '') if search_query: repayments_list = repayments_list.filter( Q(loan__borrower__first_name__icontains=search_query) | Q(loan__borrower__last_name__icontains=search_query) | Q(loan__borrower__phone_number__icontains=search_query) | Q(loan__loan_number__icontains=search_query) | Q(payment_method__icontains=search_query) ) start_date = request.GET.get('start_date') end_date = request.GET.get('end_date') if start_date: repayments_list = repayments_list.filter(payment_date__gte=start_date) if end_date: repayments_list = repayments_list.filter(payment_date__lte=end_date) payment_method = request.GET.get('payment_method') if payment_method: repayments_list = repayments_list.filter(payment_method=payment_method) loan_status = request.GET.get('loan_status') if loan_status: repayments_list = repayments_list.filter(loan__status=loan_status) receipt_status = request.GET.get('receipt_status') if receipt_status == 'with_receipt': repayments_list = repayments_list.filter(receipt__isnull=False) elif receipt_status == 'without_receipt': repayments_list = repayments_list.filter(receipt__isnull=True) repayments_list = repayments_list.order_by('-payment_date') # Create PDF response = HttpResponse(content_type='application/pdf') response['Content-Disposition'] = f'attachment; filename="repayments_analysis_report_{timezone.now().strftime("%Y%m%d")}.pdf"' doc = SimpleDocTemplate(response, pagesize=A4) elements = [] # Styles styles = getSampleStyleSheet() title_style = ParagraphStyle( 'CustomTitle', parent=styles['Heading1'], fontSize=18, spaceAfter=30, alignment=1, # Center alignment textColor=colors.darkblue ) section_style = ParagraphStyle( 'SectionTitle', parent=styles['Heading2'], fontSize=14, spaceAfter=12, spaceBefore=20, textColor=colors.darkgreen ) # Title Page title = Paragraph("Loan Repayments Analysis Report", title_style) elements.append(title) elements.append(Spacer(1, 20)) # Executive Summary summary_title = Paragraph("Executive Summary", section_style) elements.append(summary_title) # Calculate comprehensive statistics total_repayments = repayments_list.count() total_amount = repayments_list.aggregate(Sum('amount'))['amount__sum'] or 0 avg_repayment = total_amount / total_repayments if total_repayments > 0 else 0 # Payment method analysis payment_methods = repayments_list.values('payment_method').annotate( count=Count('id'), total_amount=Sum('amount') ).order_by('-total_amount') # Loan status analysis loan_statuses = repayments_list.values('loan__status').annotate( count=Count('id'), total_amount=Sum('amount') ).order_by('-total_amount') # Monthly trend analysis (last 12 months) end_date = timezone.now().date() start_date = end_date - timedelta(days=365) monthly_data = repayments_list.filter( payment_date__gte=start_date ).extra( select={'month': "DATE_FORMAT(payment_date, '%%Y-%%m-01')"} ).values('month').annotate( count=Count('id'), total_amount=Sum('amount') ).order_by('month') # Daily analysis (last 30 days) daily_start = end_date - timedelta(days=30) daily_data = repayments_list.filter( payment_date__gte=daily_start ).extra( select={'day': "DATE_FORMAT(payment_date, '%%Y-%%m-%%d')"} ).values('day').annotate( count=Count('id'), total_amount=Sum('amount') ).order_by('day') # Summary statistics summary_data = [ ['Metric', 'Value'], ['Total Repayments', f"{total_repayments:,}"], ['Total Amount Collected', f"KSh {total_amount:,.2f}"], ['Average Repayment', f"KSh {avg_repayment:,.2f}"], ['Report Period', f"{start_date} to {end_date}"], ['Generated', timezone.now().strftime('%Y-%m-%d %H:%M')], ['Branch', 'All Branches' if not selected_branch_id else f"Branch ID: {selected_branch_id}"] ] summary_table = Table(summary_data, colWidths=[2.5*inch, 2.5*inch]) summary_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.darkblue), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'LEFT'), ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), ('FONTSIZE', (0, 0), (-1, 0), 12), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.lightgrey), ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 1), (-1, -1), 10), ('GRID', (0, 0), (-1, -1), 1, colors.black), ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'), ])) elements.append(summary_table) elements.append(Spacer(1, 20)) # Payment Methods Analysis payment_title = Paragraph("Payment Methods Analysis", section_style) elements.append(payment_title) payment_data = [['Payment Method', 'Count', 'Total Amount', 'Percentage']] for method in payment_methods: try: method_name = method.get('payment_method', 'Unknown') count = method.get('count', 0) or 0 method_total = method.get('total_amount', 0) or 0 percentage = (method_total / total_amount * 100) if total_amount > 0 else 0 payment_data.append([ method_name.title(), str(count), f"KSh {method_total:,.2f}", f"{percentage:.1f}%" ]) except (KeyError, TypeError, AttributeError): continue if len(payment_data) > 1: # More than just header payment_table = Table(payment_data, colWidths=[1.5*inch, 1*inch, 1.5*inch, 1*inch]) payment_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.darkgreen), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), ('FONTSIZE', (0, 0), (-1, 0), 10), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.beige), ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 1), (-1, -1), 9), ('GRID', (0, 0), (-1, -1), 1, colors.black), ])) elements.append(payment_table) else: no_data = Paragraph("No payment method data available.", styles['Normal']) elements.append(no_data) elements.append(Spacer(1, 20)) # Loan Status Analysis status_title = Paragraph("Loan Status Distribution", section_style) elements.append(status_title) status_data = [['Loan Status', 'Count', 'Total Amount', 'Percentage']] for status in loan_statuses: try: status_name = status.get('loan__status', 'Unknown') count = status.get('count', 0) or 0 status_total = status.get('total_amount', 0) or 0 percentage = (status_total / total_amount * 100) if total_amount > 0 else 0 status_data.append([ status_name.title(), str(count), f"KSh {status_total:,.2f}", f"{percentage:.1f}%" ]) except (KeyError, TypeError, AttributeError): continue if len(status_data) > 1: # More than just header status_table = Table(status_data, colWidths=[1.5*inch, 1*inch, 1.5*inch, 1*inch]) status_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.darkred), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), ('FONTSIZE', (0, 0), (-1, 0), 10), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.lightblue), ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 1), (-1, -1), 9), ('GRID', (0, 0), (-1, -1), 1, colors.black), ])) elements.append(status_table) else: no_data = Paragraph("No loan status data available.", styles['Normal']) elements.append(no_data) elements.append(Spacer(1, 20)) # Monthly Trend Analysis trend_title = Paragraph("Monthly Repayment Trends (Last 12 Months)", section_style) elements.append(trend_title) if monthly_data: trend_data = [['Month', 'Repayments', 'Amount (KSh)']] for month in monthly_data: try: # month['month'] is already a string from DATE_FORMAT month_name = month.get('month', 'N/A') if month.get('month') else 'N/A' count = month.get('count', 0) or 0 total_amount = month.get('total_amount', 0) or 0 trend_data.append([ month_name, str(count), f"{total_amount:,.0f}" ]) except (KeyError, TypeError, AttributeError): # Skip invalid month data continue if len(trend_data) > 1: # More than just header trend_table = Table(trend_data, colWidths=[1.5*inch, 1.5*inch, 2*inch]) trend_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.purple), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), ('FONTSIZE', (0, 0), (-1, 0), 10), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.lightgrey), ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 1), (-1, -1), 9), ('GRID', (0, 0), (-1, -1), 1, colors.black), ])) elements.append(trend_table) else: no_data = Paragraph("No monthly trend data available.", styles['Normal']) elements.append(no_data) else: no_data = Paragraph("No monthly data available for the selected period.", styles['Normal']) elements.append(no_data) elements.append(Spacer(1, 20)) # Key Insights insights_title = Paragraph("Key Insights & Recommendations", section_style) elements.append(insights_title) # Generate insights based on data insights = [] # Payment method insights if payment_methods: try: top_method = payment_methods[0] method_name = top_method.get('payment_method', 'Unknown') count = top_method.get('count', 0) or 0 method_total = top_method.get('total_amount', 0) or 0 insights.append(f"• {method_name.title()} is the most popular payment method with {count} transactions ({method_total:,.0f} KSh)") except (IndexError, KeyError, TypeError): pass # Amount insights if total_amount > 0: insights.append(f"• Total collection of {total_amount:,.0f} KSh across {total_repayments} repayments") insights.append(f"• Average repayment amount is {avg_repayment:,.0f} KSh") # Status insights if loan_statuses: try: active_loans = next((s for s in loan_statuses if s.get('loan__status') == 'active'), None) if active_loans: count = active_loans.get('count', 0) or 0 status_total = active_loans.get('total_amount', 0) or 0 insights.append(f"• {count} repayments from active loans ({status_total:,.0f} KSh)") except (KeyError, TypeError, AttributeError): pass # Monthly trend insights if len(monthly_data) >= 2: try: recent_month = monthly_data[len(monthly_data)-1] previous_month = monthly_data[len(monthly_data)-2] if recent_month['total_amount'] and previous_month['total_amount'] and previous_month['total_amount'] > 0: growth = ((recent_month['total_amount'] - previous_month['total_amount']) / previous_month['total_amount']) * 100 insights.append(f"• Recent growth trend: {growth:.1f}% increase in collection from previous month") except (IndexError, KeyError, TypeError): pass # Skip trend analysis if data is insufficient # Add recommendations insights.extend([ "• Consider promoting the most successful payment methods", "• Monitor loan status distribution for risk management", "• Track monthly trends to identify seasonal patterns", "• Focus on converting pending repayments to completed status" ]) for insight in insights: insight_para = Paragraph(insight, styles['Normal']) elements.append(insight_para) elements.append(Spacer(1, 6)) elements.append(PageBreak()) # Detailed Repayments Table details_title = Paragraph("Detailed Repayments List", section_style) elements.append(details_title) table_data = [['Date', 'Borrower', 'Loan #', 'Amount', 'Method', 'Status', 'Receipt']] for repayment in repayments_list[:50]: # Limit to 50 records for PDF receipt_status = "Yes" if repayment.receipt else "No" table_data.append([ repayment.payment_date.strftime('%Y-%m-%d'), repayment.loan.borrower.get_full_name()[:15], # Truncate long names repayment.loan.loan_number[:10], # Truncate loan numbers f"KSh {repayment.amount:,.0f}", repayment.payment_method[:8], # Truncate method names repayment.loan.status.title()[:8], # Truncate status receipt_status ]) if len(repayments_list) > 50: table_data.append(['...', f'... and {len(repayments_list) - 50} more records', '', '', '', '', '']) repayments_table = Table(table_data, colWidths=[0.8*inch, 1.2*inch, 0.8*inch, 1*inch, 0.8*inch, 0.8*inch, 0.6*inch]) repayments_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.darkblue), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), ('FONTSIZE', (0, 0), (-1, 0), 8), ('BOTTOMPADDING', (0, 0), (-1, 0), 8), ('BACKGROUND', (0, 1), (-1, -1), colors.beige), ('GRID', (0, 0), (-1, -1), 1, colors.black), ('FONTSIZE', (0, 1), (-1, -1), 7), ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.lightgrey]), ])) elements.append(repayments_table) # Footer elements.append(Spacer(1, 20)) footer_text = f"Report generated on {timezone.now().strftime('%Y-%m-%d %H:%M')} | HAVEN GRAZURI INVESTMENT LIMITED| Page 1 of 2" footer = Paragraph(footer_text, styles['Normal']) elements.append(footer) # Build PDF doc.build(elements) return response except Exception as e: return HttpResponse(f"Error generating PDF file: {str(e)}", status=500) @login_required def new_repayment(request): """Create a new repayment""" # Check if user has permission to create repayments if not request.user.has_permission('repayments', 'create'): messages.error(request, 'You do not have permission to record repayments.') return redirect('loans:repayments') if request.method == 'POST': try: loan_id = request.POST.get('loan') amount = Decimal(request.POST.get('amount')) payment_method = request.POST.get('payment_method') payment_date_str = request.POST.get('payment_date') mpesa_phone = request.POST.get('mpesa_phone', '').strip() mpesa_transaction_id = request.POST.get('mpesa_transaction_id', '').strip() notes = request.POST.get('notes', '').strip() if not loan_id or amount <= 0: raise ValueError("Invalid loan or amount") loan = get_object_or_404(Loan, id=loan_id, status='active') # Parse payment date if payment_date_str: try: payment_date = datetime.strptime(payment_date_str, '%Y-%m-%dT%H:%M') from django.conf import settings if settings.USE_TZ: payment_date = timezone.make_aware(payment_date) except ValueError: if settings.USE_TZ: payment_date = get_current_datetime() else: payment_date = datetime.now() else: from django.conf import settings if settings.USE_TZ: payment_date = get_current_datetime() else: payment_date = datetime.now() # Create repayment repayment = Repayment( loan=loan, amount=amount, payment_method=payment_method, payment_date=payment_date, mpesa_phone_number=mpesa_phone or None, mpesa_transaction_id=mpesa_transaction_id or None, notes=notes or None ) repayment.save() # Create audit log from utils.models import AuditLog AuditLog.objects.create( user=request.user, action='payment', model_name='Repayment', object_id=str(repayment.id), description=f'Recorded payment of KES {amount:,.2f} for loan {loan.loan_number}' ) # Send payment confirmation SMS try: from payments.sms_service import send_payment_confirmation_sms new_balance = float(loan.outstanding_amount) send_payment_confirmation_sms( borrower_name=loan.borrower.get_full_name(), amount=float(amount), reference=repayment.receipt_number, loan_number=loan.loan_number, new_balance=new_balance, customer_phone=loan.borrower.phone_number, ) except Exception as sms_err: logger.warning(f"Payment confirmation SMS failed for {loan.loan_number}: {sms_err}") messages.success(request, f'Payment of KES {amount:,.2f} recorded successfully for loan {loan.loan_number}.') return redirect('loans:repayments') except (ValueError, DecimalException) as e: messages.error(request, f'Error recording payment: {str(e)}') except Exception as e: messages.error(request, f'Unexpected error: {str(e)}') # Only include truly active loans for new repayments loans = Loan.objects.filter(status='active').select_related('borrower').order_by('loan_number') return render(request, 'loans/new_repayment.html', {'loans': loans}) @login_required def repayment_detail(request, pk): """Detail view for a repayment""" # Check if user has permission to access repayments if not request.user.has_permission('repayments', 'access'): messages.error(request, 'You do not have permission to view repayment details.') return redirect('loans:repayments') repayment = get_object_or_404(Repayment, pk=pk) return render(request, 'loans/repayment_detail.html', {'repayment': repayment}) @login_required def rollovers(request): """List all rollover requests""" # Check if user has permission to access loans if not request.user.has_permission('loans', 'access'): messages.error(request, 'You do not have permission to view rollovers.') return redirect('loans:loans') rollovers = RolloverRequest.objects.select_related('loan', 'borrower').order_by('-requested_at') return render(request, 'loans/rollovers.html', {'rollovers': rollovers}) @login_required def approve_rollover(request, pk): """Approve a rollover request with enhanced parameters""" # Check if user has permission to approve loans if not request.user.has_permission('loans', 'approve'): messages.error(request, 'You do not have permission to approve rollovers.') return redirect('loans:rollovers') rollover = get_object_or_404(RolloverRequest, pk=pk) if request.method == 'POST': notes = request.POST.get('notes', '') # Handle disbursement date (admin only can set past dates) disbursement_date_str = request.POST.get('disbursement_date') disbursement_date = None if disbursement_date_str: try: disbursement_date = datetime.strptime(disbursement_date_str, '%Y-%m-%d') # Check if user is trying to set a past date if disbursement_date.date() < datetime.now().date(): if not request.user.is_admin(): messages.error(request, 'Only administrators can set past disbursement dates.') return redirect('loans:rollovers') # Convert to timezone-aware datetime for the new loan if USE_TZ is True from django.conf import settings if settings.USE_TZ: disbursement_date = timezone.make_aware(disbursement_date) except ValueError: messages.error(request, 'Invalid disbursement date format.') return redirect('loans:rollovers') # Use the requested parameters from the rollover request new_amount = rollover.requested_amount new_duration = rollover.requested_duration new_interest_rate = rollover.requested_interest_rate new_processing_fee = rollover.requested_processing_fee # Allow admin to override parameters if provided in form override_amount = request.POST.get('override_amount') override_duration = request.POST.get('override_duration') override_interest_rate = request.POST.get('override_interest_rate') override_processing_fee = request.POST.get('override_processing_fee') if override_amount: try: new_amount = Decimal(override_amount) except (ValueError, DecimalException): messages.error(request, 'Invalid override amount format') return redirect('loans:rollovers') if override_duration: try: new_duration = int(override_duration) except ValueError: messages.error(request, 'Invalid override duration format') return redirect('loans:rollovers') if override_interest_rate: try: new_interest_rate = Decimal(override_interest_rate) except (ValueError, DecimalException): messages.error(request, 'Invalid override interest rate format') return redirect('loans:rollovers') if override_processing_fee: try: new_processing_fee = Decimal(override_processing_fee) except (ValueError, DecimalException): messages.error(request, 'Invalid override processing fee format') return redirect('loans:rollovers') # Process rollover approval with enhanced parameters new_loan = rollover.approve( approved_by=request.user, notes=notes, new_amount=new_amount, new_duration=new_duration, new_interest_rate=new_interest_rate, new_processing_fee=new_processing_fee, disbursement_date=disbursement_date ) messages.success(request, f'Rollover approved! New active loan {new_loan.loan_number} created.') return redirect('loans:loan_detail', pk=new_loan.pk) return redirect('loans:rollovers') @login_required def reject_rollover(request, pk): """Reject a rollover request""" # Check if user has permission to reject loans if not request.user.has_permission('loans', 'reject'): messages.error(request, 'You do not have permission to reject rollovers.') return redirect('loans:rollovers') rollover = get_object_or_404(RolloverRequest, pk=pk) if request.method == 'POST': notes = request.POST.get('notes', '') # Process rollover rejection rollover.reject( rejected_by=request.user, notes=notes ) messages.success(request, 'Rollover request rejected') return redirect('loans:loan_detail', pk=rollover.loan.pk) return redirect('loans:loan_detail', pk=rollover.loan.pk) @login_required def mpesa_transactions(request): """List all M-Pesa transactions""" transactions = MpesaTransaction.objects.order_by('-created_at') return render(request, 'loans/mpesa_transactions.html', {'transactions': transactions}) @login_required def mpesa_detail(request, pk): """Detail view for an M-Pesa transaction""" transaction = get_object_or_404(MpesaTransaction, pk=pk) return render(request, 'loans/mpesa_detail.html', {'transaction': transaction}) @login_required def record_repayment(request, pk): """Record a new repayment for a loan""" # Check if user has permission to create repayments if not request.user.has_permission('repayments', 'create'): messages.error(request, 'You do not have permission to record repayments.') return redirect('loans:loan_detail', pk=pk) loan = get_object_or_404(Loan, pk=pk) if request.method == 'POST': try: amount = Decimal(request.POST.get('amount')) payment_method = request.POST.get('payment_method') if amount <= 0: raise ValueError("Amount must be greater than 0") # Get payment date from form or use current time payment_date_str = request.POST.get('payment_date') if payment_date_str: try: # Parse the datetime-local input format payment_date = datetime.strptime(payment_date_str, '%Y-%m-%dT%H:%M') # Handle timezone based on Django settings from django.conf import settings if settings.USE_TZ: payment_date = timezone.make_aware(payment_date) except ValueError: # If parsing fails, use current time if settings.USE_TZ: payment_date = get_current_datetime() else: payment_date = datetime.now() else: # Use appropriate datetime based on timezone settings from django.conf import settings if settings.USE_TZ: payment_date = get_current_datetime() else: payment_date = datetime.now() # Create repayment record with retry logic for receipt number conflicts max_attempts = 3 for attempt in range(max_attempts): try: # Use Django ORM instead of raw SQL to ensure save method is called repayment = Repayment( loan=loan, amount=amount, payment_method=payment_method, payment_date=payment_date, receipt_number=f"RCP-{timezone.now().strftime('%Y%m%d%H%M%S')}" ) repayment.save() # This will trigger the save method that updates loan.amount_paid break # Success, exit retry loop except IntegrityError as e: if 'receipt_number' in str(e) and attempt < max_attempts - 1: # Receipt number conflict, retry with slight delay import time time.sleep(0.1) continue else: # Different error or max attempts reached raise ValueError("Unable to generate unique receipt number. Please try again.") # Create audit log AuditLog.objects.create( user=request.user, action='payment', model_name='Repayment', object_id=str(repayment.id), description=f'Recorded payment of KES {amount:,.2f} for loan {loan.loan_number}' ) # If M-Pesa, create transaction record if payment_method == 'mpesa': phone_number = request.POST.get('mpesa_phone') try: # Create MpesaTransaction without the loan field to avoid missing loan_id column error mpesa_transaction = MpesaTransaction( transaction_type='c2b', repayment=repayment, phone_number=phone_number, amount=amount, status='success' ) # Only set loan if the database column exists # Since we have the repayment, we can get the loan through that relationship # mpesa_transaction.loan = loan # Commented out to avoid missing column error mpesa_transaction.save() except Exception as e: # Log the error but don't fail the entire repayment process print(f"Warning: Could not create MpesaTransaction: {e}") # The repayment was still successful, just the M-Pesa transaction logging failed # Loan payment tracking is handled automatically by the Repayment model's save method # Send payment confirmation SMS try: from payments.sms_service import send_payment_confirmation_sms new_balance = float(loan.outstanding_amount) send_payment_confirmation_sms( borrower_name=loan.borrower.get_full_name(), amount=float(amount), reference=repayment.receipt_number, loan_number=loan.loan_number, new_balance=new_balance, customer_phone=loan.borrower.phone_number, ) except Exception as sms_err: logger.warning(f"Payment SMS failed for {loan.loan_number}: {sms_err}") messages.success(request, 'Payment recorded successfully', extra_tags='loan_detail') return redirect('loans:loan_detail', pk=pk) except (ValueError, DecimalException) as e: messages.error(request, str(e), extra_tags='loan_detail') return redirect('loans:loan_detail', pk=pk) return redirect('loans:loan_detail', pk=pk) @login_required def request_rollover(request, pk): """Request a loan rollover with enhanced options""" # Check if user has permission to create loans (for rollover requests) if not request.user.has_permission('loans', 'create'): messages.error(request, 'You do not have permission to request rollovers.') return redirect('loans:loan_detail', pk=pk) loan = get_object_or_404(Loan, pk=pk) if request.method == 'POST': try: # Get enhanced rollover data with proper decimal conversion requested_amount_str = request.POST.get('requested_amount', '0') requested_duration_str = request.POST.get('requested_duration', '0') interest_rate_str = request.POST.get('interest_rate', '0') processing_fee_str = request.POST.get('processing_fee', '0') rollover_date_str = request.POST.get('rollover_date', '').strip() reason = request.POST.get('reason', '').strip() # Validate and convert amounts with proper error handling try: requested_amount = Decimal(str(requested_amount_str).replace(',', '')) if requested_amount <= 0: raise ValueError("Amount must be greater than 0") except (ValueError, TypeError, DecimalException) as e: messages.error(request, f'Invalid loan amount: {requested_amount_str}', extra_tags='loan_detail') return redirect('loans:loan_detail', pk=pk) try: requested_duration = int(requested_duration_str) if requested_duration <= 0: raise ValueError("Duration must be greater than 0 days") except (ValueError, TypeError) as e: messages.error(request, f'Invalid duration: {requested_duration_str}', extra_tags='loan_detail') return redirect('loans:loan_detail', pk=pk) try: interest_rate = Decimal(str(interest_rate_str).replace(',', '')) if interest_rate_str else Decimal('0') except (ValueError, TypeError, DecimalException): interest_rate = Decimal('0') try: processing_fee = Decimal(str(processing_fee_str).replace(',', '')) if processing_fee_str else Decimal('0') except (ValueError, TypeError, DecimalException): processing_fee = Decimal('0') # Parse rollover date rollover_date = None if rollover_date_str: try: from datetime import datetime rollover_date = datetime.strptime(rollover_date_str, '%Y-%m-%d').date() except ValueError: messages.error(request, 'Invalid rollover date format', extra_tags='loan_detail') return redirect('loans:loan_detail', pk=pk) if not reason: messages.error(request, 'Please provide a reason for the rollover', extra_tags='loan_detail') return redirect('loans:loan_detail', pk=pk) # Calculate rollover fee based on loan product rollover_fee_percentage = loan.application.loan_product.rollover_fee_percentage rollover_fee = (loan.outstanding_amount * rollover_fee_percentage) / Decimal('100') # Create enhanced rollover request rollover_request = RolloverRequest.objects.create( loan=loan, borrower=loan.borrower, # Use loan borrower, not request user requested_amount=requested_amount, requested_duration=requested_duration, requested_interest_rate=interest_rate if interest_rate > 0 else None, requested_processing_fee=processing_fee if processing_fee > 0 else None, rollover_date=rollover_date, reason=reason, rollover_fee=rollover_fee ) messages.success(request, 'Enhanced rollover request submitted successfully', extra_tags='loan_detail') return redirect('loans:loan_detail', pk=pk) except (ValueError, DecimalException) as e: messages.error(request, str(e), extra_tags='loan_detail') return redirect('loans:loan_detail', pk=pk) return redirect('loans:loan_detail', pk=pk) @login_required def add_existing_loan(request): """Add an existing loan to the system, with client search on GET""" from users.models import CustomUser if request.method == 'POST': try: # Get form data client = get_object_or_404(CustomUser, pk=request.POST.get('client'), role='borrower') loan_number = request.POST.get('loan_number') amount = Decimal(request.POST.get('amount')) disbursement_date = request.POST.get('disbursement_date') due_date = request.POST.get('due_date') status = request.POST.get('status') # Create loan application application = LoanApplication.objects.create( borrower=client, loan_product=LoanProduct.objects.first(), # Use default product for existing loans requested_amount=amount, status='approved', reviewed_by=request.user, reviewed_at=datetime.now(), approval_notes='Added as existing loan' ) # Create loan record loan = Loan.objects.create( application=application, borrower=client, loan_number=loan_number, principal_amount=amount, total_amount=amount, # For existing loans, use principal as total disbursement_date=disbursement_date, due_date=due_date, status=status, created_by=request.user ) # Create audit log AuditLog.objects.create( user=request.user, action='create', model_name='Loan', object_id=str(loan.id), description=f'Added existing loan for {client.get_full_name()}' ) messages.success(request, 'Existing loan added successfully!', extra_tags='loan_detail') return redirect('loans:loan_detail', pk=loan.pk) except Exception as e: messages.error(request, f'Error adding loan: {str(e)}', extra_tags='loans') # GET: Render form with client search borrowers = CustomUser.objects.filter(role='borrower', is_active=True) return render(request, 'loans/add_existing_loan.html', {'borrowers': borrowers}) @login_required def api_loan_data(request): """API endpoint for loan data with enhanced analytics""" period = request.GET.get('period', '6m') selected_branch_id = request.session.get('selected_branch_id') today = datetime.now().date() # Determine date range based on period if period == '1w': months_back = 1 start_date = today - timedelta(days=7) elif period == '1m': months_back = 1 start_date = today - timedelta(days=30) elif period == '3m': months_back = 3 start_date = today - timedelta(days=90) elif period == '6m': months_back = 6 start_date = today - timedelta(days=180) elif period == '1y': months_back = 12 start_date = today - timedelta(days=365) elif period == '2y': months_back = 24 start_date = today - timedelta(days=730) elif period == '5y': months_back = 60 start_date = today - timedelta(days=1825) elif period == 'all': months_back = 120 # 10 years max start_date = today - timedelta(days=3650) else: # 6m default months_back = 6 start_date = today - timedelta(days=180) # Base querysets with branch filtering loans_qs = Loan.objects.all() repayments_qs = Repayment.objects.all() if selected_branch_id: loans_qs = loans_qs.filter(borrower__branch_id=selected_branch_id) repayments_qs = repayments_qs.filter(loan__borrower__branch_id=selected_branch_id) # Get time-based data labels = [] disbursements = [] repayments = [] # Determine the appropriate time interval and data points if period in ['1w']: # Daily data for 1 week data_points = 7 for i in range(data_points): day_start = today - timedelta(days=i) day_end = day_start + timedelta(days=1) day_start = datetime.combine(day_start, datetime.min.time()) day_end = datetime.combine(day_end, datetime.min.time()) disbursed = loans_qs.filter( created_at__gte=day_start, created_at__lt=day_end ).aggregate(total=Sum('principal_amount'))['total'] or 0 repaid = repayments_qs.filter( payment_date__gte=day_start, payment_date__lt=day_end ).aggregate(total=Sum('amount'))['total'] or 0 day_label = day_start.strftime('%a %d') labels.insert(0, day_label) disbursements.insert(0, float(disbursed)) repayments.insert(0, float(repaid)) elif period in ['1m']: # Weekly data for 1 month data_points = 4 for i in range(data_points): week_start = today - timedelta(weeks=i) week_end = week_start + timedelta(weeks=1) week_start = datetime.combine(week_start, datetime.min.time()) week_end = datetime.combine(week_end, datetime.min.time()) disbursed = loans_qs.filter( created_at__gte=week_start, created_at__lt=week_end ).aggregate(total=Sum('principal_amount'))['total'] or 0 repaid = repayments_qs.filter( payment_date__gte=week_start, payment_date__lt=week_end ).aggregate(total=Sum('amount'))['total'] or 0 week_label = f"Week {week_start.strftime('%d %b')}" labels.insert(0, week_label) disbursements.insert(0, float(disbursed)) repayments.insert(0, float(repaid)) else: # Monthly data for longer periods for i in range(months_back): # Calculate month boundaries month_start = datetime.now().replace(day=1) - timedelta(days=30*i) month_start = datetime.combine(month_start.date(), datetime.min.time()) # Get next month start for end boundary if month_start.month == 12: next_month = month_start.replace(year=month_start.year + 1, month=1, day=1) else: next_month = month_start.replace(month=month_start.month + 1, day=1) # Query disbursements for this month disbursed = loans_qs.filter( created_at__gte=month_start, created_at__lt=next_month ).aggregate(total=Sum('principal_amount'))['total'] or 0 # Query repayments for this month repaid = repayments_qs.filter( payment_date__gte=month_start, payment_date__lt=next_month ).aggregate(total=Sum('amount'))['total'] or 0 # Format month label month_label = month_start.strftime('%b %Y') # Insert at beginning to maintain chronological order labels.insert(0, month_label) disbursements.insert(0, float(disbursed)) repayments.insert(0, float(repaid)) # Calculate additional metrics total_disbursed = sum(disbursements) total_repaid = sum(repayments) net_flow = total_disbursed - total_repaid # Get loan status distribution (not filtered by time period) distribution_loans_qs = Loan.objects.all() if selected_branch_id: distribution_loans_qs = distribution_loans_qs.filter(borrower__branch_id=selected_branch_id) loan_statuses = distribution_loans_qs.values('status').annotate(count=Count('id')).order_by('status') status_labels = [] status_data = [] status_colors = [] color_map = { 'active': '#10B981', # Green 'paid': '#3B82F6', # Blue 'defaulted': '#EF4444', # Red 'rolled_over': '#F59E0B', # Yellow 'pending': '#8B5CF6' # Purple } for status in loan_statuses: status_labels.append(status['status'].title()) status_data.append(status['count']) status_colors.append(color_map.get(status['status'], '#6B7280')) # Get client growth data (filtered by time period for growth chart) users_qs = CustomUser.objects.filter(role='borrower') if selected_branch_id: users_qs = users_qs.filter(branch_id=selected_branch_id) client_growth = [] for i in range(months_back): month_start = datetime.now().replace(day=1) - timedelta(days=30*i) month_start = datetime.combine(month_start.date(), datetime.min.time()) if month_start.month == 12: next_month = month_start.replace(year=month_start.year + 1, month=1, day=1) else: next_month = month_start.replace(month=month_start.month + 1, day=1) new_clients = users_qs.filter( date_joined__gte=month_start, date_joined__lt=next_month ).count() client_growth.insert(0, new_clients) return JsonResponse({ 'success': True, 'period': period, 'labels': labels, 'disbursements': disbursements, 'repayments': repayments, 'summary': { 'total_disbursed': total_disbursed, 'total_repaid': total_repaid, 'net_flow': net_flow, 'months_count': months_back }, 'distribution': { 'labels': status_labels, 'data': status_data, 'colors': status_colors }, 'client_growth': { 'labels': labels, 'data': client_growth } }) @login_required @portfolio_access_required def loans_ajax(request): """AJAX endpoint for loans list with filters""" try: # Get selected branch from session selected_branch_id = request.session.get('selected_branch_id') # Get filter parameters search = request.GET.get('search', '').strip() status_filter = request.GET.get('status', '') product_type_filter = request.GET.get('product_type', '') date_from = request.GET.get('date_from', '') date_to = request.GET.get('date_to', '') # Start with all loans and applications loans = Loan.objects.select_related('borrower', 'loan_product').all() applications = LoanApplication.objects.select_related('borrower', 'loan_product').all() # Apply portfolio-based access control for staff members if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser: # Staff members can only see loans from their assigned borrowers loans = loans.filter(borrower__portfolio_manager=request.user) applications = applications.filter(borrower__portfolio_manager=request.user) elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser: # Secretaries and auditors can only see loans from borrowers in their branch if request.user.branch: loans = loans.filter(borrower__branch=request.user.branch) applications = applications.filter(borrower__branch=request.user.branch) # Apply branch filtering if a branch is selected if selected_branch_id: loans = loans.filter(borrower__branch_id=selected_branch_id) applications = applications.filter(borrower__branch_id=selected_branch_id) # Apply search filter if search: loans = loans.filter( Q(loan_number__icontains=search) | Q(borrower__first_name__icontains=search) | Q(borrower__last_name__icontains=search) | Q(borrower__business_name__icontains=search) | Q(borrower__phone_number__icontains=search) ) applications = applications.filter( Q(application_number__icontains=search) | Q(borrower__first_name__icontains=search) | Q(borrower__last_name__icontains=search) | Q(borrower__business_name__icontains=search) | Q(borrower__phone_number__icontains=search) ) # Apply status filter if status_filter: loans = loans.filter(status=status_filter) applications = applications.filter(status=status_filter) # Apply product type filter if product_type_filter: loans = loans.filter(loan_product__product_type=product_type_filter) applications = applications.filter(loan_product__product_type=product_type_filter) # Apply date filters if date_from: loans = loans.filter(created_at__gte=date_from) applications = applications.filter(submitted_at__gte=date_from) if date_to: loans = loans.filter(created_at__lte=date_to) applications = applications.filter(submitted_at__lte=date_to) # Combine and order by creation date all_items = list(loans) + list(applications) all_items.sort(key=lambda x: x.created_at if hasattr(x, 'created_at') else x.submitted_at, reverse=True) # Render the table HTML from django.template.loader import render_to_string html = render_to_string('loans/loans_table.html', { 'loans': all_items, 'request': request }, request=request) return JsonResponse({ 'success': True, 'html': html, 'count': len(all_items) }) except Exception as e: return JsonResponse({ 'success': False, 'error': str(e) }) @login_required def api_recent_activity(request): """API endpoint for recent activity""" # Get recent activity with naive datetime queries recent_activity = AuditLog.objects.select_related('user').order_by('-created_at')[:10] activities = [] for activity in recent_activity: activity_type = activity.action icon = { 'create': 'plus-circle', 'update': 'edit', 'delete': 'trash', 'payment': 'money-bill-wave', 'login': 'sign-in-alt', 'logout': 'sign-out-alt' }.get(activity_type, 'info') activities.append({ 'type': activity_type, 'user': activity.user.get_full_name() if activity.user else 'System', 'action': activity.description, 'timestamp': activity.created_at.isoformat(), 'icon': icon }) return JsonResponse({'activities': activities}) @login_required @admin_only def edit_loan(request, pk): """ Edit an existing loan (admin only) Requirements: Security Requirements 1, 2, 3 """ from .validators import LoanEditValidator from django.core.exceptions import ValidationError as DjangoValidationError loan = get_object_or_404(Loan, pk=pk) # Get available status choices for the dropdown status_choices = Loan.STATUS_CHOICES if request.method == 'POST': try: # Use validator to validate all form inputs # Requirements: 3.12, 10.7, 10.8 validated_data = LoanEditValidator.validate_loan_edit_form(request.POST) # Track changes for audit log changes = [] old_principal = loan.principal_amount old_interest_amount = loan.interest_amount old_processing_fee = loan.processing_fee old_status = loan.status old_disbursement_date = loan.disbursement_date old_due_date = loan.due_date # Get status from POST data directly (validator might not include it) new_status = request.POST.get('status', old_status) # Validate status transition if status is changing if new_status != old_status: try: loan.validate_status_transition(new_status) # Additional validation for 'paid' status if new_status == 'paid': total_owed = loan.total_amount + loan.total_penalties if loan.amount_paid < total_owed: messages.error(request, f'Cannot mark as paid: amount paid (KES {loan.amount_paid:,.2f}) ' f'is less than total owed (KES {total_owed:,.2f})') return render(request, 'loans/edit_loan.html', { 'loan': loan, 'status_choices': status_choices }) changes.append(f'Status: {old_status} → {new_status}') loan.status = new_status except ValueError as e: messages.error(request, str(e)) return render(request, 'loans/edit_loan.html', { 'loan': loan, 'status_choices': status_choices }) # Update loan amounts if old_principal != validated_data['principal_amount']: changes.append(f'Principal: KES {old_principal:,.2f} → KES {validated_data["principal_amount"]:,.2f}') if old_interest_amount != validated_data['interest_amount']: changes.append(f'Interest: KES {old_interest_amount:,.2f} → KES {validated_data["interest_amount"]:,.2f}') if old_processing_fee != validated_data['processing_fee']: changes.append(f'Processing Fee: KES {old_processing_fee:,.2f} → KES {validated_data["processing_fee"]:,.2f}') # Debug logging print(f"DEBUG: Validated data - Principal: {validated_data['principal_amount']}, Interest: {validated_data['interest_amount']}, Processing Fee: {validated_data['processing_fee']}") print(f"DEBUG: Old values - Principal: {old_principal}, Interest: {old_interest_amount}, Processing Fee: {old_processing_fee}") loan.principal_amount = validated_data['principal_amount'] loan.interest_amount = validated_data['interest_amount'] loan.processing_fee = validated_data['processing_fee'] print(f"DEBUG: After assignment - Principal: {loan.principal_amount}, Interest: {loan.interest_amount}, Processing Fee: {loan.processing_fee}") print(f"DEBUG: Total before save: {loan.total_amount}") # Update dates if validated_data['disbursement_date'] != old_disbursement_date.date(): changes.append(f'Disbursement Date: {old_disbursement_date.strftime("%Y-%m-%d")} → {validated_data["disbursement_date"].strftime("%Y-%m-%d")}') loan.disbursement_date = make_datetime_compatible( datetime.combine(validated_data['disbursement_date'], datetime.min.time()) ) if validated_data['due_date'] != old_due_date.date(): changes.append(f'Due Date: {old_due_date.strftime("%Y-%m-%d")} → {validated_data["due_date"].strftime("%Y-%m-%d")}') loan.due_date = make_datetime_compatible( datetime.combine(validated_data['due_date'], datetime.min.time()) ) # Just save the loan - the pre_save signal will recalculate total_amount loan.save() # Refresh from database to get the updated total loan.refresh_from_db() print(f"DEBUG: Total after save and refresh: {loan.total_amount}") # Create audit log entry if changes: AuditLog.objects.create( user=request.user, action='update', model_name='Loan', object_id=str(loan.pk), description=f'Updated loan {loan.loan_number}: {"; ".join(changes)}' ) # Show detailed success message with new total messages.success(request, f'Loan {loan.loan_number} has been updated successfully. ' f'New total: KES {loan.total_amount:,.2f} ' f'(Principal: KES {loan.principal_amount:,.2f} + ' f'Interest: KES {loan.interest_amount:,.2f} + ' f'Processing Fee: KES {loan.processing_fee:,.2f})') return redirect('loans:loan_detail', pk=loan.pk) except DjangoValidationError as e: # Validation errors from our validator messages.error(request, str(e)) return render(request, 'loans/edit_loan.html', { 'loan': loan, 'status_choices': status_choices }) except (ValueError, DecimalException) as e: messages.error(request, 'Invalid input. Please check your values and try again.') return render(request, 'loans/edit_loan.html', { 'loan': loan, 'status_choices': status_choices }) except Exception as e: messages.error(request, f'An error occurred while updating the loan: {str(e)}') return render(request, 'loans/edit_loan.html', { 'loan': loan, 'status_choices': status_choices }) return render(request, 'loans/edit_loan.html', { 'loan': loan, 'status_choices': status_choices }) @login_required def delete_loan(request, pk): """Soft delete a loan (admin only)""" # Check if user has permission to delete loans if not request.user.has_permission('loans', 'delete'): messages.error(request, 'You do not have permission to delete loans.') return redirect('loans:loans') if not request.user.is_admin(): messages.error(request, 'You do not have permission to delete loans.') return redirect('loans:loans') loan = get_object_or_404(Loan, pk=pk) if request.method == 'POST': try: loan_number = loan.loan_number borrower_name = loan.borrower.get_full_name() # Log the deletion AuditLog.objects.create( user=request.user, action='soft_delete', model_name='Loan', object_id=str(loan.pk), description=f'Soft deleted loan {loan_number} for {borrower_name}' ) # Soft delete the loan loan.soft_delete(deleted_by=request.user) messages.success(request, f'Loan {loan_number} has been moved to deleted loans.') return redirect('loans:loans') except Exception as e: messages.error(request, f'An error occurred while deleting the loan: {str(e)}') return redirect('loans:loan_detail', pk=loan.pk) return render(request, 'loans/delete_loan.html', {'loan': loan}) @login_required def deleted_loans(request): """ View deleted loans (admin only) Implements Requirement 5.5: Display ONLY loans where is_deleted equals true. Ensures no active loans appear on this page. Property 15: Deleted loans page exclusivity - For any loan displayed on the deleted loans page, it should have is_deleted flag equal to true, and no loans with is_deleted equal to false should appear. """ if not request.user.is_admin(): messages.error(request, 'You do not have permission to view deleted loans.') return redirect('loans:loans') # Get selected branch from session selected_branch_id = request.session.get('selected_branch_id') # Get all deleted loans (Requirement 5.5: ONLY loans with is_deleted=True) # This filter ensures complete exclusivity - no active loans will appear deleted_loans_queryset = Loan.objects.filter(is_deleted=True).select_related( 'borrower', 'application__loan_product', 'deleted_by' ) # Apply branch filtering if selected_branch_id: deleted_loans_queryset = deleted_loans_queryset.filter(borrower__branch_id=selected_branch_id) elif hasattr(request.user, 'branch') and request.user.branch and not request.user.is_superuser: deleted_loans_queryset = deleted_loans_queryset.filter(borrower__branch=request.user.branch) # Superuser without branch selection sees all deleted loans deleted_loans_queryset = deleted_loans_queryset.order_by('-deleted_at') # Search functionality search_query = request.GET.get('search', '') if search_query: deleted_loans_queryset = deleted_loans_queryset.filter( Q(loan_number__icontains=search_query) | Q(borrower__first_name__icontains=search_query) | Q(borrower__last_name__icontains=search_query) | Q(borrower__phone_number__icontains=search_query) ) # Pagination paginator = Paginator(deleted_loans_queryset, 25) page = request.GET.get('page') try: deleted_loans = paginator.page(page) except PageNotAnInteger: deleted_loans = paginator.page(1) except EmptyPage: deleted_loans = paginator.page(paginator.num_pages) context = { 'deleted_loans': deleted_loans, 'search_query': search_query, 'total_deleted': deleted_loans_queryset.count(), } return render(request, 'loans/deleted_loans.html', context) @login_required def restore_loan(request, pk): """Restore a soft-deleted loan (admin only)""" if not request.user.is_admin(): messages.error(request, 'You do not have permission to restore loans.') return redirect('loans:deleted_loans') loan = get_object_or_404(Loan, pk=pk, is_deleted=True) if request.method == 'POST': try: loan_number = loan.loan_number borrower_name = loan.borrower.get_full_name() # Log the restoration AuditLog.objects.create( user=request.user, action='restore', model_name='Loan', object_id=str(loan.pk), description=f'Restored loan {loan_number} for {borrower_name}' ) # Restore the loan loan.restore() messages.success(request, f'Loan {loan_number} has been restored successfully.') return redirect('loans:loans') except Exception as e: messages.error(request, f'Error restoring loan: {str(e)}') return redirect('loans:deleted_loans') return render(request, 'loans/restore_loan.html', {'loan': loan}) @login_required def permanently_delete_loan(request, pk): """Permanently delete a loan (admin only)""" if not request.user.is_admin(): messages.error(request, 'You do not have permission to permanently delete loans.') return redirect('loans:deleted_loans') loan = get_object_or_404(Loan, pk=pk, is_deleted=True) # Get repayment count for confirmation message repayment_count = loan.repayments.count() if request.method == 'POST': try: loan_number = loan.loan_number borrower_name = loan.borrower.get_full_name() # Log the permanent deletion AuditLog.objects.create( user=request.user, action='permanent_delete', model_name='Loan', object_id=str(loan.pk), description=f'Permanently deleted loan {loan_number} for {borrower_name}' ) # Permanently delete the loan (this will cascade delete repayments) loan.delete() if repayment_count > 0: messages.success(request, f'Loan {loan_number} and its {repayment_count} repayment(s) have been permanently deleted.') else: messages.success(request, f'Loan {loan_number} has been permanently deleted.') return redirect('loans:deleted_loans') except Exception as e: messages.error(request, f'Error permanently deleting loan: {str(e)}') return redirect('loans:deleted_loans') return render(request, 'loans/permanently_delete_loan.html', {'loan': loan}) @login_required def rolled_over_loans(request): """ View for rolled over loans with filtering Requirements: 11.1, 11.2, 11.3, 11.4, 11.5 """ from reports.filter_service import ReportFilterService from loans.models import LoanProduct selected_branch_id = request.session.get('selected_branch_id') # Parse filter parameters filter_params = ReportFilterService.parse_filter_params(request) # Base queryset - only rolled over loans loans_qs = Loan.objects.filter(Q(status='rolled_over') | Q(is_rolled_over=True)) # Exclude soft-deleted loans from statistics (Requirement 11.5) loans_qs = loans_qs.filter(is_deleted=False) # Apply rollover date range filter (Requirement 11.2) if filter_params.start_date and filter_params.end_date: loans_qs = ReportFilterService.apply_date_range_filter( loans_qs, filter_params.start_date, filter_params.end_date, date_field='updated_at' # Use updated_at as rollover date ) # Apply loan product filter (Requirement 11.3) if filter_params.product_id: loans_qs = ReportFilterService.apply_loan_product_filter( loans_qs, filter_params.product_id ) # Apply branch filtering if a branch is selected if selected_branch_id: loans_qs = loans_qs.filter(borrower__branch_id=selected_branch_id) # Select related to optimize queries loans_qs = loans_qs.select_related( 'borrower', 'application', 'application__loan_product' ).prefetch_related('borrower__loans') # Order by most recent rollover loans_qs = loans_qs.order_by('-updated_at') # Calculate statistics (excluding deleted loans) total_count = loans_qs.count() total_amount = loans_qs.aggregate(total=Sum('principal_amount'))['total'] or 0 total_rollover_fees = sum( loan.application.loan_product.rollover_fee_percentage / 100 * loan.principal_amount for loan in loans_qs ) if loans_qs.exists() else 0 # Pagination paginator = Paginator(loans_qs, 25) page = request.GET.get('page') try: loans = paginator.page(page) except PageNotAnInteger: loans = paginator.page(1) except EmptyPage: loans = paginator.page(paginator.num_pages) # Get loan products for filter dropdown loan_products = LoanProduct.objects.filter(is_active=True).order_by('name') # Get branches for filter dropdown try: from users.models import Branch branches = Branch.objects.filter(is_active=True).order_by('name') except: branches = [] # Handle export requests export_format = request.GET.get('export') if export_format: from reports.export_service import ReportExportService export_service = ReportExportService() # Prepare report data for export report_data = { 'loans': [ { 'loan_number': loan.loan_number, 'borrower_name': loan.borrower.get_full_name(), 'borrower_phone': loan.borrower.phone_number, 'principal_amount': loan.principal_amount, 'processing_fee': loan.application.loan_product.rollover_fee_percentage / 100 * loan.principal_amount if loan.application and loan.application.loan_product else 0, 'disbursement_date': loan.updated_at, } for loan in loans_qs ] } filters = { 'start_date': filter_params.start_date, 'end_date': filter_params.end_date, 'product_id': filter_params.product_id, } if export_format == 'excel': return export_service.export_to_excel(report_data, 'rolled_over_loans', filters) elif export_format == 'pdf': return export_service.export_to_pdf(report_data, 'rolled_over_loans', filters) context = { 'loans': loans, 'status_filter': 'rolled_over', 'title': 'Rolled Over Loans', 'total_count': total_count, 'total_amount': total_amount, 'total_rollover_fees': total_rollover_fees, 'today': timezone.now().date(), 'loan_products': loan_products, 'branches': branches, 'filter_params': filter_params, 'selected_branch_id': selected_branch_id, } return render(request, 'loans/rolled_over_loans.html', context) @login_required def edit_repayment(request, pk): """Edit repayment details""" repayment = get_object_or_404(Repayment, pk=pk) # Check permissions - only staff can edit repayments if not request.user.is_staff: messages.error(request, 'You do not have permission to edit repayments.') return redirect('loans:repayments') if request.method == 'POST': try: # Get form data amount = Decimal(request.POST.get('amount', '0')) payment_method = request.POST.get('payment_method') payment_date = request.POST.get('payment_date') mpesa_transaction_id = request.POST.get('mpesa_transaction_id', '').strip() mpesa_phone_number = request.POST.get('mpesa_phone_number', '').strip() # Validate amount if amount <= 0: messages.error(request, 'Amount must be greater than zero.') return render(request, 'loans/edit_repayment.html', { 'repayment': repayment, 'payment_methods': Repayment.PAYMENT_METHODS }) # Store old values for loan recalculation old_loan = repayment.loan old_amount = repayment.amount # Update repayment repayment.amount = amount repayment.payment_method = payment_method if payment_date: repayment.payment_date = timezone.datetime.strptime(payment_date, '%Y-%m-%dT%H:%M') repayment.mpesa_transaction_id = mpesa_transaction_id or None repayment.mpesa_phone_number = mpesa_phone_number or None # Save the repayment (this will trigger loan recalculation via signal) repayment.save() # Force loan recalculation to ensure amounts are updated old_loan.recalculate_amounts() old_loan.save() # Create audit log AuditLog.objects.create( user=request.user, action='update', model_name='Repayment', object_id=str(repayment.pk), description=f'Updated repayment {repayment.receipt_number}: Amount changed from KES {old_amount:,.2f} to KES {amount:,.2f}' ) messages.success(request, f'Repayment {repayment.receipt_number} updated successfully.') return redirect('loans:loan_detail', pk=repayment.loan.pk) except Exception as e: messages.error(request, f'Error updating repayment: {str(e)}') context = { 'repayment': repayment, 'payment_methods': Repayment.PAYMENT_METHODS } return render(request, 'loans/edit_repayment.html', context) @login_required def delete_repayment(request, pk): """Delete repayment""" # Check if user has permission to delete repayments if not request.user.has_permission('repayments', 'delete'): messages.error(request, 'You do not have permission to delete repayments.') return redirect('loans:repayments') repayment = get_object_or_404(Repayment, pk=pk) # Check permissions - only staff can delete repayments if not request.user.is_staff: messages.error(request, 'You do not have permission to delete repayments.') return redirect('loans:repayments') if request.method == 'POST': try: loan = repayment.loan receipt_number = repayment.receipt_number # Delete the repayment (this will cascade delete the receipt) repayment.delete() # Recalculate loan totals from django.db.models import Sum loan.amount_paid = loan.repayments.aggregate( total=Sum('amount') )['total'] or Decimal('0.00') # Update loan status if needed if loan.amount_paid < loan.total_amount and loan.status == 'paid': loan.status = 'active' loan.save() messages.success(request, f'Repayment {receipt_number} deleted successfully.') return redirect('loans:loan_detail', pk=loan.pk) except Exception as e: messages.error(request, f'Error deleting repayment: {str(e)}') return redirect('loans:loan_detail', pk=repayment.loan.pk) context = { 'repayment': repayment } return render(request, 'loans/delete_repayment.html', context) @login_required def refresh_loan_payments(request, pk): """Refresh and sync all payments for a specific loan""" loan = get_object_or_404(Loan, pk=pk) try: from django.db import connection, transaction from django.http import JsonResponse with transaction.atomic(): with connection.cursor() as cursor: # Strategy 1: Update repayments that have matching mpesa transactions # First, get the borrower's phone number borrower_phone = loan.borrower.phone_number cursor.execute(""" UPDATE repayments r INNER JOIN mpesa_transactions mt ON r.id = mt.repayment_id SET r.loan_id = %s WHERE (r.loan_id IS NULL OR r.loan_id = '') AND mt.phone_number = %s AND r.payment_date BETWEEN %s AND %s """, [ str(loan.id).replace('-', ''), borrower_phone, loan.disbursement_date - timedelta(days=1) if loan.disbursement_date else timezone.now() - timedelta(days=365), timezone.now() + timedelta(days=1) ]) updated_count = cursor.rowcount # Strategy 2: Match by date range for remaining orphaned repayments cursor.execute(""" UPDATE repayments r SET r.loan_id = %s WHERE (r.loan_id IS NULL OR r.loan_id = '') AND r.payment_date BETWEEN %s AND %s AND r.id NOT IN ( SELECT DISTINCT r2.id FROM ( SELECT id FROM repayments WHERE loan_id = %s ) r2 ) LIMIT 100 """, [ str(loan.id).replace('-', ''), loan.disbursement_date - timedelta(days=1) if loan.disbursement_date else timezone.now() - timedelta(days=365), timezone.now() + timedelta(days=1), str(loan.id).replace('-', '') ]) additional_count = cursor.rowcount total_synced = updated_count + additional_count # Recalculate loan totals loan.refresh_from_db() # Create audit log AuditLog.objects.create( user=request.user, action='refresh_payments', model_name='Loan', object_id=str(loan.id), description=f'Refreshed payments for loan {loan.loan_number} - synced {total_synced} payments' ) return JsonResponse({ 'success': True, 'synced_count': total_synced, 'updated_count': updated_count, 'additional_count': additional_count, 'message': f'Successfully synced {total_synced} payments for loan {loan.loan_number}.' if total_synced > 0 else f'No orphaned payments found for loan {loan.loan_number}.' }) except Exception as e: return JsonResponse({ 'success': False, 'error': str(e) }) @login_required def refresh_all_payments(request): """Refresh and sync all payments for all loans""" if not request.user.is_admin(): return JsonResponse({ 'success': False, 'error': 'You do not have permission to refresh all payments.' }) try: from django.db import connection, transaction from django.http import JsonResponse with transaction.atomic(): with connection.cursor() as cursor: # Strategy 1: Direct phone number lookup to avoid subquery issues cursor.execute(""" UPDATE repayments r INNER JOIN mpesa_transactions mt ON r.id = mt.repayment_id INNER JOIN loans l ON mt.phone_number = ( SELECT u.phone_number FROM users u WHERE u.id = l.borrower_id ) SET r.loan_id = l.id WHERE (r.loan_id IS NULL OR r.loan_id = '') AND r.payment_date BETWEEN DATE_SUB(l.disbursement_date, INTERVAL 1 DAY) AND DATE_ADD(NOW(), INTERVAL 1 DAY) LIMIT 500 """) strategy1_count = cursor.rowcount # Strategy 2: Use a more direct approach with temporary table cursor.execute(""" CREATE TEMPORARY TABLE temp_payment_matches AS SELECT r.id as repayment_id, (SELECT l.id FROM loans l INNER JOIN users u ON l.borrower_id = u.id WHERE l.is_deleted = 0 AND r.payment_date BETWEEN DATE_SUB(l.disbursement_date, INTERVAL 1 DAY) AND DATE_ADD(l.due_date, INTERVAL 30 DAY) ORDER BY ABS(DATEDIFF(r.payment_date, l.disbursement_date)) LIMIT 1) as loan_id FROM repayments r WHERE (r.loan_id IS NULL OR r.loan_id = '') LIMIT 1000 """) # Update using the temporary table cursor.execute(""" UPDATE repayments r INNER JOIN temp_payment_matches tpm ON r.id = tpm.repayment_id SET r.loan_id = tpm.loan_id WHERE tpm.loan_id IS NOT NULL AND (r.loan_id IS NULL OR r.loan_id = '') """) strategy2_count = cursor.rowcount # Clean up temporary table cursor.execute("DROP TEMPORARY TABLE IF EXISTS temp_payment_matches") total_synced = strategy1_count + strategy2_count # Create audit log AuditLog.objects.create( user=request.user, action='refresh_all_payments', model_name='System', object_id='system', description=f'Refreshed all loan payments - synced {total_synced} payments (Strategy 1: {strategy1_count}, Strategy 2: {strategy2_count})' ) return JsonResponse({ 'success': True, 'total_synced': total_synced, 'strategy1_count': strategy1_count, 'strategy2_count': strategy2_count, 'loans_updated': total_synced, 'message': f'Successfully synced {total_synced} orphaned payments across all loans.' if total_synced > 0 else 'No orphaned payments found to sync.' }) except Exception as e: return JsonResponse({ 'success': False, 'error': str(e) }) @login_required def completed_loans(request): """View for managing completed (paid) loans with filtering, pagination, and reporting""" # Get selected branch from session selected_branch_id = request.session.get('selected_branch_id') # Get all loan applications with related data where loan status is 'paid' loans_list = LoanApplication.objects.all().select_related( 'borrower', 'loan_product', 'loan' ).prefetch_related( 'loan__repayments' ).filter( loan__status='paid', loan__is_deleted=False ) # Apply branch filtering if selected_branch_id: loans_list = loans_list.filter(borrower__branch_id=selected_branch_id) elif hasattr(request.user, 'branch') and request.user.branch and not request.user.is_superuser: loans_list = loans_list.filter(borrower__branch=request.user.branch) # Superuser without branch selection sees all loans loans_list = loans_list.order_by('-loan__due_date') # Apply filters product_type = request.GET.get('product_type') search = request.GET.get('search') date_from = request.GET.get('date_from') date_to = request.GET.get('date_to') if product_type: loans_list = loans_list.filter(loan_product__product_type=product_type) if search: loans_list = loans_list.filter( Q(application_number__icontains=search) | Q(loan__loan_number__icontains=search) | Q(borrower__first_name__icontains=search) | Q(borrower__last_name__icontains=search) | Q(borrower__email__icontains=search) | Q(borrower__phone_number__icontains=search) | Q(borrower__business_name__icontains=search) ) if date_from: try: date_from = datetime.strptime(date_from, '%Y-%m-%d') loans_list = loans_list.filter(loan__disbursement_date__date__gte=date_from) except ValueError: pass if date_to: try: date_to = datetime.strptime(date_to, '%Y-%m-%d') loans_list = loans_list.filter(loan__due_date__date__lte=date_to) except ValueError: pass # Get summary statistics total_loans = loans_list.count() total_principal = loans_list.aggregate( total=Sum('loan__principal_amount') )['total'] or 0 total_interest = loans_list.aggregate( total=Sum('loan__interest_amount') )['total'] or 0 total_collected = loans_list.aggregate( total=Sum('loan__total_amount') )['total'] or 0 # Advanced statistics advanced_stats = { 'avg_loan_amount': loans_list.aggregate( avg=Avg('loan__principal_amount') )['avg'] or 0, 'loans_by_product': loans_list.values( 'loan_product__product_type' ).annotate( count=Count('id'), total_amount=Sum('loan__principal_amount') ).order_by('-count'), 'client_summary': loans_list.values( 'borrower__first_name', 'borrower__last_name' ).annotate( total_loans=Count('id'), total_amount=Sum('loan__principal_amount') ).order_by('-total_amount')[:5], } # Pagination paginator = Paginator(loans_list, 25) # Show 25 loans per page page = request.GET.get('page') try: loans = paginator.page(page) except PageNotAnInteger: loans = paginator.page(1) except EmptyPage: loans = paginator.page(paginator.num_pages) # Generate report if requested if request.GET.get('generate_report') == 'true': from reports.views import generate_loans_report return generate_loans_report(request, loans_list, 'Completed Loans Report') context = { 'loans': loans, 'total_loans': total_loans, 'total_principal': total_principal, 'total_interest': total_interest, 'total_collected': total_collected, 'advanced_stats': advanced_stats, 'search_query': search, 'selected_product_type': product_type, 'date_from': date_from.strftime('%Y-%m-%d') if isinstance(date_from, datetime) else '', 'date_to': date_to.strftime('%Y-%m-%d') if isinstance(date_to, datetime) else '', 'today': timezone.now().date(), } return render(request, 'loans/completed_loans.html', context) @login_required def loan_receipt(request, pk): """Generate a loan receipt for a specific loan""" loan = get_object_or_404(Loan, pk=pk) # Get all repayments for this loan repayments = Repayment.objects.filter(loan=loan).order_by('payment_date') # Calculate totals using stored values total_paid = repayments.aggregate(total=Sum('amount'))['total'] or Decimal('0.00') # Use stored values for accurate calculations current_interest = loan.interest_amount current_processing_fee = loan.processing_fee current_total = loan.total_amount remaining_balance = current_total - total_paid context = { 'loan': loan, 'repayments': repayments, 'total_paid': total_paid, 'remaining_balance': remaining_balance, 'current_total': current_total, 'generated_at': timezone.now(), } return render(request, 'loans/loan_receipt.html', context) @login_required @portfolio_access_required def loan_amortization(request, pk): """Generate a beautiful loan amortization schedule""" loan = get_object_or_404(Loan, pk=pk) # Check if user has access to this loan if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser: if loan.borrower.portfolio_manager != request.user: messages.error(request, 'You do not have permission to view this loan.') return redirect('loans:loans') elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser: if request.user.branch and loan.borrower.branch != request.user.branch: messages.error(request, 'You do not have permission to view this loan.') return redirect('loans:loans') # Get existing repayments existing_repayments = Repayment.objects.filter(loan=loan).order_by('payment_date') # Calculate amortization schedule amortization_schedule = loan.get_amortization_schedule() # Calculate summary statistics using stored values total_principal = loan.principal_amount or Decimal('0.00') total_interest = loan.interest_amount total_processing_fee = loan.processing_fee total_amount = loan.total_amount # Calculate paid amounts total_paid = existing_repayments.aggregate(total=Sum('amount'))['total'] or Decimal('0.00') remaining_balance = total_amount - total_paid # Calculate payment statistics total_payments_made = existing_repayments.count() total_scheduled_payments = len(amortization_schedule) payments_remaining = total_scheduled_payments - total_payments_made context = { 'loan': loan, 'amortization_schedule': amortization_schedule, 'existing_repayments': existing_repayments, 'total_principal': total_principal, 'total_interest': total_interest, 'total_processing_fee': total_processing_fee, 'total_amount': total_amount, 'total_paid': total_paid, 'remaining_balance': remaining_balance, 'total_payments_made': total_payments_made, 'total_scheduled_payments': total_scheduled_payments, 'payments_remaining': payments_remaining, 'generated_at': timezone.now(), } return render(request, 'loans/loan_amortization.html', context) # ======================= # INTEREST SCHEDULE VIEW # ======================= from django.shortcuts import render, get_object_or_404, redirect from django.contrib.auth.decorators import login_required from decimal import Decimal @login_required def interest_schedule(request, pk): """Display interest schedule for a loan""" from loans.models import Loan loan = get_object_or_404(Loan, pk=pk) # Get payment schedule try: schedule = loan.get_payment_schedule() except: schedule = [] # Calculate interest breakdown using stored values principal = loan.principal_amount or Decimal('0.00') interest_amount = loan.interest_amount processing_fee = loan.processing_fee total_amount = loan.total_amount # Calculate interest paid vs outstanding amount_paid = loan.amount_paid or Decimal('0.00') if total_amount > 0: interest_portion = interest_amount / total_amount interest_paid = min(interest_amount, amount_paid * interest_portion) interest_outstanding = interest_amount - interest_paid else: interest_paid = Decimal('0.00') interest_outstanding = Decimal('0.00') context = { 'loan': loan, 'schedule': schedule, 'interest_breakdown': { 'total_interest': interest_amount, 'interest_paid': interest_paid, 'interest_outstanding': interest_outstanding, 'interest_rate': loan.get_display_interest_rate(), } } return render(request, 'loans/interest_schedule.html', context) @login_required @admin_only_ajax def add_penalty(request, loan_id): """ Add penalty to a loan (admin only) AJAX endpoint for penalty addition Requirements: 4.1, 4.2, 4.3, 4.4, 4.5, 4.6, 4.8, 4.9, 4.11 Security Requirements: 1, 2, 3 """ from .validators import PenaltyValidator from .sanitizers import InputSanitizer from django.core.exceptions import ValidationError as DjangoValidationError from .models import PenaltyCharge # Only accept POST requests if request.method != 'POST': return JsonResponse({ 'status': 'error', 'message': 'Only POST requests are allowed' }, status=405) # Get the loan try: loan = Loan.objects.get(pk=loan_id) except Loan.DoesNotExist: return JsonResponse({ 'status': 'error', 'message': 'Loan not found' }, status=404) try: # Parse JSON data if content type is JSON, otherwise use POST data if request.content_type == 'application/json': import json data = json.loads(request.body) else: data = request.POST.dict() # Sanitize input data first # Requirements: 10.9, 10.10 sanitized_data = InputSanitizer.sanitize_form_data(data, { 'amount': 'string', 'penalty_date': 'string', 'reason': 'text' }) # Validate penalty form data # Requirements: 4.4, 4.5, 4.11 validated_data = PenaltyValidator.validate_penalty_form(sanitized_data) # Create penalty charge # Requirements: 4.3, 4.6 penalty = PenaltyCharge.objects.create( loan=loan, amount=validated_data['amount'], penalty_date=validated_data['penalty_date'], reason=validated_data['reason'], penalty_rate=Decimal('0.00'), # Manual penalty, no rate days_overdue=0, # Manual penalty outstanding_amount=loan.outstanding_amount, is_automatic=False, applied_by=request.user ) # Update loan's outstanding amount # Requirements: 4.8, 4.9 loan.recalculate_amounts() loan.save() # Create audit log AuditLog.objects.create( user=request.user, action='create', model_name='PenaltyCharge', object_id=str(penalty.pk), description=f'Added penalty of KES {penalty.amount:,.2f} to loan {loan.loan_number}' ) return JsonResponse({ 'status': 'success', 'message': f'Penalty of KES {penalty.amount:,.2f} added successfully', 'penalty': { 'id': str(penalty.pk), 'amount': str(penalty.amount), 'penalty_date': penalty.penalty_date.strftime('%Y-%m-%d'), 'reason': penalty.reason, 'created_at': penalty.created_at.strftime('%Y-%m-%d %H:%M:%S') }, 'loan': { 'outstanding_amount': str(loan.outstanding_amount), 'total_penalties': str(loan.total_penalties) } }) except DjangoValidationError as e: # Validation errors from our validator # Requirement: 4.11 return JsonResponse({ 'status': 'error', 'message': str(e) }, status=400) except Exception as e: # Log unexpected errors import logging logger = logging.getLogger(__name__) logger.error(f'Error adding penalty to loan {loan_id}: {str(e)}', exc_info=True) return JsonResponse({ 'status': 'error', 'message': 'An unexpected error occurred while adding the penalty' }, status=500)