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 loan_due_installment_totals(loansfilter):
    loan_data   = []
    loan_totals = {
        "principal_due":0,"interest_due":0,"penalty":0,
        "princ_paid":0,"int_paid":0,"total_due":0,"total_paid":0,"progress":0
    }
    loans = LoanRepaymentview.objects.filter(**loansfilter["filter"]).values(
        "member_number","name","telephone","id","loan_start_date","loan_end_date","status").annotate(interest_balance=Sum('interest_balance'),principal_balance=Sum('principal_balance'),penalty_balance=Sum('penalty_balance'),princ_paid=Sum('princ_paid'),int_paid=Sum('int_paid'),penalty_paid=Sum('penalty_paid'),total_due=Sum('loan_balance'),total_paid=Sum('paid_amount'),schedule_expected_date=Min('schedule_expected_date'),first_arrear_date=Min('loan_arrear_date'),)
    if loans:
        for loan in loans:
            progress = 0
            if loan['loan_balance'] > 0:
                progress = (loan['total_paid']/loan['loan_balance'])* 100

            arrear_days =  (datetime.strptime(loansfilter["end"].split(' ')[0],"%Y-%m-%d") - datetime.strptime(loan["first_arrear_date"].strftime('%Y-%m-%d'),'%Y-%m-%d')).days - 1
            loan_data.append({
                "member_number":loan["member_number"],
                "name":loan["name"],
                "telephone":loan["telephone"],
                "id":loan["id"],
                "loan_due_date":loan["first_arrear_date"],
                "schedule_expected_date":loan["schedule_expected_date"],
                "principal_due":loan['principal_balance'],
                "interest_due":loan['interest_balance'],
                "penalty":loan['penalty_balance'],
                "total_due":loan['loan_balance'],
                "princ_paid":loan['princ_paid'],
                "int_paid":loan['int_paid'],
                "penalty_paid":loan['penalty_paid'],
                "total_paid":loan['total_paid'],
                "arrear_days":arrear_days,
                "progress":progress
            })
            loan_totals["principal_due"] += loan['principal_balance']
            loan_totals["interest_due"]  += loan['interest_balance']
            loan_totals["penalty"]       += loan['penalty_balance']
            loan_totals["total_due"]     += loan['loan_balance']

            loan_totals["princ_paid"]   += loan['princ_paid']
            loan_totals["int_paid"]     += loan['int_paid']
            loan_totals["penalty_paid"] += loan['penalty_paid']
            loan_totals["total_paid"]   += loan['total_paid']
            loan_totals["progress"]     += progress
            
    return {"loan_data":loan_data,"loan_totals":loan_totals}

def filter_due_installment_reports(report_filters):
        loans       = []
        loans_count = 0
        result    = {}
        filter_1  = report_filters['filter_1']
        filter_2  = report_filters['filter_2']
        search    = report_filters['search']
        
        page_size     = report_filters['page_size']
        loan_filter   = report_filters['loan_filter']
        report_filter = report_filters['report_filter']
        request       = report_filters['request']
    
        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
            if search and len(search) > 0:
                loans = LoanRepaymentview.objects.filter(Q(member_number__icontains=search) | Q(old_member_number__icontains=search) |  Q(name__icontains=search),**loan_filter).distinct("id")
            else:
                loans = LoanRepaymentview.objects.filter(**loan_filter).distinct("id")
            loans_count = len(loans)
            dued_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_expected_repayment_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
            if search and len(search) > 0:
                loans = LoanRepaymentview.objects.filter(Q(member_number__icontains=search) | Q(old_member_number__icontains=search) |  Q(name__icontains=search),**loan_filter).distinct("id")
            else:
                loans = LoanRepaymentview.objects.filter(**loan_filter).distinct("id")
            loans_count = len(loans)
            dued_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_expected_repayment_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
            if search and len(search) > 0:
                loans = LoanRepaymentview.objects.filter(Q(member_number__icontains=search) | Q(old_member_number__icontains=search) |  Q(name__icontains=search),**loan_filter).distinct("id")
            else:
                loans = LoanRepaymentview.objects.filter(**loan_filter).distinct("id")
            loans_count = len(loans)
            dued_loans = paginator.paginate_queryset(loans, request)
            result     = filter_expected_repayment_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
            if search and len(search) > 0:
                loans = LoanRepaymentview.objects.filter(Q(member_number__icontains=search) | Q(old_member_number__icontains=search) |  Q(name__icontains=search),**loan_filter).distinct("id")
            else:
                loans = LoanRepaymentview.objects.filter(**loan_filter).distinct("id")
            loans_count = len(loans)
            dued_loans = paginator.paginate_queryset(loans, request)
            result     = filter_expected_repayment_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
            if search and len(search) > 0:
                loans = LoanRepaymentview.objects.filter(Q(member_number__icontains=search) | Q(old_member_number__icontains=search) |  Q(name__icontains=search),**loan_filter).distinct("id")
            else:
                loans = LoanRepaymentview.objects.filter(**loan_filter).distinct("id")
            loans_count = len(loans)
            dued_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_expected_repayment_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
            if search and len(search) > 0:
                loans = LoanRepaymentview.objects.filter(Q(member_number__icontains=search) | Q(old_member_number__icontains=search) |  Q(name__icontains=search),**loan_filter).distinct("id")
            else:
                loans = LoanRepaymentview.objects.filter(**loan_filter).distinct("id")
            loans_count = len(loans)
            dued_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_expected_repayment_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
            if search and len(search) > 0:
                loans = LoanRepaymentview.objects.filter(Q(member_number__icontains=search) | Q(old_member_number__icontains=search) |  Q(name__icontains=search),**loan_filter).distinct("id")
            else:
                loans = LoanRepaymentview.objects.filter(**loan_filter).distinct("id")
            loans_count = len(loans)
            dued_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_expected_repayment_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
            if search and len(search) > 0:
                loans = LoanRepaymentview.objects.filter(Q(member_number__icontains=search) | Q(old_member_number__icontains=search) |  Q(name__icontains=search),**loan_filter).distinct("id")
            else:
                loans = LoanRepaymentview.objects.filter(**loan_filter).distinct("id")
            loans_count = len(loans)
            dued_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_expected_repayment_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
            if search and len(search) > 0:
                loans = LoanRepaymentview.objects.filter(Q(member_number__icontains=search) | Q(old_member_number__icontains=search) |  Q(name__icontains=search),**loan_filter).distinct("id")
            else:
                loans = LoanRepaymentview.objects.filter(**loan_filter).distinct("id")
            loans_count = len(loans)
            dued_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_expected_repayment_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
            if search and len(search) > 0:
                loans = LoanRepaymentview.objects.filter(Q(member_number__icontains=search) | Q(old_member_number__icontains=search) |  Q(name__icontains=search),**loan_filter).distinct("id")
            else:
                loans = LoanRepaymentview.objects.filter(**loan_filter).distinct("id")
            loans_count = len(loans)
            dued_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_expected_repayment_group_branch(filter_1,filter_2,dued_loans,report_filters['start'],report_filters['end'])
        return Response({"results":result,"count":loans_count})

def filter_expected_repayment_officer_client_type(officer_ids,client_type_ids,dued_loans,start,end):
    section        = []
    dued_loan_list = {}
    dued_loan_ids  = []
    grand_totals   = {
       "principal_due":0,"interest_due":0,"penalty":0,
       "princ_paid":0,"int_paid":0,"total_due":0,"total_paid":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_due":0,"interest_due":0,"penalty":0,
                "princ_paid":0,"int_paid":0,"total_due":0,"total_paid":0
            }

            for client_type_id in client_type_ids:
                client_type      = CustomerType.objects.get(id=client_type_id)
                loan_filter = {"loan_officer_id":loan_officer.id,"customer_type_id":client_type_id,"id__in":dued_loan_ids,"status":"disbursed"}
                loan__main_filter = {"filter":loan_filter,"dued_loan_list":dued_loan_list,"start":start,"end":end}
                results = loan_due_installment_totals(loan__main_filter)

                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_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["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']

                        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["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']

            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_expected_repayment_officer_gender(officer_ids,gender_list,dued_loans,start,end):
    section        = []
    dued_loan_list = {}
    dued_loan_ids  = []
    grand_totals   = {
       "principal_due":0,"interest_due":0,"penalty":0,
        "princ_paid":0,"int_paid":0,"total_due":0,"total_paid":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_due":0,"interest_due":0,"penalty":0,
                "princ_paid":0,"int_paid":0,"total_due":0,"total_paid":0
            }

            for gender in gender_list:
                gender_name = 'Male'
                if gender == 'F':
                    gender_name = 'Female'
                elif gender == 'O':
                    gender_name = 'Other'

                loan_filter = {"loan_officer_id":loan_officer.id,"gender":gender,"id__in":dued_loan_ids,"status":"disbursed"}
                loan__main_filter = {"filter":loan_filter,"dued_loan_list":dued_loan_list,"start":start,"end":end}
                results     = loan_due_installment_totals(loan__main_filter)
                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_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["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']

                    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["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']


            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_expected_repayment_officer_branch(officer_ids,branch_ids,dued_loans,start,end):
    section        = []
    dued_loan_list = {}
    dued_loan_ids  = []
    grand_totals   = {
      "principal_due":0,"interest_due":0,"penalty":0,
      "princ_paid":0,"int_paid":0,"total_due":0,"total_paid":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_due":0,"interest_due":0,"penalty":0,
               "princ_paid":0,"int_paid":0,"total_due":0,"total_paid":0
            }

            for officer_id in officer_ids:
                loan_officer    = Staff.objects.get(pk=officer_id)
                loan_filter = {"loan_officer_id":loan_officer.id,"branch_id":loan_branch.id,"id__in":dued_loan_ids,"status":"disbursed"}
                loan__main_filter = {"filter":loan_filter,"dued_loan_list":dued_loan_list,"start":start,"end":end}
                results = loan_due_installment_totals(loan__main_filter)

                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_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["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'] 

                        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["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'] 

            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_expected_repayment_product_client_type(product_ids,client_type_ids,dued_loans,start,end):
    section        = []
    dued_loan_list = {}
    dued_loan_ids  = []
    grand_totals   = {
        "principal_due":0,"interest_due":0,"penalty":0,
        "princ_paid":0,"int_paid":0,"total_due":0,"total_paid":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_due":0,"interest_due":0,"penalty":0,
                "princ_paid":0,"int_paid":0,"total_due":0,"total_paid":0
            }

            for client_type_id in client_type_ids:
                client_type      = CustomerType.objects.get(id=client_type_id)
                loan_filter = {"loan_product_id":loan_product.id,"customer_type_id":client_type_id,"id__in":dued_loan_ids,"status":"disbursed"}
                loan__main_filter = {"filter":loan_filter,"dued_loan_list":dued_loan_list,"start":start,"end":end}
                results = loan_due_installment_totals(loan__main_filter)

                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_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["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'] 

                        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["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'] 

            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_expected_repayment_product_gender(product_ids,gender_list,dued_loans,start,end):
    section        = []
    dued_loan_list = {}
    dued_loan_ids  = []
    grand_totals   = {
        "principal_balance":0,"interest_balance":0,"penalty":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_balance":0,"interest_balance":0,"penalty":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_product_id":loan_officer.id,"gender":gender,"id__in":dued_loan_ids,"status":"disbursed"}
                loan__main_filter = {"filter":loan_filter,"dued_loan_list":dued_loan_list,"start":start,"end":end}
                results     = loan_due_installment_totals(loan__main_filter)
                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_balance"] += gender_totals["principal_balance"]
                    product_totals["interest_balance"]  += gender_totals["interest_balance"]
                    product_totals["penalty"]        += gender_totals["penalty"]
                    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_balance"] += gender_totals["principal_balance"]
                    grand_totals["interest_balance"]  += gender_totals["interest_balance"]
                    grand_totals["penalty"]  += gender_totals["penalty"]
                    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_expected_repayment_product_branch(product_ids,branch_ids,dued_loans,start,end):
    section        = []
    dued_loan_list = {}
    dued_loan_ids  = []
    grand_totals   = {
       "principal_balance":0,"interest_balance":0,"penalty":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_balance":0,"interest_balance":0,"penalty":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_product_id":loan_product.id,"branch_id":loan_branch.id,"id__in":dued_loan_ids,"status":"disbursed"}
                loan__main_filter = {"filter":loan_filter,"dued_loan_list":dued_loan_list,"start":start,"end":end}
                results = loan_due_installment_totals(loan__main_filter)

                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_balance"] += products_totals["principal_balance"]
                        branch_totals["interest_balance"]  += products_totals["interest_balance"]
                        branch_totals["penalty"]        += products_totals["penalty"]
                        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_balance"] += products_totals["principal_balance"]
                        grand_totals["interest_balance"]  += products_totals["interest_balance"]
                        grand_totals["penalty"]  += products_totals["penalty"]
                        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_expected_repayment_product_officer(product_ids,officer_ids,dued_loans,start,end):
    section        = []
    dued_loan_list = {}
    dued_loan_ids  = []
    grand_totals   = {
        "principal_balance":0,"interest_balance":0,"penalty":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_balance":0,"interest_balance":0,"penalty":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_product_id":loan_product.id,"loan_officer_id":loan_officer.id,"id__in":dued_loan_ids,"status":"disbursed"}
                loan__main_filter = {"filter":loan_filter,"dued_loan_list":dued_loan_list,"start":start,"end":end}
                results = loan_due_installment_totals(loan__main_filter)

                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_balance"] += products_totals["principal_balance"]
                        officer_totals["interest_balance"]  += products_totals["interest_balance"]
                        officer_totals["penalty"]        += products_totals["penalty"]
                        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_balance"] += products_totals["principal_balance"]
                        grand_totals["interest_balance"]  += products_totals["interest_balance"]
                        grand_totals["penalty"]  += products_totals["penalty"]
                        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_expected_repayment_product_sector(product_ids,sector_ids,dued_loans,start,end):
    section        = []
    dued_loan_list = {}
    dued_loan_ids  = []
    grand_totals   = {
        "principal_balance":0,"interest_balance":0,"penalty":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_balance":0,"interest_balance":0,"penalty":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_product_id":loan_product.id,"loan_sector_id":loan_sector.id,"id__in":dued_loan_ids,"status":"disbursed"}
                loan__main_filter = {"filter":loan_filter,"dued_loan_list":dued_loan_list,"start":start,"end":end}
                results = loan_due_installment_totals(loan__main_filter)

                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_balance"] += products_totals["principal_balance"]
                        sector_totals["interest_balance"]  += products_totals["interest_balance"]
                        sector_totals["penalty"]        += products_totals["penalty"]
                        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_balance"] += products_totals["principal_balance"]
                        grand_totals["interest_balance"]  += products_totals["interest_balance"]
                        grand_totals["penalty"]  += products_totals["penalty"]
                        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_expected_repayment_gender_branch(branch_ids,gender_list,dued_loans,start,end):
        section        = []
        dued_loan_list = {}
        dued_loan_ids  = []
        grand_totals   = {
            "principal_balance":0,"interest_balance":0,"penalty":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_balance":0,"interest_balance":0,"penalty":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 = {"branch_id":loan_branch.id,"gender":gender,"id__in":dued_loan_ids,"status":"disbursed"}
                    loan__main_filter = {"filter":loan_filter,"dued_loan_list":dued_loan_list,"start":start,"end":end}
                    results     = loan_due_installment_totals(loan__main_filter)
                    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_balance"] += gender_totals["principal_balance"]
                        branch_totals["interest_balance"]  += gender_totals["interest_balance"]
                        branch_totals["penalty"]        += gender_totals["penalty"]
                        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_balance"] += gender_totals["principal_balance"]
                        grand_totals["interest_balance"]  += gender_totals["interest_balance"]
                        grand_totals["penalty"]  += gender_totals["penalty"]
                        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_expected_repayment_group_branch(branch_ids,group_ids,dued_loans,start,end):
    section        = []
    dued_loan_list = {}
    dued_loan_ids  = []
    grand_totals   = {
       "principal_balance":0,"interest_balance":0,"penalty":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_balance":0,"interest_balance":0,"penalty":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 = {"customer_id__in":customer_ids,"branch_id":loan_branch.id,"id__in":dued_loan_ids,"status":"disbursed"}
                loan__main_filter = {"filter":loan_filter,"dued_loan_list":dued_loan_list,"start":start,"end":end}
                results = loan_due_installment_totals(loan__main_filter)

                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_balance"] += group_totals["principal_balance"]
                        branch_totals["interest_balance"]  += group_totals["interest_balance"]
                        branch_totals["penalty"]        += group_totals["penalty"]
                        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_balance"] += group_totals["principal_balance"]
                        grand_totals["interest_balance"]  += group_totals["interest_balance"]
                        grand_totals["penalty"]  += group_totals["penalty"]
                        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}