""" Simple Reports Service for Dashboard Provides basic analytics without complex calculations that might fail """ from django.db import models from django.db.models import Sum, Count, Q, Avg, F, Case, When, Value, DecimalField, Max from django.utils import timezone from datetime import datetime, timedelta, date from decimal import Decimal from loans.models import Loan, LoanApplication, Repayment from users.models import CustomUser class SimpleReportsService: """ Simple service class for generating basic reports and analytics """ def __init__(self): self._refresh_dates() def _refresh_dates(self): """Refresh date references — called at the start of each request to avoid stale singleton dates""" self.today = timezone.now().date() self.current_month_start = self.today.replace(day=1) def get_summary_metrics(self, branch_id=None, portfolio_manager_id=None): """Get basic summary metrics for the dashboard""" # Base queryset - exclude soft-deleted and rolled-over loans loans_qs = Loan.objects.filter( status='active', is_deleted=False, is_rolled_over=False ).exclude(status='rolled_over') # Apply portfolio-based filtering if portfolio_manager_id: loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id) elif branch_id: # Branch ID is a UUID, no need to convert to int loans_qs = loans_qs.filter(borrower__branch_id=branch_id) # Basic counts and sums total_active_loans = loans_qs.count() # Portfolio value (sum of principal amounts) portfolio_data = loans_qs.aggregate( total_portfolio=Sum('principal_amount'), total_disbursed=Sum('total_amount') ) total_portfolio_value = portfolio_data['total_portfolio'] or Decimal('0.00') total_disbursed = portfolio_data['total_disbursed'] or Decimal('0.00') # Calculate total collected and outstanding by iterating (since amount_paid is a property) total_collected = Decimal('0.00') total_outstanding = Decimal('0.00') for loan in loans_qs: loan_total = loan.total_amount or Decimal('0.00') loan_paid = loan.amount_paid or Decimal('0.00') total_collected += loan_paid total_outstanding += (loan_total - loan_paid) # Collection rate collection_rate = Decimal('0.00') if total_disbursed > 0: collection_rate = (total_collected / total_disbursed) * 100 # Loans due today using repayment scheduler from loans.repayment_scheduler import RepaymentScheduler loans_due_today = 0 overdue_loans = 0 for loan in loans_qs: next_due_date = RepaymentScheduler.get_next_payment_due_date(loan) if next_due_date == self.today: loans_due_today += 1 if RepaymentScheduler.is_loan_in_arrears(loan): overdue_loans += 1 return { 'total_active_loans': total_active_loans, 'total_portfolio_value': total_portfolio_value, 'total_outstanding': total_outstanding, 'collection_rate': float(collection_rate), 'loans_due_today': loans_due_today, 'overdue_loans': overdue_loans, } def get_loans_due_today(self, branch_id=None, portfolio_manager_id=None): """Get loans due today using repayment scheduler""" from loans.repayment_scheduler import RepaymentScheduler # Exclude soft-deleted and rolled-over loans loans_qs = Loan.objects.filter( status='active', is_deleted=False, is_rolled_over=False ).exclude(status='rolled_over') if portfolio_manager_id: loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id) elif branch_id: # Branch ID is a UUID, no need to convert to int loans_qs = loans_qs.filter(borrower__branch_id=branch_id) loans_due_today = [] daily_count = 0 weekly_count = 0 monthly_count = 0 for loan in loans_qs.select_related('borrower', 'application', 'application__loan_product'): next_due_date = RepaymentScheduler.get_next_payment_due_date(loan) if next_due_date == self.today: expected_payment_amount = RepaymentScheduler.calculate_expected_payment_amount(loan) repayment_method = RepaymentScheduler.get_repayment_method(loan) # Count by repayment method if repayment_method == 'daily': daily_count += 1 elif repayment_method == 'weekly': weekly_count += 1 else: monthly_count += 1 loan_data = { 'id': str(loan.id), # Convert UUID to string for JSON serialization 'loan_number': loan.loan_number, 'borrower__first_name': loan.borrower.first_name, 'borrower__last_name': loan.borrower.last_name, 'borrower__phone_number': loan.borrower.phone_number, 'total_amount': float(loan.total_amount), 'amount_paid': float(loan.amount_paid), 'outstanding_balance': float(loan.outstanding_amount), 'expected_payment_amount': float(expected_payment_amount), 'repayment_method': repayment_method, 'due_date': next_due_date.isoformat() if isinstance(next_due_date, date) else str(next_due_date), 'loan_product': loan.application.loan_product.name if loan.application and loan.application.loan_product else 'Unknown', 'product_type': loan.application.loan_product.product_type if loan.application and loan.application.loan_product else 'unknown' } loans_due_today.append(loan_data) return { 'loans': loans_due_today, 'summary': { 'total_loans_due': len(loans_due_today), 'total_amount_due': sum(loan['outstanding_balance'] for loan in loans_due_today) }, 'categorized': { 'today': loans_due_today }, 'daily_count': daily_count, 'weekly_count': weekly_count, 'monthly_count': monthly_count } def get_delinquent_loans(self, branch_id=None, portfolio_manager_id=None): """Get delinquent loans categorized by severity using repayment scheduler""" from loans.repayment_scheduler import RepaymentScheduler # Exclude soft-deleted and rolled-over loans loans_qs = Loan.objects.filter( status='active', is_deleted=False, is_rolled_over=False ).exclude(status='rolled_over') if portfolio_manager_id: loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id) elif branch_id: # Branch ID is a UUID, no need to convert to int loans_qs = loans_qs.filter(borrower__branch_id=branch_id) # Categorize by severity using repayment scheduler mild_delinquent = 0 moderate_delinquent = 0 severe_delinquent = 0 daily_critical_count = 0 for loan in loans_qs: if RepaymentScheduler.is_loan_in_arrears(loan): arrears_summary = RepaymentScheduler.get_arrears_summary(loan) days_overdue = arrears_summary['days_overdue'] repayment_method = arrears_summary['repayment_method'] # Adjust thresholds based on repayment method if repayment_method == 'daily': if days_overdue <= 3: mild_delinquent += 1 elif days_overdue <= 7: moderate_delinquent += 1 else: severe_delinquent += 1 daily_critical_count += 1 # Daily loans over 7 days are critical elif repayment_method == 'weekly': if days_overdue <= 14: mild_delinquent += 1 elif days_overdue <= 21: moderate_delinquent += 1 else: severe_delinquent += 1 else: # monthly if days_overdue <= 30: mild_delinquent += 1 elif days_overdue <= 60: moderate_delinquent += 1 else: severe_delinquent += 1 return { 'loans': [], 'summary': { 'mild_delinquent_count': mild_delinquent, 'moderate_delinquent_count': moderate_delinquent, 'severe_delinquent_count': severe_delinquent, 'total_delinquent': mild_delinquent + moderate_delinquent + severe_delinquent }, 'daily_critical_count': daily_critical_count } def get_processing_fees_current_month(self, branch_id=None, portfolio_manager_id=None): """Get processing fees for current month""" loans_qs = Loan.objects.filter( is_deleted=False, created_at__date__gte=self.current_month_start ) if portfolio_manager_id: loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id) elif branch_id: # Branch ID is a UUID, no need to convert to int loans_qs = loans_qs.filter(borrower__branch_id=branch_id) fees_data = loans_qs.aggregate( total_fees=Sum('processing_fee'), total_loans=Count('id') ) return { 'summary': { 'total_processing_fees': fees_data['total_fees'] or Decimal('0.00'), 'total_loans_processed': fees_data['total_loans'] or 0 } } def get_interest_income_current_month(self, branch_id=None, portfolio_manager_id=None): """Get interest income for current month""" loans_qs = Loan.objects.filter( is_deleted=False, created_at__date__gte=self.current_month_start ) if portfolio_manager_id: loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id) elif branch_id: # Branch ID is a UUID, no need to convert to int loans_qs = loans_qs.filter(borrower__branch_id=branch_id) interest_data = loans_qs.aggregate( total_interest=Sum('interest_amount'), total_loans=Count('id') ) return { 'summary': { 'total_interest_income': interest_data['total_interest'] or Decimal('0.00'), 'total_loans': interest_data['total_loans'] or 0 } } def get_registration_fees_current_month(self, branch_id=None, portfolio_manager_id=None): """Get registration fees for current month - placeholder""" return { 'summary': { 'total_registration_income': Decimal('0.00'), 'total_registrations': 0 } } def get_customer_requests_current_month(self, branch_id=None, portfolio_manager_id=None): """Get customer requests for current month - placeholder""" return { 'summary': { 'pending_requests': 0, 'in_progress_requests': 0, 'resolved_requests': 0 } } def get_completed_loans_analytics(self, branch_id=None, portfolio_manager_id=None): """Get completed loans analytics""" # Use 'paid' status instead of 'completed' loans_qs = Loan.objects.filter(status='paid', is_deleted=False) if portfolio_manager_id: loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id) elif branch_id: # Branch ID is a UUID, no need to convert to int loans_qs = loans_qs.filter(borrower__branch_id=branch_id) # Total completed loans total_completed = loans_qs.count() # Completed this month - use updated_at to track when loan was marked as paid completed_this_month = loans_qs.filter( updated_at__gte=self.current_month_start ).count() # Completed this year year_start = self.today.replace(month=1, day=1) completed_this_year = loans_qs.filter( updated_at__gte=year_start ).count() # Total value of completed loans - calculate amount_paid manually since it's a property completed_data = loans_qs.aggregate( total_principal=Sum('principal_amount') ) # Calculate total collected by iterating (amount_paid is a property) total_collected = Decimal('0.00') for loan in loans_qs: total_collected += (loan.amount_paid or Decimal('0.00')) return { 'summary': { 'total_completed_loans': total_completed, 'completed_this_month': completed_this_month, 'completed_this_year': completed_this_year, 'total_principal_completed': completed_data['total_principal'] or Decimal('0.00'), 'total_amount_collected': total_collected, } } def get_overdue_loans_analytics(self, branch_id=None, portfolio_manager_id=None): """Get detailed overdue loans analytics""" from loans.repayment_scheduler import RepaymentScheduler # Exclude soft-deleted and rolled-over loans loans_qs = Loan.objects.filter( status='active', is_deleted=False, is_rolled_over=False ).exclude(status='rolled_over') if portfolio_manager_id: loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id) elif branch_id: # Branch ID is a UUID, no need to convert to int loans_qs = loans_qs.filter(borrower__branch_id=branch_id) # Count overdue loans by severity total_overdue = 0 mild_overdue = 0 # 1-30 days moderate_overdue = 0 # 31-60 days severe_overdue = 0 # 60+ days total_overdue_amount = Decimal('0.00') for loan in loans_qs: if RepaymentScheduler.is_loan_in_arrears(loan): total_overdue += 1 arrears_summary = RepaymentScheduler.get_arrears_summary(loan) days_overdue = arrears_summary['days_overdue'] total_overdue_amount += arrears_summary['arrears_amount'] if days_overdue <= 30: mild_overdue += 1 elif days_overdue <= 60: moderate_overdue += 1 else: severe_overdue += 1 return { 'summary': { 'total_overdue_loans': total_overdue, 'mild_overdue': mild_overdue, 'moderate_overdue': moderate_overdue, 'severe_overdue': severe_overdue, 'total_overdue_amount': total_overdue_amount, } } def get_client_growth_analytics(self, branch_id=None, portfolio_manager_id=None): """Get comprehensive client growth analytics""" clients_qs = CustomUser.objects.filter(role='borrower', status='active') if portfolio_manager_id: clients_qs = clients_qs.filter(portfolio_manager_id=portfolio_manager_id) elif branch_id: # Branch ID is a UUID, no need to convert to int clients_qs = clients_qs.filter(branch_id=branch_id) # Total clients total_clients = clients_qs.count() # This week week_start = self.today - timedelta(days=self.today.weekday()) clients_this_week = clients_qs.filter(date_joined__gte=week_start).count() # This month clients_this_month = clients_qs.filter(date_joined__gte=self.current_month_start).count() # This year year_start = self.today.replace(month=1, day=1) clients_this_year = clients_qs.filter(date_joined__gte=year_start).count() # Monthly breakdown for the year monthly_breakdown = {} for month_num in range(1, 13): month_start = year_start.replace(month=month_num) if month_num == 12: month_end = year_start.replace(year=year_start.year + 1, month=1) else: month_end = year_start.replace(month=month_num + 1) count = clients_qs.filter( date_joined__gte=month_start, date_joined__lt=month_end ).count() month_name = month_start.strftime('%B') monthly_breakdown[month_name] = count # Find best and worst months if monthly_breakdown: best_month = max(monthly_breakdown, key=monthly_breakdown.get) worst_month = min(monthly_breakdown, key=monthly_breakdown.get) best_month_count = monthly_breakdown[best_month] worst_month_count = monthly_breakdown[worst_month] else: best_month = None worst_month = None best_month_count = 0 worst_month_count = 0 # Weekly breakdown for the last 12 weeks weekly_breakdown = {} for week_offset in range(12): week_start_date = week_start - timedelta(weeks=week_offset) week_end_date = week_start_date + timedelta(days=7) count = clients_qs.filter( date_joined__gte=week_start_date, date_joined__lt=week_end_date ).count() week_label = f"Week of {week_start_date.strftime('%b %d')}" weekly_breakdown[week_label] = count # Growth rate calculation last_month_start = (self.current_month_start - timedelta(days=1)).replace(day=1) clients_last_month = clients_qs.filter( date_joined__gte=last_month_start, date_joined__lt=self.current_month_start ).count() if clients_last_month > 0: growth_rate = ((clients_this_month - clients_last_month) / clients_last_month) * 100 else: growth_rate = 100.0 if clients_this_month > 0 else 0.0 return { 'summary': { 'total_clients': total_clients, 'clients_this_week': clients_this_week, 'clients_this_month': clients_this_month, 'clients_this_year': clients_this_year, 'growth_rate': float(growth_rate), 'best_month': best_month, 'best_month_count': best_month_count, 'worst_month': worst_month, 'worst_month_count': worst_month_count, }, 'monthly_breakdown': monthly_breakdown, 'weekly_breakdown': weekly_breakdown, } def get_missed_payments_summary(self, branch_id=None, portfolio_manager_id=None): """Get summary of missed payments by repayment method""" from loans.repayment_scheduler import RepaymentScheduler # Base queryset - exclude soft-deleted and rolled-over loans loans_qs = Loan.objects.filter( status='active', is_deleted=False, is_rolled_over=False ).exclude(status='rolled_over') # Apply portfolio-based filtering if portfolio_manager_id: loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id) elif branch_id: # Branch ID is a UUID, no need to convert to int loans_qs = loans_qs.filter(borrower__branch_id=branch_id) # Count missed payments by repayment method daily_missed = 0 weekly_missed = 0 monthly_missed = 0 for loan in loans_qs: missed_periods = RepaymentScheduler.get_missed_payment_periods(loan) if missed_periods: repayment_method = RepaymentScheduler.get_repayment_method(loan) if repayment_method == 'daily': daily_missed += 1 elif repayment_method == 'weekly': weekly_missed += 1 else: # monthly monthly_missed += 1 return { 'summary': { 'daily_missed': daily_missed, 'weekly_missed': weekly_missed, 'monthly_missed': monthly_missed, 'total_missed': daily_missed + weekly_missed + monthly_missed } } def _safe_call(self, method, *args, default=None, **kwargs): """Call a method and return default on any exception, logging the error.""" import logging import traceback logger = logging.getLogger(__name__) try: return method(*args, **kwargs) except Exception as e: logger.error(f"[SimpleReportsService] {method.__name__} FAILED: {e}\n{traceback.format_exc()}") return default def generate_dashboard_data(self, branch_id=None, portfolio_manager_id=None): """Generate dashboard data — each section is independent so one failure doesn't zero everything.""" import logging import uuid as _uuid logger = logging.getLogger(__name__) self._refresh_dates() # Validate branch_id is a proper UUID if branch_id: try: _uuid.UUID(str(branch_id)) except (ValueError, AttributeError): logger.warning(f"[SimpleReportsService] Invalid branch_id '{branch_id}' — ignoring filter") branch_id = None # Defaults for each section default_summary = { 'total_active_loans': 0, 'total_portfolio_value': Decimal('0.00'), 'total_outstanding': Decimal('0.00'), 'collection_rate': 0.0, 'loans_due_today': 0, 'overdue_loans': 0, } default_loans_due = { 'loans': [], 'summary': {'total_loans_due': 0, 'total_amount_due': Decimal('0.00')}, 'categorized': {'today': []}, 'daily_count': 0, 'weekly_count': 0, 'monthly_count': 0, } default_delinquent = { 'loans': [], 'daily_critical_count': 0, 'summary': {'mild_delinquent_count': 0, 'moderate_delinquent_count': 0, 'severe_delinquent_count': 0, 'total_delinquent': 0}, } default_missed = {'summary': {'daily_missed': 0, 'weekly_missed': 0, 'monthly_missed': 0, 'total_missed': 0}} default_fees = {'summary': {'total_processing_fees': Decimal('0.00'), 'total_loans_processed': 0}} default_interest = {'summary': {'total_interest_income': Decimal('0.00'), 'total_loans': 0}} default_reg = {'summary': {'total_registration_income': Decimal('0.00'), 'total_registrations': 0}} default_requests = {'summary': {'pending_requests': 0, 'in_progress_requests': 0, 'resolved_requests': 0}} default_completed = {'summary': {'total_completed_loans': 0, 'completed_this_month': 0, 'completed_this_year': 0, 'total_principal_completed': Decimal('0.00'), 'total_amount_collected': Decimal('0.00')}} default_overdue = {'summary': {'total_overdue_loans': 0, 'mild_overdue': 0, 'moderate_overdue': 0, 'severe_overdue': 0, 'total_overdue_amount': Decimal('0.00')}} default_growth = { 'summary': {'total_clients': 0, 'clients_this_week': 0, 'clients_this_month': 0, 'clients_this_year': 0, 'growth_rate': 0.0, 'best_month': None, 'best_month_count': 0, 'worst_month': None, 'worst_month_count': 0}, 'monthly_breakdown': {}, 'weekly_breakdown': {}, } return { 'summary_metrics': self._safe_call(self.get_summary_metrics, branch_id, portfolio_manager_id, default=default_summary), 'loans_due_today': self._safe_call(self.get_loans_due_today, branch_id, portfolio_manager_id, default=default_loans_due), 'delinquent_loans': self._safe_call(self.get_delinquent_loans, branch_id, portfolio_manager_id, default=default_delinquent), 'missed_payments': self._safe_call(self.get_missed_payments_summary, branch_id, default=default_missed), 'processing_fees_current_month': self._safe_call(self.get_processing_fees_current_month, branch_id, portfolio_manager_id, default=default_fees), 'interest_income_current_month': self._safe_call(self.get_interest_income_current_month, branch_id, portfolio_manager_id, default=default_interest), 'registration_fees_current_month': self._safe_call(self.get_registration_fees_current_month, branch_id, portfolio_manager_id, default=default_reg), 'customer_requests_current_month': self._safe_call(self.get_customer_requests_current_month, branch_id, portfolio_manager_id, default=default_requests), 'completed_loans': self._safe_call(self.get_completed_loans_analytics, branch_id, portfolio_manager_id, default=default_completed), 'overdue_loans': self._safe_call(self.get_overdue_loans_analytics, branch_id, portfolio_manager_id, default=default_overdue), 'client_growth': self._safe_call(self.get_client_growth_analytics, branch_id, portfolio_manager_id, default=default_growth), 'generated_at': timezone.now().isoformat(), } # Placeholder methods for compatibility with existing views def get_processing_fees_report(self, **kwargs): """ Get processing fees report with detailed data and filtering Requirements: 6.1, 6.2, 6.3, 6.4, 6.5 """ from django.utils import timezone from django.db.models import Sum from decimal import Decimal branch_id = kwargs.get('branch_id') start_date = kwargs.get('start_date') end_date = kwargs.get('end_date') period = kwargs.get('period', 'month') loan_product_id = kwargs.get('loan_product_id') # Set date range if not start_date: if period == 'today': start_date = timezone.now().date() end_date = timezone.now().date() elif period == 'week': start_date = timezone.now().date() - timedelta(days=7) end_date = timezone.now().date() elif period == 'month': start_date = timezone.now().date() - timedelta(days=30) end_date = timezone.now().date() elif period == 'quarter': start_date = timezone.now().date() - timedelta(days=90) end_date = timezone.now().date() else: start_date = timezone.now().date() - timedelta(days=30) end_date = timezone.now().date() if not end_date: end_date = timezone.now().date() # Get loans with processing fees in the date range # Requirement 6.2: Filter by date range (disbursement date) # Exclude soft-deleted and rolled-over loans loans_qs = Loan.objects.filter( is_deleted=False, is_rolled_over=False, disbursement_date__gte=start_date, disbursement_date__lte=end_date ).select_related('borrower', 'application__loan_product') # Requirement 6.1: Filter by branch if branch_id: loans_qs = loans_qs.filter(borrower__branch_id=branch_id) # Requirement 6.3: Filter by loan product if loan_product_id: loans_qs = loans_qs.filter(application__loan_product_id=loan_product_id) # Requirement 6.4: Calculate total processing fees using aggregation total_processing_fees = loans_qs.aggregate( total=Sum('processing_fee') )['total'] or Decimal('0.00') # Build detailed loan data processing_fees = [] for loan in loans_qs: processing_fee = loan.processing_fee or Decimal('0.00') if processing_fee > 0: # Calculate fee percentage based on principal amount fee_percentage = (processing_fee / loan.principal_amount * 100) if loan.principal_amount > 0 else Decimal('0.00') # Get product name product_name = 'Standard Loan' if loan.application and loan.application.loan_product: product_name = loan.application.loan_product.name fee_data = { 'id': str(loan.id), 'loan_number': loan.loan_number or f'LOAN-{loan.id}', 'borrower_name': f"{loan.borrower.first_name} {loan.borrower.last_name}", 'borrower_phone': loan.borrower.phone_number or '', 'product_name': product_name, 'principal_amount': loan.principal_amount or Decimal('0.00'), 'processing_fee': processing_fee, 'fee_rate': fee_percentage, 'disbursement_date': loan.disbursement_date.isoformat() if loan.disbursement_date else None, 'status': loan.status, 'fee_status': 'paid' if loan.status == 'active' else 'pending', } processing_fees.append(fee_data) # Calculate summary statistics total_loans = len(processing_fees) average_fee = total_processing_fees / total_loans if total_loans > 0 else Decimal('0.00') # Requirement 6.5: Calculate period comparison (current vs previous period) current_duration = (end_date - start_date).days previous_end = start_date - timedelta(days=1) previous_start = previous_end - timedelta(days=current_duration) # Get previous period fees previous_loans_qs = Loan.objects.filter( is_deleted=False, is_rolled_over=False, disbursement_date__gte=previous_start, disbursement_date__lte=previous_end ) if branch_id: previous_loans_qs = previous_loans_qs.filter(borrower__branch_id=branch_id) if loan_product_id: previous_loans_qs = previous_loans_qs.filter(application__loan_product_id=loan_product_id) previous_period_fees = previous_loans_qs.aggregate( total=Sum('processing_fee') )['total'] or Decimal('0.00') # Calculate period difference and growth rate period_difference = total_processing_fees - previous_period_fees growth_rate = Decimal('0.00') if previous_period_fees > 0: growth_rate = (period_difference / previous_period_fees) * 100 # Calculate monthly breakdown monthly_breakdown = {} for fee in processing_fees: if fee['disbursement_date']: month_key = fee['disbursement_date'][:7] # Extract 'YYYY-MM' if month_key not in monthly_breakdown: monthly_breakdown[month_key] = {'count': 0, 'amount': Decimal('0.00')} monthly_breakdown[month_key]['count'] += 1 monthly_breakdown[month_key]['amount'] += fee['processing_fee'] # Calculate additional metrics fees_collected = sum(fee['processing_fee'] for fee in processing_fees if fee['fee_status'] == 'paid') fees_pending = total_processing_fees - fees_collected collection_rate = (fees_collected / total_processing_fees * 100) if total_processing_fees > 0 else Decimal('0.00') highest_fee = max([fee['processing_fee'] for fee in processing_fees], default=Decimal('0.00')) # Find most active product product_counts = {} for fee in processing_fees: product = fee['product_name'] product_counts[product] = product_counts.get(product, 0) + 1 top_product = max(product_counts.items(), key=lambda x: x[1])[0] if product_counts else 'N/A' return { 'summary': { 'total_processing_fees': total_processing_fees, 'total_loans_processed': total_loans, 'average_fee': average_fee, 'average_processing_fee': average_fee, # Alias for template compatibility 'fees_collected': fees_collected, 'fees_pending': fees_pending, 'collection_rate': collection_rate, 'growth_rate': growth_rate, 'highest_fee': highest_fee, 'top_product': top_product, 'current_period_fees': total_processing_fees, 'previous_period_fees': previous_period_fees, 'period_difference': period_difference, 'fee_percentage': Decimal('0.00'), # Placeholder }, 'monthly_breakdown': monthly_breakdown, 'loans': processing_fees, 'fees': processing_fees, # Alias for template compatibility 'chart_data': { 'monthly_trend': { 'labels': list(monthly_breakdown.keys()), 'amounts': [float(monthly_breakdown[key]['amount']) for key in monthly_breakdown.keys()] }, 'product_breakdown': { 'labels': ['Processing Fees'], 'amounts': [float(total_processing_fees)] } } } def get_processing_fees_trend_analysis(self, **kwargs): """Placeholder for processing fees trend analysis""" return { 'monthly_trends': [], 'summary': {} } def get_interest_income_report(self, **kwargs): """Get interest income report with detailed data""" from django.utils import timezone from decimal import Decimal branch_id = kwargs.get('branch_id') month = kwargs.get('month') year = kwargs.get('year') # Set date range if not year: year = timezone.now().year if not month: month = timezone.now().month # Get loans with interest in the specified month/year - exclude soft-deleted loans_qs = Loan.objects.filter( is_deleted=False, created_at__year=year, created_at__month=month ) if branch_id: loans_qs = loans_qs.filter(borrower__branch_id=branch_id) # Calculate interest income using actual interest_amount field interest_income = [] total_interest_income = Decimal('0.00') for loan in loans_qs: # Use the actual interest amount from the loan record interest_amt = loan.interest_amount or Decimal('0.00') if interest_amt > 0: # Calculate interest rate from actual amounts if loan.principal_amount and loan.principal_amount > 0: # Calculate the effective interest rate interest_rate = (interest_amt / loan.principal_amount * 100) else: interest_rate = Decimal('0.00') income_data = { 'id': str(loan.id), # Convert UUID to string for JSON serialization 'loan_number': loan.loan_number or f'LOAN-{loan.id}', 'borrower_name': f"{loan.borrower.first_name} {loan.borrower.last_name}", 'loan_amount': loan.principal_amount or Decimal('0.00'), 'interest_rate': interest_rate, 'monthly_interest': interest_amt, # This is the total interest, not monthly 'total_interest': interest_amt, 'created_date': loan.created_at.date().isoformat() if loan.created_at else None, 'status': loan.status, } interest_income.append(income_data) total_interest_income += interest_amt # Calculate summary statistics total_loans = len(interest_income) average_interest = total_interest_income / total_loans if total_loans > 0 else Decimal('0.00') # Calculate monthly breakdown for the year using actual interest amounts monthly_breakdown = {} for month_num in range(1, 13): month_loans = Loan.objects.filter( is_deleted=False, created_at__year=year, created_at__month=month_num ) if branch_id: month_loans = month_loans.filter(borrower__branch_id=branch_id) # Use actual interest_amount field month_interest = month_loans.aggregate( total=Sum('interest_amount') )['total'] or Decimal('0.00') monthly_breakdown[f"{year}-{month_num:02d}"] = float(month_interest) # Calculate rate distribution rate_distribution = { '15%': len([loan for loan in interest_income if loan['interest_rate'] == 15.0]), '20%': len([loan for loan in interest_income if loan['interest_rate'] == 20.0]), '25%': len([loan for loan in interest_income if loan['interest_rate'] == 25.0]), } return { 'summary': { 'total_interest_income': total_interest_income, 'total_loans': total_loans, 'average_interest': average_interest, }, 'monthly_breakdown': monthly_breakdown, 'rate_distribution': rate_distribution, 'loans': interest_income, 'chart_data': { 'monthly_trend': { 'labels': list(monthly_breakdown.keys()), 'amounts': list(monthly_breakdown.values()) }, 'rate_distribution': { 'labels': list(rate_distribution.keys()), 'counts': list(rate_distribution.values()) }, 'product_performance': { 'labels': ['Interest Income'], 'amounts': [float(total_interest_income)] } } } def get_interest_income_trend_analysis(self, **kwargs): """Placeholder for interest income trend analysis""" return { 'monthly_trends': [], 'summary': {} } def get_all_time_registration_fees_report(self, **kwargs): """Get all-time registration fees focusing on client registration fees""" from django.db.models import Sum, Count, Q from django.db.models.functions import Coalesce from django.utils import timezone from users.models import CustomUser from decimal import Decimal branch_id = kwargs.get('branch_id') start_date = kwargs.get('start_date') end_date = kwargs.get('end_date') payment_status = kwargs.get('payment_status') # Initialize totals total_income = Decimal('0.00') total_count = 0 fees_list = [] try: # Focus on CustomUser registration fees (the ones added during client registration) user_reg_fees = CustomUser.objects.filter( role='borrower', registration_fee_amount__gt=0 ).exclude(registration_fee_amount__isnull=True) # Apply branch filtering if branch_id: user_reg_fees = user_reg_fees.filter(branch_id=branch_id) # Apply date filtering if start_date: user_reg_fees = user_reg_fees.filter(created_at__gte=start_date) if end_date: user_reg_fees = user_reg_fees.filter(created_at__lte=end_date) # Apply payment status filtering if payment_status == 'paid': user_reg_fees = user_reg_fees.filter(registration_fee_paid=True) elif payment_status == 'pending': user_reg_fees = user_reg_fees.filter(Q(registration_fee_paid=False) | Q(registration_fee_paid__isnull=True)) # Get paid registration fees paid_user_fees = user_reg_fees.filter(registration_fee_paid=True) # Calculate totals from paid CustomUser registration fees user_data = paid_user_fees.aggregate( total_income=Coalesce(Sum('registration_fee_amount'), Decimal('0.00')), total_count=Count('id') ) total_income = user_data['total_income'] or Decimal('0.00') total_count = user_data['total_count'] or 0 # Get pending fees pending_user_fees = user_reg_fees.filter(Q(registration_fee_paid=False) | Q(registration_fee_paid__isnull=True)) pending_data = pending_user_fees.aggregate( total_pending=Coalesce(Sum('registration_fee_amount'), Decimal('0.00')), pending_count=Count('id') ) total_pending = pending_data['total_pending'] or Decimal('0.00') pending_count = pending_data['pending_count'] or 0 # Add to fees list - all registration fees for user in user_reg_fees.order_by('-registration_fee_payment_date', '-created_at'): payment_status_display = 'paid' if user.registration_fee_paid else 'pending' fees_list.append({ 'id': str(user.id), 'client_id': str(user.id), 'client_name': user.get_full_name(), 'client_phone': user.phone_number, 'client_id_number': getattr(user, 'id_number', 'N/A'), 'customer_name': user.get_full_name(), 'customer_phone': user.phone_number, 'registration_date': user.created_at, 'product_type': 'Client Registration', 'fee_name': 'Client Registration Fee', 'fee_amount': user.registration_fee_amount, 'amount': user.registration_fee_amount, 'discount_amount': 0, 'payment_method': user.get_registration_fee_payment_method_display() if user.registration_fee_payment_method else 'Not Specified', 'payment_date': user.registration_fee_payment_date or user.created_at if user.registration_fee_paid else None, 'payment_status': payment_status_display, 'notes': f'Registered on {user.created_at.strftime("%Y-%m-%d")}' }) except Exception as e: # Log error but continue import logging logger = logging.getLogger(__name__) logger.warning(f"Error processing registration fee data: {e}") # Calculate average fee average_fee = total_income / total_count if total_count > 0 else Decimal('0.00') # Calculate collection rate total_expected = total_income + total_pending collection_rate = (total_income / total_expected * 100) if total_expected > 0 else Decimal('0.00') collection_efficiency = collection_rate return { 'report_type': 'registration_fees', 'period': { 'start_date': start_date, 'end_date': end_date, 'period_display': 'Custom Period' if start_date or end_date else 'All Time' }, 'summary': { 'total_registration_income': total_income, 'total_registrations': total_count + pending_count, 'average_registration_fee': average_fee, 'collection_rate': collection_rate, 'collection_efficiency': collection_efficiency, 'paid_registrations': total_count, 'pending_payments': total_pending, 'pending_count': pending_count, 'fees_collected': total_income, 'fees_outstanding': total_pending, 'monthly_growth': Decimal('0.00'), 'monthly_growth_rate': Decimal('0.00'), }, 'fees': fees_list, 'registrations': fees_list, # Alias for template compatibility } def get_registration_fees_report(self, **kwargs): """ Get registration fees report with comprehensive metrics Requirements: 8.1, 8.2, 8.3, 8.4, 8.5 """ from django.db.models import Sum, Count, Q, Avg from django.db.models.functions import Coalesce from django.utils import timezone from users.models import CustomUser from decimal import Decimal branch_id = kwargs.get('branch_id') start_date = kwargs.get('start_date') end_date = kwargs.get('end_date') payment_status = kwargs.get('payment_status') # Initialize totals total_income = Decimal('0.00') total_count = 0 fees_list = [] try: # Focus on CustomUser registration fees (the ones added during client registration) user_reg_fees = CustomUser.objects.filter( role='borrower', registration_fee_amount__gt=0 ).exclude(registration_fee_amount__isnull=True) # Requirement 8.1: Apply branch filtering if branch_id: user_reg_fees = user_reg_fees.filter(branch_id=branch_id) # Apply date filtering (registration date range) if start_date: user_reg_fees = user_reg_fees.filter(created_at__gte=start_date) if end_date: user_reg_fees = user_reg_fees.filter(created_at__lte=end_date) # Apply payment status filtering if payment_status == 'paid': user_reg_fees = user_reg_fees.filter(registration_fee_paid=True) elif payment_status == 'pending': user_reg_fees = user_reg_fees.filter(Q(registration_fee_paid=False) | Q(registration_fee_paid__isnull=True)) # Get paid registration fees paid_user_fees = user_reg_fees.filter(registration_fee_paid=True) # Calculate totals from paid CustomUser registration fees user_data = paid_user_fees.aggregate( total_income=Coalesce(Sum('registration_fee_amount'), Decimal('0.00')), total_count=Count('id') ) total_income = user_data['total_income'] or Decimal('0.00') total_count = user_data['total_count'] or 0 # Get pending fees pending_user_fees = user_reg_fees.filter(Q(registration_fee_paid=False) | Q(registration_fee_paid__isnull=True)) pending_data = pending_user_fees.aggregate( total_pending=Coalesce(Sum('registration_fee_amount'), Decimal('0.00')), pending_count=Count('id') ) total_pending = pending_data['total_pending'] or Decimal('0.00') pending_count = pending_data['pending_count'] or 0 # Requirement 8.3: Calculate highest single fee (maximum value) highest_single_fee = Decimal('0.00') if paid_user_fees.exists(): highest_fee_data = paid_user_fees.aggregate( max_fee=Coalesce(Max('registration_fee_amount'), Decimal('0.00')) ) highest_single_fee = highest_fee_data['max_fee'] or Decimal('0.00') # Requirement 8.4: Calculate average days to pay average_days_to_pay = Decimal('0.00') days_to_pay_list = [] for user in paid_user_fees: if user.registration_fee_payment_date and user.created_at: days_diff = (user.registration_fee_payment_date.date() - user.created_at.date()).days if days_diff >= 0: # Only count non-negative values days_to_pay_list.append(days_diff) if days_to_pay_list: average_days_to_pay = Decimal(str(sum(days_to_pay_list) / len(days_to_pay_list))) # Requirement 8.2 & 8.5: Calculate growth vs last period (period comparison) current_period_total = total_income previous_period_total = Decimal('0.00') growth_vs_last_period = Decimal('0.00') growth_rate = Decimal('0.00') if start_date and end_date: # Calculate previous period with same duration current_duration = (end_date - start_date).days previous_end = start_date - timedelta(days=1) previous_start = previous_end - timedelta(days=current_duration) # Get previous period fees previous_fees = CustomUser.objects.filter( role='borrower', registration_fee_amount__gt=0, registration_fee_paid=True, created_at__gte=previous_start, created_at__lte=previous_end ).exclude(registration_fee_amount__isnull=True) if branch_id: previous_fees = previous_fees.filter(branch_id=branch_id) previous_data = previous_fees.aggregate( total=Coalesce(Sum('registration_fee_amount'), Decimal('0.00')) ) previous_period_total = previous_data['total'] or Decimal('0.00') # Requirement 8.5: Calculate arithmetic difference growth_vs_last_period = current_period_total - previous_period_total # Calculate growth rate percentage if previous_period_total > 0: growth_rate = (growth_vs_last_period / previous_period_total) * 100 elif current_period_total > 0: growth_rate = Decimal('100.00') # 100% growth from zero # Add to fees list - all registration fees for user in user_reg_fees.order_by('-registration_fee_payment_date', '-created_at'): payment_status_display = 'paid' if user.registration_fee_paid else 'pending' fees_list.append({ 'id': str(user.id), 'client_id': str(user.id), 'client_name': user.get_full_name(), 'client_phone': user.phone_number, 'client_id_number': getattr(user, 'id_number', 'N/A'), 'customer_name': user.get_full_name(), 'customer_phone': user.phone_number, 'registration_date': user.created_at, 'product_type': 'Client Registration', 'fee_name': 'Client Registration Fee', 'fee_amount': user.registration_fee_amount, 'amount': user.registration_fee_amount, 'discount_amount': 0, 'payment_method': user.get_registration_fee_payment_method_display() if user.registration_fee_payment_method else 'Not Specified', 'payment_date': user.registration_fee_payment_date or user.created_at if user.registration_fee_paid else None, 'payment_status': payment_status_display, 'notes': f'Registered on {user.created_at.strftime("%Y-%m-%d")}' }) except Exception as e: # Log error but continue import logging logger = logging.getLogger(__name__) logger.warning(f"Error processing registration fee data: {e}") # Calculate average fee average_fee = total_income / total_count if total_count > 0 else Decimal('0.00') # Calculate collection rate total_expected = total_income + total_pending collection_rate = (total_income / total_expected * 100) if total_expected > 0 else Decimal('0.00') collection_efficiency = collection_rate return { 'report_type': 'registration_fees', 'period': { 'start_date': start_date, 'end_date': end_date, 'period_display': 'Custom Period' if start_date or end_date else 'All Time' }, 'summary': { 'total_registration_income': total_income, 'total_registrations': total_count + pending_count, 'average_registration_fee': average_fee, 'collection_rate': collection_rate, 'collection_efficiency': collection_efficiency, 'paid_registrations': total_count, 'pending_payments': total_pending, 'pending_count': pending_count, 'fees_collected': total_income, 'fees_outstanding': total_pending, # New metrics for Requirements 8.2, 8.3, 8.4, 8.5 'highest_single_fee': highest_single_fee, 'average_days_to_pay': average_days_to_pay, 'current_period_total': current_period_total, 'previous_period_total': previous_period_total, 'growth_vs_last_period': growth_vs_last_period, 'growth_rate': growth_rate, 'monthly_growth': growth_vs_last_period, # Alias for compatibility 'monthly_growth_rate': growth_rate, # Alias for compatibility }, 'fees': fees_list, 'registrations': fees_list, # Alias for template compatibility } def get_loans_in_arrears_report(self, **kwargs): """ Get loans in arrears report with detailed data using repayment method-aware calculation. Implements proper arrears definition: - due_date < current_date AND outstanding_amount > 0 - Excludes fully paid loans (outstanding_amount = 0) - Excludes rolled-over loans - Excludes soft-deleted loans Validates: Requirements 14.1, 14.2, 14.3, 14.4, 14.5 """ from django.utils import timezone from django.db.models import Sum, Count, Avg, Q, F from decimal import Decimal from datetime import datetime from loans.repayment_scheduler import RepaymentScheduler from reports.filter_service import ReportFilterService branch_id = kwargs.get('branch_id') arrears_filter = kwargs.get('arrears_amount_filter') sort_by = kwargs.get('sort_by', 'amount_desc') start_date = kwargs.get('start_date') end_date = kwargs.get('end_date') # Get all loans - we'll apply proper arrears filtering # Requirement 14.4: Exclude rolled-over loans # Requirement 14.5: Exclude soft-deleted loans loans_qs = Loan.objects.all() # Use filter service to exclude rolled-over and soft-deleted loans loans_qs = ReportFilterService.apply_loan_status_filter( loans_qs, exclude_rolled_over=True, exclude_deleted=True ) # Requirement 14.3: Exclude fully paid loans # Paid loans have outstanding_amount = 0, so they won't appear in arrears # But we also explicitly exclude loans with status='paid' for clarity loans_qs = loans_qs.exclude(status='paid') if branch_id: loans_qs = loans_qs.filter(borrower__branch_id=branch_id) # Apply date filtering if start_date: try: start_date_obj = datetime.strptime(start_date, '%Y-%m-%d').date() loans_qs = loans_qs.filter(disbursement_date__gte=start_date_obj) except ValueError: pass if end_date: try: end_date_obj = datetime.strptime(end_date, '%Y-%m-%d').date() loans_qs = loans_qs.filter(disbursement_date__lte=end_date_obj) except ValueError: pass # Use the new repayment scheduler to get loans in arrears # Requirement 14.1: Arrears definition - due_date < current_date AND outstanding_amount > 0 # Requirement 14.2: Days in arrears calculation - (current_date - due_date).days # Requirement 14.3: Fully paid exclusion - outstanding_amount = 0 excluded # The RepaymentScheduler.get_loans_in_arrears() method implements these requirements loans_in_arrears_data = RepaymentScheduler.get_loans_in_arrears(loans_qs, branch_id) loans_in_arrears = [] total_arrears_amount = Decimal('0.00') for loan_data in loans_in_arrears_data: loan = loan_data['loan'] arrears_summary = loan_data['arrears_summary'] # Get last payment try: last_payment = Repayment.objects.filter(loan=loan).order_by('-payment_date').first() except: last_payment = None # Determine severity level based on repayment method and days overdue days_overdue = arrears_summary['days_overdue'] arrears_amount = arrears_summary['arrears_amount'] repayment_method = arrears_summary['repayment_method'] # Adjust severity thresholds based on repayment method if repayment_method == 'daily': if days_overdue > 7: severity_level = 'critical' severity_label = 'Critical' elif days_overdue > 3: severity_level = 'high' severity_label = 'High Risk' elif days_overdue > 1: severity_level = 'medium' severity_label = 'Medium Risk' else: severity_level = 'low' severity_label = 'Low Risk' elif repayment_method == 'weekly': if days_overdue > 21: severity_level = 'critical' severity_label = 'Critical' elif days_overdue > 14: severity_level = 'high' severity_label = 'High Risk' elif days_overdue > 7: severity_level = 'medium' severity_label = 'Medium Risk' else: severity_level = 'low' severity_label = 'Low Risk' else: # monthly if days_overdue > 90: severity_level = 'critical' severity_label = 'Critical' elif days_overdue > 60: severity_level = 'high' severity_label = 'High Risk' elif days_overdue > 30: severity_level = 'medium' severity_label = 'Medium Risk' else: severity_level = 'low' severity_label = 'Low Risk' loan_info = { 'id': str(loan.id), # Convert UUID to string for JSON serialization 'loan_number': loan.loan_number or f'LOAN-{loan.id}', 'borrower_name': f"{loan.borrower.first_name} {loan.borrower.last_name}", 'borrower_phone': loan.borrower.phone_number, 'due_date': loan.due_date.isoformat() if hasattr(loan.due_date, 'isoformat') else str(loan.due_date) if loan.due_date else None, 'arrears_amount': float(arrears_amount), 'days_overdue': days_overdue, 'severity_level': severity_level, 'severity_label': severity_label, 'last_payment_date': last_payment.payment_date.isoformat() if last_payment and hasattr(last_payment.payment_date, 'isoformat') else (str(last_payment.payment_date) if last_payment and last_payment.payment_date else None), 'last_payment_amount': float(last_payment.amount) if last_payment else None, 'loan_product': loan.application.loan_product.name if loan.application and loan.application.loan_product else 'Unknown', 'product_type': loan.application.loan_product.product_type if loan.application and loan.application.loan_product else 'unknown', 'repayment_method': repayment_method, 'expected_payment_amount': float(arrears_summary['expected_payment_amount']), 'payment_frequency': arrears_summary['payment_frequency'], 'missed_periods_count': arrears_summary['missed_periods_count'], 'next_payment_due_date': RepaymentScheduler.get_next_payment_due_date(loan), 'payment_status': RepaymentScheduler.get_payment_status(loan), 'principal_amount': float(loan.principal_amount), 'total_amount': float(loan.total_amount), 'amount_paid': float(loan.amount_paid), 'missed_periods': arrears_summary['missed_periods'] } loans_in_arrears.append(loan_info) total_arrears_amount += arrears_amount # Apply arrears amount filter if arrears_filter: if arrears_filter == 'under_10k': loans_in_arrears = [loan for loan in loans_in_arrears if loan['arrears_amount'] < 10000] elif arrears_filter == '10k_50k': loans_in_arrears = [loan for loan in loans_in_arrears if 10000 <= loan['arrears_amount'] < 50000] elif arrears_filter == 'over_50k': loans_in_arrears = [loan for loan in loans_in_arrears if loan['arrears_amount'] >= 50000] # Apply sorting if sort_by == 'amount_desc': loans_in_arrears.sort(key=lambda x: x['arrears_amount'], reverse=True) elif sort_by == 'amount_asc': loans_in_arrears.sort(key=lambda x: x['arrears_amount']) elif sort_by == 'days_overdue_desc': loans_in_arrears.sort(key=lambda x: x['days_overdue'], reverse=True) elif sort_by == 'days_overdue_asc': loans_in_arrears.sort(key=lambda x: x['days_overdue']) elif sort_by == 'borrower_name': loans_in_arrears.sort(key=lambda x: x['borrower_name']) elif sort_by == 'due_date_desc': loans_in_arrears.sort(key=lambda x: x['due_date'], reverse=True) elif sort_by == 'due_date_asc': loans_in_arrears.sort(key=lambda x: x['due_date']) # Calculate summary statistics total_loans = len(loans_in_arrears) average_arrears = total_arrears_amount / total_loans if total_loans > 0 else Decimal('0.00') # Calculate severity breakdown severity_breakdown = { 'low_risk_count': len([loan for loan in loans_in_arrears if loan['severity_level'] == 'low']), 'medium_risk_count': len([loan for loan in loans_in_arrears if loan['severity_level'] == 'medium']), 'high_risk_count': len([loan for loan in loans_in_arrears if loan['severity_level'] == 'high']), 'critical_count': len([loan for loan in loans_in_arrears if loan['severity_level'] == 'critical']), } return { 'summary': { 'total_loans_in_arrears': total_loans, 'total_arrears_amount': total_arrears_amount, 'average_arrears_amount': average_arrears, 'recovery_rate': 0, # Placeholder }, 'severity_breakdown': severity_breakdown, 'loans': loans_in_arrears, 'recovery_trend': { 'labels': [], 'data': [] } } def get_loans_due_report(self, **kwargs): """Get loans due report with detailed data using repayment method-aware calculation""" from django.utils import timezone from decimal import Decimal from datetime import timedelta from loans.repayment_scheduler import RepaymentScheduler branch_id = kwargs.get('branch_id') start_date = kwargs.get('start_date') end_date = kwargs.get('end_date') today_only = kwargs.get('today_only', False) # Set date range if today_only: start_date = timezone.now().date() end_date = timezone.now().date() elif not start_date: start_date = timezone.now().date() if not end_date: end_date = start_date + timedelta(days=30) # Get all active loans (we'll filter for due dates using the new scheduler) - exclude soft-deleted loans_qs = Loan.objects.filter( is_deleted=False, status__in=['active', 'rolled_over', 'defaulted'] ) if branch_id: loans_qs = loans_qs.filter(borrower__branch_id=branch_id) # Calculate loans due using repayment scheduler loans_due = [] total_due_amount = Decimal('0.00') for loan in loans_qs: # Get next payment due date using repayment scheduler next_due_date = RepaymentScheduler.get_next_payment_due_date(loan) expected_payment_amount = RepaymentScheduler.calculate_expected_payment_amount(loan) repayment_method = RepaymentScheduler.get_repayment_method(loan) # Check if this loan has a payment due in our date range if start_date <= next_due_date <= end_date: days_until_due = (next_due_date - timezone.now().date()).days # Determine priority based on repayment method if repayment_method == 'daily': if days_until_due < 0: priority = 'overdue' priority_label = 'Overdue' elif days_until_due == 0: priority = 'due_today' priority_label = 'Due Today' else: priority = 'due_later' priority_label = 'Due Later' elif repayment_method == 'weekly': if days_until_due < 0: priority = 'overdue' priority_label = 'Overdue' elif days_until_due <= 1: priority = 'due_today' priority_label = 'Due Today/Tomorrow' elif days_until_due <= 7: priority = 'due_this_week' priority_label = 'Due This Week' else: priority = 'due_later' priority_label = 'Due Later' else: # monthly if days_until_due < 0: priority = 'overdue' priority_label = 'Overdue' elif days_until_due == 0: priority = 'due_today' priority_label = 'Due Today' elif days_until_due <= 7: priority = 'due_this_week' priority_label = 'Due This Week' else: priority = 'due_later' priority_label = 'Due Later' # Get last payment try: last_payment = Repayment.objects.filter(loan=loan).order_by('-payment_date').first() except: last_payment = None loan_data = { 'id': str(loan.id), # Convert UUID to string for JSON serialization 'loan_number': loan.loan_number or f'LOAN-{loan.id}', 'borrower_name': f"{loan.borrower.first_name} {loan.borrower.last_name}", 'borrower_phone': loan.borrower.phone_number, 'due_date': next_due_date.isoformat() if isinstance(next_due_date, date) else str(next_due_date) if next_due_date else None, 'outstanding_balance': float(loan.outstanding_amount), 'expected_payment_amount': float(expected_payment_amount), 'days_until_due': days_until_due, 'priority': priority, 'priority_label': priority_label, 'last_payment_date': last_payment.payment_date.isoformat() if last_payment and hasattr(last_payment.payment_date, 'isoformat') else (str(last_payment.payment_date) if last_payment and last_payment.payment_date else None), 'last_payment_amount': float(last_payment.amount) if last_payment else None, 'repayment_method': repayment_method, 'loan_product': loan.application.loan_product.name if loan.application and loan.application.loan_product else 'Unknown', 'product_type': loan.application.loan_product.product_type if loan.application and loan.application.loan_product else 'unknown', 'principal_amount': float(loan.principal_amount), 'total_amount': float(loan.total_amount), 'amount_paid': float(loan.amount_paid), } loans_due.append(loan_data) total_due_amount += expected_payment_amount # Calculate summary statistics total_loans = len(loans_due) average_due = total_due_amount / total_loans if total_loans > 0 else Decimal('0.00') # Calculate priority breakdown priority_breakdown = { 'overdue_count': len([loan for loan in loans_due if loan['priority'] == 'overdue']), 'due_today_count': len([loan for loan in loans_due if loan['priority'] == 'due_today']), 'due_this_week_count': len([loan for loan in loans_due if loan['priority'] == 'due_this_week']), 'due_later_count': len([loan for loan in loans_due if loan['priority'] == 'due_later']), } return { 'summary': { 'total_loans_due': total_loans, 'total_amount_due': total_due_amount, 'average_due_amount': average_due, }, 'priority_breakdown': priority_breakdown, 'loans': loans_due, 'chart_data': { 'due_date_distribution': { 'labels': [], 'amounts': [] } } } def get_delinquent_loans_report(self, **kwargs): """Get delinquent loans report with detailed data""" from django.utils import timezone from decimal import Decimal branch_id = kwargs.get('branch_id') days_filter = kwargs.get('days_overdue_filter') # Get loans that are overdue (include active, rolled_over, and defaulted) - exclude soft-deleted loans_qs = Loan.objects.filter( is_deleted=False, status__in=['active', 'rolled_over', 'defaulted'], due_date__lt=timezone.now() ) if branch_id: loans_qs = loans_qs.filter(borrower__branch_id=branch_id) # Calculate delinquent loans delinquent_loans = [] total_delinquent_amount = Decimal('0.00') for loan in loans_qs: # Calculate outstanding amount outstanding = loan.outstanding_amount if outstanding > 0: # Handle both date and datetime objects due_date = loan.due_date if hasattr(due_date, 'date'): due_date = due_date.date() days_overdue = (timezone.now().date() - due_date).days # Apply days filter if days_filter: if days_filter == '1_30' and not (1 <= days_overdue <= 30): continue elif days_filter == '31_60' and not (31 <= days_overdue <= 60): continue elif days_filter == '61_90' and not (61 <= days_overdue <= 90): continue elif days_filter == '90_plus' and days_overdue < 90: continue # Determine delinquency level if days_overdue <= 30: delinquency_level = 'mild' delinquency_label = 'Mild (1-30 days)' elif days_overdue <= 60: delinquency_level = 'moderate' delinquency_label = 'Moderate (31-60 days)' elif days_overdue <= 90: delinquency_level = 'severe' delinquency_label = 'Severe (61-90 days)' else: delinquency_level = 'critical' delinquency_label = 'Critical (90+ days)' # Get last payment try: last_payment = Repayment.objects.filter(loan=loan).order_by('-payment_date').first() except: last_payment = None loan_data = { 'id': str(loan.id), # Convert UUID to string for JSON serialization 'loan_number': loan.loan_number or f'LOAN-{loan.id}', 'borrower_name': f"{loan.borrower.first_name} {loan.borrower.last_name}", 'borrower_phone': loan.borrower.phone_number, 'due_date': loan.due_date.isoformat() if hasattr(loan.due_date, 'isoformat') else str(loan.due_date) if loan.due_date else None, 'outstanding_balance': outstanding, 'days_overdue': days_overdue, 'delinquency_level': delinquency_level, 'delinquency_label': delinquency_label, 'last_payment_date': last_payment.payment_date.isoformat() if last_payment and hasattr(last_payment.payment_date, 'isoformat') else (str(last_payment.payment_date) if last_payment and last_payment.payment_date else None), 'last_payment_amount': float(last_payment.amount) if last_payment else None, } delinquent_loans.append(loan_data) total_delinquent_amount += outstanding # Calculate summary statistics total_loans = len(delinquent_loans) average_delinquent = total_delinquent_amount / total_loans if total_loans > 0 else Decimal('0.00') # Calculate delinquency breakdown delinquency_breakdown = { 'mild_count': len([loan for loan in delinquent_loans if loan['delinquency_level'] == 'mild']), 'moderate_count': len([loan for loan in delinquent_loans if loan['delinquency_level'] == 'moderate']), 'severe_count': len([loan for loan in delinquent_loans if loan['delinquency_level'] == 'severe']), 'critical_count': len([loan for loan in delinquent_loans if loan['delinquency_level'] == 'critical']), } return { 'summary': { 'total_delinquent_loans': total_loans, 'total_delinquent_amount': total_delinquent_amount, 'average_delinquent_amount': average_delinquent, }, 'delinquency_breakdown': delinquency_breakdown, 'loans': delinquent_loans, 'recovery_trend': { 'labels': [], 'amounts': [] }, 'age_analysis': { 'counts': [delinquency_breakdown['mild_count'], delinquency_breakdown['moderate_count'], delinquency_breakdown['severe_count'], delinquency_breakdown['critical_count']] } } def get_customer_requests_report(self, **kwargs): """Placeholder for customer requests report""" return { 'summary': { 'pending_requests': 0, 'in_progress_requests': 0, 'resolved_requests': 0 }, 'requests': [] } def generate_comprehensive_dashboard_data(self, **kwargs): """Alias for generate_dashboard_data for compatibility""" return self.generate_dashboard_data(kwargs.get('branch_id')) def _get_summary_metrics(self, **kwargs): """Alias for get_summary_metrics for compatibility""" return self.get_summary_metrics(kwargs.get('branch_id')) # Create a global instance simple_reports_service = SimpleReportsService()