from rest_framework import serializers from datetime import datetime, timedelta from django.utils.timezone import make_aware from questbanker_api.utils import get_current_user from .models import * from integrations.models import * from django.db.models import Sum from django.db import transaction from .helper import loan_schedules_with_payments, loan_balance, loan_amounts_paid, release_with_held_shares_savings, loan_schedules_dues, get_loan_schedule_due_date, cancel_loan_approval from customers.serializers import CustomerFilesSerializer from locations.models import CustomerAddress, LocationView from integrations.models import * from savings.savings_bal_helper import get_account_balance class LoanProductChargesSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_charge_added_by = serializers.CharField(read_only=True) chart_code = serializers.CharField(read_only=True, source="chart.account_code") chart_name = serializers.CharField(read_only=True, source="chart.account_name") class Meta: model = LoanProductCharges fields = '__all__' class LoanProductsSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_product_added_by = serializers.CharField(read_only=True) organisation = serializers.PrimaryKeyRelatedField(read_only=True, required=False) loan_product_charges = LoanProductChargesSerializer(many=True, read_only=True) chart = serializers.PrimaryKeyRelatedField(read_only=True, required=False) class Meta: model = LoanProduct fields = '__all__' def update(self, instance, validated_data): for (key, value) in validated_data.items(): setattr(instance, key, value) instance.save() # updated the disbursed loans arrear_grace_period = instance.arrears_period arrears_period_type = instance.arrears_period_type write_off_grace_period = instance.write_off_period write_off_period_type = instance.write_off_period_type penalty_rate = instance.penalty_rate penalty_type = instance.penalty_type penalty_period_type = instance.penalty_period_type penalty_grace_period = instance.penalty_grace_period penalty_grace_period_type = instance.penalty_grace_period_type data = {"arrear_grace_period":arrear_grace_period, "arrears_period_type":arrears_period_type, "write_off_grace_period":write_off_grace_period, "write_off_period_type":write_off_period_type, "penalty_rate":penalty_rate, "penalty_type":penalty_type, "penalty_period_type":penalty_period_type, "penalty_grace_period":penalty_grace_period, "penalty_grace_period_type":penalty_grace_period_type} LoanApplicationDisbursement.objects.filter(loan_application__loan_application_product=instance, loan_application__status='disbursed').update(**data) auto_payments = instance.auto_payments auto_pay_penalty = instance.auto_pay_penalty payments = {"auto_payments":auto_payments, "auto_pay_penalty":auto_pay_penalty} LoanApplication.objects.filter(loan_application_product=instance).update(**payments) return instance class LoanIncomeSourceSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_income_source_added_by = serializers.CharField(read_only=True) class Meta: model = LoanIncomeSource fields = '__all__' class GreenFinanceValueChainNodeSerializer(serializers.ModelSerializer): class Meta: model = GreenFinanceValueChainNode fields = ['id', 'value_chain_node', 'description'] read_only_fields = ['id', 'value_chain_node', 'description'] class GreenFinanceValueChainSerializer(serializers.ModelSerializer): created_by_name = serializers.CharField(source='created_by.username', read_only=True) updated_by_name = serializers.CharField(source='updated_by.username', read_only=True) # Include the linked nodes nodes = GreenFinanceValueChainNodeSerializer(many=True, read_only=True) class Meta: model = GreenFinanceValueChain fields = [ 'id', 'name', 'description', 'is_active', 'created_at', 'updated_at', 'created_by_name', 'updated_by_name', 'nodes' ] read_only_fields = ['id', 'created_at', 'updated_at', 'created_by_name', 'updated_by_name', 'nodes'] # def create(self, validated_data): # user = self.context['request'].user # org = user.user_organisation_branch.branch_organisation # value_chain = GreenFinanceValueChain.objects.create( # organisation=org, # created_by=user, # **validated_data # ) # # Attach the three fixed nodes # default_nodes = GreenFinanceValueChainNode.objects.filter( # value_chain_node__in=['production', 'processing', 'marketing'] # ) # value_chain.nodes.set(default_nodes) # return value_chain def update(self, instance, validated_data): user = self.context['request'].user for attr, value in validated_data.items(): setattr(instance, attr, value) instance.updated_by = user instance.save() return instance class LoanApplicationSecuritySerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_app_sec_added_by = serializers.CharField(read_only=True) class Meta: model = LoanApplicationSecurity fields = '__all__' class LoanApplicationApprovalSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_approval_added_by = serializers.CharField(read_only=True) loan_approval_added_by_name = serializers.SerializerMethodField(read_only=True) class Meta: model = LoanApplicationApproval fields = '__all__' def get_loan_approval_added_by_name(self, obj): full_name = obj.loan_approval_added_by.user_staff.name return full_name class LoanApplicationDisbursementSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_disburse_added_by = serializers.CharField(read_only=True) loan_disburser_added_by_name = serializers.SerializerMethodField(read_only=True) class Meta: model = LoanApplicationDisbursement fields = '__all__' def get_loan_disburser_added_by_name(self, obj): full_name = obj.loan_disburse_added_by.user_staff.name return full_name class LoanGuarantorsSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_guarantor_added_by = serializers.CharField(read_only=True) customer_full_name = serializers.CharField(read_only=True, source="guarantor_customer.name") customer_member_number = serializers.CharField(read_only=True, source="guarantor_customer.member_number") customer_old_member_number = serializers.CharField(read_only=True, source="guarantor_customer.old_member_number") telephone = serializers.CharField(read_only=True, source="guarantor_customer.telephone") photo = serializers.SerializerMethodField() def get_photo(self, obj): # Fetch the profile picture using the correct related name photo_file = obj.guarantor_customer.customer_file.filter(file_type="profile_pic", is_current=True).first() if photo_file: return photo_file.url return None class Meta: model = LoanGuarantors fields = '__all__' class OrganisationClientNonMembersSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) organisation_non_member_added_by = serializers.CharField(read_only=True) organisation_branch = serializers.PrimaryKeyRelatedField(read_only=True, required=False) class Meta: model = OrganisationClientNonMembers fields = '__all__' class NonMemberLoanGuarantorsSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) organ_non_member_guarantor_added_by = serializers.CharField(read_only=True) class Meta: model = NonMemberLoanGuarantors fields = '__all__' class LoanRepaymentScheduleSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_schedule_added_by = serializers.CharField(read_only=True) interest_waived = serializers.SerializerMethodField() def get_interest_waived(self, obj): interest_waived = LoanInterestWaivered.objects.filter(loan_application=obj.loan_application, loan_repayment_schedule=obj).aggregate(total_amount=Sum('amount'))['total_amount'] return interest_waived if interest_waived else 0 class Meta: model = LoanRepaymentSchedule fields = '__all__' class LoanMainTransactionsSerializer(serializers.ModelSerializer): class Meta: model = LoanMainTransactions fields = '__all__' class LoanApplicationWithHoldSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_withhold_added_by = serializers.CharField(read_only=True) account_details = serializers.SerializerMethodField() def get_account_details(self, obj): if obj.account: savings_account = SavingAccount.objects.filter(id=obj.account.id).first() if savings_account: return {"account_no":savings_account.account_no, "account_name":savings_account.account_product.accounts_chart.account_name} return {} class Meta: model = LoanApplicationWithHold fields = '__all__' class LoanApplicationsSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_app_added_by = serializers.CharField(read_only=True) organisation_branch = serializers.PrimaryKeyRelatedField(read_only=True, required=False) customer_full_name = serializers.CharField(read_only=True, source="customer.name") customer_telephone = serializers.CharField(read_only=True, source="customer.telephone") customer_member_number = serializers.CharField(read_only=True, source="customer.member_number") customer_old_member_number = serializers.CharField(read_only=True, source="customer.old_member_number") customer_type = serializers.CharField(read_only=True, source="customer.branch_customer_type.customer_type") loan_officer_full_name = serializers.SerializerMethodField(read_only=True) organisation_branch_name = serializers.CharField(read_only=True, source="organisation_branch.name") loan_application_approval = serializers.SerializerMethodField() loan_application_disbursement = serializers.SerializerMethodField() loan_application_product_data = serializers.SerializerMethodField() class Meta: model = LoanApplication fields = '__all__' def get_loan_officer_full_name(self, obj): full_name = obj.loan_officer.name return full_name def get_loan_application_approval(self, obj): approval = LoanApplicationApproval.objects.filter(loan_application=obj).first() if approval: serializer = LoanApplicationApprovalSerializer(approval) return serializer.data return {} def get_loan_application_disbursement(self, obj): disbursement = LoanApplicationDisbursement.objects.filter(loan_application=obj).first() if disbursement: serializer = LoanApplicationDisbursementSerializer(disbursement) return serializer.data return {} def get_loan_application_product_data(self, obj): product = LoanProduct.objects.get(pk=obj.loan_application_product.id) if product: serializer = LoanProductsSerializer(product) return serializer.data return {} class LoanApplicationSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_app_added_by = serializers.CharField(read_only=True) organisation_branch = serializers.PrimaryKeyRelatedField(read_only=True, required=False) customer_full_name = serializers.CharField(read_only=True, source="customer.name") customer_telephone = serializers.CharField(read_only=True, source="customer.telephone") customer_nin = serializers.CharField(read_only=True, source="customer.nin") customer_member_number = serializers.CharField(read_only=True, source="customer.member_number") customer_old_member_number = serializers.CharField(read_only=True, source="customer.old_member_number") customer_type = serializers.CharField(read_only=True, source="customer.branch_customer_type.customer_type") loan_officer_full_name = serializers.SerializerMethodField(read_only=True) organisation_branch_name = serializers.CharField(read_only=True, source="organisation_branch.name") loan_sector_name = serializers.CharField(read_only=True, source="loan_sector.name") loan_sub_sector_name = serializers.CharField(read_only=True, source="loan_sub_sector.name") loan_income_source = LoanIncomeSourceSerializer(many=True, read_only=True) loan_application_security = LoanApplicationSecuritySerializer(many=True, read_only=True) loan_application_approval = serializers.SerializerMethodField() loan_application_write_off = serializers.SerializerMethodField() loan_application_disbursement = serializers.SerializerMethodField() loan_application_product_data = serializers.SerializerMethodField() last_recovery = serializers.SerializerMethodField() loan_application_guarantors = LoanGuarantorsSerializer(many=True, read_only=True) non_member_loan_guarantors = serializers.SerializerMethodField() loan_balance = serializers.SerializerMethodField() days_to_write_off = serializers.SerializerMethodField() loan_schedule_due_data = serializers.SerializerMethodField() rescheduled_loan_data = serializers.SerializerMethodField() customer_files = serializers.SerializerMethodField() customer_address_details = serializers.SerializerMethodField() loan_app_withhold = LoanApplicationWithHoldSerializer(many=True, read_only=True) green_finance_loan_details = serializers.SerializerMethodField() loan_cycles_details = serializers.SerializerMethodField() value_chain = serializers.PrimaryKeyRelatedField( queryset=GreenFinanceValueChain.objects.all(), allow_null=True, required=False ) value_chain_node = serializers.PrimaryKeyRelatedField( queryset=GreenFinanceValueChainNode.objects.all(), allow_null=True, required=False ) classification_item_loan = serializers.PrimaryKeyRelatedField( queryset=ClassificationItemLoans.objects.all(), required=False, allow_null=True ) class_id = serializers.IntegerField(write_only=True, required=False) class_item_id = serializers.IntegerField(write_only=True, required=False) class Meta: model = LoanApplication fields = '__all__' def get_green_finance_loan_details(self, obj): green_finance = ClassificationItemLoans.objects.filter(classification_item_loan=obj)\ .select_related("classification_item__classification")\ .first() if green_finance: return { "purpose": green_finance.classification_item.name, "indicator": green_finance.classification_item.classification.name, } return {} def get_value_chain_nodes(self, obj): """ Returns the list of node names for the linked value chain """ if obj.value_chain: return list(obj.value_chain.nodes.values_list('value_chain_node', flat=True)) return [] def validate(self, data): value_chain = data.get("value_chain") value_chain_node = data.get("value_chain_node") loan_sector = data.get("loan_sector") loan_sub_sector = data.get("loan_sub_sector") # If sub-sector is provided, auto-assign its parent sector and validate consistency if loan_sub_sector: if loan_sector and loan_sub_sector.loan_sector_id != loan_sector.id: raise serializers.ValidationError( "The selected sub-sector does not belong to the selected sector." ) data["loan_sector"] = loan_sub_sector.loan_sector # Determine purpose and indicator if self.instance: # Existing loan classification_item_loan = ClassificationItemLoans.objects.filter(classification_item_loan=self.instance).first() purpose = classification_item_loan.classification_item.name if classification_item_loan else None indicator = classification_item_loan.classification_item.classification.name if classification_item_loan else None else: # New loan class_item_id = data.get("class_item_id") class_id = data.get("class_id") purpose = ClassificationItems.objects.get(id=class_item_id).name if class_item_id else None indicator = Classifications.objects.get(id=class_id).name if class_id else None if (value_chain or value_chain_node) and (not purpose or not indicator): raise serializers.ValidationError( "You can only assign a value chain and value chain node if purpose and indicator are set." ) return data def create(self, validated_data): class_item_id = validated_data.pop("class_item_id", None) class_id = validated_data.pop("class_id", None) loan = LoanApplication.objects.create(**validated_data) # If class_item_id is provided, create a ClassificationItemLoans record if class_item_id: ClassificationItemLoans.objects.create( classification_item_id=class_item_id, classification_item_loan=loan ) return loan def update(self, instance, validated_data): class_item_id = validated_data.pop("class_item_id", None) class_id = validated_data.pop("class_id", None) for attr, value in validated_data.items(): setattr(instance, attr, value) instance.save() # Ensure ClassificationItemLoans exists for this loan if green if class_item_id: ClassificationItemLoans.objects.get_or_create( classification_item_id=class_item_id, classification_item_loan=instance ) return instance # def update(self, instance, validated_data): # # Check related classification for purpose/indicator # classification_item_loan = ClassificationItemLoans.objects.filter(classification_item_loan=instance).first() # purpose = classification_item_loan.classification_item.name if classification_item_loan else None # indicator = classification_item_loan.classification_item.classification.name if classification_item_loan else None # if ("value_chain" in validated_data or "value_chain_node" in validated_data) and (not purpose or not indicator): # raise serializers.ValidationError( # "Cannot assign a value chain or value chain node unless the loan has a purpose and indicator." # ) # Normal update for key, value in validated_data.items(): setattr(instance, key, value) instance.save() if instance.status == "cleared_off": release_with_held_shares_savings(instance) if instance.status == "pending": cancel_loan_approval(instance) return instance def get_loan_officer_full_name(self, obj): full_name = obj.loan_officer.name return full_name def get_customer_files(self, obj): customer_files = CustomerFiles.objects.filter(customer=obj.customer).all() return CustomerFilesSerializer(customer_files, many=True).data # def get_green_finance_loan_details(self, obj): # green_finance = ( # ClassificationItemLoans.objects # .filter(classification_item_loan=obj) # FK to loan app # .select_related("classification_item__classification") # .first() # ) # if green_finance: # return { # "id": green_finance.id, # "purpose": green_finance.classification_item.name, # "indicator": green_finance.classification_item.classification.name, # } # return {} def get_loan_cycles_details(self, loan_obj): count = LoanApplication.objects.filter(customer=loan_obj.customer, status__in=['disbursed', 'cleared_off','written_off']).count() sum_amount = LoanApplication.objects.filter(customer=loan_obj.customer, status__in=['disbursed', 'cleared_off','written_off']).aggregate(total=Sum('loan_amount'))['total'] return {"loan_cysles":count, "total_amount_cycles":sum_amount if sum_amount else 0} def get_loan_application_approval(self, obj): approval = LoanApplicationApproval.objects.filter(loan_application=obj).first() if approval: serializer = LoanApplicationApprovalSerializer(approval) return serializer.data return {} def get_loan_application_disbursement(self, obj): disbursement = LoanApplicationDisbursement.objects.filter(loan_application=obj).first() if disbursement: serializer = LoanApplicationDisbursementSerializer(disbursement) return serializer.data return {} def get_loan_application_write_off(self, obj): written_off_loan = LoanWrittenOff.objects.filter(loan_application=obj).first() if written_off_loan: serializer = LoanWrittenOffSerializer(written_off_loan) return serializer.data return {} def get_last_recovery(self, obj): loan_recovery = LoanRecovery.objects.filter(loan_application=obj).order_by("-loan_main_transaction__system_transaction__record_date").first() if loan_recovery: return LoanRecoverySerializer(loan_recovery).data else: return {} def get_loan_application_product_data(self, obj): product = LoanProduct.objects.get(pk=obj.loan_application_product.id) if product: serializer = LoanProductsSerializer(product) return serializer.data return {} def get_customer_address_details(self, obj): address = CustomerAddress.objects.filter(customer=obj.customer).first() if address: if address.village: location_view = LocationView.objects.filter(id=address.village.id).first() if location_view: return {"village":location_view.villagename, "parish":location_view.parishname, "district":location_view.districtname, "region":location_view.regionname} else: return {"village":address.physical_address, "parish":'', "district":'', "region":address.region} return {} def get_non_member_loan_guarantors(self, obj): non_members = NonMemberLoanGuarantors.objects.filter(loan_application=obj) response = [] for non_member in non_members: response.append( {"id":non_member.id, "client_id":non_member.organisation_client_non_member.id, "status":non_member.status, "client_name":non_member.organisation_client_non_member.client_name, "phone":non_member.organisation_client_non_member.phone, "identity_no":non_member.organisation_client_non_member.identity_no, "physical_address":non_member.organisation_client_non_member.physical_address, "nationality":non_member.organisation_client_non_member.nationality, "dob":non_member.organisation_client_non_member.dob, "gender":non_member.organisation_client_non_member.gender, "occupation":non_member.organisation_client_non_member.occupation, "identity_type":non_member.organisation_client_non_member.identity_type, "cust_sign":non_member.organisation_client_non_member.cust_sign, "image":non_member.organisation_client_non_member.image}) return response def get_loan_balance(self, obj): principal_bal, interest_bal, penalty_bal, written_off_amount = loan_balance(obj.id) recovered_princ_amount = 0 recovered_interest = 0 recovered_princ = LoanMainTransactions.objects.filter(loan_application=obj, transaction_type='LoanRecovery' ).aggregate(total=Sum('amount'))['total'] if recovered_princ: recovered_princ_amount = recovered_princ if recovered_princ > principal_bal: recovered_interest = min(recovered_princ - principal_bal, interest_bal) principal_bal = max(0, principal_bal - recovered_princ) interest_bal = max(0, interest_bal - recovered_interest) else: principal_bal -= recovered_princ # Ensure total balance reflects waiver adjustments total_balance = principal_bal + interest_bal total_with_penalty = total_balance + penalty_bal return {"principal_bal":principal_bal,"interest_bal":interest_bal,"penalty_bal":penalty_bal, "total":total_balance, "total_with_penalty": total_with_penalty,"recovered_principal":recovered_princ_amount} def get_days_to_write_off(self,loan_details): days_to_write_off = 0 loan_disbursement_details = LoanApplicationDisbursement.objects.filter(loan_application=loan_details).first() if loan_disbursement_details: loan_approval_details = LoanApplicationApproval.objects.filter(loan_application=loan_details).first() period_type = loan_approval_details.period_type loan_period = loan_approval_details.loan_period write_off_period = loan_details.loan_application_product.write_off_period write_off_period_type = loan_details.loan_application_product.write_off_period write_off_days = self.duration_for_a_period(period_type,loan_period) + self.duration_for_a_period(write_off_period_type, write_off_period) + 1 write_off_date = loan_disbursement_details.loan_start_date + timedelta(days=write_off_days) days_to_write_off = (datetime.strptime(write_off_date.strftime("%m/%d/%Y"),"%m/%d/%Y") - datetime.strptime(datetime.now().strftime("%m/%d/%Y"),"%m/%d/%Y")).days return days_to_write_off def duration_for_a_period(self,period_type, period): if period_type == 'w': return period * 7 elif period_type == 'd': return period * 1 elif period_type == 'y': return period * 365 elif period_type == 'q': return period * 90 else: return period * 30 def get_loan_schedule_due_data(self, loan_details): if loan_details.status == 'pending' or loan_details.status == 'approved' or loan_details.status == 'rejected': return {"princ_due": 0, "interest_due": 0, "penalty_due": 0, "total_due": 0, "number_of_schedules":0} schedules_with_payments = loan_schedules_with_payments(loan_details.id)['schedule_due'] if schedules_with_payments: return {"princ_due": schedules_with_payments['principal_expected'], "interest_due": schedules_with_payments['interest_expected'], "penalty_due": schedules_with_payments['penalty_expected'], "total_due": schedules_with_payments['total_payment'], "number_of_schedules":schedules_with_payments['number_of_schedules']} return {"princ_due": 0, "interest_due": 0, "penalty_due": 0, "total_due": 0, "number_of_schedules":0} def get_rescheduled_loan_data(self, loan_details): rescheduled_loan_details = RescheduledLoans.objects.filter(loan_application=loan_details).order_by('-id').first() if not rescheduled_loan_details: return {"principal_expected": 0, "interest_expected":0} # get all past payments loan_schedules = LoanRepaymentSchedule.objects.filter(loan_application=loan_details, status="active").aggregate(total_interest_expected=Sum('interest_expected'), total_principal_expected = Sum('principal_expected') ) total_interest_expected = loan_schedules['total_interest_expected'] if loan_schedules['total_interest_expected'] else 0 total_principal_expected = loan_schedules['total_principal_expected'] if loan_schedules['total_principal_expected'] else 0 return {"principal_expected": total_principal_expected, "interest_expected":total_interest_expected} class LoanPenaltyWaiveredSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) loan_main_transaction = serializers.CharField(read_only=True) loan_penalty_waivered_added_by = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) class Meta: model = LoanPenaltyWaivered fields = '__all__' class LoanInterestWaiveredSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) loan_main_transaction = serializers.CharField(read_only=True) loan_interest_waivered_added_by = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_repayment_schedule = serializers.CharField(read_only=True) class Meta: model = LoanInterestWaivered fields = '__all__' class LoanWrittenOffSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) loan_system_transaction = serializers.CharField(read_only=True) loan_writeoff_added_by = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) class Meta: model = LoanWrittenOff fields = '__all__' class LoanRecoverySerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) loan_main_transaction = serializers.CharField(read_only=True) recovery_added_by = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_application = serializers.CharField(read_only=True) loan_main_transaction = serializers.CharField(read_only=True) transaction_date = serializers.CharField(read_only=True, source = 'loan_main_transaction.record_date') reference_no = serializers.CharField(read_only=True, source = 'loan_main_transaction.ref_no') class Meta: model = LoanRecovery fields = '__all__' def get_loan_application(self,obj): loan_application_id = LoanApplication.objects.filter(id=obj.loan_application.id).first() if loan_application_id: serializer = LoanApplicationSerializer(loan_application_id) return serializer.data return {} def get_recovery_added_by_name(self, obj): full_name = obj.recovery_added_by.user_staff.name return full_name def get_loan_income_source(self,obj): loan_income = LoanIncomeSource.objects.filter(id=obj.loan_application_id).first() return loan_income def get_loan_write_off(self,obj): write_off = LoanWrittenOff.objects.filter(id=obj.loan_application.id).first() return write_off class LoanApplicationsViewSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) loan_application_approval = serializers.SerializerMethodField() loan_application_disbursement = serializers.SerializerMethodField() class Meta: model = LoanApplicationsView fields = '__all__' def get_loan_application_approval(self, obj): approval = LoanApplicationApproval.objects.filter( loan_application__id=obj.id, ).first() if approval: serializer = LoanApplicationApprovalSerializer(approval) return serializer.data return {} def get_loan_application_disbursement(self, obj): disbursement = LoanApplicationDisbursement.objects.filter( loan_application__id=obj.id, ).first() if disbursement: serializer = LoanApplicationDisbursementSerializer(disbursement) return serializer.data return {} class RescheduledLoansSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) loan_reschedule_added_by = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) class Meta: model = RescheduledLoans fields = '__all__' class LoansTopUpSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) loan_topup_added_by = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) class Meta: model = LoanTopUp fields = '__all__' class LoanPaymentsSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) amount = serializers.CharField(read_only=True, source="loan_main_transaction.system_transaction.amount") reference_no = serializers.CharField(read_only=True, source="loan_main_transaction.system_transaction.reference_no") voucher_no = serializers.CharField(read_only=True, source="loan_main_transaction.system_transaction.voucher_no") transaction_type = serializers.CharField(read_only=True, source="loan_main_transaction.transaction_type") record_date = serializers.CharField(read_only=True, source="loan_main_transaction.system_transaction.record_date") loan_application = serializers.SerializerMethodField() loan_payments_added_by_name = serializers.CharField(read_only=True, source="loan_payments_added_by.user_staff.name") member_number = serializers.CharField(read_only=True, source="loan_application.customer.member_number") class Meta: model = LoanPayments fields = '__all__' def get_loan_application(self,obj): loan_application_id = LoanApplication.objects.filter(id=obj.loan_application.id).first() if loan_application_id: serializer = LoanApplicationSerializer(loan_application_id) return serializer.data return {} class LoanPenaltySerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) loan_penality_added_by = serializers.CharField(read_only=True) loan_repayment_schedule = serializers.CharField(required=False) class Meta: model = LoanPenalty fields = '__all__' class LoanSectorsSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) loan_sector_added_by = serializers.CharField(read_only=True) organisation = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) class Meta: model = LoanSectors fields = '__all__' class LoanSubSectorsSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) loan_sub_sector_added_by = serializers.CharField(read_only=True) date_added = serializers.CharField(read_only=True) last_updated = serializers.CharField(read_only=True) loan_sector_name = serializers.CharField(read_only=True, source='loan_sector.name') class Meta: model = LoanSubSectors fields = '__all__' class LoansViewSerializer(serializers.ModelSerializer): id = serializers.CharField(read_only=True) loan_application_approval = serializers.SerializerMethodField() loan_application_disbursement = serializers.SerializerMethodField() loan_payments = serializers.SerializerMethodField() loan_balance = serializers.SerializerMethodField() loan_application_security = serializers.SerializerMethodField() loan_due_date = serializers.SerializerMethodField() is_loan_written_off = serializers.SerializerMethodField() loan_added_by_full_name = serializers.SerializerMethodField() loan_cycles_details = serializers.SerializerMethodField() green_finance_details = serializers.SerializerMethodField() location_details = serializers.SerializerMethodField() date_of_birth = serializers.SerializerMethodField() class Meta: model = LoansView fields = '__all__' def get_date_of_birth(self, loan_obj): dob = None dob_fields = CustomerTypeField.objects.filter(customer_reg_field__id__in=[11,30], organisation__id=loan_obj.organisation_id) for dob_field in dob_fields: age_value = CustomerFieldMeta.objects.filter(customer__id=loan_obj.customer_id, customer_field=dob_field).first() if age_value: dob = age_value.value return dob def get_location_details(self, loan_obj): location = '' address = CustomerAddress.objects.filter(customer__id=loan_obj.customer_id).first() if address: if address.village: location = address.village.parish.subcounty.county.district.districtname else: location = address.physical_address return location def get_green_finance_details(self, loan_obj): response = {} green_finance = ClassificationItemLoans.objects.filter(classification_item_loan__id=loan_obj.id).first() if green_finance: response = {"id":green_finance.id, "class_name":green_finance.classification_item.classification.name, "item_name":green_finance.classification_item.name} return response def get_loan_cycles_details(self, loan_obj): count = LoanApplication.objects.filter(customer__id=loan_obj.customer_id, status__in=['disbursed', 'cleared_off','written_off']).count() sum_amount = LoanApplication.objects.filter(customer__id=loan_obj.customer_id, status__in=['disbursed', 'cleared_off','written_off']).aggregate(total=Sum('loan_amount'))['total'] return {"loan_cysles":count, "total_amount_cycles":sum_amount if sum_amount else 0} def get_loan_added_by_full_name(self, obj): loan_application = LoanApplication.objects.filter(id=obj.id).first() full_name = "" if loan_application.loan_app_added_by: full_name = loan_application.loan_app_added_by.user_staff.name return full_name def get_loan_application_approval(self, obj): approval = LoanApplicationApproval.objects.filter(loan_application__id=obj.id).first() if approval: serializer = LoanApplicationApprovalSerializer(approval) return serializer.data return {} def get_loan_application_disbursement(self, obj): disbursement = LoanApplicationDisbursement.objects.filter(loan_application__id=obj.id).first() if disbursement: serializer = LoanApplicationDisbursementSerializer(disbursement) return serializer.data return {} def get_loan_payments(self, obj): as_at = self.context.get('as_at', None) principal_paid, interest_paid, penalty_paid = loan_amounts_paid(obj.id, True, as_at) return {"principal_paid":principal_paid,"interest_paid":interest_paid,"penalty_paid":penalty_paid, "total":principal_paid+interest_paid, "total_with_penalty": principal_paid+interest_paid+penalty_paid} def get_loan_balance(self, obj): as_at = self.context.get('as_at', None) principal_bal, interest_bal, penalty_bal, written_off_amount = loan_balance(obj.id, as_at, True) return {"principal_bal":principal_bal,"interest_bal":interest_bal,"penalty_bal":penalty_bal, "total":principal_bal+interest_bal, "total_with_penalty": principal_bal+interest_bal+penalty_bal, "written_off_amount":written_off_amount} def get_loan_application_security(self, obj): loan_security = LoanApplicationSecurity.objects.filter(loan_application__id=obj.id).first() if loan_security: return {"description":loan_security.description, "value":loan_security.value} return {} def get_loan_due_date(self, obj): loan_last_schedule = LoanRepaymentSchedule.objects.filter(loan_application__id=obj.id, status='active').order_by('-id').first() if loan_last_schedule: return loan_last_schedule.expected_date return '' def get_is_loan_written_off(self, obj): response = 'False' loan_application = LoanApplication.objects.get(pk=obj.id) if loan_application and loan_application.status == 'written_off': as_at = self.context.get('as_at', None) written_off_record = LoanWrittenOff.objects.filter(loan_application=loan_application, write_off_date__date__lte=as_at).first() if written_off_record: response = 'True' return response class LoanRepaymentviewSerializer2(serializers.ModelSerializer): class Meta: model = LoanInstallmentRepaymentview fields = '__all__' class LoanRepaymentviewSerializer(serializers.ModelSerializer): class Meta: model = LoanRepaymentview fields = '__all__' class LoanLossProvisionSerializer(serializers.ModelSerializer): class Meta: model = LoanLossProvision fields = '__all__' class LoansRepaymentViewSerializer(serializers.ModelSerializer): loan_dues = serializers.SerializerMethodField(read_only=True) par_details = serializers.SerializerMethodField(read_only=True) loan_aging_details = serializers.SerializerMethodField(read_only=True) loan_expected_repayment_details = serializers.SerializerMethodField(read_only=True) loan_balance = serializers.SerializerMethodField() customer_address = serializers.SerializerMethodField() savings_details = serializers.SerializerMethodField(read_only=True) class Meta: model = LoansRepaymentView fields = '__all__' def get_loan_expected_repayment_details(self, obj): response = {"expected_date":'', "due_date":''} loan_report = self.context.get('type', None) as_at = self.context.get('as_at', None) if loan_report == 'loans_expected_repayment': response = get_loan_schedule_due_date(obj.id, as_at) return response def get_customer_address(self, obj): address = CustomerAddress.objects.filter(customer__id=obj.customer_id).first() if address: if address.village: location_view = LocationView.objects.filter(id=address.village.id).first() if location_view: return {"village":location_view.villagename, "parish":location_view.parishname, "district":location_view.districtname, "region":location_view.regionname} else: return { "village": address.physical_address, "parish":'', "district":'', "region": address.region } return {} def get_loan_dues(self, obj): response = {"princ_due": 0, "interest_due": 0, "penalty_due": 0, "total_due": 0} loan_report = self.context.get('type', None) as_at = self.context.get('as_at', None) start_date = self.context.get('start_date', None) if (loan_report == 'arrears_report' or loan_report == 'par_report' or loan_report == 'par_aging_report' or loan_report == 'dues_repayment_report' or loan_report == 'loans_expected_repayment' or loan_report == 'loan_savings_report') and as_at: bulk_dues = self.context.get('bulk_dues') if bulk_dues is not None: response = bulk_dues.get(obj.id, response) else: response = loan_schedules_dues(obj.id, as_at, start_date) return response def get_loan_balance(self, obj): bulk_recovery = self.context.get('bulk_recovery') if bulk_recovery is not None: return {"recovered_principal": bulk_recovery.get(obj.id) or 0} recovered_princ_amount = 0 recovered_princ = LoanMainTransactions.objects.filter(loan_application=obj.id, transaction_type='LoanRecovery').aggregate(total=Sum('amount'))['total'] if recovered_princ: recovered_princ_amount = recovered_princ return {"recovered_principal": recovered_princ_amount} def get_par_details(self, obj): loan_report = self.context.get('type', None) outstanding_principal_bal = self.context.get('outstanding_principal_bal', 0) as_at = self.context.get('as_at', None) if (loan_report == 'par_report' and as_at and outstanding_principal_bal > 0): princ_paid = obj.princ_paid if obj.princ_paid and obj.princ_paid > 0 else 0 total_principal_expected = obj.total_principal_expected if obj.total_principal_expected and obj.total_principal_expected > 0 else 0 reschedule = RescheduledLoans.objects.filter(loan_application__id=obj.id).order_by('-id').first() if reschedule: total_principal_expected = reschedule.principal_amount return {"par_percent": round(( (total_principal_expected - princ_paid) / outstanding_principal_bal) * 100, 3), "princ_bal":outstanding_principal_bal} return {"par_percent": 0, "princ_bal":0} def get_loan_aging_details(self, obj): response = {"l_1_30":0, "l_31_60":0,"l_61_90":0,"l_91_120":0,"l_121_180":0,"m_180":0, "princ_due":0, "int_due":0} loan_report = self.context.get('type', None) as_at = self.context.get('as_at', None) total_principal_due = 0 total_interest_due = 0 loan_expected_princ = 0 last_aging = "l_1_30" par_aging_total_princ = 0 if loan_report == 'loan_aging_report' or loan_report == 'par_aging_report': loan_details = LoanApplication.objects.get(pk=obj.id) extra_date_added = {"date_added__date__lte": as_at } extra_payment_added = {"payment_date__date__lte": as_at } loan_disbursement = LoanApplicationDisbursement.objects.filter(loan_application=loan_details).first() loan_expected_princ = loan_disbursement.total_principal_expected if loan_disbursement else 0 loan_rescheduled = RescheduledLoans.objects.filter(loan_application=loan_details).order_by('-id').first() if loan_rescheduled: loan_expected_princ = loan_rescheduled.principal_amount loan_repayment_schedules = LoanRepaymentSchedule.objects.filter(loan_application=loan_details, status="active").order_by('id') interest_waivered_bal = 0 for loan_repayment_schedule in loan_repayment_schedules: interest_waived = LoanInterestWaivered.objects.filter(loan_application=loan_details, loan_repayment_schedule=loan_repayment_schedule, **extra_date_added).aggregate(total_amount=Sum('amount'))['total_amount'] total_interest_waived = interest_waived if interest_waived else 0 loan_payments_totals = LoanPayments.objects.filter(loan_application = loan_details, loan_repayment_schedule=loan_repayment_schedule, payment_status='normal', **extra_payment_added).aggregate(total_int_paid=Sum('int_paid'), total_princ_paid = Sum('princ_paid') , total_penalty_paid = Sum('penalty_paid')) is_paid_off = 'false' total_princ_paid = loan_payments_totals['total_princ_paid'] if loan_payments_totals['total_princ_paid'] is not None else 0 total_int_paid = loan_payments_totals['total_int_paid'] if loan_payments_totals['total_int_paid'] is not None else 0 loan_expected_princ = loan_expected_princ - total_princ_paid # spread interest waivered total_interest_waived = total_interest_waived + interest_waivered_bal _interest_waivered_bal = loan_repayment_schedule.interest_expected - (total_interest_waived + total_int_paid) if _interest_waivered_bal < 0: interest_waivered_bal = interest_waivered_bal + abs(_interest_waivered_bal) total_interest_waived = loan_repayment_schedule.interest_expected - total_int_paid if float(loan_repayment_schedule.principal_expected + loan_repayment_schedule.interest_expected) <= float(total_princ_paid + total_int_paid + total_interest_waived ): is_paid_off = 'true' schedule_date = make_aware(datetime.strptime(loan_repayment_schedule.expected_date.strftime('%Y-%m-%d') + ' 23:59:59', '%Y-%m-%d %H:%M:%S')) new_as_at = make_aware(datetime.strptime(as_at + ' 23:59:59', '%Y-%m-%d %H:%M:%S')) if new_as_at > schedule_date and is_paid_off == 'false': principal_due = (loan_repayment_schedule.principal_expected - total_princ_paid) interest_due = (loan_repayment_schedule.interest_expected - (total_int_paid + total_interest_waived) ) total_principal_due += principal_due total_interest_due += interest_due loan_expected_princ = loan_expected_princ - principal_due days = (new_as_at - schedule_date).days total_due = principal_due if loan_report == 'par_aging_report': if par_aging_total_princ == 0: if days >= 1 and days <=30: last_aging = 'l_1_30' elif days >= 31 and days <=60: last_aging = 'l_31_60' elif days >= 61 and days <=90: last_aging = 'l_61_90' elif days >= 91 and days <=120: last_aging = 'l_91_120' elif days >= 121 and days <=180: last_aging = 'l_121_180' elif days >= 181: last_aging = 'm_180' par_aging_total_princ += total_due else: if days >= 1 and days <=30: values = response['l_1_30'] + total_due response['l_1_30'] = values elif days >= 31 and days <=60: values = response['l_31_60'] + total_due response['l_31_60'] = values elif days >= 61 and days <=90: values = response['l_61_90'] + total_due response['l_61_90'] = values elif days >= 91 and days <=120: values = response['l_91_120'] + total_due response['l_91_120'] = values elif days >= 121 and days <=180: values = response['l_121_180'] + total_due response['l_121_180'] = values elif days >= 181: values = response['m_180'] + total_due response['m_180'] = values response['princ_due'] = total_principal_due response['int_due'] = total_interest_due if loan_report == 'par_aging_report': # age whole oustanding response[last_aging] = par_aging_total_princ + loan_expected_princ return response def get_savings_details(self, obj): response = {'total_deposits': 0, 'total_withdrawals': 0, 'balance': 0, 'balance_raw': 0, 'balance_actual': 0, 'with_held': 0, 'blocked_amount': 0} customer_id = obj.customer_id accounts = SavingAccount.objects.filter(account_customer__id=customer_id, status='active', is_active=True, deleted=False) for account in accounts: bal = get_account_balance(account) for key in response: val = bal.get(key) or 0 if isinstance(val, str): val = val.replace(',', '') response[key] += float(val or 0) return response class LoanPaymentTransactionSerializer(serializers.ModelSerializer): loan_application_details = serializers.SerializerMethodField() loan_application_payment_details = serializers.SerializerMethodField() class Meta: model = LoanPaymentTransaction fields = '__all__' def get_loan_application_details(self, obj): response = {} loan = LoanApplication.objects.filter(id=obj.loan_application.id).first() if loan: response = {"customer_old_member_number":loan.customer.old_member_number, "customer_member_number":loan.customer.member_number, "customer_full_name":loan.customer.name } return response def get_loan_application_payment_details(self, obj): payment_details = LoanPayments.objects.filter(loan_payment_transaction=obj, payment_status='normal').aggregate(total_princ_paid=Sum('princ_paid'), total_int_paid=Sum('int_paid'), total_penalty_paid=Sum('penalty_paid')) total_int_paid = payment_details['total_int_paid'] if payment_details['total_int_paid'] else 0 total_princ_paid = payment_details['total_princ_paid'] if payment_details['total_princ_paid'] else 0 total_penalty_paid = payment_details['total_penalty_paid'] if payment_details['total_penalty_paid'] else 0 return {"total_int_paid":total_int_paid, "total_princ_paid":total_princ_paid, "total_penalty_paid":total_penalty_paid, "total_paid": total_int_paid + total_princ_paid + total_penalty_paid}