/// <summary>
/// </summary>
/// <param name="schedule"></param>
/// <param name="trx"></param>
/// <returns></returns>
private void LoadScheduleProperties(Schedule schedule, IDataAccessTransaction trx)
{
string sql = "SELECT * FROM SCHEDULEPROPERTY WHERE SCHEDULE_ID = @SCHEDULE_ID ORDER BY ATTRIBUTE, SEQUENCE";
try
{
List <ScheduleProperty> list = new List <ScheduleProperty>();
using (IDbCommand cmd = GetCommand(sql, trx))
{
cmd.Parameters.Add(GetDataParameter("@SCHEDULE_ID", schedule.Id));
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
list.Add(CreateSchedulePropertiesFromReader(schedule, reader, ordinals));
}
}
}
schedule.ScheduleProperties = list;
}
catch (Exception ex)
{
throw new DataAccessException(sql, ex);
}
}
/// <summary>
/// Returns all global schedules (those not associated with either any equipment serial numbers
/// nor any component codes). True global schedules also have a null equipment code.
/// </summary>
/// <param name="trx"></param>
/// <returns></returns>
public virtual IList <Schedule> FindGlobalSchedules(IDataAccessTransaction trx)
{
List <Schedule> list = new List <Schedule>();
StringBuilder sqlBuilder = new StringBuilder(string.Format("SELECT SCHEDULE.*, {0}.* FROM SCHEDULE, {1}", TableName, TableName), 500);
sqlBuilder.Append(" WHERE SCHEDULE.EQUIPMENTCODE IS NULL");
sqlBuilder.Append(" AND NOT EXISTS ( SELECT SCHEDULE_ID FROM SCHEDULEDEQUIPMENT WHERE SCHEDULE_ID = SCHEDULE.ID )");
sqlBuilder.Append(" AND NOT EXISTS ( SELECT SCHEDULE_ID FROM SCHEDULEDCOMPONENTCODE WHERE SCHEDULE_ID = SCHEDULE.ID )");
sqlBuilder.AppendFormat(" AND {0}.SCHEDULE_ID = SCHEDULE.ID", TableName);
string sql = sqlBuilder.ToString();
try
{
using (IDbCommand cmd = GetCommand(string.Format(sql, TableName, TableName), trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
Schedule sched = CreateFromReader(reader, ordinals);
list.Add(sched);
LoadScheduleProperties(sched, trx);
}
}
}
}
catch (Exception ex)
{
throw new DataAccessException(sql, ex);
}
return(list);
}
//TODO:Ajay Duplicate this mehtod with IDataAccessTransaction method
//Other method that is accepting DataAccessTransaction can be removed.
static internal IDbCommand GetCommand(string sql, int commandTimeout, IDataAccessTransaction trx)
{
DataAccessTransaction transaction = (DataAccessTransaction)trx;
IDbCommand cmd = GetCommand(sql, transaction.Connection);
cmd.Transaction = transaction.Transaction;
if (commandTimeout != NULL_INT_FLAG)
{
cmd.CommandTimeout = commandTimeout;
}
return(cmd);
}
/// <summary>
/// Finds all EventJournals for the specified equipment/component serial numbers.
/// </summary>
/// <param name="serialNumbers"></param>
/// <param name="trx"></param>
/// <returns>An empty array is returned if no match is found.</returns>
public virtual List <EventJournal> FindBySerialNumbers(string[] serialNumbers, IDataAccessTransaction trx)
{
List <EventJournal> journalsList = new List <EventJournal>();
string sql = string.Format("SELECT * FROM EVENTJOURNAL WHERE SN IN ( {0} )", MakeCommaDelimitedParamNames(serialNumbers.Length, "@SN"));
using (IDbCommand cmd = GetCommand(sql, trx))
{
for (int i = 0; i < serialNumbers.Length; i++)
{
cmd.Parameters.Add(GetDataParameter("@SN" + (i + 1).ToString(), serialNumbers[i]));
}
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
journalsList.Add(CreateFromReader(reader, ordinals));
}
}
}
return(journalsList);
}
/// <summary>
/// Finds all EventJournals for the last event of the specified type for the specified instrument serial number and its components.
/// </summary>
/// <param name="instrumentSerialNumber"></param>
/// <param name="eventCode"></param>
/// <param name="trx"></param>
/// <returns>An empty array is returned if no match is found.</returns>
public virtual List <EventJournal> FindLastEventByInstrumentSerialNumber(string instrumentSerialNumber, string eventCode, IDataAccessTransaction trx)
{
List <EventJournal> journalsList = new List <EventJournal>();
//string sql = "SELECT * FROM EVENTJOURNAL WHERE (SN = @SN OR INSTRUMENTSN = @SN) AND EVENTCODE = @EVENTCODE";
string sql = "SELECT * FROM EVENTJOURNAL WHERE (SN = @SN OR INSTRUMENTSN = @SN) AND EVENTCODE = @EVENTCODE AND EVENTTIMEUTC = (SELECT MAX(EVENTTIMEUTC) FROM EVENTJOURNAL WHERE (SN = @SN OR INSTRUMENTSN = @SN) AND EVENTCODE = @EVENTCODE)";
using (IDbCommand cmd = GetCommand(sql, trx))
{
cmd.Parameters.Add(GetDataParameter("@SN", instrumentSerialNumber));
cmd.Parameters.Add(GetDataParameter("@EVENTCODE", eventCode));
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
journalsList.Add(CreateFromReader(reader, ordinals));
}
}
}
return(journalsList);
}
//TODO:Ajay Duplicate this mehtod with IDataAccessTransaction method
//Other method that is accepting DataAccessTransaction can be removed.
static internal IDbCommand GetCommand(string sql, IDataAccessTransaction trx)
{
return(GetCommand(sql, NULL_INT_FLAG, trx));
}
/// <summary>
/// Returns all global type-specific schedules (those not associated with either any equipment serial numbers
/// nor any component codes). Global type-specific schedules do not have a null equipment code and can be assumed
/// to only be for instrument events.
/// </summary>
/// <param name="equipmentCode">The device types</param>
/// <param name="trx"></param>
/// <returns>A list of global type-specific schedules for the equipment</returns>
public virtual IList <Schedule> FindGlobalTypeSpecificSchedules(string[] equipmentCodes, IDataAccessTransaction trx)
{
List <Schedule> list = new List <Schedule>();
StringBuilder sqlBuilder = new StringBuilder(string.Format("SELECT SCHEDULE.*, {0}.* FROM SCHEDULE, {1}", TableName, TableName), 500);
sqlBuilder.Append(string.Format(" WHERE SCHEDULE.EQUIPMENTCODE IN ( {0} ) ", MakeCommaDelimitedParamNames(equipmentCodes.Length, "@EQUIPMENTCODE")));
sqlBuilder.Append(" AND NOT EXISTS ( SELECT SCHEDULE_ID FROM SCHEDULEDEQUIPMENT WHERE SCHEDULE_ID = SCHEDULE.ID )");
sqlBuilder.Append(" AND NOT EXISTS ( SELECT SCHEDULE_ID FROM SCHEDULEDCOMPONENTCODE WHERE SCHEDULE_ID = SCHEDULE.ID )");
sqlBuilder.AppendFormat(" AND {0}.SCHEDULE_ID = SCHEDULE.ID", TableName);
string sql = sqlBuilder.ToString();
try
{
using (IDbCommand cmd = GetCommand(sql, trx))
{
for (int i = 0; i < equipmentCodes.Length; i++)
{
cmd.Parameters.Add(GetDataParameter("@EQUIPMENTCODE" + (i + 1).ToString(), equipmentCodes[i]));
}
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
Schedule sched = CreateFromReader(reader, ordinals);
list.Add(sched);
LoadScheduleProperties(sched, trx);
}
}
}
}
catch (Exception ex)
{
throw new DataAccessException(sql, ex);
}
return(list);
}
/// <summary>
/// </summary>
/// <param name="id"></param>
/// <param name="tableName">Must be either SCHEDULEDCOMPONENTCODE or SCHEDULEDEQUIPMENT </param>
/// <param name="stringFieldName">COMPONENTCODE or SN</param>
/// <param name="trx"></param>
/// <returns></returns>
private string[] FindScheduledForItems(long id, string tableName, string stringFieldName, IDataAccessTransaction trx)
{
List <string> strList = new List <string>();
string sql = string.Format(string.Format("SELECT {0} FROM {1} WHERE SCHEDULE_ID = @SCHEDULE_ID", stringFieldName, tableName));
try
{
using (IDbCommand cmd = GetCommand(sql, trx))
{
cmd.Parameters.Add(GetDataParameter("@SCHEDULE_ID", id));
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
string str = SqlSafeGetString(reader, ordinals[stringFieldName]);
if (str != null && str.Length > 0)
{
strList.Add(str);
}
}
}
}
}
catch (Exception ex)
{
throw new DataAccessException(sql, ex);
}
return(strList.ToArray());;
}
public virtual IList <Schedule> FindByComponentCodes(string[] componentCodes, IDataAccessTransaction trx)
{
return(FindByScheduledItems(componentCodes, "SCHEDULEDCOMPONENTCODE", "COMPONENTCODE", trx));
}
/// <summary>
/// </summary>
/// <remarks>
/// "Items" is either one or more pieces of equipment (specified by serial number)
/// or one or more component (sensor) types.
/// </remarks>
/// <param name="items">serial numbers or component type codes</param>
/// <param name="itemTableName">SCHEDULEDCOMPONENTCODE or SCHEDULEDEQUIPMENT</param>
/// <param name="stringFieldName">COMPONENTCODE or SN</param>
/// <param name="trx"></param>
/// <returns></returns>
private IList <Schedule> FindByScheduledItems(string[] items, string itemTableName, string itemFieldName, IDataAccessTransaction trx)
{
List <Schedule> list = new List <Schedule>();
StringBuilder sqlBuilder = new StringBuilder(string.Format("SELECT SCHEDULE.*, {0}.* FROM SCHEDULE, {1}, {2}", TableName, TableName, itemTableName), 500);
sqlBuilder.AppendFormat(" WHERE {0}.{1} IN ( {2} )", itemTableName, itemFieldName, MakeCommaDelimitedParamNames(items.Length, "@PARAM")); // SGF 05-Nov-2010 Single Sensor Cal and Bump -- corrected bug in statement
sqlBuilder.AppendFormat(" AND {0}.SCHEDULE_ID = SCHEDULE.ID", itemTableName);
sqlBuilder.AppendFormat(" AND {0}.SCHEDULE_ID = SCHEDULE.ID", TableName);
string sql = sqlBuilder.ToString();
try
{
using (IDbCommand cmd = GetCommand(string.Format(sql, TableName, TableName), trx))
{
for (int i = 0; i < items.Length; i++)
{
cmd.Parameters.Add(GetDataParameter("@PARAM" + (i + 1).ToString(), items[i]));
}
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
Schedule sched = CreateFromReader(reader, ordinals);
sched.ComponentCodes = new List <string>(FindScheduledForItems(sched.Id, "SCHEDULEDCOMPONENTCODE", "COMPONENTCODE", trx));
sched.SerialNumbers = new List <string>(FindScheduledForItems(sched.Id, "SCHEDULEDEQUIPMENT", "SN", trx));
LoadScheduleProperties(sched, trx);
list.Add(sched);
}
}
}
}
catch (Exception ex)
{
throw new DataAccessException(sql, ex);
}
return(list);
}
public virtual IList <Schedule> FindBySerialNumbers(string[] serialNumbers, IDataAccessTransaction trx)
{
return(FindByScheduledItems(serialNumbers, "SCHEDULEDEQUIPMENT", "SN", trx));
}
/// <summary>
/// Initializes a new instance of the <see cref="DataAccessCommandHandler{TCommand}" /> class.
/// </summary>
/// <param name="mediator">
/// A processing intermediary that is used to process sub-commands.
/// </param>
/// <param name="repositoryFactory">
/// The factory that produces data access repositories for the handler.
/// </param>
/// <param name="transaction">
/// A transaction that is used to process the command.
/// </param>
/// <exception cref="ArgumentException">
/// <paramref name="transaction" /> is in an invalid state.
/// </exception>
/// <exception cref="ArgumentNullException">
/// <paramref name="mediator" /> is <see langword="null" /> -or- <paramref name="repositoryFactory" /> is
/// <see langword="null" /> -or- <paramref name="transaction" /> is <see langword="null" />.
/// </exception>
protected DataAccessCommandHandler(ICommandMediator mediator, IDataAccessRepositoryFactory repositoryFactory, IDataAccessTransaction transaction)
: base(mediator)
{
Repositories = new FactoryProducedInstanceGroup(repositoryFactory);
Transaction = transaction.RejectIf().IsNull(nameof(transaction)).OrIf((argument) => argument.State != DataAccessTransactionState.Ready, nameof(transaction)).TargetArgument;
}
/// <summary> /// Processes the specified command. /// </summary> /// <param name="command"> /// The command to process. /// </param> /// <param name="repositories"> /// An object that provides access to data access repositories. /// </param> /// <param name="transaction"> /// A transaction that is used to process the command. /// </param> /// <param name="controlToken"> /// A token that ensures thread safety for the operation. /// </param> protected abstract void Process(TCommand command, IFactoryProducedInstanceGroup repositories, IDataAccessTransaction transaction, ConcurrencyControlToken controlToken);
/// <summary>
/// Processes the specified command.
/// </summary>
/// <param name="command">
/// The command to process.
/// </param>
/// <param name="repositories">
/// An object that provides access to data access repositories.
/// </param>
/// <param name="transaction">
/// A transaction that is used to process the command.
/// </param>
/// <param name="controlToken">
/// A token that ensures thread safety for the operation.
/// </param>
/// <returns>
/// The result that is emitted when processing the command.
/// </returns>
protected sealed override IEnumerable <Int64> Process(GetFibonacciNumberValuesCommand command, IFactoryProducedInstanceGroup repositories, IDataAccessTransaction transaction, ConcurrencyControlToken controlToken)
{
var numberSeriesNumberRepository = repositories.Get <NumberSeriesNumberRepository>();
return(numberSeriesNumberRepository.FindWhere(entity => entity.NumberSeries.Identifier == NumberSeries.Named.Fibonacci.Identifier).Select(entity => entity.Number.Value).ToList());
}
/// <summary>
/// Processes the specified command.
/// </summary>
/// <param name="command">
/// The command to process.
/// </param>
/// <param name="repositories">
/// An object that provides access to data access repositories.
/// </param>
/// <param name="transaction">
/// A transaction that is used to process the command.
/// </param>
/// <param name="controlToken">
/// A token that ensures thread safety for the operation.
/// </param>
protected override void Process(AddFibonacciNumberCommand command, IFactoryProducedInstanceGroup repositories, IDataAccessTransaction transaction, ConcurrencyControlToken controlToken)
{
var fibonacciNumberSeries = NumberSeries.Named.Fibonacci;
var numberRepository = repositories.Get <NumberRepository>();
var number = numberRepository.FindByValue(command.NumberValue);
if (number is null)
{
number = new Number()
{
Identifier = Guid.NewGuid(),
Value = command.NumberValue
};
numberRepository.Add(number);
}
var numberSeriesNumberRespository = repositories.Get <NumberSeriesNumberRepository>();
var numberSeriesNumber = numberSeriesNumberRespository.FindWhere(entity => entity.Number.Value == number.Value && entity.NumberSeriesIdentifier == fibonacciNumberSeries.Identifier).SingleOrDefault();
if (numberSeriesNumber is null)
{
numberSeriesNumber = new NumberSeriesNumber()
{
Identifier = Guid.NewGuid(),
Number = number,
NumberIdentifier = number.Identifier,
NumberSeriesIdentifier = fibonacciNumberSeries.Identifier
};
numberSeriesNumberRespository.Add(numberSeriesNumber);
}
}
