from django.db.models import Sum,Min,OuterRef,Value,FloatField from django.db.models.functions import Coalesce,Cast from datetime import timedelta, datetime from rest_framework.response import Response from django.db.models import Sum from rest_framework import status from questbanker_api.utils import get_current_user, send_email from django.utils.timezone import make_aware import pytz import math from .models import * from ledgers.ledgers_helper import * from ledgers.models import InterBranchTransactions from savings.models import SavingAccountTransactions,GroupSavingTransaction from savings.savings_bal_helper import get_account_balance,get_group_memebr_account_balance from .data import system_define_loan_loss_provisions from dateutil.relativedelta import relativedelta import datetime as datetime_timedelta from django.contrib.auth import get_user_model from exservices.exservices_helper import send_customer_sms from notifications.notifications_helper import * def _active_loan_schedules_queryset(**filters): filters["deleted"] = False return LoanRepaymentSchedule.objects.filter(**filters) def _active_loan_payments_queryset(**filters): filters["deleted"] = False return LoanPayments.objects.filter(**filters) def generate_loan_schedules(request, loan_id = None, loan_type = 'new', loan_data=None): loan_schedules = [] if loan_id: loan_details = LoanApplication.objects.get(pk=loan_id) if loan_details: loan_application_approval = LoanApplicationApproval.objects.filter(loan_application=loan_details).first() if not loan_application_approval: return {"message":"Loan not approved"} # end of validation loan_interest_method = loan_details.int_method if loan_type == 'rescheduling': loan_rescheduling_details = RescheduledLoans.objects.filter(loan_application=loan_details).order_by('-id').first() if loan_rescheduling_details: loan_interest_method = loan_rescheduling_details.int_method if loan_interest_method == 'flat': loan_schedules = calculate_flat_loan_schedule(request, loan_id, loan_type) if loan_interest_method == 'declining': loan_schedules = calculate_declining_loan_schedule(request, loan_id, loan_type) if loan_interest_method == 'amortization': loan_schedules = calculate_amortization_loan_schedule(request, loan_id, loan_type) elif loan_data: loan_interest_method = loan_data.get('int_method') if loan_interest_method == 'flat': loan_schedules = calculate_flat_loan_schedule(request, loan_type=loan_type, loan_data=loan_data) if loan_interest_method == 'declining': loan_schedules = calculate_declining_loan_schedule(request, loan_type=loan_type, loan_data=loan_data) if loan_interest_method == 'amortization': loan_schedules = calculate_amortization_loan_schedule(request, loan_type=loan_type, loan_data=loan_data) return loan_schedules def loan_payment(request, loan_id = None, loan_type= 'new', loan_data=None ): # Calculate termly interest rate if loan_id: loan_details = LoanApplication.objects.get(pk=loan_id) if loan_details.status == 'pending': return (0,0,0) loan_disbursement_details = LoanApplicationDisbursement.objects.filter(loan_application=loan_details).first() 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 loan_amount = loan_disbursement_details.loan_amount if loan_disbursement_details else loan_approval_details.loan_amount interest_rate = loan_approval_details.int_rate int_method = loan_details.int_method frequency = loan_approval_details.frequency if loan_approval_details.frequency > 0 else 1 principal = loan_disbursement_details.loan_amount if loan_disbursement_details else loan_approval_details.loan_amount grace_period = loan_approval_details.app_grace_period if loan_approval_details else 0 grace_period_type = loan_approval_details.grace_period_type if loan_approval_details else loan_details.grace_period_type # update loan details if rescheduling if loan_type == 'rescheduling': loan_rescheduling_details = RescheduledLoans.objects.filter(loan_application=loan_details).order_by('-id').first() if loan_rescheduling_details: period_type = loan_rescheduling_details.period_type loan_period = loan_rescheduling_details.loan_period loan_amount = loan_rescheduling_details.principal_amount interest_rate = loan_rescheduling_details.int_rate int_method = loan_rescheduling_details.int_method frequency = loan_rescheduling_details.frequency principal = loan_rescheduling_details.principal_amount grace_period = loan_rescheduling_details.grace_period grace_period_type = loan_rescheduling_details.grace_period_type else: return (0, 0, 0) elif loan_data: period_type = loan_data.get('period_type') loan_period = int(loan_data.get('loan_period')) loan_amount = float(loan_data.get('loan_amount')) interest_rate = float(loan_data.get('int_rate')) frequency = int(loan_data.get('frequency')) int_method = loan_data.get('int_method') principal = float(loan_data.get('loan_amount')) grace_period = int(loan_data.get('grace_period')) grace_period_type = loan_data.get('grace_period_type') else: return (0, 0, 0) total_interest = 0 total_payment = 0 num_of_payments = calculate_num_of_payments(loan_period, frequency) if frequency > 1 else 0 # new loan period with repayment intervals original_loan_period = loan_period loan_period = num_of_payments if frequency > 1 else loan_period if int_method == 'declining': # Convert the annual interest rate to a termly rate the_term_rate = int_for_term(period_type, interest_rate) # Calculate the termly interest rate termly_interest_rate = the_term_rate / 100 # calculate interest payment if with grace period if grace_period > 0 and grace_period_type == 'pay_i': total_interest = (loan_amount * termly_interest_rate) * grace_period * frequency loan_period -= grace_period # Calculate the termly payment term_payment = loan_amount / loan_period # Calculate the interest for each month for i in range(0, loan_period): # Calculate the interest for this month interest = loan_amount * termly_interest_rate * frequency # Add the interest to the total interest total_interest += interest # Subtract the monthly payment from the principal loan_amount -= term_payment # Return a tuple with the expected interest, principal, and total payment total_payment = principal + total_interest elif int_method == 'flat': # Convert the annual interest rate to a termly rate the_term_rate = int_for_term(period_type, interest_rate) # Calculate the termly interest rate termly_interest_rate = the_term_rate / 100 # Calculate the total interest total_interest = loan_amount * termly_interest_rate * loan_period * frequency total_payment = principal + total_interest else: # Calculate the term interest rate termly_rate = int_for_term(period_type, interest_rate) termly_interest_rate = termly_rate / 100 # Calculate the monthly payment using the formula for amortized loans r = interest_rate / 100 / loan_period n = (original_loan_period/12) * loan_period # Calculate the monthly payment if frequency > 1: termly_payment = (loan_amount * r) / (1 - math.pow(1 + r, -n)) else: r = termly_interest_rate * frequency termly_payment = loan_amount * termly_interest_rate * ((1 + termly_interest_rate) ** loan_period) / (((1 + termly_interest_rate) ** loan_period) - 1) # Calculate the interest for each term for i in range(loan_period): # Calculate the interest for this term interest = loan_amount * r # Add the interest to the total interest total_interest += interest # Subtract the portion of the payment that goes towards interest from the termly payment principal_payment = termly_payment - interest # Subtract the principal payment from the principal loan_amount -= principal_payment total_payment = principal + total_interest return (round_off_amount(request, total_interest), round_off_amount(request, principal), round_off_amount(request, total_payment)) def loan_schedule_due_date(input_date, grace_period, grace_period_type): if grace_period_type == 'd': delta = relativedelta(days=grace_period) elif grace_period_type == 'm': delta = relativedelta(months=grace_period) elif grace_period_type == 'w': delta = relativedelta(weeks=grace_period) elif grace_period_type == 'bw': delta = relativedelta(weeks=grace_period * 2) elif grace_period_type == 'q': delta = relativedelta(months = grace_period * 3) elif grace_period_type == 'y': delta = relativedelta(years = grace_period * 3) return input_date + delta def int_for_term(period_type, interest_rate): if period_type == 'w': return (interest_rate/12)/4 elif period_type == 'd': return (interest_rate/12)/30 elif period_type == 'y': return interest_rate elif period_type == 'bw': return (interest_rate/12)/2 elif period_type == 'q': return (interest_rate/4) else: return (interest_rate/12) def freq_for_term(period_type, frequency): if period_type == 'w': return frequency * 7 elif period_type == 'd': return frequency * 1 elif period_type == 'y': return frequency * 365 elif period_type == 'q': return frequency * 90 elif period_type == 'bw': return frequency * 14 else: return frequency * 30 def calculate_num_of_payments(num_terms, repayment_interval): num_payments = num_terms // repayment_interval if num_terms % repayment_interval != 0: num_payments += 1 return num_payments def refine_amount(request, amount, is_refine_only = False): refine = round_off_amount(request, amount) supplement = 0 enable_refine_schedule = True if request: organisation_id = get_current_user(request, 'organisation_id', None) general_setting = OrganisationSetting.objects.filter(org_setting__id=organisation_id, setting_key='enable_refine_schedule').first() if general_setting and general_setting.setting_value == 'off': enable_refine_schedule = False if enable_refine_schedule: if amount > 100: refine = int(amount/100) * 100 supplement = amount - refine elif amount < 100 and amount > 50: refine = int(amount/50) * 50 supplement = amount - refine else: refine = round_off_amount(request, amount) supplement = 0 return (refine, supplement) if is_refine_only == False else refine def round_off_amount(request, amount): enable_refine_schedule = True if request: organisation_id = get_current_user(request, 'organisation_id', None) general_setting = OrganisationSetting.objects.filter(org_setting__id=organisation_id, setting_key='enable_refine_schedule').first() if general_setting and general_setting.setting_value == 'off': enable_refine_schedule = False if enable_refine_schedule: refine = int(amount) supplement = amount - refine return refine + 1 if supplement >= 0.5 else refine return round(amount, 2) def loan_start_date_is_last_date(date_string): try: # Parse the date string into a datetime object date_obj = datetime.strptime(date_string, "%Y-%m-%d") # Check if the day is either 30 or 31 day = date_obj.day if day == 30 or day == 31: # Calculate the 1st day of the following month next_month = date_obj.replace(day=1) + relativedelta(months=1) return next_month.strftime("%Y-%m-%d") else: return date_string except ValueError: # Handle invalid date format return date_string def calculate_declining_loan_schedule(request, loan_id=None, loan_type = 'new', loan_data=None): # Calculate termly interest rate if loan_id: loan_details = LoanApplication.objects.get(pk=loan_id) loan_disbursement_details = LoanApplicationDisbursement.objects.filter(loan_application=loan_details).first() 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 loan_amount = loan_disbursement_details.loan_amount if loan_disbursement_details else loan_approval_details.loan_amount interest_rate = loan_approval_details.int_rate frequency = loan_approval_details.frequency if loan_approval_details.frequency > 0 else 1 grace_period = loan_approval_details.app_grace_period grace_period_type = loan_approval_details.grace_period_type if loan_approval_details else loan_details.grace_period_type loan_start_date = loan_disbursement_details.loan_start_date if loan_disbursement_details else loan_approval_details.approval_date # update loan details if rescheduling if loan_type == 'rescheduling': loan_rescheduling_details = RescheduledLoans.objects.filter(loan_application=loan_details).order_by('-id').first() if loan_rescheduling_details: period_type = loan_rescheduling_details.period_type loan_period = loan_rescheduling_details.loan_period loan_amount = loan_rescheduling_details.principal_amount interest_rate = loan_rescheduling_details.int_rate frequency = loan_rescheduling_details.frequency grace_period = loan_rescheduling_details.grace_period grace_period_type = loan_rescheduling_details.grace_period_type loan_start_date = loan_rescheduling_details.reschedule_date else: return [] elif loan_data: period_type = loan_data.get('period_type') loan_period = int(loan_data.get('loan_period')) loan_amount = float(loan_data.get('loan_amount')) interest_rate = float(loan_data.get('int_rate')) frequency = int(loan_data.get('frequency')) grace_period = int(loan_data.get('grace_period')) grace_period_type = loan_data.get('grace_period_type') loan_start_date = datetime.strptime(loan_data.get('loan_start_date').split('T')[0], '%Y-%m-%d') else: return [] num_of_payments = calculate_num_of_payments(loan_period, frequency) if frequency > 1 else 0 # new loan period with repayment intervals loan_period = num_of_payments if frequency > 1 else loan_period # Calculate the termly interest rate termly_rate = int_for_term(period_type, interest_rate) termly_interest_rate = termly_rate / 100 # Calculate the termly payment using the declining balance method termly_payment = loan_amount / loan_period original_grace_period = grace_period # Initialize the schedule list with the header row schedules = [] # Initialize the balance to the principal amount interest, principal, total = loan_payment(request, loan_id, loan_type, loan_data) toal_loan_start_balance = loan_amount + interest loan_balance = loan_amount # refine amounts refine_termly_payment_sup = 0 refine_pay_interest_sup = 0 for i in range(0, loan_period): # Calculate the interest for this term pay_interest = loan_balance * termly_interest_rate * frequency # calculate interest paid if with grace period and pay interest only if grace_period > 0 and grace_period_type == 'pay_i': grace_period -=1 termly_payment = 0 else: termly_payment = loan_amount/(loan_period - original_grace_period) if original_grace_period > 0 and grace_period_type == 'pay_i' else loan_amount/loan_period loan_balance -= termly_payment # refine termly_payment amount refine_termly_payment, sup_termly_payment = refine_amount(request, termly_payment) refine_termly_payment_sup += sup_termly_payment # refine termly_payment amount refine_pay_interest, sup_pay_interest = refine_amount(request, pay_interest) refine_pay_interest_sup += sup_pay_interest # balance refine refine_loan_start_balance = round_off_amount(request, toal_loan_start_balance) if i == loan_period - 1: refine_termly_payment = round_off_amount(request, refine_termly_payment + refine_termly_payment_sup) refine_pay_interest = round_off_amount(request, refine_pay_interest + refine_pay_interest_sup) total_schedule = refine_termly_payment + refine_pay_interest toal_loan_end_balance = refine_loan_start_balance - total_schedule months = (i + 1) * freq_for_term(period_type, frequency) // 30 delta = relativedelta(months=months) if period_type == 'w': weeks = (i + 1) * freq_for_term(period_type, frequency) // 7 delta = relativedelta(weeks=weeks) elif period_type == 'bw': biweeks = (i + 1) * freq_for_term(period_type, frequency) // 14 delta = relativedelta(weeks=biweeks) elif period_type == 'q': quartely = (i + 1) * freq_for_term(period_type, frequency) // 30 delta = relativedelta(months=quartely) elif period_type == 'y': annually = (i + 1) * freq_for_term(period_type, frequency) // 365 delta = relativedelta(years=annually) elif period_type == 'd': days = (i + 1) * freq_for_term(period_type, frequency) delta = relativedelta(days=days) eat_timezone = pytz.timezone("Africa/Nairobi") loan_start_date = loan_start_date.astimezone(eat_timezone) loan_start_date = datetime.strptime(loan_start_date_is_last_date(loan_start_date.strftime("%Y-%m-%d")), "%Y-%m-%d") schedules.append({ "expected_date": loan_start_date + delta, # Date "starting_balance":refine_loan_start_balance, # Starting Balance "principal_expected":refine_termly_payment, # Payment "interest_expected":refine_pay_interest, # Interest "total_payment":total_schedule, # total payment "ending_balance":toal_loan_end_balance # Ending Balance }) toal_loan_start_balance = toal_loan_end_balance # calculate with grace period loan_schedules = schedules if grace_period > 0 and grace_period_type in ['pay_none', 'pay_i', 'pay_p']: loan_schedules = [] # reset refine amounts refine_termly_payment_sup = 0 refine_pay_interest_sup = 0 # Calculate the schedule for each term grace_period_interest_bal = 0 grace_period_principal_bal = 0 toal_loan_start_balance = loan_amount + interest for i in range(0, loan_period): interest_paid = 0 principal_paid = 0 if i < grace_period: if grace_period_type == 'pay_none': grace_period_interest_bal += schedules[i]['interest_expected'] grace_period_principal_bal += schedules[i]['principal_expected'] if grace_period_type == 'pay_i': grace_period_principal_bal += schedules[i]['principal_expected'] grace_period_interest_bal = 0 interest_paid = schedules[i]['interest_expected'] if grace_period_type == 'pay_p': grace_period_interest_bal += schedules[i]['interest_expected'] grace_period_principal_bal = 0 principal_paid = schedules[i]['principal_expected'] else: new_grace_period_interest_bal = grace_period_interest_bal / (loan_period - grace_period) new_grace_period_principal_bal = grace_period_principal_bal / (loan_period - grace_period) interest_paid = new_grace_period_interest_bal + schedules[i]['interest_expected'] principal_paid = new_grace_period_principal_bal + schedules[i]['principal_expected'] # refine termly_payment amount refine_termly_payment, sup_termly_payment = refine_amount(request, principal_paid) refine_termly_payment_sup += sup_termly_payment # refine termly_payment amount refine_pay_interest, sup_pay_interest = refine_amount(request, interest_paid) refine_pay_interest_sup += sup_pay_interest # balance refine refine_loan_start_balance = round_off_amount(request, toal_loan_start_balance) if i == loan_period - 1: refine_termly_payment = round_off_amount(request, refine_termly_payment + refine_termly_payment_sup) refine_pay_interest = round_off_amount(request, refine_pay_interest + refine_pay_interest_sup) total_schedule = refine_termly_payment + refine_pay_interest toal_loan_end_balance = refine_loan_start_balance - total_schedule loan_schedules.append({ "expected_date":schedules[i]['expected_date'], # Date "starting_balance":refine_loan_start_balance, # Starting Balance "principal_expected":refine_termly_payment, # Payment "interest_expected":refine_pay_interest, # Interest "total_payment":total_schedule, # total payment "ending_balance":toal_loan_end_balance # Ending Balance }) toal_loan_start_balance = toal_loan_end_balance return loan_schedules def calculate_amortization_loan_schedule(request, loan_id=None, loan_type = 'new', loan_data=None): # Calculate termly interest rate if loan_id: loan_details = LoanApplication.objects.get(pk=loan_id) loan_disbursement_details = LoanApplicationDisbursement.objects.filter(loan_application=loan_details).first() 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 loan_amount = loan_disbursement_details.loan_amount if loan_disbursement_details else loan_approval_details.loan_amount interest_rate = loan_approval_details.int_rate frequency = loan_approval_details.frequency if loan_approval_details.frequency > 0 else 1 grace_period = loan_approval_details.app_grace_period grace_period_type = loan_approval_details.grace_period_type if loan_approval_details else loan_details.grace_period_type loan_start_date = loan_disbursement_details.loan_start_date if loan_disbursement_details else loan_approval_details.approval_date # update loan details if rescheduling if loan_type == 'rescheduling': loan_rescheduling_details = RescheduledLoans.objects.filter(loan_application=loan_details).order_by('-id').first() if loan_rescheduling_details: period_type = loan_rescheduling_details.period_type loan_period = loan_rescheduling_details.loan_period loan_amount = loan_rescheduling_details.principal_amount interest_rate = loan_rescheduling_details.int_rate frequency = loan_rescheduling_details.frequency grace_period = loan_rescheduling_details.grace_period grace_period_type = loan_rescheduling_details.grace_period_type loan_start_date = loan_rescheduling_details.reschedule_date else: return [] elif loan_data: period_type = loan_data.get('period_type') loan_period = int(loan_data.get('loan_period')) loan_amount = float(loan_data.get('loan_amount')) interest_rate = float(loan_data.get('int_rate')) frequency = int(loan_data.get('frequency')) grace_period = int(loan_data.get('grace_period')) grace_period_type = loan_data.get('grace_period_type') loan_start_date = datetime.strptime(loan_data.get('loan_start_date').split('T')[0], '%Y-%m-%d') else: return [] num_of_payments = calculate_num_of_payments(loan_period, frequency) if frequency > 1 else 0 # new loan period with repayment intervals original_loan_period = loan_period loan_period = num_of_payments if frequency > 1 else loan_period # Calculate the termly interest rate interest, principal, total = loan_payment(request, loan_id, loan_type, loan_data) termly_rate = int_for_term(period_type, interest_rate) termly_interest_rate = termly_rate / 100 # Calculate the monthly payment r = interest_rate / 100 / loan_period n = (original_loan_period/12) * loan_period # Calculate the monthly payment if frequency > 1: termly_payment = (loan_amount * r) / (1 - math.pow(1 + r, -n)) else: r = termly_interest_rate * frequency termly_payment = loan_amount * termly_interest_rate * ((1 + termly_interest_rate) ** loan_period) / (((1 + termly_interest_rate) ** loan_period) - 1) # Initialize the remaining balance to the principal remaining_balance = loan_amount toal_loan_start_balance = loan_amount + interest # Initialize an empty list to store the repayment schedule schedules = [] # refine amounts refine_termly_payment_sup = 0 refine_pay_interest_sup = 0 # Loop through each termly of the loan for i in range(loan_period): # Calculate the interest for the term interest_paid = remaining_balance * r # Calculate the principal for the term principal_payment = termly_payment - interest_paid # Subtract the principal payment from the principal remaining_balance -= principal_payment # refine termly_payment amount refine_termly_payment, sup_termly_payment = refine_amount(request, principal_payment) refine_termly_payment_sup += sup_termly_payment # refine termly_payment amount refine_pay_interest, sup_pay_interest = refine_amount(request, interest_paid) refine_pay_interest_sup += sup_pay_interest # balance refine refine_loan_start_balance = round_off_amount(request, toal_loan_start_balance) if i == loan_period - 1: refine_termly_payment = round_off_amount(request, refine_termly_payment + refine_termly_payment_sup) refine_pay_interest = round_off_amount(request, refine_pay_interest + refine_pay_interest_sup) total_schedule = refine_termly_payment + refine_pay_interest toal_loan_end_balance = refine_loan_start_balance - total_schedule months = (i + 1) * freq_for_term(period_type, frequency) // 30 delta = relativedelta(months=months) if period_type == 'w': weeks = (i + 1) * freq_for_term(period_type, frequency) // 7 delta = relativedelta(weeks=weeks) elif period_type == 'bw': biweeks = (i + 1) * freq_for_term(period_type, frequency) // 14 delta = relativedelta(weeks=biweeks) elif period_type == 'q': quartely = (i + 1) * freq_for_term(period_type, frequency) // 30 delta = relativedelta(months=quartely) elif period_type == 'y': annually = (i + 1) * freq_for_term(period_type, frequency) // 365 delta = relativedelta(years=annually) elif period_type == 'd': days = (i + 1) * freq_for_term(period_type, frequency) delta = relativedelta(days=days) eat_timezone = pytz.timezone("Africa/Nairobi") loan_start_date = loan_start_date.astimezone(eat_timezone) loan_start_date = datetime.strptime(loan_start_date_is_last_date(loan_start_date.strftime("%Y-%m-%d")), "%Y-%m-%d") schedules.append({ 'expected_date': loan_start_date + delta, 'starting_balance': refine_loan_start_balance, 'principal_expected': refine_termly_payment, 'interest_expected': refine_pay_interest, 'total_payment': total_schedule, 'ending_balance': toal_loan_end_balance }) toal_loan_start_balance = toal_loan_end_balance # calculate with grace period loan_schedules = schedules if grace_period > 0 and grace_period_type in ['pay_none', 'pay_i', 'pay_p']: loan_schedules = [] # reset refine amounts refine_termly_payment_sup = 0 refine_pay_interest_sup = 0 # Calculate the schedule for each term grace_period_interest_bal = 0 grace_period_principal_bal = 0 toal_loan_start_balance = loan_amount + interest for i in range(0, loan_period): interest_paid = 0 principal_paid = 0 if i < grace_period: if grace_period_type == 'pay_none': grace_period_interest_bal += schedules[i]['interest_expected'] grace_period_principal_bal += schedules[i]['principal_expected'] if grace_period_type == 'pay_i': grace_period_principal_bal += schedules[i]['principal_expected'] grace_period_interest_bal = 0 interest_paid = schedules[i]['interest_expected'] if grace_period_type == 'pay_p': grace_period_interest_bal += schedules[i]['interest_expected'] grace_period_principal_bal = 0 principal_paid = schedules[i]['principal_expected'] else: new_grace_period_interest_bal = grace_period_interest_bal / (loan_period - grace_period) new_grace_period_principal_bal = grace_period_principal_bal / (loan_period - grace_period) interest_paid = new_grace_period_interest_bal + schedules[i]['interest_expected'] principal_paid = new_grace_period_principal_bal + schedules[i]['principal_expected'] # refine termly_payment amount refine_termly_payment, sup_termly_payment = refine_amount(request, principal_paid) refine_termly_payment_sup += sup_termly_payment # refine termly_payment amount refine_pay_interest, sup_pay_interest = refine_amount(request, interest_paid) refine_pay_interest_sup += sup_pay_interest # balance refine refine_loan_start_balance = round_off_amount(request, toal_loan_start_balance) if i == loan_period - 1: refine_termly_payment = round_off_amount(request, refine_termly_payment + refine_termly_payment_sup) refine_pay_interest = round_off_amount(request, refine_pay_interest + refine_pay_interest_sup) total_schedule = refine_termly_payment + refine_pay_interest toal_loan_end_balance = refine_loan_start_balance - total_schedule loan_schedules.append({ 'expected_date': schedules[i]['expected_date'], 'starting_balance': refine_loan_start_balance, 'principal_expected': refine_termly_payment, 'interest_expected': refine_pay_interest, 'total_payment': total_schedule, 'ending_balance': toal_loan_end_balance }) toal_loan_start_balance = toal_loan_end_balance return loan_schedules def calculate_flat_loan_schedule(request, loan_id =None , loan_type = 'new', loan_data = None): # Calculate termly interest rate if loan_id: loan_details = LoanApplication.objects.get(pk=loan_id) loan_disbursement_details = LoanApplicationDisbursement.objects.filter(loan_application=loan_details).first() 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 loan_amount = loan_disbursement_details.loan_amount if loan_disbursement_details else loan_approval_details.loan_amount interest_rate = loan_approval_details.int_rate frequency = loan_approval_details.frequency if loan_approval_details.frequency > 0 else 1 grace_period = loan_approval_details.app_grace_period grace_period_type = loan_approval_details.grace_period_type if loan_approval_details else loan_details.grace_period_type loan_start_date = loan_disbursement_details.loan_start_date if loan_disbursement_details else loan_approval_details.approval_date # update loan details if rescheduling if loan_type == 'rescheduling': loan_rescheduling_details = RescheduledLoans.objects.filter(loan_application=loan_details).order_by('-id').first() if loan_rescheduling_details: period_type = loan_rescheduling_details.period_type loan_period = loan_rescheduling_details.loan_period loan_amount = loan_rescheduling_details.principal_amount interest_rate = loan_rescheduling_details.int_rate frequency = loan_rescheduling_details.frequency grace_period = loan_rescheduling_details.grace_period grace_period_type = loan_rescheduling_details.grace_period_type loan_start_date = loan_rescheduling_details.reschedule_date else: return [] elif loan_data: period_type = loan_data.get('period_type') loan_period = int(loan_data.get('loan_period')) loan_amount = float(loan_data.get('loan_amount')) interest_rate = float(loan_data.get('int_rate')) frequency = int(loan_data.get('frequency')) grace_period = int(loan_data.get('grace_period')) grace_period_type = loan_data.get('grace_period_type') loan_start_date = make_aware(datetime.strptime(loan_data.get('loan_start_date').split('T')[0], '%Y-%m-%d')) else: return [] num_of_payments = calculate_num_of_payments(loan_period, frequency) if frequency > 1 else 0 # Calculate the termly interest rate interest, principal, total = loan_payment(request, loan_id, loan_type, loan_data) termly_rate = int_for_term(period_type, interest_rate) termly_interest_rate = termly_rate / 100 # new loan period with repayment intervals original_loan_period = loan_period loan_period = num_of_payments if frequency > 1 else loan_period # Calculate the monthly payment, including the interest charged on the original principal termly_payment = (loan_amount + (loan_amount * termly_interest_rate * original_loan_period)) / loan_period # Initialize the remaining balance to the principal toal_loan_start_balance = loan_amount + interest # Initialize an empty list to store the repayment schedule schedules = [] # refine amounts refine_termly_payment_sup = 0 refine_pay_interest_sup = 0 # Loop through each termly of the loan for i in range(loan_period): # Calculate the interest for the month, based on the original principal interest_paid = loan_amount * termly_interest_rate * frequency # Calculate the principal for the term principal_paid = termly_payment - interest_paid # refine termly_payment amount refine_termly_payment, sup_termly_payment = refine_amount(request, principal_paid) refine_termly_payment_sup += sup_termly_payment # refine termly_payment amount refine_pay_interest, sup_pay_interest = refine_amount(request, interest_paid) refine_pay_interest_sup += sup_pay_interest # balance refine refine_loan_start_balance = round_off_amount(request, toal_loan_start_balance) if i == loan_period - 1: refine_termly_payment = round_off_amount(request, refine_termly_payment + refine_termly_payment_sup) refine_pay_interest = round_off_amount(request, refine_pay_interest + refine_pay_interest_sup) # Add the month's repayment details to the schedule total_schedule = refine_termly_payment + refine_pay_interest toal_loan_end_balance = refine_loan_start_balance - total_schedule months = (i + 1) * freq_for_term(period_type, frequency) // 30 delta = relativedelta(months=months) if period_type == 'w': weeks = (i + 1) * freq_for_term(period_type, frequency) // 7 delta = relativedelta(weeks=weeks) elif period_type == 'bw': biweeks = (i + 1) * freq_for_term(period_type, frequency) // 14 delta = relativedelta(weeks=biweeks) elif period_type == 'q': quartely = (i + 1) * freq_for_term(period_type, frequency) // 30 delta = relativedelta(months=quartely) elif period_type == 'y': annually = (i + 1) * freq_for_term(period_type, frequency) // 365 delta = relativedelta(years=annually) elif period_type == 'd': days = (i + 1) * freq_for_term(period_type, frequency) delta = relativedelta(days=days) eat_timezone = pytz.timezone("Africa/Nairobi") loan_start_date = loan_start_date.astimezone(eat_timezone) loan_start_date = datetime.strptime(loan_start_date_is_last_date(loan_start_date.strftime("%Y-%m-%d")), "%Y-%m-%d") schedules.append({ 'expected_date': loan_start_date + delta, 'starting_balance':refine_loan_start_balance, 'principal_expected': refine_termly_payment, 'interest_expected': refine_pay_interest, 'total_payment': total_schedule, 'ending_balance': toal_loan_end_balance }) toal_loan_start_balance = toal_loan_end_balance # calculate with grace period loan_schedules = schedules if grace_period > 0 and grace_period_type in ['pay_none', 'pay_i', 'pay_p']: loan_schedules = [] # reset refine amounts refine_termly_payment_sup = 0 refine_pay_interest_sup = 0 # Calculate the schedule for each term grace_period_interest_bal = 0 grace_period_principal_bal = 0 grace_period_total_payment = 0 toal_loan_start_balance = loan_amount + interest for i in range(0, loan_period): interest_paid = 0 principal_paid = 0 if i < grace_period: if grace_period_type == 'pay_none': grace_period_interest_bal += schedules[i]['interest_expected'] grace_period_principal_bal += schedules[i]['principal_expected'] grace_period_total_payment += schedules[i]['total_payment'] if grace_period_type == 'pay_i': grace_period_principal_bal += schedules[i]['principal_expected'] grace_period_total_payment += schedules[i]['principal_expected'] grace_period_interest_bal = 0 interest_paid = schedules[i]['interest_expected'] if grace_period_type == 'pay_p': grace_period_interest_bal += schedules[i]['interest_expected'] grace_period_total_payment += schedules[i]['interest_expected'] grace_period_principal_bal = 0 principal_paid = schedules[i]['principal_expected'] else: new_grace_period_interest_bal = grace_period_interest_bal / (loan_period - grace_period) new_grace_period_principal_bal = grace_period_principal_bal / (loan_period - grace_period) interest_paid = new_grace_period_interest_bal + schedules[i]['interest_expected'] principal_paid = new_grace_period_principal_bal + schedules[i]['principal_expected'] # refine termly_payment amount refine_termly_payment, sup_termly_payment = refine_amount(request, principal_paid) refine_termly_payment_sup += sup_termly_payment # refine termly_payment amount refine_pay_interest, sup_pay_interest = refine_amount(request, interest_paid) refine_pay_interest_sup += sup_pay_interest # balance refine refine_loan_start_balance = round_off_amount(request, toal_loan_start_balance) if i == loan_period - 1: refine_termly_payment = round_off_amount(request, refine_termly_payment + refine_termly_payment_sup) refine_pay_interest = round_off_amount(request, refine_pay_interest + refine_pay_interest_sup) total_schedule = refine_termly_payment + refine_pay_interest toal_loan_end_balance = refine_loan_start_balance - total_schedule loan_schedules.append({ 'expected_date': schedules[i]['expected_date'], 'starting_balance': refine_loan_start_balance, 'principal_expected': refine_termly_payment, 'interest_expected': refine_pay_interest, 'total_payment': total_schedule, 'ending_balance': toal_loan_end_balance }) toal_loan_start_balance = toal_loan_end_balance return loan_schedules def loan_schedules_with_payments(loan_id): loan_schedules = [] reschedule_object = [] next_schedule = False interest_waivered_bal = 0 # dues total_principal_due = 0 total_interest_due = 0 total_penalty_due = 0 number_of_schedules = 0 # _total_int_paid=0 # _total_interest_waived=0 if loan_id: loan_details = LoanApplication.objects.get(pk=loan_id) if loan_details: loan_application_approval = LoanApplicationApproval.objects.filter(loan_application=loan_details).first() if not loan_application_approval: return {"message":"Loan not approved", "loan_id":loan_id} loan_application_disbursement = LoanApplicationDisbursement.objects.filter(loan_application=loan_details).first() if not loan_application_disbursement: return {"message":"Loan not disbursed", "loan_id":loan_id} loan_repayment_schedules = _active_loan_schedules_queryset( loan_application=loan_details, status="active" ).order_by('id') if not loan_repayment_schedules: return {"message":"Loan has no schedules", "loan_id":loan_id} penalty_waived = LoanPenaltyWaivered.objects.filter(loan_application=loan_details).aggregate(total_amount=Sum('amount'))['total_amount'] total_penalty_waived = penalty_waived if penalty_waived else 0 loan_penalities_paid = _active_loan_payments_queryset( loan_application=loan_details, payment_status='normal' ).aggregate(total_penalty_paid = Sum('penalty_paid'))['total_penalty_paid'] loan_penalities_paid = loan_penalities_paid if loan_penalities_paid else 0 total_loan_penalties = LoanPenalty.objects.filter(loan_application=loan_details).aggregate(total_penalty = Sum('amount'))['total_penalty'] total_loan_penalties = total_loan_penalties if total_loan_penalties else 0 princ_pal, int_bal, penaly_bal, write_off_amount = loan_balance(loan_id) for loan_repayment_schedule in loan_repayment_schedules: loan_payments_list = [] interest_waived = LoanInterestWaivered.objects.filter(loan_application=loan_details, loan_repayment_schedule=loan_repayment_schedule).aggregate(total_amount=Sum('amount'))['total_amount'] total_interest_waived = interest_waived if interest_waived else 0 loan_payments_totals = _active_loan_payments_queryset( loan_application=loan_details, loan_repayment_schedule=loan_repayment_schedule, payment_status='normal', ).aggregate( total_int_paid=Sum('int_paid'), total_princ_paid=Sum('princ_paid'), total_penalty_paid=Sum('penalty_paid'), ) loan_payments = _active_loan_payments_queryset( loan_application=loan_details, loan_repayment_schedule=loan_repayment_schedule, payment_status='normal', ).order_by('id') 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 # 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 # _total_interest_waived = _total_interest_waived + total_interest_waived for loan_payment in loan_payments: loan_payments_list.append({ "id": loan_payment.id, "loan_application_id":loan_payment.loan_application.id, "loan_repayment_schedule_id": loan_payment.loan_repayment_schedule.id, "loan_main_transaction_id":loan_payment.loan_main_transaction.id, "int_paid":loan_payment.int_paid, "princ_paid":loan_payment.princ_paid, "penalty_paid":loan_payment.penalty_paid, "date_added":loan_payment.date_added, "interest_waived":total_interest_waived, }) 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' current = timezone.now() current_date = make_aware(datetime.strptime(current.strftime('%Y-%m-%d') + ' 00:00', '%Y-%m-%d %H:%M')) schedule_date = make_aware(datetime.strptime(loan_repayment_schedule.expected_date.strftime('%Y-%m-%d') + ' 00:00', '%Y-%m-%d %H:%M')) if current_date > schedule_date and is_paid_off == 'false': total_principal_due += (loan_repayment_schedule.principal_expected - total_princ_paid) total_interest_due += (loan_repayment_schedule.interest_expected - (total_int_paid + total_interest_waived) ) total_penalty_due = total_loan_penalties - (total_penalty_waived + loan_penalities_paid) number_of_schedules += 1 # _total_int_paid = _total_int_paid + total_int_paid # _total_interest_waived = _total_interest_waived +total_interest_waived if princ_pal + int_bal + penaly_bal <= 0: number_of_schedules = 0 is_paid_off = 'true' total_principal_due = 0 total_interest_due = 0 total_penalty_due = 0 if current_date <= schedule_date and not next_schedule and is_paid_off == 'false': next_schedule = { "id":loan_repayment_schedule.id, "loan_application_id":loan_repayment_schedule.loan_application.id, "principal_expected":(loan_repayment_schedule.principal_expected - total_princ_paid), "interest_expected":(loan_repayment_schedule.interest_expected - (total_int_paid + total_interest_waived) ), "total_payment":(loan_repayment_schedule.principal_expected - total_princ_paid) + (loan_repayment_schedule.interest_expected - (total_int_paid + total_interest_waived) ), "penalty_expected": total_loan_penalties - (total_penalty_waived + loan_penalities_paid), "interest_waived":total_interest_waived, "expected_date":loan_repayment_schedule.expected_date, "ending_balance":loan_repayment_schedule.ending_balance, "starting_balance":loan_repayment_schedule.starting_balance, "ref_no":loan_repayment_schedule.ref_no, "date_added":loan_repayment_schedule.date_added, "status":loan_repayment_schedule.status, "payment_number":loan_repayment_schedule.payment_number } loan_schedules.append({ "id":loan_repayment_schedule.id, "loan_application_id":loan_repayment_schedule.loan_application.id, "payments_details":loan_payments_list, "principal_expected":loan_repayment_schedule.principal_expected, "interest_expected":loan_repayment_schedule.interest_expected, "total_payment":loan_repayment_schedule.total_payment, "penalty_expected":0, "interest_waived":total_interest_waived, "expected_date":loan_repayment_schedule.expected_date, "ending_balance":loan_repayment_schedule.ending_balance, "starting_balance":loan_repayment_schedule.starting_balance, "ref_no":loan_repayment_schedule.ref_no, "date_added":loan_repayment_schedule.date_added, "status":loan_repayment_schedule.status, "payment_number":loan_repayment_schedule.payment_number, "payments":{ "total_int_paid":total_int_paid, "total_princ_paid":total_princ_paid, "total_penalty_paid":loan_payments_totals['total_penalty_paid'] if loan_payments_totals['total_penalty_paid'] is not None else 0, "total_payment": loan_repayment_schedule.total_payment if is_paid_off == 'true' else total_princ_paid + total_int_paid, "balance": (loan_repayment_schedule.principal_expected + loan_repayment_schedule.interest_expected) - (total_princ_paid + total_int_paid + total_interest_waived) }, "is_paid_off":is_paid_off }) # rescheduled loans loan_reschedules = _active_loan_schedules_queryset( loan_application=loan_details, status="rescheduled" ).order_by('payment_number') # if loan_reschedules.exists(): # total_interest_due = 0 for loan_reschedule in loan_reschedules: payments = _active_loan_payments_queryset( loan_repayment_schedule=loan_reschedule, payment_status='normal', ).aggregate(total_princ_paid=Sum('princ_paid'), total_int_paid=Sum('int_paid')) interest_paid = payments['total_int_paid'] if payments['total_int_paid'] else 0 principal_paid = payments['total_princ_paid'] if payments['total_princ_paid'] else 0 interest_waived = LoanInterestWaivered.objects.filter(loan_application=loan_details, loan_repayment_schedule=loan_reschedule).aggregate(total_amount=Sum('amount'))['total_amount'] total_interest_waived = interest_waived if interest_waived else 0 payment_status = "not_paid" if interest_paid + total_interest_waived + principal_paid == loan_reschedule.total_payment: payment_status = "paid" # rescheduled_loan = RescheduledLoans.objects.filter(loan_application=loan_details) # total_interest_due += (loan_reschedule.interest_expected - (total_int_paid + total_interest_waived) ) # _total_int_paid = _total_int_paid + interest_paid # _total_interest_waived = _total_interest_waived + total_interest_waived reschedule_object.append({ "loan_reschedule":loan_reschedule.id, "principal_expected":loan_reschedule.principal_expected, "interest_expected":loan_reschedule.interest_expected, "total_payment":loan_reschedule.total_payment, "penalty_expected":0, "interest_waived":total_interest_waived, "expected_date":loan_reschedule.expected_date, "ending_balance":loan_reschedule.ending_balance, "starting_balance":loan_reschedule.starting_balance, "total_paid":principal_paid + interest_paid, "payment_status":payment_status }) # Bash Rescheduled # rescheduled_loan_details = RescheduledLoans.objects.filter(loan_application=loan_details).order_by('-id').first() # if rescheduled_loan_details : # total_interest_due = (rescheduled_loan_details.interest_expected - (_total_int_paid + _total_interest_waived)) # total_interest_due = total_interest_due if total_interest_due >=0 else 0 # End of Bash Rescheduled return {"loan_schedules":loan_schedules, "schedule_due": {"number_of_schedules":number_of_schedules, "principal_expected":total_principal_due, "interest_expected":total_interest_due, "penalty_expected":total_penalty_due, "total_payment":total_principal_due + total_interest_due + total_penalty_due }, "next_schedule":next_schedule, "reschedule":reschedule_object} def get_loan_schedule_due_date(loan_id, as_at): response = {"expected_date":'', "due_date": ''} loan_details = LoanApplication.objects.get(pk=loan_id) extra_date_added = {"date_added__date__lte": as_at } extra_payment_added = {"payment_date__date__lte": as_at } loan_repayment_schedules = _active_loan_schedules_queryset( loan_application=loan_details, status="active" ).order_by('id') penalty_waived = LoanPenaltyWaivered.objects.filter(loan_application=loan_details, **extra_date_added).aggregate(total_amount=Sum('amount'))['total_amount'] total_penalty_waived = penalty_waived if penalty_waived else 0 loan_penalities_paid = _active_loan_payments_queryset( loan_application=loan_details, payment_status='normal', **extra_payment_added, ).aggregate(total_penalty_paid = Sum('penalty_paid'))['total_penalty_paid'] loan_penalities_paid = loan_penalities_paid if loan_penalities_paid else 0 total_loan_penalties = LoanPenalty.objects.filter(loan_application=loan_details, **extra_date_added).aggregate(total_penalty = Sum('amount'))['total_penalty'] total_loan_penalties = total_loan_penalties if total_loan_penalties else 0 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 = _active_loan_payments_queryset( 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 # 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 ) and (total_loan_penalties - (total_penalty_waived + loan_penalities_paid)) <= 0: 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': loan_disbursement = LoanApplicationDisbursement.objects.filter(loan_application=loan_details).first() due_date = '' if loan_disbursement: arrear_grace_period = loan_disbursement.arrear_grace_period arrears_period_type = loan_disbursement.arrears_period_type if not loan_disbursement.arrears_period_type: arrear_grace_period = loan_disbursement.loan_application.loan_application_product.arrears_period arrears_period_type = loan_disbursement.loan_application.loan_application_product.arrears_period_type if not arrears_period_type: arrear_grace_period = 0 arrears_period_type = 'd' due_date = loan_schedule_due_date(loan_repayment_schedule.expected_date, arrear_grace_period, arrears_period_type) eat_timezone = pytz.timezone("Africa/Nairobi") expected_date = loan_repayment_schedule.expected_date.astimezone(eat_timezone) response['expected_date'] = expected_date.strftime('%Y-%m-%d') response['due_date'] = due_date return response def loan_schedules_dues(loan_id, as_at, start_date = None): total_principal_due = 0 total_interest_due = 0 total_penalty_due = 0 total_prepaid = 0 total_expected_princ = 0 total_expected_int = 0 princ_paid = 0 int_paid = 0 loan_details = LoanApplication.objects.get(pk=loan_id) extra_date_added = {"date_added__date__lte": as_at } extra_payment_added = {"payment_date__date__lte": as_at } extra_schedule_details = {"expected_date__date__lte":as_at, "status":"active", "loan_application":loan_details} if start_date: extra_date_added['date_added__date__gte'] = start_date extra_payment_added['payment_date__date__gte'] = start_date extra_schedule_details["expected_date__date__gte"] = start_date loan_repayment_schedules = _active_loan_schedules_queryset( **extra_schedule_details ).order_by('id') penalty_waived = LoanPenaltyWaivered.objects.filter(loan_application=loan_details, **extra_date_added).aggregate(total_amount=Sum('amount'))['total_amount'] total_penalty_waived = penalty_waived if penalty_waived else 0 loan_penalities_paid = _active_loan_payments_queryset( loan_application=loan_details, payment_status='normal', **extra_payment_added, ).aggregate(total_penalty_paid = Sum('penalty_paid'))['total_penalty_paid'] loan_penalities_paid = loan_penalities_paid if loan_penalities_paid else 0 total_loan_penalties = LoanPenalty.objects.filter(loan_application=loan_details, **extra_date_added).aggregate(total_penalty = Sum('amount'))['total_penalty'] total_loan_penalties = total_loan_penalties if total_loan_penalties else 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 = _active_loan_payments_queryset( 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'), ) 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 # include early payments if start_date: early_loan_payments_totals = _active_loan_payments_queryset( loan_application=loan_details, loan_repayment_schedule=loan_repayment_schedule, payment_status='normal', payment_date__date__lt=start_date, ).aggregate( total_int_paid=Sum('int_paid'), total_princ_paid=Sum('princ_paid'), total_penalty_paid=Sum('penalty_paid'), ) early_int_paid = early_loan_payments_totals['total_int_paid'] if early_loan_payments_totals['total_int_paid'] is not None else 0 early_princ_paid = early_loan_payments_totals['total_princ_paid'] if early_loan_payments_totals['total_princ_paid'] is not None else 0 early_interest_waived = LoanInterestWaivered.objects.filter(loan_application=loan_details, loan_repayment_schedule=loan_repayment_schedule, date_added__date__lt=start_date).aggregate(total_amount=Sum('amount'))['total_amount'] early_interest_waived = early_interest_waived if early_interest_waived else 0 total_princ_paid += early_princ_paid total_int_paid += early_int_paid total_interest_waived += early_interest_waived total_principal_due += (loan_repayment_schedule.principal_expected - total_princ_paid) total_interest_due += (loan_repayment_schedule.interest_expected - (total_int_paid + total_interest_waived) ) princ_paid += total_princ_paid int_paid += total_int_paid + total_interest_waived total_expected_princ += loan_repayment_schedule.principal_expected total_expected_int += loan_repayment_schedule.interest_expected total_penalty_due = total_loan_penalties - (total_penalty_waived + loan_penalities_paid) # payment rate [loan dues vs repayment] if start_date and as_at: # get the instalments due before the selected dates loan_dues_schedules = _active_loan_schedules_queryset( expected_date__date__lt=start_date, status="active", loan_application=loan_details, ).order_by('id') for loan_dues_schedule in loan_dues_schedules: due_interest_waived = LoanInterestWaivered.objects.filter(loan_application=loan_details, loan_repayment_schedule=loan_dues_schedule, date_added__date__lt=start_date).aggregate(total_amount=Sum('amount'))['total_amount'] due_total_interest_waived = due_interest_waived if due_interest_waived else 0 due_loan_payments_totals = _active_loan_payments_queryset( loan_application=loan_details, loan_repayment_schedule=loan_dues_schedule, payment_status='normal', payment_date__date__lt=start_date, ).aggregate( total_int_paid=Sum('int_paid'), total_princ_paid=Sum('princ_paid'), total_penalty_paid=Sum('penalty_paid'), ) due_total_princ_paid = due_loan_payments_totals['total_princ_paid'] if due_loan_payments_totals['total_princ_paid'] is not None else 0 due_total_int_paid = due_loan_payments_totals['total_int_paid'] if due_loan_payments_totals['total_int_paid'] is not None else 0 if (loan_dues_schedule.principal_expected - due_total_princ_paid) > 0 or (loan_dues_schedule.interest_expected - (due_total_int_paid + due_total_interest_waived)) > 0: total_expected_princ += (loan_dues_schedule.principal_expected - due_total_princ_paid) total_expected_int += (loan_dues_schedule.interest_expected - (due_total_int_paid + due_total_interest_waived)) # get schedule payments interest_waived = LoanInterestWaivered.objects.filter(loan_application=loan_details, loan_repayment_schedule=loan_dues_schedule, **extra_date_added).aggregate(total_amount=Sum('amount'))['total_amount'] total_interest_waived = interest_waived if interest_waived else 0 loan_payments_totals = _active_loan_payments_queryset( loan_application=loan_details, loan_repayment_schedule=loan_dues_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'), ) 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 #add the dues payments that happened within the selected dates princ_paid += total_princ_paid int_paid += (total_interest_waived + total_int_paid) total_expected_amount = total_expected_princ + total_expected_int penalty_payment = total_penalty_waived + loan_penalities_paid payment = princ_paid + int_paid rate = (payment/total_expected_amount) * 100 if total_expected_amount > 0 else 0 if start_date: prepaid_loan_repayment_schedules = _active_loan_schedules_queryset( loan_application=loan_details, status='active', expected_date__date__gt=as_at, ).order_by('id') for prepaid_loan_repayment_schedule in prepaid_loan_repayment_schedules: pre_paid_interest_waived = LoanInterestWaivered.objects.filter(loan_application=loan_details, loan_repayment_schedule=prepaid_loan_repayment_schedule, **extra_date_added).aggregate(total_amount=Sum('amount'))['total_amount'] total_pre_paid_interest_waived = pre_paid_interest_waived if pre_paid_interest_waived else 0 pre_paid_loan_payments_totals = _active_loan_payments_queryset( loan_application=loan_details, loan_repayment_schedule=prepaid_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'), ) total_pre_paid_princ_paid = pre_paid_loan_payments_totals['total_princ_paid'] if pre_paid_loan_payments_totals['total_princ_paid'] is not None else 0 total_pre_paid_int_paid = pre_paid_loan_payments_totals['total_int_paid'] if pre_paid_loan_payments_totals['total_int_paid'] is not None else 0 total_prepaid += total_pre_paid_interest_waived + total_pre_paid_princ_paid + total_pre_paid_int_paid total_expected_princ = total_expected_princ if total_expected_princ >= 1 else 0 total_expected_int = total_expected_int if total_expected_int >= 1 else 0 total_expected_amount = total_expected_amount if total_expected_amount >= 1 else 0 total_loan_penalties = total_loan_penalties if total_loan_penalties >= 1 else 0 total_principal_due = total_principal_due if total_principal_due >= 1 else 0 total_interest_due = total_interest_due if total_interest_due >= 1 else 0 total_penalty_due = total_penalty_due if total_penalty_due >= 1 else 0 total_due = total_principal_due + total_interest_due + total_penalty_due response = {"princ_expected":total_expected_princ, "int_expected":total_expected_int, "total_expected":total_expected_amount, "penalty_expected": total_loan_penalties, "princ_due": total_principal_due, "interest_due": total_interest_due, "penalty_due": total_penalty_due, "total_due": total_due, "total_prepaid":total_prepaid, "princ_paid":princ_paid, "int_paid":int_paid, "total_paid": princ_paid + int_paid + penalty_payment, "repayment_rate":round(rate, 2)} return response def loan_balance(loan_id, as_at=None, with_all_payments=False): principal_bal = 0 interest_bal = 0 penalty_bal = 0 interest_waivered = 0 penalty_waivered = 0 written_off_amount = 0 if not loan_id: return (0, 0, 0, 0) # Fetch loan details loan_details = LoanApplication.objects.get(pk=loan_id) if not loan_details: return (0, 0, 0, 0) # Base filter dicts payments_obj = {"loan_application": loan_details} others_obj = {"loan_application": loan_details} loan_schedule_filters = {"loan_application": loan_details, "status": "active"} if with_all_payments: loan_schedule_filters = {"loan_application": loan_details} # Apply as_at filter if provided if as_at: payments_obj['payment_date__date__lte'] = as_at others_obj['date_added__date__lte'] = as_at # Fetch loan disbursement loan_application_disbursement = LoanApplicationDisbursement.objects.filter(loan_application=loan_details).first() if not loan_application_disbursement: return (0, 0, 0, 0) # Fetch loan schedules loan_schedules = _active_loan_schedules_queryset(**loan_schedule_filters) principal_paid = 0 interest_paid = 0 penalty_paid = 0 for loan_schedule in loan_schedules: # Copy payments_obj to avoid mutation filters = payments_obj.copy() filters.pop('loan_application', None) payments_total = _active_loan_payments_queryset( loan_repayment_schedule=loan_schedule, loan_application=loan_details, payment_status='normal', **filters ).aggregate( total_int_paid=Sum('int_paid'), total_princ_paid=Sum('princ_paid') ) principal_paid += payments_total['total_princ_paid'] or 0 interest_paid += payments_total['total_int_paid'] or 0 # Penalty payments filters = payments_obj.copy() filters.pop('loan_application', None) penalty_payment_total = _active_loan_payments_queryset( loan_application=loan_details, payment_status='normal', **filters ).aggregate(total_penalty_paid=Sum('penalty_paid'))['total_penalty_paid'] penalty_paid = penalty_payment_total or 0 # Expected totals total_principal_expected = loan_application_disbursement.total_principal_expected total_interest_expected = loan_application_disbursement.total_interest_expected # Waivers filters = others_obj.copy() filters.pop('loan_application', None) waivered_interests = LoanInterestWaivered.objects.filter( loan_application=loan_details, **filters ).aggregate(total_waivered_interest=Sum('amount')) interest_waivered = waivered_interests['total_waivered_interest'] or 0 waivered_penalties = LoanPenaltyWaivered.objects.filter( loan_application=loan_details, **filters ).aggregate(total_penalty_waivered=Sum('amount')) penalty_waivered = waivered_penalties['total_penalty_waivered'] or 0 # Manual penalties manual_penalties = LoanPenalty.objects.filter( loan_application=loan_details, **filters ).aggregate(total_penalty=Sum('amount')) penalty_bal = (manual_penalties['total_penalty'] or 0) - (penalty_paid + penalty_waivered) # Rescheduled loans rescheduled_loan_details = RescheduledLoans.objects.filter(loan_application=loan_details).order_by('-id').first() if rescheduled_loan_details and not with_all_payments: total_interest_expected = rescheduled_loan_details.interest_expected or 0 total_principal_expected = rescheduled_loan_details.principal_amount or 0 principal_bal = total_principal_expected - principal_paid interest_bal = total_interest_expected - (interest_paid + interest_waivered) # Written-off loans filter_data = {"loan_application": loan_details} if as_at: filter_data['loan_system_transaction__record_date__date__lte'] = as_at write_off = LoanWrittenOff.objects.filter(**filter_data).first() if write_off: written_off_amount = write_off.loan_system_transaction.amount # Floor balances at 0 principal_bal = principal_bal if principal_bal >= 1 else 0 interest_bal = interest_bal if interest_bal >= 1 else 0 penalty_bal = penalty_bal if penalty_bal >= 1 else 0 return ( round(principal_bal, 2), round(interest_bal, 2), round(penalty_bal, 2), round(written_off_amount, 2) ) def loan_amounts_paid(loan_id, with_all_payments = False, as_at=None): principal_paid = 0 interest_paid = 0 penalty_paid = 0 if loan_id: loan_details = LoanApplication.objects.get(pk=loan_id ) if loan_details: payment_details = {"loan_application":loan_details} other_details = {"loan_application":loan_details} loan_schedule_filters = {"loan_application": loan_details, "status":"active"} if with_all_payments: loan_schedule_filters = {"loan_application": loan_details} current = timezone.now() current_date = current.strftime('%Y-%m-%d') if as_at is not None: current_date = as_at loan_application_disbursement = LoanApplicationDisbursement.objects.filter(loan_application=loan_details).first() if loan_application_disbursement: loan_schedules = _active_loan_schedules_queryset( **loan_schedule_filters ) for loan_schedule in loan_schedules: payments_total = _active_loan_payments_queryset( loan_repayment_schedule=loan_schedule, payment_date__date__lte=current_date, payment_status='normal', **payment_details, ).exclude( loan_main_transaction__system_transaction__isnull=True ).aggregate(total_int_paid=Sum('int_paid'), total_princ_paid = Sum('princ_paid') ) principal_paid += payments_total['total_princ_paid'] if payments_total['total_princ_paid'] is not None else 0 interest_paid += payments_total['total_int_paid'] if payments_total['total_int_paid'] is not None else 0 payments_total = _active_loan_payments_queryset( payment_status='normal', payment_date__date__lte=current_date, **payment_details, ).aggregate(total_penalty_paid = Sum('penalty_paid'))['total_penalty_paid'] penalty_paid = payments_total if payments_total else 0 #fetch sum of all waivered interest waivered_interests = LoanInterestWaivered.objects.filter(date_added__date__lte=current_date, **other_details).aggregate(total_waivered_interest=Sum('amount'))['total_waivered_interest'] interest_waivered = waivered_interests if waivered_interests else 0 #fetch sum of all waivered interest waivered_penalties = LoanPenaltyWaivered.objects.filter(date_added__date__lte=current_date, **other_details).aggregate(total_penalty_waivered=Sum('amount'))['total_penalty_waivered'] penalty_waivered = waivered_penalties if waivered_penalties else 0 penalty_paid = penalty_paid + penalty_waivered interest_paid = interest_paid + interest_waivered return (principal_paid, interest_paid, penalty_paid) def process_loan_payment(payment_details, request = None): user = request.user if request else payment_details.get('user', None) organisation_id = get_current_user(request, 'organisation_id', None) if request else payment_details.get('organisation_id') branch_id = get_current_user(request, 'organisation_branch_id', None) if request else payment_details.get('organisation_branch_id') for item in ['amount_paid', 'principal_paid', 'int_paid', 'penalty_paid', 'payment_method', 'account', 'date_added', 'voucher_no', 'cheque', 'loan_id']: if item not in payment_details: return False amount_paid = payment_details['amount_paid'] principal_paid = payment_details['principal_paid'] int_paid = payment_details['int_paid'] penalty_paid = payment_details['penalty_paid'] payment_method = payment_details['payment_method'] account = payment_details['account'] date_added = payment_details['date_added'] voucher_no = payment_details['voucher_no'] cheque = payment_details['cheque'] loan_id = payment_details['loan_id'] account_id = payment_details['account_id'] heading = 'Loan Payment' # InterBranch chart saving_account = None bank_account = None penalty_paid_transaction = None loan_main_payment = None try: loan_application = LoanApplication.objects.get(pk=loan_id) interbranch_chart = get_inter_branch_chart(OrganisationBranch.objects.get(pk=branch_id), loan_application.organisation_branch) if payment_method == "offset": saving_account = SavingAccount.objects.filter(id=account_id).first() if not saving_account: return False interbranch_chart = get_inter_branch_chart(saving_account.customer_branch, loan_application.organisation_branch) elif payment_method == "bank" or payment_method == 'cheque': bank_account = BankAccounts.objects.filter(id=account_id).first() if not bank_account: return False interbranch_chart = get_inter_branch_chart(bank_account.branch, loan_application.organisation_branch) selected_account = OrganisationSubAccount.objects.get(pk=account) # Generate reference number credit_chart = loan_application.loan_application_product.chart loan_product = loan_application.loan_application_product.product_name interest_income_chart = loan_application.loan_application_product.interest_income_chart penality_income_chart = loan_application.loan_application_product.penalty_income_chart if principal_paid > 0 or int_paid > 0 or penalty_paid > 0: loan_main_payment = LoanPaymentTransaction.objects.create(amount=amount_paid, loan_application=loan_application, loan_payment_transaction_added_by=user, payment_date=date_added) # branch details source_branch_id = branch_id if payment_method == "offset" and saving_account: source_branch_id = saving_account.customer_branch.id elif (payment_method == "bank" or payment_method == "cheque") and bank_account: source_branch_id = bank_account.branch.id # post principal if principal_paid > 0: heading = 'Loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation_id, 'ln-p') transaction = None transaction_2 = None # Handle inter-branch transactions update -> soure branch if (branch_id != loan_application.organisation_branch.id and payment_method == "cash") or (payment_method == "offset" and saving_account.customer_branch.id != loan_application.organisation_branch.id) or ((payment_method == "bank" or payment_method == "cheque") and bank_account.branch.id != loan_application.organisation_branch.id): heading = 'Inter-branch Loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product transaction = SystemTransactions.objects.create(amount=principal_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=selected_account.id, credit_chart_id=interbranch_chart.id, branch_id=source_branch_id, added_by=user, record_date=date_added) if not transaction: return False # Handle inter-branch transactions update -> destination branch transaction_2 = SystemTransactions.objects.create(amount=principal_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=interbranch_chart.id, credit_chart_id=credit_chart.id, branch_id=loan_application.organisation_branch.id, added_by=user, record_date=date_added) if not transaction_2: return False # Reconcile inter-branch transactions if transaction and transaction_2: inter_branch_trans_field = { "source_transaction":transaction, "destination_transaction":transaction_2, "added_by":user, } InterBranchTransactions.objects.create(**inter_branch_trans_field) else: transacting_branch = branch_id if payment_method == "offset" and branch_id != loan_application.organisation_branch.id: transacting_branch = loan_application.organisation_branch.id transaction = SystemTransactions.objects.create(amount=principal_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=selected_account.id, credit_chart_id=credit_chart.id, branch_id=transacting_branch, added_by=user, record_date=date_added) if not transaction: return False data = {"heading":heading, "amount":int_paid, "cheque":cheque, "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":voucher_no, "transaction_type":'LoanPrincipalPayment', "loan_main_transaction_added_by":user, "payment_date":date_added, "system_transaction":transaction} if transaction_2: data['system_transaction'] = transaction_2 principal_paid_transaction = LoanMainTransactions.objects.create(**data) if not principal_paid_transaction: return False if payment_method == 'offset': saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if loan_application.loan_group and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # post interest if int_paid > 0: heading = 'Loan interest income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation_id, 'ln-in') transaction = None transaction_2 = None # Handle inter-branch transactions update -> soure branch if (branch_id != loan_application.organisation_branch.id and payment_method == "cash") or (payment_method == "offset" and saving_account.customer_branch.id != loan_application.organisation_branch.id) or ((payment_method == "bank" or payment_method == "cheque") and bank_account.branch.id != loan_application.organisation_branch.id): heading = 'Inter-branch Loan interest income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product transaction = SystemTransactions.objects.create(amount=int_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=selected_account.id, credit_chart_id=interbranch_chart.id, branch_id=source_branch_id, added_by=user, record_date=date_added) if not transaction: return False # Handle inter-branch transactions update -> destination branch transaction_2 = SystemTransactions.objects.create(amount=int_paid, heading=heading, reference_no=reference_no, payment_method='settlement',voucher_no=voucher_no, debit_chart_id=interbranch_chart.id, credit_chart_id=interest_income_chart.id, branch_id=loan_application.organisation_branch.id, added_by=user, record_date=date_added) if not transaction_2: return False # Reconcile inter-branch transactions if transaction and transaction_2: inter_branch_trans_field = { "source_transaction":transaction, "destination_transaction":transaction_2, "added_by":user, } InterBranchTransactions.objects.create(**inter_branch_trans_field) else: transacting_branch = branch_id if payment_method == "offset" and branch_id != loan_application.organisation_branch.id: transacting_branch = loan_application.organisation_branch.id transaction = SystemTransactions.objects.create(amount=int_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=selected_account.id, credit_chart_id=interest_income_chart.id, branch_id=transacting_branch, added_by=user, record_date=date_added) if not transaction: return False data = {"heading":heading, "amount":int_paid, "cheque":cheque, "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":voucher_no, "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "payment_date":date_added, "system_transaction":transaction} if transaction_2: data['system_transaction'] = transaction_2 int_paid_transaction = LoanMainTransactions.objects.create(**data) if not int_paid_transaction: return False if payment_method == 'offset': saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if loan_application.loan_group and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # post penalty if penalty_paid > 0: heading = 'Loan penalty income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation_id, 'ln-in') transaction = None transaction_2 = None # Handle inter-branch transactions update -> soure branch if (branch_id != loan_application.organisation_branch.id and payment_method == "cash") or (payment_method == "offset" and saving_account.customer_branch.id != loan_application.organisation_branch.id) or ((payment_method == "bank" or payment_method == "cheque") and bank_account.branch.id != loan_application.organisation_branch.id): heading = 'Inter-branch Loan penalty income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product transaction = SystemTransactions.objects.create(amount=penalty_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=selected_account.id, credit_chart_id=interbranch_chart.id, branch_id=source_branch_id, added_by=user, record_date=date_added) if not transaction: return False # Handle inter-branch transactions update -> destination branch transaction_2 = SystemTransactions.objects.create(amount=penalty_paid, heading=heading, reference_no=reference_no, payment_method='settlement',voucher_no=voucher_no, debit_chart_id=interbranch_chart.id, credit_chart_id=penality_income_chart.id, branch_id=loan_application.organisation_branch.id, added_by=user, record_date=date_added) if not transaction_2: return False # Reconcile inter-branch transactions if transaction and transaction_2: inter_branch_trans_field = { "source_transaction":transaction, "destination_transaction":transaction_2, "added_by":user, } InterBranchTransactions.objects.create(**inter_branch_trans_field) else: transacting_branch = branch_id if payment_method == "offset" and branch_id != loan_application.organisation_branch.id: transacting_branch = loan_application.organisation_branch.id transaction = SystemTransactions.objects.create(amount=penalty_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=selected_account.id, credit_chart_id=penality_income_chart.id, branch_id=transacting_branch, added_by=user, record_date=date_added) if not transaction: return False data = {"heading":heading, "amount":penalty_paid, "cheque":cheque, "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":voucher_no, "transaction_type":'PenaltyPayment', "loan_main_transaction_added_by":user, "payment_date":date_added, "system_transaction":transaction} if transaction_2: data['system_transaction'] = transaction_2 penalty_paid_transaction = LoanMainTransactions.objects.create(**data) if not penalty_paid_transaction: return False if payment_method == 'offset': saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if loan_application.loan_group and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # calculate the paid schedules payment_schedules = [] list_loan_schedules = LoanRepaymentSchedule.objects.filter(status="active", loan_application=loan_application).order_by('payment_number') for list_loan_schedule in list_loan_schedules: pay_int = 0 pay_princ = 0 schedule_payment = LoanPayments.objects.filter(loan_repayment_schedule=list_loan_schedule, loan_application=loan_application, payment_status='normal').aggregate(total_int_paid=Sum('int_paid'), total_princ_paid = Sum('princ_paid') , total_penalty_paid = Sum('penalty_paid')) total_int_paid = round(float(schedule_payment['total_int_paid']), 2) if schedule_payment['total_int_paid'] else 0 total_princ_paid = round(float(schedule_payment['total_princ_paid']), 2) if schedule_payment['total_princ_paid'] else 0 # added loan interest waived off on payments interest_waivered = LoanInterestWaivered.objects.filter(loan_application=loan_application, loan_repayment_schedule=list_loan_schedule).aggregate(total=Sum('amount'))['total'] interest_waivered = round(interest_waivered, 2) if interest_waivered else 0 total_int_paid += interest_waivered # if no payment for schedule if total_int_paid == 0: if int_paid - list_loan_schedule.interest_expected > 0: pay_int = list_loan_schedule.interest_expected elif int_paid - list_loan_schedule.interest_expected <= 0 and int_paid > 0: pay_int = int_paid if total_princ_paid == 0: if principal_paid - list_loan_schedule.principal_expected > 0: pay_princ = list_loan_schedule.principal_expected elif principal_paid - list_loan_schedule.principal_expected <= 0 and principal_paid > 0: pay_princ = principal_paid # if there's a partial payment for a schedule if total_int_paid > 0: int_balance = round(list_loan_schedule.interest_expected - total_int_paid, 2) if int_balance == 0: pay_int = 0 elif int_paid - int_balance > 0 and int_balance > 0: pay_int = int_balance elif int_paid - int_balance <= 0 and int_paid > 0: pay_int = int_paid if total_princ_paid > 0: princ_balance = round(list_loan_schedule.principal_expected - total_princ_paid, 2) if princ_balance == 0: pay_princ = 0 elif principal_paid - princ_balance > 0 and princ_balance > 0: pay_princ = princ_balance elif principal_paid - princ_balance <= 0 and principal_paid > 0: pay_princ = principal_paid if pay_int > 0: payment_schedules.append({"loan_schedule":list_loan_schedule.id, "int_paid":pay_int, "principal_paid": 0, "loan_main_transaction":int_paid_transaction}) if pay_princ > 0: payment_schedules.append({"loan_schedule":list_loan_schedule.id, "int_paid": 0, "principal_paid":pay_princ, "loan_main_transaction":principal_paid_transaction }) int_paid = round(int_paid - pay_int, 2) principal_paid = round(principal_paid - pay_princ, 2) if int_paid < 1 and principal_paid < 1: break for payment_schedule in payment_schedules: post_loan_schedule = LoanRepaymentSchedule.objects.get(pk=payment_schedule['loan_schedule']) data = {"loan_application":loan_application, "loan_repayment_schedule":post_loan_schedule, "loan_payments_added_by":user, "loan_main_transaction":payment_schedule['loan_main_transaction'], "int_paid":payment_schedule['int_paid'], "princ_paid":payment_schedule['principal_paid'], "penalty_paid":0, "payment_date":date_added, "loan_payment_transaction":loan_main_payment} loan_payment_details = LoanPayments.objects.create(**data) if not loan_payment_details: return False # post penalty if penalty_paid_transaction: data = {"loan_application":loan_application, "loan_payments_added_by":user, "loan_main_transaction":penalty_paid_transaction, "int_paid":0, "princ_paid":0, "penalty_paid":penalty_paid, "payment_date":date_added, "loan_payment_transaction":loan_main_payment} loan_penalty_payment_details = LoanPayments.objects.create(**data) if not loan_penalty_payment_details: return False except Exception as e: print(e) return True def process_loan_recovery(loan_recovery_details, request): user = request.user organisation_id = get_current_user(request, 'organisation_id', None) branch_id = get_current_user(request, 'organisation_branch_id', None) for item in ['recovered_ammount', 'payment_method', 'debit_account', 'credit_account','recovery_date', 'loan_id']: if item not in loan_recovery_details: return False recovered_ammount = loan_recovery_details['recovered_ammount'] payment_method = loan_recovery_details['payment_method'] debit_account = loan_recovery_details['debit_account'] recovery_date = loan_recovery_details['recovery_date'] voucher_no = loan_recovery_details['voucher_no'] loan_id = loan_recovery_details['loan_id'] account_id = loan_recovery_details['account_id'] heading = 'Loan Payment' credit_account = loan_recovery_details['credit_account'] # InterBranch chart saving_account = None bank_account = None try: loan_application = LoanApplication.objects.get(pk=loan_id) interbranch_chart = get_inter_branch_chart(OrganisationBranch.objects.get(pk=branch_id), loan_application.organisation_branch) if payment_method == "offset": saving_account = SavingAccount.objects.filter(id=account_id).first() if not saving_account: return False interbranch_chart = get_inter_branch_chart(saving_account.customer_branch, loan_application.organisation_branch) elif payment_method == "bank" or payment_method == 'cheque': bank_account = BankAccounts.objects.filter(id=account_id).first() if not bank_account: return False interbranch_chart = get_inter_branch_chart(bank_account.branch, loan_application.organisation_branch) loan_product = loan_application.loan_application_product.product_name # post principal if recovered_ammount is not None and int(recovered_ammount) > 0: heading = 'Loan recovery payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_account.account_line, organisation_id, 'ln-r') transaction = None transaction_2 = None # Handle inter-branch transactions update -> soure branch if (branch_id != loan_application.organisation_branch.id and payment_method == "cash") or (payment_method == "offset" and saving_account.customer_branch.id != loan_application.organisation_branch.id) or ((payment_method == "bank" or payment_method == "cheque") and bank_account.branch.id != loan_application.organisation_branch.id): heading = 'Inter-branch Loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product source_branch = branch_id if payment_method == "offset": source_branch = saving_account.customer_branch.id transaction = SystemTransactions.objects.create(amount=recovered_ammount, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=debit_account.id, credit_chart_id=interbranch_chart.id, branch_id=source_branch, added_by=user, record_date=recovery_date) if not transaction: return False # Handle inter-branch transactions update -> destination branch transaction_2 = SystemTransactions.objects.create(amount=recovered_ammount, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=interbranch_chart.id, credit_chart_id=credit_account.id, branch_id=loan_application.organisation_branch.id, added_by=user, record_date=recovery_date) if not transaction_2: return False # Reconcile inter-branch transactions if transaction and transaction_2: inter_branch_trans_field = { "source_transaction":transaction, "destination_transaction":transaction_2, "added_by":user, } InterBranchTransactions.objects.create(**inter_branch_trans_field) else: transacting_branch = branch_id if payment_method == "offset" and branch_id != loan_application.organisation_branch.id: transacting_branch = loan_application.organisation_branch.id transaction = SystemTransactions.objects.create(amount=recovered_ammount, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=debit_account.id, credit_chart_id=credit_account.id, branch_id=transacting_branch, added_by=user, record_date=recovery_date) if not transaction: return False data = {"heading":heading, "amount":recovered_ammount, "cheque":voucher_no, "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":voucher_no, "transaction_type":'LoanRecovery', "loan_main_transaction_added_by":user, "payment_date":recovery_date, "system_transaction":transaction} if transaction_2: transaction = transaction_2 data['system_transaction'] = transaction_2 principal_paid_transaction = LoanMainTransactions.objects.create(**data) if not principal_paid_transaction: return False if payment_method == 'offset': saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if loan_application.loan_group and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) data = {"loan_application":loan_application, "recovery_added_by":user, "recovered_ammount":recovered_ammount, "loan_main_transaction":principal_paid_transaction,"recovery_date":recovery_date} loan_payment_details = LoanRecovery.objects.create(**data) if not loan_payment_details: return False except Exception as e: print(e) return True def auto_loan_repayment(payment_data): loan_id = payment_data['loan'] interest_paid = payment_data['interest'] principal_paid = payment_data['principal'] penalty_paid = payment_data['penalty'] total = interest_paid + principal_paid schedule = payment_data['schedule'] tota_amount_paid = 0 account_balance = 0 pay_details = {} isGroupLoanPayment = False loan_schedule = LoanRepaymentSchedule.objects.filter(id=schedule).first() if not loan_schedule: return False loan_application = LoanApplication.objects.filter(id=loan_id, is_deleted=False).first() if not loan_application: return False # check if auto pay penalty if loan_application.loan_application_product.auto_pay_penalty: total += penalty_paid else: penalty_paid = 0 savings_account = SavingAccount.objects.filter(account_customer=loan_application.customer,status='active').all().order_by('id').first() payment_method = 'offset' selected_account = None user = loan_application.loan_app_added_by # Get loan off set account if savings_account: account_bal = get_account_balance(savings_account) account_balance = account_bal["balance_raw"] if account_bal and account_bal["balance_raw"] > 0 else 0 selected_account = savings_account.account_product.accounts_chart # Get group loan off set account if (loan_application.loan_group) and (not savings_account or account_balance < 100): group_accounts = SavingAccount.objects.filter(account_customer=loan_application.loan_group, status='active').all() for group_account in group_accounts: isGroupLoanPayment = True account_balance = get_group_memebr_account_balance(group_account,loan_application.customer) savings_account = group_account selected_account = group_account.account_product.accounts_chart if account_balance > 0: break if savings_account: pay_details = { "penalty_paid":0, "interest_paid":0, "principal_paid":0, "branch":savings_account.customer_branch.id, "branch_name":savings_account.customer_branch.name, "payment_date":0, "saving_account":savings_account.account_no, "loan_schedule":None } # Generate reference number loan_product = loan_application.loan_application_product.product_name organisation = loan_application.organisation_branch.branch_organisation credit_chart = loan_application.loan_application_product.chart interest_income_chart = loan_application.loan_application_product.interest_income_chart penalty_income_chart = loan_application.loan_application_product.penalty_income_chart loan_payment_transaction = None interbranch_chart = get_inter_branch_chart(savings_account.customer_branch, loan_application.organisation_branch) if account_balance >= total: # make paymet of both interest and principal, penalty # penalty payment if penalty_paid > 0 and loan_application.loan_application_product.auto_pay_penalty and account_balance >= penalty_paid: heading = 'Auto loan penalty income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-in') # inter branch chart transaction2 = None transaction_1 = {"amount":penalty_paid, "heading": heading, "reference_no": reference_no, "payment_method":payment_method, "voucher_no":'', "debit_chart_id":selected_account.id, "credit_chart_id":penalty_income_chart.id, "branch_id":savings_account.customer_branch.id, "added_by":user } if int(savings_account.customer_branch.id) != int(loan_application.organisation_branch.id): heading = f"Inter-branch {heading}" transaction_1['credit_chart_id'] = interbranch_chart.id transaction_1['heading'] = heading transaction_2 = {"amount":penalty_paid, "heading": heading, "reference_no": reference_no, "payment_method":'settlement', "voucher_no":'', "debit_chart_id":interbranch_chart.id, "credit_chart_id":penalty_income_chart.id, "branch_id":loan_application.organisation_branch.id, "added_by":user } transaction2 = SystemTransactions.objects.create(**transaction_2) transaction = SystemTransactions.objects.create(**transaction_1) if transaction: if transaction2: InterBranchTransactions.objects.create(source_transaction=transaction, destination_transaction=transaction2, added_by=user) data = {"heading":heading, "amount":penalty_paid, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'PenaltyPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction} int_paid_transaction = LoanMainTransactions.objects.create(**data) if int_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if isGroupLoanPayment and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # post payment loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=penalty_paid, loan_application=loan_application, transaction_type='auto' ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":int_paid_transaction, "penalty_paid":penalty_paid, "loan_payment_transaction":loan_payment_transaction} loan_details = LoanPayments.objects.create(**payment_data) tota_amount_paid += penalty_paid account_balance -= penalty_paid penalty_paid = 0 pay_details[ "penalty_paid"] = loan_details.penalty_paid pay_details[ "payment_date"] = loan_details.payment_date.date() # interest payment if interest_paid > 0 and account_balance >= interest_paid: heading = 'Auto loan interest income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-in') # inter branch chart transaction2 = None transaction_1 = {"amount":interest_paid, "heading": heading, "reference_no": reference_no, "payment_method":payment_method, "voucher_no":'', "debit_chart_id":selected_account.id, "credit_chart_id":interest_income_chart.id, "branch_id":savings_account.customer_branch.id, "added_by":user } if int(savings_account.customer_branch.id) != int(loan_application.organisation_branch.id): heading = f"Inter-branch {heading}" transaction_1['credit_chart_id'] = interbranch_chart.id transaction_1['heading'] = heading transaction_2 = {"amount":interest_paid, "heading": heading, "reference_no": reference_no, "payment_method":'settlement', "voucher_no":'', "debit_chart_id":interbranch_chart.id, "credit_chart_id":interest_income_chart.id, "branch_id":loan_application.organisation_branch.id, "added_by":user } transaction2 = SystemTransactions.objects.create(**transaction_2) transaction = SystemTransactions.objects.create(**transaction_1) if transaction: if transaction2: InterBranchTransactions.objects.create(source_transaction=transaction, destination_transaction=transaction2, added_by=user) data = {"heading":heading, "amount":interest_paid, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction} int_paid_transaction = LoanMainTransactions.objects.create(**data) if int_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if isGroupLoanPayment and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=interest_paid, loan_application=loan_application, transaction_type='auto' ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":int_paid_transaction, "int_paid":interest_paid, "loan_payment_transaction":loan_payment_transaction} loan_details = LoanPayments.objects.create(**payment_data) tota_amount_paid += interest_paid loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance -= interest_paid interest_paid = 0 pay_details[ "interest_paid"] = loan_details.int_paid pay_details[ "payment_date"] = loan_details.payment_date.date() # principal payment if principal_paid > 0 and account_balance >= principal_paid: heading = 'Auto loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-p') # inter branch chart transaction2 = None transaction_1 = {"amount":principal_paid, "heading": heading, "reference_no": reference_no, "payment_method":payment_method, "voucher_no":'', "debit_chart_id":selected_account.id, "credit_chart_id":credit_chart.id, "branch_id":savings_account.customer_branch.id, "added_by":user } if int(savings_account.customer_branch.id) != int(loan_application.organisation_branch.id): heading = f"Inter-branch {heading}" transaction_1['credit_chart_id'] = interbranch_chart.id transaction_1['heading'] = heading transaction_2 = {"amount":principal_paid, "heading": heading, "reference_no": reference_no, "payment_method":'settlement', "voucher_no":'', "debit_chart_id":interbranch_chart.id, "credit_chart_id":credit_chart.id, "branch_id":loan_application.organisation_branch.id, "added_by":user } transaction2 = SystemTransactions.objects.create(**transaction_2) transaction = SystemTransactions.objects.create(**transaction_1) if transaction: if transaction2: InterBranchTransactions.objects.create(source_transaction=transaction, destination_transaction=transaction2, added_by=user) data = {"heading":heading, "amount":principal_paid, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'LoanPrincipalPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction} principal_paid_transaction = LoanMainTransactions.objects.create(**data) if principal_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if isGroupLoanPayment and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=principal_paid, loan_application=loan_application, transaction_type='auto' ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":principal_paid_transaction, "princ_paid":principal_paid, "loan_payment_transaction":loan_payment_transaction} loan_details = LoanPayments.objects.create(**payment_data) tota_amount_paid += principal_paid loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance -= principal_paid principal_paid = 0 pay_details[ "principal_paid"] = loan_details.princ_paid pay_details[ "payment_date"] = loan_details.payment_date.date() else: if account_balance >= penalty_paid and penalty_paid > 0 and loan_application.loan_application_product.auto_pay_penalty: # make penalty payment only heading = 'Auto loan penalty income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-in') # inter branch chart transaction2 = None transaction_1 = {"amount":penalty_paid, "heading": heading, "reference_no": reference_no, "payment_method":payment_method, "voucher_no":'', "debit_chart_id":selected_account.id, "credit_chart_id":penalty_income_chart.id, "branch_id":savings_account.customer_branch.id, "added_by":user } if int(savings_account.customer_branch.id) != int(loan_application.organisation_branch.id): heading = f"Inter-branch {heading}" transaction_1['credit_chart_id'] = interbranch_chart.id transaction_1['heading'] = heading transaction_2 = {"amount":penalty_paid, "heading": heading, "reference_no": reference_no, "payment_method":'settlement', "voucher_no":'', "debit_chart_id":interbranch_chart.id, "credit_chart_id":penalty_income_chart.id, "branch_id":loan_application.organisation_branch.id, "added_by":user } transaction2 = SystemTransactions.objects.create(**transaction_2) transaction = SystemTransactions.objects.create(**transaction_1) if transaction: if transaction2: InterBranchTransactions.objects.create(source_transaction=transaction, destination_transaction=transaction2, added_by=user) data = {"heading":heading, "amount":penalty_paid, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction} int_paid_transaction = LoanMainTransactions.objects.create(**data) if int_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if isGroupLoanPayment and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=penalty_paid, loan_application=loan_application, transaction_type='auto' ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":int_paid_transaction, "penalty_paid":penalty_paid, "loan_payment_transaction":loan_payment_transaction} loan_details = LoanPayments.objects.create(**payment_data) tota_amount_paid += penalty_paid loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance -= penalty_paid penalty_paid = 0 pay_details[ "penalty_paid"] = loan_details.penalty_paid pay_details[ "payment_date"] = loan_details.payment_date.date() if account_balance >= interest_paid and interest_paid > 0: # make interest payment only heading = 'Auto loan interest income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-in') # inter branch chart transaction2 = None transaction_1 = {"amount":interest_paid, "heading": heading, "reference_no": reference_no, "payment_method":payment_method, "voucher_no":'', "debit_chart_id":selected_account.id, "credit_chart_id":interest_income_chart.id, "branch_id":savings_account.customer_branch.id, "added_by":user } if int(savings_account.customer_branch.id) != int(loan_application.organisation_branch.id): heading = f"Inter-branch {heading}" transaction_1['credit_chart_id'] = interbranch_chart.id transaction_1['heading'] = heading transaction_2 = {"amount":interest_paid, "heading": heading, "reference_no": reference_no, "payment_method":'settlement', "voucher_no":'', "debit_chart_id":interbranch_chart.id, "credit_chart_id":interest_income_chart.id, "branch_id":loan_application.organisation_branch.id, "added_by":user } transaction2 = SystemTransactions.objects.create(**transaction_2) transaction = SystemTransactions.objects.create(**transaction_1) if transaction: if transaction2: InterBranchTransactions.objects.create(source_transaction=transaction, destination_transaction=transaction2, added_by=user) data = {"heading":heading, "amount":interest_paid, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction} int_paid_transaction = LoanMainTransactions.objects.create(**data) if int_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if isGroupLoanPayment and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=interest_paid, loan_application=loan_application, transaction_type='auto' ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":int_paid_transaction, "int_paid":interest_paid, "loan_payment_transaction":loan_payment_transaction} loan_details = LoanPayments.objects.create(**payment_data) tota_amount_paid += interest_paid loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance -= interest_paid interest_paid = 0 pay_details[ "interest_paid"] = loan_details.int_paid pay_details[ "payment_date"] = loan_details.payment_date.date() if account_balance >= principal_paid and principal_paid > 0: # make principal payment only heading = 'Auto loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-p') # inter branch chart transaction2 = None transaction_1 = {"amount":principal_paid, "heading": heading, "reference_no": reference_no, "payment_method":payment_method, "voucher_no":'', "debit_chart_id":selected_account.id, "credit_chart_id":credit_chart.id, "branch_id":savings_account.customer_branch.id, "added_by":user } if int(savings_account.customer_branch.id) != int(loan_application.organisation_branch.id): heading = f"Inter-branch {heading}" transaction_1['credit_chart_id'] = interbranch_chart.id transaction_1['heading'] = heading transaction_2 = {"amount":principal_paid, "heading": heading, "reference_no": reference_no, "payment_method":'settlement', "voucher_no":'', "debit_chart_id":interbranch_chart.id, "credit_chart_id":credit_chart.id, "branch_id":loan_application.organisation_branch.id, "added_by":user } transaction2 = SystemTransactions.objects.create(**transaction_2) transaction = SystemTransactions.objects.create(**transaction_1) if transaction: if transaction2: InterBranchTransactions.objects.create(source_transaction=transaction, destination_transaction=transaction2, added_by=user) data = {"heading":heading, "amount":principal_paid, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'LoanPrincipalPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction} principal_paid_transaction = LoanMainTransactions.objects.create(**data) pay_details[ "principal_paid"] = principal_paid pay_details[ "payment_date"] = principal_paid_transaction.payment_date.date() if principal_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if isGroupLoanPayment and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=principal_paid, loan_application=loan_application, transaction_type='auto' ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":principal_paid_transaction, "princ_paid":principal_paid, "loan_payment_transaction":loan_payment_transaction} loan_details = LoanPayments.objects.create(**payment_data) tota_amount_paid += principal_paid loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance -= principal_paid principal_paid = 0 pay_details[ "principal_paid"] = loan_details.princ_paid pay_details[ "payment_date"] = loan_details.payment_date.date() # pay if account has less money if account_balance > 0 and account_balance < penalty_paid and penalty_paid > 0: # make penalty payment only heading = 'Auto loan penalty income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-in') # inter branch chart transaction2 = None transaction_1 = {"amount":account_balance, "heading": heading, "reference_no": reference_no, "payment_method":payment_method, "voucher_no":'', "debit_chart_id":selected_account.id, "credit_chart_id":penalty_income_chart.id, "branch_id":savings_account.customer_branch.id, "added_by":user } if int(savings_account.customer_branch.id) != int(loan_application.organisation_branch.id): heading = f"Inter-branch {heading}" transaction_1['credit_chart_id'] = interbranch_chart.id transaction_1['heading'] = heading transaction_2 = {"amount":account_balance, "heading": heading, "reference_no": reference_no, "payment_method":'settlement', "voucher_no":'', "debit_chart_id":interbranch_chart.id, "credit_chart_id":penalty_income_chart.id, "branch_id":loan_application.organisation_branch.id, "added_by":user } transaction2 = SystemTransactions.objects.create(**transaction_2) transaction = SystemTransactions.objects.create(**transaction_1) if transaction: if transaction2: InterBranchTransactions.objects.create(source_transaction=transaction, destination_transaction=transaction2, added_by=user) data = {"heading":heading, "amount":account_balance, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction} int_paid_transaction = LoanMainTransactions.objects.create(**data) if int_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if isGroupLoanPayment and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=account_balance, loan_application=loan_application, transaction_type='auto' ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":int_paid_transaction, "penalty_paid":account_balance, "loan_payment_transaction":loan_payment_transaction} loan_details = LoanPayments.objects.create(**payment_data) tota_amount_paid += account_balance loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance = 0 penalty_paid = 0 pay_details[ "penalty_paid"] = loan_details.penalty_paid pay_details[ "payment_date"] = loan_details.payment_date.date() if account_balance > 0 and account_balance < interest_paid and interest_paid > 0: # make interest payment only heading = 'Auto loan interest income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-in') # inter branch chart transaction2 = None transaction_1 = {"amount":account_balance, "heading": heading, "reference_no": reference_no, "payment_method":payment_method, "voucher_no":'', "debit_chart_id":selected_account.id, "credit_chart_id":interest_income_chart.id, "branch_id":savings_account.customer_branch.id, "added_by":user } if int(savings_account.customer_branch.id) != int(loan_application.organisation_branch.id): heading = f"Inter-branch {heading}" transaction_1['credit_chart_id'] = interbranch_chart.id transaction_1['heading'] = heading transaction_2 = {"amount":account_balance, "heading": heading, "reference_no": reference_no, "payment_method":'settlement', "voucher_no":'', "debit_chart_id":interbranch_chart.id, "credit_chart_id":interest_income_chart.id, "branch_id":loan_application.organisation_branch.id, "added_by":user } transaction2 = SystemTransactions.objects.create(**transaction_2) transaction = SystemTransactions.objects.create(**transaction_1) if transaction: if transaction2: InterBranchTransactions.objects.create(source_transaction=transaction, destination_transaction=transaction2, added_by=user) data = {"heading":heading, "amount":account_balance, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction} int_paid_transaction = LoanMainTransactions.objects.create(**data) if int_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if isGroupLoanPayment and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=account_balance, loan_application=loan_application, transaction_type='auto' ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":int_paid_transaction, "int_paid":account_balance, "loan_payment_transaction":loan_payment_transaction} loan_details = LoanPayments.objects.create(**payment_data) tota_amount_paid += account_balance loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance = 0 interest_paid = 0 pay_details[ "interet_paid"] = loan_details.int_paid pay_details[ "payment_date"] = loan_details.payment_date.date() if account_balance > 0 and account_balance < principal_paid and principal_paid > 0: # make principal payment only heading = 'Auto loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-p') # inter branch chart transaction2 = None transaction_1 = {"amount":account_balance, "heading": heading, "reference_no": reference_no, "payment_method":payment_method, "voucher_no":'', "debit_chart_id":selected_account.id, "credit_chart_id":credit_chart.id, "branch_id":savings_account.customer_branch.id, "added_by":user } if int(savings_account.customer_branch.id) != int(loan_application.organisation_branch.id): heading = f"Inter-branch {heading}" transaction_1['credit_chart_id'] = interbranch_chart.id transaction_1['heading'] = heading transaction_2 = {"amount":account_balance, "heading": heading, "reference_no": reference_no, "payment_method":'settlement', "voucher_no":'', "debit_chart_id":interbranch_chart.id, "credit_chart_id":credit_chart.id, "branch_id":loan_application.organisation_branch.id, "added_by":user } transaction2 = SystemTransactions.objects.create(**transaction_2) transaction = SystemTransactions.objects.create(**transaction_1) if transaction: if transaction2: InterBranchTransactions.objects.create(source_transaction=transaction, destination_transaction=transaction2, added_by=user) data = {"heading":heading, "amount":account_balance, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'LoanPrincipalPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction} principal_paid_transaction = LoanMainTransactions.objects.create(**data) if principal_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if isGroupLoanPayment and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=account_balance, loan_application=loan_application, transaction_type='auto' ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":principal_paid_transaction, "princ_paid":account_balance, "loan_payment_transaction":loan_payment_transaction} loan_details = LoanPayments.objects.create(**payment_data) tota_amount_paid += account_balance loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance = 0 principal_paid = 0 pay_details[ "principal_paid"] = loan_details.princ_paid pay_details[ "payment_date"] = loan_details.payment_date.date() if pay_details['penalty_paid'] > 0 or pay_details['interest_paid'] > 0 or pay_details['principal_paid'] > 0: short_name = organisation.short_name if organisation.short_name else '' sms_msg = f"Dear {(loan_application.customer.name).capitalize()}, Loan Payment for {loan_product} : Amount UGX Principal: {pay_details['principal_paid']} Interest: {pay_details['interest_paid']} Penalty: {pay_details['penalty_paid']} for instalment period {loan_schedule.expected_date.date()} \n {short_name}" data = {"sms_key":"auto_loan_payment_sms","customer":loan_application.customer,"user":user,"branch_id":pay_details['branch'],"sms_msg":sms_msg,"loan":loan_application} send_customer_sms(data) save_user_notification({ "heading": "Loan Auto Payments", "message": f"Loan Auto Payment. Principal Paid: {pay_details[ 'principal_paid']} Interest Paid: {pay_details[ 'interest_paid']} Penalty Paid: {pay_details[ 'penalty_paid']} from {pay_details['saving_account']} as at {pay_details['payment_date']}", "branch":OrganisationBranch.objects.get(pk=savings_account.customer_branch.id), "branch_name":pay_details['branch_name'], "added_by":None, "last_updated_by":None, "key":"loan_notifications" }) def process_edit_loan_disbursement(request): amount = request.data.get('loan_amount') loan_disbursement_date = request.data.get('loan_disbursement_date') loan_start_date = request.data.get('loan_start_date') loan_application_product = request.data.get('loan_application_product') loan_period = request.data.get('loan_period') period_type = request.data.get('period_type') frequency = request.data.get('frequency') grace_period_type = request.data.get('grace_period_type') loan_sector = request.data.get('loan_sector') loan_officer = request.data.get('loan_officer') int_rate = request.data.get('int_rate') app_grace_period = request.data.get('app_grace_period') int_method = request.data.get('int_method') loan_application_id = request.data.get('loan_application_id') comment = request.data.get('comment') action = request.data.get('action') loan_application = LoanApplication.objects.get(pk=loan_application_id) if not loan_application: return Response({"message":"No loan application found"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) loan_application_approval = LoanApplicationApproval.objects.filter(loan_application=loan_application).first() if not loan_application_approval: return Response({"message":"Loan not approved"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) loan_application_disbursement = LoanApplicationDisbursement.objects.filter(loan_application=loan_application).first() if not loan_application_disbursement: return Response({"message":"Loan not disbursed"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) account_id = loan_application_disbursement.disbursement_account_id selected_account = loan_application_disbursement.system_transaction.credit_chart.id apply_charges = False customer = loan_application.customer if loan_application_disbursement.disburse_method == 'credit': if not (account_id and SavingAccount.objects.filter(id=account_id)): savings_account = SavingAccount.objects.filter(account_customer=customer, deleted=False, status='active').order_by('id').first() if loan_application.loan_group: savings_account = SavingAccount.objects.filter(account_customer=loan_application.loan_group, deleted=False, status='active').order_by('id').first() if savings_account: account_id = savings_account.id selected_account = savings_account.account_product.accounts_chart.id elif loan_application_disbursement.disburse_method == 'cash': if account_id and CashAccounts.objects.filter(id=account_id): cash_account = CashAccounts.objects.filter(id=account_id).first() account_id = cash_account.id selected_account = cash_account.chart.id else: cash_account = CashAccounts.objects.filter(teller=request.user, status="active").order_by('id').first() if cash_account: account_id = cash_account.id selected_account = cash_account.chart.id else: if account_id and BankAccounts.objects.filter(id=account_id): bank_account = BankAccounts.objects.filter(id=account_id).first() account_id = bank_account.id selected_account = bank_account.chart.id else: bank_account = BankAccounts.objects.filter(status='active', branch=loan_application.organisation_branch).order_by('id').first() if bank_account: account_id = bank_account.id selected_account = bank_account.chart.id if account_id == None: return Response({"message":"No disbursement account found"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) # validate generate loan schedule loan_repayment_schedules = generate_loan_schedules(request, loan_application.id) if not loan_repayment_schedules: return Response({"message":"Failed to generate loan schedule"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) customer = loan_application.customer loan_payment_transactions = LoanPaymentTransaction.objects.filter(loan_application=loan_application).values_list('id', flat=True) loan_main_payments = LoanMainTransactions.objects.filter(loan_application=loan_application).exclude(transaction_type__in = ['LoanCustomCharge','LoanDisbursement']) from django.utils import timezone now = timezone.now() user_id = request.user.id if request and hasattr(request, 'user') else None df = dict(deleted=True, deleted_by_id=user_id, deleted_at=now) if user_id and loan_application.organisation_branch: try: from users.audit_log_helper import add_system_audit_trail from users.models import User _u = User.objects.filter(pk=user_id).first() if _u: add_system_audit_trail('transaction_management', 'edit_loan_disbursement_delete_payments', f'Deleted loan payments for edit-disbursement: {loan_application.customer.name} Mem No: {loan_application.customer.member_number}', '', {}, {}, _u, loan_application.organisation_branch) except Exception: pass for loan_main_payment in loan_main_payments: if loan_main_payment.system_transaction: SavingAccountTransactions.objects.filter(transaction=loan_main_payment.system_transaction).update(**df) SystemTransactions.objects.filter(id=loan_main_payment.system_transaction.id).update(**df) LoanMainTransactions.objects.filter(id=loan_main_payment.id).update(**df) LoanPaymentTransaction.objects.filter(loan_application=loan_application, id__in=loan_payment_transactions).update(**df) LoanRepaymentSchedule.objects.filter(loan_application=loan_application, status='deleted').delete() LoanRepaymentSchedule.objects.filter(loan_application=loan_application).update(**{**df, 'status': 'deleted'}) if action == 'delete_loan_ded_trans': apply_charges = False charges = LoanMainTransactions.objects.filter(loan_application=loan_application, transaction_type='LoanCustomCharge') for charge in charges: if charge.system_transaction: SavingAccountTransactions.objects.filter(transaction=charge.system_transaction).update(**df) SystemTransactions.objects.filter(id=charge.system_transaction.id).update(**df) LoanMainTransactions.objects.filter(id=charge.id).update(**df) elif action == 'delete_post_trans': apply_charges = True charges = LoanMainTransactions.objects.filter(loan_application=loan_application, transaction_type='LoanCustomCharge') for charge in charges: if charge.system_transaction: SavingAccountTransactions.objects.filter(transaction=charge.system_transaction).update(**df) SystemTransactions.objects.filter(id=charge.system_transaction.id).update(**df) LoanMainTransactions.objects.filter(id=charge.id).update(**df) # update loan details old_loan_product = loan_application.loan_application_product.id loan_application_product = LoanProduct.objects.filter(id=loan_application_product).first() loan_application.loan_application_product = loan_application_product loan_application.loan_sector = LoanSectors.objects.filter(id=loan_sector).first() loan_application.loan_officer = Staff.objects.filter(id=loan_officer).first() loan_application.int_method = int_method loan_application.loan_amount = amount loan_application.save() # save loan approval loan_application_approval.loan_period = loan_period loan_application_approval.period_type = period_type loan_application_approval.frequency = frequency loan_application_approval.grace_period_type = grace_period_type loan_application_approval.int_rate = int_rate loan_application_approval.app_grace_period = app_grace_period loan_application_approval.loan_amount = amount loan_application_approval.save() # Update the diisbursement details interest, principal, total = loan_payment(request, loan_application.id) loan_application_disbursement.loan_start_date = loan_start_date loan_application_disbursement.loan_disbursement_date = loan_disbursement_date loan_application_disbursement.loan_amount = amount loan_application_disbursement.total_expected = round_off_amount(request, total) loan_application_disbursement.total_principal_expected = round_off_amount(request, principal) loan_application_disbursement.total_interest_expected = round_off_amount(request, interest) loan_application_disbursement.comment = comment loan_application_disbursement.disbursement_account_id = account_id loan_application_disbursement.save() # update the new payment expected amounts interest, principal, total = loan_payment(request, loan_application.id) loan_application_disbursement.total_expected = round_off_amount(request, total) loan_application_disbursement.total_principal_expected = round_off_amount(request, principal) loan_application_disbursement.total_interest_expected = round_off_amount(request, interest) loan_application_disbursement.save() # Update Payment transactions loan_main_payments = LoanMainTransactions.objects.filter(system_transaction=loan_application_disbursement.system_transaction,transaction_type = 'LoanDisbursement').first() loan_main_payments.amount = amount loan_main_payments.save() source_transation = None destination_transaction = None inter_branch_1 = InterBranchTransactions.objects.filter(source_transaction=loan_application_disbursement.system_transaction).first() inter_branch_2 = InterBranchTransactions.objects.filter(destination_transaction=loan_application_disbursement.system_transaction).first() if inter_branch_1: source_transation = inter_branch_1.source_transaction destination_transaction = inter_branch_1.destination_transaction if inter_branch_2: source_transation = inter_branch_2.source_transaction destination_transaction = inter_branch_2.destination_transaction if source_transation and destination_transaction: source_transation.amount = amount source_transation.record_date = loan_disbursement_date destination_transaction.amount = amount destination_transaction.record_date = loan_disbursement_date source_transation.save() source_transation.save() # update product disbursement incase product is updated source_debit_chart = source_transation.debit_chart.id destination_debit_chart = destination_transaction.debit_chart.id loan_prdt = LoanProduct.objects.filter(id=old_loan_product, chart=source_debit_chart).first() if loan_prdt: source_transation.debit_chart = loan_application_product.chart source_transation.save() else: loan_prdt = LoanProduct.objects.filter(id=old_loan_product, chart=destination_debit_chart).first() if loan_prdt: destination_transaction.debit_chart = loan_application_product.chart source_transation.save() else: system_transaction = loan_application_disbursement.system_transaction system_transaction.amount = amount system_transaction.record_date = loan_disbursement_date # update product disbursement incase product is updated system_transaction.debit_chart = loan_application_product.chart system_transaction.save() loan_repayment_schedules = generate_loan_schedules(request, loan_application.id) if not loan_repayment_schedules: return Response({"message":"Failed to generate loan schedule"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) # save loan schedule count = 1 for loan_repayment_schedule in loan_repayment_schedules: schedule_data = {'principal_expected':loan_repayment_schedule['principal_expected'], 'loan_application':loan_application, 'interest_expected':loan_repayment_schedule['interest_expected'], 'total_payment':loan_repayment_schedule['total_payment'], 'ending_balance':loan_repayment_schedule['ending_balance'], 'starting_balance':loan_repayment_schedule['starting_balance'], 'payment_number':count, 'expected_date': loan_repayment_schedule['expected_date'], 'loan_schedule_added_by':loan_application_disbursement.loan_disburse_added_by, "date_added":loan_application_disbursement.date_added } LoanRepaymentSchedule.objects.create(**schedule_data) count = count + 1 # post charges total_charges = 0 received_amount = amount credit_chart = OrganisationSubAccount.objects.get(pk=selected_account) if apply_charges: charges = LoanProductCharges.objects.filter(loan_product=loan_application.loan_application_product, is_deleted=False).all() for charge in charges: if int(charge.above_limit_amount) == 0 or float(loan_application_disbursement.loan_amount) >= float(charge.above_limit_amount): # post charges heading = charge.name charge_amount = float(charge.amount) if charge.apply_type == 'percent': charge_amount = round( ( float(charge.amount) / 100 ) * float(amount)) if charge.max_charge_cap and charge_amount > float(charge.max_charge_cap): charge_amount = float(charge.max_charge_cap) reference_no = generate_reference_no(credit_chart.account_line,loan_application.organisation_branch.branch_organisation.id, 'ln-int') charge_transaction = None # Check if the disbursement was previously inter-branch if source_transation and destination_transaction: inter_branch_chart = source_transation.credit_chart charge_transaction = SystemTransactions.objects.create( amount=charge_amount, heading=heading, reference_no=reference_no, payment_method=loan_application_disbursement.disburse_method, voucher_no=loan_application_disbursement.system_transaction.voucher_no, debit_chart_id=inter_branch_chart.id, credit_chart_id=charge.chart.id, branch_id=source_transation.branch.id, added_by=loan_application_disbursement.loan_disburse_added_by, date_added=loan_disbursement_date, record_date=loan_disbursement_date) if charge_transaction: charge_transaction2 = SystemTransactions.objects.create( amount=charge_amount, heading=heading, reference_no=reference_no, payment_method='settlement', voucher_no=loan_application_disbursement.system_transaction.voucher_no, debit_chart_id=credit_chart.id, credit_chart_id=inter_branch_chart.id, branch_id=destination_transaction.branch.id, added_by=loan_application_disbursement.loan_disburse_added_by, date_added=loan_disbursement_date, record_date=loan_disbursement_date) # Reconcile inter-branch transactions inter_branch_trans_field = { "source_transaction":charge_transaction, "destination_transaction":charge_transaction2, "added_by":charge_transaction.added_by, "date_added":charge_transaction.date_added } InterBranchTransactions.objects.create(**inter_branch_trans_field) charge_transaction = charge_transaction2 else: charge_transaction = SystemTransactions.objects.create( amount=charge_amount, heading=heading, reference_no=reference_no, payment_method=loan_application_disbursement.disburse_method, voucher_no=loan_application_disbursement.system_transaction.voucher_no, debit_chart_id=credit_chart.id, credit_chart_id=charge.chart.id, branch_id=loan_application_disbursement.system_transaction.branch.id, added_by=loan_application_disbursement.loan_disburse_added_by, date_added=loan_disbursement_date, record_date=loan_disbursement_date) if charge_transaction: total_charges += charge_amount data = { "heading":heading, "amount":charge_amount, "payment_method":loan_application_disbursement.disburse_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":loan_application_disbursement.system_transaction.voucher_no, "transaction_type":'LoanCustomCharge', "loan_main_transaction_added_by":loan_application_disbursement.loan_disburse_added_by, "payment_date":loan_disbursement_date, "system_transaction":charge_transaction, "date_added":loan_disbursement_date } LoanMainTransactions.objects.create(**data) if loan_application_disbursement.disburse_method == 'credit' and charge_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":charge_transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if loan_application.loan_group and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) response = {"client_name":customer.name, "loan_amount":loan_application.loan_amount, "total_charges":total_charges, "amount":str(float(received_amount) - float(total_charges))} return Response(response, status=status.HTTP_200_OK) def process_edit_loan_disbursement_old(request, loan_details): branch_id = get_current_user(request, 'organisation_branch_id', None) for item in ["amount", "loan_disbursement_date", "loan_start_date", "apply_charges", "loan_application_product", "loan_period", "period_type", "frequency", "grace_period_type", "loan_sector", "loan_officer", "int_rate", "app_grace_period", "int_method", "action"]: if item not in loan_details: return Response({"message":"Failed to edit disbursement"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) amount = float(loan_details['amount']) loan_disbursement_date = loan_details['loan_disbursement_date'] loan_start_date = loan_details['loan_start_date'] loan_application_product = loan_details['loan_application_product'] loan_period = loan_details['loan_period'] period_type = loan_details['period_type'] frequency = loan_details['frequency'] grace_period_type = loan_details['grace_period_type'] loan_sector = loan_details['loan_sector'] loan_officer = loan_details['loan_officer'] int_rate = loan_details['int_rate'] app_grace_period = loan_details['app_grace_period'] int_method = loan_details['int_method'] loan_application_id = loan_details['loan_application_id'] action = loan_details['action'] comment = loan_details['comment'] loan_application = LoanApplication.objects.get(pk=loan_application_id) if not loan_application: return Response({"message":"No loan application found"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) loan_application_disbursement = LoanApplicationDisbursement.objects.filter(loan_application=loan_application).first() if not loan_application_disbursement: return Response({"message":"Loan not disbursed"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) customer = loan_application.customer original_disburse_method = loan_application_disbursement.disburse_method original_loan_disburse_added_by = loan_application_disbursement.loan_disburse_added_by original_ref_no = loan_application_disbursement.ref_no original_voucher_no = loan_application_disbursement.voucher_no original_date_added = loan_application_disbursement.date_added original_arrear_grace_period= loan_application_disbursement.arrear_grace_period original_write_off_grace_period = loan_application_disbursement.write_off_grace_period original_arrears_period_type = loan_application_disbursement.arrears_period_type original_penalty_type = loan_application_disbursement.penalty_type original_penalty_period_type = loan_application_disbursement.penalty_period_type original_penalty_rate = loan_application_disbursement.penalty_rate original_write_off_period_type = loan_application_disbursement.write_off_period_type original_disbursement_account_id = loan_application_disbursement.disbursement_account_id loan_payment_transactions = LoanPaymentTransaction.objects.filter(loan_application=loan_application).values_list('id', flat=True) loan_main_payments = LoanMainTransactions.objects.filter(loan_application=loan_application).exclude(transaction_type='LoanCustomCharge') from django.utils import timezone now = timezone.now() user_id = request.user.id if request and hasattr(request, 'user') else None df = dict(deleted=True, deleted_by_id=user_id, deleted_at=now) if user_id and loan_application.organisation_branch: try: from users.audit_log_helper import add_system_audit_trail from users.models import User _u = User.objects.filter(pk=user_id).first() if _u: add_system_audit_trail('transaction_management', 'edit_loan_disbursement_delete_payments', f'Deleted loan payments for edit-disbursement (old): {loan_application.customer.name} Mem No: {loan_application.customer.member_number}', '', {}, {}, _u, loan_application.organisation_branch) except Exception: pass for loan_main_payment in loan_main_payments: if loan_main_payment.system_transaction: SystemTransactions.objects.filter(id=loan_main_payment.system_transaction.id).update(**df) LoanMainTransactions.objects.filter(id=loan_main_payment.id).update(**df) LoanPaymentTransaction.objects.filter(loan_application=loan_application, id__in=loan_payment_transactions).update(**df) LoanRepaymentSchedule.objects.filter(loan_application=loan_application).update(**df) LoanApplicationDisbursement.objects.filter(loan_application=loan_application).update(**df) loan_application.status = "approved" loan_application.save() disbursement_details = {"account_id":None, "apply_charges":True} if original_disburse_method == 'credit': if original_disbursement_account_id and SavingAccount.objects.filter(id=original_disbursement_account_id): savings_account = SavingAccount.objects.filter(id=original_disbursement_account_id).first() disbursement_details['account_id'] = savings_account.id disbursement_details['selected_account'] = savings_account.account_product.accounts_chart.id else: savings_account = SavingAccount.objects.filter(account_customer=customer, deleted=False, status='active').order_by('id').first() if loan_application.loan_group: savings_account = SavingAccount.objects.filter(account_customer=loan_application.loan_group, deleted=False, status='active').order_by('id').first() if savings_account: disbursement_details['account_id'] = savings_account.id disbursement_details['selected_account'] = savings_account.account_product.accounts_chart.id elif original_disburse_method == 'cash': if original_disbursement_account_id and CashAccounts.objects.filter(id=original_disbursement_account_id): cash_account = CashAccounts.objects.filter(id=original_disbursement_account_id).first() disbursement_details['account_id'] = cash_account.id disbursement_details['selected_account'] = cash_account.chart.id else: cash_account = CashAccounts.objects.filter(teller=request.user, status="active").order_by('id').first() if cash_account: disbursement_details['account_id'] = cash_account.id disbursement_details['selected_account'] = cash_account.chart.id else: if original_disbursement_account_id and BankAccounts.objects.filter(id=original_disbursement_account_id): bank_account = BankAccounts.objects.filter(id=original_disbursement_account_id).first() disbursement_details['account_id'] = bank_account.id disbursement_details['selected_account'] = bank_account.chart.id else: bank_account = BankAccounts.objects.filter(status='active', branch__id=branch_id).order_by('id').first() if bank_account: disbursement_details['account_id'] = bank_account.id disbursement_details['selected_account'] = bank_account.chart.id if disbursement_details['account_id'] == None: return Response({"message":"No disbursement account found"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) if action == 'do_nothing': disbursement_details['apply_charges'] = False elif action == 'delete_loan_ded_trans': disbursement_details['apply_charges'] = False charges = LoanMainTransactions.objects.filter(loan_application=loan_application, transaction_type='LoanCustomCharge') for charge in charges: if charge.system_transaction: SystemTransactions.objects.filter(id=charge.system_transaction.id).update(**df) LoanMainTransactions.objects.filter(id=charge.id).update(**df) elif action == 'delete_post_trans': disbursement_details['apply_charges'] = True charges = LoanMainTransactions.objects.filter(loan_application=loan_application, transaction_type='LoanCustomCharge') for charge in charges: if charge.system_transaction: SystemTransactions.objects.filter(id=charge.system_transaction.id).update(**df) LoanMainTransactions.objects.filter(id=charge.id).update(**df) # update the loan details disbursement_details['amount'] = amount disbursement_details['disburse_method'] = original_disburse_method disbursement_details['send_sms'] = False disbursement_details['voucher_no'] = original_voucher_no disbursement_details['original_ref_no'] = original_ref_no disbursement_details['loan_disbursement_date'] = loan_disbursement_date disbursement_details['loan_start_date'] = loan_start_date disbursement_details['customer_id'] = customer.id disbursement_details['loan_application_id'] = loan_application_id disbursement_extra_data = {"loan_application_product":loan_application_product, "amount":amount, "loan_period":loan_period, "period_type":period_type, "frequency":frequency, "grace_period_type":grace_period_type, "loan_sector":loan_sector, "loan_officer":loan_officer, "int_rate":int_rate, "app_grace_period":app_grace_period, "int_method":int_method, "date_added":original_date_added, "arrear_grace_period":original_arrear_grace_period, "write_off_grace_period":original_write_off_grace_period, "arrears_period_type":original_arrears_period_type, "penalty_type":original_penalty_type, "penalty_period_type":original_penalty_period_type, "penalty_rate":original_penalty_rate, "write_off_period_type":original_write_off_period_type, "loan_disburse_added_by":original_loan_disburse_added_by, "comment":comment} return process_loan_disbursement(request, disbursement_details, disbursement_extra_data) def process_loan_disbursement(request, loan_details, disbursement_extra_data=None): organisation_id = get_current_user(request, 'organisation_id', None) branch_id = get_current_user(request, 'organisation_branch_id', None) # print('++++++++++++++++++++++++++++++ process_loan_disbursement ') for item in ['amount', 'disburse_method', 'selected_account', 'send_sms', 'voucher_no', 'loan_disbursement_date', 'loan_start_date', 'customer_id', 'loan_application_id']: if item not in loan_details: return Response({"message":"Failed to disburse loan"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) amount = loan_details['amount'] disburse_method = loan_details['disburse_method'] selected_account = loan_details['selected_account'] send_sms = loan_details['send_sms'] voucher_no = loan_details['voucher_no'] loan_disbursement_date = loan_details['loan_disbursement_date'] loan_start_date = loan_details['loan_start_date'] customer_id = loan_details['customer_id'] loan_application_id = loan_details['loan_application_id'] account_id = loan_details['account_id'] apply_charges = loan_details['apply_charges'] # print('++++++++++++++++++++++++++++++ send sms') # print(send_sms) added_by = request.user date_added = timezone.now() if disbursement_extra_data: if disbursement_extra_data['loan_disburse_added_by']: added_by = disbursement_extra_data['loan_disburse_added_by'] if disbursement_extra_data['date_added']: date_added = disbursement_extra_data['date_added'] # end of validation transaction = None transaction_2 = None savings_account = None bank_account = None loan_application = LoanApplication.objects.get(pk=loan_application_id) organisation_branch = OrganisationBranch.objects.get(pk=branch_id) if disbursement_extra_data: # update loan details loan_application_product = LoanProduct.objects.filter(id= disbursement_extra_data['loan_application_product']).first() loan_application.loan_application_product = loan_application_product loan_application.loan_sector = LoanSectors.objects.filter(id=disbursement_extra_data['loan_sector']).first() loan_application.loan_officer = Staff.objects.filter(id=disbursement_extra_data['loan_officer']).first() loan_application.int_method = disbursement_extra_data['int_method'] loan_application.loan_amount = disbursement_extra_data['amount'] loan_application.save() loan_account = loan_application.loan_application_product.chart.id interbranch_chart = get_inter_branch_chart(OrganisationBranch.objects.get(pk=branch_id), loan_application.organisation_branch) if disburse_method == 'credit': savings_account = SavingAccount.objects.filter(id=account_id).first() if not savings_account: return Response({"message":"No saving account found"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) interbranch_chart = get_inter_branch_chart(savings_account.customer_branch, loan_application.organisation_branch) elif disburse_method == 'bank': bank_account = BankAccounts.objects.filter(id=account_id).first() if not bank_account: return Response({"message":"No bank account found"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) interbranch_chart = get_inter_branch_chart(bank_account.branch, loan_application.organisation_branch) credit_chart = OrganisationSubAccount.objects.get(pk=selected_account) if not credit_chart: return Response({"message":"No chart of account found"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) if not loan_application: return Response({"message":"No loan application found"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) loan_application_approval = LoanApplicationApproval.objects.filter(loan_application=loan_application).first() if not loan_application_approval: return Response({"message":"Loan not approved"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) customer = Customer.objects.get(pk=customer_id) if not customer: return Response({"message":"No customer found"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) heading = 'Loan Disbursement: ('+ customer.member_number +') to ' + customer.name if loan_application.loan_group: heading = 'Group Loan Disbursement: ' + loan_application.loan_group.name+'('+ loan_application.loan_group.member_number +') to ' + customer.name+'('+ customer.member_number +')' if disburse_method == 'cash' or disburse_method == 'bank': # check balance account_balance = get_chart_of_account_balance_at(credit_chart.id, branch_id)['balance_raw'] if float(account_balance) < float(amount): return Response({"message":"Insufficient balance on account"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) # validate generate loan schedule loan_repayment_schedules = generate_loan_schedules(request, loan_application.id) if not loan_repayment_schedules: return Response({"message":"Failed to generate loan schedule"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) # Generate reference number reference_no = generate_reference_no(credit_chart.account_line, organisation_id, 'ln-d') # Handle inter-branch transactions update -> soure branch if (disburse_method == 'cash' and branch_id != loan_application.organisation_branch.id ) or (disburse_method == 'credit' and savings_account.customer_branch.id != loan_application.organisation_branch.id ) or (disburse_method == 'bank' and bank_account.branch.id != loan_application.organisation_branch.id ): destination_branch = branch_id if disburse_method == 'credit': destination_branch = savings_account.customer_branch.id heading = 'Inter-branch Loan Disbursement: ('+ customer.member_number +') to ' + customer.name if loan_application.loan_group: heading = 'Inter-branch Group Loan Disbursement: ' + loan_application.loan_group.name+'('+ loan_application.loan_group.member_number +') to ' + customer.name+'('+ customer.member_number +')' transaction = SystemTransactions.objects.create(amount=amount, heading=heading, reference_no=reference_no, payment_method=disburse_method,voucher_no=voucher_no, debit_chart_id=loan_account, credit_chart_id=interbranch_chart.id, branch_id=loan_application.organisation_branch.id, added_by=added_by, date_added=date_added, record_date=loan_disbursement_date) if not transaction: return Response({"message":"Failed to disburse loan"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) # Handle inter-branch transactions update -> destination branch transaction_2 = SystemTransactions.objects.create(amount=amount, heading=heading, reference_no=reference_no,payment_method='settlement',voucher_no=voucher_no, debit_chart_id=interbranch_chart.id, credit_chart_id=credit_chart.id, branch_id=destination_branch, added_by=added_by, date_added=date_added, record_date=loan_disbursement_date) if not transaction_2: return Response({"message":"Failed to disburse loan"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) # Reconcile inter-branch transactions inter_branch_trans_field = { "source_transaction":transaction, "destination_transaction":transaction_2, "added_by":added_by, "date_added":date_added } InterBranchTransactions.objects.create(**inter_branch_trans_field) else: transaction = SystemTransactions.objects.create(amount=amount, heading=heading, reference_no=reference_no, payment_method=disburse_method,voucher_no=voucher_no, debit_chart_id=loan_account, credit_chart_id=credit_chart.id, branch_id=branch_id, added_by=added_by, date_added=date_added, record_date=loan_disbursement_date) if not transaction: return Response({"message":"Failed to disburse loan"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) # save disbursement if disbursement_extra_data: loan_application_approval.loan_period = disbursement_extra_data['loan_period'] loan_application_approval.period_type = disbursement_extra_data['period_type'] loan_application_approval.frequency = disbursement_extra_data['frequency'] loan_application_approval.grace_period_type = disbursement_extra_data['grace_period_type'] loan_application_approval.int_rate = disbursement_extra_data['int_rate'] loan_application_approval.app_grace_period = disbursement_extra_data['app_grace_period'] loan_application_approval.loan_amount = disbursement_extra_data['amount'] loan_application_approval.save() interest, principal, total = loan_payment(request, loan_application.id) data = {"loan_amount":amount, "loan_application":loan_application, "loan_start_date":loan_start_date, "loan_disburse_added_by":added_by, "disburse_method":disburse_method, "loan_disbursement_date":loan_disbursement_date, "ref_no":reference_no, "voucher_no":voucher_no, "total_interest_expected":round_off_amount(request, interest), "total_principal_expected":round_off_amount(request, principal), "total_expected":round_off_amount(request, total), "heading":heading,"system_transaction":transaction, "date_added":date_added,"disbursement_account_id":account_id, "write_off_grace_period": loan_application.loan_application_product.write_off_period, "write_off_period_type":loan_application.loan_application_product.write_off_period_type, "arrear_grace_period":loan_application.loan_application_product.arrears_period, "arrears_period_type":loan_application.loan_application_product.arrears_period_type, "penalty_rate":loan_application.loan_application_product.penalty_rate, "penalty_period_type":loan_application.loan_application_product.penalty_period_type, "penalty_type":loan_application.loan_application_product.penalty_type, "penalty_grace_period":loan_application.loan_application_product.penalty_grace_period, "penalty_grace_period_type":loan_application.loan_application_product.penalty_grace_period_type } if transaction_2: data["system_transaction"] = transaction_2 loan_disbursement = LoanApplicationDisbursement.objects.create(**data) # print('>>>>>>>>>>>>>>>>>>>>>>> loan_disbursement') if loan_disbursement: # print('<<<<<<<<<<<<<<<<<<<<<<<<<<< after loan_disbursement') # update loan status response = {} loan_application.status = 'disbursed' loan_application.save() if disbursement_extra_data: loan_disbursement.date_added = disbursement_extra_data['date_added'] loan_disbursement.arrear_grace_period = disbursement_extra_data['arrear_grace_period'] loan_disbursement.write_off_grace_period = disbursement_extra_data['write_off_grace_period'] loan_disbursement.arrears_period_type = disbursement_extra_data['arrears_period_type'] loan_disbursement.penalty_type = disbursement_extra_data['penalty_type'] loan_disbursement.penalty_period_type = disbursement_extra_data['penalty_period_type'] loan_disbursement.write_off_period_type = disbursement_extra_data['write_off_period_type'] loan_disbursement.penalty_rate = disbursement_extra_data['penalty_rate'] loan_disbursement.penalty_grace_period = disbursement_extra_data.get('penalty_grace_period', 0) loan_disbursement.penalty_grace_period_type = disbursement_extra_data.get('penalty_grace_period_type', 'd') loan_disbursement.loan_disburse_added_by = disbursement_extra_data['loan_disburse_added_by'] loan_disbursement.comment = disbursement_extra_data['comment'] loan_disbursement.save() # generate loan schedule loan_repayment_schedules = generate_loan_schedules(request, loan_application.id) if not loan_repayment_schedules: return Response({"message":"Failed to generate loan schedule"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) # print('<<<<<<<<<<<<<<<<<<<<<<<<<<< 1. ') # save loan schedule count = 1 for loan_repayment_schedule in loan_repayment_schedules: schedule_data = {'principal_expected':loan_repayment_schedule['principal_expected'], 'loan_application':loan_application, 'interest_expected':loan_repayment_schedule['interest_expected'], 'total_payment':loan_repayment_schedule['total_payment'], 'ending_balance':loan_repayment_schedule['ending_balance'], 'starting_balance':loan_repayment_schedule['starting_balance'], 'payment_number':count, 'expected_date': loan_repayment_schedule['expected_date'], 'loan_schedule_added_by':added_by, "date_added":date_added } LoanRepaymentSchedule.objects.create(**schedule_data) count = count + 1 data = { "heading":heading, "amount":amount, "payment_method":disburse_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":voucher_no, "transaction_type":'LoanDisbursement', "loan_main_transaction_added_by":added_by, "payment_date":loan_disbursement_date, "system_transaction":transaction, "date_added":date_added} if transaction_2: transaction = transaction_2 data["system_transaction"] = transaction_2 LoanMainTransactions.objects.create(**data) # print('<<<<<<<<<<<<<<<<<<<<<<<<<<< 2. ') if disburse_method == 'credit': if transaction: saved_transaction_fields = { "transaction_type":'deposit', "customer_account_id":account_id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) print("++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Testing +++++++++++") print(loan_application.loan_group) # print(json.dumps(group_trans_field, default=str, indent=2)) if loan_application.loan_group: membership = GroupMembership.objects.filter(member=customer,group=loan_application.loan_group, active=True).first() print("++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Membership +++++++++++") # print(membership) print(json.dumps(membership, default=str, indent=2)) if membership: group_trans_field = { "membership": membership, "savings": saved_trans } print("++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ group_trans_field +++++++++++") # print(group_trans_field) print(json.dumps(group_trans_field, default=str, indent=2)) print("++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ saved_trans +++++++++++") # print(group_trans_field) print(json.dumps(saved_trans, default=str, indent=2)) # GroupSavingTransaction.objects.create(**group_trans_field) try: x = GroupSavingTransaction.objects.create(**group_trans_field) print("Transaction created successfully:", x) except Exception as e: print("Failed to create transaction:", e) # print('<<<<<<<<<<<<<<<<<<<<<<<<<<< 3. ') # post charges total_charges = 0 received_amount = amount # print('----------------------------- apply_charges') # print(apply_charges) if apply_charges: charges = LoanProductCharges.objects.filter(loan_product=loan_application.loan_application_product, is_deleted=False).all() for charge in charges: is_apply_charge = False if int(charge.below_limit_amount) > 0 and loan_disbursement.loan_amount <= charge.below_limit_amount: is_apply_charge = True elif int(charge.range_min_amount) > 0 and int(charge.range_max_amount) > 0 and loan_disbursement.loan_amount >= charge.range_min_amount and loan_disbursement.loan_amount <= charge.range_max_amount: is_apply_charge = True elif int(charge.above_limit_amount) > 0 and loan_disbursement.loan_amount >= charge.above_limit_amount: is_apply_charge = True elif int(charge.below_limit_amount) == 0 and int(charge.range_min_amount) == 0 and int(charge.range_max_amount) == 0 and int(charge.above_limit_amount) == 0: is_apply_charge = True if is_apply_charge: # post charges heading = charge.name charge_amount = float(charge.amount) if charge.apply_type == 'percent': charge_amount = round( ( float(charge.amount) / 100 ) * float(amount)) if charge.max_charge_cap and charge_amount > float(charge.max_charge_cap): charge_amount = float(charge.max_charge_cap) reference_no = generate_reference_no(credit_chart.account_line, organisation_id, 'ln-int') # transaction = None # Handle inter-branch transactions update -> soure branch # if (disburse_method == 'cash' and branch_id != loan_application.organisation_branch.id ) or (disburse_method == 'credit' and savings_account.customer_branch.id != loan_application.organisation_branch.id ) or (disburse_method == 'bank' and bank_account.branch.id != loan_application.organisation_branch.id ): # source_branch = branch_id # if disburse_method == 'credit': # source_branch = savings_account.customer_branch.id # transaction = SystemTransactions.objects.create(amount=charge_amount, heading=heading, reference_no=reference_no, payment_method='offset',voucher_no=voucher_no, debit_chart_id=interbranch_chart.id, credit_chart_id=charge.chart.id, branch_id=source_branch, added_by=added_by, date_added=date_added, record_date=loan_disbursement_date) # if transaction: # transaction_2 = SystemTransactions.objects.create(amount=charge_amount, heading=heading, reference_no=reference_no, payment_method='settlement',voucher_no=voucher_no, debit_chart_id=credit_chart.id, credit_chart_id=interbranch_chart.id, branch_id=loan_application.organisation_branch.id , added_by=added_by, date_added=date_added, record_date=loan_disbursement_date) # # Reconcile inter-branch transactions # inter_branch_trans_field = { # "source_transaction":transaction, # "destination_transaction":transaction_2, # "added_by":added_by, # "date_added":date_added # } # InterBranchTransactions.objects.create(**inter_branch_trans_field) # transaction = transaction_2 # else: transaction = SystemTransactions.objects.create(amount=charge_amount, heading=heading, reference_no=reference_no, payment_method=disburse_method,voucher_no=voucher_no, debit_chart_id=credit_chart.id, credit_chart_id=charge.chart.id, branch_id=branch_id, added_by=added_by, date_added=date_added, record_date=loan_disbursement_date) if transaction: total_charges += charge_amount data = { "heading":heading, "amount":charge_amount, "payment_method":disburse_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":voucher_no, "transaction_type":'LoanCustomCharge', "loan_main_transaction_added_by":added_by, "payment_date":loan_disbursement_date, "system_transaction":transaction, "date_added":date_added } LoanMainTransactions.objects.create(**data) if disburse_method == 'credit' and transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if loan_application.loan_group and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field) # print('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX send_sms') # print(send_sms) if send_sms: if disburse_method == 'credit': # print('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX disburse_method') # print(disburse_method) f_amount = f"{float(loan_disbursement.loan_amount):,}" pdt_name = loan_disbursement.loan_application.loan_application_product.product_name sms_msg = 'Dear '+loan_disbursement.loan_application.customer.name.capitalize() +', Credit of ('+pdt_name + ' loan) disbursement Amount UGX: '+ f_amount+'\n'+ (organisation_branch.branch_organisation.short_name if organisation_branch.branch_organisation.short_name else '') data = {"sms_key":"loan_disbursement_sms","customer":loan_disbursement.loan_application.customer,"user":request.user,"branch_id":branch_id,"sms_msg":sms_msg,"loan":loan_disbursement.loan_application} # print("..................................................................") # print(sms_msg) send_customer_sms(data) else: f_amount = f"{float(loan_disbursement.loan_amount):,}" pdt_name = loan_disbursement.loan_application.loan_application_product.product_name sms_msg = 'Dear '+loan_disbursement.loan_application.customer.name.capitalize() +', Your ('+pdt_name + ' loan) Amount UGX: '+ f_amount+' has been Disbursed\n'+ (organisation_branch.branch_organisation.short_name if organisation_branch.branch_organisation.short_name else '') data = {"sms_key":"loan_disbursement_sms","customer":loan_disbursement.loan_application.customer,"user":request.user,"branch_id":branch_id,"sms_msg":sms_msg,"loan":loan_disbursement.loan_application} send_customer_sms(data) response = {"client_name":customer.name, "loan_amount":amount, "total_charges":total_charges, "amount":received_amount - total_charges} return Response(response, status=status.HTTP_200_OK) def get_write_off_account(request,loan_application_id): user_id = request.user.id write_off_chart = None organisation_id = get_current_user(request,'organisation_id', 1) loan_application = LoanApplication.objects.get(pk=loan_application_id) if loan_application: loan_product = loan_application.loan_application_product if loan_product.write_off_chart is not None: expense_account = OrganisationSubAccount.objects.filter(id =loan_product.write_off_chart.id) if expense_account: write_off_chart = loan_product.write_off_chart return write_off_chart if write_off_chart is None: #Generate write off chart of account for this product account_organisation = Organisation.objects.get(pk=organisation_id) parent_account = get_chart_of_account_by_code('sys-516',account_organisation) write_off_code = generate_chart_of_account_code(parent_account.id,'expenses',organisation_id) # Registering loss account due to write off for every loan product. write_off_chart_obj = OrganisationSubAccount( account_organisation=account_organisation,status='active', account_code=write_off_code, account_name = loan_product.product_name+" loss from loan write off ", account_type='user_defined', account_line='expenses', added_by=user_id,parent_id=parent_account) write_off_chart_obj.save() write_off_chart = get_chart_of_account_by_code(write_off_code,account_organisation) if write_off_chart is not None: #Update write off chart on the loan product. updated_product = LoanProduct.objects.get(pk=loan_product.id) updated_product.write_off_chart = write_off_chart updated_product.save() return write_off_chart return write_off_chart def get_penalty_waiver_account(request,loan_application_id): user_id = request.user.id expenses_from_penality_account = None organisation_id = get_current_user(request,'organisation_id', 1) loan_application = LoanApplication.objects.get(pk=loan_application_id) if loan_application: loan_product = loan_application.loan_application_product if loan_product.expenses_from_penality_account is not None: expense_account = OrganisationSubAccount.objects.filter(id =loan_product.expenses_from_penality_account.id) if expense_account: expenses_from_penality_account = loan_product.expenses_from_penality_account return expenses_from_penality_account if expenses_from_penality_account is None: #Generate loan penalty waiver chart of account for this product account_organisation = Organisation.objects.get(pk=organisation_id) parent_account = get_chart_of_account_by_code('sys-512',account_organisation) penalty_waiver_code = generate_chart_of_account_code(parent_account.id,'expenses',organisation_id) # Registering loss account due to penalty waiver for every loan product. expenses_from_penality_account_chart_obj = OrganisationSubAccount( account_organisation=account_organisation,status='active', account_code=penalty_waiver_code, account_name = loan_product.product_name+" loss from loan penalty waiver. ", account_type='user_defined', account_line='expenses', added_by=user_id,parent_id=parent_account) expenses_from_penality_account_chart_obj.save() expenses_from_penality_account = get_chart_of_account_by_code(penalty_waiver_code,account_organisation) if expenses_from_penality_account is not None: #Update write off chart on the loan product. updated_product = LoanProduct.objects.get(pk=loan_product.id) updated_product.expenses_from_penality_account = expenses_from_penality_account updated_product.save() return expenses_from_penality_account return expenses_from_penality_account def get_interest_waiver_account(request,loan_application_id): user_id = request.user.id expenses_from_interest_account = None organisation_id = get_current_user(request,'organisation_id', 1) loan_application = LoanApplication.objects.get(pk=loan_application_id) if loan_application: loan_product = loan_application.loan_application_product if loan_product.expenses_from_interest_account is not None: expense_account = OrganisationSubAccount.objects.filter(id =loan_product.expenses_from_interest_account.id) if expense_account: expenses_from_interest_account = loan_product.expenses_from_interest_account return expenses_from_interest_account if expenses_from_interest_account is None: #Generate loan interest waiver chart of account for this product account_organisation = Organisation.objects.get(pk=organisation_id) parent_account = get_chart_of_account_by_code('sys-512',account_organisation) interest_waiver_code = generate_chart_of_account_code(parent_account.id,'expenses',organisation_id) # Registering loss account due to interest waiver for every loan product. expenses_from_interest_account_obj = OrganisationSubAccount( account_organisation=account_organisation,status='active', account_code=interest_waiver_code, account_name = loan_product.product_name+" loss from loan interest waiver.", account_type='user_defined', account_line='expenses', added_by=user_id,parent_id=parent_account) expenses_from_interest_account_obj.save() expenses_from_interest_account = get_chart_of_account_by_code(interest_waiver_code,account_organisation) if expenses_from_interest_account is not None: #Update write off chart on the loan product. updated_product = LoanProduct.objects.get(pk=loan_product.id) updated_product.expenses_from_interest_account = expenses_from_interest_account updated_product.save() return expenses_from_interest_account return expenses_from_interest_account def computeArrearDays(expected_date,selected_date,extra_days = 0): expected_date += timedelta(days=extra_days*10) return(selected_date - expected_date).days def get_loan_provistions(user_id,organisation_id): provisions_list = [] provisions = LoanLossProvision.objects.filter(organisation__id=organisation_id).order_by("from_value") if provisions: for provision in provisions: provisions_list.append({"key":str(provision.from_value) + '_' + str(provision.to_value), "from_value":provision.from_value,"to_value":provision.to_value,"percentage":provision.percentage}) else: provisions_list = system_define_loan_loss_provisions save_provistions(user_id,organisation_id) return provisions_list def save_provistions(user_id,organisation_id): organisation = Organisation.objects.get(pk = organisation_id) for prov in system_define_loan_loss_provisions: field = { "from_value":prov["from_value"], "to_value":prov["to_value"], "percentage":prov["percentage"], "added_by":user_id, "last_updated_by":user_id, "organisation":organisation } LoanLossProvision.objects.create(**field) def release_with_held_shares_savings(loan): if loan and loan.status == 'cleared_off': loan_application = LoanApplication.objects.filter(id=loan.id).first() loan_application.clear_off_date = timezone.now() loan_application.save() LoanApplicationWithHold.objects.filter(loan_application=loan, status='held').update(status="released") def cancel_loan_approval(loan, user_id=None, branch=None): from django.utils import timezone from users.audit_log_helper import add_system_audit_trail now = timezone.now() df = dict(deleted=True, deleted_by_id=None, deleted_at=now) if loan and loan.status == 'pending': if not branch: branch = loan.organisation_branch if user_id and branch: try: from users.models import User user = User.objects.filter(pk=user_id).first() if user: add_system_audit_trail( 'transaction_management', 'delete_loan_approval', f'Cancelled Loan Approval for {loan.customer.name} Mem No: {loan.customer.member_number}', '', {}, {}, user, branch ) except Exception: pass loan_application = LoanApplication.objects.filter(id=loan.id).first() LoanApplicationApproval.objects.filter(loan_application=loan_application).update(**df) loan_disbursement = LoanApplicationDisbursement.objects.filter(loan_application=loan_application).first() if loan_disbursement: if loan_disbursement.system_transaction: SystemTransactions.objects.filter(id=loan_disbursement.system_transaction.id).update(**df) LoanApplicationDisbursement.objects.filter(loan_application=loan_application).update(**df) LoanRepaymentSchedule.objects.filter(loan_application=loan_application).update(**df) RescheduledLoans.objects.filter(loan_application=loan_application).update(**df) def filter_loan_balances(loan_filter,extraFilters): search = None payment_filter = {} penalty_filter = {} pen_wav_filter = {} int_wav_filter = {} additional_filters = Q() data_keys = extraFilters.keys() if 'start' in data_keys: payment_filter["loan_main_transaction__system_transaction__record_date__gte"] = extraFilters["start"] penalty_filter["expected_pay_date__gte"] = extraFilters["start"] pen_wav_filter["date_added__gte"] = extraFilters["start"] penalty_filter["date_added__gte"] = extraFilters["start"] if 'end' in data_keys: payment_filter["loan_main_transaction__system_transaction__record_date__lte"] = extraFilters["end"] penalty_filter["expected_pay_date__lte"] = extraFilters["end"] pen_wav_filter["date_added__lte"] = extraFilters["end"] int_wav_filter["date_added__lte"] = extraFilters["end"] if 'search' in data_keys: search = extraFilters["search"] if 'report_type' in data_keys: if extraFilters["report_type"] in ["expected_repayment","due_vs_repayment"]: additional_filters.add(Q(**{"principal_due__gt": 0}), Q.OR) additional_filters.add(Q(**{"interest_due__gt": 0}), Q.OR) additional_filters.add(Q(**{"penalty_balance__gt": 0}), Q.OR) if extraFilters["report_type"] in ["arrears","par","ageing"]: additional_filters.add(Q(**{"penalty_balance__gt": 0}), Q.AND) if extraFilters["report_type"] in ["to_be_cleared"]: additional_filters.add(Q(**{"principal_due__lte": 0}), Q.AND) additional_filters.add(Q(**{"interest_due__lte": 0}), Q.AND) additional_filters.add(Q(**{"penalty_balance__lte": 0}), Q.AND) schedules_expected_pay = LoanRepaymentSchedule.objects.filter(loan_application__id=OuterRef('id')).values('loan_application__id').annotate(loan_princ=Sum('principal_expected'),loan_interest=Sum('interest_expected')).values('loan_princ',"loan_interest") repayments = LoanPayments.objects.filter(loan_application__id=OuterRef('id'), payment_status='normal',**payment_filter).values('loan_application__id').annotate(princ_paid_sum=Sum("princ_paid"),int_paid_sum=Sum('int_paid'),penalty_paid_sum=Sum('penalty_paid')).values("princ_paid_sum",'int_paid_sum','penalty_paid_sum') loan_princ = schedules_expected_pay.values("loan_princ") loan_interest = schedules_expected_pay.values("loan_interest") princ_paid = repayments.values("princ_paid_sum") int_paid = repayments.values("int_paid_sum") pen_paid = repayments.values("penalty_paid_sum") penalty = LoanPenalty.objects.filter(loan_application__id=OuterRef('id'),**penalty_filter).values('loan_application__id').annotate(penalty=Sum('amount')).values('penalty') pen_wav = LoanPenaltyWaivered.objects.filter(loan_application__id=OuterRef('id'),**pen_wav_filter).values('loan_application__id').annotate(penalty_wav=Sum('amount')).values('penalty_wav') int_wav = LoanInterestWaivered.objects.filter(loan_application__id=OuterRef('id'),**int_wav_filter).values('loan_application__id').annotate(int_wav=Sum('amount')).values('int_wav') int_pay = Cast(Coalesce(int_paid,Value(0.0),output_field=models.FloatField()), output_field=FloatField()) + Cast(Coalesce(int_wav,Value(0.0),output_field=models.FloatField()), output_field=FloatField()) pen_pay = Cast(Coalesce(pen_paid,Value(0.0),output_field=models.FloatField()), output_field=FloatField()) + Cast(Coalesce(pen_wav,Value(0.0),output_field=models.FloatField()), output_field=FloatField()) pay_amount = Cast(Coalesce(princ_paid,Value(0.0),output_field=models.FloatField()), output_field=FloatField()) + Cast(Coalesce(int_paid,Value(0.0),output_field=models.FloatField()),output_field=FloatField()) + Cast(Coalesce(pen_paid,Value(0.0),output_field=models.FloatField()),output_field=FloatField()) if search and len(search) > 0: loans = LoanRepaymentview.objects.values("id","member_number","name","telephone","loan_start_date","loan_end_date","status","loan_amount","loan_write_off_date").filter(Q(member_number__icontains=search) | Q(old_member_number__icontains=search) | Q(name__icontains=search),**loan_filter).annotate( loan_princ = Coalesce(loan_princ,Value(0.0),output_field=models.FloatField()), loan_interest = Coalesce(loan_interest,Value(0.0),output_field=models.FloatField()), principal_expected = Sum('schedule_principal_expected'), interest_expected = Sum('schedule_interest_expected'), first_arrear_date=Min('loan_arrear_date'), schedule_expected_date=Min('schedule_expected_date'), princ_paid = Coalesce(princ_paid,Value(0.0),output_field=models.FloatField()), int_paid = Coalesce(int_paid,Value(0.0),output_field=models.FloatField()), pen_paid = Coalesce(pen_paid,Value(0.0),output_field=models.FloatField()), paid_amount = pay_amount, penalty = Coalesce(penalty,Value(0.0),output_field=models.FloatField()), pen_wav = Coalesce(pen_wav,Value(0.0),output_field=models.FloatField()), int_wav = Coalesce(int_wav,Value(0.0),output_field=models.FloatField()), principal_due = Sum('schedule_principal_expected') - Coalesce(princ_paid,Value(0.0),output_field=models.FloatField()), interest_due = Sum('schedule_interest_expected') - Coalesce(int_pay,Value(0.0),output_field=models.FloatField()), penalty_balance = Coalesce(penalty,Value(0.0),output_field=models.FloatField()) - Coalesce(pen_pay,Value(0.0),output_field=models.FloatField()), loan_due_balance = Sum('schedule_principal_expected') + Sum('schedule_interest_expected') + Coalesce(penalty,Value(0.0),output_field=models.FloatField()) - (Coalesce(princ_paid,Value(0.0),output_field=models.FloatField()) + Coalesce(int_pay,Value(0.0),output_field=models.FloatField()) + Coalesce(pen_pay,Value(0.0),output_field=models.FloatField())), principal_balance = Coalesce(loan_princ,Value(0.0),output_field=models.FloatField()) - Coalesce(princ_paid,Value(0.0),output_field=models.FloatField()), interest_balance = Coalesce(loan_interest,Value(0.0),output_field=models.FloatField()) - Coalesce(int_pay,Value(0.0),output_field=models.FloatField()), loan_balance = Coalesce(loan_princ,Value(0.0),output_field=models.FloatField()) + Coalesce(loan_interest,Value(0.0),output_field=models.FloatField()) + Coalesce(penalty,Value(0.0),output_field=models.FloatField()) - (Coalesce(princ_paid,Value(0.0),output_field=models.FloatField()) + Coalesce(int_pay,Value(0.0),output_field=models.FloatField()) + Coalesce(pen_pay,Value(0.0),output_field=models.FloatField())) ).filter(additional_filters) else: loans = LoanRepaymentview.objects.values("id","member_number","name","telephone","loan_start_date","loan_end_date","status","loan_amount","loan_write_off_date").filter(**loan_filter).annotate( loan_princ = Coalesce(loan_princ,Value(0.0),output_field=models.FloatField()), loan_interest = Coalesce(loan_interest,Value(0.0),output_field=models.FloatField()), principal_expected = Sum('schedule_principal_expected'), interest_expected = Sum('schedule_interest_expected'), first_arrear_date=Min('loan_arrear_date'), schedule_expected_date=Min('schedule_expected_date'), princ_paid = Coalesce(princ_paid,Value(0.0),output_field=models.FloatField()), int_paid = Coalesce(int_paid,Value(0.0),output_field=models.FloatField()), pen_paid = Coalesce(pen_paid,Value(0.0),output_field=models.FloatField()), paid_amount = pay_amount, penalty = Coalesce(penalty,Value(0.0),output_field=models.FloatField()), pen_wav = Coalesce(pen_wav,Value(0.0),output_field=models.FloatField()), int_wav = Coalesce(int_wav,Value(0.0),output_field=models.FloatField()), principal_due = Sum('schedule_principal_expected') - Coalesce(princ_paid,Value(0.0),output_field=models.FloatField()), interest_due = Sum('schedule_interest_expected') - Coalesce(int_pay,Value(0.0),output_field=models.FloatField()), penalty_balance = Coalesce(penalty,Value(0.0),output_field=models.FloatField()) - Coalesce(pen_pay,Value(0.0),output_field=models.FloatField()), loan_due_balance = Sum('schedule_principal_expected') + Sum('schedule_interest_expected') + Coalesce(penalty,Value(0.0),output_field=models.FloatField()) - (Coalesce(princ_paid,Value(0.0),output_field=models.FloatField()) + Coalesce(int_pay,Value(0.0),output_field=models.FloatField()) + Coalesce(pen_pay,Value(0.0),output_field=models.FloatField())), principal_balance = Coalesce(loan_princ,Value(0.0),output_field=models.FloatField()) - Coalesce(princ_paid,Value(0.0),output_field=models.FloatField()), interest_balance = Coalesce(loan_interest,Value(0.0),output_field=models.FloatField()) - Coalesce(int_pay,Value(0.0),output_field=models.FloatField()), loan_balance = Coalesce(loan_princ,Value(0.0),output_field=models.FloatField()) + Coalesce(loan_interest,Value(0.0),output_field=models.FloatField()) + Coalesce(penalty,Value(0.0),output_field=models.FloatField()) - (Coalesce(princ_paid,Value(0.0),output_field=models.FloatField()) + Coalesce(int_pay,Value(0.0),output_field=models.FloatField()) + Coalesce(pen_pay,Value(0.0),output_field=models.FloatField())) ).filter(additional_filters) return loans def refine_disbursed_loans(request, organisation): loan_disbursements = LoanApplicationDisbursement.objects.filter(loan_application__id__in=[13062], loan_application__organisation_branch__branch_organisation__id=24) for loan_disbursement in loan_disbursements: loan_schedules = LoanRepaymentSchedule.objects.filter(loan_application=loan_disbursement.loan_application, status='active').order_by('id') new_schedule = generate_loan_schedules(request, loan_disbursement.loan_application.id) counter = 0 for loan_schedule in loan_schedules: if new_schedule[counter]['expected_date']: loan_schedule.expected_date = new_schedule[counter]['expected_date'] loan_schedule.save() counter += 1 return True def refine_loans(request, organisation): loan_disbursements = LoanApplicationDisbursement.objects.filter(loan_application__id=56334) for loan_disbursement in loan_disbursements: loan_approval = LoanApplicationApproval.objects.filter(loan_application = loan_disbursement.loan_application).first() if loan_approval: loan_schedules = LoanRepaymentSchedule.objects.filter(loan_application=loan_disbursement.loan_application, status='active').order_by('id') # Check if the day is either 30 or 31 # day = loan_disbursement.loan_start_date.day # if day == 30 or day == 31: new_schedule = generate_loan_schedules(request, loan_disbursement.loan_application.id) counter = 0 for loan_schedule in loan_schedules: if new_schedule[counter]['expected_date']: loan_schedule.expected_date = new_schedule[counter]['expected_date'] loan_schedule.save() counter += 1 return True def refine_loan_amounts(request, organisation): # organisation_branch__branch_organisation__id=organisation new_schedule = [] loan_disbursements = LoanApplicationDisbursement.objects.filter(loan_application__id=3423) for loan_disbursement in loan_disbursements: interest, principal, total = loan_payment(request, loan_disbursement.loan_application.id) loan_disbursement.total_interest_expected = interest loan_disbursement.total_principal_expected = principal loan_disbursement.total_expected = total loan_disbursement.save() loan_schedules = LoanRepaymentSchedule.objects.filter(loan_application=loan_disbursement.loan_application, status='active').order_by('id') new_schedule = generate_loan_schedules(request, loan_disbursement.loan_application.id) counter = 0 for loan_schedule in loan_schedules: loan_schedule.principal_expected = new_schedule[counter]['principal_expected'] loan_schedule.interest_expected = new_schedule[counter]['interest_expected'] loan_schedule.total_payment = new_schedule[counter]['total_payment'] loan_schedule.ending_balance = new_schedule[counter]['ending_balance'] loan_schedule.starting_balance = new_schedule[counter]['starting_balance'] loan_schedule.save() counter += 1 # make payments loan_schedules = LoanRepaymentSchedule.objects.filter(loan_application=loan_disbursement.loan_application, status='active').order_by('id') princ_balance = 0 int_balance = 0 for loan_schedule in loan_schedules: loan_schedule_payments = LoanPayments.objects.filter(loan_application=loan_disbursement.loan_application, loan_repayment_schedule=loan_schedule).aggregate(total_int_paid=Sum('int_paid'), total_princ_paid=Sum('princ_paid') ) princ_paid = loan_schedule_payments['total_princ_paid'] if loan_schedule_payments['total_princ_paid'] else 0 int_paid = loan_schedule_payments['total_int_paid'] if loan_schedule_payments['total_int_paid'] else 0 if princ_paid > 0 or int_paid > 0: if princ_paid > 0 and loan_schedule.principal_expected < princ_paid: loan_schedules_paid = LoanPayments.objects.filter(loan_application=loan_disbursement.loan_application, loan_repayment_schedule=loan_schedule) for loan_schedule_paid in loan_schedules_paid: if loan_schedule_paid.princ_paid > (princ_paid - loan_schedule.principal_expected): loan_schedule_paid.princ_paid = princ_paid - (princ_paid - loan_schedule.principal_expected) loan_schedule_paid.save() princ_balance += (princ_paid - loan_schedule.principal_expected) break if int_paid > 0 and loan_schedule.interest_expected < int_paid: loan_schedules_paid = LoanPayments.objects.filter(loan_application=loan_disbursement.loan_application, loan_repayment_schedule=loan_schedule) for loan_schedule_paid in loan_schedules_paid: if loan_schedule_paid.int_paid > (int_paid - loan_schedule.interest_expected): loan_schedule_paid.int_paid = int_paid - (int_paid - loan_schedule.interest_expected) loan_schedule_paid.save() int_balance += (int_paid - loan_schedule.interest_expected) break if princ_balance > 0 or int_balance > 0: # calculate the paid schedules payment_schedules = [] list_loan_schedules = LoanRepaymentSchedule.objects.filter(status="active", loan_application=loan_disbursement.loan_application).order_by('payment_number') for list_loan_schedule in list_loan_schedules: pay_int = 0 pay_princ = 0 schedule_payment = LoanPayments.objects.filter(loan_repayment_schedule=list_loan_schedule, loan_application=loan_disbursement.loan_application).aggregate(total_int_paid=Sum('int_paid'), total_princ_paid = Sum('princ_paid') , total_penalty_paid = Sum('penalty_paid')) total_int_paid = float(schedule_payment['total_int_paid']) if schedule_payment['total_int_paid'] else 0 total_princ_paid = float(schedule_payment['total_princ_paid']) if schedule_payment['total_princ_paid'] else 0 # if no payment for schedule if total_int_paid == 0: if int_balance - list_loan_schedule.interest_expected > 0: pay_int = list_loan_schedule.interest_expected elif int_balance - list_loan_schedule.interest_expected <= 0 and int_balance > 0: pay_int = int_balance if total_princ_paid == 0: if princ_balance - list_loan_schedule.principal_expected > 0: pay_princ = list_loan_schedule.principal_expected elif princ_balance - list_loan_schedule.principal_expected <= 0 and princ_balance > 0: pay_princ = princ_balance # if there's a partial payment for a schedule if total_int_paid > 0: _int_balance = list_loan_schedule.interest_expected - total_int_paid if _int_balance == 0: pay_int = 0 elif int_balance - _int_balance > 0 and _int_balance > 0: pay_int = _int_balance elif int_balance - _int_balance <= 0 and int_balance > 0: pay_int = int_balance if total_princ_paid > 0: _princ_balance = list_loan_schedule.principal_expected - total_princ_paid if _princ_balance == 0: pay_princ = 0 elif princ_balance - _princ_balance > 0 and _princ_balance > 0: pay_princ = _princ_balance elif princ_balance - _princ_balance <= 0 and princ_balance > 0: pay_princ = princ_balance if pay_int > 0: payment_schedules.append({"loan_schedule":list_loan_schedule.id, "int_paid":pay_int, "principal_paid": 0}) if pay_princ > 0: payment_schedules.append({"loan_schedule":list_loan_schedule.id, "int_paid": 0, "principal_paid":pay_princ }) int_balance = int_balance - pay_int princ_balance = princ_balance - pay_princ if int_balance < 1 and princ_balance < 1: break schedule_payment = LoanPayments.objects.filter(loan_application=loan_disbursement.loan_application).order_by('-id').first() for payment_schedule in payment_schedules: post_loan_schedule = LoanRepaymentSchedule.objects.get(pk=payment_schedule['loan_schedule']) data = {"loan_application":loan_disbursement.loan_application, "loan_repayment_schedule":post_loan_schedule, "loan_payments_added_by":schedule_payment.loan_payments_added_by, "loan_main_transaction":schedule_payment.loan_main_transaction, "int_paid":payment_schedule['int_paid'], "princ_paid":payment_schedule['principal_paid'], "penalty_paid":0, "payment_date":schedule_payment.payment_date, "loan_payment_transaction":schedule_payment.loan_payment_transaction} loan_payment_details = LoanPayments.objects.create(**data) if not loan_payment_details: return False return new_schedule def loan_migration_process_payment(payment_details, request): user = request.user organisation_id = get_current_user(request, 'organisation_id', None) branch_id = get_current_user(request, 'organisation_branch_id', None) for item in ['amount_paid', 'principal_paid', 'int_paid', 'penalty_paid', 'payment_method', 'account', 'date_added', 'voucher_no', 'cheque', 'loan_id']: if item not in payment_details: return False amount_paid = payment_details['amount_paid'] principal_paid = payment_details['principal_paid'] int_paid = payment_details['int_paid'] payment_method = payment_details['payment_method'] account = payment_details['account'] voucher_no = payment_details['voucher_no'] cheque = payment_details['cheque'] loan_id = payment_details['loan_id'] account_id = payment_details['account_id'] heading = 'Loan Payment' loan_application = LoanApplication.objects.get(pk=loan_id) selected_account = OrganisationSubAccount.objects.get(pk=account) loan_main_payment = None # Generate reference number credit_chart = loan_application.loan_application_product.chart loan_product = loan_application.loan_application_product.product_name interest_income_chart = loan_application.loan_application_product.interest_income_chart # calculate the paid schedules payment_schedules = [] list_loan_schedules = LoanRepaymentSchedule.objects.filter(status="active", loan_application=loan_application).order_by('payment_number') for list_loan_schedule in list_loan_schedules: pay_int = 0 pay_princ = 0 if int_paid >= list_loan_schedule.interest_expected: pay_int = list_loan_schedule.interest_expected else: if int_paid > 0: pay_int = int_paid if principal_paid >= list_loan_schedule.principal_expected: pay_princ = list_loan_schedule.principal_expected else: if principal_paid > 0: pay_princ = principal_paid if pay_int > 0: payment_schedules.append({"loan_schedule":list_loan_schedule.id, "int_paid":pay_int, "principal_paid": 0}) if pay_princ > 0: payment_schedules.append({"loan_schedule":list_loan_schedule.id, "int_paid": 0, "principal_paid":pay_princ }) int_paid = int_paid - pay_int principal_paid = principal_paid - pay_princ if (amount_paid > 0) and len(payment_schedules) > 0: post_loan_schedule = LoanRepaymentSchedule.objects.get(pk=payment_schedules[len(payment_schedules)-1]['loan_schedule']) loan_main_payment = LoanPaymentTransaction.objects.create(amount=amount_paid, loan_application=loan_application, loan_payment_transaction_added_by=user, payment_date=post_loan_schedule.expected_date) for payment_schedule in payment_schedules: post_loan_schedule = LoanRepaymentSchedule.objects.get(pk=payment_schedule['loan_schedule']) sch_total_principal_paid = payment_schedule['principal_paid'] sch_total_int_paid = payment_schedule['int_paid'] # post principal if sch_total_principal_paid > 0: heading = 'Loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation_id, 'ln-p') transaction = SystemTransactions.objects.create(amount=sch_total_principal_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=selected_account.id, credit_chart_id=credit_chart.id, branch_id=branch_id, added_by=user, record_date=post_loan_schedule.expected_date) if not transaction: return False data = {"heading":heading, "amount":sch_total_principal_paid, "cheque":cheque, "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":voucher_no, "transaction_type":'LoanPrincipalPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction, "payment_date":post_loan_schedule.expected_date} main_loan_transaction = LoanMainTransactions.objects.create(**data) if not main_loan_transaction: return False if payment_method == 'offset': saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # post interest if sch_total_int_paid > 0: heading = 'Loan interest income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation_id, 'ln-in') transaction = None transaction = SystemTransactions.objects.create(amount=sch_total_int_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=selected_account.id, credit_chart_id=interest_income_chart.id, branch_id=branch_id, added_by=user, record_date=post_loan_schedule.expected_date) if not transaction: return False data = {"heading":heading, "amount":sch_total_int_paid, "cheque":cheque, "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":voucher_no, "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction, "payment_date":post_loan_schedule.expected_date} main_loan_transaction = LoanMainTransactions.objects.create(**data) if not main_loan_transaction: return False if payment_method == 'offset': saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) data = {"loan_application":loan_application, "loan_repayment_schedule":post_loan_schedule, "loan_payments_added_by":user, "loan_main_transaction":main_loan_transaction, "int_paid":sch_total_int_paid, "princ_paid":sch_total_principal_paid, "penalty_paid":0, "payment_date":post_loan_schedule.expected_date, "loan_payment_transaction":loan_main_payment} loan_payment_details = LoanPayments.objects.create(**data) if not loan_payment_details: return False return True def bunyaruguru_auto_loan_repayment(payment_data): loan_id = payment_data['loan'] interest_paid = payment_data['interest'] principal_paid = payment_data['principal'] penalty_paid = payment_data['penalty'] total = interest_paid + principal_paid schedule = payment_data['schedule'] tota_amount_paid = 0 datetime = datetime_timedelta.datetime date_added = make_aware(datetime.strptime('2023-07-31', '%Y-%m-%d')) loan_schedule = LoanRepaymentSchedule.objects.filter(id=schedule).first() if not loan_schedule: return False loan_application = LoanApplication.objects.filter(id=loan_id, is_deleted=False).first() if not loan_application: return False # check if auto pay penalty if loan_application.loan_application_product.auto_pay_penalty: total += penalty_paid else: penalty_paid = 0 savings_account = SavingAccount.objects.filter(account_customer=loan_application.customer, status='active').all().order_by('id').first() if savings_account: payment_method = 'offset' account_bal = get_account_balance(savings_account) account_balance = account_bal["balance_raw"] if account_bal and account_bal["balance_raw"] > 0 else 0 selected_account = savings_account.account_product.accounts_chart user = savings_account.saving_account_added_by # Generate reference number loan_product = loan_application.loan_application_product.product_name organisation = loan_application.organisation_branch.branch_organisation credit_chart = loan_application.loan_application_product.chart interest_income_chart = loan_application.loan_application_product.interest_income_chart penalty_income_chart = loan_application.loan_application_product.penalty_income_chart loan_payment_transaction = None if account_balance >= total: # make paymet of both interest and principal, penalty # penalty payment if penalty_paid > 0 and loan_application.loan_application_product.auto_pay_penalty and account_balance >= penalty_paid: heading = 'Auto loan penalty income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-in') transaction = SystemTransactions.objects.create(amount=penalty_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no='', debit_chart_id=selected_account.id, credit_chart_id=penalty_income_chart.id, branch_id=savings_account.customer_branch.id, added_by=user, record_date=date_added) if transaction: data = {"heading":heading, "amount":penalty_paid, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'PenaltyPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction, "payment_date":date_added} int_paid_transaction = LoanMainTransactions.objects.create(**data) if int_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # post payment loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=penalty_paid, loan_application=loan_application, transaction_type='auto', payment_date=date_added ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":int_paid_transaction, "penalty_paid":penalty_paid, "loan_payment_transaction":loan_payment_transaction, "payment_date":date_added} LoanPayments.objects.create(**payment_data) tota_amount_paid += penalty_paid account_balance -= penalty_paid penalty_paid = 0 # interest payment if interest_paid > 0 and account_balance >= interest_paid: heading = 'Auto loan interest income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-in') transaction = SystemTransactions.objects.create(amount=interest_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no='', debit_chart_id=selected_account.id, credit_chart_id=interest_income_chart.id, branch_id=savings_account.customer_branch.id, added_by=user, record_date=date_added) if transaction: data = {"heading":heading, "amount":interest_paid, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction, "payment_date":date_added} int_paid_transaction = LoanMainTransactions.objects.create(**data) if int_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=interest_paid, loan_application=loan_application, transaction_type='auto', payment_date=date_added ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":int_paid_transaction, "int_paid":interest_paid, "loan_payment_transaction":loan_payment_transaction, "payment_date":date_added} LoanPayments.objects.create(**payment_data) tota_amount_paid += interest_paid loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance -= interest_paid interest_paid = 0 # principal payment if principal_paid > 0 and account_balance >= principal_paid: heading = 'Auto loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-p') transaction = SystemTransactions.objects.create(amount=principal_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no='', debit_chart_id=selected_account.id, credit_chart_id=credit_chart.id, branch_id=savings_account.customer_branch.id, added_by=user, record_date=date_added) if transaction: data = {"heading":heading, "amount":principal_paid, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'LoanPrincipalPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction, "payment_date":date_added} principal_paid_transaction = LoanMainTransactions.objects.create(**data) if principal_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=principal_paid, loan_application=loan_application, transaction_type='auto', payment_date=date_added ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":principal_paid_transaction, "princ_paid":principal_paid, "loan_payment_transaction":loan_payment_transaction, "payment_date":date_added} LoanPayments.objects.create(**payment_data) tota_amount_paid += principal_paid loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance -= principal_paid principal_paid = 0 else: if account_balance >= penalty_paid and penalty_paid > 0 and loan_application.loan_application_product.auto_pay_penalty: # make penalty payment only heading = 'Auto loan penalty income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-in') transaction = SystemTransactions.objects.create(amount=penalty_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no='', debit_chart_id=selected_account.id, credit_chart_id=penalty_income_chart.id, branch_id=savings_account.customer_branch.id, added_by=user, record_date=date_added) if transaction: data = {"heading":heading, "amount":penalty_paid, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction, "payment_date":date_added} int_paid_transaction = LoanMainTransactions.objects.create(**data) if int_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=penalty_paid, loan_application=loan_application, transaction_type='auto', payment_date=date_added ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":int_paid_transaction, "penalty_paid":penalty_paid, "loan_payment_transaction":loan_payment_transaction, "payment_date":date_added} LoanPayments.objects.create(**payment_data) tota_amount_paid += penalty_paid loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance -= penalty_paid penalty_paid = 0 if account_balance >= interest_paid and interest_paid > 0: # make interest payment only heading = 'Auto loan interest income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-in') transaction = SystemTransactions.objects.create(amount=interest_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no='', debit_chart_id=selected_account.id, credit_chart_id=interest_income_chart.id, branch_id=savings_account.customer_branch.id, added_by=user, record_date=date_added) if transaction: data = {"heading":heading, "amount":interest_paid, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction, "payment_date":date_added} int_paid_transaction = LoanMainTransactions.objects.create(**data) if int_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=interest_paid, loan_application=loan_application, transaction_type='auto', payment_date=date_added ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":int_paid_transaction, "int_paid":interest_paid, "loan_payment_transaction":loan_payment_transaction, "payment_date":date_added} LoanPayments.objects.create(**payment_data) tota_amount_paid += interest_paid loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance -= interest_paid interest_paid = 0 if account_balance >= principal_paid and principal_paid > 0: # make principal payment only heading = 'Auto loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-p') transaction = SystemTransactions.objects.create(amount=principal_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no='', debit_chart_id=selected_account.id, credit_chart_id=credit_chart.id, branch_id=savings_account.customer_branch.id, added_by=user, record_date=date_added) if transaction: data = {"heading":heading, "amount":principal_paid, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'LoanPrincipalPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction, "payment_date":date_added} principal_paid_transaction = LoanMainTransactions.objects.create(**data) if principal_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=principal_paid, loan_application=loan_application, transaction_type='auto', payment_date=date_added ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":principal_paid_transaction, "princ_paid":principal_paid, "loan_payment_transaction":loan_payment_transaction, "payment_date":date_added} LoanPayments.objects.create(**payment_data) tota_amount_paid += principal_paid loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance -= principal_paid principal_paid = 0 # pay if account has less money if account_balance > 0 and account_balance < penalty_paid and penalty_paid > 0: # make penalty payment only heading = 'Auto loan penalty income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-in') transaction = SystemTransactions.objects.create(amount=account_balance, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no='', debit_chart_id=selected_account.id, credit_chart_id=penalty_income_chart.id, branch_id=savings_account.customer_branch.id, added_by=user, record_date=date_added) if transaction: data = {"heading":heading, "amount":account_balance, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction, "payment_date":date_added} int_paid_transaction = LoanMainTransactions.objects.create(**data) if int_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=account_balance, loan_application=loan_application, transaction_type='auto', payment_date=date_added ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":int_paid_transaction, "penalty_paid":account_balance, "loan_payment_transaction":loan_payment_transaction, "payment_date":date_added} LoanPayments.objects.create(**payment_data) tota_amount_paid += account_balance loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance = 0 penalty_paid = 0 if account_balance > 0 and account_balance < interest_paid and interest_paid > 0: # make interest payment only heading = 'Auto loan interest income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-in') transaction = SystemTransactions.objects.create(amount=account_balance, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no='', debit_chart_id=selected_account.id, credit_chart_id=interest_income_chart.id, branch_id=savings_account.customer_branch.id, added_by=user, record_date=date_added) if transaction: data = {"heading":heading, "amount":account_balance, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction, "payment_date":date_added} int_paid_transaction = LoanMainTransactions.objects.create(**data) if int_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=account_balance, loan_application=loan_application, transaction_type='auto', payment_date=date_added ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":int_paid_transaction, "int_paid":account_balance, "loan_payment_transaction":loan_payment_transaction, "payment_date":date_added} LoanPayments.objects.create(**payment_data) tota_amount_paid += account_balance loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance = 0 interest_paid = 0 if account_balance > 0 and account_balance < principal_paid and principal_paid > 0: # make principal payment only heading = 'Auto loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation.id, 'ln-p') transaction = SystemTransactions.objects.create(amount=account_balance, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no='', debit_chart_id=selected_account.id, credit_chart_id=credit_chart.id, branch_id=savings_account.customer_branch.id, added_by=user, record_date=date_added) if transaction: data = {"heading":heading, "amount":account_balance, "cheque":'', "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":'', "transaction_type":'LoanPrincipalPayment', "loan_main_transaction_added_by":user, "system_transaction":transaction,"payment_date":date_added} principal_paid_transaction = LoanMainTransactions.objects.create(**data) if principal_paid_transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":savings_account.id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # post loan payment if not loan_payment_transaction: loan_payment_transaction = LoanPaymentTransaction.objects.create(amount=account_balance, loan_application=loan_application, transaction_type='auto', payment_date=date_added ) payment_data = {"loan_application":loan_application, "loan_repayment_schedule":loan_schedule, "loan_main_transaction":principal_paid_transaction, "princ_paid":account_balance, "loan_payment_transaction":loan_payment_transaction, "payment_date":date_added} LoanPayments.objects.create(**payment_data) tota_amount_paid += account_balance loan_payment_transaction.amount = tota_amount_paid loan_payment_transaction.save() account_balance = 0 principal_paid = 0 def bds_process_loan_payment(payment_details): user = None if int(payment_details['added_by']) == 0: user = get_user_model().objects.filter(username='g.manuelina').first() else: user = get_user_model().objects.get(pk=payment_details['added_by']) for item in ['amount_paid', 'principal_paid', 'int_paid', 'penalty_paid', 'payment_method', 'account', 'date_added', 'voucher_no', 'cheque', 'loan_id']: if item not in payment_details: return False amount_paid = payment_details['amount_paid'] principal_paid = payment_details['principal_paid'] int_paid = payment_details['int_paid'] penalty_paid = payment_details['penalty_paid'] payment_method = payment_details['payment_method'] account = payment_details['account'] payment_date = payment_details['payment_date'] date_added = payment_details['date_added'] transaction_type = payment_details['transaction_type'] voucher_no = payment_details['voucher_no'] cheque = payment_details['cheque'] loan_id = payment_details['loan_id'] account_id = payment_details['account_id'] heading = 'Loan Payment' loan_application = LoanApplication.objects.get(pk=loan_id) selected_account = OrganisationSubAccount.objects.get(pk=account) organisation_id = 21 branch_id = loan_application.customer.customer_branch.id penalty_paid_transaction = None loan_main_payment = None # Generate reference number credit_chart = loan_application.loan_application_product.chart loan_product = loan_application.loan_application_product.product_name interest_income_chart = loan_application.loan_application_product.interest_income_chart penality_income_chart = loan_application.loan_application_product.penalty_income_chart if principal_paid > 0 or int_paid > 0 or penalty_paid > 0: loan_main_payment = LoanPaymentTransaction.objects.create(amount=amount_paid, loan_application=loan_application, loan_payment_transaction_added_by=user, payment_date=payment_date, date_added=date_added, transaction_type=transaction_type) # post principal if principal_paid > 0: heading = 'Loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation_id, 'ln-p') transaction = None transaction = SystemTransactions.objects.create(amount=principal_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=selected_account.id, credit_chart_id=credit_chart.id, branch_id=branch_id, added_by=user, record_date=payment_date, date_added=date_added) if not transaction: return False data = {"heading":heading, "amount":int_paid, "cheque":cheque, "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":voucher_no, "transaction_type":'LoanPrincipalPayment', "loan_main_transaction_added_by":user, "payment_date":payment_date, "date_added":date_added, "system_transaction":transaction} principal_paid_transaction = LoanMainTransactions.objects.create(**data) if not principal_paid_transaction: return False if payment_method == 'offset': saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # post interest if int_paid > 0: heading = 'Loan interest income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation_id, 'ln-in') transaction = None transaction = SystemTransactions.objects.create(amount=int_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=selected_account.id, credit_chart_id=interest_income_chart.id, branch_id=branch_id, added_by=user, record_date=payment_date, date_added=date_added) if not transaction: return False data = {"heading":heading, "amount":int_paid, "cheque":cheque, "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":voucher_no, "transaction_type":'InterestPayment', "loan_main_transaction_added_by":user, "payment_date":payment_date, "date_added":date_added, "system_transaction":transaction} int_paid_transaction = LoanMainTransactions.objects.create(**data) if not int_paid_transaction: return False if payment_method == 'offset': saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # post penalty if penalty_paid > 0: heading = 'Loan penalty income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product reference_no = generate_reference_no(credit_chart.account_line, organisation_id, 'ln-in') transaction = None transaction = SystemTransactions.objects.create(amount=penalty_paid, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no=voucher_no, debit_chart_id=selected_account.id, credit_chart_id=penality_income_chart.id, branch_id=branch_id, added_by=user, record_date=payment_date, date_added=date_added) if not transaction: return False data = {"heading":heading, "amount":penalty_paid, "cheque":cheque, "payment_method":payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":voucher_no, "transaction_type":'PenaltyPayment', "loan_main_transaction_added_by":user, "payment_date":payment_date, "system_transaction":transaction, "date_added":date_added} penalty_paid_transaction = LoanMainTransactions.objects.create(**data) if not penalty_paid_transaction: return False if payment_method == 'offset': saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":transaction.id } SavingAccountTransactions.objects.create(**saved_transaction_fields) # calculate the paid schedules payment_schedules = [] list_loan_schedules = LoanRepaymentSchedule.objects.filter(status="active", loan_application=loan_application).order_by('payment_number') for list_loan_schedule in list_loan_schedules: pay_int = 0 pay_princ = 0 schedule_payment = LoanPayments.objects.filter(loan_repayment_schedule=list_loan_schedule, loan_application=loan_application, payment_status='normal').aggregate(total_int_paid=Sum('int_paid'), total_princ_paid = Sum('princ_paid') , total_penalty_paid = Sum('penalty_paid')) total_int_paid = float(schedule_payment['total_int_paid']) if schedule_payment['total_int_paid'] else 0 total_princ_paid = float(schedule_payment['total_princ_paid']) if schedule_payment['total_princ_paid'] else 0 # if no payment for schedule if total_int_paid == 0: if int_paid - list_loan_schedule.interest_expected > 0: pay_int = list_loan_schedule.interest_expected elif int_paid - list_loan_schedule.interest_expected <= 0 and int_paid > 0: pay_int = int_paid if total_princ_paid == 0: if principal_paid - list_loan_schedule.principal_expected > 0: pay_princ = list_loan_schedule.principal_expected elif principal_paid - list_loan_schedule.principal_expected <= 0 and principal_paid > 0: pay_princ = principal_paid # if there's a partial payment for a schedule if total_int_paid > 0: int_balance = list_loan_schedule.interest_expected - total_int_paid if int_balance == 0: pay_int = 0 elif int_paid - int_balance > 0 and int_balance > 0: pay_int = int_balance elif int_paid - int_balance <= 0 and int_paid > 0: pay_int = int_paid if total_princ_paid > 0: princ_balance = list_loan_schedule.principal_expected - total_princ_paid if princ_balance == 0: pay_princ = 0 elif principal_paid - princ_balance > 0 and princ_balance > 0: pay_princ = princ_balance elif principal_paid - princ_balance <= 0 and principal_paid > 0: pay_princ = principal_paid if pay_int > 0: payment_schedules.append({"loan_schedule":list_loan_schedule.id, "int_paid":pay_int, "principal_paid": 0, "loan_main_transaction":int_paid_transaction}) if pay_princ > 0: payment_schedules.append({"loan_schedule":list_loan_schedule.id, "int_paid": 0, "principal_paid":pay_princ, "loan_main_transaction":principal_paid_transaction }) int_paid = int_paid - pay_int principal_paid = principal_paid - pay_princ if int_paid < 1 and principal_paid < 1: break for payment_schedule in payment_schedules: post_loan_schedule = LoanRepaymentSchedule.objects.get(pk=payment_schedule['loan_schedule']) data = {"loan_application":loan_application, "loan_repayment_schedule":post_loan_schedule, "loan_payments_added_by":user, "loan_main_transaction":payment_schedule['loan_main_transaction'], "int_paid":payment_schedule['int_paid'], "princ_paid":payment_schedule['principal_paid'], "penalty_paid":0, "payment_date":payment_date, "date_added":date_added, "loan_payment_transaction":loan_main_payment} loan_payment_details = LoanPayments.objects.create(**data) if not loan_payment_details: return False # post penalty if penalty_paid_transaction: data = {"loan_application":loan_application, "loan_payments_added_by":user, "loan_main_transaction":penalty_paid_transaction, "int_paid":0, "princ_paid":0, "penalty_paid":penalty_paid, "payment_date":payment_date, "date_added":date_added, "loan_payment_transaction":loan_main_payment} loan_penalty_payment_details = LoanPayments.objects.create(**data) if not loan_penalty_payment_details: return False return True def loans_transaction_management(request, update_type, update_details, payment_transaction_id =None, system_transaction_id=None ): reference_number = update_details.get('reference_number', 'ln-p') if reference_number not in ['ln-in', 'ln-p']: return False if system_transaction_id: loan_main_payment = LoanMainTransactions.objects.filter(system_transaction__id=system_transaction_id).first() if not loan_main_payment: inter_branch = InterBranchTransactions.objects.filter(Q(source_transaction__id=system_transaction_id) | Q(destination_transaction__id=system_transaction_id)).first() if inter_branch: loan_main_payment = LoanMainTransactions.objects.filter(system_transaction__id=inter_branch.source_transaction.id).first() if not loan_main_payment: loan_main_payment = LoanMainTransactions.objects.filter(system_transaction__id=inter_branch.destination_transaction.id).first() if loan_main_payment: loan_payment_obj = LoanPayments.objects.filter(loan_main_transaction=loan_main_payment).first() if loan_payment_obj: payment_transaction_id = loan_payment_obj.loan_payment_transaction.id if not payment_transaction_id: return False if update_type == 'edit': if payment_transaction_id: update_details['transaction_id'] = payment_transaction_id loan_repayment_edit(request, update_details) elif update_type == 'reversal': comment = update_details.get('comment', 'Reversal') loan_repayment_reversal(request, comment, None, payment_transaction_id) elif update_type == 'delete' or update_type == 'deletion': comment = update_details.get('comment', 'Deletion') loan_repayment_delete(request, payment_transaction_id,comment) return True def loan_repayment_delete(request, payment_transaction_id, comment): from django.utils import timezone from users.audit_log_helper import add_system_audit_trail now = timezone.now() user_id = request.user.id if request and hasattr(request, 'user') else None df = dict(deleted=True, deleted_by_id=user_id, deleted_at=now) loan_payment_transaction = LoanPaymentTransaction.objects.filter(id=payment_transaction_id).first() if loan_payment_transaction: branch = loan_payment_transaction.loan_application.organisation_branch if user_id and branch: try: add_system_audit_trail( 'transaction_management', 'delete_loan_payment', f'Deleted Loan Repayment for {loan_payment_transaction.loan_application.customer.name} Mem No: {loan_payment_transaction.loan_application.customer.member_number}', comment, {}, {}, request.user, branch ) except Exception: pass main_transactions = [] loan_payments = LoanPayments.objects.filter(loan_payment_transaction=loan_payment_transaction) for loan_payment in loan_payments: main_transactions.append(loan_payment.loan_main_transaction.id) LoanPayments.objects.filter(loan_main_transaction__id__in=main_transactions).update(**df) transactions = LoanMainTransactions.objects.filter(id__in=main_transactions) list_transactions = [] for transaction in transactions: if transaction.system_transaction: list_transactions.append(transaction.system_transaction.id) inter_branch = InterBranchTransactions.objects.filter(Q(source_transaction=transaction.system_transaction) | Q(destination_transaction=transaction.system_transaction)).first() if inter_branch: list_transactions.append(inter_branch.source_transaction.id) list_transactions.append(inter_branch.destination_transaction.id) SavingAccountTransactions.objects.filter(transaction=transaction.system_transaction).update(**df) LoanMainTransactions.objects.filter(id=transaction.id).update(**df) SystemTransactions.objects.filter(id__in=set(list_transactions)).update(**df) LoanPaymentTransaction.objects.filter(id=payment_transaction_id).update(**df) return True def loan_repayment_edit(request, update_details): record_date = update_details['record_date'] update_reason = update_details['comment'] transaction_id = update_details.get('transaction_id', None) if not transaction_id: return False loan_payment_transaction = LoanPaymentTransaction.objects.filter(id=transaction_id).first() if loan_payment_transaction: loan_payment_transaction.payment_date = record_date loan_payment_transaction.save() loan_payments = LoanPayments.objects.filter(loan_payment_transaction=loan_payment_transaction) for loan_payment in loan_payments: loan_payment.payment_date = record_date loan_payment.save() transaction = LoanMainTransactions.objects.filter(id=loan_payment.loan_main_transaction.id).first() if transaction: transaction.payment_date = record_date transaction.save() if transaction.system_transaction: system_transaction = SystemTransactions.objects.get(pk=transaction.system_transaction.id) if system_transaction: system_transaction.record_date = record_date system_transaction.coment = update_reason system_transaction.save() return True def loan_repayment_reversal(request, comment, system_transaction_id=None, loan_payment_id=None): reversal_status = False if system_transaction_id: loan_main_payment = LoanMainTransactions.objects.filter(system_transaction__id=system_transaction_id).first() if not loan_main_payment: # interbranch transactions inter_branch = InterBranchTransactions.objects.filter(Q(source_transaction__id=system_transaction_id) | Q(destination_transaction__id=system_transaction_id)).first() if inter_branch: loan_main_payment = LoanMainTransactions.objects.filter(system_transaction__id=inter_branch.source_transaction.id).first() if not loan_main_payment: loan_main_payment = LoanMainTransactions.objects.filter(system_transaction__id=inter_branch.destination_transaction.id).first() if loan_main_payment: loan_payment_obj = LoanPayments.objects.filter(loan_main_transaction=loan_main_payment).first() if loan_payment_obj: loan_payment_id = loan_payment_obj.loan_payment_transaction.id if loan_payment_id: loan_payment_transaction = LoanPaymentTransaction.objects.filter(id=loan_payment_id, transaction_status='normal' ).first() if loan_payment_transaction: loan_payments = LoanPayments.objects.filter(loan_payment_transaction=loan_payment_transaction).distinct('loan_main_transaction').values_list('loan_main_transaction__system_transaction__id', flat=True) for loan_payment in loan_payments: trans_reversal = loan_repayment_system_reversal(loan_payment, request, comment) if trans_reversal: loan_payment_transaction.transaction_status = 'reversed' loan_payment_transaction.save() reversal_status = True LoanPayments.objects.filter(loan_payment_transaction=loan_payment_transaction).update(payment_status='reversed') LoanMainTransactions.objects.filter(system_transaction__id=loan_payment).update(transaction_status='reversed') return reversal_status def loan_repayment_system_reversal(system_transaction_id, request, comment): system_transaction = SystemTransactions.objects.get(pk=system_transaction_id) # check if its an interbranch inter_branch = InterBranchTransactions.objects.filter(Q(source_transaction__id=system_transaction_id) | Q(destination_transaction__id=system_transaction_id)).first() if inter_branch: # transaction 1 source_transaction = inter_branch.source_transaction destination_transaction = inter_branch.destination_transaction # source transaction data = {"amount":system_transaction.amount, "heading":system_transaction.heading + ' - reversal', "reference_no":'rev-'+ system_transaction.reference_no, "payment_method":system_transaction.payment_method, "voucher_no":system_transaction.voucher_no, "debit_chart":source_transaction.credit_chart, "credit_chart":source_transaction.debit_chart, "branch":system_transaction.branch, "added_by":request.user, "record_date":system_transaction.record_date, "transaction_type":'reversed', 'coment':comment } source_transaction_reversal = SystemTransactions.objects.create(**data) if source_transaction_reversal: source_transaction.transaction_type = 'reversal' source_transaction.save() savings_reversal = SavingAccountTransactions.objects.filter(transaction=source_transaction).first() if savings_reversal: savings_reversal.transaction_type = "withdrawal_reversal" savings_reversal.save() saving_obj = {"transaction_type":'withdrawal_reversed', "transaction":source_transaction_reversal, "customer_account":savings_reversal.customer_account } SavingAccountTransactions.objects.create(**saving_obj) # destination transaction data = {"amount":system_transaction.amount, "heading":system_transaction.heading + ' - reversal', "reference_no":'rev-'+ system_transaction.reference_no, "payment_method":system_transaction.payment_method, "voucher_no":system_transaction.voucher_no, "debit_chart":destination_transaction.credit_chart, "credit_chart":destination_transaction.debit_chart, "branch":system_transaction.branch, "added_by":request.user, "record_date":system_transaction.record_date, "transaction_type":'reversed', 'coment':comment } destination_transaction_reversal = SystemTransactions.objects.create(**data) if destination_transaction_reversal: destination_transaction.transaction_type = 'reversal' destination_transaction.save() savings_reversal = SavingAccountTransactions.objects.filter(transaction=destination_transaction).first() if savings_reversal: savings_reversal.transaction_type = "withdrawal_reversal" savings_reversal.save() saving_obj = {"transaction_type":'withdrawal_reversed', "transaction":destination_transaction_reversal, "customer_account":savings_reversal.customer_account } SavingAccountTransactions.objects.create(**saving_obj) return True else: data = {"amount":system_transaction.amount, "heading":system_transaction.heading + ' - reversal', "reference_no":'rev-'+ system_transaction.reference_no, "payment_method":system_transaction.payment_method, "voucher_no":system_transaction.voucher_no, "debit_chart":system_transaction.credit_chart, "credit_chart":system_transaction.debit_chart, "branch":system_transaction.branch, "added_by":request.user, "record_date":system_transaction.record_date, "transaction_type":'reversed', 'coment':comment } system_transaction_reversal = SystemTransactions.objects.create(**data) savings_reversal = SavingAccountTransactions.objects.filter(transaction=system_transaction).first() if savings_reversal and system_transaction_reversal: saving_obj = {"transaction_type":'withdrawal_reversed', "transaction":system_transaction_reversal, "customer_account":savings_reversal.customer_account } SavingAccountTransactions.objects.create(**saving_obj) savings_reversal.transaction_type = 'withdrawal_reversal' savings_reversal.save() if system_transaction_reversal: system_transaction.transaction_type = 'reversal' system_transaction.save() return True return False def loan_payments_reminder(): current_customer_id = 0 try: org_sms_settings = OrganisationSetting.objects.filter(setting_key='loan_payment_reminder').order_by('id') cron_organisations = [] for org_sms_setting in org_sms_settings: organisation_reminder_settings = eval(org_sms_setting.setting_value) if org_sms_setting.setting_value and len(organisation_reminder_settings) > 0: days_before_due_date = int(organisation_reminder_settings['days_before_due_date']) if 'days_before_due_date' in organisation_reminder_settings and organisation_reminder_settings['days_before_due_date'] !='' else 0 days_after_due_date = int(organisation_reminder_settings['days_after_due_date']) if 'days_after_due_date' in organisation_reminder_settings and organisation_reminder_settings['days_after_due_date'] !='' else 0 include_due_date = organisation_reminder_settings['include_due_date'] if 'include_due_date' in organisation_reminder_settings and organisation_reminder_settings['include_due_date'] !='' else 'no' organisation = org_sms_setting.org_setting if days_before_due_date == 0 and days_after_due_date == 0 and include_due_date == 'no': continue # get organisation loans cron_organisations.append(organisation.name) loans = LoanApplication.objects.filter(organisation_branch__branch_organisation=organisation, status='disbursed' ) for loan_details in loans: loan_disbursement = LoanApplicationDisbursement.objects.filter(loan_application=loan_details).first() if not loan_disbursement: continue principal_bal, interest_bal, penalty_bal, written_off_amount = loan_balance(loan_details.id) if (principal_bal + interest_bal + penalty_bal) > 0: loan_filter_details = {"loan_application": loan_details, "status": "active"} # if days_before_due_date <= 0: # current = timezone.now() # loan_filter_details["expected_date__date__lte"] = current.strftime('%Y-%m-%d') loan_repayment_schedules = LoanRepaymentSchedule.objects.filter(**loan_filter_details).order_by('id') if not loan_repayment_schedules: continue penalty_waived = LoanPenaltyWaivered.objects.filter(loan_application=loan_details).aggregate(total_amount=Sum('amount'))['total_amount'] total_penalty_waived = penalty_waived if penalty_waived else 0 loan_penalities_paid = LoanPayments.objects.filter(loan_application = loan_details, payment_status='normal').aggregate(total_penalty_paid = Sum('penalty_paid'))['total_penalty_paid'] loan_penalities_paid = loan_penalities_paid if loan_penalities_paid else 0 total_loan_penalties = LoanPenalty.objects.filter(loan_application=loan_details).aggregate(total_penalty = Sum('amount'))['total_penalty'] total_loan_penalties = total_loan_penalties if total_loan_penalties else 0 total_penalty = total_loan_penalties - (loan_penalities_paid + total_penalty_waived) total_penalty = total_penalty if total_penalty > 0 else 0 total_before_due = 0 total_interest_before_due = 0 total_princ_before_due = 0 before_due_date = None total_after_due = 0 total_interest_after_due = 0 total_princ_after_due = 0 after_due_date = None after_due_arrear_days = None 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).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').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 # 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' current = timezone.now() current_date = make_aware(datetime.strptime(current.strftime('%Y-%m-%d') + ' 00:00', '%Y-%m-%d %H:%M')) eat_timezone = pytz.timezone("Africa/Nairobi") expected_date = loan_repayment_schedule.expected_date.astimezone(eat_timezone) schedule_date = make_aware(datetime.strptime(expected_date.strftime('%Y-%m-%d') + ' 00:00', '%Y-%m-%d %H:%M')) delta = relativedelta(days=days_before_due_date) current_date_before_due = current_date + delta after_due_date = schedule_date if current_date_before_due == schedule_date and is_paid_off == 'false' and days_before_due_date > 0: total_before_due += (loan_repayment_schedule.principal_expected - total_princ_paid) + (loan_repayment_schedule.interest_expected - (total_int_paid + total_interest_waived)) total_interest_before_due += loan_repayment_schedule.interest_expected - (total_int_paid + total_interest_waived) total_princ_before_due += (loan_repayment_schedule.principal_expected - total_princ_paid) loan_date = loan_repayment_schedule.expected_date.astimezone(eat_timezone) before_due_date = loan_date.strftime('%Y-%m-%d') if current_date == schedule_date and is_paid_off == 'false' and include_due_date == 'yes': total_before_due += (loan_repayment_schedule.principal_expected - total_princ_paid) + (loan_repayment_schedule.interest_expected - (total_int_paid + total_interest_waived)) total_interest_before_due += (loan_repayment_schedule.interest_expected - (total_int_paid + total_interest_waived)) total_princ_before_due += (loan_repayment_schedule.principal_expected - total_princ_paid) before_due_date = 'today' if is_paid_off == 'false' and days_after_due_date > 0: arrear_days = (current_date - schedule_date).days if arrear_days > 0 and arrear_days % days_after_due_date == 0: total_after_due += (loan_repayment_schedule.principal_expected - total_princ_paid) + (loan_repayment_schedule.interest_expected - (total_int_paid + total_interest_waived)) total_interest_after_due += (loan_repayment_schedule.interest_expected - (total_int_paid + total_interest_waived)) total_princ_after_due += (loan_repayment_schedule.principal_expected - total_princ_paid) after_due_date = loan_repayment_schedule.expected_date.strftime('%Y-%m-%d') if after_due_date == None else after_due_date after_due_arrear_days = arrear_days if after_due_arrear_days == None else after_due_arrear_days #send sms sms_msg = None total_after_due = total_after_due + total_penalty if total_after_due > 0 and after_due_date and after_due_arrear_days: total_interest_after_due = total_interest_after_due if total_interest_after_due > 0 else 0 total_princ_after_due = total_princ_after_due if total_princ_after_due > 0 else 0 sms_msg = "Dear "+ (loan_details.customer.name) + ", Your "+ loan_details.loan_application_product.product_name + " loan, is due: Princ: " + '{:0,.0f}'.format(total_princ_after_due) + ", Int: " + '{:0,.0f}'.format(total_interest_after_due) + ", Penalty: "+ '{:0,.0f}'.format(total_penalty) +". In "+ str(after_due_arrear_days) + " arrear days." sms_msg += '\n\nThanks for saving with '+ loan_details.organisation_branch.branch_organisation.name else: total_interest_before_due = total_interest_before_due if total_interest_before_due > 0 else 0 total_princ_before_due = total_princ_before_due if total_princ_before_due > 0 else 0 if before_due_date == 'today': sms_msg = "Dear "+ (loan_details.customer.name) + ", Your "+ loan_details.loan_application_product.product_name + " loan, is due today: Princ: "+ '{:0,.0f}'.format(total_princ_before_due) + ", Int: " + '{:0,.0f}'.format(total_interest_before_due) + ", Penalty: " + '{:0,.0f}'.format(total_penalty) sms_msg += '\n\nThanks for saving with '+ loan_details.organisation_branch.branch_organisation.name elif before_due_date: sms_msg = "Dear "+ (loan_details.customer.name) + ", Your "+ loan_details.loan_application_product.product_name + " loan, will be due on " + str(before_due_date) + " by: Princ: " + '{:0,.0f}'.format(total_princ_before_due) + ", Int: "+ '{:0,.0f}'.format(total_interest_before_due) sms_msg += '\n\nThanks for saving with '+ loan_details.organisation_branch.branch_organisation.name if sms_msg: current_customer_id = loan_details.customer.id data = {"sms_key":"loan_payment_reminder", "customer":loan_details.customer, "user":loan_details.loan_app_added_by, "branch_id":loan_details.organisation_branch.id, "sms_msg":sms_msg, "loan":loan_details} send_customer_sms(data) save_user_notification({ "heading": "Loan Payments Reminder", "message": f"Loan Payments Reminder has been sent to: {loan_details.customer.name} as at {loan_details.date_added.date()}", "branch":OrganisationBranch.objects.get(pk=loan_details.organisation_branch.id), "branch_name":loan_details.organisation_branch.name, "added_by":None, "last_updated_by":None, "key":"loan_notifications" }) organCombinedString = ','.join(cron_organisations) send_email('Loan payment reminders cron completed successfully for [ ' + organCombinedString + ' ]', 'Loan Payment Reminders Cron.' ) except Exception as e: send_email('Loan payment reminders cron failed ' + str(e) + ' for Customer with Id: ' + str(current_customer_id), 'Loan Payment Reminders Cron.' ) def process_loan_interest_waiver(loan_application, waiver_data): amount = waiver_data.get('amount') user = waiver_data.get('user') comment = waiver_data.get('comment') loan_repayment_schedules = LoanRepaymentSchedule.objects.filter(loan_application=loan_application, status="active").order_by('id') if not loan_repayment_schedules: return False # get waived off installements schedules_to_waive_off = [] interest_waived = float(amount) interest_waivered_bal = 0 for loan_repayment_schedule in loan_repayment_schedules: loan_payments_totals = LoanPayments.objects.filter(loan_application = loan_application, loan_repayment_schedule=loan_repayment_schedule, payment_status='normal').aggregate(total_int_paid=Sum('int_paid')) is_paid_off = 'false' # total schedule waived Interest schedule_interest_waived = LoanInterestWaivered.objects.filter(loan_repayment_schedule=loan_repayment_schedule, loan_application = loan_application).aggregate(total_schedule_interest_waived=Sum('amount'))['total_schedule_interest_waived'] total_schedule_interest_waived = round(schedule_interest_waived, 2) if schedule_interest_waived else 0 # get total interest paid, and comapare with waived, expected total_int_paid = round(float(loan_payments_totals['total_int_paid']), 2) if loan_payments_totals['total_int_paid'] is not None else 0 # spread interest waivered total_schedule_interest_waived = total_schedule_interest_waived + interest_waivered_bal _interest_waivered_bal = loan_repayment_schedule.interest_expected - (total_schedule_interest_waived + total_int_paid) if _interest_waivered_bal < 0: interest_waivered_bal = interest_waivered_bal + abs(_interest_waivered_bal) total_schedule_interest_waived = loan_repayment_schedule.interest_expected - total_int_paid if float(loan_repayment_schedule.interest_expected) <= float(total_int_paid + total_schedule_interest_waived): is_paid_off = 'true' if is_paid_off == 'false' and interest_waived > 0: interest_balance = round(loan_repayment_schedule.interest_expected - total_int_paid, 2) if float(interest_waived) == float(interest_balance): schedules_to_waive_off.append({"schedule":loan_repayment_schedule.id, "amount":interest_waived}) interest_waived = 0 break else: waive_amount = interest_balance if interest_waived > interest_balance else interest_waived schedules_to_waive_off.append({"schedule":loan_repayment_schedule.id, "amount":waive_amount}) if interest_waived - waive_amount > 0: interest_waived = round(interest_waived - waive_amount, 2) else: break # waive off selected installemnets if len(schedules_to_waive_off) > 0: count = 0 for schedule in schedules_to_waive_off: loan_repayment_schedule = LoanRepaymentSchedule.objects.get(pk=schedule['schedule']) LoanInterestWaivered.objects.create(loan_application=loan_application, loan_interest_waivered_added_by=user, loan_repayment_schedule=loan_repayment_schedule, amount=round(schedule['amount'], 2), comment=comment) count +=1 return True return False def process_loan_penalty_waiver(loan_application, waiver_data): amount = waiver_data.get('amount') user = waiver_data.get('user') comment = waiver_data.get('comment') loan_repayment_schedule = LoanRepaymentSchedule.objects.filter(loan_application=loan_application, status="active").order_by('id').first() if not loan_repayment_schedule: return False data = {"loan_application":loan_application, "loan_penalty_waivered_added_by":user, "loan_repayment_schedule":loan_repayment_schedule, "amount":amount, "comment":comment} LoanPenaltyWaivered.objects.create(**data) return True def process_loan_top_up(data): loan_topup_id = data.get('loan_topup_id') organisation_branch_id = data.get('organisation_branch_id') request = data.get('request') # disburse new loan loan_top_up = LoanTopUp.objects.get(pk=loan_topup_id) amount = float(loan_top_up.topup_amount ) + float(loan_top_up.principal_bal) disburse_method = loan_top_up.disburse_method send_sms = loan_top_up.send_sms voucher_no = loan_top_up.voucher_no loan_disbursement_date = loan_top_up.approval_disb_date loan_start_date = loan_top_up.approval_disb_date customer_id = loan_top_up.loan_application.customer.id account_id = loan_top_up.disbursement_account_id organisation_branch = OrganisationBranch.objects.get(pk=organisation_branch_id) previous_approval = LoanApplicationApproval.objects.filter(loan_application=loan_top_up.loan_application).first() selected_account = None if disburse_method == 'credit': saving_account = SavingAccount.objects.filter(id=account_id).first() if saving_account: selected_account = saving_account.account_product.accounts_chart.id elif disburse_method == 'cash': cash_account = CashAccounts.objects.filter(id=account_id).first() if cash_account: selected_account = cash_account.chart.id elif disburse_method == 'bank': bank_account = BankAccounts.objects.filter(id=account_id).first() if bank_account: selected_account = bank_account.chart.id if selected_account is not None: loan_application_data = {"loan_amount":amount, "loan_application_product":loan_top_up.loan_application.loan_application_product, "loan_app_added_by":request.user, "loan_officer":loan_top_up.loan_application.loan_officer, "reason":loan_top_up.comment, "app_grace_period":loan_top_up.grace_period, "grace_period_type":loan_top_up.grace_period_type, "customer":loan_top_up.loan_application.customer, "organisation_branch":organisation_branch, "loan_date":loan_top_up.approval_disb_date, "status":"pending", "int_rate":loan_top_up.int_rate, "int_method":loan_top_up.loan_application.int_method, "auto_payments":loan_top_up.auto_payment, "auto_pay_penalty":loan_top_up.loan_application.auto_pay_penalty, "loan_sector":loan_top_up.loan_application.loan_sector, "loan_period":loan_top_up.loan_period, "period_type":loan_top_up.period_type} loan_application = LoanApplication.objects.create(**loan_application_data) if loan_application: loan_application_approval_data = {"loan_application":loan_application, "int_rate":loan_top_up.int_rate, "loan_amount":amount, "loan_approval_added_by":request.user, "loan_period":loan_top_up.loan_period, "period_type":loan_top_up.period_type, "frequency_type":loan_top_up.period_type, "frequency":loan_top_up.frequency, "automatic_savings_pay":previous_approval.automatic_savings_pay, "reason":loan_top_up.comment, "app_grace_period":loan_top_up.grace_period, "grace_period_type":loan_top_up.grace_period_type, "approval_date":loan_top_up.approval_disb_date} loan_application_approval = LoanApplicationApproval.objects.create(**loan_application_approval_data) if loan_application_approval: loan_details = {"amount":amount, "disburse_method":disburse_method, "selected_account":selected_account, "send_sms":send_sms, "voucher_no":voucher_no, "loan_disbursement_date":loan_disbursement_date, "loan_start_date":loan_start_date, "customer_id":customer_id, "loan_application_id":loan_application.id, "account_id":account_id, "apply_charges":True } # print('*********************** about to call disbursement') disburse_loan = process_loan_disbursement(request, loan_details) # print('*********************** after calling disbursement') if disburse_loan.status_code == 200: loan_disbursement = LoanApplicationDisbursement.objects.filter(loan_application=loan_application).first() if loan_disbursement: interest, principal, total = loan_payment(request, loan_application.id) loan_disbursement.total_interest_expected = interest loan_disbursement.total_principal_expected = principal loan_disbursement.total_expected = total loan_disbursement.save() # make loan payment. now_date = timezone.now() principal_bal, interest_bal, penalty_bal, written_off_amount = loan_balance(loan_top_up.loan_application.id) payment_method = "offset" if disburse_method == 'credit' else disburse_method payment_details = {"amount_paid":principal_bal, "principal_paid":principal_bal, "int_paid":0, "penalty_paid":0, "payment_method":payment_method, "account":selected_account, "date_added":now_date, "voucher_no":voucher_no, "cheque":"", "loan_id":loan_top_up.loan_application.id, "account_id":account_id} payment = process_loan_payment(payment_details, request) if payment: # waive off intererst if interest_bal > 0: waiver_data = {"amount":interest_bal, "user":request.user, "comment":loan_top_up.comment} process_loan_interest_waiver(loan_top_up.loan_application, waiver_data) # waive the penalty if penalty_bal > 0: waiver_data = {"amount":penalty_bal, "user":request.user, "comment":loan_top_up.comment} process_loan_penalty_waiver(loan_top_up.loan_application, waiver_data) # apply the charge # credit_chart = OrganisationSubAccount.objects.get(pk=selected_account) # if not credit_chart: # return Response({"message":"No chart of account found"}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) # loanCustomCharges(loan_top_up.loan_application, amount, credit_chart,organisation_branch.branch_organisation_id,disburse_method,voucher_no,organisation_branch_id, loan_top_up.loan_application.loan_app_added_by_id,loan_start_date,loan_disbursement_date,account_id) # if send_sms: # f_amount = f"{float(loan_disbursement.loan_amount):,}" return True return False def delete_loan_by_id(loan_application_id, delete_type, reason_for_delete, user_id=None, branch=None): from django.utils import timezone from users.audit_log_helper import add_system_audit_trail now = timezone.now() df = dict(deleted=True, deleted_by_id=user_id, deleted_at=now) loan_obj = LoanApplication.objects.filter(id=loan_application_id).first() if loan_obj: if not branch: branch = loan_obj.organisation_branch if user_id and branch: try: from users.models import User user = User.objects.filter(pk=user_id).first() if user: add_system_audit_trail( 'transaction_management', 'delete_loan_application', f'Deleted Loan Application for {loan_obj.customer.name} Mem No: {loan_obj.customer.member_number}', reason_for_delete or '', {}, {}, user, branch ) except Exception: pass if loan_obj.status == 'pending' or delete_type == 'permanent': system_transactions = [] loan_main_transactions = [] loan_transactions = [] loan_payments = LoanPayments.objects.filter(loan_application=loan_obj).all() for loan_payment in loan_payments: loan_main_transactions.append(loan_payment.loan_main_transaction.id) system_transactions.append(loan_payment.loan_main_transaction.system_transaction.id) inter_branch = InterBranchTransactions.objects.filter(Q(source_transaction=loan_payment.loan_main_transaction.system_transaction) | Q(destination_transaction=loan_payment.loan_main_transaction.system_transaction)).first() if inter_branch: system_transactions.append(inter_branch.source_transaction.id) system_transactions.append(inter_branch.destination_transaction.id) SavingAccountTransactions.objects.filter(transaction=loan_payment.loan_main_transaction.system_transaction).update(**df) if loan_payment.loan_payment_transaction: loan_transactions.append(loan_payment.loan_payment_transaction.id) LoanPayments.objects.filter(id=loan_payment.id).update(**df) for loan_transaction in loan_transactions: LoanPaymentTransaction.objects.filter(id=loan_transaction).update(**df) for loan_main_transaction in loan_main_transactions: LoanMainTransactions.objects.filter(id=loan_main_transaction).update(**df) for system_transaction in system_transactions: SystemTransactions.objects.filter(id=system_transaction).update(**df) all_loan_transactions = LoanMainTransactions.objects.filter(loan_application=loan_obj) transactions_list = [] for all_loan_transaction in all_loan_transactions: transactions_list.append(all_loan_transaction.system_transaction.id) SavingAccountTransactions.objects.filter(transaction=all_loan_transaction.system_transaction).update(**df) inter_branch = InterBranchTransactions.objects.filter(Q(source_transaction=all_loan_transaction.system_transaction) | Q(destination_transaction=all_loan_transaction.system_transaction)).first() if inter_branch: transactions_list.append(inter_branch.source_transaction.id) transactions_list.append(inter_branch.destination_transaction.id) SystemTransactions.objects.filter(id__in=set(transactions_list)).update(**df) LoanMainTransactions.objects.filter(loan_application=loan_obj).update(**df) RescheduledLoans.objects.filter(loan_application=loan_obj).update(**df) LoanRepaymentSchedule.objects.filter(loan_application=loan_obj).update(**df) LoanPenalty.objects.filter(loan_application=loan_obj).update(**df) LoanPenaltyWaivered.objects.filter(loan_application=loan_obj).update(**df) LoanInterestWaivered.objects.filter(loan_application=loan_obj).update(**df) LoanWrittenOff.objects.filter(loan_application=loan_obj).update(**df) LoanTopUp.objects.filter(loan_application=loan_obj).update(**df) NonMemberLoanGuarantors.objects.filter(loan_application=loan_obj).update(**df) LoanApplicationDisbursement.objects.filter(loan_application=loan_obj).update(**df) LoanApplicationApproval.objects.filter(loan_application=loan_obj).update(**df) LoanApplicationWithHold.objects.filter(loan_application=loan_obj).update(**df) LoanIncomeSource.objects.filter(loan_application=loan_obj).update(**df) loan_guarantors = LoanGuarantors.objects.filter(loan_application=loan_obj) for loan_guarantor in loan_guarantors: LoanGuarantorsSecurity.objects.filter(loan_guarantor=loan_guarantor).update(**df) LoanGuarantors.objects.filter(loan_application=loan_obj).update(**df) LoanApplicationSecurity.objects.filter(loan_application=loan_obj).update(**df) loan_obj.deleted = True loan_obj.deleted_by_id = user_id loan_obj.deleted_at = now loan_obj.save() else: loan_obj.is_deleted = True loan_obj.reason_for_delete = reason_for_delete if user_id: loan_obj.deleted_by_id = user_id loan_obj.save() return True def refine_inter_branch_transactions(organisation_id, product_id): # principal loan_products = LoanProduct.objects.filter(organisation__id=organisation_id, id=product_id) for loan_product in loan_products: loan_applications = LoanApplication.objects.filter(loan_application_product=loan_product) for loan_application in loan_applications: # principal payments loan_main_payments = LoanMainTransactions.objects.filter(loan_application=loan_application, system_transaction__heading__startswith='Inter-branch', system_transaction__reference_no__startswith='ln-p') for loan_main_payment in loan_main_payments: system_payment = SystemTransactions.objects.filter(id=loan_main_payment.system_transaction.id).first() if system_payment: member_number = loan_main_payment.loan_application.customer.member_number second_transaction = SystemTransactions.objects.filter(reference_no=system_payment.reference_no, branch__id__in=[61,62], heading__icontains=member_number, amount=system_payment.amount).exclude(id=system_payment.id).first() if second_transaction: list_transactions = [second_transaction.id, system_payment.id] inter_branch_tran = InterBranchTransactions.objects.filter(Q(source_transaction=system_payment) | Q(destination_transaction=system_payment)).first() customer_account = None savings_transaction = SavingAccountTransactions.objects.filter(Q(transaction=system_payment) | Q(transaction=second_transaction)).first() if savings_transaction: customer_account = savings_transaction.customer_account else: customer_account = SavingAccount.objects.filter(account_customer=loan_main_payment.loan_application.customer).first() # similar branch ids post_type = None if second_transaction.branch.id == system_payment.branch.id: if loan_main_payment.loan_application.organisation_branch.id == customer_account.customer_branch.id: # delete repost as non inter-branch post_type = 'not_inter_branch' print('delete repost as inter-branch', system_payment.reference_no) else: post_type = 'inter_branch' print('delete repost as inter-branch', system_payment.reference_no) else: if inter_branch_tran: if inter_branch_tran.source_transaction.branch.id != customer_account.customer_branch.id or inter_branch_tran.destination_transaction.branch.id != loan_main_payment.loan_application.organisation_branch.id: print('delete repost as not Know', system_payment.reference_no) post_type = 'not_known' else: print('delete repost as not Know', system_payment.reference_no) post_type = 'not_known' # start posting if post_type == 'not_inter_branch': saving_transaction = SavingAccountTransactions.objects.filter(transaction__id__in=list_transactions, transaction__reference_no=system_payment.reference_no).first() if saving_transaction: current_transaction = saving_transaction.transaction # remove unwanted transaction if inter_branch_tran: inter_branch_tran.deleted = True; inter_branch_tran.deleted_at = timezone.now(); inter_branch_tran.save() # deleted_by not available in this context (no request) if loan_main_payment.system_transaction.id !=current_transaction.id: loan_main_payment.system_transaction = current_transaction loan_main_payment.save() for delete_id in list_transactions: if int(delete_id) != int(current_transaction.id): SystemTransactions.objects.filter(pk=delete_id).update(deleted=True, deleted_at=timezone.now()) loan_product = loan_application.loan_application_product.product_name heading = 'Loan principal payment: ('+ str(loan_application.customer.member_number) + '-' + loan_application.customer.name + '):' + loan_product current_transaction.branch = customer_account.customer_branch current_transaction.heading = heading current_transaction.debit_chart = customer_account.account_product.accounts_chart current_transaction.credit_chart = loan_application.loan_application_product.chart current_transaction.save() elif post_type == 'inter_branch': saving_transaction = SavingAccountTransactions.objects.filter(transaction__id__in=list_transactions, transaction__reference_no=system_payment.reference_no).first() if saving_transaction: current_transaction = saving_transaction.transaction # remove unwanted transaction if loan_main_payment.system_transaction.id !=current_transaction.id: loan_main_payment.system_transaction = current_transaction loan_main_payment.save() interbranch_chart = get_inter_branch_chart(customer_account.customer_branch, loan_application.organisation_branch) loan_product = loan_application.loan_application_product.product_name heading = 'Inter-branch Loan principal payment: ('+ str(loan_application.customer.member_number) + '-' + loan_application.customer.name + '):' + loan_product current_transaction.branch = customer_account.customer_branch current_transaction.heading = heading current_transaction.payment_method='offset' current_transaction.debit_chart = customer_account.account_product.accounts_chart current_transaction.credit_chart = interbranch_chart current_transaction.save() second_transaction_record = None for delete_id in list_transactions: if int(delete_id) != int(current_transaction.id): second_transaction_record = SystemTransactions.objects.get(pk=delete_id) second_transaction_record.branch = loan_application.organisation_branch second_transaction_record.heading = heading second_transaction_record.payment_method='settlement' second_transaction_record.debit_chart = interbranch_chart second_transaction_record.credit_chart = loan_application.loan_application_product.chart second_transaction_record.save() if second_transaction_record: if inter_branch_tran: inter_branch_tran.source_transaction = current_transaction inter_branch_tran.destination_transaction = second_transaction_record inter_branch_tran.save() else: data_obj = {"source_transaction":current_transaction, "destination_transaction":second_transaction_record, "added_by":current_transaction.added_by} new_inter_branch_tran = InterBranchTransactions.objects.create(**data_obj) elif post_type == 'not_known': if (loan_main_payment.loan_application.organisation_branch.id == customer_account.customer_branch.id): # not inter branch saving_transaction = SavingAccountTransactions.objects.filter(transaction__id__in=list_transactions, transaction__reference_no=system_payment.reference_no).first() if saving_transaction: current_transaction = saving_transaction.transaction # remove unwanted transaction if inter_branch_tran: inter_branch_tran.deleted = True; inter_branch_tran.deleted_at = timezone.now(); inter_branch_tran.save() # deleted_by not available in this context (no request) if loan_main_payment.system_transaction.id !=current_transaction.id: loan_main_payment.system_transaction = current_transaction loan_main_payment.save() for delete_id in list_transactions: if int(delete_id) != int(current_transaction.id): SystemTransactions.objects.filter(pk=delete_id).update(deleted=True, deleted_at=timezone.now()) loan_product = loan_application.loan_application_product.product_name heading = 'Loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product current_transaction.branch = customer_account.customer_branch current_transaction.heading = heading current_transaction.debit_chart = customer_account.account_product.accounts_chart current_transaction.credit_chart = loan_application.loan_application_product.chart current_transaction.save() else: saving_transaction = SavingAccountTransactions.objects.filter(transaction__id__in=list_transactions, transaction__reference_no=system_payment.reference_no).first() if saving_transaction: current_transaction = saving_transaction.transaction # remove unwanted transaction if loan_main_payment.system_transaction.id !=current_transaction.id: loan_main_payment.system_transaction = current_transaction loan_main_payment.save() interbranch_chart = get_inter_branch_chart(customer_account.customer_branch, loan_application.organisation_branch) loan_product = loan_application.loan_application_product.product_name heading = 'Inter-branch Loan principal payment: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product current_transaction.branch = customer_account.customer_branch current_transaction.heading = heading current_transaction.payment_method='offset' current_transaction.debit_chart = customer_account.account_product.accounts_chart current_transaction.credit_chart = interbranch_chart current_transaction.save() second_transaction_record = None for delete_id in list_transactions: if int(delete_id) != int(current_transaction.id): second_transaction_record = SystemTransactions.objects.get(pk=delete_id) second_transaction_record.branch = loan_application.organisation_branch second_transaction_record.heading = heading second_transaction_record.payment_method='settlement' second_transaction_record.debit_chart = interbranch_chart second_transaction_record.credit_chart = loan_application.loan_application_product.chart second_transaction_record.save() if second_transaction_record: if inter_branch_tran: inter_branch_tran.source_transaction = current_transaction inter_branch_tran.destination_transaction = second_transaction_record inter_branch_tran.save() else: data_obj = {"source_transaction":current_transaction, "destination_transaction":second_transaction_record, "added_by":current_transaction.added_by} new_inter_branch_tran = InterBranchTransactions.objects.create(**data_obj) else: print('Missing second record', system_payment.reference_no) # interest loan_products_interests = LoanProduct.objects.filter(organisation__id=organisation_id, id=product_id) for loan_product in loan_products_interests: loan_applications = LoanApplication.objects.filter(loan_application_product=loan_product) for loan_application in loan_applications: # principal payments loan_main_payments = LoanMainTransactions.objects.filter(loan_application=loan_application, system_transaction__heading__startswith='Inter-branch', transaction_type='InterestPayment', system_transaction__reference_no__startswith='ln-in-') for loan_main_payment in loan_main_payments: system_payment = SystemTransactions.objects.filter(id=loan_main_payment.system_transaction.id).first() if system_payment: member_number = loan_main_payment.loan_application.customer.member_number second_transaction = SystemTransactions.objects.filter(reference_no=system_payment.reference_no, branch__id__in=[61,62], heading__icontains=member_number, amount=system_payment.amount).exclude(id=system_payment.id).first() if second_transaction: list_transactions = [second_transaction.id, system_payment.id] inter_branch_tran = InterBranchTransactions.objects.filter(Q(source_transaction=system_payment) | Q(destination_transaction=system_payment)).first() customer_account = None savings_transaction = SavingAccountTransactions.objects.filter(Q(transaction=system_payment) | Q(transaction=second_transaction)).first() if savings_transaction: customer_account = savings_transaction.customer_account else: customer_account = SavingAccount.objects.filter(account_customer=loan_main_payment.loan_application.customer).first() # similar branch ids post_type = None if second_transaction.branch.id == system_payment.branch.id: if loan_main_payment.loan_application.organisation_branch.id == customer_account.customer_branch.id: # delete repost as non inter-branch post_type = 'not_inter_branch' print('delete repost as inter-branch', system_payment.reference_no) else: post_type = 'inter_branch' print('delete repost as inter-branch', system_payment.reference_no) else: if inter_branch_tran: if inter_branch_tran.source_transaction.branch.id != customer_account.customer_branch.id or inter_branch_tran.destination_transaction.branch.id != loan_main_payment.loan_application.organisation_branch.id: print('delete repost as not Know', system_payment.reference_no) post_type = 'not_known' else: print('delete repost as not Know', system_payment.reference_no) post_type = 'not_known' # start posting if post_type == 'not_inter_branch': saving_transaction = SavingAccountTransactions.objects.filter(transaction__id__in=list_transactions, transaction__reference_no=system_payment.reference_no).first() if saving_transaction: current_transaction = saving_transaction.transaction # remove unwanted transaction if inter_branch_tran: inter_branch_tran.deleted = True; inter_branch_tran.deleted_at = timezone.now(); inter_branch_tran.save() # deleted_by not available in this context (no request) if loan_main_payment.system_transaction.id !=current_transaction.id: loan_main_payment.system_transaction = current_transaction loan_main_payment.save() for delete_id in list_transactions: if int(delete_id) != int(current_transaction.id): SystemTransactions.objects.filter(pk=delete_id).update(deleted=True, deleted_at=timezone.now()) loan_product = loan_application.loan_application_product.product_name heading = 'Loan interest income: ('+ str(loan_application.customer.member_number) + '-' + loan_application.customer.name + '):' + loan_product current_transaction.branch = customer_account.customer_branch current_transaction.heading = heading current_transaction.debit_chart = customer_account.account_product.accounts_chart current_transaction.credit_chart = loan_application.loan_application_product.chart current_transaction.save() elif post_type == 'inter_branch': saving_transaction = SavingAccountTransactions.objects.filter(transaction__id__in=list_transactions, transaction__reference_no=system_payment.reference_no).first() if saving_transaction: current_transaction = saving_transaction.transaction # remove unwanted transaction if loan_main_payment.system_transaction.id !=current_transaction.id: loan_main_payment.system_transaction = current_transaction loan_main_payment.save() interbranch_chart = get_inter_branch_chart(customer_account.customer_branch, loan_application.organisation_branch) loan_product = loan_application.loan_application_product.product_name heading = 'Inter-branch Loan interest income: ('+ str(loan_application.customer.member_number) + '-' + loan_application.customer.name + '):' + loan_product current_transaction.branch = customer_account.customer_branch current_transaction.heading = heading current_transaction.payment_method='offset' current_transaction.debit_chart = customer_account.account_product.accounts_chart current_transaction.credit_chart = interbranch_chart current_transaction.save() second_transaction_record = None for delete_id in list_transactions: if int(delete_id) != int(current_transaction.id): second_transaction_record = SystemTransactions.objects.get(pk=delete_id) second_transaction_record.branch = loan_application.organisation_branch second_transaction_record.heading = heading second_transaction_record.payment_method='settlement' second_transaction_record.debit_chart = interbranch_chart second_transaction_record.credit_chart = loan_application.loan_application_product.chart second_transaction_record.save() if second_transaction_record: if inter_branch_tran: inter_branch_tran.source_transaction = current_transaction inter_branch_tran.destination_transaction = second_transaction_record inter_branch_tran.save() else: data_obj = {"source_transaction":current_transaction, "destination_transaction":second_transaction_record, "added_by":current_transaction.added_by} new_inter_branch_tran = InterBranchTransactions.objects.create(**data_obj) elif post_type == 'not_known': if (loan_main_payment.loan_application.organisation_branch.id == customer_account.customer_branch.id): # not inter branch saving_transaction = SavingAccountTransactions.objects.filter(transaction__id__in=list_transactions, transaction__reference_no=system_payment.reference_no).first() if saving_transaction: current_transaction = saving_transaction.transaction # remove unwanted transaction if inter_branch_tran: inter_branch_tran.deleted = True; inter_branch_tran.deleted_at = timezone.now(); inter_branch_tran.save() # deleted_by not available in this context (no request) if loan_main_payment.system_transaction.id !=current_transaction.id: loan_main_payment.system_transaction = current_transaction loan_main_payment.save() for delete_id in list_transactions: if int(delete_id) != int(current_transaction.id): SystemTransactions.objects.filter(pk=delete_id).update(deleted=True, deleted_at=timezone.now()) loan_product = loan_application.loan_application_product.product_name heading = 'Loan interest income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product current_transaction.branch = customer_account.customer_branch current_transaction.heading = heading current_transaction.debit_chart = customer_account.account_product.accounts_chart current_transaction.credit_chart = loan_application.loan_application_product.chart current_transaction.save() else: saving_transaction = SavingAccountTransactions.objects.filter(transaction__id__in=list_transactions, transaction__reference_no=system_payment.reference_no).first() if saving_transaction: current_transaction = saving_transaction.transaction # remove unwanted transaction if loan_main_payment.system_transaction.id !=current_transaction.id: loan_main_payment.system_transaction = current_transaction loan_main_payment.save() interbranch_chart = get_inter_branch_chart(customer_account.customer_branch, loan_application.organisation_branch) loan_product = loan_application.loan_application_product.product_name heading = 'Inter-branch Loan interest income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product current_transaction.branch = customer_account.customer_branch current_transaction.heading = heading current_transaction.payment_method='offset' current_transaction.debit_chart = customer_account.account_product.accounts_chart current_transaction.credit_chart = interbranch_chart current_transaction.save() second_transaction_record = None for delete_id in list_transactions: if int(delete_id) != int(current_transaction.id): second_transaction_record = SystemTransactions.objects.get(pk=delete_id) second_transaction_record.branch = loan_application.organisation_branch second_transaction_record.heading = heading second_transaction_record.payment_method='settlement' second_transaction_record.debit_chart = interbranch_chart second_transaction_record.credit_chart = loan_application.loan_application_product.chart second_transaction_record.save() if second_transaction_record: if inter_branch_tran: inter_branch_tran.source_transaction = current_transaction inter_branch_tran.destination_transaction = second_transaction_record inter_branch_tran.save() else: data_obj = {"source_transaction":current_transaction, "destination_transaction":second_transaction_record, "added_by":current_transaction.added_by} new_inter_branch_tran = InterBranchTransactions.objects.create(**data_obj) else: print('Missing second record', system_payment.reference_no) # penalty loan_products_penalties = LoanProduct.objects.filter(organisation__id=organisation_id, id=product_id) for loan_product in loan_products_penalties: loan_applications = LoanApplication.objects.filter(loan_application_product=loan_product) for loan_application in loan_applications: # principal payments loan_main_payments = LoanMainTransactions.objects.filter(loan_application=loan_application, system_transaction__heading__startswith='Inter-branch', transaction_type='PenaltyPayment', system_transaction__reference_no__startswith='ln-in-') for loan_main_payment in loan_main_payments: system_payment = SystemTransactions.objects.filter(id=loan_main_payment.system_transaction.id).first() if system_payment: member_number = loan_main_payment.loan_application.customer.member_number second_transaction = SystemTransactions.objects.filter(reference_no=system_payment.reference_no, branch__id__in=[61,62], heading__icontains=member_number, amount=system_payment.amount).exclude(id=system_payment.id).first() if second_transaction: list_transactions = [second_transaction.id, system_payment.id] inter_branch_tran = InterBranchTransactions.objects.filter(Q(source_transaction=system_payment) | Q(destination_transaction=system_payment)).first() customer_account = None savings_transaction = SavingAccountTransactions.objects.filter(Q(transaction=system_payment) | Q(transaction=second_transaction)).first() if savings_transaction: customer_account = savings_transaction.customer_account else: customer_account = SavingAccount.objects.filter(account_customer=loan_main_payment.loan_application.customer).first() # similar branch ids post_type = None if second_transaction.branch.id == system_payment.branch.id: if loan_main_payment.loan_application.organisation_branch.id == customer_account.customer_branch.id: # delete repost as non inter-branch post_type = 'not_inter_branch' print('delete repost as inter-branch', system_payment.reference_no) else: post_type = 'inter_branch' print('delete repost as inter-branch', system_payment.reference_no) else: if inter_branch_tran: if inter_branch_tran.source_transaction.branch.id != customer_account.customer_branch.id or inter_branch_tran.destination_transaction.branch.id != loan_main_payment.loan_application.organisation_branch.id: print('delete repost as not Know', system_payment.reference_no) post_type = 'not_known' else: print('delete repost as not Know', system_payment.reference_no) post_type = 'not_known' # start posting if post_type == 'not_inter_branch': saving_transaction = SavingAccountTransactions.objects.filter(transaction__id__in=list_transactions, transaction__reference_no=system_payment.reference_no).first() if saving_transaction: current_transaction = saving_transaction.transaction # remove unwanted transaction if inter_branch_tran: inter_branch_tran.deleted = True; inter_branch_tran.deleted_at = timezone.now(); inter_branch_tran.save() # deleted_by not available in this context (no request) if loan_main_payment.system_transaction.id !=current_transaction.id: loan_main_payment.system_transaction = current_transaction loan_main_payment.save() for delete_id in list_transactions: if int(delete_id) != int(current_transaction.id): SystemTransactions.objects.filter(pk=delete_id).update(deleted=True, deleted_at=timezone.now()) loan_product = loan_application.loan_application_product.product_name heading = 'Loan penalty income: ('+ str(loan_application.customer.member_number) + '-' + loan_application.customer.name + '):' + loan_product current_transaction.branch = customer_account.customer_branch current_transaction.heading = heading current_transaction.debit_chart = customer_account.account_product.accounts_chart current_transaction.credit_chart = loan_application.loan_application_product.chart current_transaction.save() elif post_type == 'inter_branch': saving_transaction = SavingAccountTransactions.objects.filter(transaction__id__in=list_transactions, transaction__reference_no=system_payment.reference_no).first() if saving_transaction: current_transaction = saving_transaction.transaction # remove unwanted transaction if loan_main_payment.system_transaction.id !=current_transaction.id: loan_main_payment.system_transaction = current_transaction loan_main_payment.save() interbranch_chart = get_inter_branch_chart(customer_account.customer_branch, loan_application.organisation_branch) loan_product = loan_application.loan_application_product.product_name heading = 'Inter-branch Loan penalty income: ('+ str(loan_application.customer.member_number) + '-' + loan_application.customer.name + '):' + loan_product current_transaction.branch = customer_account.customer_branch current_transaction.heading = heading current_transaction.payment_method='offset' current_transaction.debit_chart = customer_account.account_product.accounts_chart current_transaction.credit_chart = interbranch_chart current_transaction.save() second_transaction_record = None for delete_id in list_transactions: if int(delete_id) != int(current_transaction.id): second_transaction_record = SystemTransactions.objects.get(pk=delete_id) second_transaction_record.branch = loan_application.organisation_branch second_transaction_record.heading = heading second_transaction_record.payment_method='settlement' second_transaction_record.debit_chart = interbranch_chart second_transaction_record.credit_chart = loan_application.loan_application_product.chart second_transaction_record.save() if second_transaction_record: if inter_branch_tran: inter_branch_tran.source_transaction = current_transaction inter_branch_tran.destination_transaction = second_transaction_record inter_branch_tran.save() else: data_obj = {"source_transaction":current_transaction, "destination_transaction":second_transaction_record, "added_by":current_transaction.added_by} new_inter_branch_tran = InterBranchTransactions.objects.create(**data_obj) elif post_type == 'not_known': if (loan_main_payment.loan_application.organisation_branch.id == customer_account.customer_branch.id): # not inter branch saving_transaction = SavingAccountTransactions.objects.filter(transaction__id__in=list_transactions, transaction__reference_no=system_payment.reference_no).first() if saving_transaction: current_transaction = saving_transaction.transaction # remove unwanted transaction if inter_branch_tran: inter_branch_tran.deleted = True; inter_branch_tran.deleted_at = timezone.now(); inter_branch_tran.save() # deleted_by not available in this context (no request) if loan_main_payment.system_transaction.id !=current_transaction.id: loan_main_payment.system_transaction = current_transaction loan_main_payment.save() for delete_id in list_transactions: if int(delete_id) != int(current_transaction.id): SystemTransactions.objects.filter(pk=delete_id).update(deleted=True, deleted_at=timezone.now()) loan_product = loan_application.loan_application_product.product_name heading = 'Loan penalty income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product current_transaction.branch = customer_account.customer_branch current_transaction.heading = heading current_transaction.debit_chart = customer_account.account_product.accounts_chart current_transaction.credit_chart = loan_application.loan_application_product.chart current_transaction.save() else: saving_transaction = SavingAccountTransactions.objects.filter(transaction__id__in=list_transactions, transaction__reference_no=system_payment.reference_no).first() if saving_transaction: current_transaction = saving_transaction.transaction # remove unwanted transaction if loan_main_payment.system_transaction.id !=current_transaction.id: loan_main_payment.system_transaction = current_transaction loan_main_payment.save() interbranch_chart = get_inter_branch_chart(customer_account.customer_branch, loan_application.organisation_branch) loan_product = loan_application.loan_application_product.product_name heading = 'Inter-branch Loan penalty income: ('+ loan_application.customer.member_number + '-' + loan_application.customer.name + '):' + loan_product current_transaction.branch = customer_account.customer_branch current_transaction.heading = heading current_transaction.payment_method='offset' current_transaction.debit_chart = customer_account.account_product.accounts_chart current_transaction.credit_chart = interbranch_chart current_transaction.save() second_transaction_record = None for delete_id in list_transactions: if int(delete_id) != int(current_transaction.id): second_transaction_record = SystemTransactions.objects.get(pk=delete_id) second_transaction_record.branch = loan_application.organisation_branch second_transaction_record.heading = heading second_transaction_record.payment_method='settlement' second_transaction_record.debit_chart = interbranch_chart second_transaction_record.credit_chart = loan_application.loan_application_product.chart second_transaction_record.save() if second_transaction_record: if inter_branch_tran: inter_branch_tran.source_transaction = current_transaction inter_branch_tran.destination_transaction = second_transaction_record inter_branch_tran.save() else: data_obj = {"source_transaction":current_transaction, "destination_transaction":second_transaction_record, "added_by":current_transaction.added_by} new_inter_branch_tran = InterBranchTransactions.objects.create(**data_obj) else: print('Missing second record', system_payment.reference_no) return True def refine_inter_branch_transactions_income(organisation_id, product_id): response = [] # loan_products_interests = LoanProduct.objects.filter(organisation__id=organisation_id, id=product_id) # for loan_product in loan_products_interests: # loan_applications = LoanApplication.objects.filter(loan_application_product=loan_product) # for loan_application in loan_applications: # loan_main_payments = LoanMainTransactions.objects.filter(loan_application=loan_application, system_transaction__heading__startswith='Inter-branch', transaction_type='InterestPayment', system_transaction__reference_no__startswith='ln-in-') # for loan_main_payment in loan_main_payments: # system_payment = SystemTransactions.objects.filter(id=loan_main_payment.system_transaction.id).first() # if system_payment: # inter_branch_tran = InterBranchTransactions.objects.filter(Q(source_transaction=system_payment) | Q(destination_transaction=system_payment)).first() # if inter_branch_tran: # customer_account = None # savings_transaction = SavingAccountTransactions.objects.filter(Q(transaction=inter_branch_tran.source_transaction) | Q(transaction=inter_branch_tran.destination_transaction)).first() # if savings_transaction: # customer_account = savings_transaction.customer_account # else: # customer_account = SavingAccount.objects.filter(account_customer=loan_main_payment.loan_application.customer).first() # if inter_branch_tran.destination_transaction.credit_chart.id == loan_application.loan_application_product.chart.id and inter_branch_tran.source_transaction.debit_chart.id == customer_account.account_product.accounts_chart.id: # system_tran = SystemTransactions.objects.filter(id=inter_branch_tran.destination_transaction.id).first() # if system_tran and loan_application.loan_application_product.interest_income_chart: # # system_tran.credit_chart = loan_application.loan_application_product.interest_income_chart # # system_tran.save() # response.append({"id":system_tran.id, "ref":system_tran.reference_no}) # loan_products_penalities = LoanProduct.objects.filter(organisation__id=organisation_id, id=product_id) # for loan_product in loan_products_penalities: # loan_applications = LoanApplication.objects.filter(loan_application_product=loan_product) # for loan_application in loan_applications: # loan_main_payments = LoanMainTransactions.objects.filter(loan_application=loan_application, system_transaction__heading__startswith='Inter-branch', transaction_type='PenaltyPayment', system_transaction__reference_no__startswith='ln-in-') # for loan_main_payment in loan_main_payments: # system_payment = SystemTransactions.objects.filter(id=loan_main_payment.system_transaction.id).first() # if system_payment: # inter_branch_tran = InterBranchTransactions.objects.filter(Q(source_transaction=system_payment) | Q(destination_transaction=system_payment)).first() # if inter_branch_tran: # customer_account = None # savings_transaction = SavingAccountTransactions.objects.filter(Q(transaction=inter_branch_tran.source_transaction) | Q(transaction=inter_branch_tran.destination_transaction)).first() # if savings_transaction: # customer_account = savings_transaction.customer_account # else: # customer_account = SavingAccount.objects.filter(account_customer=loan_main_payment.loan_application.customer).first() # if inter_branch_tran.destination_transaction.credit_chart.id == loan_application.loan_application_product.chart.id and inter_branch_tran.source_transaction.debit_chart.id == customer_account.account_product.accounts_chart.id: # system_tran = SystemTransactions.objects.filter(id=inter_branch_tran.destination_transaction.id).first() # if system_tran and loan_application.loan_application_product.penalty_income_chart: # system_tran.credit_chart = loan_application.loan_application_product.penalty_income_chart # system_tran.save() systems = SystemTransactions.objects.filter( heading__startswith='Inter-branch', reference_no__startswith='ln-d', branch__id__in = [27,29,30,31]) for system in systems: inter_branch = InterBranchTransactions.objects.filter(Q(source_transaction=system) | Q(destination_transaction=system)) if not inter_branch: second_transaction = SystemTransactions.objects.filter(reference_no=system.reference_no, branch__id__in=[27,29,30,31], heading=system.heading, amount=system.amount).exclude(id=system.id).first() if second_transaction: list_tran = sorted([system.id, second_transaction.id]) first_tran = SystemTransactions.objects.filter(id=list_tran[0]).first() sec_tran = SystemTransactions.objects.filter(id=list_tran[1]).first() InterBranchTransactions.objects.create(source_transaction=first_tran, destination_transaction=sec_tran, added_by=system.added_by, date_added=system.date_added) return response def get_loan_payment_type_desc(key): transaction_types = { 'LoanDisbursement':'Loan Disbursement', 'LoanPayment':'Loan Payment', 'InterestPayment':'Interest Payment', 'PenaltyPayment':'Penalty Payment', 'LoanPrincipalPayment':'Loan Principal Payment', 'LoanWriteOff':'Loan Write Off', 'LoanPenaltyWaivered':'Loan Penalty Waivered', 'LoanInterestWaivered':'Loan Interest Waivered', 'LoanCustomCharge':'Loan custom charge', 'LoanRecovery':'Loan Recovered' } if key in transaction_types.keys(): return transaction_types[key] return 'Payment' def get_loan_arrears_details(loan_id, as_at=None, payment_transaction_id=None): arrear_days = 0 total_principal = 0 total_interest = 0 loan_disbursement = LoanApplicationDisbursement.objects.filter(loan_application__id=loan_id).first() if not payment_transaction_id and as_at: count = 0 loan_application_schedules = LoanRepaymentSchedule.objects.filter(loan_application__id=loan_id, expected_date__date__lt=as_at, status='active' ).order_by('id') for loan_application_schedule in loan_application_schedules: schedule_payments = LoanPayments.objects.filter(loan_repayment_schedule=loan_application_schedule, loan_application__id=loan_id, payment_status='normal', payment_date__date__lt=as_at).aggregate(total_int_paid=Sum('int_paid'), total_princ_paid=Sum('princ_paid')) total_int_paid = schedule_payments['total_int_paid'] if schedule_payments['total_int_paid'] else 0 total_princ_paid = schedule_payments['total_princ_paid'] if schedule_payments['total_princ_paid'] else 0 interest_waivered = LoanInterestWaivered.objects.filter(loan_application__id=loan_id, date_added__date__lt=as_at,loan_repayment_schedule=loan_application_schedule).aggregate(total=Sum('amount'))['total'] interest_waivered = interest_waivered if interest_waivered else 0 # update total paid interest total_int_paid += interest_waivered if float(total_princ_paid) != loan_application_schedule.principal_expected or float(total_int_paid) != loan_application_schedule.interest_expected: if count == 0: arrear_grace_period = loan_disbursement.arrear_grace_period if loan_disbursement.arrear_grace_period > 0 else loan_disbursement.loan_application.loan_application_product.arrears_period arrears_period_type = loan_disbursement.arrears_period_type if loan_disbursement.arrear_grace_period > 0 else loan_disbursement.loan_application.loan_application_product.arrears_period_type due_date = loan_schedule_due_date(loan_application_schedule.expected_date, arrear_grace_period, arrears_period_type) eat_timezone = pytz.timezone("Africa/Nairobi") loan_due_date = due_date.astimezone(eat_timezone) loan_payment_date = datetime.strptime(as_at, "%Y-%m-%d").astimezone(eat_timezone) arrear_days = (loan_payment_date - loan_due_date).days total_principal += (loan_application_schedule.principal_expected - total_princ_paid) total_interest += (loan_application_schedule.interest_expected - total_int_paid) count+=1 elif payment_transaction_id: payment_transaction = LoanPaymentTransaction.objects.filter(id=payment_transaction_id).first() if payment_transaction: loan_payments = LoanPayments.objects.filter(loan_payment_transaction=payment_transaction, payment_status='normal').order_by('id') for loan_payment in loan_payments: arrear_grace_period = loan_disbursement.arrear_grace_period if loan_disbursement.arrear_grace_period > 0 else loan_disbursement.loan_application.loan_application_product.arrears_period arrears_period_type = loan_disbursement.arrears_period_type if loan_disbursement.arrear_grace_period > 0 else loan_disbursement.loan_application.loan_application_product.arrears_period_type if loan_payment.loan_repayment_schedule: due_date = loan_schedule_due_date(loan_payment.loan_repayment_schedule.expected_date, arrear_grace_period, arrears_period_type) eat_timezone = pytz.timezone("Africa/Nairobi") loan_due_date = due_date.astimezone(eat_timezone) loan_payment_date = payment_transaction.payment_date.astimezone(eat_timezone) past_days = (loan_payment_date - loan_due_date).days if past_days > 0: arrear_days = past_days break return {"arrear_days":arrear_days, "total_principal":total_principal, "total_interest":total_interest } def loanCustomCharges(loan_application, loan_amount, credit_chart, organisation_id, disburse_method, voucher_no, branch_id, added_by, date_added, loan_disbursement_date, account_id): total_charges = 0 # received_amount = amount received_amount = loan_amount charges = LoanProductCharges.objects.filter(loan_product=loan_application.loan_application_product, is_deleted=False).all() for charge in charges: is_apply_charge = False if int(charge.below_limit_amount) > 0 and loan_amount <= charge.below_limit_amount: is_apply_charge = True elif int(charge.range_min_amount) > 0 and int(charge.range_max_amount) > 0 and loan_amount >= charge.range_min_amount and loan_amount <= charge.range_max_amount: is_apply_charge = True elif int(charge.above_limit_amount) > 0 and loan_amount >= charge.above_limit_amount: is_apply_charge = True elif int(charge.below_limit_amount) == 0 and int(charge.range_min_amount) == 0 and int(charge.range_max_amount) == 0 and int(charge.above_limit_amount) == 0: is_apply_charge = True if is_apply_charge: heading = charge.name charge_amount = float(charge.amount) if charge.apply_type == 'percent': charge_amount = round( ( float(charge.amount) / 100 ) * float(loan_amount)) if charge.max_charge_cap and charge_amount > float(charge.max_charge_cap): charge_amount = float(charge.max_charge_cap) reference_no = generate_reference_no(credit_chart.account_line, organisation_id, 'ln-int') transaction = SystemTransactions.objects.create(amount=charge_amount, heading=heading, reference_no=reference_no, payment_method=disburse_method,voucher_no=voucher_no, debit_chart_id=credit_chart.id, credit_chart_id=charge.chart.id, branch_id=branch_id, added_by=added_by, date_added=date_added, record_date=loan_disbursement_date) if transaction: total_charges += charge_amount data = { "heading":heading, "amount":charge_amount, "payment_method":disburse_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":voucher_no, "transaction_type":'LoanCustomCharge', "loan_main_transaction_added_by":added_by, "payment_date":loan_disbursement_date, "system_transaction":transaction, "date_added":date_added } LoanMainTransactions.objects.create(**data) if disburse_method == 'credit' and transaction: saved_transaction_fields = { "transaction_type":'withdrawal', "customer_account_id":account_id, "transaction_id":transaction.id } saved_trans = SavingAccountTransactions.objects.create(**saved_transaction_fields) if loan_application.loan_group and saved_trans: membership = GroupMembership.objects.filter(member=loan_application.customer,group=loan_application.loan_group, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": saved_trans } GroupSavingTransaction.objects.create(**group_trans_field)