from rest_framework import serializers from ledgers.models import OrganisationSubAccount, SystemTransactions from ledgers.ledgers_helper import get_chart_of_account_balance_at, get_chart_of_account_balance_between from locations.models import CustomerAddress from datetime import datetime, timedelta from savings.models import * from locations.serializers import CustomerAddressSerializer from django.db.models import Sum from savings.savings_bal_helper import get_account_balance from .models import SavingsAccountBalances, MinistryReportSettings from .models import * from loans.helper import loan_balance from dateutil.relativedelta import relativedelta class MinistryReportSettingsSerializer(serializers.ModelSerializer): """ Serializer for Ministry Report Settings """ organisation_id = serializers.IntegerField(write_only=True) organisation = serializers.PrimaryKeyRelatedField(read_only=True) class Meta: model = MinistryReportSettings fields = [ 'id', 'organisation_id', 'organisation', 'report_title', # SACCO Basic Details 'sacco_name', 'registration_number', 'sacco_code', 'physical_address', 'district', 'region', 'registration_date', 'registration_status', # Contact Information 'email', 'telephone', 'contact_person', # Manager Information 'manager_name', 'manager_email', 'manager_phone', # Chairperson Information 'chairperson_name', 'chairperson_email', 'chairperson_phone', # Chairperson SUPCO Information 'supco_chairperson_name', 'supco_chairperson_email', 'supco_chairperson_phone', # Governance 'board_members_male', 'board_members_female', 'loan_committee_male', 'loan_committee_female', 'supervisory_committee_male', 'supervisory_committee_female', 'vetting_committee_male', 'vetting_committee_female', 'loan_committee_frequency', 'board_meeting_frequency', 'supco_visit_frequency', 'last_agm_date', 'agm_reason', # Staffing 'male_staff', 'female_staff', 'management_staff_male', 'management_staff_female', 'support_staff_male', 'support_staff_female', 'field_officers_male', 'field_officers_female', # Report Header Customization 'report_header_title', 'report_subtitle', 'ministry_department', 'ministry_address', 'ministry_contact', # Additional Notes 'sacco_manager_comments', 'dco_comments', 'additional_notes', # Metadata 'created_at', 'updated_at', ] read_only_fields = ['id', 'created_at', 'updated_at'] def validate_organisation_id(self, value): """Ensure organisation_id is valid""" if value and value <= 0: raise serializers.ValidationError("organisation_id must be a positive integer") return value def validate_board_members_male(self, value): """Ensure board_members_male is non-negative""" if value < 0: raise serializers.ValidationError("board_members_male must be non-negative") return value def validate_board_members_female(self, value): """Ensure board_members_female is non-negative""" if value < 0: raise serializers.ValidationError("board_members_female must be non-negative") return value class MinistryReportSettingsOptimizedSerializer(serializers.ModelSerializer): """ Optimized serializer for Ministry Report Settings - minimal fields for faster loading """ total_board_members = serializers.SerializerMethodField() total_staff = serializers.SerializerMethodField() class Meta: model = MinistryReportSettings fields = [ 'id', 'sacco_name', 'registration_number', 'sacco_code', 'physical_address', 'district', 'region', 'registration_date', 'email', 'telephone', 'manager_name', 'chairperson_name', 'total_board_members', 'total_staff', 'report_title', 'board_members_male', 'board_members_female', 'male_staff', 'female_staff', 'created_at', 'updated_at' ] def get_total_board_members(self, obj): return obj.board_members_male + obj.board_members_female def get_total_staff(self, obj): return obj.male_staff + obj.female_staff class Meta: model = MinistryReportSettings fields = [ 'id', 'organisation_id', 'organisation', 'report_title', # SACCO Basic Details 'sacco_name', 'registration_number', 'sacco_code', 'physical_address', 'district', 'region', 'registration_date', 'registration_status', # Contact Information 'email', 'telephone', 'contact_person', # Manager Information 'manager_name', 'manager_email', 'manager_phone', # Chairperson Information 'chairperson_name', 'chairperson_email', 'chairperson_phone', # Chairperson SUPCO Information 'supco_chairperson_name', 'supco_chairperson_email', 'supco_chairperson_phone', # Governance 'board_members_male', 'board_members_female', 'loan_committee_male', 'loan_committee_female', 'supervisory_committee_male', 'supervisory_committee_female', 'vetting_committee_male', 'vetting_committee_female', 'loan_committee_frequency', 'board_meeting_frequency', 'supco_visit_frequency', 'last_agm_date', 'agm_reason', # Staffing 'male_staff', 'female_staff', 'management_staff_male', 'management_staff_female', 'support_staff_male', 'support_staff_female', 'field_officers_male', 'field_officers_female', # Report Header Customization 'report_header_title', 'report_subtitle', 'ministry_department', 'ministry_address', 'ministry_contact', # Additional Notes 'sacco_manager_comments', 'dco_comments', 'additional_notes', # Metadata 'created_at', 'updated_at', ] read_only_fields = ['id', 'created_at', 'updated_at'] def validate_organisation_id(self, value): """Ensure organisation_id is valid""" if value and value <= 0: raise serializers.ValidationError("organisation_id must be a positive integer") return value def validate_board_members_male(self, value): """Ensure board_members_male is non-negative""" if value < 0: raise serializers.ValidationError("board_members_male must be non-negative") return value def validate_board_members_female(self, value): """Ensure board_members_female is non-negative""" if value < 0: raise serializers.ValidationError("board_members_female must be non-negative") return value class MinistryReportDataSerializer(serializers.Serializer): """ Optimized serializer for ministry report data response """ organization_info = serializers.DictField() ministry_settings = MinistryReportSettingsOptimizedSerializer(allow_null=True) customer_statistics = serializers.DictField() savings_statistics = serializers.DictField() savings_products = serializers.ListField() loan_statistics = serializers.DictField() loan_products = serializers.ListField() shares_statistics = serializers.DictField() financial_performance = serializers.DictField() last_updated = serializers.DateTimeField() metadata = serializers.DictField(required=False) class OrganisationSubAccountSerializer(serializers.ModelSerializer): balance_bf = serializers.SerializerMethodField(read_only=True) balance_between = serializers.SerializerMethodField(read_only=True) parent_account = serializers.SerializerMethodField(read_only=True) class Meta: model = OrganisationSubAccount fields = ['id', 'account_code', 'account_name', 'balance_bf', 'balance_between', 'parent_account'] def get_parent_account(self, obj): serializer = OrganisationSubAccountSerializer(instance=obj.parent_id) return serializer.data def get_balance_bf(self, account): start = self.context.get('start_date', None) branch_id = self.context.get('branch_id', None) type = self.context.get('type', None) # Get transactions between date. if start and type == 'balanced': if account.account_line in ['assets', 'liabilities', 'capital']: return get_chart_of_account_balance_at(account, branch_id, start) # get balances for only current year for incomes and expenses end = datetime.strptime(start, "%Y-%m-%d") - timedelta(days=1) end = end.strftime('%Y-%m-%d') start = start.split('-')[0] + '-01-01' return get_chart_of_account_balance_between(account, start, end, branch_id) def get_balance_between(self, account): end = self.context.get('end_date', None) start = self.context.get('start_date', None) branch_id = self.context.get('branch_id', None) type = self.context.get('type', None) # Get transactions between date. if start and end: return get_chart_of_account_balance_between(account, start, end, branch_id) class SavingsAccountBalancesSerializer(serializers.ModelSerializer): balance_raw = serializers.SerializerMethodField(read_only=True) balance_actual = serializers.SerializerMethodField(read_only=True) balance_bf = serializers.SerializerMethodField(read_only=True) balance_at = serializers.SerializerMethodField(read_only=True) latest_date = serializers.SerializerMethodField(read_only=True) last_updated = serializers.SerializerMethodField(read_only=True) def get_latest_date(self, bal): return bal.latest_date.strftime('%Y-%m-%d') def get_last_updated(self, bal): return bal.last_updated.strftime('%Y-%m-%d') def get_balance_raw(self, bal): return (bal.credits + bal.credits_bf) - (bal.debits + bal.debits_bf + bal.min_balance + bal.blocked_amount + bal.blocked_amount_bf + bal.total_held_amount + bal.total_held_amount_bf) def get_balance_actual(self,bal): return (bal.credits + bal.credits_bf) - (bal.debits + bal.debits_bf) def get_balance_bf(self, bal): return bal.credits_bf - bal.debits_bf def get_balance_at(self,bal): return bal.credits - bal.debits class Meta: model = SavingsAccountBalances fields = '__all__' class SavingAccountReportSerializer(serializers.ModelSerializer): customer_branch = serializers.CharField(read_only=True) customer_name = serializers.CharField(source='account_customer.name',read_only=True) customer_member_number = serializers.CharField(source='account_customer.member_number',read_only=True) customer_old_member_number = serializers.CharField(source='account_customer.old_member_number',read_only=True) customer_type = serializers.CharField(source='account_customer.branch_customer_type.customer_type',read_only=True) product_name = serializers.CharField(source='account_product.product_name',read_only=True) default_charge_type = serializers.CharField(source='account_product.default_charge_type',read_only=True) product_name = serializers.CharField(source='account_product.product_name',read_only=True) currency = serializers.CharField(source='account_product.currency',read_only=True) customer_type_name = serializers.CharField(read_only=True, source="account_customer.branch_customer_type.customer_type") customer_address = serializers.SerializerMethodField(read_only=True) account_balance = serializers.SerializerMethodField(read_only=True) saving_account_added_by = serializers.CharField(read_only=True) saving_account_last_updated_by = serializers.CharField(read_only=True) def get_customer_address(self, saving_account): addresses = CustomerAddress.objects.filter(customer=saving_account.account_customer) serializer = CustomerAddressSerializer(addresses, many=True) return serializer.data def get_account_balance(self, account): if not isinstance(account, SavingAccount): account = SavingAccount.objects.get(pk=account) as_at = self.context.get('as_at', None) credits = 0 debits = 0 deposits = 0 withdrawals = 0 transfers_sent = 0 transfers_recieved = 0 share_purchases = 0 loan_payments = 0 balance_raw = 0 balance_actual = 0 if as_at: current_bal = get_account_balance(account,as_at) balance_raw = current_bal['balance_raw'] balance_actual = current_bal['balance_actual'] else: current_bal = get_account_balance(account) balance_raw = current_bal['balance_raw'] balance_actual = current_bal['balance_actual'] if as_at: credits = SystemTransactions.objects.filter(credit_chart=account.account_product.accounts_chart, record_date__lte=as_at, system_transactions__customer_account=account).aggregate(total=Sum('amount'))['total'] else: credits = SystemTransactions.objects.filter( credit_chart=account.account_product.accounts_chart, system_transactions__customer_account=account).aggregate(total=Sum('amount'))['total'] if as_at: debits = SystemTransactions.objects.filter(debit_chart=account.account_product.accounts_chart, record_date__lte=as_at, system_transactions__customer_account=account).aggregate(total=Sum('amount'))['total'] else: debits = SystemTransactions.objects.filter( debit_chart=account.account_product.accounts_chart, system_transactions__customer_account=account).aggregate(total=Sum('amount'))['total'] if as_at: deposits = SystemTransactions.objects.filter(credit_chart=account.account_product.accounts_chart, record_date__lte=as_at, system_transactions__customer_account=account,system_transactions__transaction_type='deposit').aggregate(total=Sum('amount'))['total'] else: deposits = SystemTransactions.objects.filter(credit_chart=account.account_product.accounts_chart, system_transactions__customer_account=account,system_transactions__transaction_type='deposit').aggregate(total=Sum('amount'))['total'] if as_at: withdrawals = SystemTransactions.objects.filter(debit_chart=account.account_product.accounts_chart, record_date__lte=as_at, system_transactions__customer_account=account,system_transactions__transaction_type='withdrawal').aggregate(total=Sum('amount'))['total'] else: withdrawals = SystemTransactions.objects.filter( debit_chart=account.account_product.accounts_chart, system_transactions__customer_account=account,system_transactions__transaction_type='withdrawal').aggregate(total=Sum('amount'))['total'] if as_at: transfers_sent = SystemTransactions.objects.filter(debit_chart=account.account_product.accounts_chart,record_date__lte=as_at, system_transactions__customer_account=account,system_transactions__transaction_type='transfer').aggregate(total=Sum('amount'))['total'] else: transfers_sent = SystemTransactions.objects.filter(debit_chart=account.account_product.accounts_chart, system_transactions__customer_account=account,system_transactions__transaction_type='transfer').aggregate(total=Sum('amount'))['total'] if as_at: transfers_recieved = SystemTransactions.objects.filter(credit_chart=account.account_product.accounts_chart,record_date__lte=as_at, system_transactions__customer_account=account,system_transactions__transaction_type='transfer').aggregate(total=Sum('amount'))['total'] else: transfers_recieved = SystemTransactions.objects.filter(credit_chart=account.account_product.accounts_chart, system_transactions__customer_account=account,system_transactions__transaction_type='transfer').aggregate(total=Sum('amount'))['total'] if as_at: share_purchases = SystemTransactions.objects.filter(shares_system_trasactions__shareholder__customer=account.account_customer,credit_chart=account.account_product.accounts_chart,record_date__lte=as_at).aggregate(total=Sum('amount'))['total'] else: #debit_chart=account.account_product.accounts_chart, share_purchases = SystemTransactions.objects.filter(shares_system_trasactions__shareholder__customer=account.account_customer,credit_chart=account.account_product.accounts_chart).aggregate(total=Sum('amount'))['total'] if as_at: loan_payments = SystemTransactions.objects.filter(debit_chart=account.account_product.accounts_chart,record_date__lte=as_at, main_system_transaction__loan_application__customer=account.account_customer).aggregate(total=Sum('amount'))['total'] else: loan_payments = SystemTransactions.objects.filter(debit_chart=account.account_product.accounts_chart, main_system_transaction__loan_application__customer=account.account_customer).aggregate(total=Sum('amount'))['total'] if not deposits: deposits = 0 if not withdrawals: withdrawals = 0 if not transfers_sent: transfers_sent = 0 if not transfers_recieved: transfers_recieved = 0 if not credits: credits = 0 if not debits: debits = 0 if not share_purchases: share_purchases = 0 if not loan_payments: loan_payments = 0 other_credits = credits - (deposits + transfers_recieved) if other_credits <1: other_credits = 0 other_debits = debits - (withdrawals + loan_payments + share_purchases + transfers_sent) if other_debits <1: other_debits = 0 return { 'total_deposits': deposits, 'total_withdrawals': withdrawals, 'balance': f"{balance_raw}", 'balance_raw': balance_raw, 'balance_actual': balance_actual, 'transfers_sent': transfers_sent, 'transfers_recieved': transfers_recieved, 'transfers': transfers_sent + transfers_recieved, 'share_purchases':share_purchases, 'loan_payments':loan_payments, 'other_credits':other_credits, 'other_debits':other_debits } class Meta: model = SavingAccount fields = '__all__''' class ClearedOffLoansReportSerializer(serializers.ModelSerializer): class Meta: model = ClearedOffLoansReport fields = '__all__' class RescheduledLoansReportSerializer(serializers.ModelSerializer): loan_balance = serializers.SerializerMethodField() maturity_date = serializers.SerializerMethodField() def get_loan_balance(self, obj): principal_bal, interest_bal, penalty_bal, written_off_amount = loan_balance(obj.id) return {"principal_bal":principal_bal,"interest_bal":interest_bal,"penalty_bal":penalty_bal} def get_maturity_date(self, obj): if int(obj.loan_period) > (12 * 10): delta = relativedelta(months=(12 * 10)) else: delta = relativedelta(months=obj.loan_period) if obj.period_type == 'y': if int(obj.loan_period) > 10: delta = relativedelta(years=10) else: delta = relativedelta(years=obj.loan_period) elif obj.period_type == 'q': if int(obj.loan_period) > (4 * 10): delta = relativedelta(years=(4 * 10)) else: delta = relativedelta(months=obj.loan_period) elif obj.period_type == 'w': delta = relativedelta(weeks=obj.loan_period) elif obj.period_type == 'bw': delta = relativedelta(weeks=obj.loan_period) elif obj.period_type == 'd': delta = relativedelta(days=obj.loan_period) return obj.reschedule_date + delta class Meta: model = RescheduledLoansReport fields = '__all__'