Client Optimization Project Part 2

Client Optimization Project Part 2 in VB.NET

The main objective of this progress report is to build the mathematical optimization model for the Client Staffing Optimization problem, connect to different databases for input and display tables in the database. You will continue building on your previous project from Progress Report 2 according to the instructions below, some of which have references to the Progress Report 2 instructions provided in the file PR2Instructions.pdf

Actions

DATABASE
a. Download the file ClientDatabase2.xlsx. Prepare a new Access database file named ClientStaffDatabase2.accdb in the project folder from the tables in ClientDatabase2.xlsx following the same procedure in PR2 Instructions.
b. In the Assignment class, add a new property called ID as a String.
c. Go to the the GetAssignment function in the Database class. In this function, find the line where you add the Assignment object to the list of Assignment objects at the end of the For-loop. Add a single line of code right before this line, where you concatenate the StaffID property of the Staff property of the Assignment object created at the beginning of the For-loop with the ClientID property of the Client property of this Assignment object with a dash ("-") in between, and assign it to the ID property of the same Assignment object.
d. At the beginning of the Database class, add a public property called DatabasePath as a string.
e. In the GetDataAdapter function in the Database class, set your connection string as "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DatabasePath
DATA FORMS
a. Create four new forms named frmStaff, frmClient, frmTask and frmAssignment. Change the Text property of these forms as Staff, Client, Task and Assignment, respectively.
b. On each form, create a new DataGridView control (under the Data collection in the Toolbox), name it as dgvData and make it occupy the entire form by clicking on the triangle on the upper right and selecting "Dock in Parent container."
c. For each form, go to the code page for the form and add a public property called DS as DataSet.
d. For each form, go to the design page, double-click on the top bar of the form to create the Load event handler. In the event handler add the line of code dgvData.DataSource = DS.Tables("Staff").DefaultView to set the data source for dgvData, where you change the table name accordingly ("Staff" in frmStaff, "Client" in frmClient, "Task" in frmTask and "Assignment" in frmAssignment).
MAIN FORM DESIGN
a. Reorganize the cost related label and text box controls in the Cost group box from PR2 as shown in the screenshot at the end of the instructions, and add a new button named btnOptimize with the Text property Optimize as in the screenshot.
b. Make extra room at the bottom of the form. Move the Cost group box, Staff group box, Client group box and the Close button down to make room at the top of the form. Create a new group box and set its Text property as Data.
c. In the Data group box, include a button name btnOpenDatabase with Text property as Open Database, a text box named txtDatabasePath to the right of btnOpenDatabase, a label with Text property as Database Path above txtDatabasePath, a list box named lstData below txtDatabasePath, and a button named btnOpenTable with Text property as Open Table to the right of lstData. The button btnOpenDatabase will allow you to select a database using an open file dialog window, and the button btnOpenTable will allow you to open the forms with the DataGridView objects based on the user's table selection in the list box lstData. See the screenshot for the relative placement of these controls.
MAIN FORM CODE
a. Include a public property called DatabasePath as string below the existing properties at the top of the frmMain code page.
b. In the Initialize sub (from PR2), after the first line of code, where you create a new Database object and assign it to DB, include the code DB.DatabasePath = DatabasePath, which will bring the path to the current database to the Database class. Right before each of the For-each loops that populate cboStaffName and cboClientname, write a line to clear the Items collection of these combo boxes.
c. Create a new sub named UpdateCosts; then move the code in Initialize sub starting with the declaration of the variables for the budgeted cost and idle cost (following the For-Each loops) to the end of the code in Initialize sub (which ends with the assignment of the total cost to its relative text box) to the UpdateCosts sub. At the bottom of the UpdateCosts sub set the selected index for cboStaffName first to 1, then to 0 (in two separate lines); do the same for cboClientName. Finally, back in Initialize sub, call UpdateCosts at the end of the function.
d. Create a new sub named OpenDatabase. In this sub, declare a variable as a new instance of type OpenFileDialog (e.g. Dim openDlg as New OpenFileDialog). Set the Filter property to Access files using the code openDlg.Filter = "accdb files (*.accdb)|*.accdb|All files (*.*)|*.*"
Using an If-then block check if openDlg.ShowDialog() equals DialogResults.OK. Inside this If-block, assign openDlg.FileName to the DatabasePath property (which is declared at the top of frmMain), assing DatabasePath to the Text property of txtDatabasePath, and then call the Initialize sub.
e. Create a new sub named OpenTable. In this sub create a Try-catch block. Inside the Try block create a Select-case block on the Text property of lstData. Create cases for "Staff", "Client", "Task" and "Assignment". Under each case, create a new instance of the respective form (e.g. Dim frm As New frmStaff), assign the MyDataSet property of DB to the DS property of this form instance, and call the ShowDialog function of the form instance. Include an Else case, in which you throw a new Exception with a message "Choose a table first." In the Catch block, show the message of the Exception thrown to the user in a message box.
f. Remove the call to Initialize sub from the Load event handler of frmMain. Double click on btnOpenDatabase to create the Click event handler, and inside this event handler call the OpenDatabase sub. Double click on btnOpenTable to create the Click event handler, and inside this event handler call the OpenTable sub.
OPTIMIZATION CODE
a. Download the class file Optimization.vb and save it in your project folder. Then using Project > Add Existing Item, browse to your project folder and find the Optimization class file you downloaded to add it to your project.
b. In the code page for frmMain, include the public property Opt as a new instance of the Optimization class below the other properties at the top of frmMain code page.
c. In the code page for frmMain, create a sub called ClientStaffingOptimization, which has one parameter called StaffClassID as integer. In this sub do the following:
- Declare an integer variable called maxWeek and call GetMaxWeek to assign to this variable.
- Call the InitSolver sub of Opt to initialize your optimization model.
- Create a LINQ query over AssignmentList where the StaffClassID property of the Staff property of the LINQ variable equals the parameter StaffClassID.
- For the decision variables, create a For-each loop over the output list of the LINQ query from the previous step. Do the following in this loop:
> Set the Assigned property of the loop variable to False.
> Call AddVar sub of Opt to create the decision variables, where the first parameter (for variable name) is the ID property of the loop variable, the second parameter (for lower bound) is 0 and the third parameter (for upper bound) is 1.
> Call SetInteger sub of Opt, where the only parameter is the ID property of the loop variable.
- For the constraints regarding one consultant per staff class, create a For-each loop over the Values collection of ClientSList. In this loop do the following:
> Declare a string variable to hold the constraint name, concatenate the string "StaffClass" and the ClientID property of the loop variable with a dash ("-") in between and assign it to this string variable for the constraint name.
> Call AddFun sub of Opt with the constraint name string as the first parameter, 1 as the second parameter and also the third parameter.
> Create a LINQ query over AssignmentList where the ClientID property of the Client property of the query variable equals the ClientID property of the variable for the For-each loop and the StaffClassID property of the Staff property of the query variable equals the StaffClassID parameter.
> Create an inner For-each loop over the output list of the LINQ query from the previous step. In this inner loop, call SetCoef sub of Opt with the variable for the constraint name as the first parameter, the ID property of the variable for the inner For-each loop as the second parameter, and 1 as the third parameter.
- For the 40 hour per week constraints for each consultant, first create a LINQ query over the Values collection of StaffSList where the StaffClassID property of the query variable equals the StaffClassID parameter. Then create a For-each loop over the output list of the LINQ query from the previous step. In this loop do the following:
> Create an inner For-loop with the counter variable i from 1 to maxWeek. In this inner for-loop, do the following:
>> Declare a string variable to hold the constraint name, concatenate the string "TotalHours", the StaffID property of the outer For-each loop variable, and the inner for-loop counter variable i with a dash ("-") in between them (e.g. "TotalHours" & "-" & s.StaffID & "-" & i) and assign it to this string variable for the constraint name.
>> Call AddFun sub of Opt with constraint name string as the first parameter, 0 as the second parameter, and 40 as the third parameter.
>> Declare an integer variable to hold the current week number and assign the inner for-loop counter variable i to this variable.
>> Create a LINQ query over AssignmentList where the StaffID property of the Staff property of the query variable equals the StaffID property of the outer For-each loop variable.
>> Create another inner For-each loop (inside the inner For-loop with counter variable i) over the output list of the LINQ query from the previous step. In this For-each loop do the following:
>>> Declare an integer variable for holding the total number of hours, call GetTotalHours function to assign to this variable with the innermost For-each loop variable as the first parameter, the counter variable i of the For-loop as the second parameter and also the third parameter of the GetTotalHours function.
>>> Call SetCoef sub of Opt with the constraint name string as the first parameter, ID property of the innermost For-each loop variable as the second parameter, and the variable for total number of hours from the previous step as the third variable.
- For the objective function, create a string variable to hold the objective function name and assign the string "obj" to it.
- Call AddObj sub of Opt with the string variable for the objective function name as the only parameter.
- Create a LINQ query over the Values collection of StaffSList where the StaffClassID property of the query variable equals the StaffClassID parameter.
- Create a For-each loop over the output list of the LINQ query from the previous step. In this loop do the following:
> Create a LINQ query over AssignmentList where the StaffID property of the Staff property of the query variable equals StaffID property of the For-each loop variable.
> Create an inner For-each loop over the output list of the LINQ query from the previous step. In this for loop do the following:
>> Declare an integer variable for total hours, call GetTotalHours with the inner For-each loop variable as the first parameter, 1 as the second parameter, maxWeek as the third parameter, and assign it to the total hours variable.
>> Declare a decimal variable for cost, assign the product of the total hours variable and the difference between the Rate property of the inner For-each loop variable and two times the Salary property of the Staff property of the inner For-each loop variable to the cost variable (e.g. Dim cost as Decimal = totalHours * (a.Rate - 2 * a.Staff.Salary).
>> Call SetCoef sub of Opt with the objective function name variable as the first parameter, ID property of the inner For-each loop variable as the second parameter and the cost variable from the previous step as the third variable.
- To solve the model, call SolveModel sub of Opt with the objective function string as the first argument and True as the second argument (for minimization).
- To get the solution results, first create an If-then-else block, where you check if the call to IsOptimal function of Opt equal True. Inside the If case of the If-then-else block do the following:
> Create a LINQ query over AssignmentList where the StaffClassID property of the Staff property of the query variable equals the StaffClassID parameter.
> Create a For-each loop over the output list of the LINQ query from the previous step. In this loop do the following:
>> Declare a decimal variable for the optimal decision value. Call GetVarValue function of Opt with the only parameter as the ID property of the For-each loop variable, and assign it to the variable for the optimal decision value.
>> Create an If-then block where you check if the variable optimal decision value is greater than 0.001. Inside this if block set the Assigned property of the For-each loop variable to True.
- Inside the Else case of the If-then-else block (to get the solution results), concatenate the string "Problem is infeasible for StaffClassID = " with the StaffClassID parameter and assign it to a string varibale. Then display this string variable in a message box.
d. Double click on btnOptimize button to create the event handler for the Click event. Inside this event handler, create a For-loop for i from 1 to 5. Inside this loop call the ClientStaffOptimization sub with the loop counter variable i as its only parameter. After the For-loop call the UpdateCosts sub.