from django.conf import settings from decouple import config import boto3 from botocore.exceptions import NoCredentialsError from botocore.exceptions import ClientError from botocore.client import Config from django.utils import timezone import datetime as datetime_timedelta from django.utils.timezone import make_aware from questbanker_api.utils import send_email from django.db.models import Q, Sum import logging from loans.models import * from .models import * from loans.helper import generate_loan_schedules, loan_payment,round_off_amount, process_loan_payment, auto_loan_repayment, loan_balance, loan_schedules_dues, loan_schedules_with_payments from ledgers.ledgers_helper import generate_reference_no, get_chart_of_account_by_code from savings.models import SavingAccount, SavingsProduct from savings.savings_helper import generate_saving_account_code, get_account_balance from locations.models import CustomerAddress from shares.models import * import os import time import pytz import tempfile import zipfile from notifications.notifications_helper import * from openpyxl import Workbook from django.db import connection from datetime import date def create_presigned_url(object_name, file_name, extension= 'png', expiration=9600): """Generate a presigned URL to share an S3 object :param object_name: string :param expiration: Time in seconds for the presigned URL to remain valid :return: Presigned URL as string. If error, returns None. """ # Generate a presigned URL for the S3 object s3_client = boto3.client('s3', aws_access_key_id=settings.AWS_ACCESS_KEY_ID, aws_secret_access_key=settings.AWS_SECRET_ACCESS_KEY, config=Config(signature_version=settings.AWS_S3_SIGNATURE_VERSION), region_name=settings.AWS_S3_REGION_NAME) try: response = s3_client.generate_presigned_url('get_object', Params={'Bucket': settings.AWS_STORAGE_BUCKET_NAME, 'ResponseContentDisposition': f"attachment; filename = {file_name + '.'+ extension}", 'Key': object_name}, ExpiresIn=expiration) except ClientError as e: logging.error(e) return None # The response contains the presigned URL return response def upload_file_to_aws(local_file, s3_file): s3 = boto3.client('s3', aws_access_key_id=settings.AWS_ACCESS_KEY_ID, aws_secret_access_key=settings.AWS_SECRET_ACCESS_KEY) try: response = s3.upload_file(local_file, settings.AWS_STORAGE_BUCKET_NAME, s3_file) upload_url = f"https://{settings.AWS_STORAGE_BUCKET_NAME}.s3.{settings.AWS_S3_REGION_NAME}.amazonaws.com/{s3_file}" return upload_url except FileNotFoundError: print("The file was not found") return False except NoCredentialsError: print("Credentials not available") return False def customer_obligation_transactions(): try: today = date.today() obligations = CustomerObligations.objects.filter(is_active=True).order_by('id') for obligation in obligations: if obligation.start_day <= today.day <= obligation.end_day: if obligation.obligation_choice == 'voluntary': customers = CustomerObligationSubscription.objects.filter(obligation = obligation, is_active=True) for customer in customers: already_deducted = CustomerObligationTransactions.objects.filter( customer_obligation=obligation, customer_transaction=customer.customer, date__year=today.year, date__month=today.month ).count() if already_deducted < 2: # process deduction process_customer_obligation(obligation, customer.customer, customer) else: customers = Customer.objects.filter(customer_branch__branch_organisation=obligation.organisation) for customer in customers: already_deducted = CustomerObligationTransactions.objects.filter( customer_obligation=obligation, customer_transaction=customer, date__year=today.year, date__month=today.month ).count() if already_deducted < 2: # process deduction process_customer_obligation(obligation, customer, None) except Exception as e: send_email('Programmed saving Cash transfer cron failed ' + str(e), 'Programmed saving Cash transfer Cron.' ) def process_loan_obligations(customer): try: current = timezone.now() as_at = current.strftime('%Y-%m-%d') organisation_id = customer.customer_branch.branch_organisation.id extra_filters = f"date(lad.loan_disbursement_date at time zone ''Africa/Nairobi'') <= ''{as_at}''" loan_filter = f"{extra_filters}" with connection.cursor() as cursor: query = f""" SELECT json_build_object( 'id', id, 'princ_expected', princ_expected, 'int_expected', int_expected, 'int_waivered', int_waivered, 'princ_paid', princ_paid, 'int_paid', int_paid, 'int_paid_with_waiver', int_paid + int_waivered, 'total_paid', total_paid, 'penalty_paid', penalty_paid, 'total_paid_with_penalty', total_paid + penalty_paid, 'princ_bal', princ_expected - princ_paid, 'int_bal', int_expected - (int_paid + int_waivered), 'penalty_bal', total_penalty - penalty_paid, 'total_bal', ((int_expected + princ_expected) - total_paid), 'total_bal_with_penalty', ((int_expected + princ_expected + total_penalty) - (total_paid + penalty_paid)), 'loan_officer_full_name', loan_officer_full_name ) AS loan_data FROM public.get_loan_tracking_data({organisation_id}, '{as_at}', '{loan_filter}') ORDER BY branch_id, loan_officer_id; """ cursor.execute(query) rows = cursor.fetchall() # Convert to list of loan objects # loans = [row[0] for row in rows] customer_account = SavingAccount.objects.filter(account_customer=customer, is_active=True, status='active').order_by('id').first() if customer_account: # Print IDs for each loan for loan in rows: loan_id = loan[0]['id'] principal_bal, interest_bal, penalty_bal, written_off_amount = loan_balance(loan_id) if principal_bal + interest_bal + penalty_bal > 5: schedule_due = loan_schedules_with_payments(loan_id)['schedule_due'] if schedule_due: total = 0 total_pen = schedule_due['penalty_expected'] total_int = schedule_due['interest_expected'] total_princ = schedule_due['principal_expected'] payment_details = {"amount_paid":0, "principal_paid":0, "int_paid":0, "penalty_paid":0, "payment_method":'offset', "account":customer_account.account_product.accounts_chart.id, "date_added":as_at, "voucher_no":'', "cheque":'', "loan_id":loan_id, "account_id":customer_account.id, "organisation_id":organisation_id, "organisation_branch_id": customer.customer_branch.id} account_balance = get_account_balance(customer_account)['balance_raw'] if account_balance > 0 and account_balance - total_pen >= 0: payment_details['penalty_paid'] = total_pen account_balance = account_balance - total_pen total = total + total_pen if account_balance > 0 and account_balance - total_int >= 0: payment_details['int_paid'] = total_int account_balance = account_balance - total_int total = total + total_int if account_balance > 0 and account_balance - total_princ >= 0: payment_details['principal_paid'] = total_princ account_balance = account_balance - total_princ total = total + total_princ payment_details['amount_paid'] = total if total > 0: process_loan_payment(payment_details, None) next_schedule = loan_schedules_with_payments(loan_id)['next_schedule'] if next_schedule: total = 0 total_int = next_schedule['interest_expected'] total_princ = next_schedule['principal_expected'] account_balance = get_account_balance(customer_account)['balance_raw'] payment_details = {"amount_paid":0, "principal_paid":0, "int_paid":0, "penalty_paid":0, "payment_method":'offset', "account":customer_account.account_product.accounts_chart.id, "date_added":as_at, "voucher_no":'', "cheque":'', "loan_id":loan_id, "account_id":customer_account.id, "organisation_id":organisation_id, "organisation_branch_id": customer.customer_branch.id} if account_balance > 0 and account_balance - total_int >= 0: payment_details['int_paid'] = total_int account_balance = account_balance - total_int total = total + total_int if account_balance > 0 and account_balance - total_princ >= 0: payment_details['principal_paid'] = total_princ account_balance = account_balance - total_princ total = total + total_princ if total > 0: process_loan_payment(payment_details, None) except Exception as e: send_email('Programmed saving Cash transfer cron failed ' + str(e), 'Programmed saving Cash transfer Cron.' ) def process_customer_obligation(obligation, customer, subscription = None): try: if obligation.obligation_type == 'loans': process_loan_obligations(customer) if obligation.obligation_type == 'savings': source_chart = obligation.source_chart destination_chart = obligation.destination_chart amount = obligation.amount if subscription: source_chart = subscription.customer_source_chart amount = subscription.amount customer_account = SavingAccount.objects.filter(account_customer=customer, account_product__accounts_chart=source_chart, is_active=True, status='active').first() customer_dest_account = SavingAccount.objects.filter(account_customer=customer, account_product__accounts_chart=destination_chart, is_active=True, status='active').first() if source_chart and destination_chart and amount > 0 and customer_account and customer_dest_account: account_bal = get_account_balance(customer_account) account_balance = account_bal["balance_raw"] if account_bal and account_bal["balance_raw"] > 0 else 0 if account_balance > 0 and account_balance >= amount: chart = OrganisationSubAccount.objects.get(account_code='sys-215', account_organisation_id=customer.customer_branch.branch_organisation.id) productname = obligation.name heading = f'Monthly deduction ({productname}): for {customer.name} - {customer.old_member_number if customer.old_member_number else customer.member_number}' reference_no = generate_reference_no(source_chart.account_line, customer.customer_branch.branch_organisation.id,'sav-tr') data = {"amount": amount, "heading": heading, "payment_method":"offset", "reference_no":reference_no, "credit_chart":chart, "debit_chart": source_chart, "branch_id": customer_account.customer_branch.id, "added_by":None } transaction = SystemTransactions.objects.create(**data) transaction.save() if transaction: saved_transaction_fields = { "transaction_type":'transfer', "customer_account_id":customer_account.id, "transaction_id":transaction.id } saving_1 = SavingAccountTransactions.objects.create(**saved_transaction_fields) receiver_obj = { "heading":heading, "coment":heading, "amount":amount, "debit_chart":chart, "credit_chart":destination_chart, "reference_no":reference_no, "voucher_no": "", "payment_method":"offset", "added_by":None, "branch":customer_account.customer_branch } receiver_transaction = SystemTransactions.objects.create(**receiver_obj) if receiver_transaction: saving_fields = { "transaction_type":"transfer", "transaction":receiver_transaction, "customer_account":customer_dest_account } saving_2 = SavingAccountTransactions.objects.create(**saving_fields) # Save transfer transactions details mapping if saving_1 and saving_2: transfer_fields = { "sender_transaction": saving_1, "reciever_transaction": saving_2 } TransferTransactions.objects.create(**transfer_fields) obligationTran = {"customer_transaction": customer, "customer_obligation": obligation, "amount": amount, "reference": transaction.id} CustomerObligationTransactions.objects.create(**obligationTran) except Exception as e: send_email('Programmed saving Cash transfer cron failed ' + str(e), 'Programmed saving Cash transfer Cron.' ) def process_salary_transaction(salary_details): """ salary_details = { "heading": str, "coment": str, "amount": float, "debit_amount": float, # Amount to debit (can be negative for receivables) "credit_amount": float, # Amount to credit (always positive) "credit_chart": OrganisationSubAccount, "debit_chart": OrganisationSubAccount, "reference_no": str, "record_date": datetime, "payment_method": str, "user": User, "branch": OrganisationBranch, "customer_account": SavingAccount, } """ customer_account = salary_details["customer_account"] # Use debit_amount if provided, otherwise fall back to amount transaction_amount = salary_details.get("debit_amount", salary_details["amount"]) transaction_fields = { "heading": salary_details["heading"], "coment": salary_details["coment"], "amount": transaction_amount, "credit_chart": salary_details["credit_chart"], "debit_chart": salary_details["debit_chart"], "reference_no": salary_details["reference_no"], "voucher_no": "", "record_date": salary_details["record_date"], "payment_method": salary_details["payment_method"], "added_by": salary_details["user"], "branch": salary_details["branch"], } # Create the main system transaction saved_transaction = SystemTransactions.objects.create(**transaction_fields) # Map it to the employee's salary account SavingAccountTransactions.objects.create( transaction_type='salary', customer_account=customer_account, transaction=saved_transaction ) return saved_transaction def loan_auto_payments(): datetime = datetime_timedelta.datetime try: organisations_list = [] org_settings = Organisation.objects.all() for org_setting in org_settings: loan_applications = LoanApplication.objects.filter(auto_payments=True, status='disbursed', organisation_branch__branch_organisation=org_setting).all() if loan_applications: organisations_list.append(org_setting.name) for loan_application in loan_applications: now_date = timezone.now() today = make_aware(datetime.strptime(now_date.strftime('%Y-%m-%d'), '%Y-%m-%d')) loan_schedules = LoanRepaymentSchedule.objects.filter(loan_application=loan_application, status='active', expected_date__lte=today ).order_by('payment_number') for loan_schedule in loan_schedules: # get schedule payments int_bal = 0 princ_bal = 0 schedule_payments = LoanPayments.objects.filter(loan_repayment_schedule=loan_schedule, loan_application=loan_application, payment_status='normal').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=loan_application, loan_repayment_schedule=loan_schedule).aggregate(total_amount_waivered=Sum('amount'))['total_amount_waivered'] interest_waivered = interest_waivered if interest_waivered else 0 int_bal = loan_schedule.interest_expected - ( total_int_paid + interest_waivered ) princ_bal = loan_schedule.principal_expected - total_princ_paid penalty_payment = LoanPayments.objects.filter( loan_application=loan_application, payment_status='normal').aggregate(total_penalty_paid=Sum('penalty_paid'))['total_penalty_paid'] penalty_payment = penalty_payment if penalty_payment else 0 penalty_waived = LoanPenaltyWaivered.objects.filter( loan_application=loan_application ).aggregate(total_amount_waived=Sum('amount'))['total_amount_waived'] penalty_waived = penalty_waived if penalty_waived else 0 penalty_payment = penalty_payment + penalty_waived loan_penalties = LoanPenalty.objects.filter(loan_application=loan_application).aggregate(total_penalty=Sum('amount'))['total_penalty'] total_penalty = loan_penalties if loan_penalties else 0 if int_bal > 0 or princ_bal > 0 or (total_penalty - penalty_payment ) > 0: # make payment payment_data = {"interest":int_bal, "principal":princ_bal, "penalty":(total_penalty - penalty_payment ), "schedule":loan_schedule.id, "loan":loan_application.id} auto_loan_repayment(payment_data) # if int(loan_application.organisation_branch.branch_organisation.id) == 21: # bunyaruguru_auto_loan_repayment(payment_data) # else: # auto_loan_repayment(payment_data) organCombinedString = ','.join(organisations_list) send_email('Loan auto payments cron completed successfully for [ ' + organCombinedString + ' ]', 'Loan Auto Payments Cron.' ) return True except Exception as e: send_email('Loan auto payments cron failed ' + str(e), 'Loan Auto Payments Cron.' ) print("An exception occurred") def loan_auto_penalties(): try: datetime = datetime_timedelta.datetime organisations_list = [] org_settings = Organisation.objects.filter(id__in=[20,39,55,21,53,19,30,92,104,103,123,33,99,142,128,352,139,91]) for org_setting in org_settings: loan_applications = LoanApplication.objects.filter(status='disbursed', organisation_branch__branch_organisation=org_setting).all() if loan_applications: organisations_list.append(org_setting.name) for loan_application in loan_applications: loan_disbursement = LoanApplicationDisbursement.objects.filter(loan_application=loan_application).first() if loan_disbursement: principal_bal = 0 interest_bal = 0 schedules_with_payments = loan_schedules_with_payments(loan_application.id).get('schedule_due', None) if schedules_with_payments is None : send_email('Loan with ID : ' + str(loan_application.id) + ' has no active repayment schedule', 'Loan Auto Penalty Error.' ) continue principal_bal = schedules_with_payments['principal_expected'] interest_bal = schedules_with_payments['interest_expected'] if loan_disbursement and (principal_bal + interest_bal) > 0: now_date = timezone.now() eat_timezone = pytz.timezone("Africa/Nairobi") now_date = now_date.astimezone(eat_timezone) today = make_aware(datetime.strptime(now_date.strftime('%Y-%m-%d 00:00:00'), '%Y-%m-%d 00:00:00')) loan_schedules = LoanRepaymentSchedule.objects.filter(loan_application=loan_application, status='active', expected_date__date__lt=now_date.strftime('%Y-%m-%d') ).order_by('payment_number') arrears_period = loan_disbursement.arrear_grace_period if loan_disbursement.arrear_grace_period > 0 else loan_application.loan_application_product.arrears_period arrears_period_type = loan_disbursement.arrears_period_type if loan_disbursement.arrears_period_type else loan_application.loan_application_product.arrears_period_type arrear_maturity_days = arrears_per_term(arrears_period, arrears_period_type) if arrears_period > 0 else 0 penalty_rate = loan_disbursement.penalty_rate if loan_disbursement.penalty_rate > 0 else loan_application.loan_application_product.penalty_rate penalty_period_type = loan_disbursement.penalty_period_type if loan_disbursement.penalty_period_type else loan_application.loan_application_product.penalty_period_type penalty_type = loan_disbursement.penalty_type if loan_disbursement.penalty_type else loan_application.loan_application_product.penalty_type # Get penalty grace period settings 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 penalty_rate > 0: due_schedule = None schedule_expected_date = None original_schedule_expected_date = None for loan_schedule in loan_schedules: laon_schedule_payments = LoanPayments.objects.filter(loan_repayment_schedule=loan_schedule, loan_application=loan_application, payment_status='normal').aggregate(total_princ_paid = Sum('princ_paid'), total_int_paid=Sum('int_paid')) interest_waivered = LoanInterestWaivered.objects.filter(loan_application=loan_application, loan_repayment_schedule=loan_schedule).aggregate(total_amount=Sum('amount'))['total_amount'] interest_waivered = interest_waivered if interest_waivered else 0 laon_schedule_princ_payment = laon_schedule_payments['total_princ_paid'] if laon_schedule_payments['total_princ_paid'] else 0 laon_schedule_int_payment = laon_schedule_payments['total_int_paid'] if laon_schedule_payments['total_int_paid'] else 0 laon_schedule_int_payment = laon_schedule_int_payment + interest_waivered schedule_expected_date = loan_schedule.expected_date + datetime_timedelta.timedelta(days=arrear_maturity_days) schedule_expected_date = schedule_expected_date.astimezone(eat_timezone) # Calculate penalty grace period end date penalty_grace_end_date = schedule_expected_date if penalty_grace_period > 0: penalty_grace_days = arrears_per_term(penalty_grace_period, penalty_grace_period_type) penalty_grace_end_date = schedule_expected_date + datetime_timedelta.timedelta(days=penalty_grace_days) if ((laon_schedule_princ_payment < loan_schedule.principal_expected or laon_schedule_int_payment < loan_schedule.interest_expected) and penalty_grace_end_date < today): # penalise due_schedule = loan_schedule original_schedule_expected_date = penalty_grace_end_date break passed_days = 0 if due_schedule and schedule_expected_date and original_schedule_expected_date: passed_days = (today - original_schedule_expected_date).days penalty_from_date = original_schedule_expected_date penalty_to_date = today # get the last penalty charge date last_penalised_date = LoanPenalty.objects.filter(loan_application=loan_application, status ="auto").order_by('-id').first() if last_penalised_date and original_schedule_expected_date < last_penalised_date.date_to.astimezone(eat_timezone): penalised_date = last_penalised_date.date_to.astimezone(eat_timezone) last_date = make_aware(datetime.strptime(penalised_date.strftime('%Y-%m-%d 59:00:00'), '%Y-%m-%d 59:00:00')) passed_days = (today - last_date).days penalty_from_date = last_penalised_date.date_to.astimezone(eat_timezone) if passed_days > 0: if penalty_type == 'both': total_amount_to_penalise = principal_bal + interest_bal elif penalty_type == 'interest': total_amount_to_penalise = interest_bal elif penalty_type == 'principal': total_amount_to_penalise = principal_bal if total_amount_to_penalise >= 1 and penalty_rate > 0: new_penalty_rate = penalty_per_term(penalty_rate, penalty_period_type) penalty_amount = ((new_penalty_rate/100) * total_amount_to_penalise) penalty_amount = round(penalty_amount * passed_days, 0) # save penality to_date = penalty_to_date.strftime('%Y-%m-%d') loan_schedules_due = loan_schedules_dues(loan_application.id, to_date , None) principal_balance_due = loan_schedules_due['princ_due'] interest_balance_due = loan_schedules_due['interest_due'] # Calculate arrear days from the original schedule expected date (not penalty grace end date) schedule_expected_date_original = due_schedule.expected_date + datetime_timedelta.timedelta(days=arrear_maturity_days) arrear_days = (penalty_to_date - schedule_expected_date_original.astimezone(eat_timezone)).days data = {"comment":"Late Payment", "loan_application":loan_application, "loan_repayment_schedule":due_schedule, "amount":penalty_amount , "status":"auto", "date_from":penalty_from_date.strftime('%Y-%m-%d'), "date_to":penalty_to_date.strftime('%Y-%m-%d'), "principal_arrears":principal_balance_due, "interest_arrears":interest_balance_due, "arrear_days":arrear_days, "penalty_rate":penalty_rate, "penalty_period_type":penalty_period_type, "expected_pay_date":due_schedule.expected_date} penalty_pay = LoanPenalty.objects.create(**data) if penalty_pay: save_user_notification({ "heading": "Loan Auto Penalties", "message": f"Loan Auto Penalty Payment. of amount {penalty_pay.amount} for principal arrears: {penalty_pay.principal_arrears} and interest arrears: principal arrears: {penalty_pay.interest_arrears} for loan {penalty_pay.loan_application.loan_amount} customer: {penalty_pay.loan_application.customer.name} member number: {penalty_pay.loan_application.customer.member_number} from {datetime.strptime(penalty_pay.date_from,'%Y-%m-%d').date()} to {datetime.strptime(penalty_pay.date_to,'%Y-%m-%d').date()}", "branch":OrganisationBranch.objects.get(pk=penalty_pay.loan_application.organisation_branch.id), "branch_name":penalty_pay.loan_application.organisation_branch.name, "added_by":None, "last_updated_by":None, "key":"loan_notifications" }) organCombinedString = ','.join(organisations_list) send_email('Loan auto penalty cron completed successfully for [ ' + organCombinedString + ' ]', 'Loan Auto Penalty Cron.' ) return True except Exception as e: send_email('Loan auto penalty cron failed ' + str(e), 'Loan Auto Penalty Cron.' ) print("An exception occurred") def penalty_per_term(penalty_rate, penalty_period_type): if penalty_period_type == 'w': return (1 + penalty_rate) ** (1/7) - 1 elif penalty_period_type == 'm': return (1 + penalty_rate) ** (1/30) - 1 elif penalty_period_type == 'bw': return (1 + penalty_rate) ** (1/14) - 1 elif penalty_period_type == 'q': return (1 + penalty_rate) ** (1/90) - 1 elif penalty_period_type == 'y': return (1 + penalty_rate) ** (1/365) - 1 else: return penalty_rate def arrears_per_term(arrears_period, arrears_period_type): if arrears_period_type == 'w': return arrears_period * 7 elif arrears_period_type == 'm': return arrears_period * 30 elif arrears_period_type == 'bw': return arrears_period * 14 elif arrears_period_type == 'q': return arrears_period * 91 elif arrears_period_type == 'y': return arrears_period * 365 else: return arrears_period def migrate_loans_security_to_main_database(request, initiation_id): initiation = BulkImportInitiation.objects.get(pk=initiation_id) if not initiation: return False loans_security = BulkTempLoanSecurityImport.objects.filter(initiation=initiation).all() for loan_security in loans_security: status = False loan_application = LoanApplication.objects.get(pk=loan_security.loan_id) account = SavingAccount.objects.filter(account_customer=loan_application.customer,deleted=False,status='active' ).order_by('-id').first() if loan_security.savings_held > 0 and account: status = True # hold savings data = {"loan_application":loan_application, "hold_type":"savings", "account":account, "amount":loan_security.savings_held, "loan_withhold_added_by":request.user, "date_added":loan_security.date_withheld} LoanApplicationWithHold.objects.create(**data) if loan_security.shares_held > 0: # hold shares status = True share = SharesSettings.objects.filter(organisation=loan_application.customer.customer_branch.branch_organisation).first() if share: amount = round(float(loan_security.shares_held)/share.share_value, 2) data = {"loan_application":loan_application, "hold_type":"shares", "amount":amount, "loan_withhold_added_by":request.user, "date_added":loan_security.date_withheld} LoanApplicationWithHold.objects.create(**data) if status: loan_security.status = 'migrated' loan_security.save() initiation.status = 'migrated' initiation.save() return True def migrate_loans_to_main_database(request, initiation_id): initiation = BulkImportInitiation.objects.get(pk=initiation_id) if not initiation: return False # get loans to migrate loans = BulkTempLoansImport.objects.filter(loan_initiation=initiation).all() for loan in loans: # create loan application loan_app_data = { "loan_amount":loan.loan_amount, "loan_application_product":loan.loan_application_product, "loan_app_added_by":request.user, "loan_officer":loan.loan_officer, "customer":loan.customer, "organisation_branch":loan.organisation_branch, "loan_date":loan.disbursement_date, "status":'pending', "int_rate":loan.int_rate, "int_method":loan.int_method, "loan_sector":loan.loan_sector, "grace_period_type":loan.grace_period_type, "loan_group":loan.group_customer_number, "loan_number": loan.reference_id } loan_application = LoanApplication.objects.create(**loan_app_data) if loan_application: # save loan migration history if loan.reference_id: loan_history = LoanMigrationHistory(loan=loan_application, loan_number=loan.reference_id) loan_history.save() # save approved loan loan_application_approval_data = {"loan_application":loan_application, "int_rate":loan_application.int_rate, "loan_amount":loan_application.loan_amount, "loan_approval_added_by":request.user, "loan_period":loan.loan_period, "period_type":loan.period_type, "frequency":loan.payment_frequency, "frequency_type":loan.period_type, "approval_date":loan.disbursement_date, "app_grace_period":loan.grace_period, "grace_period_type":loan.grace_period_type} loan_application_approval = LoanApplicationApproval.objects.create(**loan_application_approval_data) if loan_application_approval: # update loan status loan_application.status = "approved" loan_application.save() # get payment amounts expected heading = 'LoanDisbursement: ('+ loan.customer.member_number +') to ' + loan.customer.name payment_method = "cash" loan_account = loan_application.loan_application_product.chart.id # validate generate loan schedule loan_repayment_schedules = generate_loan_schedules(request, loan_application.id) if not loan_repayment_schedules: return False # customer account credit_chart = OrganisationSubAccount.objects.get(pk=loan.account_id) if not credit_chart: return False # Generate reference number organisation_id = initiation.organisation_branch.branch_organisation.id branch_id = initiation.organisation_branch.id reference_no = generate_reference_no(credit_chart.account_line, organisation_id, 'ln-d') transaction = SystemTransactions.objects.create(amount=loan.loan_amount, heading=heading, reference_no=reference_no, payment_method=payment_method,voucher_no='', debit_chart_id=loan_account, credit_chart_id=credit_chart.id, branch_id=branch_id, added_by=request.user, record_date=loan.disbursement_date) # save disbursement interest, principal, total = loan_payment(request, loan_application.id) loan_disbursement_data = {"loan_amount":loan.loan_amount, "loan_application":loan_application, "loan_start_date":loan.loan_date, "loan_disburse_added_by":request.user, "disburse_method":loan.payment_method, "loan_disbursement_date":loan.disbursement_date, "ref_no":reference_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 } # disburse loan loan_application_disbursement = LoanApplicationDisbursement.objects.create(**loan_disbursement_data) # save the schedules if loan_application_disbursement: # update loan status loan_application.status = "disbursed" loan_application.save() data = { "heading":heading, "amount":loan.loan_amount, "payment_method":loan.payment_method, "loan_application":loan_application, "ref_no":reference_no, "voucher_no":"", "transaction_type":'LoanDisbursement', "loan_main_transaction_added_by":request.user, "payment_date":loan.disbursement_date, "system_transaction":transaction} LoanMainTransactions.objects.create(**data) # generate the schedule for cobis if int(loan_application.organisation_branch.branch_organisation.id) == 49: interest_paid = loan_application_disbursement.total_interest_expected - round_off_amount(request, loan.int_bal) principal_grace_period = get_principal_grace_period(request, loan_application, interest_paid) if principal_grace_period > 0 : loan_application_approval.app_grace_period = principal_grace_period loan_application_approval.grace_period_type = 'pay_i' loan_application_approval.save() # generate loan schedule loan_repayment_schedules = generate_loan_schedules(request, loan_application.id) if not loan_repayment_schedules: return False # 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':request.user } LoanRepaymentSchedule.objects.create(**schedule_data) count = count + 1 first_schedule = LoanRepaymentSchedule.objects.filter(loan_application=loan_application, status='active').order_by('id').first() if loan.penalty > 0 and first_schedule: penalty_data = {"loan_application":loan_application, "comment":"Penalty Migrated", "amount":loan.penalty, "status":"manual", "loan_penality_added_by":request.user, "loan_repayment_schedule":first_schedule, "date_added":first_schedule.expected_date, "date_from":first_schedule.expected_date, "date_to":first_schedule.expected_date} LoanPenalty.objects.create(**penalty_data) # make payment of the schedules principal_paid = loan_application_disbursement.total_principal_expected - round_off_amount(request, loan.princ_bal) interest_paid = loan_application_disbursement.total_interest_expected - round_off_amount(request, loan.int_bal) # process_loan_payment # datetime = datetime_timedelta.datetime # loan_start_date = make_aware(datetime.strptime('2023-07-29', '%Y-%m-%d')) payment_details = {"amount_paid": principal_paid + interest_paid, "principal_paid": principal_paid, "int_paid": interest_paid, "penalty_paid":0, "payment_method":loan.payment_method, "account":loan.account_id, "date_added":initiation.as_at, "voucher_no":'', "cheque":'', "loan_id":loan_application.id, "account_id":'' } process_loan_payment(payment_details, request) loan.status = 'migrated' loan.save() initiation.status = 'migrated' initiation.save() return True def migrate_members_to_main_database(request, initiation_id): initiation = BulkImportInitiation.objects.get(pk=initiation_id) if not initiation: return False # get loans to migrate members = BulkTempMembersImport.objects.filter(member_initiation=initiation).all() for member in members: if member.status == 'in-progress': client_type = CustomerType.objects.filter(id=member.client_type, organisation=member.organisation_branch.branch_organisation).first() # create member data = { "name":member.name, "member_number":member.member_number, "old_member_number":member.old_member_number, "telephone":member.phone, "gender":member.gender, "customer_added_by":request.user, "customer_branch":member.organisation_branch, "branch_customer_type":client_type, "status":"active" } if member.nationality: data["nationality"] = member.nationality if member.district: data["district"] = member.district if member.age is not None: data["age"] = member.age if member.disability is not None: data["disability"] = member.disability customer_filters = Q() customer_filters.add(Q(**{"member_number":member.member_number}), Q.OR) customer_filters.add(Q(**{"old_member_number":member.old_member_number}), Q.OR) customer_filters.add(Q(**{"customer_branch__branch_organisation__id": member.organisation_branch.branch_organisation.id}), Q.AND) #Check if the customer doesn't already exist customer = Customer.objects.filter(customer_filters).first() if not customer: customer = Customer.objects.create(**data) if customer: # create savings account account_product = SavingsProduct.objects.get(pk=member.saving_product_id) account_no = generate_saving_account_code(initiation.organisation_branch.id) account_data = {"account_no":account_no, "account_customer":customer, "account_product":account_product, "customer_branch":initiation.organisation_branch, "status":"active", "is_active":True, "saving_account_added_by":request.user, "saving_account_last_updated_by":request.user, "opened_by":request.user.user_staff} savings_details = SavingAccount.objects.create(**account_data) if savings_details: member.status = 'migrated' member.save() # add member to group if member.group_member_number: group_customer = Customer.objects.filter(member_number=member.group_member_number).first() if group_customer: data = {"member":customer, "group":group_customer, "added_by":request.user} GroupMembership.objects.create(**data) if member.address: CustomerAddress.objects.create(physical_address=member.address, region= member.region, address_added_by=request.user, customer=customer) initiation.status = 'migrated' initiation.save() return True def migrate_accounts_to_main_database(request, initiation_id): initiation = BulkImportInitiation.objects.get(pk=initiation_id) if not initiation or initiation.status != 'in-progress': return False # get loans to migrate accounts = BulkTempAccountsImport.objects.filter(member_initiation=initiation).all() for account in accounts: # create savings account account_no = generate_saving_account_code(initiation.organisation_branch.id) account_data = { "account_no":account_no, "account_customer":Customer.objects.get(pk=account.customer_id), "account_product":SavingsProduct.objects.get(pk=account.saving_product_id), "customer_branch":initiation.organisation_branch, "status":"active", "is_active":True, "saving_account_added_by":request.user, "saving_account_last_updated_by":request.user, "opened_by":request.user.user_staff } savings_details = SavingAccount.objects.create(**account_data) if savings_details: account.status = 'migrated' account.save() initiation.status = 'migrated' initiation.save() return True def migrate_member_number_updates_to_main_database(request, initiation_id): initiation = BulkImportInitiation.objects.get(pk=initiation_id) if not initiation or initiation.status != 'in-progress': return False # get loans to migrate members = BulkTempMemberNumberImport.objects.filter(member_initiation=initiation).all() for member in members: # update customer details customer = Customer.objects.get(pk=member.customer_id) if member.new_member_number: customer.old_member_number = member.new_member_number customer.save() if member.telephone: customer.telephone = member.telephone customer.save() customer_address = CustomerAddress.objects.filter(customer=customer).first() if member.physical_address: if customer_address and member.physical_address: customer_address.physical_address = member.physical_address customer_address.region = member.region customer_address.save() else: CustomerAddress.objects.create(physical_address=member.physical_address, region= member.region, address_added_by=request.user, customer=customer) member.status = 'updated' initiation.status = 'migrated' initiation.save() return True def update_loan_penalty_dates(organisation): # get repayment schedules for all loans within the organisations penalties = LoanPenalty.objects.filter(loan_application__organisation_branch__branch_organisation__id=organisation,expected_pay_date__isnull=True) if penalties: for penalty in penalties: loan_schedule = LoanRepaymentSchedule.objects.filter(id = penalty.loan_repayment_schedule.id).first() if loan_schedule: loan_disbursement = LoanApplicationDisbursement.objects.filter(loan_application=penalty.loan_application).first() arrear_maturity_days = 0 arrear_maturity_days += arrears_per_term(loan_disbursement.arrear_grace_period, loan_disbursement.arrears_period_type) if loan_disbursement.arrear_grace_period > 0 else 0 loan_schedule_expected_date = loan_schedule.expected_date + datetime_timedelta.timedelta(days=arrear_maturity_days) penality_from_date = loan_schedule_expected_date penality_to_date = penality_from_date + datetime_timedelta.timedelta(days=1) laon_schedule_payments = LoanPayments.objects.filter(loan_repayment_schedule=loan_schedule, loan_application=loan_schedule.loan_application,date_added__lte=penality_to_date, payment_status='normal').aggregate(total_princ_paid = Sum('princ_paid'), total_int_paid=Sum('int_paid')) laon_schedule_princ_payment = laon_schedule_payments['total_princ_paid'] if laon_schedule_payments['total_princ_paid'] else 0 laon_schedule_int_payment = laon_schedule_payments['total_int_paid'] if laon_schedule_payments['total_int_paid'] else 0 principal_balance = loan_schedule.principal_expected - laon_schedule_princ_payment interest_balance = loan_schedule.interest_expected - laon_schedule_int_payment penalty_rate = loan_disbursement.penalty_rate if loan_disbursement.penalty_rate > 0 else loan_schedule.loan_application.loan_application_product.penalty_rate penalty_period_type = loan_disbursement.penalty_period_type if loan_disbursement.penalty_period_type else loan_schedule.loan_application.loan_application_product.penalty_period_type penalty.date_added = penality_to_date penalty.date_from = penality_from_date penalty.date_to = penality_to_date penalty.principal_arrears = principal_balance penalty.interest_arrears = interest_balance penalty.penalty_rate = penalty_rate penalty.penalty_period_type = penalty_period_type penalty.arrear_days = 0 penalty.expected_pay_date = loan_schedule_expected_date penalty.save() '''{"organisation":2,"type":"update_loan_penalty_data"}''' def process_db_cron_back_up(): try: # Database details DB_HOST = config('QB_DB_HOSTNAME', default='') DB_USER = config('QB_DB_USERNAME', default='') DB_PASS = config('QB_DB_PASSWORD', default='') DB_NAME = config('QB_DB_NAME', default='') # Backup details BACKUP_PATH = tempfile.gettempdir() # BACKUP_PATH = settings.STATIC_ROOT + '/db_back_ups' TIMESTAMP = time.strftime('%Y-%m-%d') BACKUP_FILE = DB_NAME + '_' + TIMESTAMP + '.sql' ZIP_FILE = DB_NAME + '_' + TIMESTAMP + '.zip' # # Command to take a backup file_path = os.path.join(BACKUP_PATH, BACKUP_FILE) zip_path = os.path.join(BACKUP_PATH, ZIP_FILE) BACKUP_CMD = "export PGPASSWORD={0}; pg_dump -h {1} -U {2} -f {3} {4}".format(DB_PASS, DB_HOST, DB_USER, file_path, DB_NAME) # Execute the backup command os.system(BACKUP_CMD) # Zip the backup file with zipfile.ZipFile(zip_path, 'w', zipfile.ZIP_DEFLATED) as zipf: zipf.write(file_path, BACKUP_FILE) # upload back-up to aws bucket. upload_file_to_aws(zip_path, 'qb_core_back_ups/{}'.format(ZIP_FILE)) send_email('DB BackUp cron Successfully', 'DB BackUp Cron.' ) except Exception as e: send_email('DB BackUp cron failed' + str(e), 'DB BackUp Cron.' ) print("An exception occurred") finally: # Cleanup: Delete the SQL and ZIP files try: if os.path.exists(file_path): os.remove(file_path) print(f"Deleted backup file: {file_path}") if os.path.exists(zip_path): os.remove(zip_path) print(f"Deleted zip file: {zip_path}") except Exception as cleanup_error: print(f"Cleanup failed: {cleanup_error}") def get_principal_grace_period(request, loan_application, interest_paid): installments_interest_paid = 0 loan_repayment_schedules = generate_loan_schedules(request, loan_application.id) installments_count = len(loan_repayment_schedules) if loan_repayment_schedules and interest_paid > 0: for loan_repayment_schedule in loan_repayment_schedules: installments_count = installments_count - 1 if float(interest_paid) >= (float(loan_repayment_schedule['interest_expected']) + float(loan_repayment_schedule['principal_expected'])) and installments_count >= 1: installments_interest_paid += 1 interest_paid = interest_paid - (loan_repayment_schedule['interest_expected'] + loan_repayment_schedule['principal_expected']) return installments_interest_paid def date_time_zone_convert(record_date, date_added=None): date_value = None if isinstance(record_date, str): date_value = record_date elif record_date is not None: if timezone.is_aware(record_date): date_value = timezone.localtime(record_date, timezone.get_current_timezone()).strftime('%Y-%m-%d') else: date_value = record_date.strftime('%Y-%m-%d') else: date_value = timezone.localdate().strftime('%Y-%m-%d') current_date = timezone.now() if date_added is None else date_added if timezone.is_naive(current_date): current_date = make_aware(current_date, timezone.get_current_timezone()) date_object = datetime_timedelta.datetime.strptime(date_value, '%Y-%m-%d').date() current_time = timezone.localtime(current_date, timezone.get_current_timezone()).time() combined_datetime = datetime_timedelta.datetime.combine(date_object, current_time) if timezone.is_naive(combined_datetime): combined_datetime = make_aware(combined_datetime, timezone.get_current_timezone()) return combined_datetime def push_negative_savings_to_main_db(initiation_id): initiation_obj = BulkImportInitiation.objects.get(pk=initiation_id) if initiation_obj and initiation_obj.status == 'in-progress': negative_savings = BulkTempNegativeSavingsImports.objects.filter(initiation=initiation_obj, status='Pending') for negative_saving in negative_savings: customer_account = SavingAccount.objects.filter(account_product=negative_saving.product, account_customer=negative_saving.customer).first() if customer_account: reference_no = generate_reference_no( customer_account.account_product.accounts_chart.account_line, negative_saving.organisation_branch.branch_organisation.id, 'wd') transaction_fields = { "heading": negative_saving.heading, "coment": negative_saving.comment, "amount": negative_saving.amount, "debit_chart": customer_account.account_product.accounts_chart, "credit_chart": negative_saving.credit_chart, "reference_no": reference_no, "voucher_no": '', "payment_method": 'offset', "record_date":negative_saving.record_date, "added_by":initiation_obj.initiation_added_by, "branch": initiation_obj.organisation_branch } system_transaction = SystemTransactions.objects.create( **transaction_fields) if system_transaction: saved_transaction_fields = { "transaction_type": 'withdrawal', "customer_account_id": customer_account.id, "transaction_id": system_transaction.id, "date_added":negative_saving.record_date } SavingAccountTransactions.objects.create( **saved_transaction_fields) negative_saving.status = 'Processed' negative_saving.save() initiation_obj.status = 'migrated' initiation_obj.save() return True def logger_to_file(message): try: file_path = os.path.join(settings.BASE_DIR + "/logs", "debug_log.log") if not os.path.exists(file_path): open(file_path, "w").close() with open(file_path, "a", encoding="utf8") as f: f.write(message + "\n") except Exception as e: print(e)