public JsonResult addTask(user_tasks employee)
{
TaskResponse response = new TaskResponse();
if (ModelState.IsValid)
{
try
{
employee.user = encdata.AESDecrypt(employee.user.Replace(' ', '+'), encStringKey).ToUpper();
response = addUserTasks(employee);
}
catch (Exception)
{
response.code = -1;
response.message = "Unable to determine user, please refresh the page...";
}
}
else
{
response.code = 0;
response.message = "Please don't leave 'Time' or 'Task' field empty";
}
return(new JsonResult {
Data = response
});
}
private TaskResponse addUserTasks(user_tasks activities)
{
TaskResponse response = new TaskResponse();
try
{
string month = activities.tasks[0].task_date.Month < 10 ? "0" + activities.tasks[0].task_date.Month : activities.tasks[0].task_date.Month.ToString();
using (MySqlConnection con = new MySqlConnection(connection))
{
con.Open();
MySqlTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
using (MySqlCommand cmd = con.CreateCommand())
{
string values = string.Empty;
for (int count = 0; count < activities.tasks[0].taskLists.Count; count++)
{
//converts work order number to uppercase
activities.tasks[0].taskLists[count].woNo = activities.tasks[0].taskLists[count].woNo == null ? "N/A" : activities.tasks[0].taskLists[count].woNo.ToUpper();
values += " ( @user, @date, @timeFrom" + count + ", @timeTo" + count + ", @woNO" + count + ", @tasks" + count + " ) ,";
cmd.Parameters.AddWithValue("timeFrom" + count, activities.tasks[0].taskLists[count].timeFrom);
cmd.Parameters.AddWithValue("timeTo" + count, activities.tasks[0].taskLists[count].timeTo);
cmd.Parameters.AddWithValue("woNO" + count, activities.tasks[0].taskLists[count].woNo);
cmd.Parameters.AddWithValue("tasks" + count, activities.tasks[0].taskLists[count].task);
}
cmd.CommandText = "INSERT INTO kpDailyTask.Report" + month + "(user, date, timeFrom, timeTo, WOno, Task)"
+ "VALUES" + values.Substring(0, values.Length - 1) + ";";
cmd.Parameters.AddWithValue("user", activities.user);
cmd.Parameters.AddWithValue("date", activities.tasks[0].task_date);
if (cmd.ExecuteNonQuery() > 0)
{
trans.Commit();
response.code = 1;
response.message = "Successfully Added!";
}
else
{
response.code = 0;
response.message = "Server error upon adding, please try again";
}
}
}
}
catch (Exception)
{
response.code = -1;
response.message = "Unexpected error occured, try again in a few minutes...";
}
return(response);
}
public JsonResult deleteTask(user_tasks taskDetails)
{
TaskResponse response = new TaskResponse();
try
{
using (MySqlConnection con = new MySqlConnection(connection))
{
con.Open();
MySqlTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
using (MySqlCommand cmd = con.CreateCommand())
{
string currentMonth = taskDetails.tasks[0].task_date.Month > 9 ? taskDetails.tasks[0].task_date.Month.ToString()
: "0" + taskDetails.tasks[0].task_date.Month.ToString();
cmd.CommandText = "DELETE FROM `kpDailyTask`.`Report" + currentMonth + "` WHERE taskid=@taskID AND USER = @user AND task = @task";
cmd.Parameters.AddWithValue("user", encdata.AESDecrypt(taskDetails.user, encStringKey).ToUpper());
cmd.Parameters.AddWithValue("taskID", taskDetails.tasks[0].taskLists[0].taskID);
cmd.Parameters.AddWithValue("task", taskDetails.tasks[0].taskLists[0].task);
if (cmd.ExecuteNonQuery() > 0)
{
trans.Commit();
response.code = 1;
response.message = "Task Successfully Deleted!";
}
else
{
response.code = 0;
response.message = "Server error upon deleting task, please try again";
}
}
}
}
catch (Exception)
{
response.code = -1;
response.message = "Unable to determine user, please refresh the page...";
}
return(new JsonResult {
Data = response
});
}
public ActionResult searchTask(TaskSearch taskSearch)
{
user_tasks data = new user_tasks();
List <daily_task> daily = new List <daily_task>();
List <task_list> tasks = new List <task_list>();
try
{
using (MySqlConnection con = new MySqlConnection(connection))
{
con.Open();
using (MySqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT *, TIME_FORMAT(timeFrom, \"%h:%i %p\") AS timeFrom_formatted, "
+ " TIME_FORMAT(timeTo, \"%h:%i %p\") AS timeTo_formatted FROM kpDailyTask.Report" + taskSearch.Month
+ " WHERE user = @user AND YEAR(DATE) = @year ORDER BY date DESC, timeFrom DESC;";
cmd.Parameters.AddWithValue("user", encdata.AESDecrypt(taskSearch.encUser.Replace(' ', '+'), encStringKey));
cmd.Parameters.AddWithValue("year", taskSearch.Year);
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
String currDate = string.Empty;
while (rdr.Read())
{
if (currDate == string.Empty)
{
currDate = rdr["date"].ToString();
}
if (currDate == rdr["date"].ToString())
{
tasks.Add(new task_list
{
taskID = rdr["taskID"].ToString(),
timeFrom = rdr["timeFrom_formatted"].ToString(),
timeTo = rdr["timeTo_formatted"].ToString(),
task = rdr["Task"].ToString(),
woNo = rdr["WOno"].ToString(),
confirmed = Convert.ToBoolean(rdr["confirmed"].ToString())
});
}
else
{
daily.Add(new daily_task
{
task_date = Convert.ToDateTime(currDate),
taskLists = tasks
});
tasks = new List <task_list>();
currDate = rdr["date"].ToString();
tasks.Add(new task_list
{
taskID = rdr["taskID"].ToString(),
timeFrom = rdr["timeFrom_formatted"].ToString(),
timeTo = rdr["timeTo_formatted"].ToString(),
task = rdr["Task"].ToString(),
woNo = rdr["WOno"].ToString(),
confirmed = Convert.ToBoolean(rdr["confirmed"].ToString())
});
}
}
//final daily add from last date result-
daily.Add(new daily_task
{
task_date = Convert.ToDateTime(currDate),
taskLists = tasks
});
data = new user_tasks {
user = taskSearch.encUser, tasks = daily
};
}
else
{
ViewBag.message = "No tasks retrieved...";
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("error: " + ex.ToString());
}
return(PartialView("_getTask", data));
}
