Django Group by month and display in template by month

Question

`class Daily(models.Model):
    transid = models.AutoField(primary_key=True)
    transdate = models.DateField()
    transdetails = models.ForeignKey(Details, on_delete=models.CASCADE)
    transcategory = models.ForeignKey(MasterCategory, on_delete=models.CASCADE)
    transmode = models.ForeignKey(PaymentMode, on_delete=models.CASCADE)
    transsubdetails = models.CharField(max_length=100, blank=False, null=False)
    transamount = models.DecimalField(max_digits=7, decimal_places=2)
    objects = models.Manager()

    def __str__(self):
        return "%s %s %s %s %s %s %s" % (
            self.transid,
            self.transdate,
            self.transdetails,
            self.transcategory,
            self.transmode,
            self.transsubdetails,
            self.transamount)`

Above is my model And my template is as below


{% for month in months %}
<h3>{{ month__month }}</h3>
<table style="width: 100%">
  <tr>
    <th>ID</th>
    <th>Date</th>
    <th>Details</th>
    <th>Category</th>
    <th>Pay Mode</th>
    <th>Sub Details</th>
    <th>Amount</th>
  </tr>
{% for trans in mydata %}
    <tr>
        <td>{{ trans.transid }}</td>
        <td>{{ trans.transdate }}</td>
        <td>{{ trans.transdetails }}</td>
        <td>{{ trans.transcategory }}</td>
        <td>{{ trans.transmode }}</td>
        <td>{{ trans.transsubdetails }}</td>
        <td>{{ trans.transamount }}</td>
    </tr>
{% endfor %}
</table>
{% endfor %}

and my view is

def alltrans(request):
    my_data = Daily.objects.all()
    mnths = len(Daily.objects.values('transdate__month').distinct())+1
    totamount = Daily.objects.all().aggregate(totb=Sum('transamount'))['totb']
    mnths1 = Daily.objects.values('transdate__month').distinct(),
    monthsba = [Daily.objects.filter(transdate__month=month) for month in range(1, mnths)]
    ms = {
        'months': mnths1,
        'monthsbal': monthsba,
        'mydata': my_data,
        'totamount': totamount,
        'title': 'All Trans',
    }
    return render(request, 'alltrans.html', ms)

I am trying to bring in template by month ...

January sum(transamount) filter by month = 1 id Date Details Category Pay Mode Sub Details Amount

February sum(transamount) filter by month = 2

and so on

Please guide me

Niko · Accepted Answer · 2023-03-15T20:02:21.353

It is possible to build the data manually as you are trying. By selecting the months (just as you did) and then looping through the data filtering by it, while aggregating values:

views.py

def trans_per_month(request):
    months = Daily.objects.values('transdate__month').distinct()
    data = {}
    for obj in months:
        month = calendar.month_name[obj['transdate__month']]
        qs = (
            Daily.objects
            .filter(transdate__month=obj['transdate__month'])
        )
        amount = qs.aggregate(total=Sum('transamount'))

        data[obj['transdate__month']] = {
            'month': month,
            'qs': qs,
            'total': amount['total']
        }

    context = {'data': data}

    return render(request, 'trans_per_month.html', context)

trans_per_month.html

<body>
    {% for key, value in data.items %}
    <h3>{{ value.month }}</h3>
    <table style="width: 100%">
        <tr>
            <th>ID</th>
            <th>Date</th>
            <th>Details</th>
            <th>Category</th>
            <th>Pay Mode</th>
            <th>Sub Details</th>
            <th>Amount</th>
        </tr>

        {% for obj in value.qs %}
        <tr>
            <td>{{ obj.transid }}</td>
            <td>{{ obj.transdate }}</td>
            <td>{{ obj.transdetails }}</td>
            <td>{{ obj.transcategory }}</td>
            <td>{{ obj.transmode }}</td>
            <td>{{ obj.transsubdetails }}</td>
            <td>{{ obj.transamount }}</td>
        </tr>
        {% endfor %}

        <tr>
            <td colspan="6">Total</td>
            <td>{{ value.total }}</td>
        </tr>
    </table>
    {% endfor %}
</body>

Like a page break report. I want all my transactions filtered by month of transdate. report title should be the name of the month if 1 January then all my trans for month 1 and total of my trans for month 1 same vise all the datas which has the months in database only should display If the data has only first 3 months, then it should display the three month details as page break — Jamal A M, Mar 14 '23 at 04:46
I do not want to sound impolite. But, you should be more specific about what you want from the beginning. What you are asking has several deviations from your original question. Although, I will try to help as soon as I have the time. — Niko, Mar 14 '23 at 08:26
I have updated the answer. Now it is up to you to tweak the query to your needs :) — Niko, Mar 14 '23 at 16:24
Sorry for not being clear at the first time. Thanks for the updated answer, I will try to execute. — Jamal A M, Mar 15 '23 at 11:45

Django Group by month and display in template by month

1 Answers1