int IComparer.Compare(object object1, object object2)
{
clsClients client1 = (clsClients)object1;
clsClients client2 = (clsClients)object2;
return(client1.Weeks.CompareTo(client2.Weeks));
}
int IComparer.Compare(object object1, object object2)
{
clsClients client1 = (clsClients)object1;
clsClients client2 = (clsClients)object2;
return(client1.FirstName.CompareTo(client2.FirstName));
}
int IComparer.Compare(object object1, object object2)
{
clsClients client1 = (clsClients)object1;
clsClients client2 = (clsClients)object2;
return(-(client1.GoalWeight.CompareTo(client2.GoalWeight)));
}
int IComparer.Compare(object object1, object object2)
{
clsClients client1 = (clsClients)object1;
clsClients client2 = (clsClients)object2;
string client1Name = client1.LastName + client1.FirstName;
string client2Name = client2.LastName + client2.FirstName;
return(client1Name.CompareTo(client2Name));
}
// The loadClients helper method reads the data from the specified file and copies
// to the client array.
private void loadClients(string sql)
{
clsClients client;
// Clear out the array before handling the file data.
mClients.Clear();
// Read the data from the specified file.
if (File.Exists(mClientFile) == false)
{
ShowMessage(mClientFile + " does not exist. Please open another DB file.");
return;
}
openDatabaseConnection();
mDB.Open();
OleDbCommand cmd;
OleDbDataReader rdr;
try
{
cmd = new OleDbCommand(sql, mDB);
rdr = cmd.ExecuteReader();
while (rdr.Read() == true)
{
// Add the data from the line just read to the next array element, making sure to get the ID.
client = new clsClients((int)rdr["ID"],
(string)rdr["FirstName"],
(string)rdr["LastName"],
(int)rdr["Age"],
(double)rdr["HeightInches"],
(double)rdr["StartWeight"],
(double)rdr["GoalWeight"],
(int)rdr["Weeks"]);
mClients.Add(client);
}
rdr.Close();
}
catch (Exception ex)
{
ShowMessage("There was an unexpected problem: " + ex.Message);
}
finally
{
closeDatabaseConnection();
}
}
// This method removes the selected client from the roster
private void btnDelete_Click(object sender, EventArgs e)
{
string sql;
int selectedClient = lstSchools.SelectedIndex - 2;
// Make sure a valid client was selected.
if (selectedClient < 0 || selectedClient >= mClients.Count)
{
ShowMessage("Please select a valid client in the listbox.");
lstSchools.SelectedIndex = -1;
return;
}
// Get the ID of the selected client to fill in the input boxes
clsClients temp = (clsClients)mClients[selectedClient];
// Delete the selected client and remove the corresponding record in the ClientPlan table
try
{
openDatabaseConnection();
mDB.Open();
OleDbCommand cmd;
sql = "DELETE FROM ClientPlan WHERE ClientID = " + clsSQL.ToSql(temp.ID);
cmd = new OleDbCommand(sql, mDB);
cmd.ExecuteNonQuery();
sql = "DELETE FROM Clients WHERE ID = " + clsSQL.ToSql(temp.ID);
cmd = new OleDbCommand(sql, mDB);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ShowMessage("There was an unexpected problem when deleting the client: " + ex.Message);
}
finally
{
closeDatabaseConnection();
}
// Erase the input values, notify the user, and display the current client roster
eraseInputFields();
ShowMessage(temp.FirstName + "'s record has been deleted.");
loadClients("SELECT * FROM Clients");
displayData();
}
// When the Update button is clicked, the user is able to modify the data
// for the selected client.
private void btnUpdate_Click(object sender, EventArgs e)
{
// Define all variables.
// Define all variables.
int Number;
string sql;
int selectedClient = lstClients.SelectedIndex - 2;
// Make sure a valid client was selected.
if (selectedClient < 0 || selectedClient >= mClients.Count)
{
ShowMessage("Please select a valid program in the listbox.");
lstClients.SelectedIndex = -1;
return;
}
// Get the ID of the selected client to fill in the input boxes
clsClients temp = (clsClients)mClients[selectedClient];
// Validate the user's input.
if (txtName.Text == "")
{
ShowMessage("Please enter a program name.");
txtName.Focus();
return;
}
if (txtDate.Text == "")
{
ShowMessage("Please enter a date for the program.");
txtDate.Focus();
return;
}
if (txtAudience.Text == "")
{
ShowMessage("Please enter a target audience for the program.");
txtDate.Focus();
return;
}
if (validateInput(txtNumber, 0, 1000, out Number) == false)
{
return;
}
// Data is valid so modify the client and reload the data from the DB
try
{
// Change a record in the Clients table.
openDatabaseConnection();
mDB.Open();
OleDbCommand cmd;
sql = "UPDATE DiversityProgram SET NameofProgram = " + clsSQL.ToSql(txtName.Text) +
", DateofProgram = " + clsSQL.ToSql(txtDate.Text) +
", TargetAudience = " + clsSQL.ToSql(txtAudience.Text) +
", NumberinAttendance = " + clsSQL.ToSql(txtNumber.Text) +
" WHERE NameofProgram = " + clsSQL.ToSql(temp.NameofProgram);
cmd = new OleDbCommand(sql, mDB);
cmd.ExecuteNonQuery();
// If the client-plan already exists, ignore. Otherwise, add it.
// sql = "SELECT * FROM ClientPlan WHERE ClientID = " + clsSQL.ToSql(temp.ID) + " AND PlanID = " + clsSQL.ToSql(planID);
//cmd = new OleDbCommand(sql, mDB);
// OleDbDataReader rdr = cmd.ExecuteReader();
//if (rdr.Read() == true) // no need to add a record
// {
// rdr.Close();
// }
// else // need to add the record
// {
// rdr.Close();
// sql = "INSERT INTO ClientPlan (ClientID, PlanID) VALUES (" +
// clsSQL.ToSql(temp.ID) + ", " +
// clsSQL.ToSql(planID) + ")";
// cmd = new OleDbCommand(sql, mDB);
// cmd.ExecuteNonQuery();
// }
}
catch (Exception ex)
{
ShowMessage("There was an unexpected problem: " + ex.Message);
}
finally
{
closeDatabaseConnection();
}
// Erase the input values, notify the user, and display the current client roster
eraseInputFields();
ShowMessage(temp.NameofProgram + " has been updated.");
loadClients("SELECT * FROM DiversityProgram");
displayData();
}
// When the Update button is clicked, the user is able to modify the data
// for the selected client.
private void btnUpdate_Click(object sender, EventArgs e)
{
// Define all variables.
int age;
double height;
double startWeight;
double goalWeight;
int totalWeeks;
int trainerID;
int planID;
string sql;
int selectedClient = lstSchools.SelectedIndex - 2;
// Make sure a valid client was selected.
if (selectedClient < 0 || selectedClient >= mClients.Count)
{
ShowMessage("Please select a valid client in the listbox.");
lstSchools.SelectedIndex = -1;
return;
}
// Get the ID of the selected client to fill in the input boxes
clsClients temp = (clsClients)mClients[selectedClient];
// Validate the user's input.
if (txtFirstName.Text == "")
{
ShowMessage("Please enter the client's first name");
txtFirstName.Focus();
return;
}
if (txtLastName.Text == "")
{
ShowMessage("Please enter the client's last name");
txtLastName.Focus();
return;
}
if (validateInput(txtAge, 16, 100, out age) == false)
{
return;
}
if (validateInput(txtHeight, 36.0, 80.5, out height) == false)
{
return;
}
if (validateInput(txtCurrentWeight, 60.0, 500.0, out startWeight) == false)
{
return;
}
if (validateInput(txtGoalWeight, 90.0, 300.0, out goalWeight) == false)
{
return;
}
if (validateInput(txtTotalWeeks, 1, 36, out totalWeeks) == false)
{
return;
}
if (cboTrainer.SelectedIndex < 0)
{
ShowMessage("Please assign a trainer");
cboTrainer.Focus();
return;
}
clsComboBoxItem trainer = (clsComboBoxItem)cboTrainer.SelectedItem;
trainerID = (int)trainer.Value;
if (cboExercisePlan.SelectedIndex < 0)
{
ShowMessage("Please assign an Exercise Plan");
cboExercisePlan.Focus();
return;
}
clsComboBoxItem plan = (clsComboBoxItem)cboExercisePlan.SelectedItem;
planID = (int)plan.Value;
// Data is valid so modify the client and reload the data from the DB
try
{
// Change a record in the Clients table.
openDatabaseConnection();
mDB.Open();
OleDbCommand cmd;
sql = "UPDATE Clients SET FirstName = " + clsSQL.ToSql(txtFirstName.Text) +
", Lastname = " + clsSQL.ToSql(txtLastName.Text) +
", Age = " + clsSQL.ToSql(age) +
", HeightInches = " + clsSQL.ToSql(height) +
", StartWeight = " + clsSQL.ToSql(startWeight) +
", GoalWeight = " + clsSQL.ToSql(goalWeight) +
", Weeks = " + clsSQL.ToSql(totalWeeks) +
" WHERE ID = " + clsSQL.ToSql(temp.ID);
cmd = new OleDbCommand(sql, mDB);
cmd.ExecuteNonQuery();
// If the client-plan already exists, ignore. Otherwise, add it.
sql = "SELECT * FROM ClientPlan WHERE ClientID = " + clsSQL.ToSql(temp.ID) + " AND PlanID = " + clsSQL.ToSql(planID);
cmd = new OleDbCommand(sql, mDB);
OleDbDataReader rdr = cmd.ExecuteReader();
if (rdr.Read() == true) // no need to add a record
{
rdr.Close();
}
else // need to add the record
{
rdr.Close();
sql = "INSERT INTO ClientPlan (ClientID, PlanID) VALUES (" +
clsSQL.ToSql(temp.ID) + ", " +
clsSQL.ToSql(planID) + ")";
cmd = new OleDbCommand(sql, mDB);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
ShowMessage("There was an unexpected problem: " + ex.Message);
}
finally
{
closeDatabaseConnection();
}
// Erase the input values, notify the user, and display the current client roster
eraseInputFields();
ShowMessage(temp.FirstName + "'s record has been updated.");
loadClients("SELECT * FROM Clients");
displayData();
}
// Revise the selected items in the two combo boxes when a name is selected.
private void lstClients_Click(object sender, EventArgs e)
{
ArrayList planID = new ArrayList();
int selectedClient = lstSchools.SelectedIndex - 2;
// Make sure a valid client was selected.
if (selectedClient < 0 || selectedClient >= mClients.Count)
{
ShowMessage("Please select a valid client in the listbox.");
lstSchools.SelectedIndex = -1;
return;
}
// Get the ID of the selected client to fill in the input boxes
clsClients temp = (clsClients)mClients[selectedClient];
txtFirstName.Text = temp.FirstName;
txtLastName.Text = temp.LastName;
txtAge.Text = temp.Age.ToString();
txtHeight.Text = temp.Height.ToString();
txtCurrentWeight.Text = temp.StartWeight.ToString();
txtGoalWeight.Text = temp.GoalWeight.ToString();
txtTotalWeeks.Text = temp.Weeks.ToString();
// Search for the matching plan of the selected client
string sql = "SELECT * FROM ClientPlan WHERE ClientID=" + clsSQL.ToSql(temp.ID);
try
{
openDatabaseConnection();
mDB.Open();
OleDbCommand cmd = new OleDbCommand(sql, mDB);
OleDbDataReader rdr = cmd.ExecuteReader();
while (rdr.Read() == true)
{
planID.Add(rdr["PlanID"]);
}
rdr.Close();
}
catch (Exception ex)
{
ShowMessage("There was an unexpected problem when adding the client: " + ex.Message + ex.StackTrace);
}
finally
{
closeDatabaseConnection();
}
// Rebuild the trainer and exercise combo boxes, selecting one plan/trainer for the client.
buildExerciseCombo();
buildTrainerCombo();
for (int i = 0; i < cboExercisePlan.Items.Count; i++)
{
clsComboBoxItem item = (clsComboBoxItem)cboExercisePlan.Items[i];
if (planID.Contains(item.Value))
{
cboExercisePlan.SelectedIndex = i;
break;
}
}
// Make sure no trainer is selected.
cboTrainer.SelectedIndex = -1;
// Notify the user of the possible actions
ShowMessage("To delete " + temp.FirstName + " " + temp.LastName + " as a client, press the delete key. \n" +
"To modify " + temp.FirstName + "'s data, edit the data in the input fields, select a trainer, select an exercise plan, and click Update.");
}
