Total and subtotal in Django template using predefined lists

Question

How do I calculate subtotal and total per column?

py views.

def soma_tuplas(a, b):
    return (a[0] + b[0], a[1] + b[1], a[2] + b[2], a[3], a[4])


def quotation_list(request):
    stores = Store.objects.all()
    products = Product.objects.all()
    # indice
    index_store = {store.id: index for index, store in enumerate(stores)}
    index_product = {product.id: index for index,
                     product in enumerate(products)}
    # dados para o template
    cabecalho = ["Lojas"] + [store.store for store in stores]
    linhas = [([product.product] + [None for store in stores] + [(0, 0, 0, None, product.product)])
              for product in products] + [["Subtotal"] + [(0, 0, 0, store.store, None) for store in stores]
                                          + [(0, 0, 0, None, None)]]

    for pev in Quotation.objects.all():
        total = pev.price * pev.quantity

        i0 = index_product[pev.product_id]
        i1 = index_store[pev.store_id] + 1
        valor = (pev.price, pev.quantity, total, pev.store, pev.product)

        linhas[i0][i1] = valor

        # Subtotal da linha
        linhas[i0][len(stores) + 1] = soma_tuplas(
            linhas[i0][len(stores) + 1], valor)

        # Subtotal da coluna
        linhas[len(products)][i1] = soma_tuplas(
            linhas[len(products)][i1], valor)

        # Total da tabela
        linhas[len(products)][len(stores) + 1] = soma_tuplas(
            linhas[len(products)][len(stores) + 1], valor)

    # retorna o menor preço de cada produto
    # a quantidade, total e loja também estão nesta lista
    mais_barato = []
    for linha in linhas:
        mais_barato.append(min(linha[1:]))

    # print(linhas[i0][len(stores) + 1])

    # destaca os menores preços no template
    mb = 0
    if request.GET.get('mais_barato', False):
        mb = 1

    # mostra somente os menores preços
    smb = 0
    if request.GET.get('somente_mais_barato', False):
        smb = 1

    linhas_mais_barato = zip(linhas, mais_barato)

    # mostra os produtos mais baratos, a quantidade e o total
    bqt = 0
    if request.GET.get('quantidade_e_total', False):
        linhas_mais_barato = sorted(linhas_mais_barato,
                                    key=lambda store: str(store[1][3]))  # sort by store
        bqt = 1

    context = {
        'cabecalho': cabecalho,
        'linhas_mais_barato': linhas_mais_barato,
        'mb': mb,
        'smb': smb,
        'bqt': bqt,
    }
    return render(request, 'core/quotation_list.html', context)

template

{% for linha, mais_barato in linhas_mais_barato %}
    <tr>
        {% for item in linha %}
            {% if bqt == 0 %}
                {% if forloop.first %}
                    <td>{{ item }}</td>
                {% elif forloop.last %}
                    <td>   </td>
                {% else %}
                    {% if mb == 1 %}
                        {% if mais_barato == item %}
                            <td class="text-center" style="border: 1px solid #f07746; background-color: #fbddd1;">{{ item.0 }}</td>
                        {% else %}
                            <td class="text-center">{{ item.0 }}</td>
                        {% endif %}
                    {% elif smb == 1 %}
                        {% if mais_barato == item %}
                            <td class="text-center">{{ item.0 }}</td>
                        {% else %}
                            <td>   </td>
                        {% endif %}
                    {% elif bqt == 0 %}
                        <td class="text-center">{{ item.0 }}</td>
                    {% endif %}
                {% endif %}
            {% endif %}
        {% endfor %}
        {% if bqt == 1 %}
            <td>{{ mais_barato.4 }}</td>
            <td class="text-center">{{ mais_barato.0 }}</td>
            <td class="text-center">{{ mais_barato.1 }}</td>
            <td class="text-center">{{ mais_barato.2 }}</td>
            <td class="text-center">{{ mais_barato.3 }}</td>
        {% endif %}

There is one thing I need to explain: on the page I am returning the following values

Generated by the following template code:

{% if bqt == 1 %}
    <td>{{ mais_barato.4 }}</td>
    <td class="text-center">{{ mais_barato.0 }}</td>
    <td class="text-center">{{ mais_barato.1 }}</td>
    <td class="text-center">{{ mais_barato.2 }}</td>
    <td class="text-center">{{ mais_barato.3 }}</td>
{% endif %}

And which in turn was generated from

py views.

# retorna o menor preço de cada produto
# a quantidade, total e loja também estão nesta lista
mais_barato = []
for linha in linhas:
    mais_barato.append(min(linha[1:]))

The problem is, as I’m using the list values mais_barato (perhaps erroneously), I need to:

Answer 1

Although its current code is suitable for a table that crosses products with stores (such as in your question that originated this whole code), this new structure of yours would benefit more from a different strategy. In particular, it is possible to make the "hard work" done by the database itself, and the processing in the view is much less. Not only should performance improve, but the code you would need to write would be much smaller.

Refactoring

Your case is quite similar to that other question. Adapted, would be:

1. Imports and auxiliary variables:

   from django.db.models import F, Min, FloatField
   
   pega_total = F('price') * F('quantity')
   pega_total.output_field = FloatField()
   

2. Begins the search for Quotation:

   Quotation.objects.
   

3. Find the lowest price of each product, and calculate the total (price x quantity):

   .annotate(menor=Min('product__quotation__price'), total=pega_total).
   

4. Filters only items whose price is equal to the minimum:

   .filter(price=F('menor')).
   

5. [if supported] Eliminates duplicates (two stores offering the product at the same value); not supported by all Bds:

   .distinct('product').
   

   (No Sqlite gave NotImplementedError: DISTINCT ON fields is not supported by this database backend)

6. Sort by the shop:

   .order_by('store__store').
   

7. Take the values that interest you to assemble the table:

   .values('product__product', 'price', 'quantity', 'total', 'store__store')
   

So, you already get the data you need for your table in a single query:

from django.db.models import F, Min, FloatField

pega_total = F('price') * F('quantity')
pega_total.output_field = FloatField()

qs = Quotation.objects.all().\
         annotate(menor=Min('product__quotation__price'), total=pega_total).\
         filter(price=F('menor')).\
         distinct('product').\
         order_by('store__store').\
         values('product__product', 'price', 'quantity', 'total', 'store__store')

context = { "linhas":list(qs) }

And in the template:

{% for linha in linhas %}
<tr>
    <td>{{ linha.product__product }}</td>
    <td>{{ linha.price }}</td>
    <td>{{ linha.quantity }}</td>
    <td>{{ linha.total }}</td>
    <td>{{ linha.store__store }}</td>
</tr>
{% endfor %}

Adding subtotal and total

As the data already comes sorted by store, calculating the subtotal is easy: just go by row by row and accumulating, and when the store changes (or the stores run out), include a special line for the subtotal:

qs = ...

subtotal = 0
total = 0
ultima_loja = None

linhas = []
for linha in qs:
    if ultima_loja is not None and ultima_loja != linha['store__store']:
        linhas.append({ 'subtotal':subtotal })
        subtotal = 0
    linhas.append(linha)
    ultima_loja = linha['store__store']
    subtotal += linha['total']
    total += linha['total']

if ultima_loja is not None:
    linhas.append({ 'subtotal':subtotal })
linhas.append({ 'total':total })

context = { "linhas":linhas }

And in the template:

{% for linha in linhas %}
<tr>
    {% if linha.product__product %}
        <td>{{ linha.product__product }}</td>
        <td>{{ linha.price }}</td>
        <td>{{ linha.quantity }}</td>
        <td>{{ linha.total }}</td>
        <td>{{ linha.store__store }}</td>
    {% elif linha.subtotal %}
        <td></td><td></td><td></td>
        <td class="subtotal">{{ linha.subtotal }}</td>
        <td>SUBTOTAL</td>
    {% else %}
        <td></td><td></td><td></td>
        <td class="total">{{ linha.total }}</td>
        <td>TOTAL</td>
    {% endif %}
</tr>
{% endfor %}

---

Note: if you are curious, this is the SQL that the query suggested in this response generates (except for the part of distinct that didn’t work here):

SELECT
    "app_product"."product",
    "app_quotation"."price",
    "app_quotation"."quantity",
    "app_store"."store",
    ("app_quotation"."price" * "app_quotation"."quantity") AS "total" 
FROM "app_quotation" 
    INNER JOIN "app_product" ON ( "app_quotation"."product_id" = "app_product"."id" )
    LEFT OUTER JOIN "app_quotation" T3 ON ( "app_product"."id" = T3."product_id" )
    INNER JOIN "app_store" ON ( "app_quotation"."store_id" = "app_store"."id" )
GROUP BY
    "app_quotation"."id",
    "app_quotation"."product_id",
    "app_quotation"."store_id",
    "app_quotation"."price",
    "app_quotation"."quantity",
    ("app_quotation"."price" * "app_quotation"."quantity"),
    "app_product"."product",
    "app_store"."store"
HAVING "app_quotation"."price" = (MIN(T3."price"))
ORDER BY "app_store"."store" ASC