from django.db import connection from questbanker_api.utils import get_current_user from rest_framework.response import Response from .general_helper import convert_json_to_sql_where from datetime import datetime def filter_lines_of_credit_reports(report_filters): results = [] filter_1 = report_filters['filter_1'] 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'] # only safe filters go here base_filter = convert_json_to_sql_where({"lv.branch_id": [organisation_branch_id]}) extra_filters = f"date(lad.loan_disbursement_date at time zone ''Africa/Nairobi'') <= ''{as_at}''" loan_filter = f"{base_filter} AND {extra_filters}" if report_filter == 'gender_agriculture': results = filter_tracking_gender_agriculture(organisation_id, loan_filter, as_at, filter_1) elif report_filter == 'value_chain_agriculture': results = filter_tracking_value_chain_agriculture(organisation_id, loan_filter, as_at, filter_1) # elif report_filter == 'value_chain_node_agriculture': # results = filter_tracking_value_chain_node_agriculture(organisation_id, loan_filter, as_at, filter_1) return Response({"results": results, "count": len(results)}) def filter_tracking_gender_agriculture(organisation_id, loan_filter, as_at, filter_value=None): with connection.cursor() as cursor: query = f""" WITH aggregated_data AS ( SELECT gender, SUM(disbursed_loan_amount) - SUM(princ_paid) AS total, ARRAY_AGG(json_build_object( 'id', gf.id, 'name', gf.name, 'telephone', gf.telephone, 'member_number', gf.member_number, 'old_member_number', gf.old_member_number, 'loan_sector_name', gf.loan_sector_name, 'loan_amount', gf.loan_amount, 'disbursed_loan_amount', gf.disbursed_loan_amount, 'princ_expected', gf.princ_expected, 'int_expected', gf.int_expected, 'loan_date', gf.loan_date, 'loan_start_date', gf.loan_start_date, 'int_waivered', gf.int_waivered, 'princ_paid', gf.princ_paid, 'int_paid', gf.int_paid, 'int_paid_with_waiver', gf.int_paid + gf.int_waivered, 'total_paid', gf.total_paid, 'penalty_paid', gf.penalty_paid, 'total_paid_with_penalty', gf.total_paid + gf.penalty_paid, 'branch_name', gf.branch_name, 'gender', gf.gender, 'princ_bal', gf.princ_expected - gf.princ_paid, 'int_bal', gf.int_expected - (gf.int_paid + gf.int_waivered), 'penalty_bal', gf.total_penalty - gf.penalty_paid, 'total_bal', ((gf.int_expected + gf.princ_expected) - gf.total_paid), 'total_bal_with_penalty', ((gf.int_expected + gf.princ_expected + gf.total_penalty) - (gf.total_paid + gf.penalty_paid)), 'int_rate', gf.int_rate, 'app_grace_period', gf.app_grace_period, 'grace_period_type', gf.grace_period_type, 'loan_period', gf.loan_period, 'period_type', gf.period_type, 'loan_officer_full_name', gf.loan_officer_full_name, 'dob', gf.dob, 'physical_address', gf.physical_address, 'villagename', gf.villagename, 'item_name', gf.item_name, 'class_name', gf.class_name, 'value_chain', vc.name, 'value_chain_node', vcn.value_chain_node )) AS loans FROM public.get_green_finance_loan_data({organisation_id}, '{as_at}', '{loan_filter}') gf LEFT JOIN green_finance_value_chains vc ON gf.value_chain::text = vc.name LEFT JOIN green_finance_value_chain_nodes vcn ON gf.value_chain_node::text = vcn.value_chain_node WHERE gf.loan_sector_name = 'Agriculture' { f"AND gf.gender = ANY('{{{','.join(filter_value)}}}'::text[])" if filter_value else "" } GROUP BY gender ) SELECT json_build_object( 'filter_1', ad.gender, 'total', ad.total, 'loans', ad.loans ) AS agriculture_data FROM aggregated_data ad ORDER BY ad.gender; """ cursor.execute(query) return [row[0] for row in cursor.fetchall()] def filter_tracking_value_chain_agriculture(organisation_id, loan_filter, as_at, filter_value): """ Fetches agriculture loans grouped by value chain. filter_value: list of string IDs from frontend (will be converted to names for filtering) """ with connection.cursor() as cursor: # Convert string IDs to integers filter_ids = [int(v) for v in filter_value] if filter_value else [] # Get the names corresponding to the IDs value_chain_names = [] if filter_ids: cursor.execute( "SELECT name FROM green_finance_value_chains WHERE id = ANY(%s)", [filter_ids] ) value_chain_names = [row[0] for row in cursor.fetchall()] query = f""" WITH aggregated_data AS ( SELECT vc.name AS value_chain, SUM(disbursed_loan_amount) - SUM(princ_paid) AS total, ARRAY_AGG(json_build_object( 'id', gf.id, 'name', gf.name, 'telephone', gf.telephone, 'member_number', gf.member_number, 'old_member_number', gf.old_member_number, 'loan_sector_name', gf.loan_sector_name, 'loan_amount', gf.loan_amount, 'disbursed_loan_amount', gf.disbursed_loan_amount, 'princ_expected', gf.princ_expected, 'int_expected', gf.int_expected, 'loan_date', gf.loan_date, 'loan_start_date', gf.loan_start_date, 'int_waivered', gf.int_waivered, 'princ_paid', gf.princ_paid, 'int_paid', gf.int_paid, 'int_paid_with_waiver', gf.int_paid + gf.int_waivered, 'total_paid', gf.total_paid, 'penalty_paid', gf.penalty_paid, 'total_paid_with_penalty', gf.total_paid + gf.penalty_paid, 'branch_name', gf.branch_name, 'gender', gf.gender, 'princ_bal', gf.princ_expected - gf.princ_paid, 'int_bal', gf.int_expected - (gf.int_paid + gf.int_waivered), 'penalty_bal', gf.total_penalty - gf.penalty_paid, 'total_bal', ((gf.int_expected + gf.princ_expected) - gf.total_paid), 'total_bal_with_penalty', ((gf.int_expected + gf.princ_expected + gf.total_penalty) - (gf.total_paid + gf.penalty_paid)), 'int_rate', gf.int_rate, 'app_grace_period', gf.app_grace_period, 'grace_period_type', gf.grace_period_type, 'loan_period', gf.loan_period, 'period_type', gf.period_type, 'loan_officer_full_name', gf.loan_officer_full_name, 'dob', gf.dob, 'physical_address', gf.physical_address, 'villagename', gf.villagename, 'item_name', gf.item_name, 'class_name', gf.class_name, 'value_chain', vc.name, 'value_chain_node', vcn.value_chain_node )) AS loans FROM public.get_green_finance_loan_data(%s, %s, %s) gf LEFT JOIN green_finance_value_chains vc ON gf.value_chain = vc.name LEFT JOIN green_finance_value_chain_nodes vcn ON gf.value_chain_node = vcn.value_chain_node WHERE gf.loan_sector_name = 'Agriculture' { "AND gf.value_chain = ANY(%s)" if value_chain_names else "" } GROUP BY vc.name ) SELECT json_build_object( 'filter_1', ad.value_chain, 'total', ad.total, 'loans', ad.loans ) AS agriculture_data FROM aggregated_data ad ORDER BY ad.value_chain; """ params = [organisation_id, as_at, loan_filter] if value_chain_names: params.append(value_chain_names) cursor.execute(query, params) return [row[0] for row in cursor.fetchall()] # def filter_tracking_value_chain_node_agriculture(organisation_id, loan_filter, as_at, value_chain_ids, node_filter=None): # """ # Fetch agriculture loans grouped by nodes within selected value chains. # Returns dict with 'filter_1' = value chains, 'filter_2' = nodes. # """ # with connection.cursor() as cursor: # value_chain_ids = [int(v) for v in value_chain_ids] if value_chain_ids else [] # node_filter = [str(n) for n in node_filter] if node_filter else [] # query = f""" # WITH aggregated_data AS ( # SELECT # vc.id AS value_chain_id, # vc.name AS value_chain_name, # vcn.value_chain_node, # SUM(disbursed_loan_amount) - SUM(princ_paid) AS total, # ARRAY_AGG(json_build_object( # 'id', gf.id, # 'name', gf.name, # 'telephone', gf.telephone, # 'member_number', gf.member_number, # 'loan_sector_name', gf.loan_sector_name, # 'loan_amount', gf.loan_amount, # 'disbursed_loan_amount', gf.disbursed_loan_amount, # 'princ_expected', gf.princ_expected, # 'int_expected', gf.int_expected, # 'loan_date', gf.loan_date, # 'loan_start_date', gf.loan_start_date, # 'int_waivered', gf.int_waivered, # 'princ_paid', gf.princ_paid, # 'int_paid', gf.int_paid, # 'total_paid', gf.total_paid, # 'branch_name', gf.branch_name, # 'gender', gf.gender, # 'princ_bal', gf.princ_expected - gf.princ_paid, # 'int_bal', gf.int_expected - (gf.int_paid + gf.int_waivered) # )) AS loans # FROM public.get_green_finance_loan_data(%s, %s, %s) gf # LEFT JOIN green_finance_value_chains vc # ON gf.value_chain::text = vc.name # LEFT JOIN green_finance_value_chain_nodes vcn # ON gf.value_chain_node::text = vcn.value_chain_node # WHERE gf.loan_sector_name = 'Agriculture' # { "AND vc.id = ANY(%s)" if value_chain_ids else "" } # { "AND vcn.value_chain_node = ANY(%s)" if node_filter else "" } # GROUP BY vc.id, vc.name, vcn.value_chain_node # ) # SELECT # value_chain_id, # value_chain_name, # value_chain_node, # total, # loans # FROM aggregated_data # ORDER BY value_chain_name, value_chain_node; # """ # params = [organisation_id, as_at, loan_filter] # if value_chain_ids: # params.append(value_chain_ids) # if node_filter: # params.append(node_filter) # cursor.execute(query, params) # rows = cursor.fetchall() # # Transform results into frontend-friendly filters # filter_1 = [] # filter_2 = [] # value_chain_map = {} # for row in rows: # vc_id, vc_name, node_name, total, loans = row # # Add value chain to filter_1 only once # if vc_id not in value_chain_map: # value_chain_map[vc_id] = True # filter_1.append({"id": vc_id, "name": vc_name}) # # Add node to filter_2 # filter_2.append({"id": node_name, "total": total, "loans": loans}) # return {"filter_1": filter_1, "filter_2": filter_2}