/* ---------- Comment: Staff Scheduling Model --------------------
   Requires an Access Database with three tables:
   1. Requirements: Lists each "day" and the requirements for the day
   2. Shifts: Lists the names of the shifts
   3. ShiftDays: Lists the days in each shift
   
   This model determines how many employees to assign to each shift 
   so as to meet each days requirements with a minimum total number 
   of employees

   ---------------------------------------------------------------- */

/* ================================================================
   MODEL SETS AND PARAMETERS
   ================================================================ */

/* ----------------------------------------------------------------
   The set of days 
   ---------------------------------------------------------------- */
set DAYS;

   /* -------------------------------------------------------------
      Daily requirements 
      ------------------------------------------------------------- */
	param Requirement{DAYS};

/* ----------------------------------------------------------------
   The set of shifts 
   ---------------------------------------------------------------- */
set SHIFTS;

/* ----------------------------------------------------------------
   The days each shift works. This is a two dimensional set. Its 
   elements are of the form (shift, day) where shift is in SHIFTS and 
   day is in DAYS.  
   ---------------------------------------------------------------- */
set SHIFTDAYS within SHIFTS cross DAYS;


/* ================================================================
   READ THE DATA
   ================================================================ */

/* ----------------------------------------------------------------
   Define a table called DayTable that brings data IN to AMPL via 
   ODBC from the datasource StaffingData. You will need to set this 
   datasource up in the Windows Control Panel. It should point to 
   our Access Database StaffScheduling.mdb. DayTable reads the Day 
   and Requirement fields from the table Requirements. Day is in []'s 
   because it is an index, i.e., we will read a Requirement for each 
   day. This also defines the set DAYS. 
   ---------------------------------------------------------------- */

table DayTable IN "ODBC"
"DSN=StaffingData"
"Requirements":
DAYS <- [Day], Requirement;

read table DayTable;

/* ----------------------------------------------------------------
   Define a table called ShiftTable that brings data IN to AMPL via 
   ODBC from the datasource StaffingData.  ShifTable reads the Shifts 
   via an SQL query and defines the set SHIFTS.  
   ---------------------------------------------------------------- */
table ShiftTable IN "ODBC"
"DSN=StaffingData"
"SQL=SELECT Shift from Shifts":
SHIFTS <- [Shift];

read table ShiftTable;

/* ----------------------------------------------------------------
   Define a table called ShiftDaytTable that brings data IN to AMPL via 
   ODBC from the datasource StaffingData.  ShifTable reads the ShiftDays  
   table and defines the set SHIFTDAYS.  
   ---------------------------------------------------------------- */
table ShiftDayTable IN "ODBC"
"DSN=StaffingData"
"ShiftDays":
SHIFTDAYS <- [Shift, Day];

read table ShiftDayTable;


/* ================================================================
   MODEL VARIABLES
   ================================================================ */

/* ----------------------------------------------------------------
   The number of employees to assign to each shift 
	must be non-negative
   ---------------------------------------------------------------- */
var Staff{SHIFTS} >= 0;



/* ================================================================
   MODEL OBJECTIVE AND CONSTRAINTS
   ================================================================ */

/* ----------------------------------------------------------------
   Objective: Minimize the number of employees
   ---------------------------------------------------------------- */

minimize TotalStaff: 
	sum{shift in SHIFTS} Staff[shift];

/* ----------------------------------------------------------------
   Meet each days requirements
   ---------------------------------------------------------------- */
s.t. MeetRequirements{day in DAYS}:
	sum{(shift, day) in SHIFTDAYS} Staff[shift] >= Requirement[day];


/* ================================================================
   SOLVE THE PROBLEM
   ================================================================ */

solve;

/* ================================================================
   REPORT THE ANSWER
   ================================================================ */

table StaffOut OUT "ODBC"
"DSN=StaffingData"
"StaffOut":
{shift in SHIFTS: Staff[shift] > 0} -> [shift],  Staff[shift]~Staff;

write table StaffOut;