/// <summary>
/// A method to populate data in the coach availability data grid
/// </summary>
private void PopulateScheduleGrid()
{
// Query obtains the interest ID from the combo box.
string interestId = this.cbxChooseDepartment.SelectedValue.ToString();
try
{
using (var context = new db_sft_2172Entities())
{
// Run stored procedure to build schedule in the database
context.GenerateSchedule(interestId);
// Run a query to get the new schedule
var interestScheduleQuery = from interestSchedules in context.SchedulePivots
select interestSchedules;
// Display the schedule to the Data Grid View
this.dataGridViewSchedule.DataSource = interestScheduleQuery.ToList();
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Method to display the list of courses
/// </summary>
private void DisplayCourses()
{
try
{
using (var context = new db_sft_2172Entities())
{
// Query user table in database and returns the list of the users in ascending order according to last name
var courseQuery = from courses in context.CourseListings
orderby courses.CourseID ascending
select courses;
// Convert query results to list
List <CourseListing> courseList = courseQuery.ToList();
// Set combo box data source and update data member settings
this.cbxChooseCourse.DataSource = courseList;
this.cbxChooseCourse.ValueMember = "CourseID";
this.cbxChooseCourse.DisplayMember = "CourseListID";
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Method to display the list of interests
/// </summary>
private void DisplayInterests()
{
try
{
using (var context = new db_sft_2172Entities())
{
// Query interest table in database and returns the list of the interests in ascending order
var interestQuery = from interests in context.Interests
orderby interests.InterestName
select interests;
// Convert query results to list
List <Interest> interestList = interestQuery.ToList();
// Set combo box data sources and update data member settings
this.cbxChooseDepartment.DataSource = interestList;
this.cbxChooseDepartment.ValueMember = "InterestName";
this.cbxChooseDepartment.DisplayMember = "InterestName";
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// A method to populate data in the coach availability data grid
/// </summary>
private void PopulateAvailabilityGrid()
{
// Query obtains the coach ID from the combo box.
string coachId = this.cbxChooseCoach.SelectedValue.ToString();
try
{
using (var context = new db_sft_2172Entities())
{
var coachAvailabilityQuery = from coachAvailability in context.GetCoachAvailabilities
where coachAvailability.CoachID.Equals(coachId)
select coachAvailability;
this.dataGridViewAvailability.DataSource = coachAvailabilityQuery.ToList();
this.dataGridViewAvailability.Columns["CoachID"].Visible = false;
this.dataGridViewAvailability.Columns["CoachAvailabilityID"].Visible = false;
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Method to display the list of users
/// </summary>
private void DisplayUsers()
{
try
{
using (var context = new db_sft_2172Entities())
{
// Query user table in database and returns the list of the users in ascending order according to last name
var userQuery = from users in context.Users
orderby users.LastName
select users;
// Convert query results to list
List <User> userList = userQuery.ToList();
// Set combo box data source and update data member settings
this.cbxChooseUser.DataSource = userList;
this.cbxChooseUser.ValueMember = "UserID";
this.cbxChooseUser.DisplayMember = "DisplayName";
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Method to obtain the current coach's list of courses.
/// This list does not change until the coach profile is saved
/// </summary>
private void GetCoachSelectedCourses()
{
try
{
using (var context = new db_sft_2172Entities())
{
string coachId = this.cbxChooseCoach.SelectedValue.ToString();
var selectedCoursesQuery = from courses in context.ViewCoachCourses
where courses.CoachID == coachId
orderby courses.CourseName
select courses;
// Convert query results to lists and store in object fields
this.coachSelectedCourseList = new BindingList <ViewCoachCours>(selectedCoursesQuery.ToList());
this.currentSelectedCourseList = new BindingList <ViewCoachCours>(selectedCoursesQuery.ToList());
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Method to create a new session in the database
/// </summary>
private void AddNewSession()
{
// Add new user to the database and save changes
try
{
using (var context = new db_sft_2172Entities())
{
// Run query and save new user data to database
context.CoachSessions.Add(this.CurrentSession);
context.SaveChanges();
}
}
catch (DbUpdateException dbUEx)
{
MessageBox.Show(dbUEx.InnerException != null ? dbUEx.InnerException.Message : dbUEx.Message);
return;
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
return;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
MessageBox.Show(@"Save completed...new session created successfully!");
}
/// <summary>
/// Method to display the list of users
/// </summary>
private void DisplayDepartments()
{
try
{
using (var context = new db_sft_2172Entities())
{
// Query user table in database and returns the list of the users in ascending order according to last name
var departmentQuery = from departments in context.Departments
orderby departments.DepartmentName
select departments;
// Convert query results to list
List <Department> departmentList = departmentQuery.ToList();
// Set combo box data source and update data member settings
this.cbxDepartment.DataSource = departmentList;
this.cbxDepartment.ValueMember = "DepartmentID";
this.cbxDepartment.DisplayMember = "DepartmentName";
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Method to display the list of supervisors in a combo box
/// </summary>
private void DisplaySupervisors()
{
try
{
using (var context = new db_sft_2172Entities())
{
// Query user table in database and return the list of supervisors in ascending order according to last name
var supervisorQuery = from users in context.Users
where users.IsSupervisor
orderby users.LastName
select users;
List <User> supervisorList = supervisorQuery.ToList();
// Set combo box data source and update data member listings
this.cbxSupervisor.DataSource = supervisorList;
this.cbxSupervisor.ValueMember = "UserID";
this.cbxSupervisor.DisplayMember = "DisplayName";
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// A method to populate data in the coach availability data grid
/// </summary>
private void PopulateScheduleGrid()
{
// Query obtains the coach ID from the combo box.
string coachId = this.cbxChooseCoach.SelectedValue.ToString();
try
{
using (var context = new db_sft_2172Entities())
{
var coachScheduleQuery = from coachSchedules in context.GetCoachSchedules
where coachSchedules.CoachID.Equals(coachId)
select coachSchedules;
this.dataGridViewSchedule.DataSource = coachScheduleQuery.ToList();
// ReSharper disable once PossibleNullReferenceException
this.dataGridViewSchedule.Columns["SessionID"].Visible = false;
this.dataGridViewSchedule.Columns["CoachID"].Visible = false;
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Event handler to click to save the new password into the database
/// </summary>
/// <param name="sender">The parameter is not used.</param>
/// <param name="e">The parameter is not used.</param>
private void BtnSaveNewPasswordClick(object sender, EventArgs e)
{
try
{
using (var context = new db_sft_2172Entities())
{
var userQuery = from u in context.Users
where u.UserID.Equals(Program.CurrentUser)
select u;
var userResult = userQuery.FirstOrDefault();
if (SaltedHash.Verify(userResult.PasswordSalt, userResult.Password, this.txtCurrentPassword.Text))
{
if (!string.IsNullOrEmpty(this.txtNewPassword.Text) ||
!string.IsNullOrEmpty(this.txtConfirmPassword.Text))
{
if (this.txtNewPassword.Text == this.txtConfirmPassword.Text)
{
// Generate salt and salted hash
SaltedHash sh = new SaltedHash(this.txtNewPassword.Text);
userResult.Password = sh.Hash;
userResult.PasswordSalt = sh.Salt;
userResult.ResetPassword = null;
context.SaveChanges();
this.txtCurrentPassword.Text = string.Empty;
this.txtNewPassword.Text = string.Empty;
this.txtConfirmPassword.Text = string.Empty;
MessageBox.Show(@"Your passsword has been saved!");
this.Close();
}
else
{
MessageBox.Show(@"Passwords do not match!");
}
}
else
{
MessageBox.Show(@"New password or confirm password is empty!");
}
}
else
{
MessageBox.Show(@"Your current password is incorrect!");
}
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// A button to allow users to cancel changes and close the current window.
/// If any changes were made, an alert will display and allow the user to back out.
/// </summary>
/// <param name="sender">The parameter is not used.</param>
/// <param name="e">The parameter is not used.</param>
private void BtnCancel_Click(object sender, EventArgs e)
{
try
{
// Query updates the user in the database
using (var context = new db_sft_2172Entities())
{
string userId = this.txtID.Text;
var userQuery = from user in context.Users
where user.UserID.Equals(userId)
select user;
if (userQuery.Any())
{
var userResult = userQuery.FirstOrDefault();
if (userResult.FirstName != this.txtFirstName.Text ||
userResult.MiddleName != this.txtMiddleName.Text ||
userResult.LastName != this.txtLastName.Text ||
userResult.DisplayName != this.txtDisplayName.Text ||
userResult.Phone != this.txtPhone.Text ||
userResult.Email != this.txtEmail.Text ||
userResult.IsActive != this.chkActive.Checked ||
userResult.IsSupervisor != this.chkSupervisor.Checked ||
userResult.IsAdmin != this.chkAdmin.Checked)
{
DialogResult cancelChoice = MessageBox.Show(
"Closing this window will remove all changes. Do you want to continue?",
"Cancel",
MessageBoxButtons.YesNo,
MessageBoxIcon.Warning);
if (cancelChoice == DialogResult.No)
{
return;
}
}
}
}
this.Close();
}
catch (DbUpdateException dbUEx)
{
MessageBox.Show(dbUEx.InnerException != null ? dbUEx.InnerException.Message : dbUEx.Message);
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Method to update an existing session in the database
/// </summary>
private void UpdateAvailability()
{
// Add new user to the database and save changes
try
{
using (var context = new db_sft_2172Entities())
{
// Run query and pull matching session from database
var availabilityQuery = from availability in context.CoachAvailabilities
where availability.CoachAvailabilityID.Equals(this.CoachAvailabilityId)
select availability;
if (availabilityQuery.Any())
{
CoachAvailability foundAvailability = availabilityQuery.FirstOrDefault();
// Update database records
if (foundAvailability != null)
{
foundAvailability.CoachID = this.CurrentAvailability.CoachID;
foundAvailability.DayID = this.CurrentAvailability.DayID;
foundAvailability.StartTime = this.CurrentAvailability.StartTime;
foundAvailability.EndTime = this.CurrentAvailability.EndTime;
}
// Save changes to database
context.SaveChanges();
}
else
{
MessageBox.Show(
@"Sorry, a matching session was not found in the database.\nPlease try again or contact an administrator for assistance.");
}
}
}
catch (DbUpdateException dbUEx)
{
MessageBox.Show(dbUEx.InnerException != null ? dbUEx.InnerException.Message : dbUEx.Message);
return;
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
return;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
MessageBox.Show(@"Save completed...Session updated successfully!");
}
/// <summary>
/// Method to update an existing session in the database
/// </summary>
private void UpdateSession()
{
// Add new session to the database and save changes
try
{
using (var context = new db_sft_2172Entities())
{
// Run query and pull matching session from database
var sessionQuery = from session in context.CoachSessions
where session.SessionID.Equals(this.CurrentSession.SessionID)
select session;
if (sessionQuery.Any())
{
CoachSession foundSession = sessionQuery.FirstOrDefault();
// Update database records
foundSession.DayID = this.CurrentSession.DayID;
foundSession.StartTime = this.CurrentSession.StartTime;
foundSession.EndTime = this.CurrentSession.EndTime;
foundSession.CoachID = this.CurrentSession.CoachID;
foundSession.Active = this.CurrentSession.Active;
// Save changes to database
context.SaveChanges();
}
else
{
MessageBox.Show(
@"Sorry, the desired session was not found in the database."
+ Environment.NewLine + @"Please try again later or contact an administrator for assistance.");
}
}
}
catch (DbUpdateException dbUEx)
{
MessageBox.Show(dbUEx.InnerException != null ? dbUEx.InnerException.Message : dbUEx.Message);
return;
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
return;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
MessageBox.Show(@"Save completed...Session updated successfully!");
}
/// <summary>
/// Method to query the database and populate the combo boxes with valid items
/// </summary>
private void PopulateComboBoxes()
{
try
{
using (var context = new db_sft_2172Entities())
{
// Run queries to get combo box data
var dayQuery = from days in context.Days
orderby days.SortOrder
select days;
var timeQuery = from times in context.Times
orderby times.Time1
select times;
// Convert query results to lists
List <Day> dayList = dayQuery.ToList();
List <Time> startTimeList = timeQuery.ToList();
List <Time> endTimeList = timeQuery.ToList();
// Set combo box data sources and update data member settings
this.cbxDay.DataSource = dayList;
this.cbxDay.ValueMember = "DayID";
this.cbxDay.DisplayMember = "DayID";
this.cbxStartTime.DataSource = startTimeList;
this.cbxStartTime.ValueMember = "Time1";
this.cbxStartTime.DisplayMember = "TimeName";
this.cbxEndTime.DataSource = endTimeList;
this.cbxEndTime.ValueMember = "Time1";
this.cbxEndTime.DisplayMember = "TimeName";
this.cbxDay.SelectedIndex = -1;
this.cbxStartTime.SelectedIndex = -1;
this.cbxEndTime.SelectedIndex = -1;
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Event handler to delete the selected Availability record when the Remove button is clicked
/// </summary>
/// <param name="sender">The parameter is not used.</param>
/// <param name="e">The parameter is not used.</param>
private void BtnRemove_Click(object sender, EventArgs e)
{
// Determine which row is selected
int selectedAvailabilityId = Convert.ToInt32(this.dataGridViewAvailability.SelectedRows[0].Cells["CoachAvailabilityID"].Value.ToString());
// Confirm whether user truly wants to remove this availability record
DialogResult confirmRemove = MessageBox.Show(
@"Are you sure you want to remove this record?",
@"Confirm delete",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
// If yes, remove the record
if (confirmRemove.Equals(DialogResult.Yes))
{
var availability = new CoachAvailability {
CoachAvailabilityID = selectedAvailabilityId
};
try
{
using (var context = new db_sft_2172Entities())
{
context.CoachAvailabilities.Attach(availability);
context.CoachAvailabilities.Remove(availability);
context.SaveChanges();
MessageBox.Show(@"Delete successful!");
this.PopulateAvailabilityGrid();
}
}
catch (DbUpdateException dbUEx)
{
MessageBox.Show(dbUEx.InnerException != null ? dbUEx.InnerException.Message : dbUEx.Message);
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
/// <summary>
/// Populates the combo boxes and text boxes with the selected user's information
/// </summary>
/// <param name="sender">The parameter is not used.</param>
/// <param name="e">The parameter is not used.</param>
private void CbxChooseUser_SelectedIndexChanged(object sender, EventArgs e)
{
if (this.cbxChooseUser.SelectedIndex == -1)
{
return;
}
try
{
using (var context = new db_sft_2172Entities())
{
// Query obtains the user ID from the combo box
string userId = this.cbxChooseUser.SelectedValue.ToString();
// Find the user in the database
var userQuery = from user in context.Users
where user.UserID.Equals(userId)
select user;
// If the query returns a user, display the corresponding info in the form
if (userQuery.Any())
{
var userResult = userQuery.FirstOrDefault();
if (userResult != null)
{
this.txtID.Text = userResult.UserID;
this.txtFirstName.Text = userResult.FirstName;
this.txtLastName.Text = userResult.LastName;
this.txtMiddleName.Text = userResult.MiddleName;
this.txtDisplayName.Text = userResult.DisplayName;
this.txtEmail.Text = userResult.Email;
this.txtPhone.Text = userResult.Phone;
this.chkActive.Checked = userResult.IsActive;
this.chkAdmin.Checked = userResult.IsAdmin;
this.chkSupervisor.Checked = userResult.IsSupervisor;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Determine whether the current coach profile has been modified.
/// If yes, update a boolean value stored in the Form object.
/// </summary>
private void CheckForModifiedProfile()
{
try
{
// Query updates the user in the database
using (var context = new db_sft_2172Entities())
{
string coachId = this.txtID.Text;
var coachQuery = from coach in context.Coaches
where coach.CoachID.Equals(coachId)
select coach;
if (coachQuery.Any())
{
var coachResult = coachQuery.FirstOrDefault();
if (coachResult.FirstName != this.txtFirstName.Text ||
coachResult.MiddleName != this.txtMiddleName.Text ||
coachResult.LastName != this.txtLastName.Text ||
coachResult.DisplayName != this.txtDisplayName.Text ||
coachResult.Phone != this.txtPhone.Text ||
coachResult.Email != this.txtEmail.Text ||
coachResult.IsActive != this.chkActive.Checked ||
coachResult.SupervisorID != this.cbxSupervisor.SelectedValue.ToString())
{
this.isCoachModified = true;
}
}
}
}
catch (DbUpdateException dbUEx)
{
MessageBox.Show(dbUEx.InnerException != null ? dbUEx.InnerException.Message : dbUEx.Message);
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Populates the combo boxes and text boxes with the selected user's information
/// </summary>
/// <param name="sender">The parameter is not used.</param>
/// <param name="e">The parameter is not used.</param>
private void CbxChooseCourse_SelectedIndexChanged(object sender, EventArgs e)
{
if (this.cbxChooseCourse.SelectedIndex == -1)
{
return;
}
try
{
using (var context = new db_sft_2172Entities())
{
// Query obtains the user ID from the combo box
string courseId = this.cbxChooseCourse.SelectedValue.ToString();
// Find the user in the database
var courseQuery = from course in context.Courses
where course.CourseID.Equals(courseId)
select course;
// If the query returns a user, display the corresponding info in the form
if (courseQuery.Any())
{
var courseResult = courseQuery.FirstOrDefault();
if (courseResult != null)
{
this.txtCourseID.Text = courseResult.CourseID;
this.txtCourseName.Text = courseResult.CourseName;
this.chkActive.Checked = courseResult.IsActive;
this.cbxDepartment.SelectedValue = courseResult.DepartmentID;
}
}
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Method to query the database and display data for the desired session
/// </summary>
private void LoadSessionData()
{
try
{
using (var context = new db_sft_2172Entities())
{
var sessionQuery = from session in context.CoachSessions
where session.SessionID.Equals(this.CurrentSession.SessionID)
select session;
if (sessionQuery.Any())
{
this.CurrentSession = sessionQuery.FirstOrDefault();
}
else
{
MessageBox.Show(@"Sorry, could not load session data from the database. Please try again later.");
this.Close();
}
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
return;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
// Populate combo boxes with current session data
this.cbxCoach.SelectedValue = this.CurrentSession.CoachID;
this.cbxDay.SelectedValue = this.CurrentSession.DayID;
this.cbxStartTime.SelectedValue = this.CurrentSession.StartTime;
this.cbxEndTime.SelectedValue = this.CurrentSession.EndTime;
this.cbxActive.SelectedIndex = this.CurrentSession.Active ? 0 : 1;
}
/// <summary>
/// Method to query the database and display data for the desired availability block
/// </summary>
private void LoadSessionData()
{
try
{
using (var context = new db_sft_2172Entities())
{
var availabilityQuery = from availability in context.CoachAvailabilities
where availability.CoachAvailabilityID.Equals(this.CoachAvailabilityId)
select availability;
if (availabilityQuery.Any())
{
this.CurrentAvailability = availabilityQuery.FirstOrDefault();
}
else
{
MessageBox.Show(@"Sorry, could not load availability data from the database. Please try again later.");
return;
}
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
return;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
// Populate combo boxes with current session data
if (this.CurrentAvailability != null)
{
this.cbxDay.SelectedValue = this.CurrentAvailability.DayID;
this.cbxStartTime.SelectedValue = this.CurrentAvailability.StartTime;
this.cbxEndTime.SelectedValue = this.CurrentAvailability.EndTime;
}
}
/// <summary>
/// Method to display the list of unselected courses
/// </summary>
private void DisplayUnselectedCourses()
{
try
{
using (var context = new db_sft_2172Entities())
{
string departmentId = this.cbxDepartment.SelectedValue.ToString();
var departmentCoursesQuery = from courses in context.ViewDepartmentCourses
where courses.DepartmentID == departmentId
orderby courses.CourseName
select courses;
var currentSelectedCoursesQuery = from courses in this.currentSelectedCourseList
orderby courses.CourseName
select courses.CourseID;
var unselectedCoursesQuery = from deptCourses in departmentCoursesQuery
where !currentSelectedCoursesQuery.Contains(deptCourses.CourseID)
select deptCourses;
// Convert query results to list
this.unselectedCourseList =
new BindingList <ViewDepartmentCours>(unselectedCoursesQuery.ToList());
// Set list box data source and update data member settings
this.lstUnselectedCourses.DataSource = this.unselectedCourseList;
this.lstUnselectedCourses.ValueMember = "CourseID";
this.lstUnselectedCourses.DisplayMember = "CourseName";
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Event handler to set temporary access code
/// </summary>
/// <param name="sender">The parameter is not used.</param>
/// <param name="e">The parameter is not used.</param>
private void BtnUpdateClick(object sender, EventArgs e)
{
// Verify that the two entered passwords match
if (!this.txtTempCode.Text.Equals(this.txtConfirmTempCode.Text))
{
MessageBox.Show(@"Sorry, the temporary passwords do not match. Please try again!");
// Clear the password boxes
this.txtTempCode.Text = string.Empty;
this.txtConfirmTempCode.Text = string.Empty;
this.txtTempCode.Focus();
}
else if (this.txtTempCode.Text.Equals(string.Empty))
{
MessageBox.Show(@"Please enter a temporary password.");
// Clear the password boxes
this.txtTempCode.Text = string.Empty;
this.txtConfirmTempCode.Text = string.Empty;
this.txtTempCode.Focus();
}
else
{
// Find current user, then update password in database
try
{
using (var context = new db_sft_2172Entities())
{
// Run query to get user data
var userQuery = from users in context.Users
where users.UserID.Equals(this.CurrentUserId)
select users;
User currentUser = userQuery.FirstOrDefault();
if (currentUser != null)
{
// Generate salt and salted hash
SaltedHash sh = new SaltedHash(this.txtTempCode.Text);
currentUser.Password = sh.Hash;
currentUser.PasswordSalt = sh.Salt;
currentUser.ResetPassword = "******";
context.SaveChanges();
// Show confirmation if save is successful
MessageBox.Show(@"Temporary password updated successfully!");
}
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
// Close the form when finished
this.Close();
}
}
/// <summary>
/// Submit button sends the added or updated info to the database.
/// </summary>
/// <param name="sender">The parameter is not used.</param>
/// <param name="e">The parameter is not used.</param>
private void BtnSubmitClick(object sender, EventArgs e)
{
try
{
// Run query to check for a corresponding user in the database
using (var context = new db_sft_2172Entities())
{
string courseId = this.txtCourseID.Text;
var courseQuery = from course in context.Courses
where course.CourseID.Equals(courseId)
select course;
if (courseQuery.Any())
{
var courseResult = courseQuery.FirstOrDefault();
if (courseResult != null)
{
courseResult.CourseID = this.txtCourseID.Text;
courseResult.CourseName = this.txtCourseName.Text;
Department selectedDepartment = (Department)this.cbxDepartment.SelectedItem;
courseResult.DepartmentID = selectedDepartment.DepartmentID;
}
context.SaveChanges();
this.DisplayCourses();
MessageBox.Show(@"Course Updated");
}
else
{
Course newCourse = new Course
{
CourseID = this.txtCourseID.Text,
CourseName = this.txtCourseName.Text,
Department = (Department)this.cbxDepartment.SelectedItem,
IsActive = true
};
context.Courses.Add(newCourse);
context.SaveChanges();
MessageBox.Show(@"Course Added");
// If save is successful, update the user list and display the new user profile
this.DisplayCourses();
this.cbxChooseCourse.SelectedValue = newCourse.CourseID;
}
}
}
catch (DbUpdateException dbUEx)
{
MessageBox.Show(dbUEx.InnerException != null ? dbUEx.InnerException.Message : dbUEx.Message);
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.txtCourseID.Enabled = false;
}
/// <summary>
/// Event handler to save session data and close form when the Save button is clicked
/// </summary>
/// <param name="sender">The parameter is not used.</param>
/// <param name="e">The parameter is not used.</param>
private void BtnSaveClick(object sender, EventArgs e)
{
if (this.CurrentSession == null)
{
this.CurrentSession = new CoachSession();
}
// Get data from form and insert into current Session object
this.CurrentSession.DayID = this.cbxDay.SelectedValue.ToString();
this.CurrentSession.CoachID = this.cbxCoach.SelectedValue.ToString();
// Get data from form and insert into current Availability object
this.CurrentSession.DayID = this.cbxDay.SelectedValue.ToString();
// Get selected start time as a TimeSpan
Time selectedStartTime = (Time)this.cbxStartTime.SelectedItem;
this.CurrentSession.StartTime = selectedStartTime.Time1;
// Get selected end time as a TimeSpan
Time selectedEndTime = (Time)this.cbxEndTime.SelectedItem;
this.CurrentSession.EndTime = selectedEndTime.Time1;
// Update active status based on selected value
this.CurrentSession.Active = this.cbxActive.SelectedIndex == 0;
// Verify that the selected coach has availablility on the desired day/time
try
{
using (var context = new db_sft_2172Entities())
{
var availableCoachSessions = from coachAvailability in context.CoachAvailabilities
where
coachAvailability.CoachID.Equals(this.CurrentSession.CoachID) &&
coachAvailability.DayID.Equals(this.CurrentSession.DayID) &&
coachAvailability.StartTime <= this.CurrentSession.StartTime &&
coachAvailability.EndTime >= this.CurrentSession.EndTime
select coachAvailability;
if (availableCoachSessions.Any())
{
// Call appropriate method to add or update session data
if (this.SessionId == -1)
{
this.AddNewSession();
}
else
{
this.UpdateSession();
}
}
else
{
MessageBox.Show(
@"Sorry, the desired coach is not available for the selected day/time."
+ Environment.NewLine + @"Please select another combination!");
return;
}
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
// Close form once finished
this.Close();
}
/// <summary>
/// Submit button sends the added or updated info to the database.
/// </summary>
/// <param name="sender">The parameter is not used.</param>
/// <param name="e">The parameter is not used.</param>
private void BtnSubmitClick(object sender, EventArgs e)
{
List <ViewCoachCours> addList = new List <ViewCoachCours>();
List <ViewCoachCours> removeList = new List <ViewCoachCours>();
// Loop through selected courses and determine whether selected courses
// need to be added to the list
foreach (ViewCoachCours selectedCourse in this.currentSelectedCourseList)
{
bool isCourseSelected = false;
foreach (ViewCoachCours coachCourse in this.coachSelectedCourseList)
{
if (coachCourse.CourseID.Equals(selectedCourse.CourseID))
{
isCourseSelected = true;
break;
}
}
if (!isCourseSelected)
{
addList.Add(selectedCourse);
}
}
// Loop through the originally selected courses and determine whether
// any were removed
foreach (ViewCoachCours coachCourse in this.coachSelectedCourseList)
{
bool isCourseSelected = false;
foreach (ViewCoachCours selectedCourse in this.currentSelectedCourseList)
{
if (coachCourse.CourseID.Equals(selectedCourse.CourseID))
{
isCourseSelected = true;
break;
}
}
if (!isCourseSelected)
{
removeList.Add(coachCourse);
}
}
try
{
// Query updates the user in the database
using (var context = new db_sft_2172Entities())
{
string coachId = this.txtID.Text;
var coachQuery = from coach in context.Coaches
where coach.CoachID.Equals(coachId)
select coach;
if (coachQuery.Any())
{
var coachResult = coachQuery.FirstOrDefault();
coachResult.FirstName = this.txtFirstName.Text;
coachResult.MiddleName = this.txtMiddleName.Text;
coachResult.LastName = this.txtLastName.Text;
coachResult.DisplayName = this.txtDisplayName.Text;
coachResult.Phone = this.txtPhone.Text;
coachResult.Email = this.txtEmail.Text;
coachResult.IsActive = this.chkActive.Checked;
coachResult.SupervisorID = this.cbxSupervisor.SelectedIndex == -1
? string.Empty
: this.cbxSupervisor.SelectedValue.ToString();
foreach (ViewCoachCours course in addList)
{
CoachCourse newCourse = new CoachCourse
{
CoachID = this.txtID.Text,
CourseID = course.CourseID,
Active = true
};
context.CoachCourses.Add(newCourse);
}
foreach (ViewCoachCours course in removeList)
{
CoachCourse dropCourse = new CoachCourse
{
CoachID = this.txtID.Text,
CourseID = course.CourseID
};
context.CoachCourses.Attach(dropCourse);
context.CoachCourses.Remove(dropCourse);
}
context.SaveChanges();
MessageBox.Show(@"Coach Profile Updated");
this.GetCoachSelectedCourses();
this.DisplaySelectedCourses();
this.DisplayUnselectedCourses();
}
else
{
Coach newCoach = new Coach
{
CoachID = this.txtID.Text,
FirstName = this.txtFirstName.Text,
MiddleName = this.txtMiddleName.Text,
LastName = this.txtLastName.Text,
DisplayName = this.txtDisplayName.Text,
Phone = this.txtPhone.Text,
Email = this.txtEmail.Text,
IsActive = this.chkActive.Checked,
SupervisorID =
this.cbxSupervisor.SelectedIndex == -1
? string.Empty
: this.cbxSupervisor.SelectedValue.ToString()
};
foreach (ViewCoachCours course in addList)
{
CoachCourse newCourse = new CoachCourse
{
CoachID = this.txtID.Text,
CourseID = course.CourseID,
Active = true
};
context.CoachCourses.Add(newCourse);
}
foreach (ViewCoachCours course in removeList)
{
CoachCourse dropCourse = new CoachCourse
{
CoachID = this.txtID.Text,
CourseID = course.CourseID
};
context.CoachCourses.Attach(dropCourse);
context.CoachCourses.Remove(dropCourse);
}
context.Coaches.Add(newCoach);
context.SaveChanges();
MessageBox.Show(@"Coach Profile Added");
// If save is successful, update the coach list and display the new coach profile
this.DisplayCoaches();
this.cbxChooseCoach.SelectedValue = newCoach.CoachID;
this.txtID.Enabled = false;
this.GetCoachSelectedCourses();
this.DisplaySelectedCourses();
this.DisplayUnselectedCourses();
}
}
}
catch (DbUpdateException dbUEx)
{
MessageBox.Show(dbUEx.InnerException != null ? dbUEx.InnerException.Message : dbUEx.Message);
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Method to match password with the database (using salted hash), then login and load the role form
/// </summary>
/// <param name="username">The username string entered by the user</param>
/// <param name="password">The password string entered by the user</param>
private void Login(string username, string password)
{
try
{
using (db_sft_2172Entities context = new db_sft_2172Entities())
{
var userQuery = from u in context.Users
where u.UserID.Equals(username)
select u;
if (userQuery.Any())
{
var userResult = userQuery.FirstOrDefault();
// Determine whether user is active. If not, display a message and Logout.
if (!userResult.IsActive)
{
MessageBox.Show(
@"Sorry, this user is inactive. Please contact an administrator if you need to reactivate your account.");
Program.Logout();
return;
}
/*************************************************************/
/** Applying salted hash technique to verify password **/
/** **/
/** If you wish to use a non-encrypted password, uncomment **/
/** the first "if" statement below **/
/** Otherwise, uncomment the second "if" to use encryption. **/
/*************************************************************/
if (SaltedHash.Verify(userResult.PasswordSalt, userResult.Password, password))
{
// Update static variable containing User ID
Program.CurrentUser = userResult.UserID;
// If flag is set to reset password, load the Change Password form.
if (userResult.ResetPassword != null)
{
MessageBox.Show(
@"Your password is outdated and needs to be changed. Please reset your password now.");
ResetMyPassword changePassword = new ResetMyPassword();
changePassword.ShowDialog();
}
else
{
// If any of these three values are true, update static variables
if (userResult.IsSupervisor)
{
Program.IsSupervisor = true;
}
if (userResult.IsAdmin)
{
Program.IsAdmin = true;
}
// Close window once finished
this.Close();
}
}
else
{
MessageBox.Show(@"Sorry, invalid username or password. Please try again!");
this.txtUsername.Text = string.Empty;
this.txtPassword.Text = string.Empty;
this.txtUsername.Focus();
}
}
else
{
MessageBox.Show(@"Sorry, invalid username or password. Please try again!");
this.txtUsername.Text = string.Empty;
this.txtPassword.Text = string.Empty;
this.txtUsername.Focus();
}
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Submit button sends the added or updated info to the database.
/// </summary>
/// <param name="sender">The parameter is not used.</param>
/// <param name="e">The parameter is not used.</param>
private void BtnSubmitClick(object sender, EventArgs e)
{
try
{
// Run query to check for a corresponding user in the database
using (var context = new db_sft_2172Entities())
{
string userId = this.txtID.Text;
var userQuery = from user in context.Users
where user.UserID.Equals(userId)
select user;
if (userQuery.Any())
{
var userResult = userQuery.FirstOrDefault();
userResult.FirstName = this.txtFirstName.Text;
userResult.MiddleName = this.txtMiddleName.Text;
userResult.LastName = this.txtLastName.Text;
userResult.DisplayName = this.txtDisplayName.Text;
userResult.Phone = this.txtPhone.Text;
userResult.Email = this.txtEmail.Text;
userResult.IsActive = this.chkActive.Checked;
userResult.IsAdmin = this.chkAdmin.Checked;
userResult.IsSupervisor = this.chkSupervisor.Checked;
context.SaveChanges();
MessageBox.Show(@"User Profile Updated");
}
else
{
User newUser = new User
{
UserID = this.txtID.Text,
FirstName = this.txtFirstName.Text,
MiddleName = this.txtMiddleName.Text,
LastName = this.txtLastName.Text,
DisplayName = this.txtDisplayName.Text,
Phone = this.txtPhone.Text,
Email = this.txtEmail.Text,
IsActive = this.chkActive.Checked,
IsAdmin = this.chkAdmin.Checked,
IsSupervisor = this.chkSupervisor.Checked
};
context.Users.Add(newUser);
context.SaveChanges();
MessageBox.Show(@"User Profile Created");
// If save is successful, update the user list and display the new user profile
this.DisplayUsers();
this.cbxChooseUser.SelectedValue = newUser.UserID;
this.txtID.Enabled = false;
this.btnAdd.Enabled = true;
}
}
}
catch (DbUpdateException dbUEx)
{
MessageBox.Show(dbUEx.InnerException != null ? dbUEx.InnerException.Message : dbUEx.Message);
return;
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
return;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
/// <summary>
/// Event handler to save session data and close form when the Save button is clicked
/// </summary>
/// <param name="sender">The parameter is not used.</param>
/// <param name="e">The parameter is not used.</param>
private void BtnSaveClick(object sender, EventArgs e)
{
// Get data from form and insert into current Availability object
this.CurrentAvailability.DayID = this.cbxDay.SelectedValue.ToString();
// Get selected start time as a TimeSpan
Time selectedStartTime = (Time)this.cbxStartTime.SelectedItem;
this.CurrentAvailability.StartTime = selectedStartTime.Time1;
// Get selected end time as a TimeSpan
Time selectedEndTime = (Time)this.cbxEndTime.SelectedItem;
this.CurrentAvailability.EndTime = selectedEndTime.Time1;
// If end time is earlier or the same as the start time, display an error message, then cancel the save action.
if (TimeSpan.Compare(this.CurrentAvailability.StartTime, this.CurrentAvailability.EndTime) >= 0)
{
MessageBox.Show(@"Error: Start time must be earlier than the end time.");
return;
}
// Verify that the current availablility block does not overlap another block on the same day
try
{
using (var context = new db_sft_2172Entities())
{
var sameDayCoachAvailability = from coachAvailability in context.CoachAvailabilities
where
coachAvailability.CoachID.Equals(this.CurrentAvailability.CoachID) &&
coachAvailability.DayID.Equals(this.CurrentAvailability.DayID) &&
!coachAvailability.CoachAvailabilityID.Equals(this.CurrentAvailability.CoachAvailabilityID)
select coachAvailability;
if (sameDayCoachAvailability.Any())
{
var overlappingCoachAvailability = from overlapping in sameDayCoachAvailability
where (!(this.CurrentAvailability.StartTime < overlapping.StartTime && this.CurrentAvailability.EndTime <= overlapping.StartTime) &&
!(this.CurrentAvailability.StartTime >= overlapping.EndTime && this.CurrentAvailability.EndTime > overlapping.EndTime))
select overlapping;
if (overlappingCoachAvailability.Any())
{
MessageBox.Show(
@"Sorry, the desired coach is already available for part or all of this time block.\nPlease select another combination or modify an existing block!");
return;
}
}
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
return;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
// Call appropriate method to add or update session data
if (this.CoachAvailabilityId == -1)
{
this.AddNewAvailability();
}
else
{
this.UpdateAvailability();
}
// Close form once finished
this.Close();
}
/// <summary>
/// Populates the combo boxes and text boxes with the selected coach's information.
/// </summary>
/// <param name="sender">The parameter is not used.</param>
/// <param name="e">The parameter is not used.</param>
private void CbxChooseCoach_SelectedIndexChanged(object sender, EventArgs e)
{
if (this.cbxChooseCoach.SelectedIndex == -1)
{
return;
}
else
{
// Call method to determine whether current coach profile has been modified
this.CheckForModifiedProfile();
// If the current coach profile is modified, display an alert
if (this.isCoachModified)
{
DialogResult cancelChoice = MessageBox.Show(
@"This action will remove all current changes. Do you want to continue?",
@"Cancel",
MessageBoxButtons.YesNo,
MessageBoxIcon.Warning);
if (cancelChoice == DialogResult.No)
{
return;
}
}
}
try
{
using (var context = new db_sft_2172Entities())
{
// Obtain the selected coach ID from the combo box
string coachId = this.cbxChooseCoach.SelectedValue.ToString();
// Find the coach in the database.
var coachQuery = from coach in context.Coaches
where coach.CoachID.Equals(coachId)
select coach;
// If the query returns a a user, display the corresponding info in the form
if (coachQuery.Any())
{
var coachResult = coachQuery.FirstOrDefault();
// If a query result is found, update fields and course lists
if (coachResult != null)
{
this.txtID.Text = coachResult.CoachID;
this.txtFirstName.Text = coachResult.FirstName;
this.txtLastName.Text = coachResult.LastName;
this.txtMiddleName.Text = coachResult.MiddleName;
this.txtDisplayName.Text = coachResult.DisplayName;
this.txtEmail.Text = coachResult.Email;
this.txtPhone.Text = coachResult.Phone;
this.cbxSupervisor.SelectedValue = coachResult.SupervisorID;
this.chkActive.Checked = coachResult.IsActive;
this.GetCoachSelectedCourses();
this.DisplaySelectedCourses();
// Reset modified flag
this.isCoachModified = false;
}
}
}
}
catch (SqlException sqlEx)
{
MessageBox.Show(sqlEx.InnerException != null ? sqlEx.InnerException.Message : sqlEx.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}