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 dued_loans_totals(loansfilter,extraFilters = {}): loan_data = [] loan_totals = { "principal_expected":0,"interest_expected":0,"principal_balance":0, "principal_due":0,"interest_due":0,"total_due":0, } loans = filter_loan_balances(loansfilter,extraFilters) if loans: for loan in loans: princ_arr_age = 0 principal_due = loan["principal_due"] interest_due = loan["interest_due"] outstanding_princ = loan["principal_balance"] if interest_due < 0: interest_due = 0 if principal_due > 0: princ_arr_age = (principal_due/outstanding_princ ) * 100 if outstanding_princ > 0 else 0 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 and princ_arr_age > 0: loan_data.append({ "member_number":loan["member_number"], "name":loan["name"], "telephone":loan["telephone"], "id":loan["id"], "loan_start_date":loan["loan_start_date"], "loan_end_date":loan["loan_end_date"], "status":loan["status"], "principal_expected":loan["loan_princ"], "interest_expected":loan["loan_interest"], "principal_balance":outstanding_princ, "principal_due":principal_due, "interest_due":interest_due, "total_due":principal_due + interest_due, "arrear_days":arrear_days, "princ_arr_age":princ_arr_age }) loan_totals["principal_expected"] += loan["loan_princ"] loan_totals["interest_expected"] += loan["loan_interest"] loan_totals["principal_balance"] += loan["principal_balance"] loan_totals["principal_due"] += principal_due loan_totals["interest_due"] += interest_due loan_totals["total_due"] += principal_due +interest_due return {"loan_data":loan_data,"loan_totals":loan_totals} def filter_arrear_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":"arrears","search":search,"end":make_aware(datetime.strptime(report_filters['as_at'], '%Y-%m-%d %H:%M:%S'))} loan_filter = {"loan_arrear_date__lte":make_aware(datetime.strptime(report_filters['as_at'], '%Y-%m-%d %H:%M:%S')),"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['as_at']) 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['as_at']) 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['as_at']) 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['as_at']) 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['as_at']) 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['as_at']) 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['as_at']) 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['as_at']) 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['as_at']) 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['as_at']) return Response({"results":result,"count":loans_count}) def filter_arrears_officer_client_type(officer_ids,client_type_ids,dued_loans,as_at): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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 = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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":"arrears","end":as_at} results = dued_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_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["principal_expected"] += client_totals["principal_expected"] officers_totals["interest_expected"] += client_totals["interest_expected"] officers_totals["principal_balance"] += client_totals["principal_balance"] officers_totals["principal_due"] += client_totals["principal_due"] officers_totals["interest_due"] += client_totals["interest_due"] officers_totals["total_due"] += client_totals["total_due"] grand_totals["principal_expected"] += client_totals["principal_expected"] grand_totals["interest_expected"] += client_totals["interest_expected"] grand_totals["principal_balance"] += client_totals["principal_balance"] grand_totals["principal_due"] += client_totals["principal_due"] grand_totals["interest_due"] += client_totals["interest_due"] grand_totals["total_due"] += client_totals["total_due"] 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,as_at): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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 = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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":"arrears","end":as_at} results = dued_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_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["principal_expected"] += gender_totals["principal_expected"] officers_totals["interest_expected"] += gender_totals["interest_expected"] officers_totals["principal_balance"] += gender_totals["principal_balance"] officers_totals["principal_due"] += gender_totals["principal_due"] officers_totals["interest_due"] += gender_totals["interest_due"] officers_totals["total_due"] += gender_totals["total_due"] grand_totals["principal_expected"] += gender_totals["principal_expected"] grand_totals["interest_expected"] += gender_totals["interest_expected"] grand_totals["principal_balance"] += gender_totals["principal_balance"] grand_totals["principal_due"] += gender_totals["principal_due"] grand_totals["interest_due"] += gender_totals["interest_due"] grand_totals["total_due"] += gender_totals["total_due"] 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,as_at): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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":"arrears","end":as_at} results = dued_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_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["principal_expected"] += officers_totals["principal_expected"] branch_totals["interest_expected"] += officers_totals["interest_expected"] branch_totals["principal_balance"] += officers_totals["principal_balance"] branch_totals["principal_due"] += officers_totals["principal_due"] branch_totals["interest_due"] += officers_totals["interest_due"] branch_totals["total_due"] += officers_totals["total_due"] grand_totals["principal_expected"] += officers_totals["principal_expected"] grand_totals["interest_expected"] += officers_totals["interest_expected"] grand_totals["principal_balance"] += officers_totals["principal_balance"] grand_totals["principal_due"] += officers_totals["principal_due"] grand_totals["interest_due"] += officers_totals["interest_due"] grand_totals["total_due"] += officers_totals["total_due"] 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,as_at): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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 = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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":"arrears","end":as_at} results = dued_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_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["principal_expected"] += client_totals["principal_expected"] loan_products_totals["interest_expected"] += client_totals["interest_expected"] loan_products_totals["principal_balance"] += client_totals["principal_balance"] loan_products_totals["principal_due"] += client_totals["principal_due"] loan_products_totals["interest_due"] += client_totals["interest_due"] loan_products_totals["total_due"] += client_totals["total_due"] grand_totals["principal_expected"] += client_totals["principal_expected"] grand_totals["interest_expected"] += client_totals["interest_expected"] grand_totals["principal_balance"] += client_totals["principal_balance"] grand_totals["principal_due"] += client_totals["principal_due"] grand_totals["interest_due"] += client_totals["interest_due"] grand_totals["total_due"] += client_totals["total_due"] 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,as_at): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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 = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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":"arrears","end":as_at} results = dued_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_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["principal_expected"] += gender_totals["principal_expected"] product_totals["interest_expected"] += gender_totals["interest_expected"] product_totals["principal_balance"] += gender_totals["principal_balance"] product_totals["principal_due"] += gender_totals["principal_due"] product_totals["interest_due"] += gender_totals["interest_due"] product_totals["total_due"] += gender_totals["total_due"] grand_totals["principal_expected"] += gender_totals["principal_expected"] grand_totals["interest_expected"] += gender_totals["interest_expected"] grand_totals["principal_balance"] += gender_totals["principal_balance"] grand_totals["principal_due"] += gender_totals["principal_due"] grand_totals["interest_due"] += gender_totals["interest_due"] grand_totals["total_due"] += gender_totals["total_due"] 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,as_at): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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":"arrears","end":as_at} results = dued_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_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["principal_expected"] += products_totals["principal_expected"] branch_totals["interest_expected"] += products_totals["interest_expected"] branch_totals["principal_balance"] += products_totals["principal_balance"] branch_totals["principal_due"] += products_totals["principal_due"] branch_totals["interest_due"] += products_totals["interest_due"] branch_totals["total_due"] += products_totals["total_due"] grand_totals["principal_expected"] += products_totals["principal_expected"] grand_totals["interest_expected"] += products_totals["interest_expected"] grand_totals["principal_balance"] += products_totals["principal_balance"] grand_totals["principal_due"] += products_totals["principal_due"] grand_totals["interest_due"] += products_totals["interest_due"] grand_totals["total_due"] += products_totals["total_due"] 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,as_at): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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 = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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":"arrears","end":as_at} results = dued_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_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["principal_expected"] += products_totals["principal_expected"] officer_totals["interest_expected"] += products_totals["interest_expected"] officer_totals["principal_balance"] += products_totals["principal_balance"] officer_totals["principal_due"] += products_totals["principal_due"] officer_totals["interest_due"] += products_totals["interest_due"] officer_totals["total_due"] += products_totals["total_due"] grand_totals["principal_expected"] += products_totals["principal_expected"] grand_totals["interest_expected"] += products_totals["interest_expected"] grand_totals["principal_balance"] += products_totals["principal_balance"] grand_totals["principal_due"] += products_totals["principal_due"] grand_totals["interest_due"] += products_totals["interest_due"] grand_totals["total_due"] += products_totals["total_due"] 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,as_at): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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 = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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":"arrears","end":as_at} results = dued_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_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["principal_expected"] += products_totals["principal_expected"] sector_totals["interest_expected"] += products_totals["interest_expected"] sector_totals["principal_balance"] += products_totals["principal_balance"] sector_totals["principal_due"] += products_totals["principal_due"] sector_totals["interest_due"] += products_totals["interest_due"] sector_totals["total_due"] += products_totals["total_due"] grand_totals["principal_expected"] += products_totals["principal_expected"] grand_totals["interest_expected"] += products_totals["interest_expected"] grand_totals["principal_balance"] += products_totals["principal_balance"] grand_totals["principal_due"] += products_totals["principal_due"] grand_totals["interest_due"] += products_totals["interest_due"] grand_totals["total_due"] += products_totals["total_due"] 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,as_at): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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":"arrears","end":as_at} results = dued_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_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["principal_expected"] += gender_totals["principal_expected"] branch_totals["interest_expected"] += gender_totals["interest_expected"] branch_totals["principal_balance"] += gender_totals["principal_balance"] branch_totals["principal_due"] += gender_totals["principal_due"] branch_totals["interest_due"] += gender_totals["interest_due"] branch_totals["total_due"] += gender_totals["total_due"] grand_totals["principal_expected"] += gender_totals["principal_expected"] grand_totals["interest_expected"] += gender_totals["interest_expected"] grand_totals["principal_balance"] += gender_totals["principal_balance"] grand_totals["principal_due"] += gender_totals["principal_due"] grand_totals["interest_due"] += gender_totals["interest_due"] grand_totals["total_due"] += gender_totals["total_due"] 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,as_at): section = [] dued_loan_list = {} dued_loan_ids = [] grand_totals = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":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_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_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":"arrears","end":as_at} results = dued_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_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["principal_expected"] += group_totals["principal_expected"] branch_totals["interest_expected"] += group_totals["interest_expected"] branch_totals["principal_balance"] += group_totals["principal_balance"] branch_totals["principal_due"] += group_totals["principal_due"] branch_totals["interest_due"] += group_totals["interest_due"] branch_totals["total_due"] += group_totals["total_due"] grand_totals["principal_expected"] += group_totals["principal_expected"] grand_totals["interest_expected"] += group_totals["interest_expected"] grand_totals["principal_balance"] += group_totals["principal_balance"] grand_totals["principal_due"] += group_totals["principal_due"] grand_totals["interest_due"] += group_totals["interest_due"] grand_totals["total_due"] += group_totals["total_due"] 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}