Generating lists/reports with in-line summaries in Django

Question

I am trying to write a view that will generate a report which displays all Items within my Inventory system, and provide summaries at a certain point. This report is purely just an HTML template by the way.

In my case, each Item is part of an Order. An Order can have several items, and I want to be able to display SUM based summaries after the end of each order.

So the report kind of looks like this:

Order #25        <Qty> <Qty Sold> <Cost> <Cost Value>
Some Item          2       1       29.99    29.99
Another Item       4       0       10.00    40.00
<Subtotal Line>    6       1       39.99    69.99
Order #26        <Qty> <Qty Sold> <Cost> <Cost Value>
... Etc, you get the point

Now, I'm perfectly capable of displaying all the values and already have a report showing all the Items, but I have no idea how I can place Subtotals within the report like that without doing alot of queries. The Quantity, Qty Sold, and Cost fields are just part of the Item model, and Cost Value is just a simple model function.

Any help would be appreciated. Thanks in advance :-)

Answer 1

Subtotals are SELECT SUM(qty) GROUP BY order_number things.

They are entirely separate from a query to get details.

The results of the two queries need to be interleaved. A good way to do this is to create each order as a tuple ( list_of_details, appropriate summary ).

Then the display is easy

{% for order in orderList %}
    {% for line in order.0 %}
        {{ line }}
    {% endfor %}
    {{ order.1 }}
{% endfor %}

The hard part is interleaving the two queries.

details = Line.objects.all()
ddict = defaultdict( list )
for d in details:
    ddict[d.order_number].append(d)

interleaved= []
subtotals = ... Django query to get subtotals ... 
for s in subtotals:
    interleaved.append( ( ddict[s.order], s.totals ) )

This interleaved object can be given to your template for rendering.

Answer 2

You could compute the subtotals in Python in the Django view.

The sub-totals could be stored in instances of the Model object with an attribute indicating that it's a sub-total. To keep the report template simple you could insert the sub-total objects in the right places in the result list and use the sub-total attribute to render the sub-total lines differently.