/// <summary>
/// Finds and returns all FactoryCylinders in the database.
/// </summary>
public IList <FactoryCylinder> FindAll(DataAccessTransaction trx)
{
IList <FactoryCylinder> list = new List <FactoryCylinder>();
using (IDbCommand cmd = GetCommand("SELECT * FROM FACTORYCYLINDER", trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
list.Add(CreateFromReader(reader, ordinals));
}
}
}
// For each cylinder, load its gases
FactoryCylinderGasDataAccess factoryCylinderGasDataAccess = new FactoryCylinderGasDataAccess();
foreach (FactoryCylinder cylinder in list)
{
LoadFactoryCylinderGases(cylinder, factoryCylinderGasDataAccess, trx);
}
return(list);
}
/// <summary>
/// Returns the 'version' of this database's schema.
/// </summary>
/// <remarks>
/// During startup, the application needs to obtain this version and compare it
/// to what version it thinks it's compatible with.
/// </remarks>
/// <returns></returns>
public int GetSchemaVersion()
{
try
{
// THIS TRANSACTION MUST REMAIN WRITABLE. SINCE IT IS CALLED FIRST IT WILL ENSURE ALL
// PENDING DATABASE TRANSACTIONS HAVE BEEN ROLLED BACK.
using (DataAccessTransaction trx = new DataAccessTransaction(this._dataSourceId, false))
{
using (IDbCommand cmd = GetCommand("SELECT VERSION FROM SCHEMA", trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
// There should never be more than one row. Just take the first.
if (reader.Read())
{
int result = SqlSafeGetInt(reader, ordinals["VERSION"]);
trx.Commit();
return(result);
}
trx.Commit();
return(0); // return a default value of 0 for version
}
}
}
}
catch (Exception ex)
{
throw new DataAccessException("Could not get Version from Queue Schema. See inner exception for details.", ex);
}
}
protected internal override Schedule CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals)
{
short dayOfMonth = SqlSafeGetShort(reader, ordinals["DAYOFMONTH"]);
short week = SqlSafeGetShort(reader, ordinals["WEEK"]);
DayOfWeek?dayOfWeek = null;
short rawDayOfWeek = SqlSafeGetShort(reader, ordinals["DAYOFWEEK"]);
if (rawDayOfWeek != DomainModelConstant.NullShort)
{
dayOfWeek = (DayOfWeek)rawDayOfWeek;
}
return(new ScheduledMonthly(
GetId(reader, ordinals),
GetRefId(reader, ordinals),
GetName(reader, ordinals),
GetEventCode(reader, ordinals),
GetEquipmentCode(reader, ordinals),
GetEquipmentSubTypeCode(reader, ordinals),
GetEnabled(reader, ordinals),
GetOnDocked(reader, ordinals),
GetInterval(reader, ordinals),
GetStartDate(reader, ordinals),
GetRunAtTime(reader, ordinals),
week, dayOfWeek, dayOfMonth));
}
/// <summary>
/// Return the GasConcentrations of the cylinder's part number.
/// </summary>
/// <param name="factoryCylinder"></param>
/// <param name="trx"></param>
/// <returns>Returned list is empty if part number can't be found.</returns>
internal IList <GasConcentration> FindByFactoryCylinder(FactoryCylinder factoryCylinder, DataAccessTransaction trx)
{
List <GasConcentration> list = new List <GasConcentration>();
using (IDbCommand cmd = GetCommand("SELECT * FROM FACTORYCYLINDERGAS WHERE PARTNUMBER = @PARTNUMBER", trx))
{
cmd.Parameters.Add(GetDataParameter("@PARTNUMBER", factoryCylinder.PartNumber));
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
string gasCode = SqlSafeGetString(reader, ordinals["GASCODE"]);
double concentration = SqlSafeGetDouble(reader, ordinals["CONCENTRATION"], 0.0f);
GasConcentration gasConcentration = new GasConcentration(gasCode, concentration);
list.Add(gasConcentration);
}
}
}
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());;
}
/// <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);
}
private SensorCalibrationLimits CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals)
{
string sensorCode = SqlSafeGetString(reader, ordinals["SENSORCODE"]);
int age = SqlSafeGetInt(reader, ordinals["AGE"]);
return(new SensorCalibrationLimits(sensorCode, age));
}
/// <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 the oldest item on the queue
/// </summary>
/// <param name="trx"></param>
/// <returns>null is returned if the queue is empty.</returns>
public PersistedQueueData FindOldest(DataAccessTransaction trx)
{
using (IDbCommand cmd = GetCommand(string.Format("SELECT * FROM {0} WHERE ID = ( SELECT MIN(ID) FROM {1} )", TableName, TableName), trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
long id = SqlSafeGetLong(reader, ordinals["ID"]);
string accountNum = SqlSafeGetString(reader, ordinals["ACCOUNTNUM"]);
DateTime timeStamp = SqlSafeGetDateTime(reader, ordinals["TIMESTAMPUTC"], DateTimeKind.Utc);
string label = SqlSafeGetString(reader, ordinals["LABEL"]);
string type = SqlSafeGetString(reader, ordinals["TYPE"]);
string body = SqlSafeGetString(reader, ordinals["BODY"]);
PersistedQueueData persistedQueueData = new PersistedQueueData(accountNum, label, type, body);
persistedQueueData.Id = id;
persistedQueueData.Timestamp = timeStamp;
FindProperties(persistedQueueData, trx);
return(persistedQueueData);
}
}
}
protected TimeSpan GetRunAtTime(IDataReader reader, DataAccessOrdinals ordinals)
{
string runAtTime = SqlSafeGetString(reader, ordinals["RUNATTIME"]);
TimeSpan runAtTimeSpan = (runAtTime == null) ? TimeSpan.Zero : TimeSpan.Parse(runAtTime);
return(runAtTimeSpan);
}
/// <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);
}
private FactoryCylinder CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals)
{
string partNumber = SqlSafeGetString(reader, ordinals["PARTNUMBER"]);
string manufacturerCode = SqlSafeGetString(reader, ordinals["MANUFACTURERCODE"]);
return(new FactoryCylinder(partNumber, manufacturerCode));
}
private CriticalError CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals)
{
int code = SqlSafeGetInt(reader, ordinals["CODE"]);
string description = SqlSafeGetString(reader, ordinals["DESCRIPTION"]);
return(new CriticalError(code, description));
}
/// <summary>
///
/// </summary>
/// <param name="readOnly">We allow performing the 'find' using a writable transaction.
/// We do allow this because when we're starting up the device, this is the very first
/// query on the database. If there's a sqlite "hot journal" file in existence, when
/// the first query is issued, then sqlite will want to roll back the unclosed transaction.
/// But it's unable to do so if the transaction is read-only.</param>
/// <returns></returns>
public Schema Find(bool readOnly)
{
Schema schema = null;
using (DataAccessTransaction trx = new DataAccessTransaction(readOnly))
{
using (IDbCommand cmd = GetCommand("SELECT * FROM " + TableName, trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
// There should never be more than one row. Just take the first.
if (reader.Read())
{
schema = new Schema();
schema.Version = SqlSafeGetInt(reader, ordinals["VERSION"]);
schema.AccountNum = SqlSafeGetString(reader, ordinals["ACCOUNTNUM"]);
schema.Activated = (SqlSafeGetShort(reader, ordinals["ACTIVE"]) == 1) ? true : false;
if (schema.Version >= INET_VERSION_INS3017) // SGF 08-Jun-2012 Changed constant to reflect which version this data was added
{
schema.IsManufacturing = (SqlSafeGetShort(reader, ordinals["ISMANUFACTURING"]) == 1) ? true : false;
}
// It's assumed the dates are all stored in UTC. So pass in DateTimeKind.Utc
// to ensure they're not converted to local time when retrieved
schema.CylindersVersion = SqlSafeGetNullableDateTime(reader, ordinals["FACTORYCYLINDERSUTCVERSION"], DateTimeKind.Utc);
schema.SchedulesVersion = SqlSafeGetNullableDateTime(reader, ordinals["SCHEDULESUTCVERSION"], DateTimeKind.Utc);
schema.EventJournalsVersion = SqlSafeGetNullableDateTime(reader, ordinals["EVENTJOURNALSUTCVERSION"], DateTimeKind.Utc);
schema.SettingsVersion = SqlSafeGetNullableDateTime(reader, ordinals["SETTINGSUTCVERSION"], DateTimeKind.Utc);
schema.EquipmentVersion = SqlSafeGetNullableDateTime(reader, ordinals["EQUIPMENTUTCVERSION"], DateTimeKind.Utc);
//Suresh 06-FEB-2012 INS-2622
//Column 'CRITICALTERRORSUTCVERSION' is newly added to database so it won't be available in older version of database.
if (schema.Version >= INET_VERSION_INS2622) // SGF 08-Jun-2012 Changed constant to reflect which version this data was added
{
schema.CriticalErrorsVersion = SqlSafeGetNullableDateTime(reader, ordinals["CRITICALINSTRUMENTERRORSUTCVERSION"], DateTimeKind.Utc);
}
//INS-7715/7282 - To determine the account's Service type, to do checks based on Service accounts
if (schema.Version >= INET_VERSION_INS7715) //The version in which SERVICECODE is added
{
schema.ServiceCode = SqlSafeGetString(reader, ordinals["SERVICECODE"]);
}
if (!trx.ReadOnly)
{
trx.Commit();
}
}
}
}
}
return(schema);
}
protected internal override Schedule CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals)
{
return(new ScheduledUponDocking(
GetId(reader, ordinals),
GetRefId(reader, ordinals),
GetName(reader, ordinals),
GetEventCode(reader, ordinals),
GetEquipmentCode(reader, ordinals),
GetEquipmentSubTypeCode(reader, ordinals),
GetEnabled(reader, ordinals)));
}
private FirmwareUpgradeSetting CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals)
{
string equipmentCode = SqlSafeGetString(reader, ordinals["EQUIPMENTCODE"]);
string equipmentSubTypeCode = SqlSafeGetString(reader, ordinals["EQUIPMENTSUBTYPECODE"]);
string equipmentFullCode = SqlSafeGetString(reader, ordinals["EQUIPMENTFULLCODE"]);
string version = SqlSafeGetString(reader, ordinals["VERSION"]);
byte[] checksum = SqlSafeGetBLOB(reader, ordinals["CHECKSUM"]);
string fileName = SqlSafeGetString(reader, ordinals["FILENAME"]);
return(new FirmwareUpgradeSetting(equipmentCode, equipmentSubTypeCode, equipmentFullCode, version, checksum, fileName));
}
private EventJournal CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals)
{
string eventCode = SqlSafeGetString(reader, ordinals["EVENTCODE"]);
string sn = SqlSafeGetString(reader, ordinals["SN"]);
string instSn = SqlSafeGetString(reader, ordinals["INSTRUMENTSN"]);
DateTime runTime = SqlSafeGetDateTime(reader, ordinals["RUNTIMEUTC"], DateTimeKind.Utc);
DateTime eventTime = SqlSafeGetDateTime(reader, ordinals["EVENTTIMEUTC"], DateTimeKind.Utc);
bool passed = SqlSafeGetShort(reader, ordinals["PASSED"]) == 0 ? false : true;
int position = SqlSafeGetInt(reader, ordinals["POSITION"]);
string softwareVersion = SqlSafeGetString(reader, ordinals["SOFTWAREVERSION"]);
return(new EventJournal(eventCode, sn, instSn, runTime, eventTime, passed, position, softwareVersion));
}
protected bool[] GetDaysFromReader(IDataReader reader, DataAccessOrdinals ordinals)
{
bool[] days = new bool[7];
days[(int)DayOfWeek.Sunday] = (SqlSafeGetShort(reader, ordinals["SUNDAY"]) > 0) ? true : false;
days[(int)DayOfWeek.Monday] = (SqlSafeGetShort(reader, ordinals["MONDAY"]) > 0) ? true : false;
days[(int)DayOfWeek.Tuesday] = (SqlSafeGetShort(reader, ordinals["TUESDAY"]) > 0) ? true : false;
days[(int)DayOfWeek.Wednesday] = (SqlSafeGetShort(reader, ordinals["WEDNESDAY"]) > 0) ? true : false;
days[(int)DayOfWeek.Thursday] = (SqlSafeGetShort(reader, ordinals["THURSDAY"]) > 0) ? true : false;
days[(int)DayOfWeek.Friday] = (SqlSafeGetShort(reader, ordinals["FRIDAY"]) > 0) ? true : false;
days[(int)DayOfWeek.Saturday] = (SqlSafeGetShort(reader, ordinals["SATURDAY"]) > 0) ? true : false;
return(days);
}
protected internal override Schedule CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals)
{
return(new ScheduledDaily(
GetId(reader, ordinals),
GetRefId(reader, ordinals),
GetName(reader, ordinals),
GetEventCode(reader, ordinals),
GetEquipmentCode(reader, ordinals),
GetEquipmentSubTypeCode(reader, ordinals),
GetEnabled(reader, ordinals),
GetOnDocked(reader, ordinals),
GetInterval(reader, ordinals),
GetStartDate(reader, ordinals),
GetRunAtTime(reader, ordinals)));
}
/// <summary>
/// Finds and returns all Sensor Calibration Limits in the database.
/// </summary>
public List <SensorCalibrationLimits> FindAll(DataAccessTransaction trx)
{
List <SensorCalibrationLimits> list = new List <SensorCalibrationLimits>();
using (IDbCommand cmd = GetCommand("SELECT * FROM " + TableName, trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
list.Add(CreateFromReader(reader, ordinals));
}
}
}
return(list);
}
/// <summary>
/// Return all the serial numbers in the table.
/// </summary>
/// <param name="trx"></param>
/// <returns>
/// The order of the returned strings in undefined (they may be in any order).
/// </returns>
public List <string> FindAll(DataAccessTransaction trx)
{
List <string> snList = new List <string>();
using (IDbCommand cmd = GetCommand(string.Format("SELECT * FROM {0}", TableName), trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
snList.Add(SqlSafeGetString(reader, ordinals["SN"]));
}
}
}
return(snList);
}
/// <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);
}
private long FindIdByRefId(long refId, DataAccessTransaction trx)
{
using (IDbCommand cmd = GetCommand("SELECT ID FROM SCHEDULE WHERE REFID = @REFID", trx))
{
cmd.Parameters.Add(GetDataParameter("@REFID", refId));
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
// There should never be more than one row. Just take the first.
if (reader.Read())
{
return(SqlSafeGetLong(reader, ordinals["ID"]));
}
}
}
return(DomainModelConstant.NullId);
}
private GasEndPoint MakeInstalledCylinder(IDataReader reader, DataAccessOrdinals ordinals, DataAccessTransaction trx)
{
short position = SqlSafeGetShort(reader, ordinals["POSITION"]);
string partNumber = SqlSafeGetString(reader, ordinals["PARTNUMBER"]);
// Try and get the Factory Cylinder information for the part number.
// Note that there may not be any factory cylinder info available if the
// part number is for a new cylinder type that's unknown to to iNet.
FactoryCylinder factoryCylinder = null;
if (partNumber != string.Empty)
{
factoryCylinder = new FactoryCylinderDataAccess().FindByPartNumber(partNumber, trx);
}
Cylinder cylinder;
if (factoryCylinder != null)
{
cylinder = new Cylinder(factoryCylinder);
}
else
{
cylinder = new Cylinder();
cylinder.PartNumber = partNumber;
}
string installationTypeString = SqlSafeGetString(reader, ordinals["INSTALLATIONTYPE"]);
GasEndPoint.Type installationType = (GasEndPoint.Type)Enum.Parse(typeof(GasEndPoint.Type), installationTypeString, true);
GasEndPoint gep = new GasEndPoint(cylinder, position, installationType);
gep.Cylinder.FactoryId = SqlSafeGetString(reader, ordinals["FACTORYID"]);
gep.Cylinder.ExpirationDate = SqlSafeGetDate(reader, ordinals["EXPIRATIONDATE"]);
gep.Cylinder.RefillDate = SqlSafeGetDate(reader, ordinals["REFILLDATE"]);
string pressure = SqlSafeGetString(reader, ordinals["PRESSURE"]);
gep.Cylinder.Pressure = (PressureLevel)Enum.Parse(typeof(PressureLevel), pressure, true);
return(gep);
}
/// <summary>
/// Returns all of the currently known installed cylinders.
/// </summary>
/// <param name="trx"></param>
/// <returns>The list of InstalledCylinders will be sorted by Position</returns>
public List <GasEndPoint> FindAll(DataAccessTransaction trx)
{
List <GasEndPoint> list = new List <GasEndPoint>();
string sql = "SELECT * FROM GASENDPOINT ORDER BY POSITION";
using (IDbCommand cmd = GetCommand(sql, trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
GasEndPoint gep = MakeInstalledCylinder(reader, ordinals, trx);
list.Add(gep);
}
}
}
return(list);
}
/// <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>
/// Returns all data on the queue (except for the Body fields).
/// </summary>
/// <param name="count">Ignored if zero or less.</param>
/// <param name="trx"></param>
/// <returns></returns>
public List <PersistedQueueData> FindAll(int count)
{
using (DataAccessTransaction trx = new DataAccessTransaction(this._dataSourceId, true))
{
List <PersistedQueueData> list = new List <PersistedQueueData>();
string sql = string.Format("SELECT ID, ACCOUNTNUM, TIMESTAMPUTC, LABEL, TYPE FROM {0} ORDER BY ID DESC", TableName);
if (count > 0)
{
sql += string.Format(" LIMIT {0}", count);
}
using (IDbCommand cmd = GetCommand(sql, trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
long id = SqlSafeGetLong(reader, ordinals["ID"]);
string accountNum = SqlSafeGetString(reader, ordinals["ACCOUNTNUM"]);
DateTime timeStamp = SqlSafeGetDateTime(reader, ordinals["TIMESTAMPUTC"], DateTimeKind.Utc);
string label = SqlSafeGetString(reader, ordinals["LABEL"]);
string type = SqlSafeGetString(reader, ordinals["TYPE"]);
PersistedQueueData persistedQueueData = new PersistedQueueData(accountNum, label, type, string.Empty);
persistedQueueData.Id = id;
persistedQueueData.Timestamp = timeStamp;
FindProperties(persistedQueueData, trx);
list.Add(persistedQueueData);
}
}
}
return(list);
}
}
/// <summary>
/// Queries the schema table for just the database version.
/// </summary>
/// <returns>0 is returned if table is empty (which should probably never happen).</returns>
public int FindVersion()
{
int version = 0;
using (DataAccessTransaction trx = new DataAccessTransaction(true))
{
using (IDbCommand cmd = GetCommand("SELECT VERSION FROM " + TableName, trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
// There should never be more than one row. Just take the first.
if (reader.Read())
{
version = SqlSafeGetInt(reader, ordinals["VERSION"]);
}
}
}
}
return(version);
}
/// <summary>
/// Finds all EventJournals 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 List <EventJournal> FindByInstrumentSerialNumber(string instrumentSerialNumber, string eventCode, DataAccessTransaction trx)
{
List <EventJournal> journalsList = new List <EventJournal>();
string sql = "SELECT * 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);
}
private void FindProperties(PersistedQueueData persistedQueueData, DataAccessTransaction trx)
{
persistedQueueData.Properties.Clear();
string sql = string.Format("SELECT ATTRIBUTE, VALUE FROM {0} WHERE QUEUE_ID = {1}", TableName + "PROPERTY", persistedQueueData.Id);
using (IDbCommand cmd = GetCommand(sql, trx))
{
using (IDataReader reader = cmd.ExecuteReader())
{
DataAccessOrdinals ordinals = new DataAccessOrdinals(reader);
while (reader.Read())
{
string attribute = SqlSafeGetString(reader, ordinals["ATTRIBUTE"]);
string value = SqlSafeGetString(reader, ordinals["VALUE"]);
persistedQueueData.Properties[attribute] = value;
}
}
}
}