CODEWITHSUNDEEP
pythondjangodjango-3.1
overclock
overclock

Reputation: 625

How to calculate total working hours in Django?

I created the following model:

class Timesheet(models.Model):
    date = models.DateField(auto_now=False, auto_now_add=False, verbose_name="Data")
    entry = models.TimeField(auto_now=False, auto_now_add=False, verbose_name="Hora Entrada")
    lunch = models.TimeField(auto_now=False, auto_now_add=False, null=True, blank=True, verbose_name="Início do Almoço")
    lunch_end = models.TimeField(auto_now=False, auto_now_add=False, null=True, blank=True, verbose_name="Fim do Almoço")
    out = models.TimeField(auto_now=False, auto_now_add=False, verbose_name="Hora de Saída")

This is then returned in a table, that has an extra field called "Total Hours", in which I need to calculate the total worked hours. Each entry refers to the same day.

And I have the following view:

def timesheet(request):
    c = Timesheet.objects.all()
    context = {'c': c}
    return render(request, "tracker/timesheet.html", context)

The calculation I need to do is: (out - entry) - (lunch_end - lunch).

How can I achieve this?

Upvotes: 4

Views: 1552

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477318

You can .annotate(…) [Django-doc] each Timesheet object with:

from django.db.models import DurationField, ExpressionWrapper, F, IntegerField

Timesheet.objects.annotate(
    total_time=ExpressionWrapper(
        ExpressionWrapper(F('out') - F('entry'), output_field=IntegerField()) -
        ExpressionWrapper(F('lunch_end') - F('lunch'), output_field=IntegerField()),
       output_field=DurationField()
    )
)

Here each Timesheet object that arises from this queryset will have an extra attribute .total_time which will contain the duration the person worked (so minus the luch break).

Or you can sum all records up with an .aggregate(…) [Django-doc]:

from django.db.models import DurationField, ExpressionWrapper, F, IntegerField

Timesheet.objects.aggregate(
    total_time=Sum(
        ExpressionWrapper(F('out') - F('entry'), output_field=IntegerField()) -
        ExpressionWrapper(F('lunch_end') - F('lunch'), output_field=IntegerField()),
       output_field=DurationField()
    )
)['total_time']

if the lunch and lunch_end can be None/NULL, you can work with Coalesce [Django-doc]:

from django.db.models import DurationField, ExpressionWrapper, F, IntegerField, Value
from django.db.models.functions import Coalesce

Timesheet.objects.aggregate(
    total_time=Sum(
        ExpressionWrapper(F('out') - F('entry'), output_field=IntegerField()) -
        Coalesce(ExpressionWrapper(F('lunch_end') - F('lunch'), output_field=IntegerField()), Value(0)),
       output_field=DurationField()
    )
)['total_time']

If you thus have .annotate(…)d all Timesheet records, and passed these as c, you can use:

{% for ci in c %}
    {{ ci.data }}: {{ ci.entry }} - {{ ci.out }}; {{ ci.total_time }}
{% endfor %}

Upvotes: 4

Related Questions