void SendResultInfoAsJson(GetEmployeesAvailBtwnResponse 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)
{
GetEmployeesAvailBtwnRequest req;
GetEmployeesAvailBtwnResponse res = new GetEmployeesAvailBtwnResponse();
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 getAvailEmps = @"SELECT EmployeeID, FirstName, LastName
FROM Employee
WHERE EmployeeID IN
(
SELECT DISTINCT e.EmployeeID
FROM Employee e
LEFT JOIN AvailabilityTbl a ON e.EmployeeID = a.EmployeeID
AND a.DayOfWeek = DATEPART(WEEKDAY, @StartDate)
WHERE
Cast(@StartDate as time) >= Cast(a.StartTime as time)
AND Cast(a.EndTime as time) >= Cast(@EndDate as time)
)
AND EmployeeID NOT IN
(
SELECT DISTINCT e.EmployeeID
FROM Employee e
LEFT JOIN Schedule s ON e.EmployeeID = s.EmployeeID
WHERE
@StartDate >= s.StartOfShift
AND s.EndOfShift >= @EndDate
)
AND StoreID = @StoreID
AND JobType = 1" ;
SqlCommand getAvailEmpsCmd = new SqlCommand(getAvailEmps, connection);
getAvailEmpsCmd.Parameters.Add("@StoreID", SqlDbType.Int);
getAvailEmpsCmd.Parameters.Add("@StartDate", SqlDbType.DateTime);
getAvailEmpsCmd.Parameters.Add("@EndDate", SqlDbType.DateTime);
getAvailEmpsCmd.Parameters["@StoreID"].Value = req.StoreID;
getAvailEmpsCmd.Parameters["@StartDate"].Value = req.StartDate;
getAvailEmpsCmd.Parameters["@EndDate"].Value = req.EndDate;
SqlDataReader reader = getAvailEmpsCmd.ExecuteReader();
res.employees = new List <Employee>();
while (reader.Read())
{
Employee curE = new Employee();
curE.EmployeeID = Convert.ToInt32(reader["EmployeeID"]);
curE.FirstName = Convert.ToString(reader["FirstName"]);
curE.LastName = Convert.ToString(reader["LastName"]);
res.employees.Add(curE);
}
reader.Close();
}
catch (Exception ex)
{
res.error = ex.Message.ToString();
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
// Return the results as Json.
SendResultInfoAsJson(res);
}