import math
from datetime import timedelta
from django.db.models import Avg, Count, Q
from django.utils.timezone import now
import pandas as pd
import plotly.express as px
from dashboards.component import BasicTable, Chart, Gauge, Map, Text
from dashboards.component.chart import ChartSerializer
from dashboards.component.gauge.gauge import GaugeValue
from dashboards.component.layout import Card, ComponentLayout
from dashboards.component.table import SerializedTable
from dashboards.component.text import StatData
from dashboards.dashboard import Dashboard
from dashboards.registry import registry
from django_countries.fields import countries
from example.employees.models import Employee
def get_number_of_employees(**kwargs):
return StatData(
text="Number of employees",
sub_text=Employee.objects.filter(leave_date__isnull=True).count(),
)
def get_employees_breakdown(**kwargs):
number_of_employees = Employee.objects.filter(leave_date__isnull=True).count()
avg_satisfaction = Employee.objects.filter(leave_date__isnull=True).aggregate(
avg=Avg("satisfaction")
)["avg"]
new_count = Employee.objects.filter(
leave_date__isnull=True, joined_date__gt=now() - timedelta(days=15)
).count()
vacancies = Employee.objects.filter(
leave_date__gt=now() - timedelta(days=30)
).count()
content = "<h2>Employee Breakdown</h2>"
content += "<div style='text-align: left'>"
content += f"<div><strong>Total employees</strong>: {number_of_employees}</div>"
content += f"<div><strong>Vacancies</strong>: {vacancies}</div>"
content += f"<div><strong>Under offer</strong>: {new_count}</div>"
content += f"<div><strong>Job Satisfaction</strong>: {avg_satisfaction:.2f}%</div>"
content += f"</div>"
return content
def get_new_employees_table(**kwargs):
year = now().year
month = now().month
qs = Employee.objects.aggregate(
employees_this_month=Count("pk", filter=Q(leave_date__isnull=True)),
employees_last_month=Count(
"pk",
filter=Q(
joined_date__lt=now() - timedelta(days=30), leave_date__isnull=True
),
),
leavers_this_month=Count(
"pk",
filter=Q(
leave_date__year=year, leave_date__month=month, leave_date__isnull=False
),
),
leavers_last_month=Count(
"pk",
filter=Q(
leave_date__year=year,
leave_date__month=month - 1,
leave_date__isnull=False,
),
),
)
data = [
{
"row": "No. Employees",
"current": qs["employees_this_month"],
"last": qs["employees_last_month"],
},
{
"row": "Vacancies",
"current": qs["leavers_this_month"],
"last": qs["leavers_last_month"],
},
]
return SerializedTable(
columns={"row": "", "current": "This month", "last": "Last month"},
data=data,
columns_datatables=[],
order=[],
)
def get_new_hires(**kwargs):
new_cutoff = now() - timedelta(days=30)
new_count = Employee.objects.filter(
leave_date__isnull=True, joined_date__gt=new_cutoff
).count()
return StatData(
text=f"{new_count} new starters",
sub_text=f"{Employee.objects.filter(leave_date__gt=new_cutoff).count()} terminations",
)
def get_employee_satisfaction(**kwargs):
avg_satisfaction = Employee.objects.filter(leave_date__isnull=True).aggregate(
avg=Avg("satisfaction")
)["avg"]
return StatData(text="Job satisfaction", sub_text=f"{int(avg_satisfaction)}%")
def get_gender_distribution_ratio(**kwargs):
employees = Employee.objects.filter(leave_date__isnull=True)
male_count = employees.filter(gender=Employee.GenderChoices.male).count()
female_count = employees.filter(gender=Employee.GenderChoices.female).count()
value = math.floor(100 * male_count / (male_count + female_count))
return GaugeValue(
value=value,
max=100,
value_text=f"{value}%:{100-value}%",
sub_text="Male to Female",
)
class EmployeeCountChartSerializer(ChartSerializer):
class Meta:
fields = ["department", "count"]
model = Employee
title = "Employee Department Count"
height = 600
def get_queryset(self, *args, **kwargs):
return (
Employee.objects.filter(leave_date__isnull=True)
.values("department")
.annotate(count=Count("pk"))
.order_by("-count")
)
def to_fig(self, df):
fig = px.bar(df, x="department", y="count", color="department")
fig.update_layout(
xaxis_title="Department",
yaxis_title=None,
)
return fig
class HiresAndTerminationsChartSerializer(ChartSerializer):
class Meta:
fields = ["joined_date", "department", "leave_date"]
model = Employee
title = "Hires and Terminations"
height = 600
def to_fig(self, df):
df.joined_date = df.joined_date.dt.to_period("M").dt.to_timestamp()
df.leave_date = df.leave_date.dt.to_period("M").dt.to_timestamp()
joined_group = (
df.groupby(["joined_date", "department"]).size().reset_index(name="counts")
)
leave_group = (
df[df.leave_date.notnull()]
.groupby(["leave_date"])
.size()
.reset_index(name="counts")
)
leave_group["department"] = "Leavers"
leave_group = leave_group.rename(columns={"leave_date": "joined_date"})
merged_df = pd.concat(
[joined_group, leave_group], ignore_index=True, sort=False
)
fig = px.line(
merged_df,
x="joined_date",
y="counts",
color="department",
title="Hires",
markers=True,
hover_data={"joined_date": False},
)
fig.update_layout(hovermode="x unified", yaxis_title="Month")
fig.update_layout(
xaxis=dict(
rangeselector=dict(
buttons=list(
[
dict(
count=1, label="1m", step="month", stepmode="backward"
),
dict(
count=6, label="6m", step="month", stepmode="backward"
),
dict(count=1, label="YTD", step="year", stepmode="todate"),
dict(count=1, label="1y", step="year", stepmode="backward"),
dict(step="all"),
]
)
),
type="date",
)
)
fig.update_traces(
patch={"line": {"color": "black", "width": 4, "dash": "dot"}},
selector={"legendgroup": "Leavers"},
hovertemplate=None,
)
return fig
class JobVacanciesChartSerializer(ChartSerializer):
class Meta:
fields = ["department", "count"]
model = Employee
title = "Job Vacancies"
def get_queryset(self, *args, **kwargs):
return (
Employee.objects.filter(leave_date__isnull=False)
.values("department")
.annotate(count=Count("pk"))
.order_by("-count")
)
def to_fig(self, df):
fig = px.pie(
df,
values="count",
names="department",
hole=0.6,
color_discrete_sequence=px.colors.sequential.RdBu,
)
return fig
class EmployeesByCountryMapSerializer(ChartSerializer):
class Meta:
fields = ["country", "count"]
model = Employee
title = "Employee numbers per location"
def get_queryset(self, *args, **kwargs):
return (
Employee.objects.filter(leave_date__isnull=False)
.values("country")
.annotate(count=Count("pk"))
.order_by("-count")
)[0:100]
def to_fig(self, df):
countries_dict = dict(countries)
df["country_name"] = df["country"].map(countries_dict)
fig = px.choropleth(
df, locationmode="country names", locations="country_name", color="count"
)
return fig
class EmployeesDashboard(Dashboard):
employees_breakdown = Text(value=get_employees_breakdown, mark_safe=True)
gender = Gauge(value=get_gender_distribution_ratio, title="Gender diversity ratio")
employees_by_department = Chart(value=EmployeeCountChartSerializer)
hires_and_terminations = Chart(value=HiresAndTerminationsChartSerializer)
job_vacancies = Chart(value=JobVacanciesChartSerializer)
employees_by_country = Map(value=EmployeesByCountryMapSerializer)
new_employees_table = BasicTable(value=get_new_employees_table)
class Meta:
name = "Employees"
class Layout(Dashboard.Layout):
components = ComponentLayout(
Card("employees_breakdown", "gender", grid_css_classes="span-3"),
Card("employees_by_country", grid_css_classes="span-9"),
Card("employees_by_department", grid_css_classes="span-6"),
Card("hires_and_terminations", grid_css_classes="span-6"),
Card(
"new_employees_table",
heading="Number of Employment",
grid_css_classes="span-6",
),
Card("job_vacancies", grid_css_classes="span-6"),
)
registry.register(EmployeesDashboard)