Beispiel #1
0
def graph_outstanding_billing(request):
    """Graph outstanding billing, including overdue clients bills"""
    end = nextMonth(date.today() + timedelta(45))
    current = (end - timedelta(30) * 24).replace(day=1)
    today = date.today()
    months = []
    outstanding = []
    outstanding_overdue = []
    graph_data = []
    subsidiary = get_subsidiary_from_request(request)
    while current < end:
        months.append(current.isoformat())
        next_month = nextMonth(current)
        bills = ClientBill.objects.filter(due_date__lte=next_month, state__in=("1_SENT", "2_PAID")).exclude(payment_date__lt=current)
        if subsidiary:
            bills = bills.filter(lead__subsidiary=subsidiary)
        overdue_bills = bills.exclude(payment_date__lte=F("due_date")).exclude(payment_date__gt=next_month).exclude(due_date__gt=today)
        outstanding.append(float(bills.aggregate(Sum("amount"))["amount__sum"] or 0))
        outstanding_overdue.append(float(overdue_bills.aggregate(Sum("amount"))["amount__sum"] or 0))
        current = next_month

    graph_data.append(["x"] + months)
    graph_data.append([_("billing outstanding")] + outstanding)
    graph_data.append([_("billing outstanding overdue")] + outstanding_overdue)

    return render(request, "billing/graph_outstanding_billing.html",
                  {"graph_data": json.dumps(graph_data),
                   "series_colors": COLORS,
                   "user": request.user})
Beispiel #2
0
def graph_outstanding_billing(request):
    """Graph outstanding billing, including overdue clients bills"""
    end = nextMonth(date.today())
    current = (end - timedelta(30) * 24).replace(day=1)
    months = []
    outstanding = []
    outstanding_overdue = []
    graph_data = []
    while current < end:
        months.append(current.isoformat())
        next_month = nextMonth(current)
        outstanding.append(
            float(
                ClientBill.objects.filter(due_date__lte=next_month).exclude(
                    payment_date__lt=next_month).aggregate(
                        Sum("amount"))["amount__sum"] or 0))
        outstanding_overdue.append(
            float(
                ClientBill.objects.filter(due_date__lte=current).exclude(
                    payment_date__lt=next_month).aggregate(
                        Sum("amount"))["amount__sum"] or 0))
        current = next_month

    graph_data.append(["x"] + months)
    graph_data.append([_("billing outstanding")] + outstanding)
    graph_data.append([_("billing outstanding overdue")] + outstanding_overdue)

    return render(
        request, "billing/graph_outstanding_billing.html", {
            "graph_data": json.dumps(graph_data),
            "series_colors": COLORS,
            "user": request.user
        })
Beispiel #3
0
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
Beispiel #4
0
    def pivotable_data(self, startDate=None, endDate=None):
        """Compute raw data for pivot table on that mission"""
        #TODO: factorize with staffing.views.mission_timesheet
        data = []
        mission_id = self.mission_id()
        mission_name = self.short_name()
        current_month = date.today().replace(day=1)  # Current month
        subsidiary = unicode(self.subsidiary)
        dateTrunc = connections[Timesheet.objects.db].ops.date_trunc_sql  # Shortcut to SQL date trunc function
        consultant_rates = self.consultant_rates()
        billing_mode = self.get_billing_mode_display()

        # Gather timesheet (Only consider timesheet up to current month)
        timesheets = Timesheet.objects.filter(mission=self).filter(working_date__lt=nextMonth(current_month)).order_by("working_date")
        if startDate:
            timesheets = timesheets.filter(working_date__gte=startDate)
        if endDate:
            timesheets = timesheets.filter(working_date__lte=endDate)
        timesheetMonths = list(timesheets.dates("working_date", "month"))

        for consultant in self.consultants():
            consultant_name = unicode(consultant)
            timesheet_data = dict(timesheets.filter(consultant=consultant).extra(select={'month': dateTrunc("month", "working_date")}).values_list("month").annotate(Sum("charge")).order_by("month"))
            timesheet_data = convertDictKeyToDate(timesheet_data)

            for month in timesheetMonths:
                data.append({ugettext("mission id"): mission_id,
                             ugettext("mission name"): mission_name,
                             ugettext("consultant"): consultant_name,
                             ugettext("subsidiary"): subsidiary,
                             ugettext("billing mode"): billing_mode,
                             ugettext("date"): month.strftime("%Y/%m"),
                             ugettext("done (days)"): timesheet_data.get(month, 0),
                             ugettext("done (keur)"): timesheet_data.get(month, 0) * consultant_rates[consultant][0] / 1000})
        return data
Beispiel #5
0
    def pivotable_data(self, startDate=None, endDate=None):
        """Compute raw data for pivot table on that mission"""
        #TODO: factorize with staffing.views.mission_timesheet
        data = []
        mission_id = self.mission_id()
        mission_name = self.short_name()
        current_month = date.today().replace(day=1)  # Current month
        subsidiary = unicode(self.subsidiary)
        dateTrunc = connections[Timesheet.objects.db].ops.date_trunc_sql  # Shortcut to SQL date trunc function
        consultant_rates = self.consultant_rates()
        billing_mode = self.get_billing_mode_display()

        # Gather timesheet (Only consider timesheet up to current month)
        timesheets = Timesheet.objects.filter(mission=self).filter(working_date__lt=nextMonth(current_month)).order_by("working_date")
        if startDate:
            timesheets = timesheets.filter(working_date__gte=startDate)
        if endDate:
            timesheets = timesheets.filter(working_date__lte=endDate)
        timesheetMonths = list(timesheets.dates("working_date", "month"))

        for consultant in self.consultants():
            consultant_name = unicode(consultant)
            timesheet_data = dict(timesheets.filter(consultant=consultant).extra(select={'month': dateTrunc("month", "working_date")}).values_list("month").annotate(Sum("charge")).order_by("month"))
            timesheet_data = convertDictKeyToDate(timesheet_data)

            for month in timesheetMonths:
                data.append({ugettext("mission id"): mission_id,
                             ugettext("mission name"): mission_name,
                             ugettext("consultant"): consultant_name,
                             ugettext("subsidiary"): subsidiary,
                             ugettext("billing mode"): billing_mode,
                             ugettext("date"): month.strftime("%Y/%m"),
                             ugettext("done (days)"): timesheet_data.get(month, 0),
                             ugettext("done (keur)"): timesheet_data.get(month, 0) * consultant_rates[consultant][0] / 1000})
        return data
Beispiel #6
0
    def forecasted_work(self):
        """Compute forecasted work according to staffing for this mission
        Result is cached for few seconds
        @return: (forecasted work in days, forecasted work in euros"""
        rates = dict([(i.id, j[0]) for i, j in self.consultant_rates().items()
                      ])  # switch to consultant id
        days = 0
        amount = 0
        current_month = date.today().replace(day=1)
        staffings = Staffing.objects.filter(mission=self,
                                            staffing_date__gte=current_month)
        staffings = staffings.values_list("consultant").annotate(
            Sum("charge")).order_by()
        current_month_done = Timesheet.objects.filter(
            mission=self,
            working_date__gte=current_month,
            working_date__lt=nextMonth(date.today()))
        current_month_done = dict(
            current_month_done.values_list("consultant").annotate(
                Sum("charge")).order_by())
        for consultant_id, charge in staffings:
            days += charge  # Add forecasted days
            days -= current_month_done.get(
                consultant_id,
                0)  # Substract current month done works from forecastinng
            if consultant_id in rates:
                amount += charge * rates[consultant_id]
                amount -= current_month_done.get(consultant_id,
                                                 0) * rates[consultant_id]

        return (days, amount)
Beispiel #7
0
 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]])
Beispiel #8
0
def holidayDays(month=None):
    """
    @param month: month (datetime) to consider for holidays. Current month if None
    @return: list of holidays days of given month """
    if not month:
        month = date.today()
    month = month.replace(day=1)
    return [h.day for h in  Holiday.objects.filter(day__gte=month).filter(day__lt=nextMonth(month))]
Beispiel #9
0
def holidayDays(month=None):
    """
    @param month: month (datetime) to consider for holidays. Current month if None
    @return: list of holidays days of given month """
    if not month:
        month = date.today()
    month = month.replace(day=1)
    return [h.day for h in  Holiday.objects.filter(day__gte=month).filter(day__lt=nextMonth(month))]
Beispiel #10
0
def compute_automatic_staffing(mission, mode, duration, user=None):
    """Compute staffing for a given mission. Mode can be after (current staffing) for replace (erase and create)"""
    now = datetime.now().replace(microsecond=0)  # Remove useless microsecond
    current_month = date.today().replace(day=1)
    start_date = current_month
    total = 0

    if not mission.consultants():
        # no consultant, no staffing. Come on.
        return

    if mode=="replace":
        mission.staffing_set.all().delete()
        cache.delete("Mission.forecasted_work%s" % mission.id)
        cache.delete("Mission.done_work%s" % mission.id)
        if mission.lead:
            start_date = max(current_month, mission.lead.start_date.replace(day=1))
    else:
        max_staffing = Staffing.objects.filter(mission=mission).aggregate(Max("staffing_date"))["staffing_date__max"]
        if max_staffing:
            start_date = max(current_month, nextMonth(max_staffing))

    if mission.start_date:
        start_date = max(start_date, mission.start_date)

    margin = mission.remaining(mode="target")
    rates = mission.consultant_rates()
    rates_sum = sum([i[0] for i in rates.values()])
    days = margin*1000 / rates_sum / duration
    days = max(floor(days * 4) / 4, 0.25)

    for consultant in rates.keys():
        month = start_date
        for i in range(duration):
            if total > margin*1000:
                break
            if mission.end_date and month > mission.end_date:
                break
            s = Staffing(mission=mission, consultant=consultant, charge=days, staffing_date=month, update_date = now)
            if user:
                s.last_user = str(user)
            s.save()
            total += days * rates[consultant][0]

            month = nextMonth(month)
Beispiel #11
0
    def pivotable_data(self, startDate=None, endDate=None):
        """Compute raw data for pivot table on that mission"""
        #TODO: factorize with staffing.views.mission_timesheet
        #TODO: denormalize by adding done/planned as a type column and move days/amount in values columns
        data = []
        mission_id = self.mission_id()
        mission_name = self.short_name()
        current_month = date.today().replace(day=1)  # Current month
        subsidiary = str(self.subsidiary)
        consultant_rates = self.consultant_rates()
        billing_mode = self.get_billing_mode_display()

        # Gather timesheet and staffing (Only consider data up to current month)
        timesheets = Timesheet.objects.filter(mission=self).filter(working_date__lt=nextMonth(current_month)).order_by("working_date")
        staffings = Staffing.objects.filter(mission=self).filter(staffing_date__gte=nextMonth(current_month)).order_by("staffing_date")
        if startDate:
            timesheets = timesheets.filter(working_date__gte=startDate)
            staffings = staffings.filter(staffing_date__gte=startDate)
        if endDate:
            timesheets = timesheets.filter(working_date__lte=endDate)
            staffings = staffings.filter(staffing_date__lte=endDate)
        timesheetMonths = list(timesheets.dates("working_date", "month"))
        staffingMonths = list(staffings.dates("staffing_date", "month"))

        for consultant in self.consultants():
            consultant_name = str(consultant)
            timesheet_data = dict(timesheets.filter(consultant=consultant).annotate(month=TruncMonth("working_date")).values_list("month").annotate(Sum("charge")).order_by("month"))
            staffing_data = dict(staffings.filter(consultant=consultant).values_list("staffing_date").annotate(Sum("charge")).order_by("staffing_date"))

            for month in set(timesheetMonths + staffingMonths):
                data.append({ugettext("mission id"): mission_id,
                             ugettext("mission name"): mission_name,
                             ugettext("consultant"): consultant_name,
                             ugettext("subsidiary"): subsidiary,
                             ugettext("billing mode"): billing_mode,
                             ugettext("date"): month.strftime("%Y/%m"),
                             ugettext("done (days)"): timesheet_data.get(month, 0),
                             ugettext("done (€)"): timesheet_data.get(month, 0) * consultant_rates[consultant][0],
                             ugettext("forecast (days)"): staffing_data.get(month, 0),
                             ugettext("forecast (€)"): staffing_data.get(month, 0) * consultant_rates[consultant][0]})
        return data
Beispiel #12
0
def gatherTimesheetData(consultant, missions, month):
    """Gather existing timesheet timesheetData
    @returns: (timesheetData, timesheetTotal, warning)
    timesheetData represent timesheet form post timesheetData as a dict
    timesheetTotal is a dict of total charge (key is mission id)
    warning is a list of 0 (ok) or 1 (surbooking) or 2 (no data). One entry per day"""
    timesheetData = {}
    timesheetTotal = {}
    warning = []
    totalPerDay = [0] * month_days(month)
    next_month = nextMonth(month)
    for mission in missions:
        timesheets = Timesheet.objects.select_related().filter(
            consultant=consultant).filter(mission=mission)
        timesheets = timesheets.filter(working_date__gte=month).filter(
            working_date__lt=next_month)
        for timesheet in timesheets:
            timesheetData["charge_%s_%s" %
                          (timesheet.mission.id,
                           timesheet.working_date.day)] = timesheet.charge
            if mission.id in timesheetTotal:
                timesheetTotal[mission.id] += timesheet.charge
            else:
                timesheetTotal[mission.id] = timesheet.charge
            totalPerDay[timesheet.working_date.day - 1] += timesheet.charge
    # Gather lunck ticket data
    totalTicket = 0
    lunchTickets = LunchTicket.objects.filter(consultant=consultant)
    lunchTickets = lunchTickets.filter(lunch_date__gte=month).filter(
        lunch_date__lt=next_month)
    for lunchTicket in lunchTickets:
        timesheetData["lunch_ticket_%s" %
                      lunchTicket.lunch_date.day] = lunchTicket.no_ticket
        totalTicket += 1
    timesheetTotal["ticket"] = totalTicket
    # Compute warnings (overbooking and no data)
    for i in totalPerDay:
        i = round(
            i, 4
        )  # We must round because using keyboard time input may lead to real numbers that are truncated
        if i > 1:  # Surbooking
            warning.append(1)
        elif i == 1:  # Ok
            warning.append(0)
        else:  # warning (no data, or half day)
            warning.append(2)
    # Don't emit warning for no data during week ends and holidays
    holiday_days = holidayDays(month)
    for day in daysOfMonth(month):
        if day.isoweekday() in (6, 7) or day in holiday_days:
            warning[day.day - 1] = None

    return (timesheetData, timesheetTotal, warning)
Beispiel #13
0
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
Beispiel #14
0
    def __init__(self, *args, **kwargs):
        minDate = kwargs.pop("minDate", date.today() - timedelta(30*11))
        nMonth = kwargs.pop("nMonth", 12)
        months = []
        month = minDate.replace(day=1)
        for i in range(nMonth):
            months.append(month)
            month = nextMonth(month)

        kwargs["choices"] = [(i, formats.date_format(i, format="YEAR_MONTH_FORMAT")) for i in months]
        kwargs["empty_value"] = None

        super(BillingDateChoicesField, self).__init__(*args, **kwargs)
Beispiel #15
0
def staffingDates(n=12, format=None, minDate=None):
    """Returns a list of n next month as datetime (if format="datetime") or
    as a list of dict() with short/long(encoded) string date"""
    staffingDate = minDate or date.today().replace(day=1)
    dates = []
    for i in range(n):
        if format == "datetime":
            dates.append(staffingDate)
        else:
            dates.append({"value": formats.localize_input(staffingDate),
                          "label": formats.date_format(staffingDate, format="YEAR_MONTH_FORMAT").encode("latin-1"), })
        staffingDate = nextMonth(staffingDate)
    return dates
Beispiel #16
0
def staffingDates(n=12, format=None, minDate=None):
    """Returns a list of n next month as datetime (if format="datetime") or
    as a list of dict() with short/long(encoded) string date"""
    staffingDate = minDate or date.today().replace(day=1)
    dates = []
    for i in range(int(n)):
        if format == "datetime":
            dates.append(staffingDate)
        else:
            dates.append({"value": formats.localize_input(staffingDate),
                          "label": formats.date_format(staffingDate, format="YEAR_MONTH_FORMAT").encode("latin-1"), })
        staffingDate = nextMonth(staffingDate)
    return dates
Beispiel #17
0
    def __init__(self, *args, **kwargs):
        minDate = kwargs.pop("minDate", date.today() - timedelta(30*11))
        nMonth = kwargs.pop("nMonth", 12)
        months = []
        month = minDate.replace(day=1)
        for i in range(nMonth):
            months.append(month)
            month = nextMonth(month)

        kwargs["choices"] = [(i, formats.date_format(i, format="YEAR_MONTH_FORMAT")) for i in months]
        kwargs["empty_value"] = None

        super(BillingDateChoicesField, self).__init__(*args, **kwargs)
Beispiel #18
0
def timesheet_report_data_grouped(mission, start=None, end=None):
    """Timesheet charges for a single mission, on a timerange, by whole month
    For each month, charges are grouped by daily rate
    Returns a list of lines to be sent as CSV"""

    timesheets = Timesheet.objects.select_related().filter(mission=mission)
    months = timesheets.dates("working_date", "month")
    data = []

    data.append([mission.short_name()])
    rates_consultants = {}
    for consultant, rate in mission.consultant_rates().items():
        daily_rate, _ = rate
        if daily_rate not in rates_consultants:
            rates_consultants[daily_rate] = []
        rates_consultants[daily_rate].append(consultant)

    for month in months:
        if start and month < start:
            continue
        if end and month > end:
            break
        next_month = nextMonth(month)

        # Header
        data.append([""])
        data.append([formats.date_format(month, format="YEAR_MONTH_FORMAT")])

        rates = sorted(rates_consultants.keys())
        for rate in rates:
            rate_label = "R {}".format(rate)
            total = 0
            row = [
                rate_label,
            ]

            # timesheets is already a Queryset, we cannot aggregate in SQL
            rate_timesheets_charges = timesheets.filter(
                consultant__in=rates_consultants[rate],
                working_date__gte=month,
                working_date__lt=next_month).values("charge")

            for c in rate_timesheets_charges:
                total += c["charge"]
            row.append(total)

            if total:
                data.append(row)

    return data
Beispiel #19
0
 def done_work(self):
     """Compute done work according to timesheet for this mission
     Result is cached for few seconds
     @return: (done work in days, done work in euros)"""
     rates = dict([(i.id, j[0]) for i, j in self.consultant_rates().items()])  # switch to consultant id
     days = 0
     amount = 0
     timesheets = Timesheet.objects.filter(mission=self, working_date__lt=nextMonth(date.today()))
     timesheets = timesheets.values_list("consultant").annotate(Sum("charge")).order_by()
     for consultant_id, charge in timesheets:
         days += charge
         if consultant_id in rates:
             amount += charge * rates[consultant_id]
     return (days, amount)
Beispiel #20
0
 def done_work(self):
     """Compute done work according to timesheet for this mission
     Result is cached for few seconds
     @return: (done work in days, done work in euros)"""
     rates = dict([(i.id, j[0]) for i, j in self.consultant_rates().items()])  # switch to consultant id
     days = 0
     amount = 0
     timesheets = Timesheet.objects.filter(mission=self, working_date__lt=nextMonth(date.today()))
     timesheets = timesheets.values_list("consultant").annotate(Sum("charge")).order_by()
     for consultant_id, charge in timesheets:
         days += charge
         if consultant_id in rates:
             amount += charge * rates[consultant_id]
     return (days, amount)
Beispiel #21
0
def update_client_bill_from_timesheet(bill, mission, start_date, end_date):
    """Populate bill detail for given mission from timesheet of given interval"""
    ClientBill = apps.get_model("billing", "clientbill")
    BillDetail = apps.get_model("billing", "billdetail")
    Consultant = apps.get_model("people", "Consultant")
    rates = mission.consultant_rates()
    month = start_date
    while month < end_date:
        timesheet_data = mission.timesheet_set.filter(
            working_date__gte=month, working_date__lt=nextMonth(month))
        timesheet_data = timesheet_data.order_by("consultant").values(
            "consultant").annotate(Sum("charge"))
        for i in timesheet_data:
            consultant = Consultant.objects.get(id=i["consultant"])
            billDetail = BillDetail(bill=bill,
                                    mission=mission,
                                    month=month,
                                    consultant=consultant,
                                    quantity=i["charge__sum"],
                                    unit_price=rates[consultant][0])
            billDetail.save()
        month = nextMonth(month)
    bill.save()  # save again to update bill amount according to its details
    return bill
Beispiel #22
0
def compute_automatic_staffing(mission, mode, duration, user=None):
    """Compute staffing for a given mission. Mode can be after (current staffing) for replace (erase and create)"""
    now = datetime.now().replace(microsecond=0)  # Remove useless microsecond
    current_month = date.today().replace(day=1)
    start_date = current_month
    total = 0

    if mode=="replace":
        mission.staffing_set.all().delete()
        cache.delete("Mission.forecasted_work%s" % mission.id)
        cache.delete("Mission.done_work%s" % mission.id)
        if mission.lead:
            start_date = max(current_month, mission.lead.start_date.replace(day=1))
    else:
        max_staffing = Staffing.objects.filter(mission=mission).aggregate(Max("staffing_date"))["staffing_date__max"]
        if max_staffing:
            start_date = max(current_month, nextMonth(max_staffing))

    margin = mission.margin(mode="target")
    rates = mission.consultant_rates()
    rates_sum = sum([i[0] for i in rates.values()])
    days = margin*1000 / rates_sum / duration
    days = max(floor(days * 4) / 4, 0.25)

    for consultant in rates.keys():
        month = start_date
        for i in range(duration):
            if total > margin*1000:
                break
            s = Staffing(mission=mission, consultant=consultant, charge=days, staffing_date=month, update_date = now)
            if user:
                s.last_user = str(user)
            s.save()
            total += days * rates[consultant][0]

            month = nextMonth(month)
Beispiel #23
0
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})
Beispiel #24
0
 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
Beispiel #25
0
 def forecasted_work(self):
     """Compute forecasted work according to staffing for this mission
     Result is cached for few seconds
     @return: (forecasted work in days, forecasted work in euros"""
     rates = dict([(i.id, j[0]) for i, j in self.consultant_rates().items()
                   ])  # switch to consultant id
     days = 0
     amount = 0
     current_month = date.today().replace(day=1)
     staffings = Staffing.objects.filter(mission=self,
                                         staffing_date__gte=current_month)
     staffings = staffings.values_list("consultant").annotate(
         Sum("charge")).order_by()
     current_month_done = Timesheet.objects.filter(
         mission=self,
         working_date__gte=current_month,
         working_date__lt=date.today())
     current_month_done = dict(
         current_month_done.values_list("consultant").annotate(
             Sum("charge")).order_by())
     current_month_staffing = Staffing.objects.filter(
         mission=self,
         staffing_date__gte=current_month,
         staffing_date__lt=nextMonth(current_month))
     current_month_staffing = dict(
         current_month_staffing.values_list("consultant").annotate(
             Sum("charge")).order_by())
     for consultant_id, charge in staffings:
         days += charge  # Add forecasted days
         current_month_balance = current_month_staffing.get(
             consultant_id, 0) - current_month_done.get(consultant_id, 0)
         charge_adjustement = 0
         if current_month_balance > 0:
             charge_adjustement = -current_month_done.get(
                 consultant_id, 0)  # leave remaining forecast
         else:
             charge_adjustement = -current_month_staffing.get(
                 consultant_id, 0)  # forecast has been exhausted
         days += charge_adjustement
         if consultant_id in rates:
             amount += (charge + charge_adjustement) * rates[consultant_id]
     if days < 0:
         # Negative forecast, means no forecast.
         days = 0
         amount = 0
     return (days, amount)
Beispiel #26
0
def gatherTimesheetData(consultant, missions, month):
    """Gather existing timesheet timesheetData
    @returns: (timesheetData, timesheetTotal, warning)
    timesheetData represent timesheet form post timesheetData as a dict
    timesheetTotal is a dict of total charge (key is mission id)
    warning is a list of 0 (ok) or 1 (surbooking) or 2 (no data). One entry per day"""
    timesheetData = {}
    timesheetTotal = {}
    warning = []
    totalPerDay = [0] * month_days(month)
    next_month = nextMonth(month)
    for mission in missions:
        timesheets = Timesheet.objects.select_related().filter(consultant=consultant).filter(mission=mission)
        timesheets = timesheets.filter(working_date__gte=month).filter(working_date__lt=next_month)
        for timesheet in timesheets:
            timesheetData["charge_%s_%s" % (timesheet.mission.id, timesheet.working_date.day)] = timesheet.charge
            if mission.id in timesheetTotal:
                timesheetTotal[mission.id] += timesheet.charge
            else:
                timesheetTotal[mission.id] = timesheet.charge
            totalPerDay[timesheet.working_date.day - 1] += timesheet.charge
    # Gather lunck ticket data
    totalTicket = 0
    lunchTickets = LunchTicket.objects.filter(consultant=consultant)
    lunchTickets = lunchTickets.filter(lunch_date__gte=month).filter(lunch_date__lt=next_month)
    for lunchTicket in lunchTickets:
        timesheetData["lunch_ticket_%s" % lunchTicket.lunch_date.day] = lunchTicket.no_ticket
        totalTicket += 1
    timesheetTotal["ticket"] = totalTicket
    # Compute warnings (overbooking and no data)
    for i in totalPerDay:
        i = round(i, 4)  # We must round because using keyboard time input may lead to real numbers that are truncated
        if i > 1:  # Surbooking
            warning.append(1)
        elif i == 1:  # Ok
            warning.append(0)
        else:  # warning (no data, or half day)
            warning.append(2)
    # Don't emit warning for no data during week ends and holidays
    holiday_days = holidayDays(month)
    for day in daysOfMonth(month):
        if day.isoweekday() in (6, 7) or day in holiday_days:
            warning[day.day - 1] = None

    return (timesheetData, timesheetTotal, warning)
Beispiel #27
0
 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)
Beispiel #28
0
def create_client_bill_from_timesheet(mission, month):
    """Create (and return) a bill and bill detail for given mission from timesheet of given month"""
    ClientBill = apps.get_model("billing", "clientbill")
    BillDetail = apps.get_model("billing", "billdetail")
    bill = ClientBill(lead=mission.lead)
    bill.save()
    rates = mission.consultant_rates()
    timesheet_data = mission.timesheet_set.filter(
        working_date__gte=month, working_date__lt=nextMonth(month))
    timesheet_data = timesheet_data.order_by("consultant").values(
        "consultant").annotate(Sum("charge"))
    for i in timesheet_data:
        consultant = Consultant.objects.get(id=i["consultant"])
        billDetail = BillDetail(bill=bill,
                                mission=mission,
                                month=month,
                                consultant=consultant,
                                quantity=i["charge__sum"],
                                unit_price=rates[consultant][0])
        billDetail.save()
    compute_bill(bill)  # update bill amount according to its details
    return bill
Beispiel #29
0
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
Beispiel #30
0
def graph_leads_pipe(request):
    """Graph in/out leads for given (or all) subsidiary"""
    graph_data = []
    input_count = {}
    input_amount = {}
    output_count = {}
    output_amount = {}
    output_states = ("WON", "LOST", "FORGIVEN", "SLEEPING")
    start_date = (datetime.today() - timedelta(3 * 365))
    subsidiary = get_subsidiary_from_session(request)
    leads = Lead.objects.filter(creation_date__gt=start_date)
    leads = leads.annotate(timesheet_start=Min("mission__timesheet__working_date"))
    if subsidiary:
        leads = leads.filter(subsidiary=subsidiary)

    for lead in leads:
        month = lead.creation_date.replace(day=1).date()
        input_count[month] = input_count.get(month, 0) + 1
        input_amount[month] = input_amount.get(month, 0) + (lead.sales or 0)
        if lead.state in output_states:
            out_date = lead.timesheet_start or lead.start_date or lead.update_date.date()
            out_date = out_date.replace(day=1)
            output_count[out_date] = output_count.get(out_date, 0) - 1
            output_amount[out_date] = output_amount.get(out_date, 0) - (lead.sales or 0)

    pipe_end_date = max(max(output_count.keys()), max(input_count.keys()))
    pipe_start_date = min(min(output_count.keys()), min(input_count.keys()))
    months = []
    month = pipe_start_date
    pipe_count = [0]  # start with fake 0 to allow sum with previous month
    pipe_amount = [0]
    while month <= pipe_end_date:
        months.append(month)
        pipe_count.append(pipe_count[-1] + input_count.get(month, 0) + output_count.get(month, 0))
        pipe_amount.append(pipe_amount[-1] + input_amount.get(month, 0) + output_amount.get(month, 0))
        month = nextMonth(month)
    # Remove fake zero
    pipe_count.pop(0)
    pipe_amount.pop(0)

    # Pad for month without data and switch to list of values
    input_count = [input_count.get(month, 0) for month in months]
    input_amount = [round(input_amount.get(month, 0)) for month in months]
    output_count = [output_count.get(month, 0) for month in months]
    output_amount = [round(output_amount.get(month, 0)) for month in months]

    # Compute offset by measuring pipe of last month
    current_leads = Lead.objects.exclude(state__in=output_states)
    if subsidiary:
        current_leads = current_leads.filter(subsidiary=subsidiary)
    offset_count = current_leads.count() - pipe_count[-1]
    pipe_count = [i + offset_count for i in pipe_count]
    offset_amount = (current_leads.aggregate(Sum("sales"))["sales__sum"] or 0) - pipe_amount[-1]
    pipe_amount = [round(i + offset_amount) for i in pipe_amount]

    graph_data.append(["x"] + [i.isoformat() for i in months])
    graph_data.append(["input_count"] + input_count)
    graph_data.append(["output_count"] + output_count)
    graph_data.append(["pipe_count"] + pipe_count)
    graph_data.append(["input_amount"] + input_amount)
    graph_data.append(["output_amount"] + output_amount)
    graph_data.append(["pipe_amount"] + pipe_amount)

    count_max = max([max(input_count), -max(output_count), max(pipe_count)])
    amount_max = max([max(input_amount), -max(output_amount), max(pipe_amount)])

    return render(request, "leads/graph_leads_pipe.html",
              {"graph_data": json.dumps(graph_data),
               "count_max": count_max,
               "amount_max": amount_max,
               "series_colors": COLORS,
               "user": request.user})
Beispiel #31
0
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})
Beispiel #32
0
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
        })
Beispiel #33
0
def create_client_bill_from_timesheet(mission, month):
    """Create (and return) a bill and bill detail for given mission from timesheet of given month"""
    ClientBill = apps.get_model("billing", "clientbill")
    BillDetail = apps.get_model("billing", "billdetail")
    bill = ClientBill(lead=mission.lead)
    bill.save()
    rates = mission.consultant_rates()
    timesheet_data = mission.timesheet_set.filter(working_date__gte=month, working_date__lt=nextMonth(month))
    timesheet_data = timesheet_data.order_by("consultant").values("consultant").annotate(Sum("charge"))
    for i in timesheet_data:
        consultant = Consultant.objects.get(id=i["consultant"])
        billDetail =  BillDetail(bill=bill, mission=mission, month=month, consultant=consultant, quantity=i["charge__sum"], unit_price=rates[consultant][0])
        billDetail.save()
    compute_bill(bill)  # update bill amount according to its details
    return bill
Beispiel #34
0
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})
Beispiel #35
0
 def done_work(self):
     """Compute done work according to timesheet for this mission
     Result is cached for few seconds
     @return: (done work in days, done work in euros)"""
     return self.done_work_period(None, nextMonth(date.today()))
Beispiel #36
0
 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]])
Beispiel #37
0
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
Beispiel #38
0
    def forecasted_work(self):
        """Compute forecasted work according to staffing for this mission
        Result is cached for few seconds
        @return: (forecasted work in days, forecasted work in euros"""
        rates = dict([(i.id, j[0]) for i, j in self.consultant_rates().items()])  # switch to consultant id
        days = 0
        amount = 0
        current_month = date.today().replace(day=1)
        staffings = Staffing.objects.filter(mission=self, staffing_date__gte=current_month)
        staffings = staffings.values_list("consultant").annotate(Sum("charge")).order_by()
        current_month_done = Timesheet.objects.filter(mission=self, working_date__gte=current_month, working_date__lt=nextMonth(date.today()))
        current_month_done = dict(current_month_done.values_list("consultant").annotate(Sum("charge")).order_by())
        for consultant_id, charge in staffings:
            days += charge  # Add forecasted days
            days -= current_month_done.get(consultant_id, 0) # Substract current month done works from forecastinng
            if consultant_id in rates:
                amount += charge * rates[consultant_id]
                amount -= current_month_done.get(consultant_id, 0) * rates[consultant_id]

        return (days, amount)
Beispiel #39
0
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
        })
Beispiel #40
0
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})
Beispiel #41
0
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
Beispiel #42
0
def graph_people_count(request, subsidiary_id=None, team_id=None):
    """Active people count
    @:param subsidiary_id: filter graph on the given subsidiary
    @:param team_id: filter graph on the given team"""
    #TODO: add start/end timeframe
    graph_data = []
    iso_months = []
    start_date = (date.today() - 3 * timedelta(365)).replace(day=1)  # Last three years
    end_date = date.today().replace(day=1)
    consultants_count = {}
    subcontractors_count = {}

    consultants = Consultant.objects.filter(subcontractor=False, productive=True)
    subcontractors = Consultant.objects.filter(subcontractor=True, productive=True)

    subsidiaries = Subsidiary.objects.filter(mission__nature="PROD")
    subsidiaries = subsidiaries.annotate(Count("mission__timesheet__consultant"))
    subsidiaries = subsidiaries.filter(mission__timesheet__consultant__count__gt=0)

    if subsidiary_id:
        subsidiaries = subsidiaries.filter(subsidiary_id=subsidiary_id)

    for subsidiary in subsidiaries:
        consultants_count[subsidiary] = []
        subcontractors_count[subsidiary] = []

    # Filter on scope
    if team_id:
        consultants = consultants.filter(staffing_manager_id=team_id)
        subcontractors = subcontractors.filter(staffing_manageid=0)  # Don't consider subcontractors for team counting
    elif subsidiary_id:
        consultants = consultants.filter(company_id=subsidiary_id)
        subcontractors = subcontractors.filter(timesheet__mission__subsidiar__id=subsidiary_id)

    month = start_date
    while month < end_date:
        next_month = nextMonth(month)
        iso_months.append(month.isoformat())
        for subsidiary in subsidiaries:
            consultants_count[subsidiary].append(consultants.filter(company=subsidiary,
                                                                    timesheet__working_date__gte=month,
                                                                    timesheet__working_date__lt=next_month).distinct().count())
            subcontractors_count[subsidiary].append(subcontractors.filter(timesheet__working_date__gte=month,
                                                                          timesheet__working_date__lt=next_month,
                                                                          timesheet__mission__subsidiary=subsidiary,
                                                                          timesheet__mission__nature="PROD").distinct().count())

        month = next_month

    if not iso_months or set(consultants_count) == {None}:
        return HttpResponse('')

    graph_data.append(["x"] + iso_months)
    for subsidiary in subsidiaries:
        graph_data.append([_("consultants %s" % subsidiary)] + consultants_count[subsidiary])
        graph_data.append([_("subcontractors %s" % subsidiary)] + subcontractors_count[subsidiary])

    return render(request, "people/graph_people_count.html",
              {"graph_data": json.dumps(graph_data),
               "series_colors": COLORS,
               "subsidiaries": subsidiaries,
               "user": request.user})
Beispiel #43
0
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
        })
Beispiel #44
0
def get_client_billing_control_pivotable_data(filter_on_subsidiary=None,
                                              filter_on_company=None,
                                              filter_on_lead=None,
                                              only_active=False):
    """Compute pivotable to check lead/mission billing."""
    # local import to avoid circurlar weirdness
    ClientBill = apps.get_model("billing", "ClientBill")
    BillDetail = apps.get_model("billing", "BillDetail")
    BillExpense = apps.get_model("billing", "BillExpense")
    Lead = apps.get_model("leads", "Lead")
    Expense = apps.get_model("expense", "Expense")
    Consultant = apps.get_model("people", "Consultant")

    data = []
    bill_state = ("1_SENT", "2_PAID"
                  )  # Only consider clients bills in those status
    leads = Lead.objects.all()
    if filter_on_subsidiary:
        leads = leads.filter(subsidiary=filter_on_subsidiary)
    if filter_on_company:
        leads = leads.filter(client__organisation__company=filter_on_company)
    if filter_on_lead:
        leads = leads.filter(id=filter_on_lead.id)

    if only_active:
        leads = leads.filter(mission__active=True).distinct()

    leads = leads.select_related("client__organisation__company",
                                 "business_broker__company", "subsidiary")

    for lead in leads:
        lead_data = {
            _("deal id"): lead.deal_id,
            _("client organisation"): str(lead.client.organisation),
            _("client company"): str(lead.client.organisation.company),
            _("broker"): str(lead.business_broker or _("Direct")),
            _("subsidiary"): str(lead.subsidiary),
            _("responsible"): str(lead.responsible),
            _("consultant"): "-"
        }
        # Add legacy bills non related to specific mission (ie. not using pydici billing, just header and pdf payload)
        legacy_bills = ClientBill.objects.filter(
            lead=lead,
            state__in=bill_state).annotate(Count("billdetail"),
                                           Count("billexpense")).filter(
                                               billdetail__count=0,
                                               billexpense__count=0)
        for legacy_bill in legacy_bills:
            legacy_bill_data = lead_data.copy()
            legacy_bill_data[_("amount")] = -float(legacy_bill.amount or 0)
            legacy_bill_data[_("month")] = legacy_bill.creation_date.replace(
                day=1).isoformat()
            legacy_bill_data[_("type")] = _("Service bill")
            legacy_bill_data[_("mission")] = "-"
            mission = lead.mission_set.first()
            if mission:  # default to billing mode of first mission. Not 100% accurate...
                legacy_bill_data[_(
                    "billing mode")] = mission.get_billing_mode_display()
            data.append(legacy_bill_data)
        # Add chargeable expense
        expenses = Expense.objects.filter(lead=lead, chargeable=True)
        bill_expenses = BillExpense.objects.filter(bill__lead=lead).exclude(
            expense_date=None)
        for qs, label, way in ((expenses, _("Expense"), 1),
                               (bill_expenses, _("Expense bill"), -1)):
            qs = qs.annotate(month=TruncMonth("expense_date")).order_by(
                "month").values("month")
            for month, amount in qs.annotate(Sum("amount")).values_list(
                    "month", "amount__sum"):
                expense_data = lead_data.copy()
                expense_data[_("month")] = month.isoformat()
                expense_data[_("type")] = label
                expense_data[_("billing mode")] = _("Chargeable expense")
                expense_data[_("amount")] = float(amount) * way
                data.append(expense_data)
        # Add new-style client bills and done work per mission
        for mission in lead.mission_set.all().select_related("responsible"):
            mission_data = lead_data.copy()
            mission_data[_("mission")] = mission.short_name()
            mission_data[_("responsible")] = str(mission.responsible
                                                 or mission.lead.responsible)
            mission_data[_(
                "billing mode")] = mission.get_billing_mode_display()
            # Add fixed price bills
            if mission.billing_mode == "FIXED_PRICE":
                for billDetail in BillDetail.objects.filter(
                        mission=mission, bill__state__in=bill_state):
                    mission_fixed_price_data = mission_data.copy()
                    mission_fixed_price_data[_(
                        "month")] = billDetail.bill.creation_date.replace(
                            day=1).isoformat()
                    mission_fixed_price_data[_("type")] = _("Service bill")
                    mission_fixed_price_data[_("amount")] = -float(
                        billDetail.amount or 0)
                    data.append((mission_fixed_price_data))
            # Add done work and time spent bills
            consultants = Consultant.objects.filter(
                timesheet__mission=mission).distinct()
            for month in mission.timesheet_set.dates("working_date",
                                                     "month",
                                                     order="ASC"):
                next_month = nextMonth(month)
                for consultant in consultants:
                    mission_month_consultant_data = mission_data.copy()
                    turnover = float(
                        mission.done_work_period(
                            month,
                            next_month,
                            include_external_subcontractor=True,
                            include_internal_subcontractor=True,
                            filter_on_consultant=consultant)[1])
                    mission_month_consultant_data[_("consultant")] = str(
                        consultant)
                    mission_month_consultant_data[_(
                        "month")] = month.isoformat()
                    mission_month_consultant_data[_("amount")] = turnover
                    mission_month_consultant_data[_("type")] = _("Done work")
                    data.append(mission_month_consultant_data)
                    if mission.billing_mode == "TIME_SPENT":
                        # Add bills for time spent mission
                        mission_month_consultant_data = mission_month_consultant_data.copy(
                        )
                        billed = BillDetail.objects.filter(
                            mission=mission,
                            consultant=consultant,
                            month=month,
                            bill__state__in=bill_state)
                        billed = float(
                            billed.aggregate(Sum("amount"))["amount__sum"]
                            or 0)
                        mission_month_consultant_data[_("amount")] = -billed
                        mission_month_consultant_data[_("type")] = _(
                            "Service bill")
                        data.append(mission_month_consultant_data)

    return json.dumps(data)
Beispiel #45
0
def graph_people_count(request):
    """Active people count"""
    #TODO: add start/end timeframe
    graph_data = []
    iso_months = []
    start_date = (date.today() - 3 * timedelta(365)).replace(
        day=1)  # Last three years
    end_date = date.today().replace(day=1)
    consultants_count = {}
    subcontractors_count = {}

    consultants = Consultant.objects.filter(subcontractor=False,
                                            productive=True)
    subcontractors = Consultant.objects.filter(subcontractor=True,
                                               productive=True)

    subsidiary = get_subsidiary_from_session(request)
    if subsidiary:
        subsidiaries = [
            subsidiary,
        ]
        consultants = consultants.filter(company=subsidiary)
        subcontractors = subcontractors.filter(
            timesheet__mission__subsidiary=subsidiary)
    else:
        subsidiaries = Subsidiary.objects.filter(
            mission__nature="PROD").distinct()
        subsidiaries = subsidiaries.annotate(
            Count("mission__timesheet__consultant"))
        subsidiaries = subsidiaries.filter(
            mission__timesheet__consultant__count__gt=0)

    for subsidiary in subsidiaries:
        consultants_count[subsidiary] = []
        subcontractors_count[subsidiary] = []

    month = start_date
    while month < end_date:
        next_month = nextMonth(month)
        iso_months.append(month.isoformat())
        for subsidiary in subsidiaries:
            consultants_count[subsidiary].append(
                consultants.filter(
                    company=subsidiary,
                    timesheet__mission__nature__in=("PROD", "NONPROD"),
                    timesheet__working_date__gte=month,
                    timesheet__working_date__lt=next_month).distinct().count())
            subcontractors_count[subsidiary].append(
                subcontractors.filter(
                    timesheet__working_date__gte=month,
                    timesheet__working_date__lt=next_month,
                    timesheet__mission__subsidiary=subsidiary,
                    timesheet__mission__nature="PROD").distinct().count())

        month = next_month

    if not iso_months or set(consultants_count) == {None}:
        return HttpResponse('')

    graph_data.append(["x"] + iso_months)
    for subsidiary in subsidiaries:
        graph_data.append([_("consultants %s" % subsidiary)] +
                          consultants_count[subsidiary])
        graph_data.append([_("subcontractors %s" % subsidiary)] +
                          subcontractors_count[subsidiary])

    return render(
        request, "people/graph_people_count.html", {
            "graph_data": json.dumps(graph_data),
            "series_colors": COLORS,
            "subsidiaries": subsidiaries,
            "user": request.user
        })
Beispiel #46
0
 def done_work(self):
     """Compute done work according to timesheet for this mission
     Result is cached for few seconds
     @return: (done work in days, done work in euros)"""
     return self.done_work_period(None, nextMonth(date.today()))