Total and subtotal in Django template using predefined lists

Question

Total and subtotal in Django template using predefined lists

Navigation

#1 by (1 votes)

4

How do I calculate the subtotal and the total per column?

views.py

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 %}

I've been missing something: on the page I'm returning the following values

Generatedbythefollowingtemplatecode:

{%ifbqt==1%}<td>{{mais_barato.4}}</td><tdclass="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 that in turn was generated from

views.py

# 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 that as I'm using the values from the mais_barato list (maybe wrongly), I need to:

django django-templates

asked by anonymous 07.12.2015 / 22:40

1 answer

Media width queries applied incorrectly on mobile devices Access your mobile camera in a WebView in Android Studio

score 1 · Accepted Answer

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

Refactoring

Your case is very similar to this other question . Adapted, it would:

Imports and auxiliary variables:

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

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

The search for Quotation starts:

Quotation.objects.

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

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

Filters only items whose price is equal to the minimum:

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

[if supported] Removes duplicates (two stores that offer the product by the same value); not supported by all BDs:

.distinct('product').

(In SQLite gave NotImplementedError: DISTINCT ON fields is not supported by this database backend )

Order by store:

.order_by('store__store').

Get the values that interest you to build 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 ordered by store, calculating the subtotal is easy: just go following line by line 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 did not 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