from rest_framework.response import Response from django.db import connection from loans.serializers import * from .general_helper import convert_json_to_sql_where from questbanker_api.utils import get_current_user def filter_loan_performing_reports(report_filters): results = [] filter_1 = report_filters['filter_1'] filter_2 = report_filters['filter_2'] princ_bal = 0 report_filter = report_filters['report_filter'] organisation_branch_id = get_current_user(report_filters['request'], 'organisation_branch_id', None) organisation_id = get_current_user(report_filters['request'], 'organisation_id', None) as_at = report_filters['as_at'] extra_filters = f"date(lad.loan_disbursement_date at time zone ''Africa/Nairobi'') <= ''{as_at}''" if report_filter == 'product_branch': loan_filter = convert_json_to_sql_where({"lv.loan_product_id": filter_1, "lv.branch_id": filter_2}) loan_filter = f"{loan_filter} AND {extra_filters}" results = filter_arrears_product_branch(organisation_id, loan_filter, as_at) elif report_filter == 'officer_client_type': loan_filter = convert_json_to_sql_where({"lv.loan_officer_id": filter_1, "lv.customer_type_id": filter_2, "lv.branch_id": [organisation_branch_id]}) loan_filter = f"{loan_filter} AND {extra_filters}" results = filter_arrears_officer_client_type(organisation_id, loan_filter, as_at) elif report_filter == 'officer_gender': loan_filter = convert_json_to_sql_where({"lv.loan_officer_id": filter_1, "lv.gender": filter_2, "lv.branch_id": [organisation_branch_id]}) loan_filter = f"{loan_filter} AND {extra_filters}" results = filter_arrears_officer_gender(organisation_id, loan_filter, as_at) elif report_filter == 'officer_branch': loan_filter = convert_json_to_sql_where({"lv.loan_officer_id": filter_1, "lv.branch_id": filter_2}) loan_filter = f"{loan_filter} AND {extra_filters}" results = filter_arrears_officer_branch(organisation_id, loan_filter, as_at) elif report_filter == 'product_client_type': loan_filter = convert_json_to_sql_where({"lv.loan_product_id": filter_1, "lv.customer_type_id": filter_2, "lv.branch_id": [organisation_branch_id]}) loan_filter = f"{loan_filter} AND {extra_filters}" results = filter_arrears_product_client_type(organisation_id, loan_filter, as_at) elif report_filter == 'product_officer': loan_filter = convert_json_to_sql_where({"lv.loan_product_id": filter_1, "lv.loan_officer_id": filter_2, "lv.branch_id": [organisation_branch_id]}) loan_filter = f"{loan_filter} AND {extra_filters}" results = filter_arrears_product_officer(organisation_id, loan_filter, as_at) elif report_filter == 'product_gender': loan_filter = convert_json_to_sql_where({"lv.loan_product_id": filter_1, "lv.gender": filter_2, "lv.branch_id": [organisation_branch_id]}) loan_filter = f"{loan_filter} AND {extra_filters}" results = filter_arrears_product_gender(organisation_id, loan_filter, as_at) elif report_filter == 'gender_branch': loan_filter = convert_json_to_sql_where({"lv.branch_id": filter_1, "lv.gender": filter_2}) loan_filter = f"{loan_filter} AND {extra_filters}" results = filter_arrears_gender_branch(organisation_id, loan_filter, as_at) elif report_filter == 'product_sector': loan_filter = convert_json_to_sql_where({"lv.loan_product_id": filter_1, "lv.loan_sector_id": filter_2, "lv.branch_id": [organisation_branch_id]}) loan_filter = f"{loan_filter} AND {extra_filters}" results = filter_arrears_product_sector(organisation_id, loan_filter, as_at) elif report_filter == 'group_branch': loan_filter = convert_json_to_sql_where({"lv.branch_id": filter_1, "lv.group_id": filter_2}) loan_filter = f"{loan_filter} AND {extra_filters}" results = filter_arrears_group_branch(organisation_id, loan_filter, as_at) return Response({"results": results, "count":len(results), "org_portfolio": princ_bal}) def filter_arrears_product_branch(organisation_id, loan_filter, as_at): # branch vs profucts with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT branch_id, loan_product_id, ARRAY_AGG(json_build_object( 'id', id, 'name', name, 'telephone', telephone, 'member_number', member_number, 'old_member_number', old_member_number, 'loan_sector_name', loan_sector_name, 'loan_amount', loan_amount, 'disbursed_loan_amount', disbursed_loan_amount, 'princ_expected', princ_expected, 'int_expected', int_expected, 'loan_date', loan_date, 'int_waivered', int_waivered, 'princ_paid', princ_paid, 'int_paid', int_paid, 'int_paid_with_waiver', int_paid + int_waivered, 'total_paid', total_paid, 'penalty_paid', penalty_paid, 'branch_name', branch_name, 'gender', gender, 'princ_bal', princ_expected - princ_paid, 'int_bal', int_expected - (int_paid + int_waivered), 'penalty_bal', total_penalty - penalty_paid, 'total_bal', ( (int_expected + princ_expected) - total_paid ), 'total_bal_with_penalty', ( (int_expected + princ_expected + total_penalty) - (total_paid + penalty_paid ) ), 'int_rate', int_rate, 'app_grace_period', app_grace_period, 'grace_period_type', grace_period_type, 'loan_period', loan_period, 'period_type', period_type, 'loan_officer_full_name', loan_officer_full_name, 'arrears_days', arrears_days, 'princ_due', princ_due, 'int_due', int_due, 'penalty_due', total_penalty - penalty_paid, 'total_due', princ_due + int_due + (total_penalty - penalty_paid), 'loan_start_date', loan_start_date )) AS loans FROM public.get_loan_arrears_data({organisation_id}, '{as_at}', '{loan_filter}') WHERE arrears_days < 1 AND (FLOOR(princ_expected - princ_paid) > 0 OR FLOOR(int_expected - (int_paid + int_waivered)) > 0 ) GROUP BY branch_id, loan_product_id ) SELECT json_build_object( 'filter_1', ad.branch_id, 'filter_1_name', b.name, 'filter_2', ad.loan_product_id, 'filter_2_name', lp.product_name, 'loans', ad.loans ) AS branch_data FROM aggregated_data ad JOIN organisation_branch b ON ad.branch_id = b.id JOIN loan_products lp ON ad.loan_product_id = lp.id ORDER BY ad.branch_id; """ cursor.execute(query) # Fetch all rows response = [] rows = cursor.fetchall() results = [row[0] for row in rows] branch_map = {} for row in results: branch_id = row['filter_1'] branch_name = row['filter_1_name'] loan_product_id = row['filter_2'] product_name = row['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': loan_product_id, 'filter_2_name': product_name, 'loans': row['loans'] } branch_entry['filter_2'].append(loan_product_entry) return response def filter_arrears_officer_branch(organisation_id, loan_filter, as_at): with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT branch_id, loan_officer_id, SUM(disbursed_loan_amount) - SUM(princ_paid) AS total, ARRAY_AGG(json_build_object( 'id', id, 'name', name, 'telephone', telephone, 'member_number', member_number, 'old_member_number', old_member_number, 'loan_sector_name', loan_sector_name, 'loan_amount', loan_amount, 'disbursed_loan_amount', disbursed_loan_amount, 'princ_expected', princ_expected, 'int_expected', int_expected, 'loan_date', loan_date, 'int_waivered', int_waivered, 'princ_paid', princ_paid, 'int_paid', int_paid, 'int_paid_with_waiver', int_paid + int_waivered, 'total_paid', total_paid, 'penalty_paid', penalty_paid, 'branch_name', branch_name, 'gender', gender, 'princ_bal', princ_expected - princ_paid, 'int_bal', int_expected - (int_paid + int_waivered), 'penalty_bal', total_penalty - penalty_paid, 'total_bal', ( (int_expected + princ_expected) - total_paid ), 'total_bal_with_penalty', ( (int_expected + princ_expected + total_penalty) - (total_paid + penalty_paid ) ), 'int_rate', int_rate, 'app_grace_period', app_grace_period, 'grace_period_type', grace_period_type, 'loan_period', loan_period, 'period_type', period_type, 'loan_officer_full_name', loan_officer_full_name, 'arrears_days', arrears_days, 'princ_due', princ_due, 'int_due', int_due, 'penalty_due', total_penalty - penalty_paid, 'total_due', princ_due + int_due + (total_penalty - penalty_paid), 'loan_start_date', loan_start_date )) AS loans FROM public.get_loan_arrears_data({organisation_id}, '{as_at}', '{loan_filter}') WHERE arrears_days < 1 AND (FLOOR(princ_expected - princ_paid) > 0 OR FLOOR(int_expected - (int_paid + int_waivered)) > 0 ) GROUP BY branch_id, loan_officer_id ) SELECT json_build_object( 'filter_1', ad.branch_id, 'filter_1_name', b.name, 'filter_2', ad.loan_officer_id, 'filter_2_name', sf.name, 'total', ad.total, 'loans', ad.loans ) AS branch_data FROM aggregated_data ad JOIN organisation_branch b ON ad.branch_id = b.id JOIN staff sf ON ad.loan_officer_id = sf.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'] loan_officer_id = row[0]['filter_2'] staff_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 staff entry to filter_2 loan_product_entry = { 'filter_2': loan_officer_id, 'filter_2_name': staff_name, 'total': row[0]['total'], 'loans': row[0]['loans'] } branch_entry['filter_2'].append(loan_product_entry) return response def filter_arrears_product_client_type(organisation_id, loan_filter, as_at): with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT customer_type_id, loan_product_id, SUM(disbursed_loan_amount) - SUM(princ_paid) AS total, ARRAY_AGG(json_build_object( 'id', id, 'name', name, 'telephone', telephone, 'member_number', member_number, 'old_member_number', old_member_number, 'loan_sector_name', loan_sector_name, 'loan_amount', loan_amount, 'disbursed_loan_amount', disbursed_loan_amount, 'princ_expected', princ_expected, 'int_expected', int_expected, 'loan_date', loan_date, 'int_waivered', int_waivered, 'princ_paid', princ_paid, 'int_paid', int_paid, 'int_paid_with_waiver', int_paid + int_waivered, 'total_paid', total_paid, 'penalty_paid', penalty_paid, 'branch_name', branch_name, 'gender', gender, 'princ_bal', princ_expected - princ_paid, 'int_bal', int_expected - (int_paid + int_waivered), 'penalty_bal', total_penalty - penalty_paid, 'total_bal', ( (int_expected + princ_expected) - total_paid ), 'total_bal_with_penalty', ( (int_expected + princ_expected + total_penalty) - (total_paid + penalty_paid ) ), 'int_rate', int_rate, 'app_grace_period', app_grace_period, 'grace_period_type', grace_period_type, 'loan_period', loan_period, 'period_type', period_type, 'loan_officer_full_name', loan_officer_full_name, 'arrears_days', arrears_days, 'princ_due', princ_due, 'int_due', int_due, 'penalty_due', total_penalty - penalty_paid, 'total_due', princ_due + int_due + (total_penalty - penalty_paid), 'loan_start_date', loan_start_date )) AS loans FROM public.get_loan_arrears_data({organisation_id}, '{as_at}', '{loan_filter}') WHERE arrears_days < 1 AND (FLOOR(princ_expected - princ_paid) > 0 OR FLOOR(int_expected - (int_paid + int_waivered)) > 0 ) GROUP BY customer_type_id, loan_product_id ) SELECT json_build_object( 'filter_1', ad.customer_type_id, 'filter_1_name', b.customer_type, 'filter_2', ad.loan_product_id, 'filter_2_name', lp.product_name, 'total', ad.total, 'loans', ad.loans ) AS branch_data FROM aggregated_data ad JOIN customer_type b ON ad.customer_type_id = b.id JOIN loan_products lp ON ad.loan_product_id = lp.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_id = row[0]['filter_1'] customer_type = 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_id not in branch_map: # Create a new entry for the branch branch_entry = { 'filter_1': customer_type_id, 'filter_1_name': customer_type, 'filter_2': [] } branch_map[customer_type_id] = branch_entry response.append(branch_entry) # Get the current branch entry branch_entry = branch_map[customer_type_id] # Add the staff entry to filter_2 loan_product_entry = { 'filter_2': loan_product_id, 'filter_2_name': product_name, 'total': row[0]['total'], 'loans': row[0]['loans'] } branch_entry['filter_2'].append(loan_product_entry) return response def filter_arrears_product_officer(organisation_id, loan_filter, as_at): with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT loan_officer_id, loan_product_id, SUM(disbursed_loan_amount) - SUM(princ_paid) AS total, ARRAY_AGG(json_build_object( 'id', id, 'name', name, 'telephone', telephone, 'member_number', member_number, 'old_member_number', old_member_number, 'loan_sector_name', loan_sector_name, 'loan_amount', loan_amount, 'disbursed_loan_amount', disbursed_loan_amount, 'princ_expected', princ_expected, 'int_expected', int_expected, 'loan_date', loan_date, 'int_waivered', int_waivered, 'princ_paid', princ_paid, 'int_paid', int_paid, 'int_paid_with_waiver', int_paid + int_waivered, 'total_paid', total_paid, 'penalty_paid', penalty_paid, 'branch_name', branch_name, 'gender', gender, 'princ_bal', princ_expected - princ_paid, 'int_bal', int_expected - (int_paid + int_waivered), 'penalty_bal', total_penalty - penalty_paid, 'total_bal', ( (int_expected + princ_expected) - total_paid ), 'total_bal_with_penalty', ( (int_expected + princ_expected + total_penalty) - (total_paid + penalty_paid ) ), 'int_rate', int_rate, 'app_grace_period', app_grace_period, 'grace_period_type', grace_period_type, 'loan_period', loan_period, 'period_type', period_type, 'loan_officer_full_name', loan_officer_full_name, 'arrears_days', arrears_days, 'princ_due', princ_due, 'int_due', int_due, 'penalty_due', total_penalty - penalty_paid, 'total_due', princ_due + int_due + (total_penalty - penalty_paid), 'loan_start_date', loan_start_date )) AS loans FROM public.get_loan_arrears_data({organisation_id}, '{as_at}', '{loan_filter}') WHERE arrears_days < 1 AND (FLOOR(princ_expected - princ_paid) > 0 OR FLOOR(int_expected - (int_paid + int_waivered)) > 0 ) GROUP BY loan_officer_id, loan_product_id ) SELECT json_build_object( 'filter_1', ad.loan_officer_id, 'filter_1_name', b.name, 'filter_2', ad.loan_product_id, 'filter_2_name', lp.product_name, 'total', ad.total, 'loans', ad.loans ) AS branch_data FROM aggregated_data ad JOIN staff b ON ad.loan_officer_id = b.id JOIN loan_products lp ON ad.loan_product_id = lp.id ORDER BY ad.loan_officer_id; """ cursor.execute(query) # Fetch all rows rows = cursor.fetchall() response = [] branch_map = {} for row in rows: loan_officer_id = row[0]['filter_1'] staff_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 loan_officer_id not in branch_map: # Create a new entry for the branch branch_entry = { 'filter_1': loan_officer_id, 'filter_1_name': staff_name, 'filter_2': [] } branch_map[loan_officer_id] = branch_entry response.append(branch_entry) # Get the current branch entry branch_entry = branch_map[loan_officer_id] # Add the loan product entry to filter_2 loan_product_entry = { 'filter_2': loan_product_id, 'filter_2_name': product_name, 'total': row[0]['total'], 'loans': row[0]['loans'] } branch_entry['filter_2'].append(loan_product_entry) return response def filter_arrears_officer_client_type(organisation_id, loan_filter, as_at): with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT loan_officer_id, customer_type_id, SUM(disbursed_loan_amount) - SUM(princ_paid) AS total, ARRAY_AGG(json_build_object( 'id', id, 'name', name, 'telephone', telephone, 'member_number', member_number, 'old_member_number', old_member_number, 'loan_sector_name', loan_sector_name, 'loan_amount', loan_amount, 'disbursed_loan_amount', disbursed_loan_amount, 'princ_expected', princ_expected, 'int_expected', int_expected, 'loan_date', loan_date, 'int_waivered', int_waivered, 'princ_paid', princ_paid, 'int_paid', int_paid, 'int_paid_with_waiver', int_paid + int_waivered, 'total_paid', total_paid, 'penalty_paid', penalty_paid, 'branch_name', branch_name, 'gender', gender, 'princ_bal', princ_expected - princ_paid, 'int_bal', int_expected - (int_paid + int_waivered), 'penalty_bal', total_penalty - penalty_paid, 'total_bal', ( (int_expected + princ_expected) - total_paid ), 'total_bal_with_penalty', ( (int_expected + princ_expected + total_penalty) - (total_paid + penalty_paid ) ), 'int_rate', int_rate, 'app_grace_period', app_grace_period, 'grace_period_type', grace_period_type, 'loan_period', loan_period, 'period_type', period_type, 'loan_officer_full_name', loan_officer_full_name, 'arrears_days', arrears_days, 'princ_due', princ_due, 'int_due', int_due, 'penalty_due', total_penalty - penalty_paid, 'total_due', princ_due + int_due + (total_penalty - penalty_paid), 'loan_start_date', loan_start_date )) AS loans FROM public.get_loan_arrears_data({organisation_id}, '{as_at}', '{loan_filter}') WHERE arrears_days < 1 AND (FLOOR(princ_expected - princ_paid) > 0 OR FLOOR(int_expected - (int_paid + int_waivered)) > 0 ) GROUP BY loan_officer_id, customer_type_id ) SELECT json_build_object( 'filter_1', ad.loan_officer_id, 'filter_1_name', b.name, 'filter_2', ad.customer_type_id, 'filter_2_name', lp.customer_type, 'total', ad.total, 'loans', ad.loans ) AS branch_data FROM aggregated_data ad JOIN staff b ON ad.loan_officer_id = b.id JOIN customer_type lp ON ad.customer_type_id = lp.id ORDER BY ad.loan_officer_id; """ cursor.execute(query) # Fetch all rows rows = cursor.fetchall() response = [] branch_map = {} for row in rows: loan_officer_id = row[0]['filter_1'] staff_name = row[0]['filter_1_name'] customer_type_id = row[0]['filter_2'] customer_type = row[0]['filter_2_name'] # Check if the branch_id is already in the response if loan_officer_id not in branch_map: # Create a new entry for the branch branch_entry = { 'filter_1': loan_officer_id, 'filter_1_name': staff_name, 'filter_2': [] } branch_map[loan_officer_id] = branch_entry response.append(branch_entry) # Get the current branch entry branch_entry = branch_map[loan_officer_id] # Add the loan product entry to filter_2 loan_product_entry = { 'filter_2': customer_type_id, 'filter_2_name': customer_type, 'total': row[0]['total'], 'loans': row[0]['loans'] } branch_entry['filter_2'].append(loan_product_entry) return response def filter_arrears_group_branch(organisation_id, loan_filter, as_at): with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT branch_id, group_id, SUM(disbursed_loan_amount) - SUM(princ_paid) AS total, ARRAY_AGG(json_build_object( 'id', id, 'name', name, 'telephone', telephone, 'member_number', member_number, 'old_member_number', old_member_number, 'loan_sector_name', loan_sector_name, 'loan_amount', loan_amount, 'disbursed_loan_amount', disbursed_loan_amount, 'princ_expected', princ_expected, 'int_expected', int_expected, 'loan_date', loan_date, 'int_waivered', int_waivered, 'princ_paid', princ_paid, 'int_paid', int_paid, 'int_paid_with_waiver', int_paid + int_waivered, 'total_paid', total_paid, 'penalty_paid', penalty_paid, 'branch_name', branch_name, 'gender', gender, 'princ_bal', princ_expected - princ_paid, 'int_bal', int_expected - (int_paid + int_waivered), 'penalty_bal', total_penalty - penalty_paid, 'total_bal', ( (int_expected + princ_expected) - total_paid ), 'total_bal_with_penalty', ( (int_expected + princ_expected + total_penalty) - (total_paid + penalty_paid ) ), 'int_rate', int_rate, 'app_grace_period', app_grace_period, 'grace_period_type', grace_period_type, 'loan_period', loan_period, 'period_type', period_type, 'loan_officer_full_name', loan_officer_full_name, 'arrears_days', arrears_days, 'princ_due', princ_due, 'int_due', int_due, 'penalty_due', total_penalty - penalty_paid, 'total_due', princ_due + int_due + (total_penalty - penalty_paid), 'loan_start_date', loan_start_date )) AS loans FROM public.get_loan_arrears_data({organisation_id}, '{as_at}', '{loan_filter}') WHERE arrears_days < 1 AND (FLOOR(princ_expected - princ_paid) > 0 OR FLOOR(int_expected - (int_paid + int_waivered)) > 0 ) GROUP BY branch_id, group_id ) SELECT json_build_object( 'filter_1', ad.branch_id, 'filter_1_name', b.name, 'filter_2', ad.group_id, 'filter_2_name', lp.name, 'total', ad.total, 'loans', ad.loans ) AS branch_data FROM aggregated_data ad JOIN organisation_branch b ON ad.branch_id = b.id JOIN customer lp ON ad.group_id = lp.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'] group_id = row[0]['filter_2'] customer_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': group_id, 'filter_2_name': customer_name, 'total': row[0]['total'], 'loans': row[0]['loans'] } branch_entry['filter_2'].append(loan_product_entry) return response def filter_arrears_product_sector(organisation_id, loan_filter, as_at): with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT loan_sector_id, loan_product_id, SUM(disbursed_loan_amount) - SUM(princ_paid) AS total, ARRAY_AGG(json_build_object( 'id', id, 'name', name, 'telephone', telephone, 'member_number', member_number, 'old_member_number', old_member_number, 'loan_sector_name', loan_sector_name, 'loan_amount', loan_amount, 'disbursed_loan_amount', disbursed_loan_amount, 'princ_expected', princ_expected, 'int_expected', int_expected, 'loan_date', loan_date, 'int_waivered', int_waivered, 'princ_paid', princ_paid, 'int_paid', int_paid, 'int_paid_with_waiver', int_paid + int_waivered, 'total_paid', total_paid, 'penalty_paid', penalty_paid, 'branch_name', branch_name, 'gender', gender, 'princ_bal', princ_expected - princ_paid, 'int_bal', int_expected - (int_paid + int_waivered), 'penalty_bal', total_penalty - penalty_paid, 'total_bal', ( (int_expected + princ_expected) - total_paid ), 'total_bal_with_penalty', ( (int_expected + princ_expected + total_penalty) - (total_paid + penalty_paid ) ), 'int_rate', int_rate, 'app_grace_period', app_grace_period, 'grace_period_type', grace_period_type, 'loan_period', loan_period, 'period_type', period_type, 'loan_officer_full_name', loan_officer_full_name, 'arrears_days', arrears_days, 'princ_due', princ_due, 'int_due', int_due, 'penalty_due', total_penalty - penalty_paid, 'total_due', princ_due + int_due + (total_penalty - penalty_paid), 'loan_start_date', loan_start_date )) AS loans FROM public.get_loan_arrears_data({organisation_id}, '{as_at}', '{loan_filter}') WHERE arrears_days < 1 AND (FLOOR(princ_expected - princ_paid) > 0 OR FLOOR(int_expected - (int_paid + int_waivered)) > 0 ) GROUP BY loan_sector_id, loan_product_id ) SELECT json_build_object( 'filter_1', ad.loan_sector_id, 'filter_1_name', b.name, 'filter_2', ad.loan_product_id, 'filter_2_name', lp.product_name, 'total', ad.total, 'loans', ad.loans ) AS branch_data FROM aggregated_data ad JOIN loan_sectors b ON ad.loan_sector_id = b.id JOIN loan_products lp ON ad.loan_product_id = lp.id ORDER BY ad.loan_sector_id; """ cursor.execute(query) # Fetch all rows rows = cursor.fetchall() response = [] branch_map = {} for row in rows: loan_sector_id = row[0]['filter_1'] sectore_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 loan_sector_id not in branch_map: # Create a new entry for the branch branch_entry = { 'filter_1': loan_sector_id, 'filter_1_name': sectore_name, 'filter_2': [] } branch_map[loan_sector_id] = branch_entry response.append(branch_entry) # Get the current branch entry branch_entry = branch_map[loan_sector_id] # Add the loan product entry to filter_2 loan_product_entry = { 'filter_2': loan_product_id, 'filter_2_name': product_name, 'total': row[0]['total'], 'loans': row[0]['loans'] } branch_entry['filter_2'].append(loan_product_entry) return response def filter_arrears_product_gender(organisation_id, loan_filter, as_at): with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT loan_product_id, gender, SUM(disbursed_loan_amount) - SUM(princ_paid) AS total, ARRAY_AGG(json_build_object( 'id', id, 'name', name, 'telephone', telephone, 'member_number', member_number, 'old_member_number', old_member_number, 'loan_sector_name', loan_sector_name, 'loan_amount', loan_amount, 'disbursed_loan_amount', disbursed_loan_amount, 'princ_expected', princ_expected, 'int_expected', int_expected, 'loan_date', loan_date, 'int_waivered', int_waivered, 'princ_paid', princ_paid, 'int_paid', int_paid, 'int_paid_with_waiver', int_paid + int_waivered, 'total_paid', total_paid, 'penalty_paid', penalty_paid, 'branch_name', branch_name, 'gender', gender, 'princ_bal', princ_expected - princ_paid, 'int_bal', int_expected - (int_paid + int_waivered), 'penalty_bal', total_penalty - penalty_paid, 'total_bal', ( (int_expected + princ_expected) - total_paid ), 'total_bal_with_penalty', ( (int_expected + princ_expected + total_penalty) - (total_paid + penalty_paid ) ), 'int_rate', int_rate, 'app_grace_period', app_grace_period, 'grace_period_type', grace_period_type, 'loan_period', loan_period, 'period_type', period_type, 'loan_officer_full_name', loan_officer_full_name, 'arrears_days', arrears_days, 'princ_due', princ_due, 'int_due', int_due, 'penalty_due', total_penalty - penalty_paid, 'total_due', princ_due + int_due + (total_penalty - penalty_paid), 'loan_start_date', loan_start_date )) AS loans FROM public.get_loan_arrears_data({organisation_id}, '{as_at}', '{loan_filter}') WHERE arrears_days < 1 AND (FLOOR(princ_expected - princ_paid) > 0 OR FLOOR(int_expected - (int_paid + int_waivered)) > 0 ) GROUP BY loan_product_id, gender ) SELECT json_build_object( 'filter_1', ad.loan_product_id, 'filter_1_name', b.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, 'total', ad.total, 'loans', ad.loans ) AS branch_data FROM aggregated_data ad JOIN loan_products b ON ad.loan_product_id = b.id ORDER BY ad.loan_product_id; """ cursor.execute(query) # Fetch all rows rows = cursor.fetchall() response = [] branch_map = {} for row in rows: loan_product_id = row[0]['filter_1'] product_name = row[0]['filter_1_name'] gender_id = row[0]['filter_2'] gender_name = row[0]['filter_2_name'] # Check if the branch_id is already in the response if loan_product_id not in branch_map: # Create a new entry for the branch branch_entry = { 'filter_1': loan_product_id, 'filter_1_name': product_name, 'filter_2': [] } branch_map[loan_product_id] = branch_entry response.append(branch_entry) # Get the current branch entry branch_entry = branch_map[loan_product_id] # Add the loan product entry to filter_2 loan_product_entry = { 'filter_2': gender_id, 'filter_2_name': gender_name, 'total': row[0]['total'], 'loans': row[0]['loans'] } branch_entry['filter_2'].append(loan_product_entry) return response def filter_arrears_gender_branch(organisation_id, loan_filter, as_at): with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT branch_id, gender, SUM(disbursed_loan_amount) - SUM(princ_paid) AS total, ARRAY_AGG(json_build_object( 'id', id, 'name', name, 'telephone', telephone, 'member_number', member_number, 'old_member_number', old_member_number, 'loan_sector_name', loan_sector_name, 'loan_amount', loan_amount, 'disbursed_loan_amount', disbursed_loan_amount, 'princ_expected', princ_expected, 'int_expected', int_expected, 'loan_date', loan_date, 'int_waivered', int_waivered, 'princ_paid', princ_paid, 'int_paid', int_paid, 'int_paid_with_waiver', int_paid + int_waivered, 'total_paid', total_paid, 'penalty_paid', penalty_paid, 'branch_name', branch_name, 'gender', gender, 'princ_bal', princ_expected - princ_paid, 'int_bal', int_expected - (int_paid + int_waivered), 'penalty_bal', total_penalty - penalty_paid, 'total_bal', ( (int_expected + princ_expected) - total_paid ), 'total_bal_with_penalty', ( (int_expected + princ_expected + total_penalty) - (total_paid + penalty_paid ) ), 'int_rate', int_rate, 'app_grace_period', app_grace_period, 'grace_period_type', grace_period_type, 'loan_period', loan_period, 'period_type', period_type, 'loan_officer_full_name', loan_officer_full_name, 'arrears_days', arrears_days, 'princ_due', princ_due, 'int_due', int_due, 'penalty_due', total_penalty - penalty_paid, 'total_due', princ_due + int_due + (total_penalty - penalty_paid), 'loan_start_date', loan_start_date )) AS loans FROM public.get_loan_arrears_data({organisation_id}, '{as_at}', '{loan_filter}') WHERE arrears_days < 1 AND (FLOOR(princ_expected - princ_paid) > 0 OR FLOOR(int_expected - (int_paid + int_waivered)) > 0 ) 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, 'total', ad.total, 'loans', ad.loans ) 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_id = 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_id, 'filter_2_name': gender_name, 'total': row[0]['total'], 'loans': row[0]['loans'] } branch_entry['filter_2'].append(loan_product_entry) return response def filter_arrears_officer_gender(organisation_id, loan_filter, as_at): with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT loan_officer_id, gender, SUM(disbursed_loan_amount) - SUM(princ_paid) AS total, ARRAY_AGG(json_build_object( 'id', id, 'name', name, 'telephone', telephone, 'member_number', member_number, 'old_member_number', old_member_number, 'loan_sector_name', loan_sector_name, 'loan_amount', loan_amount, 'disbursed_loan_amount', disbursed_loan_amount, 'princ_expected', princ_expected, 'int_expected', int_expected, 'loan_date', loan_date, 'int_waivered', int_waivered, 'princ_paid', princ_paid, 'int_paid', int_paid, 'int_paid_with_waiver', int_paid + int_waivered, 'total_paid', total_paid, 'penalty_paid', penalty_paid, 'branch_name', branch_name, 'gender', gender, 'princ_bal', princ_expected - princ_paid, 'int_bal', int_expected - (int_paid + int_waivered), 'penalty_bal', total_penalty - penalty_paid, 'total_bal', ( (int_expected + princ_expected) - total_paid ), 'total_bal_with_penalty', ( (int_expected + princ_expected + total_penalty) - (total_paid + penalty_paid ) ), 'int_rate', int_rate, 'app_grace_period', app_grace_period, 'grace_period_type', grace_period_type, 'loan_period', loan_period, 'period_type', period_type, 'loan_officer_full_name', loan_officer_full_name, 'arrears_days', arrears_days, 'princ_due', princ_due, 'int_due', int_due, 'penalty_due', total_penalty - penalty_paid, 'total_due', princ_due + int_due + (total_penalty - penalty_paid), 'loan_start_date', loan_start_date )) AS loans FROM public.get_loan_arrears_data({organisation_id}, '{as_at}', '{loan_filter}') WHERE arrears_days < 1 AND (FLOOR(princ_expected - princ_paid) > 0 OR FLOOR(int_expected - (int_paid + int_waivered)) > 0 ) GROUP BY loan_officer_id, gender ) SELECT json_build_object( 'filter_1', ad.loan_officer_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, 'total', ad.total, 'loans', ad.loans ) AS branch_data FROM aggregated_data ad JOIN staff b ON ad.loan_officer_id = b.id ORDER BY ad.loan_officer_id; """ cursor.execute(query) # Fetch all rows rows = cursor.fetchall() response = [] branch_map = {} for row in rows: loan_officer_id = row[0]['filter_1'] staff_name = row[0]['filter_1_name'] gender_id = row[0]['filter_2'] gender_name = row[0]['filter_2_name'] # Check if the branch_id is already in the response if loan_officer_id not in branch_map: # Create a new entry for the branch branch_entry = { 'filter_1': loan_officer_id, 'filter_1_name': staff_name, 'filter_2': [] } branch_map[loan_officer_id] = branch_entry response.append(branch_entry) # Get the current branch entry branch_entry = branch_map[loan_officer_id] # Add the loan product entry to filter_2 loan_product_entry = { 'filter_2': gender_id, 'filter_2_name': gender_name, 'total': row[0]['total'], 'loans': row[0]['loans'] } branch_entry['filter_2'].append(loan_product_entry) return response