def timesheet_report_data(mission, start=None, end=None, padding=False): """Prepare data for timesheet report from start to end. Padding align total in the same column""" timesheets = Timesheet.objects.select_related().filter(mission=mission) months = timesheets.dates("working_date", "month") data = [] for month in months: if start and month < start: continue if end and month > end: break days = daysOfMonth(month) next_month = nextMonth(month) padding_length = 31 - len( days ) # Padding for month with less than 31 days to align total column # Header data.append([""]) data.append([formats.date_format(month, format="YEAR_MONTH_FORMAT")]) # Days data.append([ "", ] + [d.day for d in days]) dayHeader = [_("Consultants")] + [_(d.strftime("%a")) for d in days] if padding: dayHeader.extend([""] * padding_length) dayHeader.append(_("total")) data.append(dayHeader) for consultant in mission.consultants(): total = 0 row = [ consultant, ] consultant_timesheets = {} for timesheet in timesheets.filter(consultant_id=consultant.id, working_date__gte=month, working_date__lt=next_month): consultant_timesheets[ timesheet.working_date] = timesheet.charge for day in days: try: charge = consultant_timesheets.get(day) if charge: row.append( formats.number_format(to_int_or_round(charge, 2))) total += charge else: row.append("") except Timesheet.DoesNotExist: row.append("") if padding: row.extend([""] * padding_length) row.append(formats.number_format(to_int_or_round(total, 2))) if total > 0: data.append(row) return data
def timesheet_report_data(mission, start=None, end=None, padding=False): """Prepare data for timesheet report from start to end. Padding align total in the same column""" timesheets = Timesheet.objects.select_related().filter(mission=mission) months = timesheets.dates("working_date", "month") data = [] for month in months: if start and month < start: continue if end and month > end: break days = daysOfMonth(month) next_month = nextMonth(month) padding_length = 31 - len(days) # Padding for month with less than 31 days to align total column # Header data.append([""]) data.append([formats.date_format(month, format="YEAR_MONTH_FORMAT")]) # Days data.append(["", ] + [d.day for d in days]) dayHeader = [_("Consultants")] + [_(d.strftime("%a")) for d in days] if padding: dayHeader.extend([""] * padding_length) dayHeader.append(_("total")) data.append(dayHeader) for consultant in mission.consultants(): total = 0 row = [consultant, ] consultant_timesheets = {} for timesheet in timesheets.filter(consultant_id=consultant.id, working_date__gte=month, working_date__lt=next_month): consultant_timesheets[timesheet.working_date] = timesheet.charge for day in days: try: charge = consultant_timesheets.get(day) if charge: row.append(formats.number_format(to_int_or_round(charge, 2))) total += charge else: row.append("") except Timesheet.DoesNotExist: row.append("") if padding: row.extend([""] * padding_length) row.append(formats.number_format(to_int_or_round(total, 2))) if total > 0: data.append(row) return data
def render_column(self, row, column): if column == "user": return link_to_consultant(row.user) elif column == "receipt": return self.receipt_template.render( RequestContext(self.request, {"record": row})) elif column == "lead": if row.lead: return u"<a href='{0}'>{1}</a>".format( row.lead.get_absolute_url(), row.lead) else: return u"-" elif column in ("creation_date", "expense_date"): return self.date_template.render({"date": getattr(row, column)}) elif column == "update_date": return row.update_date.strftime("%x %X") elif column in ("chargeable", "corporate_card"): if getattr(row, column): return self.ok_sign else: return self.ko_sign elif column == "state": return self.state_template.render( RequestContext(self.request, {"record": row})) elif column == "amount": return to_int_or_round(row.amount, 2) else: return super(ExpenseTableDT, self).render_column(row, column)
def render_column(self, row, column): if column in ("amount", "amount_with_vat"): return to_int_or_round(getattr(row, column), 2) elif column == "lead": if row.lead: return "<a href='{0}'>{1}</a>".format( row.lead.get_absolute_url(), row.lead) else: return "-" elif column in ("creation_date", "due_date", "payment_date"): attr = getattr(row, column) if attr: return attr.strftime("%d/%m/%y") else: return "-" elif column == "state": return row.get_state_display() elif column == "file": return mark_safe( """<a href='%s'><span class="glyphicon glyphicon-file"></span></a>""" % row.bill_file_url()) elif column == "subsidiary": return str(row.lead.subsidiary) else: return super(BillTableDT, self).render_column(row, column)
def get_billing_info(timesheet_data): """compute billing information from this timesheet data @:param timesheet_data: value queryset with mission, consultant and charge in days @:return billing information as a tuple (lead, (lead total, (mission total, billing data)) """ Mission = apps.get_model("staffing", "Mission") Consultant = apps.get_model("people", "Consultant") billing_data = {} for mission_id, consultant_id, charge in timesheet_data: mission = Mission.objects.select_related("lead").get(id=mission_id) if mission.lead: lead = mission.lead else: # Bad data, mission with nature prod without lead... This should not happened continue consultant = Consultant.objects.get(id=consultant_id) rates = mission.consultant_rates() if not lead in billing_data: billing_data[lead] = [0.0, {}] # Lead Total and dict of mission if not mission in billing_data[lead][1]: billing_data[lead][1][mission] = [ 0.0, [] ] # Mission Total and detail per consultant total = charge * rates[consultant][0] billing_data[lead][0] += total billing_data[lead][1][mission][0] += total billing_data[lead][1][mission][1].append([ consultant, to_int_or_round(charge, 2), rates[consultant][0], total ]) # Sort data billing_data = list(billing_data.items()) billing_data.sort(key=lambda x: x[0].deal_id) return billing_data
def get_billing_info(timesheet_data): """compute billing information from this timesheet data @:param timesheet_data: value queryset with mission, consultant and charge in days @:return billing information as a tuple (lead, (lead total, (mission total, billing data)) """ billing_data = {} for mission_id, consultant_id, charge in timesheet_data: mission = Mission.objects.select_related("lead").get(id=mission_id) if mission.lead: lead = mission.lead else: # Bad data, mission with nature prod without lead... This should not happened continue consultant = Consultant.objects.get(id=consultant_id) rates = mission.consultant_rates() if not lead in billing_data: billing_data[lead] = [0.0, {}] # Lead Total and dict of mission if not mission in billing_data[lead][1]: billing_data[lead][1][mission] = [0.0, []] # Mission Total and detail per consultant total = charge * rates[consultant][0] billing_data[lead][0] += total billing_data[lead][1][mission][0] += total billing_data[lead][1][mission][1].append( [consultant, to_int_or_round(charge, 2), rates[consultant][0], total]) # Sort data billing_data = list(billing_data.items()) billing_data.sort(key=lambda x: x[0].deal_id) return billing_data
def render_column(self, row, column): if column == "user": return link_to_consultant(row.user) elif column == "receipt": return self.receipt_template.render(context={"record": row}, request=self.request) elif column == "lead": if row.lead: return "<a href='{0}'>{1}</a>".format( row.lead.get_absolute_url(), row.lead) else: return "-" elif column in ("creation_date", "expense_date"): return self.date_template.render( context={"date": getattr(row, column)}, request=self.request) elif column == "update_date": return row.update_date.strftime("%x %X") elif column in ("chargeable", "corporate_card"): if getattr(row, column): return self.ok_sign else: return self.ko_sign elif column == "state": return self.state_template.render(context={"record": row}, request=self.request) elif column == "amount": return to_int_or_round(row.amount, 2) elif column == "vat": return """<div id="{0}" class="jeditable-vat">{1}</div>""".format( row.id, row.vat) else: return super(ExpenseTableDT, self).render_column(row, column)
def render_column(self, row, column): if column == "user": return link_to_consultant(row.user()) elif column == "payment_date": return self.date_template.render(context={"date": row.payment_date}, request=self.request) elif column == "amount": return to_int_or_round(row.amount(), 2) elif column == "modification": return self.modification_template.render(RequestContext(self.request, {"record": row})) else: return super(ExpensePaymentTableDT, self).render_column(row, column)
def pre_billing(request, year=None, month=None): """Pre billing page: help to identify bills to send""" if year and month: month = date(int(year), int(month), 1) else: month = previousMonth(date.today()) next_month = nextMonth(month) timeSpentBilling = {} # Key is lead, value is total and dict of mission(total, Mission billingData) rates = {} # Key is mission, value is Consultant rates dict fixedPriceMissions = Mission.objects.filter(nature="PROD", billing_mode="FIXED_PRICE", timesheet__working_date__gte=month, timesheet__working_date__lt=next_month) fixedPriceMissions = fixedPriceMissions.order_by("lead").distinct() timesheets = Timesheet.objects.filter(working_date__gte=month, working_date__lt=next_month, mission__nature="PROD", mission__billing_mode="TIME_SPENT") timesheet_data = timesheets.order_by("mission__lead", "consultant").values_list("mission", "consultant").annotate(Sum("charge")) for mission_id, consultant_id, charge in timesheet_data: mission = Mission.objects.select_related("lead").get(id=mission_id) if mission.lead: lead = mission.lead else: # Bad data, mission with nature prod without lead... This should not happened continue consultant = Consultant.objects.get(id=consultant_id) if not mission in rates: rates[mission] = mission.consultant_rates() if not lead in timeSpentBilling: timeSpentBilling[lead] = [0.0, {}] # Lead Total and dict of mission if not mission in timeSpentBilling[lead][1]: timeSpentBilling[lead][1][mission] = [0.0, []] # Mission Total and detail per consultant total = charge * rates[mission][consultant][0] timeSpentBilling[lead][0] += total timeSpentBilling[lead][1][mission][0] += total timeSpentBilling[lead][1][mission][1].append([consultant, to_int_or_round(charge, 2), rates[mission][consultant][0], total]) # Sort data timeSpentBilling = timeSpentBilling.items() timeSpentBilling.sort(key=lambda x: x[0].deal_id) return render(request, "billing/pre_billing.html", {"time_spent_billing": timeSpentBilling, "fixed_price_missions": fixedPriceMissions, "month": month, "user": request.user})
def render_column(self, row, column): if column in ("amount", "amount_with_vat"): return to_int_or_round(getattr(row, column), 2) elif column == "lead": if row.lead: return "<a href='{0}'>{1}</a>".format(row.lead.get_absolute_url(), row.lead) else: return "-" elif column in ("creation_date", "due_date", "payment_date"): return getattr(row, column).strftime("%d/%m/%y") elif column == "state": return row.get_state_display() elif column == "file": return mark_safe("""<a href='%s'><span class="glyphicon glyphicon-file"></span></a>""" % row.bill_file_url()) elif column == "subsidiary": return str(row.lead.subsidiary) else: return super(BillTableDT, self).render_column(row, column)
def pre_billing(request, year=None, month=None, mine=False): """Pre billing page: help to identify bills to send""" if year and month: month = date(int(year), int(month), 1) else: month = previousMonth(date.today()) next_month = nextMonth(month) timeSpentBilling = { } # Key is lead, value is total and dict of mission(total, Mission billingData) rates = {} # Key is mission, value is Consultant rates dict try: billing_consultant = Consultant.objects.get( trigramme__iexact=request.user.username) except Consultant.DoesNotExist: billing_consultant = None mine = False # Check consultant timesheet to hint if billing could be done based on a clean state timesheet_ok = {} for consultant in Consultant.objects.filter(active=True, subcontractor=False): missions = consultant.timesheet_missions(month=month) timesheetData, timesheetTotal, warning = gatherTimesheetData( consultant, missions, month) days = sum([v for (k, v) in timesheetTotal.items() if k != "ticket" ]) # Compute timesheet days. Remove lunch ticket count if days == working_days(month, holidayDays(month=month)): timesheet_ok[consultant.id] = True else: timesheet_ok[consultant.id] = False fixedPriceMissions = Mission.objects.filter( nature="PROD", billing_mode="FIXED_PRICE", timesheet__working_date__gte=month, timesheet__working_date__lt=next_month) undefinedBillingModeMissions = Mission.objects.filter( nature="PROD", billing_mode=None, timesheet__working_date__gte=month, timesheet__working_date__lt=next_month) if mine: fixedPriceMissions = fixedPriceMissions.filter( Q(lead__responsible=billing_consultant) | Q(responsible=billing_consultant)) undefinedBillingModeMissions = undefinedBillingModeMissions.filter( Q(lead__responsible=billing_consultant) | Q(responsible=billing_consultant)) fixedPriceMissions = fixedPriceMissions.order_by("lead").distinct() undefinedBillingModeMissions = undefinedBillingModeMissions.order_by( "lead").distinct() timesheets = Timesheet.objects.filter(working_date__gte=month, working_date__lt=next_month, mission__nature="PROD", mission__billing_mode="TIME_SPENT") if mine: timesheets = timesheets.filter( Q(mission__lead__responsible=billing_consultant) | Q(mission__responsible=billing_consultant)) timesheet_data = timesheets.order_by( "mission__lead", "consultant").values_list("mission", "consultant").annotate(Sum("charge")) for mission_id, consultant_id, charge in timesheet_data: mission = Mission.objects.select_related("lead").get(id=mission_id) if mission.lead: lead = mission.lead else: # Bad data, mission with nature prod without lead... This should not happened continue consultant = Consultant.objects.get(id=consultant_id) if not mission in rates: rates[mission] = mission.consultant_rates() if not lead in timeSpentBilling: timeSpentBilling[lead] = [0.0, {}] # Lead Total and dict of mission if not mission in timeSpentBilling[lead][1]: timeSpentBilling[lead][1][mission] = [ 0.0, [] ] # Mission Total and detail per consultant total = charge * rates[mission][consultant][0] timeSpentBilling[lead][0] += total timeSpentBilling[lead][1][mission][0] += total timeSpentBilling[lead][1][mission][1].append([ consultant, to_int_or_round(charge, 2), rates[mission][consultant][0], total, timesheet_ok.get(consultant_id, True) ]) # Sort data timeSpentBilling = timeSpentBilling.items() timeSpentBilling.sort(key=lambda x: x[0].deal_id) return render( request, "billing/pre_billing.html", { "time_spent_billing": timeSpentBilling, "fixed_price_missions": fixedPriceMissions, "undefined_billing_mode_missions": undefinedBillingModeMissions, "month": month, "mine": mine, "user": request.user })
def pre_billing(request, year=None, month=None, mine=False): """Pre billing page: help to identify bills to send""" if year and month: month = date(int(year), int(month), 1) else: month = previousMonth(date.today()) next_month = nextMonth(month) timeSpentBilling = {} # Key is lead, value is total and dict of mission(total, Mission billingData) rates = {} # Key is mission, value is Consultant rates dict try: billing_consultant = Consultant.objects.get(trigramme__iexact=request.user.username) except Consultant.DoesNotExist: billing_consultant = None mine = False # Check consultant timesheet to hint if billing could be done based on a clean state timesheet_ok = {} for consultant in Consultant.objects.filter(active=True, subcontractor=False): missions = consultant.timesheet_missions(month=month) timesheetData, timesheetTotal, warning = gatherTimesheetData(consultant, missions, month) days = sum([v for (k,v) in timesheetTotal.items() if k!="ticket"]) # Compute timesheet days. Remove lunch ticket count if days == working_days(month, holidayDays(month=month)): timesheet_ok[consultant.id] = True else: timesheet_ok[consultant.id] = False fixedPriceMissions = Mission.objects.filter(nature="PROD", billing_mode="FIXED_PRICE", timesheet__working_date__gte=month, timesheet__working_date__lt=next_month) undefinedBillingModeMissions = Mission.objects.filter(nature="PROD", billing_mode=None, timesheet__working_date__gte=month, timesheet__working_date__lt=next_month) if mine: fixedPriceMissions = fixedPriceMissions.filter(Q(lead__responsible=billing_consultant) | Q(responsible=billing_consultant)) undefinedBillingModeMissions = undefinedBillingModeMissions.filter(Q(lead__responsible=billing_consultant) | Q(responsible=billing_consultant)) fixedPriceMissions = fixedPriceMissions.order_by("lead").distinct() undefinedBillingModeMissions = undefinedBillingModeMissions.order_by("lead").distinct() timesheets = Timesheet.objects.filter(working_date__gte=month, working_date__lt=next_month, mission__nature="PROD", mission__billing_mode="TIME_SPENT") if mine: timesheets = timesheets.filter(Q(mission__lead__responsible=billing_consultant) | Q(mission__responsible=billing_consultant)) timesheet_data = timesheets.order_by("mission__lead", "consultant").values_list("mission", "consultant").annotate(Sum("charge")) for mission_id, consultant_id, charge in timesheet_data: mission = Mission.objects.select_related("lead").get(id=mission_id) if mission.lead: lead = mission.lead else: # Bad data, mission with nature prod without lead... This should not happened continue consultant = Consultant.objects.get(id=consultant_id) if not mission in rates: rates[mission] = mission.consultant_rates() if not lead in timeSpentBilling: timeSpentBilling[lead] = [0.0, {}] # Lead Total and dict of mission if not mission in timeSpentBilling[lead][1]: timeSpentBilling[lead][1][mission] = [0.0, []] # Mission Total and detail per consultant total = charge * rates[mission][consultant][0] timeSpentBilling[lead][0] += total timeSpentBilling[lead][1][mission][0] += total timeSpentBilling[lead][1][mission][1].append([consultant, to_int_or_round(charge, 2), rates[mission][consultant][0], total, timesheet_ok.get(consultant_id, True)]) # Sort data timeSpentBilling = timeSpentBilling.items() timeSpentBilling.sort(key=lambda x: x[0].deal_id) return render(request, "billing/pre_billing.html", {"time_spent_billing": timeSpentBilling, "fixed_price_missions": fixedPriceMissions, "undefined_billing_mode_missions": undefinedBillingModeMissions, "month": month, "mine": mine, "user": request.user})
def graph_leads_activity(request): """some graph and figures about current leads activity""" subsidiary = get_subsidiary_from_session(request) # lead stat current_leads = Lead.objects.active() if subsidiary: current_leads = current_leads.filter(subsidiary=subsidiary) leads_state_data = leads_state_stat(current_leads) leads = Lead.objects.all() if subsidiary: leads = leads.filter(subsidiary=subsidiary) # lead creation rate per week first_lead_creation_date = leads.aggregate(Min("creation_date")).get( "creation_date__min", datetime.now()).date() today = date.today() lead_creation_rate_data = [] max_creation_rate = 0 for timeframe in (30, 30 * 6, 365): start = today - timedelta(timeframe) if start > first_lead_creation_date: rate = 7 * leads.filter( creation_date__gte=start).count() / timeframe rate = round(rate, 2) lead_creation_rate_data.append( [_("Last %s days") % timeframe, rate]) max_creation_rate = max(rate, max_creation_rate) # lead duration d_leads = leads.filter(creation_date__gt=(datetime.today() - timedelta(2 * 365))) d_leads = d_leads.annotate( timesheet_start=Min("mission__timesheet__working_date")) leads_duration = defaultdict(list) for lead in d_leads: end_date = lead.timesheet_start or lead.start_date or lead.update_date.date( ) duration = (end_date - lead.creation_date.date()).days leads_duration[lead.creation_date.date().replace( day=1)].append(duration) leads_duration_per_month = to_int_or_round( [sum(i) / len(i) for i in sortedValues(leads_duration)], 1) leads_duration_data = [ ["x"] + [d.isoformat() for d in sorted(leads_duration.keys())], [_("duration")] + leads_duration_per_month, [_("average duration 6 months")] + moving_average(leads_duration_per_month, 6, round_digits=1) ] return render( request, "leads/graph_leads_activity.html", { "leads_state_data": leads_state_data, "leads_state_title": _("%s leads in progress") % len(current_leads), "lead_creation_rate_data": json.dumps(lead_creation_rate_data), "max_creation_rate": max_creation_rate, "leads_duration_data": json.dumps(leads_duration_data), "user": request.user })
def solver_solution_format(solver, staffing, consultants, missions, staffing_dates, missions_charge, consultants_freetime, consultant_rates): """Prepare solver solution for template rendering. Returns staffing_array and mission_remaining_array""" results = [] missions_remaining_results = [] class_optim_ok = "optim_ok" class_optim_info = "optim_info" class_optim_warn = "optim_warn" for mission in missions: mission_id = mission.mission_id() mission_link = mark_safe("<a href='%s#tab-timesheet'>%s</a>" % (mission.get_absolute_url(), escape(mission))) new_forecast = sum([ solver.Value(staffing[consultant.trigramme][mission_id][month[1]]) * consultant_rates[consultant.trigramme][mission_id] / 1000 for consultant in consultants for month in staffing_dates ]) new_target_remaining = mission.remaining(mode="current") - new_forecast missions_remaining_results.append([ mission_link, to_int_or_round(mission.price or 3), to_int_or_round(mission.remaining(mode="current"), 3), to_int_or_round(mission.remaining(mode="target"), 3), to_int_or_round(new_target_remaining, 3), ]) for consultant in consultants: consultant_link = mark_safe( "<a href='%s#tab-staffing'>%s</a>" % (consultant.get_absolute_url(), escape(consultant))) charges = [] display_consultant = False for month in staffing_dates: charge = solver.Value( staffing[consultant.trigramme][mission_id][month[1]]) try: delta = charge - Staffing.objects.get( mission=mission, consultant=consultant, staffing_date=month[0]).charge except Staffing.DoesNotExist: delta = charge if charge or delta: display_consultant = True class_optim = None if (abs(delta) == charge) or ( charge == 0 and delta != 0): # Notify for newcomers and leavers class_optim = class_optim_info if delta > 0: charges.append((class_optim, mark_safe("%i <small>(+%i)</small>" % (charge, delta)))) elif delta < 0: charges.append((class_optim, mark_safe("%i <small>(%i)</small>" % (charge, delta)))) else: charges.append((class_optim, "%i" % charge)) else: charges.append( "") # Don't display zero to ease readability # Add charges for mission / consultant if needed if display_consultant: results.append([mission_link, consultant_link, *charges]) all_charges = [] results.append([""] * (len(staffing_dates) + 2)) for month in staffing_dates: mission_charge = sum( solver.Value(staffing[consultant.trigramme][mission_id][ month[1]]) for consultant in consultants) if mission_charge > 0 or missions_charge[mission_id][month[1]] > 0: if abs(missions_charge[mission_id][month[1]] - mission_charge) < 2: class_optim = class_optim_ok else: class_optim = class_optim_warn all_charges.append( (class_optim, "%s/%s\t" % (mission_charge, missions_charge[mission_id][month[1]]))) else: # No charge planned of forecasted, display nothing for this month all_charges.append(("", "")) results.append([mission_link, _("All"), *all_charges]) results.append( [""] * 2 + [None, ""] * len(staffing_dates)) # Zero content row (hack for bold line) results.append( [mark_safe(" ")] * 2 + [[None, mark_safe(" ")]] * len(staffing_dates)) # Empty row for consultant in consultants: consultant_link = mark_safe( "<a href='%s#tab-staffing'>%s</a>" % (consultant.get_absolute_url(), escape(consultant))) all_charges = [] for month in staffing_dates: consultant_charge = sum( solver.Value(staffing[consultant.trigramme][ mission.mission_id()][month[1]]) for mission in missions) if consultants_freetime[consultant.trigramme][ month[1]] - consultant_charge < 2: class_optim = class_optim_warn else: class_optim = class_optim_ok all_charges.append( (class_optim, "%s/%s" % (consultant_charge, consultants_freetime[consultant.trigramme][month[1]]))) results.append([_("All missions"), consultant_link, *all_charges]) return results, missions_remaining_results