{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import sys\n",
    "#!{sys.executable} -m pip install ortools \n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "import random\n",
    "from ortools.linear_solver import pywraplp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "InputData = pd.read_excel(\"Recplex.xlsx\", sep=\"\\t\")\n",
    "InputData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Determine the start time\n",
    "StartTime = time.process_time()\n",
    "\n",
    "# Define our Linear Program\n",
    "Solver = pywraplp.Solver('Solver', pywraplp.Solver.CBC_MIXED_INTEGER_PROGRAMMING)\n",
    "\n",
    "# Define the Preference Coefficient P[i,j], for Residence i working in Week j\n",
    "n=14\n",
    "m=35\n",
    "\n",
    "P = np.zeros(shape=(n, m), dtype=int)\n",
    "\n",
    "for j in range(m):\n",
    "    for i in range(n):\n",
    "        P[i,j] = InputData[j+1][i]\n",
    "        \n",
    "# Define the binary variable X[i,j], which will equal 1 if Employee i is assigned to Shift j\n",
    "X = {}\n",
    "for i in range(n):\n",
    "    for j in range(m):\n",
    "        X[i,j] = Solver.IntVar(0, 1, 'X[%d, %d]' % (i,j))\n",
    "        \n",
    "# Set up our Happiness Function, which maximizes the total number of Happiness Points\n",
    "HappinessFunction = Solver.Sum(P[i,j]*X[i,j] for i in range(n) for j in range(m))\n",
    "Solver.Minimize(HappinessFunction)\n",
    "\n",
    "# Include our first constraint: Each employee must work at least two and at most three shifts\n",
    "for i in range(n):\n",
    "    Solver.Add(Solver.Sum([X[i,j] for j in range(m)]) >= 2)\n",
    "    Solver.Add(Solver.Sum([X[i,j] for j in range(m)]) <= 3)\n",
    "                       \n",
    "# Include our second constraint: Each week must be covered by at least one employee and at most two\n",
    "for j in range(m):\n",
    "    Solver.Add(Solver.Sum([X[i,j] for i in range(n)]) >= 1)\n",
    "    Solver.Add(Solver.Sum([X[i,j] for i in range(n)]) <= 2)\n",
    "\n",
    "# Include our third constraint: you can't work a Cave Shift and a Reception Shift at the same time\n",
    "for i in range(n):\n",
    "    Solver.Add(X[i,3] + X[i,27] <= 1)\n",
    "    Solver.Add(X[i,4] + X[i,28] <= 1)\n",
    "    Solver.Add(X[i,8] + X[i,29] <= 1)\n",
    "    Solver.Add(X[i,9] + X[i,30] <= 1)\n",
    "    Solver.Add(X[i,13] + X[i,31] <= 1)\n",
    "    Solver.Add(X[i,14] + X[i,32] <= 1)\n",
    "    Solver.Add(X[i,18] + X[i,33] <= 1)\n",
    "    Solver.Add(X[i,19] + X[i,34] <= 1)\n",
    "    \n",
    "\n",
    "# Include our fourth constraint: a man can't work during WomXn hours\n",
    "for i in [0,3,5,7,10,12]:\n",
    "    Solver.Add(X[i,8] + X[i,18] + X[i,27] == 0)\n",
    "\n",
    "    \n",
    "# Solve the Integer Linear program\n",
    "Output = Solver.Solve()\n",
    "TotalPoints = round(Solver.Objective().Value())\n",
    "\n",
    "# Determine the total time of running the program.\n",
    "TotalTime = round(time.process_time() - StartTime, 4)\n",
    "\n",
    "# Output one of the possible optimal solutions.\n",
    "print(\"Python returns a solution with\", TotalPoints, \"Total Happiness Points in\", TotalTime, \"seconds\")\n",
    "pd.options.mode.chained_assignment = None\n",
    "OutputData = InputData.copy()\n",
    "for i in range(n):\n",
    "    for j in range(m):\n",
    "        if X[i,j].solution_value()==0:\n",
    "            OutputData[j+1][i] = \"\"\n",
    "OutputData "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(n):\n",
    "    for j in range(m):\n",
    "        if X[i,j].solution_value()==1:\n",
    "            print(InputData[\"Employee\"][i], \"will work shift\", j+1, \"with score\", P[i,j])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for j in range(m):\n",
    "    for i in range(n):\n",
    "        if X[i,j].solution_value()==1:\n",
    "            print(\"Shift\", j+1, \"will be worked by\", InputData[\"Employee\"][i], \"with score\", P[i,j])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}