from organisations.models import * from customers.models import * from django.db import connection from loans.helpers.general_helper import convert_json_to_sql_where from datetime import date as _date def _cache_is_fresh(organisation_id, as_at): """Return True if the cache table has data for this org refreshed today.""" try: balance_date = as_at.split('T')[0] if as_at else str(_date.today()) with connection.cursor() as cur: cur.execute(""" SELECT 1 FROM reports_savingsaccountbalances WHERE organisation_id = %s AND balance_date = %s AND last_refreshed >= NOW() - INTERVAL '25 hours' LIMIT 1 """, [organisation_id, balance_date]) return cur.fetchone() is not None except Exception: return False def _query_cache(select_cols, group_cols, where_sql, as_at): """ Query reports_savingsaccountbalances (the pre-computed cache) instead of calling savings_balances_func live. select_cols : list of column expressions for SELECT group_cols : list of column names for GROUP BY where_sql : extra WHERE clause fragment (already safe, built by convert_json_to_sql_where) as_at : date string used to pick the right balance_date partition """ balance_date = as_at.split('T')[0] if as_at else str(_date.today()) cols = ', '.join(select_cols) groups = ', '.join(group_cols) sql = f""" SELECT {cols} FROM reports_savingsaccountbalances acc WHERE balance_date = '{balance_date}' AND {where_sql} GROUP BY {groups} """ with connection.cursor() as cur: cur.execute(sql) return cur.fetchall() def _build_product_branch_response(rows, primary_key): """Build the nested branch/product response structure from aggregated rows.""" response = [] branch_map = {} for row in rows: f1_id, f2_id, savings_data = row[0], row[1], row[2] if f1_id not in branch_map: branch_map[f1_id] = {'filter_1': f1_id, 'filter_1_name': '', 'filter_2': []} response.append(branch_map[f1_id]) branch_map[f1_id]['filter_2'].append({'filter_2': f2_id, 'filter_2_name': '', 'savings': savings_data}) return response def filter_savings_by_product_branch(filters): filter_1 = filters['filter_1'] filter_2 = filters['filter_2'] organisation_id = filters['organisation_id'] as_at = filters['as_at'] start = filters['start'] report_type = filters['report_type'] product_ids = ','.join(str(i) for i in filter_1) if filter_1 else None branch_ids = ','.join(str(i) for i in filter_2) if filter_2 else None product_filter = f'AND sa.account_product_id IN ({product_ids})' if product_ids else '' branch_filter = f'AND sa.customer_branch_id IN ({branch_ids})' if branch_ids else f'AND sp.saving_product_org_id = {organisation_id}' if report_type == 'balance' and not start and _cache_is_fresh(organisation_id, as_at): cache_filter = convert_json_to_sql_where({ "organisation_id": organisation_id, **( {"product_id": filter_1} if filter_1 else {}), **( {"branch_id": filter_2} if filter_2 else {}), }) select_cols = [ 'branch_id', 'product_id', "ARRAY_AGG(json_build_object(" "'id', id, 'account_no', account_no, 'customer_name', customer_name," "'customer_member_number', customer_member_number," "'customer_old_member_number', customer_old_member_number," "'latest_date', latest_date, 'last_updated', last_refreshed," "'balance_raw', (credits + credits_bf) - (debits + debits_bf + min_balance + blocked_amount + blocked_amount_bf + total_held_amount + total_held_amount_bf)," "'balance_at', (credits - debits)," "'balance_actual', (credits + credits_bf) - (debits + debits_bf)," "'balance_bf', (credits_bf - debits_bf)" ")) AS savings_data", ] rows = _query_cache(select_cols, ['branch_id', 'product_id'], cache_filter, as_at) response = [] branch_map = {} for row in rows: branch_id, product_id, savings_data = row[0], row[1], row[2] if branch_id not in branch_map: branch_map[branch_id] = {'filter_1': branch_id, 'filter_1_name': '', 'filter_2': []} response.append(branch_map[branch_id]) branch_map[branch_id]['filter_2'].append({'filter_2': product_id, 'filter_2_name': '', 'savings': savings_data}) return response with connection.cursor() as cursor: if report_type == 'accumulated_balance': query = f""" WITH filtered_accounts AS MATERIALIZED ( SELECT sa.id, sp.accounts_chart_id FROM saving_account sa JOIN savings_product sp ON sp.id = sa.account_product_id WHERE sp.saving_product_org_id = {organisation_id} {product_filter} {branch_filter} ), txn_agg AS ( SELECT sat.customer_account_id, COALESCE(SUM(st.amount) FILTER (WHERE st.credit_chart_id = fa.accounts_chart_id AND DATE(st.record_date) <= DATE('{as_at}')), 0) AS credits, COALESCE(SUM(st.amount) FILTER (WHERE st.debit_chart_id = fa.accounts_chart_id AND DATE(st.record_date) <= DATE('{as_at}')), 0) AS debits, COALESCE(SUM(st.amount) FILTER (WHERE st.credit_chart_id = fa.accounts_chart_id AND sat.transaction_type = 'deposit' AND DATE(st.record_date) <= DATE('{as_at}')), 0) AS deposits, COALESCE(SUM(st.amount) FILTER (WHERE st.debit_chart_id = fa.accounts_chart_id AND sat.transaction_type = 'withdrawal' AND DATE(st.record_date) <= DATE('{as_at}')), 0) AS withdrawals, COALESCE(SUM(st.amount) FILTER (WHERE st.credit_chart_id = fa.accounts_chart_id AND sat.transaction_type = 'transfer' AND DATE(st.record_date) <= DATE('{as_at}')), 0) AS transfers_recieved, COALESCE(SUM(st.amount) FILTER (WHERE st.debit_chart_id = fa.accounts_chart_id AND sat.transaction_type = 'transfer' AND DATE(st.record_date) <= DATE('{as_at}')), 0) AS transfers_sent FROM saving_account_transactions sat JOIN system_transactions st ON st.id = sat.transaction_id JOIN filtered_accounts fa ON fa.id = sat.customer_account_id WHERE sat.deleted = FALSE GROUP BY sat.customer_account_id ), blocked_agg AS ( SELECT b.customer_account_id, COALESCE(SUM(b.amount) FILTER (WHERE DATE(b.date_added) <= DATE('{as_at}')), 0) AS blocked_amount FROM savings_blocked_amount b JOIN filtered_accounts fa ON fa.id = b.customer_account_id GROUP BY b.customer_account_id ), held_agg AS ( SELECT h.account_id, COALESCE(SUM(h.amount) FILTER (WHERE DATE(h.date_added) <= DATE('{as_at}')), 0) AS total_held_amount FROM loan_application_withhold h JOIN filtered_accounts fa ON fa.id = h.account_id GROUP BY h.account_id ), aggregated_data AS ( SELECT acc.branch_id, acc.product_id, ARRAY_AGG(json_build_object( 'id', acc.id, 'account_no', acc.account_no, 'customer_name', acc.customer_name, 'customer_member_number', acc.customer_member_number, 'customer_old_member_number', acc.customer_old_member_number, 'product_name', acc.product_name, 'customer_type', acc.customer_type_name, 'deposits', COALESCE(t.deposits, 0), 'withdrawals', COALESCE(t.withdrawals, 0), 'transfers', COALESCE(t.transfers_recieved, 0) + COALESCE(t.transfers_sent, 0), 'transfers_recieved', COALESCE(t.transfers_recieved, 0), 'share_purchases', 0, 'loan_payments', 0, 'other_credits', COALESCE(t.credits, 0) - COALESCE(t.deposits, 0) - COALESCE(t.transfers_recieved, 0), 'other_debits', COALESCE(t.debits, 0) - COALESCE(t.withdrawals, 0) - COALESCE(t.transfers_sent, 0), 'balance_raw', COALESCE(t.credits, 0) - (COALESCE(t.debits, 0) + acc.min_balance + COALESCE(b.blocked_amount, 0) + COALESCE(h.total_held_amount, 0)), 'balance_actual', COALESCE(t.credits, 0) - COALESCE(t.debits, 0) )) AS savings_data FROM savings_account_search_view acc JOIN filtered_accounts fa ON fa.id = acc.id LEFT JOIN txn_agg t ON t.customer_account_id = acc.id LEFT JOIN blocked_agg b ON b.customer_account_id = acc.id LEFT JOIN held_agg h ON h.account_id = acc.id GROUP BY acc.branch_id, acc.product_id ) SELECT json_build_object( 'filter_1', ad.branch_id, 'filter_1_name', b.name, 'filter_2', ad.product_id, 'filter_2_name', sp.product_name, 'savings_data', ad.savings_data ) AS branch_data FROM aggregated_data ad JOIN organisation_branch b ON b.id = ad.branch_id JOIN savings_product sp ON sp.id = ad.product_id ORDER BY ad.branch_id; """ else: start_filter_bf = f"AND DATE(st.record_date) < DATE('{start}')" if start else f"AND DATE(st.record_date) <= DATE('{as_at}')" start_filter = f"AND DATE(st.record_date) >= DATE('{start}') AND DATE(st.record_date) <= DATE('{as_at}')" if start else f"AND DATE(st.record_date) <= DATE('{as_at}')" blocked_bf = f"AND DATE(b.date_added) < DATE('{start}')" if start else '' blocked_period = f"AND DATE(b.date_added) >= DATE('{start}') AND DATE(b.date_added) <= DATE('{as_at}')" if start else f"AND DATE(b.date_added) <= DATE('{as_at}')" held_bf = f"AND DATE(h.date_added) < DATE('{start}')" if start else '' held_period = f"AND DATE(h.date_added) >= DATE('{start}') AND DATE(h.date_added) <= DATE('{as_at}')" if start else f"AND DATE(h.date_added) <= DATE('{as_at}')" query = f""" WITH filtered_accounts AS MATERIALIZED ( SELECT sa.id, sp.accounts_chart_id FROM saving_account sa JOIN savings_product sp ON sp.id = sa.account_product_id WHERE sp.saving_product_org_id = {organisation_id} {product_filter} {branch_filter} ), txn_agg AS ( SELECT sat.customer_account_id, COALESCE(SUM(st.amount) FILTER (WHERE st.credit_chart_id = fa.accounts_chart_id {start_filter_bf}), 0) AS credits_bf, COALESCE(SUM(st.amount) FILTER (WHERE st.debit_chart_id = fa.accounts_chart_id {start_filter_bf}), 0) AS debits_bf, COALESCE(SUM(st.amount) FILTER (WHERE st.credit_chart_id = fa.accounts_chart_id {start_filter}), 0) AS credits, COALESCE(SUM(st.amount) FILTER (WHERE st.debit_chart_id = fa.accounts_chart_id {start_filter}), 0) AS debits, COALESCE(MAX(st.record_date), NULL) AS latest_date FROM saving_account_transactions sat JOIN system_transactions st ON st.id = sat.transaction_id JOIN filtered_accounts fa ON fa.id = sat.customer_account_id WHERE sat.deleted = FALSE GROUP BY sat.customer_account_id ), blocked_agg AS ( SELECT b.customer_account_id, COALESCE(SUM(b.amount) FILTER (WHERE TRUE {blocked_bf}), 0) AS blocked_amount_bf, COALESCE(SUM(b.amount) FILTER (WHERE TRUE {blocked_period}), 0) AS blocked_amount FROM savings_blocked_amount b JOIN filtered_accounts fa ON fa.id = b.customer_account_id GROUP BY b.customer_account_id ), held_agg AS ( SELECT h.account_id, COALESCE(SUM(h.amount) FILTER (WHERE TRUE {held_bf}), 0) AS total_held_amount_bf, COALESCE(SUM(h.amount) FILTER (WHERE TRUE {held_period}), 0) AS total_held_amount FROM loan_application_withhold h JOIN filtered_accounts fa ON fa.id = h.account_id GROUP BY h.account_id ), aggregated_data AS ( SELECT acc.branch_id, acc.product_id, ARRAY_AGG(json_build_object( 'id', acc.id, 'account_no', acc.account_no, 'customer_name', acc.customer_name, 'customer_member_number', acc.customer_member_number, 'customer_old_member_number', acc.customer_old_member_number, 'latest_date', COALESCE(t.latest_date, acc.open_date::timestamp with time zone), 'last_updated', acc.last_updated, 'balance_raw', (COALESCE(t.credits,0) + COALESCE(t.credits_bf,0)) - (COALESCE(t.debits,0) + COALESCE(t.debits_bf,0) + acc.min_balance + COALESCE(b.blocked_amount,0) + COALESCE(b.blocked_amount_bf,0) + COALESCE(h.total_held_amount,0) + COALESCE(h.total_held_amount_bf,0)), 'balance_at', COALESCE(t.credits,0) - COALESCE(t.debits,0), 'balance_actual', (COALESCE(t.credits,0) + COALESCE(t.credits_bf,0)) - (COALESCE(t.debits,0) + COALESCE(t.debits_bf,0)), 'balance_bf', COALESCE(t.credits_bf,0) - COALESCE(t.debits_bf,0) )) AS savings_data FROM savings_account_search_view acc JOIN filtered_accounts fa ON fa.id = acc.id LEFT JOIN txn_agg t ON t.customer_account_id = acc.id LEFT JOIN blocked_agg b ON b.customer_account_id = acc.id LEFT JOIN held_agg h ON h.account_id = acc.id GROUP BY acc.branch_id, acc.product_id ) SELECT json_build_object( 'filter_1', ad.branch_id, 'filter_1_name', b.name, 'filter_2', ad.product_id, 'filter_2_name', sp.product_name, 'savings_data', ad.savings_data ) AS branch_data FROM aggregated_data ad JOIN organisation_branch b ON b.id = ad.branch_id JOIN savings_product sp ON sp.id = ad.product_id ORDER BY ad.branch_id; """ cursor.execute(query) rows = cursor.fetchall() response = [] branch_map = {} for row in rows: branch_id = row[0]['filter_1'] branch_name = row[0]['filter_1_name'] loan_product_id = row[0]['filter_2'] product_name = row[0]['filter_2_name'] if branch_id not in branch_map: branch_entry = {'filter_1': branch_id, 'filter_1_name': branch_name, 'filter_2': []} branch_map[branch_id] = branch_entry response.append(branch_entry) branch_map[branch_id]['filter_2'].append({ 'filter_2': loan_product_id, 'filter_2_name': product_name, 'savings': row[0]['savings_data'] }) return response def filter_savings_by_gender_product(filters): filter_1 = filters['filter_1'] filter_2 = filters['filter_2'] report_type = filters['report_type'] organisation_id = filters['organisation_id'] organisation_branch_id = filters['organisation_branch_id'] as_at = filters['as_at'] start = filters['start'] savings_filter = convert_json_to_sql_where({"acc.organisation_id": organisation_id, "acc.product_id": filter_1, "acc.gender": filter_2, "acc.branch_id": [organisation_branch_id]}) query_sting = f' where {savings_filter}' if report_type == 'balance' and not start and _cache_is_fresh(organisation_id, as_at): cache_filter = convert_json_to_sql_where({"organisation_id": organisation_id, "product_id": filter_1, "gender": filter_2, "branch_id": [organisation_branch_id]}) select_cols = [ 'product_id', 'gender', "ARRAY_AGG(json_build_object(" "'id', id, 'account_no', account_no, 'customer_name', customer_name," "'customer_member_number', customer_member_number," "'customer_old_member_number', customer_old_member_number," "'latest_date', latest_date, 'last_updated', last_refreshed," "'balance_raw', (credits + credits_bf) - (debits + debits_bf + min_balance + blocked_amount + blocked_amount_bf + total_held_amount + total_held_amount_bf)," "'balance_at', (credits - debits)," "'balance_actual', (credits + credits_bf) - (debits + debits_bf)," "'balance_bf', (credits_bf - debits_bf)" ")) AS savings_data", ] rows = _query_cache(select_cols, ['product_id', 'gender'], cache_filter, as_at) return _build_product_branch_response(rows, 'product') with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT product_id, gender, ARRAY_AGG(json_build_object( 'id', id, 'account_no', account_no, 'customer_name', customer_name, 'customer_member_number', customer_member_number, 'customer_old_member_number', customer_old_member_number, 'latest_date', latest_date, 'last_updated', last_updated, 'balance_raw', (credits + credits_bf) - (debits + debits_bf + min_balance + blocked_amount + blocked_amount_bf + total_held_amount + total_held_amount_bf ), 'balance_at', (credits - debits), 'balance_actual', (credits + credits_bf) - (debits + debits_bf), 'balance_bf', (credits_bf - debits_bf) )) AS savings_data FROM public.savings_balances_func('{query_sting}','{as_at}','{start}') GROUP BY product_id, gender ) SELECT json_build_object( 'filter_1', ad.product_id, 'filter_1_name', sp.product_name, 'filter_2', ad.gender, 'filter_2_name', CASE WHEN ad.gender = 'M' THEN 'Male' WHEN ad.gender = 'F' THEN 'Female' WHEN ad.gender = 'O' THEN 'Others' ELSE 'Others' END, 'savings_data', ad.savings_data ) AS branch_data FROM aggregated_data ad JOIN savings_product sp ON ad.product_id = sp.id ORDER BY ad.product_id; """ if report_type == 'accumulated_balance': query = f""" WITH aggregated_data AS ( SELECT product_id, gender, ARRAY_AGG(json_build_object( 'id', id, 'account_no', account_no, 'customer_name', customer_name, 'customer_member_number', customer_member_number, 'customer_old_member_number', customer_old_member_number, 'product_name', product_name, 'customer_type', customer_type_name, 'deposits', deposits, 'withdrawals', withdrawals, 'transfers', transfers_recieved + transfers_sent, 'transfers_recieved', transfers_recieved, 'share_purchases', share_purchases, 'loan_payments', loan_payments, 'other_credits', credits - (deposits + transfers_recieved), 'other_debits', debits - (withdrawals + loan_payments + share_purchases + transfers_sent), 'balance_raw', credits - (debits + min_balance + blocked_amount + total_held_amount), 'balance_actual', credits - debits )) AS savings_data FROM public.cummulated_savings_balances_func('{query_sting}','{as_at}') GROUP BY product_id, gender ) SELECT json_build_object( 'filter_1', ad.product_id, 'filter_1_name', sp.product_name, 'filter_2', ad.gender, 'filter_2_name', CASE WHEN ad.gender = 'M' THEN 'Male' WHEN ad.gender = 'F' THEN 'Female' WHEN ad.gender = 'O' THEN 'Others' ELSE 'Others' END, 'savings_data', ad.savings_data ) AS branch_data FROM aggregated_data ad JOIN savings_product sp ON ad.product_id = sp.id ORDER BY ad.product_id; """ cursor.execute(query) # Fetch all rows rows = cursor.fetchall() response = [] branch_map = {} for row in rows: product_id = row[0]['filter_1'] product_name = row[0]['filter_1_name'] gender = row[0]['filter_2'] gender_name = row[0]['filter_2_name'] # Check if the branch_id is already in the response if product_id not in branch_map: # Create a new entry for the branch branch_entry = { 'filter_1': product_id, 'filter_1_name': product_name, 'filter_2': [] } branch_map[product_id] = branch_entry response.append(branch_entry) # Get the current branch entry branch_entry = branch_map[product_id] # Add the loan product entry to filter_2 loan_product_entry = { 'filter_2': gender, 'filter_2_name': gender_name, 'savings': row[0]['savings_data'] } branch_entry['filter_2'].append(loan_product_entry) return response def filter_savings_by_client_type_product(filters): filter_1 = filters['filter_1'] filter_2 = filters['filter_2'] report_type = filters['report_type'] organisation_id = filters['organisation_id'] organisation_branch_id = filters['organisation_branch_id'] as_at = filters['as_at'] start = filters['start'] savings_filter = convert_json_to_sql_where({"acc.organisation_id": organisation_id, "acc.product_id": filter_1, "acc.customer_type_id": filter_2, "acc.branch_id": [organisation_branch_id]}) query_sting = f' where {savings_filter}' if report_type == 'balance' and not start and _cache_is_fresh(organisation_id, as_at): cache_filter = convert_json_to_sql_where({"organisation_id": organisation_id, "product_id": filter_1, "customer_type_id": filter_2, "branch_id": [organisation_branch_id]}) select_cols = [ 'customer_type_id', 'product_id', "ARRAY_AGG(json_build_object(" "'id', id, 'account_no', account_no, 'customer_name', customer_name," "'customer_member_number', customer_member_number," "'customer_old_member_number', customer_old_member_number," "'latest_date', latest_date, 'last_updated', last_refreshed," "'balance_raw', (credits + credits_bf) - (debits + debits_bf + min_balance + blocked_amount + blocked_amount_bf + total_held_amount + total_held_amount_bf)," "'balance_at', (credits - debits)," "'balance_actual', (credits + credits_bf) - (debits + debits_bf)," "'balance_bf', (credits_bf - debits_bf)" ")) AS savings_data", ] rows = _query_cache(select_cols, ['customer_type_id', 'product_id'], cache_filter, as_at) return _build_product_branch_response(rows, 'customer_type') with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT customer_type_id, product_id, ARRAY_AGG(json_build_object( 'id', id, 'account_no', account_no, 'customer_name', customer_name, 'customer_member_number', customer_member_number, 'customer_old_member_number', customer_old_member_number, 'latest_date', latest_date, 'last_updated', last_updated, 'balance_raw', (credits + credits_bf) - (debits + debits_bf + min_balance + blocked_amount + blocked_amount_bf + total_held_amount + total_held_amount_bf ), 'balance_at', (credits - debits), 'balance_actual', (credits + credits_bf) - (debits + debits_bf), 'balance_bf', (credits_bf - debits_bf) )) AS savings_data FROM public.savings_balances_func('{query_sting}','{as_at}','{start}') GROUP BY customer_type_id, product_id ) SELECT json_build_object( 'filter_1', ad.customer_type_id, 'filter_1_name', ct.customer_type, 'filter_2', ad.product_id, 'filter_2_name', sp.product_name, 'savings_data', ad.savings_data ) AS branch_data FROM aggregated_data ad JOIN customer_type ct ON ad.customer_type_id = ct.id JOIN savings_product sp ON ad.product_id = sp.id ORDER BY ad.customer_type_id; """ if report_type == 'accumulated_balance': query = f""" WITH aggregated_data AS ( SELECT customer_type_id, product_id, ARRAY_AGG(json_build_object( 'id', id, 'account_no', account_no, 'customer_name', customer_name, 'customer_member_number', customer_member_number, 'customer_old_member_number', customer_old_member_number, 'product_name', product_name, 'customer_type', customer_type_name, 'deposits', deposits, 'withdrawals', withdrawals, 'transfers', transfers_recieved + transfers_sent, 'transfers_recieved', transfers_recieved, 'share_purchases', share_purchases, 'loan_payments', loan_payments, 'other_credits', credits - (deposits + transfers_recieved), 'other_debits', debits - (withdrawals + loan_payments + share_purchases + transfers_sent), 'balance_raw', credits - (debits + min_balance + blocked_amount + total_held_amount), 'balance_actual', credits - debits )) AS savings_data FROM public.cummulated_savings_balances_func('{query_sting}','{as_at}') GROUP BY customer_type_id, product_id ) SELECT json_build_object( 'filter_1', ad.customer_type_id, 'filter_1_name', ct.customer_type, 'filter_2', ad.product_id, 'filter_2_name', sp.product_name, 'savings_data', ad.savings_data ) AS branch_data FROM aggregated_data ad JOIN customer_type ct ON ad.customer_type_id = ct.id JOIN savings_product sp ON ad.product_id = sp.id ORDER BY ad.customer_type_id; """ cursor.execute(query) # Fetch all rows rows = cursor.fetchall() response = [] branch_map = {} for row in rows: customer_type = row[0]['filter_1'] customer_type_name = row[0]['filter_1_name'] loan_product_id = row[0]['filter_2'] product_name = row[0]['filter_2_name'] # Check if the branch_id is already in the response if customer_type not in branch_map: # Create a new entry for the branch branch_entry = { 'filter_1': customer_type, 'filter_1_name': customer_type_name, 'filter_2': [] } branch_map[customer_type] = branch_entry response.append(branch_entry) # Get the current branch entry branch_entry = branch_map[customer_type] # Add the loan product entry to filter_2 loan_product_entry = { 'filter_2': loan_product_id, 'filter_2_name': product_name, 'savings': row[0]['savings_data'] } branch_entry['filter_2'].append(loan_product_entry) return response def filter_savings_by_gender_branch(filters): filter_1 = filters['filter_1'] filter_2 = filters['filter_2'] report_type = filters['report_type'] organisation_id = filters['organisation_id'] as_at = filters['as_at'] start = filters['start'] savings_filter = convert_json_to_sql_where({"acc.organisation_id": organisation_id, "acc.branch_id": filter_1, "acc.gender": filter_2 }) query_sting = f' where {savings_filter}' if report_type == 'balance' and not start and _cache_is_fresh(organisation_id, as_at): cache_filter = convert_json_to_sql_where({"organisation_id": organisation_id, "branch_id": filter_1, "gender": filter_2}) select_cols = [ 'branch_id', 'gender', "ARRAY_AGG(json_build_object(" "'id', id, 'account_no', account_no, 'customer_name', customer_name," "'customer_member_number', customer_member_number," "'customer_old_member_number', customer_old_member_number," "'latest_date', latest_date, 'last_updated', last_refreshed," "'balance_raw', (credits + credits_bf) - (debits + debits_bf + min_balance + blocked_amount + blocked_amount_bf + total_held_amount + total_held_amount_bf)," "'balance_at', (credits - debits)," "'balance_actual', (credits + credits_bf) - (debits + debits_bf)," "'balance_bf', (credits_bf - debits_bf)" ")) AS savings_data", ] rows = _query_cache(select_cols, ['branch_id', 'gender'], cache_filter, as_at) return _build_product_branch_response(rows, 'branch') with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT branch_id, gender, ARRAY_AGG(json_build_object( 'id', id, 'account_no', account_no, 'customer_name', customer_name, 'customer_member_number', customer_member_number, 'customer_old_member_number', customer_old_member_number, 'latest_date', latest_date, 'last_updated', last_updated, 'balance_raw', (credits + credits_bf) - (debits + debits_bf + min_balance + blocked_amount + blocked_amount_bf + total_held_amount + total_held_amount_bf ), 'balance_at', (credits - debits), 'balance_actual', (credits + credits_bf) - (debits + debits_bf), 'balance_bf', (credits_bf - debits_bf) )) AS savings_data FROM public.savings_balances_func('{query_sting}','{as_at}','{start}') GROUP BY branch_id, gender ) SELECT json_build_object( 'filter_1', ad.branch_id, 'filter_1_name', b.name, 'filter_2', ad.gender, 'filter_2_name', CASE WHEN ad.gender = 'M' THEN 'Male' WHEN ad.gender = 'F' THEN 'Female' WHEN ad.gender = 'O' THEN 'Others' ELSE 'Others' END, 'savings_data', ad.savings_data ) AS branch_data FROM aggregated_data ad JOIN organisation_branch b ON ad.branch_id = b.id ORDER BY ad.branch_id; """ if report_type == 'accumulated_balance': query = f""" WITH aggregated_data AS ( SELECT branch_id, gender, ARRAY_AGG(json_build_object( 'id', id, 'account_no', account_no, 'customer_name', customer_name, 'customer_member_number', customer_member_number, 'customer_old_member_number', customer_old_member_number, 'product_name', product_name, 'customer_type', customer_type_name, 'deposits', deposits, 'withdrawals', withdrawals, 'transfers', transfers_recieved + transfers_sent, 'transfers_recieved', transfers_recieved, 'share_purchases', share_purchases, 'loan_payments', loan_payments, 'other_credits', credits - (deposits + transfers_recieved), 'other_debits', debits - (withdrawals + loan_payments + share_purchases + transfers_sent), 'balance_raw', credits - (debits + min_balance + blocked_amount + total_held_amount), 'balance_actual', credits - debits )) AS savings_data FROM public.cummulated_savings_balances_func('{query_sting}','{as_at}') GROUP BY branch_id, gender ) SELECT json_build_object( 'filter_1', ad.branch_id, 'filter_1_name', b.name, 'filter_2', ad.gender, 'filter_2_name', CASE WHEN ad.gender = 'M' THEN 'Male' WHEN ad.gender = 'F' THEN 'Female' WHEN ad.gender = 'O' THEN 'Others' ELSE 'Others' END, 'savings_data', ad.savings_data ) AS branch_data FROM aggregated_data ad JOIN organisation_branch b ON ad.branch_id = b.id ORDER BY ad.branch_id; """ cursor.execute(query) # Fetch all rows rows = cursor.fetchall() response = [] branch_map = {} for row in rows: branch_id = row[0]['filter_1'] branch_name = row[0]['filter_1_name'] gender = row[0]['filter_2'] gender_name = row[0]['filter_2_name'] # Check if the branch_id is already in the response if branch_id not in branch_map: # Create a new entry for the branch branch_entry = { 'filter_1': branch_id, 'filter_1_name': branch_name, 'filter_2': [] } branch_map[branch_id] = branch_entry response.append(branch_entry) # Get the current branch entry branch_entry = branch_map[branch_id] # Add the loan product entry to filter_2 loan_product_entry = { 'filter_2': gender, 'filter_2_name': gender_name, 'savings': row[0]['savings_data'] } branch_entry['filter_2'].append(loan_product_entry) return response