0

I have employees and types of leaves applied by each person like Casual,sick,vacation,maternity,paternity...I want a table for each employee containing number of leaves approved in different leave_types excluding sat,sun in the current year 2017

 Eg:
 person 1 applied sick leave from 11th sept,Mon 2017 to 13 sept,Wed 2017--3 days
 person 1 applied sick leave from 14th sept,Thu 2017 to 15 sept,Fri 2017--2 days

 person 1 applied Casual leave from 14th ,Thu 2017 to 15 sept,Fri 2017--2 days
 person 1 applied Vacation leave from  18th ,Mon 2017 to 26 sept,Tue 2017--7 days excluding sat,sun

Then I need a table in the form of

| leave type|count|
|  vacation | 7  |
|  casual   | 2  |
|  sick     | 5  |

This is for single employee...I need it even for each and every employee

models.py

class employees(models.Model):
    emp_name = models.CharField(max_length = 100)
    emp_loc = models.CharField(max_length = 100)
    manager_id=models.IntegerField(null=True)

class leave(models.Model): 
     employee = models.ForeignKey(employees, on_delete=models.CASCADE, default='1')
    start_date = models.DateField()
    end_date = models.DateField()
    l_type=models.CharField(max_length=1)
divya
  • 315
  • 1
  • 5
  • 15

2 Answers2

0

can you try this

  from django.db.models import Count 
  leave.objects.all().values('l_type').annotate(count=Count('l_type')).
                                   order_by('l_type')
Robert
  • 3,373
  • 1
  • 18
  • 34
  • Here I am getting how many times the status is Sick,vacation in query but not the count between the start_date and end_date for leave of a particular type – divya Sep 14 '17 at 18:56
0

I think you can do it by making these changes:

 class leaves(models.Model)
     employee = models.ForeignKey(employees, on_delete=models.CASCADE, default='1')
     start_date = models.DateField()
     end_date = models.DateField()
     duration = models.DurationField(null=True)
     l_type = models.CharField(max_length=20)

     def save(self, force_insert=False, force_update=False, using=None,
         update_fields=None):
         self.duration = (self.end_date - self.start_date)
         return super(Leaves, self).save()

The save() will calculate duration automatically when the object is created.

Now to get the sum of days of leaves of a particular type, say 'vacation':

  leave_length = leaves.objects.filter(l_type='vacation').aggregate(Sum('duration'))['duration__sum'].days

aggregate(Sum('fieldname')) returns a dict with the sum value associated with a key name fieldname__sum, in our case duration__sum

For excluding weekdays refer the answers of this question : Count number of days between dates, ignoring weekends

Ajmal Noushad
  • 926
  • 7
  • 18