public static TimeBlock[] GetAvailableTimePeriods(string serviceBrokerGuid, string groupName, string ussGuid,
string labServerGuid, string clientGuid, DateTime startTime, DateTime endTime)
{
List<Recurrence> recList = new List<Recurrence>();
List<TimeBlock> tpList = new List<TimeBlock>();
// create sql connection
DbConnection connection = FactoryDB.GetConnection();
// create sql command
// command executes the "RetrieveRecurrences" stored procedure
DbCommand cmd = FactoryDB.CreateCommand("Recurrences_Retrieve", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@sbGUID", serviceBrokerGuid, DbType.AnsiString, 50));
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@group", groupName, DbType.String, 256));
//cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@ussGUID", ussGuid, DbType.AnsiString, 50));
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@clientGuid", clientGuid, DbType.AnsiString, 50));
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@lsGuid", labServerGuid, DbType.AnsiString, 50));
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@start", DateUtil.SpecifyUTC(startTime), DbType.DateTime));
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@end", DateUtil.SpecifyUTC(endTime), DbType.DateTime));
DbDataReader dataReader = null;
try
{
connection.Open();
dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
Recurrence rec = new Recurrence();
if (dataReader[1] != System.DBNull.Value)
rec.recurrenceId = (int)dataReader.GetInt32(1);
if (dataReader[2] != System.DBNull.Value)
rec.resourceId = (int)dataReader.GetInt32(2);
if (dataReader[3] != System.DBNull.Value)
rec.recurrenceType = (Recurrence.RecurrenceType)dataReader.GetInt32(3);
if (dataReader[4] != System.DBNull.Value)
rec.dayMask = dataReader.GetByte(4);
if (dataReader[5] != System.DBNull.Value)
rec.startDate = DateUtil.SpecifyUTC(dataReader.GetDateTime(5));
if (dataReader[6] != System.DBNull.Value)
rec.numDays = dataReader.GetInt32(6);
if (dataReader[7] != System.DBNull.Value)
rec.startOffset = TimeSpan.FromSeconds(dataReader.GetInt32(7));
if (dataReader[8] != System.DBNull.Value)
rec.endOffset = TimeSpan.FromSeconds(dataReader.GetInt32(8));
if (dataReader[9] != System.DBNull.Value)
rec.quantum = dataReader.GetInt32(9);
recList.Add(rec);
}
connection.Close();
}
catch (Exception e)
{
throw;
}
if (recList.Count > 0)
{
return tpList.ToArray();
}
else return null;
}
/// <summary>
/// Returns an array of the immutable Recurrence objects that correspond ot the supplied Recurrence IDs
/// </summary>
/// <param name="timeBlockIDs"></param>
/// <returns></returns>an array of immutable objects describing the specified Recurrence
public static Recurrence[] GetRecurrences(int[] recurrenceIDs)
{
Recurrence[] recurrences = new Recurrence[recurrenceIDs.Length];
for (int i = 0; i < recurrenceIDs.Length; i++)
{
recurrences[i] = new Recurrence();
}
// create sql connection
DbConnection connection = FactoryDB.GetConnection();
// create sql command
// command executes the "RetrieveRecurrenceByID" stored procedure
DbCommand cmd = FactoryDB.CreateCommand("Recurrence_RetrieveByID", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@recurrenceID", null, DbType.Int32));
//execute the command
try
{
connection.Open();
for (int i = 0; i < recurrenceIDs.Length; i++)
{
// populate the parameters
cmd.Parameters["@recurrenceID"].Value = recurrenceIDs[i];
DbDataReader dataReader = null;
dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
recurrences[i].recurrenceId = recurrenceIDs[i];
if (dataReader[0] != System.DBNull.Value)
recurrences[i].startDate = DateUtil.SpecifyUTC(dataReader.GetDateTime(0));
if (dataReader[1] != System.DBNull.Value)
recurrences[i].numDays = dataReader.GetInt32(1);
if (dataReader[2] != System.DBNull.Value)
recurrences[i].recurrenceType = (Recurrence.RecurrenceType) dataReader.GetInt32(2);
if (dataReader[3] != System.DBNull.Value)
{
recurrences[i].startOffset = TimeSpan.FromSeconds((double)dataReader.GetInt32(3));
}
if (dataReader[4] != System.DBNull.Value)
recurrences[i].endOffset = TimeSpan.FromSeconds((double)dataReader.GetInt32(4));
if (dataReader[5] != System.DBNull.Value)
recurrences[i].quantum = dataReader.GetInt32(5);
if (dataReader[6] != System.DBNull.Value)
recurrences[i].resourceId = (int)dataReader.GetInt32(6);
if (dataReader[7] != System.DBNull.Value)
recurrences[i].dayMask = dataReader.GetByte(7);
}
dataReader.Close();
}
}
catch (Exception ex)
{
throw new Exception("Exception thrown in get recurrence", ex);
}
finally
{
connection.Close();
}
return recurrences;
}
public static Recurrence[] GetRecurrences(string serviceBrokerGuid, string groupName,
string labServerGuid, string clientGuid, DateTime startTime, DateTime endTime)
{
TimeBlock range = new TimeBlock(startTime, endTime);
List<Recurrence> recurrences = new List<Recurrence>();
DbConnection connection = FactoryDB.GetConnection();
// create sql command
// command executes the "RetrieveRecurrenceIDs" stored procedure
DbCommand cmd = FactoryDB.CreateCommand("Recurrences_Retrieve", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@sbGuid", serviceBrokerGuid, DbType.AnsiString, 50));
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@group", groupName, DbType.String, 256));
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@lsGuid", labServerGuid, DbType.AnsiString, 50));
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@clientGuid", clientGuid, DbType.AnsiString, 50));
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@start", DateUtil.SpecifyUTC(startTime), DbType.DateTime));
cmd.Parameters.Add(FactoryDB.CreateParameter(cmd,"@end", DateUtil.SpecifyUTC(endTime), DbType.DateTime));
try
{
DbDataReader dataReader = null;
Recurrence recur = null;
connection.Open();
//recurrence_id,resource_id,recurrence_type,day_mask,recurrence_start_date,recurrence_num_days
//recurrence_start_offset,recurrence_end_offset, quantum
dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
recur = new Recurrence();
recur.recurrenceId = dataReader.GetInt32(0);
recur.resourceId = dataReader.GetInt32(1);
recur.recurrenceType = (Recurrence.RecurrenceType)dataReader.GetInt32(2);
recur.dayMask = dataReader.GetByte(3);
recur.startDate = DateUtil.SpecifyUTC(dataReader.GetDateTime(4));
recur.numDays = dataReader.GetInt32(5);
recur.startOffset = TimeSpan.FromSeconds((double)dataReader.GetInt32(6));
recur.endOffset = TimeSpan.FromSeconds((double)dataReader.GetInt32(7));
recur.quantum = dataReader.GetInt32(8);
TimeBlock[] blocks = recur.GetTimeBlocks();
foreach (TimeBlock tb in blocks)
{
if (range.Intersects(tb))
{
recurrences.Add(recur);
break;
}
}
}
dataReader.Close();
}
catch (Exception ex)
{
throw new Exception("Exception thrown in get recurrences", ex);
}
finally
{
connection.Close();
}
return recurrences.ToArray();
}
/// <summary>
/// Parse the user time displayed values and convert to UTC 'user reperesentations', add user offset before
/// inserting into the database.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSave_Click(object sender, EventArgs e)
{
DateTime startDate = DateTime.MinValue;
TimeSpan startTime = TimeSpan.MinValue;
int startHours = -1;
int startMinutes = -1;
DateTime endDate = DateTime.MinValue;
TimeSpan endTime = TimeSpan.MinValue;
int endHours = -1;
int endMinutes = -1;
int recurType = 0;
int quan = 0;
// input error check
try
{
if (ddlLabServers.SelectedIndex <= 0)
{
lblErrorMessage.Text = iLabs.UtilLib.Utilities.FormatWarningMessage("You must select a Lab Server Resource.");
lblErrorMessage.Visible = true;
return;
}
if (ddlRecurrence.SelectedIndex <= 0)
{
lblErrorMessage.Text = iLabs.UtilLib.Utilities.FormatWarningMessage("You must select a recurrence type.");
lblErrorMessage.Visible = true;
return;
}
// Local System date forced to UTC type
if (txtStartDate.Text.Length == 0 || txtStartDate.Text.CompareTo(culture.DateTimeFormat.ShortDatePattern) == 0)
{
lblErrorMessage.Text = iLabs.UtilLib.Utilities.FormatWarningMessage("You must enter the start date of the recurring time block.");
lblErrorMessage.Visible = true;
return;
}
startDate = DateTime.SpecifyKind(DateTime.Parse(txtStartDate.Text, culture), DateTimeKind.Utc);
// Local System date forced to UTC type
if (txtEndDate.Text.Length == 0 || txtEndDate.Text.CompareTo(culture.DateTimeFormat.ShortDatePattern) == 0)
{
lblErrorMessage.Text = iLabs.UtilLib.Utilities.FormatWarningMessage("You must enter the end date of the recurring time block.");
lblErrorMessage.Visible = true;
return;
}
endDate = DateTime.SpecifyKind(DateTime.Parse(txtEndDate.Text, culture), DateTimeKind.Utc);
endDate = endDate.Add(TimeSpan.FromDays(1.0));
if (endDate <= startDate)
{
lblErrorMessage.Text = iLabs.UtilLib.Utilities.FormatWarningMessage("The start date must be less than or equal to the end date.");
lblErrorMessage.Visible = true;
return;
}
startHours = ddlStartHour.SelectedIndex;
if (ddlStartAM.Text.CompareTo("PM") == 0)
{
startHours += 12;
}
if (txtStartMin.Text.Length > 0)
startMinutes = int.Parse(txtStartMin.Text);
if (startMinutes >= 60 || startMinutes < 0)
{
string msg = "Please input minutes ( 0 - 59 ) in the start time ";
lblErrorMessage.Text = iLabs.UtilLib.Utilities.FormatWarningMessage(msg);
lblErrorMessage.Visible = true;
return;
}
endHours = ddlEndHour.SelectedIndex;
if (ddlEndAM.Text.CompareTo("PM") == 0)
{
endHours += 12;
}
if (txtEndMin.Text.Length > 0)
endMinutes = int.Parse(txtEndMin.Text);
if (endMinutes >= 60 || endMinutes < 0)
{
string msg = "Please input minutes ( 0 - 59 ) in the end time ";
lblErrorMessage.Text = iLabs.UtilLib.Utilities.FormatWarningMessage(msg);
lblErrorMessage.Visible = true;
return;
}
startTime = new TimeSpan(startHours, startMinutes, 0);
endTime = new TimeSpan(endHours, endMinutes, 0);
if (endHours == 0 && endMinutes == 0)
endTime = endTime.Add(TimeSpan.FromHours(24));
if (startTime >= endTime)
{
// If confirm see JavaScript
if(endDate.Subtract(startDate) > TimeSpan.FromDays(1.0)){
endTime = endTime.Add(TimeSpan.FromDays(1.0));
}
if (startTime >= endTime)
{
lblErrorMessage.Text = iLabs.UtilLib.Utilities.FormatWarningMessage("the start time should be earlier than the end time.");
lblErrorMessage.Visible = true;
return;
}
}
}
catch (Exception ex)
{
string msg = ex.Message;
lblErrorMessage.Text = Utilities.FormatErrorMessage(msg);
lblErrorMessage.Visible = true;
return;
}
if (txtQuantum.Text.CompareTo("") == 0)
{
lblErrorMessage.Text = Utilities.FormatWarningMessage("You must enter the Quantum of the Recurrence.");
lblErrorMessage.Visible = true;
return;
}
try
{
quan = Int32.Parse(txtQuantum.Text);
}
catch (Exception ex)
{
lblErrorMessage.Text = Utilities.FormatWarningMessage("Please enter a positive integer in the Quantum text box.");
lblErrorMessage.Visible = true;
return;
}
if (quan <= 0)
{
lblErrorMessage.Text = Utilities.FormatWarningMessage("Please enter an integer value greater than Zero in the Quantum text box.");
lblErrorMessage.Visible = true;
return;
}
//If all the input error checks are cleared
//add a new Recurrence
try
{
Recurrence recur = new Recurrence();
recur.resourceId = int.Parse(ddlLabServers.SelectedValue);
recur.recurrenceType = (Recurrence.RecurrenceType) int.Parse(ddlRecurrence.SelectedValue);
TimeSpan numDays = endDate - startDate;
numDays.Add(TimeSpan.FromDays(1));
recur.numDays = (int)numDays.TotalDays;
recur.startDate = startDate.AddMinutes(-localTzOffset);
recur.startOffset = startTime;
recur.endOffset = endTime;
recur.dayMask = 0;
recur.quantum = quan;
if(recur.recurrenceType == Recurrence.RecurrenceType.Weekly){
foreach (ListItem it in cbxRecurWeekly.Items)
{
if (it.Selected)
{
switch (it.Text)
{
case "Sunday":
recur.dayMask |= DateUtil.SunBit;
break;
case "Monday":
recur.dayMask |= DateUtil.MonBit;
break;
case "Tuesday":
recur.dayMask |= DateUtil.TuesBit;
break;
case "Wednesday":
recur.dayMask |= DateUtil.WedBit;
break;
case "Thursday":
recur.dayMask |= DateUtil.ThursBit;
break;
case "Friday":
recur.dayMask |= DateUtil.FriBit;
break;
case "Saturday":
recur.dayMask |= DateUtil.SatBit;
break;
default:
break;
}
//repeatDays.Add(it.Text);
}
}
}
//CheckValid is not written
//string message = recur.CheckValid();
//if( message != null){
// lblErrorMessage.Text = iLabs.UtilLib.Utilities.FormatWarningMessage(message);
// lblErrorMessage.Visible = true;
// return;
//}
//Create UTC copies of the the startTime and EndTime, to check Recurrence
DateTime uStartTime = recur.Start;
DateTime uEndTime = recur.End;
// the database will also throw an exception if the combination of start time,
// end time,lab server id, resource_id exists, or there are some
// Recurrences are included in the recurrence to be added.
// since combination of them must be unique and the time slots should
// be be overlapped
// this is just another check to throw a meaningful exception
// should only get & check recurrences on this resource
Recurrence[] recurs = dbManager.GetRecurrences(dbManager.ListRecurrenceIDsByResourceID(uStartTime, uEndTime, recur.resourceId));
if (recurs != null && recurs.Length > 0)
{
buf = new StringBuilder();
bool conflict = false;
foreach (Recurrence r in recurs){
if(recur.HasConflict(r))
{
conflict = true;
report(r);
}
}
if (conflict)
{
lblErrorMessage.Text = Utilities.FormatErrorMessage(buf.ToString());
lblErrorMessage.Visible = true;
return;
}
}
//Add recurrence accordingly
// if no recurrence is selected
int recurID = dbManager.AddRecurrence(recur);
Session["newOccurrenceID"] = recurID;
//No longer creating TimeBlocks
Page.ClientScript.RegisterStartupScript(this.GetType(), "Success", "ReloadParent();", true);
return;
}
catch (Exception ex)
{
string msg = "Exception: Cannot add the time block '" + ddlLabServers.SelectedItem.Text + " " + Session["labServerName"].ToString() + " " + txtStartDate.Text + " " + txtEndDate.Text + "'. " + ex.Message + ". " + ex.GetBaseException() + ".";
lblErrorMessage.Text = iLabs.UtilLib.Utilities.FormatErrorMessage(msg);
lblErrorMessage.Visible = true;
}
}
private void report(Recurrence recur)
{
// tell whether the recurrence recur is overlapping with the input recurrence
buf.Append("Conflict with recurrence ");
buf.Append(recur.recurrenceId + ": " + recur.recurrenceType + " ");
buf.Append(DateUtil.ToUserDate(recur.startDate, culture, localTzOffset) + "->");
buf.Append(DateUtil.ToUserDate(recur.startDate.AddDays(recur.numDays -1), culture, localTzOffset) + " ");
TimeSpan offset = TimeSpan.FromMinutes(localTzOffset);
buf.Append(recur.startOffset.Add(offset) + " -- " + recur.endOffset.Add(offset));
buf.AppendLine("<br />");
}
/* !------------------------------------------------------------------------------!
* CALLS FOR Recurrence
* !------------------------------------------------------------------------------!
*/
public static int AddRecurrence(Recurrence recur)
{
return AddRecurrence(recur.startDate, recur.numDays, (int) recur.recurrenceType,
recur.startOffset, recur.endOffset, recur.quantum, recur.resourceId, recur.dayMask);
}
/*
* Builds the Select Recurrence List using a specified array of recurrence.
* This is used to return the results of a search
*/
private void BuildRecurrenceListBox(Recurrence[] recurs)
{
StringBuilder buf = null;
lbxSelectTimeBlock.Items .Clear ();
btnEdit.Visible = false;
foreach (Recurrence recur in recurs)
{
buf = new StringBuilder();
//buf.Append("<pre>");
ListItem recurItem = new ListItem();
//string ussName = LSSSchedulingAPI.GetUSSInfos(new int[]{LSSSchedulingAPI.ListUSSInfoID(cs.ussGuid)})[0].ussName;
// string labServerName = LSSSchedulingAPI.RetrieveLabServerName(recur.labServerGuid);
buf.Append(String.Format("{0,-30}",Session["labServerName"].ToString() + ": "));
buf.Append(String.Format("{0,-10}",DateUtil.ToUserTime(recur.startDate, culture, localTzOffset)));
buf.Append(" -- " );
buf.Append(String.Format("{0,10}", DateUtil.ToUserTime(recur.startDate.AddDays(recur.numDays), culture, localTzOffset)));
buf.Append(String.Format(" {0,-15}",recur.recurrenceType + ":"));
TimeSpan tzOffset = TimeSpan.FromMinutes(localTzOffset);
DateTime localStart = recur.startDate.AddMinutes(localTzOffset);
buf.Append(localStart.Add(recur.startOffset).TimeOfDay + " -- " + localStart.Add(recur.endOffset).TimeOfDay);
//buf.Append(recur.startOffset + " -- " + recur.endOffset);
if(recur.recurrenceType == Recurrence.RecurrenceType.Weekly){
buf.Append(" Days: ");
buf.Append(DateUtil.ListDays(recur.dayMask,culture));
}
//buf.Append("</pre>");
recurItem.Text = buf.ToString();
recurItem.Value = recur.recurrenceId.ToString();
lbxSelectTimeBlock.Items .Add(recurItem);
}
}
public bool HasConflict(Recurrence recur)
{
bool status = false;
switch ( (RecurrenceType) recurrenceType)
{
case RecurrenceType.SingleBlock: // Single block always overlaps
if (Intersects(recur))
status = true;
break;
case RecurrenceType.Daily: //Daily block
switch ( (RecurrenceType) recur.recurrenceType)
{
case RecurrenceType.SingleBlock: // Single block always overlaps
if(Intersects(recur))
status = true;
break;
case RecurrenceType.Daily: //Daily block
case RecurrenceType.Weekly: // Weekly block
if (Intersects(recur))
{
if((startDate.TimeOfDay.Add(startOffset)
< recur.startDate.TimeOfDay.Add(recur.endOffset))
&& (startDate.TimeOfDay.Add(endOffset)
> recur.startDate.TimeOfDay.Add(recur.startOffset)))
{
status = true;
}
}
break;
default:
break;
}
break;
case RecurrenceType.Weekly: // Weekly block
switch ( (RecurrenceType) recur.recurrenceType)
{
case RecurrenceType.SingleBlock: // Single block always overlaps
if (Intersects(recur))
{
status = true;
}
break;
case RecurrenceType.Daily: //Daily block
if (Intersects(recur))
{
if ((startDate.TimeOfDay.Add(startOffset)
< recur.startDate.TimeOfDay.Add(recur.endOffset))
&& (startDate.TimeOfDay.Add(endOffset)
> recur.startDate.TimeOfDay.Add(recur.startOffset)))
{
status = true;
}
}
break;
case RecurrenceType.Weekly: // Weekly block, currently not supported
if (Intersects(recur))
{
if ((startDate.TimeOfDay.Add(startOffset)
< recur.startDate.TimeOfDay.Add(recur.endOffset))
&& (startDate.TimeOfDay.Add(endOffset)
> recur.startDate.TimeOfDay.Add(recur.startOffset)))
{
if((dayMask & recur.dayMask) !=0)
status = true;
}
}
break;
default:
break;
}
break;
default:
break;
}
return status;
}