def graph_outstanding_billing(request):
"""Graph outstanding billing, including overdue clients bills"""
end = nextMonth(date.today() + timedelta(45))
current = (end - timedelta(30) * 24).replace(day=1)
today = date.today()
months = []
outstanding = []
outstanding_overdue = []
graph_data = []
subsidiary = get_subsidiary_from_request(request)
while current < end:
months.append(current.isoformat())
next_month = nextMonth(current)
bills = ClientBill.objects.filter(due_date__lte=next_month, state__in=("1_SENT", "2_PAID")).exclude(payment_date__lt=current)
if subsidiary:
bills = bills.filter(lead__subsidiary=subsidiary)
overdue_bills = bills.exclude(payment_date__lte=F("due_date")).exclude(payment_date__gt=next_month).exclude(due_date__gt=today)
outstanding.append(float(bills.aggregate(Sum("amount"))["amount__sum"] or 0))
outstanding_overdue.append(float(overdue_bills.aggregate(Sum("amount"))["amount__sum"] or 0))
current = next_month
graph_data.append(["x"] + months)
graph_data.append([_("billing outstanding")] + outstanding)
graph_data.append([_("billing outstanding overdue")] + outstanding_overdue)
return render(request, "billing/graph_outstanding_billing.html",
{"graph_data": json.dumps(graph_data),
"series_colors": COLORS,
"user": request.user})
def graph_outstanding_billing(request):
"""Graph outstanding billing, including overdue clients bills"""
end = nextMonth(date.today())
current = (end - timedelta(30) * 24).replace(day=1)
months = []
outstanding = []
outstanding_overdue = []
graph_data = []
while current < end:
months.append(current.isoformat())
next_month = nextMonth(current)
outstanding.append(
float(
ClientBill.objects.filter(due_date__lte=next_month).exclude(
payment_date__lt=next_month).aggregate(
Sum("amount"))["amount__sum"] or 0))
outstanding_overdue.append(
float(
ClientBill.objects.filter(due_date__lte=current).exclude(
payment_date__lt=next_month).aggregate(
Sum("amount"))["amount__sum"] or 0))
current = next_month
graph_data.append(["x"] + months)
graph_data.append([_("billing outstanding")] + outstanding)
graph_data.append([_("billing outstanding overdue")] + outstanding_overdue)
return render(
request, "billing/graph_outstanding_billing.html", {
"graph_data": json.dumps(graph_data),
"series_colors": COLORS,
"user": request.user
})
def timesheet_report_data(mission, start=None, end=None, padding=False):
"""Prepare data for timesheet report from start to end.
Padding align total in the same column"""
timesheets = Timesheet.objects.select_related().filter(mission=mission)
months = timesheets.dates("working_date", "month")
data = []
for month in months:
if start and month < start:
continue
if end and month > end:
break
days = daysOfMonth(month)
next_month = nextMonth(month)
padding_length = 31 - len(
days
) # Padding for month with less than 31 days to align total column
# Header
data.append([""])
data.append([formats.date_format(month, format="YEAR_MONTH_FORMAT")])
# Days
data.append([
"",
] + [d.day for d in days])
dayHeader = [_("Consultants")] + [_(d.strftime("%a")) for d in days]
if padding:
dayHeader.extend([""] * padding_length)
dayHeader.append(_("total"))
data.append(dayHeader)
for consultant in mission.consultants():
total = 0
row = [
consultant,
]
consultant_timesheets = {}
for timesheet in timesheets.filter(consultant_id=consultant.id,
working_date__gte=month,
working_date__lt=next_month):
consultant_timesheets[
timesheet.working_date] = timesheet.charge
for day in days:
try:
charge = consultant_timesheets.get(day)
if charge:
row.append(
formats.number_format(to_int_or_round(charge, 2)))
total += charge
else:
row.append("")
except Timesheet.DoesNotExist:
row.append("")
if padding:
row.extend([""] * padding_length)
row.append(formats.number_format(to_int_or_round(total, 2)))
if total > 0:
data.append(row)
return data
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 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 forecasted_work(self):
"""Compute forecasted work according to staffing for this mission
Result is cached for few seconds
@return: (forecasted work in days, forecasted work in euros"""
rates = dict([(i.id, j[0]) for i, j in self.consultant_rates().items()
]) # switch to consultant id
days = 0
amount = 0
current_month = date.today().replace(day=1)
staffings = Staffing.objects.filter(mission=self,
staffing_date__gte=current_month)
staffings = staffings.values_list("consultant").annotate(
Sum("charge")).order_by()
current_month_done = Timesheet.objects.filter(
mission=self,
working_date__gte=current_month,
working_date__lt=nextMonth(date.today()))
current_month_done = dict(
current_month_done.values_list("consultant").annotate(
Sum("charge")).order_by())
for consultant_id, charge in staffings:
days += charge # Add forecasted days
days -= current_month_done.get(
consultant_id,
0) # Substract current month done works from forecastinng
if consultant_id in rates:
amount += charge * rates[consultant_id]
amount -= current_month_done.get(consultant_id,
0) * rates[consultant_id]
return (days, amount)
def test_mission_timesheet(self):
self.client.login(username=TEST_USERNAME, password=TEST_PASSWORD)
current_month = date.today().replace(day=1)
next_month = nextMonth(current_month)
previous_month = previousMonth(current_month)
lead = Lead.objects.get(id=1)
c1 = Consultant.objects.get(id=1)
c2 = Consultant.objects.get(id=2)
mission = Mission(lead=lead, subsidiary_id=1, billing_mode="TIME_SPENT", nature="PROD", probability=100)
mission.save()
response = self.client.get(urlresolvers.reverse("staffing.views.mission_timesheet", args=[mission.id,]), follow=True, HTTP_X_REQUESTED_WITH="XMLHttpRequest")
self.assertEqual(response.status_code, 200)
self.assertEqual(response.context["margin"], 0)
self.assertEqual(response.context["objective_margin_total"], 0)
self.assertEqual(response.context["forecasted_unused"], 0)
self.assertEqual(response.context["current_unused"], 0)
self.assertEqual(response.context["avg_daily_rate"], 0)
# Add some forecast
Staffing(mission=mission, staffing_date=current_month, consultant=c1, charge=15).save()
Staffing(mission=mission, staffing_date=current_month, consultant=c2, charge=10).save()
Staffing(mission=mission, staffing_date=next_month, consultant=c1, charge=8).save()
Staffing(mission=mission, staffing_date=next_month, consultant=c2, charge=6).save()
# Add some timesheet - we fake with all charge on the first day
Timesheet(mission=mission, working_date=previous_month, consultant=c1, charge=8).save()
Timesheet(mission=mission, working_date=previous_month, consultant=c2, charge=5).save()
Timesheet(mission=mission, working_date=current_month, consultant=c1, charge=11).save()
Timesheet(mission=mission, working_date=current_month, consultant=c2, charge=9).save()
# Define objective rates for consultants
RateObjective(consultant=c1, start_date=previous_month, daily_rate=700).save()
RateObjective(consultant=c2, start_date=previous_month, daily_rate=1050).save()
# Add financial conditions for this mission
FinancialCondition(consultant=c1, mission=mission, daily_rate=800).save()
FinancialCondition(consultant=c2, mission=mission, daily_rate=1100).save()
# Define mission price
mission.price = 50
mission.save()
# Let's test if computation are rights
response = self.client.get(urlresolvers.reverse("staffing.views.mission_timesheet", args=[mission.id,]), follow=True, HTTP_X_REQUESTED_WITH="XMLHttpRequest")
self.assertEqual(response.status_code, 200)
self.assertEqual(response.context["margin"], 0) # That's because we are in fixed price
self.assertEqual(response.context["objective_margin_total"], 2600)
self.assertEqual(response.context["forecasted_unused"], 2.1)
self.assertEqual(response.context["current_unused"], 19.4)
# Switch to fixed price mission
mission.billing_mode = "FIXED_PRICE"
mission.save()
response = self.client.get(urlresolvers.reverse("staffing.views.mission_timesheet", args=[mission.id,]), follow=True, HTTP_X_REQUESTED_WITH="XMLHttpRequest")
self.assertEqual(response.status_code, 200)
self.assertEqual(response.context["margin"], 2.1)
self.assertEqual(response.context["objective_margin_total"], 2600)
self.assertEqual(response.context["forecasted_unused"], 0) # Unused is margin in fixes price :-)
self.assertEqual(response.context["current_unused"], 0) # idem
# Check mission data main table
data = response.context["mission_data"]
self.assertListEqual(data[0], [c2, [5, 9, 14, 15.4], [1, 6, 7, 7.7], [21, 23.1]])
self.assertListEqual(data[1], [c1, [8, 11, 19, 15.2], [4, 8, 12, 9.6], [31, 24.8]])
self.assertListEqual(data[2], [None, [13, 20, 33, 30.6], [5, 14, 19, 17.3], [52, 47.9],
[11.9, 18.7], [4.3, 13],
[915.4, 935, 927.3], [860, 928.6, 910.5]])
def holidayDays(month=None):
"""
@param month: month (datetime) to consider for holidays. Current month if None
@return: list of holidays days of given month """
if not month:
month = date.today()
month = month.replace(day=1)
return [h.day for h in Holiday.objects.filter(day__gte=month).filter(day__lt=nextMonth(month))]
def holidayDays(month=None):
"""
@param month: month (datetime) to consider for holidays. Current month if None
@return: list of holidays days of given month """
if not month:
month = date.today()
month = month.replace(day=1)
return [h.day for h in Holiday.objects.filter(day__gte=month).filter(day__lt=nextMonth(month))]
def compute_automatic_staffing(mission, mode, duration, user=None):
"""Compute staffing for a given mission. Mode can be after (current staffing) for replace (erase and create)"""
now = datetime.now().replace(microsecond=0) # Remove useless microsecond
current_month = date.today().replace(day=1)
start_date = current_month
total = 0
if not mission.consultants():
# no consultant, no staffing. Come on.
return
if mode=="replace":
mission.staffing_set.all().delete()
cache.delete("Mission.forecasted_work%s" % mission.id)
cache.delete("Mission.done_work%s" % mission.id)
if mission.lead:
start_date = max(current_month, mission.lead.start_date.replace(day=1))
else:
max_staffing = Staffing.objects.filter(mission=mission).aggregate(Max("staffing_date"))["staffing_date__max"]
if max_staffing:
start_date = max(current_month, nextMonth(max_staffing))
if mission.start_date:
start_date = max(start_date, mission.start_date)
margin = mission.remaining(mode="target")
rates = mission.consultant_rates()
rates_sum = sum([i[0] for i in rates.values()])
days = margin*1000 / rates_sum / duration
days = max(floor(days * 4) / 4, 0.25)
for consultant in rates.keys():
month = start_date
for i in range(duration):
if total > margin*1000:
break
if mission.end_date and month > mission.end_date:
break
s = Staffing(mission=mission, consultant=consultant, charge=days, staffing_date=month, update_date = now)
if user:
s.last_user = str(user)
s.save()
total += days * rates[consultant][0]
month = nextMonth(month)
def pivotable_data(self, startDate=None, endDate=None):
"""Compute raw data for pivot table on that mission"""
#TODO: factorize with staffing.views.mission_timesheet
#TODO: denormalize by adding done/planned as a type column and move days/amount in values columns
data = []
mission_id = self.mission_id()
mission_name = self.short_name()
current_month = date.today().replace(day=1) # Current month
subsidiary = str(self.subsidiary)
consultant_rates = self.consultant_rates()
billing_mode = self.get_billing_mode_display()
# Gather timesheet and staffing (Only consider data up to current month)
timesheets = Timesheet.objects.filter(mission=self).filter(working_date__lt=nextMonth(current_month)).order_by("working_date")
staffings = Staffing.objects.filter(mission=self).filter(staffing_date__gte=nextMonth(current_month)).order_by("staffing_date")
if startDate:
timesheets = timesheets.filter(working_date__gte=startDate)
staffings = staffings.filter(staffing_date__gte=startDate)
if endDate:
timesheets = timesheets.filter(working_date__lte=endDate)
staffings = staffings.filter(staffing_date__lte=endDate)
timesheetMonths = list(timesheets.dates("working_date", "month"))
staffingMonths = list(staffings.dates("staffing_date", "month"))
for consultant in self.consultants():
consultant_name = str(consultant)
timesheet_data = dict(timesheets.filter(consultant=consultant).annotate(month=TruncMonth("working_date")).values_list("month").annotate(Sum("charge")).order_by("month"))
staffing_data = dict(staffings.filter(consultant=consultant).values_list("staffing_date").annotate(Sum("charge")).order_by("staffing_date"))
for month in set(timesheetMonths + staffingMonths):
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 (€)"): timesheet_data.get(month, 0) * consultant_rates[consultant][0],
ugettext("forecast (days)"): staffing_data.get(month, 0),
ugettext("forecast (€)"): staffing_data.get(month, 0) * consultant_rates[consultant][0]})
return data
def gatherTimesheetData(consultant, missions, month):
"""Gather existing timesheet timesheetData
@returns: (timesheetData, timesheetTotal, warning)
timesheetData represent timesheet form post timesheetData as a dict
timesheetTotal is a dict of total charge (key is mission id)
warning is a list of 0 (ok) or 1 (surbooking) or 2 (no data). One entry per day"""
timesheetData = {}
timesheetTotal = {}
warning = []
totalPerDay = [0] * month_days(month)
next_month = nextMonth(month)
for mission in missions:
timesheets = Timesheet.objects.select_related().filter(
consultant=consultant).filter(mission=mission)
timesheets = timesheets.filter(working_date__gte=month).filter(
working_date__lt=next_month)
for timesheet in timesheets:
timesheetData["charge_%s_%s" %
(timesheet.mission.id,
timesheet.working_date.day)] = timesheet.charge
if mission.id in timesheetTotal:
timesheetTotal[mission.id] += timesheet.charge
else:
timesheetTotal[mission.id] = timesheet.charge
totalPerDay[timesheet.working_date.day - 1] += timesheet.charge
# Gather lunck ticket data
totalTicket = 0
lunchTickets = LunchTicket.objects.filter(consultant=consultant)
lunchTickets = lunchTickets.filter(lunch_date__gte=month).filter(
lunch_date__lt=next_month)
for lunchTicket in lunchTickets:
timesheetData["lunch_ticket_%s" %
lunchTicket.lunch_date.day] = lunchTicket.no_ticket
totalTicket += 1
timesheetTotal["ticket"] = totalTicket
# Compute warnings (overbooking and no data)
for i in totalPerDay:
i = round(
i, 4
) # We must round because using keyboard time input may lead to real numbers that are truncated
if i > 1: # Surbooking
warning.append(1)
elif i == 1: # Ok
warning.append(0)
else: # warning (no data, or half day)
warning.append(2)
# Don't emit warning for no data during week ends and holidays
holiday_days = holidayDays(month)
for day in daysOfMonth(month):
if day.isoweekday() in (6, 7) or day in holiday_days:
warning[day.day - 1] = None
return (timesheetData, timesheetTotal, warning)
def timesheet_report_data(mission, start=None, end=None, padding=False):
"""Prepare data for timesheet report from start to end.
Padding align total in the same column"""
timesheets = Timesheet.objects.select_related().filter(mission=mission)
months = timesheets.dates("working_date", "month")
data = []
for month in months:
if start and month < start:
continue
if end and month > end:
break
days = daysOfMonth(month)
next_month = nextMonth(month)
padding_length = 31 - len(days) # Padding for month with less than 31 days to align total column
# Header
data.append([""])
data.append([formats.date_format(month, format="YEAR_MONTH_FORMAT")])
# Days
data.append(["", ] + [d.day for d in days])
dayHeader = [_("Consultants")] + [_(d.strftime("%a")) for d in days]
if padding:
dayHeader.extend([""] * padding_length)
dayHeader.append(_("total"))
data.append(dayHeader)
for consultant in mission.consultants():
total = 0
row = [consultant, ]
consultant_timesheets = {}
for timesheet in timesheets.filter(consultant_id=consultant.id,
working_date__gte=month,
working_date__lt=next_month):
consultant_timesheets[timesheet.working_date] = timesheet.charge
for day in days:
try:
charge = consultant_timesheets.get(day)
if charge:
row.append(formats.number_format(to_int_or_round(charge, 2)))
total += charge
else:
row.append("")
except Timesheet.DoesNotExist:
row.append("")
if padding:
row.extend([""] * padding_length)
row.append(formats.number_format(to_int_or_round(total, 2)))
if total > 0:
data.append(row)
return data
def __init__(self, *args, **kwargs):
minDate = kwargs.pop("minDate", date.today() - timedelta(30*11))
nMonth = kwargs.pop("nMonth", 12)
months = []
month = minDate.replace(day=1)
for i in range(nMonth):
months.append(month)
month = nextMonth(month)
kwargs["choices"] = [(i, formats.date_format(i, format="YEAR_MONTH_FORMAT")) for i in months]
kwargs["empty_value"] = None
super(BillingDateChoicesField, self).__init__(*args, **kwargs)
def staffingDates(n=12, format=None, minDate=None):
"""Returns a list of n next month as datetime (if format="datetime") or
as a list of dict() with short/long(encoded) string date"""
staffingDate = minDate or date.today().replace(day=1)
dates = []
for i in range(n):
if format == "datetime":
dates.append(staffingDate)
else:
dates.append({"value": formats.localize_input(staffingDate),
"label": formats.date_format(staffingDate, format="YEAR_MONTH_FORMAT").encode("latin-1"), })
staffingDate = nextMonth(staffingDate)
return dates
def staffingDates(n=12, format=None, minDate=None):
"""Returns a list of n next month as datetime (if format="datetime") or
as a list of dict() with short/long(encoded) string date"""
staffingDate = minDate or date.today().replace(day=1)
dates = []
for i in range(int(n)):
if format == "datetime":
dates.append(staffingDate)
else:
dates.append({"value": formats.localize_input(staffingDate),
"label": formats.date_format(staffingDate, format="YEAR_MONTH_FORMAT").encode("latin-1"), })
staffingDate = nextMonth(staffingDate)
return dates
def __init__(self, *args, **kwargs):
minDate = kwargs.pop("minDate", date.today() - timedelta(30*11))
nMonth = kwargs.pop("nMonth", 12)
months = []
month = minDate.replace(day=1)
for i in range(nMonth):
months.append(month)
month = nextMonth(month)
kwargs["choices"] = [(i, formats.date_format(i, format="YEAR_MONTH_FORMAT")) for i in months]
kwargs["empty_value"] = None
super(BillingDateChoicesField, self).__init__(*args, **kwargs)
def timesheet_report_data_grouped(mission, start=None, end=None):
"""Timesheet charges for a single mission, on a timerange, by whole month
For each month, charges are grouped by daily rate
Returns a list of lines to be sent as CSV"""
timesheets = Timesheet.objects.select_related().filter(mission=mission)
months = timesheets.dates("working_date", "month")
data = []
data.append([mission.short_name()])
rates_consultants = {}
for consultant, rate in mission.consultant_rates().items():
daily_rate, _ = rate
if daily_rate not in rates_consultants:
rates_consultants[daily_rate] = []
rates_consultants[daily_rate].append(consultant)
for month in months:
if start and month < start:
continue
if end and month > end:
break
next_month = nextMonth(month)
# Header
data.append([""])
data.append([formats.date_format(month, format="YEAR_MONTH_FORMAT")])
rates = sorted(rates_consultants.keys())
for rate in rates:
rate_label = "R {}".format(rate)
total = 0
row = [
rate_label,
]
# timesheets is already a Queryset, we cannot aggregate in SQL
rate_timesheets_charges = timesheets.filter(
consultant__in=rates_consultants[rate],
working_date__gte=month,
working_date__lt=next_month).values("charge")
for c in rate_timesheets_charges:
total += c["charge"]
row.append(total)
if total:
data.append(row)
return data
def done_work(self):
"""Compute done work according to timesheet for this mission
Result is cached for few seconds
@return: (done work in days, done work in euros)"""
rates = dict([(i.id, j[0]) for i, j in self.consultant_rates().items()]) # switch to consultant id
days = 0
amount = 0
timesheets = Timesheet.objects.filter(mission=self, working_date__lt=nextMonth(date.today()))
timesheets = timesheets.values_list("consultant").annotate(Sum("charge")).order_by()
for consultant_id, charge in timesheets:
days += charge
if consultant_id in rates:
amount += charge * rates[consultant_id]
return (days, amount)
def done_work(self):
"""Compute done work according to timesheet for this mission
Result is cached for few seconds
@return: (done work in days, done work in euros)"""
rates = dict([(i.id, j[0]) for i, j in self.consultant_rates().items()]) # switch to consultant id
days = 0
amount = 0
timesheets = Timesheet.objects.filter(mission=self, working_date__lt=nextMonth(date.today()))
timesheets = timesheets.values_list("consultant").annotate(Sum("charge")).order_by()
for consultant_id, charge in timesheets:
days += charge
if consultant_id in rates:
amount += charge * rates[consultant_id]
return (days, amount)
def update_client_bill_from_timesheet(bill, mission, start_date, end_date):
"""Populate bill detail for given mission from timesheet of given interval"""
ClientBill = apps.get_model("billing", "clientbill")
BillDetail = apps.get_model("billing", "billdetail")
Consultant = apps.get_model("people", "Consultant")
rates = mission.consultant_rates()
month = start_date
while month < end_date:
timesheet_data = mission.timesheet_set.filter(
working_date__gte=month, working_date__lt=nextMonth(month))
timesheet_data = timesheet_data.order_by("consultant").values(
"consultant").annotate(Sum("charge"))
for i in timesheet_data:
consultant = Consultant.objects.get(id=i["consultant"])
billDetail = BillDetail(bill=bill,
mission=mission,
month=month,
consultant=consultant,
quantity=i["charge__sum"],
unit_price=rates[consultant][0])
billDetail.save()
month = nextMonth(month)
bill.save() # save again to update bill amount according to its details
return bill
def compute_automatic_staffing(mission, mode, duration, user=None):
"""Compute staffing for a given mission. Mode can be after (current staffing) for replace (erase and create)"""
now = datetime.now().replace(microsecond=0) # Remove useless microsecond
current_month = date.today().replace(day=1)
start_date = current_month
total = 0
if mode=="replace":
mission.staffing_set.all().delete()
cache.delete("Mission.forecasted_work%s" % mission.id)
cache.delete("Mission.done_work%s" % mission.id)
if mission.lead:
start_date = max(current_month, mission.lead.start_date.replace(day=1))
else:
max_staffing = Staffing.objects.filter(mission=mission).aggregate(Max("staffing_date"))["staffing_date__max"]
if max_staffing:
start_date = max(current_month, nextMonth(max_staffing))
margin = mission.margin(mode="target")
rates = mission.consultant_rates()
rates_sum = sum([i[0] for i in rates.values()])
days = margin*1000 / rates_sum / duration
days = max(floor(days * 4) / 4, 0.25)
for consultant in rates.keys():
month = start_date
for i in range(duration):
if total > margin*1000:
break
s = Staffing(mission=mission, consultant=consultant, charge=days, staffing_date=month, update_date = now)
if user:
s.last_user = str(user)
s.save()
total += days * rates[consultant][0]
month = nextMonth(month)
def pre_billing(request, year=None, month=None):
"""Pre billing page: help to identify bills to send"""
if year and month:
month = date(int(year), int(month), 1)
else:
month = previousMonth(date.today())
next_month = nextMonth(month)
timeSpentBilling = {} # Key is lead, value is total and dict of mission(total, Mission billingData)
rates = {} # Key is mission, value is Consultant rates dict
fixedPriceMissions = Mission.objects.filter(nature="PROD", billing_mode="FIXED_PRICE",
timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month)
fixedPriceMissions = fixedPriceMissions.order_by("lead").distinct()
timesheets = Timesheet.objects.filter(working_date__gte=month, working_date__lt=next_month,
mission__nature="PROD", mission__billing_mode="TIME_SPENT")
timesheet_data = timesheets.order_by("mission__lead", "consultant").values_list("mission", "consultant").annotate(Sum("charge"))
for mission_id, consultant_id, charge in timesheet_data:
mission = Mission.objects.select_related("lead").get(id=mission_id)
if mission.lead:
lead = mission.lead
else:
# Bad data, mission with nature prod without lead... This should not happened
continue
consultant = Consultant.objects.get(id=consultant_id)
if not mission in rates:
rates[mission] = mission.consultant_rates()
if not lead in timeSpentBilling:
timeSpentBilling[lead] = [0.0, {}] # Lead Total and dict of mission
if not mission in timeSpentBilling[lead][1]:
timeSpentBilling[lead][1][mission] = [0.0, []] # Mission Total and detail per consultant
total = charge * rates[mission][consultant][0]
timeSpentBilling[lead][0] += total
timeSpentBilling[lead][1][mission][0] += total
timeSpentBilling[lead][1][mission][1].append([consultant, to_int_or_round(charge, 2), rates[mission][consultant][0], total])
# Sort data
timeSpentBilling = timeSpentBilling.items()
timeSpentBilling.sort(key=lambda x: x[0].deal_id)
return render(request, "billing/pre_billing.html",
{"time_spent_billing": timeSpentBilling,
"fixed_price_missions": fixedPriceMissions,
"month": month,
"user": request.user})
def createMissionRow(mission, start_date, end_date):
"""Inner function to create mission row"""
missionRow = []
missionRow.append(get_fiscal_year(start_date))
missionRow.append(end_date.isoformat())
missionRow.append("timesheet")
missionRow.append(mission.nature)
missionRow.append(not mission.active)
if mission.lead:
missionRow.append(mission.lead.subsidiary)
missionRow.append(mission.lead.client.organisation.company.name)
missionRow.append(mission.lead.client.organisation.company.code)
missionRow.append(mission.lead.client.organisation.name)
missionRow.append(mission.lead.name)
missionRow.append(mission.lead.deal_id)
missionRow.append(mission.lead.sales or 0)
missionRow.append(
list(
mission.lead.clientbill_set.filter(
state__in=("1_SENT", "2_PAID"),
creation_date__lt=end_date,
creation_date__gte=start_date).aggregate(
Sum("amount")).values())[0] or 0)
if mission.lead.responsible:
missionRow.append(mission.lead.responsible.name)
missionRow.append(mission.lead.responsible.trigramme)
missionRow.append(
mission.lead.responsible.staffing_manager.trigramme
if mission.lead.responsible.staffing_manager else "")
else:
missionRow.extend(["", "", ""])
else:
missionRow.extend(
[mission.subsidiary, "", "", "", "", "", 0, 0, "", "", ""])
missionRow.append(mission.description or "")
missionRow.append(mission.mission_id())
missionRow.append(mission.mission_analytic_code())
missionRow.append(
mission.analytic_code.description if mission.analytic_code else "")
missionRow.append(mission.billing_mode or "")
missionRow.append(mission.price or 0)
missionRow.extend(mission.done_work_period(None, nextMonth(end_date)))
last_timesheet = Timesheet.objects.filter(mission=mission).aggregate(
Max("working_date"))["working_date__max"]
missionRow.append(last_timesheet.isoformat() if last_timesheet else "")
return missionRow
def forecasted_work(self):
"""Compute forecasted work according to staffing for this mission
Result is cached for few seconds
@return: (forecasted work in days, forecasted work in euros"""
rates = dict([(i.id, j[0]) for i, j in self.consultant_rates().items()
]) # switch to consultant id
days = 0
amount = 0
current_month = date.today().replace(day=1)
staffings = Staffing.objects.filter(mission=self,
staffing_date__gte=current_month)
staffings = staffings.values_list("consultant").annotate(
Sum("charge")).order_by()
current_month_done = Timesheet.objects.filter(
mission=self,
working_date__gte=current_month,
working_date__lt=date.today())
current_month_done = dict(
current_month_done.values_list("consultant").annotate(
Sum("charge")).order_by())
current_month_staffing = Staffing.objects.filter(
mission=self,
staffing_date__gte=current_month,
staffing_date__lt=nextMonth(current_month))
current_month_staffing = dict(
current_month_staffing.values_list("consultant").annotate(
Sum("charge")).order_by())
for consultant_id, charge in staffings:
days += charge # Add forecasted days
current_month_balance = current_month_staffing.get(
consultant_id, 0) - current_month_done.get(consultant_id, 0)
charge_adjustement = 0
if current_month_balance > 0:
charge_adjustement = -current_month_done.get(
consultant_id, 0) # leave remaining forecast
else:
charge_adjustement = -current_month_staffing.get(
consultant_id, 0) # forecast has been exhausted
days += charge_adjustement
if consultant_id in rates:
amount += (charge + charge_adjustement) * rates[consultant_id]
if days < 0:
# Negative forecast, means no forecast.
days = 0
amount = 0
return (days, amount)
def gatherTimesheetData(consultant, missions, month):
"""Gather existing timesheet timesheetData
@returns: (timesheetData, timesheetTotal, warning)
timesheetData represent timesheet form post timesheetData as a dict
timesheetTotal is a dict of total charge (key is mission id)
warning is a list of 0 (ok) or 1 (surbooking) or 2 (no data). One entry per day"""
timesheetData = {}
timesheetTotal = {}
warning = []
totalPerDay = [0] * month_days(month)
next_month = nextMonth(month)
for mission in missions:
timesheets = Timesheet.objects.select_related().filter(consultant=consultant).filter(mission=mission)
timesheets = timesheets.filter(working_date__gte=month).filter(working_date__lt=next_month)
for timesheet in timesheets:
timesheetData["charge_%s_%s" % (timesheet.mission.id, timesheet.working_date.day)] = timesheet.charge
if mission.id in timesheetTotal:
timesheetTotal[mission.id] += timesheet.charge
else:
timesheetTotal[mission.id] = timesheet.charge
totalPerDay[timesheet.working_date.day - 1] += timesheet.charge
# Gather lunck ticket data
totalTicket = 0
lunchTickets = LunchTicket.objects.filter(consultant=consultant)
lunchTickets = lunchTickets.filter(lunch_date__gte=month).filter(lunch_date__lt=next_month)
for lunchTicket in lunchTickets:
timesheetData["lunch_ticket_%s" % lunchTicket.lunch_date.day] = lunchTicket.no_ticket
totalTicket += 1
timesheetTotal["ticket"] = totalTicket
# Compute warnings (overbooking and no data)
for i in totalPerDay:
i = round(i, 4) # We must round because using keyboard time input may lead to real numbers that are truncated
if i > 1: # Surbooking
warning.append(1)
elif i == 1: # Ok
warning.append(0)
else: # warning (no data, or half day)
warning.append(2)
# Don't emit warning for no data during week ends and holidays
holiday_days = holidayDays(month)
for day in daysOfMonth(month):
if day.isoweekday() in (6, 7) or day in holiday_days:
warning[day.day - 1] = None
return (timesheetData, timesheetTotal, warning)
def test_turnover(self):
current_month = date.today().replace(day=1)
next_month = nextMonth(current_month)
previous_month = previousMonth(current_month)
lead = Lead.objects.get(id=1)
c1 = Consultant.objects.get(id=1)
c2 = Consultant.objects.get(id=2)
mission = Mission(lead=lead, subsidiary_id=1, billing_mode="TIME_SPENT", nature="PROD", probability=100)
mission.save()
cache.clear() # avoid bad computation due to rates cache with previous values
# Add some timesheet - we fake with all charge on the first day
Timesheet(mission=mission, working_date=previous_month, consultant=c1, charge=10).save()
Timesheet(mission=mission, working_date=previous_month, consultant=c2, charge=5).save()
Timesheet(mission=mission, working_date=current_month, consultant=c1, charge=10).save()
Timesheet(mission=mission, working_date=current_month, consultant=c2, charge=5).save()
# Add financial conditions for this mission
FinancialCondition(consultant=c1, mission=mission, daily_rate=2000).save()
FinancialCondition(consultant=c2, mission=mission, daily_rate=1000).save()
done_work = (10 + 10) * 2000 + (5 + 5) * 1000
# Define mission price
mission.price = 40
mission.billing_mode = "TIME_SPENT"
mission.save()
# In time spent, turnover is what we did
self.assertEqual(c1.get_turnover(end_date=next_month), 20 * 2000)
mission.billing_mode = "FIXED_PRICE"
mission.save()
# In fixed price, turnover is limited by price in proportion of all work
self.assertEqual(c1.get_turnover(end_date=next_month), 20 * 2000 * mission.price * 1000 / done_work)
self.assertEqual(c1.get_turnover(end_date=next_month) + c2.get_turnover(end_date=next_month), mission.price * 1000)
# Let add some margin by changing mission price.
mission.price = 60
mission.save()
self.assertEqual(c1.get_turnover(end_date=next_month), 20 * 2000) # like in time spent
self.assertEqual(c1.get_turnover(end_date=next_month) + c2.get_turnover(end_date=next_month), done_work)
# Let archive mission to validate margin
mission.active = False
mission.save()
self.assertEqual(c1.get_turnover(end_date=next_month), 20 * 2000 * mission.price * 1000 / done_work) # like in time spent
self.assertEqual(c1.get_turnover(end_date=next_month) + c2.get_turnover(end_date=next_month), mission.price * 1000)
def create_client_bill_from_timesheet(mission, month):
"""Create (and return) a bill and bill detail for given mission from timesheet of given month"""
ClientBill = apps.get_model("billing", "clientbill")
BillDetail = apps.get_model("billing", "billdetail")
bill = ClientBill(lead=mission.lead)
bill.save()
rates = mission.consultant_rates()
timesheet_data = mission.timesheet_set.filter(
working_date__gte=month, working_date__lt=nextMonth(month))
timesheet_data = timesheet_data.order_by("consultant").values(
"consultant").annotate(Sum("charge"))
for i in timesheet_data:
consultant = Consultant.objects.get(id=i["consultant"])
billDetail = BillDetail(bill=bill,
mission=mission,
month=month,
consultant=consultant,
quantity=i["charge__sum"],
unit_price=rates[consultant][0])
billDetail.save()
compute_bill(bill) # update bill amount according to its details
return bill
def financial_control(request, start_date=None, end_date=None):
"""Financial control extraction. This view is intented to be processed by
a spreadsheet or a financial package software"""
if end_date is None:
end_date = previousMonth(datetime.date.today())
else:
end_date = datetime.date(int(end_date[0:4]), int(end_date[4:6]), 1)
if start_date is None:
start_date = previousMonth(previousMonth(datetime.date.today()))
else:
start_date = datetime.date(int(start_date[0:4]), int(start_date[4:6]), 1)
response = HttpResponse(content_type="text/plain")
response["Content-Disposition"] = "attachment; filename=financialControl.dat"
writer = csv.writer(response, delimiter=';')
financialConditions = {}
for fc in FinancialCondition.objects.all():
financialConditions["%s-%s" % (fc.mission_id, fc.consultant_id)] = (fc.daily_rate, fc.bought_daily_rate)
# Header
header = ["FiscalYear", "Month", "Type", "Nature", "Archived",
"Subsidiary", "ClientCompany", "ClientCompanyCode", "ClientOrganization",
"Lead", "DealId", "LeadPrice", "Billed", "LeadResponsible", "LeadResponsibleTrigramme", "LeadTeam",
"Mission", "MissionId", "BillingMode", "MissionPrice",
"TotalQuantityInDays", "TotalQuantityInEuros",
"ConsultantSubsidiary", "ConsultantTeam", "Trigramme", "Consultant", "Subcontractor", "CrossBilling",
"ObjectiveRate", "DailyRate", "BoughtDailyRate", "BudgetType", "QuantityInDays", "QuantityInEuros",
"StartDate", "EndDate"]
writer.writerow(header)
timesheets = Timesheet.objects.filter(working_date__gte=start_date, working_date__lt=nextMonth(end_date))
staffings = Staffing.objects.filter(staffing_date__gte=start_date, staffing_date__lt=nextMonth(end_date))
consultants = dict([(i.trigramme.lower(), i) for i in Consultant.objects.all().select_related()])
missionsIdsFromStaffing = Mission.objects.filter(probability__gt=0, staffing__staffing_date__gte=start_date, staffing__staffing_date__lt=nextMonth(end_date)).values_list("id", flat=True)
missionsIdsFromTimesheet = Mission.objects.filter(probability__gt=0, timesheet__working_date__gte=start_date, timesheet__working_date__lt=nextMonth(end_date)).values_list("id", flat=True)
missionsIds = set(list(missionsIdsFromStaffing) + list(missionsIdsFromTimesheet))
missions = Mission.objects.filter(id__in=missionsIds)
missions = missions.distinct().select_related().prefetch_related("lead__client__organisation__company", "lead__responsible")
def createMissionRow(mission, start_date, end_date):
"""Inner function to create mission row"""
missionRow = []
missionRow.append(start_date.year)
missionRow.append(end_date.isoformat())
missionRow.append("timesheet")
missionRow.append(mission.nature)
missionRow.append(not mission.active)
if mission.lead:
missionRow.append(mission.lead.subsidiary)
missionRow.append(mission.lead.client.organisation.company.name)
missionRow.append(mission.lead.client.organisation.company.code)
missionRow.append(mission.lead.client.organisation.name)
missionRow.append(mission.lead.name)
missionRow.append(mission.lead.deal_id)
missionRow.append(mission.lead.sales or 0)
missionRow.append(list(mission.lead.clientbill_set.filter(state__in=("1_SENT", "2_PAID"), creation_date__lt=end_date, creation_date__gte=start_date).aggregate(Sum("amount")).values())[0] or 0)
if mission.lead.responsible:
missionRow.append(mission.lead.responsible.name)
missionRow.append(mission.lead.responsible.trigramme)
missionRow.append(mission.lead.responsible.staffing_manager.trigramme if mission.lead.responsible.staffing_manager else "")
else:
missionRow.extend(["", "", ""])
else:
missionRow.extend([mission.subsidiary, "", "", "", "", "", 0, 0, "", "", ""])
missionRow.append(mission.description or "")
missionRow.append(mission.mission_id())
missionRow.append(mission.billing_mode or "")
missionRow.append(mission.price or 0)
missionRow.extend(mission.done_work())
return missionRow
for mission in missions:
missionRow = createMissionRow(mission, start_date, end_date)
for consultant in mission.consultants().select_related().prefetch_related("staffing_manager"):
consultantRow = missionRow[:] # copy
daily_rate, bought_daily_rate = financialConditions.get("%s-%s" % (mission.id, consultant.id), [0, 0])
rateObjective = consultant.getRateObjective(end_date, rate_type="DAILY_RATE")
if rateObjective:
rateObjective = rateObjective.rate
else:
rateObjective = 0
doneDays = timesheets.filter(mission_id=mission.id, consultant=consultant.id).aggregate(charge=Sum("charge"), min_date=Min("working_date"), max_date=Max("working_date"))
forecastedDays = staffings.filter(mission_id=mission.id, consultant=consultant.id).aggregate(charge=Sum("charge"), min_date=Min("staffing_date"), max_date=Max("staffing_date"))
consultantRow.append(consultant.company)
consultantRow.append(consultant.staffing_manager.trigramme if consultant.staffing_manager else "")
consultantRow.append(consultant.trigramme)
consultantRow.append(consultant.name)
consultantRow.append(consultant.subcontractor)
if mission.lead:
consultantRow.append(mission.lead.subsidiary != consultant.company)
else:
consultantRow.append(mission.subsidiary != consultant.company)
consultantRow.append(rateObjective)
consultantRow.append(daily_rate or 0)
consultantRow.append(bought_daily_rate or 0)
# Timesheet row
for budgetType, days in (("done", doneDays), ("forecast", forecastedDays)):
quantity = days["charge"] or 0
row = consultantRow[:] # Copy
row.append(budgetType)
row.append(quantity or 0)
row.append((quantity * daily_rate) if (quantity > 0 and daily_rate > 0) else 0)
row.append(days["min_date"] or "")
row.append(days["max_date"] or "")
writer.writerow(row)
archivedMissions = Mission.objects.filter(active=False, archived_date__gte=start_date, archived_date__lt=end_date)
archivedMissions = archivedMissions.filter(lead__state="WON")
archivedMissions = archivedMissions.prefetch_related("lead__client__organisation__company", "lead__responsible")
for mission in archivedMissions:
if mission in missions:
# Mission has already been processed for this period
continue
missionRow = createMissionRow(mission, start_date, end_date)
writer.writerow(missionRow)
for expense in Expense.objects.filter(expense_date__gte=start_date, expense_date__lt=nextMonth(end_date), chargeable=False).select_related():
row = []
row.append(start_date.year)
row.append(end_date.isoformat())
row.append("expense")
row.append(expense.category)
if expense.lead:
row.append(expense.lead.subsidiary)
row.extend(["", "", "", ""])
row.append(expense.lead.deal_id)
else:
row.extend(["", "", "", "", "", ""])
row.extend(["", "", "", "", ""])
try:
consultant = consultants[expense.user.username.lower()]
row.append(consultant.company.name)
row.append(consultant.staffing_manager.trigramme)
row.append(consultant.trigramme)
row.append(consultant.name)
row.append(consultant.subcontractor)
if expense.lead:
row.append(expense.lead.subsidiary != consultant.company)
else:
row.append("unknown for now")
except KeyError:
# Exepense user is not a consultant
row.extend(["", "", "", "", "", ""])
row.extend(["", "", "", "", ""])
row.append(expense.amount) # TODO: compute pseudo HT amount
writer.writerow(row)
return response
def graph_leads_pipe(request):
"""Graph in/out leads for given (or all) subsidiary"""
graph_data = []
input_count = {}
input_amount = {}
output_count = {}
output_amount = {}
output_states = ("WON", "LOST", "FORGIVEN", "SLEEPING")
start_date = (datetime.today() - timedelta(3 * 365))
subsidiary = get_subsidiary_from_session(request)
leads = Lead.objects.filter(creation_date__gt=start_date)
leads = leads.annotate(timesheet_start=Min("mission__timesheet__working_date"))
if subsidiary:
leads = leads.filter(subsidiary=subsidiary)
for lead in leads:
month = lead.creation_date.replace(day=1).date()
input_count[month] = input_count.get(month, 0) + 1
input_amount[month] = input_amount.get(month, 0) + (lead.sales or 0)
if lead.state in output_states:
out_date = lead.timesheet_start or lead.start_date or lead.update_date.date()
out_date = out_date.replace(day=1)
output_count[out_date] = output_count.get(out_date, 0) - 1
output_amount[out_date] = output_amount.get(out_date, 0) - (lead.sales or 0)
pipe_end_date = max(max(output_count.keys()), max(input_count.keys()))
pipe_start_date = min(min(output_count.keys()), min(input_count.keys()))
months = []
month = pipe_start_date
pipe_count = [0] # start with fake 0 to allow sum with previous month
pipe_amount = [0]
while month <= pipe_end_date:
months.append(month)
pipe_count.append(pipe_count[-1] + input_count.get(month, 0) + output_count.get(month, 0))
pipe_amount.append(pipe_amount[-1] + input_amount.get(month, 0) + output_amount.get(month, 0))
month = nextMonth(month)
# Remove fake zero
pipe_count.pop(0)
pipe_amount.pop(0)
# Pad for month without data and switch to list of values
input_count = [input_count.get(month, 0) for month in months]
input_amount = [round(input_amount.get(month, 0)) for month in months]
output_count = [output_count.get(month, 0) for month in months]
output_amount = [round(output_amount.get(month, 0)) for month in months]
# Compute offset by measuring pipe of last month
current_leads = Lead.objects.exclude(state__in=output_states)
if subsidiary:
current_leads = current_leads.filter(subsidiary=subsidiary)
offset_count = current_leads.count() - pipe_count[-1]
pipe_count = [i + offset_count for i in pipe_count]
offset_amount = (current_leads.aggregate(Sum("sales"))["sales__sum"] or 0) - pipe_amount[-1]
pipe_amount = [round(i + offset_amount) for i in pipe_amount]
graph_data.append(["x"] + [i.isoformat() for i in months])
graph_data.append(["input_count"] + input_count)
graph_data.append(["output_count"] + output_count)
graph_data.append(["pipe_count"] + pipe_count)
graph_data.append(["input_amount"] + input_amount)
graph_data.append(["output_amount"] + output_amount)
graph_data.append(["pipe_amount"] + pipe_amount)
count_max = max([max(input_count), -max(output_count), max(pipe_count)])
amount_max = max([max(input_amount), -max(output_amount), max(pipe_amount)])
return render(request, "leads/graph_leads_pipe.html",
{"graph_data": json.dumps(graph_data),
"count_max": count_max,
"amount_max": amount_max,
"series_colors": COLORS,
"user": request.user})
def pre_billing(request, year=None, month=None, mine=False):
"""Pre billing page: help to identify bills to send"""
if year and month:
month = date(int(year), int(month), 1)
else:
month = previousMonth(date.today())
next_month = nextMonth(month)
timeSpentBilling = {} # Key is lead, value is total and dict of mission(total, Mission billingData)
rates = {} # Key is mission, value is Consultant rates dict
try:
billing_consultant = Consultant.objects.get(trigramme__iexact=request.user.username)
except Consultant.DoesNotExist:
billing_consultant = None
mine = False
# Check consultant timesheet to hint if billing could be done based on a clean state
timesheet_ok = {}
for consultant in Consultant.objects.filter(active=True, subcontractor=False):
missions = consultant.timesheet_missions(month=month)
timesheetData, timesheetTotal, warning = gatherTimesheetData(consultant, missions, month)
days = sum([v for (k,v) in timesheetTotal.items() if k!="ticket"]) # Compute timesheet days. Remove lunch ticket count
if days == working_days(month, holidayDays(month=month)):
timesheet_ok[consultant.id] = True
else:
timesheet_ok[consultant.id] = False
fixedPriceMissions = Mission.objects.filter(nature="PROD", billing_mode="FIXED_PRICE",
timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month)
undefinedBillingModeMissions = Mission.objects.filter(nature="PROD", billing_mode=None,
timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month)
if mine:
fixedPriceMissions = fixedPriceMissions.filter(Q(lead__responsible=billing_consultant) | Q(responsible=billing_consultant))
undefinedBillingModeMissions = undefinedBillingModeMissions.filter(Q(lead__responsible=billing_consultant) | Q(responsible=billing_consultant))
fixedPriceMissions = fixedPriceMissions.order_by("lead").distinct()
undefinedBillingModeMissions = undefinedBillingModeMissions.order_by("lead").distinct()
timesheets = Timesheet.objects.filter(working_date__gte=month, working_date__lt=next_month,
mission__nature="PROD", mission__billing_mode="TIME_SPENT")
if mine:
timesheets = timesheets.filter(Q(mission__lead__responsible=billing_consultant) | Q(mission__responsible=billing_consultant))
timesheet_data = timesheets.order_by("mission__lead", "consultant").values_list("mission", "consultant").annotate(Sum("charge"))
for mission_id, consultant_id, charge in timesheet_data:
mission = Mission.objects.select_related("lead").get(id=mission_id)
if mission.lead:
lead = mission.lead
else:
# Bad data, mission with nature prod without lead... This should not happened
continue
consultant = Consultant.objects.get(id=consultant_id)
if not mission in rates:
rates[mission] = mission.consultant_rates()
if not lead in timeSpentBilling:
timeSpentBilling[lead] = [0.0, {}] # Lead Total and dict of mission
if not mission in timeSpentBilling[lead][1]:
timeSpentBilling[lead][1][mission] = [0.0, []] # Mission Total and detail per consultant
total = charge * rates[mission][consultant][0]
timeSpentBilling[lead][0] += total
timeSpentBilling[lead][1][mission][0] += total
timeSpentBilling[lead][1][mission][1].append([consultant, to_int_or_round(charge, 2), rates[mission][consultant][0], total, timesheet_ok.get(consultant_id, True)])
# Sort data
timeSpentBilling = timeSpentBilling.items()
timeSpentBilling.sort(key=lambda x: x[0].deal_id)
return render(request, "billing/pre_billing.html",
{"time_spent_billing": timeSpentBilling,
"fixed_price_missions": fixedPriceMissions,
"undefined_billing_mode_missions": undefinedBillingModeMissions,
"month": month,
"mine": mine,
"user": request.user})
def pre_billing(request, year=None, month=None, mine=False):
"""Pre billing page: help to identify bills to send"""
if year and month:
month = date(int(year), int(month), 1)
else:
month = previousMonth(date.today())
next_month = nextMonth(month)
timeSpentBilling = {
} # Key is lead, value is total and dict of mission(total, Mission billingData)
rates = {} # Key is mission, value is Consultant rates dict
internalBilling = {
} # Same structure as timeSpentBilling but for billing between internal subsidiaries
try:
billing_consultant = Consultant.objects.get(
trigramme__iexact=request.user.username)
except Consultant.DoesNotExist:
billing_consultant = None
mine = False
fixedPriceMissions = Mission.objects.filter(
nature="PROD",
billing_mode="FIXED_PRICE",
timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month)
undefinedBillingModeMissions = Mission.objects.filter(
nature="PROD",
billing_mode=None,
timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month)
timespent_timesheets = Timesheet.objects.filter(
working_date__gte=month,
working_date__lt=next_month,
mission__nature="PROD",
mission__billing_mode="TIME_SPENT")
internalBillingTimesheets = Timesheet.objects.filter(
working_date__gte=month,
working_date__lt=next_month,
mission__nature="PROD")
internalBillingTimesheets = internalBillingTimesheets.exclude(
Q(consultant__company=F("mission__subsidiary"))
& Q(consultant__company=F("mission__lead__subsidiary")))
#TODO: hanlde fixed price mission fully delegated to a subsidiary
if mine: # Filter on consultant mission/lead as responsible
fixedPriceMissions = fixedPriceMissions.filter(
Q(lead__responsible=billing_consultant)
| Q(responsible=billing_consultant))
undefinedBillingModeMissions = undefinedBillingModeMissions.filter(
Q(lead__responsible=billing_consultant)
| Q(responsible=billing_consultant))
timespent_timesheets = timespent_timesheets.filter(
Q(mission__lead__responsible=billing_consultant)
| Q(mission__responsible=billing_consultant))
internalBillingTimesheets = internalBillingTimesheets.filter(
Q(mission__lead__responsible=billing_consultant)
| Q(mission__responsible=billing_consultant))
fixedPriceMissions = fixedPriceMissions.order_by("lead").distinct()
undefinedBillingModeMissions = undefinedBillingModeMissions.order_by(
"lead").distinct()
timesheet_data = timespent_timesheets.order_by(
"mission__lead",
"consultant").values_list("mission",
"consultant").annotate(Sum("charge"))
timeSpentBilling = get_billing_info(timesheet_data)
for subsidiary in Subsidiary.objects.all():
subsidiary_timesheet_data = internalBillingTimesheets.filter(
consultant__company=subsidiary)
for target_subsidiary in Subsidiary.objects.exclude(pk=subsidiary.id):
timesheet_data = subsidiary_timesheet_data.filter(
mission__lead__subsidiary=target_subsidiary)
timesheet_data = timesheet_data.order_by(
"mission__lead",
"consultant").values_list("mission",
"consultant").annotate(Sum("charge"))
billing_info = get_billing_info(timesheet_data)
if billing_info:
internalBilling[(subsidiary, target_subsidiary)] = billing_info
return render(
request, "billing/pre_billing.html", {
"time_spent_billing": timeSpentBilling,
"fixed_price_missions": fixedPriceMissions,
"undefined_billing_mode_missions": undefinedBillingModeMissions,
"internal_billing": internalBilling,
"month": month,
"mine": mine,
"user": request.user
})
def create_client_bill_from_timesheet(mission, month):
"""Create (and return) a bill and bill detail for given mission from timesheet of given month"""
ClientBill = apps.get_model("billing", "clientbill")
BillDetail = apps.get_model("billing", "billdetail")
bill = ClientBill(lead=mission.lead)
bill.save()
rates = mission.consultant_rates()
timesheet_data = mission.timesheet_set.filter(working_date__gte=month, working_date__lt=nextMonth(month))
timesheet_data = timesheet_data.order_by("consultant").values("consultant").annotate(Sum("charge"))
for i in timesheet_data:
consultant = Consultant.objects.get(id=i["consultant"])
billDetail = BillDetail(bill=bill, mission=mission, month=month, consultant=consultant, quantity=i["charge__sum"], unit_price=rates[consultant][0])
billDetail.save()
compute_bill(bill) # update bill amount according to its details
return bill
def consultant_detail(request, consultant_id):
"""Summary page of consultant activity"""
if not request.META.get('HTTP_X_REQUESTED_WITH') == 'XMLHttpRequest':
# This view should only be accessed by ajax request. Redirect lost users
return redirect("people:consultant_home_by_id", consultant_id)
try:
consultant = Consultant.objects.get(id=consultant_id)
staff = consultant.team(onlyActive=True)
month = date.today().replace(day=1)
# Compute consultant current mission based on forecast
missions = consultant.active_missions().filter(nature="PROD").filter(lead__state="WON")
# Identify staled missions that may need new staffing or archiving
staled_missions = [m for m in missions if m.no_more_staffing_since()]
# Consultant clients and missions
business_territory = Company.objects.filter(businessOwner=consultant)
leads_as_responsible = set(consultant.lead_responsible.active())
leads_as_staffee = consultant.lead_set.active()
# Timesheet donut data
holidays = [h.day for h in Holiday.objects.all()]
month_days = working_days(month, holidays, upToToday=False)
done_days = consultant.done_days()
late = working_days(month, holidays, upToToday=True) - done_days
if late < 0:
late = 0 # Don't warn user if timesheet is ok !
# Forecast donut data
forecasted = consultant.forecasted_days()
to_be_done = month_days - late - done_days
forecasting_balance = month_days - forecasted
if forecasting_balance < 0:
overhead = -forecasting_balance
missing = 0
else:
overhead = 0
missing = forecasting_balance
# Turnover
monthTurnover = consultant.getTurnover(month)
lastMonthTurnover = None
day = date.today().day
while lastMonthTurnover is None:
try:
lastMonthTurnover = consultant.getTurnover(previousMonth(month), previousMonth(month).replace(day=day)) # Turnover for last month up to the same day
except ValueError:
# Corner case, last month has fewer days than current one. Go back one day and try again till it works.
lastMonthTurnover = None
day -= 1
if lastMonthTurnover:
turnoverVariation = 100 * (monthTurnover - lastMonthTurnover) / lastMonthTurnover
else:
turnoverVariation = 100
# Daily rate
fc = consultant.getFinancialConditions(month, nextMonth(month))
if fc:
daily_rate = int(sum([rate * days for rate, days in fc]) / sum([days for rate, days in fc]))
else:
daily_rate = 0
daily_rate_objective = consultant.getRateObjective(workingDate=month, rate_type="DAILY_RATE")
if daily_rate_objective:
daily_rate_objective = daily_rate_objective.rate
else:
daily_rate_objective = daily_rate
if daily_rate > daily_rate_objective:
daily_overhead = daily_rate - daily_rate_objective
daily_missing = 0
daily_rate -= daily_overhead
else:
daily_overhead = 0
daily_missing = daily_rate_objective - daily_rate
# Production rate
prod_rate = round(100 * consultant.getProductionRate(month, nextMonth(month)), 1)
prod_rate_objective = consultant.getRateObjective(workingDate=month, rate_type="PROD_RATE")
if prod_rate_objective:
prod_rate_objective = prod_rate_objective.rate
else:
prod_rate_objective = prod_rate
if prod_rate > prod_rate_objective:
prod_overhead = round(prod_rate - prod_rate_objective, 1)
prod_missing = 0
prod_rate -= prod_overhead
else:
prod_overhead = 0
prod_missing = round(prod_rate_objective - prod_rate, 1)
except Consultant.DoesNotExist:
raise Http404
return render(request, "people/consultant_detail.html",
{"consultant": consultant,
"staff": staff,
"missions": missions,
"staled_missions": staled_missions,
"business_territory": business_territory,
"leads_as_responsible": leads_as_responsible,
"leads_as_staffee": leads_as_staffee,
"done_days": done_days,
"late": late,
"to_be_done": to_be_done,
"forecasted": forecasted,
"missing": missing,
"overhead": overhead,
"prod_rate": prod_rate,
"prod_overhead": prod_overhead,
"prod_missing": prod_missing,
"daily_rate": daily_rate,
"daily_overhead": daily_overhead,
"daily_missing": daily_missing,
"month_days": month_days,
"forecasting_balance": forecasting_balance,
"month_turnover": monthTurnover,
"turnover_variation": turnoverVariation,
"tasks": compute_consultant_tasks(consultant),
"user": request.user})
def done_work(self):
"""Compute done work according to timesheet for this mission
Result is cached for few seconds
@return: (done work in days, done work in euros)"""
return self.done_work_period(None, nextMonth(date.today()))
def test_mission_timesheet(self):
self.client.force_login(self.test_user)
current_month = date.today().replace(day=1)
next_month = nextMonth(current_month)
previous_month = previousMonth(current_month)
lead = Lead.objects.get(id=1)
c1 = Consultant.objects.get(id=1)
c2 = Consultant.objects.get(id=2)
mission = Mission(lead=lead, subsidiary_id=1, billing_mode="TIME_SPENT", nature="PROD", probability=100)
mission.save()
cache.clear() # avoid bad computation due to rates cache with previous values
response = self.client.get(urlresolvers.reverse("staffing:mission_timesheet", args=[mission.id,]), follow=True, HTTP_X_REQUESTED_WITH="XMLHttpRequest")
self.assertEqual(response.status_code, 200)
self.assertEqual(response.context["margin"], 0)
self.assertEqual(response.context["objective_margin_total"], 0)
self.assertEqual(response.context["forecasted_unused"], 0)
self.assertEqual(response.context["current_unused"], 0)
self.assertEqual(response.context["avg_daily_rate"], 0)
# Add some forecast
Staffing(mission=mission, staffing_date=current_month, consultant=c1, charge=15).save()
Staffing(mission=mission, staffing_date=current_month, consultant=c2, charge=10).save()
Staffing(mission=mission, staffing_date=next_month, consultant=c1, charge=8).save()
Staffing(mission=mission, staffing_date=next_month, consultant=c2, charge=6).save()
# Add some timesheet - we fake with all charge on the first day
Timesheet(mission=mission, working_date=previous_month, consultant=c1, charge=8).save()
Timesheet(mission=mission, working_date=previous_month, consultant=c2, charge=5).save()
Timesheet(mission=mission, working_date=current_month, consultant=c1, charge=11).save()
Timesheet(mission=mission, working_date=current_month, consultant=c2, charge=9).save()
# Define objective rates for consultants
RateObjective(consultant=c1, start_date=previous_month, rate=700, rate_type="DAILY_RATE").save()
RateObjective(consultant=c2, start_date=previous_month, rate=1050, rate_type="DAILY_RATE").save()
# Add financial conditions for this mission
FinancialCondition(consultant=c1, mission=mission, daily_rate=800).save()
FinancialCondition(consultant=c2, mission=mission, daily_rate=1100).save()
# Define mission price
mission.price = 50
mission.save()
# Let's test if computation are rights
cache.clear() # avoid bad computation due to rates cache with previous values
response = self.client.get(urlresolvers.reverse("staffing:mission_timesheet", args=[mission.id,]), follow=True, HTTP_X_REQUESTED_WITH="XMLHttpRequest")
self.assertEqual(response.status_code, 200)
self.assertEqual(response.context["margin"], 0) # That's because we are in fixed price
self.assertEqual(response.context["objective_margin_total"], 2600)
self.assertEqual(response.context["forecasted_unused"], 2.1)
self.assertEqual(response.context["current_unused"], 19.4)
# Switch to fixed price mission
mission.billing_mode = "FIXED_PRICE"
mission.save()
response = self.client.get(urlresolvers.reverse("staffing:mission_timesheet", args=[mission.id,]), follow=True, HTTP_X_REQUESTED_WITH="XMLHttpRequest")
self.assertEqual(response.status_code, 200)
self.assertEqual(response.context["margin"], 2.1)
self.assertEqual(response.context["objective_margin_total"], 2600)
self.assertEqual(response.context["forecasted_unused"], 0) # Unused is margin in fixes price :-)
self.assertEqual(response.context["current_unused"], 0) # idem
# Check mission data main table
data = list(response.context["mission_data"])
self.assertListEqual(data[0], [c2, [5, 9, 14, 15.4], [1, 6, 7, 7.7], [21, 23.1], None, None, None, None])
self.assertListEqual(data[1], [c1, [8, 11, 19, 15.2], [4, 8, 12, 9.6], [31, 24.8], None, None, None, None])
self.assertListEqual(data[2], [None, [13, 20, 33, 30.6], [5, 14, 19, 17.3], [52, 47.9],
[11.9, 18.7], [4.3, 13],
[915.4, 935, 927.3], [860, 928.6, 910.5]])
def financialControl(request, start_date=None, end_date=None):
"""Financial control extraction. This view is intented to be processed by
a spreadsheet or a financial package software"""
if end_date is None:
end_date = previousMonth(datetime.date.today())
else:
end_date = datetime.date(int(end_date[0:4]), int(end_date[4:6]), 1)
if start_date is None:
start_date = previousMonth(previousMonth(datetime.date.today()))
else:
start_date = datetime.date(int(start_date[0:4]), int(start_date[4:6]), 1)
response = HttpResponse(content_type="text/plain")
response["Content-Disposition"] = "attachment; filename=financialControl.dat"
writer = csv.writer(response, delimiter=';')
financialConditions = {}
for fc in FinancialCondition.objects.all():
financialConditions["%s-%s" % (fc.mission_id, fc.consultant_id)] = (fc.daily_rate, fc.bought_daily_rate)
# Header
header = ["FiscalYear", "Month", "Type", "Nature", "AccountingColumn",
"MissionSubsidiary", "ClientCompany", "ClientCompanyCode", "ClientOrganization",
"Lead", "DealId", "LeadPrice", "LeadResponsible", "LeadResponsibleTrigramme",
"Mission", "MissionId", "BillingMode", "MissionPrice",
"ConsultantSubsidiary", "ConsultantTeam", "Trigramme", "Consultant", "Subcontractor", "CrossBilling",
"ObjectiveRate", "DailyRate", "BoughtDailyRate", "BudgetType", "QuantityInDays", "QuantityInEuros"]
writer.writerow([unicode(i).encode("ISO-8859-15", "ignore") for i in header])
timesheets = Timesheet.objects.filter(working_date__gte=start_date, working_date__lt=nextMonth(end_date))
staffings = Staffing.objects.filter(staffing_date__gte=start_date, staffing_date__lt=nextMonth(end_date))
consultants = dict([(i.trigramme.lower(), i) for i in Consultant.objects.all().select_related()])
missionsIdsFromStaffing = Mission.objects.filter(probability__gt=0, staffing__staffing_date__gte=start_date, staffing__staffing_date__lt=nextMonth(end_date)).values_list("id", flat=True)
missionsIdsFromTimesheet = Mission.objects.filter(probability__gt=0, timesheet__working_date__gte=start_date, timesheet__working_date__lt=nextMonth(end_date)).values_list("id", flat=True)
missionsIds = set(list(missionsIdsFromStaffing) + list(missionsIdsFromTimesheet))
missions = Mission.objects.filter(id__in=missionsIds)
missions = missions.distinct().select_related().prefetch_related("lead__client__organisation__company", "lead__responsible")
for mission in missions:
missionRow = []
missionRow.append(start_date.year)
missionRow.append(end_date.isoformat())
missionRow.append("timesheet")
missionRow.append(mission.nature)
missionRow.append("mission accounting (tbd)")
missionRow.append(mission.subsidiary)
if mission.lead:
missionRow.append(mission.lead.client.organisation.company.name)
missionRow.append(mission.lead.client.organisation.company.code)
missionRow.append(mission.lead.client.organisation.name)
missionRow.append(mission.lead.name)
missionRow.append(mission.lead.deal_id)
missionRow.append(numberformat.format(mission.lead.sales, ",") if mission.lead.sales else 0)
if mission.lead.responsible:
missionRow.append(mission.lead.responsible.name)
missionRow.append(mission.lead.responsible.trigramme)
else:
missionRow.extend(["", ""])
else:
missionRow.extend(["", "", "", "", "", 0, "", ""])
missionRow.append(mission.description or "")
missionRow.append(mission.mission_id())
missionRow.append(mission.billing_mode or "")
missionRow.append(numberformat.format(mission.price, ",") if mission.price else 0)
for consultant in mission.consultants().select_related().prefetch_related("manager"):
consultantRow = missionRow[:] # copy
daily_rate, bought_daily_rate = financialConditions.get("%s-%s" % (mission.id, consultant.id), [0, 0])
rateObjective = consultant.getRateObjective(end_date)
if rateObjective:
rateObjective = rateObjective.daily_rate
else:
rateObjective = 0
doneDays = timesheets.filter(mission_id=mission.id, consultant=consultant.id).aggregate(Sum("charge")).values()[0] or 0
forecastedDays = staffings.filter(mission_id=mission.id, consultant=consultant.id).aggregate(Sum("charge")).values()[0] or 0
consultantRow.append(consultant.company)
consultantRow.append(consultant.manager.trigramme if consultant.manager else "")
consultantRow.append(consultant.trigramme)
consultantRow.append(consultant.name)
consultantRow.append(consultant.subcontractor)
consultantRow.append(mission.subsidiary != consultant.company)
consultantRow.append(numberformat.format(rateObjective, ","))
consultantRow.append(numberformat.format(daily_rate, ",") if daily_rate else 0)
consultantRow.append(numberformat.format(bought_daily_rate, ",") if bought_daily_rate else 0)
# Timesheet row
for budgetType, quantity in (("done", doneDays), ("forecast", forecastedDays)):
row = consultantRow[:] # Copy
row.append(budgetType)
row.append(numberformat.format(quantity, ",") if quantity else 0)
row.append(numberformat.format(quantity * daily_rate, ",") if (quantity > 0 and daily_rate > 0) else 0)
writer.writerow([unicode(i).encode("ISO-8859-15", "ignore") for i in row])
#
for expense in Expense.objects.filter(expense_date__gte=start_date, expense_date__lt=nextMonth(end_date), chargeable=False).select_related():
row = []
row.append(start_date.year)
row.append(end_date.isoformat())
row.append("expense")
row.append(expense.category)
row.append("expense accounting (tbd)")
if expense.lead:
row.append(expense.lead.subsidiary)
row.extend(["", "", "", ""])
row.append(expense.lead.deal_id)
else:
row.extend(["", "", "", "", "", ""])
row.extend(["", "", "", "", ""])
try:
consultant = consultants[expense.user.username.lower()]
row.append(consultant.company.name)
row.append(consultant.manager.trigramme)
row.append(consultant.trigramme)
row.append(consultant.name)
row.append(consultant.subcontractor)
if expense.lead:
row.append(expense.lead.subsidiary != consultant.company)
else:
row.append("unknown for now")
except KeyError:
# Exepense user is not a consultant
row.extend(["", "", "", "", "", ""])
row.extend(["", "", "", "", ""])
row.append(expense.amount) # TODO: compute pseudo HT amount
writer.writerow([unicode(i).encode("ISO-8859-15", "ignore") for i in row])
return response
def forecasted_work(self):
"""Compute forecasted work according to staffing for this mission
Result is cached for few seconds
@return: (forecasted work in days, forecasted work in euros"""
rates = dict([(i.id, j[0]) for i, j in self.consultant_rates().items()]) # switch to consultant id
days = 0
amount = 0
current_month = date.today().replace(day=1)
staffings = Staffing.objects.filter(mission=self, staffing_date__gte=current_month)
staffings = staffings.values_list("consultant").annotate(Sum("charge")).order_by()
current_month_done = Timesheet.objects.filter(mission=self, working_date__gte=current_month, working_date__lt=nextMonth(date.today()))
current_month_done = dict(current_month_done.values_list("consultant").annotate(Sum("charge")).order_by())
for consultant_id, charge in staffings:
days += charge # Add forecasted days
days -= current_month_done.get(consultant_id, 0) # Substract current month done works from forecastinng
if consultant_id in rates:
amount += charge * rates[consultant_id]
amount -= current_month_done.get(consultant_id, 0) * rates[consultant_id]
return (days, amount)
def pre_billing(request, year=None, month=None, mine=False):
"""Pre billing page: help to identify bills to send"""
if year and month:
month = date(int(year), int(month), 1)
else:
month = previousMonth(date.today())
next_month = nextMonth(month)
timeSpentBilling = {
} # Key is lead, value is total and dict of mission(total, Mission billingData)
rates = {} # Key is mission, value is Consultant rates dict
try:
billing_consultant = Consultant.objects.get(
trigramme__iexact=request.user.username)
except Consultant.DoesNotExist:
billing_consultant = None
mine = False
# Check consultant timesheet to hint if billing could be done based on a clean state
timesheet_ok = {}
for consultant in Consultant.objects.filter(active=True,
subcontractor=False):
missions = consultant.timesheet_missions(month=month)
timesheetData, timesheetTotal, warning = gatherTimesheetData(
consultant, missions, month)
days = sum([v for (k, v) in timesheetTotal.items() if k != "ticket"
]) # Compute timesheet days. Remove lunch ticket count
if days == working_days(month, holidayDays(month=month)):
timesheet_ok[consultant.id] = True
else:
timesheet_ok[consultant.id] = False
fixedPriceMissions = Mission.objects.filter(
nature="PROD",
billing_mode="FIXED_PRICE",
timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month)
undefinedBillingModeMissions = Mission.objects.filter(
nature="PROD",
billing_mode=None,
timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month)
if mine:
fixedPriceMissions = fixedPriceMissions.filter(
Q(lead__responsible=billing_consultant)
| Q(responsible=billing_consultant))
undefinedBillingModeMissions = undefinedBillingModeMissions.filter(
Q(lead__responsible=billing_consultant)
| Q(responsible=billing_consultant))
fixedPriceMissions = fixedPriceMissions.order_by("lead").distinct()
undefinedBillingModeMissions = undefinedBillingModeMissions.order_by(
"lead").distinct()
timesheets = Timesheet.objects.filter(working_date__gte=month,
working_date__lt=next_month,
mission__nature="PROD",
mission__billing_mode="TIME_SPENT")
if mine:
timesheets = timesheets.filter(
Q(mission__lead__responsible=billing_consultant)
| Q(mission__responsible=billing_consultant))
timesheet_data = timesheets.order_by(
"mission__lead",
"consultant").values_list("mission",
"consultant").annotate(Sum("charge"))
for mission_id, consultant_id, charge in timesheet_data:
mission = Mission.objects.select_related("lead").get(id=mission_id)
if mission.lead:
lead = mission.lead
else:
# Bad data, mission with nature prod without lead... This should not happened
continue
consultant = Consultant.objects.get(id=consultant_id)
if not mission in rates:
rates[mission] = mission.consultant_rates()
if not lead in timeSpentBilling:
timeSpentBilling[lead] = [0.0,
{}] # Lead Total and dict of mission
if not mission in timeSpentBilling[lead][1]:
timeSpentBilling[lead][1][mission] = [
0.0, []
] # Mission Total and detail per consultant
total = charge * rates[mission][consultant][0]
timeSpentBilling[lead][0] += total
timeSpentBilling[lead][1][mission][0] += total
timeSpentBilling[lead][1][mission][1].append([
consultant,
to_int_or_round(charge, 2), rates[mission][consultant][0], total,
timesheet_ok.get(consultant_id, True)
])
# Sort data
timeSpentBilling = timeSpentBilling.items()
timeSpentBilling.sort(key=lambda x: x[0].deal_id)
return render(
request, "billing/pre_billing.html", {
"time_spent_billing": timeSpentBilling,
"fixed_price_missions": fixedPriceMissions,
"undefined_billing_mode_missions": undefinedBillingModeMissions,
"month": month,
"mine": mine,
"user": request.user
})
def consultant_detail(request, consultant_id):
"""Summary page of consultant activity"""
if not request.META.get('HTTP_X_REQUESTED_WITH') == 'XMLHttpRequest':
# This view should only be accessed by ajax request. Redirect lost users
return redirect("people:consultant_home_by_id", consultant_id)
try:
consultant = Consultant.objects.get(id=consultant_id)
staff = consultant.team(onlyActive=True)
month = date.today().replace(day=1)
# Compute consultant current mission based on forecast
missions = consultant.active_missions().filter(nature="PROD").filter(lead__state="WON")
# Identify staled missions that may need new staffing or archiving
staled_missions = [m for m in missions if m.no_more_staffing_since()]
# Consultant clients and missions
companies = Company.objects.filter(clientorganisation__client__lead__mission__timesheet__consultant=consultant).distinct()
business_territory = Company.objects.filter(businessOwner=consultant)
leads_as_responsible = set(consultant.lead_responsible.active())
leads_as_staffee = consultant.lead_set.active()
# Timesheet donut data
holidays = [h.day for h in Holiday.objects.all()]
month_days = working_days(month, holidays, upToToday=False)
done_days = consultant.done_days()
late = working_days(month, holidays, upToToday=True) - done_days
if late < 0:
late = 0 # Don't warn user if timesheet is ok !
# Forecast donut data
forecasted = consultant.forecasted_days()
to_be_done = month_days - late - done_days
forecasting_balance = month_days - forecasted
if forecasting_balance < 0:
overhead = -forecasting_balance
missing = 0
else:
overhead = 0
missing = forecasting_balance
# Turnover
monthTurnover = consultant.getTurnover(month)
lastMonthTurnover = None
day = date.today().day
while lastMonthTurnover is None:
try:
lastMonthTurnover = consultant.getTurnover(previousMonth(month), previousMonth(month).replace(day=day)) # Turnover for last month up to the same day
except ValueError:
# Corner case, last month has fewer days than current one. Go back one day and try again till it works.
lastMonthTurnover = None
day -= 1
if lastMonthTurnover:
turnoverVariation = 100 * (monthTurnover - lastMonthTurnover) / lastMonthTurnover
else:
turnoverVariation = 100
# Daily rate
fc = consultant.getFinancialConditions(month, nextMonth(month))
if fc:
daily_rate = int(sum([rate * days for rate, days in fc]) / sum([days for rate, days in fc]))
else:
daily_rate = 0
daily_rate_objective = consultant.getRateObjective(workingDate=month, rate_type="DAILY_RATE")
if daily_rate_objective:
daily_rate_objective = daily_rate_objective.rate
else:
daily_rate_objective = daily_rate
if daily_rate > daily_rate_objective:
daily_overhead = daily_rate - daily_rate_objective
daily_missing = 0
daily_rate -= daily_overhead
else:
daily_overhead = 0
daily_missing = daily_rate_objective - daily_rate
# Production rate
prod_rate = round(100 * consultant.getProductionRate(month, nextMonth(month)), 1)
prod_rate_objective = consultant.getRateObjective(workingDate=month, rate_type="PROD_RATE")
if prod_rate_objective:
prod_rate_objective = prod_rate_objective.rate
else:
prod_rate_objective = prod_rate
if prod_rate > prod_rate_objective:
prod_overhead = round(prod_rate - prod_rate_objective, 1)
prod_missing = 0
prod_rate -= prod_overhead
else:
prod_overhead = 0
prod_missing = round(prod_rate_objective - prod_rate, 1)
except Consultant.DoesNotExist:
raise Http404
return render(request, "people/consultant_detail.html",
{"consultant": consultant,
"staff": staff,
"missions": missions,
"staled_missions": staled_missions,
"companies": companies,
"business_territory": business_territory,
"leads_as_responsible": leads_as_responsible,
"leads_as_staffee": leads_as_staffee,
"done_days": done_days,
"late": late,
"to_be_done": to_be_done,
"forecasted": forecasted,
"missing": missing,
"overhead": overhead,
"prod_rate": prod_rate,
"prod_overhead": prod_overhead,
"prod_missing": prod_missing,
"daily_rate": daily_rate,
"daily_overhead": daily_overhead,
"daily_missing": daily_missing,
"month_days": month_days,
"forecasting_balance": forecasting_balance,
"month_turnover": monthTurnover,
"turnover_variation": turnoverVariation,
"user": request.user})
def financial_control(request, start_date=None, end_date=None):
"""Financial control extraction. This view is intented to be processed by
a spreadsheet or a financial package software"""
if end_date is None:
end_date = previousMonth(datetime.date.today())
else:
end_date = datetime.date(int(end_date[0:4]), int(end_date[4:6]), 1)
if start_date is None:
start_date = previousMonth(previousMonth(datetime.date.today()))
else:
start_date = datetime.date(int(start_date[0:4]), int(start_date[4:6]),
1)
response = HttpResponse(content_type="text/plain")
response[
"Content-Disposition"] = "attachment; filename=financialControl.dat"
writer = csv.writer(response, delimiter=';')
financialConditions = {}
for fc in FinancialCondition.objects.all():
financialConditions["%s-%s" % (fc.mission_id, fc.consultant_id)] = (
fc.daily_rate, fc.bought_daily_rate)
# Header
header = [
"FiscalYear", "Month", "Type", "Nature", "Archived", "Subsidiary",
"ClientCompany", "ClientCompanyCode", "ClientOrganization", "Lead",
"DealId", "LeadPrice", "Billed", "LeadResponsible",
"LeadResponsibleTrigramme", "LeadTeam", "Mission", "MissionId",
"AnalyticCode", "AnalyticDescription", "BillingMode", "MissionPrice",
"TotalQuantityInDays", "TotalQuantityInEuros", "LastTimesheet",
"ConsultantSubsidiary", "ConsultantTeam", "Trigramme", "Consultant",
"Subcontractor", "CrossBilling", "ObjectiveRate", "DailyRate",
"BoughtDailyRate", "BudgetType", "QuantityInDays", "QuantityInEuros",
"StartDate", "EndDate"
]
writer.writerow(header)
timesheets = Timesheet.objects.filter(working_date__gte=start_date,
working_date__lt=nextMonth(end_date))
staffings = Staffing.objects.filter(staffing_date__gte=start_date,
staffing_date__lt=nextMonth(end_date))
consultants = dict([(i.trigramme.lower(), i)
for i in Consultant.objects.all().select_related()])
missionsIdsFromStaffing = Mission.objects.filter(
probability__gt=0,
staffing__staffing_date__gte=start_date,
staffing__staffing_date__lt=nextMonth(end_date)).values_list("id",
flat=True)
missionsIdsFromTimesheet = Mission.objects.filter(
probability__gt=0,
timesheet__working_date__gte=start_date,
timesheet__working_date__lt=nextMonth(end_date)).values_list("id",
flat=True)
missionsIds = set(
list(missionsIdsFromStaffing) + list(missionsIdsFromTimesheet))
missions = Mission.objects.filter(id__in=missionsIds)
missions = missions.distinct().select_related().prefetch_related(
"lead__client__organisation__company", "lead__responsible")
def createMissionRow(mission, start_date, end_date):
"""Inner function to create mission row"""
missionRow = []
missionRow.append(get_fiscal_year(start_date))
missionRow.append(end_date.isoformat())
missionRow.append("timesheet")
missionRow.append(mission.nature)
missionRow.append(not mission.active)
if mission.lead:
missionRow.append(mission.lead.subsidiary)
missionRow.append(mission.lead.client.organisation.company.name)
missionRow.append(mission.lead.client.organisation.company.code)
missionRow.append(mission.lead.client.organisation.name)
missionRow.append(mission.lead.name)
missionRow.append(mission.lead.deal_id)
missionRow.append(mission.lead.sales or 0)
missionRow.append(
list(
mission.lead.clientbill_set.filter(
state__in=("1_SENT", "2_PAID"),
creation_date__lt=end_date,
creation_date__gte=start_date).aggregate(
Sum("amount")).values())[0] or 0)
if mission.lead.responsible:
missionRow.append(mission.lead.responsible.name)
missionRow.append(mission.lead.responsible.trigramme)
missionRow.append(
mission.lead.responsible.staffing_manager.trigramme
if mission.lead.responsible.staffing_manager else "")
else:
missionRow.extend(["", "", ""])
else:
missionRow.extend(
[mission.subsidiary, "", "", "", "", "", 0, 0, "", "", ""])
missionRow.append(mission.description or "")
missionRow.append(mission.mission_id())
missionRow.append(mission.mission_analytic_code())
missionRow.append(
mission.analytic_code.description if mission.analytic_code else "")
missionRow.append(mission.billing_mode or "")
missionRow.append(mission.price or 0)
missionRow.extend(mission.done_work_period(None, nextMonth(end_date)))
last_timesheet = Timesheet.objects.filter(mission=mission).aggregate(
Max("working_date"))["working_date__max"]
missionRow.append(last_timesheet.isoformat() if last_timesheet else "")
return missionRow
for mission in missions:
missionRow = createMissionRow(mission, start_date, end_date)
for consultant in mission.consultants().select_related(
).prefetch_related("staffing_manager"):
consultantRow = missionRow[:] # copy
daily_rate, bought_daily_rate = financialConditions.get(
"%s-%s" % (mission.id, consultant.id), [0, 0])
rateObjective = consultant.get_rate_objective(
working_date=end_date, rate_type="DAILY_RATE")
if rateObjective:
rateObjective = rateObjective.rate
else:
rateObjective = 0
doneDays = timesheets.filter(mission_id=mission.id,
consultant=consultant.id).aggregate(
charge=Sum("charge"),
min_date=Min("working_date"),
max_date=Max("working_date"))
forecastedDays = staffings.filter(
mission_id=mission.id, consultant=consultant.id).aggregate(
charge=Sum("charge"),
min_date=Min("staffing_date"),
max_date=Max("staffing_date"))
consultantRow.append(consultant.company)
consultantRow.append(consultant.staffing_manager.trigramme
if consultant.staffing_manager else "")
consultantRow.append(consultant.trigramme)
consultantRow.append(consultant.name)
consultantRow.append(consultant.subcontractor)
if mission.lead:
consultantRow.append(
mission.lead.subsidiary != consultant.company)
else:
consultantRow.append(mission.subsidiary != consultant.company)
consultantRow.append(rateObjective)
consultantRow.append(daily_rate or 0)
consultantRow.append(bought_daily_rate or 0)
# Timesheet row
for budgetType, days in (("done", doneDays), ("forecast",
forecastedDays)):
quantity = days["charge"] or 0
row = consultantRow[:] # Copy
row.append(budgetType)
row.append(quantity or 0)
row.append((quantity * daily_rate) if (
quantity > 0 and daily_rate > 0) else 0)
row.append(days["min_date"] or "")
row.append(days["max_date"] or "")
writer.writerow(row)
archivedMissions = Mission.objects.filter(active=False,
archived_date__gte=start_date,
archived_date__lt=end_date)
archivedMissions = archivedMissions.filter(lead__state="WON")
archivedMissions = archivedMissions.prefetch_related(
"lead__client__organisation__company", "lead__responsible")
for mission in archivedMissions:
if mission in missions:
# Mission has already been processed for this period
continue
missionRow = createMissionRow(mission, start_date, end_date)
writer.writerow(missionRow)
for expense in Expense.objects.filter(expense_date__gte=start_date,
expense_date__lt=nextMonth(end_date),
chargeable=False).select_related():
row = []
row.append(get_fiscal_year(start_date))
row.append(end_date.isoformat())
row.append("expense")
row.append(expense.category)
if expense.lead:
row.append(expense.lead.subsidiary)
row.extend(["", "", "", ""])
row.append(expense.lead.deal_id)
else:
row.extend(["", "", "", "", "", ""])
row.extend(["", "", "", "", ""])
try:
consultant = consultants[expense.user.username.lower()]
row.append(consultant.company.name)
row.append(consultant.staffing_manager.trigramme)
row.append(consultant.trigramme)
row.append(consultant.name)
row.append(consultant.subcontractor)
if expense.lead:
row.append(expense.lead.subsidiary != consultant.company)
else:
row.append("unknown for now")
except KeyError:
# Exepense user is not a consultant
row.extend(["", "", "", "", "", ""])
row.extend(["", "", "", "", ""])
row.append(expense.amount) # TODO: compute pseudo HT amount
writer.writerow(row)
return response
def graph_people_count(request, subsidiary_id=None, team_id=None):
"""Active people count
@:param subsidiary_id: filter graph on the given subsidiary
@:param team_id: filter graph on the given team"""
#TODO: add start/end timeframe
graph_data = []
iso_months = []
start_date = (date.today() - 3 * timedelta(365)).replace(day=1) # Last three years
end_date = date.today().replace(day=1)
consultants_count = {}
subcontractors_count = {}
consultants = Consultant.objects.filter(subcontractor=False, productive=True)
subcontractors = Consultant.objects.filter(subcontractor=True, productive=True)
subsidiaries = Subsidiary.objects.filter(mission__nature="PROD")
subsidiaries = subsidiaries.annotate(Count("mission__timesheet__consultant"))
subsidiaries = subsidiaries.filter(mission__timesheet__consultant__count__gt=0)
if subsidiary_id:
subsidiaries = subsidiaries.filter(subsidiary_id=subsidiary_id)
for subsidiary in subsidiaries:
consultants_count[subsidiary] = []
subcontractors_count[subsidiary] = []
# Filter on scope
if team_id:
consultants = consultants.filter(staffing_manager_id=team_id)
subcontractors = subcontractors.filter(staffing_manageid=0) # Don't consider subcontractors for team counting
elif subsidiary_id:
consultants = consultants.filter(company_id=subsidiary_id)
subcontractors = subcontractors.filter(timesheet__mission__subsidiar__id=subsidiary_id)
month = start_date
while month < end_date:
next_month = nextMonth(month)
iso_months.append(month.isoformat())
for subsidiary in subsidiaries:
consultants_count[subsidiary].append(consultants.filter(company=subsidiary,
timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month).distinct().count())
subcontractors_count[subsidiary].append(subcontractors.filter(timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month,
timesheet__mission__subsidiary=subsidiary,
timesheet__mission__nature="PROD").distinct().count())
month = next_month
if not iso_months or set(consultants_count) == {None}:
return HttpResponse('')
graph_data.append(["x"] + iso_months)
for subsidiary in subsidiaries:
graph_data.append([_("consultants %s" % subsidiary)] + consultants_count[subsidiary])
graph_data.append([_("subcontractors %s" % subsidiary)] + subcontractors_count[subsidiary])
return render(request, "people/graph_people_count.html",
{"graph_data": json.dumps(graph_data),
"series_colors": COLORS,
"subsidiaries": subsidiaries,
"user": request.user})
def pre_billing(request, start_date=None, end_date=None, mine=False):
"""Pre billing page: help to identify bills to send"""
subsidiary = get_subsidiary_from_session(request)
if end_date is None:
end_date = date.today().replace(day=1)
else:
end_date = date(int(end_date[0:4]), int(end_date[4:6]), 1)
if start_date is None:
start_date = previousMonth(date.today())
else:
start_date = date(int(start_date[0:4]), int(start_date[4:6]), 1)
if end_date - start_date > timedelta(180):
# Prevent excessive window that is useless would lead to deny of service
start_date = (end_date - timedelta(180)).replace(day=1)
if end_date < start_date:
end_date = nextMonth(start_date)
timeSpentBilling = {
} # Key is lead, value is total and dict of mission(total, Mission billingData)
rates = {} # Key is mission, value is Consultant rates dict
internalBilling = {
} # Same structure as timeSpentBilling but for billing between internal subsidiaries
try:
billing_consultant = Consultant.objects.get(
trigramme__iexact=request.user.username)
except Consultant.DoesNotExist:
billing_consultant = None
mine = False
fixedPriceMissions = Mission.objects.filter(
nature="PROD",
billing_mode="FIXED_PRICE",
timesheet__working_date__gte=start_date,
timesheet__working_date__lt=end_date)
undefinedBillingModeMissions = Mission.objects.filter(
nature="PROD",
billing_mode=None,
timesheet__working_date__gte=start_date,
timesheet__working_date__lt=end_date)
timespent_timesheets = Timesheet.objects.filter(
working_date__gte=start_date,
working_date__lt=end_date,
mission__nature="PROD",
mission__billing_mode="TIME_SPENT")
internalBillingTimesheets = Timesheet.objects.filter(
working_date__gte=start_date,
working_date__lt=end_date,
mission__nature="PROD")
internalBillingTimesheets = internalBillingTimesheets.exclude(
Q(consultant__company=F("mission__subsidiary"))
& Q(consultant__company=F("mission__lead__subsidiary")))
#TODO: hanlde fixed price mission fully delegated to a subsidiary
if mine: # Filter on consultant mission/lead as responsible
fixedPriceMissions = fixedPriceMissions.filter(
Q(lead__responsible=billing_consultant)
| Q(responsible=billing_consultant))
undefinedBillingModeMissions = undefinedBillingModeMissions.filter(
Q(lead__responsible=billing_consultant)
| Q(responsible=billing_consultant))
timespent_timesheets = timespent_timesheets.filter(
Q(mission__lead__responsible=billing_consultant)
| Q(mission__responsible=billing_consultant))
internalBillingTimesheets = internalBillingTimesheets.filter(
Q(mission__lead__responsible=billing_consultant)
| Q(mission__responsible=billing_consultant))
fixedPriceMissions = fixedPriceMissions.order_by("lead").distinct()
undefinedBillingModeMissions = undefinedBillingModeMissions.order_by(
"lead").distinct()
if subsidiary: # filter on subsidiary
fixedPriceMissions = fixedPriceMissions.filter(subsidiary=subsidiary)
timespent_timesheets = timespent_timesheets.filter(
mission__subsidiary=subsidiary)
undefinedBillingModeMissions = undefinedBillingModeMissions.filter(
subsidiary=subsidiary)
timesheet_data = timespent_timesheets.order_by(
"mission__lead",
"consultant").values_list("mission",
"consultant").annotate(Sum("charge"))
timeSpentBilling = get_billing_info(timesheet_data)
for subsidiary in Subsidiary.objects.all():
subsidiary_timesheet_data = internalBillingTimesheets.filter(
consultant__company=subsidiary)
for target_subsidiary in Subsidiary.objects.exclude(pk=subsidiary.id):
timesheet_data = subsidiary_timesheet_data.filter(
mission__lead__subsidiary=target_subsidiary)
timesheet_data = timesheet_data.order_by(
"mission__lead",
"consultant").values_list("mission",
"consultant").annotate(Sum("charge"))
billing_info = get_billing_info(timesheet_data)
if billing_info:
internalBilling[(subsidiary, target_subsidiary)] = billing_info
return render(
request, "billing/pre_billing.html", {
"time_spent_billing": timeSpentBilling,
"fixed_price_missions": fixedPriceMissions,
"undefined_billing_mode_missions": undefinedBillingModeMissions,
"internal_billing": internalBilling,
"start_date": start_date,
"end_date": end_date,
"mine": mine,
"user": request.user
})
def get_client_billing_control_pivotable_data(filter_on_subsidiary=None,
filter_on_company=None,
filter_on_lead=None,
only_active=False):
"""Compute pivotable to check lead/mission billing."""
# local import to avoid circurlar weirdness
ClientBill = apps.get_model("billing", "ClientBill")
BillDetail = apps.get_model("billing", "BillDetail")
BillExpense = apps.get_model("billing", "BillExpense")
Lead = apps.get_model("leads", "Lead")
Expense = apps.get_model("expense", "Expense")
Consultant = apps.get_model("people", "Consultant")
data = []
bill_state = ("1_SENT", "2_PAID"
) # Only consider clients bills in those status
leads = Lead.objects.all()
if filter_on_subsidiary:
leads = leads.filter(subsidiary=filter_on_subsidiary)
if filter_on_company:
leads = leads.filter(client__organisation__company=filter_on_company)
if filter_on_lead:
leads = leads.filter(id=filter_on_lead.id)
if only_active:
leads = leads.filter(mission__active=True).distinct()
leads = leads.select_related("client__organisation__company",
"business_broker__company", "subsidiary")
for lead in leads:
lead_data = {
_("deal id"): lead.deal_id,
_("client organisation"): str(lead.client.organisation),
_("client company"): str(lead.client.organisation.company),
_("broker"): str(lead.business_broker or _("Direct")),
_("subsidiary"): str(lead.subsidiary),
_("responsible"): str(lead.responsible),
_("consultant"): "-"
}
# Add legacy bills non related to specific mission (ie. not using pydici billing, just header and pdf payload)
legacy_bills = ClientBill.objects.filter(
lead=lead,
state__in=bill_state).annotate(Count("billdetail"),
Count("billexpense")).filter(
billdetail__count=0,
billexpense__count=0)
for legacy_bill in legacy_bills:
legacy_bill_data = lead_data.copy()
legacy_bill_data[_("amount")] = -float(legacy_bill.amount or 0)
legacy_bill_data[_("month")] = legacy_bill.creation_date.replace(
day=1).isoformat()
legacy_bill_data[_("type")] = _("Service bill")
legacy_bill_data[_("mission")] = "-"
mission = lead.mission_set.first()
if mission: # default to billing mode of first mission. Not 100% accurate...
legacy_bill_data[_(
"billing mode")] = mission.get_billing_mode_display()
data.append(legacy_bill_data)
# Add chargeable expense
expenses = Expense.objects.filter(lead=lead, chargeable=True)
bill_expenses = BillExpense.objects.filter(bill__lead=lead).exclude(
expense_date=None)
for qs, label, way in ((expenses, _("Expense"), 1),
(bill_expenses, _("Expense bill"), -1)):
qs = qs.annotate(month=TruncMonth("expense_date")).order_by(
"month").values("month")
for month, amount in qs.annotate(Sum("amount")).values_list(
"month", "amount__sum"):
expense_data = lead_data.copy()
expense_data[_("month")] = month.isoformat()
expense_data[_("type")] = label
expense_data[_("billing mode")] = _("Chargeable expense")
expense_data[_("amount")] = float(amount) * way
data.append(expense_data)
# Add new-style client bills and done work per mission
for mission in lead.mission_set.all().select_related("responsible"):
mission_data = lead_data.copy()
mission_data[_("mission")] = mission.short_name()
mission_data[_("responsible")] = str(mission.responsible
or mission.lead.responsible)
mission_data[_(
"billing mode")] = mission.get_billing_mode_display()
# Add fixed price bills
if mission.billing_mode == "FIXED_PRICE":
for billDetail in BillDetail.objects.filter(
mission=mission, bill__state__in=bill_state):
mission_fixed_price_data = mission_data.copy()
mission_fixed_price_data[_(
"month")] = billDetail.bill.creation_date.replace(
day=1).isoformat()
mission_fixed_price_data[_("type")] = _("Service bill")
mission_fixed_price_data[_("amount")] = -float(
billDetail.amount or 0)
data.append((mission_fixed_price_data))
# Add done work and time spent bills
consultants = Consultant.objects.filter(
timesheet__mission=mission).distinct()
for month in mission.timesheet_set.dates("working_date",
"month",
order="ASC"):
next_month = nextMonth(month)
for consultant in consultants:
mission_month_consultant_data = mission_data.copy()
turnover = float(
mission.done_work_period(
month,
next_month,
include_external_subcontractor=True,
include_internal_subcontractor=True,
filter_on_consultant=consultant)[1])
mission_month_consultant_data[_("consultant")] = str(
consultant)
mission_month_consultant_data[_(
"month")] = month.isoformat()
mission_month_consultant_data[_("amount")] = turnover
mission_month_consultant_data[_("type")] = _("Done work")
data.append(mission_month_consultant_data)
if mission.billing_mode == "TIME_SPENT":
# Add bills for time spent mission
mission_month_consultant_data = mission_month_consultant_data.copy(
)
billed = BillDetail.objects.filter(
mission=mission,
consultant=consultant,
month=month,
bill__state__in=bill_state)
billed = float(
billed.aggregate(Sum("amount"))["amount__sum"]
or 0)
mission_month_consultant_data[_("amount")] = -billed
mission_month_consultant_data[_("type")] = _(
"Service bill")
data.append(mission_month_consultant_data)
return json.dumps(data)
def graph_people_count(request):
"""Active people count"""
#TODO: add start/end timeframe
graph_data = []
iso_months = []
start_date = (date.today() - 3 * timedelta(365)).replace(
day=1) # Last three years
end_date = date.today().replace(day=1)
consultants_count = {}
subcontractors_count = {}
consultants = Consultant.objects.filter(subcontractor=False,
productive=True)
subcontractors = Consultant.objects.filter(subcontractor=True,
productive=True)
subsidiary = get_subsidiary_from_session(request)
if subsidiary:
subsidiaries = [
subsidiary,
]
consultants = consultants.filter(company=subsidiary)
subcontractors = subcontractors.filter(
timesheet__mission__subsidiary=subsidiary)
else:
subsidiaries = Subsidiary.objects.filter(
mission__nature="PROD").distinct()
subsidiaries = subsidiaries.annotate(
Count("mission__timesheet__consultant"))
subsidiaries = subsidiaries.filter(
mission__timesheet__consultant__count__gt=0)
for subsidiary in subsidiaries:
consultants_count[subsidiary] = []
subcontractors_count[subsidiary] = []
month = start_date
while month < end_date:
next_month = nextMonth(month)
iso_months.append(month.isoformat())
for subsidiary in subsidiaries:
consultants_count[subsidiary].append(
consultants.filter(
company=subsidiary,
timesheet__mission__nature__in=("PROD", "NONPROD"),
timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month).distinct().count())
subcontractors_count[subsidiary].append(
subcontractors.filter(
timesheet__working_date__gte=month,
timesheet__working_date__lt=next_month,
timesheet__mission__subsidiary=subsidiary,
timesheet__mission__nature="PROD").distinct().count())
month = next_month
if not iso_months or set(consultants_count) == {None}:
return HttpResponse('')
graph_data.append(["x"] + iso_months)
for subsidiary in subsidiaries:
graph_data.append([_("consultants %s" % subsidiary)] +
consultants_count[subsidiary])
graph_data.append([_("subcontractors %s" % subsidiary)] +
subcontractors_count[subsidiary])
return render(
request, "people/graph_people_count.html", {
"graph_data": json.dumps(graph_data),
"series_colors": COLORS,
"subsidiaries": subsidiaries,
"user": request.user
})
def done_work(self):
"""Compute done work according to timesheet for this mission
Result is cached for few seconds
@return: (done work in days, done work in euros)"""
return self.done_work_period(None, nextMonth(date.today()))
