void SendResultInfoAsJson(SetRequestStatusResponse res)
{
string strJson = JsonConvert.SerializeObject(res);
Response.ContentType = "application/json; charset=utf-8";
Response.AppendHeader("Access-Control-Allow-Origin", "*");
Response.Write(strJson);
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
SetRequestStatusRequest req;
SetRequestStatusResponse res = new SetRequestStatusResponse();
res.error = String.Empty;
// 1. Deserialize the incoming Json.
try
{
req = GetRequestInfo();
}
catch (Exception ex)
{
res.error = ex.Message.ToString();
// Return the results as Json.
SendResultInfoAsJson(res);
return;
}
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
try
{
connection.Open();
string getRequestInfo = "SELECT EmployeeID, RequestStatus, RequestType, ScheduleID1, ScheduleID2 FROM Request WHERE RequestID = @RequestID";
SqlCommand getRequestInfoCommand = new SqlCommand(getRequestInfo, connection);
getRequestInfoCommand.Parameters.Add("@RequestID", SqlDbType.Int);
getRequestInfoCommand.Parameters["@RequestID"].Value = req.RequestID;
SqlDataReader reader = getRequestInfoCommand.ExecuteReader();
if (!reader.HasRows)
{
res.error = "Store not found";
SendResultInfoAsJson(res);
return;
}
else
{
reader.Read();
if (Convert.ToInt32(reader["RequestStatus"]) != 0)
{
res.error = "Request already set";
SendResultInfoAsJson(res);
return;
}
}
int scheduleID = Convert.ToInt32(reader["ScheduleID1"]);
int empId = Convert.ToInt32(reader["EmployeeID"]);
int requestType = Convert.ToInt32(reader["RequestType"]);
int scheduleID1 = Convert.ToInt32(reader["ScheduleID1"]);
int scheduleID2 = Convert.ToInt32(reader["ScheduleID2"]);
int emp1 = -1, emp2 = -1;
reader.Close();
string sql = "UPDATE Request SET RequestStatus = @RequestStatus WHERE RequestID = @RequestID";
SqlCommand setRequestStatusCMD = new SqlCommand(sql, connection);
setRequestStatusCMD.Parameters.Add("@RequestID", SqlDbType.Int);
setRequestStatusCMD.Parameters.Add("@RequestStatus", SqlDbType.Int);
setRequestStatusCMD.Parameters["@RequestID"].Value = req.RequestID;
setRequestStatusCMD.Parameters["@RequestStatus"].Value = req.RequestStatus;
setRequestStatusCMD.ExecuteNonQuery();
if (req.RequestStatus == 1)
{
if (requestType == 1) //drop shift
{
string updateSql = "UPDATE Schedule SET ShiftStatus = 0 WHERE ScheduleID = @ScheduleID";
SqlCommand doRequest = new SqlCommand(updateSql, connection);
doRequest.Parameters.Add("@ScheduleID", SqlDbType.Int);
doRequest.Parameters["@ScheduleID"].Value = scheduleID;
doRequest.ExecuteNonQuery();
}
else if (requestType == 2) //pickup shift
{
string updateSql = "UPDATE Schedule SET ShiftStatus = 1, EmployeeID = @EmployeeID WHERE ScheduleID = @ScheduleID";
SqlCommand doRequest = new SqlCommand(updateSql, connection);
doRequest.Parameters.Add("@ScheduleID", SqlDbType.Int);
doRequest.Parameters.Add("@EmployeeID", SqlDbType.Int);
doRequest.Parameters["@ScheduleID"].Value = scheduleID;
doRequest.Parameters["@EmployeeID"].Value = empId;
doRequest.ExecuteNonQuery();
}
else if (requestType == 3) //swap shift
{
string getSchedules = "SELECT EmployeeID, ScheduleID FROM Schedule WHERE ScheduleID IN (@ScheduleID1, @ScheduleID2)";
SqlCommand getSchedulesCmd = new SqlCommand(getSchedules, connection);
getSchedulesCmd.Parameters.Add("@ScheduleID1", SqlDbType.Int);
getSchedulesCmd.Parameters.Add("@ScheduleID2", SqlDbType.Int);
getSchedulesCmd.Parameters["@ScheduleID1"].Value = scheduleID1;
getSchedulesCmd.Parameters["@ScheduleID2"].Value = scheduleID2;
SqlDataReader schReader = getSchedulesCmd.ExecuteReader();
while (schReader.Read())
{
int cSchID = Convert.ToInt32(schReader["ScheduleID"]);
if (cSchID == scheduleID1)
{
emp1 = Convert.ToInt32(schReader["EmployeeID"]);
}
else
{
emp2 = Convert.ToInt32(schReader["EmployeeID"]);
}
}
schReader.Close();
string updateSql = "UPDATE Schedule SET EmployeeID = @EmployeeID2 WHERE ScheduleID = @ScheduleID1;UPDATE Schedule SET EmployeeID = @EmployeeID1 WHERE ScheduleID = @ScheduleID2;";
SqlCommand doRequest = new SqlCommand(updateSql, connection);
doRequest.Parameters.Add("@ScheduleID1", SqlDbType.Int);
doRequest.Parameters.Add("@ScheduleID2", SqlDbType.Int);
doRequest.Parameters.Add("@EmployeeID1", SqlDbType.Int);
doRequest.Parameters.Add("@EmployeeID2", SqlDbType.Int);
doRequest.Parameters["@ScheduleID1"].Value = scheduleID1;
doRequest.Parameters["@ScheduleID2"].Value = scheduleID2;
doRequest.Parameters["@EmployeeID1"].Value = emp1;
doRequest.Parameters["@EmployeeID2"].Value = emp2;
doRequest.ExecuteNonQuery();
}
else if (requestType == 4) //Accept Availability Change
{
//Delete real
string delSql = "DELETE FROM AvailabilityTbl WHERE EmloyeeID = @EmployeeID AND Status = 1";
SqlCommand delCmd = new SqlCommand(delSql, connection);
delCmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
delCmd.Parameters["@EmployeeID"].Value = empId;
delCmd.ExecuteNonQuery();
string updateSql = "UPDATE AvailabilityTbl SET Status = 1 WHERE EmployeeID = @EmployeeID AND Status = 0";
SqlCommand updateCmd = new SqlCommand(updateSql, connection);
updateCmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
updateCmd.Parameters["@EmployeeID"].Value = empId;
}
}
else if (requestType == 4) //Denied Availability Change
{
//Delete pending
string delSql = "DELETE FROM AvailabilityTbl WHERE EmloyeeID = EmployeeID AND Status = 0";
SqlCommand delCmd = new SqlCommand(delSql, connection);
delCmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
delCmd.Parameters["@EmployeeID"].Value = empId;
delCmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
res.error = ex.Message.ToString();
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
// Return the results as Json.
SendResultInfoAsJson(res);
}