//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;
}