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 written_off_loans_totals(loansfilter):
    loan_data   = []
    loan_totals = {
        "principal_expected":0,"interest_expected":0,"principal_balance":0,
        "interest_balance":0,"amount_recovery":0
    }
    loans = LoanRepaymentview.objects.filter(**loansfilter["filter"]).values(
        "member_number","name","id","loan_write_off_date","status").annotate(principal_expected=Sum('schedule_principal_expected'),interest_expected=Sum('schedule_interest_expected'),principal_balance=Sum('principal_balance'),interest_balance=Sum('interest_balance'))
    if loans:
        for loan in loans:
            loan_data.append({
                "member_number":loan["member_number"],
                "name":loan["name"],
                "telephone":loan["telephone"],
                "id":loan["id"],
                "loan_write_off_date":loan["loan_write_off_date"],
                "amount_recovery":loan["amount_recovery"],
                "principal_expected":loan["principal_expected"],
                "interest_expected":loan["interest_expected"],
                "principal_balance":loan["principal_balance"],
                "interest_balance":loan["interest_balance"],
                "recovery_status":"---",
            })
            loan_totals["principal_expected"] += loan["principal_expected"]
            loan_totals["interest_expected"]  += loan["interest_expected"]
            loan_totals["principal_balance"]  += loan["principal_balance"]
            loan_totals["interest_balance"]   += loan["interest_balance"]
            loan_totals["amount_recovery"]    += loan["amount_recovery"]
    return {"loan_data":loan_data,"loan_totals":loan_totals}

def filter_written_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']
        loan_filter   = report_filters['loan_filter']
        report_filter = report_filters['report_filter']
        request       = report_filters['request']
        search       = report_filters['search']
        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)
            written_off_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_written_offs_officer_client_type(filter_1,filter_2,written_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
            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)
            written_off_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_written_offs_officer_gender(filter_1,filter_2,written_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
            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)
            written_off_loans = paginator.paginate_queryset(loans, request)
            result     = filter_written_offs_officer_branch(filter_1,filter_2,written_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
            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)
            written_off_loans = paginator.paginate_queryset(loans, request)
            result     = filter_written_offs_product_client_type(filter_1,filter_2,written_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
            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)
            written_off_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_written_offs_product_gender(filter_1,filter_2,written_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
            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)
            written_off_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_written_offs_product_branch(filter_1,filter_2,written_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
            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)
            written_off_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_written_offs_product_officer(filter_1,filter_2,written_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
            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)
            written_off_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_written_offs_product_sector(filter_1,filter_2,written_off_loans,report_filters['as_at'])

        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)
            written_off_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_written_offs_gender_branch(filter_1,filter_2,written_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
            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)
            written_off_loans  = paginator.paginate_queryset(loans, request)
            result      = filter_written_offs_group_branch(filter_1,filter_2,written_off_loans,report_filters['as_at'])
        return Response({"results":result,"count":loans_count})
        
def filter_written_offs_officer_client_type(officer_ids,client_type_ids,written_off_loans,as_at):
    section        = []
    written_off_loan_list = {}
    written_off_loan_ids  = []
    grand_totals   = {
        "principal_expected":0,"interest_expected":0,"principal_balance":0,
        "interest_balance":0,"amount_recovery":0
    }

    if written_off_loans:
        for written_off_loan in written_off_loans:
            written_off_loan_list[written_off_loan.id] = written_off_loan
            written_off_loan_ids.append(written_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,"principal_balance":0,
                "interest_balance":0,"amount_recovery":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":written_off_loan_ids,"status":"written_off"}
                loan__main_filter = {"filter":loan_filter,"written_off_loan_list":written_off_loan_list,"as_at":as_at}
                results = written_off_loans_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_expected"] += client_totals["principal_expected"]
                        officers_totals["interest_expected"]  += client_totals["interest_expected"]
                        officers_totals["principal_balance"]  += client_totals["principal_balance"]
                        officers_totals["interest_balance"]   += client_totals["interest_balance"]
                        officers_totals["amount_recovery"]    += client_totals["amount_recovery"]

                        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["interest_balance"]   += client_totals["interest_balance"]
                        grand_totals["amount_recovery"]    += client_totals["amount_recovery"]

            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_written_offs_officer_gender(officer_ids,gender_list,written_off_loans,as_at):
    section        = []
    written_off_loan_list = {}
    written_off_loan_ids  = []
    grand_totals   = {
        "principal_expected":0,"interest_expected":0,"principal_balance":0,
        "interest_balance":0,"amount_recovery":0
    }

    if written_off_loans:
        for written_off_loan in written_off_loans:
            written_off_loan_list[written_off_loan.id] = written_off_loan
            written_off_loan_ids.append(written_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,"principal_balance":0,
                "interest_balance":0,"amount_recovery":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":written_off_loan_ids,"status":"written_off"}
                loan__main_filter = {"filter":loan_filter,"written_off_loan_list":written_off_loan_list,"as_at":as_at}
                results     = written_off_loans_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_expected"] += gender_totals["principal_expected"]
                    officers_totals["interest_expected"]  += gender_totals["interest_expected"]
                    officers_totals["principal_balance"]  += gender_totals["principal_balance"]
                    officers_totals["interest_balance"]   += gender_totals["interest_balance"]
                    officers_totals["amount_recovery"]    += gender_totals["amount_recovery"]

                    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["interest_balance"]   += gender_totals["interest_balance"]
                    grand_totals["amount_recovery"]    += gender_totals["amount_recovery"] 

            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_written_offs_officer_branch(officer_ids,branch_ids,written_off_loans,as_at):
    section        = []
    written_off_loan_list = {}
    written_off_loan_ids  = []
    grand_totals   = {
        "principal_expected":0,"interest_expected":0,"principal_balance":0,
        "interest_balance":0,"amount_recovery":0
    }

    if written_off_loans:
        for written_off_loan in written_off_loans:
            written_off_loan_list[written_off_loan.id] = written_off_loan
            written_off_loan_ids.append(written_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,"principal_balance":0,
                "interest_balance":0,"amount_recovery":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":written_off_loan_ids,"status":"written_off"}
                loan__main_filter = {"filter":loan_filter,"written_off_loan_list":written_off_loan_list,"as_at":as_at}
                results = written_off_loans_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_expected"] += officers_totals["principal_expected"]
                        branch_totals["interest_expected"]  += officers_totals["interest_expected"]
                        branch_totals["principal_balance"]  += officers_totals["principal_balance"]
                        branch_totals["interest_balance"]   += officers_totals["interest_balance"]
                        branch_totals["amount_recovery"]    += officers_totals["amount_recovery"]

                        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["interest_balance"]   += officers_totals["interest_balance"]
                        grand_totals["amount_recovery"]    += officers_totals["amount_recovery"] 

            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_written_offs_product_client_type(product_ids,client_type_ids,written_off_loans,as_at):
    section        = []
    written_off_loan_list = {}
    written_off_loan_ids  = []
    grand_totals   = {
        "principal_expected":0,"interest_expected":0,"principal_balance":0,
        "interest_balance":0,"amount_recovery":0
    }

    if written_off_loans:
        for written_off_loan in written_off_loans:
            written_off_loan_list[written_off_loan.id] = written_off_loan
            written_off_loan_ids.append(written_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,"principal_balance":0,
                "interest_balance":0,"amount_recovery":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":written_off_loan_ids,"status":"written_off"}
                loan__main_filter = {"filter":loan_filter,"written_off_loan_list":written_off_loan_list,"as_at":as_at}
                results = written_off_loans_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_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["interest_balance"]   += client_totals["interest_balance"]
                        loan_products_totals["amount_recovery"]    += client_totals["amount_recovery"]

                        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["interest_balance"]   += client_totals["interest_balance"]
                        grand_totals["amount_recovery"]    += client_totals["amount_recovery"]

            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_written_offs_product_gender(product_ids,gender_list,written_off_loans,as_at):
    section        = []
    written_off_loan_list = {}
    written_off_loan_ids  = []
    grand_totals   = {
        "principal_expected":0,"interest_expected":0,"principal_balance":0,
        "interest_balance":0,"amount_recovery":0
    }

    if written_off_loans:
        for written_off_loan in written_off_loans:
            written_off_loan_list[written_off_loan.id] = written_off_loan
            written_off_loan_ids.append(written_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,"principal_balance":0,
                "interest_balance":0,"amount_recovery":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":written_off_loan_ids,"status":"written_off"}
                loan__main_filter = {"filter":loan_filter,"written_off_loan_list":written_off_loan_list,"as_at":as_at}
                results     = written_off_loans_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_expected"] += gender_totals["principal_expected"]
                    product_totals["interest_expected"]  += gender_totals["interest_expected"]
                    product_totals["principal_balance"]  += gender_totals["principal_balance"]
                    product_totals["interest_balance"]   += gender_totals["interest_balance"]
                    product_totals["amount_recovery"]    += gender_totals["amount_recovery"]

                    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["interest_balance"]   += gender_totals["interest_balance"]
                    grand_totals["amount_recovery"]    += gender_totals["amount_recovery"]

            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_written_offs_product_branch(product_ids,branch_ids,written_off_loans,as_at):
    section        = []
    written_off_loan_list = {}
    written_off_loan_ids  = []
    grand_totals   = {
        "principal_expected":0,"interest_expected":0,"principal_balance":0,
        "interest_balance":0,"amount_recovery":0
    }

    if written_off_loans:
        for written_off_loan in written_off_loans:
            written_off_loan_list[written_off_loan.id] = written_off_loan
            written_off_loan_ids.append(written_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,"principal_balance":0,
                "interest_balance":0,"amount_recovery":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":written_off_loan_ids,"status":"written_off"}
                loan__main_filter = {"filter":loan_filter,"written_off_loan_list":written_off_loan_list,"as_at":as_at}
                results = written_off_loans_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_expected"] += products_totals["principal_expected"]
                        branch_totals["interest_expected"]  += products_totals["interest_expected"]
                        branch_totals["principal_balance"]  += products_totals["principal_balance"]
                        branch_totals["interest_balance"]   += products_totals["interest_balance"]
                        branch_totals["amount_recovery"]    += products_totals["amount_recovery"]

                        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["interest_balance"]   += products_totals["interest_balance"]
                        grand_totals["amount_recovery"]    += products_totals["amount_recovery"]

            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_written_offs_product_officer(product_ids,officer_ids,written_off_loans,as_at):
    section        = []
    written_off_loan_list = {}
    written_off_loan_ids  = []
    grand_totals   = {
        "principal_expected":0,"interest_expected":0,"principal_balance":0,
        "interest_balance":0,"amount_recovery":0
    }

    if written_off_loans:
        for written_off_loan in written_off_loans:
            written_off_loan_list[written_off_loan.id] = written_off_loan
            written_off_loan_ids.append(written_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,"principal_balance":0,
                "interest_balance":0,"amount_recovery":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":written_off_loan_ids,"status":"written_off"}
                loan__main_filter = {"filter":loan_filter,"written_off_loan_list":written_off_loan_list,"as_at":as_at}
                results = written_off_loans_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_expected"] += products_totals["principal_expected"]
                        officer_totals["interest_expected"]  += products_totals["interest_expected"]
                        officer_totals["principal_balance"]  += products_totals["principal_balance"]
                        officer_totals["interest_balance"]   += products_totals["interest_balance"]
                        officer_totals["amount_recovery"]    += products_totals["amount_recovery"]

                        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["interest_balance"]   += products_totals["interest_balance"]
                        grand_totals["amount_recovery"]    += products_totals["amount_recovery"] 

            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_written_offs_product_sector(product_ids,sector_ids,written_off_loans,as_at):
    section        = []
    written_off_loan_list = {}
    written_off_loan_ids  = []
    grand_totals   = {
       "principal_expected":0,"interest_expected":0,"principal_balance":0,
       "interest_balance":0,"amount_recovery":0
    }

    if written_off_loans:
        for written_off_loan in written_off_loans:
            written_off_loan_list[written_off_loan.id] = written_off_loan
            written_off_loan_ids.append(written_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,"principal_balance":0,
                "interest_balance":0,"amount_recovery":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":written_off_loan_ids,"status":"written_off"}
                loan__main_filter = {"filter":loan_filter,"written_off_loan_list":written_off_loan_list,"as_at":as_at}
                results = written_off_loans_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_expected"] += products_totals["principal_expected"]
                        sector_totals["interest_expected"]  += products_totals["interest_expected"]
                        sector_totals["principal_balance"]  += products_totals["principal_balance"]
                        sector_totals["interest_balance"]   += products_totals["interest_balance"]
                        sector_totals["amount_recovery"]    += products_totals["amount_recovery"]

                        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["interest_balance"]   += products_totals["interest_balance"]
                        grand_totals["amount_recovery"]    += products_totals["amount_recovery"]  

            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_written_offs_gender_branch(branch_ids,gender_list,written_off_loans,as_at):
        section        = []
        written_off_loan_list = {}
        written_off_loan_ids  = []
        grand_totals   = {
            "principal_expected":0,"interest_expected":0,"principal_balance":0,
            "interest_balance":0,"amount_recovery":0
        }

        if written_off_loans:
            for written_off_loan in written_off_loans:
                written_off_loan_list[written_off_loan.id] = written_off_loan
                written_off_loan_ids.append(written_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,"principal_balance":0,
                    "interest_balance":0,"amount_recovery":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":written_off_loan_ids,"status":"written_off"}
                    loan__main_filter = {"filter":loan_filter,"written_off_loan_list":written_off_loan_list,"as_at":as_at}
                    results     = written_off_loans_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_expected"] += gender_totals["principal_expected"]
                        branch_totals["interest_expected"]  += gender_totals["interest_expected"]
                        branch_totals["principal_balance"]  += gender_totals["principal_balance"]
                        branch_totals["interest_balance"]   += gender_totals["interest_balance"]
                        branch_totals["amount_recovery"]    += gender_totals["amount_recovery"]

                        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["interest_balance"]   += gender_totals["interest_balance"]
                        grand_totals["amount_recovery"]    += gender_totals["amount_recovery"]  

                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_written_offs_group_branch(branch_ids,group_ids,written_off_loans,as_at):
    section        = []
    written_off_loan_list = {}
    written_off_loan_ids  = []
    grand_totals   = {
       "principal_expected":0,"interest_expected":0,"principal_balance":0,
       "interest_balance":0,"amount_recovery":0
    }

    if written_off_loans:
        for written_off_loan in written_off_loans:
            written_off_loan_list[written_off_loan.id] = written_off_loan
            written_off_loan_ids.append(written_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,"principal_balance":0,
               "interest_balance":0,"amount_recovery":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":written_off_loan_ids,"status":"written_off"}
                loan__main_filter = {"filter":loan_filter,"written_off_loan_list":written_off_loan_list,"as_at":as_at}
                results = written_off_loans_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_expected"] += group_totals["principal_expected"]
                        branch_totals["interest_expected"]  += group_totals["interest_expected"]
                        branch_totals["principal_balance"]  += group_totals["principal_balance"]
                        branch_totals["interest_balance"]   += group_totals["interest_balance"]
                        branch_totals["amount_recovery"]    += group_totals["amount_recovery"]

                        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["interest_balance"]   += group_totals["interest_balance"]
                        grand_totals["amount_recovery"]    += group_totals["amount_recovery"] 

            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}