def campaign_planning(campaign, *, external_view=False):
projects = Project.objects.filter(campaign=campaign)
projects_ids = ([project.id for project in projects],)
with connections["default"].cursor() as cursor:
cursor.execute(
"""\
WITH sq AS (
SELECT unnest(weeks) AS week
FROM planning_plannedwork
WHERE project_id = ANY %s
UNION ALL
SELECT date_trunc('week', date)::date
FROM planning_milestone
WHERE project_id = ANY %s
)
SELECT MIN(week), MAX(week) FROM sq
""",
[projects_ids, projects_ids],
)
result = list(cursor)[0]
if result[0]:
result = (min(result[0], monday() - dt.timedelta(days=14)), result[1])
weeks = list(
islice(
recurring(result[0], "weekly"),
2 + (result[1] - result[0]).days // 7,
)
)
else:
weeks = list(islice(recurring(monday() - dt.timedelta(days=14), "weekly"), 80))
planning = Planning(weeks=weeks, projects=projects, external_view=external_view)
planning.add_planned_work_and_milestones(
PlannedWork.objects.filter(project__campaign=campaign).select_related(
"service_type"
),
Milestone.objects.filter(project__campaign=campaign),
ExternalWork.objects.filter(project__campaign=campaign).select_related(
"service_type"
),
)
if not external_view:
planning.add_worked_hours(LoggedHours.objects.all())
planning.add_absences(Absence.objects.all())
planning.add_public_holidays()
planning.add_milestones(Milestone.objects.all())
return planning.report()
def test_recurring(self):
"""The recurring() utilty returns expected values"""
self.assertEqual(
list(islice(recurring(dt.date(2016, 2, 29), "yearly"), 5)),
[
dt.date(2016, 2, 29),
dt.date(2017, 3, 1),
dt.date(2018, 3, 1),
dt.date(2019, 3, 1),
dt.date(2020, 2, 29),
],
)
self.assertEqual(
list(islice(recurring(dt.date(2016, 1, 31), "quarterly"), 5)),
[
dt.date(2016, 1, 31),
dt.date(2016, 5, 1),
dt.date(2016, 7, 31),
dt.date(2016, 10, 31),
dt.date(2017, 1, 31),
],
)
self.assertEqual(
list(islice(recurring(dt.date(2016, 1, 31), "monthly"), 5)),
[
dt.date(2016, 1, 31),
dt.date(2016, 3, 1),
dt.date(2016, 3, 31),
dt.date(2016, 5, 1),
dt.date(2016, 5, 31),
],
)
self.assertEqual(
list(islice(recurring(dt.date(2016, 1, 1), "weekly"), 5)),
[
dt.date(2016, 1, 1),
dt.date(2016, 1, 8),
dt.date(2016, 1, 15),
dt.date(2016, 1, 22),
dt.date(2016, 1, 29),
],
)
with self.assertRaises(ValueError):
list(islice(recurring(dt.date(2016, 1, 1), "unknown"), 5))
def create_accruals_for_last_month():
today = dt.date.today()
start = today.replace(year=today.year - 2, day=1)
for day in recurring(start, "monthly"):
if day > today:
break
Accruals.objects.for_cutoff_date(day - dt.timedelta(days=1))
def user_planning(user):
weeks = list(
islice(recurring(monday() - dt.timedelta(days=14), "weekly"), 80))
planning = Planning(weeks=weeks, users=[user])
planning.add_planned_work(user.planned_work.all())
planning.add_planning_requests(user.received_planning_requests.all())
planning.add_worked_hours(user.loggedhours.all())
planning.add_absences(user.absences.all())
return planning.report()
def project_planning(project):
with connections["default"].cursor() as cursor:
cursor.execute(
"""\
WITH sq AS (
SELECT unnest(weeks) AS week
FROM planning_plannedwork
WHERE project_id=%s
UNION ALL
SELECT earliest_start_on AS week
FROM planning_planningrequest
WHERE project_id=%s
UNION ALL
SELECT completion_requested_on - 7 AS week
FROM planning_planningrequest
WHERE project_id=%s
)
SELECT MIN(week), MAX(week) FROM sq
""",
[project.id, project.id, project.id],
)
result = list(cursor)[0]
if result[0]:
result = (min(result[0], monday() - dt.timedelta(days=14)), result[1])
weeks = list(
islice(
recurring(result[0], "weekly"),
2 + (result[1] - result[0]).days // 7,
))
else:
weeks = list(
islice(recurring(monday() - dt.timedelta(days=14), "weekly"), 80))
planning = Planning(weeks=weeks)
planning.add_planned_work(project.planned_work.all())
planning.add_planning_requests(project.planning_requests.all())
planning.add_worked_hours(LoggedHours.objects.all())
planning.add_absences(Absence.objects.all())
return planning.report()
def offers_hourly_rate(date_range):
margin_m = defaultdict(lambda: Z2)
hours_m = defaultdict(lambda: Z1)
margin_y = defaultdict(lambda: Z2)
hours_y = defaultdict(lambda: Z1)
def month(day):
return (day.year, day.month)
for offer in (Offer.objects.accepted().filter(
closed_on__range=date_range).prefetch_related("services")):
margin = offer.total_excl_tax - sum(
(service.third_party_costs
for service in offer.services.all() if service.third_party_costs),
Z2,
)
hours = sum(
(service.service_hours for service in offer.services.all()),
Z1,
)
margin_m[month(offer.closed_on)] += margin
hours_m[month(offer.closed_on)] += hours
margin_y[offer.closed_on.year] += margin
hours_y[offer.closed_on.year] += hours
months = [
month(m) for m in takewhile(
lambda day: day < date_range[1],
recurring(date_range[0], "monthly"),
)
]
return {
"by_month": {
month: {
"gross_margin":
margin_m[month],
"hours":
hours_m[month],
"hourly_rate":
margin_m[month] / hours_m[month] if hours_m[month] else Z2,
}
for month in months
},
"by_year": {
year: {
"gross_margin":
margin_y[year],
"hours":
hours_y[year],
"hourly_rate":
margin_y[year] / hours_y[year] if hours_y[year] else Z2,
}
for year in range(date_range[0].year, date_range[1].year + 1)
},
}
def team_planning(team):
weeks = list(
islice(recurring(monday() - dt.timedelta(days=14), "weekly"), 80))
planning = Planning(weeks=weeks, users=list(team.members.active()))
planning.add_planned_work(PlannedWork.objects.filter(user__teams=team))
planning.add_planning_requests(
PlanningRequest.objects.filter(
Q(receivers__teams=team) | Q(planned_work__user__teams=team)))
planning.add_worked_hours(
LoggedHours.objects.filter(rendered_by__teams=team))
planning.add_absences(Absence.objects.filter(user__teams=team))
return planning.report()
def team_planning(team, date_range):
start, end = date_range
weeks = list(takewhile(lambda x: x <= end, recurring(monday(start), "weekly")))
planning = Planning(weeks=weeks, users=list(team.members.active()))
planning.add_planned_work_and_milestones(
PlannedWork.objects.filter(user__teams=team).select_related("service_type"),
Milestone.objects.filter(project__planned_work__user__teams=team),
)
planning.add_worked_hours(LoggedHours.objects.filter(rendered_by__teams=team))
planning.add_absences(Absence.objects.filter(user__teams=team))
planning.add_public_holidays()
planning.add_milestones(Milestone.objects.all())
return planning.report()
def user_planning(user, date_range):
start, end = date_range
weeks = list(takewhile(lambda x: x <= end, recurring(monday(start), "weekly")))
planning = Planning(weeks=weeks, users=[user])
planning.add_planned_work_and_milestones(
user.planned_work.select_related("service_type"),
Milestone.objects.filter(
Q(project__planned_work__user=user) | Q(project__owned_by=user)
),
)
planning.add_worked_hours(user.loggedhours.all())
planning.add_absences(user.absences.all())
planning.add_public_holidays()
planning.add_milestones(Milestone.objects.all())
return planning.report()
def gross_margin_by_month(date_range):
gross = gross_profit_by_month(date_range)
third = third_party_costs_by_month(date_range)
accruals = accruals_by_month(date_range)
fte = full_time_equivalents_by_month()
pi = projected_invoices()
first_of_months = list(
takewhile(
lambda day: day < date_range[1],
recurring(date_range[0], "monthly"),
))
profit = []
for day in first_of_months:
month = (day.year, day.month)
row = {
"month": month,
"key": "%s-%s" % month,
"date": day,
"gross_profit": gross[month],
"third_party_costs": third[month],
"accruals": accruals.get(month) or {
"accrual": None,
"delta": Z2
},
"fte": fte.get(day, Z2),
"projected_invoices": pi["monthly_overall"].get(month),
}
if not any((
row["gross_profit"],
row["third_party_costs"],
row["accruals"]["delta"],
row["projected_invoices"],
)):
continue
row["gross_margin"] = (row["gross_profit"] + row["third_party_costs"] +
row["accruals"]["delta"])
row["margin_per_fte"] = row["gross_margin"] / row["fte"] if row[
"fte"] else None
profit.append(row)
return profit
def create_invoices(self):
invoices = []
days = recurring(
max(filter(None, (self.next_period_starts_on, self.starts_on))),
self.periodicity,
)
generate_until = min(
filter(None, (in_days(-self.create_invoice_on_day), self.ends_on)))
this_period = next(days)
while True:
if this_period > generate_until:
break
next_period = next(days)
invoices.append(
self.create_single_invoice(
period_starts_on=this_period,
period_ends_on=next_period - dt.timedelta(days=1),
))
self.next_period_starts_on = next_period
this_period = next_period
self.save()
return invoices
class ExternalWorkForm(ModelForm):
class Meta:
model = ExternalWork
fields = (
"provided_by",
"title",
"service_type",
"notes",
"milestone",
)
widgets = {
"provided_by": Autocomplete(model=Organization),
"notes": Textarea,
}
def __init__(self, *args, **kwargs):
initial = kwargs.setdefault("initial", {})
request = kwargs["request"]
self.project = kwargs.pop("project", None)
if not self.project: # Updating
self.project = kwargs["instance"].project
else:
initial["provided_by"] = self.project.customer_id
if service_id := request.GET.get("service"):
try:
service = self.project.services.get(pk=service_id)
except (self.project.services.model.DoesNotExist, TypeError, ValueError):
pass
else:
initial.update(
{
"title": f"{self.project.title}: {service.title}",
"notes": service.description,
"planned_hours": service.service_hours,
}
)
# if pk := request.GET.get("copy"):
# try:
# pw = ExternalWork.objects.get(pk=pk)
# except (ExternalWork.DoesNotExist, TypeError, ValueError):
# pass
# else:
# initial.update(
# {
# "project": pw.project_id,
# "offer": pw.offer_id,
# "title": pw.title,
# "notes": pw.notes,
# "planned_hours": pw.planned_hours,
# "weeks": pw.weeks,
# "is_provisional": pw.is_provisional,
# "service_type": pw.service_type_id,
# "milestone": pw.milestone_id,
# }
# )
super().__init__(*args, **kwargs)
self.instance.project = self.project
self.fields["milestone"].queryset = self.project.milestones.all()
# self.fields["service_type"].required = True
date_from_options = [
monday(),
self.instance.weeks and min(self.instance.weeks),
initial.get("weeks") and min(initial["weeks"]),
]
date_from = min(filter(None, date_from_options)) - dt.timedelta(days=21)
self.fields["weeks"] = forms.TypedMultipleChoiceField(
label=capfirst(_("weeks")),
choices=[
(
day,
"KW{} ({} - {})".format(
local_date_format(day, fmt="W"),
local_date_format(day),
local_date_format(day + dt.timedelta(days=6)),
),
)
for day in islice(recurring(date_from, "weekly"), 80)
],
widget=forms.SelectMultiple(attrs={"size": 20}),
initial=self.instance.weeks or [monday()],
coerce=parse_date,
)
initial.get("weeks") and min(initial["weeks"]),
]
date_from = min(filter(None, date_from_options)) - dt.timedelta(days=21)
self.fields["weeks"] = forms.TypedMultipleChoiceField(
label=capfirst(_("weeks")),
choices=[
(
day,
"KW{} ({} - {})".format(
local_date_format(day, fmt="W"),
local_date_format(day),
local_date_format(day + dt.timedelta(days=6)),
),
)
for day in islice(recurring(date_from, "weekly"), 80)
],
widget=forms.SelectMultiple(attrs={"size": 20}),
initial=self.instance.weeks or [monday()],
coerce=parse_date,
)
def clean(self):
data = super().clean()
if (weeks := data.get("weeks")) and (milestone := data.get("milestone")):
if after := [week for week in weeks if week >= monday(milestone.date)]:
self.add_warning(
_(
"The milestone is scheduled on %(date)s, but work is"
" planned in the same or following week(s): %(weeks)s"
def weeks(self):
return list(
takewhile(
lambda x: x < self.completion_requested_on,
recurring(self.earliest_start_on, "weekly"),
))