protected void Page_Load(object sender, EventArgs e)
{
(this.Master as MasterPage).ManageMasterLayout();
Application.Lock();
volunteerQueue = Application["volunteerQueue"] as VolunteerQueue.VolunteerQueue;
cinderellaQueue = Application["cinderellaAutomatedQueue"] as CinderellaQueue.CinderellaQueue;
cinderellaQueue.recalibratePriority();
if (!(volunteerQueue.isEmpty()) && !(cinderellaQueue.isEmpty()))
{
int volunteerID = volunteerQueue.getValofFrontNode().VolunteerID;
int cinderellaID = cinderellaQueue.getValofFrontNode().CinderellaID;
volunteerQueue.dequeue();
cinderellaQueue.dequeue();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
//Open the connection
conn.Open();
//Query to pair cinderella in the database
string pairCinderellaQuery = "UPDATE Cinderella "
+ "SET Volunteer_ID='" + volunteerID.ToString() + "' "
+ "WHERE CinderellaID='" + cinderellaID.ToString() + "'";
//Turn string into a SQL command
SqlCommand pairCinderella = new SqlCommand(pairCinderellaQuery, conn);
// Execute query
pairCinderella.ExecuteNonQuery();
//Get current Time
DateTime now = DateTime.Now;
/************************
* 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='" + cinderellaID + "'";
//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 ('" + cinderellaID + "', '" + 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='" + volunteerID + "'";
//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 ('" + volunteerID + "', '" + now + "', 'Paired', 'Y')";
//Execute query
SqlCommand addNewVolStatusRecord = new SqlCommand(addNewVolStatusRecordQuery, conn);
//Execute Query
addNewVolStatusRecord.ExecuteNonQuery();
conn.Close();
}
Response.AppendHeader("Refresh", 10 + "; URL=AutomatedPairing.aspx");
//CinderellaQueue.CinderellaQueue queueCopy = new CinderellaQueue.CinderellaQueue();
//Application.Lock();
//queueCopy = Application["cinderellaAutomatedQueue"] as CinderellaQueue.CinderellaQueue;
//Label1.Text = queueCopy.getNumItems().ToString();
//Label2.Text = queueCopy.getValofFrontNode().CinderellaID.ToString();
//Application.UnLock();
//VolunteerQueue.VolunteerQueue queueCopy2 = new VolunteerQueue.VolunteerQueue();
//Application.Lock();
//queueCopy2 = Application["volunteerQueue"] as VolunteerQueue.VolunteerQueue;
//Label3.Text = queueCopy2.getNumItems().ToString();
//Label4.Text = queueCopy2.getValofFrontNode().VolunteerID.ToString();
//Application.UnLock();
}
protected void TakeOffBreakButton_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 = VolunteerOnBreakGridView.SelectedValue.ToString();
//Query to retrieve information needed for confirmation label
string volunteerInfoQuery = "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 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 currentRole = dataSet.Tables[0].Rows[0]["Role_Name"].ToString();
string isValid = dataSet.Tables[0].Rows[0]["IsValid"].ToString();
// Creating a variable to hold the current time
string now = DateTime.Now.ToString();
//Check if volunteer is not deleted
if (isValid == "Y")
{
if (currentStatus == "On Break")
{
// SQL string to update the Volunteer's current status (On Break) to not current
string sql = "UPDATE VolunteerStatusRecord "
+ "SET EndTime = '" + now + "', IsCurrent = 'N' "
+ "WHERE Volunteer_ID = '" + getVolunteerID + "' AND IsCurrent = 'Y'";
// Execute query
SqlCommand comm1 = new SqlCommand(sql, conn);
comm1.ExecuteNonQuery();
// SQL string to insert new current status of Ready
sql = "INSERT INTO VolunteerStatusRecord (Volunteer_ID, StartTime, Status_Name, IsCurrent) "
+ "VALUES ('" + getVolunteerID + "', '" + now + "', 'Ready', 'Y')";
// Execute query
SqlCommand comm2 = new SqlCommand(sql, conn);
comm2.ExecuteNonQuery();
//If volunteer if a Godmother, put her back into the automated pairing queue
if (currentRole == "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();
ConfirmLabel.Text = firstName + " " + lastName + " has been taken off of break and put back into automated pairing.";
ConfirmLabel.Visible = true;
ConfirmLabel.ForeColor = System.Drawing.Color.Green;
}
catch (Exception ex)
{
}
}
else
{
ConfirmLabel.Text = firstName + " " + lastName + " has been taken off of break.";
ConfirmLabel.Visible = true;
ConfirmLabel.ForeColor = System.Drawing.Color.Green;
}
}
}
else
{
ConfirmLabel.Text = firstName + " " + lastName + " has been deleted. Cannot take off 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;
TakeOffBreakButton.Enabled = false;
}
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 CheckinButton_Click(object sender, EventArgs e)
{
// Checking to make sure a Volunteer is selected
if (VolunteerGridView.SelectedRow == null)
{
// Outputs error message if no Volunteer is selected
String errorVar = "Please select a Volunteer";
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + errorVar + "');", true);
}
else
{
// Creating a variable to hold a string of the Volunteer's ID
string SelectedVolunteerID = VolunteerGridView.SelectedValue.ToString();
// Creating a variable to hold the current time
string now = DateTime.Now.ToString();
// Creating a variable to hold the current date
DateTime today = DateTime.Today.Date;
//Initialize database connection with "DefaultConnection" setup in the web.config
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
//Open the connection
conn.Open();
// SQL string to INSERT Waiting status into StatusRecord
string sql = "UPDATE VolunteerStatusRecord "
+ "SET EndTime = '" + now + "', IsCurrent = 'N' "
+ "WHERE Volunteer_ID = '" + SelectedVolunteerID + "' AND IsCurrent = 'Y'";
// Execute query
SqlCommand comm1 = new SqlCommand(sql, conn);
comm1.ExecuteNonQuery();
// SQL string to UPDATE Pending status
sql = "INSERT INTO VolunteerStatusRecord (Volunteer_ID, StartTime, Status_Name, IsCurrent) "
+ "VALUES ('" + SelectedVolunteerID + "', '" + now + "', 'Ready', 'Y')";
// Execute query
SqlCommand comm2 = new SqlCommand(sql, conn);
comm2.ExecuteNonQuery();
// SQL string to SELECT the Volunteer's Role for this Shift
sql = "SELECT Role_Name "
+ "FROM VolunteerShiftRecord "
+ "WHERE Shift_Name = '" + DateTime.Now.DayOfWeek + "' AND Volunteer_ID = '" + SelectedVolunteerID + "'";
SqlCommand comm3 = new SqlCommand(sql, conn);
string ShiftRole = comm3.ExecuteScalar().ToString();
// SQL string to INSERT the Volunteer's RoleRecord
sql = "INSERT INTO VolunteerRoleRecord(Volunteer_ID, StartTime, Role_Name, IsCurrent) "
+ "VALUES ('" + SelectedVolunteerID + "', '" + now + "', '" + ShiftRole + "', 'Y')";
// Execute query
SqlCommand comm4 = new SqlCommand(sql, conn);
comm4.ExecuteNonQuery();
//REMEMBER TO CLOSE CONNECTION!!
conn.Close();
// Rebind the data to refresh the Grid
VolunteerGridView.DataBind();
VolunteerGridView.SelectedIndex = -1;
ResultLabel.Text = "Volunteer has been checked in";
ResultLabel.Visible = true;
if (ShiftRole == "Godmother")
{
try
{
//Retrieve ID of selected volunteer
int volID = Convert.ToInt32(SelectedVolunteerID);
//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();
ResultLabel.Text += " and placed in queue.";
}
catch (Exception ex)
{
ResultLabel.Visible = false;
}
}
}
}
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 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;
}