""" Enhanced Views with Chart Data and Export Functionality Support for beautiful dashboards with interactive charts and professional exports """ from django.shortcuts import render, get_object_or_404 from django.http import HttpResponse, JsonResponse from django.contrib.auth.decorators import login_required from django.db.models import Sum, Count, Avg, Q, F from django.utils import timezone from datetime import datetime, timedelta from decimal import Decimal import json import logging from loans.models import Loan, LoanApplication, Repayment from users.models import CustomUser, Branch from utils.models import Receipt, Document, Notification from utils.enhanced_charts import EnhancedChartGenerator from utils.enhanced_pdf_generator import EnhancedPDFGenerator from utils.enhanced_excel_generator import EnhancedExcelGenerator logger = logging.getLogger(__name__) class EnhancedDashboardService: """Service class for generating enhanced dashboard data with charts""" @staticmethod def get_dashboard_data(user=None, branch_id=None): """Get comprehensive dashboard data with chart information""" try: # Base queryset with branch filtering loans_qs = Loan.objects.all() clients_qs = CustomUser.objects.filter(user_type='client') if branch_id: loans_qs = loans_qs.filter(borrower__branch_id=branch_id) clients_qs = clients_qs.filter(branch_id=branch_id) elif user and hasattr(user, 'branch') and user.branch: loans_qs = loans_qs.filter(borrower__branch=user.branch) clients_qs = clients_qs.filter(branch=user.branch) # Summary metrics summary_metrics = { 'total_loans': loans_qs.count(), 'active_loans': loans_qs.filter(status='active').count(), 'completed_loans': loans_qs.filter(status='completed').count(), 'overdue_loans': loans_qs.filter( status='active', due_date__lt=timezone.now().date() ).count(), 'total_disbursed': loans_qs.aggregate( total=Sum('principal_amount') )['total'] or 0, 'total_collected': loans_qs.aggregate( total=Sum('amount_paid') )['total'] or 0, 'total_clients': clients_qs.count(), 'active_clients': clients_qs.filter(is_active=True).count(), } # Calculate derived metrics if summary_metrics['total_disbursed'] > 0: summary_metrics['collection_rate'] = ( summary_metrics['total_collected'] / summary_metrics['total_disbursed'] * 100 ) else: summary_metrics['collection_rate'] = 0 summary_metrics['outstanding_amount'] = ( summary_metrics['total_disbursed'] - summary_metrics['total_collected'] ) # Chart data chart_data = EnhancedDashboardService._generate_chart_data(loans_qs, clients_qs) # Recent activity recent_loans = loans_qs.select_related( 'borrower', 'application__loan_product' ).order_by('-created_at')[:10] return { 'summary_metrics': summary_metrics, 'chart_data': chart_data, 'recent_loans': recent_loans, 'generated_at': timezone.now() } except Exception as e: logger.error(f"Dashboard data generation failed: {e}") return { 'error': str(e), 'summary_metrics': {}, 'chart_data': {}, 'recent_loans': [], 'generated_at': timezone.now() } @staticmethod def _generate_chart_data(loans_qs, clients_qs): """Generate chart data for visualizations""" try: # Loan status distribution loan_status = { 'Active': loans_qs.filter(status='active').count(), 'Completed': loans_qs.filter(status='completed').count(), 'Overdue': loans_qs.filter( status='active', due_date__lt=timezone.now().date() ).count(), 'Defaulted': loans_qs.filter(status='defaulted').count() } # Monthly disbursement trends (last 6 months) end_date = timezone.now().date() start_date = end_date - timedelta(days=180) monthly_data = [] current_date = start_date.replace(day=1) while current_date <= end_date: next_month = (current_date.replace(day=28) + timedelta(days=4)).replace(day=1) month_loans = loans_qs.filter( disbursement_date__gte=current_date, disbursement_date__lt=next_month ) monthly_data.append({ 'month': current_date.strftime('%b %Y'), 'count': month_loans.count(), 'amount': month_loans.aggregate( total=Sum('principal_amount') )['total'] or 0 }) current_date = next_month # Risk analysis risk_data = { 'current': loans_qs.filter( status='active', due_date__gte=timezone.now().date() ).count(), 'mild_risk': loans_qs.filter( status='active', due_date__lt=timezone.now().date(), due_date__gte=timezone.now().date() - timedelta(days=30) ).count(), 'high_risk': loans_qs.filter( status='active', due_date__lt=timezone.now().date() - timedelta(days=30) ).count() } # Client demographics client_demographics = { 'total_clients': clients_qs.count(), 'new_this_month': clients_qs.filter( date_joined__gte=timezone.now().date().replace(day=1) ).count(), 'by_branch': {} } # Branch distribution branches = Branch.objects.all() for branch in branches: client_demographics['by_branch'][branch.name] = clients_qs.filter( branch=branch ).count() return { 'loan_status': loan_status, 'monthly_trends': monthly_data, 'risk_analysis': risk_data, 'client_demographics': client_demographics } except Exception as e: logger.error(f"Chart data generation failed: {e}") return {} @staticmethod def get_client_analytics(user=None, branch_id=None): """Get client-specific analytics data""" try: clients_qs = CustomUser.objects.filter(user_type='client') if branch_id: clients_qs = clients_qs.filter(branch_id=branch_id) elif user and hasattr(user, 'branch') and user.branch: clients_qs = clients_qs.filter(branch=user.branch) # Client metrics total_clients = clients_qs.count() active_clients = clients_qs.filter(is_active=True).count() new_clients_this_month = clients_qs.filter( date_joined__gte=timezone.now().date().replace(day=1) ).count() # Portfolio metrics per client client_portfolio_data = [] for client in clients_qs.select_related('branch')[:100]: # Limit for performance client_loans = Loan.objects.filter(borrower=client) portfolio_value = client_loans.aggregate( total=Sum('principal_amount') )['total'] or 0 client_portfolio_data.append({ 'id': client.id, 'name': client.get_full_name(), 'phone': client.phone_number, 'branch': client.branch.name if client.branch else 'N/A', 'total_loans': client_loans.count(), 'active_loans': client_loans.filter(status='active').count(), 'portfolio_value': portfolio_value, 'status': 'active' if client.is_active else 'inactive', 'last_login': client.last_login }) # Chart data for client analytics chart_data = { 'demographics': { 'age_groups': { '18-25': clients_qs.filter( date_of_birth__gte=timezone.now().date() - timedelta(days=25*365) ).count(), '26-35': clients_qs.filter( date_of_birth__gte=timezone.now().date() - timedelta(days=35*365), date_of_birth__lt=timezone.now().date() - timedelta(days=26*365) ).count(), '36-45': clients_qs.filter( date_of_birth__gte=timezone.now().date() - timedelta(days=45*365), date_of_birth__lt=timezone.now().date() - timedelta(days=36*365) ).count(), '46+': clients_qs.filter( date_of_birth__lt=timezone.now().date() - timedelta(days=46*365) ).count() }, 'gender': { 'Male': clients_qs.filter(gender='male').count(), 'Female': clients_qs.filter(gender='female').count() } }, 'portfolio': { 'distribution': {}, 'performance': {} } } return { 'total_clients': total_clients, 'active_clients': active_clients, 'new_clients_this_month': new_clients_this_month, 'clients': client_portfolio_data, 'chart_data': chart_data, 'branches': Branch.objects.all() } except Exception as e: logger.error(f"Client analytics generation failed: {e}") return { 'error': str(e), 'total_clients': 0, 'active_clients': 0, 'new_clients_this_month': 0, 'clients': [], 'chart_data': {}, 'branches': [] } @staticmethod def get_loan_analytics(user=None, branch_id=None): """Get loan-specific analytics data""" try: loans_qs = Loan.objects.all() if branch_id: loans_qs = loans_qs.filter(borrower__branch_id=branch_id) elif user and hasattr(user, 'branch') and user.branch: loans_qs = loans_qs.filter(borrower__branch=user.branch) # Loan metrics total_loans = loans_qs.count() active_loans = loans_qs.filter(status='active').count() completed_loans = loans_qs.filter(status='completed').count() overdue_loans = loans_qs.filter( status='active', due_date__lt=timezone.now().date() ).count() total_disbursed = loans_qs.aggregate( total=Sum('principal_amount') )['total'] or 0 total_collected = loans_qs.aggregate( total=Sum('amount_paid') )['total'] or 0 collection_rate = (total_collected / total_disbursed * 100) if total_disbursed > 0 else 0 # Recent loans with enhanced data recent_loans = [] for loan in loans_qs.select_related( 'borrower', 'application__loan_product' ).order_by('-created_at')[:50]: # Calculate repayment progress repayment_progress = 0 if loan.principal_amount > 0: repayment_progress = (loan.amount_paid / loan.principal_amount) * 100 # Calculate days overdue days_overdue = 0 if loan.due_date and loan.due_date < timezone.now().date(): days_overdue = (timezone.now().date() - loan.due_date).days recent_loans.append({ 'id': loan.id, 'loan_number': loan.loan_number, 'borrower': loan.borrower, 'principal_amount': loan.principal_amount, 'amount_paid': loan.amount_paid, 'disbursement_date': loan.disbursement_date, 'due_date': loan.due_date, 'status': loan.status, 'repayment_progress': repayment_progress, 'days_overdue': days_overdue, 'application': loan.application }) return { 'total_loans': total_loans, 'active_loans': active_loans, 'completed_loans': completed_loans, 'overdue_loans': overdue_loans, 'defaulted_loans': loans_qs.filter(status='defaulted').count(), 'total_disbursed': total_disbursed, 'total_collected': total_collected, 'collection_rate': collection_rate, 'recent_loans': recent_loans, 'today': timezone.now().date() } except Exception as e: logger.error(f"Loan analytics generation failed: {e}") return { 'error': str(e), 'total_loans': 0, 'active_loans': 0, 'completed_loans': 0, 'overdue_loans': 0, 'defaulted_loans': 0, 'total_disbursed': 0, 'total_collected': 0, 'collection_rate': 0, 'recent_loans': [], 'today': timezone.now().date() } @login_required def enhanced_dashboard(request): """Enhanced dashboard with interactive charts and analytics""" try: # Get selected branch from session selected_branch_id = request.session.get('selected_branch_id') # Get dashboard data dashboard_data = EnhancedDashboardService.get_dashboard_data( user=request.user, branch_id=selected_branch_id ) # Handle export requests export_format = request.GET.get('format') if export_format: return handle_dashboard_export(dashboard_data, export_format) return render(request, 'reports/dashboard.html', { 'dashboard_data': dashboard_data, 'page_title': 'Enhanced Dashboard' }) except Exception as e: logger.error(f"Enhanced dashboard view failed: {e}") return render(request, 'reports/dashboard.html', { 'dashboard_data': {'error': str(e)}, 'page_title': 'Dashboard - Error' }) @login_required def enhanced_client_list(request): """Enhanced client list with analytics and charts""" try: # Get selected branch from session selected_branch_id = request.session.get('selected_branch_id') # Get client analytics data client_data = EnhancedDashboardService.get_client_analytics( user=request.user, branch_id=selected_branch_id ) # Handle export requests export_format = request.GET.get('format') if export_format: return handle_client_export(client_data, export_format) return render(request, 'users/enhanced_client_list.html', client_data) except Exception as e: logger.error(f"Enhanced client list view failed: {e}") return render(request, 'users/enhanced_client_list.html', { 'error': str(e), 'total_clients': 0, 'active_clients': 0, 'new_clients_this_month': 0, 'clients': [], 'chart_data': {}, 'branches': [] }) @login_required def enhanced_loans_dashboard(request): """Enhanced loans dashboard with comprehensive analytics""" try: # Get selected branch from session selected_branch_id = request.session.get('selected_branch_id') # Get loan analytics data loan_data = EnhancedDashboardService.get_loan_analytics( user=request.user, branch_id=selected_branch_id ) # Handle export requests export_format = request.GET.get('format') if export_format: return handle_loan_export(loan_data, export_format) return render(request, 'loans/enhanced_loans_dashboard.html', loan_data) except Exception as e: logger.error(f"Enhanced loans dashboard view failed: {e}") return render(request, 'loans/enhanced_loans_dashboard.html', { 'error': str(e), 'total_loans': 0, 'active_loans': 0, 'completed_loans': 0, 'overdue_loans': 0, 'recent_loans': [], 'today': timezone.now().date() }) def handle_dashboard_export(data, format_type): """Handle dashboard export in various formats""" try: if format_type == 'pdf': pdf_generator = EnhancedPDFGenerator() buffer = pdf_generator.generate_dashboard_report(data) if buffer: response = HttpResponse(buffer.getvalue(), content_type='application/pdf') response['Content-Disposition'] = f'attachment; filename="dashboard_report_{datetime.now().strftime("%Y%m%d_%H%M%S")}.pdf"' return response elif format_type == 'excel': excel_generator = EnhancedExcelGenerator() buffer = excel_generator.generate_dashboard_excel(data) if buffer: response = HttpResponse( buffer.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response['Content-Disposition'] = f'attachment; filename="dashboard_report_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx"' return response elif format_type == 'csv': # Generate CSV data import csv import io output = io.StringIO() writer = csv.writer(output) # Write dashboard summary writer.writerow(['Dashboard Report']) writer.writerow(['Generated:', datetime.now().strftime('%Y-%m-%d %H:%M:%S')]) writer.writerow([]) # Write summary metrics if 'summary_metrics' in data: writer.writerow(['Summary Metrics']) writer.writerow(['Metric', 'Value']) for key, value in data['summary_metrics'].items(): writer.writerow([key.replace('_', ' ').title(), value]) response = HttpResponse(output.getvalue(), content_type='text/csv') response['Content-Disposition'] = f'attachment; filename="dashboard_report_{datetime.now().strftime("%Y%m%d_%H%M%S")}.csv"' return response # If format not supported, return JSON return JsonResponse({'error': f'Export format {format_type} not supported'}) except Exception as e: logger.error(f"Dashboard export failed: {e}") return JsonResponse({'error': str(e)}) def handle_client_export(data, format_type): """Handle client data export in various formats""" try: if format_type == 'pdf': pdf_generator = EnhancedPDFGenerator() # Create client report data structure report_data = { 'summary': { 'total_clients': data.get('total_clients', 0), 'active_clients': data.get('active_clients', 0), 'new_clients_this_month': data.get('new_clients_this_month', 0) }, 'clients': { 'details': [[ client['name'], client['phone'], client['branch'], client['total_loans'], f"KES {client['portfolio_value']:,.2f}", client['status'] ] for client in data.get('clients', [])] } } buffer = pdf_generator.generate_dashboard_report(report_data) if buffer: response = HttpResponse(buffer.getvalue(), content_type='application/pdf') response['Content-Disposition'] = f'attachment; filename="clients_report_{datetime.now().strftime("%Y%m%d_%H%M%S")}.pdf"' return response elif format_type == 'excel': excel_generator = EnhancedExcelGenerator() # Create Excel-compatible data structure excel_data = { 'summary': data.get('summary_metrics', {}), 'clients': { 'details': [[ client['name'], client['phone'], client['branch'], client['total_loans'], client['portfolio_value'], client['status'] ] for client in data.get('clients', [])] } } buffer = excel_generator.generate_dashboard_excel(excel_data) if buffer: response = HttpResponse( buffer.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response['Content-Disposition'] = f'attachment; filename="clients_report_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx"' return response elif format_type == 'csv': import csv import io output = io.StringIO() writer = csv.writer(output) # Write client report writer.writerow(['Client Report']) writer.writerow(['Generated:', datetime.now().strftime('%Y-%m-%d %H:%M:%S')]) writer.writerow([]) # Write client data writer.writerow(['Name', 'Phone', 'Branch', 'Total Loans', 'Portfolio Value', 'Status']) for client in data.get('clients', []): writer.writerow([ client['name'], client['phone'], client['branch'], client['total_loans'], f"KES {client['portfolio_value']:,.2f}", client['status'] ]) response = HttpResponse(output.getvalue(), content_type='text/csv') response['Content-Disposition'] = f'attachment; filename="clients_report_{datetime.now().strftime("%Y%m%d_%H%M%S")}.csv"' return response return JsonResponse({'error': f'Export format {format_type} not supported'}) except Exception as e: logger.error(f"Client export failed: {e}") return JsonResponse({'error': str(e)}) def handle_loan_export(data, format_type): """Handle loan data export in various formats""" try: if format_type == 'pdf': pdf_generator = EnhancedPDFGenerator() # Create loan report data structure report_data = { 'summary': { 'total_loans': data.get('total_loans', 0), 'active_loans': data.get('active_loans', 0), 'completed_loans': data.get('completed_loans', 0), 'overdue_loans': data.get('overdue_loans', 0), 'total_disbursed': data.get('total_disbursed', 0), 'collection_rate': data.get('collection_rate', 0) }, 'loans': { 'details': [[ loan['loan_number'], loan['borrower'].get_full_name(), f"KES {loan['principal_amount']:,.2f}", loan['disbursement_date'].strftime('%Y-%m-%d') if loan['disbursement_date'] else 'N/A', loan['due_date'].strftime('%Y-%m-%d') if loan['due_date'] else 'N/A', loan['status'].title() ] for loan in data.get('recent_loans', [])] } } buffer = pdf_generator.generate_dashboard_report(report_data) if buffer: response = HttpResponse(buffer.getvalue(), content_type='application/pdf') response['Content-Disposition'] = f'attachment; filename="loans_report_{datetime.now().strftime("%Y%m%d_%H%M%S")}.pdf"' return response # Similar implementations for Excel and CSV... return JsonResponse({'error': f'Export format {format_type} not supported'}) except Exception as e: logger.error(f"Loan export failed: {e}") return JsonResponse({'error': str(e)})