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 due_repayement_loans_totals(loan_filter,extraFilters = {}): loan_data = [] loan_totals = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "total_due":0, "penalty":0, "total_prepaid":0, } loans = filter_loan_balances(loan_filter,extraFilters) if loans: for loan in loans: 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 '''Calculate prepayments''' prepaid_principal = 0 prepaid_interest = 0 paid_penalty = 0 rate = 0 due_principal = loan['principal_due'] due_interest = loan['interest_due'] penalty = loan['penalty_balance'] princ_paid = loan['princ_paid'] int_paid = loan['int_paid'] total_payment = loan['princ_paid'] + loan['int_paid'] + loan['pen_paid'] + loan['int_wav'] + loan['pen_wav'] if due_principal < 0: prepaid_principal = abs(due_principal) due_principal = 0 if due_interest < 0: prepaid_interest = abs(due_interest) due_interest = 0 if penalty < 0: paid_penalty = abs(penalty) penalty = 0 if due_principal > 0: x = princ_paid - prepaid_principal #x = total_payment - (prepaid_principal + prepaid_interest + paid_penalty) rate = float(x/due_principal) * 100 if (due_principal + due_interest + penalty) > 0: loan_data.append({ "member_number":loan["member_number"], "name":loan["name"], "telephone":loan["telephone"], "id":loan["id"], "due_date":loan["first_arrear_date"], "arrear_days":arrear_days, "principal_due":due_principal, "interest_due":due_interest, "penalty":penalty, "total_due":due_principal + due_interest + penalty, "princ_paid":loan["princ_paid"], "int_paid":loan["int_paid"], "penalty_paid":loan['pen_paid'], "total_paid":loan['princ_paid'] + loan['int_paid'] + loan['pen_paid'], "total_prepaid":prepaid_principal + prepaid_interest + paid_penalty, "rate":rate }) loan_totals["princ_paid"] += loan["princ_paid"] loan_totals["int_paid"] += loan["int_paid"] loan_totals["penalty_paid"] += loan['pen_paid'] loan_totals['total_paid'] += loan['princ_paid'] + loan['int_paid'] + loan['pen_paid'] loan_totals["principal_due"] += due_principal loan_totals["interest_due"] += due_interest loan_totals["penalty"] += penalty loan_totals["total_due"] += due_principal + due_interest + penalty loan_totals['total_prepaid'] += prepaid_principal + prepaid_interest + paid_penalty return {"loan_data":loan_data,"loan_totals":loan_totals} def filter_due_repayment_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":"due_vs_repayment","search":search,"start":make_aware(datetime.strptime(report_filters['start'], '%Y-%m-%d %H:%M:%S')),"end":make_aware(datetime.strptime(report_filters['end'], '%Y-%m-%d %H:%M:%S'))} loan_filter = {"loan_arrear_date__gte":report_filters['start'],"loan_arrear_date__lte":report_filters['end'],"loan_balance__gt":0,"is_deleted":False,"status":"disbursed","organisation_id":report_filters["organisation_id"]} 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 = filter_loan_balances(loan_filter,extraFilters) loans_count = len(loans) dued_loans = paginator.paginate_queryset(loans, request) result = filter_arrears_officer_client_type(filter_1,filter_2,dued_loans,report_filters['start'],report_filters['end']) 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_arrears_officer_gender(filter_1,filter_2,dued_loans,report_filters['start'],report_filters['end']) 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_arrears_officer_branch(filter_1,filter_2,dued_loans,report_filters['start'],report_filters['end']) 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_arrears_product_client_type(filter_1,filter_2,dued_loans,report_filters['start'],report_filters['end']) 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_arrears_product_gender(filter_1,filter_2,dued_loans,report_filters['start'],report_filters['end']) 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_arrears_product_branch(filter_1,filter_2,dued_loans,report_filters['start'],report_filters['end']) 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_arrears_product_officer(filter_1,filter_2,dued_loans,report_filters['start'],report_filters['end']) 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_arrears_product_sector(filter_1,filter_2,dued_loans,report_filters['start'],report_filters['end']) 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_arrears_gender_branch(filter_1,filter_2,dued_loans,report_filters['start'],report_filters['end']) 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_arrears_group_branch(filter_1,filter_2,dued_loans,report_filters['start'],report_filters['end']) return Response({"results":result,"count":loans_count}) def filter_arrears_officer_client_type(officer_ids,client_type_ids,dued_loans,start,end): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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 = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":0 } for client_type_id in client_type_ids: client_type = CustomerType.objects.get(id=client_type_id) extraFilters = {"report_type":"due_vs_repayment","start":start,"end":end} loan_filter = {"loan_arrear_date__gte":start,"loan_arrear_date__lte":end,"loan_officer_id":loan_officer.id,"customer_type_id":client_type_id,"id__in":dued_loan_ids,"status":"disbursed"} results = due_repayement_loans_totals(loan_filter,extraFilters) 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_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["princ_paid"] += client_totals["princ_paid"] officers_totals["int_paid"] += client_totals["int_paid"] officers_totals["penalty_paid"] += client_totals["penalty_paid"] officers_totals['total_paid'] += client_totals["total_paid"] officers_totals["principal_due"] += client_totals["principal_due"] officers_totals["interest_due"] += client_totals["interest_due"] officers_totals["penalty"] += client_totals["penalty"] officers_totals["total_due"] += client_totals["total_due"] officers_totals["total_prepaid"] += client_totals["total_prepaid"] grand_totals["princ_paid"] += client_totals["princ_paid"] grand_totals["int_paid"] += client_totals["int_paid"] grand_totals["penalty_paid"] += client_totals["penalty_paid"] grand_totals['total_paid'] += client_totals["total_paid"] grand_totals["principal_due"] += client_totals["principal_due"] grand_totals["interest_due"] += client_totals["interest_due"] grand_totals["penalty"] += client_totals["penalty"] grand_totals["total_due"] += client_totals["total_due"] grand_totals["total_prepaid"] += client_totals["total_prepaid"] if len(sub_section) > 0: section.append({ "id":loan_officer.id, "name":loan_officer.name, "section_data":sub_section, "section_total":officers_totals }) return {"total":len(section),"data":section,"grand_total":grand_totals} def filter_arrears_officer_gender(officer_ids,gender_list,dued_loans,start,end): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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 = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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__gte":start,"loan_arrear_date__lte":end,"loan_officer_id":loan_officer.id,"gender":gender,"id__in":dued_loan_ids,"status":"disbursed"} extraFilters = {"report_type":"due_vs_repayment","start":start,"end":end} results = due_repayement_loans_totals(loan_filter,extraFilters) 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_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["princ_paid"] += gender_totals["princ_paid"] officers_totals["int_paid"] += gender_totals["int_paid"] officers_totals["penalty_paid"] += gender_totals["penalty_paid"] officers_totals['total_paid'] += gender_totals["total_paid"] officers_totals["principal_due"] += gender_totals["principal_due"] officers_totals["interest_due"] += gender_totals["interest_due"] officers_totals["penalty"] += gender_totals["penalty"] officers_totals["total_due"] += gender_totals["total_due"] officers_totals["total_prepaid"] += gender_totals["total_prepaid"] grand_totals["princ_paid"] += gender_totals["princ_paid"] grand_totals["int_paid"] += gender_totals["int_paid"] grand_totals["penalty_paid"] += gender_totals["penalty_paid"] grand_totals['total_paid'] += gender_totals["total_paid"] grand_totals["principal_due"] += gender_totals["principal_due"] grand_totals["interest_due"] += gender_totals["interest_due"] grand_totals["penalty"] += gender_totals["penalty"] grand_totals["total_due"] += gender_totals["total_due"] grand_totals["total_prepaid"] += gender_totals["total_prepaid"] if len(sub_section) > 0: section.append({ "id":loan_officer.id, "name":loan_officer.name, "section_data":sub_section, "section_total":officers_totals }) return {"total":len(section),"data":section,"grand_total":grand_totals} def filter_arrears_officer_branch(officer_ids,branch_ids,dued_loans,start,end): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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 = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":0 } for officer_id in officer_ids: loan_officer = Staff.objects.get(pk=officer_id) loan_filter = {"loan_arrear_date__gte":start,"loan_arrear_date__lte":end,"loan_officer_id":loan_officer.id,"branch_id":loan_branch.id,"id__in":dued_loan_ids,"status":"disbursed"} extraFilters = {"report_type":"due_vs_repayment","start":start,"end":end} results = due_repayement_loans_totals(loan_filter,extraFilters) 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_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["princ_paid"] += officers_totals["princ_paid"] branch_totals["int_paid"] += officers_totals["int_paid"] branch_totals["penalty_paid"] += officers_totals["penalty_paid"] branch_totals['total_paid'] += officers_totals["total_paid"] branch_totals["principal_due"] += officers_totals["principal_due"] branch_totals["interest_due"] += officers_totals["interest_due"] branch_totals["penalty"] += officers_totals["penalty"] branch_totals["total_due"] += officers_totals["total_due"] branch_totals["total_prepaid"] += officers_totals["total_prepaid"] grand_totals["princ_paid"] += officers_totals["princ_paid"] grand_totals["int_paid"] += officers_totals["int_paid"] grand_totals["penalty_paid"] += officers_totals["penalty_paid"] grand_totals['total_paid'] += officers_totals["total_paid"] grand_totals["principal_due"] += officers_totals["principal_due"] grand_totals["interest_due"] += officers_totals["interest_due"] grand_totals["penalty"] += officers_totals["penalty"] grand_totals["total_due"] += officers_totals["total_due"] grand_totals["total_prepaid"] += officers_totals["total_prepaid"] if len(sub_section) > 0: section.append({ "id":loan_branch.id, "name":loan_branch.name, "section_data":sub_section, "section_total":branch_totals }) return {"total":len(section),"data":section,"grand_total":grand_totals} def filter_arrears_product_client_type(product_ids,client_type_ids,dued_loans,start,end): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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 = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":0 } for client_type_id in client_type_ids: client_type = CustomerType.objects.get(id=client_type_id) loan_filter = {"loan_arrear_date__gte":start,"loan_arrear_date__lte":end,"loan_product_id":loan_product.id,"customer_type_id":client_type_id,"id__in":dued_loan_ids,"status":"disbursed"} extraFilters = {"report_type":"due_vs_repayment","start":start,"end":end} results = due_repayement_loans_totals(loan_filter,extraFilters) 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_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["princ_paid"] += client_totals["princ_paid"] loan_products_totals["int_paid"] += client_totals["int_paid"] loan_products_totals["penalty_paid"] += client_totals["penalty_paid"] loan_products_totals['total_paid'] += client_totals["total_paid"] loan_products_totals["principal_due"] += client_totals["principal_due"] loan_products_totals["interest_due"] += client_totals["interest_due"] loan_products_totals["penalty"] += client_totals["penalty"] loan_products_totals["total_due"] += client_totals["total_due"] loan_products_totals["total_prepaid"] += client_totals["total_prepaid"] grand_totals["princ_paid"] += client_totals["princ_paid"] grand_totals["int_paid"] += client_totals["int_paid"] grand_totals["penalty_paid"] += client_totals["penalty_paid"] grand_totals['total_paid'] += client_totals["total_paid"] grand_totals["principal_due"] += client_totals["principal_due"] grand_totals["interest_due"] += client_totals["interest_due"] grand_totals["penalty"] += client_totals["penalty"] grand_totals["total_due"] += client_totals["total_due"] grand_totals["total_prepaid"] += client_totals["total_prepaid"] if len(sub_section) > 0: section.append({ "id":loan_product.id, "name":loan_product.product_name, "section_data":sub_section, "section_total":loan_products_totals }) return {"total":len(section),"data":section,"grand_total":grand_totals} def filter_arrears_product_gender(product_ids,gender_list,dued_loans,start,end): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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 = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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__gte":start,"loan_arrear_date__lte":end,"loan_product_id":loan_officer.id,"gender":gender,"id__in":dued_loan_ids,"status":"disbursed"} extraFilters = {"report_type":"due_vs_repayment","start":start,"end":end} results = due_repayement_loans_totals(loan_filter,extraFilters) 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_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["princ_paid"] += gender_totals["princ_paid"] product_totals["int_paid"] += gender_totals["int_paid"] product_totals["penalty_paid"] += gender_totals["penalty_paid"] product_totals['total_paid'] += gender_totals["total_paid"] product_totals["principal_due"] += gender_totals["principal_due"] product_totals["interest_due"] += gender_totals["interest_due"] product_totals["penalty"] += gender_totals["penalty"] product_totals["total_due"] += gender_totals["total_due"] grand_totals["princ_paid"] += gender_totals["princ_paid"] grand_totals["int_paid"] += gender_totals["int_paid"] grand_totals["penalty_paid"] += gender_totals["penalty_paid"] grand_totals['total_paid'] += gender_totals["total_paid"] grand_totals["principal_due"] += gender_totals["principal_due"] grand_totals["interest_due"] += gender_totals["interest_due"] grand_totals["penalty"] += gender_totals["penalty"] grand_totals["total_due"] += gender_totals["total_due"] grand_totals["total_prepaid"] += gender_totals["total_prepaid"] if len(sub_section) > 0: section.append({ "id":loan_officer.id, "name":loan_officer.product_name, "section_data":sub_section, "section_total":product_totals }) return {"total":len(section),"data":section,"grand_total":grand_totals} def filter_arrears_product_branch(product_ids,branch_ids,dued_loans,start,end): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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 = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":0 } for product_id in product_ids: loan_product = LoanProduct.objects.get(pk=product_id) loan_filter = {"loan_arrear_date__gte":start,"loan_arrear_date__lte":end,"loan_product_id":loan_product.id,"branch_id":loan_branch.id,"id__in":dued_loan_ids,"status":"disbursed"} extraFilters = {"report_type":"due_vs_repayment","start":start,"end":end} results = due_repayement_loans_totals(loan_filter,extraFilters) 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_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["princ_paid"] += products_totals["princ_paid"] branch_totals["int_paid"] += products_totals["int_paid"] branch_totals["penalty_paid"] += products_totals["penalty_paid"] branch_totals['total_paid'] += products_totals["total_paid"] branch_totals["principal_due"] += products_totals["principal_due"] branch_totals["interest_due"] += products_totals["interest_due"] branch_totals["penalty"] += products_totals["penalty"] branch_totals["total_due"] += products_totals["total_due"] branch_totals["total_prepaid"] += products_totals["total_prepaid"] grand_totals["princ_paid"] += products_totals["princ_paid"] grand_totals["int_paid"] += products_totals["int_paid"] grand_totals["penalty_paid"] += products_totals["penalty_paid"] grand_totals['total_paid'] += products_totals["total_paid"] grand_totals["principal_due"] += products_totals["principal_due"] grand_totals["interest_due"] += products_totals["interest_due"] grand_totals["penalty"] += products_totals["penalty"] grand_totals["total_due"] += products_totals["total_due"] grand_totals["total_prepaid"] += products_totals["total_prepaid"] if len(sub_section) > 0: section.append({ "id":loan_branch.id, "name":loan_branch.name, "section_data":sub_section, "section_total":branch_totals }) return {"total":len(section),"data":section,"grand_total":grand_totals} def filter_arrears_product_officer(product_ids,officer_ids,dued_loans,start,end): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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 = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":0 } for product_id in product_ids: loan_product = LoanProduct.objects.get(pk=product_id) loan_filter = {"loan_arrear_date__gte":start,"loan_arrear_date__lte":end,"loan_product_id":loan_product.id,"loan_officer_id":loan_officer.id,"id__in":dued_loan_ids,"status":"disbursed"} extraFilters = {"report_type":"due_vs_repayment","start":start,"end":end} results = due_repayement_loans_totals(loan_filter,extraFilters) product_data = results['loan_data'] products_totals = results['loan_totals'] if loan_product: if len(product_data) > 0: products_totals['member_number'] = loan_product.product_name+" "+str(len(product_data)) 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["princ_paid"] += products_totals["princ_paid"] officer_totals["int_paid"] += products_totals["int_paid"] officer_totals["penalty_paid"] += products_totals["penalty_paid"] officer_totals['total_paid'] += products_totals["total_paid"] officer_totals["principal_due"] += products_totals["principal_due"] officer_totals["interest_due"] += products_totals["interest_due"] officer_totals["penalty"] += products_totals["penalty"] officer_totals["total_due"] += products_totals["total_due"] officer_totals["total_prepaid"] += products_totals["total_prepaid"] grand_totals["princ_paid"] += products_totals["princ_paid"] grand_totals["int_paid"] += products_totals["int_paid"] grand_totals["penalty_paid"] += products_totals["penalty_paid"] grand_totals['total_paid'] += products_totals["total_paid"] grand_totals["principal_due"] += products_totals["principal_due"] grand_totals["interest_due"] += products_totals["interest_due"] grand_totals["penalty"] += products_totals["penalty"] grand_totals["total_due"] += products_totals["total_due"] grand_totals["total_prepaid"] += products_totals["total_prepaid"] if len(sub_section) > 0: section.append({ "id":loan_officer.id, "name":loan_officer.name, "section_data":sub_section, "section_total":officer_totals }) return {"total":len(section),"data":section,"grand_total":grand_totals} def filter_arrears_product_sector(product_ids,sector_ids,dued_loans,start,end): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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 = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":0 } for product_id in product_ids: loan_product = LoanProduct.objects.get(pk=product_id) loan_filter = {"loan_arrear_date__gte":start,"loan_arrear_date__lte":end,"loan_product_id":loan_product.id,"loan_sector_id":loan_sector.id,"id__in":dued_loan_ids,"status":"disbursed"} extraFilters = {"report_type":"due_vs_repayment","start":start,"end":end} results = due_repayement_loans_totals(loan_filter,extraFilters) 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_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["princ_paid"] += products_totals["princ_paid"] sector_totals["int_paid"] += products_totals["int_paid"] sector_totals["penalty_paid"] += products_totals["penalty_paid"] sector_totals['total_paid'] += products_totals["total_paid"] sector_totals["principal_due"] += products_totals["principal_due"] sector_totals["interest_due"] += products_totals["interest_due"] sector_totals["penalty"] += products_totals["penalty"] sector_totals["total_due"] += products_totals["total_due"] sector_totals["total_prepaid"] += products_totals["total_prepaid"] grand_totals["princ_paid"] += products_totals["princ_paid"] grand_totals["int_paid"] += products_totals["int_paid"] grand_totals["penalty_paid"] += products_totals["penalty_paid"] grand_totals['total_paid'] += products_totals["total_paid"] grand_totals["principal_due"] += products_totals["principal_due"] grand_totals["interest_due"] += products_totals["interest_due"] grand_totals["penalty"] += products_totals["penalty"] grand_totals["total_due"] += products_totals["total_due"] grand_totals["total_prepaid"] += products_totals["total_prepaid"] if len(sub_section) > 0: section.append({ "id":loan_sector.id, "name":loan_sector.name, "section_data":sub_section, "section_total":sector_totals }) return {"total":len(section),"data":section,"grand_total":grand_totals} def filter_arrears_gender_branch(branch_ids,gender_list,dued_loans,start,end): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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 = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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__gte":start,"loan_arrear_date__lte":end,"branch_id":loan_branch.id,"gender":gender,"id__in":dued_loan_ids,"status":"disbursed"} extraFilters = {"report_type":"due_vs_repayment","start":start,"end":end} results = due_repayement_loans_totals(loan_filter,extraFilters) 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_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["princ_paid"] += gender_totals["princ_paid"] branch_totals["int_paid"] += gender_totals["int_paid"] branch_totals["penalty_paid"] += gender_totals["penalty_paid"] branch_totals['total_paid'] += gender_totals["total_paid"] branch_totals["principal_due"] += gender_totals["principal_due"] branch_totals["interest_due"] += gender_totals["interest_due"] branch_totals["penalty"] += gender_totals["penalty"] branch_totals["total_due"] += gender_totals["total_due"] branch_totals["total_prepaid"] += gender_totals["total_prepaid"] grand_totals["princ_paid"] += gender_totals["princ_paid"] grand_totals["int_paid"] += gender_totals["int_paid"] grand_totals["penalty_paid"] += gender_totals["penalty_paid"] grand_totals['total_paid'] += gender_totals["total_paid"] grand_totals["principal_due"] += gender_totals["principal_due"] grand_totals["interest_due"] += gender_totals["interest_due"] grand_totals["penalty"] += gender_totals["penalty"] grand_totals["total_due"] += gender_totals["total_due"] grand_totals["total_prepaid"] += gender_totals["total_prepaid"] if len(sub_section) > 0: section.append({ "id":loan_branch.id, "name":loan_branch.name, "section_data":sub_section, "section_total":branch_totals }) return {"total":len(section),"data":section,"grand_total":grand_totals} def filter_arrears_group_branch(branch_ids,group_ids,dued_loans,start,end): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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 = { "princ_paid":0, "int_paid":0, "penalty_paid":0, "total_paid":0, "principal_due":0, "interest_due":0, "penalty":0, "total_due":0, "total_prepaid":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__gte":start,"loan_arrear_date__lte":end,"customer_id__in":customer_ids,"branch_id":loan_branch.id,"id__in":dued_loan_ids,"status":"disbursed"} extraFilters = {"report_type":"due_vs_repayment","start":start,"end":end} results = due_repayement_loans_totals(loan_filter,extraFilters) 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_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["princ_paid"] += group_totals["princ_paid"] branch_totals["int_paid"] += group_totals["int_paid"] branch_totals["penalty_paid"] += group_totals["penalty_paid"] branch_totals['total_paid'] += group_totals["total_paid"] branch_totals["principal_due"] += group_totals["principal_due"] branch_totals["interest_due"] += group_totals["interest_due"] branch_totals["penalty"] += group_totals["penalty"] branch_totals["total_due"] += group_totals["total_due"] branch_totals["total_prepaid"] += group_totals["total_prepaid"] grand_totals["princ_paid"] += group_totals["princ_paid"] grand_totals["int_paid"] += group_totals["int_paid"] grand_totals["penalty_paid"] += group_totals["penalty_paid"] grand_totals['total_paid'] += group_totals["total_paid"] grand_totals["principal_due"] += group_totals["principal_due"] grand_totals["interest_due"] += group_totals["interest_due"] grand_totals["penalty"] += group_totals["penalty"] grand_totals["total_due"] += group_totals["total_due"] grand_totals["total_prepaid"] += group_totals["total_prepaid"] if len(sub_section) > 0: section.append({ "id":loan_branch.id, "name":loan_branch.name, "section_data":sub_section, "section_total":branch_totals }) return {"total":len(section),"data":section,"grand_total":grand_totals}