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");
}
