def average_employment_duration():
stats = {}
stats["current_users"] = query(
"""
with date_from_until as (
select
user_id,
date_from,
case
when date_until='9999-12-31' then current_date
else date_until
end as date_until
from awt_employment e
left join accounts_user u on e.user_id=u.id
where u.is_active=TRUE
),
durations as (
select user_id, sum(date_until - date_from) as duration
from date_from_until
group by user_id
)
select avg(duration)/365.24 from durations
""",
[],
)[0][0]
stats["all_users"] = query(
"""
with date_from_until as (
select
user_id,
date_from,
case
when date_until='9999-12-31' then current_date
else date_until
end as date_until
from awt_employment
),
durations as (
select user_id, sum(date_until - date_from) as duration
from date_from_until
group by user_id
)
select avg(duration)/365.24 from durations
""",
[],
)[0][0]
return stats
def work_anniversaries():
anniversaries = []
today = dt.date.today()
for name, start in query(
"""
WITH
employments AS (
SELECT
user_id,
date_from,
date_until
FROM awt_employment
WHERE percentage>0
ORDER BY date_from
),
ends AS (
SELECT
user_id,
array_agg(date_until + 1) AS ends
FROM employments
GROUP BY user_id
),
earliest AS (
SELECT
user_id,
-- The latest start which ...
MAX(date_from) AS start
FROM employments
LEFT JOIN ends USING (user_id)
-- ... has no ending employment preceeding it (one day difference)
WHERE date_from <> ALL(ends.ends)
GROUP BY user_id
)
SELECT _full_name, earliest.start
FROM accounts_user u
LEFT JOIN earliest ON u.id=earliest.user_id
WHERE earliest.start IS NOT NULL AND u.is_active=TRUE
""",
[],
):
this_year = start.replace(year=today.year)
anniversaries.append({
"name": name,
"start": start,
"this_year": this_year,
"anniversary": this_year.year - start.year,
"already": this_year > today,
})
return sorted(anniversaries,
key=lambda row: (row["this_year"], row["name"]))
def capacity(self):
by_user = defaultdict(dict)
total = defaultdict(int)
user_ids = ([user.id for user in self.users]
if self.users else list(self._user_ids))
for week, user, capacity in query(
"""
select week, user_id, percentage * 5 * planning_hours_per_day / 100 as capacity
from generate_series(%s::date, %s::date, '7 days') as week
left outer join lateral (
select user_id, date_from, date_until, percentage, planning_hours_per_day
from awt_employment
left join accounts_user on awt_employment.user_id=accounts_user.id
where user_id = any (%s)
) as employment
on employment.date_from <= week and employment.date_until > week
where percentage is not NULL -- NULL produced by outer join
""",
[min(self.weeks), max(self.weeks), user_ids],
):
by_user[user][week.date()] = capacity
total[week.date()] += capacity
users = self.users or list(User.objects.filter(id__in=by_user))
return {
"total": [total.get(week, 0) for week in self.weeks],
"by_user": [{
"user": {
"name": user.get_full_name(),
"url": user.urls["planning"],
},
"capacity":
[by_user[user.id].get(week, 0) for week in self.weeks],
} for user in sorted(users)],
}
def planning_vs_logbook(date_range, *, users):
planned = defaultdict(dict)
logged = defaultdict(dict)
user_ids = [user.id for user in users]
seen_weeks = set()
for customer_id, week, planned_hours in query(
"""
with
planned_per_week as (
select
p.customer_id,
unnest(weeks) as week,
planned_hours / array_length(weeks, 1) / 5 as hours
from planning_plannedwork pw
left join projects_project p on pw.project_id = p.id
where user_id = any(%s)
),
weeks as (
select
date_trunc('week', day) as week,
count(*) as days
from generate_series(%s::date, %s::date, '1 day') as day
where extract(dow from day) between 1 and 5
group by week
)
select
customer_id,
weeks.week,
sum(hours * weeks.days)
from
planned_per_week, weeks
where
planned_per_week.week = weeks.week
group by customer_id, weeks.week
""",
[user_ids, *date_range],
):
seen_weeks.add(week)
planned[customer_id][week] = planned_hours
for customer_id, week, worked_hours in query(
"""
select
p.customer_id,
date_trunc('week', rendered_on) as week,
sum(hours) as hours
from logbook_loggedhours lh
left join projects_service ps on lh.service_id = ps.id
left join projects_project p on ps.project_id = p.id
where
rendered_by_id = any(%s)
and rendered_on between %s::date and %s::date
group by customer_id, week
""",
[user_ids, *date_range],
):
seen_weeks.add(week)
logged[customer_id][week] = worked_hours
customers = {
customer.id: customer
for customer in Organization.objects.filter(
id__in=planned.keys() | logged.keys()
)
}
weeks = sorted(seen_weeks)
def _customer(planned, logged):
ret = [
{
"customer": customers[customer_id],
"per_week": [
{
"week": week,
"planned": planned[customer_id].get(week),
"logged": logged[customer_id].get(week),
}
for week in weeks
],
"planned": sum(planned[customer_id].values(), Z1),
"logged": sum(logged[customer_id].values(), Z1),
}
for customer_id in planned.keys() | logged.keys()
]
return sorted(ret, key=lambda row: (-row["planned"], -row["logged"]))
ret = {
"per_customer": _customer(planned, logged),
"weeks": weeks,
}
ret["planned"] = sum((c["planned"] for c in ret["per_customer"]), Z1)
ret["logged"] = sum((c["logged"] for c in ret["per_customer"]), Z1)
return ret
def capacity(self):
by_user = defaultdict(dict)
total = defaultdict(int)
user_ids = (
[user.id for user in self.users] if self.users else list(self._user_ids)
)
for week, user, capacity in query(
"""
select
week,
user_id,
coalesce(percentage, 0) * 5 * coalesce(planning_hours_per_day, 0) / 100
- coalesce(pw_hours, 0)
- coalesce(abs_hours, 0)
- coalesce(ph_days, 0)
* coalesce(planning_hours_per_day, 0)
* coalesce(percentage, 0) / 100
as capacity
--
-- Determine the employment percentage and planning_hours_per_day
-- for each user and week
--
from generate_series(%s::date, %s::date, '7 days') as week
left outer join lateral (
select
user_id,
date_from,
date_until,
percentage,
planning_hours_per_day
from awt_employment
left join accounts_user on awt_employment.user_id=accounts_user.id
where user_id = any (%s)
) as employment
on employment.date_from <= week and employment.date_until > week
--
-- Aggregate planned hours per week, distributing equally the planned_hours
-- value over all weeks in the planned work record
--
left outer join lateral (
select
user_id as pw_user_id,
sum(planned_hours / cardinality(weeks)) as pw_hours,
unnest(weeks) as pw_week
from planning_plannedwork
where user_id = any (%s)
group by pw_user_id, pw_week
) as planned
on week=pw_week and user_id=pw_user_id
left outer join lateral(
--
-- Generate rows of absences (user_id, days, planning_hours, weeks::date[])
--
with sq as (
select
user_id as abs_user_id,
days,
planning_hours_per_day,
(
select array_agg(w::date) from generate_series(
date_trunc('week', starts_on),
date_trunc('week', ends_on),
'7 days'
) as w
) as weeks
from awt_absence
where user_id = any(%s)
and user_id=employment.user_id
and employment.date_from <= date_trunc('week', week)
and employment.date_until > date_trunc('week', week)
)
--
-- Calculate the planning hours for each absence and distribute the hours
-- over all weeks in which the absence takes place
--
select
abs_user_id,
sum(days * planning_hours_per_day / cardinality(weeks)) as abs_hours,
unnest(weeks) as abs_week
from sq
group by abs_user_id, abs_week
) as absences
on week=abs_week and user_id=abs_user_id
left outer join lateral(
select
date_trunc('week', date) as ph_week,
sum(fraction) as ph_days
from planning_publicholiday
where extract(dow from date) between 1 and 6
group by ph_week
) as public_holidays
on week=ph_week
""",
[min(self.weeks), max(self.weeks), user_ids, user_ids, user_ids],
):
by_user[user][week.date()] = capacity
total[week.date()] += capacity
users = self.users or list(User.objects.filter(id__in=by_user))
return {
"total": [total.get(week, 0) for week in self.weeks],
"by_user": [
{
"user": {
"name": user.get_full_name(),
"url": user.urls["planning"],
},
"capacity": [by_user[user.id].get(week, 0) for week in self.weeks],
}
for user in sorted(users)
],
}
def add_public_holidays(self):
ud = {user.id: user for user in User.objects.filter(id__in=self._user_ids)}
for (
id,
date,
name,
user_id,
planning_hours_per_day,
fraction,
percentage,
) in query(
"""
select
ph.id,
ph.date,
ph.name,
user_id,
planning_hours_per_day,
fraction,
percentage
from planning_publicholiday ph
left outer join lateral (
select
user_id,
date_from,
date_until,
percentage,
planning_hours_per_day
from awt_employment
left join accounts_user on awt_employment.user_id=accounts_user.id
where user_id = any (%s)
) as employment
on employment.date_from <= ph.date and employment.date_until > ph.date
where ph.date between %s and %s and user_id is not null
order by ph.date
""",
[list(ud.keys()), min(self.weeks), max(self.weeks)],
):
# Skip weekends
if date.weekday() >= 5:
continue
week = monday(date)
idx = self.weeks.index(week)
user = ud[user_id]
ph_hours = (
(planning_hours_per_day or 0)
* (fraction or 0)
* (percentage or 0)
/ 100
)
detail = " × ".join(
(
f"{hours(planning_hours_per_day)}/d",
f"{percentage}%",
f"{fraction}d",
)
)
self._absences[user][idx].append(
(
ph_hours,
f"{name} ({detail} = {hours(ph_hours)})",
reverse("planning_publicholiday_detail", kwargs={"pk": id}),
)
)
self._by_week[week] += ph_hours
def logged_hours(user):
stats = {}
from_ = monday(in_days(-180))
hours_per_week = {}
for week, type, hours in query(
"""
WITH sq AS (
SELECT
date_trunc('week', rendered_on) AS week,
project.type AS type,
SUM(hours) AS hours
FROM logbook_loggedhours hours
LEFT JOIN projects_service service ON hours.service_id=service.id
LEFT JOIN projects_project project ON service.project_id=project.id
WHERE rendered_by_id=%s AND rendered_on>=%s
GROUP BY week, project.type
)
SELECT series.week, sq.type, COALESCE(sq.hours, 0)
FROM generate_series(%s, %s, '7 days') AS series(week)
LEFT OUTER JOIN sq ON series.week=sq.week
ORDER BY series.week
""",
[user.id, from_, from_,
monday() + dt.timedelta(days=6)],
):
if week in hours_per_week:
hours_per_week[week]["hours"] += hours
hours_per_week[week]["by_type"][type] = hours
else:
hours_per_week[week] = {
"week": week,
"hours": hours,
"by_type": {
type: hours
},
}
stats["hours_per_week"] = [
row[1] for row in sorted(hours_per_week.items())
]
hours_per_customer = defaultdict(dict)
total_hours_per_customer = defaultdict(int)
for week, customer, hours in query(
"""
WITH sq AS (
SELECT
date_trunc('week', rendered_on) AS week,
customer.name AS customer,
SUM(hours) AS hours
FROM logbook_loggedhours hours
LEFT JOIN projects_service service ON hours.service_id=service.id
LEFT JOIN projects_project project ON service.project_id=project.id
LEFT JOIN contacts_organization customer ON project.customer_id=customer.id
WHERE rendered_by_id=%s AND rendered_on>=%s
GROUP BY week, customer.name
)
SELECT series.week, COALESCE(sq.customer, ''), COALESCE(sq.hours, 0)
FROM generate_series(%s, %s, '7 days') AS series(week)
LEFT OUTER JOIN sq ON series.week=sq.week
ORDER BY series.week
""",
[user.id, from_, from_,
monday() + dt.timedelta(days=6)],
):
customer = customer.split("\n")[0]
hours_per_customer[week][customer] = hours
total_hours_per_customer[customer] += hours
customers = [
row[0] for row in sorted(total_hours_per_customer.items(),
key=lambda row: row[1],
reverse=True)
][:10]
weeks = sorted(hours_per_customer.keys())
stats["hours_per_customer"] = {
"weeks":
weeks,
"by_customer": [{
"name":
customer,
"hours":
[hours_per_customer[week].get(customer, 0) for week in weeks],
} for customer in customers],
}
customers = set(customers)
stats["hours_per_customer"]["by_customer"].append({
"name":
_("All others"),
"hours": [
sum(
(hours for customer, hours in hours_per_customer[week].items()
if customer not in customers),
0,
) for week in weeks
],
})
dows = [
None,
_("Monday"),
_("Tuesday"),
_("Wednesday"),
_("Thursday"),
_("Friday"),
_("Saturday"),
_("Sunday"),
]
stats["rendered_hours_per_weekday"] = [{
"dow": int(dow),
"name": dows[int(dow)],
"hours": hours
} for dow, hours in query(
"""
WITH sq AS (
SELECT
(extract(isodow from rendered_on)::integer) as dow,
SUM(hours) AS hours
FROM logbook_loggedhours
WHERE rendered_by_id=%s AND rendered_on>=%s
GROUP BY dow
ORDER BY dow
)
SELECT series.dow, COALESCE(sq.hours, 0)
FROM generate_series(1, 7) AS series(dow)
LEFT OUTER JOIN sq ON series.dow=sq.dow
ORDER BY series.dow
""",
[user.id, from_],
)]
stats["created_hours_per_weekday"] = [{
"dow": int(dow),
"name": dows[int(dow)],
"hours": hours
} for dow, hours in query(
"""
WITH sq AS (
SELECT
(extract(isodow from timezone('CET', created_at))::integer) as dow,
SUM(hours) AS hours
FROM logbook_loggedhours
WHERE rendered_by_id=%s AND rendered_on>=%s
GROUP BY dow
ORDER BY dow
)
SELECT series.dow, COALESCE(sq.hours, 0)
FROM generate_series(1, 7) AS series(dow)
LEFT OUTER JOIN sq ON series.dow=sq.dow
ORDER BY series.dow
""",
[user.id, from_],
)]
return stats