Ejemplo n.º 1
0
        /// <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);
        }
Ejemplo n.º 2
0
        /// <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);
            }
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 5
0
        /// <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());;
        }
Ejemplo n.º 6
0
        /// <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));
        }
Ejemplo n.º 8
0
        /// <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);
            }
        }
Ejemplo n.º 9
0
        /// <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);
                }
            }
        }
Ejemplo n.º 10
0
        protected TimeSpan GetRunAtTime(IDataReader reader, DataAccessOrdinals ordinals)
        {
            string   runAtTime     = SqlSafeGetString(reader, ordinals["RUNATTIME"]);
            TimeSpan runAtTimeSpan = (runAtTime == null) ? TimeSpan.Zero : TimeSpan.Parse(runAtTime);

            return(runAtTimeSpan);
        }
Ejemplo n.º 11
0
        /// <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);
        }
Ejemplo n.º 12
0
        private FactoryCylinder CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals)
        {
            string partNumber = SqlSafeGetString(reader, ordinals["PARTNUMBER"]);

            string manufacturerCode = SqlSafeGetString(reader, ordinals["MANUFACTURERCODE"]);

            return(new FactoryCylinder(partNumber, manufacturerCode));
        }
Ejemplo n.º 13
0
        private CriticalError CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals)
        {
            int code = SqlSafeGetInt(reader, ordinals["CODE"]);

            string description = SqlSafeGetString(reader, ordinals["DESCRIPTION"]);

            return(new CriticalError(code, description));
        }
Ejemplo n.º 14
0
        /// <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)));
 }
Ejemplo n.º 16
0
        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));
        }
Ejemplo n.º 17
0
        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);
        }
Ejemplo n.º 21
0
        /// <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);
        }
Ejemplo n.º 22
0
        /// <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);
        }
Ejemplo n.º 23
0
        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);
        }
Ejemplo n.º 26
0
        /// <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);
        }
Ejemplo n.º 27
0
        /// <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);
            }
        }
Ejemplo n.º 28
0
        /// <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);
        }
Ejemplo n.º 29
0
        /// <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);
        }
Ejemplo n.º 30
0
        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;
                    }
                }
            }
        }