Django Group by month and payment mode, get a report in template only for the datas present

Question

In my database I have only data up to 'March' so I need only up to 'Mar'

I want achiever monthly sum grouped by 'mode'

def by_mode(request):
    all_modes = dict(Transactions.PAYMENT_MODE_CHOICES).keys()

    totals = Transactions.objects.annotate(
        month=TruncMonth('trans_date'),
        mode=F('trans_mode')
    ).values('month', 'mode').annotate(
        total=Sum('trans_amount')
    )

    data = {}
    for total in totals:
        month = total['month'].strftime('%B')
        mode = total['mode']
        if mode not in data:
            data[mode] = {}
        data[mode][month] = total['total']

    return render(request, 'by_mode.html', {'data': data, 'modes': all_modes})

class Transactions(models.Model): TRANSACTION_TYPE_CHOICES = ( ('income', 'Income'), ('expense', 'Expense'), )

PAYMENT_MODE_CHOICES = (
    ('cash', 'Cash'),
    ('enbd', 'ENBD'),
    ('nol', 'NOL'),
    ('sib', 'SIB'),
)

trans_id = models.AutoField(primary_key=True)
trans_date = models.DateField(verbose_name="Date")
trans_type = models.CharField(max_length=10, choices=TRANSACTION_TYPE_CHOICES, verbose_name="Type")
trans_details = models.ForeignKey(Details, on_delete=models.CASCADE, verbose_name="Details", related_name="trnsactions")
trans_category = models.ForeignKey(MasterCategory, on_delete=models.CASCADE, verbose_name="Category", related_name="transactions")
trans_mode = models.CharField(max_length=10, choices=PAYMENT_MODE_CHOICES, verbose_name="Payment Mode")
trans_amount = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="Amount")

objects = models.Manager()

class Meta:
    verbose_name_plural = "Transactions"

def __str__(self):
    return f"{self.trans_id} - {self.trans_type} - {self.trans_details} - {self.trans_category} - {self.trans_amount}"

score 2 · Accepted Answer · answered Mar 17 '23 at 08:12

You can do something like,

totals = Transactions.objects.annotate(
        month=TruncMonth('trans_date'),
    ).values('month').annotate(
        cash_t=Sum('trans_amount', filter=Q(trans_mode='cash')),
        enbd_t=Sum('trans_amount', filter=Q(trans_mode='enbd')),
        nol_t=Sum('trans_amount', filter=Q(trans_mode='nol')),
        sib_t=Sum('trans_amount', filter=Q(trans_mode='sib'))
    ).order_by()

OR

totals = Transaction.objects.annotate(month=TruncMonth('trans_date')).values(
   'month', 'trans_mode').annotate(tot=Sum('trans_amount')).order_by()

It is necessary to add order_by() at the end to prevent Django's default ordering. Refer to docs of order_by()

score 0 · Answer 2 · answered Apr 06 '23 at 09:56

    import calendar
    from django.db.models import Sum
    from django.shortcuts import render
    from dailytrans.models import Transactions
    
    
    def transaction_summary(request):
        months = Transactions.objects.values('trans_date__month').distinct()
        data = {}
        running_total = 0
        running_total_modes = {}
        for obj in months:
            month = calendar.month_name[obj['trans_date__month']]
            qs = (
                Transactions.objects
                .filter(trans_date__month=obj['trans_date__month'])
                .order_by('trans_id', 'trans_date')  # Add ordering to ensure correct running total
            )
            total_modes = {}
            for trans in qs:
                running_total += trans.trans_amount
                trans.running_total = running_total
                # Update running total for each trans_mode
                mode_total = running_total_modes.get(trans.trans_mode, 0)
                mode_total += trans.trans_amount
                running_total_modes[trans.trans_mode] = mode_total
                trans.running_total_modes = mode_total
                # Update total modes for this month
                mode_month_total = total_modes.get(trans.trans_mode, 0)
                mode_month_total += trans.trans_amount
                total_modes[trans.trans_mode] = mode_month_total
    
            amount = qs.aggregate(total=Sum('trans_amount'))
    
            # Add this month's data to the overall data dictionary
            data[obj['trans_date__month']] = {
                'month': month,
                'qs': qs,
                'total': amount['total'],
                'total_modes': total_modes,
                'running_total': running_total,
                'running_total_modes': running_total_modes.copy()  # Make a copy to avoid modifying the running total dict
            }
    
        context = {'data': data}
    
        return render(request, 'test2.html', context)

with the above view, I am able to render get the result I wanted,

but I feel the view is messy, can anyone edit it for me.

{% extends 'base.html' %}

{% block content %}
<table border="2">
  <tr>
    <td>Trans Mode</td>
    {% for month_data in data.values %}
      <td>{{ month_data.month }}</td>
    {% endfor %}
  </tr>
  <tr>
    <td>Cash</td>
    {% for month_data in data.values %}
      <td><b>{{ month_data.running_total_modes.Cash|default:"0" | floatformat:"2g" }}</b></td>
    {% endfor %}
  </tr>
  <tr>
    <td>Enbd</td>
    {% for month_data in data.values %}
      <td><b>{{ month_data.running_total_modes.ENBD|default:"0" | floatformat:"2g" }}</b></td>
    {% endfor %}
  </tr>
  <tr>
    <td>NoL</td>
    {% for month_data in data.values %}
      <td><b>{{ month_data.running_total_modes.NoL|default:"0" | floatformat:"2g" }}</b></td>
    {% endfor %}
  </tr>
  <tr>
    <td>Pay IT</td>
    {% for month_data in data.values %}
      <td><b>{{ month_data.running_total_modes.PayIT|default:"0" | floatformat:"2g" }}</b></td>
    {% endfor %}
  </tr>
  <tr>
    <td>SIB</td>
    {% for month_data in data.values %}
      <td><b>{{ month_data.running_total_modes.SIB|default:"0" | floatformat:"2g" }}</b></td>
    {% endfor %}
  </tr>
    <tr>
    <td>Total</td>
    {% for month_data in data.values %}
      <td><b>{{ month_data.running_total|default:"0" | floatformat:"2g" }}</b></td>
    {% endfor %}
  </tr>
</table>

{% endblock %}

Django Group by month and payment mode, get a report in template only for the datas present

2 Answers2