from django.db.models import Sum,Min,Max from ..serializers import * from ..models import * from ..helper import * from ledgers.models import * from ledgers.ledgers_helper import * from rest_framework.pagination import PageNumberPagination def par_loans_totals(loansfilter,extraFilters ={}): loan_data = [] loan_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0} loans = filter_loan_balances(loansfilter,extraFilters) if loans: for loan in loans: par = 0 par_rate = 0 #arrear rate outstanding_princ = loan["loan_princ"] - loan["princ_paid"] if loan["principal_due"] > 0: par = (outstanding_princ/extraFilters["general_bal"]["principal_balance"]) * 100 par_rate = (loan['principal_due']/outstanding_princ) * 100 arrear_days = (datetime.strptime(extraFilters["end"].split(' ')[0],"%Y-%m-%d") - datetime.strptime(loan["first_arrear_date"].strftime('%Y-%m-%d'),'%Y-%m-%d')).days - 1 if arrear_days > 0: loan_data.append({ "member_number":loan["member_number"], "customer_name":loan["name"], "telephone":loan["telephone"], "id":loan["id"], "loan_amount":loan["loan_amount"], "principal_balance":outstanding_princ, "principal_due":loan['principal_due'], "total_due":loan['principal_due'], "arrear_days":arrear_days, "par_rate":par_rate, "par":par }) loan_totals["principal_balance"] += outstanding_princ loan_totals["principal_due"] += loan['principal_due'] loan_totals["total_due"] += loan['principal_due'] loan_totals["loan_amount"] += loan["loan_amount"] return {"loan_data":loan_data,"loan_totals":loan_totals} def filter_par_reports(report_filters): loans = [] loans_count = 0 result = {} filter_1 = report_filters['filter_1'] filter_2 = report_filters['filter_2'] page_size = report_filters['page_size'] report_filter = report_filters['report_filter'] request = report_filters['request'] search = report_filters['search'] extraFilters = {"report_type":"par","search":search,"end":make_aware(datetime.strptime(report_filters['as_at'], '%Y-%m-%d %H:%M:%S'))} loan_filter = {"loan_arrear_date__lte":report_filters['as_at'],"is_deleted":False,"status":"disbursed","organisation_id":report_filters['organisation_id']} # Get total out standing principals for all the distursed loans general_bal = {"principal_balance":0} general_bal_filter = {"loan_disbursement_date__lte":report_filters['as_at'],"is_deleted":False,"status":"disbursed","organisation_id":report_filters['organisation_id']} pay_filter = {"loan_main_transaction__system_transaction__record_date__lte":report_filters['as_at'],"loan_application__is_deleted":False,"loan_application__status":"disbursed","loan_application__organisation_branch__branch_organisation__id":report_filters['organisation_id']} princ_paid = LoanPayments.objects.filter(payment_status='normal',**pay_filter).aggregate(princ_paid_sum=Sum('princ_paid'))["princ_paid_sum"] principal = LoanRepaymentview.objects.filter(**general_bal_filter).aggregate(schedule_principal_expected_sum=Sum('schedule_principal_expected'))["schedule_principal_expected_sum"] # Outsatanding principal for the entire loan if principal: general_bal["principal_balance"] = principal if princ_paid: general_bal["principal_balance"] = principal - princ_paid paginator = PageNumberPagination() paginator.page_size = page_size if report_filter == 'officer_client_type': loan_filter["loan_officer_id__in"] = filter_1 loan_filter["customer_type_id__in"] = filter_2 loans = [] loans = filter_loan_balances(loan_filter,extraFilters) loans_count = len(loans) dued_loans = paginator.paginate_queryset(loans, request) result = filter_par_officer_client_type(filter_1,filter_2,dued_loans,report_filters['as_at'],general_bal) elif report_filter == 'officer_gender': loan_filter["loan_officer_id__in"] = filter_1 loan_filter["gender__in"] = filter_2 loans = filter_loan_balances(loan_filter,extraFilters) loans_count = len(loans) dued_loans = paginator.paginate_queryset(loans, request) result = filter_par_officer_gender(filter_1,filter_2,dued_loans,report_filters['as_at'],general_bal) elif report_filter == 'officer_branch': loan_filter["loan_officer_id__in"] = filter_1 loan_filter["branch_id__in"] = filter_2 loans = filter_loan_balances(loan_filter,extraFilters) loans_count = len(loans) dued_loans = paginator.paginate_queryset(loans, request) result = filter_par_officer_branch(filter_1,filter_2,dued_loans,report_filters['as_at'],general_bal) elif report_filter == 'product_client_type': loan_filter["loan_product_id__in"] = filter_1 loan_filter["customer_type_id__in"] = filter_2 loans = filter_loan_balances(loan_filter,extraFilters) loans_count = len(loans) dued_loans = paginator.paginate_queryset(loans, request) result = filter_par_product_client_type(filter_1,filter_2,dued_loans,report_filters['as_at'],general_bal) elif report_filter == 'product_gender': loan_filter["loan_product_id__in"] = filter_1 loan_filter["gender__in"] = filter_2 loans = filter_loan_balances(loan_filter,extraFilters) loans_count = len(loans) dued_loans = paginator.paginate_queryset(loans, request) result = filter_par_product_gender(filter_1,filter_2,dued_loans,report_filters['as_at'],general_bal) elif report_filter == 'product_branch': loan_filter["loan_product_id__in"] = filter_1 loan_filter["branch_id__in"] = filter_2 loans = filter_loan_balances(loan_filter,extraFilters) loans_count = len(loans) dued_loans = paginator.paginate_queryset(loans, request) result = filter_par_product_branch(filter_1,filter_2,dued_loans,report_filters['as_at'],general_bal) elif report_filter == 'product_officer': loan_filter["loan_product_id__in"] = filter_1 loan_filter["loan_officer_id__in"] = filter_2 loans = filter_loan_balances(loan_filter,extraFilters) loans_count = len(loans) dued_loans = paginator.paginate_queryset(loans, request) result = filter_par_product_officer(filter_1,filter_2,dued_loans,report_filters['as_at'],general_bal) elif report_filter == 'product_sector': loan_filter["loan_product_id__in"] = filter_1 loan_filter["loan_sector_id__in"] = filter_2 loans = filter_loan_balances(loan_filter,extraFilters) loans_count = len(loans) dued_loans = paginator.paginate_queryset(loans, request) result = filter_par_product_sector(filter_1,filter_2,dued_loans,report_filters['as_at'],general_bal) elif report_filter == 'gender_branch': loan_filter["branch_id__in"] = filter_1 loan_filter["gender__in"] = filter_2 loans = filter_loan_balances(loan_filter,extraFilters) loans_count = len(loans) dued_loans = paginator.paginate_queryset(loans, request) result = filter_par_gender_branch(filter_1,filter_2,dued_loans,report_filters['as_at'],general_bal) elif report_filter == 'group_branch': customer_ids = GroupMembership.objects.filter(group__id__in=filter_2).values_list('member__id', flat=True) loan_filter["branch_id__in"] = filter_1 loan_filter["customer_id__in"] = customer_ids loans = filter_loan_balances(loan_filter,extraFilters) loans_count = len(loans) dued_loans = paginator.paginate_queryset(loans, request) result = filter_par_group_branch(filter_1,filter_2,dued_loans,report_filters['as_at'],general_bal) return Response({"results":result,"count":loans_count}) def filter_par_officer_client_type(officer_ids,client_type_ids,dued_loans,as_at,general_bal): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0,"count":0} if dued_loans: for dued_loan in dued_loans: dued_loan_list[dued_loan['id']] = dued_loan dued_loan_ids.append(dued_loan['id']) loan_officers = Staff.objects.filter(id__in=officer_ids) if loan_officers: for loan_officer in loan_officers: sub_section = [] officers_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0} for client_type_id in client_type_ids: client_type = CustomerType.objects.get(id=client_type_id) loan_filter = {"loan_arrear_date__lte":as_at,"loan_officer_id":loan_officer.id,"customer_type_id":client_type_id,"id__in":dued_loan_ids,"status":"disbursed"} extra_filters = {"report_type":"par","end":as_at,"general_bal":general_bal} results = par_loans_totals(loan_filter,extra_filters) client_type_data = results['loan_data'] client_totals = results['loan_totals'] if client_type: if len(client_type_data) > 0: #client totols client_totals['member_number'] = client_type.customer_type+" "+str(len(client_type_data)) sub_par = 0 sub_par_rate = 0 if client_totals["principal_balance"] > 0: sub_par = (client_totals["principal_balance"]/general_bal["principal_balance"]) * 100 sub_par_rate = (client_totals['principal_due']/client_totals["principal_balance"]) * 100 client_totals["par"] = sub_par client_totals["par_rate"] = sub_par_rate sub_section.append({ "id":client_type.id, "name":client_type.customer_type, "sub_section_data":client_type_data, "sub_section_total":client_totals, "sub_section_total_count":len(client_type_data) }) officers_totals["loan_amount"] += client_totals["loan_amount"] officers_totals["principal_balance"] += client_totals["principal_balance"] officers_totals["principal_due"] += client_totals["principal_due"] officers_totals["total_due"] += client_totals["total_due"] grand_totals["loan_amount"] += client_totals["loan_amount"] grand_totals["principal_balance"] += client_totals["principal_balance"] grand_totals["principal_due"] += client_totals["principal_due"] grand_totals["total_due"] += client_totals["total_due"] grand_totals["count"] += 1 if len(sub_section) > 0: par = 0 par_rate = 0 if officers_totals["principal_balance"] > 0: par = (officers_totals["principal_balance"]/general_bal["principal_balance"]) * 100 par_rate = (officers_totals['principal_due']/officers_totals["principal_balance"]) * 100 officers_totals["par"] = par officers_totals["par_rate"] = par_rate section.append({ "id":loan_officer.id, "name":loan_officer.name, "section_data":sub_section, "section_total":officers_totals }) if grand_totals["count"] > 0: g_par = 0 g_par_rate = 0 if grand_totals["principal_balance"] > 0: g_par = (grand_totals["principal_balance"]/general_bal["principal_balance"]) * 100 g_par_rate = (grand_totals['principal_due']/grand_totals["principal_balance"]) * 100 grand_totals["par"] = g_par grand_totals["par_rate"] = g_par_rate return {"total":len(section),"data":section,"grand_total":grand_totals,"par":g_par,"par_rate":g_par_rate} else: return {"total":len(section),"data":section,"grand_total":grand_totals,"par":0,"par_rate":0} def filter_par_officer_gender(officer_ids,gender_list,dued_loans,as_at,general_bal): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0,"count":0} if dued_loans: for dued_loan in dued_loans: dued_loan_list[dued_loan['id']] = dued_loan dued_loan_ids.append(dued_loan['id']) loan_officers = Staff.objects.filter(id__in=officer_ids) if loan_officers: for loan_officer in loan_officers: sub_section = [] officers_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0} for gender in gender_list: gender_name = 'Male' if gender == 'F': gender_name = 'Female' elif gender == 'O': gender_name = 'Other' loan_filter = {"loan_arrear_date__lte":as_at,"loan_officer_id":loan_officer.id,"gender":gender,"id__in":dued_loan_ids,"status":"disbursed"} extra_filters = {"report_type":"par","end":as_at,"general_bal":general_bal} results = par_loans_totals(loan_filter,extra_filters) gender_data = results['loan_data'] gender_totals = results['loan_totals'] if len(gender_data) > 0: #client totols gender_totals['member_number'] = gender_name+" "+str(len(gender_data)) sub_par = 0 sub_par_rate = 0 if gender_totals["principal_balance"] > 0: sub_par = (gender_totals["principal_balance"]/general_bal["principal_balance"]) * 100 sub_par_rate = (gender_totals['principal_due']/gender_totals["principal_balance"]) * 100 gender_totals["par"] = sub_par gender_totals["par_rate"] = sub_par_rate sub_section.append({ "id":gender, "name":gender_name, "sub_section_data":gender_data, "sub_section_total":gender_totals, "sub_section_total_count":len(gender_data) }) officers_totals["loan_amount"] += gender_totals["loan_amount"] officers_totals["principal_balance"] += gender_totals["principal_balance"] officers_totals["principal_due"] += gender_totals["principal_due"] officers_totals["total_due"] += gender_totals["total_due"] officers_totals["par"] += gender_totals["par"] officers_totals["par_rate"] += gender_totals["par_rate"] grand_totals["loan_amount"] += gender_totals["loan_amount"] grand_totals["principal_balance"] += gender_totals["principal_balance"] grand_totals["principal_due"] += gender_totals["principal_due"] grand_totals["total_due"] += gender_totals["total_due"] grand_totals["par"] += (gender_totals["par"]/len(gender_data)) grand_totals["par_rate"] += (gender_totals["par_rate"]/len(gender_data)) grand_totals["count"] += 1 if len(sub_section) > 0: par = 0 par_rate = 0 if officers_totals["principal_balance"] > 0: par = (officers_totals["principal_balance"]/general_bal["principal_balance"]) * 100 par_rate = (officers_totals['principal_due']/officers_totals["principal_balance"]) * 100 officers_totals["par"] = par officers_totals["par_rate"] = par_rate section.append({ "id":loan_officer.id, "name":loan_officer.name, "section_data":sub_section, "section_total":officers_totals }) if grand_totals["count"] > 0: g_par = 0 g_par_rate = 0 if grand_totals["principal_balance"] > 0: g_par = (grand_totals["principal_balance"]/general_bal["principal_balance"]) * 100 g_par_rate = (grand_totals['principal_due']/grand_totals["principal_balance"]) * 100 grand_totals["par"] = g_par grand_totals["par_rate"] = g_par_rate return {"total":len(section),"data":section,"grand_total":grand_totals,"par":g_par,"par_rate":g_par_rate} else: return {"total":len(section),"data":section,"grand_total":grand_totals,"par":0,"par_rate":0} def filter_par_officer_branch(officer_ids,branch_ids,dued_loans,as_at,general_bal): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0,"count":0} if dued_loans: for dued_loan in dued_loans: dued_loan_list[dued_loan['id']] = dued_loan dued_loan_ids.append(dued_loan['id']) loan_branches = OrganisationBranch.objects.filter(id__in=branch_ids) if loan_branches: for loan_branch in loan_branches: sub_section = [] branch_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0} for officer_id in officer_ids: loan_officer = Staff.objects.get(pk=officer_id) loan_filter = {"loan_arrear_date__lte":as_at,"loan_officer_id":loan_officer.id,"branch_id":loan_branch.id,"id__in":dued_loan_ids,"status":"disbursed"} extra_filters = {"report_type":"par","end":as_at,"general_bal":general_bal} results = par_loans_totals(loan_filter,extra_filters) officers_data = results['loan_data'] officers_totals = results['loan_totals'] if loan_officer: if len(officers_data) > 0: #client totols officers_totals['member_number'] = loan_officer.name+" "+str(len(officers_data)) sub_par = 0 sub_par_rate = 0 if officers_totals["principal_balance"] > 0: sub_par = (officers_totals["principal_balance"]/general_bal["principal_balance"]) * 100 sub_par_rate = (officers_totals['principal_due']/officers_totals["principal_balance"]) * 100 officers_totals["par"] = sub_par officers_totals["par_rate"] = sub_par_rate sub_section.append({ "id":loan_officer.id, "name":loan_officer.name, "sub_section_data":officers_data, "sub_section_total":officers_totals, "sub_section_total_count":len(officers_data) }) branch_totals["loan_amount"] += officers_totals["loan_amount"] branch_totals["principal_balance"] += officers_totals["principal_balance"] branch_totals["principal_due"] += officers_totals["principal_due"] branch_totals["total_due"] += officers_totals["total_due"] branch_totals["par"] += officers_totals["par"] branch_totals["par_rate"] += officers_totals["par_rate"] grand_totals["loan_amount"] += officers_totals["loan_amount"] grand_totals["principal_balance"] += officers_totals["principal_balance"] grand_totals["principal_due"] += officers_totals["principal_due"] grand_totals["total_due"] += officers_totals["total_due"] grand_totals["par"] += (officers_totals["par"]/len(officers_data)) grand_totals["par_rate"] += (officers_totals["par_rate"]/len(officers_data)) grand_totals["count"] += 1 if len(sub_section) > 0: par = 0 par_rate = 0 if branch_totals["principal_balance"] > 0: par = (branch_totals["principal_balance"]/general_bal["principal_balance"]) * 100 par_rate = (branch_totals['principal_due']/branch_totals["principal_balance"]) * 100 officers_totals["par"] = par officers_totals["par_rate"] = par_rate section.append({ "id":loan_branch.id, "name":loan_branch.name, "section_data":sub_section, "section_total":branch_totals }) if grand_totals["count"] > 0: g_par = 0 g_par_rate = 0 if grand_totals["principal_balance"] > 0: g_par = (grand_totals["principal_balance"]/general_bal["principal_balance"]) * 100 g_par_rate = (grand_totals['principal_due']/grand_totals["principal_balance"]) * 100 grand_totals["par"] = g_par grand_totals["par_rate"] = g_par_rate return {"total":len(section),"data":section,"grand_total":grand_totals,"par":g_par,"par_rate":g_par_rate} else: return {"total":len(section),"data":section,"grand_total":grand_totals,"par":0,"par_rate":0} def filter_par_product_client_type(product_ids,client_type_ids,dued_loans,as_at,general_bal): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0,"count":0} if dued_loans: for dued_loan in dued_loans: dued_loan_list[dued_loan['id']] = dued_loan dued_loan_ids.append(dued_loan['id']) loan_products = LoanProduct.objects.filter(id__in=product_ids) if loan_products: for loan_product in loan_products: sub_section = [] loan_products_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0} for client_type_id in client_type_ids: client_type = CustomerType.objects.get(id=client_type_id) loan_filter = {"loan_arrear_date__lte":as_at,"loan_product_id":loan_product.id,"customer_type_id":client_type_id,"id__in":dued_loan_ids,"status":"disbursed"} extra_filters = {"report_type":"par","end":as_at,"general_bal":general_bal} results = par_loans_totals(loan_filter,extra_filters) client_type_data = results['loan_data'] client_totals = results['loan_totals'] if client_type: if len(client_type_data) > 0: #client totols client_totals['member_number'] = client_type.customer_type+" "+str(len(client_type_data)) sub_par = 0 sub_par_rate = 0 if client_totals["principal_balance"] > 0: sub_par = (client_totals["principal_balance"]/general_bal["principal_balance"]) * 100 sub_par_rate = (client_totals['principal_due']/client_totals["principal_balance"]) * 100 client_totals["par"] = sub_par client_totals["par_rate"] = sub_par_rate sub_section.append({ "id":client_type.id, "name":client_type.customer_type, "sub_section_data":client_type_data, "sub_section_total":client_totals, "sub_section_total_count":len(client_type_data) }) loan_products_totals["loan_amount"] += client_totals["loan_amount"] loan_products_totals["principal_balance"] += client_totals["principal_balance"] loan_products_totals["principal_due"] += client_totals["principal_due"] loan_products_totals["total_due"] += client_totals["total_due"] loan_products_totals["par"] += (client_totals["par"]/len(client_type_data)) loan_products_totals["par_rate"] += (client_totals["par_rate"]/len(client_type_data)) grand_totals["loan_amount"] += client_totals["loan_amount"] grand_totals["principal_balance"] += client_totals["principal_balance"] grand_totals["principal_due"] += client_totals["principal_due"] grand_totals["total_due"] += client_totals["total_due"] grand_totals["par"] += (client_totals["par"]/len(client_type_data)) grand_totals["par_rate"] += (client_totals["par_rate"]/len(client_type_data)) grand_totals["count"] += 1 if len(sub_section) > 0: par = 0 par_rate = 0 if loan_products_totals["principal_balance"] > 0: par = (loan_products_totals["principal_balance"]/general_bal["principal_balance"]) * 100 par_rate = (loan_products_totals['principal_due']/loan_products_totals["principal_balance"]) * 100 loan_products_totals["par"] = par loan_products_totals["par_rate"] = par_rate section.append({ "id":loan_product.id, "name":loan_product.product_name, "section_data":sub_section, "section_total":loan_products_totals }) if grand_totals["count"] > 0: g_par = 0 g_par_rate = 0 if grand_totals["principal_balance"] > 0: g_par = (grand_totals["principal_balance"]/general_bal["principal_balance"]) * 100 g_par_rate = (grand_totals['principal_due']/grand_totals["principal_balance"]) * 100 grand_totals["par"] = g_par grand_totals["par_rate"] = g_par_rate return {"total":len(section),"data":section,"grand_total":grand_totals,"par":g_par,"par_rate":g_par_rate} else: return {"total":len(section),"data":section,"grand_total":grand_totals,"par":0,"par_rate":0} def filter_par_product_gender(product_ids,gender_list,dued_loans,as_at,general_bal): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0,"count":0} if dued_loans: for dued_loan in dued_loans: dued_loan_list[dued_loan['id']] = dued_loan dued_loan_ids.append(dued_loan['id']) loan_products = LoanProduct.objects.filter(id__in=product_ids) if loan_products: for loan_officer in loan_products: sub_section = [] product_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0} for gender in gender_list: gender_name = 'Male' if gender == 'F': gender_name = 'Female' elif gender == 'O': gender_name = 'Other' loan_filter = {"loan_arrear_date__lte":as_at,"loan_product_id":loan_officer.id,"gender":gender,"id__in":dued_loan_ids,"status":"disbursed"} extra_filters = {"report_type":"par","end":as_at,"general_bal":general_bal} results = par_loans_totals(loan_filter,extra_filters) gender_data = results['loan_data'] gender_totals = results['loan_totals'] if len(gender_data) > 0: gender_totals['member_number'] = gender_name+" "+str(len(gender_data)) sub_par = 0 sub_par_rate = 0 if gender_totals["principal_balance"] > 0: sub_par = (gender_totals["principal_balance"]/general_bal["principal_balance"]) * 100 sub_par_rate = (gender_totals['principal_due']/gender_totals["principal_balance"]) * 100 gender_totals["par"] = sub_par gender_totals["par_rate"] = sub_par_rate sub_section.append({ "id":gender, "name":gender_name, "sub_section_data":gender_data, "sub_section_total":gender_totals, "sub_section_total_count":len(gender_data) }) product_totals["loan_amount"] += gender_totals["loan_amount"] product_totals["principal_balance"] += gender_totals["principal_balance"] product_totals["principal_due"] += gender_totals["principal_due"] product_totals["total_due"] += gender_totals["total_due"] product_totals["par"] += (gender_totals["par"]/len(gender_data)) product_totals["par_rate"] += (gender_totals["par_rate"]/len(gender_data)) grand_totals["loan_amount"] += gender_totals["loan_amount"] grand_totals["principal_balance"] += gender_totals["principal_balance"] grand_totals["principal_due"] += gender_totals["principal_due"] grand_totals["total_due"] += gender_totals["total_due"] grand_totals["par"] += (gender_totals["par"]/len(gender_data)) grand_totals["par_rate"] += (gender_totals["par_rate"]/len(gender_data)) grand_totals["count"] += 1 if len(sub_section) > 0: par = 0 par_rate = 0 if product_totals["principal_balance"] > 0: par = (product_totals["principal_balance"]/general_bal["principal_balance"]) * 100 par_rate = (product_totals['principal_due']/product_totals["principal_balance"]) * 100 product_totals["par"] = par product_totals["par_rate"] = par_rate section.append({ "id":loan_officer.id, "name":loan_officer.product_name, "section_data":sub_section, "section_total":product_totals }) if grand_totals["count"] > 0: g_par = 0 g_par_rate = 0 if grand_totals["principal_balance"] > 0: g_par = (grand_totals["principal_balance"]/general_bal["principal_balance"]) * 100 g_par_rate = (grand_totals['principal_due']/grand_totals["principal_balance"]) * 100 grand_totals["par"] = g_par grand_totals["par_rate"] = g_par_rate return {"total":len(section),"data":section,"grand_total":grand_totals,"par":g_par,"par_rate":g_par_rate} else: return {"total":len(section),"data":section,"grand_total":grand_totals,"par":0,"par_rate":0} def filter_par_product_branch(product_ids,branch_ids,dued_loans,as_at,general_bal): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0,"count":0} if dued_loans: for dued_loan in dued_loans: dued_loan_list[dued_loan['id']] = dued_loan dued_loan_ids.append(dued_loan['id']) loan_branches = OrganisationBranch.objects.filter(id__in=branch_ids) if loan_branches: for loan_branch in loan_branches: sub_section = [] branch_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0} for product_id in product_ids: loan_product = LoanProduct.objects.get(pk=product_id) loan_filter = {"loan_arrear_date__lte":as_at,"loan_product_id":loan_product.id,"branch_id":loan_branch.id,"id__in":dued_loan_ids,"status":"disbursed"} extra_filters = {"report_type":"par","end":as_at,"general_bal":general_bal} results = par_loans_totals(loan_filter,extra_filters) product_data = results['loan_data'] products_totals = results['loan_totals'] if loan_product: if len(product_data) > 0: #client totols products_totals['member_number'] = loan_product.product_name+" "+str(len(product_data)) sub_par = 0 sub_par_rate = 0 if products_totals["principal_balance"] > 0: sub_par = (products_totals["principal_balance"]/general_bal["principal_balance"]) * 100 sub_par_rate = (products_totals['principal_due']/products_totals["principal_balance"]) * 100 products_totals["par"] = sub_par products_totals["par_rate"] = sub_par_rate sub_section.append({ "id":loan_product.id, "name":loan_product.product_name, "sub_section_data":product_data, "sub_section_total":products_totals, "sub_section_total_count":len(product_data) }) branch_totals["loan_amount"] += products_totals["loan_amount"] branch_totals["principal_balance"] += products_totals["principal_balance"] branch_totals["principal_due"] += products_totals["principal_due"] branch_totals["total_due"] += products_totals["total_due"] branch_totals["par"] += (products_totals["par"]/len(product_data)) branch_totals["par_rate"] += (products_totals["par_rate"]/len(product_data)) grand_totals["loan_amount"] += products_totals["loan_amount"] grand_totals["principal_balance"] += products_totals["principal_balance"] grand_totals["principal_due"] += products_totals["principal_due"] grand_totals["total_due"] += products_totals["total_due"] grand_totals["par"] += (products_totals["par"]/len(product_data)) grand_totals["par_rate"] += (products_totals["par_rate"]/len(product_data)) grand_totals["count"] += 1 if len(sub_section) > 0: par = 0 par_rate = 0 if branch_totals["principal_balance"] > 0: par = (branch_totals["principal_balance"]/general_bal["principal_balance"]) * 100 par_rate = (branch_totals['principal_due']/branch_totals["principal_balance"]) * 100 branch_totals["par"] = par branch_totals["par_rate"] = par_rate section.append({ "id":loan_branch.id, "name":loan_branch.name, "section_data":sub_section, "section_total":branch_totals }) if grand_totals["count"] > 0: g_par = 0 g_par_rate = 0 if grand_totals["principal_balance"] > 0: g_par = (grand_totals["principal_balance"]/general_bal["principal_balance"]) * 100 g_par_rate = (grand_totals['principal_due']/grand_totals["principal_balance"]) * 100 grand_totals["par"] = g_par grand_totals["par_rate"] = g_par_rate return {"total":len(section),"data":section,"grand_total":grand_totals,"par":g_par,"par_rate":g_par_rate} else: return {"total":len(section),"data":section,"grand_total":grand_totals,"par":0,"par_rate":0} def filter_par_product_officer(product_ids,officer_ids,dued_loans,as_at,general_bal): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0,"count":0} if dued_loans: for dued_loan in dued_loans: dued_loan_list[dued_loan['id']] = dued_loan dued_loan_ids.append(dued_loan['id']) loan_officers = Staff.objects.filter(id__in=officer_ids) if loan_officers: for loan_officer in loan_officers: sub_section = [] officer_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0} for product_id in product_ids: loan_product = LoanProduct.objects.get(pk=product_id) loan_filter = {"loan_arrear_date__lte":as_at,"loan_product_id":loan_product.id,"loan_officer_id":loan_officer.id,"id__in":dued_loan_ids,"status":"disbursed"} extra_filters = {"report_type":"par","end":as_at,"general_bal":general_bal} results = par_loans_totals(loan_filter,extra_filters) product_data = results['loan_data'] products_totals = results['loan_totals'] if loan_product: if len(product_data) > 0: #client totols products_totals['member_number'] = loan_product.product_name+" "+str(len(product_data)) sub_par = 0 sub_par_rate = 0 if products_totals["principal_balance"] > 0: sub_par = (products_totals["principal_balance"]/general_bal["principal_balance"]) * 100 sub_par_rate = (products_totals['principal_due']/products_totals["principal_balance"]) * 100 products_totals["par"] = sub_par products_totals["par_rate"] = sub_par_rate sub_section.append({ "id":loan_product.id, "name":loan_product.product_name, "sub_section_data":product_data, "sub_section_total":products_totals, "sub_section_total_count":len(product_data) }) officer_totals["loan_amount"] += products_totals["loan_amount"] officer_totals["principal_balance"] += products_totals["principal_balance"] officer_totals["principal_due"] += products_totals["principal_due"] officer_totals["total_due"] += products_totals["total_due"] officer_totals["par"] += (products_totals["par"]/len(product_data)) officer_totals["par_rate"] += (products_totals["par_rate"]/len(product_data)) grand_totals["loan_amount"] += products_totals["loan_amount"] grand_totals["principal_balance"] += products_totals["principal_balance"] grand_totals["principal_due"] += products_totals["principal_due"] grand_totals["total_due"] += products_totals["total_due"] grand_totals["par"] += (products_totals["par"]/len(product_data)) grand_totals["par_rate"] += (products_totals["par_rate"]/len(product_data)) grand_totals["count"] += 1 if len(sub_section) > 0: par = 0 par_rate = 0 if officer_totals["principal_balance"] > 0: par = (officer_totals["principal_balance"]/general_bal["principal_balance"]) * 100 par_rate = (officer_totals['principal_due']/officer_totals["principal_balance"]) * 100 officer_totals["par"] = par officer_totals["par_rate"] = par_rate section.append({ "id":loan_officer.id, "name":loan_officer.name, "section_data":sub_section, "section_total":officer_totals, "par":par, "par_rate":par_rate }) if grand_totals["count"] > 0: g_par = 0 g_par_rate = 0 if grand_totals["principal_balance"] > 0: g_par = (grand_totals["principal_balance"]/general_bal["principal_balance"]) * 100 g_par_rate = (grand_totals['principal_due']/grand_totals["principal_balance"]) * 100 grand_totals["par"] = g_par grand_totals["par_rate"] = g_par_rate return {"total":len(section),"data":section,"grand_total":grand_totals,"par":g_par,"par_rate":g_par_rate} else: return {"total":len(section),"data":section,"grand_total":grand_totals,"par":0,"par_rate":0} def filter_par_product_sector(product_ids,sector_ids,dued_loans,as_at,general_bal): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0,"count":0} if dued_loans: for dued_loan in dued_loans: dued_loan_list[dued_loan['id']] = dued_loan dued_loan_ids.append(dued_loan['id']) loan_sectors = LoanSectors.objects.filter(id__in=sector_ids) if loan_sectors: for loan_sector in loan_sectors: sub_section = [] sector_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0} for product_id in product_ids: loan_product = LoanProduct.objects.get(pk=product_id) loan_filter = {"loan_arrear_date__lte":as_at,"loan_product_id":loan_product.id,"loan_sector_id":loan_sector.id,"id__in":dued_loan_ids,"status":"disbursed"} extra_filters = {"report_type":"par","end":as_at,"general_bal":general_bal} results = par_loans_totals(loan_filter,extra_filters) product_data = results['loan_data'] products_totals = results['loan_totals'] if loan_product: if len(product_data) > 0: #client totols products_totals['member_number'] = loan_product.product_name+" "+str(len(product_data)) sub_par = 0 sub_par_rate = 0 if products_totals["principal_balance"] > 0: sub_par = (products_totals["principal_balance"]/general_bal["principal_balance"]) * 100 sub_par_rate = (products_totals['principal_due']/products_totals["principal_balance"]) * 100 products_totals["par"] = sub_par products_totals["par_rate"] = sub_par_rate sub_section.append({ "id":loan_product.id, "name":loan_product.product_name, "sub_section_data":product_data, "sub_section_total":products_totals, "sub_section_total_count":len(product_data) }) sector_totals["loan_amount"] += products_totals["loan_amount"] sector_totals["principal_balance"] += products_totals["principal_balance"] sector_totals["principal_due"] += products_totals["principal_due"] sector_totals["total_due"] += products_totals["total_due"] sector_totals["par"] += (products_totals["par"]/len(product_data)) sector_totals["par_rate"] += (products_totals["par_rate"]/len(product_data)) grand_totals["loan_amount"] += products_totals["loan_amount"] grand_totals["principal_balance"] += products_totals["principal_balance"] grand_totals["principal_due"] += products_totals["principal_due"] grand_totals["total_due"] += products_totals["total_due"] grand_totals["par"] += (products_totals["par"]/len(product_data)) grand_totals["par_rate"] += (products_totals["par_rate"]/len(product_data)) grand_totals["count"] += 1 if len(sub_section) > 0: par = 0 par_rate = 0 if sector_totals["principal_balance"] > 0: par = (sector_totals["principal_balance"]/general_bal["principal_balance"]) * 100 par_rate = (sector_totals['principal_due']/sector_totals["principal_balance"]) * 100 sector_totals["par"] = par sector_totals["par_rate"] = par_rate section.append({ "id":loan_sector.id, "name":loan_sector.name, "section_data":sub_section, "section_total":sector_totals }) if grand_totals["count"] > 0: g_par = 0 g_par_rate = 0 if grand_totals["principal_balance"] > 0: g_par = (grand_totals["principal_balance"]/general_bal["principal_balance"]) * 100 g_par_rate = (grand_totals['principal_due']/grand_totals["principal_balance"]) * 100 grand_totals["par"] = g_par grand_totals["par_rate"] = g_par_rate return {"total":len(section),"data":section,"grand_total":grand_totals,"par":g_par,"par_rate":g_par_rate} else: return {"total":len(section),"data":section,"grand_total":grand_totals,"par":0,"par_rate":0} def filter_par_gender_branch(branch_ids,gender_list,dued_loans,as_at,general_bal): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0,"count":0} if dued_loans: for dued_loan in dued_loans: dued_loan_list[dued_loan['id']] = dued_loan dued_loan_ids.append(dued_loan['id']) loan_branches = OrganisationBranch.objects.filter(id__in=branch_ids) if loan_branches: for loan_branch in loan_branches: sub_section = [] branch_totals = {"loan_amount":0,"principal_balance":0,"principal_due":0,"total_due":0,"par":0,"par_rate":0} for gender in gender_list: gender_name = 'Male' if gender == 'F': gender_name = 'Female' elif gender == 'O': gender_name = 'Other' loan_filter = {"loan_arrear_date__lte":as_at,"branch_id":loan_branch.id,"gender":gender,"id__in":dued_loan_ids,"status":"disbursed"} extra_filters = {"report_type":"par","end":as_at,"general_bal":general_bal} results = par_loans_totals(loan_filter,extra_filters) gender_data = results['loan_data'] gender_totals = results['loan_totals'] if len(gender_data) > 0: #client totols gender_totals['member_number'] = gender_name+" "+str(len(gender_data)) sub_par = 0 sub_par_rate = 0 if gender_totals["principal_balance"] > 0: sub_par = (gender_totals["principal_balance"]/general_bal["principal_balance"]) * 100 sub_par_rate = (gender_totals['principal_due']/gender_totals["principal_balance"]) * 100 gender_totals["par"] = sub_par gender_totals["par_rate"] = sub_par_rate sub_section.append({ "id":gender, "name":gender_name, "sub_section_data":gender_data, "sub_section_total":gender_totals, "sub_section_total_count":len(gender_data) }) branch_totals["loan_amount"] += gender_totals["loan_amount"] branch_totals["principal_balance"] += gender_totals["principal_balance"] branch_totals["principal_due"] += gender_totals["principal_due"] branch_totals["total_due"] += gender_totals["total_due"] branch_totals["par"] += (gender_totals["par"]/len(gender_data)) branch_totals["par_rate"] += (gender_totals["par_rate"]/len(gender_data)) grand_totals["loan_amount"] += gender_totals["loan_amount"] grand_totals["principal_balance"] += gender_totals["principal_balance"] grand_totals["principal_due"] += gender_totals["principal_due"] grand_totals["total_due"] += gender_totals["total_due"] grand_totals["par"] += (gender_totals["par"]/len(gender_data)) grand_totals["par_rate"] += (gender_totals["par_rate"]/len(gender_data)) grand_totals["count"] += 1 if len(sub_section) > 0: par = 0 par_rate = 0 if branch_totals["principal_balance"] > 0: par = (branch_totals["principal_balance"]/general_bal["principal_balance"]) * 100 par_rate = (branch_totals['principal_due']/branch_totals["principal_balance"]) * 100 branch_totals["par"] = par branch_totals["par_rate"] = par_rate section.append({ "id":loan_branch.id, "name":loan_branch.name, "section_data":sub_section, "section_total":branch_totals }) if grand_totals["count"] > 0: g_par = 0 g_par_rate = 0 if grand_totals["principal_balance"] > 0: g_par = (grand_totals["principal_balance"]/general_bal["principal_balance"]) * 100 g_par_rate = (grand_totals['principal_due']/grand_totals["principal_balance"]) * 100 grand_totals["par"] = g_par grand_totals["par_rate"] = g_par_rate return {"total":len(section),"data":section,"grand_total":grand_totals,"par":g_par,"par_rate":g_par_rate} else: return {"total":len(section),"data":section,"grand_total":grand_totals,"par":0,"par_rate":0} def filter_par_group_branch(branch_ids,group_ids,dued_loans,as_at,general_bal): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = {"principal_balance":0,"principal_due":0,"count":0} if dued_loans: for dued_loan in dued_loans: dued_loan_list[dued_loan['id']] = dued_loan dued_loan_ids.append(dued_loan['id']) loan_branches = OrganisationBranch.objects.filter(id__in=branch_ids) if loan_branches: for loan_branch in loan_branches: sub_section = [] branch_totals = { "principal_balance":0,"principal_due":0} for group_id in group_ids: group = Customer.objects.get(pk=group_id) customer_ids = GroupMembership.objects.filter(group=group).values_list('member__id', flat=True) loan_filter = {"loan_arrear_date__lte":as_at,"customer_id__in":customer_ids,"branch_id":loan_branch.id,"id__in":dued_loan_ids,"status":"disbursed"} extra_filters = {"report_type":"par","end":as_at,"general_bal":general_bal} results = par_loans_totals(loan_filter,extra_filters) group_data = results['loan_data'] group_totals = results['loan_totals'] if group: if len(group_data) > 0: group_totals['member_number'] = group.name+" "+str(len(group_data)) sub_par = 0 sub_par_rate = 0 if group_totals["principal_balance"] > 0: sub_par = (group_totals["principal_balance"]/general_bal["principal_balance"]) * 100 sub_par_rate = (group_totals['principal_due']/group_totals["principal_balance"]) * 100 group_totals["par"] = sub_par group_totals["par_rate"] = sub_par_rate sub_section.append({ "id":group.id, "name":group.name, "sub_section_data":group_data, "sub_section_total":group_totals, "sub_section_total_count":len(group_data) }) branch_totals["loan_amount"] += group_totals["loan_amount"] branch_totals["principal_balance"] += group_totals["principal_balance"] branch_totals["principal_due"] += group_totals["principal_due"] branch_totals["total_due"] += group_totals["total_due"] branch_totals["par"] += (group_totals["par"]/len(group_data)) branch_totals["par_rate"] += (group_totals["par_rate"]/len(group_data)) grand_totals["loan_amount"] += group_totals["loan_amount"] grand_totals["principal_balance"] += group_totals["principal_balance"] grand_totals["principal_due"] += group_totals["principal_due"] grand_totals["total_due"] += group_totals["total_due"] grand_totals["par"] += (group_totals["par"]/len(group_data)) grand_totals["par_rate"] += (group_totals["par_rate"]/len(group_data)) grand_totals["count"] += 1 if len(sub_section) > 0: par = 0 par_rate = 0 if branch_totals["principal_balance"] > 0: par = (branch_totals["principal_balance"]/general_bal["principal_balance"]) * 100 par_rate = (branch_totals['principal_due']/branch_totals["principal_balance"]) * 100 branch_totals["par"] = par branch_totals["par_rate"] = par_rate section.append({ "id":loan_branch.id, "name":loan_branch.name, "section_data":sub_section, "section_total":branch_totals }) if grand_totals["count"] > 0: g_par = 0 g_par_rate = 0 if grand_totals["principal_balance"] > 0: g_par = (grand_totals["principal_balance"]/general_bal["principal_balance"]) * 100 g_par_rate = (grand_totals['principal_due']/grand_totals["principal_balance"]) * 100 grand_totals["par"] = g_par grand_totals["par_rate"] = g_par_rate return {"total":len(section),"data":section,"grand_total":grand_totals,"par":g_par,"par_rate":g_par_rate} else: return {"total":len(section),"data":section,"grand_total":grand_totals,"par":0,"par_rate":0}