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