//add Reminder to a contact
//->Testar e ver teste.cshtml
public static int addReminder(Reminder r, String username)
{
int id = -1;
int idx = -1;
String contact = r.contact;
SqlTransaction trans;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
trans = conn.BeginTransaction();
try
{
SqlCommand getCommand = new SqlCommand("Select * from Receive_Users where contact = @name AND EmailId = @Email ", conn);
getCommand.Parameters.AddWithValue("@name", contact);
getCommand.Parameters.AddWithValue("@Email", username);
getCommand.Transaction = trans;
using (SqlDataReader rdr = getCommand.ExecuteReader())
{
if (rdr.Read())
{
idx = (int)rdr["id"];
}
}
if (idx < 0) return idx;
SqlCommand insertCommand = new SqlCommand("INSERT INTO Temporary_Reminders (title,description,date, time, urls,EmailId,contact,daysofweek) OUTPUT inserted.ID VALUES (@title,@description,@date, @time, @urls,@Email,@contact,@daysofweek)", conn);
insertCommand.Transaction = trans;
insertCommand.Parameters.AddWithValue("@date", r.date);
insertCommand.Parameters.AddWithValue("@time", r.time);
insertCommand.Parameters.AddWithValue("@urls", r.urls);
insertCommand.Parameters.AddWithValue("@contact", idx);
insertCommand.Parameters.AddWithValue("@title", r.title);
insertCommand.Parameters.AddWithValue("@description", r.description);
insertCommand.Parameters.AddWithValue("@daysofweek", r.daysofweek);
insertCommand.Parameters.AddWithValue("@Email", username);
id = (int)insertCommand.ExecuteScalar();
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
throw e;
}
}
return id;
}
public HttpResponseMessage PostReminder(Reminder r)
{
if (!this.ModelState.IsValid || r == null || !Utils.checkUri(r.urls))
throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.BadRequest));
GenericIdentity idd = (GenericIdentity)System.Web.HttpContext.Current.User.Identity;
String username = idd.Name;
int _id = PictogramsDb.addReminder(r,username);
if (_id == -1) return this.Request.CreateResponse(HttpStatusCode.NotFound, "Can´t add reminder to a user that don´t exist");
HttpResponseMessage response = this.Request.CreateResponse(HttpStatusCode.Created, r);
response.Headers.Location = new Uri(Url.Link("Default", new { idx = _id , Controller = "Home",Action = "EditReminder" }));
return response;
}
public void EditReminder()
{
Reminder r = new Reminder();
r.id = 135;
r.description = "aaa";
r.title = "bbb";
r.date = "2020-10-10";
r.time = "20:30";
r.repeatingDays = new Boolean[] { false, false, false, false, false, false, false };
r.urls = "[]";
int result = ReminderBusinessLayer.EditReminder(r, "*****@*****.**");
int res = 2;
Assert.AreEqual(result, res);
}
public void addReminderWithInvalidDate()
{
Reminder r = new Reminder();
r.contact = "Jorge_S3_mini";
r.description = "aaa";
r.title = "bbb";
r.date = "2010-10-10";
r.time = "20:30";
r.repeatingDays = new Boolean[] { false, false, false, false, false, false, false };
r.urls = "[]";
int result = ReminderBusinessLayer.addReminder(r, "*****@*****.**");
int res = -2;
Assert.AreEqual(result, res);
}
public void addReminderAndDeleteIt()
{
Reminder r = new Reminder();
r.contact = "Jorge_S3_mini";
r.description = "aaa";
r.title = "bbb";
r.date = "2020-10-10";
r.time = "20:30";
r.repeatingDays = new Boolean[] { false, false, false, false, false, false, false };
r.urls = "[]";
int result = ReminderBusinessLayer.addReminder(r, "*****@*****.**");
int res = 0;
Assert.Greater(result, res);
Boolean d = ReminderBusinessLayer.DeleteReminder("*****@*****.**", result);
Assert.AreEqual(d, true);
}
public ActionResult addReminder(Reminder reminder)
{
//get error string if model is not valid or the uris are not uris from our amazon bucket
String error = "";
bool ModelError = false; bool UriError = false;
if ((ModelError = !this.ModelState.IsValid) || reminder == null || (UriError=!Utils.checkUri(reminder.urls)))
{
if (ModelError)
{
foreach (ModelState modelState in ViewData.ModelState.Values)
{
foreach (ModelError e in modelState.Errors)
{
error += e.ErrorMessage + "<br/>";
}
}
}
if (UriError) error += "Urls de imagem inválidos ou ultrapassou o limite de imagens num lembrete" + "<br/>";
return new HttpStatusCodeResult(HttpStatusCode.BadRequest, error);
}
if (Request.IsAjaxRequest())
{
String name = (HttpContext.User as ICustomPrincipal).Identity.Name;
int idx = ReminderBusinessLayer.addReminder(reminder, name);
if (idx == -1) return Json(new { redirectUrl = "SelectContact" }, JsonRequestBehavior.AllowGet);
if (idx == -2)
{
error += "Data inválida, não pode selecionar uma hora passada" + "<br/>";
return new HttpStatusCodeResult(HttpStatusCode.BadRequest, error);
}
return Json(new { id = idx, title = reminder.title, contact = PictogramsDb.getContactId(reminder.contact, name) }, JsonRequestBehavior.AllowGet);
}
return new HttpStatusCodeResult(HttpStatusCode.NotFound);
}
//get reminders from a user to a contac
public static IEnumerable<Reminder> getAllReminders(int contact, String user)
{
List<Reminder> reminders = new List<Reminder>();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
SqlCommand getCommand = new SqlCommand("Select * from Temporary_Reminders where EmailId = @Email and contact=@contact ", conn);
getCommand.Parameters.AddWithValue("@Email", user);
getCommand.Parameters.AddWithValue("@contact", contact);
using (SqlDataReader rdr = getCommand.ExecuteReader())
{
while (rdr.Read())
{
Reminder r = new Reminder();
r.contact = rdr["contact"].ToString();
r.id = (int)rdr["id"];
r.date = rdr["date"].ToString();
r.time = rdr["time"].ToString();
r.urls = rdr["urls"].ToString();
r.title = rdr["title"].ToString();
r.description = rdr["description"].ToString();
r.daysofweek = (int)rdr["daysofweek"];
reminders.Add(r);
}
}
}
return reminders;
}
internal static Reminder getHistoricalReminder(int idx, string name)
{
Reminder r = null;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
SqlCommand getCommand = new SqlCommand("Select * from Historical_Reminders where id=@id AND EmailId=@Email ", conn);
getCommand.Parameters.AddWithValue("@id", idx);
getCommand.Parameters.AddWithValue("@Email", name);
using (SqlDataReader rdr = getCommand.ExecuteReader())
{
if (rdr.Read())
{
r = new Reminder();
r.contact = rdr["contact"].ToString();
r.id = (int)rdr["id"];
r.date = rdr["date"].ToString();
r.time = rdr["time"].ToString();
r.urls = rdr["urls"].ToString();
r.title = rdr["title"].ToString();
r.description = rdr["description"].ToString();
r.daysofweek = (int)rdr["daysofweek"];
}
}
}
return r;
}
//Garantir que nenhuma property pode ser null
internal static void EditReminder(Reminder r)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
SqlCommand updateCommand = new SqlCommand("UPDATE Temporary_Reminders SET title = @title, description = @description,date = @date, time = @time, urls=@urls,daysofweek=@daysofweek Where id=@id", conn);
updateCommand.Parameters.AddWithValue("@title", r.title);
updateCommand.Parameters.AddWithValue("@description", r.description);
updateCommand.Parameters.AddWithValue("@date", r.date);
updateCommand.Parameters.AddWithValue("@time", r.time);
updateCommand.Parameters.AddWithValue("@urls", r.urls);
updateCommand.Parameters.AddWithValue("@id", r.id);
updateCommand.Parameters.AddWithValue("@daysofweek", r.daysofweek);
updateCommand.ExecuteNonQuery();
}
}
//get reminder
//Testar
public static Reminder getReminder(int idx, String mvcuser)
{
Boolean check = false;
int count = 0;
if (idx < 0) return null;
Reminder r = new Reminder();
SqlTransaction trans;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
trans = conn.BeginTransaction();
try
{
SqlCommand getCommand1 = new SqlCommand("Select * from Temporary_Reminders where EmailId = @Email AND id=@id", conn);
getCommand1.Parameters.AddWithValue("@Email", mvcuser);
getCommand1.Parameters.AddWithValue("@id", idx);
getCommand1.Transaction = trans;
using (SqlDataReader rdr = getCommand1.ExecuteReader())
{
if (rdr.Read())
{
check = true;
}
}
if (!check) return null;
SqlCommand getCommand = new SqlCommand("Select * from Temporary_Reminders where ID = @ID", conn);
getCommand.Parameters.AddWithValue("@ID", idx);
getCommand.Transaction = trans;
using (SqlDataReader rdr = getCommand.ExecuteReader())
{
while (rdr.Read())
{
r.id = (int)rdr["id"];
r.date = rdr["date"].ToString();
r.time = rdr["time"].ToString();
r.urls = rdr["urls"].ToString();
r.title = rdr["title"].ToString();
r.description = rdr["description"].ToString();
r.contact = rdr["contact"].ToString();
r.daysofweek = (int)rdr["daysofweek"];
count++;
}
}
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
throw e;
}
}
if (count == 0) return null;
return r;
}
public ActionResult EditReminder(Reminder r)
{
//get error string if model is not valid or the uris are not uris from our amazon bucket
String error = "";
bool ModelError = false; bool UriError = false;
if ((ModelError = !this.ModelState.IsValid) || r == null || (UriError=!Utils.checkUri(r.urls)))
{
if (ModelError)
{
foreach (ModelState modelState in ViewData.ModelState.Values)
{
foreach (ModelError e in modelState.Errors)
{
error += e.ErrorMessage + "<br/>";
}
}
}
if (UriError) error += "Urls de imagem inválidos ou ultrapassou o limite de imagens num lembrete" + "<br/>";
return new HttpStatusCodeResult(HttpStatusCode.BadRequest, error);
}
if (Request.IsAjaxRequest())
{
String name = (HttpContext.User as ICustomPrincipal).Identity.Name;
//check if the reminder that is being edited is from this user
int check = ReminderBusinessLayer.EditReminder(r, name);
if (check == -1) return new HttpStatusCodeResult(HttpStatusCode.NotFound);
if (check == -2)
{
error += "Data inválida, não pode selecionar uma hora passada" + "<br/>";
return new HttpStatusCodeResult(HttpStatusCode.BadRequest, error);
}
return new HttpStatusCodeResult(HttpStatusCode.OK);
}
return new HttpStatusCodeResult(HttpStatusCode.NotFound);
}
