from django.http import HttpResponse from django.utils import timezone from io import BytesIO from openpyxl import Workbook from openpyxl.styles import Font, Alignment, PatternFill, Border, Side, NamedStyle from openpyxl.utils import get_column_letter from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule from openpyxl.chart import BarChart, PieChart, LineChart, Reference from openpyxl.chart.label import DataLabelList from openpyxl.utils.dataframe import dataframe_to_rows from openpyxl.worksheet.table import Table, TableStyleInfo from openpyxl.worksheet.datavalidation import DataValidation 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 import pandas as pd from datetime import datetime, date from decimal import Decimal from typing import Dict, List, Any, Optional, Union import logging logger = logging.getLogger(__name__) class EnhancedExcelExportService: """ Enhanced Excel export service with multi-worksheet support, conditional formatting, pivot tables, charts, and advanced styling capabilities """ def __init__(self): """Initialize the Excel export service""" self.workbook = None self.styles = self._create_styles() # Color scheme for consistent branding self.colors = { 'primary': '2C3E50', 'secondary': '3498DB', 'success': '27AE60', 'warning': 'F39C12', 'danger': 'E74C3C', 'light_gray': 'F8F9FC', 'dark_gray': '5A5C69' } def _create_styles(self) -> Dict[str, NamedStyle]: """Create named styles for consistent formatting""" styles = {} # Header style header_style = NamedStyle(name="header_style") header_style.font = Font(bold=True, color="FFFFFF", size=12) header_style.fill = PatternFill(start_color="2C3E50", end_color="2C3E50", fill_type="solid") header_style.alignment = Alignment(horizontal='center', vertical='center') header_style.border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) styles['header'] = header_style # Data style data_style = NamedStyle(name="data_style") data_style.font = Font(size=10) data_style.alignment = Alignment(horizontal='left', vertical='center') data_style.border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) styles['data'] = data_style # Currency style currency_style = NamedStyle(name="currency_style") currency_style.font = Font(size=10) currency_style.alignment = Alignment(horizontal='right', vertical='center') currency_style.number_format = '"KES "#,##0.00' currency_style.border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) styles['currency'] = currency_style # Date style date_style = NamedStyle(name="date_style") date_style.font = Font(size=10) date_style.alignment = Alignment(horizontal='center', vertical='center') date_style.number_format = 'YYYY-MM-DD' date_style.border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) styles['date'] = date_style # Title style title_style = NamedStyle(name="title_style") title_style.font = Font(bold=True, size=16, color="2C3E50") title_style.alignment = Alignment(horizontal='center', vertical='center') styles['title'] = title_style # Subtitle style subtitle_style = NamedStyle(name="subtitle_style") subtitle_style.font = Font(bold=True, size=12, color="3498DB") subtitle_style.alignment = Alignment(horizontal='left', vertical='center') styles['subtitle'] = subtitle_style return styles def create_multi_worksheet_export(self, report_data: Dict[str, Any], report_type: str, filters: Dict[str, Any]) -> HttpResponse: """ Create comprehensive Excel export with multiple worksheets Args: report_data: Complete report data report_type: Type of report filters: Applied filters Returns: HttpResponse: Excel file response """ try: self.workbook = Workbook() # Remove default worksheet self.workbook.remove(self.workbook.active) # Add named styles to workbook for style_name, style in self.styles.items(): if style_name not in [s.name for s in self.workbook.named_styles]: self.workbook.add_named_style(style) # Create worksheets based on report type if report_type == 'loans_in_arrears': self._create_arrears_worksheets(report_data, filters) elif report_type == 'loans_due': self._create_loans_due_worksheets(report_data, filters) elif report_type == 'delinquent_loans': self._create_delinquent_worksheets(report_data, filters) elif report_type == 'processing_fees': self._create_processing_fees_worksheets(report_data, filters) elif report_type == 'interest_income': self._create_interest_worksheets(report_data, filters) else: self._create_general_worksheets(report_data, filters, report_type) # Save to BytesIO buffer = BytesIO() self.workbook.save(buffer) buffer.seek(0) # Create HTTP response response = HttpResponse( buffer.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) filename = f"{report_type}_comprehensive_report_{timezone.now().strftime('%Y%m%d_%H%M%S')}.xlsx" response['Content-Disposition'] = f'attachment; filename="{filename}"' return response except Exception as e: logger.error(f"Error creating multi-worksheet export: {str(e)}") return self._create_error_response(str(e)) def _create_arrears_worksheets(self, report_data: Dict[str, Any], filters: Dict[str, Any]): """Create worksheets for loans in arrears report""" # 1. Summary Dashboard summary_ws = self.workbook.create_sheet("Summary Dashboard") self._create_summary_dashboard(summary_ws, report_data.get('summary', {}), 'Loans in Arrears') # 2. Detailed Loans Data loans_ws = self.workbook.create_sheet("Loans Details") self._create_loans_detail_sheet(loans_ws, report_data.get('loans', []), 'arrears') # 3. Risk Analysis risk_ws = self.workbook.create_sheet("Risk Analysis") self._create_risk_analysis_sheet(risk_ws, report_data) # 4. Recovery Tracking recovery_ws = self.workbook.create_sheet("Recovery Tracking") self._create_recovery_tracking_sheet(recovery_ws, report_data) # 5. Charts and Visualizations charts_ws = self.workbook.create_sheet("Charts") self._create_charts_sheet(charts_ws, report_data, 'arrears') # 6. Pivot Analysis pivot_ws = self.workbook.create_sheet("Pivot Analysis") self._create_pivot_analysis_sheet(pivot_ws, report_data.get('loans', [])) # 7. Filters Applied filters_ws = self.workbook.create_sheet("Filters & Metadata") self._create_filters_metadata_sheet(filters_ws, filters, report_data) def _create_loans_due_worksheets(self, report_data: Dict[str, Any], filters: Dict[str, Any]): """Create worksheets for loans due report""" # Summary Dashboard summary_ws = self.workbook.create_sheet("Summary Dashboard") self._create_summary_dashboard(summary_ws, report_data.get('summary', {}), 'Loans Due') # Detailed Loans Data loans_ws = self.workbook.create_sheet("Loans Details") self._create_loans_detail_sheet(loans_ws, report_data.get('loans', []), 'due') # Urgency Analysis urgency_ws = self.workbook.create_sheet("Urgency Analysis") self._create_urgency_analysis_sheet(urgency_ws, report_data) # Charts charts_ws = self.workbook.create_sheet("Charts") self._create_charts_sheet(charts_ws, report_data, 'due') # Filters filters_ws = self.workbook.create_sheet("Filters & Metadata") self._create_filters_metadata_sheet(filters_ws, filters, report_data) def _create_delinquent_worksheets(self, report_data: Dict[str, Any], filters: Dict[str, Any]): """Create worksheets for delinquent loans report""" # Summary Dashboard summary_ws = self.workbook.create_sheet("Summary Dashboard") self._create_summary_dashboard(summary_ws, report_data.get('summary', {}), 'Delinquent Loans') # Detailed Loans Data loans_ws = self.workbook.create_sheet("Loans Details") self._create_loans_detail_sheet(loans_ws, report_data.get('loans', []), 'delinquent') # Aging Analysis aging_ws = self.workbook.create_sheet("Aging Analysis") self._create_aging_analysis_sheet(aging_ws, report_data) # Collection Strategy collection_ws = self.workbook.create_sheet("Collection Strategy") self._create_collection_strategy_sheet(collection_ws, report_data) # Charts charts_ws = self.workbook.create_sheet("Charts") self._create_charts_sheet(charts_ws, report_data, 'delinquent') # Filters filters_ws = self.workbook.create_sheet("Filters & Metadata") self._create_filters_metadata_sheet(filters_ws, filters, report_data) def _create_processing_fees_worksheets(self, report_data: Dict[str, Any], filters: Dict[str, Any]): """Create worksheets for processing fees report""" # Summary Dashboard summary_ws = self.workbook.create_sheet("Summary Dashboard") self._create_summary_dashboard(summary_ws, report_data.get('summary', {}), 'Processing Fees') # Revenue Analysis revenue_ws = self.workbook.create_sheet("Revenue Analysis") self._create_revenue_analysis_sheet(revenue_ws, report_data) # Product Performance product_ws = self.workbook.create_sheet("Product Performance") self._create_product_performance_sheet(product_ws, report_data) # Charts charts_ws = self.workbook.create_sheet("Charts") self._create_charts_sheet(charts_ws, report_data, 'fees') # Filters filters_ws = self.workbook.create_sheet("Filters & Metadata") self._create_filters_metadata_sheet(filters_ws, filters, report_data) def _create_interest_worksheets(self, report_data: Dict[str, Any], filters: Dict[str, Any]): """Create worksheets for interest income report""" # Summary Dashboard summary_ws = self.workbook.create_sheet("Summary Dashboard") self._create_summary_dashboard(summary_ws, report_data.get('summary', {}), 'Interest Income') # Income Analysis income_ws = self.workbook.create_sheet("Income Analysis") self._create_income_analysis_sheet(income_ws, report_data) # Product Breakdown product_ws = self.workbook.create_sheet("Product Breakdown") self._create_product_breakdown_sheet(product_ws, report_data) # Charts charts_ws = self.workbook.create_sheet("Charts") self._create_charts_sheet(charts_ws, report_data, 'interest') # Filters filters_ws = self.workbook.create_sheet("Filters & Metadata") self._create_filters_metadata_sheet(filters_ws, filters, report_data) def _create_general_worksheets(self, report_data: Dict[str, Any], filters: Dict[str, Any], report_type: str): """Create general worksheets for other report types""" # Summary Dashboard summary_ws = self.workbook.create_sheet("Summary Dashboard") self._create_summary_dashboard(summary_ws, report_data.get('summary', {}), report_type.replace('_', ' ').title()) # Data Sheet data_ws = self.workbook.create_sheet("Data") self._create_general_data_sheet(data_ws, report_data, report_type) # Charts charts_ws = self.workbook.create_sheet("Charts") self._create_charts_sheet(charts_ws, report_data, 'general') # Filters filters_ws = self.workbook.create_sheet("Filters & Metadata") self._create_filters_metadata_sheet(filters_ws, filters, report_data) def _create_summary_dashboard(self, worksheet, summary_data: Dict[str, Any], report_title: str): """Create summary dashboard worksheet with KPIs and key metrics""" # Title worksheet['A1'] = f"{report_title} - Summary Dashboard" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Generation info worksheet['A3'] = "Report Generated:" worksheet['B3'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S') worksheet['A3'].font = Font(bold=True) # KPI Section worksheet['A5'] = "Key Performance Indicators" worksheet['A5'].style = 'subtitle_style' # KPI Headers kpi_headers = ['Metric', 'Value', 'Description'] for col, header in enumerate(kpi_headers, start=1): cell = worksheet.cell(row=6, column=col, value=header) cell.style = 'header' # KPI Data kpi_data = self._extract_kpi_data(summary_data) for row, (metric, value, description) in enumerate(kpi_data, start=7): worksheet.cell(row=row, column=1, value=metric).style = 'data' # Format value based on type value_cell = worksheet.cell(row=row, column=2, value=value) if isinstance(value, (int, float, Decimal)) and 'amount' in metric.lower(): value_cell.style = 'currency' else: value_cell.style = 'data' worksheet.cell(row=row, column=3, value=description).style = 'data' # Apply conditional formatting to KPI values self._apply_kpi_conditional_formatting(worksheet, len(kpi_data) + 6) # Auto-adjust column widths self._auto_adjust_columns(worksheet) def _create_loans_detail_sheet(self, worksheet, loans_data: List[Dict[str, Any]], sheet_type: str): """Create detailed loans data sheet with advanced formatting""" if not loans_data: worksheet['A1'] = "No loan data available" return # Title title = f"Detailed Loans Data ({sheet_type.title()})" worksheet['A1'] = title worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:J1') # Headers based on sheet type if sheet_type == 'arrears': headers = [ 'Loan Number', 'Borrower Name', 'Borrower Phone', 'Product Type', 'Repayment Method', 'Arrears Amount', 'Days Overdue', 'Risk Level', 'Last Payment Date', 'Last Payment Amount' ] elif sheet_type == 'due': headers = [ 'Loan Number', 'Borrower Name', 'Borrower Phone', 'Product Type', 'Due Date', 'Due Amount', 'Status', 'Days Until Due', 'Contact Priority', 'Portfolio Manager' ] elif sheet_type == 'delinquent': headers = [ 'Loan Number', 'Borrower Name', 'Borrower Phone', 'Product Type', 'Days Overdue', 'Overdue Amount', 'Total Outstanding', 'Risk Category', 'Collection Status', 'Last Contact Date' ] else: headers = list(loans_data[0].keys()) if loans_data else [] # Add headers for col, header in enumerate(headers, start=1): cell = worksheet.cell(row=3, column=col, value=header) cell.style = 'header' # Add data for row_idx, loan in enumerate(loans_data, start=4): for col_idx, header in enumerate(headers, start=1): value = loan.get(header.lower().replace(' ', '_'), '') cell = worksheet.cell(row=row_idx, column=col_idx, value=value) # Apply appropriate styling if 'amount' in header.lower() or 'outstanding' in header.lower(): cell.style = 'currency' elif 'date' in header.lower(): cell.style = 'date' else: cell.style = 'data' # Create Excel table table_range = f"A3:{get_column_letter(len(headers))}{len(loans_data) + 3}" table = Table(displayName=f"LoansTable_{sheet_type}", ref=table_range) # Apply table style style = TableStyleInfo( name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False ) table.tableStyleInfo = style worksheet.add_table(table) # Apply conditional formatting self._apply_loans_conditional_formatting(worksheet, len(loans_data) + 3, sheet_type) # Add data validation self._add_data_validation(worksheet, len(loans_data) + 3, headers) # Auto-adjust columns self._auto_adjust_columns(worksheet) def _create_charts_sheet(self, worksheet, report_data: Dict[str, Any], chart_type: str): """Create charts worksheet with multiple visualizations""" worksheet['A1'] = "Charts and Visualizations" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:H1') chart_row = 3 if chart_type == 'arrears': # Risk distribution pie chart chart_row = self._create_risk_distribution_chart(worksheet, report_data, chart_row) # Amount distribution bar chart chart_row = self._create_amount_distribution_chart(worksheet, report_data, chart_row) elif chart_type == 'due': # Urgency distribution chart chart_row = self._create_urgency_chart(worksheet, report_data, chart_row) # Due amounts trend chart chart_row = self._create_due_amounts_trend_chart(worksheet, report_data, chart_row) elif chart_type == 'delinquent': # Aging analysis chart chart_row = self._create_aging_chart(worksheet, report_data, chart_row) elif chart_type == 'fees': # Revenue trend chart chart_row = self._create_revenue_trend_chart(worksheet, report_data, chart_row) elif chart_type == 'interest': # Product performance chart chart_row = self._create_product_performance_chart(worksheet, report_data, chart_row) def _create_risk_distribution_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int: """Create risk distribution pie chart""" # Data for chart risk_data = [ ['Risk Level', 'Count'], ['Low Risk', report_data.get('severity_breakdown', {}).get('low_risk_count', 0)], ['Medium Risk', report_data.get('severity_breakdown', {}).get('medium_risk_count', 0)], ['High Risk', report_data.get('severity_breakdown', {}).get('high_risk_count', 0)], ['Critical', report_data.get('severity_breakdown', {}).get('critical_count', 0)] ] # Add data to worksheet for row_idx, row_data in enumerate(risk_data, start=start_row): for col_idx, value in enumerate(row_data, start=1): worksheet.cell(row=row_idx, column=col_idx, value=value) # Create pie chart chart = PieChart() chart.title = "Risk Distribution" # Data references data = Reference(worksheet, min_col=2, min_row=start_row+1, max_row=start_row+4) labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+4) chart.add_data(data) chart.set_categories(labels) # Styling chart.dataLabels = DataLabelList() chart.dataLabels.showPercent = True # Position chart worksheet.add_chart(chart, f"D{start_row}") return start_row + 20 def _create_amount_distribution_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int: """Create amount distribution bar chart""" # Sample amount ranges data amount_ranges = report_data.get('amount_ranges', { '0-10K': 5, '10K-50K': 15, '50K-100K': 8, '100K+': 3 }) # Data for chart chart_data = [['Amount Range', 'Count']] chart_data.extend(list(amount_ranges.items())) # Add data to worksheet for row_idx, row_data in enumerate(chart_data, start=start_row): for col_idx, value in enumerate(row_data, start=1): worksheet.cell(row=row_idx, column=col_idx, value=value) # Create bar chart chart = BarChart() chart.title = "Amount Distribution" chart.x_axis.title = "Amount Range" chart.y_axis.title = "Number of Loans" # Data references data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+len(amount_ranges)) labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+len(amount_ranges)) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) # Position chart worksheet.add_chart(chart, f"D{start_row}") return start_row + 20 def _apply_kpi_conditional_formatting(self, worksheet, end_row: int): """Apply conditional formatting to KPI values""" # Green for positive values, red for negative green_rule = CellIsRule(operator='greaterThan', formula=['0'], fill=PatternFill(start_color='C6EFCE', end_color='C6EFCE')) red_rule = CellIsRule(operator='lessThan', formula=['0'], fill=PatternFill(start_color='FFC7CE', end_color='FFC7CE')) worksheet.conditional_formatting.add(f'B7:B{end_row}', green_rule) worksheet.conditional_formatting.add(f'B7:B{end_row}', red_rule) def _apply_loans_conditional_formatting(self, worksheet, end_row: int, sheet_type: str): """Apply conditional formatting to loans data""" if sheet_type == 'arrears': # Color code risk levels high_risk_rule = CellIsRule(operator='equal', formula=['"High Risk"'], fill=PatternFill(start_color='FFC7CE', end_color='FFC7CE')) medium_risk_rule = CellIsRule(operator='equal', formula=['"Medium Risk"'], fill=PatternFill(start_color='FFEB9C', end_color='FFEB9C')) low_risk_rule = CellIsRule(operator='equal', formula=['"Low Risk"'], fill=PatternFill(start_color='C6EFCE', end_color='C6EFCE')) # Apply to risk level column (assuming column H) worksheet.conditional_formatting.add(f'H4:H{end_row}', high_risk_rule) worksheet.conditional_formatting.add(f'H4:H{end_row}', medium_risk_rule) worksheet.conditional_formatting.add(f'H4:H{end_row}', low_risk_rule) elif sheet_type == 'due': # Color code urgency urgent_rule = CellIsRule(operator='lessThanOrEqual', formula=['0'], fill=PatternFill(start_color='FFC7CE', end_color='FFC7CE')) soon_rule = CellIsRule(operator='between', formula=['1', '7'], fill=PatternFill(start_color='FFEB9C', end_color='FFEB9C')) # Apply to days until due column worksheet.conditional_formatting.add(f'H4:H{end_row}', urgent_rule) worksheet.conditional_formatting.add(f'H4:H{end_row}', soon_rule) def _add_data_validation(self, worksheet, end_row: int, headers: List[str]): """Add data validation rules""" # Add dropdown for risk levels if present if 'Risk Level' in headers: risk_col = headers.index('Risk Level') + 1 risk_validation = DataValidation(type="list", formula1='"Low Risk,Medium Risk,High Risk,Critical"') risk_validation.error = 'Please select a valid risk level' risk_validation.errorTitle = 'Invalid Risk Level' worksheet.add_data_validation(risk_validation) risk_validation.add(f'{get_column_letter(risk_col)}4:{get_column_letter(risk_col)}{end_row}') def _auto_adjust_columns(self, worksheet): """Auto-adjust column widths based on content""" for column in worksheet.columns: max_length = 0 column_letter = get_column_letter(column[0].column) 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) worksheet.column_dimensions[column_letter].width = adjusted_width def _extract_kpi_data(self, summary_data: Dict[str, Any]) -> List[tuple]: """Extract KPI data for dashboard""" kpis = [] if 'total_loans' in summary_data: kpis.append(('Total Loans', summary_data['total_loans'], 'Total number of loans in the report')) if 'total_amount' in summary_data: kpis.append(('Total Amount', float(summary_data['total_amount']), 'Total monetary value')) if 'average_amount' in summary_data: kpis.append(('Average Amount', float(summary_data['average_amount']), 'Average loan amount')) if 'collection_rate' in summary_data: kpis.append(('Collection Rate', f"{summary_data['collection_rate']:.1f}%", 'Overall collection efficiency')) if 'recovery_rate' in summary_data: kpis.append(('Recovery Rate', f"{summary_data['recovery_rate']:.1f}%", 'Recovery success rate')) return kpis def _create_risk_analysis_sheet(self, worksheet, report_data: Dict[str, Any]): """Create risk analysis worksheet""" worksheet['A1'] = "Risk Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Risk distribution summary worksheet['A3'] = "Risk Distribution Summary" worksheet['A3'].style = 'subtitle_style' severity_breakdown = report_data.get('severity_breakdown', {}) risk_data = [ ['Risk Level', 'Count', 'Percentage', 'Total Amount', 'Average Amount'], ['Low Risk', severity_breakdown.get('low_risk_count', 0), f"{severity_breakdown.get('low_risk_percentage', 0):.1f}%", severity_breakdown.get('low_risk_amount', 0), severity_breakdown.get('low_risk_avg', 0)], ['Medium Risk', severity_breakdown.get('medium_risk_count', 0), f"{severity_breakdown.get('medium_risk_percentage', 0):.1f}%", severity_breakdown.get('medium_risk_amount', 0), severity_breakdown.get('medium_risk_avg', 0)], ['High Risk', severity_breakdown.get('high_risk_count', 0), f"{severity_breakdown.get('high_risk_percentage', 0):.1f}%", severity_breakdown.get('high_risk_amount', 0), severity_breakdown.get('high_risk_avg', 0)], ['Critical', severity_breakdown.get('critical_count', 0), f"{severity_breakdown.get('critical_percentage', 0):.1f}%", severity_breakdown.get('critical_amount', 0), severity_breakdown.get('critical_avg', 0)] ] # Add risk data to worksheet for row_idx, row_data in enumerate(risk_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' elif col_idx in [4, 5]: # Amount columns cell.style = 'currency' else: cell.style = 'data' # Auto-adjust columns self._auto_adjust_columns(worksheet) def _create_recovery_tracking_sheet(self, worksheet, report_data: Dict[str, Any]): """Create recovery tracking worksheet""" worksheet['A1'] = "Recovery Tracking" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Recovery metrics worksheet['A3'] = "Recovery Metrics" worksheet['A3'].style = 'subtitle_style' summary = report_data.get('summary', {}) recovery_data = [ ['Metric', 'Value', 'Target', 'Status'], ['Recovery Rate', f"{summary.get('recovery_rate', 0):.1f}%", "85%", "Good" if summary.get('recovery_rate', 0) >= 85 else "Needs Improvement"], ['Average Recovery Time', f"{summary.get('avg_recovery_days', 0)} days", "30 days", "Good" if summary.get('avg_recovery_days', 0) <= 30 else "Needs Improvement"], ['Total Recovered', summary.get('total_recovered', 0), summary.get('recovery_target', 0), "On Track" if summary.get('total_recovered', 0) >= summary.get('recovery_target', 0) * 0.8 else "Behind"], ] # Add recovery data for row_idx, row_data in enumerate(recovery_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' elif col_idx == 2 and isinstance(value, (int, float)): # Amount columns cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_urgency_analysis_sheet(self, worksheet, report_data: Dict[str, Any]): """Create urgency analysis worksheet""" worksheet['A1'] = "Urgency Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Urgency breakdown worksheet['A3'] = "Urgency Breakdown" worksheet['A3'].style = 'subtitle_style' urgency_data = [ ['Urgency Level', 'Count', 'Total Amount', 'Action Required'], ['Overdue', report_data.get('overdue_count', 0), report_data.get('overdue_amount', 0), 'Immediate Contact'], ['Due Today', report_data.get('due_today_count', 0), report_data.get('due_today_amount', 0), 'Contact Today'], ['Due This Week', report_data.get('due_week_count', 0), report_data.get('due_week_amount', 0), 'Schedule Contact'], ['Due Next Week', report_data.get('due_next_week_count', 0), report_data.get('due_next_week_amount', 0), 'Monitor'] ] # Add urgency data for row_idx, row_data in enumerate(urgency_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' elif col_idx == 3: # Amount column cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_aging_analysis_sheet(self, worksheet, report_data: Dict[str, Any]): """Create aging analysis worksheet""" worksheet['A1'] = "Aging Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Aging buckets worksheet['A3'] = "Aging Buckets" worksheet['A3'].style = 'subtitle_style' aging_data = [ ['Days Overdue', 'Count', 'Total Amount', 'Percentage'], ['1-30 days', report_data.get('aging_1_30_count', 0), report_data.get('aging_1_30_amount', 0), f"{report_data.get('aging_1_30_percentage', 0):.1f}%"], ['31-60 days', report_data.get('aging_31_60_count', 0), report_data.get('aging_31_60_amount', 0), f"{report_data.get('aging_31_60_percentage', 0):.1f}%"], ['61-90 days', report_data.get('aging_61_90_count', 0), report_data.get('aging_61_90_amount', 0), f"{report_data.get('aging_61_90_percentage', 0):.1f}%"], ['90+ days', report_data.get('aging_90_plus_count', 0), report_data.get('aging_90_plus_amount', 0), f"{report_data.get('aging_90_plus_percentage', 0):.1f}%"] ] # Add aging data for row_idx, row_data in enumerate(aging_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' elif col_idx == 3: # Amount column cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_collection_strategy_sheet(self, worksheet, report_data: Dict[str, Any]): """Create collection strategy worksheet""" worksheet['A1'] = "Collection Strategy" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Collection recommendations worksheet['A3'] = "Collection Recommendations" worksheet['A3'].style = 'subtitle_style' strategy_data = [ ['Risk Level', 'Recommended Action', 'Timeline', 'Priority'], ['Low Risk', 'Friendly reminder call', '1-2 days', 'Low'], ['Medium Risk', 'Formal notice + call', '1 day', 'Medium'], ['High Risk', 'Field visit + payment plan', 'Immediate', 'High'], ['Critical', 'Legal action consideration', 'Immediate', 'Critical'] ] # Add strategy data for row_idx, row_data in enumerate(strategy_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_revenue_analysis_sheet(self, worksheet, report_data: Dict[str, Any]): """Create revenue analysis worksheet""" worksheet['A1'] = "Revenue Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Revenue metrics worksheet['A3'] = "Revenue Metrics" worksheet['A3'].style = 'subtitle_style' summary = report_data.get('summary', {}) revenue_data = [ ['Metric', 'Current Period', 'Previous Period', 'Change'], ['Total Processing Fees', summary.get('total_fees', 0), summary.get('previous_total_fees', 0), f"{summary.get('fees_change_percentage', 0):.1f}%"], ['Average Fee per Loan', summary.get('avg_fee', 0), summary.get('previous_avg_fee', 0), f"{summary.get('avg_fee_change_percentage', 0):.1f}%"], ['Fee Collection Rate', f"{summary.get('fee_collection_rate', 0):.1f}%", f"{summary.get('previous_fee_collection_rate', 0):.1f}%", f"{summary.get('collection_rate_change', 0):.1f}%"] ] # Add revenue data for row_idx, row_data in enumerate(revenue_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' elif col_idx in [2, 3] and isinstance(value, (int, float)): # Amount columns cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_product_performance_sheet(self, worksheet, report_data: Dict[str, Any]): """Create product performance worksheet""" worksheet['A1'] = "Product Performance" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Product breakdown worksheet['A3'] = "Product Performance Breakdown" worksheet['A3'].style = 'subtitle_style' products = report_data.get('product_breakdown', {}) product_data = [['Product Type', 'Count', 'Total Amount', 'Average Amount', 'Performance']] for product_name, product_info in products.items(): product_data.append([ product_name, product_info.get('count', 0), product_info.get('total_amount', 0), product_info.get('average_amount', 0), product_info.get('performance_rating', 'N/A') ]) # Add product data for row_idx, row_data in enumerate(product_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' elif col_idx in [3, 4] and isinstance(value, (int, float)): # Amount columns cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_income_analysis_sheet(self, worksheet, report_data: Dict[str, Any]): """Create income analysis worksheet""" worksheet['A1'] = "Income Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Income metrics worksheet['A3'] = "Interest Income Metrics" worksheet['A3'].style = 'subtitle_style' summary = report_data.get('summary', {}) income_data = [ ['Metric', 'Amount', 'Target', 'Achievement'], ['Total Interest Income', summary.get('total_interest', 0), summary.get('interest_target', 0), f"{summary.get('interest_achievement_rate', 0):.1f}%"], ['Average Interest Rate', f"{summary.get('avg_interest_rate', 0):.2f}%", f"{summary.get('target_interest_rate', 0):.2f}%", "On Target" if summary.get('avg_interest_rate', 0) >= summary.get('target_interest_rate', 0) else "Below Target"], ['Interest Collection Rate', f"{summary.get('interest_collection_rate', 0):.1f}%", "95%", "Good" if summary.get('interest_collection_rate', 0) >= 95 else "Needs Improvement"] ] # Add income data for row_idx, row_data in enumerate(income_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' elif col_idx == 2 and isinstance(value, (int, float)): # Amount columns cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_product_breakdown_sheet(self, worksheet, report_data: Dict[str, Any]): """Create product breakdown worksheet""" worksheet['A1'] = "Product Breakdown" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Product interest breakdown worksheet['A3'] = "Interest Income by Product" worksheet['A3'].style = 'subtitle_style' products = report_data.get('product_breakdown', {}) breakdown_data = [['Product Type', 'Interest Income', 'Loan Count', 'Average Interest', 'Contribution %']] total_interest = sum(p.get('interest_income', 0) for p in products.values()) for product_name, product_info in products.items(): interest_income = product_info.get('interest_income', 0) contribution = (interest_income / total_interest * 100) if total_interest > 0 else 0 breakdown_data.append([ product_name, interest_income, product_info.get('loan_count', 0), product_info.get('average_interest', 0), f"{contribution:.1f}%" ]) # Add breakdown data for row_idx, row_data in enumerate(breakdown_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' elif col_idx in [2, 4] and isinstance(value, (int, float)): # Amount columns cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_general_data_sheet(self, worksheet, report_data: Dict[str, Any], report_type: str): """Create general data worksheet""" worksheet['A1'] = f"{report_type.replace('_', ' ').title()} Data" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Add summary if available if 'summary' in report_data: worksheet['A3'] = "Summary" worksheet['A3'].style = 'subtitle_style' summary = report_data['summary'] summary_data = [['Metric', 'Value']] for key, value in summary.items(): if isinstance(value, (int, float)): if 'amount' in key.lower(): summary_data.append([key.replace('_', ' ').title(), value]) else: summary_data.append([key.replace('_', ' ').title(), value]) else: summary_data.append([key.replace('_', ' ').title(), str(value)]) # Add summary data for row_idx, row_data in enumerate(summary_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' elif col_idx == 2 and isinstance(value, (int, float)) and 'amount' in summary_data[row_idx-4][0].lower(): cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_pivot_analysis_sheet(self, worksheet, loans_data: List[Dict[str, Any]]): """Create pivot analysis worksheet""" worksheet['A1'] = "Pivot Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') if not loans_data: worksheet['A3'] = "No data available for pivot analysis" return # Create pivot-like analysis worksheet['A3'] = "Analysis by Risk Level" worksheet['A3'].style = 'subtitle_style' # Group data by risk level risk_groups = {} for loan in loans_data: risk_level = loan.get('risk_level', 'Unknown') if risk_level not in risk_groups: risk_groups[risk_level] = {'count': 0, 'total_amount': 0} risk_groups[risk_level]['count'] += 1 amount = loan.get('arrears_amount', 0) or loan.get('due_amount', 0) or 0 if isinstance(amount, (int, float)): risk_groups[risk_level]['total_amount'] += amount # Create pivot table pivot_data = [['Risk Level', 'Count', 'Total Amount', 'Average Amount']] for risk_level, data in risk_groups.items(): avg_amount = data['total_amount'] / data['count'] if data['count'] > 0 else 0 pivot_data.append([ risk_level, data['count'], data['total_amount'], avg_amount ]) # Add pivot data for row_idx, row_data in enumerate(pivot_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' elif col_idx in [3, 4]: # Amount columns cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_filters_metadata_sheet(self, worksheet, filters: Dict[str, Any], report_data: Dict[str, Any]): """Create filters and metadata worksheet""" worksheet['A1'] = "Filters & Metadata" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Applied filters worksheet['A3'] = "Applied Filters" worksheet['A3'].style = 'subtitle_style' filter_data = [['Filter', 'Value']] for filter_name, filter_value in filters.items(): filter_data.append([filter_name.replace('_', ' ').title(), str(filter_value)]) # Add filter data for row_idx, row_data in enumerate(filter_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' else: cell.style = 'data' # Report metadata metadata_start_row = len(filter_data) + 6 worksheet[f'A{metadata_start_row}'] = "Report Metadata" worksheet[f'A{metadata_start_row}'].style = 'subtitle_style' metadata = [ ['Generated On', datetime.now().strftime('%Y-%m-%d %H:%M:%S')], ['Total Records', len(report_data.get('loans', []))], ['Report Version', '2.0'], ['System', 'Loan Management System'] ] # Add metadata for row_idx, row_data in enumerate(metadata, start=metadata_start_row + 1): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_urgency_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int: """Create urgency distribution chart""" # Data for chart urgency_data = [ ['Urgency Level', 'Count'], ['Overdue', report_data.get('overdue_count', 0)], ['Due Today', report_data.get('due_today_count', 0)], ['Due This Week', report_data.get('due_week_count', 0)], ['Due Next Week', report_data.get('due_next_week_count', 0)] ] # Add data to worksheet for row_idx, row_data in enumerate(urgency_data, start=start_row): for col_idx, value in enumerate(row_data, start=1): worksheet.cell(row=row_idx, column=col_idx, value=value) # Create bar chart chart = BarChart() chart.title = "Urgency Distribution" chart.x_axis.title = "Urgency Level" chart.y_axis.title = "Number of Loans" # Data references data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+4) labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+4) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) # Position chart worksheet.add_chart(chart, f"D{start_row}") return start_row + 20 def _create_due_amounts_trend_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int: """Create due amounts trend chart""" # Sample trend data trend_data = report_data.get('trend_data', { 'Jan': 100000, 'Feb': 120000, 'Mar': 110000, 'Apr': 140000, 'May': 130000, 'Jun': 150000 }) # Data for chart chart_data = [['Month', 'Due Amount']] chart_data.extend(list(trend_data.items())) # Add data to worksheet for row_idx, row_data in enumerate(chart_data, start=start_row): for col_idx, value in enumerate(row_data, start=1): worksheet.cell(row=row_idx, column=col_idx, value=value) # Create line chart chart = LineChart() chart.title = "Due Amounts Trend" chart.x_axis.title = "Month" chart.y_axis.title = "Amount (KES)" # Data references data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+len(trend_data)) labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+len(trend_data)) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) # Position chart worksheet.add_chart(chart, f"D{start_row}") return start_row + 20 def _create_aging_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int: """Create aging analysis chart""" # Data for chart aging_data = [ ['Days Overdue', 'Count'], ['1-30 days', report_data.get('aging_1_30_count', 0)], ['31-60 days', report_data.get('aging_31_60_count', 0)], ['61-90 days', report_data.get('aging_61_90_count', 0)], ['90+ days', report_data.get('aging_90_plus_count', 0)] ] # Add data to worksheet for row_idx, row_data in enumerate(aging_data, start=start_row): for col_idx, value in enumerate(row_data, start=1): worksheet.cell(row=row_idx, column=col_idx, value=value) # Create bar chart chart = BarChart() chart.title = "Aging Analysis" chart.x_axis.title = "Days Overdue" chart.y_axis.title = "Number of Loans" # Data references data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+4) labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+4) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) # Position chart worksheet.add_chart(chart, f"D{start_row}") return start_row + 20 def _create_revenue_trend_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int: """Create revenue trend chart""" # Sample revenue trend data revenue_trend = report_data.get('revenue_trend', { 'Jan': 50000, 'Feb': 60000, 'Mar': 55000, 'Apr': 70000, 'May': 65000, 'Jun': 75000 }) # Data for chart chart_data = [['Month', 'Revenue']] chart_data.extend(list(revenue_trend.items())) # Add data to worksheet for row_idx, row_data in enumerate(chart_data, start=start_row): for col_idx, value in enumerate(row_data, start=1): worksheet.cell(row=row_idx, column=col_idx, value=value) # Create line chart chart = LineChart() chart.title = "Revenue Trend" chart.x_axis.title = "Month" chart.y_axis.title = "Revenue (KES)" # Data references data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+len(revenue_trend)) labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+len(revenue_trend)) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) # Position chart worksheet.add_chart(chart, f"D{start_row}") return start_row + 20 def _create_product_performance_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int: """Create product performance chart""" # Product performance data products = report_data.get('product_breakdown', { 'Personal Loans': {'performance_score': 85}, 'Business Loans': {'performance_score': 78}, 'Emergency Loans': {'performance_score': 92}, 'Asset Financing': {'performance_score': 73} }) # Data for chart chart_data = [['Product', 'Performance Score']] for product_name, product_info in products.items(): chart_data.append([product_name, product_info.get('performance_score', 0)]) # Add data to worksheet for row_idx, row_data in enumerate(chart_data, start=start_row): for col_idx, value in enumerate(row_data, start=1): worksheet.cell(row=row_idx, column=col_idx, value=value) # Create bar chart chart = BarChart() chart.title = "Product Performance" chart.x_axis.title = "Product Type" chart.y_axis.title = "Performance Score" # Data references data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+len(products)) labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+len(products)) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) # Position chart worksheet.add_chart(chart, f"D{start_row}") return start_row + 20 def _create_error_response(self, error_message: str) -> HttpResponse: """Create error response when export fails""" wb = Workbook() ws = wb.active ws.title = "Error" ws['A1'] = "Export Error" ws['A2'] = f"An error occurred while generating the export: {error_message}" buffer = BytesIO() wb.save(buffer) buffer.seek(0) response = HttpResponse( buffer.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response['Content-Disposition'] = f'attachment; filename="export_error_{timezone.now().strftime("%Y%m%d_%H%M%S")}.xlsx"' return response def create_advanced_excel_export(self, report_data: Dict[str, Any], report_type: str, filters: Dict[str, Any], include_formulas: bool = True, include_pivot_tables: bool = True) -> HttpResponse: """ Create advanced Excel export with formulas, pivot tables, and enhanced styling Args: report_data: Complete report data report_type: Type of report filters: Applied filters include_formulas: Whether to include calculated formulas include_pivot_tables: Whether to include pivot tables Returns: HttpResponse: Advanced Excel file response """ try: self.workbook = Workbook() # Remove default worksheet self.workbook.remove(self.workbook.active) # Add named styles to workbook for style_name, style in self.styles.items(): if style_name not in [s.name for s in self.workbook.named_styles]: self.workbook.add_named_style(style) # Create main data worksheet with formulas main_ws = self.workbook.create_sheet("Data Analysis") self._create_advanced_data_sheet(main_ws, report_data, include_formulas) # Create pivot table worksheet if requested if include_pivot_tables and report_data.get('loans'): pivot_ws = self.workbook.create_sheet("Pivot Analysis") self._create_advanced_pivot_sheet(pivot_ws, report_data.get('loans', [])) # Create formula calculations worksheet if include_formulas: calc_ws = self.workbook.create_sheet("Calculations") self._create_formula_calculations_sheet(calc_ws, report_data) # Create dashboard worksheet dashboard_ws = self.workbook.create_sheet("Dashboard") self._create_dashboard_worksheet(dashboard_ws, report_data) # Save to BytesIO buffer = BytesIO() self.workbook.save(buffer) buffer.seek(0) # Create HTTP response response = HttpResponse( buffer.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) filename = f"{report_type}_advanced_export_{timezone.now().strftime('%Y%m%d_%H%M%S')}.xlsx" response['Content-Disposition'] = f'attachment; filename="{filename}"' return response except Exception as e: logger.error(f"Error creating advanced Excel export: {str(e)}") return self._create_error_response(str(e)) def _create_advanced_data_sheet(self, worksheet, report_data: Dict[str, Any], include_formulas: bool): """Create advanced data sheet with formulas and enhanced styling""" worksheet['A1'] = "Advanced Data Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:H1') loans_data = report_data.get('loans', []) if not loans_data: worksheet['A3'] = "No data available" return # Enhanced headers with calculated columns headers = [ 'Loan Number', 'Borrower Name', 'Principal Amount', 'Outstanding Amount', 'Days Overdue', 'Risk Score', 'Collection Priority', 'Projected Recovery' ] # Add headers with enhanced styling for col, header in enumerate(headers, start=1): cell = worksheet.cell(row=3, column=col, value=header) cell.style = 'header' cell.fill = PatternFill(start_color=self.colors['primary'], end_color=self.colors['primary'], fill_type="solid") cell.font = Font(bold=True, color="FFFFFF", size=12) # Add data with formulas for row_idx, loan in enumerate(loans_data, start=4): # Basic data columns worksheet.cell(row=row_idx, column=1, value=loan.get('loan_number', '')).style = 'data' worksheet.cell(row=row_idx, column=2, value=loan.get('borrower_name', '')).style = 'data' # Amount columns with currency formatting principal_cell = worksheet.cell(row=row_idx, column=3, value=loan.get('principal_amount', 0)) principal_cell.style = 'currency' outstanding_cell = worksheet.cell(row=row_idx, column=4, value=loan.get('outstanding_amount', 0)) outstanding_cell.style = 'currency' # Days overdue days_cell = worksheet.cell(row=row_idx, column=5, value=loan.get('days_overdue', 0)) days_cell.style = 'data' if include_formulas: # Risk score formula (example: based on days overdue and amount) risk_formula = f"=MIN(100, MAX(0, E{row_idx}*2 + (D{row_idx}/C{row_idx})*50))" risk_cell = worksheet.cell(row=row_idx, column=6, value=risk_formula) risk_cell.style = 'data' # Collection priority formula priority_formula = f'=IF(F{row_idx}>80,"High",IF(F{row_idx}>50,"Medium","Low"))' priority_cell = worksheet.cell(row=row_idx, column=7, value=priority_formula) priority_cell.style = 'data' # Projected recovery formula recovery_formula = f"=D{row_idx}*IF(F{row_idx}>80,0.6,IF(F{row_idx}>50,0.8,0.95))" recovery_cell = worksheet.cell(row=row_idx, column=8, value=recovery_formula) recovery_cell.style = 'currency' else: # Static values if formulas not requested worksheet.cell(row=row_idx, column=6, value=loan.get('risk_score', 0)).style = 'data' worksheet.cell(row=row_idx, column=7, value=loan.get('priority', 'Medium')).style = 'data' worksheet.cell(row=row_idx, column=8, value=loan.get('projected_recovery', 0)).style = 'currency' # Add summary formulas at the bottom if include_formulas and loans_data: summary_row = len(loans_data) + 5 worksheet.cell(row=summary_row, column=2, value="TOTALS:").font = Font(bold=True) worksheet.cell(row=summary_row, column=3, value=f"=SUM(C4:C{len(loans_data)+3})").style = 'currency' worksheet.cell(row=summary_row, column=4, value=f"=SUM(D4:D{len(loans_data)+3})").style = 'currency' worksheet.cell(row=summary_row, column=5, value=f"=AVERAGE(E4:E{len(loans_data)+3})").style = 'data' worksheet.cell(row=summary_row, column=6, value=f"=AVERAGE(F4:F{len(loans_data)+3})").style = 'data' worksheet.cell(row=summary_row, column=8, value=f"=SUM(H4:H{len(loans_data)+3})").style = 'currency' # Apply advanced conditional formatting self._apply_advanced_conditional_formatting(worksheet, len(loans_data) + 3) # Auto-adjust columns self._auto_adjust_columns(worksheet) def _create_advanced_pivot_sheet(self, worksheet, loans_data: List[Dict[str, Any]]): """Create advanced pivot table analysis""" worksheet['A1'] = "Pivot Table Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') if not loans_data: worksheet['A3'] = "No data available for pivot analysis" return # Create multiple pivot-style analyses # 1. Risk Level Analysis worksheet['A3'] = "Analysis by Risk Level" worksheet['A3'].style = 'subtitle_style' risk_analysis = self._create_risk_level_pivot(loans_data) self._add_pivot_table_to_sheet(worksheet, risk_analysis, 4, 1) # 2. Product Type Analysis worksheet['A12'] = "Analysis by Product Type" worksheet['A12'].style = 'subtitle_style' product_analysis = self._create_product_type_pivot(loans_data) self._add_pivot_table_to_sheet(worksheet, product_analysis, 13, 1) # 3. Branch Analysis worksheet['A21'] = "Analysis by Branch" worksheet['A21'].style = 'subtitle_style' branch_analysis = self._create_branch_pivot(loans_data) self._add_pivot_table_to_sheet(worksheet, branch_analysis, 22, 1) def _create_risk_level_pivot(self, loans_data: List[Dict[str, Any]]) -> List[List]: """Create risk level pivot analysis""" risk_groups = {} for loan in loans_data: risk_level = loan.get('risk_level', 'Unknown') amount = loan.get('outstanding_amount', 0) or loan.get('arrears_amount', 0) or 0 if risk_level not in risk_groups: risk_groups[risk_level] = { 'count': 0, 'total_amount': 0, 'amounts': [] } risk_groups[risk_level]['count'] += 1 if isinstance(amount, (int, float)): risk_groups[risk_level]['total_amount'] += amount risk_groups[risk_level]['amounts'].append(amount) # Create pivot table data pivot_data = [['Risk Level', 'Count', 'Total Amount', 'Average Amount', 'Min Amount', 'Max Amount']] for risk_level, data in risk_groups.items(): amounts = data['amounts'] avg_amount = data['total_amount'] / data['count'] if data['count'] > 0 else 0 min_amount = min(amounts) if amounts else 0 max_amount = max(amounts) if amounts else 0 pivot_data.append([ risk_level, data['count'], data['total_amount'], avg_amount, min_amount, max_amount ]) return pivot_data def _create_product_type_pivot(self, loans_data: List[Dict[str, Any]]) -> List[List]: """Create product type pivot analysis""" product_groups = {} for loan in loans_data: product_type = loan.get('product_type', 'Unknown') amount = loan.get('outstanding_amount', 0) or loan.get('principal_amount', 0) or 0 if product_type not in product_groups: product_groups[product_type] = { 'count': 0, 'total_amount': 0 } product_groups[product_type]['count'] += 1 if isinstance(amount, (int, float)): product_groups[product_type]['total_amount'] += amount # Create pivot table data pivot_data = [['Product Type', 'Count', 'Total Amount', 'Average Amount', 'Percentage']] total_count = sum(data['count'] for data in product_groups.values()) for product_type, data in product_groups.items(): avg_amount = data['total_amount'] / data['count'] if data['count'] > 0 else 0 percentage = (data['count'] / total_count * 100) if total_count > 0 else 0 pivot_data.append([ product_type, data['count'], data['total_amount'], avg_amount, f"{percentage:.1f}%" ]) return pivot_data def _create_branch_pivot(self, loans_data: List[Dict[str, Any]]) -> List[List]: """Create branch pivot analysis""" branch_groups = {} for loan in loans_data: branch = loan.get('branch', 'Main Branch') amount = loan.get('outstanding_amount', 0) or loan.get('principal_amount', 0) or 0 if branch not in branch_groups: branch_groups[branch] = { 'count': 0, 'total_amount': 0 } branch_groups[branch]['count'] += 1 if isinstance(amount, (int, float)): branch_groups[branch]['total_amount'] += amount # Create pivot table data pivot_data = [['Branch', 'Count', 'Total Amount', 'Average Amount']] for branch, data in branch_groups.items(): avg_amount = data['total_amount'] / data['count'] if data['count'] > 0 else 0 pivot_data.append([ branch, data['count'], data['total_amount'], avg_amount ]) return pivot_data def _add_pivot_table_to_sheet(self, worksheet, pivot_data: List[List], start_row: int, start_col: int): """Add pivot table data to worksheet""" for row_idx, row_data in enumerate(pivot_data): for col_idx, value in enumerate(row_data): cell = worksheet.cell(row=start_row + row_idx, column=start_col + col_idx, value=value) if row_idx == 0: # Header row cell.style = 'header' elif col_idx in [2, 3, 4, 5] and isinstance(value, (int, float)): # Amount columns cell.style = 'currency' else: cell.style = 'data' def _create_formula_calculations_sheet(self, worksheet, report_data: Dict[str, Any]): """Create worksheet with formula calculations""" worksheet['A1'] = "Formula Calculations" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Key metrics with formulas worksheet['A3'] = "Key Metrics (Formula-Based)" worksheet['A3'].style = 'subtitle_style' # Reference the main data sheet for calculations metrics_data = [ ['Metric', 'Formula', 'Result'], ['Total Outstanding', '=SUM(\'Data Analysis\'!D:D)', '=SUM(\'Data Analysis\'!D:D)'], ['Average Risk Score', '=AVERAGE(\'Data Analysis\'!F:F)', '=AVERAGE(\'Data Analysis\'!F:F)'], ['High Risk Count', '=COUNTIF(\'Data Analysis\'!G:G,"High")', '=COUNTIF(\'Data Analysis\'!G:G,"High")'], ['Collection Efficiency', '=SUM(\'Data Analysis\'!H:H)/SUM(\'Data Analysis\'!D:D)', '=SUM(\'Data Analysis\'!H:H)/SUM(\'Data Analysis\'!D:D)'], ['Portfolio at Risk', '=SUMIF(\'Data Analysis\'!E:E,">30",\'Data Analysis\'!D:D)/SUM(\'Data Analysis\'!D:D)', '=SUMIF(\'Data Analysis\'!E:E,">30",\'Data Analysis\'!D:D)/SUM(\'Data Analysis\'!D:D)'] ] # Add metrics with formulas for row_idx, row_data in enumerate(metrics_data, start=4): for col_idx, value in enumerate(row_data, start=1): cell = worksheet.cell(row=row_idx, column=col_idx, value=value) if row_idx == 4: # Header row cell.style = 'header' elif col_idx == 3: # Result column with formulas if row_idx > 4: # Skip header cell.style = 'currency' if 'SUM' in str(value) else 'data' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_dashboard_worksheet(self, worksheet, report_data: Dict[str, Any]): """Create dashboard-style worksheet""" worksheet['A1'] = "Executive Dashboard" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:H1') # KPI Cards summary = report_data.get('summary', {}) # Create KPI card layout kpi_cards = [ {'title': 'Total Loans', 'value': summary.get('total_loans', 0), 'color': self.colors['primary']}, {'title': 'Total Amount', 'value': summary.get('total_amount', 0), 'color': self.colors['secondary']}, {'title': 'Collection Rate', 'value': f"{summary.get('collection_rate', 0):.1f}%", 'color': self.colors['success']}, {'title': 'At Risk Amount', 'value': summary.get('at_risk_amount', 0), 'color': self.colors['warning']} ] # Add KPI cards for i, kpi in enumerate(kpi_cards): col_start = i * 2 + 1 # Title title_cell = worksheet.cell(row=3, column=col_start, value=kpi['title']) title_cell.font = Font(bold=True, size=12) title_cell.fill = PatternFill(start_color=kpi['color'], end_color=kpi['color'], fill_type="solid") title_cell.font = Font(bold=True, color="FFFFFF") worksheet.merge_cells(f'{get_column_letter(col_start)}3:{get_column_letter(col_start+1)}3') # Value value_cell = worksheet.cell(row=4, column=col_start, value=kpi['value']) value_cell.font = Font(bold=True, size=16, color=kpi['color']) value_cell.alignment = Alignment(horizontal='center') if isinstance(kpi['value'], (int, float)) and 'Amount' in kpi['title']: value_cell.style = 'currency' worksheet.merge_cells(f'{get_column_letter(col_start)}4:{get_column_letter(col_start+1)}4') self._auto_adjust_columns(worksheet) def _apply_advanced_conditional_formatting(self, worksheet, end_row: int): """Apply advanced conditional formatting""" # Risk score color scale (column F) risk_rule = ColorScaleRule(start_type='min', start_color='00FF00', # Green mid_type='percentile', mid_value=50, mid_color='FFFF00', # Yellow end_type='max', end_color='FF0000') # Red worksheet.conditional_formatting.add(f'F4:F{end_row}', risk_rule) # Priority highlighting (column G) high_priority_rule = CellIsRule(operator='equal', formula=['"High"'], fill=PatternFill(start_color='FFC7CE', end_color='FFC7CE')) medium_priority_rule = CellIsRule(operator='equal', formula=['"Medium"'], fill=PatternFill(start_color='FFEB9C', end_color='FFEB9C')) low_priority_rule = CellIsRule(operator='equal', formula=['"Low"'], fill=PatternFill(start_color='C6EFCE', end_color='C6EFCE')) worksheet.conditional_formatting.add(f'G4:G{end_row}', high_priority_rule) worksheet.conditional_formatting.add(f'G4:G{end_row}', medium_priority_rule) worksheet.conditional_formatting.add(f'G4:G{end_row}', low_priority_rule) # Days overdue highlighting (column E) overdue_rule = CellIsRule(operator='greaterThan', formula=['30'], fill=PatternFill(start_color='FFC7CE', end_color='FFC7CE')) worksheet.conditional_formatting.add(f'E4:E{end_row}', overdue_rule) # Enhanced export function using the new service def export_enhanced_excel(report_data: Dict[str, Any], report_type: str, filters: Dict[str, Any]) -> HttpResponse: """ Enhanced Excel export with multi-worksheet support, conditional formatting, and charts Args: report_data: Complete report data report_type: Type of report filters: Applied filters Returns: HttpResponse: Excel file response """ service = EnhancedExcelExportService() return service.create_multi_worksheet_export(report_data, report_type, filters) def export_advanced_excel(report_data: Dict[str, Any], report_type: str, filters: Dict[str, Any], include_formulas: bool = True, include_pivot_tables: bool = True) -> HttpResponse: """ Advanced Excel export with formulas, pivot tables, and enhanced styling Args: report_data: Complete report data report_type: Type of report filters: Applied filters include_formulas: Whether to include calculated formulas include_pivot_tables: Whether to include pivot tables Returns: HttpResponse: Advanced Excel file response """ service = EnhancedExcelExportService() return service.create_advanced_excel_export(report_data, report_type, filters, include_formulas, include_pivot_tables) def export_loans_in_arrears_excel(report_data, filters): """Export loans in arrears report to Excel format""" # Create workbook and worksheet wb = Workbook() ws = wb.active ws.title = "Loans in Arrears Report" # Define styles header_font = Font(bold=True, color="FFFFFF") header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid") border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) # Add title ws['A1'] = "Loans in Arrears Report" ws['A1'].font = Font(size=16, bold=True) ws.merge_cells('A1:J1') # Add summary information summary = report_data.get('summary', {}) ws['A3'] = "Summary" ws['A3'].font = Font(bold=True, size=14) summary_data = [ ['Total Loans in Arrears', summary.get('total_loans_in_arrears', 0)], ['Total Arrears Amount', f"KSh {summary.get('total_arrears_amount', 0):,.2f}"], ['Average Arrears Amount', f"KSh {summary.get('average_arrears_amount', 0):,.2f}"], ['Recovery Rate', f"{summary.get('recovery_rate', 0):.1f}%"] ] for i, (label, value) in enumerate(summary_data, start=4): ws[f'A{i}'] = label ws[f'B{i}'] = value ws[f'A{i}'].font = Font(bold=True) # Add filters information ws['A9'] = "Filters Applied" ws['A9'].font = Font(bold=True, size=12) filter_info = [] if filters.get('arrears_filter'): filter_info.append(['Risk Filter', filters['arrears_filter']]) if filters.get('sort_by'): filter_info.append(['Sort By', filters['sort_by']]) if filters.get('start_date'): filter_info.append(['Start Date', filters['start_date']]) if filters.get('end_date'): filter_info.append(['End Date', filters['end_date']]) for i, (label, value) in enumerate(filter_info, start=10): ws[f'A{i}'] = label ws[f'B{i}'] = value # Add loans data loans = report_data.get('loans', []) if loans: # Headers headers = [ 'Loan Number', 'Borrower Name', 'Borrower Phone', 'Product Type', 'Repayment Method', 'Arrears Amount', 'Days Overdue', 'Risk Level', 'Last Payment Date', 'Last Payment Amount' ] start_row = 15 for col, header in enumerate(headers, start=1): cell = ws.cell(row=start_row, column=col, value=header) cell.font = header_font cell.fill = header_fill cell.border = border cell.alignment = Alignment(horizontal='center') # Data rows for row_idx, loan in enumerate(loans, start=start_row + 1): ws.cell(row=row_idx, column=1, value=loan.get('loan_number', '')) ws.cell(row=row_idx, column=2, value=loan.get('borrower_name', '')) ws.cell(row=row_idx, column=3, value=loan.get('borrower_phone', '')) ws.cell(row=row_idx, column=4, value=loan.get('loan_product', '')) ws.cell(row=row_idx, column=5, value=loan.get('repayment_method', '').title()) ws.cell(row=row_idx, column=6, value=float(loan.get('arrears_amount', 0))) ws.cell(row=row_idx, column=7, value=loan.get('days_overdue', 0)) ws.cell(row=row_idx, column=8, value=loan.get('severity_label', '')) ws.cell(row=row_idx, column=9, value=loan.get('last_payment_date', '')) ws.cell(row=row_idx, column=10, value=float(loan.get('last_payment_amount', 0)) if loan.get('last_payment_amount') else '') # Apply borders to all cells for col in range(1, 11): ws.cell(row=row_idx, column=col).border = border # Auto-adjust column widths for col in range(1, 11): column_letter = get_column_letter(col) max_length = 0 for row in range(start_row, start_row + len(loans) + 1): cell_value = ws[f'{column_letter}{row}'].value if cell_value: max_length = max(max_length, len(str(cell_value))) ws.column_dimensions[column_letter].width = min(max_length + 2, 30) # Save to BytesIO buffer = BytesIO() wb.save(buffer) buffer.seek(0) # Create HTTP response response = HttpResponse( buffer.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response['Content-Disposition'] = f'attachment; filename="loans_in_arrears_report_{timezone.now().strftime("%Y%m%d_%H%M%S")}.xlsx"' return response def export_loans_in_arrears_pdf(report_data, filters): """Export loans in arrears report to PDF format with charts""" from reportlab.graphics.shapes import Drawing from reportlab.graphics.charts.piecharts import Pie from reportlab.graphics.charts.barcharts import VerticalBarChart from reportlab.graphics.charts.legends import Legend from reportlab.lib.colors import HexColor # Create the HttpResponse object with PDF headers response = HttpResponse(content_type='application/pdf') response['Content-Disposition'] = f'attachment; filename="loans_in_arrears_report_{timezone.now().strftime("%Y%m%d_%H%M%S")}.pdf"' # Create the PDF object buffer = BytesIO() doc = SimpleDocTemplate(buffer, pagesize=A4, rightMargin=72, leftMargin=72, topMargin=72, bottomMargin=18) # Container for the 'Flowable' objects elements = [] # Get styles styles = getSampleStyleSheet() title_style = ParagraphStyle( 'CustomTitle', parent=styles['Heading1'], fontSize=18, spaceAfter=30, alignment=1 # Center alignment ) # Add title title = Paragraph("Loans in Arrears Report", title_style) elements.append(title) elements.append(Spacer(1, 12)) # Add summary information summary = report_data.get('summary', {}) summary_data = [ ['Metric', 'Value'], ['Total Loans in Arrears', str(summary.get('total_loans_in_arrears', 0))], ['Total Arrears Amount', f"KSh {summary.get('total_arrears_amount', 0):,.2f}"], ['Average Arrears Amount', f"KSh {summary.get('average_arrears_amount', 0):,.2f}"], ['Recovery Rate', f"{summary.get('recovery_rate', 0):.1f}%"] ] summary_table = Table(summary_data) summary_table.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, 0), 'Helvetica-Bold'), ('FONTSIZE', (0, 0), (-1, 0), 14), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.beige), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(summary_table) elements.append(Spacer(1, 20)) # Add charts section elements.append(Paragraph("Risk Distribution Analysis", styles['Heading2'])) elements.append(Spacer(1, 12)) # Create risk distribution pie chart severity_breakdown = report_data.get('severity_breakdown', {}) if any(severity_breakdown.values()): # Create pie chart drawing = Drawing(400, 300) pie = Pie() pie.x = 50 pie.y = 50 pie.width = 200 pie.height = 200 # Prepare data for pie chart labels = [] data = [] colors_list = [HexColor('#10B981'), HexColor('#F59E0B'), HexColor('#EF4444'), HexColor('#DC2626')] if severity_breakdown.get('low_risk_count', 0) > 0: labels.append('Low Risk') data.append(severity_breakdown['low_risk_count']) if severity_breakdown.get('medium_risk_count', 0) > 0: labels.append('Medium Risk') data.append(severity_breakdown['medium_risk_count']) if severity_breakdown.get('high_risk_count', 0) > 0: labels.append('High Risk') data.append(severity_breakdown['high_risk_count']) if severity_breakdown.get('critical_count', 0) > 0: labels.append('Critical') data.append(severity_breakdown['critical_count']) if data: pie.data = data pie.labels = labels pie.slices.strokeWidth = 0.5 pie.slices[0].fillColor = colors_list[0] if len(colors_list) > 0 else colors.green if len(colors_list) > 1: pie.slices[1].fillColor = colors_list[1] if len(colors_list) > 2: pie.slices[2].fillColor = colors_list[2] if len(colors_list) > 3: pie.slices[3].fillColor = colors_list[3] drawing.add(pie) elements.append(drawing) elements.append(Spacer(1, 20)) # Add arrears trend chart if available recovery_trend = report_data.get('recovery_trend', {}) if recovery_trend and recovery_trend.get('data'): try: import json trend_labels = json.loads(recovery_trend.get('labels', '[]')) trend_data = json.loads(recovery_trend.get('data', '[]')) if trend_labels and trend_data: elements.append(Paragraph("Arrears Trend Analysis", styles['Heading2'])) elements.append(Spacer(1, 12)) # Create bar chart for trend drawing = Drawing(400, 250) chart = VerticalBarChart() chart.x = 50 chart.y = 50 chart.width = 300 chart.height = 150 chart.data = [trend_data] chart.categoryAxis.categoryNames = trend_labels chart.valueAxis.valueMin = 0 chart.bars[0].fillColor = HexColor('#3B82F6') chart.bars[0].strokeColor = HexColor('#1E40AF') chart.bars[0].strokeWidth = 1 drawing.add(chart) elements.append(drawing) elements.append(Spacer(1, 20)) except Exception as e: print(f"Error creating trend chart: {e}") # Add filters information if any(filters.values()): filter_data = [['Filter', 'Value']] if filters.get('arrears_filter'): filter_data.append(['Risk Filter', filters['arrears_filter']]) if filters.get('sort_by'): filter_data.append(['Sort By', filters['sort_by']]) if filters.get('start_date'): filter_data.append(['Start Date', filters['start_date']]) if filters.get('end_date'): filter_data.append(['End Date', filters['end_date']]) if len(filter_data) > 1: filter_table = Table(filter_data) filter_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.lightblue), ('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), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("Filters Applied", styles['Heading2'])) elements.append(filter_table) elements.append(Spacer(1, 20)) # Add loans data loans = report_data.get('loans', []) if loans: elements.append(Paragraph("Loans in Arrears Details", styles['Heading2'])) elements.append(Spacer(1, 12)) # Prepare data for table loans_data = [['Loan Number', 'Borrower', 'Product', 'Repayment', 'Amount', 'Days Overdue', 'Risk Level']] for loan in loans: loans_data.append([ loan.get('loan_number', ''), loan.get('borrower_name', ''), loan.get('loan_product', ''), loan.get('repayment_method', '').title(), f"KSh {float(loan.get('arrears_amount', 0)):,.0f}", str(loan.get('days_overdue', 0)), loan.get('severity_label', '') ]) # Create table loans_table = Table(loans_data) loans_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), 10), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.beige), ('GRID', (0, 0), (-1, -1), 1, colors.black), ('FONTSIZE', (0, 1), (-1, -1), 8), ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.lightgrey]) ])) elements.append(loans_table) else: elements.append(Paragraph("No loans in arrears found.", styles['Normal'])) # Build PDF doc.build(elements) # Get the value of the BytesIO buffer and write it to the response pdf = buffer.getvalue() buffer.close() response.write(pdf) return response def _create_risk_analysis_sheet(self, worksheet, report_data: Dict[str, Any]): """Create risk analysis worksheet""" worksheet['A1'] = "Risk Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Risk breakdown data risk_breakdown = report_data.get('severity_breakdown', {}) # Risk summary table worksheet['A3'] = "Risk Level Summary" worksheet['A3'].style = 'subtitle_style' risk_headers = ['Risk Level', 'Count', 'Percentage', 'Total Amount', 'Average Amount'] for col, header in enumerate(risk_headers, start=1): cell = worksheet.cell(row=4, column=col, value=header) cell.style = 'header' # Risk data total_count = sum(risk_breakdown.values()) if risk_breakdown else 1 risk_levels = ['Low Risk', 'Medium Risk', 'High Risk', 'Critical'] for row, risk_level in enumerate(risk_levels, start=5): count = risk_breakdown.get(f"{risk_level.lower().replace(' ', '_')}_count", 0) percentage = (count / total_count) * 100 if total_count > 0 else 0 worksheet.cell(row=row, column=1, value=risk_level).style = 'data' worksheet.cell(row=row, column=2, value=count).style = 'data' worksheet.cell(row=row, column=3, value=f"{percentage:.1f}%").style = 'data' worksheet.cell(row=row, column=4, value=count * 50000).style = 'currency' # Sample calculation worksheet.cell(row=row, column=5, value=50000).style = 'currency' # Sample average self._auto_adjust_columns(worksheet) def _create_recovery_tracking_sheet(self, worksheet, report_data: Dict[str, Any]): """Create recovery tracking worksheet""" worksheet['A1'] = "Recovery Tracking" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:H1') # Recovery metrics worksheet['A3'] = "Recovery Metrics" worksheet['A3'].style = 'subtitle_style' recovery_headers = ['Metric', 'Current Month', 'Previous Month', 'Change', 'Target', 'Achievement'] for col, header in enumerate(recovery_headers, start=1): cell = worksheet.cell(row=4, column=col, value=header) cell.style = 'header' # Sample recovery data recovery_metrics = [ ['Recovery Rate', '75%', '70%', '+5%', '80%', '94%'], ['Amount Recovered', 'KES 2,500,000', 'KES 2,200,000', '+13.6%', 'KES 3,000,000', '83%'], ['Cases Resolved', '45', '38', '+18.4%', '50', '90%'], ['Average Recovery Time', '15 days', '18 days', '-16.7%', '12 days', '80%'] ] for row, metrics in enumerate(recovery_metrics, start=5): for col, value in enumerate(metrics, start=1): cell = worksheet.cell(row=row, column=col, value=value) if col == 1: cell.style = 'data' elif 'KES' in str(value): cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_urgency_analysis_sheet(self, worksheet, report_data: Dict[str, Any]): """Create urgency analysis worksheet for loans due""" worksheet['A1'] = "Urgency Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') summary = report_data.get('summary', {}) # Urgency breakdown worksheet['A3'] = "Due Date Urgency Breakdown" worksheet['A3'].style = 'subtitle_style' urgency_headers = ['Urgency Level', 'Count', 'Amount', 'Percentage', 'Action Required'] for col, header in enumerate(urgency_headers, start=1): cell = worksheet.cell(row=4, column=col, value=header) cell.style = 'header' urgency_data = [ ['Due Today', summary.get('today_due_count', 0), summary.get('today_due_amount', 0), '25%', 'Immediate Contact'], ['Due Tomorrow', summary.get('tomorrow_due_count', 0), summary.get('tomorrow_due_amount', 0), '20%', 'Priority Contact'], ['Due This Week', summary.get('week_due_count', 0), summary.get('week_due_amount', 0), '35%', 'Schedule Contact'], ['Due Later', summary.get('later_due_count', 0), summary.get('later_due_amount', 0), '20%', 'Monitor'] ] for row, data in enumerate(urgency_data, start=5): for col, value in enumerate(data, start=1): cell = worksheet.cell(row=row, column=col, value=value) if col == 3: # Amount column cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_aging_analysis_sheet(self, worksheet, report_data: Dict[str, Any]): """Create aging analysis worksheet for delinquent loans""" worksheet['A1'] = "Aging Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Aging buckets worksheet['A3'] = "Delinquency Aging Buckets" worksheet['A3'].style = 'subtitle_style' aging_headers = ['Days Overdue', 'Count', 'Amount', 'Percentage', 'Collection Strategy'] for col, header in enumerate(aging_headers, start=1): cell = worksheet.cell(row=4, column=col, value=header) cell.style = 'header' aging_buckets = report_data.get('aging_buckets', {}) aging_data = [ ['1-30 days', aging_buckets.get('1-30', 0), aging_buckets.get('1-30', 0) * 25000, '40%', 'Phone calls'], ['31-60 days', aging_buckets.get('31-60', 0), aging_buckets.get('31-60', 0) * 35000, '30%', 'Formal notices'], ['61-90 days', aging_buckets.get('61-90', 0), aging_buckets.get('61-90', 0) * 45000, '20%', 'Field visits'], ['90+ days', aging_buckets.get('90+', 0), aging_buckets.get('90+', 0) * 55000, '10%', 'Legal action'] ] for row, data in enumerate(aging_data, start=5): for col, value in enumerate(data, start=1): cell = worksheet.cell(row=row, column=col, value=value) if col == 3: # Amount column cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_collection_strategy_sheet(self, worksheet, report_data: Dict[str, Any]): """Create collection strategy worksheet""" worksheet['A1'] = "Collection Strategy" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:G1') # Collection action plan worksheet['A3'] = "Collection Action Plan" worksheet['A3'].style = 'subtitle_style' strategy_headers = ['Priority', 'Loan Count', 'Amount', 'Action', 'Timeline', 'Responsible', 'Expected Recovery'] for col, header in enumerate(strategy_headers, start=1): cell = worksheet.cell(row=4, column=col, value=header) cell.style = 'header' strategy_data = [ ['High', 15, 750000, 'Immediate contact + field visit', '1-3 days', 'Senior Officer', '80%'], ['Medium', 25, 1250000, 'Phone calls + SMS reminders', '3-7 days', 'Loan Officer', '70%'], ['Low', 35, 875000, 'SMS reminders + letters', '7-14 days', 'Collection Team', '60%'], ['Monitor', 20, 500000, 'Regular follow-up', '14-30 days', 'Junior Officer', '50%'] ] for row, data in enumerate(strategy_data, start=5): for col, value in enumerate(data, start=1): cell = worksheet.cell(row=row, column=col, value=value) if col == 3: # Amount column cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_revenue_analysis_sheet(self, worksheet, report_data: Dict[str, Any]): """Create revenue analysis worksheet for processing fees""" worksheet['A1'] = "Revenue Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Monthly revenue trend worksheet['A3'] = "Monthly Revenue Trend" worksheet['A3'].style = 'subtitle_style' revenue_headers = ['Month', 'Revenue', 'Growth %', 'Target', 'Achievement %', 'Variance'] for col, header in enumerate(revenue_headers, start=1): cell = worksheet.cell(row=4, column=col, value=header) cell.style = 'header' # Sample monthly data monthly_data = [ ['Jan 2024', 450000, '5%', 500000, '90%', -50000], ['Feb 2024', 520000, '15.6%', 550000, '94.5%', -30000], ['Mar 2024', 580000, '11.5%', 600000, '96.7%', -20000], ['Apr 2024', 620000, '6.9%', 650000, '95.4%', -30000] ] for row, data in enumerate(monthly_data, start=5): for col, value in enumerate(data, start=1): cell = worksheet.cell(row=row, column=col, value=value) if col in [2, 4, 6]: # Amount columns cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_product_performance_sheet(self, worksheet, report_data: Dict[str, Any]): """Create product performance worksheet""" worksheet['A1'] = "Product Performance" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Product breakdown worksheet['A3'] = "Product Performance Breakdown" worksheet['A3'].style = 'subtitle_style' product_headers = ['Product Type', 'Count', 'Revenue', 'Average Fee', 'Market Share', 'Growth Rate'] for col, header in enumerate(product_headers, start=1): cell = worksheet.cell(row=4, column=col, value=header) cell.style = 'header' product_data = [ ['Personal Loans', 150, 375000, 2500, '35%', '12%'], ['Business Loans', 80, 320000, 4000, '30%', '8%'], ['Emergency Loans', 120, 180000, 1500, '20%', '15%'], ['Asset Finance', 50, 200000, 4000, '15%', '5%'] ] for row, data in enumerate(product_data, start=5): for col, value in enumerate(data, start=1): cell = worksheet.cell(row=row, column=col, value=value) if col in [3, 4]: # Amount columns cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_income_analysis_sheet(self, worksheet, report_data: Dict[str, Any]): """Create income analysis worksheet for interest income""" worksheet['A1'] = "Interest Income Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Interest income breakdown worksheet['A3'] = "Interest Income Breakdown" worksheet['A3'].style = 'subtitle_style' income_headers = ['Period', 'Interest Income', 'Principal', 'Interest Rate', 'Yield %', 'Performance'] for col, header in enumerate(income_headers, start=1): cell = worksheet.cell(row=4, column=col, value=header) cell.style = 'header' income_data = [ ['Q1 2024', 1250000, 25000000, '15%', '5.0%', 'Above Target'], ['Q2 2024', 1380000, 27000000, '15%', '5.1%', 'Above Target'], ['Q3 2024', 1420000, 28500000, '15%', '4.98%', 'On Target'], ['Q4 2024', 1500000, 30000000, '15%', '5.0%', 'Above Target'] ] for row, data in enumerate(income_data, start=5): for col, value in enumerate(data, start=1): cell = worksheet.cell(row=row, column=col, value=value) if col in [2, 3]: # Amount columns cell.style = 'currency' else: cell.style = 'data' self._auto_adjust_columns(worksheet) def _create_product_breakdown_sheet(self, worksheet, report_data: Dict[str, Any]): """Create product breakdown worksheet""" worksheet['A1'] = "Product Breakdown" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') product_breakdown = report_data.get('product_breakdown', {}) # Product performance table worksheet['A3'] = "Product Performance Summary" worksheet['A3'].style = 'subtitle_style' breakdown_headers = ['Product', 'Income', 'Percentage', 'Loans Count', 'Average Income', 'Trend'] for col, header in enumerate(breakdown_headers, start=1): cell = worksheet.cell(row=4, column=col, value=header) cell.style = 'header' total_income = sum(product_breakdown.values()) if product_breakdown else 1 for row, (product, income) in enumerate(product_breakdown.items(), start=5): percentage = (income / total_income) * 100 if total_income > 0 else 0 worksheet.cell(row=row, column=1, value=product).style = 'data' worksheet.cell(row=row, column=2, value=float(income)).style = 'currency' worksheet.cell(row=row, column=3, value=f"{percentage:.1f}%").style = 'data' worksheet.cell(row=row, column=4, value=25).style = 'data' # Sample count worksheet.cell(row=row, column=5, value=float(income)/25).style = 'currency' worksheet.cell(row=row, column=6, value="↗").style = 'data' # Sample trend self._auto_adjust_columns(worksheet) def _create_general_data_sheet(self, worksheet, report_data: Dict[str, Any], report_type: str): """Create general data sheet for other report types""" worksheet['A1'] = f"{report_type.replace('_', ' ').title()} Data" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') # Add summary data if available summary = report_data.get('summary', {}) if summary: worksheet['A3'] = "Summary Information" worksheet['A3'].style = 'subtitle_style' row = 4 for key, value in summary.items(): worksheet.cell(row=row, column=1, value=key.replace('_', ' ').title()).style = 'data' cell = worksheet.cell(row=row, column=2, value=value) if isinstance(value, (int, float, Decimal)) and 'amount' in key.lower(): cell.style = 'currency' else: cell.style = 'data' row += 1 self._auto_adjust_columns(worksheet) def _create_pivot_analysis_sheet(self, worksheet, loans_data: List[Dict[str, Any]]): """Create pivot analysis worksheet""" worksheet['A1'] = "Pivot Analysis" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:F1') if not loans_data: worksheet['A3'] = "No data available for pivot analysis" return # Create summary pivot table worksheet['A3'] = "Summary by Product Type" worksheet['A3'].style = 'subtitle_style' pivot_headers = ['Product Type', 'Count', 'Total Amount', 'Average Amount', 'Percentage'] for col, header in enumerate(pivot_headers, start=1): cell = worksheet.cell(row=4, column=col, value=header) cell.style = 'header' # Group data by product type product_summary = {} total_amount = 0 for loan in loans_data: product = loan.get('loan_product', 'Unknown') amount = float(loan.get('arrears_amount', 0)) if 'arrears_amount' in loan else float(loan.get('due_amount', 0)) if product not in product_summary: product_summary[product] = {'count': 0, 'total_amount': 0} product_summary[product]['count'] += 1 product_summary[product]['total_amount'] += amount total_amount += amount # Add pivot data row = 5 for product, data in product_summary.items(): count = data['count'] amount = data['total_amount'] avg_amount = amount / count if count > 0 else 0 percentage = (amount / total_amount) * 100 if total_amount > 0 else 0 worksheet.cell(row=row, column=1, value=product).style = 'data' worksheet.cell(row=row, column=2, value=count).style = 'data' worksheet.cell(row=row, column=3, value=amount).style = 'currency' worksheet.cell(row=row, column=4, value=avg_amount).style = 'currency' worksheet.cell(row=row, column=5, value=f"{percentage:.1f}%").style = 'data' row += 1 self._auto_adjust_columns(worksheet) def _create_filters_metadata_sheet(self, worksheet, filters: Dict[str, Any], report_data: Dict[str, Any]): """Create filters and metadata worksheet""" worksheet['A1'] = "Filters & Metadata" worksheet['A1'].style = 'title_style' worksheet.merge_cells('A1:D1') # Report generation info worksheet['A3'] = "Report Generation Information" worksheet['A3'].style = 'subtitle_style' info_data = [ ['Generated On', datetime.now().strftime('%Y-%m-%d %H:%M:%S')], ['Generated By', 'Loan Management System'], ['Report Version', '2.0'], ['Data Source', 'Production Database'] ] for row, (label, value) in enumerate(info_data, start=4): worksheet.cell(row=row, column=1, value=label).font = Font(bold=True) worksheet.cell(row=row, column=2, value=value).style = 'data' # Applied filters if filters: worksheet['A9'] = "Applied Filters" worksheet['A9'].style = 'subtitle_style' row = 10 for key, value in filters.items(): if value: # Only show non-empty filters worksheet.cell(row=row, column=1, value=key.replace('_', ' ').title()).font = Font(bold=True) worksheet.cell(row=row, column=2, value=str(value)).style = 'data' row += 1 # Data summary summary = report_data.get('summary', {}) if summary: worksheet['A15'] = "Data Summary" worksheet['A15'].style = 'subtitle_style' row = 16 for key, value in summary.items(): worksheet.cell(row=row, column=1, value=key.replace('_', ' ').title()).font = Font(bold=True) cell = worksheet.cell(row=row, column=2, value=value) if isinstance(value, (int, float, Decimal)) and 'amount' in key.lower(): cell.style = 'currency' else: cell.style = 'data' row += 1 self._auto_adjust_columns(worksheet) def _create_urgency_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int: """Create urgency distribution chart for loans due""" summary = report_data.get('summary', {}) # Data for chart urgency_data = [ ['Urgency', 'Count'], ['Due Today', summary.get('today_due_count', 0)], ['Due Tomorrow', summary.get('tomorrow_due_count', 0)], ['Due This Week', summary.get('week_due_count', 0)], ['Due Later', summary.get('later_due_count', 0)] ] # Add data to worksheet for row_idx, row_data in enumerate(urgency_data, start=start_row): for col_idx, value in enumerate(row_data, start=1): worksheet.cell(row=row_idx, column=col_idx, value=value) # Create bar chart chart = BarChart() chart.title = "Loans Due by Urgency" chart.x_axis.title = "Urgency Level" chart.y_axis.title = "Number of Loans" # Data references data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+4) labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+4) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) # Position chart worksheet.add_chart(chart, f"D{start_row}") return start_row + 20 def _create_due_amounts_trend_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int: """Create due amounts trend chart""" # Sample trend data trend_data = [ ['Date', 'Due Amount'], ['2024-01-01', 500000], ['2024-01-02', 750000], ['2024-01-03', 600000], ['2024-01-04', 800000], ['2024-01-05', 650000] ] # Add data to worksheet for row_idx, row_data in enumerate(trend_data, start=start_row): for col_idx, value in enumerate(row_data, start=1): worksheet.cell(row=row_idx, column=col_idx, value=value) # Create line chart chart = LineChart() chart.title = "Due Amounts Trend" chart.x_axis.title = "Date" chart.y_axis.title = "Amount (KES)" # Data references data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+5) labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+5) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) # Position chart worksheet.add_chart(chart, f"D{start_row}") return start_row + 20 def _create_aging_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int: """Create aging analysis chart""" aging_buckets = report_data.get('aging_buckets', { '1-30 days': 15, '31-60 days': 10, '61-90 days': 8, '90+ days': 5 }) # Data for chart chart_data = [['Age Bucket', 'Count']] chart_data.extend(list(aging_buckets.items())) # Add data to worksheet for row_idx, row_data in enumerate(chart_data, start=start_row): for col_idx, value in enumerate(row_data, start=1): worksheet.cell(row=row_idx, column=col_idx, value=value) # Create bar chart chart = BarChart() chart.title = "Delinquency Aging Analysis" chart.x_axis.title = "Days Overdue" chart.y_axis.title = "Number of Loans" # Data references data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+len(aging_buckets)) labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+len(aging_buckets)) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) # Position chart worksheet.add_chart(chart, f"D{start_row}") return start_row + 20 def _create_revenue_trend_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int: """Create revenue trend chart""" # Sample revenue data revenue_data = [ ['Month', 'Revenue'], ['Jan', 450000], ['Feb', 520000], ['Mar', 580000], ['Apr', 620000], ['May', 680000] ] # Add data to worksheet for row_idx, row_data in enumerate(revenue_data, start=start_row): for col_idx, value in enumerate(row_data, start=1): worksheet.cell(row=row_idx, column=col_idx, value=value) # Create line chart chart = LineChart() chart.title = "Revenue Trend" chart.x_axis.title = "Month" chart.y_axis.title = "Revenue (KES)" # Data references data = Reference(worksheet, min_col=2, min_row=start_row, max_row=start_row+5) labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+5) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) # Position chart worksheet.add_chart(chart, f"D{start_row}") return start_row + 20 def _create_product_performance_chart(self, worksheet, report_data: Dict[str, Any], start_row: int) -> int: """Create product performance chart""" product_performance = report_data.get('product_performance', { 'Personal Loans': 375000, 'Business Loans': 320000, 'Emergency Loans': 180000, 'Asset Finance': 200000 }) # Data for chart chart_data = [['Product', 'Income']] chart_data.extend(list(product_performance.items())) # Add data to worksheet for row_idx, row_data in enumerate(chart_data, start=start_row): for col_idx, value in enumerate(row_data, start=1): worksheet.cell(row=row_idx, column=col_idx, value=value) # Create pie chart chart = PieChart() chart.title = "Product Performance" # Data references data = Reference(worksheet, min_col=2, min_row=start_row+1, max_row=start_row+len(product_performance)) labels = Reference(worksheet, min_col=1, min_row=start_row+1, max_row=start_row+len(product_performance)) chart.add_data(data) chart.set_categories(labels) # Styling chart.dataLabels = DataLabelList() chart.dataLabels.showPercent = True # Position chart worksheet.add_chart(chart, f"D{start_row}") return start_row + 20