public ActionResult Create(Course course)
{
if (ModelState.IsValid)
{
try
{
course.Responsible = (int)SessionManager.GetCurrentUserId(HttpContext.User.Identity.Name);
CourseModel.CreateCourse(course);
return RedirectToAction("Index");
}
catch (GrException gex)
{
System.Diagnostics.Debug.WriteLine(gex.UserMessage);
System.Diagnostics.Debug.WriteLine(gex.StackTrace);
ModelState.AddModelError("", gex.UserMessage);
return View(course);
}
}
else
{
// addinge extra error message here in case JS is deactivated on client.
ModelState.AddModelError("", "vous avez envoyé des données invalides");
return View(course);
}
}
/// <summary>
/// Gets the course liste by executing the given Sql command
/// </summary>
/// <param name="cmd">The sql command to be executed</param>
/// <returns>Returns the course list</returns>
private static List<Course> ListCourses(SqlCommand cmd)
{
List<Course> list = new List<Course>();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
//string coltype = rdr.GetFieldType(rdr.GetOrdinal("active")).Name;
string name = rdr.GetString(rdr.GetOrdinal("name"));
string key = rdr.GetString(rdr.GetOrdinal("Key"));
bool active = rdr.GetBoolean(rdr.GetOrdinal("active"));
int id_person = rdr.GetInt32(rdr.GetOrdinal("id_person"));
int id_course = rdr.GetInt32(rdr.GetOrdinal("id_course"));
Course course = new Course(id_course, name, key,
active, id_person);
course.ResponsibleString = rdr.GetString(rdr.GetOrdinal("firstname")) + " " + rdr.GetString(rdr.GetOrdinal("lastname"));
list.Add(course);
}
rdr.Close();
return list;
}
/// <summary>
/// Update the course in the database
/// </summary>
/// <param name="course">The course to be updated</param>
public static void UpdateCourse(Course course)
{
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.RepeatableRead);
try
{
byte[] timestamp = course.getByteTimestamp();
SqlCommand cmd = new SqlCommand("SELECT * " +
"FROM Course C " +
"WHERE C.id_course=@id_course AND C.timestamp=@timestamp;", db, transaction);
cmd.Parameters.Add("@id_course", SqlDbType.Int).Value = course.ID;
cmd.Parameters.Add("@timestamp", SqlDbType.Binary).Value = timestamp;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
rdr.Close();
cmd = new SqlCommand("UPDATE Course " +
"SET name=@name, [key]=@key, active=@active " +
"WHERE id_course=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = course.ID;
cmd.Parameters.Add("@name", SqlDbType.Char).Value = course.Name;
cmd.Parameters.Add("@key", SqlDbType.Char).Value = course.Key;
cmd.Parameters.Add("@active", SqlDbType.Bit).Value = course.Active;
cmd.ExecuteNonQuery();
}
else
{
rdr.Close();
transaction.Commit();
throw new GrException(Messages.recommencerEdit);
}
transaction.Commit();
}
catch(Exception e)
{
if (e is GrException) throw e;
transaction.Rollback();
throw new GrException(e, Messages.errProd);
}
db.Close();
}
catch(Exception e)
{
if (e is GrException) throw e;
throw new GrException(e, Messages.errProd);
}
}
/// <summary>
/// Gets the course object for a given course id
/// </summary>
/// <param name="id">The id of the course</param>
/// <returns>Returns the course</returns>
public static Course GetCourse(int id)
{
Course course = null;
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
SqlCommand cmd = new SqlCommand("SELECT C.id_course, C.name, C.[key], C.active, R.id_person, R.firstname, R.lastname, C.timestamp " +
"FROM Course C " +
"INNER JOIN Responsible R ON R.id_person = C.id_person " +
"WHERE C.id_course=@id_course;", db, transaction);
cmd.Parameters.Add("@id_course", SqlDbType.Int).Value = id;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
string name = rdr.GetString(rdr.GetOrdinal("name"));
string key = rdr.GetString(rdr.GetOrdinal("Key"));
bool active = rdr.GetBoolean(rdr.GetOrdinal("active"));
int id_person = rdr.GetInt32(rdr.GetOrdinal("id_person"));
int id_course = rdr.GetInt32(rdr.GetOrdinal("id_course"));
course = new Course(id_course, name, key,
active, id_person);
course.ResponsibleString = rdr.GetString(rdr.GetOrdinal("firstname")) + " " + rdr.GetString(rdr.GetOrdinal("lastname"));
byte[] buffer = new byte[100];
rdr.GetBytes(rdr.GetOrdinal("timestamp"), 0, buffer, 0, 100);
course.setTimestamp(buffer);
}
rdr.Close();
transaction.Commit();
}
catch(Exception e)
{
transaction.Rollback();
throw new GrException(e, Messages.errProd);
}
db.Close();
}
catch(Exception e)
{
if (e is GrException) throw e;
throw new GrException(e, Messages.errProd);
}
return course;
}
/// <summary>
/// Insert's the course in the database
/// </summary>
/// <param name="course">The course to be inserted</param>
public static int CreateCourse(Course course)
{
int id;
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted);
try
{
SqlCommand cmd = new SqlCommand("INSERT INTO Course " +
"(name, [key], active, id_person) " +
"VALUES (@name, @key, @active, @id_person); " +
"SELECT scope_identity()", db, transaction);
cmd.Parameters.Add("@name", SqlDbType.Char).Value = course.Name;
cmd.Parameters.Add("@key", SqlDbType.Char).Value = course.Key;
cmd.Parameters.Add("@active", SqlDbType.Bit).Value = course.Active;
cmd.Parameters.Add("@id_person", SqlDbType.Int).Value = course.Responsible;
id = Convert.ToInt32(cmd.ExecuteScalar());
transaction.Commit();
}
catch(Exception e)
{
transaction.Rollback();
throw new GrException(e, Messages.errProd);
}
db.Close();
}
catch(Exception e)
{
if (e is GrException) throw e;
throw new GrException(e, Messages.errProd);
}
return id;
}
/// <summary>
/// Unsubscribes a user from the current course
/// </summary>
/// <param name="id_person">The id of the user</param>
public void Unsubscribe(int? id_person, Course course)
{
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.Serializable);
try
{
SqlCommand cmd = new SqlCommand("DELETE FROM Reservation " +
"WHERE id_person=@id_person AND id_slot IN (SELECT id_slot " +
"FROM slot INNER JOIN slotRange ON slot.id_slotRange=slotRange.id_slotRange WHERE id_course=@id_course);", db, transaction);
cmd.Parameters.Add("@id_course", SqlDbType.Int).Value = ID;
cmd.Parameters.Add("@id_person", SqlDbType.Int).Value = id_person;
cmd.ExecuteNonQuery();
cmd = new SqlCommand("DELETE FROM Subscription " +
"WHERE id_person=@id_person AND id_course=@id_course;", db, transaction);
cmd.Parameters.Add("@id_course", SqlDbType.Int).Value = ID;
cmd.Parameters.Add("@id_person", SqlDbType.Int).Value = id_person;
cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw new GrException(e, Messages.errProd);
}
db.Close();
}
catch (Exception e)
{
if (e is GrException) throw e;
throw new GrException(e, Messages.errProd);
}
}
public ActionResult Delete(int id, Course course)
{
if (IsAuthorized(id))
{
try
{
CourseModel.DeleteCourse(course);
return RedirectToAction("Index");
}
catch (GrException gex)
{
System.Diagnostics.Debug.WriteLine(gex.UserMessage);
System.Diagnostics.Debug.WriteLine(gex.StackTrace);
ModelState.AddModelError("", gex.UserMessage);
return View(CourseModel.GetCourse(id));
}
}
else
{
SessionManager.RedirectAccessDenied(HttpContext.Request.RequestContext);
return null;
}
}
public ActionResult Unsubscribe(int id, Course course)
{
try
{
course.Unsubscribe(SessionManager.GetCurrentUserId(HttpContext.User.Identity.Name), course);
return RedirectToAction("Index");
}
catch (GrException gex)
{
System.Diagnostics.Debug.WriteLine(gex.UserMessage);
System.Diagnostics.Debug.WriteLine(gex.StackTrace);
ModelState.AddModelError("", gex.UserMessage);
return View();
}
}
public ActionResult Subscribe(int id, Course course)
{
try
{
if (CourseModel.GetCourse(id).Key == course.Key)
{
course.Subscribe(SessionManager.GetCurrentUserId(HttpContext.User.Identity.Name));
return RedirectToAction("ListMyCourse");
}
else
{
ModelState.AddModelError("", "La clefs n'est pas correct.");
return View(course);
}
}
catch (GrException gex)
{
System.Diagnostics.Debug.WriteLine(gex.UserMessage);
System.Diagnostics.Debug.WriteLine(gex.StackTrace);
ModelState.AddModelError("", gex.UserMessage);
return View();
}
}
public ActionResult Edit(int id, Course course)
{
if (IsAuthorized(id))
{
if (ModelState.IsValid)
{
try
{
CourseModel.UpdateCourse(course);
return RedirectToAction("Index");
}
catch (GrException gex)
{
System.Diagnostics.Debug.WriteLine(gex.UserMessage);
System.Diagnostics.Debug.WriteLine(gex.StackTrace);
ModelState.AddModelError("", gex.UserMessage);
return View(course);
}
}
else
{
// addinge extra error message here in case JS is deactivated on client.
ModelState.AddModelError("", "vous avez envoyé des données invalides");
return View(course);
}
}
else
{
SessionManager.RedirectAccessDenied(HttpContext.Request.RequestContext);
return null;
}
}
public ActionResult Duplicate(Course course)
{
if (IsAuthorized(course.ID))
{
if (ModelState.IsValid)
{
try
{
course.Responsible = (int)SessionManager.GetCurrentUserId(HttpContext.User.Identity.Name);
int id_course = CourseModel.CreateCourse(course);
List<SlotRange> slotRanges = course.GetSlotRangesForCourse();
int days = 0;
foreach (SlotRange sr in slotRanges)
{
if (days == 0)
{
TimeSpan span = course.DuplDestDate - sr.StartRes;
days = (int)span.TotalDays;
}
SlotRangeModel.DuplicateSlotRange(sr, days, id_course);
}
return RedirectToAction("Index");
}
catch (GrException gex)
{
System.Diagnostics.Debug.WriteLine(gex.UserMessage);
System.Diagnostics.Debug.WriteLine(gex.StackTrace);
ModelState.AddModelError("", gex.UserMessage);
return View(course);
}
}
else
{
// addinge extra error message here in case JS is deactivated on client.
ModelState.AddModelError("", "vous avez envoyé des données invalides");
return View(course);
}
}
else
{
SessionManager.RedirectAccessDenied(HttpContext.Request.RequestContext);
return null;
}
}