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 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 render_column(self, row, column):
if column == "user":
return link_to_consultant(row.user)
elif column == "receipt":
return self.receipt_template.render(
RequestContext(self.request, {"record": row}))
elif column == "lead":
if row.lead:
return u"<a href='{0}'>{1}</a>".format(
row.lead.get_absolute_url(), row.lead)
else:
return u"-"
elif column in ("creation_date", "expense_date"):
return self.date_template.render({"date": getattr(row, column)})
elif column == "update_date":
return row.update_date.strftime("%x %X")
elif column in ("chargeable", "corporate_card"):
if getattr(row, column):
return self.ok_sign
else:
return self.ko_sign
elif column == "state":
return self.state_template.render(
RequestContext(self.request, {"record": row}))
elif column == "amount":
return to_int_or_round(row.amount, 2)
else:
return super(ExpenseTableDT, self).render_column(row, column)
def render_column(self, row, column):
if column in ("amount", "amount_with_vat"):
return to_int_or_round(getattr(row, column), 2)
elif column == "lead":
if row.lead:
return "<a href='{0}'>{1}</a>".format(
row.lead.get_absolute_url(), row.lead)
else:
return "-"
elif column in ("creation_date", "due_date", "payment_date"):
attr = getattr(row, column)
if attr:
return attr.strftime("%d/%m/%y")
else:
return "-"
elif column == "state":
return row.get_state_display()
elif column == "file":
return mark_safe(
"""<a href='%s'><span class="glyphicon glyphicon-file"></span></a>"""
% row.bill_file_url())
elif column == "subsidiary":
return str(row.lead.subsidiary)
else:
return super(BillTableDT, self).render_column(row, column)
def get_billing_info(timesheet_data):
"""compute billing information from this timesheet data
@:param timesheet_data: value queryset with mission, consultant and charge in days
@:return billing information as a tuple (lead, (lead total, (mission total, billing data)) """
Mission = apps.get_model("staffing", "Mission")
Consultant = apps.get_model("people", "Consultant")
billing_data = {}
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)
rates = mission.consultant_rates()
if not lead in billing_data:
billing_data[lead] = [0.0, {}] # Lead Total and dict of mission
if not mission in billing_data[lead][1]:
billing_data[lead][1][mission] = [
0.0, []
] # Mission Total and detail per consultant
total = charge * rates[consultant][0]
billing_data[lead][0] += total
billing_data[lead][1][mission][0] += total
billing_data[lead][1][mission][1].append([
consultant,
to_int_or_round(charge, 2), rates[consultant][0], total
])
# Sort data
billing_data = list(billing_data.items())
billing_data.sort(key=lambda x: x[0].deal_id)
return billing_data
def get_billing_info(timesheet_data):
"""compute billing information from this timesheet data
@:param timesheet_data: value queryset with mission, consultant and charge in days
@:return billing information as a tuple (lead, (lead total, (mission total, billing data)) """
billing_data = {}
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)
rates = mission.consultant_rates()
if not lead in billing_data:
billing_data[lead] = [0.0, {}] # Lead Total and dict of mission
if not mission in billing_data[lead][1]:
billing_data[lead][1][mission] = [0.0, []] # Mission Total and detail per consultant
total = charge * rates[consultant][0]
billing_data[lead][0] += total
billing_data[lead][1][mission][0] += total
billing_data[lead][1][mission][1].append(
[consultant, to_int_or_round(charge, 2), rates[consultant][0], total])
# Sort data
billing_data = list(billing_data.items())
billing_data.sort(key=lambda x: x[0].deal_id)
return billing_data
def render_column(self, row, column):
if column == "user":
return link_to_consultant(row.user)
elif column == "receipt":
return self.receipt_template.render(context={"record": row},
request=self.request)
elif column == "lead":
if row.lead:
return "<a href='{0}'>{1}</a>".format(
row.lead.get_absolute_url(), row.lead)
else:
return "-"
elif column in ("creation_date", "expense_date"):
return self.date_template.render(
context={"date": getattr(row, column)}, request=self.request)
elif column == "update_date":
return row.update_date.strftime("%x %X")
elif column in ("chargeable", "corporate_card"):
if getattr(row, column):
return self.ok_sign
else:
return self.ko_sign
elif column == "state":
return self.state_template.render(context={"record": row},
request=self.request)
elif column == "amount":
return to_int_or_round(row.amount, 2)
elif column == "vat":
return """<div id="{0}" class="jeditable-vat">{1}</div>""".format(
row.id, row.vat)
else:
return super(ExpenseTableDT, self).render_column(row, column)
def render_column(self, row, column):
if column == "user":
return link_to_consultant(row.user())
elif column == "payment_date":
return self.date_template.render(context={"date": row.payment_date}, request=self.request)
elif column == "amount":
return to_int_or_round(row.amount(), 2)
elif column == "modification":
return self.modification_template.render(RequestContext(self.request, {"record": row}))
else:
return super(ExpensePaymentTableDT, self).render_column(row, column)
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 render_column(self, row, column):
if column in ("amount", "amount_with_vat"):
return to_int_or_round(getattr(row, column), 2)
elif column == "lead":
if row.lead:
return "<a href='{0}'>{1}</a>".format(row.lead.get_absolute_url(), row.lead)
else:
return "-"
elif column in ("creation_date", "due_date", "payment_date"):
return getattr(row, column).strftime("%d/%m/%y")
elif column == "state":
return row.get_state_display()
elif column == "file":
return mark_safe("""<a href='%s'><span class="glyphicon glyphicon-file"></span></a>""" % row.bill_file_url())
elif column == "subsidiary":
return str(row.lead.subsidiary)
else:
return super(BillTableDT, self).render_column(row, column)
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
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 graph_leads_activity(request):
"""some graph and figures about current leads activity"""
subsidiary = get_subsidiary_from_session(request)
# lead stat
current_leads = Lead.objects.active()
if subsidiary:
current_leads = current_leads.filter(subsidiary=subsidiary)
leads_state_data = leads_state_stat(current_leads)
leads = Lead.objects.all()
if subsidiary:
leads = leads.filter(subsidiary=subsidiary)
# lead creation rate per week
first_lead_creation_date = leads.aggregate(Min("creation_date")).get(
"creation_date__min", datetime.now()).date()
today = date.today()
lead_creation_rate_data = []
max_creation_rate = 0
for timeframe in (30, 30 * 6, 365):
start = today - timedelta(timeframe)
if start > first_lead_creation_date:
rate = 7 * leads.filter(
creation_date__gte=start).count() / timeframe
rate = round(rate, 2)
lead_creation_rate_data.append(
[_("Last %s days") % timeframe, rate])
max_creation_rate = max(rate, max_creation_rate)
# lead duration
d_leads = leads.filter(creation_date__gt=(datetime.today() -
timedelta(2 * 365)))
d_leads = d_leads.annotate(
timesheet_start=Min("mission__timesheet__working_date"))
leads_duration = defaultdict(list)
for lead in d_leads:
end_date = lead.timesheet_start or lead.start_date or lead.update_date.date(
)
duration = (end_date - lead.creation_date.date()).days
leads_duration[lead.creation_date.date().replace(
day=1)].append(duration)
leads_duration_per_month = to_int_or_round(
[sum(i) / len(i) for i in sortedValues(leads_duration)], 1)
leads_duration_data = [
["x"] + [d.isoformat() for d in sorted(leads_duration.keys())],
[_("duration")] + leads_duration_per_month,
[_("average duration 6 months")] +
moving_average(leads_duration_per_month, 6, round_digits=1)
]
return render(
request, "leads/graph_leads_activity.html", {
"leads_state_data": leads_state_data,
"leads_state_title":
_("%s leads in progress") % len(current_leads),
"lead_creation_rate_data": json.dumps(lead_creation_rate_data),
"max_creation_rate": max_creation_rate,
"leads_duration_data": json.dumps(leads_duration_data),
"user": request.user
})
def solver_solution_format(solver, staffing, consultants, missions,
staffing_dates, missions_charge,
consultants_freetime, consultant_rates):
"""Prepare solver solution for template rendering. Returns staffing_array and mission_remaining_array"""
results = []
missions_remaining_results = []
class_optim_ok = "optim_ok"
class_optim_info = "optim_info"
class_optim_warn = "optim_warn"
for mission in missions:
mission_id = mission.mission_id()
mission_link = mark_safe("<a href='%s#tab-timesheet'>%s</a>" %
(mission.get_absolute_url(), escape(mission)))
new_forecast = sum([
solver.Value(staffing[consultant.trigramme][mission_id][month[1]])
* consultant_rates[consultant.trigramme][mission_id] / 1000
for consultant in consultants for month in staffing_dates
])
new_target_remaining = mission.remaining(mode="current") - new_forecast
missions_remaining_results.append([
mission_link,
to_int_or_round(mission.price or 3),
to_int_or_round(mission.remaining(mode="current"), 3),
to_int_or_round(mission.remaining(mode="target"), 3),
to_int_or_round(new_target_remaining, 3),
])
for consultant in consultants:
consultant_link = mark_safe(
"<a href='%s#tab-staffing'>%s</a>" %
(consultant.get_absolute_url(), escape(consultant)))
charges = []
display_consultant = False
for month in staffing_dates:
charge = solver.Value(
staffing[consultant.trigramme][mission_id][month[1]])
try:
delta = charge - Staffing.objects.get(
mission=mission,
consultant=consultant,
staffing_date=month[0]).charge
except Staffing.DoesNotExist:
delta = charge
if charge or delta:
display_consultant = True
class_optim = None
if (abs(delta) == charge) or (
charge == 0 and
delta != 0): # Notify for newcomers and leavers
class_optim = class_optim_info
if delta > 0:
charges.append((class_optim,
mark_safe("%i <small>(+%i)</small>" %
(charge, delta))))
elif delta < 0:
charges.append((class_optim,
mark_safe("%i <small>(%i)</small>" %
(charge, delta))))
else:
charges.append((class_optim, "%i" % charge))
else:
charges.append(
"") # Don't display zero to ease readability
# Add charges for mission / consultant if needed
if display_consultant:
results.append([mission_link, consultant_link, *charges])
all_charges = []
results.append([""] * (len(staffing_dates) + 2))
for month in staffing_dates:
mission_charge = sum(
solver.Value(staffing[consultant.trigramme][mission_id][
month[1]]) for consultant in consultants)
if mission_charge > 0 or missions_charge[mission_id][month[1]] > 0:
if abs(missions_charge[mission_id][month[1]] -
mission_charge) < 2:
class_optim = class_optim_ok
else:
class_optim = class_optim_warn
all_charges.append(
(class_optim, "%s/%s\t" %
(mission_charge, missions_charge[mission_id][month[1]])))
else:
# No charge planned of forecasted, display nothing for this month
all_charges.append(("", ""))
results.append([mission_link, _("All"), *all_charges])
results.append(
[""] * 2 + [None, ""] *
len(staffing_dates)) # Zero content row (hack for bold line)
results.append(
[mark_safe(" ")] * 2 +
[[None, mark_safe(" ")]] * len(staffing_dates)) # Empty row
for consultant in consultants:
consultant_link = mark_safe(
"<a href='%s#tab-staffing'>%s</a>" %
(consultant.get_absolute_url(), escape(consultant)))
all_charges = []
for month in staffing_dates:
consultant_charge = sum(
solver.Value(staffing[consultant.trigramme][
mission.mission_id()][month[1]]) for mission in missions)
if consultants_freetime[consultant.trigramme][
month[1]] - consultant_charge < 2:
class_optim = class_optim_warn
else:
class_optim = class_optim_ok
all_charges.append(
(class_optim, "%s/%s" %
(consultant_charge,
consultants_freetime[consultant.trigramme][month[1]])))
results.append([_("All missions"), consultant_link, *all_charges])
return results, missions_remaining_results
