from ledgers.models import SystemTransactions from django.conf import settings from django.db import connection, transaction from .models import * from overdraft.models import * from .serializers import AccountBookingsSerializer from django.db.models import Sum, F, Q, Max from ledgers.ledgers_helper import post_transaction,generate_reference_no, post_transaction,get_inter_branch_chart,get_chart_of_account_by_code,get_serialized_transaction import threading import pytz from ledgers.models import CashAccounts from organisations.models import * from .savings_bal_helper import get_account_balance from questbanker_api.utils import get_current_user from django.contrib.auth import get_user_model from exservices.exservices_helper import send_customer_sms from users.audit_log_helper import add_system_audit_trail from django.utils import timezone from django.utils.timezone import make_aware from django.utils.dateparse import parse_datetime, parse_date from datetime import date, datetime,timedelta from rest_framework.response import Response from rest_framework import status from django.db.models.functions import TruncDate from django.db.models import Max from django.http import Http404 from notifications.notifications_helper import * import pandas as pd import os from reports.models import SavingsAccountBalances from reports.serializers import SavingsAccountBalancesSerializer from loans.helpers.general_helper import convert_json_to_sql_where from dateutil.relativedelta import relativedelta from rest_framework.exceptions import ValidationError AML_DEPOSIT_THRESHOLD_SETTING_KEY = "aml_deposit_threshold" AML_WITHDRAWAL_BLOCK_REASON = ( "Deposit exceeded the organisation anti-money laundering threshold. " "Withdrawals are blocked until the source of funds is approved." ) def make_booking_payment(booking, organisation_id, branch_id, user_id=None): if not isinstance(booking, AccountBookings): booking = AccountBookings.objects.get(pk=booking) if not user_id: user_id = booking.added_by account_chart = booking.account.account_product.accounts_chart transaction_prefix = 'inc' if booking.booking_type == 'income' else ('ast' if booking.booking_type == 'assets' else 'lb-t') amount_to_pay = 0 account_balance = get_account_balance(booking.account) booking_data = AccountBookingsSerializer(booking).data amount_due = booking.amount - booking_data['payments']['total'] if amount_due > 0 and account_balance['balance_raw'] > 0: if account_balance['balance_raw'] < amount_due: amount_to_pay = account_balance['balance_raw'] else: amount_to_pay = amount_due if amount_to_pay <= 0: return {"status": 'failed', "payment": {}} # transaction details transaction_details = { "heading": booking.heading, "amount": amount_to_pay, "record_date": timezone.now(), "debit_chart_id": account_chart.id, "credit_chart_id": booking.chart.id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": transaction_prefix, "organisation_id": organisation_id, "branch_id": branch_id, "user_id": user_id } # Save general transaction transaction = post_transaction(transaction_details) # Link transaction to savings account. offset = SavingAccountTransactions(customer_account=booking.account, transaction=transaction, transaction_type=transaction_details['payment_method']) offset.save() # Link transaction to booking payments bookingPayment = AccountBookingPayments(booking=booking, reference_transaction=transaction, added_by=user_id) bookingPayment.save() return {"status": 'success', "payment": bookingPayment} def get_account_bookings(account, status='all'): if not isinstance(account, SavingAccount): account = SavingAccount.objects.get(pk=account) if status == 'all': return AccountBookings.objects.filter(account=account, reversed=False) elif status == 'pending': return AccountBookings.objects.annotate(balance=F('amount') - Sum('booking_payment__reference_transaction__amount')).filter(account=account, reversed=False).filter(Q(balance=None) | Q(balance__gt=0)).order_by('id') else: return AccountBookings.objects.annotate(balance=F('amount') - Sum('booking_payment__reference_transaction__amount')).filter(account=account, reversed=False, balance__lte=0).order_by('id') def process_multiple_bookings(bookings, organisation_id, branch_id, user_id): for booking in bookings: make_booking_payment(booking, organisation_id, branch_id, user_id) def thread_multiple_booking_payments(account, organisation_id, branch_id, user_id): account_bookings = get_account_bookings(account, 'pending') thread = threading.Thread(target=process_multiple_bookings, args=(account_bookings, organisation_id, branch_id, user_id)) thread.start() def get_teller_cash_account(teller_id): return CashAccounts.objects.filter(teller_id=teller_id).first() def generate_saving_account_code(branch_id): account_code = 1 customer_branch = OrganisationBranch.objects.get(pk=branch_id) saving_accounts = SavingAccount.objects.filter(customer_branch = OrganisationBranch.objects.get(pk=branch_id)).order_by('-id') if saving_accounts: account_no = saving_accounts[0].account_no try: account_code = str(int(account_no[-6:])+1) except ValueError as ve: account_code = str(len(saving_accounts) + 1) account_code_str = str(account_code).zfill(6) if customer_branch.account_number_prefix is not None: account_code_str = f"{customer_branch.account_number_prefix}{account_code_str}" return account_code_str def _ensure_aware_datetime(value): if value is None: return None if isinstance(value, str): parsed_datetime = parse_datetime(value) if parsed_datetime is not None: value = parsed_datetime else: parsed_date = parse_date(value) if parsed_date is not None: value = datetime.combine(parsed_date, datetime.min.time()) else: return None if timezone.is_naive(value): return make_aware(value) return value def _coerce_positive_float(value): try: amount = float(value or 0) except (TypeError, ValueError): return 0 return amount if amount > 0 else 0 def get_organisation_aml_deposit_threshold(organisation): organisation_id = getattr(organisation, "id", organisation) if not organisation_id: return 0 setting = OrganisationSetting.objects.filter( org_setting__id=organisation_id, setting_key=AML_DEPOSIT_THRESHOLD_SETTING_KEY, ).first() if not setting: return 0 return _coerce_positive_float(setting.setting_value) def get_savings_account_aml_block_message(account): return ( f"Savings account {account.account_no} is blocked for withdrawals until " "approved after verifying the source of the deposited money." ) def mark_savings_account_for_aml_deposit(account, amount, transaction=None): if not isinstance(account, SavingAccount): account = SavingAccount.objects.select_related("customer_branch").get(pk=account) deposit_amount = _coerce_positive_float(amount) threshold = get_organisation_aml_deposit_threshold( account.customer_branch.branch_organisation_id ) if threshold <= 0 or deposit_amount <= threshold: return account, False changed_fields = [] now = timezone.now() if not account.is_aml_withdrawal_blocked: account.is_aml_withdrawal_blocked = True account.aml_blocked_at = now changed_fields.extend(["is_aml_withdrawal_blocked", "aml_blocked_at"]) if account.aml_threshold_amount != threshold: account.aml_threshold_amount = threshold changed_fields.append("aml_threshold_amount") if account.aml_trigger_amount != deposit_amount: account.aml_trigger_amount = deposit_amount changed_fields.append("aml_trigger_amount") if transaction and account.aml_trigger_transaction_id != transaction.id: account.aml_trigger_transaction = transaction changed_fields.append("aml_trigger_transaction") if account.aml_block_reason != AML_WITHDRAWAL_BLOCK_REASON: account.aml_block_reason = AML_WITHDRAWAL_BLOCK_REASON changed_fields.append("aml_block_reason") if changed_fields: account.last_updated = now changed_fields.append("last_updated") account.save(update_fields=changed_fields) return account, True def flag_savings_account_aml_withdrawal_attempt(account): if not isinstance(account, SavingAccount): account = SavingAccount.objects.get(pk=account) if not account.is_aml_withdrawal_blocked: return account, False changed_fields = [] now = timezone.now() if not account.aml_is_flagged: account.aml_is_flagged = True account.aml_flagged_at = now changed_fields.extend(["aml_is_flagged", "aml_flagged_at"]) if changed_fields: account.last_updated = now changed_fields.append("last_updated") account.save(update_fields=changed_fields) return account, bool(changed_fields) def get_savings_account_initial_state(account_product, open_date=None, as_at=None): if not isinstance(account_product, SavingsProduct): account_product = SavingsProduct.objects.get(pk=account_product) as_at = _ensure_aware_datetime(as_at) or timezone.now() open_date = _ensure_aware_datetime(open_date) or as_at activation_period = max(int(account_product.activation_period or 0), 0) activation_date = open_date + timedelta(hours=activation_period) if activation_period > 0 else None if activation_date and as_at < activation_date: return { "status": "inactive", "is_active": False, "activation_date": activation_date, } return { "status": "active", "is_active": True, "activation_date": activation_date, } def _get_savings_account_last_activity(account, last_activity_at=None): if last_activity_at is not None: return _ensure_aware_datetime(last_activity_at) latest_transaction = SavingAccountTransactions.objects.filter( customer_account=account, deleted=False, transaction__deleted=False, ).aggregate(latest_date=Max("transaction__record_date"))["latest_date"] return _ensure_aware_datetime(latest_transaction) or _ensure_aware_datetime(account.open_date) or timezone.now() def get_savings_account_lifecycle(account, as_at=None, last_activity_at=None): if not isinstance(account, SavingAccount): account = SavingAccount.objects.select_related("account_product").get(pk=account) as_at = _ensure_aware_datetime(as_at) or timezone.now() current_status = (account.status or "active").lower() if account.deleted: return { "status": current_status, "is_active": False, "activation_date": None, "dormancy_date": _ensure_aware_datetime(account.dormancy_date), "deactivation_date": None, "last_activity_at": None, "reason": "deleted", } if current_status == "pending": return { "status": "pending", "is_active": False, "activation_date": None, "dormancy_date": None, "deactivation_date": None, "last_activity_at": None, "reason": "pending_approval", } if current_status == "closure_pending": return { "status": "closure_pending", "is_active": False, "activation_date": None, "dormancy_date": _ensure_aware_datetime(account.dormancy_date), "deactivation_date": None, "last_activity_at": _get_savings_account_last_activity(account, last_activity_at), "reason": "closure_pending", } if current_status == "closed": return { "status": "closed", "is_active": False, "activation_date": None, "dormancy_date": _ensure_aware_datetime(account.dormancy_date), "deactivation_date": None, "last_activity_at": _get_savings_account_last_activity(account, last_activity_at), "reason": "closed", } open_date = _ensure_aware_datetime(account.open_date) or as_at initial_state = get_savings_account_initial_state( account.account_product, open_date=open_date, as_at=as_at, ) if initial_state["status"] == "inactive": return { "status": "inactive", "is_active": False, "activation_date": initial_state["activation_date"], "dormancy_date": None, "deactivation_date": None, "last_activity_at": None, "reason": "activation_period", } last_activity_at = _get_savings_account_last_activity(account, last_activity_at) dormancy_period = max(int(account.account_product.dormancy_period or 0), 0) deactivation_period = max(int(account.account_product.deactivation_period or 0), 0) target_status = "active" target_is_active = True dormancy_date = None deactivation_date = None reason = "active" if dormancy_period > 0: dormancy_date = last_activity_at + relativedelta(months=dormancy_period) if as_at >= dormancy_date: target_status = "dormant" reason = "dormancy_period" if deactivation_period > 0: deactivation_date = dormancy_date + relativedelta(months=deactivation_period) if as_at >= deactivation_date: target_status = "inactive" target_is_active = False reason = "deactivation_period" if target_status == "active": dormancy_date = None return { "status": target_status, "is_active": target_is_active, "activation_date": initial_state["activation_date"], "dormancy_date": dormancy_date, "deactivation_date": deactivation_date, "last_activity_at": last_activity_at, "reason": reason, } def sync_savings_account_lifecycle(account, as_at=None, last_activity_at=None): if not isinstance(account, SavingAccount): account = SavingAccount.objects.select_related("account_product").get(pk=account) lifecycle = get_savings_account_lifecycle(account, as_at=as_at, last_activity_at=last_activity_at) changed_fields = [] target_dormancy_date = lifecycle["dormancy_date"] if account.status != lifecycle["status"]: account.status = lifecycle["status"] changed_fields.append("status") if account.is_active != lifecycle["is_active"]: account.is_active = lifecycle["is_active"] changed_fields.append("is_active") if _ensure_aware_datetime(account.dormancy_date) != target_dormancy_date: account.dormancy_date = target_dormancy_date changed_fields.append("dormancy_date") if changed_fields: account.last_updated = timezone.now() changed_fields.append("last_updated") account.save(update_fields=changed_fields) return account, lifecycle, bool(changed_fields) def _build_savings_account_status_message(account, lifecycle, action): if lifecycle["reason"] == "pending_approval": return f"Savings account {account.account_no} is pending approval and cannot be used for {action}." if lifecycle["reason"] == "activation_period" and lifecycle["activation_date"]: activation_date = timezone.localtime(lifecycle["activation_date"]).strftime("%Y-%m-%d %H:%M") return f"Savings account {account.account_no} is still in its activation period until {activation_date} and cannot be used for {action}." if lifecycle["reason"] == "dormancy_period": return f"Savings account {account.account_no} is dormant and cannot be used for {action} until it is reactivated." if lifecycle["reason"] == "deactivation_period": return f"Savings account {account.account_no} is inactive after exceeding its dormancy and deactivation periods and cannot be used for {action}." if lifecycle["reason"] == "closure_pending": return f"Savings account {account.account_no} is pending closure and cannot be used for {action}." if lifecycle["reason"] == "closed": return f"Savings account {account.account_no} is closed and cannot be used for {action}." return f"Savings account {account.account_no} is inactive and cannot be used for {action}." def can_credit_savings_account(account, as_at=None): account, lifecycle, _ = sync_savings_account_lifecycle(account, as_at=as_at) return lifecycle["status"] not in ["pending", "closure_pending", "closed"] and not account.deleted, lifecycle, account def can_debit_savings_account(account, as_at=None): account, lifecycle, _ = sync_savings_account_lifecycle(account, as_at=as_at) if account.is_aml_withdrawal_blocked: account, _ = flag_savings_account_aml_withdrawal_attempt(account) return False, lifecycle, account return lifecycle["status"] == "active" and not account.deleted, lifecycle, account def assert_savings_account_can_credit(account, action="this transaction", as_at=None): allowed, lifecycle, account = can_credit_savings_account(account, as_at=as_at) if not allowed: raise ValidationError({"message": _build_savings_account_status_message(account, lifecycle, action)}) return account, lifecycle def assert_savings_account_can_debit(account, action="this transaction", as_at=None): allowed, lifecycle, account = can_debit_savings_account(account, as_at=as_at) if not allowed: if account.is_aml_withdrawal_blocked: raise ValidationError({ "message": get_savings_account_aml_block_message(account), "code": "aml_withdrawal_blocked", "aml_blocked": True, }) raise ValidationError({"message": _build_savings_account_status_message(account, lifecycle, action)}) return account, lifecycle def get_savings_account_booking_outstanding(account): if not isinstance(account, SavingAccount): account = SavingAccount.objects.get(pk=account) outstanding_amount = 0 bookings = AccountBookings.objects.filter( account=account, deleted=False, reversed=False, ) for booking in bookings: paid_amount = ( AccountBookingPayments.objects.filter( booking=booking, deleted=False, reference_transaction__deleted=False, ).aggregate(total=Sum('reference_transaction__amount'))['total'] or 0 ) outstanding_amount += max(float(booking.amount or 0) - float(paid_amount or 0), 0) return outstanding_amount def get_savings_account_closure_available_balance(account): if not isinstance(account, SavingAccount): account = SavingAccount.objects.get(pk=account) balance = get_account_balance(account) available_balance = ( float(balance.get('balance_actual', 0) or 0) - float(balance.get('blocked_amount', 0) or 0) - float(balance.get('with_held', 0) or 0) ) return round(max(available_balance, 0), 2) def get_active_savings_account_closure(account): if not isinstance(account, SavingAccount): account = SavingAccount.objects.get(pk=account) return ( SavingAccountClosure.objects.filter( saving_account=account, status='initiated', deleted=False, ) .order_by('-id') .first() ) def validate_savings_account_closure_eligibility(account, closure_fee=0): if not isinstance(account, SavingAccount): account = SavingAccount.objects.select_related('account_customer', 'account_product').get(pk=account) if account.deleted: raise ValidationError({"message": f"Savings account {account.account_no} has been deleted and cannot be closed."}) current_status = (account.status or '').lower() if current_status == 'pending': raise ValidationError({"message": f"Savings account {account.account_no} is pending approval and cannot be closed."}) if current_status == 'closed': raise ValidationError({"message": f"Savings account {account.account_no} is already closed."}) if current_status == 'closure_pending': raise ValidationError({"message": f"Savings account {account.account_no} already has a pending closure. Complete the current closure first."}) if FixedDeposit.objects.filter( saving_account=account, status__in=['pending', 'in-progress'], deleted=False, ).exists(): raise ValidationError({"message": f"Savings account {account.account_no} cannot be closed while it has active fixed deposits."}) balance = get_account_balance(account) blocked_amount = float(balance.get('blocked_amount', 0) or 0) withheld_amount = float(balance.get('with_held', 0) or 0) if blocked_amount > 0: raise ValidationError({"message": f"Savings account {account.account_no} has blocked funds and cannot be closed until they are cleared."}) if withheld_amount > 0: raise ValidationError({"message": f"Savings account {account.account_no} has withheld funds and cannot be closed until they are cleared."}) outstanding_bookings = get_savings_account_booking_outstanding(account) if outstanding_bookings > 0: raise ValidationError({"message": f"Savings account {account.account_no} has outstanding account bookings and cannot be closed."}) available_balance = get_savings_account_closure_available_balance(account) if float(closure_fee or 0) > available_balance: raise ValidationError({"message": f"Savings account {account.account_no} does not have enough balance to cover the account closure fee."}) return { "balance": balance, "available_balance": available_balance, "outstanding_bookings": outstanding_bookings, } @transaction.atomic def initiate_savings_account_closure(*, account, closure_fee, fee_collection_account, record_date, user, organisation_id, branch_id): if not isinstance(account, SavingAccount): account = SavingAccount.objects.select_related( 'account_customer', 'account_product', 'customer_branch', ).get(pk=account) existing_closure = get_active_savings_account_closure(account) if existing_closure is not None: raise ValidationError({"message": f"Savings account {account.account_no} already has a pending closure."}) validate_savings_account_closure_eligibility(account, closure_fee=closure_fee) record_date = _ensure_aware_datetime(record_date) or timezone.now() branch = OrganisationBranch.objects.get(pk=branch_id) fee_transaction = None if float(closure_fee or 0) > 0: fee_reference_no = generate_reference_no( fee_collection_account.account_line, organisation_id, 'wdacf', ) fee_system_transaction = SystemTransactions.objects.create( heading=f'Account closure fee on A/C No: {account.account_no}', coment=f'Account closure fee on A/C No: {account.account_no}', amount=float(closure_fee), debit_chart=account.account_product.accounts_chart, credit_chart=fee_collection_account, reference_no=fee_reference_no, payment_method='internal', added_by=user, branch=branch, record_date=record_date, ) fee_transaction = SavingAccountTransactions.objects.create( customer_account=account, transaction=fee_system_transaction, transaction_type='account_closure_fee', ) closure = SavingAccountClosure.objects.create( saving_account=account, closure_fee=float(closure_fee or 0), fee_collection_account=fee_collection_account, fee_transaction=fee_transaction, status='initiated', record_date=record_date, closure_added_by=user, ) account.status = 'closure_pending' account.is_active = False account.last_updated = timezone.now() account.saving_account_last_updated_by = user account.save( update_fields=['status', 'is_active', 'last_updated', 'saving_account_last_updated_by'] ) return closure @transaction.atomic def finalize_savings_account_closure(*, closure, amount, destination_chart, payment_method, voucher_no, record_date, receiver, send_sms, user, organisation_id, branch_id): if not isinstance(closure, SavingAccountClosure): closure = SavingAccountClosure.objects.select_related( 'saving_account', 'saving_account__account_customer', 'saving_account__account_product', 'saving_account__customer_branch', ).get(pk=closure) if closure.deleted: raise ValidationError({"message": "This savings account closure record has been deleted."}) if closure.status != 'initiated': raise ValidationError({"message": "Only initiated savings account closures can be completed."}) account = closure.saving_account if account.deleted: raise ValidationError({"message": f"Savings account {account.account_no} has been deleted and cannot be closed."}) if (account.status or '').lower() == 'closed': raise ValidationError({"message": f"Savings account {account.account_no} is already closed."}) record_date = _ensure_aware_datetime(record_date) or timezone.now() receiver = receiver or account.account_customer.name withdrawable_balance = get_savings_account_closure_available_balance(account) amount = float(amount or 0) withdrawal_transaction = None if withdrawable_balance > 0: if amount <= 0: raise ValidationError({"message": "A final withdrawal amount is required to complete this account closure."}) if abs(amount - withdrawable_balance) > 0.01: raise ValidationError({"message": "Account closure requires withdrawing the full remaining available balance."}) if not payment_method: raise ValidationError({"message": "A payment method is required for the final closure withdrawal."}) if destination_chart is None: raise ValidationError({"message": "A destination account is required for the final closure withdrawal."}) branch = OrganisationBranch.objects.get(pk=branch_id) interbranch_chart = get_inter_branch_chart(branch, account.customer_branch) reference_no = generate_reference_no(account.account_product.accounts_chart.account_line, organisation_id, 'wd') transaction_fields = { "heading": 'Account closure withdrawal by ' + receiver + ' From: ' + account.account_customer.name + '-' + account.account_customer.old_member_number, "coment": account.account_customer.name + ' has withdrawn (' + str(amount) + ') from A/C No: ' + account.account_no + ' during account closure', "amount": amount, "debit_chart": account.account_product.accounts_chart, "credit_chart": destination_chart, "reference_no": reference_no, "voucher_no": voucher_no, "payment_method": payment_method, "added_by": user, "branch": branch, "record_date": record_date, } if branch_id != account.customer_branch.id: transaction_fields["heading"] = 'Inter-branch account closure withdrawal by ' + receiver + ' From: ' + account.account_customer.name + '-' + account.account_customer.old_member_number transaction_fields["branch"] = branch transaction_fields["debit_chart"] = interbranch_chart saved_transaction = SystemTransactions.objects.create(**transaction_fields) if branch_id != account.customer_branch.id: transaction_fields["debit_chart"] = account.account_product.accounts_chart transaction_fields["branch"] = account.customer_branch transaction_fields["credit_chart"] = interbranch_chart transaction_fields["payment_method"] = 'settlement' inter_branch_trans = SystemTransactions.objects.create(**transaction_fields) InterBranchTransactions.objects.create( source_transaction=saved_transaction, destination_transaction=inter_branch_trans, added_by=user, ) withdrawal_transaction = SavingAccountTransactions.objects.create( customer_account=account, transaction=inter_branch_trans, transaction_type='withdrawal', ) else: withdrawal_transaction = SavingAccountTransactions.objects.create( customer_account=account, transaction=saved_transaction, transaction_type='withdrawal', ) if send_sms: data = { "sms_key": "cash_withdraw_sms", "customer_account": account, "user": user, "branch_id": branch_id, "save_trans": withdrawal_transaction, "customer": account.account_customer, } send_customer_sms(data) final_balance = float(get_account_balance(account).get('balance_actual', 0) or 0) if abs(final_balance) > 0.01: raise ValidationError({"message": f"Savings account {account.account_no} still has a balance and cannot be marked closed."}) account.status = 'closed' account.is_active = False account.last_updated = timezone.now() account.saving_account_last_updated_by = user account.save( update_fields=['status', 'is_active', 'last_updated', 'saving_account_last_updated_by'] ) closure.amount_withdrawn = amount closure.receiver = receiver closure.payment_method = payment_method closure.voucher_no = voucher_no closure.withdrawal_destination_chart = destination_chart closure.withdrawal_transaction = withdrawal_transaction closure.status = 'closed' closure.closed_at = record_date closure.closure_completed_by = user closure.last_updated = timezone.now() closure.save() return closure def save_sender_transactions(user, request, reciever, extra_data = None): charge = 0 request_data = request.data sender_id = request.data.get('sender_id') if not extra_data else extra_data['sender_id'] send_sms = request_data.get('send_sms') if not extra_data else extra_data['send_sms'] heading = extra_data.get('heading', None) if extra_data else None voucher_no = request.data.get('voucher_no') if not extra_data else extra_data.get('voucher_no', None) organisation_id = None branch_id = None current_amount = reciever['charge'] reciever_id = reciever['id'] customer_account = SavingAccount.objects.get(pk=sender_id) reciever_account = SavingAccount.objects.get(pk=reciever_id) allowed, _, customer_account = can_debit_savings_account(customer_account, as_at=reciever.get('date')) if not allowed: return None if extra_data and extra_data.get('source', '') == 'ussd': organisation_id = customer_account.customer_branch.branch_organisation.id branch_id = customer_account.customer_branch.id else: organisation_id = get_current_user(request, 'organisation_id', None) branch_id = get_current_user(request, 'organisation_branch_id', None) # InterBranch chart interbranch_chart = get_inter_branch_chart(customer_account.customer_branch, reciever_account.customer_branch) reference_no = generate_reference_no(customer_account.account_product.accounts_chart.account_line, organisation_id,'sav-tr') amount = float(reciever['amount']) description = request_data.get('description') if not extra_data else extra_data['description'] transfer_charge = SavingProductCharge.objects.filter(saving_product = customer_account.account_product,charge_key = 'transfer_charge').first() account_balance = get_account_balance(customer_account.id) saved_transaction = None print(".......................... transfer sender 1. .........") if customer_account.account_product.transfer_custom_charge: print(".......................... transfer sender 2. .........") charge = float(current_amount) else: print(".......................... transfer sender 3. .........") if transfer_charge: print(".......................... transfer sender 4. .........") if customer_account.account_product.default_charge_type == 'flat': charge = round(float(current_amount)/100)*100 print(".......................... transfer sender 5. .........") if customer_account.account_product.default_charge_type != 'flat': charge_amount = (float(current_amount)/100)*amount charge = round(charge_amount/100) * 100 print(".......................... transfer sender 6. .........") print(".................. Charge ............. "+ str(charge)) print(".................. amount ............. "+ str(amount)) print(".................. balance_raw ............. "+ str(account_balance['balance_raw'])) if account_balance['balance_raw'] >= (amount + charge): print(".......................... transfer sender 7. .........") transfer_desc = description if description else f'Cash transfer by {customer_account.account_customer.name} (A/C No: {customer_account.account_no}) to {reciever_account.account_customer.name} (A/C No: {reciever_account.account_no})' trans_heading = heading if heading else transfer_desc # heading = heading if heading else f'Cash transfer by {customer_account.account_customer.name} (A/C No: {customer_account.account_no}) to {reciever_account.account_customer.name} (A/C No: {reciever_account.account_no})' transaction_fields = { "heading":trans_heading, "coment":transfer_desc, "amount":amount, "debit_chart":customer_account.account_product.accounts_chart, "credit_chart":OrganisationSubAccount.objects.get(account_code='sys-215', account_organisation_id=organisation_id), "reference_no":reference_no, "voucher_no": voucher_no, "payment_method":"offset", "added_by":user, "branch":customer_account.customer_branch, "record_date":reciever['date'] } # Handle inter-branch transactions update -> soure branch if customer_account.customer_branch != reciever_account.customer_branch: print(".......................... transfer sender 8. .........") inter_transfer_desc = description if description else f'cash transfer by {customer_account.account_customer.name} (A/C No: {customer_account.account_no}) to {reciever_account.account_customer.name} (A/C No: {reciever_account.account_no})' trans_heading = f'Inter-branch {heading}' if heading else f'Inter-branch {inter_transfer_desc}' # trans_heading = f'Inter-branch {heading}' if heading else f'Inter-branch Cash transfer by {customer_account.account_customer.name} (A/C No: {customer_account.account_no}) to {reciever_account.account_customer.name} (A/C No: {reciever_account.account_no})' transaction_fields["heading"] = trans_heading transaction_fields["branch"] = customer_account.customer_branch transaction_fields["credit_chart"] = interbranch_chart saved_system_transaction = SystemTransactions.objects.create(**transaction_fields) if saved_system_transaction: print(".......................... transfer sender 9. .........") saving_fields = { "transaction_type":"transfer", "transaction":saved_system_transaction, "customer_account":customer_account } saved_transaction = SavingAccountTransactions.objects.create(**saving_fields) if charge > 0 and transfer_charge: print(".......................... transfer sender 10. .........") reference_no = generate_reference_no(transfer_charge.accounts_chart.account_line, organisation_id) charge_fields = { "heading":'Cash transfer charge:' + ' on A/C No: ' +customer_account.account_no, "amount":charge, "credit_chart":transfer_charge.accounts_chart, "debit_chart":customer_account.account_product.accounts_chart, "reference_no":reference_no, "payment_method":"settlement", "added_by":user, "branch":OrganisationBranch.objects.get(pk=branch_id), "record_date":reciever['date'] } # Handle inter-branch charge transactions update -> source branch if customer_account.customer_branch.id != branch_id: print(".......................... transfer sender 11. .........") # InterBranch chart interbranch_charge_chart = get_inter_branch_chart(customer_account.customer_branch, branch_id) charge_fields["heading"] = "Inter-branch cash transfer charge: " + " on A/C No: " +customer_account.account_no charge_fields["branch"] = OrganisationBranch.objects.get(pk=branch_id) charge_fields["debit_chart"] = interbranch_charge_chart charge_transaction = SystemTransactions.objects.create(**charge_fields) if charge_transaction: print(".......................... transfer sender 12. .........") # Handle inter-branch charge transactions update -> destination branch if customer_account.customer_branch.id != branch_id: charge_fields["debit_chart"] = customer_account.account_product.accounts_chart charge_fields["branch"] = customer_account.customer_branch charge_fields["credit_chart"] = interbranch_charge_chart charge_fields["payment_method"] = 'offset' inter_branch_charge_trans = SystemTransactions.objects.create(**charge_fields) # Reconcile inter-branch transactions if inter_branch_charge_trans: print(".......................... transfer sender 13. .........") inter_branch_trans_field = { "source_transaction":charge_transaction, "destination_transaction":inter_branch_charge_trans, "added_by":user, } InterBranchTransactions.objects.create(**inter_branch_trans_field) saving_fields = { "transaction_type":"transfer_charge", "transaction":inter_branch_charge_trans, "customer_account":customer_account, "parent_id":saved_transaction.id } SavingAccountTransactions.objects.create(**saving_fields) else: print(".......................... transfer sender 14. .........") saving_fields = { "transaction_type":"transfer_charge", "transaction":charge_transaction, "customer_account":customer_account, "parent_id":saved_transaction.id } SavingAccountTransactions.objects.create(**saving_fields) if send_sms and saved_transaction: print(".......................... transfer sender 15. .........") sms_msg = 'Dear '+ customer_account.account_customer.name.capitalize()+', you have sent UGX: '+ f"{saved_transaction.transaction.amount:,}"+' from A/C: '+customer_account.account_no+' to '+reciever_account.account_customer.name.capitalize() data = {"sms_key":"cash_transfer_sms","customer_account":customer_account,"user":user,"branch_id":branch_id,"save_trans":saved_transaction,"sms_msg":sms_msg,"customer":customer_account.account_customer} send_customer_sms(data) sync_savings_account_lifecycle(customer_account) print(".......................... transfer sender 16. .........") return saved_transaction def save_reciever_transactions(user, request, reciever, extra_data = None): request_data = request.data reciever_id = reciever['id'] sender_id = request.data.get('sender_id') if not extra_data else extra_data['sender_id'] send_sms = request_data.get('send_sms') if not extra_data else extra_data['send_sms'] organisation_id = None branch_id = None sender_account = SavingAccount.objects.get(pk=sender_id) customer_account = SavingAccount.objects.get(pk=reciever_id) allowed, _, customer_account = can_credit_savings_account(customer_account, as_at=reciever.get('date')) if not allowed: return None if extra_data and extra_data.get('source', '') == 'ussd': organisation_id = sender_account.customer_branch.branch_organisation.id branch_id = sender_account.customer_branch.id else: organisation_id = get_current_user(request, 'organisation_id', None) branch_id = get_current_user(request, 'organisation_branch_id', None) # InterBranch chart interbranch_chart = get_inter_branch_chart(sender_account.customer_branch, customer_account.customer_branch) reference_no = generate_reference_no(customer_account.account_product.accounts_chart.account_line, organisation_id,'sav-tr') amount = float(reciever['amount']) saved_transaction = None transaction_fields = { "heading":'Cash transfer: by ' +sender_account.account_customer.name + ' (A/C No: ' +sender_account.account_no+') to ' +customer_account.account_customer.name + '(A/C No: ' +customer_account.account_no+')', "coment":sender_account.account_customer.name + ' (A/C No: ' +sender_account.account_no+') has transfered cash of ('+str(amount)+') to ' +customer_account.account_customer.name + '(A/C No: ' +customer_account.account_no+')', "amount":amount, "credit_chart":customer_account.account_product.accounts_chart, "debit_chart":OrganisationSubAccount.objects.get(account_code='sys-215', account_organisation_id=organisation_id), "reference_no":reference_no, "voucher_no":"", "payment_method":"offset", "added_by":user, "branch":customer_account.customer_branch, "record_date":reciever['date'] } # Handle inter-branch transactions update -> soure branch if sender_account.customer_branch != customer_account.customer_branch: transaction_fields["heading"] = "Inter-branch cash transfer: by " +sender_account.account_customer.name + " A/C No: " +sender_account.account_no+" to " +customer_account.account_customer.name + " A/C No: " +customer_account.account_no transaction_fields["branch"] = customer_account.customer_branch transaction_fields["debit_chart"] = interbranch_chart saved_system_transaction = SystemTransactions.objects.create(**transaction_fields) if saved_system_transaction: saving_fields = { "transaction_type":"transfer", "transaction":saved_system_transaction, "customer_account":customer_account } saved_transaction = SavingAccountTransactions.objects.create(**saving_fields) if send_sms: sms_msg = 'Dear '+customer_account.account_customer.name.capitalize()+', you have recieved UGX: '+f"{saved_transaction.transaction.amount:,}"+' on A/C: '+customer_account.account_no+' from '+sender_account.account_customer.name.capitalize() data = {"sms_key":"cash_transfer_sms","customer_account":customer_account,"user":user,"branch_id":branch_id,"save_trans":saved_transaction,"sms_msg":sms_msg,"customer":customer_account.account_customer} send_customer_sms(data) sync_savings_account_lifecycle(customer_account) return saved_transaction def pay_fixed_deposit_schedule(schedule, amount=None): if not isinstance(schedule, FixedDepositSchedule): schedule = FixedDepositSchedule.objects.active().get(pk=schedule) fd_expense_chart_code = 'sys-5113' fd_withholding_chart_code = 'sys-2122' branch_id = schedule.fixed_deposit.branch.id savings_account = schedule.fixed_deposit.saving_account organisation_id = schedule.fixed_deposit.branch.branch_organisation.id fd_expense_chart = OrganisationSubAccount.objects.filter(account_code=fd_expense_chart_code, account_organisation=organisation_id) fd_withholding_chart = OrganisationSubAccount.objects.filter(account_code=fd_withholding_chart_code, account_organisation=organisation_id) saving_product_chart = savings_account.account_product.accounts_chart fixed_deposit_pay_details = { "amount":0, "withholding_tax_amount":0, "branch":schedule.fixed_deposit.branch.id, "branch_name":schedule.fixed_deposit.branch.name, "payment_date":0, "customer":savings_account.account_customer.name, "member_number":savings_account.account_customer.member_number, "fixed_amount":schedule.fixed_deposit.amount } # Post payment transaction. transaction_details = { "heading": "Fixed Deposit Interest Payment: " + savings_account.account_customer.name + " - " + savings_account.account_no, "amount": schedule.interest, "record_date": timezone.now(), "debit_chart_id": fd_expense_chart[0].id, "credit_chart_id": saving_product_chart.id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": 'fx-py', "organisation_id": organisation_id, "branch_id": branch_id, "user_id": None } if amount: transaction_details['amount'] = amount # inter-branch payment transaction_1 = None if branch_id != savings_account.customer_branch.id: source_branch = OrganisationBranch.objects.get(pk=branch_id) destination_branch = OrganisationBranch.objects.get(pk=savings_account.customer_branch.id) interbranch_chart = get_inter_branch_chart(source_branch, destination_branch) heading = 'Interbranch '+ transaction_details['heading'] # second transaction transaction1_data = transaction_details transaction1_data['heading'] = heading transaction1_data['debit_chart_id'] = fd_expense_chart[0].id transaction1_data['credit_chart_id'] = interbranch_chart.id transaction1_data['branch_id'] = branch_id transaction_1 = post_transaction(transaction1_data) #transaction1 transaction_details['heading'] = heading transaction_details['debit_chart_id'] = interbranch_chart.id transaction_details['credit_chart_id'] = saving_product_chart.id transaction_details['branch_id'] = savings_account.customer_branch.id # Save general transaction payment = post_transaction(transaction_details) # Link payment to schedule schedule.reference_transaction = transaction_1 if transaction_1 is not None else payment schedule.status = 'paid' schedule.save() fixed_deposit_pay_details['amount'] = transaction_details['amount'] fixed_deposit_pay_details['payment_date'] = transaction_details['record_date'] # interbranch mapping if transaction_1 and payment: inter_branch_trans_field = { "source_transaction": transaction_1, "destination_transaction": payment, "added_by": None, } InterBranchTransactions.objects.create(**inter_branch_trans_field) # Link payment to savings account account_transaction = SavingAccountTransactions(customer_account=savings_account, transaction=payment, transaction_type='fixed-deposit-payment') account_transaction.save() # Deduct withholding tax if schedule.fixed_deposit.withholding_tax: # Post deduction transaction. withholding_tax = 15 withholding_tax_setting = OrganisationSetting.objects.filter(org_setting__id=schedule.fixed_deposit.branch.branch_organisation.id,setting_key='withholding_tax').first() if withholding_tax_setting: withholding_tax = float(withholding_tax_setting.setting_value) transaction_details = { "heading": "Fixed Deposit Interest Tax: " + savings_account.account_customer.name + " - " + savings_account.account_no, "amount": float(schedule.interest * (withholding_tax * 0.01)), "record_date": timezone.now(), "debit_chart_id": saving_product_chart.id, "credit_chart_id": fd_withholding_chart[0].id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": 'fx-py-t', "organisation_id": organisation_id, "branch_id": branch_id, "user_id": None } transaction_2 = None if branch_id != savings_account.customer_branch.id: source_branch = OrganisationBranch.objects.get(pk=savings_account.customer_branch.id) destination_branch = OrganisationBranch.objects.get(pk=branch_id) interbranch_chart = get_inter_branch_chart(source_branch, destination_branch) heading = 'Interbranch '+ transaction_details['heading'] transaction_details['heading'] = heading transaction_details['debit_chart_id'] = saving_product_chart.id transaction_details['credit_chart_id'] = interbranch_chart.id transaction_details['branch_id'] = savings_account.customer_branch.id # second transaction transaction2_data = transaction_details transaction2_data['heading'] = heading transaction2_data['debit_chart_id'] = interbranch_chart.id transaction2_data['credit_chart_id'] = fd_withholding_chart[0].id transaction2_data['branch_id'] = branch_id transaction_2 = post_transaction(transaction2_data) # Save deduction transaction tax_transaction = post_transaction(transaction_details) # interbranch mapping if transaction_2 and tax_transaction: inter_branch_trans_field = { "source_transaction": tax_transaction, "destination_transaction": transaction_2, "added_by": None, } InterBranchTransactions.objects.create(**inter_branch_trans_field) # Link deduction to savings account parent_id = account_transaction.id account_transaction = SavingAccountTransactions(customer_account=savings_account, transaction=tax_transaction, transaction_type='deposit_charge', parent_id=parent_id) account_transaction.save() fixed_deposit_pay_details['withholding_tax_amount'] = transaction_details['amount'] fixed_deposit_pay_details['payment_date'] = transaction_details['record_date'] if branch_id and schedule.fixed_deposit.branch and fixed_deposit_pay_details['amount'] is not None: save_user_notification({ "heading": "Fixed Deposit Interest Auto Payment", "message": f"Fixed Deposit Interest Payment. Interest {fixed_deposit_pay_details['amount']} and withholding tax of {fixed_deposit_pay_details['withholding_tax_amount']} for fixed amount of {fixed_deposit_pay_details['fixed_amount']} customer {fixed_deposit_pay_details['customer']} member number: {fixed_deposit_pay_details['member_number']} as at {fixed_deposit_pay_details['payment_date'].date()}", "branch":OrganisationBranch.objects.get(pk=branch_id), "branch_name":fixed_deposit_pay_details['branch_name'], "added_by":None, "last_updated_by":None, "key":"savings_notifications" }) # def save_reciever_transactions(user,request,reciever, extra_data = None): # request_data = request.data # reciever_id = reciever['id'] # sender_id = request.data.get('sender_id') if not extra_data else extra_data['sender_id'] # send_sms = request_data.get('send_sms') if not extra_data else extra_data['send_sms'] # heading = extra_data.get('heading', None) if extra_data else None # voucher_no = request.data.get('voucher_no') if not extra_data else extra_data.get('voucher_no', None) # organisation_id = get_current_user(request, 'organisation_id', None) # branch_id = get_current_user(request, 'organisation_branch_id', None) # sender_account = SavingAccount.objects.get(pk=sender_id) # customer_account = SavingAccount.objects.get(pk=reciever_id) # # InterBranch chart # interbranch_chart = get_inter_branch_chart(sender_account.customer_branch, customer_account.customer_branch) # reference_no = generate_reference_no(customer_account.account_product.accounts_chart.account_line, organisation_id,'sav-tr') # amount = float(reciever['amount']) # saved_transaction = None # description = request_data.get('description') if not extra_data else extra_data['description'] # # trans_heading = heading if heading else f'Cash transfer: by {sender_account.account_customer.name } (A/C No: { sender_account.account_no }) to { customer_account.account_customer.name } (A/C No: { customer_account.account_no})' # transfer_desc = description if description else f'Cash transfer by {sender_account.account_customer.name} (A/C No: {sender_account.account_no }) to {customer_account.account_customer.name} (A/C No: {customer_account.account_no})' # trans_heading = heading if heading else transfer_desc # transaction_fields = { # "heading": trans_heading, # "coment":transfer_desc, # "amount":amount, # "credit_chart":customer_account.account_product.accounts_chart, # "debit_chart":OrganisationSubAccount.objects.get(account_code='sys-215', account_organisation_id=organisation_id), # "reference_no":reference_no, # "voucher_no": voucher_no, # "payment_method":"offset", # "added_by":user, # "branch":customer_account.customer_branch, # "record_date":reciever['date'] # } # # Handle inter-branch transactions update -> soure branch # if sender_account.customer_branch != customer_account.customer_branch: # # trans_heading = f'Inter-branch {heading}' if heading else f'Inter-branch cash transfer: by {sender_account.account_customer.name } (A/C No: { sender_account.account_no }) to { customer_account.account_customer.name } (A/C No: { customer_account.account_no})' # inter_transfer_desc = description if description else f'cash transfer by {sender_account.account_customer.name} (A/C No: {sender_account.account_no}) to {customer_account.account_customer.name} (A/C No: {customer_account.account_no})' # trans_heading = f'Inter-branch {heading}' if heading else f'Inter-branch {inter_transfer_desc}' # transaction_fields["heading"] = trans_heading # transaction_fields["branch"] = customer_account.customer_branch # transaction_fields["debit_chart"] = interbranch_chart # saved_system_transaction = SystemTransactions.objects.create(**transaction_fields) # if saved_system_transaction: # saving_fields = { # "transaction_type":"transfer", # "transaction":saved_system_transaction, # "customer_account":customer_account # } # saved_transaction = SavingAccountTransactions.objects.create(**saving_fields) # if send_sms: # sms_msg = 'Dear '+customer_account.account_customer.name.capitalize()+', you have recieved UGX: '+f"{saved_transaction.transaction.amount:,}"+' on A/C: '+customer_account.account_no+' from '+sender_account.account_customer.name.capitalize() # data = {"sms_key":"cash_transfer_sms","customer_account":customer_account,"user":user,"branch_id":branch_id,"save_trans":saved_transaction,"sms_msg":sms_msg,"customer":customer_account.account_customer} # send_customer_sms(data) # return saved_transaction def pay_fixed_deposit_pay_one_batch(fd_pay_details): fd_expense_chart_code = 'sys-5113' fd_withholding_chart_code = 'sys-2122' branch_id = fd_pay_details['branch_id'] saving_account = fd_pay_details['saving_account'] amount = fd_pay_details['amount'] interest = fd_pay_details['interest'] organisation_id = fd_pay_details['organisation_id'] withholding_tax_active = fd_pay_details['withholding_tax_active'] user_id = fd_pay_details['user_id'] fd_expense_chart = OrganisationSubAccount.objects.filter(account_code=fd_expense_chart_code, account_organisation=organisation_id) fd_withholding_chart = OrganisationSubAccount.objects.filter(account_code=fd_withholding_chart_code, account_organisation=organisation_id) saving_product_chart = saving_account.account_product.accounts_chart # Post payment transaction. transaction_details = { "heading": "Fixed Deposit Interest Payment: " + saving_account.account_customer.name + " - " + saving_account.account_no, "amount": interest, "record_date": timezone.now(), "debit_chart_id": fd_expense_chart[0].id, "credit_chart_id": saving_product_chart.id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": 'fx-py', "organisation_id": organisation_id, "branch_id": branch_id, "user_id": user_id } if amount: transaction_details['amount'] = amount # inter-branch payment transaction_1 = None if branch_id != saving_account.customer_branch.id: source_branch = OrganisationBranch.objects.get(pk=branch_id) destination_branch = OrganisationBranch.objects.get(pk=saving_account.customer_branch.id) interbranch_chart = get_inter_branch_chart(source_branch, destination_branch) heading = 'Interbranch '+ transaction_details['heading'] transaction_details['heading'] = heading transaction_details['debit_chart_id'] = interbranch_chart.id transaction_details['credit_chart_id'] = saving_product_chart.id transaction_details['branch_id'] = saving_account.customer_branch.id # second transaction transaction1_data = transaction_details transaction1_data['debit_chart_id'] = fd_expense_chart[0].id transaction1_data['credit_chart_id'] = interbranch_chart.id transaction1_data['branch_id'] = branch_id transaction_1 = post_transaction(transaction1_data) # Save general transaction payment = post_transaction(transaction_details) # Link payment to the last schedule # Link payment to savings account account_transaction = SavingAccountTransactions(customer_account=saving_account, transaction=payment, transaction_type='fixed-deposit-payment') account_transaction.save() # interbranch mapping if transaction_1 and payment: inter_branch_trans_field = { "source_transaction": transaction_1, "destination_transaction": payment, "added_by": get_user_model().objects.get(pk=user_id) } InterBranchTransactions.objects.create(**inter_branch_trans_field) # Deduct withholding tax if withholding_tax_active: # Post deduction transaction. withholding_tax = 15 withholding_tax_setting = OrganisationSetting.objects.filter(org_setting__id=organisation_id,setting_key='withholding_tax').first() if withholding_tax_setting: withholding_tax = float(withholding_tax_setting.setting_value) transaction_details = { "heading": "Fixed Deposit Interest Tax: " + saving_account.account_customer.name + " - " + saving_account.account_no, "amount": float(interest * (withholding_tax * 0.01)), "record_date": timezone.now(), "debit_chart_id": saving_product_chart.id, "credit_chart_id": fd_withholding_chart[0].id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": 'fx-py-t', "organisation_id": organisation_id, "branch_id": branch_id, "user_id": get_user_model().objects.get(pk=user_id) } transaction_2 = None if branch_id != saving_account.customer_branch.id: source_branch = OrganisationBranch.objects.get(pk=saving_account.customer_branch.id) destination_branch = OrganisationBranch.objects.get(pk=branch_id) interbranch_chart = get_inter_branch_chart(source_branch, destination_branch) heading = 'Interbranch '+ transaction_details['heading'] transaction_details['heading'] = heading transaction_details['debit_chart_id'] = saving_product_chart.id transaction_details['credit_chart_id'] = interbranch_chart.id transaction_details['branch_id'] = saving_account.customer_branch.id # second transaction transaction2_data = transaction_details transaction2_data['debit_chart_id'] = interbranch_chart.id transaction2_data['credit_chart_id'] = fd_withholding_chart[0].id transaction2_data['branch_id'] = branch_id transaction_2 = post_transaction(transaction2_data) # Save deduction transaction tax_transaction = post_transaction(transaction_details) # interbranch mapping if transaction_2 and tax_transaction: inter_branch_trans_field = { "source_transaction": tax_transaction, "destination_transaction": transaction_2, "added_by": get_user_model().objects.get(pk=user_id) } InterBranchTransactions.objects.create(**inter_branch_trans_field) # Link deduction to savings account parent_id = account_transaction.id account_transaction = SavingAccountTransactions(customer_account=saving_account, transaction=tax_transaction, transaction_type='deposit_charge', parent_id=parent_id) account_transaction.save() return payment def process_bulk_fixed_deposit_payment(): time = timezone.now() time = make_aware(datetime.strptime(time.strftime("%Y-%m-%d") + ' 23:59', '%Y-%m-%d %H:%M')) fixed_deposit_schedules = FixedDepositSchedule.objects.active().filter(fixed_deposit__status='pending', fixed_deposit__auto_payments=True, status='pending', expected_date__lte=time) # make schedule payments for schedule in fixed_deposit_schedules: pay_fixed_deposit_schedule(schedule) def process_bulk_fixed_deposit_closure(): today = date.today() today = today.strftime("%Y-%m-%d") fixed_deposits_due = FixedDeposit.objects.annotate( latest_instalment_date=Max( 'fixed_deposit__expected_date', filter=Q(fixed_deposit__deleted=False), ) ).filter(latest_instalment_date__date__lte=today, status='pending', auto_close=True) for fixed_deposit in fixed_deposits_due: amount = FixedDepositSchedule.objects.active().filter(fixed_deposit=fixed_deposit, status='pending').aggregate(total=Sum('interest'))['total'] if amount: # Delete all unpaid schedules and create a single merged schedule. FixedDepositSchedule.objects.active().filter(fixed_deposit=fixed_deposit, status='pending').delete() # Create two schedules, one to take paid interest and the other to take unpiad interest. paid_schedule = FixedDepositSchedule(fixed_deposit=fixed_deposit, expected_date=fixed_deposit.latest_instalment_date, interest=amount) paid_schedule.save() pay_fixed_deposit_schedule(paid_schedule) # Transfer principal to member account. savings_account = fixed_deposit.saving_account organisation_id = savings_account.account_customer.customer_branch.branch_organisation.id heading = 'Fixed deposit principal (' + savings_account.account_customer.name + '-' + savings_account.account_customer.old_member_number + ')' fixed_deposit_ledger_code = 'sys-2111' fixed_deposit_ledger = OrganisationSubAccount.objects.filter(account_code=fixed_deposit_ledger_code, account_organisation_id=organisation_id) reference_no = generate_reference_no(fixed_deposit_ledger[0].account_line, organisation_id, 'fx-d-p') # Save Payment # inter-branch payment transaction_1 = None branch_id = fixed_deposit.branch.id transaction_details = { "amount": fixed_deposit.amount, "heading": heading, "payment_method":'offset', "reference_no": reference_no, "credit_chart": savings_account.account_product.accounts_chart, "debit_chart":fixed_deposit_ledger[0], "branch_id":savings_account.customer_branch.id } # Define fd_expense_chart before usage fd_expense_chart_code = 'sys-5113' fd_expense_chart = OrganisationSubAccount.objects.filter(account_code=fd_expense_chart_code, account_organisation=organisation_id) if branch_id != savings_account.customer_branch.id: source_branch = OrganisationBranch.objects.get(pk=branch_id) destination_branch = OrganisationBranch.objects.get(pk=savings_account.customer_branch.id) interbranch_chart = get_inter_branch_chart(source_branch, destination_branch) heading = 'Interbranch '+ transaction_details['heading'] transaction_details['heading'] = heading transaction_details['debit_chart_id'] = interbranch_chart.id transaction_details['credit_chart_id'] = savings_account.account_product.accounts_chart transaction_details['branch_id'] = savings_account.customer_branch.id # second transaction transaction1_data = transaction_details transaction1_data['debit_chart_id'] = fd_expense_chart[0].id transaction1_data['credit_chart_id'] = interbranch_chart.id transaction1_data['branch_id'] = branch_id transaction_1 = post_transaction(transaction1_data) transaction = SystemTransactions(**transaction_details) transaction.save() fixed_deposit_close = { "amount":amount, "branch":fixed_deposit.branch.id, "branch_name":fixed_deposit.branch.name, "payment_date":transaction.record_date.date(), "customer":savings_account.account_customer.name, "member_number":savings_account.account_customer.member_number, } deduction_details = { "customer_account": savings_account, "transaction": transaction, "transaction_type": 'fixed-deposit' } SavingAccountTransactions.objects.create(**deduction_details) # Close FD fixed_deposit.closure_transaction = transaction_1 if transaction_1 is not None else transaction fixed_deposit.status = 'closed' fixed_deposit.save() # interbranch mapping if transaction_1 and transaction: inter_branch_trans_field = { "source_transaction": transaction_1, "destination_transaction": transaction, "added_by": None, } InterBranchTransactions.objects.create(**inter_branch_trans_field) if fixed_deposit_close['amount'] is not None: save_user_notification({ "heading": "Fixed Deposit Auto Closure", "message": f"Fixed Deposit Auto Closure for fixed amount {fixed_deposit_close['amount']} for customer {fixed_deposit_close['customer']} member number: {fixed_deposit_close['member_number']} as at {fixed_deposit_close['payment_date']}", "branch":OrganisationBranch.objects.get(pk=fixed_deposit.branch.id), "branch_name":fixed_deposit_close['branch_name'], "added_by":None, "last_updated_by":None, "key":"savings_notifications" }) def process_bulk_savings_amount_unblocking(): time = timezone.now() time = make_aware(datetime.strptime(time.strftime("%Y-%m-%d") + ' 23:59', '%Y-%m-%d %H:%M')) blocked_amounts = SavingsBlockedAmount.objects.filter(expiry_date__lte=time, status='active', manual_unblock=False) for blocked_amount in blocked_amounts: blocked_amount.status = 'inactive' blocked_amount.save() branch_id = blocked_amount.customer_branch.id save_user_notification({ "heading": "Saving Auto UnBlocking", "message": f"Savings Auto UnBlocking. Amount: {blocked_amount.amount} for {blocked_amount.customer_account.account_customer.name} member number {blocked_amount.customer_account.account_customer.member_number} as at {blocked_amount.date_added.date()}", "branch":OrganisationBranch.objects.get(pk=branch_id), "branch_name":blocked_amount.customer_branch.name, "added_by":None, "last_updated_by":None, "key":"savings_notifications" }) def update_savings_account_statuses(organisation_id=None, branch_id=None, customer_id=None, account_ids=None): updated_accounts = 0 filters = Q(deleted=False) if organisation_id is not None: filters &= Q(customer_branch__branch_organisation_id=organisation_id) if branch_id is not None: filters &= Q(customer_branch_id=branch_id) if customer_id is not None: filters &= Q(account_customer_id=customer_id) if account_ids is not None: filters &= Q(id__in=account_ids) accounts = SavingAccount.objects.filter(filters).select_related("account_product").annotate( latest_transaction_date=Max( "customer_account__transaction__record_date", filter=Q( customer_account__deleted=False, customer_account__transaction__deleted=False, ), ) ) for account in accounts: _, _, changed = sync_savings_account_lifecycle( account, last_activity_at=getattr(account, "latest_transaction_date", None), ) if changed: updated_accounts += 1 return updated_accounts def process_savings_withdraw(withdraw_details): charge = 0 organisation_id = withdraw_details['organisation_id'] branch_id = withdraw_details['organisation_branch_id'] credit_chart_id = withdraw_details['credit_chart'] record_date = withdraw_details['record_date'] voucher_no = withdraw_details['voucher_no'] payment_method = withdraw_details['payment_method'] amount = float(withdraw_details['amount']) status = withdraw_details['status'] receiver = withdraw_details['receiver'] send_sms = withdraw_details['send_sms'] added_by = withdraw_details['added_by'] pending_withdrawal = withdraw_details['pending_withdrawal'] customer_account_id = withdraw_details['customer_account'] withdrawal_charge_amount = withdraw_details['charge'] inter_branch_trans = None save_trans = None customer_account = SavingAccount.objects.get(pk=customer_account_id) assert_savings_account_can_debit(customer_account, "withdrawals", as_at=record_date) max_withdraws = customer_account.account_product.max_withdraws # InterBranch chart interbranch_chart = get_inter_branch_chart(OrganisationBranch.objects.get(pk=branch_id), customer_account.customer_branch) reference_no = generate_reference_no(customer_account.account_product.accounts_chart.account_line, organisation_id, 'wd') # Default withdraw charge withdrawal_charge = SavingProductCharge.objects.filter(saving_product=customer_account.account_product, charge_key='withdrawal_charge').first() if customer_account.account_product.withdraw_custom_charge: charge = float(withdrawal_charge_amount) else: # Default withdraw charge if withdrawal_charge and float(withdrawal_charge_amount) > 0: if customer_account.account_product.default_charge_type == 'flat': charge = round(float(withdrawal_charge_amount)/100)*100 if customer_account.account_product.default_charge_type != 'flat': charge_amount = (float(withdrawal_charge_amount)/100)*amount charge = round(charge_amount/100) * 100 # Max validate withdraw amount if(float(max_withdraws) > 0) and amount > float(max_withdraws): # Ignore the default charge and apply withdrawal limit charge if customer_account.account_product.max_withdraw_limit_charge_type == 'flat': charge = round(float(withdrawal_charge_amount)/100)*100 if customer_account.account_product.max_withdraw_limit_charge_type != 'flat': charge_amount = (float(withdrawal_charge_amount)/100)*amount charge = round(charge_amount/100) * 100 if not receiver: receiver = customer_account.account_customer.name account_balance = get_account_balance(customer_account.id) if account_balance['balance_raw'] >= (amount + charge): transaction_fields = { "heading": 'Withdrawal by ' + receiver + ' From: ' + customer_account.account_customer.name + '-' + customer_account.account_customer.old_member_number, "coment": customer_account.account_customer.name + 'has withdrawn ('+str(amount)+') ' + 'from' + ' A/C No: ' + customer_account.account_no, "amount": amount, "debit_chart": customer_account.account_product.accounts_chart, "credit_chart": OrganisationSubAccount.objects.get(pk=credit_chart_id), "reference_no": reference_no, "voucher_no":voucher_no, "payment_method":payment_method, "added_by": get_user_model().objects.get(pk=added_by), "branch": OrganisationBranch.objects.get(pk=branch_id), "record_date": record_date } # Handle inter-branch transactions update -> soure branch if branch_id != customer_account.customer_branch.id: transaction_fields["heading"] = 'Inter-branch withdrawal by ' + receiver + ' From: ' + customer_account.account_customer.name + '-' + customer_account.account_customer.old_member_number transaction_fields["branch"] = OrganisationBranch.objects.get(pk=branch_id) transaction_fields["debit_chart"] = interbranch_chart # Register withdrawal transactions saved_transaction = SystemTransactions.objects.create(**transaction_fields) if saved_transaction: # Handle inter-branch transactions update -> destination branch if branch_id != customer_account.customer_branch.id: transaction_fields["debit_chart"] = customer_account.account_product.accounts_chart transaction_fields["branch"] = customer_account.customer_branch transaction_fields["credit_chart"] = interbranch_chart transaction_fields["payment_method"] = 'settlement' inter_branch_trans = SystemTransactions.objects.create(**transaction_fields) # Reconcile inter-branch transactions if inter_branch_trans: inter_branch_trans_field = { "source_transaction": saved_transaction, "destination_transaction": inter_branch_trans, "added_by": get_user_model().objects.get(pk=added_by), } InterBranchTransactions.objects.create(**inter_branch_trans_field) # Register saving withdrawal transaction mapping for inter-branch inter_branch_trans_field = { "source_transaction": saved_transaction, "destination_transaction": inter_branch_trans, "added_by": get_user_model().objects.get(pk=added_by), } saving_charge_fields = { "customer_account": customer_account, "transaction": inter_branch_trans, "parent_id": save_trans.id, "transaction_type": 'withdrawal_charge' } save_trans = SavingAccountTransactions.objects.create(**{ "customer_account": customer_account, "transaction": inter_branch_trans, "transaction_type": 'withdrawal' }) else: # Register saving withdrawal transaction mapping for single branch save_trans = SavingAccountTransactions.objects.create(**{ "customer_account": customer_account, "transaction": saved_transaction, "transaction_type": 'withdrawal' }) if save_trans: # Update pending withrawal with transaction Id. This indicate the approved withdrawal has been process. if status == 'approved': pending_withdrawal_obj = PendingWithdrawals.objects.get(pk=pending_withdrawal) pending_withdrawal_obj.saving_account_transaction = save_trans pending_withdrawal_obj.save() if charge > 0 and withdrawal_charge: reference_no = generate_reference_no(withdrawal_charge.accounts_chart.account_line, organisation_id) charge_fields = { "heading": 'Saving withdrawal charge on A/C No: ' + customer_account.account_no, "coment": 'Saving withdrawal charge on A/C No: ' + customer_account.account_no, "amount": charge, "credit_chart": withdrawal_charge.accounts_chart, "debit_chart": customer_account.account_product.accounts_chart, "reference_no": reference_no, "payment_method":payment_method, "added_by": get_user_model().objects.get(pk=added_by), "branch": OrganisationBranch.objects.get(pk=branch_id), "record_date": record_date } # Handle inter-branch withdrawal charges transactions update -> source branch if branch_id != customer_account.customer_branch.id: charge_fields["heading"] = 'Inter-branch saving withdrawal charge on A/C No: ' + customer_account.account_no charge_fields["branch"] = OrganisationBranch.objects.get(pk=branch_id) charge_fields["debit_chart"] = interbranch_chart charge_transaction = SystemTransactions.objects.create(**charge_fields) if charge_transaction: # Handle inter-branch withdrawal charges transactions update -> destination branch if branch_id != customer_account.customer_branch.id: charge_fields["debit_chart"] = customer_account.account_product.accounts_chart charge_fields["branch"] = customer_account.customer_branch charge_fields["credit_chart"] = interbranch_chart charge_fields["payment_method"] = 'settlement' inter_branch_trans = SystemTransactions.objects.create(**charge_fields) # Reconcile inter-branch transactions if inter_branch_trans: inter_branch_trans_field = { "source_transaction": charge_transaction, "destination_transaction": inter_branch_trans, "added_by": get_user_model().objects.get(pk=added_by), } InterBranchTransactions.objects.create(**inter_branch_trans_field) # Register saving withdrawal charge transactions for inter-branch saving_charge_fields = { "customer_account": customer_account, "transaction": inter_branch_trans, "parent_id": save_trans.id, "transaction_type": 'withdrawal_charge' } SavingAccountTransactions.objects.create(**saving_charge_fields) else: # Register saving withdrawal charge transactions for single branch saving_charge_fields = { "customer_account": customer_account, "transaction": charge_transaction, "parent_id": save_trans.id, "transaction_type": 'withdrawal_charge' } SavingAccountTransactions.objects.create(**saving_charge_fields) if send_sms: data = {"sms_key": "cash_withdraw_sms", "customer_account": customer_account, "user":get_user_model().objects.get(pk=added_by), "branch_id": branch_id, "save_trans": save_trans,"customer":customer_account.account_customer} send_customer_sms(data) sync_savings_account_lifecycle(customer_account) return save_trans def update_group_member_transactions(main_transaction,member_withdraw_list): if len(member_withdraw_list) > 0: for member_detail in member_withdraw_list: membership = GroupMembership.objects.filter(member__id=member_detail['member_id'], active=True).first() if membership: group_trans_field = { "membership":membership, "savings": main_transaction } GroupSavingTransaction.objects.create(**group_trans_field) def process_group_deposits(user,request): charge = 0 branch_id = get_current_user(request, 'organisation_branch_id', None) debit_chart_id = request.data.get('debit_chart') send_sms = request.data.get('send_sms') deposits = request.data.get('deposits') comment = request.data.get('heading') notify_admin = request.data.get('notify_admin') deposit_charge_amount = request.data.get('charge',0) response_status = False success_count = 0 total_deposits = 0 customer_account = SavingAccount.objects.get(pk=request.data.get('credit_chart')) assert_savings_account_can_credit(customer_account, "deposits", as_at=request.data.get('record_date')) if customer_account: if deposits: if len(deposits) > 0: for deposit in deposits: amount = float(deposit['amount']) member_id = deposit['member_id'] customer = Customer.objects.get(pk=member_id) if customer: deposit_charge = SavingProductCharge.objects.filter(saving_product=customer_account.account_product, charge_key='deposit_charge').first() if deposit_charge and float(deposit_charge_amount) > 0: if customer_account.account_product.default_charge_type == 'flat': charge = round(float(deposit_charge_amount)/100)*100 if customer_account.account_product.default_charge_type != 'flat': charge_amount = (float(deposit_charge_amount)/100)*amount charge = round(charge_amount/100) * 100 if amount > charge: deposit_details = { "heading":'Deposit: by ' + customer.name + ' for ' + customer_account.account_customer.name + '-' + customer_account.account_customer.old_member_number, "coment": comment, "amount": amount, "credit_chart": customer_account.account_product.accounts_chart, "debit_chart": OrganisationSubAccount.objects.get(pk=debit_chart_id), "voucher_no": request.data.get('voucher_no'), "record_date": request.data.get('record_date'), "payment_method": request.data.get('payment_method'), "user": get_user_model().objects.get(pk=user.id), "branch": OrganisationBranch.objects.get(pk=branch_id), "customer":customer, "customer_account":customer_account, "charge":charge, "send_sms":send_sms, "is_group_deposit":True } sav_trans = process_customer_deposits(deposit_details) if sav_trans: success_count += 1 total_deposits += amount response_status = True if notify_admin and total_deposits > 0: sms_msg = 'Dear '+customer_account.account_customer.name.capitalize()+', Credit Deposit UGX: '+f"{total_deposits:,}"+' on A/C: '+customer_account.account_no data = {"sms_key": "deposit_notify_admin", "customer_account": customer_account, "user": deposit_details["user"], "branch_id": branch_id, "save_trans": None,"sms_msg":sms_msg,"customer":customer_account.account_customer} send_customer_sms(data) # Send SMS to two group signatories signatories = GroupMembership.objects.filter( group=customer_account.account_customer, active=True, role__in=['Chair Person', 'Secretary', 'Treasurer'] )[:2] for signatory in signatories: signatory_sms_msg = f'Dear {signatory.member.name.capitalize()}, Group Credit Deposit UGX: {total_deposits:,} on A/C: {customer_account.account_no}' signatory_data = {"sms_key": "deposit_notify_admin", "customer_account": customer_account, "user": deposit_details["user"], "branch_id": branch_id, "save_trans": None, "sms_msg": signatory_sms_msg, "customer": signatory.member} send_customer_sms(signatory_data) return {"response_status":response_status,"success_count":success_count} def process_customer_deposits(deposit_details): branch_id = deposit_details["branch"].id organisation_id = deposit_details["branch"].branch_organisation.id customer_account = deposit_details["customer_account"] amount = deposit_details["amount"] customer = deposit_details["customer"] send_sms = deposit_details["send_sms"] charge = deposit_details["charge"] is_group_deposit = deposit_details["is_group_deposit"] saved_transaction = None # InterBranch chart interbranch_chart = get_inter_branch_chart(OrganisationBranch.objects.get(pk=branch_id), customer_account.customer_branch) reference_no = generate_reference_no(customer_account.account_product.accounts_chart.account_line, organisation_id, 'dep') if amount > charge: transaction_fields = { "heading":deposit_details["heading"], "coment": deposit_details["coment"], "amount": amount, "credit_chart": deposit_details["credit_chart"], "debit_chart": deposit_details["debit_chart"], "reference_no": reference_no, "voucher_no": deposit_details["voucher_no"], "record_date":deposit_details["record_date"], "payment_method": deposit_details["payment_method"], "added_by": deposit_details["user"], "branch":deposit_details["branch"], } # Handle inter-branch transactions update -> soure branch if branch_id != customer_account.customer_branch.id: transaction_fields["heading"] = 'Inter-branch deposit by ' + customer.name + ' for ' + customer_account.account_customer.name + '-' + customer_account.account_customer.old_member_number transaction_fields["branch"] = OrganisationBranch.objects.get(pk=branch_id) transaction_fields["credit_chart"] = interbranch_chart # Register deposit transactions saved_transaction = SystemTransactions.objects.create(**transaction_fields) if saved_transaction: save_trans = None # Handle inter-branch transactions update -> destination branch if branch_id != customer_account.customer_branch.id: transaction_fields["credit_chart"] = customer_account.account_product.accounts_chart transaction_fields["branch"] = customer_account.customer_branch transaction_fields["debit_chart"] = interbranch_chart transaction_fields["payment_method"] = 'settlement' inter_branch_trans = SystemTransactions.objects.create(**transaction_fields) # Reconcile inter-branch transactions if inter_branch_trans: inter_branch_trans_field = { "source_transaction": saved_transaction, "destination_transaction": inter_branch_trans, "added_by":deposit_details["user"], } InterBranchTransactions.objects.create(**inter_branch_trans_field) # Register saving deposit transaction mapping for inter-branch saving_trans_trans_field = { "transaction_type":'deposit', "customer_account": customer_account, "transaction":inter_branch_trans } save_trans = SavingAccountTransactions.objects.create(**saving_trans_trans_field) else: # add saving deposit transaction mapping for single branch saving_trans_trans_field = { "transaction_type":'deposit', "customer_account": customer_account, "transaction":saved_transaction } save_trans = SavingAccountTransactions.objects.create(**saving_trans_trans_field) if save_trans: # Register group deposit mapping. i.e a user deposited money to group account membership = GroupMembership.objects.filter(member__id=customer.id,group=customer_account.account_customer, active=True).first() if membership and is_group_deposit: group_trans_field = { "membership": membership, "savings": save_trans } GroupSavingTransaction.objects.create(**group_trans_field) deposit_charge = SavingProductCharge.objects.filter(saving_product=customer_account.account_product, charge_key='deposit_charge').first() if charge > 0 and deposit_charge: sav_charge_trans = None reference_no = generate_reference_no(deposit_charge.accounts_chart.account_line, organisation_id) charge_fields = { "heading": 'Deposit Charge: ' + ' on A/C No: ' + customer_account.account_no, "coment": 'Deposit Charge: ('+f"{charge:,}"+') ' + ' on A/C No: ' + customer_account.account_no, "amount": charge, "credit_chart": deposit_charge.accounts_chart, "debit_chart": customer_account.account_product.accounts_chart, "reference_no": reference_no, "voucher_no": deposit_details["voucher_no"], "record_date":deposit_details["record_date"], "payment_method": deposit_details["payment_method"], "added_by":deposit_details["user"], "branch": deposit_details["branch"], } # Handle inter-branch deposit charge transactions update -> source branch if branch_id != customer_account.customer_branch.id: charge_fields["heading"] = 'Inter-branch deposit charge: (' + f"{charge:,}"+') ' + ' on A/C No: ' + customer_account.account_no charge_fields["branch"] = OrganisationBranch.objects.get(pk=branch_id) charge_fields["debit_chart"] = interbranch_chart charge_transaction = SystemTransactions.objects.create(**charge_fields) if charge_transaction: # Handle inter-branch deposit charge transactions update -> destination branch if branch_id != customer_account.customer_branch.id: charge_fields["debit_chart"] = customer_account.account_product.accounts_chart charge_fields["branch"] = customer_account.customer_branch charge_fields["credit_chart"] = interbranch_chart charge_fields["payment_method"] = 'settlement' inter_branch_trans = SystemTransactions.objects.create(**charge_fields) # Reconcile inter-branch transactions if inter_branch_trans: inter_branch_trans_field = { "source_transaction": saved_transaction, "destination_transaction": inter_branch_trans, "added_by":deposit_details["user"], } InterBranchTransactions.objects.create(**inter_branch_trans_field) # Register saving deposit charge transactions mapping for inter-branch saving_charge_fields = { "customer_account": customer_account, "transaction": inter_branch_trans, "transaction_type": "deposit_charge", "parent_id": save_trans.id } sav_charge_trans = SavingAccountTransactions.objects.create(**saving_charge_fields) else: # Register saving deposit charge transactions mapping for single branch saving_charge_fields = { "customer_account": customer_account, "transaction": charge_transaction, "transaction_type": "deposit_charge", "parent_id": save_trans.id } sav_charge_trans = SavingAccountTransactions.objects.create(**saving_charge_fields) if sav_charge_trans: membership = GroupMembership.objects.filter(member__id=customer.id,group=customer_account.account_customer, active=True).first() if membership and is_group_deposit: group_trans_field = { "membership":membership, "savings":sav_charge_trans } GroupSavingTransaction.objects.create(**group_trans_field) if send_sms: data = {"sms_key": "cash_deposit_sms", "customer_account": customer_account, "user": deposit_details["user"], "branch_id": branch_id, "save_trans": save_trans,"customer":customer} send_customer_sms(data) mark_savings_account_for_aml_deposit( customer_account, amount, transaction=save_trans.transaction, ) sync_savings_account_lifecycle(customer_account) #if branch_id == customer_account.customer_branch.id: # Process account booking payments thread_multiple_booking_payments(customer_account, organisation_id,customer_account.customer_branch.id,deposit_details["user"].id) return saved_transaction def process_group_withdraws(user,request): charge = 0 organisation_id = get_current_user(request, 'organisation_id', None) branch_id = get_current_user(request, 'organisation_branch_id', None) inter_branch_trans = None send_sms = request.data.get('send_sms') withdraws = request.data.get('withdraws') credit_chart_id = request.data.get('credit_chart') record_date = request.data.get('record_date') withdrawal_charge_amount = request.data.get('charge',0) notify_admin = request.data.get('notify_admin') response_status = False success_count = 0 total_withdraww = 0 customer_account = SavingAccount.objects.get(pk=request.data.get('customer_account')) assert_savings_account_can_debit(customer_account, "withdrawals", as_at=record_date) if customer_account: if withdraws: if len(withdraws) > 0: for deposit in withdraws: amount = float(deposit['amount']) member_id = deposit['member_id'] customer = Customer.objects.get(pk=member_id) if customer: # InterBranch chart interbranch_chart = get_inter_branch_chart(OrganisationBranch.objects.get(pk=branch_id), customer_account.customer_branch) reference_no = generate_reference_no(customer_account.account_product.accounts_chart.account_line, organisation_id, 'wd') withdrawal_charge = SavingProductCharge.objects.filter(saving_product=customer_account.account_product, charge_key='withdrawal_charge').first() if withdrawal_charge and float(withdrawal_charge_amount) > 0: if customer_account.account_product.default_charge_type == 'flat': charge = round(float(withdrawal_charge_amount)/100)*100 if customer_account.account_product.default_charge_type != 'flat': charge_amount = (float(withdrawal_charge_amount)/100)*amount charge = round(charge_amount/100) * 100 account_balance = get_account_balance(customer_account.id) if account_balance['balance_raw'] >= (amount + charge): transaction_fields = { "heading": 'Withdrawal by ' + customer.name + ' From: ' + customer_account.account_customer.name + '-' + customer_account.account_customer.old_member_number, "coment": customer_account.account_customer.name + 'has withdrawn ('+str(amount)+') ' + 'from' + ' A/C No: ' + customer_account.account_no, "amount": amount, "debit_chart": customer_account.account_product.accounts_chart, "credit_chart": OrganisationSubAccount.objects.get(pk=credit_chart_id), "reference_no": reference_no, "voucher_no": request.data.get('voucher_no'), "payment_method": request.data.get('payment_method'), "added_by": get_user_model().objects.get(pk=user.id), "branch": OrganisationBranch.objects.get(pk=branch_id), "record_date": record_date } # Handle inter-branch transactions update -> soure branch if branch_id != customer_account.customer_branch.id: transaction_fields["heading"] = 'Inter-branch withdrawal by ' + customer.name + ' From: ' + customer_account.account_customer.name + '-' + customer_account.account_customer.old_member_number transaction_fields["branch"] = OrganisationBranch.objects.get(pk=branch_id) transaction_fields["debit_chart"] = interbranch_chart # Register withdrawal transactions saved_transaction = SystemTransactions.objects.create(**transaction_fields) if saved_transaction: save_trans = None total_withdraww += amount # Handle inter-branch transactions update -> destination branch if branch_id != customer_account.customer_branch.id: transaction_fields["debit_chart"] = customer_account.account_product.accounts_chart transaction_fields["branch"] = customer_account.customer_branch transaction_fields["credit_chart"] = interbranch_chart transaction_fields["payment_method"] = 'settlement' inter_branch_trans = SystemTransactions.objects.create(**transaction_fields) # Reconcile inter-branch transactions if inter_branch_trans: inter_branch_trans_field = { "source_transaction": saved_transaction, "destination_transaction": inter_branch_trans, "added_by": get_user_model().objects.get(pk=user.id), } InterBranchTransactions.objects.create(**inter_branch_trans_field) # Register saving withdrawal transaction mapping for inter-branch saving_trans_trans_field = { "transaction_type":'withdrawal', "customer_account": customer_account, "transaction":inter_branch_trans } save_trans = SavingAccountTransactions.objects.create(**saving_trans_trans_field) else: # Register saving withdrawal transaction mapping for single branch saving_trans_trans_field = { "transaction_type":'withdrawal', "customer_account": customer_account, "transaction":saved_transaction } save_trans = SavingAccountTransactions.objects.create(**saving_trans_trans_field) if save_trans: # Register group withdraw mapping. i.e a user withdraw money from group account response_status = True success_count += 1 membership = GroupMembership.objects.filter(member__id=member_id,group=customer_account.account_customer, active=True).first() if membership: group_trans_field = { "membership": membership, "savings": save_trans } GroupSavingTransaction.objects.create(**group_trans_field) if charge > 0 and withdrawal_charge: sav_charge_trans = None reference_no = generate_reference_no(withdrawal_charge.accounts_chart.account_line, organisation_id) charge_fields = { "heading": 'Saving withdrawal charge on A/C No: ' + customer_account.account_no, "coment": 'Saving withdrawal charge on A/C No: ' + customer_account.account_no, "amount": charge, "credit_chart": withdrawal_charge.accounts_chart, "debit_chart": customer_account.account_product.accounts_chart, "reference_no": reference_no, "payment_method": request.data.get('payment_method'), "added_by": get_user_model().objects.get(pk=user.id), "branch": OrganisationBranch.objects.get(pk=branch_id), "record_date": record_date } # Handle inter-branch withdrawal charges transactions update -> source branch if branch_id != customer_account.customer_branch.id: charge_fields["heading"] = 'Inter-branch saving withdrawal charge on A/C No: ' + customer_account.account_no charge_fields["branch"] = OrganisationBranch.objects.get(pk=branch_id) charge_fields["debit_chart"] = interbranch_chart charge_transaction = SystemTransactions.objects.create(**charge_fields) if charge_transaction: # Handle inter-branch withdrawal charges transactions update -> destination branch if branch_id != customer_account.customer_branch.id: charge_fields["debit_chart"] = customer_account.account_product.accounts_chart charge_fields["branch"] = customer_account.customer_branch charge_fields["credit_chart"] = interbranch_chart charge_fields["payment_method"] = 'settlement' inter_branch_trans = SystemTransactions.objects.create(**charge_fields) # Reconcile inter-branch transactions if inter_branch_trans: inter_branch_trans_field = { "source_transaction": saved_transaction, "destination_transaction": inter_branch_trans, "added_by": get_user_model().objects.get(pk=user.id), } InterBranchTransactions.objects.create(**inter_branch_trans_field) # Register saving withdrawal charge transactions for inter-branch saving_charge_fields = { "customer_account": customer_account, "transaction": inter_branch_trans, "parent_id": save_trans.id, "transaction_type": 'withdrawal_charge' } sav_charge_trans = SavingAccountTransactions.objects.create(**saving_charge_fields) else: # Register saving withdrawal charge transactions for single branch saving_charge_fields = { "customer_account": customer_account, "transaction": charge_transaction, "parent_id": save_trans.id, "transaction_type": 'withdrawal_charge' } sav_charge_trans = SavingAccountTransactions.objects.create(**saving_charge_fields) if sav_charge_trans: membership = GroupMembership.objects.filter(member__id=member_id,group=customer_account.account_customer, active=True).first() if membership: group_trans_field = { "membership":membership, "savings":sav_charge_trans } GroupSavingTransaction.objects.create(**group_trans_field) if send_sms: data = {"sms_key": "cash_withdraw_sms", "customer_account": customer_account, "user": user, "branch_id": branch_id, "save_trans": save_trans,"customer":customer} send_customer_sms(data) sync_savings_account_lifecycle(customer_account) if notify_admin and total_withdraww > 0: sms_msg = 'Dear '+customer_account.account_customer.name.capitalize()+', Cash Withdraw UGX: '+f"{total_withdraww:,}"+' from A/C: '+customer_account.account_no data = {"sms_key": "withdraw_notify_admin", "customer_account": customer_account, "user":user, "branch_id": branch_id, "save_trans": None,"sms_msg":sms_msg,"customer":customer_account.account_customer} send_customer_sms(data) # Send SMS to two group signatories signatories = GroupMembership.objects.filter( group=customer_account.account_customer, active=True, role__in=['Chair Person', 'Secretary', 'Treasurer'] )[:2] for signatory in signatories: signatory_sms_msg = f'Dear {signatory.member.name.capitalize()}, Group Cash Withdraw UGX: {total_withdraww:,} from A/C: {customer_account.account_no}' signatory_data = {"sms_key": "withdraw_notify_admin", "customer_account": customer_account, "user": user, "branch_id": branch_id, "save_trans": None, "sms_msg": signatory_sms_msg, "customer": signatory.member} send_customer_sms(signatory_data) return {"response_status":response_status,"success_count":success_count} 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 process_bulk_saving_charges(initiation, user_id): try: processed_count = 0 user = get_user_model().objects.get(pk=user_id) # Fetch accounts for the savings product and branch accounts = SavingAccount.objects.filter( account_product=initiation.saving_product, customer_branch=initiation.branch ) if not accounts: return for customer_account in accounts: heading = f"{initiation.heading} on A/C No: {customer_account.account_no}" # Skip if already processed if BulkSavingsChargesTransactions.objects.filter( initiation=initiation, saving_transaction__transaction__heading=heading ).exists(): processed_count += 1 continue account_balance = get_account_balance(customer_account.id) # Determine which date to use (record_date, start_date, end_date) date_to_use = initiation.record_date or initiation.start_date or initiation.end_date # Convert string to datetime if necessary if date_to_use: if isinstance(date_to_use, str): # fromisoformat can parse "+03:00" timezone correctly in Python 3.11+ try: date_to_use = timezone.datetime.fromisoformat(date_to_use) except ValueError: # Fallback for older versions or missing tz info date_to_use = timezone.datetime.strptime(date_to_use, "%Y-%m-%dT%H:%M:%S") # Make timezone-aware if naive if timezone.is_naive(date_to_use): date_to_use = timezone.make_aware(date_to_use, timezone.get_current_timezone()) # Enough balance: create transaction if account_balance['balance_raw'] >= initiation.amount: reference_no = generate_reference_no( initiation.accounts_chart.account_line, initiation.branch.branch_organisation.id ) transaction = SystemTransactions.objects.create( heading=heading, coment=heading, amount=initiation.amount, credit_chart=initiation.accounts_chart, debit_chart=customer_account.account_product.accounts_chart, reference_no=reference_no, payment_method="offset", record_date=date_to_use, added_by=user, branch=initiation.branch, ) saving_transaction = SavingAccountTransactions.objects.create( customer_account=customer_account, transaction=transaction, transaction_type='offset' ) # Send SMS if enabled if initiation.send_sms: account_balance = get_account_balance(customer_account.id) sms_msg = ( f"Dear {customer_account.account_customer.name.capitalize()}, " f"Your A/C: {customer_account.account_no}, has been charged UGX: " f"{initiation.amount:,} for {initiation.heading}. Balance UGX: " f"{account_balance['balance_raw']:,}. Thanks for saving with " f"{initiation.branch.branch_organisation.name}" ) send_customer_sms({ "sms_key": "bulk_savings_charge_sms", "customer_account": customer_account, "user": user, "branch_id": initiation.branch.id, "save_trans": saving_transaction, "sms_msg": sms_msg, "customer": customer_account.account_customer }) BulkSavingsChargesTransactions.objects.create( saving_transaction=saving_transaction, initiation=initiation, status="processed" ) processed_count += 1 # Insufficient balance: book account if allowed elif initiation.book_account: booking_fields = { "heading": heading, "amount": initiation.amount, "booking_type": 'income', "chart": initiation.accounts_chart, "account": customer_account, "record_date": date_to_use, "added_by": user.id, } if not AccountBookings.objects.filter( record_date=date_to_use, heading=heading, account=customer_account ).exists(): AccountBookings.objects.create(**booking_fields) processed_count += 1 else: processed_count += 1 # Mark initiation as processed initiation.status = "processed" initiation.save() except Exception as e: print(e) raise Http404 def reverse_bulk_saving_charges(initiation,record_date,reason,user_id): user = get_user_model().objects.get(pk=user_id) accounts = SavingAccount.objects.filter(account_product=initiation.saving_product,customer_branch=initiation.branch) if accounts: for customer_account in accounts: heading = initiation.heading + ' on A/C No: ' + customer_account.account_no try: charge_payments = BulkSavingsChargesTransactions.objects.filter(initiation=initiation,saving_transaction__transaction__heading=heading, saving_transaction__transaction__transaction_type='normal') if charge_payments: for charge_payment in charge_payments: original_transaction = charge_payment.saving_transaction.transaction transaction_details = { "heading":original_transaction.heading + '-reversal', "amount": original_transaction.amount, "record_date": record_date, "debit_chart_id": original_transaction.credit_chart.id, "credit_chart_id": original_transaction.debit_chart.id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix":'inc-rev', "organisation_id": initiation.branch.branch_organisation.id, "branch_id":original_transaction.branch.id, "user_id":user.id } # Save general transaction message = f'Reversed Bulk Saving Charge: {original_transaction.heading} For {customer_account.account_customer.name.capitalize()} Mem No: {customer_account.account_customer.member_number}' old_details = get_serialized_transaction(original_transaction) transaction = post_transaction(transaction_details) transaction.transaction_type = 'reversal' transaction.save() if transaction: # Update original transaction type original_transaction.transaction_type = 'reversed' original_transaction.save() sav_trans_fields = { "customer_account":customer_account, "transaction":transaction, "transaction_type":'offset' } saving_transaction = SavingAccountTransactions.objects.create(**sav_trans_fields) if saving_transaction: bulk_savings = { "saving_transaction":saving_transaction, "initiation":initiation, "status":"processed" } BulkSavingsChargesTransactions.objects.create(**bulk_savings) add_system_audit_trail('transaction_management','reverse_saving_transaction_charge',message,reason,old_details,{},user_id,original_transaction.branch) heading = initiation.heading + ' on A/C No: ' + customer_account.account_no accounts_booked = AccountBookings.objects.filter(record_date=initiation.record_date,heading=heading,account=customer_account) if accounts_booked: for account_booked in accounts_booked: account_booked.reversed = True account_booked.save() except Exception as e: print(e) raise Http404 def delete_bulk_saving_charges(initiation, reason, user_id): """Delete bulk saving charges and related transactions""" from django.utils import timezone now = timezone.now() df = dict(deleted=True, deleted_by_id=user_id, deleted_at=now) try: add_system_audit_trail( 'transaction_management', 'delete_bulk_saving_charges', f'Deleted Bulk Saving Charges: {initiation.heading}', reason, {}, {}, user_id, initiation.branch ) charge_payments = BulkSavingsChargesTransactions.objects.filter(initiation=initiation) if charge_payments: for charge_payment in charge_payments: if charge_payment.saving_transaction and charge_payment.saving_transaction.transaction: transaction = charge_payment.saving_transaction.transaction customer_account = charge_payment.saving_transaction.customer_account message = f'Deleted Bulk Saving Charge: {transaction.heading} For {customer_account.account_customer.name.capitalize()} Mem No: {customer_account.account_customer.member_number}' old_details = get_serialized_transaction(transaction) BulkSavingsChargesTransactions.objects.filter(id=charge_payment.id).update(**df) SavingAccountTransactions.objects.filter(transaction=transaction).update(**df) SystemTransactions.objects.filter(id=transaction.id).update(**df) add_system_audit_trail( 'transaction_management', 'delete_saving_transaction_charge', message, reason, old_details, {}, user_id, transaction.branch ) accounts = SavingAccount.objects.filter( account_product=initiation.saving_product, customer_branch=initiation.branch ) for customer_account in accounts: heading = initiation.heading + ' on A/C No: ' + customer_account.account_no AccountBookings.objects.filter( record_date=initiation.record_date, heading=heading, account=customer_account ).update(**df) except Exception as e: print(f"Error deleting bulk saving charges: {e}") raise e def queue_saving_products_interest_payments(): update_savings_account_statuses() savings_products = SavingsProduct.objects.all() if savings_products: for savings_product in savings_products: pass # queue_saving_interest_payments(savings_product) def queue_saving_interest_payments(savings_product, from_date, to_date, record_date, branch_organisation_id): savings_accounts = SavingAccount.objects.filter(account_product=savings_product, status='active') if savings_accounts: all_interest_settings = SavingProductInterest.objects.filter(saving_product__id=savings_product.id, is_active=True) if all_interest_settings: interest_settings = all_interest_settings[0] if interest_settings.int_rate > 0: # Check if there not is a pending queued payment # interest_payments = SavingsProductInterestPayment.objects.filter(product__id=savings_product.id, branch__id = branch_organisation_id, status='pending') # if not interest_payments: branch = OrganisationBranch.objects.filter(id=branch_organisation_id).first() saving_products_pay = SavingsProductInterestPayment.objects.create(**{ "int_rate": interest_settings.int_rate, "tax_on_int":interest_settings.tax_on_int, "frequency":interest_settings.frequency, "frequency_type":interest_settings.frequency_type, "send_sms":interest_settings.send_sms, "min_balance":interest_settings.min_balance, "record_date":record_date, 'from_date':from_date, 'to_date':to_date, "exp_payment_date":record_date, "product":savings_product, "branch": branch, "processing_status": "Processing....." }) print("********************** saving interest payment 1 ****************************************") csv_file_name = f'{settings.STATIC_ROOT}/reports/{branch.branch_organisation.id}/savings-interest/{branch.id}/{saving_products_pay.id}_accrued_interest.csv' if not os.path.exists(csv_file_name): print("********************** saving interest payment 2 ****************************************") thread = threading.Thread(target=generate_savings_interest_file, args=(saving_products_pay,)) print("********************** saving interest payment 3 ****************************************") # starting thread thread.start() def process_saving_products_interest_payments(interest_payment_id,user): interest_payments = SavingsProductInterestPayment.objects.filter(id=interest_payment_id) if interest_payments: for interest_payment in interest_payments: interest_payment.payment_status = "Paying....." interest_payment.save() try: int_expense_chart_code = 'sys-5111' branch_id = interest_payment.branch.id organisation_id = interest_payment.branch.branch_organisation.id # Fix 4: hoist all shared lookups outside the per-account loop int_expense_chart = OrganisationSubAccount.objects.filter( account_code=int_expense_chart_code, account_organisation=organisation_id ).first() interest_setting = SavingProductInterest.objects.filter( saving_product=interest_payment.product, is_active=True ).first() withholding_chart = interest_setting.tax_chart if interest_setting else None withholding_tax_setting = OrganisationSetting.objects.filter( org_setting__id=organisation_id, setting_key='withholding_tax' ).first() withholding_tax = float(withholding_tax_setting.setting_value) if withholding_tax_setting else 15 # Fix 5: load CSV once and filter in memory per account branch = interest_payment.branch organisation = branch.branch_organisation csv_file_name = f'{settings.STATIC_ROOT}/reports/{organisation.id}/savings-interest/{branch.id}/{interest_payment.id}_accrued_interest.csv' csv_df = None if os.path.exists(csv_file_name): csv_df = pd.read_csv(csv_file_name) csv_df['as_at'] = pd.to_datetime(csv_df['as_at']) as_at_filter = pd.to_datetime(interest_payment.exp_payment_date.strftime("%Y-%m-%d")) csv_df = csv_df[csv_df['as_at'] <= as_at_filter] from_date_str = interest_payment.from_date.strftime("%Y-%m-%d") if hasattr(interest_payment.from_date, 'strftime') else str(interest_payment.from_date)[:10] to_date_str = interest_payment.to_date.strftime("%Y-%m-%d") if hasattr(interest_payment.to_date, 'strftime') else str(interest_payment.to_date)[:10] # Get accounts from CSV rather than filtering by branch # This ensures uploaded interest payments process all accounts in the file if csv_df is not None and not csv_df.empty: csv_account_ids = csv_df['id'].unique().tolist() savings_accounts = SavingAccount.objects.filter( id__in=csv_account_ids, status='active' ) else: savings_accounts = SavingAccount.objects.filter( account_product=interest_payment.product, customer_branch=interest_payment.branch, status='active' ) if savings_accounts: for savings_account in savings_accounts: saving_product_chart = savings_account.account_product.accounts_chart filter_array = {"interest_payment__id":interest_payment_id,"savings__customer_account":savings_account,"savings__transaction__transaction_type":"normal","savings__transaction_type":"deposit"} paid_amount = InterestPaymentTransaction.objects.filter(**filter_array).aggregate(total=Sum('savings__transaction__amount'))['total'] if not paid_amount: # Fix 5: sum from pre-loaded DataFrame instead of re-reading CSV if csv_df is not None: interest_amount = round(csv_df[csv_df['id'] == int(savings_account.id)]['daily_int'].sum()) else: interest_amount = 0 if interest_amount > 0: transaction_details = { "heading": "Savings Interest Payment: " + savings_account.account_customer.member_number + " for " + from_date_str + " - " + to_date_str, "amount":round(interest_amount), "record_date":interest_payment.record_date, "debit_chart_id": int_expense_chart.id, "credit_chart_id": saving_product_chart.id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": 'int-py', "organisation_id": organisation_id, "branch_id": branch_id, "user_id": user.id } payment = post_transaction(transaction_details) account_transaction = SavingAccountTransactions(customer_account=savings_account, transaction=payment, transaction_type='deposit') account_transaction.save() if account_transaction: InterestPaymentTransaction.objects.create( interest_payment=interest_payment, savings=account_transaction ) if interest_payment.tax_on_int and withholding_chart: withholding_tax_amount = float(round(interest_amount * (withholding_tax * 0.01))) if withholding_tax_amount > 0: transaction_details = { "heading": "Savings Product Interest Tax: " + savings_account.account_customer.name + " - " + savings_account.account_no, "amount":withholding_tax_amount, "record_date":interest_payment.record_date, "debit_chart_id": saving_product_chart.id, "credit_chart_id": withholding_chart.id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": 'int-py-t', "organisation_id": organisation_id, "branch_id": branch_id, "user_id": user.id } tax_transaction = post_transaction(transaction_details) parent_id = account_transaction.id account_tax_transaction = SavingAccountTransactions(customer_account=savings_account, transaction=tax_transaction, transaction_type='deposit_charge', parent_id=parent_id) account_tax_transaction.save() if interest_payment.send_sms and payment: data = {"sms_key": "savings_interest_sms", "customer_account": savings_account, "user":user, "branch_id": branch_id, "save_trans": account_transaction,"customer":savings_account.account_customer} send_customer_sms(data) except Exception as e: import traceback print(f"**** process_saving_products_interest_payments ERROR: {e} ****") print(traceback.format_exc()) interest_payment.payment_status = "Completed" interest_payment.status = "processed" interest_payment.save() def generate_savings_interest_file(interest_payment): print("********************** generate savings interest file START ****************************************") try: daily_int = interest_payment.int_rate / 365 if interest_payment.int_rate > 0 else 0 branch = interest_payment.branch organisation = branch.branch_organisation print(f"**** from_date={interest_payment.from_date!r} to_date={interest_payment.to_date!r} daily_int={daily_int} ****") if not interest_payment.from_date or not interest_payment.to_date: print("**** from_date or to_date is None, aborting ****") interest_payment.processing_status = "Ready" interest_payment.save(update_fields=["processing_status"]) return from_date = interest_payment.from_date to_date = interest_payment.to_date start = from_date.strftime("%Y-%m-%d") if hasattr(from_date, 'strftime') else str(from_date)[:10] end = to_date.strftime("%Y-%m-%d") if hasattr(to_date, 'strftime') else str(to_date)[:10] print(f"**** date range: {start} to {end} ****") organisation_directory = f"{settings.STATIC_ROOT}/reports/{organisation.id}/savings-interest/{branch.id}" csv_file_name = f"{organisation_directory}/{interest_payment.id}_accrued_interest.csv" os.makedirs(organisation_directory, exist_ok=True) if os.path.exists(csv_file_name): os.remove(csv_file_name) interest_payment.processing_status = "Processing....." interest_payment.save(update_fields=["processing_status"]) if daily_int > 0: # Fix 3: single bulk query across all dates using generate_series # replaces N per-day calls to savings_balances_func with one query query = f""" SELECT acc.id, acc.account_no, acc.customer_id, acc.customer_name, acc.customer_member_number, acc.customer_old_member_number, day::date AS as_at, COALESCE(( SELECT SUM(satv.amount) FROM savings_account_transaction_view satv WHERE satv.customer_account_id = acc.id AND satv.credit_chart_id = acc.accounts_chart_id AND DATE(satv.record_date) <= day ), 0) - COALESCE(( SELECT SUM(satv.amount) FROM savings_account_transaction_view satv WHERE satv.customer_account_id = acc.id AND satv.debit_chart_id = acc.accounts_chart_id AND DATE(satv.record_date) <= day ), 0) AS balance_actual FROM generate_series('{start}'::date, '{end}'::date, '1 day') AS day CROSS JOIN savings_account_search_view acc WHERE acc.organisation_id = {organisation.id} AND acc.product_id = {interest_payment.product.id} AND acc.branch_id = {branch.id} AND acc.status = 'active' """ print(f"**** executing bulk interest query for {start} to {end} ****") with connection.cursor() as cursor: cursor.execute(query) rows = cursor.fetchall() columns = [desc[0] for desc in cursor.description] if rows: df = pd.DataFrame(rows, columns=columns) df = df[df['balance_actual'] >= interest_payment.min_balance] df['daily_int'] = round(df['balance_actual'] * daily_int * 0.01, 0) df['min_balance'] = interest_payment.min_balance df['as_at'] = df['as_at'].astype(str) # Fix 2: write CSV directly — no in-memory concat of per-day frames df.to_csv(csv_file_name, index=False) print(f"**** CSV saved with {len(df)} rows ****") else: print("**** No accrued interest data found ****") interest_payment.processing_status = "Ready" interest_payment.save(update_fields=["processing_status"]) save_user_notification({ "heading": "Savings Product Queue Interest Payment", "message": f"Savings Product Queue Interest Payment Completed. Product: {interest_payment.product} as at {interest_payment.record_date}", "branch": OrganisationBranch.objects.get(pk=interest_payment.branch.id), "branch_name": interest_payment.branch.name, "added_by": None, "last_updated_by": None, "key": "savings_notifications" }) except Exception as e: import traceback print(f"**** generate_savings_interest_file ERROR: {e} ****") print(traceback.format_exc()) interest_payment.processing_status = "Ready" interest_payment.save(update_fields=["processing_status"]) print("********************** generate savings interest file END ****************************************") def get_total_interest_accrued(interest_payment): total_interest = 0 branch = interest_payment.branch organisation = interest_payment.branch.branch_organisation csv_file_name = f'{settings.STATIC_ROOT}/reports/{organisation.id}/savings-interest/{branch.id}/{interest_payment.id}_accrued_interest.csv' if os.path.exists(csv_file_name): df = pd.read_csv(csv_file_name) as_at = pd.to_datetime(interest_payment.exp_payment_date.strftime("%Y-%m-%d")) df['as_at'] = pd.to_datetime(df['as_at']) df = df[df['as_at'] <= as_at] total_interest = round(df['daily_int'].sum()) return total_interest def get_account_total_interest_accrued(interest_payment,account): total_interest = 0 branch = interest_payment.branch organisation = interest_payment.branch.branch_organisation csv_file_name = f'{settings.STATIC_ROOT}/reports/{organisation.id}/savings-interest/{branch.id}/{interest_payment.id}_accrued_interest.csv' if os.path.exists(csv_file_name): df = pd.read_csv(csv_file_name) as_at = pd.to_datetime(interest_payment.exp_payment_date.strftime("%Y-%m-%d")) df['as_at'] = pd.to_datetime(df['as_at']) df = df[df['id'] == int(account.id)] df = df[df['as_at'] <= as_at] total_interest = round(df['daily_int'].sum()) return total_interest def get_account_interest_accrued(interest_payment,query_filters): branch = interest_payment.branch organisation = interest_payment.branch.branch_organisation page = query_filters['page'] page_size = query_filters['page_size'] search = query_filters['search'] count = 0 start_index = (int(page) - 1) * int(page_size) end_index = int(page) * int(page_size) csv_file_name = f'{settings.STATIC_ROOT}/reports/{organisation.id}/savings-interest/{branch.id}/{interest_payment.id}_accrued_interest.csv' if os.path.exists(csv_file_name): df = pd.read_csv(csv_file_name) as_at = pd.to_datetime(interest_payment.exp_payment_date.strftime("%Y-%m-%d")) df['as_at'] = pd.to_datetime(df['as_at']) df = df[df['as_at'] <= as_at] df = df.sort_values(by='as_at', ascending=False) ''' if search: filter_conditions = (df['customer_name'].str.contains(search, case=False) | df['customer_member_number'].str.contains(search, case=False)) df = df[filter_conditions]''' # count = len(df) # df = df.iloc[start_index:end_index] # return {"results":df.to_dict(orient='records'),"count":count} customer_balance = df.groupby(['customer_id', 'customer_name', 'customer_member_number', 'account_no'])['daily_int'].sum().reset_index() # Filter out records where daily_int sum is not equal to 0 customer_balance = customer_balance[customer_balance['daily_int'] != 0] results_count = len(customer_balance) return {"results":customer_balance.to_dict(orient='records'),"count":results_count} return {"results":[],"count":0} def make_account_dormant(): return update_savings_account_statuses() def over_draft_auto_payments(): overdrafts = OverDrafts.objects.filter(status='in-progress',auto_payments=True) if overdrafts: for overdraft in overdrafts: as_at = datetime.now().date() save_trans = make_over_draft_payment(overdraft, as_at) if save_trans: return Response({'message': 'Error while saviing Over Draft Payment'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR) return Response({"message": "Over Draft Payments updated successfully"}, status=status.HTTP_200_OK) def make_over_draft_payment(overdraft,as_at,user_id=None): # Get past days 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')) last_date = (overdraft.transaction_date.astimezone(eat_timezone) + timedelta(days=overdraft.over_draft_period) + timedelta(days=overdraft.withdraw_allowance_period) ) over_draft_date = make_aware(datetime.strptime(last_date.strftime('%Y-%m-%d 00:00:00'), '%Y-%m-%d 00:00:00')) # Check if past days are greater the over draft allowance period if over_draft_date < today: over_draft_pay_details = { "penalty_paid":0, "interest_paid":0, "principal_paid":0, "branch":overdraft.branch.id, "branch_name":overdraft.branch.name, "payment_date":0, "customer":overdraft.saving_account.account_customer.name, "member_number":overdraft.saving_account.account_customer.member_number } # Get due ammount (i.e principal, interest, penalty) over_draft_due_payment = compute_auto_over_due_payment(overdraft,as_at,eat_timezone) # Validate savings account balance account_balance = get_account_balance(overdraft.saving_account) available_balance = account_balance['balance'] pen_to_pay = 0 int_to_pay = 0 princ_to_pay = 0 # Penaly deductions available_balance = float(available_balance.replace(',', '')) if available_balance > 0: if available_balance >= over_draft_due_payment["penalty_balance"]: pen_to_pay = over_draft_due_payment["penalty_balance"] else: pen_to_pay = available_balance available_balance -= pen_to_pay if pen_to_pay > 0: pen_transaction_details = { "heading": "Over Draft Penalty Payment: " + overdraft.saving_account.account_customer.name + " - " + overdraft.saving_account.account_no, "amount": pen_to_pay, "record_date": timezone.now(), "debit_chart_id": overdraft.saving_account.account_product.accounts_chart.id, "credit_chart_id":overdraft.over_draft_product.penalty_income_chart.id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": 'ov-pen-py-t', "organisation_id": overdraft.branch.branch_organisation.id, "branch_id":overdraft.branch.id, "user_id": user_id } # Save deduction transaction pen_transaction = post_transaction(pen_transaction_details) # Link deduction to savings account account_transaction = SavingAccountTransactions( customer_account=overdraft.saving_account, transaction=pen_transaction, transaction_type='over-draft-penalty-payment', ) account_transaction.save() over_draft_details = { "principal_paid":0, "interest_paid": 0, "penalty_paid":pen_to_pay, "over_draft": overdraft, "transaction":pen_transaction, "transaction_type":"auto", "date_added":timezone.now(), } over_drafts_data = OverDraftPayment.objects.create(**over_draft_details) over_draft_pay_details[ "penalty_paid"] = over_drafts_data.penalty_paid over_draft_pay_details[ "payment_date"] = over_drafts_data.closing_date # Interest deductions if available_balance > 0: if available_balance >= over_draft_due_payment["interest_balance"]: int_to_pay = over_draft_due_payment["interest_balance"] else: int_to_pay = available_balance available_balance -= int_to_pay if int_to_pay > 0: int_transaction_details = { "heading": "Over Draft Interest Payment: " + overdraft.saving_account.account_customer.name + " - " + overdraft.saving_account.account_no, "amount": int_to_pay, "record_date": timezone.now(), "debit_chart_id": overdraft.saving_account.account_product.accounts_chart.id, "credit_chart_id":overdraft.over_draft_product.interest_income_chart.id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": 'ov-drf-int-py-t', "organisation_id": overdraft.branch.branch_organisation.id, "branch_id":overdraft.branch.id, "user_id": user_id } # Save deduction transaction interest_transaction = post_transaction(int_transaction_details) # Link deduction to savings account account_transaction = SavingAccountTransactions(customer_account=overdraft.saving_account, transaction=interest_transaction, transaction_type='over-draft-interest-payment') account_transaction.save() over_draft_details = { "principal_paid":0, "interest_paid": int_to_pay, "penalty_paid":0, "over_draft": overdraft, "transaction":interest_transaction, "transaction_type":"auto", "date_added":timezone.now(), } over_drafts_data = OverDraftPayment.objects.create(**over_draft_details) over_draft_pay_details[ "interest_paid"] = over_drafts_data.interest_paid over_draft_pay_details[ "payment_date"] = over_drafts_data.closing_date # Principal deductions if available_balance > 0: if available_balance >= float(over_draft_due_payment["principal_balance"]): princ_to_pay = float(over_draft_due_payment["principal_balance"]) else: princ_to_pay = available_balance if princ_to_pay > 0: credit_chart_code = "sys-116" credit_chart = get_chart_of_account_by_code(credit_chart_code, overdraft.branch.branch_organisation) princ_transaction_details = { "heading": "Over Draft Principal Payment: " + overdraft.saving_account.account_customer.name + " - " + overdraft.saving_account.account_no, "amount": princ_to_pay, "record_date": timezone.now(), "debit_chart_id": overdraft.saving_account.account_product.accounts_chart.id, "credit_chart_id":credit_chart.id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": 'ov-princ-py-t', "organisation_id": overdraft.branch.branch_organisation.id, "branch_id":overdraft.branch.id, "user_id": user_id } # Save deduction transaction princ_transaction = post_transaction(princ_transaction_details) if princ_transaction: # Link deduction to savings account account_transaction = SavingAccountTransactions(customer_account=overdraft.saving_account, transaction=princ_transaction, transaction_type=' over-draft-principal-payment') account_transaction.save() over_draft_details = { "principal_paid":princ_to_pay, "interest_paid": 0, "penalty_paid":0, "over_draft": overdraft, "transaction":princ_transaction, "transaction_type":"auto", "date_added":timezone.now(), } over_drafts_data =OverDraftPayment.objects.create(**over_draft_details) over_draft_pay_details[ "principal_paid"] = over_drafts_data.principal_paid over_draft_pay_details[ "payment_date"] = over_drafts_data.closing_date # Close over over_draft_due_payment = compute_auto_over_due_payment(overdraft,as_at,eat_timezone) total_due = float(over_draft_due_payment["principal_balance"]) + float(over_draft_due_payment["interest_balance"]) + float(over_draft_due_payment["penalty_balance"]) if total_due <= 0: overdraft.status = 'closed' overdraft.save() if over_draft_pay_details['penalty_paid'] > 0 or over_draft_pay_details['interest_paid'] > 0 or over_draft_pay_details['principal_paid'] > 0: save_user_notification({ "heading": "Over Drafts Auto Payments", "message": f"Over Drafts Auto Payments. Penalty Paid: {over_draft_pay_details['penalty_paid']} Interest Paid: {over_draft_pay_details['interest_paid']} Principal Paid: {over_draft_pay_details['principal_paid']} from {over_draft_pay_details['interest_paid']} customer: {over_draft_pay_details['customer']} member number: {over_draft_pay_details['member_number']} as at {over_draft_pay_details['payment_date']}", "branch":OrganisationBranch.objects.get(pk=over_draft_pay_details['branch']), "branch_name":over_draft_pay_details['branch_name'], "added_by":user_id, "last_updated_by":user_id, "key":"savings_notifications" }) def make_manual_over_draft_payment(payment_details): as_at = payment_details["as_at"] user_id = payment_details["user_id"] overdraft = payment_details["overdraft"] payment_date = payment_details["payment_date"] ## user instance user_instance = User.objects.get(id=user_id) # Get past days eat_timezone = pytz.timezone("Africa/Nairobi") transaction_date=datetime.strptime(overdraft.transaction_date.astimezone(eat_timezone).strftime('%Y-%m-%d'), '%Y-%m-%d') current_date=datetime.strptime(datetime.now().astimezone(eat_timezone).strftime('%Y-%m-%d'), '%Y-%m-%d') past_days = (current_date-transaction_date).days # Check if past days are greater the over draft allowance period if past_days > 0: # Get due ammount (i.e principal, interest, penalty) over_draft_due_payment = compute_over_due_payment(overdraft,as_at,eat_timezone,payment_date) # Validate savings account balance account_balance = get_account_balance(overdraft.saving_account) available_balance = account_balance['balance'] pen_to_pay = 0 int_to_pay = 0 princ_to_pay = 0 # Penaly deductions available_balance = float(available_balance.replace(',', '')) if available_balance > 0: if available_balance >= over_draft_due_payment["penalty_balance"]: pen_to_pay = over_draft_due_payment["penalty_balance"] else: pen_to_pay = available_balance if payment_details["status"] == "close": pen_to_pay = float(payment_details["closing_pen_amount"]) available_balance -= pen_to_pay if pen_to_pay > 0 and available_balance >= 0: int_transaction_details = { "heading": "Over Draft Penalty Payment: " + overdraft.saving_account.account_customer.name + " - " + overdraft.saving_account.account_no, "amount": pen_to_pay, "record_date": payment_date, "debit_chart_id": overdraft.saving_account.account_product.accounts_chart.id, "credit_chart_id":overdraft.over_draft_product.penalty_income_chart.id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": 'ov-pen-py-t', "organisation_id": overdraft.branch.branch_organisation.id, "branch_id":overdraft.branch.id, "user_id": user_id } # Save deduction transaction pen_transaction = post_transaction(int_transaction_details) # Link deduction to savings account account_transaction = SavingAccountTransactions( customer_account=overdraft.saving_account, transaction=pen_transaction, transaction_type='over-draft-penalty-payment', ) account_transaction.save() over_draft_details = { "principal_paid":0, "interest_paid": 0, "penalty_paid":pen_to_pay, "over_draft": overdraft, "transaction":pen_transaction, "date_added":timezone.now(), "closing_date":payment_date, "added_by":user_instance, "last_updated_by":user_instance } OverDraftPayment.objects.create(**over_draft_details) # Interest deductions if available_balance > 0: if available_balance >= over_draft_due_payment["interest_balance"]: int_to_pay = over_draft_due_payment["interest_balance"] else: int_to_pay = available_balance available_balance -= int_to_pay if payment_details["status"] == "close": int_to_pay = float(payment_details["closing_int_amount"]) if int_to_pay > 0 and available_balance >= 0: int_transaction_details = { "heading": "Over Draft Interest Payment: " + overdraft.saving_account.account_customer.name + " - " + overdraft.saving_account.account_no, "amount": int_to_pay, "record_date": payment_date, "debit_chart_id": overdraft.saving_account.account_product.accounts_chart.id, "credit_chart_id":overdraft.over_draft_product.interest_income_chart.id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": 'ov-drf-int-py-t', "organisation_id": overdraft.branch.branch_organisation.id, "branch_id":overdraft.branch.id, "user_id": user_id } # Save deduction transaction interest_transaction = post_transaction(int_transaction_details) # Link deduction to savings account account_transaction = SavingAccountTransactions(customer_account=overdraft.saving_account, transaction=interest_transaction, transaction_type='over-draft-interest-payment') account_transaction.save() over_draft_details = { "principal_paid":0, "interest_paid": int_to_pay, "penalty_paid":0, "over_draft": overdraft, "transaction":interest_transaction, "date_added":timezone.now(), "closing_date":payment_date, "added_by":user_instance, "last_updated_by":user_instance } OverDraftPayment.objects.create(**over_draft_details) # Principal deductions if available_balance > 0 : if available_balance >= float(over_draft_due_payment["principal_balance"]): princ_to_pay = float(over_draft_due_payment["principal_balance"]) else: princ_to_pay = available_balance if princ_to_pay > 0: credit_chart_code = "sys-116" credit_chart = get_chart_of_account_by_code(credit_chart_code, overdraft.branch.branch_organisation) princ_transaction_details = { "heading": "Over Draft Principal Payment: " + overdraft.saving_account.account_customer.name + " - " + overdraft.saving_account.account_no, "amount": princ_to_pay, "record_date": payment_date, "debit_chart_id": overdraft.saving_account.account_product.accounts_chart.id, "credit_chart_id":credit_chart.id, "payment_method": 'offset', "voucher_no": "", "ref_no_prefix": 'ov-princ-py-t', "organisation_id": overdraft.branch.branch_organisation.id, "branch_id":overdraft.branch.id, "user_id": user_id } # Save deduction transaction princ_transaction = post_transaction(princ_transaction_details) if princ_transaction: # Link deduction to savings account account_transaction = SavingAccountTransactions(customer_account=overdraft.saving_account, transaction=princ_transaction, transaction_type=' over-draft-principal-payment') account_transaction.save() over_draft_details = { "principal_paid":princ_to_pay, "interest_paid": 0, "penalty_paid":0, "over_draft": overdraft, "transaction":princ_transaction, "date_added":timezone.now(), "closing_date":payment_date, "added_by":user_instance, "last_updated_by":user_instance } OverDraftPayment.objects.create(**over_draft_details) # Close over draft over_draft_due_payment = compute_over_due_payment(overdraft,as_at, eat_timezone,payment_date) total_due = float(over_draft_due_payment["principal_balance"]) + float(over_draft_due_payment["interest_balance"]) + float(over_draft_due_payment["penalty_balance"]) if total_due <= 0: overdraft.status = 'closed' overdraft.save() def compute_auto_over_due_payment(overdraft,as_at, eat_timezone): principal_paid = 0 interest_paid = 0 penalty_paid = 0 principal_balance = 0 interest_balance = 0 penalty_balance = 0 interest_charge = 0 penalty_charge = 0 transaction_date=datetime.strptime(overdraft.transaction_date.astimezone(eat_timezone).strftime('%Y-%m-%d'), '%Y-%m-%d') current_date=datetime.strptime(datetime.now().astimezone(eat_timezone).strftime('%Y-%m-%d'), '%Y-%m-%d') past_days = ((current_date-transaction_date).days)-1 repayments = OverDraftPayment.objects.filter(over_draft=overdraft,transaction__transaction_type='normal').values('over_draft__id').annotate(princ_paid_sum=Sum("principal_paid"),int_paid_sum=Sum('interest_paid'),penalty_paid_sum=Sum('penalty_paid')).values("princ_paid_sum",'int_paid_sum','penalty_paid_sum') if repayments: repayments_values = repayments[0] principal_paid = float(repayments_values.get("princ_paid_sum", 0)) interest_paid = float(repayments_values.get("int_paid_sum", 0)) penalty_paid = float(repayments_values.get("penalty_paid_sum", 0)) #principal balance principal_balance = overdraft.amount - principal_paid #interest balance if overdraft.status != 'closed': if overdraft.charge_type == 'flat': interest_charge = overdraft.charge_rate else: interest_charge = (overdraft.charge_rate * overdraft.amount) * 0.01 if past_days: interest_balance = (interest_charge * past_days) - interest_paid else: interest_balance = 0 before_penalty_date = (overdraft.transaction_date + timedelta(days = (overdraft.penalty_grace_period + overdraft.over_draft_period))) arear_days = max((as_at - before_penalty_date.date()).days,0) penalty_days = 0 penalty_interval = overdraft.penalty_interval if penalty_interval<= 0: penalty_interval = 1 penalty_days = arear_days/penalty_interval if overdraft.status != 'closed': if overdraft.penalty_type == 'flat': penalty_charge = overdraft.penalty_rate else: penalty_charge = (overdraft.penalty_rate * principal_balance) * 0.01 penalty_balance = (penalty_days * penalty_charge) - penalty_paid else: penalty_balance = 0 return {"principal_balance":principal_balance,"interest_balance":interest_balance,"penalty_balance":penalty_balance} def compute_over_due_payment(overdraft,as_at, eat_timezone,payment_date): principal_paid = 0 interest_paid = 0 penalty_paid = 0 principal_balance = 0 interest_balance = 0 penalty_balance = 0 interest_charge = 0 penalty_charge = 0 transaction_date=datetime.strptime(overdraft.transaction_date.astimezone(eat_timezone).strftime('%Y-%m-%d'), '%Y-%m-%d') current_date=datetime.strptime(datetime.now().astimezone(eat_timezone).strftime('%Y-%m-%d'), '%Y-%m-%d') back_date=datetime.strptime(payment_date.astimezone(eat_timezone).strftime('%Y-%m-%d'), '%Y-%m-%d') past_days = ((current_date-transaction_date).days)-1 if payment_date: past_days = ((back_date-transaction_date).days)-1 repayments = OverDraftPayment.objects.filter(over_draft=overdraft,transaction__transaction_type='normal').values('over_draft__id').annotate(princ_paid_sum=Sum("principal_paid"),int_paid_sum=Sum('interest_paid'),penalty_paid_sum=Sum('penalty_paid')).values("princ_paid_sum",'int_paid_sum','penalty_paid_sum') if repayments: repayments_values = repayments[0] principal_paid = float(repayments_values.get("princ_paid_sum", 0)) interest_paid = float(repayments_values.get("int_paid_sum", 0)) penalty_paid = float(repayments_values.get("penalty_paid_sum", 0)) #principal balance principal_balance = overdraft.amount - principal_paid #interest balance if overdraft.status != 'closed': if overdraft.charge_type == 'flat': interest_charge = overdraft.charge_rate else: interest_charge = (overdraft.charge_rate * overdraft.amount) * 0.01 if past_days: interest_balance = (interest_charge * past_days) - interest_paid else: interest_balance = 0 before_penalty_date = (overdraft.transaction_date + timedelta(days = (overdraft.penalty_grace_period + overdraft.over_draft_period))) arear_days = max((as_at - before_penalty_date.date()).days,0) if payment_date: arear_days = max((back_date.date() - before_penalty_date.date()).days,0) penalty_days = 0 penalty_interval = overdraft.penalty_interval if penalty_interval<= 0: penalty_interval = 1 penalty_days = arear_days/penalty_interval if overdraft.status != 'closed': if overdraft.penalty_type == 'flat': penalty_charge = overdraft.penalty_rate else: penalty_charge = (overdraft.penalty_rate * principal_balance) * 0.01 penalty_balance = (penalty_days * penalty_charge) - penalty_paid else: penalty_balance = 0 return {"principal_balance":principal_balance,"interest_balance":interest_balance,"penalty_balance":penalty_balance}