I am trying the retrieve the last 7 days of records from my SQLite database. I have an attendance system and I am trying to display some statistics on the UI, however currently it is not working - I will display my current code for this below.

Models:

 class Meta:
    model = User
    fields = ("username", 'email', 'password1', 'password2')

class is_Present(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    date = models.DateField(default=datetime.date.today)
    is_present = models.BooleanField(default=False)


class clocked_Time(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    date = models.DateField(default=datetime.date.today)
    time = models.DateTimeField(null=True, blank=True)
    signed_out = models.BooleanField(default=False)

Views.py:

# Get Count of Present Employees in last week
def present_week_employees():
    today = date.today()
    week = today - timedelta(days=7)
    present_employees_all = is_Present.objects.filter(date=week).filter(is_present=True)
    return len(present_employees_all)


# Displays admin attendance portal functions
def attendance_portal(request):
    if not request.user.is_authenticated:
        messages.warning(request, f'Please sign in to mark attendance out')
        return redirect('login')
    elif not request.user.is_superuser or not request.user.is_staff:
        messages.warning(request, f'Must be admin to access this feature')
        return redirect('home')
    elif request.user.is_superuser:
        count_employees_all = count_employees()  # shows count of employees
        present_employee_all = present_employees()  # shows count present employees today
        present_employee_all_week = present_week_employees()  # shows count present employees in last 7 days

        # Gets the employees present today/week
        today = datetime.today()
        week = today - timedelta(days=7)
        # Gets employees displayed and paginated
        today_p = Paginator(is_Present.objects.filter(date=today).filter(is_present=True).select_related('user').all(),5)
        x = Paginator(is_Present.objects.filter(date=week).filter(is_present=True).select_related('user').all(), 5)
        page = request.GET.get('page', 1)


        try:
            users = today_p.get_page(page)  # returns the desired page object

        except PageNotAnInteger:
            # if page_number is not an integer then assign the first page
            users = today_p.page(1)

        except EmptyPage:
            # if page is empty then return last page
            users = today_p.page(today_p.num_pages)


        # this_week_emp_count_vs_date()
        # last_week_emp_count_vs_date()
        try:
            all_users = x.get_page(page)
        except PageNotAnInteger:
            # if page_number is not an integer then assign the first page
            all_users = x.page(1)
        except EmptyPage:
            # if page is empty then return last page
            all_users = x.page(x.num_pages)
        return render(request, "users/adminReports.html",
                      {'count_employees_all': count_employees_all, 'present_employee_all': present_employee_all,
                       'present_employee_all_week': present_employee_all_week, 'all_users': all_users, 'users': users})
    else:
        messages.warning(request, f'Error - please see logs for details.')
        return redirect(request, 'home')

HTML:

 <div  id="emp_present_week">
                          <div >
                            <h5 > <b> Employees present in last 7 days</b></h5>

                            <p  style="padding-top: 1em; font-size: 28px;"><b> {{ present_employee_all_week  }}</b></p>

                            </div>
                        </div>

Additionally, I have attached an image for reference. FYI - When I mark a user present today It will show them in both today and 7days, which is correct. However, employees I marked present yesterday are not being shown in the 7 day count today. I think the potential issue could be somewhere in the following:

present_employees_all = is_Present.objects.filter(date=week).filter(is_present=True)

Potentially I need to loop through the last 7 days? I have tried this method but havent succeeded.

Any help would be greatly appreciated!

Image of the system statistics

CodePudding user response：

your query is wrong, equal mean you only get records on that exact 7 days ago. You need to query since last 7 day till now, use __gte on your query (gte is greater than or equal)

It should be like so:

today = date.today()
seven_day_before = today - timedelta(days=7)
present_employees_all = is_Present.objects.filter(date__gte=seven_day_before, is_present=True)

also don't need to use chain filter, filter support multiple AND conditions