/// <summary>
/// Creates the object from the data returned from the database
/// </summary>
/// <returns></returns>
private FacilitySchedule CreateFacilityScheduleFromData()
{
var facilitySchedule = new FacilitySchedule
{
Id = int.Parse(_dataEngine.Dr["Id"].ToString()),
FacilityScheduleDescription = _dataEngine.Dr["FacilityScheduleDescription"].ToString(),
StartMinuteMonday = int.Parse(_dataEngine.Dr["StartMinuteMonday"].ToString()),
EndMinuteMonday = int.Parse(_dataEngine.Dr["EndMinuteMonday"].ToString()),
MondayFacilityBookLength = int.Parse(_dataEngine.Dr["MondayFacilityBookLength"].ToString()),
StartMinuteTuesday = int.Parse(_dataEngine.Dr["StartMinuteTuesday"].ToString()),
EndMinuteTuesday = int.Parse(_dataEngine.Dr["EndMinuteTuesday"].ToString()),
TuesdayFacilityBookLength = int.Parse(_dataEngine.Dr["TuesdayFacilityBookLength"].ToString()),
StartMinuteWednesday = int.Parse(_dataEngine.Dr["StartMinuteWednesday"].ToString()),
EndMinuteWednesday = int.Parse(_dataEngine.Dr["EndMinuteWednesday"].ToString()),
WednesdayFacilityBookLength = int.Parse(_dataEngine.Dr["WednesdayFacilityBookLength"].ToString()),
StartMinuteThursday = int.Parse(_dataEngine.Dr["StartMinuteThursday"].ToString()),
EndMinuteThursday = int.Parse(_dataEngine.Dr["EndMinuteThursday"].ToString()),
ThursdayFacilityBookLength = int.Parse(_dataEngine.Dr["ThursdayFacilityBookLength"].ToString()),
StartMinuteFriday = int.Parse(_dataEngine.Dr["StartMinuteFriday"].ToString()),
EndMinuteFriday = int.Parse(_dataEngine.Dr["EndMinuteFriday"].ToString()),
FridayFacilityBookLength = int.Parse(_dataEngine.Dr["FridayFacilityBookLength"].ToString()),
StartMinuteSaturday = int.Parse(_dataEngine.Dr["StartMinuteSaturday"].ToString()),
EndMinuteSaturday = int.Parse(_dataEngine.Dr["EndMinuteSaturday"].ToString()),
SaturdayFacilityBookLength = int.Parse(_dataEngine.Dr["SaturdayFacilityBookLength"].ToString()),
StartMinuteSunday = int.Parse(_dataEngine.Dr["StartMinuteSunday"].ToString()),
EndMinuteSunday = int.Parse(_dataEngine.Dr["EndMinuteSunday"].ToString()),
SundayFacilityBookLength = int.Parse(_dataEngine.Dr["SundayFacilityBookLength"].ToString())
};
return(facilitySchedule);
}
public void Update(FacilitySchedule saveThis)
{
_dataEngine.InitialiseParameterList();
_dataEngine.AddParameter("@FacilityBookAheadDays", saveThis.FacilityScheduleDescription);
_dataEngine.AddParameter("@StartMinuteMonday", saveThis.StartMinuteMonday.ToString());
_dataEngine.AddParameter("@EndMinuteMonday", saveThis.EndMinuteMonday.ToString());
_dataEngine.AddParameter("@MondayFacilityBookLength", saveThis.MondayFacilityBookLength.ToString());
_dataEngine.AddParameter("@StartMinuteTuesday", saveThis.StartMinuteTuesday.ToString());
_dataEngine.AddParameter("@EndMinuteTuesday", saveThis.EndMinuteTuesday.ToString());
_dataEngine.AddParameter("@TuesdayFacilityBookLength", saveThis.TuesdayFacilityBookLength.ToString());
_dataEngine.AddParameter("@StartMinuteWednesday", saveThis.StartMinuteWednesday.ToString());
_dataEngine.AddParameter("@EndMinuteWednesday", saveThis.EndMinuteWednesday.ToString());
_dataEngine.AddParameter("@WednesdayFacilityBookLength", saveThis.WednesdayFacilityBookLength.ToString());
_dataEngine.AddParameter("@StartMinuteThursday", saveThis.StartMinuteThursday.ToString());
_dataEngine.AddParameter("@EndMinuteThursday", saveThis.EndMinuteThursday.ToString());
_dataEngine.AddParameter("@ThursdayFacilityBookLength", saveThis.ThursdayFacilityBookLength.ToString());
_dataEngine.AddParameter("@StartMinuteFriday", saveThis.StartMinuteFriday.ToString());
_dataEngine.AddParameter("@EndMinuteFriday", saveThis.EndMinuteFriday.ToString());
_dataEngine.AddParameter("@FridayFacilityBookLength", saveThis.FridayFacilityBookLength.ToString());
_dataEngine.AddParameter("@StartMinuteSaturday", saveThis.StartMinuteSaturday.ToString());
_dataEngine.AddParameter("@EndMinuteSaturday", saveThis.EndMinuteSaturday.ToString());
_dataEngine.AddParameter("@SaturdayFacilityBookLength", saveThis.SaturdayFacilityBookLength.ToString());
_dataEngine.AddParameter("@StartMinuteSunday", saveThis.StartMinuteSunday.ToString());
_dataEngine.AddParameter("@EndMinuteSunday", saveThis.EndMinuteSunday.ToString());
_dataEngine.AddParameter("@SundayFacilityBookLength", saveThis.SundayFacilityBookLength.ToString());
_sqlToExecute = "UPDATE [dbo].[FacilitySchedule] SET ";
_sqlToExecute += "[FacilityScheduleDescription] = @FacilityBookAheadDays";
_sqlToExecute += ",[StartMinuteMonday] = @StartMinuteMonday";
_sqlToExecute += ",[EndMinuteMonday] = @EndMinuteMonday";
_sqlToExecute += ",[MondayFacilityBookLength] = @MondayFacilityBookLength";
_sqlToExecute += ",[StartMinuteTuesday] = @StartMinuteTuesday";
_sqlToExecute += ",[EndMinuteTuesday] = @EndMinuteTuesday";
_sqlToExecute += ",[TuesdayFacilityBookLength] = @TuesdayFacilityBookLength";
_sqlToExecute += ",[StartMinuteWednesday] = @StartMinuteWednesday";
_sqlToExecute += ",[EndMinuteWednesday] = @EndMinuteWednesday";
_sqlToExecute += ",[WednesdayFacilityBookLength] = @WednesdayFacilityBookLength";
_sqlToExecute += ",[StartMinuteThursday] = @StartMinuteThursday";
_sqlToExecute += ",[EndMinuteThursday] = @EndMinuteThursday";
_sqlToExecute += ",[ThursdayFacilityBookLength] = @ThursdayFacilityBookLength";
_sqlToExecute += ",[StartMinuteFriday] = @StartMinuteFriday";
_sqlToExecute += ",[EndMinuteFriday] = @EndMinuteFriday";
_sqlToExecute += ",[FridayFacilityBookLength] = @FridayFacilityBookLength";
_sqlToExecute += ",[StartMinuteSaturday] = @StartMinuteSaturday";
_sqlToExecute += ",[EndMinuteSaturday] = @EndMinuteSaturday";
_sqlToExecute += ",[SaturdayFacilityBookLength] = @SaturdayFacilityBookLength";
_sqlToExecute += ",[StartMinuteSunday] = @StartMinuteSunday";
_sqlToExecute += ",[EndMinuteSunday] = @EndMinuteSunday";
_sqlToExecute += ",[SundayFacilityBookLength] = @SundayFacilityBookLength ";
_sqlToExecute += "WHERE [Id] = " + saveThis.Id;
if (!_dataEngine.ExecuteSql(_sqlToExecute))
{
throw new Exception("FacilitySchedule - Update failed");
}
}
public void AddSchedule(FacilitySchedule schedule)
{
foreach (var evnt in schedule)
{
_worksheet.Cells[_insertRow, IdCell] = evnt.FacilityId;
_worksheet.Cells[_insertRow, DayOfWeekCell] = evnt.DayOfWeek.ToString();
_worksheet.Cells[_insertRow, TxStartCell] = evnt.DataTransmissionStart;
_worksheet.Cells[_insertRow, DurationCell] = evnt.DurationHours.ToString();
++_insertRow;
}
}
public void Delete(FacilitySchedule deleteThis)
{
_dataEngine.InitialiseParameterList();
_dataEngine.AddParameter("@Id", deleteThis.Id.ToString());
_sqlToExecute = "DELETE FROM [dbo].[FacilitySchedule] WHERE Id = " + _dataEngine.GetParametersForQuery();
if (!_dataEngine.ExecuteSql(_sqlToExecute))
{
throw new Exception("FacilitySchedule - Delete failed");
}
}
public void Update(FacilitySchedule updateThis)
{
try
{
_db.FacilitySchedules.Attach(updateThis);
_db.Refresh(RefreshMode.KeepCurrentValues, updateThis);
_db.SubmitChanges();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public int Insert(FacilitySchedule saveThis)
{
try
{
_db.FacilitySchedules.InsertOnSubmit(saveThis);
_db.SubmitChanges();
return(saveThis.Id);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public void CreateFacilitySchedule(FacilitySchedule facilitySchedule)
{
//Save the facility schedule record
var insertedRowId = _da.Value.FacilitySchedule.Insert(facilitySchedule);
if (insertedRowId == 0)
{
throw new Exception("Failed to create facility schdule record");
}
else
{
facilitySchedule.Id = insertedRowId;
}
}
public void Delete(FacilitySchedule deleteThis)
{
try
{
if (deleteThis.Id > 0)
{
_db.FacilitySchedules.DeleteOnSubmit(deleteThis);
_db.SubmitChanges(ConflictMode.FailOnFirstConflict);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
private FacilitySchedule CreateFacilitySchedule(Facility facility)
{
var schedule = new FacilitySchedule();
int start = 0;
for (int day = 1; day <= 7; ++day)
{
var scheduledEvent = new FacilityScheduledEvent()
{
FacilityId = facility.Id,
DayOfWeek = day,
DataTransmissionStart = String.Format("00:{0:00}", start++),
DurationHours = _options.Duration
};
schedule.Add(scheduledEvent);
}
return(schedule);
}
public IQueryable <FacilitySchedule> GetAll()
{
var facilityScheduleList = new List <FacilitySchedule>();
_sqlToExecute = "SELECT * FROM [dbo].[FacilitySchedule]";
if (!_dataEngine.CreateReaderFromSql(_sqlToExecute))
{
throw new Exception("FacilitySchedule - GetAll failed");
}
while (_dataEngine.Dr.Read())
{
FacilitySchedule facilitySchedule = CreateFacilityScheduleFromData();
facilityScheduleList.Add(facilitySchedule);
}
return(facilityScheduleList.AsQueryable());
}
public void f_Create_Facility_Schedule_Master()
{
var facilitySchedule = new FacilitySchedule()
{
FacilityScheduleDescription = "Squash 40 minute slots start at 9am finish at 10pm",
StartMinuteMonday = 540,
EndMinuteMonday = 1320,
MondayFacilityBookLength = 40,
StartMinuteTuesday = 540,
EndMinuteTuesday = 1320,
TuesdayFacilityBookLength = 40,
StartMinuteWednesday = 540,
EndMinuteWednesday = 1320,
WednesdayFacilityBookLength = 40,
StartMinuteThursday = 540,
EndMinuteThursday = 1320,
ThursdayFacilityBookLength = 40,
StartMinuteFriday = 540,
EndMinuteFriday = 1320,
FridayFacilityBookLength = 40,
StartMinuteSaturday = 540,
EndMinuteSaturday = 1320,
SaturdayFacilityBookLength = 40,
StartMinuteSunday = 540,
EndMinuteSunday = 1320,
SundayFacilityBookLength = 40
};
TestingSetupClass._logic.FacilityScheduleLogic.CreateFacilitySchedule(facilitySchedule);
if (facilitySchedule.Id == 0)
{
Assert.Fail("Facility schedule was not created, ID is 0");
}
else
{
Assert.Pass("Facility schdule created with ID of {0}", facilitySchedule.Id);
}
}
public FacilitySchedule GetById(int id)
{
_dataEngine.InitialiseParameterList();
_dataEngine.AddParameter("@Id", id.ToString());
_sqlToExecute = "SELECT * FROM [dbo].[FacilitySchedule] WHERE Id = " + _dataEngine.GetParametersForQuery();
if (!_dataEngine.CreateReaderFromSql(_sqlToExecute))
{
throw new Exception("FacilitySchedule - GetById failed");
}
if (_dataEngine.Dr.Read())
{
FacilitySchedule facilitySchedule = CreateFacilityScheduleFromData();
return(facilitySchedule);
}
else
{
throw new Exception("FacilitySchedule Id " + id.ToString() + " does not exist in database");
}
}
public int Insert(FacilitySchedule saveThis)
{
_dataEngine.InitialiseParameterList();
_dataEngine.AddParameter("@FacilityBookAheadDays", saveThis.FacilityScheduleDescription);
_dataEngine.AddParameter("@StartMinuteMonday", saveThis.StartMinuteMonday.ToString());
_dataEngine.AddParameter("@EndMinuteMonday", saveThis.EndMinuteMonday.ToString());
_dataEngine.AddParameter("@MondayFacilityBookLength", saveThis.MondayFacilityBookLength.ToString());
_dataEngine.AddParameter("@StartMinuteTuesday", saveThis.StartMinuteTuesday.ToString());
_dataEngine.AddParameter("@EndMinuteTuesday", saveThis.EndMinuteTuesday.ToString());
_dataEngine.AddParameter("@TuesdayFacilityBookLength", saveThis.TuesdayFacilityBookLength.ToString());
_dataEngine.AddParameter("@StartMinuteWednesday", saveThis.StartMinuteWednesday.ToString());
_dataEngine.AddParameter("@EndMinuteWednesday", saveThis.EndMinuteWednesday.ToString());
_dataEngine.AddParameter("@WednesdayFacilityBookLength", saveThis.WednesdayFacilityBookLength.ToString());
_dataEngine.AddParameter("@StartMinuteThursday", saveThis.StartMinuteThursday.ToString());
_dataEngine.AddParameter("@EndMinuteThursday", saveThis.EndMinuteThursday.ToString());
_dataEngine.AddParameter("@ThursdayFacilityBookLength", saveThis.ThursdayFacilityBookLength.ToString());
_dataEngine.AddParameter("@StartMinuteFriday", saveThis.StartMinuteFriday.ToString());
_dataEngine.AddParameter("@EndMinuteFriday", saveThis.EndMinuteFriday.ToString());
_dataEngine.AddParameter("@FridayFacilityBookLength", saveThis.FridayFacilityBookLength.ToString());
_dataEngine.AddParameter("@StartMinuteSaturday", saveThis.StartMinuteSaturday.ToString());
_dataEngine.AddParameter("@EndMinuteSaturday", saveThis.EndMinuteSaturday.ToString());
_dataEngine.AddParameter("@SaturdayFacilityBookLength", saveThis.SaturdayFacilityBookLength.ToString());
_dataEngine.AddParameter("@StartMinuteSunday", saveThis.StartMinuteSunday.ToString());
_dataEngine.AddParameter("@EndMinuteSunday", saveThis.EndMinuteSunday.ToString());
_dataEngine.AddParameter("@SundayFacilityBookLength", saveThis.SundayFacilityBookLength.ToString());
_sqlToExecute = "INSERT INTO [dbo].[FacilitySchedule] ";
_sqlToExecute += "([FacilityScheduleDescription]";
_sqlToExecute += ",[StartMinuteMonday]";
_sqlToExecute += ",[EndMinuteMonday]";
_sqlToExecute += ",[MondayFacilityBookLength]";
_sqlToExecute += ",[StartMinuteTuesday]";
_sqlToExecute += ",[EndMinuteTuesday]";
_sqlToExecute += ",[TuesdayFacilityBookLength]";
_sqlToExecute += ",[StartMinuteWednesday]";
_sqlToExecute += ",[EndMinuteWednesday]";
_sqlToExecute += ",[WednesdayFacilityBookLength]";
_sqlToExecute += ",[StartMinuteThursday]";
_sqlToExecute += ",[EndMinuteThursday]";
_sqlToExecute += ",[ThursdayFacilityBookLength]";
_sqlToExecute += ",[StartMinuteFriday]";
_sqlToExecute += ",[EndMinuteFriday]";
_sqlToExecute += ",[FridayFacilityBookLength]";
_sqlToExecute += ",[StartMinuteSaturday]";
_sqlToExecute += ",[EndMinuteSaturday]";
_sqlToExecute += ",[SaturdayFacilityBookLength]";
_sqlToExecute += ",[StartMinuteSunday]";
_sqlToExecute += ",[EndMinuteSunday]";
_sqlToExecute += ",[SundayFacilityBookLength]) ";
_sqlToExecute += "OUTPUT INSERTED.Id ";
_sqlToExecute += "VALUES ";
_sqlToExecute += "(";
_sqlToExecute += _dataEngine.GetParametersForQuery();
_sqlToExecute += ")";
int insertedRowId = 0;
if (!_dataEngine.ExecuteSql(_sqlToExecute, out insertedRowId))
{
throw new Exception("FacilitySchedule - Save failed");
}
return(insertedRowId);
}
