clockify_automation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "929e3867",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import requests\n",
    "import datetime\n",
    "from datetime import date, datetime, timedelta\n",
    "import pdfrw\n",
    "import holidays\n",
    "import calendar\n",
    "from collections import Counter\n",
    "import tkinter as tk\n",
    "from tkinter import messagebox"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8b53a1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extracting Clockify workspace ID (for the summary report)\n",
    "url_base = 'https://api.clockify.me/api/v1/user'\n",
    "API_KEY = os.environ['Clockify_API_Key']\n",
    "\n",
    "headers = {'content-type': 'application/json', 'X-Api-Key': API_KEY}\n",
    "response = requests.get(url_base, headers=headers)\n",
    "json_response_base = response.json()\n",
    "json_response_base['id']\n",
    "\n",
    "workspace_id = json_response_base['activeWorkspace']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73a67c75",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Getting current day, month, and year\n",
    "today = date.today()\n",
    "current_day = today.day\n",
    "current_month = today.month\n",
    "current_year = today.year\n",
    "current_year_short = str(current_year)[-2:] # Last two digits of the year\n",
    "\n",
    "# Getting first and last day of the previous month (date)\n",
    "current_first = today.replace(day=1)\n",
    "prev_last = current_first - timedelta(days=1)\n",
    "prev_first = prev_last.replace(day=1)\n",
    "prev_first_str = str(prev_first) # First day of the previous month (date)\n",
    "prev_last_str = str(prev_last) # Last day of the previous month (date)\n",
    "\n",
    "# Getting previous month and year\n",
    "last_date = prev_first.strftime(\"%Y-%m\")\n",
    "split_date = last_date.split(\"-\")\n",
    "month_prev = split_date[1]\n",
    "month_prev_int = int(month_prev) # Previous month\n",
    "year_prev = split_date[0]\n",
    "year_prev_int = int(year_prev) # Year of the previous month\n",
    "year_prev_short = year_prev[-2:] # Last two digits of the year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba73c6ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to get the Clockify summary report for the previous month\n",
    "def get_summary_report():\n",
    "    url = f\"https://reports.api.clockify.me/v1/workspaces/\"+workspace_id+\"/reports/summary\"\n",
    "    headers = {'X-Api-Key': API_KEY}\n",
    "    data = {\n",
    "        \"dateRangeStart\": prev_first_str+\"T00:00:00\",\n",
    "        \"dateRangeEnd\": prev_last_str+\"T23:59:59\",\n",
    "        \"summaryFilter\": {\n",
    "            \"groups\": [\n",
    "             \"USER\",\n",
    "             \"DATE\"\n",
    "            ]\n",
    "        }\n",
    "        }\n",
    "\n",
    "    return requests.post(url, headers=headers, json=data).json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39e9fa28",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Getting the summary report\n",
    "summary_report = get_summary_report()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71b6197c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# JSON to dataframe\n",
    "dataframe = pd.json_normalize(summary_report, record_path=['groupOne', 'children'], meta=[['children', 'name']])\n",
    "\n",
    "# Rename columns\n",
    "dataframe.rename(columns={'name':'date', 'children.name':'name'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d305d9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a list of all dates in the last month\n",
    "date_list = [prev_last - timedelta(days=x) for x in range(prev_last.day)]\n",
    "\n",
    "# Create a dataframe with all the dates in the last month\n",
    "names = dataframe['name'].unique().tolist()\n",
    "df_dates = pd.DataFrame({'name':[], 'date':[]})\n",
    "\n",
    "# Create a dataframe with all the dates and names\n",
    "for name in names:\n",
    "    df_name = pd.DataFrame({'name':[name]*len(date_list), 'date':date_list})\n",
    "    df_dates = pd.concat([df_dates, df_name])\n",
    "\n",
    "# Convert the date column to datetime\n",
    "dataframe['date'] = pd.to_datetime(dataframe['date'])\n",
    "df_dates['date'] = pd.to_datetime(df_dates['date'])\n",
    "\n",
    "# Merge the two dataframes\n",
    "dataframe = pd.merge(df_dates, dataframe, on=['name', 'date'], how='left')\n",
    "\n",
    "# Fill in the missing values with NaN\n",
    "dataframe['duration'] = dataframe['duration'].replace(r'^\\s*$', np.nan, regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6471fecd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Calculate duration in hours (decimal form)\n",
    "dataframe['hours'] = dataframe['duration']/3600\n",
    "dataframe['hours'] = dataframe['hours'].apply(lambda x:round(x,2)) # Round to 2 decimal places\n",
    "\n",
    "# Calculate total duration per person\n",
    "total_hours = dataframe.groupby(['name'])['hours'].transform('sum')\n",
    "dataframe['total_hours'] = total_hours\n",
    "dataframe['total_hours'] = dataframe['total_hours'].round(2) # Round to 2 decimal places\n",
    "\n",
    "# Drop unnecessary columns\n",
    "dataframe = dataframe.drop(columns=['amounts', 'amount', '_id', 'duration'])\n",
    "\n",
    "# Create a dictionary with the total hours per person (before the changes are applied)\n",
    "total_hours_dict_before = dict(zip(dataframe['name'], dataframe['total_hours']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7607b54b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get holidays in the Netherlands\n",
    "holidaysNL = holidays.NL(years=year_prev_int)\n",
    "\n",
    "# Get the last day of the previous month\n",
    "last_day = calendar.monthrange(year_prev_int, month_prev_int)[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da5d3a60",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to adjust the hours worked on holidays and weekends\n",
    "def adjust_hours(df):\n",
    "    # Sort the dataframe by name and date\n",
    "    df = df.sort_values(by=['name', 'date']).reset_index(drop=True)\n",
    "\n",
    "    # Create a dictionary to store the hours worked on each day\n",
    "    hours_per_day = {}\n",
    "\n",
    "    # Loop through each row in the dataframe\n",
    "    for i, row in df.iterrows():\n",
    "        name = row['name']\n",
    "        date = row['date']\n",
    "        hours = row['hours']\n",
    "        date_dt = pd.to_datetime(date).date()\n",
    "        day = date_dt.day\n",
    "        \n",
    "        if (pd.isnull(df.loc[(df['name'] == name) & (df['date'] == date), 'hours']).all()):\n",
    "            # If the person didn't work on that day, skip to the next row\n",
    "            continue\n",
    "\n",
    "        elif ((date.weekday() < 5) and (date not in holidaysNL)):\n",
    "            # If it's not a weekday or holiday, add the working hours to the dictionary\n",
    "            if (date, name) in hours_per_day:\n",
    "                hours_per_day[(date, name)] += hours\n",
    "            else:    \n",
    "                hours_per_day[(date, name)] = hours\n",
    "\n",
    "        else:\n",
    "            # Calculate the next available business day\n",
    "            next_weekday = date\n",
    "            while ((next_weekday, name) in hours_per_day # If the date is already in the dictionary\n",
    "                or (next_weekday.weekday() >= 5) # If it's a weekend\n",
    "                or (next_weekday in holidaysNL) # If it's a holiday\n",
    "                or (pd.notnull(df.loc[(df['name'] == name) & (df['date'] == next_weekday), 'hours']).all())): # If the person already worked on that day\n",
    "                next_weekday += timedelta(days=1) # Add one day\n",
    "                \n",
    "                # If we've reached the end of the month, check backwards\n",
    "                if next_weekday.month != date.month:\n",
    "                    # Calculate the previous available business day\n",
    "                    previous_weekday = date\n",
    "                    while ((previous_weekday, name) in hours_per_day # If the date is already in the dictionary\n",
    "                        or (previous_weekday.weekday() >= 5) # If it's a weekend\n",
    "                        or (previous_weekday in holidaysNL) # If it's a holiday\n",
    "                        or (pd.notnull(df.loc[(df['name'] == name) & (df['date'] == previous_weekday), 'hours']).all())): # If the person already worked on that day\n",
    "                        previous_weekday -= timedelta(days=1) # Subtract one day\n",
    "\n",
    "                        # If we've reached the beginning of the month, get the next business day\n",
    "                        if previous_weekday.month != date.month:\n",
    "                            print(f\"No available weekday found for {name} on {date}...\")\n",
    "                            # Calculate the next business day\n",
    "                            next_day = date\n",
    "                            while (next_day.weekday() >= 5 or next_day in holidaysNL):\n",
    "                                next_day += timedelta(days=1)\n",
    "                            # If we've reached the end of the month, check backwards    \n",
    "                            if next_day.month != date.month:\n",
    "                                # Calculate the previous business day\n",
    "                                next_day = date\n",
    "                                while (next_day.weekday() >= 5 or next_day in holidaysNL):\n",
    "                                    next_day -= timedelta(days=1)\n",
    "                                # If we've reached the beginning of the month, keep the hours on the original date\n",
    "                                if next_day.month != date.month:\n",
    "                                    next_day = date\n",
    "                                    # Add the hours to the dictionary\n",
    "                                    print(f\"...keeping {hours} hours on {date} for {name}\")\n",
    "                                    if (next_day, name) in hours_per_day:\n",
    "                                        hours_per_day[(next_day, name)] += hours\n",
    "                                    else:\n",
    "                                        hours_per_day[(next_day, name)] = hours\n",
    "                                    break\n",
    "\n",
    "                                else:\n",
    "                                    # Add the hours to the dictionary\n",
    "                                    print(f\"...moving {hours} hours backward, from {date} to {next_day}, for {name}\")\n",
    "                                    if (next_day, name) in hours_per_day:\n",
    "                                        hours_per_day[(next_day, name)] += hours\n",
    "                                    else:\n",
    "                                        hours_per_day[(next_day, name)] = hours\n",
    "                                    break    \n",
    "                            else:\n",
    "                                # Add the hours to the dictionary\n",
    "                                print(f\"...moving {hours} hours forward, from {date} to {next_day}, for {name}\")\n",
    "                                if (next_day, name) in hours_per_day:\n",
    "                                    hours_per_day[(next_day, name)] += hours\n",
    "                                else:\n",
    "                                    hours_per_day[(next_day, name)] = hours\n",
    "                                break\n",
    "\n",
    "                    # If we've reached the beginning of the month, break out of the loop    \n",
    "                    if previous_weekday.month != date.month:\n",
    "                        break\n",
    "\n",
    "                    print(f\"Moving {hours} hours backward, from {date} to {previous_weekday}, for {name}\")\n",
    "                    \n",
    "                    # Add the hours to the dictionary\n",
    "                    if (previous_weekday, name) in hours_per_day:\n",
    "                        hours_per_day[(previous_weekday, name)] += hours\n",
    "                    else:    \n",
    "                        hours_per_day[(previous_weekday, name)] = hours\n",
    "                    break\n",
    "\n",
    "            # If we've reached the end of the month, break out of the loop\n",
    "            if next_weekday.month != date.month:\n",
    "                continue\n",
    "\n",
    "            else:\n",
    "                print(f\"Moving {hours} hours forward, from {date} to {next_weekday}, for {name}\")\n",
    "\n",
    "                # Add the hours to the dictionary\n",
    "                if (next_weekday, name) in hours_per_day:\n",
    "                    hours_per_day[(next_weekday, name)] += hours\n",
    "                else:    \n",
    "                    hours_per_day[(next_weekday, name)] = hours\n",
    "                \n",
    "    return hours_per_day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb4ffbb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Apply the function to the dataframem to get the dictionary with the adjusted hours\n",
    "dictionary = adjust_hours(dataframe)\n",
    "\n",
    "# Convert the dictionary to a dataframe\n",
    "adjusted_df = pd.DataFrame([(k[0].date(), k[1], v) for k, v in dictionary.items()], columns=['date', 'name', 'hours'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8611d950",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a dictionary with the total hours per person after changes\n",
    "adjusted_df['total_hours'] = adjusted_df.groupby(['name'])['hours'].transform('sum')\n",
    "adjusted_df['total_hours'] = adjusted_df['total_hours'].round(2) # Round to 2 decimal places\n",
    "total_hours_dict_after = dict(zip(adjusted_df['name'], adjusted_df['total_hours']))\n",
    "\n",
    "# Check if total hours before and after changes are equal\n",
    "if Counter(total_hours_dict_before) == Counter(total_hours_dict_after):\n",
    "    print(\"Total hours before and after changes are equal. Continuing with the code...\")\n",
    "else:\n",
    "    root = tk.Tk()\n",
    "    root.wm_attributes(\"-topmost\", 1)\n",
    "    root.withdraw()\n",
    "    tk.messagebox.showerror(\"Error\", \"Something went wrong. The total number of hours per person is incorrect.\", parent=root)\n",
    "    root.destroy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad8fa61c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sort the dataframe by name and date\n",
    "adjusted_df = adjusted_df.sort_values(['name', 'date'], ascending=[True, True])\n",
    "\n",
    "# Add the total hours to the dataframe\n",
    "adjusted_df['total_hours'] = adjusted_df['name'].map(total_hours_dict_before)\n",
    "\n",
    "# Get first name and last name, and initial\n",
    "sep_names = adjusted_df['name'].str.split(' ', expand=True)\n",
    "adjusted_df['first_name'] = sep_names[0]\n",
    "adjusted_df['last_name'] = sep_names[1]\n",
    "adjusted_df['last_name2'] = sep_names[2] # For people with two last names\n",
    "adjusted_df['initial'] = adjusted_df['first_name'].astype(str).str[0]\n",
    "\n",
    "# Extract the day from the date\n",
    "date_split = adjusted_df['date'].astype('str').str.split(\"-\", expand=True)\n",
    "adjusted_df['day'] = date_split[2]\n",
    "\n",
    "# Fill the empty cells with empty strings\n",
    "adjusted_df.fillna(\"\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d4e82a7",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Group dataframe by name\n",
    "dfs = [y for x, y in adjusted_df.groupby('name')]\n",
    "\n",
    "# Get the number of people\n",
    "people = len(dfs)\n",
    "\n",
    "# Loop through the number of people\n",
    "for x in range(people):\n",
    "    # Get the dataframe for each person\n",
    "    df = pd.DataFrame(dfs[x])\n",
    "    \n",
    "    # Create a dictionary with the day as key and the duration as value\n",
    "    dict_of_hours = dict(zip(df.day, df.hours))\n",
    "    \n",
    "    # Get first and last names and remove brackets and apostrophes\n",
    "    first_name = df['first_name'].unique()\n",
    "    first_name = str(first_name).replace(\"'\", \"\").replace(\"[\", \"\").replace(\"]\", \"\")\n",
    "    last_name = df['last_name'].unique()\n",
    "    last_name = str(last_name).replace(\"'\", \"\").replace(\"[\", \"\").replace(\"]\", \"\")\n",
    "    last_name2 = df['last_name2'].unique()\n",
    "    last_name2 = str(last_name2).replace(\"'\", \"\").replace(\"[\", \"\").replace(\"]\", \"\")\n",
    "\n",
    "    # Get the initial of the first name and remove brackets and apostrophes\n",
    "    initial = df['initial'].unique()\n",
    "    initial = str(initial).replace(\"'\", \"\").replace(\"[\", \"\").replace(\"]\", \"\")\n",
    "    \n",
    "    # Get the total hours worked and remove brackets and apostrophes\n",
    "    total_hours = df['total_hours'].unique()\n",
    "    total_hours = str(total_hours).replace(\"[\", \"\").replace(\"]\", \"\")\n",
    "    \n",
    "    # Function to add a space before the second last name (if the person has a second last name)\n",
    "    def last_name2_field(last_name2):\n",
    "        if last_name2 == \"\":\n",
    "            return last_name2\n",
    "        else:\n",
    "            return ' '+last_name2\n",
    "    \n",
    "    # Function to add an underscore before the second last name (if the person has a second last name)\n",
    "    def last_name2_title(last_name2):\n",
    "        if last_name2 == \"\":\n",
    "            return last_name2\n",
    "        else:\n",
    "            return '_'+last_name2        \n",
    "\n",
    "    # Get the second last name\n",
    "    last_name2_field = last_name2_field(last_name2) # For the field in the PDF file\n",
    "    last_name2_title = last_name2_title(last_name2) # For the title of the PDF file\n",
    "\n",
    "    # Define the supervisor signing the form\n",
    "    supervisor = 'Hannes Datta'\n",
    "\n",
    "    # Define the Faculty / Office\n",
    "    faculty = 'Tilburg School of Economics and Management'\n",
    "\n",
    "    # Define the Department\n",
    "    department = 'Marketing (Tilburg Science Hub)'\n",
    "\n",
    "    # Create a dictionary with the values to fill in the PDF file\n",
    "    dict_values = {'Naam en voorletters': last_name+last_name2_field+' '+initial, # Surname and initials\n",
    "                    'Faculteit  Dienst': faculty, # Faculty / Office\n",
    "                    'Departement  Afdeling': department, # Department\n",
    "                    'maand1': month_prev, # Calendar month (mm)\n",
    "                    'jaar1': year_prev_short, # Calendar month (yy)\n",
    "                    'tot': total_hours, # Total hours (Tot.)\n",
    "                    'dag1': current_day, # Date (dd) - Submitter\n",
    "                    'maand2': current_month, # Date (mm) - Submitter\n",
    "                    'jaar2': current_year_short, # Date (yy) - Submitter\n",
    "                    'Naam gemandateerde leidinggevende': supervisor, # Name autorized supervisor / director\n",
    "                    'dag3': current_day, # Date (dd) - Supervisor\n",
    "                    'maand3': current_month, # Date (mm) - Supervisor\n",
    "                    'jaar3': current_year_short} # Date (yy) - Supervisor\n",
    "\n",
    "    # Define the path and filename of the template\n",
    "    template_path = \"tsh_form.pdf\"\n",
    "\n",
    "    # Define the path and filename of the output file\n",
    "    save_path = './generated_forms/'\n",
    "    filename = initial+'_'+last_name+last_name2_title+'_'+last_date\n",
    "    pdf_output = os.path.join(save_path, filename+\".pdf\")               \n",
    "\n",
    "    # Define the keys\n",
    "    ANNOT_KEY = '/Annots'\n",
    "    ANNOT_FIELD_KEY = '/T'\n",
    "\n",
    "    # Function to fill in the PDF file\n",
    "    def fill_pdf(input_pdf_path, output_pdf_path, data_dict):\n",
    "        template_pdf = pdfrw.PdfReader(input_pdf_path)\n",
    "        for page in template_pdf.pages:\n",
    "            annotations = page[ANNOT_KEY]\n",
    "            for annotation in annotations:\n",
    "                if annotation[ANNOT_FIELD_KEY]:\n",
    "                    key = annotation[ANNOT_FIELD_KEY][1:-1]\n",
    "                    if key in data_dict.keys():\n",
    "                        annotation.update(\n",
    "                            pdfrw.PdfDict(V='{}'.format(data_dict[key]))\n",
    "                        )\n",
    "        template_pdf.Root.AcroForm.update(pdfrw.PdfDict(NeedAppearances=pdfrw.PdfObject('true'))) # Make the changes visible in Adobe Acrobat Reader         \n",
    "        pdfrw.PdfWriter().write(output_pdf_path, template_pdf)\n",
    "\n",
    "    # Fill in the PDF file\n",
    "    fill_pdf(template_path, pdf_output, {**dict_of_hours, **dict_values})"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "vscode": {
   "interpreter": {
    "hash": "0594c261cc26547ba4e6384eaf90b25b8725d3d279446d2e1201b420027dfaaa"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}