public override Dictionary <string, string[]> Execute() { Dictionary <string, string[]> requestBody = newAction.GetRequestBody(); Dictionary <string, string[]> responseData = new Dictionary <string, string[]>(); try { List <string> result = new List <string>(); using (MySqlConnection conn = new MySqlConnection(newAction.GetSQLConn())) { conn.Open(); //Get all the shifts by a specified skill. Include the department name and skill name. string sql = "SELECT shifts.shift_id, shifts.date, shifts.duration, shifts.num_worker, shifts.max_worker, shifts.mgr_id, shifts.dept_name, shifts.skill_name FROM " + "(SELECT flexpooldb.person.emp_id, flexpooldb.shift.*, flexpooldb.dept_type.dept_name, flexpooldb.skill.skill_name FROM flexpooldb.shift " + "INNER JOIN flexpooldb.assigned_skill " + "ON flexpooldb.assigned_skill.skill_id = flexpooldb.shift.skill_id " + "INNER JOIN flexpooldb.person " + "ON flexpooldb.person.emp_id = flexpooldb.assigned_skill.emp_id " + "INNER JOIN flexpooldb.dept_type " + "ON flexpooldb.dept_type.dept_id = flexpooldb.shift.dept_id " + "INNER JOIN flexpooldb.skill " + "ON flexpooldb.skill.skill_id = flexpooldb.shift.skill_id) AS shifts " + "WHERE shifts.emp_id = " + requestBody["emp_id"][0] + ";"; if (inDevMode) { Console.WriteLine(sql); } using (MySqlCommand cmd = new MySqlCommand(sql, conn)) { using (MySqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { Shift newShift = new Shift() { shift_id = (int)rdr[0], date = (DateTime)rdr[1], duration = (int)rdr[2], num_worker = (int)rdr[3], max_worker = (int)rdr[4], mgr_id = (int)rdr[5], dept_name = (string)rdr[6], skill_name = (string)rdr[7] }; responseData.Add($"shift_id {newShift.shift_id}", new string[] { JsonConvert.SerializeObject(newShift) }); } return(responseData); } } } } catch (KeyNotFoundException) { Console.WriteLine("ERROR: missing item in dictionary."); responseData.Add("response", new string[] { "failure" }); responseData.Add("reason", new string[] { "missing item in dictionary." }); return(responseData); } catch (Exception e) { Console.WriteLine("ERROR: " + e.Message); responseData.Add("response", new string[] { "failure" }); responseData.Add("reason", new string[] { "unspecified problem." }); return(responseData); } }
public override Dictionary <string, string[]> Execute() { Dictionary <string, string[]> requestBody = newAction.GetRequestBody(); Dictionary <string, string[]> responseData = new Dictionary <string, string[]>(); try { List <string> result = new List <string>(); using (MySqlConnection conn = new MySqlConnection(newAction.GetSQLConn())) { conn.Open(); //Get all the shifts based on department id. string sql = "SELECT shift_id, date, duration, num_worker, max_worker, mgr_id, dept_id, skill_name " + "FROM flexpooldb.shift " + "INNER JOIN flexpooldb.skill " + "ON flexpooldb.shift.skill_id = flexpooldb.skill.skill_id AND " + "flexpooldb.shift.dept_id = (SELECT dept_id FROM flexpooldb.dept_type WHERE dept_name = \"" + requestBody["dept_name"][0] + "\");"; if (inDevMode) { Console.WriteLine(sql); } using (MySqlCommand cmd = new MySqlCommand(sql, conn)) { using (MySqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { Shift newShift = new Shift() { shift_id = (int)rdr[0], date = (DateTime)rdr[1], duration = (int)rdr[2], num_worker = (int)rdr[3], max_worker = (int)rdr[4], mgr_id = (int)rdr[5], dept_id = (int)rdr[6], skill_name = (string)rdr[7] }; responseData.Add($"shift_id {newShift.shift_id}", new string[] { JsonConvert.SerializeObject(newShift) }); } return(responseData); } } } } catch (KeyNotFoundException) { Console.WriteLine("ERROR: didn't receive a valid emp_id field."); responseData.Add("response", new string[] { "failure" }); responseData.Add("reason", new string[] { "missing item in dictionary." }); return(responseData); } catch (Exception e) { Console.WriteLine("ERROR: " + e.Message); responseData.Add("response", new string[] { "failure" }); responseData.Add("reason", new string[] { "unspecified problem." }); return(responseData); } }