Making of a customer report based on sales

I am trying to make a customer wise sales report, in it there will customers listed with their total number of sales, total amount, total paid and balance of sales occurred in the selected time period.

models:


class Customer(models.Model):
    name = models.CharField(max_length=128)
    phone = models.CharField(max_length=128)
    email = models.EmailField(blank=True, null=True)
    address = models.TextField()
    is_deleted = models.BooleanField(default=False)
    ...


class Sale(models.Model):
    auto_id = models.PositiveIntegerField()
    sale_id = models.CharField(max_length=128, blank=True, null=True)
    sale_date = models.DateTimeField()

    customer = models.ForeignKey('customers.Customer', limit_choices_to={'is_deleted': False}, on_delete=models.CASCADE)
    customer_address = models.TextField(blank=True, null=True)
    sale_category = models.CharField(max_length=128, choices=SALE_CATEGORY, default="intra_state")

    subtotal = models.DecimalField(default=0.0, decimal_places=2, max_digits=15, validators=[MinValueValidator(Decimal('0.00'))])
    round_off = models.DecimalField(decimal_places=3, default=0.00, max_digits=30)
    total = models.DecimalField(default=0.0, decimal_places=2, max_digits=15, validators=[MinValueValidator(Decimal('0.00'))])
    paid = models.DecimalField(default=0.0, decimal_places=2, max_digits=15, validators=[MinValueValidator(Decimal('0.00'))])
    balance = models.DecimalField(decimal_places=2, default=0.00, max_digits=15)

    is_deleted = models.BooleanField(default=False)
    ...

What I tried is passing customers with sales occurred in the time period and using a template tag getting the sale values of each customer in template

views:

def customer_sales_report(request):
    from_date = request.GET.get('from_date')
    to_date = request.GET.get('to_date')

    filter_form = {
        'from_date': from_date,
        'to_date': to_date,
    }

    from_date = datetime.datetime.strptime(from_date, '%d/%m/%Y')
    to_date = datetime.datetime.strptime(to_date, '%d/%m/%Y')

    sales = Sale.objects.filter(sale_date__date__range=[from_date, to_date], is_deleted=False)
    customer_pks = list(sales.values_list('customer_id', flat=True))
    customers = Customer.objects.filter(pk__in=customer_pks, is_deleted=False)

    filter_string = f"{filter_form['from_date']},{filter_form['to_date']}"

    context = {
        'customers': customers,
        'filter_form': filter_form,
        'filter_string': filter_string,
        "title": 'Customer sales report',
    }

    return render(request, 'customers/customer_sales_report.html', context)

template:

...
<table>
    <thead>
        <tr>
            <th style="width: 30px;">ID</th>
            <th>Name </th>
            <th>Phone </th>
            <td>Sales</td>
            <td>Total Amount</td>
            <td>Paid Amount</td>
            <td>Balance Amount</td>
            <td>Customer Balance</td>
        </tr>
    </thead>
    <tbody>
        {% load el_pagination_tags %}
        {% paginate 20 customers %}
        {% for instance in customers %}
        <tr>
            <td>{{ forloop.counter }}</td>
            <td>
                <a class="" href="{% url 'customers:customer' pk=instance.pk %}" >{{ instance }}</a>
            </td>
            <td>{{ instance.phone }}</td>

            {% with instance.pk|get_customer_sales:filter_string as sales %}
            <td>{{ sales.total_count }}</td>
            <td>{{ sales.subtotal }}</td>
            <td>{{ sales.paid }}</td>
            <td>{{ sales.balance }}</td>
            <td>{{ sales.current_balance }} ({{ sales.current_balance_type }})</td>
            {% endwith %}
        </tr>
        {% endfor %}
    </tbody>

</table>
...

template tag:


@register.filter
def get_customer_sales(pk, data):
    list_data = data.split(',')

    from_date = list_data[0]
    to_date = list_data[1]
    from_date = datetime.datetime.strptime(from_date, '%d/%m/%Y').date()
    to_date = datetime.datetime.strptime(to_date, '%d/%m/%Y').date()

    sales = Sale.objects.filter(customer_id=pk, sale_date__date__range=[from_date, to_date], is_deleted=False)
    subtotal_amount = sales.aggregate(Sum('total'))['total__sum']
    sale_payment = sales.aggregate(Sum('paid'))['paid__sum']
    sale_balance = sales.aggregate(Sum('balance'))['balance__sum']
    ...

    sale_data = {
        'total_count': sales.count(),
        'paid': sale_payment,
        'balance': sale_balance,
        'subtotal': subtotal_amount,
        "current_balance" : current_balance,
        "current_balance_type" : current_balance_type,
    }

    return sale_data

What I need now is to order by their total amount which as of now I am unable to do. is there a way I can annotate the total amount, paid, balance of sales in to customers queryset which will make it easier or any other ways

Solution

Yes, that's possible to do via annotation and will even be more efficient since all calculations are made in a single query instead of a 3 * NRows.

We can achieve that with the Filtering on annotations.

BTW, you don't need the list() for customer_pks, it should be more efficient to allow DB to work directly on a query.

sales = Sale.objects.filter(sale_date__date__range=[from_date, to_date], is_deleted=False)
customers = Customer.objects.filter(pk__in=sales.values('customer_id'), is_deleted=False)

sales_q = Q(sales__sale_date__date__range=[from_date, to_date], sales__is_deleted=False)
customers = customers.annotate(
    subtotal_amount=Sum('sales__total', filter=sales_q),
    sale_payment=Sum('sales__paid', filter=sales_q),
    sale_balance=Sum('sales__balance', filter=sales_q),
)

I don't know what is current_balance and current_balance_type, so you'll need to figure it out by yourself or amend the question.

P.S. you don't have to covert dates to the filter_string - you can pass any type of objects to the template and then to the filter.