def pivotable_data(self, startDate=None, endDate=None):
"""Compute raw data for pivot table on that mission"""
#TODO: factorize with staffing.views.mission_timesheet
data = []
mission_id = self.mission_id()
mission_name = self.short_name()
current_month = date.today().replace(day=1) # Current month
subsidiary = unicode(self.subsidiary)
dateTrunc = connections[Timesheet.objects.db].ops.date_trunc_sql # Shortcut to SQL date trunc function
consultant_rates = self.consultant_rates()
billing_mode = self.get_billing_mode_display()
# Gather timesheet (Only consider timesheet up to current month)
timesheets = Timesheet.objects.filter(mission=self).filter(working_date__lt=nextMonth(current_month)).order_by("working_date")
if startDate:
timesheets = timesheets.filter(working_date__gte=startDate)
if endDate:
timesheets = timesheets.filter(working_date__lte=endDate)
timesheetMonths = list(timesheets.dates("working_date", "month"))
for consultant in self.consultants():
consultant_name = unicode(consultant)
timesheet_data = dict(timesheets.filter(consultant=consultant).extra(select={'month': dateTrunc("month", "working_date")}).values_list("month").annotate(Sum("charge")).order_by("month"))
timesheet_data = convertDictKeyToDate(timesheet_data)
for month in timesheetMonths:
data.append({ugettext("mission id"): mission_id,
ugettext("mission name"): mission_name,
ugettext("consultant"): consultant_name,
ugettext("subsidiary"): subsidiary,
ugettext("billing mode"): billing_mode,
ugettext("date"): month.strftime("%Y/%m"),
ugettext("done (days)"): timesheet_data.get(month, 0),
ugettext("done (keur)"): timesheet_data.get(month, 0) * consultant_rates[consultant][0] / 1000})
return data
def objectiveMargin(self, startDate=None, endDate=None):
"""Compute margin over rate objective
@param startDate: starting date to consider. This date is included in range. If None, start date is the begining of the mission
@param endDate: ending date to consider. This date is excluded from range. If None, end date is last timesheet for this mission.
@return: dict where key is consultant, value is cumulated margin over objective"""
dateTrunc = connections[Timesheet.objects.db].ops.date_trunc_sql # Shortcut to SQL date trunc function
result = {}
consultant_rates = self.consultant_rates()
# Gather timesheet (Only consider timesheet up to current month)
timesheets = Timesheet.objects.filter(mission=self)
if startDate:
timesheets = timesheets.filter(working_date__gte=startDate)
if endDate:
timesheets = timesheets.filter(working_date__lt=endDate)
timesheets = timesheets.order_by("working_date")
timesheetMonths = list(timesheets.dates("working_date", "month"))
for consultant in self.consultants():
result[consultant] = 0 # Initialize margin over rate objective for this consultant
data = dict(timesheets.filter(consultant=consultant).extra(select={'month': dateTrunc("month", "working_date")}).values_list("month").annotate(Sum("charge")).order_by("month"))
data = convertDictKeyToDate(data)
for month in timesheetMonths:
n_days = data.get(month, 0)
if consultant.subcontractor:
# Compute objective margin on sold rate
if consultant_rates[consultant][0] and consultant_rates[consultant][1]:
result[consultant] += n_days * (consultant_rates[consultant][0] - consultant_rates[consultant][1])
else:
# Compute objective margin on rate objective for this period
objectiveRate = consultant.getRateObjective(workingDate=month, rate_type="DAILY_RATE")
if objectiveRate:
result[consultant] += n_days * (consultant_rates[consultant][0] - objectiveRate.rate)
return result
def objectiveMargin(self, startDate=None, endDate=None):
"""Compute margin over rate objective
@param startDate: starting date to consider. This date is included in range. If None, start date is the begining of the mission
@param endDate: ending date to consider. This date is excluded from range. If None, end date is last timesheet for this mission.
@return: dict where key is consultant, value is cumulated margin over objective"""
dateTrunc = connections[Timesheet.objects.db].ops.date_trunc_sql # Shortcut to SQL date trunc function
result = {}
consultant_rates = self.consultant_rates()
# Gather timesheet (Only consider timesheet up to current month)
timesheets = Timesheet.objects.filter(mission=self)
if startDate:
timesheets = timesheets.filter(working_date__gte=startDate)
if endDate:
timesheets = timesheets.filter(working_date__lt=endDate)
timesheets = timesheets.order_by("working_date")
timesheetMonths = list(timesheets.dates("working_date", "month"))
for consultant in self.consultants():
result[consultant] = 0 # Initialize margin over rate objective for this consultant
data = dict(timesheets.filter(consultant=consultant).extra(select={'month': dateTrunc("month", "working_date")}).values_list("month").annotate(Sum("charge")).order_by("month"))
data = convertDictKeyToDate(data)
for month in timesheetMonths:
n_days = data.get(month, 0)
if consultant.subcontractor:
# Compute objective margin on sold rate
if consultant_rates[consultant][0] and consultant_rates[consultant][1]:
result[consultant] += n_days * (consultant_rates[consultant][0] - consultant_rates[consultant][1])
else:
# Compute objective margin on rate objective for this period
objectiveRate = consultant.getRateObjective(workingDate=month, rate_type="DAILY_RATE")
if objectiveRate:
result[consultant] += n_days * (consultant_rates[consultant][0] - objectiveRate.rate)
return result
def pivotable_data(self, startDate=None, endDate=None):
"""Compute raw data for pivot table on that mission"""
#TODO: factorize with staffing.views.mission_timesheet
data = []
mission_id = self.mission_id()
mission_name = self.short_name()
current_month = date.today().replace(day=1) # Current month
subsidiary = unicode(self.subsidiary)
dateTrunc = connections[Timesheet.objects.db].ops.date_trunc_sql # Shortcut to SQL date trunc function
consultant_rates = self.consultant_rates()
billing_mode = self.get_billing_mode_display()
# Gather timesheet (Only consider timesheet up to current month)
timesheets = Timesheet.objects.filter(mission=self).filter(working_date__lt=nextMonth(current_month)).order_by("working_date")
if startDate:
timesheets = timesheets.filter(working_date__gte=startDate)
if endDate:
timesheets = timesheets.filter(working_date__lte=endDate)
timesheetMonths = list(timesheets.dates("working_date", "month"))
for consultant in self.consultants():
consultant_name = unicode(consultant)
timesheet_data = dict(timesheets.filter(consultant=consultant).extra(select={'month': dateTrunc("month", "working_date")}).values_list("month").annotate(Sum("charge")).order_by("month"))
timesheet_data = convertDictKeyToDate(timesheet_data)
for month in timesheetMonths:
data.append({ugettext("mission id"): mission_id,
ugettext("mission name"): mission_name,
ugettext("consultant"): consultant_name,
ugettext("subsidiary"): subsidiary,
ugettext("billing mode"): billing_mode,
ugettext("date"): month.strftime("%Y/%m"),
ugettext("done (days)"): timesheet_data.get(month, 0),
ugettext("done (keur)"): timesheet_data.get(month, 0) * consultant_rates[consultant][0] / 1000})
return data