""" Script to add overdue and completed loans report views to reports/views.py This will be added at the end of the file """ OVERDUE_LOANS_VIEW = ''' @login_required @portfolio_access_required def overdue_loans_report(request): """Comprehensive overdue loans report with analytics""" # Check permission if not request.user.has_permission('reports', 'access'): messages.error(request, 'You do not have permission to access reports.') return redirect('dashboard') selected_branch_id = request.session.get('selected_branch_id') # Get overdue loans (status=active and past due date) from django.utils import timezone today = timezone.now().date() overdue_loans = Loan.objects.filter( status='active', due_date__lt=today ).select_related('borrower', 'application__loan_product').exclude(is_deleted=True) # Apply portfolio filtering if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser: overdue_loans = overdue_loans.filter(borrower__portfolio_manager=request.user) elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser: if request.user.branch: overdue_loans = overdue_loans.filter(borrower__branch=request.user.branch) elif selected_branch_id: overdue_loans = overdue_loans.filter(borrower__branch_id=selected_branch_id) # Calculate days overdue for each loan loans_data = [] total_overdue_amount = Decimal('0.00') total_outstanding = Decimal('0.00') for loan in overdue_loans: days_overdue = (today - loan.due_date).days outstanding = loan.total_amount - (loan.amount_paid or Decimal('0.00')) # Calculate severity if days_overdue <= 7: severity = '1-7 days' severity_class = 'warning' elif days_overdue <= 30: severity = '8-30 days' severity_class = 'danger' else: severity = '30+ days' severity_class = 'critical' loans_data.append({ 'loan': loan, 'days_overdue': days_overdue, 'outstanding': outstanding, 'severity': severity, 'severity_class': severity_class, }) total_overdue_amount += loan.total_amount total_outstanding += outstanding # Sort by days overdue (most overdue first) loans_data.sort(key=lambda x: x['days_overdue'], reverse=True) # Calculate statistics total_count = len(loans_data) avg_days_overdue = sum(l['days_overdue'] for l in loans_data) / total_count if total_count > 0 else 0 # Group by severity severity_stats = { '1-7 days': {'count': 0, 'amount': Decimal('0.00')}, '8-30 days': {'count': 0, 'amount': Decimal('0.00')}, '30+ days': {'count': 0, 'amount': Decimal('0.00')}, } for loan_data in loans_data: severity = loan_data['severity'] severity_stats[severity]['count'] += 1 severity_stats[severity]['amount'] += loan_data['outstanding'] # Group by loan officer (for admins/managers) officer_stats = {} if request.user.role in ['admin', 'manager'] or request.user.is_superuser: for loan_data in loans_data: officer = loan_data['loan'].borrower.portfolio_manager officer_name = officer.get_full_name() if officer else 'Unassigned' if officer_name not in officer_stats: officer_stats[officer_name] = {'count': 0, 'amount': Decimal('0.00')} officer_stats[officer_name]['count'] += 1 officer_stats[officer_name]['amount'] += loan_data['outstanding'] context = { 'loans_data': loans_data, 'total_count': total_count, 'total_overdue_amount': total_overdue_amount, 'total_outstanding': total_outstanding, 'avg_days_overdue': round(avg_days_overdue, 1), 'severity_stats': severity_stats, 'officer_stats': officer_stats, 'today': today, } return render(request, 'reports/overdue_loans_report.html', context) @login_required @portfolio_access_required def completed_loans_report(request): """Comprehensive completed loans report with analytics""" # Check permission if not request.user.has_permission('reports', 'access'): messages.error(request, 'You do not have permission to access reports.') return redirect('dashboard') selected_branch_id = request.session.get('selected_branch_id') # Get completed loans completed_loans = Loan.objects.filter( status='paid' ).select_related('borrower', 'application__loan_product').exclude(is_deleted=True) # Apply portfolio filtering if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser: completed_loans = completed_loans.filter(borrower__portfolio_manager=request.user) elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser: if request.user.branch: completed_loans = completed_loans.filter(borrower__branch=request.user.branch) elif selected_branch_id: completed_loans = completed_loans.filter(borrower__branch_id=selected_branch_id) # Date filtering start_date = request.GET.get('start_date') end_date = request.GET.get('end_date') if start_date: completed_loans = completed_loans.filter(disbursement_date__gte=start_date) if end_date: completed_loans = completed_loans.filter(disbursement_date__lte=end_date) # Product filtering product_id = request.GET.get('product') if product_id: completed_loans = completed_loans.filter(application__loan_product_id=product_id) # Calculate statistics total_count = completed_loans.count() total_disbursed = completed_loans.aggregate(total=Sum('principal_amount'))['total'] or Decimal('0.00') total_repaid = completed_loans.aggregate(total=Sum('_amount_paid_cache'))['total'] or Decimal('0.00') # Calculate average duration avg_duration = 0 if total_count > 0: durations = [] for loan in completed_loans: if loan.disbursement_date and loan.updated_at: duration = (loan.updated_at.date() - loan.disbursement_date).days durations.append(duration) avg_duration = sum(durations) / len(durations) if durations else 0 # Group by product product_stats = {} for loan in completed_loans: product_name = loan.application.loan_product.name if loan.application and loan.application.loan_product else 'Unknown' if product_name not in product_stats: product_stats[product_name] = { 'count': 0, 'disbursed': Decimal('0.00'), 'repaid': Decimal('0.00'), } product_stats[product_name]['count'] += 1 product_stats[product_name]['disbursed'] += loan.principal_amount product_stats[product_name]['repaid'] += loan.amount_paid or Decimal('0.00') # Group by loan officer officer_stats = {} if request.user.role in ['admin', 'manager'] or request.user.is_superuser: for loan in completed_loans: officer = loan.borrower.portfolio_manager officer_name = officer.get_full_name() if officer else 'Unassigned' if officer_name not in officer_stats: officer_stats[officer_name] = { 'count': 0, 'disbursed': Decimal('0.00'), 'repaid': Decimal('0.00'), } officer_stats[officer_name]['count'] += 1 officer_stats[officer_name]['disbursed'] += loan.principal_amount officer_stats[officer_name]['repaid'] += loan.amount_paid or Decimal('0.00') # Get loan products for filter from loans.models import LoanProduct loan_products = LoanProduct.objects.filter(is_active=True) # Pagination from django.core.paginator import Paginator paginator = Paginator(list(completed_loans), 25) page = request.GET.get('page') loans_page = paginator.get_page(page) context = { 'loans': loans_page, 'total_count': total_count, 'total_disbursed': total_disbursed, 'total_repaid': total_repaid, 'avg_duration': round(avg_duration, 1), 'product_stats': product_stats, 'officer_stats': officer_stats, 'loan_products': loan_products, 'start_date': start_date, 'end_date': end_date, 'selected_product': product_id, } return render(request, 'reports/completed_loans_report.html', context) ''' print("Views code generated. Add these to reports/views.py") print("\nAlso add to reports/urls.py:") print(" path('overdue-loans/', views.overdue_loans_report, name='overdue_loans_report'),") print(" path('completed-loans/', views.completed_loans_report, name='completed_loans_report'),")