{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the relevant Python libraries and packages\n",
    "\n",
    "#import sys\n",
    "#!{sys.executable} -m pip install ortools openpyxl\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "from ortools.linear_solver import pywraplp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the data from the Excel file\n",
    "\n",
    "Time0 = time.process_time()\n",
    "Preferences = pd.read_excel(\"RecPlex Scheduling Program.xlsx\", \"Preferences\", sep=\"\\t\")\n",
    "Availabilities = pd.read_excel(\"RecPlex Scheduling Program.xlsx\", \"Availabilities\", sep=\"\\t\")\n",
    "Qualifications = pd.read_excel(\"RecPlex Scheduling Program.xlsx\", \"Qualifications\", sep=\"\\t\")\n",
    "Requirements = pd.read_excel(\"RecPlex Scheduling Program.xlsx\", \"Requirements\", sep=\"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Parse through all of the Excel data so we can include it in our Optimization program\n",
    "\n",
    "Time1 = time.process_time()\n",
    "Employees = Preferences[\"Employee Name\"].last_valid_index() + 1\n",
    "Days = 7\n",
    "Hours = 24\n",
    "Roles = 5\n",
    "\n",
    "MinHours = [0 for e in range(Employees)]\n",
    "MaxHours = [0 for e in range(Employees)]\n",
    "IsWoman = [0 for e in range(Employees)]\n",
    "RolePref = [[0 for r in range(Roles)] for e in range(Employees)]\n",
    "RoleQual = [[0 for r in range(Roles)] for e in range(Employees)]\n",
    "Avail = [[[0 for h in range(Hours)] for d in range(Days)] for e in range(Employees)]\n",
    "Need = [[[0 for r in range(Roles)] for h in range(Hours)] for d in range(Days)]\n",
    "Womxn = [[['NO' for r in range(Roles)] for h in range(Hours)] for d in range(Days)]\n",
    "PrefVal = [[[[0 for r in range(Roles)] for h in range(Hours)] for d in range(Days)] for e in range(Employees)]\n",
    "\n",
    "for e in range(Employees):\n",
    "    MinHours[e] = int(Preferences.iat[e,1])\n",
    "    MaxHours[e] = int(Preferences.iat[e,2])\n",
    "    RolePref[e][0] = int(Preferences.iat[e,3])\n",
    "    RolePref[e][1] = int(Preferences.iat[e,4])\n",
    "    RolePref[e][2] = int(Preferences.iat[e,5])\n",
    "    RolePref[e][3] = int(Preferences.iat[e,6])\n",
    "    RolePref[e][4] = int(Preferences.iat[e,7])\n",
    "    \n",
    "    if Qualifications.iat[e,1] == \"YES\": IsWoman[e] = 1\n",
    "    for r in range(Roles):\n",
    "        if Qualifications.iat[e,r+2] == \"YES\": RoleQual[e][r] = 1\n",
    "    \n",
    "    for d in range(5):\n",
    "        Avail[e][d][7] = int(Availabilities.iat[e,5*d+1])\n",
    "        Avail[e][d][8] = int(Availabilities.iat[e,5*d+2])\n",
    "        Avail[e][d][9] = int(Availabilities.iat[e,5*d+2])\n",
    "        Avail[e][d][15] = int(Availabilities.iat[e,5*d+3])\n",
    "        Avail[e][d][16] = int(Availabilities.iat[e,5*d+3])\n",
    "        Avail[e][d][17] = int(Availabilities.iat[e,5*d+3])\n",
    "    \n",
    "    for d in range(5):\n",
    "        Avail[e][d][18] = int(Availabilities.iat[e,5*d+4])\n",
    "        Avail[e][d][19] = int(Availabilities.iat[e,5*d+4])\n",
    "        Avail[e][d][20] = int(Availabilities.iat[e,5*d+5])\n",
    "        Avail[e][d][21] = int(Availabilities.iat[e,5*d+5])\n",
    "      \n",
    "    Avail[e][5][12] = int(Availabilities.iat[e,26])\n",
    "    Avail[e][5][13] = int(Availabilities.iat[e,26])\n",
    "    Avail[e][5][14] = int(Availabilities.iat[e,27])\n",
    "    Avail[e][5][15] = int(Availabilities.iat[e,27])\n",
    "    Avail[e][6][18] = int(Availabilities.iat[e,28])\n",
    "    Avail[e][6][19] = int(Availabilities.iat[e,28])\n",
    "    Avail[e][6][20] = int(Availabilities.iat[e,29])\n",
    "    Avail[e][6][21] = int(Availabilities.iat[e,29])\n",
    "    \n",
    "for d in range(5):\n",
    "    for h in range(7,22):\n",
    "        Womxn[d][h][0] = Requirements.iat[15*d+h-7,2]\n",
    "        Womxn[d][h][1] = Requirements.iat[15*d+h-7,3]\n",
    "        for r in range(Roles):\n",
    "            Need[d][h][r] = int(Requirements.iat[15*d+h-7,r+4])\n",
    "        \n",
    "for h in range(12,16):\n",
    "    Womxn[5][h][0] = Requirements.iat[75+h-12,2]\n",
    "    Womxn[5][h][1] = Requirements.iat[75+h-12,3]\n",
    "    for r in range(Roles):\n",
    "        Need[5][h][r] = int(Requirements.iat[75+h-12,r+4])\n",
    "        \n",
    "for h in range(18,22):\n",
    "    Womxn[6][h][0] = Requirements.iat[79+h-18,2]\n",
    "    Womxn[6][h][1] = Requirements.iat[79+h-18,3]\n",
    "    for r in range(Roles):\n",
    "        Need[6][h][r] = int(Requirements.iat[79+h-18,r+4])\n",
    "        \n",
    "for e in range(Employees):\n",
    "    for d in range(Days):\n",
    "        for h in range(Hours):\n",
    "            for r in range(Roles):\n",
    "                PrefVal[e][d][h][r] = RolePref[e][r] * RoleQual[e][r] * Avail[e][d][h]\n",
    "                if PrefVal[e][d][h][r]==0: PrefVal[e][d][h][r]=-50"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create our Optimal Timetable\n",
    "\n",
    "Time2 = time.process_time()\n",
    "Solver = pywraplp.Solver('Solver', pywraplp.Solver.CBC_MIXED_INTEGER_PROGRAMMING)\n",
    "       \n",
    "# Define the binary variable X[e,d,h,r], which will equal 1 if Employee e \n",
    "# is assigned to work a shift on Day d in Hour h in Role r\n",
    "X = {}\n",
    "for e in range(Employees):\n",
    "    for d in range(Days):\n",
    "        for h in range(Hours):\n",
    "            for r in range(Roles):\n",
    "                X[e,d,h,r] = Solver.IntVar(0, 1, 'X[%d,%d,%d,%d]' % (e,d,h,r))\n",
    "\n",
    "                \n",
    "# Set up our Happiness Function, which maximizes the total number of Happiness Points\n",
    "HappinessFunction = Solver.Sum(PrefVal[e][d][h][r]*X[e,d,h,r] for e in range(Employees) \n",
    "                               for d in range(Days) for h in range(Hours) for r in range(Roles))\n",
    "Solver.Maximize(HappinessFunction)\n",
    "\n",
    "\n",
    "# Constraint 1: The number of needed employees must be met in each day, hour, and role\n",
    "for d in range(Days):\n",
    "        for h in range(Hours):\n",
    "            for r in range(Roles):\n",
    "                Solver.Add(Solver.Sum([X[e,d,h,r] for e in range(Employees)]) <= Need[d][h][r])\n",
    "\n",
    "                \n",
    "# Constraint 2: No employee can work two roles at the same time\n",
    "for e in range(Employees):\n",
    "    for d in range(Days):\n",
    "        for h in range(Hours):\n",
    "            Solver.Add(Solver.Sum([X[e,d,h,r] for r in range(Roles)]) <= 1)\n",
    "\n",
    "            \n",
    "# Constraint 3: No employee can work a role that they prefer not to work, or are not qualified to work\n",
    "for e in range(Employees):\n",
    "    for r in range(Roles):\n",
    "        if RolePref[e][r] * RoleQual[e][r] == 0:\n",
    "            for d in range(Days):\n",
    "                for h in range(Hours):\n",
    "                    Solver.Add(X[e,d,h,r] == 0)\n",
    "            \n",
    "            \n",
    "# Constraint 4: We must ensure the minimum and maximum number of hours for each employee\n",
    "for e in range(Employees):\n",
    "    Solver.Add(Solver.Sum([X[e,d,h,r] for d in range(Days) \n",
    "                           for h in range(Hours) for r in range(Roles)]) >= MinHours[e])\n",
    "    Solver.Add(Solver.Sum([X[e,d,h,r] for d in range(Days) \n",
    "                           for h in range(Hours) for r in range(Roles)]) <= MaxHours[e])\n",
    "            \n",
    "\n",
    "# Constraint 5: Ensure the same Reception worker for these weekday blocks: 8-10, 15-18, 18-20, 20-22\n",
    "for e in range(Employees):\n",
    "    for d in range(5):\n",
    "        Solver.Add(X[e,d,8,0]==X[e,d,9,0])\n",
    "        Solver.Add(X[e,d,15,0]==X[e,d,16,0])\n",
    "        Solver.Add(X[e,d,16,0]==X[e,d,17,0])\n",
    "        Solver.Add(X[e,d,18,0]==X[e,d,19,0])\n",
    "        Solver.Add(X[e,d,20,0]==X[e,d,21,0])\n",
    "    \n",
    "        \n",
    "# Constraint 6: Ensure that if someone works reception from 7:00-8:00 they also work reception from 8:00-10:00\n",
    "for e in range(Employees):\n",
    "    for d in range(Days):\n",
    "        Solver.Add(X[e,d,7,0] <= X[e,d,8,0])\n",
    "        \n",
    "        \n",
    "# Constraint 7: Ensure the same Reception worker for these weekend blocks: 12-14, 14-16, 18-20, 20-22\n",
    "for e in range(Employees):\n",
    "    Solver.Add(X[e,5,12,0]==X[e,5,13,0])\n",
    "    Solver.Add(X[e,5,14,0]==X[e,5,15,0])\n",
    "    Solver.Add(X[e,6,18,0]==X[e,6,19,0])\n",
    "    Solver.Add(X[e,6,20,0]==X[e,6,21,0])\n",
    "        \n",
    "        \n",
    "# Constraint 8: Ensure the same Cave worker for 18-20 and 20-22 whenever they are 2-hour shifts\n",
    "for d in range(Days):\n",
    "    if Need[d][18][1]+Need[d][19][1] == 2:\n",
    "        for e in range(Employees):\n",
    "            Solver.Add(X[e,d,18,1]==X[e,d,19,1])\n",
    "    if Need[d][20][1]+Need[d][21][1] == 2:\n",
    "        for e in range(Employees):\n",
    "            Solver.Add(X[e,d,20,1]==X[e,d,21,1])\n",
    "        \n",
    "        \n",
    "# Constraint 9: Ensure the same Cave worker for 19-22 whenever they are 3-hour shifts\n",
    "for d in range(Days):\n",
    "    if Need[d][18][1] == 0 and Need[d][19][1]+Need[d][20][1]+Need[d][21][1] == 3:\n",
    "        for e in range(Employees):\n",
    "            Solver.Add(X[e,d,19,1]==X[e,d,20,1])\n",
    "            Solver.Add(X[e,d,20,1]==X[e,d,21,1])\n",
    "        \n",
    "\n",
    "# Constraint 10: No employee can work more than 4 hours in a day\n",
    "for e in range(Employees):\n",
    "    for d in range(Days):\n",
    "        Solver.Add(Solver.Sum([X[e,d,h,r] for h in range(Hours) for r in range(Roles)]) <= 4)\n",
    "        \n",
    "\n",
    "# Constraint 11: Avoid clopening: if you work 20-22 one night you can't work 7-10 the next morning\n",
    "for e in range(Employees):\n",
    "    for d in range(6):\n",
    "        Solver.Add(Solver.Sum([X[e,d,21,r] + X[e,d+1,7,r] for r in range(Roles)]) <= 1)\n",
    "        Solver.Add(Solver.Sum([X[e,d,21,r] + X[e,d+1,8,r] for r in range(Roles)]) <= 1)\n",
    "        \n",
    "\n",
    "# Constraint 12: Men cannot work during Womxn Hours\n",
    "for d in range(Days):\n",
    "    for h in range(Hours):\n",
    "        for r in range(Roles):\n",
    "            if Womxn[d][h][r]=='YES':\n",
    "                for e in range(Employees):\n",
    "                    if IsWoman[e]==0:\n",
    "                        Solver.Add(X[e,d,h,r]==0)\n",
    "\n",
    "\n",
    "# Solve the Integer Linear program\n",
    "Output = Solver.Solve()\n",
    "TotalPoints = round(Solver.Objective().Value())\n",
    "\n",
    "\n",
    "# Determine the total time of running the program.\n",
    "Time3 = time.process_time()\n",
    "DataTime = round(Time1 - Time0, 3)\n",
    "CleanTime = round(Time2 - Time1, 3)\n",
    "OptimTime = round(Time3 - Time2, 3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export our solution to Excel\n",
    "\n",
    "OurColumns = [\"Hour\", \"Role 1\", \"Role 2\", \"Role 3\", \"Role 4\", \"Role 5\"]\n",
    "\n",
    "Schedule = [ [\"-\" for r in range(Roles+1)] for hd in range(Hours*Days)]\n",
    "            \n",
    "for d in range(Days):\n",
    "        for h in range(Hours):\n",
    "            Schedule[24*d+h][0] = h\n",
    "            \n",
    "for e in range(Employees):\n",
    "    for d in range(Days):\n",
    "        for h in range(Hours):\n",
    "            for r in range(Roles):              \n",
    "                if X[e,d,h,r].solution_value()==1:\n",
    "                    Schedule[24*d+h][r+1] = Preferences[\"Employee Name\"][e]\n",
    "                    if PrefVal[e][d][h][r]<0:\n",
    "                        Schedule[24*d+h][r+1] = Preferences[\"Employee Name\"][e] + \" (X)\"\n",
    "                         \n",
    "for d in range(Days):\n",
    "    for h in range(Hours):\n",
    "        for r in range(Roles):  \n",
    "            if Need[d][h][r] == 1 and Schedule[24*d+h][r+1]==\"-\":\n",
    "                Schedule[24*d+h][r+1]=\"UNFILLED\"\n",
    "                \n",
    "                \n",
    "FinalMatrix = pd.DataFrame(Schedule, columns=OurColumns)\n",
    "Bad1 = [24*d+h for d in range(5) for h in [0,1,2,3,4,5,6,22,23]]\n",
    "Bad2 = [24*5+h for h in range(12)] + [24*5+h for h in range(16,24)] \n",
    "Bad3 = [24*6+h for h in range(18)] + [24*6+22, 24*6+23] \n",
    "AllBad = Bad1+Bad2+Bad3\n",
    "FinalMatrix = FinalMatrix.drop(AllBad)\n",
    "\n",
    "FinalMatrix.to_csv(\"Optimal RecPlex Schedule.csv\", index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Output one of the possible optimal solutions.\n",
    "\n",
    "print(\"It took\", DataTime, \"seconds to read the Excel file,\", CleanTime, \"seconds to prepare the data set\")\n",
    "print(\"And just\", OptimTime, \"seconds to produce the optimal schedule, with\", TotalPoints, \"Total Happiness Points\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}