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; }