protected void changeRoleButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString); conn.Open(); //String to get ID of selected Volunteer string getVolunteerID = VolunteerGridView.SelectedValue.ToString(); //Query to retrieve information needed for confirmation label string volunteerPrevRoleQuery = "SELECT Volunteer.VolunteerID, Volunteer.LastName, Volunteer.FirstName, VolunteerRoleRecord.Role_Name, VolunteerStatusRecord.Status_Name, Volunteer.IsValid " + "FROM Volunteer " + "INNER JOIN VolunteerRoleRecord " + "ON Volunteer.VolunteerID = VolunteerRoleRecord.Volunteer_ID " + "INNER JOIN VolunteerStatusRecord " + "ON Volunteer.VolunteerID = VolunteerStatusRecord.Volunteer_ID " + "WHERE VolunteerRoleRecord.IsCurrent = 'Y' " + "AND VolunteerStatusRecord.IsCurrent = 'Y' " + "AND VolunteerRoleRecord.Role_Name != 'Alterations' " + "AND Volunteer.VolunteerID = '" + getVolunteerID + "'"; //Execute query SqlCommand volunteerPrevRole = new SqlCommand(volunteerPrevRoleQuery, conn); //Create a new adapter SqlDataAdapter adapter = new SqlDataAdapter(volunteerPrevRole); //Create a new dataset to hold the query results DataSet dataSet = new DataSet(); //Store the results in the adapter adapter.Fill(dataSet); //Store info to be used for confirmation label in local variables string firstName = dataSet.Tables[0].Rows[0]["FirstName"].ToString(); string lastName = dataSet.Tables[0].Rows[0]["LastName"].ToString(); string currentStatus = dataSet.Tables[0].Rows[0]["Status_Name"].ToString(); string oldRole = dataSet.Tables[0].Rows[0]["Role_Name"].ToString(); string isValid = dataSet.Tables[0].Rows[0]["IsValid"].ToString(); string newRole = roleDropDownList.SelectedItem.Text; //Check if volunteer is not deleted if (isValid == "Y") { //If volunteer is shopping notify the user that role cannot be changed if (currentStatus == "Shopping") { ConfirmLabel.Text = firstName + " " + lastName + " is currently shopping and cannot have their role changed at the moment."; ConfirmLabel.ForeColor = System.Drawing.Color.Red; ConfirmLabel.Visible = true; } else { //If volunteer is Paired break the pairing, send cinderella back into the pairing queue, and .... if (currentStatus == "Paired") { // SQL string to get Cinderella paired to selected Volunteer string sql = "SELECT CinderellaID " + "FROM Cinderella " + "INNER JOIN CinderellaStatusRecord " + "ON Cinderella.CinderellaID = CinderellaStatusRecord.Cinderella_ID " + "WHERE Volunteer_ID = '" + getVolunteerID + "' AND IsCurrent = 'Y' AND Status_Name = 'Paired'"; // Execute Query SqlCommand comm1 = new SqlCommand(sql, conn); string pairedCinderella = comm1.ExecuteScalar().ToString(); //Update paired cinderella's volunterID attribute to null to signify that she does not have a volunteer sql = "UPDATE Cinderella " + "SET Volunteer_ID = NULL " + "WHERE CinderellaID = '" + pairedCinderella + "'"; // Execute Query SqlCommand comm2 = new SqlCommand(sql, conn); comm2.ExecuteNonQuery(); // SQL string to UPDATE the paired Cinderella's status sql = "UPDATE CinderellaStatusRecord " + "SET EndTime = '" + DateTime.Now + "', IsCurrent = 'N' " + "WHERE Cinderella_ID = '" + pairedCinderella + "' AND IsCurrent = 'Y'"; // Execute query string into a SQL command SqlCommand comm3 = new SqlCommand(sql, conn); comm3.ExecuteNonQuery(); // SQL string to INSERT a new status of Waiting for Godmother for Cinderella sql = "INSERT INTO CinderellaStatusRecord (Cinderella_ID, StartTime, Status_Name, IsCurrent) " + "VALUES ('" + pairedCinderella + "', '" + DateTime.Now + "', 'Waiting for Godmother', 'Y')"; SqlCommand comm4 = new SqlCommand(sql, conn); comm4.ExecuteNonQuery(); //////////////////////////// // SQL string to UPDATE the paired Volunteer's status string endVolunteerStatusQuery = "UPDATE VolunteerStatusRecord " + "SET EndTime = '" + DateTime.Now + "', IsCurrent = 'N' " + "WHERE Volunteer_ID = '" + getVolunteerID + "' AND IsCurrent = 'Y'"; // Execute query string into a SQL command SqlCommand endVolunteerStatus = new SqlCommand(endVolunteerStatusQuery, conn); endVolunteerStatus.ExecuteNonQuery(); // SQL string to INSERT a new status of Ready for Volunteer string addNewVolunteerStatusQuery = "INSERT INTO VolunteerStatusRecord (Volunteer_ID, StartTime, Status_Name, IsCurrent) " + "VALUES ('" + getVolunteerID + "', '" + DateTime.Now + "', 'Ready', 'Y')"; SqlCommand addNewVolunteerStatus = new SqlCommand(addNewVolunteerStatusQuery, conn); addNewVolunteerStatus.ExecuteNonQuery(); // Re-adding pairedCinderella to the queue at front try { //Retrieve ID of selected volunteer int cinID = Convert.ToInt32(pairedCinderella); //Create object instance with selected volunteer CinderellaClass oldCinderella = new CinderellaClass(cinID); //Lock application state so that no else can access it Application.Lock(); //Initialize a local copy of volunteer queue CinderellaQueue.CinderellaQueue cinderellaAutomatedQueueCopy = new CinderellaQueue.CinderellaQueue(); //Copy queue in the application session into the local copy cinderellaAutomatedQueueCopy = Application["cinderellaAutomatedQueue"] as CinderellaQueue.CinderellaQueue; //Insert volunter to the queue cinderellaAutomatedQueueCopy.enqueueToFront(oldCinderella); //Copy changes into application queue Application["cinderellaAutomatedQueue"] = cinderellaAutomatedQueueCopy; //Unlock Application session Application.UnLock(); } catch { } } else if (currentStatus == "Ready" && oldRole == "Godmother") { try { //Retrieve ID of selected volunteer int volID = Convert.ToInt32(getVolunteerID); //Lock application state so that no else can access it Application.Lock(); //Initialize a local copy of volunteer queue VolunteerQueue.VolunteerQueue volunteerQueueCopy = new VolunteerQueue.VolunteerQueue(); //Copy queue in the application session into the local copy volunteerQueueCopy = Application["volunteerQueue"] as VolunteerQueue.VolunteerQueue; //Insert volunter to the queue volunteerQueueCopy.selectiveDequeue(volID); //Copy changes into application queue Application["volunteerQueue"] = volunteerQueueCopy; //Unlock Application session Application.UnLock(); ConfirmLabel2.Text = firstName + " " + lastName + " has been taken out from automated pairing."; ConfirmLabel2.Visible = true; ConfirmLabel2.ForeColor = System.Drawing.Color.Green; } catch (Exception ex) { } } //End volunteer's current role string updateCurrentRole = "UPDATE VolunteerRoleRecord " + "SET EndTime = '" + DateTime.Now + "', IsCurrent = 'N' " + "WHERE Volunteer_ID = '" + getVolunteerID + "' AND IsCurrent = 'Y'"; SqlCommand updateRole = new SqlCommand(updateCurrentRole, conn); updateRole.ExecuteNonQuery(); //Assign a new role to the voluteer depending on the selected role from the dropdown string changeRoleQuery = "INSERT INTO VolunteerRoleRecord (Volunteer_ID, StartTime, Role_Name, IsCurrent) " + "VALUES ( '" + getVolunteerID + "', '" + DateTime.Now + "', '" + roleDropDownList.SelectedItem.Text + "', '" + 'Y' + "')"; // Execute query SqlCommand changeRole = new SqlCommand(changeRoleQuery, conn); changeRole.ExecuteNonQuery(); //Put Volunteer into pairing queue if role is switched to Godmother if (roleDropDownList.SelectedItem.Text == "Godmother") { try { //Retrieve ID of selected volunteer int volID = Convert.ToInt32(getVolunteerID); //Create object instance with selected volunteer VolunteerClass checkinVolunteer = new VolunteerClass(volID); //Lock application state so that no else can access it Application.Lock(); //Initialize a local copy of volunteer queue VolunteerQueue.VolunteerQueue volunteerQueueCopy = new VolunteerQueue.VolunteerQueue(); //Copy queue in the application session into the local copy volunteerQueueCopy = Application["volunteerQueue"] as VolunteerQueue.VolunteerQueue; //Insert volunter to the queue volunteerQueueCopy.enqueue(checkinVolunteer); //Copy changes into application queue Application["volunteerQueue"] = volunteerQueueCopy; //Unlock Application session Application.UnLock(); ConfirmLabel2.Text = firstName + " " + lastName + " has been put into automated pairing."; ConfirmLabel2.Visible = true; ConfirmLabel2.ForeColor = System.Drawing.Color.Green; } catch (Exception ex) { } } ConfirmLabel.Text = firstName + " " + lastName + "'s role has been changed from " + oldRole + " to " + newRole + "."; ConfirmLabel.ForeColor = System.Drawing.Color.Green; ConfirmLabel.Visible = true; } } else { ConfirmLabel.Text = firstName + " " + lastName + " has been deleted. Cannot change role."; ConfirmLabel.Visible = true; ConfirmLabel.ForeColor = System.Drawing.Color.Green; } //Close Connection conn.Close(); //Refresh drop down and grid view roleDropDownList.DataBind(); VolunteerGridView.DataBind(); //Disable dropdown and button and deselect grid view roleDropDownList.Enabled = false; changeRoleButton.Enabled = false; VolunteerGridView.SelectedIndex = -1; //Response.Redirect("/CinderellaMGS/Forms/AdminForms/ChildForms/ManageVolunteerRoles.aspx"); }
protected void SendOnBreakButton_Click(object sender, EventArgs e) { //Initialize database connection with "DefaultConnection" setup in the web.config SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString); //Open the connection conn.Open(); // Variable to hold selected Volunteer's ID string getVolunteerID = VolunteerOffBreakGridView.SelectedValue.ToString(); // Creating a variable to hold the current time string now = DateTime.Now.ToString(); //Query to retrieve information needed for confirmation label string volunteerInfoQuery = "SELECT Volunteer.VolunteerID, Volunteer.LastName, Volunteer.FirstName, VolunteerStatusRecord.Status_Name, Volunteer.IsValid, VolunteerRoleRecord.Role_Name " + "FROM Volunteer " + "INNER JOIN VolunteerRoleRecord " + "ON Volunteer.VolunteerID = VolunteerRoleRecord.Volunteer_ID " + "INNER JOIN VolunteerStatusRecord " + "ON Volunteer.VolunteerID = VolunteerStatusRecord.Volunteer_ID " + "WHERE VolunteerStatusRecord.IsCurrent = 'Y' " + "AND VolunteerRoleRecord.IsCurrent = 'Y' " + "AND Volunteer.VolunteerID = '" + getVolunteerID + "'"; //Execute query SqlCommand volunteerInfo = new SqlCommand(volunteerInfoQuery, conn); //Create a new adapter SqlDataAdapter adapter = new SqlDataAdapter(volunteerInfo); //Create a new dataset to hold the query results DataSet dataSet = new DataSet(); //Store the results in the adapter adapter.Fill(dataSet); //Store info to be used for confirmation label in local variables string firstName = dataSet.Tables[0].Rows[0]["FirstName"].ToString(); string lastName = dataSet.Tables[0].Rows[0]["LastName"].ToString(); string currentStatus = dataSet.Tables[0].Rows[0]["Status_Name"].ToString(); string isValid = dataSet.Tables[0].Rows[0]["IsValid"].ToString(); string currentRole = dataSet.Tables[0].Rows[0]["Role_Name"].ToString(); //Check if volunteer is not deleted if (isValid == "Y") { if (currentStatus == "Shopping") { ConfirmLabel.Text = firstName + " " + lastName + " is currently shopping and cannot be put on break at the moment."; ConfirmLabel.ForeColor = System.Drawing.Color.Red; ConfirmLabel.Visible = true; } else { //If volunteer is Paired breaking the pairing, send cinderella back into the pairing queue, and .... if (currentStatus == "Paired") { // SQL string to get Cinderella paired to selected Volunteer string sql = "SELECT CinderellaID " + "FROM Cinderella " + "INNER JOIN CinderellaStatusRecord " + "ON Cinderella.CinderellaID = CinderellaStatusRecord.Cinderella_ID " + "WHERE Volunteer_ID = '" + getVolunteerID + "' AND IsCurrent = 'Y' AND Status_Name = 'Paired'"; // Execute Query SqlCommand comm1 = new SqlCommand(sql, conn); string pairedCinderella = comm1.ExecuteScalar().ToString(); //Update paired cinderella's volunterID attribute to null to signify that she does not have a volunteer sql = "UPDATE Cinderella " + "SET Volunteer_ID = NULL " + "WHERE CinderellaID = '" + pairedCinderella + "'"; // Execute Query SqlCommand comm2 = new SqlCommand(sql, conn); comm2.ExecuteNonQuery(); // SQL string to UPDATE the paired Cinderella's status sql = "UPDATE CinderellaStatusRecord " + "SET EndTime = '" + DateTime.Now + "', IsCurrent = 'N' " + "WHERE Cinderella_ID = '" + pairedCinderella + "' AND IsCurrent = 'Y'"; // Execute query string into a SQL command SqlCommand comm3 = new SqlCommand(sql, conn); comm3.ExecuteNonQuery(); // SQL string to INSERT a new status of Waiting for Godmother for Cinderella sql = "INSERT INTO CinderellaStatusRecord (Cinderella_ID, StartTime, Status_Name, IsCurrent) " + "VALUES ('" + pairedCinderella + "', '" + DateTime.Now + "', 'Waiting for Godmother', 'Y')"; SqlCommand comm4 = new SqlCommand(sql, conn); comm4.ExecuteNonQuery(); // Re-adding pairedCinderella to the queue at front try { //Retrieve ID of selected volunteer int cinID = Convert.ToInt32(pairedCinderella); //Create object instance with selected volunteer CinderellaClass oldCinderella = new CinderellaClass(cinID); //Lock application state so that no else can access it Application.Lock(); //Initialize a local copy of volunteer queue CinderellaQueue.CinderellaQueue cinderellaAutomatedQueueCopy = new CinderellaQueue.CinderellaQueue(); //Copy queue in the application session into the local copy cinderellaAutomatedQueueCopy = Application["cinderellaAutomatedQueue"] as CinderellaQueue.CinderellaQueue; //Insert volunter to the queue cinderellaAutomatedQueueCopy.enqueueToFront(oldCinderella); //Copy changes into application queue Application["cinderellaAutomatedQueue"] = cinderellaAutomatedQueueCopy; //Unlock Application session Application.UnLock(); ConfirmLabel2.Text = "Cinderella paired with " + firstName + " " + lastName + " has been put back into the automated pairing queue."; ConfirmLabel2.Visible = true; ConfirmLabel2.ForeColor = System.Drawing.Color.Green; } catch { } } else if (currentStatus == "Ready" && currentRole == "Godmother") { try { //Retrieve ID of selected volunteer int volID = Convert.ToInt32(getVolunteerID); //Lock application state so that no else can access it Application.Lock(); //Initialize a local copy of volunteer queue VolunteerQueue.VolunteerQueue volunteerQueueCopy = new VolunteerQueue.VolunteerQueue(); //Copy queue in the application session into the local copy volunteerQueueCopy = Application["volunteerQueue"] as VolunteerQueue.VolunteerQueue; //Insert volunter to the queue volunteerQueueCopy.selectiveDequeue(volID); //Copy changes into application queue Application["volunteerQueue"] = volunteerQueueCopy; //Unlock Application session Application.UnLock(); ConfirmLabel2.Text = firstName + " " + lastName + " has been taken out from automated pairing."; ConfirmLabel2.Visible = true; ConfirmLabel2.ForeColor = System.Drawing.Color.Green; } catch (Exception ex) { } } // SQL string to UPDATE the paired Volunteer's status string endVolunteerStatusQuery = "UPDATE VolunteerStatusRecord " + "SET EndTime = '" + DateTime.Now + "', IsCurrent = 'N' " + "WHERE Volunteer_ID = '" + getVolunteerID + "' AND IsCurrent = 'Y'"; // Execute query string into a SQL command SqlCommand endVolunteerStatus = new SqlCommand(endVolunteerStatusQuery, conn); endVolunteerStatus.ExecuteNonQuery(); // SQL string to INSERT a new status of On Break for Volunteer string addNewVolunteerStatusQuery = "INSERT INTO VolunteerStatusRecord (Volunteer_ID, StartTime, Status_Name, IsCurrent) " + "VALUES ('" + getVolunteerID + "', '" + DateTime.Now + "', 'On Break', 'Y')"; SqlCommand addNewVolunteerStatus = new SqlCommand(addNewVolunteerStatusQuery, conn); addNewVolunteerStatus.ExecuteNonQuery(); ConfirmLabel.Text = firstName + " " + lastName + " has been put on break."; ConfirmLabel.ForeColor = System.Drawing.Color.Green; ConfirmLabel.Visible = true; } } else { ConfirmLabel.Text = firstName + " " + lastName + " has been deleted. Cannot be sent on break."; ConfirmLabel.Visible = true; ConfirmLabel.ForeColor = System.Drawing.Color.Green; } //REMEMBER TO CLOSE CONNECTION!! conn.Close(); // Rebind the data to refresh the Grids VolunteerOffBreakGridView.DataBind(); VolunteerOffBreakGridView.SelectedIndex = -1; VolunteerOnBreakGridView.DataBind(); VolunteerOnBreakGridView.SelectedIndex = -1; SendOnBreakButton.Enabled = false; }
//protected void BreakPairingButton_Click(object sender, EventArgs e) //{ //} protected void ManualPairButton_Click(object sender, EventArgs e) { try { NotificationLabel.Visible = false; //Initialize database connection with "DefaultConnection" setup in the web.config SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString); conn.Open(); //Retrieve IDs of cinderella and volunteer string selectedCinderellaID = ManualCinderellaGridView.SelectedValue.ToString(); string selectedVolunteerID = VolunteerPairingGridView.SelectedValue.ToString(); ////////////////////////////////////////////////////////////////////////////////////////////////// //Retireve first and last name of volunteer and cinderella for notification labal (lines 231-)//// ///////////////////////////////////////////////////////////////////////////////////////////////// //Retrieve volunteer full name //Query to retrieve information needed for confirmation label string volunteerFullNameQuery = "SELECT Volunteer.LastName, Volunteer.FirstName " + "FROM Volunteer " + "WHERE Volunteer.VolunteerID = '" + selectedVolunteerID + "'"; //Execute query SqlCommand volunteerFullName = new SqlCommand(volunteerFullNameQuery, conn); //Create a new adapter SqlDataAdapter adapter = new SqlDataAdapter(volunteerFullName); //Create a new dataset to hold the query results DataSet dataSet = new DataSet(); //Store the results in the adapter adapter.Fill(dataSet); //Store info to be used for confirmation label in local variables string volFirstName = dataSet.Tables[0].Rows[0]["FirstName"].ToString(); string voLastName = dataSet.Tables[0].Rows[0]["LastName"].ToString(); //Retrieve Cinderella full name //Query to retrieve information needed for confirmation label string cinderellaFullNameQuery = "SELECT Cinderella.LastName, Cinderella.FirstName " + "FROM Cinderella " + "WHERE Cinderella.CinderellaID = '" + selectedCinderellaID + "'"; //Execute query SqlCommand cinderellaFullName = new SqlCommand(cinderellaFullNameQuery, conn); //Create a new adapter SqlDataAdapter adapter2 = new SqlDataAdapter(cinderellaFullName); //Create a new dataset to hold the query results DataSet dataSet2 = new DataSet(); //Store the results in the adapter adapter2.Fill(dataSet2); //Store info to be used for confirmation label in local variables string cinFirstName = dataSet2.Tables[0].Rows[0]["FirstName"].ToString(); string cinLastName = dataSet2.Tables[0].Rows[0]["LastName"].ToString(); // Variable to hold current time DateTime now = DateTime.Now; //Write query to retrieve current CinderellaStatus string retrieveCinderellaCurrentStatusQuery = "SELECT Status_Name " + "FROM CinderellaStatusRecord " + "WHERE IsCurrent='Y' AND Cinderella_ID='" + selectedCinderellaID + "'"; //Write query to retrieve current VolunteerStatus string retrieveVolunteerCurrentStatusQuery = "SELECT Status_Name " + "FROM VolunteerStatusRecord " + "WHERE IsCurrent='Y' AND Volunteer_ID='" + selectedVolunteerID + "'"; //Convert Strings into SQL commands SqlCommand retrieveCinderellaCurrentStatus = new SqlCommand(retrieveCinderellaCurrentStatusQuery, conn); SqlCommand retrieveVolunteerCurrentStatus = new SqlCommand(retrieveVolunteerCurrentStatusQuery, conn); //Retrieve results from queries and store in a varaible string currentCinderellaStatus = retrieveCinderellaCurrentStatus.ExecuteScalar().ToString(); string currentVolunteerStatus = retrieveVolunteerCurrentStatus.ExecuteScalar().ToString(); //Check if volunteer is deleted. If so notify user if (currentVolunteerStatus == "Deleted") { NotificationLabel.Text = volFirstName + " " + voLastName + " has been deleted and cannot be currently paired."; NotificationLabel.ForeColor = System.Drawing.Color.Red; NotificationLabel.Visible = true; } //Check if volunteer is shopping. If so notify user else if (currentVolunteerStatus == "Shopping") { NotificationLabel.Text = volFirstName + " " + voLastName + " is shopping and cannot be currently paired."; NotificationLabel.ForeColor = System.Drawing.Color.Red; NotificationLabel.Visible = true; } //Check if volunteer is on break. If so notify user else if (currentVolunteerStatus == "On Break") { NotificationLabel.Text = volFirstName + " " + voLastName + " is on break and cannot be currently paired."; NotificationLabel.ForeColor = System.Drawing.Color.Red; NotificationLabel.Visible = true; } //Check if cinderella is shopping. If so notify user else if (currentCinderellaStatus == "Shopping") { NotificationLabel.Text = cinFirstName + " " + cinLastName + " is shopping and cannot be currently paired."; NotificationLabel.ForeColor = System.Drawing.Color.Red; NotificationLabel.Visible = true; } else { // Checking if the Cinderella is already paired to a volunteer if (currentCinderellaStatus == "Paired") { // SQL string to get Volunteer paired to selected Cinderella string sql = "SELECT Volunteer_ID " + "FROM Cinderella " + "WHERE CinderellaID = '" + selectedCinderellaID + "'"; // Execute Query SqlCommand comm1 = new SqlCommand(sql, conn); string pairedVolunteer = comm1.ExecuteScalar().ToString(); // Guaranteeing that the INSERTED 'Ready' status will not interefere with the later INSERTED 'Paired' status if (pairedVolunteer != selectedVolunteerID.ToString()) { // SQL string to UPDATE the paired Volunteer's status sql = "UPDATE VolunteerStatusRecord " + "SET EndTime = '" + now + "', IsCurrent = 'N'" + "WHERE Volunteer_ID = '" + pairedVolunteer + "' AND IsCurrent = 'Y'"; // Execute query string into a SQL command SqlCommand comm2 = new SqlCommand(sql, conn); comm2.ExecuteNonQuery(); // SQL string to INSERT a new Ready status for volunteer sql = "INSERT INTO VolunteerStatusRecord (Volunteer_ID, StartTime, Status_Name, IsCurrent) " + "VALUES ('" + pairedVolunteer + "', '" + now + "', 'Ready', 'Y')"; SqlCommand comm3 = new SqlCommand(sql, conn); comm3.ExecuteNonQuery(); // re-adding the pairedVolunteer to the queue at front try { //Retrieve ID of selected volunteer int volID = Convert.ToInt32(pairedVolunteer); //Create object instance with selected volunteer VolunteerClass oldVolunteer = new VolunteerClass(volID); //Lock application state so that no else can access it Application.Lock(); //Initialize a local copy of volunteer queue VolunteerQueue.VolunteerQueue volunteerQueueCopy = new VolunteerQueue.VolunteerQueue(); //Copy queue in the application session into the local copy volunteerQueueCopy = Application["volunteerQueue"] as VolunteerQueue.VolunteerQueue; //Insert volunter to the queue volunteerQueueCopy.enqueueToFront(oldVolunteer); //Copy changes into application queue Application["volunteerQueue"] = volunteerQueueCopy; //Unlock Application session Application.UnLock(); } catch (Exception ex) { } } } // Checking if the Volunteer is already paired to a Cinderella if (currentVolunteerStatus == "Paired") { // SQL string to get Cinderella paired to selected Volunteer string sql = "SELECT CinderellaID " + "FROM Cinderella " + "INNER JOIN CinderellaStatusRecord " + "ON Cinderella.CinderellaID = CinderellaStatusRecord.Cinderella_ID " + "WHERE Volunteer_ID = '" + selectedVolunteerID + "' AND IsCurrent = 'Y' AND Status_Name = 'Paired'"; // Execute Query SqlCommand comm1 = new SqlCommand(sql, conn); string pairedCinderella = comm1.ExecuteScalar().ToString(); // SQL string to UPDATE paired cinderella to not have a volunteer sql = "UPDATE Cinderella " + "SET Volunteer_ID = NULL " + "WHERE CinderellaID = '" + pairedCinderella + "'"; // Execute Query SqlCommand comm2 = new SqlCommand(sql, conn); comm2.ExecuteNonQuery(); // Guaranteeing that the INSERTED 'Waiting for Godmother' status will not interefere with the later INSERTED 'Paired' status if (pairedCinderella != selectedCinderellaID.ToString()) { // SQL string to UPDATE the paired Cinderella's status sql = "UPDATE CinderellaStatusRecord " + "SET EndTime = '" + now + "', IsCurrent = 'N'" + "WHERE Cinderella_ID = '" + pairedCinderella + "' AND IsCurrent = 'Y'"; // Execute query string into a SQL command SqlCommand comm3 = new SqlCommand(sql, conn); comm3.ExecuteNonQuery(); // SQL string to INSERT a new status of Waiting for Godmother for Cinderella sql = "INSERT INTO CinderellaStatusRecord (Cinderella_ID, StartTime, Status_Name, IsCurrent) " + "VALUES ('" + pairedCinderella + "', '" + now + "', 'Waiting for Godmother', 'Y')"; SqlCommand comm4 = new SqlCommand(sql, conn); comm4.ExecuteNonQuery(); sql = "SELECT Cinderella.isManuallyPaired " + "FROM Cinderella " + "WHERE CinderellaID = '" + pairedCinderella + "'"; // Execute Query SqlCommand retrieveManualPairingStatus = new SqlCommand(sql, conn); string manualPairingStatus = retrieveManualPairingStatus.ExecuteScalar().ToString(); // Re-adding pairedCinderella to the queue at front try { if (manualPairingStatus == "N") { //Retrieve ID of selected volunteer int cinID = Convert.ToInt32(pairedCinderella); //Create object instance with selected volunteer CinderellaClass oldCinderella = new CinderellaClass(cinID); //Lock application state so that no else can access it Application.Lock(); //Initialize a local copy of volunteer queue CinderellaQueue.CinderellaQueue cinderellaAutomatedQueueCopy = new CinderellaQueue.CinderellaQueue(); //Copy queue in the application session into the local copy cinderellaAutomatedQueueCopy = Application["cinderellaAutomatedQueue"] as CinderellaQueue.CinderellaQueue; //Insert volunter to the queue cinderellaAutomatedQueueCopy.enqueueToFront(oldCinderella); //Copy changes into application queue Application["cinderellaAutomatedQueue"] = cinderellaAutomatedQueueCopy; //Unlock Application session Application.UnLock(); } } catch { } } } //Query to pair cinderella in the database string pairCinderellaQuery = "UPDATE Cinderella " + "SET Volunteer_ID='" + selectedVolunteerID + "' " + "WHERE CinderellaID='" + selectedCinderellaID + "'"; //Turn string into a SQL command SqlCommand pairCinderella = new SqlCommand(pairCinderellaQuery, conn); // Execute query pairCinderella.ExecuteNonQuery(); /************************ * Edit Cinderella Info * * **********************/ //Query to end cinderella's "Waiting For Godmother" status record in the database string endCurrentCinStatusQuery = "UPDATE CinderellaStatusRecord " + "SET IsCurrent='N', EndTime='" + now + "' " + "WHERE IsCurrent='Y' And Cinderella_ID='" + selectedCinderellaID + "'"; //Turn string into a SQL command SqlCommand endCurrentCinStatus = new SqlCommand(endCurrentCinStatusQuery, conn); // Execute query endCurrentCinStatus.ExecuteNonQuery(); //Insert new status record "Paired" for volunteer string addNewCinStatusRecordQuery = "INSERT INTO CinderellaStatusRecord (Cinderella_ID,StartTime,Status_Name,IsCurrent) " + "VALUES ('" + selectedCinderellaID + "', '" + now + "', 'Paired', 'Y')"; //Execute query SqlCommand addNewCinStatusRecord = new SqlCommand(addNewCinStatusRecordQuery, conn); //Execute Query addNewCinStatusRecord.ExecuteNonQuery(); /*********************** * Edit Volunteer Info * * *********************/ //Query to end volunteer's "Waiting For Godmother" status record in the database string endCurrentVolStatusQuery = "UPDATE VolunteerStatusRecord " + "SET IsCurrent='N', EndTime='" + now + "' " + "WHERE IsCurrent='Y' AND Volunteer_ID='" + selectedVolunteerID + "'"; //Turn string into a SQL command SqlCommand endCurrentVolStatus = new SqlCommand(endCurrentVolStatusQuery, conn); // Execute query endCurrentVolStatus.ExecuteNonQuery(); //Insert new status record "Paired" for volunteer string addNewVolStatusRecordQuery = "INSERT INTO VolunteerStatusRecord (Volunteer_ID,StartTime,Status_Name,IsCurrent) " + "VALUES ('" + selectedVolunteerID + "', '" + now + "', 'Paired', 'Y')"; //Execute query SqlCommand addNewVolStatusRecord = new SqlCommand(addNewVolStatusRecordQuery, conn); //Execute Query addNewVolStatusRecord.ExecuteNonQuery(); //Dequeue volunteer from the automated pairing queue so that she does not get paired with another person (duplicate pairings) try { //Retrieve ID of selected volunteer int cinID = Convert.ToInt32(selectedCinderellaID); //Lock application state so that no else can access it Application.Lock(); //Initialize a local copy of volunteer queue CinderellaQueue.CinderellaQueue cinderellaAutomatedQueueCopy = new CinderellaQueue.CinderellaQueue(); //Copy queue in the application session into the local copy cinderellaAutomatedQueueCopy = Application["cinderellaAutomatedQueue"] as CinderellaQueue.CinderellaQueue; //Insert volunter to the queue cinderellaAutomatedQueueCopy.selectiveDequeue(cinID); //Copy changes into application queue Application["cinderellaAutomatedQueue"] = cinderellaAutomatedQueueCopy; //Unlock Application session Application.UnLock(); } catch (Exception ex) { } //Dequeue cinderella from the automated pairing queue so that she does not get paired with another person (pairing overriden) try { //Retrieve ID of selected volunteer int volID = Convert.ToInt32(selectedVolunteerID); //Lock application state so that no else can access it Application.Lock(); //Initialize a local copy of volunteer queue VolunteerQueue.VolunteerQueue volunteerQueueCopy = new VolunteerQueue.VolunteerQueue(); //Copy queue in the application session into the local copy volunteerQueueCopy = Application["volunteerQueue"] as VolunteerQueue.VolunteerQueue; //Insert volunter to the queue volunteerQueueCopy.selectiveDequeue(volID); //Copy changes into application queue Application["volunteerQueue"] = volunteerQueueCopy; //Unlock Application session Application.UnLock(); } catch (Exception ex) { } NotificationLabel.Text = cinFirstName + " " + cinLastName + " has been paired with " + volFirstName + " " + voLastName + "."; NotificationLabel.ForeColor = System.Drawing.Color.Green; NotificationLabel.Visible = true; } //Close connection to database conn.Close(); } catch (Exception ex) { } //Refresh databinding ManualCinderellaGridView.DataBind(); VolunteerPairingGridView.DataBind(); PairedCinderellaGridView.DataBind(); ShoppingGridView.DataBind(); //Disable and unselect gridviews ManualPairButton.Enabled = false; ManualCinderellaGridView.SelectedIndex = -1; VolunteerPairingGridView.SelectedIndex = -1; PairedCinderellaGridView.SelectedIndex = -1; ShoppingGridView.SelectedIndex = -1; }