Show Total Days Queryset: Previous Months

Using Django
1

Intro

First small project I’ve used Django for in a real world application! Hurrah! Small IT team that covers On Call Rota, plus other departments. It was a nightmare for the team, different shifts, and general frequent management of letting people know who to call out of hours 24/7. People rarely checked our calendars, emails or ERP system to find who to call and just randomly pick, waking two people instead.

Simply the Apps’s are individual departments which have members and date ranges who ever is on call.

Goal:

What I’d like to do is to display totals of who was on call for previous months. Currently my queryset doesn’t show the correct totals and I’m making three seperate filters for each Member and then doing a count(which i believe just gets the weekly total and not daily(which might be why its missing the actual total) and I just need some help in finding what query I need to be making instead.

Model:

ActiveDate

The cover is normally 1 week at a time but sometimes could be more or less depending on holidays taken.
image

The time is always 7am - 7am by detaults

image
image509×754 48.5 KB

image
image273×754 23.6 KB

View:

I’m guessing I need to query the daycount instead? Whats way should I go about this?

class RotaTotalView(TemplateView):
    template_name = 'total.html'
    def get(self, request):
        today = date.today()
        first = today.replace(day=1)
        firstDayOfLastMonth = today.replace(day=1, month=today.month-1)
        lastDayOfLastMonth = first - timedelta(days=1)
        previous_month = str(firstDayOfLastMonth) + " " + str(lastDayOfLastMonth) #str(lastDayOfLastMonth.month)
        now = date.today()

        adamtotaldays = DateActive.objects.all().filter(employee=1, startdate__year=firstDayOfLastMonth.year, startdate__month=firstDayOfLastMonth.month, enddate__year=lastDayOfLastMonth.year, enddate__month=lastDayOfLastMonth.month)
        stevetotaldays = DateActive.objects.all().filter(employee=2, startdate__year=firstDayOfLastMonth.year, startdate__month=firstDayOfLastMonth.month, enddate__year=lastDayOfLastMonth.year, enddate__month=lastDayOfLastMonth.month)
        kyletotaldays = DateActive.objects.all().filter(employee=4, startdate__year=firstDayOfLastMonth.year, startdate__month=firstDayOfLastMonth.month, enddate__year=lastDayOfLastMonth.year, enddate__month=lastDayOfLastMonth.month)

        dates = DateActive.objects.filter(startdate__lte=now, enddate__gte=now)
        args = {'dates': dates, 'name': DateActive.employee, 'adamtotaldays': str(adamtotaldays.count()*7), 'steffandays': str(stevetotaldays.count()*7), 'kaldays': str(kyletotaldays.count()*7), "previous_month" : previous_month}

        return render(request, self.template_name, args)

image
image1891×662 68.8 KB

2

I’m not following what “totals” you’re looking to generate here. What exactly is it that you’re trying to determine?

Also, can you post your DateActive model?

Finally, from the image you posted from the admin, you may have a timezone issue to deal with. When you’re trying to get things like count-by-day, you need to determine whether you’re talking about a UTC day or a local timezone day. (The database stores times as UTC.)

3

Hi Ken,
Thanks for responding.

I’m looking for the total number of days each member has been on call month by month using the start/end time.

Total Days on Call for May 2021:

Adam: 7 Days on Call
Steve: 14 Days on Call
Kyle: 10 Days on Call

DateActive Model

class DateActive(models.Model):
    def default_start_time():
        now = datetime.now()
        start = now.replace(hour=7, minute=0, second=0, microsecond=0 )
        return start if start > now else start + timedelta(days=1)

    def default_end_time():
        now = datetime.now()
        end = now.replace(hour=6, minute=59, second=59, microsecond=0)
        return end if end > now else end + timedelta(days=1)

    def DayCount(self):
        days = self.enddate - self.startdate
        return days

    isactive  = models.BooleanField(default=1)
    startdate = models.DateTimeField(max_length=250,default=default_start_time)
    enddate   = models.DateTimeField(max_length=250,default=default_end_time)
    employee  = models.ForeignKey(Members, on_delete=models.CASCADE)
    objects = models.Manager()
    showactive = DateActiveManager()

    class Meta:
        verbose_name = "Active Date"

    def __str__(self):
        return str(self.startdate)
4

Needing more clarification as to what you’re trying to determine here. There’s still a lot of ambiguity with how you’ve defined this.

Please consider the following situations:

Person A:
startdate - 10 June 2021 07:00:00
enddate - 11 June 2021 06:59:59

Is this 1 day or 2?

Person B:
startdate - 10 June 2021 07:00:00
enddate - 12 June 2021 06:59:59

Is this 1, 2, or 3 days?

Person C:
startdate - 10 June 2021 19:00:00
enddate - 11 June 2021 06:59:59
startdate - 11 June 2021 19:00:00
enddate - 12 June 2021 06:59:59
Is this 2, 3, or 4 “days”?

Person D: Assume UTC-4 is local time, change for your specific situation:
startdate - 10 June 2021 22:00:00
enddate - 11 June 2021 06:59:59
1 day or 2?

Person E: Assume UTC-4 is local time, change for your specific situation:
startdate - 10 June 2021 22:00:00
enddate - 12 June 2021 06:59:59
1, 2, or 3 days?

5

Hi Ken,

Person A:
That would be 1 Day

Person B:
That would be 2 Days

Person C:
Since the effective day is from 7am to the following 7am that would be less than 1 day. (12 hours) each range.

Goal: There will only be 1 person “On Call” at a time and it typically is for a week at a time. I Can definitely see the ambiguity your talking about.

Do my answer help explain what I’m trying to do at all?

Many thanks for your response, its greatly appreciated!

6

So to try and remove the ambiguity, is the number of days simply the number of hours divided by 24?

If that’s not correct, can you define the requirements more precisely?

7

Yes total number of hours/24 is correct

8

@adamlh From a high level, a way to solve this would be to use an annotation to execute an expression/function that would call your database’s date delta function (Postgres docs). Then ideally you could use another annotation to aggregate that information per employee.