Need help using annotate with multiple querys

Using Django Using the ORM
1

I’m trying to create a report which displays a list of customers with open balances, the amount current, 30 days, 60 days, 90 days, 120 days, and total

I am able to get the customer list and total, but I am not sure how to display the rest of the info in a table.

I may be going about this in entirely the wrong way, but this is what I currently have:

def ar_aging(request):
    customers = Workorder.objects.all().exclude(quote=1).exclude(paid_in_full=1).exclude(billed=0)
    current = Workorder.objects.all().exclude(billed=0).exclude(paid_in_full=1).exclude(aging__gt = 29)
    thirty = Workorder.objects.all().exclude(billed=0).exclude(paid_in_full=1).exclude(aging__lte = 30).exclude(aging__gt = 59)
    sixty = Workorder.objects.all().exclude(billed=0).exclude(paid_in_full=1).exclude(aging__lt = 60).exclude(aging__gt = 89)
    ninety = Workorder.objects.all().exclude(billed=0).exclude(paid_in_full=1).exclude(aging__lt = 90).exclude(aging__gt = 119)
    onetwenty = Workorder.objects.all().exclude(billed=0).exclude(paid_in_full=1).exclude(aging__lt = 120)

    # Calculate the total balance
    total_balance = customers.aggregate(total_balance=Sum('open_balance'))['total_balance'] or 0

    # Aggregate individual balances for each customer
    individual_balances = customers.values('hr_customer').annotate(total_balance=Sum('open_balance'))

    context = {
            'customers':customers,
            'total_balance':total_balance,
            'individual_balances':individual_balances,
        }
    return render(request, 'finance/reports/ar_aging.html', context)

{% extends "base.html" %}
{% load static %}
{% load crispy_forms_tags %}

{% block content %}

<center><h4>A/R Aging</h4></center>
    
    
    

      Total outstanding: {{ total_balance|floatformat:2 }}

 
    
      <table class="table">
        <thead>
          <tr>
            <th scope="col" class="">Customer</th>
            <th scope="col" class="">Current</th>
            <th scope="col" class="">1-30</th>
            <th scope="col" class="">31-60</th>
            <th scope="col" class="">61-90</th>
            <th scope="col" class="">> 90</th>
            <th scope="col" class="">Total</th>
          </tr>
        </thead>
        <tbody>
          
          {% for x in individual_balances %}
          <tr>
            <td>{{ x.hr_customer }}</td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td><td>
            <td>{{ x.total_balance|floatformat:2 }}</td>

            </tr>
          {% endfor %}
        </tbody>
      </table>

              
      {% endblock content %}
3

I’m getting closer, the following seems to work, but it displays every row in the database.

How do I group my results by customer so that it only shows once instead of x number of times the customer is in the database?

def ar_aging(request):
    customers = Workorder.objects.all().exclude(quote=1).exclude(paid_in_full=1).exclude(billed=0)
    current = customers.all().exclude(billed=0).exclude(paid_in_full=1).exclude(aging__gt = 29).values('hr_customer').annotate(current_balance=Sum('open_balance'))
    thirty = customers.all().exclude(billed=0).exclude(paid_in_full=1).exclude(aging__lte = 30).exclude(aging__gt = 59).values('hr_customer').annotate(thirty=Sum('open_balance'))
    sixty = customers.all().exclude(billed=0).exclude(paid_in_full=1).exclude(aging__lt = 60).exclude(aging__gt = 89).values('hr_customer').annotate(sixty=Sum('open_balance'))
    ninety = customers.all().exclude(billed=0).exclude(paid_in_full=1).exclude(aging__lt = 90).exclude(aging__gt = 119).values('hr_customer').annotate(ninety=Sum('open_balance'))
    onetwenty = customers.all().exclude(billed=0).exclude(paid_in_full=1).exclude(aging__lt = 120).values('hr_customer').annotate(onetwenty=Sum('open_balance'))
    total = customers.all().values('hr_customer').annotate(total_balance=Sum('open_balance'))

    #total_balance = Workorder.objects.aggregate(total_balance=Sum('open_balance'))['total_balance'] or 0
    total_balance = customers.aggregate(total_balance=Sum('open_balance'))['total_balance'] or 0

    # Aggregate individual balances for each customer
    individual_balances = Workorder.objects.values('hr_customer').annotate(
        #total_balance=Sum('open_balance'),
        total_balance=Subquery(total.filter(hr_customer=models.OuterRef('hr_customer')).values('total_balance')[:1]),
        current=Subquery(current.filter(hr_customer=models.OuterRef('hr_customer')).values('current_balance')[:1]),
        #current=Subquery(current),
        thirty=Subquery(thirty.filter(hr_customer=models.OuterRef('hr_customer')).values('thirty')[:1]),
        sixty=Subquery(sixty.filter(hr_customer=models.OuterRef('hr_customer')).values('sixty')[:1]),
        ninety=Subquery(ninety.filter(hr_customer=models.OuterRef('hr_customer')).values('ninety')[:1]),
        onetwenty=Subquery(onetwenty.filter(hr_customer=models.OuterRef('hr_customer')).values('onetwenty')[:1]),
        )
    
    for x in individual_balances:
        print(x)

    context = {
            'customers':customers,
            'total_balance':total_balance,
            'individual_balances':individual_balances,
        }
    return render(request, 'finance/reports/ar_aging.html', context)