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 cleared_off_loans_totals(loansfilter,extraFilters = {}): loan_data = [] loan_totals = { "principal_expected":0,"interest_expected":0, } loans = filter_loan_balances(loansfilter,extraFilters) if loans: for loan in loans: 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"] }) loan_totals["principal_expected"] += loan["loan_princ"] loan_totals["interest_expected"] += loan["loan_interest"] return {"loan_data":loan_data,"loan_totals":loan_totals} def filter_cleared_off_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 = {"search":search,"end":make_aware(datetime.strptime(report_filters['as_at'], '%Y-%m-%d %H:%M:%S'))} loan_filter = {"clear_off_date__lte":report_filters['as_at'],"is_deleted":False,"status":"cleared_off","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) cleared_off_loans = paginator.paginate_queryset(loans, request) result = filter_cleared_off_loans_officer_client_type(filter_1,filter_2,cleared_off_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) cleared_off_loans = paginator.paginate_queryset(loans, request) result = filter_cleared_off_loans_officer_gender(filter_1,filter_2,cleared_off_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) cleared_off_loans = paginator.paginate_queryset(loans, request) result = filter_cleared_off_loans_officer_branch(filter_1,filter_2,cleared_off_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) cleared_off_loans = paginator.paginate_queryset(loans, request) result = filter_cleared_off_loans_product_client_type(filter_1,filter_2,cleared_off_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) cleared_off_loans = paginator.paginate_queryset(loans, request) result = filter_cleared_off_loans_product_gender(filter_1,filter_2,cleared_off_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) cleared_off_loans = paginator.paginate_queryset(loans, request) result = filter_cleared_off_loans_product_branch(filter_1,filter_2,cleared_off_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) cleared_off_loans = paginator.paginate_queryset(loans, request) result = filter_cleared_off_loans_product_officer(filter_1,filter_2,cleared_off_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) cleared_off_loans = paginator.paginate_queryset(loans, request) result = filter_cleared_off_loans_product_sector(filter_1,filter_2,cleared_off_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) cleared_off_loans = paginator.paginate_queryset(loans, request) result = filter_cleared_off_loans_gender_branch(filter_1,filter_2,cleared_off_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) cleared_off_loans = paginator.paginate_queryset(loans, request) result = filter_cleared_off_loans_group_branch(filter_1,filter_2,cleared_off_loans,report_filters['as_at']) return Response({"results":result,"count":loans_count}) def filter_cleared_off_loans_officer_client_type(officer_ids,client_type_ids,cleared_off_loans,as_at): section = [] grand_totals = { "principal_expected":0,"interest_expected":0 } cleared_off_loan_ids = [] if cleared_off_loans: for cleared_off_loan in cleared_off_loans: cleared_off_loan_ids.append(cleared_off_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 } for client_type_id in client_type_ids: client_type = CustomerType.objects.get(id=client_type_id) loan_filter = {"clear_off_date__lte":as_at,"loan_officer_id":loan_officer.id,"customer_type_id":client_type_id,"id__in":cleared_off_loan_ids,"status":"cleared_off"} extra_filters = {"end":as_at} results = cleared_off_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"] grand_totals["principal_expected"] += client_totals["principal_expected"] grand_totals["interest_expected"] += client_totals["interest_expected"] 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_cleared_off_loans_officer_gender(officer_ids,gender_list,cleared_off_loans,as_at): section = [] grand_totals = { "principal_expected":0,"interest_expected":0 } cleared_off_loan_ids = [] if cleared_off_loans: for cleared_off_loan in cleared_off_loans: cleared_off_loan_ids.append(cleared_off_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 } for gender in gender_list: gender_name = 'Male' if gender == 'F': gender_name = 'Female' elif gender == 'O': gender_name = 'Other' loan_filter = {"clear_off_date__lte":as_at,"loan_officer_id":loan_officer.id,"gender":gender,"id__in":cleared_off_loan_ids,"status":"cleared_off"} extra_filters = {"end":as_at} results = cleared_off_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"] grand_totals["principal_expected"] += gender_totals["principal_expected"] grand_totals["interest_expected"] += gender_totals["interest_expected"] 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_cleared_off_loans_officer_branch(officer_ids,branch_ids,cleared_off_loans,as_at): section = [] grand_totals = { "principal_expected":0,"interest_expected":0 } cleared_off_loan_ids = [] if cleared_off_loans: for cleared_off_loan in cleared_off_loans: cleared_off_loan_ids.append(cleared_off_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 } for officer_id in officer_ids: loan_officer = Staff.objects.get(pk=officer_id) loan_filter = {"clear_off_date__lte":as_at,"loan_officer_id":loan_officer.id,"branch_id":loan_branch.id,"id__in":cleared_off_loan_ids,"status":"cleared_off"} extra_filters = {"end":as_at} results = cleared_off_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"] grand_totals["principal_expected"] += officers_totals["principal_expected"] grand_totals["interest_expected"] += officers_totals["interest_expected"] 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_cleared_off_loans_product_client_type(product_ids,client_type_ids,cleared_off_loans,as_at): section = [] grand_totals = { "principal_expected":0,"interest_expected":0 } cleared_off_loan_ids = [] if cleared_off_loans: for cleared_off_loan in cleared_off_loans: cleared_off_loan_ids.append(cleared_off_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 } for client_type_id in client_type_ids: client_type = CustomerType.objects.get(id=client_type_id) loan_filter = {"clear_off_date__lte":as_at,"loan_product_id":loan_product.id,"customer_type_id":client_type_id,"id__in":cleared_off_loan_ids,"status":"cleared_off"} extra_filters = {"end":as_at} results = cleared_off_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"] grand_totals["principal_expected"] += client_totals["principal_expected"] grand_totals["interest_expected"] += client_totals["interest_expected"] 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_cleared_off_loans_product_gender(product_ids,gender_list,cleared_off_loans,as_at): section = [] grand_totals = { "principal_expected":0,"interest_expected":0 } cleared_off_loan_ids = [] if cleared_off_loans: for cleared_off_loan in cleared_off_loans: cleared_off_loan_ids.append(cleared_off_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 } for gender in gender_list: gender_name = 'Male' if gender == 'F': gender_name = 'Female' elif gender == 'O': gender_name = 'Other' loan_filter = {"clear_off_date__lte":as_at,"loan_product_id":loan_officer.id,"gender":gender,"id__in":cleared_off_loan_ids,"status":"cleared_off"} extra_filters = {"end":as_at} results = cleared_off_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"] grand_totals["principal_expected"] += gender_totals["principal_expected"] grand_totals["interest_expected"] += gender_totals["interest_expected"] 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_cleared_off_loans_product_branch(product_ids,branch_ids,cleared_off_loans,as_at): section = [] grand_totals = { "principal_expected":0,"interest_expected":0 } cleared_off_loan_ids = [] if cleared_off_loans: for cleared_off_loan in cleared_off_loans: cleared_off_loan_ids.append(cleared_off_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 } for product_id in product_ids: loan_product = LoanProduct.objects.get(pk=product_id) loan_filter = {"clear_off_date__lte":as_at,"loan_product_id":loan_product.id,"branch_id":loan_branch.id,"id__in":cleared_off_loan_ids,"status":"cleared_off"} extra_filters = {"end":as_at} results = cleared_off_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"] grand_totals["principal_expected"] += products_totals["principal_expected"] grand_totals["interest_expected"] += products_totals["interest_expected"] 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_cleared_off_loans_product_officer(product_ids,officer_ids,cleared_off_loans,as_at): section = [] grand_totals = { "principal_expected":0,"interest_expected":0, "principal_balance":0,"principal_due":0,"interest_due":0,"total_due":0, } cleared_off_loan_ids = [] if cleared_off_loans: for cleared_off_loan in cleared_off_loans: cleared_off_loan_ids.append(cleared_off_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 } for product_id in product_ids: loan_product = LoanProduct.objects.get(pk=product_id) loan_filter = {"clear_off_date__lte":as_at,"loan_product_id":loan_product.id,"loan_officer_id":loan_officer.id,"id__in":cleared_off_loan_ids,"status":"cleared_off"} extra_filters = {"end":as_at} results = cleared_off_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"] grand_totals["principal_expected"] += products_totals["principal_expected"] grand_totals["interest_expected"] += products_totals["interest_expected"] 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_cleared_off_loans_product_sector(product_ids,sector_ids,cleared_off_loans,as_at): section = [] grand_totals = { "principal_expected":0,"interest_expected":0 } cleared_off_loan_ids = [] if cleared_off_loans: for cleared_off_loan in cleared_off_loans: cleared_off_loan_ids.append(cleared_off_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 } for product_id in product_ids: loan_product = LoanProduct.objects.get(pk=product_id) loan_filter = {"clear_off_date__lte":as_at,"loan_product_id":loan_product.id,"loan_sector_id":loan_sector.id,"id__in":cleared_off_loan_ids,"status":"cleared_off"} extra_filters = {"end":as_at} results = cleared_off_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"] grand_totals["principal_expected"] += products_totals["principal_expected"] grand_totals["interest_expected"] += products_totals["interest_expected"] 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_cleared_off_loans_gender_branch(branch_ids,gender_list,cleared_off_loans,as_at): section = [] grand_totals = { "principal_expected":0,"interest_expected":0 } cleared_off_loan_ids = [] if cleared_off_loans: for cleared_off_loan in cleared_off_loans: cleared_off_loan_ids.append(cleared_off_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 } for gender in gender_list: gender_name = 'Male' if gender == 'F': gender_name = 'Female' elif gender == 'O': gender_name = 'Other' loan_filter = {"clear_off_date__lte":as_at,"branch_id":loan_branch.id,"gender":gender,"id__in":cleared_off_loan_ids,"status":"cleared_off"} extra_filters = {"end":as_at} results = cleared_off_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) }) branch_totals["principal_expected"] += gender_totals["principal_expected"] branch_totals["interest_expected"] += gender_totals["interest_expected"] grand_totals["principal_expected"] += gender_totals["principal_expected"] grand_totals["interest_expected"] += gender_totals["interest_expected"] 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_cleared_off_loans_group_branch(branch_ids,group_ids,cleared_off_loans,as_at): section = [] grand_totals = { "principal_expected":0,"interest_expected":0 } cleared_off_loan_ids = [] if cleared_off_loans: for cleared_off_loan in cleared_off_loans: cleared_off_loan_ids.append(cleared_off_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 } 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 = {"clear_off_date__lte":as_at,"customer_id__in":customer_ids,"branch_id":loan_branch.id,"id__in":cleared_off_loan_ids,"status":"cleared_off"} extra_filters = {"end":as_at} results = cleared_off_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"] grand_totals["principal_expected"] += group_totals["principal_expected"] grand_totals["interest_expected"] += group_totals["interest_expected"] 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}