from .serializers import * from savings.models import SavingsProduct,SavingAccount,GroupSavingTransaction from organisations.models import * from customers.models import * from custom_searches.models import CustomerSearch,SavingsAccountSearch from django.db.models import Count,Sum from django.db.models import Q from datetime import datetime from django.utils.timezone import make_aware from ledgers.ledgers_helper import get_transactional_charts,get_income_statement_lines from ledgers.models import OrganisationSubAccount from savings.models import SavingAccountTransactions from shares.models import SharesTransaction from loans.models import LoanApplication,LoanApplicationApproval,LoanApplicationDisbursement,LoanRepaymentview from shares.models import SharesTransaction from shares.serializers import SharesTemplateSerializer import pandas as pd from datetime import datetime from django.conf import settings import os import json from .models import * def get_chart_child_transactions(line_charts, branch_id, start, end, coverage): last_node_transactions = [] leaf_transactions = OrganisationSubAccountSerializer( line_charts, many=True, context={ 'branch_id': branch_id, 'start_date': start, 'end_date': end, 'type': 'balanced' } ).data for account_chart_transaction in leaf_transactions: balance_bf = account_chart_transaction.get('balance_bf') or {} balance_btn = account_chart_transaction.get('balance_between') or {} forward = balance_bf.get('balance_raw', 0) between = balance_btn.get('balance_raw', 0) if ((forward != 0 or between != 0) and coverage == 'balanced') or ( balance_btn.get('balance_raw', 0) != 0 and coverage != 'balanced' ): parent_account = account_chart_transaction.get('parent_account') if not parent_account: continue # skip orphaned accounts with no parent reference account_index = get_parent_index(parent_account.get('id'), last_node_transactions) if account_index >= 0: last_node_transactions[account_index]['sub_accounts'].append({ 'id': account_chart_transaction['id'], 'account_code': account_chart_transaction['account_code'], 'account_name': account_chart_transaction['account_name'], 'balance_bf': balance_bf, 'balance_btn': balance_btn }) else: last_node_transactions.append({ 'id': parent_account.get('id'), 'account_code': parent_account.get('account_code'), 'account_name': parent_account.get('account_name'), 'sub_accounts': [ { 'id': account_chart_transaction['id'], 'account_code': account_chart_transaction['account_code'], 'account_name': account_chart_transaction['account_name'], 'balance_bf': balance_bf, 'balance_btn': balance_btn } ] }) # merge parent/child ids if only one child for i in range(len(last_node_transactions)): if len(last_node_transactions[i]['sub_accounts']) == 1: last_node_transactions[i]['id'] = last_node_transactions[i]['sub_accounts'][0]['id'] return last_node_transactions def get_parent_index(parent_id, account_list): i = 0 found = False for account in account_list: if account['id'] == parent_id: found = True break i = i + 1 if found: return i return -1 def get_savings_filters(organisation_id): branches_array = [] client_types_array = [] saving_product_array = [] # profucts saving_products = SavingsProduct.objects.filter(saving_product_org__id=organisation_id, deleted=False) for saving_product in saving_products: saving_product_array.append({ "id":saving_product.id, "name":saving_product.product_name }) # client types client_types = CustomerType.objects.filter(organisation__id=organisation_id) for client_type in client_types: client_types_array.append({ "id":client_type.id, "name":client_type.customer_type }) # gender gender = [{"id":'M', "name":'Male'}, {"id":'F', "name":'Female'}, {"id":'O', "name":'Other'}] # branches branches = OrganisationBranch.objects.filter(branch_organisation__id=organisation_id) for branch in branches: branches_array.append({ "id":branch.id, "name":branch.name }) return { "saving_product":saving_product_array, "client_types":client_types_array, "client_types":client_types_array, "gender":gender, "branches":branches_array } def get_customers_by_type(dashboard_summary,query_parameter): filter_array = {} if not query_parameter["branch_id"]: return dashboard_summary if int(query_parameter["branch_id"]) == 0: filter_array["organisation_id"] = query_parameter["organisation_id"] else: filter_array["branch_id"] = query_parameter["branch_id"] filter_array["is_deleted"] = False customer_types = CustomerSearch.objects.filter(**filter_array).values( "customer_type_name","customer_type_id").annotate(total=Count('customer_id')) if customer_types: customer_totals = 0 for customer_type in customer_types: customer_type_name = customer_type["customer_type_name"] customer_type_total = customer_type["total"] customer_totals += customer_type_total data = {"customer_type":customer_type_name,"total":customer_type_total} dashboard_summary["customers"].append(data) dashboard_summary["customer_totals"] = customer_totals return dashboard_summary def get_customers_by_gender(dashboard_summary,query_parameter): gender_array = {} total_count = 0 if not query_parameter["branch_id"]: return dashboard_summary if int(query_parameter["branch_id"]) == 0: gender_array["organisation_id"] = query_parameter["organisation_id"] else: gender_array["branch_id"] = query_parameter["branch_id"] gender_array["is_deleted"] = False customer_genders = CustomerSearch.objects.filter(**gender_array).values( "gender").annotate(total=Count('customer_id')) if customer_genders: all_gender_totals = 0 male_gender_total =0 female_gender_total = 0 for gender in customer_genders: gender_totals = gender['total'] all_gender_totals += gender_totals gender_name = gender["gender"] if gender_name == 'M': male_gender_total = gender["total"] dashboard_summary["male_total"] = male_gender_total dashboard_summary['male_count'] = male_gender_total if male_gender_total: total_count += male_gender_total if gender_name == 'F': female_gender_total = gender["total"] dashboard_summary["female_total"] = female_gender_total dashboard_summary['female_count'] = female_gender_total if female_gender_total: total_count += female_gender_total dashboard_summary["all_gender_totals"] = all_gender_totals dashboard_summary["male_count"] = male_gender_total dashboard_summary["female_count"] = female_gender_total dashboard_summary['other_total'] = all_gender_totals - (male_gender_total + female_gender_total) dashboard_summary["other_count"] = all_gender_totals - (male_gender_total + female_gender_total) return dashboard_summary def get_customers_savings_summary(dashboard_summary,query_parameter): start = query_parameter["start"] end = query_parameter["end"] total_count = 0 total_amount = 0 # Savings account status counts (all-time, not period-filtered) savings_filter = {"deleted": False} if not query_parameter["branch_id"]: return dashboard_summary if int(query_parameter["branch_id"]) == 0: savings_filter["customer_branch__branch_organisation__id"] = query_parameter["organisation_id"] else: savings_filter["customer_branch__id"] = query_parameter["branch_id"] savings_accounts = SavingAccount.objects.filter(**savings_filter) dashboard_summary["savings_active"] = savings_accounts.filter(status="active").count() dashboard_summary["savings_dormant"] = savings_accounts.filter(status="dormant").count() dashboard_summary["savings_inactive"] = savings_accounts.filter(status="inactive").count() deposit_filter = {"transaction_type":"deposit"} withdrwals_filter = {"transaction_type":"withdrawal"} transfers_filter = {"transaction_type":"transfer"} if not query_parameter["branch_id"]: return dashboard_summary if int(query_parameter["branch_id"]) == 0: deposit_filter["transaction__branch__branch_organisation__id"] = query_parameter["organisation_id"] withdrwals_filter["transaction__branch__branch_organisation__id"] = query_parameter["organisation_id"] transfers_filter["transaction__branch__branch_organisation__id"] = query_parameter["organisation_id"] else: deposit_filter["transaction__branch__id"] = query_parameter["branch_id"] withdrwals_filter["transaction__branch__id"] = query_parameter["branch_id"] transfers_filter["transaction__branch__id"] = query_parameter["branch_id"] if start: deposit_filter["transaction__record_date__gte"] = make_aware(datetime.strptime(start + ' 00:00', '%Y-%m-%d %H:%M')) withdrwals_filter["transaction__record_date__gte"] = make_aware(datetime.strptime(start + ' 00:00', '%Y-%m-%d %H:%M')) transfers_filter["transaction__record_date__gte"] = make_aware(datetime.strptime(start + ' 00:00', '%Y-%m-%d %H:%M')) if end: deposit_filter["transaction__record_date__lte"] = make_aware(datetime.strptime(end + ' 23:59', '%Y-%m-%d %H:%M')) withdrwals_filter["transaction__record_date__lte"] = make_aware(datetime.strptime(end + ' 23:59', '%Y-%m-%d %H:%M')) transfers_filter["transaction__record_date__lte"] = make_aware(datetime.strptime(end + ' 23:59', '%Y-%m-%d %H:%M')) deposits = SavingAccountTransactions.objects.filter(**deposit_filter).aggregate(total_sum=Sum('transaction__amount'),total=Count('id')) withdrwals = SavingAccountTransactions.objects.filter(**withdrwals_filter).aggregate(total_sum=Sum('transaction__amount'),total=Count('id')) transfers = SavingAccountTransactions.objects.filter(**transfers_filter).aggregate(total_sum=Sum('transaction__amount'),total=Count('id')) #Deposits dashboard_summary["deposits"] = deposits["total_sum"] dashboard_summary["deposits_count"] = deposits["total"] if deposits["total"]: total_count += deposits["total"] total_amount += deposits["total_sum"] #Withdrawals dashboard_summary["withdrwals"] = withdrwals["total_sum"] dashboard_summary["withdrwals_count"] = withdrwals["total"] if withdrwals["total"]: total_count += withdrwals["total"] total_amount += withdrwals["total_sum"] #Transfers dashboard_summary["transfers"] = transfers["total_sum"] dashboard_summary["transfers_count"] = transfers["total"] dashboard_summary["savings_total"] = total_amount dashboard_summary["savings_total_count"] = total_count return dashboard_summary def get_customers_shares_summary(dashboard_summary,query_parameter): start = query_parameter["start"] end = query_parameter["end"] total_count = 0 total_amount = 0 share_purchases_filter = {"transaction_type__in":["purchase"]} share_withdrwals_filter = {"transaction_type__in":["withdrawal"]} share_transfers_filter = {"transaction_type__in":['transfer-in','transfer-out']} if not query_parameter["branch_id"]: return dashboard_summary if int(query_parameter["branch_id"]) == 0: share_purchases_filter["organisation_branch__branch_organisation__id"] = query_parameter["organisation_id"] share_withdrwals_filter["organisation_branch__branch_organisation__id"] = query_parameter["organisation_id"] share_transfers_filter["organisation_branch__branch_organisation__id"] = query_parameter["organisation_id"] else: share_purchases_filter["organisation_branch__id"] = query_parameter["branch_id"] share_withdrwals_filter["organisation_branch__id"] = query_parameter["branch_id"] share_transfers_filter["organisation_branch__id"] = query_parameter["branch_id"] if start: share_purchases_filter["system_transaction__record_date__gte"] = make_aware(datetime.strptime(start + ' 00:00', '%Y-%m-%d %H:%M')) share_withdrwals_filter["system_transaction__record_date__gte"] = make_aware(datetime.strptime(start + ' 00:00', '%Y-%m-%d %H:%M')) share_transfers_filter["system_transaction__record_date__gte"] = make_aware(datetime.strptime(start + ' 00:00', '%Y-%m-%d %H:%M')) if end: share_purchases_filter["system_transaction__record_date__lte"] = make_aware(datetime.strptime(end + ' 23:59', '%Y-%m-%d %H:%M')) share_withdrwals_filter["system_transaction__record_date__lte"] = make_aware(datetime.strptime(end + ' 23:59', '%Y-%m-%d %H:%M')) share_transfers_filter["system_transaction__record_date__lte"] = make_aware(datetime.strptime(end + ' 23:59', '%Y-%m-%d %H:%M')) share_purchases = SharesTransaction.objects.filter(**share_purchases_filter).aggregate(total_sum=Sum('system_transaction__amount'),total=Count('id')) share_withdrwals = SharesTransaction.objects.filter(**share_withdrwals_filter).aggregate(total_sum=Sum('system_transaction__amount'),total=Count('id')) share_transfers = SharesTransaction.objects.filter(**share_transfers_filter).aggregate(total_sum=Sum('system_transaction__amount'),total=Count('id')) #Deposits dashboard_summary["share_purchases"] = share_purchases["total_sum"] dashboard_summary["share_purchases_count"] = share_purchases["total"] if share_purchases["total"]: total_count += share_purchases["total"] total_amount += share_purchases["total_sum"] #Withdrawals dashboard_summary["share_withdrwals"] = share_withdrwals["total_sum"] dashboard_summary["share_withdrwals_count"] = share_withdrwals["total"] if share_withdrwals["total"]: total_count += share_withdrwals["total"] total_amount += share_withdrwals["total_sum"] #Transfers dashboard_summary["share_transfers"] = share_transfers["total_sum"] dashboard_summary["share_transfers_count"] = share_transfers["total"] #if share_transfers["total"]: #total_count += share_transfers["total"] #total_amount += share_transfers["total_sum"] dashboard_summary["shares_total"] = total_amount dashboard_summary["shares_total_count"] = total_count return dashboard_summary def get_income_statement(dashboard_summary,query_parameter): coverage ='selected-period' tb_data = { 'income': [], 'expenses': [] } # Fetch system charts account_lines = get_income_statement_lines() for account_line in account_lines: accounts = OrganisationSubAccount.objects.filter(account_line=account_line, account_organisation=query_parameter["organisation_id"], parent_id__isnull=True).order_by('id').values('id', 'account_code', 'account_name') for account in accounts: line_charts = get_transactional_charts(query_parameter["organisation_id"], account_line, account['account_code']) account['transactions'] = get_chart_child_transactions(line_charts, query_parameter["branch_id"], query_parameter["start"], query_parameter["end"], coverage) tb_data[account_line].append(account) dashboard_summary["income_statement"] = tb_data return dashboard_summary def get_customers_loan_summary(dashboard_summary,query_parameter): start = query_parameter["start"] end = query_parameter["end"] loan_application_filter = {"status":'pending'} loans_approved_filter = {"loan_application__status":'approved'} loans_disbursed_filter = {"loan_application__status":'disbursed'} loan_due_filter = {"loan_balance__gt":0,"is_deleted":False,"status":"disbursed"} if int(query_parameter["branch_id"]) == 0: loan_application_filter["organisation_branch__branch_organisation__id"] = query_parameter["organisation_id"] loans_approved_filter["loan_application__organisation_branch__branch_organisation__id"] = query_parameter["organisation_id"] loans_disbursed_filter["loan_application__organisation_branch__branch_organisation__id"] = query_parameter["organisation_id"] loan_due_filter["organisation_id"] = query_parameter["organisation_id"] else: loan_application_filter["organisation_branch__id"] = query_parameter["branch_id"] loans_approved_filter["loan_application__organisation_branch__id"] = query_parameter["branch_id"] loans_disbursed_filter["loan_application__organisation_branch__id"] = query_parameter["branch_id"] loan_due_filter["branch_id"] = query_parameter["branch_id"] if start: loan_application_filter["loan_date__gte"] = make_aware(datetime.strptime(start + ' 00:00', '%Y-%m-%d %H:%M')) loans_approved_filter["approval_date__gte"] = make_aware(datetime.strptime(start + ' 00:00', '%Y-%m-%d %H:%M')) loans_disbursed_filter["loan_disbursement_date__gte"] = make_aware(datetime.strptime(start + ' 00:00', '%Y-%m-%d %H:%M')) loan_due_filter["loan_arrear_date__gte"] = make_aware(datetime.strptime(start + ' 00:00', '%Y-%m-%d %H:%M')) if end: loan_application_filter["loan_date__lte"] = make_aware(datetime.strptime(end + ' 23:59', '%Y-%m-%d %H:%M')) loans_approved_filter["approval_date__lte"] = make_aware(datetime.strptime(end + ' 23:59', '%Y-%m-%d %H:%M')) loans_disbursed_filter["loan_disbursement_date__lte"] = make_aware(datetime.strptime(end + ' 23:59', '%Y-%m-%d %H:%M')) loan_due_filter["loan_arrear_date__lte"] = make_aware(datetime.strptime(end + ' 23:59', '%Y-%m-%d %H:%M')) loan_applications = LoanApplication.objects.filter(**loan_application_filter).aggregate(total_sum=Sum('loan_amount'),total=Count('id')) loans_approved = LoanApplicationApproval.objects.filter(**loans_approved_filter).aggregate(total_sum=Sum('loan_amount'),total=Count('id')) loans_disbursed = LoanApplicationDisbursement.objects.filter(**loans_disbursed_filter).aggregate(total_sum=Sum('loan_amount'),total=Count('id')) loans_due = LoanRepaymentview.objects.filter(**loan_due_filter).aggregate(total_sum=Sum('loan_balance'),total=Count('id')) loans_due_total = LoanRepaymentview.objects.filter(**loan_due_filter).values_list("id").distinct("id") dashboard_summary["loan_applications"] = loan_applications["total_sum"] dashboard_summary["loan_applications_count"] = loan_applications["total"] dashboard_summary["loans_approved"] = loans_approved["total_sum"] dashboard_summary["loans_approved_count"] = loans_approved["total"] dashboard_summary["loans_disbursed"] = loans_disbursed["total_sum"] dashboard_summary["loans_disbursed_count"] = loans_disbursed["total"] dashboard_summary["loans_due"] = loans_due["total_sum"] dashboard_summary["loans_due_count"] = len(loans_due_total) return dashboard_summary def get_sacco_dashboard_summary(dashboard_summary, query_parameter): """ Fetches savings account statuses, savings transactions, shares and loans without a date filter — returns collective totals scoped only by branch. Used exclusively by the SACCO customer dashboard. """ organisation_id = query_parameter["organisation_id"] branch_id = query_parameter["branch_id"] if not branch_id: return dashboard_summary use_org = int(branch_id) == 0 # ── Savings accounts ──────────────────────────────────────────────────── sa_filter = {"deleted": False} if use_org: sa_filter["customer_branch__branch_organisation__id"] = organisation_id else: sa_filter["customer_branch__id"] = branch_id savings_qs = SavingAccount.objects.filter(**sa_filter) dashboard_summary["savings_active"] = savings_qs.filter(status="active").count() dashboard_summary["savings_dormant"] = savings_qs.filter(status="dormant").count() dashboard_summary["savings_inactive"] = savings_qs.filter(status="inactive").count() dashboard_summary["savings_total_count"] = savings_qs.count() # ── Savings transactions (all-time) ────────────────────────────────────── txn_filter = {} if use_org: txn_filter["transaction__branch__branch_organisation__id"] = organisation_id else: txn_filter["transaction__branch__id"] = branch_id deposits_qs = SavingAccountTransactions.objects.filter(transaction_type="deposit", **txn_filter) withdrawals_qs = SavingAccountTransactions.objects.filter(transaction_type="withdrawal", **txn_filter) transfers_qs = SavingAccountTransactions.objects.filter(transaction_type="transfer", **txn_filter) dep = deposits_qs.aggregate(total_sum=Sum("transaction__amount"), total=Count("id")) wdr = withdrawals_qs.aggregate(total_sum=Sum("transaction__amount"), total=Count("id")) trf = transfers_qs.aggregate(total_sum=Sum("transaction__amount"), total=Count("id")) dashboard_summary["deposits"] = dep["total_sum"] dashboard_summary["deposits_count"] = dep["total"] dashboard_summary["withdrwals"] = wdr["total_sum"] dashboard_summary["withdrwals_count"] = wdr["total"] dashboard_summary["transfers"] = trf["total_sum"] dashboard_summary["transfers_count"] = trf["total"] savings_amount = (dep["total_sum"] or 0) + (wdr["total_sum"] or 0) dashboard_summary["savings_total"] = savings_amount # ── Shares (all-time) ──────────────────────────────────────────────────── sh_filter = {"deleted": False} if use_org: sh_filter["organisation_branch__branch_organisation__id"] = organisation_id else: sh_filter["organisation_branch__id"] = branch_id purch_qs = SharesTransaction.objects.filter(transaction_type="purchase", **sh_filter) wdsh_qs = SharesTransaction.objects.filter(transaction_type="withdrawal", **sh_filter) trsh_qs = SharesTransaction.objects.filter(transaction_type__in=["transfer-in","transfer-out"], **sh_filter) sp = purch_qs.aggregate(total_sum=Sum("system_transaction__amount"), total=Count("id")) sw = wdsh_qs.aggregate(total_sum=Sum("system_transaction__amount"), total=Count("id")) st = trsh_qs.aggregate(total_sum=Sum("system_transaction__amount"), total=Count("id")) dashboard_summary["share_purchases"] = sp["total_sum"] dashboard_summary["share_purchases_count"] = sp["total"] dashboard_summary["share_withdrwals"] = sw["total_sum"] dashboard_summary["share_withdrwals_count"] = sw["total"] dashboard_summary["share_transfers"] = st["total_sum"] dashboard_summary["share_transfers_count"] = st["total"] shares_amount = (sp["total_sum"] or 0) + (sw["total_sum"] or 0) dashboard_summary["shares_total"] = shares_amount dashboard_summary["shares_total_count"] = (sp["total"] or 0) + (sw["total"] or 0) # ── Loans (all-time) ───────────────────────────────────────────────────── la_filter = {"status": "pending"} lap_filter = {"loan_application__status": "approved"} ld_filter = {"loan_application__status": "disbursed"} due_filter = {"loan_balance__gt": 0, "is_deleted": False, "status": "disbursed"} if use_org: la_filter["organisation_branch__branch_organisation__id"] = organisation_id lap_filter["loan_application__organisation_branch__branch_organisation__id"] = organisation_id ld_filter["loan_application__organisation_branch__branch_organisation__id"] = organisation_id due_filter["organisation_id"] = organisation_id else: la_filter["organisation_branch__id"] = branch_id lap_filter["loan_application__organisation_branch__id"] = branch_id ld_filter["loan_application__organisation_branch__id"] = branch_id due_filter["branch_id"] = branch_id loan_apps = LoanApplication.objects.filter(**la_filter).aggregate(total_sum=Sum("loan_amount"), total=Count("id")) loan_appr = LoanApplicationApproval.objects.filter(**lap_filter).aggregate(total_sum=Sum("loan_amount"), total=Count("id")) loan_disb = LoanApplicationDisbursement.objects.filter(**ld_filter).aggregate(total_sum=Sum("loan_amount"), total=Count("id")) loans_due = LoanRepaymentview.objects.filter(**due_filter) due_agg = loans_due.aggregate(total_sum=Sum("loan_balance"), total=Count("id")) due_count = loans_due.values_list("id").distinct("id") dashboard_summary["loan_applications"] = loan_apps["total_sum"] dashboard_summary["loan_applications_count"] = loan_apps["total"] dashboard_summary["loans_approved"] = loan_appr["total_sum"] dashboard_summary["loans_approved_count"] = loan_appr["total"] dashboard_summary["loans_disbursed"] = loan_disb["total_sum"] dashboard_summary["loans_disbursed_count"] = loan_disb["total"] dashboard_summary["loans_due"] = due_agg["total_sum"] dashboard_summary["loans_due_count"] = len(due_count) return dashboard_summary def get_period_earnings(organisation_id, branch_id, start, end): total_incomes = { 'forward': 0, 'btn': 0 } total_expenses = { 'forward': 0, 'btn': 0 } # Compute total incomes. income_charts = get_transactional_charts(organisation_id, 'income') income_balances = OrganisationSubAccountSerializer( income_charts, many=True, context={ 'branch_id': branch_id, 'start_date': start, 'end_date': end, 'type': 'balanced' } ).data for income_balance in income_balances: total_incomes['forward'] += income_balance['balance_bf']['balance_raw'] total_incomes['btn'] += income_balance['balance_between']['balance_raw'] # Compute total expenses. expense_charts = get_transactional_charts(organisation_id, 'expenses') expense_balances = OrganisationSubAccountSerializer( expense_charts, many=True, context={ 'branch_id': branch_id, 'start_date': start, 'end_date': end, 'type': 'balanced' } ).data for expense_balance in expense_balances: total_expenses['forward'] += expense_balance['balance_bf']['balance_raw'] total_expenses['btn'] += expense_balance['balance_between']['balance_raw'] return { 'debits': round(total_expenses['btn']), 'credits': round(total_incomes['btn']), 'forward': round(total_incomes['forward'] - total_expenses['forward']), 'btn': round(total_incomes['btn'] - total_expenses['btn']) } def filter_group_savings_by_gender_product(filters): filter_1 = filters['filter_1'] filter_2 = filters['filter_2'] start = filters['start'] end = filters['end'] search = filters['search'] section = [] vsla_customer_types = [] organisation_id = filters['organisation_id'] vsla_customer_setting = OrganisationSetting.objects.filter(org_setting__id=organisation_id,setting_key='group_savings_customers').first() if vsla_customer_setting: vsla_customer_types = [] if vsla_customer_setting.setting_value: vsla_customer_types = json.loads(vsla_customer_setting.setting_value) if not isinstance(vsla_customer_types, list): vsla_customer_types = list(vsla_customer_types.split(",")) groups = SavingAccount.objects.filter(account_customer__branch_customer_type__id__in = vsla_customer_types,account_product__id__in = filter_1).distinct('account_customer__id').values('account_customer__id', 'account_customer__name', 'account_customer__member_number','account_customer__old_member_number') if groups: for group in groups: sub_section = [] account_list = SavingAccount.objects.filter(account_customer__branch_customer_type__id__in = vsla_customer_types,account_customer__id = group['account_customer__id']) for gender in filter_2: gender_name = 'Male' if gender == 'F': gender_name = 'Female' elif gender == 'O': gender_name = 'Other' gender_data = [] for account in account_list: chart = account.account_product.accounts_chart.id if account: memberships = [] if len(search) > 0: memberships = GroupMembership.objects.filter(Q(member__member_number__icontains=search) | Q(member__name__icontains=search),member__gender=gender,group=account.account_customer,active=True) else: memberships = GroupMembership.objects.filter(member__gender=gender,group=account.account_customer,active=True) if memberships: for membership in memberships: member_bal_at = 0 balance_bf = 0 filter_array = {"savings__customer_account":account,"membership__id":membership.id,"savings__transaction__record_date__date__gte":start,"savings__transaction__record_date__date__lte":end,"savings__transaction__transaction_type":"normal"} filter_array_bf = {"savings__customer_account":account,"membership__id":membership.id,"savings__transaction__record_date__date__lt":start,"savings__transaction__transaction_type":"normal"} group_savings_transactions_bf = GroupSavingTransaction.objects.filter(**filter_array_bf) group_savings = GroupSavingTransaction.objects.filter(**filter_array) if len(group_savings_transactions_bf) > 0: for transacton_bf in group_savings_transactions_bf: if chart == transacton_bf.savings.transaction.credit_chart.id: balance_bf += transacton_bf.savings.transaction.amount else: balance_bf -= transacton_bf.savings.transaction.amount if group_savings: for group_saving in group_savings: if chart == group_saving.savings.transaction.credit_chart.id: member_bal_at += group_saving.savings.transaction.amount else: member_bal_at -= group_saving.savings.transaction.amount gender_data.append({ "id":membership.member.id, "customer_name":membership.member.name, "customer_member_number":membership.member.member_number, "account_no":account.account_no, "member_bal_at":member_bal_at, "balance_bf":balance_bf, "account_balance":member_bal_at + balance_bf }) if len(gender_data) > 0: sub_section.append({ "id":gender, "name":gender_name, "sub_section_data":gender_data }) if len(sub_section) > 0: section.append({ "id":group['account_customer__id'], "name":group['account_customer__name'], "group_member_number":group['account_customer__member_number'], "section_data":sub_section }) return {"total":len(section),"data":section} def filter_group_savings_by_product_branch(filters): filter_1 = filters['filter_1'] filter_2 = filters['filter_2'] start = filters['start'] end = filters['end'] search = filters['search'] section = [] vsla_customer_types = [] organisation_id = filters['organisation_id'] vsla_customer_setting = OrganisationSetting.objects.filter(org_setting__id=organisation_id,setting_key='group_savings_customers').first() if vsla_customer_setting: vsla_customer_types = [] if vsla_customer_setting.setting_value: vsla_customer_types = json.loads(vsla_customer_setting.setting_value) if not isinstance(vsla_customer_types, list): vsla_customer_types = list(vsla_customer_types.split(",")) for branch_id in filter_2: branch = OrganisationBranch.objects.get(pk=branch_id) groups = SavingAccount.objects.filter(customer_branch = branch, account_customer__branch_customer_type__id__in = vsla_customer_types,account_product__id__in = filter_1).distinct('account_customer__id').values('account_customer__id', 'account_customer__name', 'account_customer__member_number','account_customer__old_member_number') sub_section = [] if groups: for group in groups: group_data = [] account_list = SavingAccount.objects.filter(account_customer__branch_customer_type__id__in = vsla_customer_types,account_customer__id = group['account_customer__id'],customer_branch = branch) for account in account_list: chart = account.account_product.accounts_chart.id if account: memberships = [] if len(search) > 0: memberships = GroupMembership.objects.filter(Q(member__member_number__icontains=search) | Q(member__name__icontains=search),group=account.account_customer,active=True).distinct('member__id') else: memberships = GroupMembership.objects.filter(group=account.account_customer,active=True).distinct('member__id') if memberships and len(memberships) > 0: for membership in memberships: member_bal_at = 0 balance_bf = 0 filter_array = {"savings__customer_account":account,"membership__id":membership.id,"savings__transaction__record_date__date__gte":start,"savings__transaction__record_date__date__lte":end,"savings__transaction__transaction_type":"normal"} filter_array_bf = {"savings__customer_account":account,"membership__id":membership.id,"savings__transaction__record_date__date__lt":start,"savings__transaction__transaction_type":"normal"} group_savings_transactions_bf = GroupSavingTransaction.objects.filter(**filter_array_bf) group_savings = GroupSavingTransaction.objects.filter(**filter_array) if len(group_savings_transactions_bf) > 0: for transacton_bf in group_savings_transactions_bf: if chart == transacton_bf.savings.transaction.credit_chart.id: balance_bf += transacton_bf.savings.transaction.amount else: balance_bf -= transacton_bf.savings.transaction.amount if group_savings: for group_saving in group_savings: if chart == group_saving.savings.transaction.credit_chart.id: member_bal_at += group_saving.savings.transaction.amount else: member_bal_at -= group_saving.savings.transaction.amount group_data.append({ "id":membership.member.id, "customer_name":membership.member.name, "customer_member_number":membership.member.member_number, "account_no":account.account_no, "member_bal_at":member_bal_at, "balance_bf":balance_bf, "account_balance":member_bal_at + balance_bf }) if len(group_data) > 0: sub_section.append({ "id":group['account_customer__id'], "name":group['account_customer__name'], "group_member_number":group['account_customer__member_number'], "sub_section_data":group_data }) if len(sub_section) > 0: section.append({ "id":branch.id, "name":branch.name, "section_data":sub_section }) return {"total":len(section),"data":section} def filter_group_savings_by_gender_branch(filters): filter_1 = filters['filter_1'] filter_2 = filters['filter_2'] start = filters['start'] end = filters['end'] search = filters['search'] search section = [] vsla_customer_types = [] organisation_id = filters['organisation_id'] vsla_customer_setting = OrganisationSetting.objects.filter(org_setting__id=organisation_id,setting_key='group_savings_customers').first() if vsla_customer_setting: vsla_customer_types = [] if vsla_customer_setting.setting_value: vsla_customer_types = json.loads(vsla_customer_setting.setting_value) if not isinstance(vsla_customer_types, list): vsla_customer_types = list(vsla_customer_types.split(",")) for branch_id in filter_1: branch = OrganisationBranch.objects.get(pk=branch_id) account_list = [] account_list = SavingAccount.objects.filter(Q(account_customer__member_number__icontains=search) | Q(account_customer__name__icontains=search),account_customer__branch_customer_type__id__in = vsla_customer_types,customer_branch = branch) sub_section = [] for gender in filter_2: gender_name = 'Male' if gender == 'F': gender_name = 'Female' elif gender == 'O': gender_name = 'Other' gender_data = [] for account in account_list: chart = account.account_product.accounts_chart.id if account: memberships = [] if len(search) > 0: memberships = GroupMembership.objects.filter(Q(member__member_number__icontains=search) | Q(member__name__icontains=search),member__gender=gender,group=account.account_customer,active=True).distinct('member__id') else: memberships = GroupMembership.objects.filter(member__gender=gender,group=account.account_customer,active=True).distinct('member__id') if memberships and len(memberships) > 0: for membership in memberships: member_bal_at = 0 balance_bf = 0 filter_array = {"savings__customer_account":account,"membership__id":membership.id,"savings__transaction__record_date__date__gte":start,"savings__transaction__record_date__date__lte":end,"savings__transaction__transaction_type":"normal"} filter_array_bf = {"savings__customer_account":account,"membership__id":membership.id,"savings__transaction__record_date__date__lt":start,"savings__transaction__transaction_type":"normal"} group_savings_transactions_bf = GroupSavingTransaction.objects.filter(**filter_array_bf) group_savings = GroupSavingTransaction.objects.filter(**filter_array) if len(group_savings_transactions_bf) > 0: for transacton_bf in group_savings_transactions_bf: if chart == transacton_bf.savings.transaction.credit_chart.id: balance_bf += transacton_bf.savings.transaction.amount else: balance_bf -= transacton_bf.savings.transaction.amount if group_savings: for group_saving in group_savings: if chart == group_saving.savings.transaction.credit_chart.id: member_bal_at += group_saving.savings.transaction.amount else: member_bal_at -= group_saving.savings.transaction.amount gender_data.append({ "id":membership.member.id, "customer_name":membership.member.name, "customer_member_number":membership.member.member_number, "account_no":account.account_no, "member_bal_at":member_bal_at, "balance_bf":balance_bf, "account_balance":member_bal_at + balance_bf }) if len(gender_data) > 0: sub_section.append({ "id":gender, "name":gender_name, "sub_section_data":gender_data }) if len(sub_section) > 0: section.append({ "id":branch.id, "name":branch.name, "section_data":sub_section }) return {"total":len(section),"data":section} def generate_sacco_report_file(organisation_id, as_at=None): transactions = SharesTransaction.objects.filter(organisation_branch__branch_organisation_id=organisation_id) share_data = SharesTemplateSerializer( transactions, many=True ).data # Convert to DataFrame df = pd.DataFrame(share_data) # Write to CSV file organisation_directory = settings.STATIC_ROOT + f'/reports/{organisation_id}/shares' os.makedirs(organisation_directory, exist_ok=True) csv_file_name = f'{organisation_directory}/shares.csv' df.to_csv(csv_file_name, index=False) df = pd.DataFrame() def generate_sacco_shares_files(organisation_id=None): if organisation_id: generate_sacco_report_file(organisation_id) else: organisations = Organisation.objects.all().order_by('id') for organisation in organisations: generate_sacco_report_file(organisation.id) def inject_retained_earnings(tb_data, retained_earnings, chart_transactions): if retained_earnings['forward'] + retained_earnings['btn'] != 0: entry = { "id": chart_transactions['id'], "account_code": "sys-323", "account_name": "Retained Earnings/Capital Reserves", "balance_bf": { "debits": retained_earnings['debits'] + chart_transactions['balance_bf']['debits'], "credits": retained_earnings['credits'] + chart_transactions['balance_bf']['credits'], "balance": f"{retained_earnings['btn'] + chart_transactions['balance_bf']['balance_raw']:,}", "balance_raw": retained_earnings['btn'] + chart_transactions['balance_bf']['balance_raw'] }, "balance_btn": chart_transactions['balance_between'] } parent_added = False for x in range(0, len(tb_data['capital'][1]['transactions'])): if tb_data['capital'][1]['transactions'][x]['account_code'] == 'sys-32': child_added = False for y in range(0, len(tb_data['capital'][1]['transactions'][x]['sub_accounts'])): if tb_data['capital'][1]['transactions'][x]['sub_accounts'][y]['account_code'] == 'sys-323': tb_data['capital'][1]['transactions'][x]['sub_accounts'][y] = entry child_added = True break if not child_added: tb_data['capital'][1]['transactions'][x]['sub_accounts'].append(entry) parent_added = True break if not parent_added: tb_data['capital'][1]['transactions'].append({ "account_code": "sys-32", "account_name": "Institutional Capital", "sub_accounts": [ entry ] } ) return tb_data