public ActionResult Create(SlotRange range)
{
InitViewbag(range.IdCourse);
if (ModelState.IsValid)
{
try
{
SlotRangeModel.CreateSlotRange(range);
ViewBag.Mode = "créée";
// schedule linux script to be sent to resource manager by email
ScheduleEmail(range);
return View("Complete", range);
}
catch (GrException gex)
{
ModelState.AddModelError("", gex.UserMessage);
ViewBag.Error = gex.UserMessage;
System.Diagnostics.Debug.WriteLine(gex.UserMessage);
System.Diagnostics.Debug.WriteLine(gex.StackTrace);
return View(range);
}
}
ModelState.AddModelError("", Messages.invalidData);
return View(range);
}
/// <summary>
/// Gets all the slot ranges for the current course
/// </summary>
/// <returns>Return the slot ranges associated with the course</returns>
public List<SlotRange> GetSlotRangesForCourse()
{
List<SlotRange> ranges = new List<SlotRange>();
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
SqlCommand cmd = new SqlCommand("SELECT [id_slotRange], [startRes] ,[endRes], [name] ,[id_course]" +
"FROM SlotRange SR " +
"WHERE id_course=@id " +
"ORDER BY SR.startRes ASC;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = this.ID;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
DateTime startRes = rdr.GetDateTime(rdr.GetOrdinal("startRes"));
DateTime endRes = rdr.GetDateTime(rdr.GetOrdinal("endRes"));
string name = rdr.GetString(rdr.GetOrdinal("name"));
int id_course = rdr.GetInt32(rdr.GetOrdinal("id_course"));
SlotRange range = new SlotRange(rdr.GetInt32(rdr.GetOrdinal("id_slotRange")), startRes, endRes, name, id_course);
ranges.Add(range);
}
rdr.Close();
foreach (var range in ranges)
{
//get slots
cmd = new SqlCommand("SELECT [id_slot], [start], [end] FROM Slot WHERE id_slotRange=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
rdr = cmd.ExecuteReader();
//bool hasSetDuration = false;
int cpt = 0;
while (rdr.Read())
{
int id_slot = rdr.GetInt32(rdr.GetOrdinal("id_slot"));
DateTime start = rdr.GetDateTime(rdr.GetOrdinal("start"));
DateTime end = rdr.GetDateTime(rdr.GetOrdinal("end"));
int startHour = start.Hour;
int startMinute = start.Minute;
int endHour = end.Hour;
int endMinute = end.Minute;
range.Startz.Add(startHour + ":" + startMinute);
range.Endz.Add(endHour + ":" + endMinute);
if (!range.Slotdate.Contains(start.Date))
{
range.Slotdate.Add(start.Date);
}
cpt++;
range.Slots.Add(new Slot(id_slot, start, end));
}
rdr.Close();
range.NumberOfSlots = cpt;
foreach (var slot in range.Slots)
{
//get reservations
cmd = new SqlCommand("SELECT [id_person], [id_slot], [numberMachines] FROM Reservation WHERE id_slot=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = slot.ID;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int id_person = rdr.GetInt32(rdr.GetOrdinal("id_person"));
int id_slot = rdr.GetInt32(rdr.GetOrdinal("id_slot"));
int numberMachines = rdr.GetInt32(rdr.GetOrdinal("numberMachines"));
slot.Reservations.Add(new Reservation(id_person, id_slot, numberMachines));
}
rdr.Close();
}
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw new GrException(e, Messages.errProd);
}
finally
{
db.Close();
}
}
catch (Exception e)
{
if (e is GrException) throw e;
throw new GrException(e, Messages.errProd);
}
return ranges;
}
private bool IsAuthorized(SlotRange range)
{
if (range == null)
return false;
int? rId = range.GetResponsible();
if (rId != null)
{
int? pId = SessionManager.GetCurrentUserId(HttpContext.User.Identity.Name);
if (pId != null)
{
if (pId == rId)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
else
{
return false;
}
}
private void ScheduleEmail(SlotRange range)
{
// create new Scheduled Task (email)
TaskSchedulerClass scheduler = new TaskSchedulerClass();
// TODO: here we may need to specify the user
scheduler.Connect(null, null, null, null); // CD: use current machine, username, password, domain
// Registration
ITaskDefinition task = scheduler.NewTask(0);
task.RegistrationInfo.Author = "GRcalcul";
task.RegistrationInfo.Description = "email linux script task";
task.RegistrationInfo.Date = DateTime.Now.ToString("yyyy-MM-ddTHH:MM:ss");
// Settings
task.Settings.MultipleInstances = _TASK_INSTANCES_POLICY.TASK_INSTANCES_IGNORE_NEW;
task.Settings.DisallowStartIfOnBatteries = false;
task.Settings.StopIfGoingOnBatteries = false;
task.Settings.AllowHardTerminate = true;
task.Settings.StartWhenAvailable = true;
task.Settings.RunOnlyIfNetworkAvailable = true;
task.Settings.AllowDemandStart = true;
task.Settings.Hidden = false;
task.Settings.WakeToRun = true;
task.Settings.ExecutionTimeLimit = "PT1H"; // 1 hour
task.Settings.DeleteExpiredTaskAfter = "PT12M"; // 1 year
ITimeTrigger trigger = (ITimeTrigger)task.Triggers.Create(_TASK_TRIGGER_TYPE2.TASK_TRIGGER_TIME);
trigger.Id = "EmailTriggerForSlotRange_" + range.id_slotRange;
DateTime dt = range.EndRes.Add(new System.TimeSpan(1, 0, 0, 0)); // Midnight after EndRes
trigger.StartBoundary = dt.ToString("yyyy-MM-ddTHH:MM:ss");
trigger.EndBoundary = dt.Add(new System.TimeSpan(0, 0, 1, 0)).ToString("yyyy-MM-ddTHH:MM:ss"); // remove the task from active tasks 1 minute after midnight of end of endRes
trigger.ExecutionTimeLimit = "PT2M"; // 2 minutes
IExecAction action = (IExecAction)task.Actions.Create(_TASK_ACTION_TYPE.TASK_ACTION_EXEC);
action.Id = "EmailActionForSlotRange_" + range.id_slotRange;
action.Path = "C:\\script.vbs";
action.Arguments = range.id_slotRange.ToString();
ITaskFolder root = scheduler.GetFolder("\\");
IRegisteredTask regTask = root.RegisterTaskDefinition(
"EmailTaskForSlotRange_" + range.id_slotRange,
task,
(int)_TASK_CREATION.TASK_CREATE_OR_UPDATE,
null, // username - we're using the logged in user of this web app
null, // password - we're using the logged in user of this web app
_TASK_LOGON_TYPE.TASK_LOGON_INTERACTIVE_TOKEN); // for simplicity, we're using the logged in Windows user who is running this web app
}
public ActionResult Duplicate(int id, SlotRange target)
{
SlotRange source = SlotRangeModel.GetSlotRange(id);
if (source == null)
{
return View("NoSuchRange");
}
int? rId = source.GetResponsible();
if (IsAuthorized(source))
{
TimeSpan span= target.StartRes - source.StartRes;
int days = (int)span.TotalDays;
SlotRangeModel.DuplicateSlotRange(source, days, source.IdCourse);
return RedirectToAction("CourseRanges", new { id = source.IdCourse});
}
else
{
SessionManager.RedirectAccessDenied(HttpContext.Request.RequestContext);
return null;
}
}
public ActionResult Edit(int id, SlotRange range)
{
if (IsAuthorized(range))
{
if (ModelState.IsValid)
{
try
{
SlotRangeModel.UpdateSlotRange(range);
// schedule linux script to be sent to resource manager by email
ScheduleEmail(range);
ViewBag.Mode = "mise a jour";
return View("Complete", range);
}
catch (GrException gex)
{
ModelState.AddModelError("", gex.UserMessage);
return View(range);
}
}
InitViewbag(range.IdCourse);
ModelState.AddModelError("", Messages.invalidData);
return View(range);
}
else
{
SessionManager.RedirectAccessDenied(HttpContext.Request.RequestContext);
return null;
}
}
public static void DuplicateSlotRange(SlotRange source, int days, int id_course)
{
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted);
try
{
SqlCommand cmd = new SqlCommand("sp_duplSlotRange", db, transaction);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = source.id_slotRange;
cmd.Parameters.Add("@days", SqlDbType.Int).Value = days;
cmd.Parameters.Add("@id_course", SqlDbType.Int).Value = id_course;
cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw new GrException(e, Messages.errProd);
}
finally
{
db.Close();
}
}
catch(Exception e)
{
if (e is GrException) throw e;
throw new GrException(e, Messages.errProd);
}
}
public ActionResult Delete(int id, SlotRange range)
{
SlotRange sr = SlotRangeModel.GetSlotRange(id);
if (IsAuthorized(SlotRangeModel.GetSlotRange(id)))
{
try
{
SlotRangeModel.DeleteSlotRange(id, range);
return RedirectToAction("CourseRanges", new { id= sr.IdCourse});
}
catch (GrException gex)
{
ModelState.AddModelError("", gex.UserMessage);
// get updated data
SlotRange range_ = SlotRangeModel.GetSlotRange(id);
// update timestamp in case user really wants to delete this
ModelState.SetModelValue("Timestamp", new ValueProviderResult(range_.Timestamp, "", CultureInfo.InvariantCulture));
// show new values before user decided to really delete them
return View(range_);
//ViewBag.ErrorMode = "la suppression";
//return View("Error", exx);
}
}
else
{
SessionManager.RedirectAccessDenied(HttpContext.Request.RequestContext);
return null;
}
}
public static void DeleteSlotRange(int id, SlotRange range)
{
string errMsg = "";
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.RepeatableRead);
try
{
byte[] timestamp = range.getByteTimestamp();
SqlCommand cmd = new SqlCommand("SELECT * " +
"FROM SlotRange S " +
"WHERE S.id_slotRange=@id AND S.timestamp=@timestamp;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.Parameters.Add("@timestamp", SqlDbType.Binary).Value = timestamp;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
rdr.Close();
cmd = new SqlCommand("DELETE FROM Slot WHERE id_slotRange=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.ExecuteNonQuery();
cmd = new SqlCommand("DELETE FROM MachineSlotRange WHERE id_slotRange=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.ExecuteNonQuery();
cmd = new SqlCommand("DELETE FROM SlotRange WHERE id_slotRange=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.ExecuteNonQuery();
}
else
{
rdr.Close();
errMsg += " " + Messages.recommencerDelete;
Console.WriteLine("Cross modify");
throw new GrException(Messages.recommencerDelete);
}
transaction.Commit();
}
catch (SqlException sqlError)
{
Console.WriteLine(sqlError);
transaction.Rollback();
throw new GrException(sqlError, Messages.errProd);
}
db.Close();
}
catch (Exception ex)
{
if (ex is GrException) throw ex;
throw new GrException(ex, Messages.errProd);
}
}
public static void CreateSlotRange(SlotRange range)
{
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted);
try
{
SqlCommand cmd = new SqlCommand("sp_getIdentitySlotRange", db, transaction);
SqlParameter sp = new SqlParameter("@id", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(sp);
cmd.Parameters.Add("@startRes", SqlDbType.Date);
cmd.Parameters.Add("@endRes", SqlDbType.Date);
cmd.Parameters.Add("@name", SqlDbType.Char);
cmd.Parameters.Add("@id_course", SqlDbType.Int);
cmd.Parameters["@startRes"].Value = range.StartRes;
cmd.Parameters["@endRes"].Value = range.EndRes;
cmd.Parameters["@name"].Value = range.Name;
cmd.Parameters["@id_course"].Value = range.IdCourse;
cmd.ExecuteNonQuery();
int rangeId = Int32.Parse(sp.Value.ToString());
range.id_slotRange = rangeId; // CD needed by Task Scheduler
foreach (var machine in range.Machines)
{
InsertMachines(machine, rangeId, db, transaction);
}
InsertAllSlots(range, rangeId, db, transaction);
transaction.Commit();
}
catch (Exception ex) // probably an SqlException, but just to be sure...
{
transaction.Rollback();
string message = Messages.errProd;
if (ex is SqlException)
{
SqlException sex = (SqlException)ex;
if (sex.Number > 50000)
{
message = sex.Message;
}
}
throw new GrException(ex, message);
}
finally
{
db.Close();
}
}
catch (Exception ex)
{
if (ex is GrException) throw ex;
throw new GrException(ex, Messages.errProd);
}
}
private static void InsertAllSlots(SlotRange range, int rangeId, SqlConnection db, SqlTransaction transaction)
{
// step 2 slots
InsertSlots(range.Startz, range.Endz, range.Slotdate, range.SlotDuration, rangeId, db, transaction);
// step 3 added slots
InsertSlots(range.StartzAdded, range.EndzAdded, range.SlotdateAdded, range.SlotDuration, rangeId, db, transaction);
}
public static void UpdateSlotRange(SlotRange range)
{
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.RepeatableRead);
try
{
byte[] timestamp = range.getByteTimestamp();
SqlCommand cmd = new SqlCommand("SELECT * FROM SlotRange R " +
"WHERE R.[id_slotRange]=@id AND R.timestamp=@timestamp;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
cmd.Parameters.Add("@timestamp", SqlDbType.Binary).Value = timestamp;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
rdr.Close();
cmd = new SqlCommand("SELECT CASE WHEN DATEDIFF(d, startRes, GETDATE ()) >= 0 AND DATEDIFF(d, GETDATE(), endRes) >= 0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END locked " +
"FROM SlotRange where id_slotRange=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
rdr = cmd.ExecuteReader();
Boolean isLocked = false;
if (rdr.Read())
{
isLocked = rdr.GetBoolean(rdr.GetOrdinal("locked")); ;
}
rdr.Close();
cmd = new SqlCommand("UPDATE SlotRange SET startRes=@startRes, endRes=@endRes, " +
"name=@name, id_course=@idCourse WHERE id_slotRange=@id;", db, transaction);
cmd.Parameters.Add("@startRes", SqlDbType.DateTime).Value = range.StartRes;
cmd.Parameters.Add("@endRes", SqlDbType.DateTime).Value = range.EndRes;
cmd.Parameters.Add("@name", SqlDbType.Char).Value = range.Name;
cmd.Parameters.Add("@idCourse", SqlDbType.Int).Value = range.IdCourse;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
cmd.ExecuteNonQuery();
if (!isLocked)
{
SqlCommand cmd2 = new SqlCommand("DELETE FROM Slot WHERE id_slotRange=@id;", db, transaction);
cmd2.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
cmd2.ExecuteNonQuery();
//do slots
InsertAllSlots(range, range.id_slotRange, db, transaction);
}
//do machines
//we update only as long as we keep at least as many machines as there already are in the database for this slotrange
SqlCommand cmd3 = new SqlCommand("SELECT COUNT(id_slotRange) as cpt " +
"FROM [GR-Calcul].[dbo].[MachineSlotRange] WHERE id_slotRange=@id;", db, transaction);
cmd3.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
SqlDataReader rdr2 = cmd3.ExecuteReader();
int nMachines = 0;
if (rdr2.Read())
{
nMachines = rdr2.GetInt32(rdr2.GetOrdinal("cpt"));
}
rdr2.Close();
if (range.Machines.Count >= nMachines)
{
SqlCommand cmd4 = new SqlCommand("DELETE FROM MachineSlotRange WHERE id_slotRange=@id;", db, transaction);
cmd4.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
cmd4.ExecuteNonQuery();
foreach (var machine in range.Machines)
{
InsertMachines(machine, range.id_slotRange, db, transaction);
}
}
transaction.Commit();
}
else
{
rdr.Close();
Console.WriteLine("Cross modify");
throw new GrException(Messages.recommencerEdit);
}
}
catch (Exception ex)
{
transaction.Rollback();
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
throw new GrException(ex, Messages.errProd);
}
finally
{
db.Close();
}
}
catch (Exception ex)
{
if (ex is GrException) throw ex;
throw new GrException(ex, Messages.errProd);
}
}
public static SlotRange GetSlotRange(int id)
{
SlotRange range = null;
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
//SqlCommand cmd = new SqlCommand("SELECT [startRes] ,[endRes] ," +
// "[name] ,[id_course] , convert(int, [timestamp]) as timestamp FROM SlotRange WHERE id_slotRange=@id;", db, transaction);
//SqlCommand cmd = new SqlCommand("SELECT [startRes] ,[endRes] ," +
// "[name] ,[id_course] , [timestamp], " +
// "CASE WHEN DATEDIFF(d, startRes, GETDATE ()) >= 0 AND DATEDIFF(d, GETDATE(), endRes) >= 0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END locked " +
// "FROM SlotRange WHERE id_slotRange=@id;", db, transaction);
SqlCommand cmd = new SqlCommand("SELECT [startRes] ,[endRes] ," +
"[name] ,[id_course] , [timestamp] " +
"FROM SlotRange WHERE id_slotRange=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
SqlDataReader rdr = cmd.ExecuteReader();
bool hasFound = false;
if (rdr.Read())
{
DateTime startRes = rdr.GetDateTime(rdr.GetOrdinal("startRes"));
DateTime endRes = rdr.GetDateTime(rdr.GetOrdinal("endRes"));
string name = rdr.GetString(rdr.GetOrdinal("name"));
int id_course = rdr.GetInt32(rdr.GetOrdinal("id_course"));
//Boolean locked = rdr.GetBoolean(rdr.GetOrdinal("locked"));
//range = new SlotRange(id, startRes, endRes, name, id_course, locked);
range = new SlotRange(id, startRes, endRes, name, id_course);
//range.Timestamp = rdr.GetInt32(rdr.GetOrdinal("timestamp"));
byte[] buffer = new byte[100];
rdr.GetBytes(rdr.GetOrdinal("timestamp"), 0, buffer, 0, 100);
range.setTimestamp(buffer);
hasFound = true;
}
rdr.Close();
if (!hasFound)
{
return null;
}
//get machines
SqlCommand cmd2 = new SqlCommand("SELECT MSR.[id_machine] " +
"FROM MachineSlotRange MSR " +
"WHERE id_slotRange=@id;", db, transaction);
cmd2.Parameters.Add("@id", SqlDbType.Int).Value = id;
SqlDataReader rdr2 = cmd2.ExecuteReader();
while (rdr2.Read())
{
int id_machine = rdr2.GetInt32(rdr2.GetOrdinal("id_machine"));
range.Machines.Add(id_machine);
}
rdr2.Close();
//get slots
cmd2 = new SqlCommand("SELECT [start], [end] FROM Slot WHERE id_slotRange=@id;", db, transaction);
cmd2.Parameters.Add("@id", SqlDbType.Int).Value = id;
rdr2 = cmd2.ExecuteReader();
//bool hasSetDuration = false;
int cpt = 0;
while (rdr2.Read())
{
DateTime start = rdr2.GetDateTime(rdr2.GetOrdinal("start"));
DateTime end = rdr2.GetDateTime(rdr2.GetOrdinal("end"));
int startHour = start.Hour;
int startMinute = start.Minute;
int endHour = end.Hour;
int endMinute = end.Minute;
range.Startz.Add(startHour + ":" + startMinute);
range.Endz.Add(endHour + ":" + endMinute);
if (!range.Slotdate.Contains(start.Date))
{
range.Slotdate.Add(start.Date);
}
cpt++;
range.Slots.Add(new Slot(start, end));
}
rdr2.Close();
range.NumberOfSlots = cpt;
transaction.Commit();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
transaction.Rollback();
throw new GrException(ex, Messages.errProd);
}
finally
{
db.Close();
}
}
catch(Exception ex)
{
if (ex is GrException) throw ex;
throw new GrException(ex, Messages.errProd);
}
return range;
}
