Employee Breakdown

Total employees: 8069
Vacancies: 573
Under offer: 370
Job Satisfaction: 65.47%

Gender diversity ratio

57%:43% Male to Female

Number of Employment

This month Last month
No. Employees 8069 7390
Vacancies 408 386
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)