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(consultant_home, consultant_id)
try:
consultant = Consultant.objects.get(id=consultant_id)
staff = consultant.team(onlyActive=True)
month = date.today().replace(day=1)
# Compute user current mission based on forecast
missions = consultant.active_missions().filter(nature="PROD").filter(probability=100)
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()
first_day = date.today().replace(day=1)
holidays = [h.day for h in Holiday.objects.all()]
month_days = working_days(first_day, holidays, upToToday=False)
done_days = consultant.done_days()
late = working_days(first_day, holidays, upToToday=True) - done_days
if late < 0:
late = 0 # Don't warn user if timesheet is ok !
to_be_done = month_days - late - done_days
forecasting_balance = month_days - consultant.forecasted_days()
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
except Consultant.DoesNotExist:
raise Http404
return render(request, "people/consultant_detail.html",
{"consultant": consultant,
"staff": staff,
"missions": 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,
"month_days": month_days,
"forecasting_balance": forecasting_balance,
"month_turnover": monthTurnover,
"turnover_variation": turnoverVariation,
"user": request.user})
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 timesheet_is_up_to_date(self):
"""return tuple (previous month late days, current month late days). (0, 0) means everything is up to date. Current day is not included"""
Timesheet = apps.get_model("staffing", "Timesheet") # Get Timesheet with get_model to avoid circular imports
from staffing.utils import holidayDays # Idem
result = []
current_month = date.today().replace(day=1)
for month, up_to in ((previousMonth(current_month), current_month), (current_month, date.today())):
wd = working_days(month, holidayDays(month=month),upToToday=True)
td = list(Timesheet.objects.filter(consultant=self, working_date__lt=up_to, working_date__gte=month).aggregate(Sum("charge")).values())[0] or 0
result.append(wd - td)
return result
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 graph_company_business_activity_jqp(request, company_id):
"""Business activity (leads and bills) for a company
@todo: extend this graph to multiple companies"""
graph_data = []
billsData = dict()
allLeadsData = dict()
wonLeadsData = dict()
minDate = date.today()
company = Company.objects.get(id=company_id)
for bill in ClientBill.objects.filter(lead__client__organisation__company=company):
kdate = bill.creation_date.replace(day=1)
if kdate in billsData:
billsData[kdate] += int(float(bill.amount) / 1000)
else:
billsData[kdate] = int(float(bill.amount) / 1000)
for lead in Lead.objects.filter(client__organisation__company=company):
kdate = lead.creation_date.date().replace(day=1)
if lead.state == "WON":
datas = (allLeadsData, wonLeadsData)
else:
datas = (allLeadsData,)
for data in datas:
if kdate in data:
data[kdate] += 1
else:
data[kdate] = 1
for data in (billsData, allLeadsData, wonLeadsData):
kdates = data.keys()
kdates.sort()
isoKdates = [a.isoformat() for a in kdates] # List of date as string in ISO format
if len(kdates) > 0 and kdates[0] < minDate:
minDate = kdates[0]
data = zip(isoKdates, sortedValues(data))
if not data:
data = ((0, 0))
graph_data.append(data)
minDate = previousMonth(minDate)
return render(request, "crm/graph_company_business_activity_jqp.html",
{"graph_data": json.dumps(graph_data),
"series_colors": COLORS,
"min_date": minDate.isoformat(),
"user": request.user})
def graph_company_business_activity_jqp(request, company_id):
"""Business activity (leads and bills) for a company
@todo: extend this graph to multiple companies"""
graph_data = []
billsData = dict()
lostLeadsData = dict()
currentLeadsData = dict()
wonLeadsData = dict()
minDate = date.today()
company = Company.objects.get(id=company_id)
for bill in ClientBill.objects.filter(lead__client__organisation__company=company):
kdate = bill.creation_date.replace(day=1)
if kdate in billsData:
billsData[kdate] += int(float(bill.amount) / 1000)
else:
billsData[kdate] = int(float(bill.amount) / 1000)
for lead in Lead.objects.filter(client__organisation__company=company):
kdate = lead.creation_date.date().replace(day=1)
for data in (lostLeadsData, wonLeadsData, currentLeadsData, billsData):
data[kdate] = data.get(kdate, 0) # Default to 0 to avoid stacking weirdness in graph
if lead.state == "WON":
wonLeadsData[kdate] += 1
elif lead.state in ("LOST", "FORGIVEN"):
lostLeadsData[kdate] += 1
else:
currentLeadsData[kdate] += 1
for data in (billsData, lostLeadsData, wonLeadsData, currentLeadsData):
kdates = data.keys()
kdates.sort()
isoKdates = [a.isoformat() for a in kdates] # List of date as string in ISO format
if len(kdates) > 0 and kdates[0] < minDate:
minDate = kdates[0]
data = zip(isoKdates, sortedValues(data))
if not data:
data = ((0, 0))
graph_data.append(data)
minDate = previousMonth(minDate)
return render(request, "crm/graph_company_business_activity_jqp.html",
{"graph_data": json.dumps(graph_data),
"series_colors": COLORS,
"min_date": minDate.isoformat(),
"user": request.user})
def timesheet_is_up_to_date(self):
"""return tuple (previous month late days, current month late days). (0, 0) means everything is up to date. Current day is not included"""
Timesheet = apps.get_model(
"staffing", "Timesheet"
) # Get Timesheet with get_model to avoid circular imports
from staffing.utils import holidayDays # Idem
result = []
current_month = date.today().replace(day=1)
for month, up_to in ((previousMonth(current_month), current_month),
(current_month, date.today())):
wd = working_days(month, holidayDays(month=month), upToToday=True)
td = Timesheet.objects.filter(consultant=self,
working_date__lt=up_to,
working_date__gte=month).aggregate(
Sum("charge")).values()[0] or 0
result.append(wd - td)
return result
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 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 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 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 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, 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 client_bill(request, bill_id=None):
"""Add or edit client bill"""
billDetailFormSet = None
billExpenseFormSet = None
billing_management_feature = "billing_management"
forbiden = HttpResponseRedirect(reverse("core:forbiden"))
if bill_id:
try:
bill = ClientBill.objects.get(id=bill_id)
except ClientBill.DoesNotExist:
raise Http404
else:
bill = None
BillDetailFormSet = inlineformset_factory(ClientBill,
BillDetail,
formset=BillDetailInlineFormset,
form=BillDetailForm,
fields="__all__")
BillExpenseFormSet = inlineformset_factory(
ClientBill,
BillExpense,
formset=BillExpenseInlineFormset,
form=BillExpenseForm,
fields="__all__")
wip_status = ("0_DRAFT", "0_PROPOSED")
if request.POST:
form = ClientBillForm(request.POST, request.FILES, instance=bill)
# First, ensure user is allowed to manipulate the bill
if bill and bill.state not in wip_status and not user_has_feature(
request.user, billing_management_feature):
return forbiden
if form.data["state"] not in wip_status and not user_has_feature(
request.user, billing_management_feature):
return forbiden
# Now, process form
if bill and bill.state in wip_status:
billDetailFormSet = BillDetailFormSet(request.POST, instance=bill)
billExpenseFormSet = BillExpenseFormSet(request.POST,
instance=bill)
if form.is_valid() and (billDetailFormSet is None
or billDetailFormSet.is_valid()) and (
billExpenseFormSet is None
or billExpenseFormSet.is_valid()):
bill = form.save()
if billDetailFormSet:
billDetailFormSet.save()
if billExpenseFormSet:
billExpenseFormSet.save()
bill.save() # Again, to take into account modified details.
if bill.state in wip_status:
success_url = reverse_lazy("billing:client_bill",
args=[
bill.id,
])
else:
success_url = request.GET.get(
'return_to', False) or reverse_lazy(
"crm:company_detail",
args=[
bill.lead.client.organisation.company.id,
]) + "#goto_tab-billing"
if bill.bill_file:
if form.changed_data == [
"state"
] and billDetailFormSet is None and billExpenseFormSet is None:
# only state has change. No need to regenerate bill file.
messages.add_message(request, messages.INFO,
_("Bill state has beed updated"))
elif "bill_file" in form.changed_data:
# a file has been provided by user himself. We must not generate a file and overwrite it.
messages.add_message(
request, messages.WARNING,
_("Using custom user file to replace current bill")
)
else:
# bill file exist but authorized admin change information and do not provide custom file. Let's generate again bill file
messages.add_message(
request, messages.WARNING,
_("A new bill is generated and replace the previous one"
))
if os.path.exists(bill.bill_file.path):
os.remove(bill.bill_file.path)
generate_bill_pdf(bill, request)
else:
# Bill file still not exist. Let's create it
messages.add_message(
request, messages.INFO,
_("A new bill file has been generated"))
generate_bill_pdf(bill, request)
return HttpResponseRedirect(success_url)
else:
if bill:
# Create a form to edit the given bill
form = ClientBillForm(instance=bill)
if bill.state in wip_status:
billDetailFormSet = BillDetailFormSet(instance=bill)
billExpenseFormSet = BillExpenseFormSet(instance=bill)
else:
# Still no bill, let's create it with its detail if at least mission or lead has been provided
missions = []
if request.GET.get("lead"):
lead = Lead.objects.get(id=request.GET.get("lead"))
missions = lead.mission_set.all() # take all missions
if request.GET.get("mission"):
missions = [Mission.objects.get(id=request.GET.get("mission"))]
if missions:
bill = ClientBill.objects.create(lead=missions[0].lead)
bill.save()
for mission in missions:
if mission.billing_mode == "TIME_SPENT":
if request.GET.get("start_date") and request.GET.get(
"end_date"):
start_date = date(
int(request.GET.get("start_date")[0:4]),
int(request.GET.get("start_date")[4:6]), 1)
end_date = date(int(request.GET.get("end_date")[0:4]),
int(request.GET.get("end_date")[4:6]),
1)
else:
start_date = previousMonth(previousMonth(date.today()))
end_date = previousMonth(date.today())
update_client_bill_from_timesheet(bill, mission,
start_date, end_date)
else: # FIXED_PRICE mission
proportion = request.GET.get("proportion", 0.30)
bill = update_client_bill_from_proportion(
bill, mission, proportion=proportion)
if bill:
form = ClientBillForm(instance=bill)
billDetailFormSet = BillDetailFormSet(instance=bill)
billExpenseFormSet = BillExpenseFormSet(instance=bill)
else:
# Simple virgin new form
form = ClientBillForm()
return render(
request, "billing/client_bill_form.html", {
"bill_form": form,
"detail_formset": billDetailFormSet,
"detail_formset_helper": BillDetailFormSetHelper(),
"expense_formset": billExpenseFormSet,
"expense_formset_helper": BillExpenseFormSetHelper(),
"bill_id": bill.id if bill else None,
"can_delete": bill.state in wip_status if bill else False,
"can_preview": bill.state in wip_status if bill else False,
"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 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 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 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 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 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(consultant_home, consultant_id)
try:
consultant = Consultant.objects.get(id=consultant_id)
staff = consultant.team(onlyActive=True)
month = date.today().replace(day=1)
# Compute user current mission based on forecast
missions = consultant.active_missions().filter(nature="PROD").filter(
probability=100)
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()
first_day = date.today().replace(day=1)
holidays = [h.day for h in Holiday.objects.all()]
month_days = working_days(first_day, holidays, upToToday=False)
done_days = consultant.done_days()
late = working_days(first_day, holidays, upToToday=True) - done_days
if late < 0:
late = 0 # Don't warn user if timesheet is ok !
to_be_done = month_days - late - done_days
forecasting_balance = month_days - consultant.forecasted_days()
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
except Consultant.DoesNotExist:
raise Http404
return render(
request, "people/consultant_detail.html", {
"consultant": consultant,
"staff": staff,
"missions": 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,
"month_days": month_days,
"forecasting_balance": forecasting_balance,
"month_turnover": monthTurnover,
"turnover_variation": turnoverVariation,
"user": request.user
})
