import io import csv from datetime import datetime, timedelta from decimal import Decimal import matplotlib.pyplot as plt import seaborn as sns from reportlab.lib import colors from reportlab.lib.pagesizes import letter, landscape from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, Image from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle from reportlab.lib.units import inch from django.db.models import Sum, Count, Avg, Q, F from django.utils import timezone from loans.models import Loan, LoanApplication, Repayment from users.models import CustomUser from reports.models import LoanScoring class ReportGenerator: @staticmethod def generate_client_report_pdf(client): """Generate a PDF report for a client""" buffer = io.BytesIO() doc = SimpleDocTemplate(buffer, pagesize=letter) styles = getSampleStyleSheet() elements = [] # Title elements.append(Paragraph(f"Client Report - {client.get_full_name()}", styles['Title'])) elements.append(Spacer(1, 12)) # Personal Information elements.append(Paragraph("Personal Information", styles['Heading1'])) data = [ ["Full Name", client.get_full_name()], ["ID Number", client.id_number], ["Phone", str(client.phone_number)], ["Email", client.email or "N/A"], ["Status", client.get_status_display()], ["Member Since", client.created_at.strftime("%Y-%m-%d")] ] t = Table(data) t.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (0, -1), colors.lightgrey), ('TEXTCOLOR', (0, 0), (-1, -1), colors.black), ('ALIGN', (0, 0), (-1, -1), 'LEFT'), ('FONTNAME', (0, 0), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 0), (-1, -1), 10), ('BOTTOMPADDING', (0, 0), (-1, -1), 12), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(t) elements.append(Spacer(1, 12)) # Loan Summary elements.append(Paragraph("Loan Summary", styles['Heading1'])) loans = Loan.objects.filter(client=client) total_borrowed = loans.aggregate(total=Sum('principal_amount'))['total'] or 0 total_repaid = loans.aggregate(total=Sum('amount_paid'))['total'] or 0 data = [ ["Active Loans", str(loans.filter(status='active').count())], ["Total Borrowed", f"KES {total_borrowed:,.2f}"], ["Total Repaid", f"KES {total_repaid:,.2f}"], ["Outstanding", f"KES {(total_borrowed - total_repaid):,.2f}"] ] t = Table(data) t.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (0, -1), colors.lightgrey), ('TEXTCOLOR', (0, 0), (-1, -1), colors.black), ('ALIGN', (0, 0), (-1, -1), 'LEFT'), ('FONTNAME', (0, 0), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 0), (-1, -1), 10), ('BOTTOMPADDING', (0, 0), (-1, -1), 12), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(t) elements.append(Spacer(1, 12)) # Loan History Chart elements.append(Paragraph("Loan History", styles['Heading1'])) plt.figure(figsize=(8, 4)) loan_data = loans.order_by('created_at') plt.plot( [loan.created_at for loan in loan_data], [float(loan.principal_amount) for loan in loan_data], marker='o' ) plt.title("Loan Amount History") plt.xlabel("Date") plt.ylabel("Amount (KES)") plt.xticks(rotation=45) plt.tight_layout() img_buffer = io.BytesIO() plt.savefig(img_buffer, format='png') img_buffer.seek(0) img = Image(img_buffer) img.drawHeight = 4*inch img.drawWidth = 6*inch elements.append(img) plt.close() # Build and return PDF doc.build(elements) buffer.seek(0) return buffer @staticmethod def generate_client_csv(client): """Generate a CSV report for a client""" output = io.StringIO() writer = csv.writer(output) # Write header writer.writerow(['Client Report']) writer.writerow([]) # Personal Information writer.writerow(['Personal Information']) writer.writerow(['Full Name', client.get_full_name()]) writer.writerow(['ID Number', client.id_number]) writer.writerow(['Phone', client.phone_number]) writer.writerow(['Email', client.email or "N/A"]) writer.writerow(['Status', client.get_status_display()]) writer.writerow(['Member Since', client.created_at.strftime("%Y-%m-%d")]) writer.writerow([]) # Loan Summary writer.writerow(['Loan Summary']) loans = Loan.objects.filter(client=client) total_borrowed = loans.aggregate(total=Sum('principal_amount'))['total'] or 0 total_repaid = loans.aggregate(total=Sum('amount_paid'))['total'] or 0 writer.writerow(['Active Loans', loans.filter(status='active').count()]) writer.writerow(['Total Borrowed', f"KES {total_borrowed:,.2f}"]) writer.writerow(['Total Repaid', f"KES {total_repaid:,.2f}"]) writer.writerow(['Outstanding', f"KES {(total_borrowed - total_repaid):,.2f}"]) writer.writerow([]) # Loan History writer.writerow(['Loan History']) writer.writerow(['Date', 'Amount', 'Status', 'Due Date', 'Amount Paid']) for loan in loans.order_by('-created_at'): writer.writerow([ loan.created_at.strftime("%Y-%m-%d"), f"KES {loan.principal_amount:,.2f}", loan.get_status_display(), loan.due_date.strftime("%Y-%m-%d"), f"KES {loan.amount_paid:,.2f}" ]) return output.getvalue() @staticmethod def generate_loan_report_pdf(loan): """Generate a PDF report for a loan""" buffer = io.BytesIO() doc = SimpleDocTemplate(buffer, pagesize=letter) styles = getSampleStyleSheet() elements = [] # Title elements.append(Paragraph(f"Loan Report - #{loan.loan_number}", styles['Title'])) elements.append(Spacer(1, 12)) # Loan Details elements.append(Paragraph("Loan Details", styles['Heading1'])) data = [ ["Loan Number", loan.loan_number], ["Client", loan.client.get_full_name()], ["Principal Amount", f"KES {loan.principal_amount:,.2f}"], ["Interest Amount", f"KES {loan.interest_amount:,.2f}"], ["Total Amount", f"KES {(loan.principal_amount + loan.interest_amount):,.2f}"], ["Status", loan.get_status_display()], ["Disbursement Date", loan.disbursement_date.strftime("%Y-%m-%d")], ["Due Date", loan.due_date.strftime("%Y-%m-%d")] ] t = Table(data) t.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (0, -1), colors.lightgrey), ('TEXTCOLOR', (0, 0), (-1, -1), colors.black), ('ALIGN', (0, 0), (-1, -1), 'LEFT'), ('FONTNAME', (0, 0), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 0), (-1, -1), 10), ('BOTTOMPADDING', (0, 0), (-1, -1), 12), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(t) elements.append(Spacer(1, 12)) # Repayment History elements.append(Paragraph("Repayment History", styles['Heading1'])) repayments = loan.repayment_set.all().order_by('payment_date') if repayments: data = [["Date", "Amount", "Method", "Reference"]] for repayment in repayments: data.append([ repayment.payment_date.strftime("%Y-%m-%d"), f"KES {repayment.amount:,.2f}", repayment.get_payment_method_display(), repayment.reference_number ]) t = Table(data) t.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.grey), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTNAME', (0, 0), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 0), (-1, -1), 10), ('BOTTOMPADDING', (0, 0), (-1, -1), 12), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(t) else: elements.append(Paragraph("No repayments recorded", styles['Normal'])) # Build and return PDF doc.build(elements) buffer.seek(0) return buffer @staticmethod def generate_loan_csv(loan): """Generate a CSV report for a loan""" output = io.StringIO() writer = csv.writer(output) # Write header writer.writerow(['Loan Report']) writer.writerow([]) # Loan Details writer.writerow(['Loan Details']) writer.writerow(['Loan Number', loan.loan_number]) writer.writerow(['Client', loan.client.get_full_name()]) writer.writerow(['Principal Amount', f"KES {loan.principal_amount:,.2f}"]) writer.writerow(['Interest Amount', f"KES {loan.interest_amount:,.2f}"]) writer.writerow(['Total Amount', f"KES {(loan.principal_amount + loan.interest_amount):,.2f}"]) writer.writerow(['Status', loan.get_status_display()]) writer.writerow(['Disbursement Date', loan.disbursement_date.strftime("%Y-%m-%d")]) writer.writerow(['Due Date', loan.due_date.strftime("%Y-%m-%d")]) writer.writerow([]) # Repayment History writer.writerow(['Repayment History']) writer.writerow(['Date', 'Amount', 'Method', 'Reference']) for repayment in loan.repayment_set.all().order_by('payment_date'): writer.writerow([ repayment.payment_date.strftime("%Y-%m-%d"), f"KES {repayment.amount:,.2f}", repayment.get_payment_method_display(), repayment.reference_number ]) return output.getvalue() @staticmethod def generate_system_report_pdf(): """Generate a PDF report with system-wide statistics""" buffer = io.BytesIO() doc = SimpleDocTemplate(buffer, pagesize=landscape(letter)) styles = getSampleStyleSheet() elements = [] # Title elements.append(Paragraph("System Report", styles['Title'])) elements.append(Spacer(1, 12)) # System Overview elements.append(Paragraph("System Overview", styles['Heading1'])) total_clients = CustomUser.objects.filter(is_staff=False).count() active_loans = Loan.objects.filter(status='active').count() total_disbursed = Loan.objects.aggregate(total=Sum('principal_amount'))['total'] or 0 total_collected = Loan.objects.aggregate(total=Sum('amount_paid'))['total'] or 0 data = [ ["Total Clients", str(total_clients)], ["Active Loans", str(active_loans)], ["Total Disbursed", f"KES {total_disbursed:,.2f}"], ["Total Collected", f"KES {total_collected:,.2f}"] ] t = Table(data) t.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (0, -1), colors.lightgrey), ('TEXTCOLOR', (0, 0), (-1, -1), colors.black), ('ALIGN', (0, 0), (-1, -1), 'LEFT'), ('FONTNAME', (0, 0), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 0), (-1, -1), 10), ('BOTTOMPADDING', (0, 0), (-1, -1), 12), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(t) elements.append(Spacer(1, 12)) # Loan Distribution Chart elements.append(Paragraph("Loan Status Distribution", styles['Heading1'])) plt.figure(figsize=(8, 4)) status_data = dict(Loan.objects.values_list('status').annotate(count=Count('id'))) plt.pie( status_data.values(), labels=[Loan.STATUS_CHOICES[status] for status in status_data.keys()], autopct='%1.1f%%' ) plt.title("Loan Status Distribution") plt.axis('equal') img_buffer = io.BytesIO() plt.savefig(img_buffer, format='png') img_buffer.seek(0) img = Image(img_buffer) img.drawHeight = 4*inch img.drawWidth = 6*inch elements.append(img) plt.close() # Monthly Performance Chart elements.append(Paragraph("Monthly Performance", styles['Heading1'])) plt.figure(figsize=(10, 4)) # Get last 12 months of data end_date = timezone.now() start_date = end_date - timedelta(days=365) monthly_data = ( Loan.objects .filter(created_at__range=(start_date, end_date)) .extra(select={'month': "DATE_FORMAT(created_at, '%%Y-%%m')"}) .values('month') .annotate( count=Count('id'), amount=Sum('principal_amount') ) .order_by('month') ) months = [data['month'] for data in monthly_data] amounts = [float(data['amount']) for data in monthly_data] plt.bar(months, amounts) plt.title("Monthly Loan Disbursement") plt.xlabel("Month") plt.ylabel("Amount (KES)") plt.xticks(rotation=45) plt.tight_layout() img_buffer = io.BytesIO() plt.savefig(img_buffer, format='png') img_buffer.seek(0) img = Image(img_buffer) img.drawHeight = 4*inch img.drawWidth = 8*inch elements.append(img) plt.close() # Build and return PDF doc.build(elements) buffer.seek(0) return buffer @staticmethod def generate_system_csv(): """Generate a CSV report with system-wide statistics""" output = io.StringIO() writer = csv.writer(output) # Write header writer.writerow(['System Report']) writer.writerow([]) # System Overview writer.writerow(['System Overview']) total_clients = CustomUser.objects.filter(is_staff=False).count() active_loans = Loan.objects.filter(status='active').count() total_disbursed = Loan.objects.aggregate(total=Sum('principal_amount'))['total'] or 0 total_collected = Loan.objects.aggregate(total=Sum('amount_paid'))['total'] or 0 writer.writerow(['Total Clients', total_clients]) writer.writerow(['Active Loans', active_loans]) writer.writerow(['Total Disbursed', f"KES {total_disbursed:,.2f}"]) writer.writerow(['Total Collected', f"KES {total_collected:,.2f}"]) writer.writerow([]) # Loan Status Distribution writer.writerow(['Loan Status Distribution']) writer.writerow(['Status', 'Count', 'Percentage']) status_data = dict(Loan.objects.values_list('status').annotate(count=Count('id'))) total_loans = sum(status_data.values()) for status, count in status_data.items(): writer.writerow([ dict(Loan.STATUS_CHOICES)[status], count, f"{(count/total_loans)*100:.1f}%" ]) writer.writerow([]) # Monthly Performance writer.writerow(['Monthly Performance']) writer.writerow(['Month', 'Number of Loans', 'Total Amount']) end_date = timezone.now() start_date = end_date - timedelta(days=365) monthly_data = ( Loan.objects .filter(created_at__range=(start_date, end_date)) .extra(select={'month': "DATE_FORMAT(created_at, '%%Y-%%m')"}) .values('month') .annotate( count=Count('id'), amount=Sum('principal_amount') ) .order_by('month') ) for data in monthly_data: writer.writerow([ data['month'], data['count'], f"KES {data['amount']:,.2f}" ]) return output.getvalue() @staticmethod def generate_credit_score_report_pdf(client): """Generate a PDF report for a client's credit score""" buffer = io.BytesIO() doc = SimpleDocTemplate(buffer, pagesize=letter) styles = getSampleStyleSheet() elements = [] # Title elements.append(Paragraph(f"Credit Score Report - {client.get_full_name()}", styles['Title'])) elements.append(Spacer(1, 12)) # Current Score current_score = LoanScoring.objects.filter(client=client).order_by('-created_at').first() if current_score: elements.append(Paragraph("Current Credit Score", styles['Heading1'])) data = [ ["Total Score", f"{current_score.total_score}/100"], ["Payment History", f"{current_score.payment_history_score}/30"], ["Credit Utilization", f"{current_score.credit_utilization_score}/25"], ["Credit Age", f"{current_score.credit_age_score}/15"], ["Recent Inquiries", f"{current_score.recent_inquiries_score}/10"], ["Credit Mix", f"{current_score.credit_mix_score}/20"] ] t = Table(data) t.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (0, -1), colors.lightgrey), ('TEXTCOLOR', (0, 0), (-1, -1), colors.black), ('ALIGN', (0, 0), (-1, -1), 'LEFT'), ('FONTNAME', (0, 0), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 0), (-1, -1), 10), ('BOTTOMPADDING', (0, 0), (-1, -1), 12), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(t) elements.append(Spacer(1, 12)) # Score Components Pie Chart elements.append(Paragraph("Score Components", styles['Heading1'])) plt.figure(figsize=(8, 4)) components = [ ('Payment History', current_score.payment_history_score), ('Credit Utilization', current_score.credit_utilization_score), ('Credit Age', current_score.credit_age_score), ('Recent Inquiries', current_score.recent_inquiries_score), ('Credit Mix', current_score.credit_mix_score) ] plt.pie( [score for _, score in components], labels=[name for name, _ in components], autopct='%1.1f%%' ) plt.title("Credit Score Components") plt.axis('equal') img_buffer = io.BytesIO() plt.savefig(img_buffer, format='png') img_buffer.seek(0) img = Image(img_buffer) img.drawHeight = 4*inch img.drawWidth = 6*inch elements.append(img) plt.close() # Score History Chart elements.append(Paragraph("Score History", styles['Heading1'])) plt.figure(figsize=(8, 4)) scores = LoanScoring.objects.filter(client=client).order_by('created_at') plt.plot( [score.created_at for score in scores], [score.total_score for score in scores], marker='o' ) plt.title("Credit Score History") plt.xlabel("Date") plt.ylabel("Score") plt.ylim(0, 100) plt.xticks(rotation=45) plt.tight_layout() img_buffer = io.BytesIO() plt.savefig(img_buffer, format='png') img_buffer.seek(0) img = Image(img_buffer) img.drawHeight = 4*inch img.drawWidth = 6*inch elements.append(img) plt.close() else: elements.append(Paragraph("No credit score data available", styles['Normal'])) # Build and return PDF doc.build(elements) buffer.seek(0) return buffer @staticmethod def generate_credit_score_csv(client): """Generate a CSV report for a client's credit score""" output = io.StringIO() writer = csv.writer(output) # Write header writer.writerow(['Credit Score Report']) writer.writerow(['Client', client.get_full_name()]) writer.writerow([]) # Current Score current_score = LoanScoring.objects.filter(client=client).order_by('-created_at').first() if current_score: writer.writerow(['Current Credit Score']) writer.writerow(['Total Score', f"{current_score.total_score}/100"]) writer.writerow(['Payment History', f"{current_score.payment_history_score}/30"]) writer.writerow(['Credit Utilization', f"{current_score.credit_utilization_score}/25"]) writer.writerow(['Credit Age', f"{current_score.credit_age_score}/15"]) writer.writerow(['Recent Inquiries', f"{current_score.recent_inquiries_score}/10"]) writer.writerow(['Credit Mix', f"{current_score.credit_mix_score}/20"]) writer.writerow([]) # Score History writer.writerow(['Score History']) writer.writerow(['Date', 'Total Score', 'Payment History', 'Credit Utilization', 'Credit Age', 'Recent Inquiries', 'Credit Mix']) scores = LoanScoring.objects.filter(client=client).order_by('created_at') for score in scores: writer.writerow([ score.created_at.strftime("%Y-%m-%d"), score.total_score, score.payment_history_score, score.credit_utilization_score, score.credit_age_score, score.recent_inquiries_score, score.credit_mix_score ]) else: writer.writerow(['No credit score data available']) return output.getvalue()