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 })