""" Export Service for PDF and Excel Generation This service provides centralized export functionality for all report pages, generating PDF and Excel files with consistent formatting and filter metadata. """ from django.http import HttpResponse from django.utils import timezone from io import BytesIO from decimal import Decimal from datetime import date, datetime from typing import Dict, List, Any, Optional import logging # PDF generation from reportlab.lib.pagesizes import letter, A4 from reportlab.lib import colors from reportlab.lib.units import inch from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle from reportlab.lib.enums import TA_CENTER, TA_LEFT, TA_RIGHT from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, PageBreak # Excel generation from openpyxl import Workbook from openpyxl.styles import Font, Alignment, PatternFill, Border, Side from openpyxl.utils import get_column_letter logger = logging.getLogger(__name__) class ReportExportService: """ Service class for exporting reports to PDF and Excel formats. Provides consistent formatting, filter metadata inclusion, and handles large datasets with appropriate pagination or limits. """ # Maximum rows per export to prevent memory issues MAX_EXPORT_ROWS = 10000 def __init__(self): """Initialize the export service""" self.pdf_styles = getSampleStyleSheet() self._setup_custom_styles() def _setup_custom_styles(self): """Set up custom paragraph styles for PDF generation""" # Title style self.pdf_styles.add(ParagraphStyle( name='ReportTitle', parent=self.pdf_styles['Heading1'], fontSize=18, textColor=colors.HexColor('#2c3e50'), spaceAfter=20, alignment=TA_CENTER, fontName='Helvetica-Bold' )) # Subtitle style self.pdf_styles.add(ParagraphStyle( name='ReportSubtitle', parent=self.pdf_styles['Heading2'], fontSize=14, textColor=colors.HexColor('#3498db'), spaceAfter=12, alignment=TA_LEFT, fontName='Helvetica-Bold' )) # Filter info style self.pdf_styles.add(ParagraphStyle( name='FilterInfo', parent=self.pdf_styles['Normal'], fontSize=10, textColor=colors.HexColor('#5a5c69'), spaceAfter=10, alignment=TA_LEFT, fontName='Helvetica' )) def export_to_pdf(self, report_data: Dict[str, Any], report_type: str, filters: Dict[str, Any]) -> HttpResponse: """ Export report data to PDF format with comprehensive error handling. Args: report_data: Dictionary containing report data (must include 'loans' key) report_type: Type of report (e.g., 'loans_due', 'processing_fees') filters: Dictionary of applied filters Returns: HttpResponse with PDF content and appropriate headers, or error response """ try: # Validate input data if not report_data: logger.warning("Empty report_data provided for PDF export") report_data = {'loans': []} if 'loans' not in report_data: logger.warning("Missing 'loans' key in report_data") report_data['loans'] = [] # Create PDF buffer buffer = BytesIO() # Create PDF document doc = SimpleDocTemplate( buffer, pagesize=A4, rightMargin=0.75*inch, leftMargin=0.75*inch, topMargin=1*inch, bottomMargin=0.75*inch ) # Build PDF content story = [] # Add title title = self._format_report_title(report_type) story.append(Paragraph(title, self.pdf_styles['ReportTitle'])) story.append(Spacer(1, 0.3*inch)) # Add generation info generation_info = f"Generated on: {self.format_date(datetime.now())}" story.append(Paragraph(generation_info, self.pdf_styles['FilterInfo'])) story.append(Spacer(1, 0.2*inch)) # Add filter metadata if filters: story.extend(self._create_filter_metadata_section(filters)) story.append(Spacer(1, 0.3*inch)) # Add data table loans_data = report_data.get('loans', []) if loans_data: # Limit data if too large if len(loans_data) > self.MAX_EXPORT_ROWS: loans_data = loans_data[:self.MAX_EXPORT_ROWS] warning = f"Note: Export limited to {self.MAX_EXPORT_ROWS} records" story.append(Paragraph(warning, self.pdf_styles['FilterInfo'])) story.append(Spacer(1, 0.1*inch)) data_table = self._create_pdf_data_table(loans_data, report_type) story.append(data_table) else: story.append(Paragraph("No data available for export.", self.pdf_styles['Normal'])) # Build PDF doc.build(story) # Get PDF content buffer.seek(0) pdf_content = buffer.getvalue() buffer.close() # Create HTTP response response = HttpResponse(pdf_content, content_type='application/pdf') filename = f"{report_type}_report_{timezone.now().strftime('%Y%m%d_%H%M%S')}.pdf" response['Content-Disposition'] = f'attachment; filename="{filename}"' return response except Exception as e: logger.error(f"Error generating PDF export: {str(e)}") return self._create_error_response('PDF', str(e)) def export_to_excel(self, report_data: Dict[str, Any], report_type: str, filters: Dict[str, Any]) -> HttpResponse: """ Export report data to Excel format with comprehensive error handling. Args: report_data: Dictionary containing report data (must include 'loans' key) report_type: Type of report (e.g., 'loans_due', 'processing_fees') filters: Dictionary of applied filters Returns: HttpResponse with Excel content and appropriate headers, or error response """ try: # Validate input data if not report_data: logger.warning("Empty report_data provided for Excel export") report_data = {'loans': []} if 'loans' not in report_data: logger.warning("Missing 'loans' key in report_data") report_data['loans'] = [] # Create workbook workbook = Workbook() sheet = workbook.active sheet.title = "Report Data" # Add title title = self._format_report_title(report_type) sheet['A1'] = title sheet['A1'].font = Font(size=16, bold=True, color='2c3e50') sheet['A1'].alignment = Alignment(horizontal='center') sheet.merge_cells('A1:F1') # Add generation info sheet['A2'] = f"Generated on: {self.format_date(datetime.now())}" sheet['A2'].font = Font(size=10, color='5a5c69') current_row = 4 # Add filter metadata if filters: current_row = self._add_excel_filter_metadata(sheet, filters, current_row) current_row += 1 # Add data loans_data = report_data.get('loans', []) if loans_data: # Limit data if too large if len(loans_data) > self.MAX_EXPORT_ROWS: loans_data = loans_data[:self.MAX_EXPORT_ROWS] sheet[f'A{current_row}'] = f"Note: Export limited to {self.MAX_EXPORT_ROWS} records" sheet[f'A{current_row}'].font = Font(italic=True, color='e74c3c') current_row += 2 current_row = self._add_excel_data_table(sheet, loans_data, report_type, current_row) else: sheet[f'A{current_row}'] = "No data available for export." sheet[f'A{current_row}'].font = Font(italic=True) # Auto-adjust column widths self._auto_adjust_excel_columns(sheet) # Save to buffer buffer = BytesIO() workbook.save(buffer) buffer.seek(0) excel_content = buffer.getvalue() buffer.close() # Create HTTP response response = HttpResponse( excel_content, content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) filename = f"{report_type}_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 generating Excel export: {str(e)}") return self._create_error_response('Excel', str(e)) def format_currency(self, amount: Any) -> str: """ Format currency values with two decimal places. Args: amount: Numeric value to format Returns: Formatted currency string (e.g., "KES 10,000.00") """ try: if amount is None: return "KES 0.00" # Convert to Decimal for precision if isinstance(amount, str): amount = Decimal(amount) elif not isinstance(amount, Decimal): amount = Decimal(str(amount)) # Format with two decimal places and thousands separator formatted = f"KES {amount:,.2f}" return formatted except (ValueError, TypeError, Exception) as e: logger.warning(f"Error formatting currency {amount}: {str(e)}") return "KES 0.00" def format_date(self, date_value: Any) -> str: """ Format date values consistently. Args: date_value: Date or datetime object to format Returns: Formatted date string (e.g., "2024-01-15") """ try: if date_value is None: return "N/A" # Handle datetime objects if isinstance(date_value, datetime): return date_value.strftime('%Y-%m-%d %H:%M:%S') # Handle date objects if isinstance(date_value, date): return date_value.strftime('%Y-%m-%d') # Handle string dates if isinstance(date_value, str): return date_value return str(date_value) except Exception as e: logger.warning(f"Error formatting date {date_value}: {str(e)}") return "N/A" def _format_report_title(self, report_type: str) -> str: """Format report type into readable title""" return report_type.replace('_', ' ').title() + " Report" def _create_filter_metadata_section(self, filters: Dict[str, Any]) -> List: """Create filter metadata section for PDF""" elements = [] elements.append(Paragraph("Applied Filters:", self.pdf_styles['ReportSubtitle'])) filter_lines = [] if filters.get('start_date') and filters.get('end_date'): filter_lines.append(f"Date Range: {self.format_date(filters['start_date'])} to {self.format_date(filters['end_date'])}") if filters.get('product_id'): filter_lines.append(f"Loan Product: {filters['product_id']}") if filters.get('branch_id'): filter_lines.append(f"Branch: {filters['branch_id']}") if filters.get('gender'): filter_lines.append(f"Gender: {filters['gender']}") if filters.get('period'): filter_lines.append(f"Period: {filters['period']}") if not filter_lines: filter_lines.append("No filters applied") for line in filter_lines: elements.append(Paragraph(f"• {line}", self.pdf_styles['FilterInfo'])) return elements def _create_pdf_data_table(self, loans_data: List[Dict], report_type: str) -> Table: """Create data table for PDF""" # Determine columns based on report type if report_type == 'loans_due': headers = ['Loan Number', 'Borrower', 'Amount', 'Due Date', 'Outstanding'] keys = ['loan_number', 'borrower_name', 'principal_amount', 'due_date', 'outstanding_amount'] elif report_type == 'processing_fees': headers = ['Loan Number', 'Borrower', 'Processing Fee', 'Date'] keys = ['loan_number', 'borrower_name', 'processing_fee', 'disbursement_date'] elif report_type == 'interest_income': headers = ['Loan Number', 'Borrower', 'Interest Amount', 'Date'] keys = ['loan_number', 'borrower_name', 'interest_amount', 'disbursement_date'] else: # Generic columns if loans_data: headers = list(loans_data[0].keys()) keys = headers else: headers = ['No Data'] keys = [] # Build table data table_data = [headers] for loan in loans_data: row = [] for key in keys: value = loan.get(key, '') # Format based on key type if 'amount' in key.lower() or 'fee' in key.lower(): row.append(self.format_currency(value)) elif 'date' in key.lower(): row.append(self.format_date(value)) else: row.append(str(value) if value is not None else '') table_data.append(row) # Create table table = Table(table_data) # Style table table.setStyle(TableStyle([ # Header styling ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#2c3e50')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, 0), 'CENTER'), ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), ('FONTSIZE', (0, 0), (-1, 0), 11), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), # Data styling ('BACKGROUND', (0, 1), (-1, -1), colors.beige), ('TEXTCOLOR', (0, 1), (-1, -1), colors.black), ('ALIGN', (0, 1), (-1, -1), 'LEFT'), ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 1), (-1, -1), 9), ('GRID', (0, 0), (-1, -1), 1, colors.black), ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'), ])) return table def _add_excel_filter_metadata(self, sheet, filters: Dict[str, Any], start_row: int) -> int: """Add filter metadata to Excel sheet""" sheet[f'A{start_row}'] = "Applied Filters:" sheet[f'A{start_row}'].font = Font(bold=True, size=12, color='3498db') start_row += 1 if filters.get('start_date') and filters.get('end_date'): sheet[f'A{start_row}'] = f"Date Range: {self.format_date(filters['start_date'])} to {self.format_date(filters['end_date'])}" start_row += 1 if filters.get('product_id'): sheet[f'A{start_row}'] = f"Loan Product: {filters['product_id']}" start_row += 1 if filters.get('branch_id'): sheet[f'A{start_row}'] = f"Branch: {filters['branch_id']}" start_row += 1 if filters.get('gender'): sheet[f'A{start_row}'] = f"Gender: {filters['gender']}" start_row += 1 if filters.get('period'): sheet[f'A{start_row}'] = f"Period: {filters['period']}" start_row += 1 return start_row def _add_excel_data_table(self, sheet, loans_data: List[Dict], report_type: str, start_row: int) -> int: """Add data table to Excel sheet""" # Determine columns based on report type if report_type == 'loans_due': headers = ['Loan Number', 'Borrower', 'Amount', 'Due Date', 'Outstanding'] keys = ['loan_number', 'borrower_name', 'principal_amount', 'due_date', 'outstanding_amount'] elif report_type == 'processing_fees': headers = ['Loan Number', 'Borrower', 'Processing Fee', 'Date'] keys = ['loan_number', 'borrower_name', 'processing_fee', 'disbursement_date'] elif report_type == 'interest_income': headers = ['Loan Number', 'Borrower', 'Interest Amount', 'Date'] keys = ['loan_number', 'borrower_name', 'interest_amount', 'disbursement_date'] else: # Generic columns if loans_data: headers = list(loans_data[0].keys()) keys = headers else: return start_row # Add headers for col_idx, header in enumerate(headers, start=1): cell = sheet.cell(row=start_row, column=col_idx, value=header) cell.font = Font(bold=True, color='FFFFFF') cell.fill = PatternFill(start_color='2c3e50', end_color='2c3e50', fill_type='solid') cell.alignment = Alignment(horizontal='center', vertical='center') cell.border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) start_row += 1 # Add data rows for loan in loans_data: for col_idx, key in enumerate(keys, start=1): value = loan.get(key, '') # Format based on key type if 'amount' in key.lower() or 'fee' in key.lower(): formatted_value = self.format_currency(value) elif 'date' in key.lower(): formatted_value = self.format_date(value) else: formatted_value = str(value) if value is not None else '' cell = sheet.cell(row=start_row, column=col_idx, value=formatted_value) cell.alignment = Alignment(horizontal='left', vertical='center') cell.border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) start_row += 1 return start_row def _auto_adjust_excel_columns(self, sheet): """Auto-adjust column widths based on content""" for column in sheet.columns: max_length = 0 column_letter = get_column_letter(column[0].column) for cell in column: try: if cell.value: max_length = max(max_length, len(str(cell.value))) except: pass adjusted_width = min(max_length + 2, 50) sheet.column_dimensions[column_letter].width = adjusted_width def _create_error_response(self, export_type: str, error_message: str) -> HttpResponse: """Create error response for failed exports""" response = HttpResponse( f"Error generating {export_type} export: {error_message}", content_type='text/plain', status=500 ) return response