# Employee Scheduling

## 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.

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                         :  "",
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:

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.

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
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-chart {
2   fill: var(--secondary);
3}
4
5.annotation-not-red-chart {
6   fill: var(--primaryDark);
7}
8
9.annotation-red-input  {
10   border: 3px solid red;
12}
13.annotation-green-input  {
14   border: 1px solid green;
16}


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. The annotations used on the Combination Chart were red-chart and not-red-chart. The other two (green-input and red-input) are used on sp_addEditElement to create a border when adding or editing elements.

## 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*/
4   color: var(--primaryDark);
5}
6
7/*Change scalar text color*/
8.tag-scalar .kpi .value {
9   color: var(--primaryDark);
10}
11
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,
3   background: var(--primaryDark);
4}
5
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.