Employee Scheduling

https://img.shields.io/badge/AIMMS_4.86-ZIP:_Employee_Scheduling-blue https://img.shields.io/badge/AIMMS_4.86-Github:_Employee_Scheduling-blue https://img.shields.io/badge/AIMMS_Community-Forum-yellow

Story

In this example an optimal employee assignment is determined for a chain of restaurants. Each restaurant has different demands concerning the number of employees available and their skills. The objective of the optimization is to find the best placement of staff at the lowest cost, while taking into account shortage of skilled workforce.

The employees work in shifts and each position is primarily fulfilled by skilled employees. Assigning a less experienced staff member to a task will have impact on the overall cost.

Mathematical Model

The mathematical programming model for this example is a variation of the assignment problem. The similarity is that people are assigned to tasks, the differences are

  • that unmet demand is taken into account,

  • multiple shifts, and

  • the same skill is needed at different restaurants.

Employee Scheduling Problem

Sets and indices:

\(E\), \(e \in E\)

employees

\(R\), \(r \in R\)

restaurants

\(K\), \(k \in K\)

skills

\(H\), \(h \in H\)

shifts

Parameters:

\(D_{r,h,k} \in \mathbb{I}\)

demand of employees in the restaurants

\(eD_{e,h} \in \{0..1\}\)

employees’ availability for shifts

\(eH_{e,k} \in \{0..1\}\)

employees’ skills

\(eP_{r,e,h,k} \in \mathbb{R}\)

job preferences of the employees

Variables:

\(X_{r,e,h,k} \in \{0..1\}\)

employees’ work schedule

\(Y_{r,h,k} \in \mathbb{R_{+}}\)

unfulfilled positions

Constraints:

1

\(\forall r,h,k: \sum_e X_{r,e,h,k} = D_{r,h,k} - Y_{r,h,k}\)

satisfy demand

2

\(\forall e,h: \sum_{r,k} X_{r,e,h,k} \leq eD_{e,h}\)

assignment satisfies shifts

3

\(\forall r,e,h,k: X_{r,e,h,k} \leq eH_{r,e,h,k}\)

assignment satisfies skills

4

\(\forall e: \sum_{r,h,k} X_{r,e,h,k} \leq 1\)

maximum of one shift

Minimize:

\(\sum_{r,h,k} Y_{r,h,k} * 1000 + \sum_{r,e,h,k} X_{r,e,h,k} * eP_{r,e,h,k}\)

total cost

Language

In this section a few highlights of the use of the AIMMS Language in the application are pointed out.

Structure

This application structured its data nicely by using sections nested, as can be seen in the next screenshot. Each section at the leaf level contains a few identifiers which helps finding your way around the application.

../../_images/model-explorer-section-structure.png

This structuring is meaningful; the structure can be used in the application, for instance in a mathematical program declaration like:

1MathematicalProgram mp_minimizeCost {
2    Objective: v_totalCost;
3    Direction: minimize;
4    Constraints: s_employeeAssignmentConstraints;
5    Variables: s_employeeAssignmentVariables;
6    Type: Automatic;
7}

The set s_employeeAssignmentConstraints is constructed by intersecting the declarations inside the section Math_model with the predeclared AllConstraints.

1Set s_employeeAssignmentConstraints {
2    SubsetOf: AllConstraints;
3    Definition: AllConstraints * Math_Model;
4}

Such structuring eases grouping related constraints and variables together into a mathematical program; or rather, to work with multiple groups of constraints and variables and thereby defining multiple multiple mathematical programs in a single application.

Database

The Employee Scheduling example persists its data in a database, a SQLite database.

Connecting to the database

A SQLite database is just a file, and authentication is not needed, so a connection string can be build as follows:

 1StringParameter sp_connectionString {
 2    Definition: {
 3        SQLCreateConnectionString (
 4            DatabaseInterface              :  'odbc',
 5            DriverName                     :  "SQLite3 ODBC Driver",
 6            ServerName                     :  "",
 7            DatabaseName                   :  "inputs.db", !The path of your database
 8            UserId                         :  "",
 9            Password                       :  "",
10            AdditionalConnectionParameters :  "") ;
11    }
12}

Relating tables in the database to tables in the AIMMS model

An example of a table declaration in a SQLite database is illustrated in the next image:

../../_images/employee-skill-SQLite-table.png

The AIMMS database declaration of the corresponding table is as follows:

 1DatabaseTable db_readEmployeeSkills {
 2    DataSource: sp_connectionString;
 3    TableName: "employee_skill";
 4    Mapping: {
 5        "employee_id"      -->i_empl,
 6        "skill_id"         -->i_sk,
 7        "has_skill"        -->p_employeeHasSkill,
 8        "skill_preference" -->p_employeeSkillPreference
 9    }
10}

Once the connection string exists, the data in the tables is read by a database declaration and a read statement. If the data is changed in the user interface, the data is persisted using similar write statements.

Reading from the database

As you can see, the column names of the table are used to relate to the AIMMS identifiers at hand.

Transferring the data from the SQLite database to the AIMMS application is done using the following read statement:

1Procedure pr_readEmployeeSkillDB {
2    Body: {
3        empty Employee_Skill;
4
5        read from table db_readEmployeeSkills;
6    }
7}

Note that Employee_Skill is a declaration section; all identifiers declared in that section will be emptied by the first statement in the procedure pr_readEmployeeSkillDB;

Writing to the database

Transferring the data from the AIMMS application to the SQLite database is done using the following write statement:

1Procedure pr_writeEmployeeSkillDB {
2    Body: {
3        write p_employeeHasSkill(i_empl, i_sk),
4                p_employeeSkillPreference(i_empl, i_sk)
5            to table db_readEmployeeSkills in dense mode;
6    }
7}

References for using ODBC

  1. Link an SQLite Database to a Project

  2. SQLCreateConnectionString

  3. Need to go deeper into DB connections? This course is perfect for you!

Annotations

On this project annotation is used on the Combination Chart. For this, we added a new css file:

1.annotation-red {
2   fill: var(--secondary);
3}
4.annotation-not-red {
5   fill: var(--primaryDark);
6}

Create into a string parameter the logic or define directly with the css class you want. Go to the identifier shown on the Combination Chart and add that string parameter into webui::AnnotationsIdentifier.

WebUI Features

On master page, there are two ‘hidden’ features. First is that if you click with the right button on either table widgets, a small menu will appear with CRUD options for that set. And, the Gantt chant is editable, i.e. you can modify start time and duration directly into the graph! Both results page have similar functionality, click around into the tables to see different views on the Gantts and graphs.

The following WebUI features are used:

UI Styling

For this project, we used a main css file named colors.css, please check it out directly on the folder. Below there are the css files you will find with comments on what they change.

1:root {
2   --bg_app-logo: 15px 50% / 30px 30px no-repeat url(/app-resources/resources/images/schedule.png);
3   --spacing_app-logo_width: 45px;
4}
 1/*Change color of the active step*/
 2.workflow-panel .step-item.current,
 3.workflow-panel.collapse .step-item.current {
 4   box-shadow: inset 0.3125rem 0 0 var(--primaryDark);
 5}
 6
 7/*Change color of the titles*/
 8.workflow-panel .step-item.active.complete .title,
 9.workflow-panel .step-item.active.incomplete .title {
10   color: var(--primaryDark);
11}
12
13/*Change color of the icons*/
14.workflow-panel .step-item.active.complete .icon,
15.workflow-panel .step-item.active.incomplete .icon {
16   color: var(--primaryDark);
17   border: 1px solid var(--primaryDark);
18}
 1/*Change table text color*/
 2.tag-table .grid-viewport .cell:not(.flag-readOnly),
 3html:not(.using-touch) .tag-table .grid-viewport .cell:not(.flag-readOnly) {
 4   color: var(--primaryDark);
 5}
 6
 7/*Change scalar text color*/
 8.tag-scalar .kpi .value {
 9   color: var(--primaryDark);
10}
11
12/*Link color*/
13.ql-snow a {
14   color: var(--primaryDark);
15}
 1/*Add logo on the background*/
 2.scroll-wrapper--pagev2 .page-container {
 3   content: " ";
 4   background: url(img/RightBackground.png) rgb(249, 249, 249) no-repeat left/contain;
 5}
 6
 7.widgetdiv .awf-dock.top {
 8   border-bottom: 2px solid var(--primaryDark);
 9   background: linear-gradient(180deg, rgba(255,255,255,1) 20%, var(--primary) 100%);
10}
1.theme-aimms header.tag-application {
2   border-bottom: 2px solid var(--primaryDark);
3}
1/*Change color of togglelegend of the combination chart*/
2.togglelegend-button svg{
3   fill: var(--primaryDark);
4}
5
6.togglelegend-button-active:hover svg g, .togglelegend-button-active svg g {
7   fill: var(--primary);
8}
 1/*Change color after tab click*/
 2.sidepanel-container .sidepanel-tab.active {
 3   background-color: var(--primaryDark);
 4}
 5
 6/*Change letter color on hover*/
 7.sidepanel-container .sidepanel-tab.active:hover {
 8   color: white;
 9}
10
11/*Change icon color*/
12.sidepanel-container .sidepanel-tab .sidepanel-icon,
13.sidepanel-container .sidepanel-tab:hover {
14   color: var(--primaryDark);
15}
16
17/*Change color after all tabs*/
18.sidepanel-container .sidepanel-tabs-container:after {
19   background: var(--primaryDark);
20}
21
22/*Change the color below sidepanel tabs*/
23.sidepanel-container {
24   background-color:   rgb(249, 249, 249);
25
26}
27
28.sidepanel-active .sidepanel-container {
29   background-color:   rgba(249, 249, 249, 0);
30}
 1.page-action-v2 .page-action-menu,
 2.page-action-v2 .page-action-menu.open {
 3   background: var(--primaryDark);
 4}
 5
 6.page-action-v2 .page-action-menu:hover,
 7.page-action-v2 .page-action-menu:hover {
 8   background: var(--primary);
 9}
10
11.page-action-v2 .page-action-holder .page-action-item .page-action-icon,
12.page-action-v2 .page-action-holder .page-action-item .page-action-letter {
13   background-color: var(--primaryDark);
14}
15
16.page-action-v2 .page-action-holder .page-action-item .page-action-icon:hover,
17.page-action-v2 .page-action-holder .page-action-item .page-action-letter:hover {
18   background-color: var(--primary);
19}
 1.tag-table.focused .focus-cell {
 2   box-shadow: inset 0 0 0 1px var(--primaryDark);
 3}
 4
 5.tag-table .cell.flag-number input{
 6   text-align: center;
 7}
 8
 9/*Change checkbox color*/
10input.boolean-cell-editor-contents {
11   accent-color: var(--primaryDark);
12}

Minimal Requirements

AIMMS Community license is sufficient and ODBC instalation is necessary for working with this example.