using DbAPI.Connections;
using DbAPI.Errors;
using DbAPI.Logging;
using DTS.Common.Classes.Sensors;
using DTS.Common.Classes.Sensors.AnalogDiagnostics;
using DTS.Common.Interface.Database;
using DTS.Common.Interface.Sensors;
using DTS.Common.Interface.Sensors.AnalogDiagnostics;
using DTS.Common.Utilities.Logging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Globalization;
using System.Linq;
using DTS.Common;

namespace DbAPI.Sensors
{
    /// <summary>
    /// Handles sensor functions
    /// <inheritdoc cref="ISensors"/>
    /// </summary>
    internal class Sensors : ISensors
    {
        private const int DB_VERSION_TRACK_DIAGNOSTICS = 98;
        /// <summary>
        /// returns all matching analog diagnostic records
        /// </summary>
        /// <param name="user">user making query</param>
        /// <param name="connection">connection being queried over</param>
        /// <param name="Id">id of record, or null for all records</param>
        /// <param name="diagnosticRunId">id of diagnostic run, or null for all records</param>
        /// <param name="sensorId">sensor id to query for, or null for all</param>
        /// <param name="sensorSerialNumber">serial number to query for, or null for all</param>
        /// <returns></returns>
        public ulong SensorsAnalogDiagnosticsGet(IUserDbRecord user, IConnectionDetails connection,
            long? Id, long? diagnosticRunId, int? sensorId, string sensorSerialNumber, out IDiagnosticEntry[] records)
        {
            records = new IDiagnosticEntry[0];
            var list = new List<IDiagnosticEntry>();
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (connection.ConnectionDbVersion < DB_VERSION_TRACK_DIAGNOSTICS) { return ErrorCodes.ERROR_NOT_SUPPORTED; }

            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_AnalogDiagnosticsGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                AddNullableBigIntParameter(cmd, "@Id", Id);
                AddNullableBigIntParameter(cmd, "@DiagnosticRunId", diagnosticRunId);
                AddNullableIntParameter(cmd, "@SensorId", sensorId);
                AddNullableStringParameter(cmd, "@SensorSerialNumber", sensorSerialNumber);

                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(new DiagnosticEntry(reader));
                }
                records = list.ToArray();
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_AnalogDiagnosticsGet failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// retrieves any matching 
        /// </summary>
        /// <param name="user">user making query</param>
        /// <param name="Id">diagnostic run id to query for or null for any id</param>
        /// <param name="testId">test setup id to query for or null for any id</param>
        /// <param name="testName">test setup name to query for or null for any name</param>
        /// <param name="connection">connection to query on</param>
        /// <param name="records">out records</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsAnalogDiagnosticRunGet(IUserDbRecord user, IConnectionDetails connection,
            long? Id, int? testId, string testName, out IDiagnosticRun[] records)
        {
            records = new IDiagnosticRun[0];
            var list = new List<IDiagnosticRun>();
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (connection.ConnectionDbVersion < DB_VERSION_TRACK_DIAGNOSTICS) { return ErrorCodes.ERROR_NOT_SUPPORTED; }

            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_DiagnosticRunsGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                AddNullableBigIntParameter(cmd, "@Id", Id);
                AddNullableIntParameter(cmd, "@TestId", testId);
                AddNullableStringParameter(cmd, "@TestName", testName);

                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(new DiagnosticRun(reader));
                }
                records = list.ToArray();
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_DiagnosticRunsGet failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// adds an error number style parameter to command object (int output parameter)
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="paramName"></param>
        /// <param name="errorNumberParam"></param>
        private void AddErrorNumberParameter(SqlCommand cmd, string paramName, out SqlParameter errorNumberParam)
        {
            errorNumberParam =
                    new SqlParameter(paramName, SqlDbType.Int) { Direction = ParameterDirection.Output };
            cmd.Parameters.Add(errorNumberParam);
        }
        /// <summary>
        /// adds an error message style parameter to command object (nvarchar 250 output parameter)
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="paramName"></param>
        /// <param name="errorMessageParam"></param>
        private void AddErrorMessageParameter(SqlCommand cmd, string paramName, out SqlParameter errorMessageParam)
        {
            errorMessageParam =
                    new SqlParameter(paramName, SqlDbType.NVarChar, 250)
                    {
                        Direction = ParameterDirection.Output
                    };
            cmd.Parameters.Add(errorMessageParam);
        }
        /// <summary>
        /// adds a nullable double parameter to command object
        /// if input value is null or NaN, then DBNull is used
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="paramName"></param>
        /// <param name="dValue"></param>
        private void AddNullableDoubleParameter(SqlCommand cmd, string paramName, double? dValue)
        {
            var cParam = new SqlParameter(paramName, SqlDbType.Float);
            if (null == dValue || double.IsNaN((double)dValue)) { cParam.Value = DBNull.Value; }
            else { cParam.Value = (double)dValue; }
            cmd.Parameters.Add(cParam);
        }
        /// <summary>
        /// adds a nullable int parameter to the command object
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="paramName"></param>
        /// <param name="dValue"></param>
        private void AddNullableIntParameter(SqlCommand cmd, string paramName, int? dValue)
        {
            var cParam = new SqlParameter(paramName, SqlDbType.Int);
            if (null == dValue) { cParam.Value = DBNull.Value; }
            else { cParam.Value = (int)dValue; }
            cmd.Parameters.Add(cParam);
        }
        /// <summary>
        /// adds a nullable string parameter to the command object
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="paramName"></param>
        /// <param name="dValue"></param>
        private void AddNullableStringParameter(SqlCommand cmd, string paramName, string dValue)
        {
            var cParam = new SqlParameter(paramName, SqlDbType.NVarChar);
            if (string.IsNullOrEmpty(dValue)) { cParam.Value = DBNull.Value; }
            else { cParam.Value = dValue; }
            cmd.Parameters.Add(cParam);
        }
        /// <summary>
        /// adds a nullable big int parameter to command object
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="paramName"></param>
        /// <param name="dValue"></param>
        private void AddNullableBigIntParameter(SqlCommand cmd, string paramName, long? dValue)
        {
            var cParam = new SqlParameter(paramName, SqlDbType.BigInt);
            if (null == dValue) { cParam.Value = DBNull.Value; }
            else { cParam.Value = (long)dValue; }
            cmd.Parameters.Add(cParam);
        }
        /// <summary>
        /// update or inserts all entries passed in
        /// </summary>
        /// <param name="user">user committing entries</param>
        /// <param name="connection">connection to commit over</param>
        /// <param name="entries">entries to insert or update</param>
        /// <returns></returns>
        public ulong SensorsAnalogDiagnosticUpdateInsert(IUserDbRecord user,
            IConnectionDetails connection,
            ref IDiagnosticEntry entry)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (null == entry)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            if (entry.Timestamp < SqlDateTime.MinValue)
            {
                APILogger.Log("SensorsAnalogDiagnosticUpdateInsert - entry Timestamp is invalid");
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }

            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_AnalogDiagnosticsUpdateInsert");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                AddNullableBigIntParameter(cmd, "@Id", entry.Id);

                cmd.Parameters.Add(new SqlParameter("@DiagnosticRunId", SqlDbType.BigInt) { Value = entry.DiagnosticRunId });

                AddNullableDoubleParameter(cmd, "@Excitation", entry.Excitation);

                cmd.Parameters.Add(new SqlParameter("@ExcitationStatus", SqlDbType.SmallInt) { Value = (int)entry.ExcitationStatus });

                AddNullableDoubleParameter(cmd, "@Offset", entry.Offset);

                cmd.Parameters.Add(new SqlParameter("@OffsetStatus", SqlDbType.SmallInt) { Value = (int)entry.ShuntStatus });

                AddNullableDoubleParameter(cmd, "@ActualRange", entry.ActualRange);

                cmd.Parameters.Add(new SqlParameter("@ActualRangeStatus", SqlDbType.SmallInt) { Value = (int)entry.ActualRangeStatus });

                AddNullableDoubleParameter(cmd, "@Shunt", entry.Shunt);

                cmd.Parameters.Add(new SqlParameter("@ShuntStatus", SqlDbType.SmallInt) { Value = (int)entry.ShuntStatus });

                AddNullableDoubleParameter(cmd, "@Noise", entry.Noise);

                cmd.Parameters.Add(new SqlParameter("@NoiseStatus", SqlDbType.SmallInt) { Value = (int)entry.NoiseStatus });

                AddNullableIntParameter(cmd, "@SensorId", entry.SensorId);

                cmd.Parameters.Add(new SqlParameter("@SensorSerialNumber", SqlDbType.NVarChar) { Value = entry.SensorSerialNumber });

                AddNullableIntParameter(cmd, "@DasId", entry.DASId);

                cmd.Parameters.Add(new SqlParameter("@DASSerialNumber", SqlDbType.NVarChar) { Value = entry.DASSerialNumber });

                cmd.Parameters.Add(new SqlParameter("@DASChannelIdx", SqlDbType.Int) { Value = entry.DASChannelIdx });

                cmd.Parameters.Add(new SqlParameter("@UserCode", SqlDbType.NVarChar) { Value = entry.UserCode });

                cmd.Parameters.Add(new SqlParameter("@UserChannelName", SqlDbType.NVarChar) { Value = entry.UserChannelName });

                cmd.Parameters.Add(new SqlParameter("@IsoCode", SqlDbType.NVarChar) { Value = entry.IsoCode });

                cmd.Parameters.Add(new SqlParameter("@IsoChannelName", SqlDbType.NVarChar) { Value = entry.IsoChannelName });

                cmd.Parameters.Add(new SqlParameter("@ScaleFactorMV", SqlDbType.Float) { Value = entry.ScaleFactor });

                AddNullableIntParameter(cmd, "@CalibrationRecordId", entry.CalibrationRecordId);

                cmd.Parameters.Add(new SqlParameter("@CalibrationRecord", SqlDbType.NVarChar) { Value = entry.CalibrationRecordXML });

                cmd.Parameters.Add(new SqlParameter("@Timestamp", SqlDbType.DateTime) { Value = entry.Timestamp });

                AddErrorNumberParameter(cmd, "@errorNumber", out var errorNumberParam);                                

                AddErrorMessageParameter(cmd, "@errorMessage", out var errorMessageParam);                

                var newIdParam = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(newIdParam);

                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumberParam.Value) && 0 != Convert.ToInt32(errorNumberParam.Value))
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_AnalogDiagnosticsUpdateInsert failed, {Convert.ToInt32(errorNumberParam.Value)}, {errorMessageParam.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                if (!DBNull.Value.Equals(newIdParam.Value))
                {
                    entry.Id = Convert.ToInt32(newIdParam.Value);
                }
                else
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_AnalogDiagnosticsUpdateInsert failed, no new_id returned");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_AnalogDiagnosticsUpdateInsert failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// updates or inserts a Diagnostic run into the database
        /// </summary>
        /// <param name="user">user committing record</param>
        /// <param name="connection">connection diagnostic run is being committed over</param>
        /// <param name="run">the record to insert or update</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsAnalogDiagnosticRunUpdateInsert(IUserDbRecord user,
            IConnectionDetails connection,
            ref IDiagnosticRun run)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (null == run)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_DiagnosticRunsUpdateInsert");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                AddNullableBigIntParameter(cmd, "@Id", run.Id);

                cmd.Parameters.Add(new SqlParameter("@DataPROUser", SqlDbType.NVarChar) { Value = run.DataPROUser });

                AddNullableIntParameter(cmd, "@TestId", run.TestId);

                cmd.Parameters.Add(new SqlParameter("@TestName", SqlDbType.NVarChar) { Value = run.TestName });

                cmd.Parameters.Add(new SqlParameter("@PreTest", SqlDbType.Bit) { Value = run.PreTest });

                AddErrorNumberParameter(cmd, "@errorNumber", out var errorNumberParam);

                AddErrorMessageParameter(cmd, "@errorMessage", out var errorMessageParam);

                var newIdParam = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(newIdParam);

                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumberParam.Value) && 0 != Convert.ToInt32(errorNumberParam.Value))
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_DiagnosticRunsUpdateInsert failed, {Convert.ToInt32(errorNumberParam.Value)}, {errorMessageParam.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                if (!DBNull.Value.Equals(newIdParam.Value))
                {
                    run.Id = Convert.ToInt32(newIdParam.Value);
                }
                else
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_DiagnosticRunsUpdateInsert failed, no new_id returned");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_DiagnosticRunsUpdateInsert failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// updates or inserts an input stream record into the database
        /// Database id is modified on record on an insert operation
        /// appears to always do an insert currently?
        /// </summary>
        /// <param name="user">user committing record</param>
        /// <param name="connection">connection record is being committed over</param>
        /// <param name="record">record to be inserted or updated</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsInputStreamUpdateInsert(IUserDbRecord user,
            IConnectionDetails connection,
            ref IStreamInputRecord record)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (null == record)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsStreamInputUpdateInsert");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = null });
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar) { Value = record.SerialNumber });

                cmd.Parameters.Add(new SqlParameter("@UDPAddress", SqlDbType.NVarChar) { Value = record.StreamInUDPAddress });

                cmd.Parameters.Add(new SqlParameter("@LastModified", SqlDbType.DateTime) { Value = record.LastModified });
                cmd.Parameters.Add(new SqlParameter("@LastModifiedBy", SqlDbType.NVarChar) { Value = record.LastUpdatedBy });
                cmd.Parameters.Add(new SqlParameter("@UserTags", SqlDbType.Binary) { Value = record.TagsBlobBytes });
                cmd.Parameters.Add(new SqlParameter("@Broken", SqlDbType.Bit) { Value = record.Broken });
                cmd.Parameters.Add(new SqlParameter("@DoNotUse", SqlDbType.Bit) { Value = record.DoNotUse });

                var newIdParam = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(newIdParam);

                var errorNumberParam =
                    new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumberParam);

                var errorMessageParam =
                    new SqlParameter("@errorMessage", SqlDbType.NVarChar, 250)
                    {
                        Direction = ParameterDirection.Output
                    };
                cmd.Parameters.Add(errorMessageParam);
                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumberParam.Value) && 0 != Convert.ToInt32(errorNumberParam.Value))
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsStreamInputUpdateInsert failed, {Convert.ToInt32(errorNumberParam.Value)}, {errorMessageParam.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                if (!DBNull.Value.Equals(newIdParam.Value))
                {
                    record.Id = Convert.ToInt32(newIdParam.Value);
                }
                else
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsStreamInputUpdateInsert failed, no new_id returned");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsStreamInputUpdateInsert failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// retrieves matching input streams from database
        /// </summary>
        /// <param name="user">user making request</param>
        /// <param name="connection">connection over which to look for records</param>
        /// <param name="Id">database id of record (use null for all)</param>
        /// <param name="SerialNumber">serial number of record (use null for all)</param>
        /// <param name="records">all matching records</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsInputStreamGet(IUserDbRecord user,
            IConnectionDetails connection,
            int? Id,
            string SerialNumber,
            out IStreamInputRecord[] records)
        {
            records = null;
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsStreamInputGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                if (null == Id)
                {
                    cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = null });
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = (int)Id });
                }
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = SerialNumber });

                var reader = cmd.ExecuteReader();
                var list = new List<IStreamInputRecord>();
                while (reader.Read())
                {
                    list.Add(new StreamInputRecord(reader));
                }
                records = list.ToArray();
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsStreamInputGet failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// retrieves matching thermocouplers from database
        /// </summary>
        /// <param name="user">user making request</param>
        /// <param name="connection">connection over which to look for records</param>
        /// <param name="Id">database id of record (use null for all)</param>
        /// <param name="SerialNumber">serial number of record (use null for all)</param>
        /// <param name="records">all matching records</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsThermocouplerGet(IUserDbRecord user,
            IConnectionDetails connection,
            int clientDbVersion,
            int? Id,
            string SerialNumber,
            out IThermocouplerRecord[] records)
        {
            var storedProcedureVersionToUse = 0;
            SqlCommand cmd;

            records = null;
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = Database.Database.PrepareForDbAccess(user, connection, connection.ClientDbVersion,
                "sp_SensorsThermocouplerGet", out storedProcedureVersionToUse, out cmd);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                if (null == Id)
                {
                    cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = null });
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = (int)Id });
                }
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = SerialNumber });

                var reader = cmd.ExecuteReader();
                var list = new List<IThermocouplerRecord>();
                while (reader.Read())
                {
                    list.Add(new ThermocouplerRecord(reader));
                }
                records = list.ToArray();
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsThermocouplerGet failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// updates or inserts an output stream record into the database
        /// Database id is modified on record on an insert operation
        /// appears to always do an insert currently?
        /// </summary>
        /// <param name="user">user committing record</param>
        /// <param name="connection">connection record is being committed over</param>
        /// <param name="record">record to be inserted or updated</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsOutputStreamUpdateInsert(IUserDbRecord user,
            IConnectionDetails connection,
            ref IStreamOutputRecord record)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (null == record)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, Database.Database.GetStoredProcedureVersionCached(connection, "sp_SensorsStreamOutputUpdateInsert"));
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = null });
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar) { Value = record.SerialNumber });

                cmd.Parameters.Add(new SqlParameter("@StreamProfile", SqlDbType.NVarChar) { Value = record.StreamOutUDPProfile });
                cmd.Parameters.Add(new SqlParameter("@UDPAddress", SqlDbType.NVarChar) { Value = record.StreamOutUDPAddress });
                cmd.Parameters.Add(new SqlParameter("@TimeChannelId", SqlDbType.Int) { Value = record.StreamOutUDPTimeChannelId });
                cmd.Parameters.Add(new SqlParameter("@DataChannelId", SqlDbType.Int) { Value = record.StreamOutUDPDataChannelId });
                cmd.Parameters.Add(new SqlParameter("@TmNSConfig", SqlDbType.NVarChar) { Value = record.StreamOutUDPTmNSConfig });
                cmd.Parameters.Add(new SqlParameter("@IRIGTimeDataPacketIntervalMs", SqlDbType.Int) { Value = record.StreamOutIRIGTimeDataPacketIntervalMs });

                cmd.Parameters.Add(new SqlParameter("@LastModified", SqlDbType.DateTime) { Value = record.LastModified });
                cmd.Parameters.Add(new SqlParameter("@LastModifiedBy", SqlDbType.NVarChar) { Value = record.LastUpdatedBy });
                cmd.Parameters.Add(new SqlParameter("@UserTags", SqlDbType.Binary) { Value = record.TagsBlobBytes });
                cmd.Parameters.Add(new SqlParameter("@Broken", SqlDbType.Bit) { Value = record.Broken });
                cmd.Parameters.Add(new SqlParameter("@DoNotUse", SqlDbType.Bit) { Value = record.DoNotUse });

                if (connection.ClientDbVersion >= DTS.Common.Constants.TMATS_INTERVAL_VERSION
                    && connection.ConnectionDbVersion >= DTS.Common.Constants.TMATS_INTERVAL_VERSION)
                {
                    cmd.Parameters.Add(new SqlParameter("@TMATSIntervalMs", SqlDbType.Int) { Value = record.StreamOutTMATSIntervalMs });
                }

                var newIdParam = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(newIdParam);

                var errorNumberParam =
                    new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumberParam);

                var errorMessageParam =
                    new SqlParameter("@errorMessage", SqlDbType.NVarChar, 250)
                    {
                        Direction = ParameterDirection.Output
                    };
                cmd.Parameters.Add(errorMessageParam);
                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumberParam.Value) && 0 != Convert.ToInt32(errorNumberParam.Value))
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsStreamOutputUpdateInsert failed, {Convert.ToInt32(errorNumberParam.Value)}, {errorMessageParam.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                if (!DBNull.Value.Equals(newIdParam.Value))
                {
                    record.Id = Convert.ToInt32(newIdParam.Value);
                }
                else
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsStreamOutputUpdateInsert failed, no new_id returned");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsStreamOutputUpdateInsert failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// retrieves matching output streams from database
        /// </summary>
        /// <param name="user">user making request</param>
        /// <param name="connection">connection over which to look for records</param>
        /// <param name="Id">database id of record (use null for all)</param>
        /// <param name="SerialNumber">serial number of record (use null for all)</param>
        /// <param name="records">all matching records</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsOutputStreamGet(IUserDbRecord user,
            IConnectionDetails connection,
            int? Id,
            string SerialNumber,
            out IStreamOutputRecord[] records)
        {
            records = null;
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, Database.Database.GetStoredProcedureVersionCached(connection, "sp_SensorsStreamOutputGet"));
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                if (null == Id)
                {
                    cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = null });
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = (int)Id });
                }
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = SerialNumber });

                var reader = cmd.ExecuteReader();
                var list = new List<IStreamOutputRecord>();
                while (reader.Read())
                {
                    list.Add(new StreamOutputRecord(reader, connection.ClientDbVersion, connection.ConnectionDbVersion));
                }
                records = list.ToArray();
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsStreamOutputGet failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// updates or inserts a new UART record into the database
        /// Id is updated if a new record is inserted
        /// </summary>
        /// <param name="user">user committing UART</param>
        /// <param name="connection">connection UART should be committed on</param>
        /// <param name="record">record being inserted or updated</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsUARTUpdateInsert(IUserDbRecord user,
            IConnectionDetails connection,
            ref IUARTRecord record)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (null == record)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsUARTUpdateInsert");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = null });
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar) { Value = record.SerialNumber });

                cmd.Parameters.Add(new SqlParameter("@BaudRate", SqlDbType.Int) { Value = record.UartBaudRate });
                cmd.Parameters.Add(new SqlParameter("@DataBits", SqlDbType.Int) { Value = record.UartDataBits });
                cmd.Parameters.Add(new SqlParameter("@StopBits", SqlDbType.NVarChar) { Value = record.UartStopBits });
                cmd.Parameters.Add(new SqlParameter("@Parity", SqlDbType.NVarChar) { Value = record.UartParity });
                cmd.Parameters.Add(new SqlParameter("@FlowControl", SqlDbType.NVarChar) { Value = record.UartFlowControl });
                cmd.Parameters.Add(new SqlParameter("@DataFormat", SqlDbType.NVarChar) { Value = record.UartDataFormat });

                cmd.Parameters.Add(new SqlParameter("@LastModified", SqlDbType.DateTime) { Value = record.LastModified });
                cmd.Parameters.Add(new SqlParameter("@LastModifiedBy", SqlDbType.NVarChar) { Value = record.LastUpdatedBy });
                cmd.Parameters.Add(new SqlParameter("@UserTags", SqlDbType.Binary) { Value = record.TagsBlobBytes });
                cmd.Parameters.Add(new SqlParameter("@Broken", SqlDbType.Bit) { Value = record.Broken });
                cmd.Parameters.Add(new SqlParameter("@DoNotUse", SqlDbType.Bit) { Value = record.DoNotUse });

                var newIdParam = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(newIdParam);

                var errorNumberParam =
                    new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumberParam);

                var errorMessageParam =
                    new SqlParameter("@errorMessage", SqlDbType.NVarChar, 250)
                    {
                        Direction = ParameterDirection.Output
                    };
                cmd.Parameters.Add(errorMessageParam);
                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumberParam.Value) && 0 != Convert.ToInt32(errorNumberParam.Value))
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsUARTUpdateInsert failed, {Convert.ToInt32(errorNumberParam.Value)}, {errorMessageParam.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                if (!DBNull.Value.Equals(newIdParam.Value))
                {
                    record.Id = Convert.ToInt32(newIdParam.Value);
                }
                else
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsUARTUpdateInsert failed, no new_id returned");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsUARTUpdateInsert failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// retrieves UART settings
        /// </summary>
        /// <param name="user">user making requests</param>
        /// <param name="connection">connection request sh</param>
        /// <param name="Id">Id to search for (use null for all)</param>
        /// <param name="SerialNumber">serial number to search for (use null or empty for all)</param>
        /// <param name="records">matching records found</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsUARTGet(IUserDbRecord user,
            IConnectionDetails connection,
            int? Id,
            string SerialNumber,
            out IUARTRecord[] records)
        {
            records = null;
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsUARTGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                if (null == Id)
                {
                    cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = null });
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = (int)Id });
                }
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = SerialNumber });

                var reader = cmd.ExecuteReader();
                var list = new List<IUARTRecord>();
                while (reader.Read())
                {
                    list.Add(new UARTRecord(reader));
                }
                records = list.ToArray();
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsUARTGet failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        public ulong SensorsCanGet(IUserDbRecord user,
            IConnectionDetails connection,
            int? Id,
            string SerialNumber,
            out ICANRecord[] records)
        {
            records = null;
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = DbAPI.GetDatabaseVersion(connection, out int actualDbVersion); if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            if (actualDbVersion < Constants.SLICE_PRO_CAN_FD_DB_VERSION) { return ErrorCodes.ERROR_NOT_SUPPORTED; }
            ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsCANGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                if (null == Id)
                {
                    cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = null });
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = (int)Id });
                }
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = SerialNumber });

                var reader = cmd.ExecuteReader();
                var list = new List<ICANRecord>();
                while (reader.Read())
                {
                    list.Add(new CANRecord(reader));
                }
                records = list.ToArray();
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsCANGet failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        public ulong SensorsCanUpdateInsert(IUserDbRecord user,
            IConnectionDetails connection,
            ref ICANRecord record)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (null == record)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            var ret = DbAPI.GetDatabaseVersion(connection, out int actualDbVersion); if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            if (actualDbVersion < Constants.SLICE_PRO_CAN_FD_DB_VERSION) { return ErrorCodes.ERROR_NOT_SUPPORTED; }
            ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsCanUpdateInsert");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = null });
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar) { Value = record.SerialNumber });

                cmd.Parameters.Add(new SqlParameter("@LastModified", SqlDbType.DateTime) { Value = record.LastModified });
                cmd.Parameters.Add(new SqlParameter("@LastModifiedBy", SqlDbType.NVarChar) { Value = record.LastUpdatedBy });
                cmd.Parameters.Add(new SqlParameter("@UserTags", SqlDbType.Binary) { Value = record.TagsBlobBytes });
                cmd.Parameters.Add(new SqlParameter("@Broken", SqlDbType.Bit) { Value = record.Broken });
                cmd.Parameters.Add(new SqlParameter("@DoNotUse", SqlDbType.Bit) { Value = record.DoNotUse });

                var newIdParam = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(newIdParam);

                var errorNumberParam =
                    new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumberParam);

                var errorMessageParam =
                    new SqlParameter("@errorMessage", SqlDbType.NVarChar, 250)
                    {
                        Direction = ParameterDirection.Output
                    };
                cmd.Parameters.Add(errorMessageParam);
                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumberParam.Value) && 0 != Convert.ToInt32(errorNumberParam.Value))
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsCanUpdateInsert failed, {Convert.ToInt32(errorNumberParam.Value)}, {errorMessageParam.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                if (!DBNull.Value.Equals(newIdParam.Value))
                {
                    record.Id = Convert.ToInt32(newIdParam.Value);
                }
                else
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsCanUpdateInsert failed, no new_id returned");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsCanUpdateInsert failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// commits a digital output setting to the db
        /// The record will be modified with a new database id if
        /// a new record is inserted
        /// </summary>
        /// <param name="user">user making commit</param>
        /// <param name="connection">connection over which to commit</param>
        /// <param name="record">the record to commit</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsDigitalOutUpdateInsert(IUserDbRecord user,
            IConnectionDetails connection,
            IDigitalOutDbRecord record)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsDigitalOutUpdateInsert");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = null });
                cmd.Parameters.Add(
                    new SqlParameter("@SerialNumber", SqlDbType.NVarChar) { Value = record.SerialNumber });
                cmd.Parameters.Add(
                    new SqlParameter("@DelayMS", SqlDbType.Float) { Value = record.DODelay });
                cmd.Parameters.Add(
                    new SqlParameter("@DurationMSFloat", SqlDbType.Float)
                    {
                        Value = record.DODuration
                    });
                cmd.Parameters.Add(
                    new SqlParameter("@OutputMode", SqlDbType.SmallInt) { Value = (int)record.DOMode });
                cmd.Parameters.Add(
                    new SqlParameter("@LimitDuration", SqlDbType.Bit) { Value = record.LimitDuration });
                cmd.Parameters.Add(
                    new SqlParameter("@LastModified", SqlDbType.DateTime) { Value = record.LastModified });
                cmd.Parameters.Add(
                    new SqlParameter("@LastModifiedBy", SqlDbType.NVarChar) { Value = record.ModifiedBy });
                cmd.Parameters.Add(new SqlParameter("@Version", SqlDbType.Int) { Value = record.Version });
                cmd.Parameters.Add(new SqlParameter("@UserTags", SqlDbType.Binary) { Value = record.TagsBlobBytes });
                cmd.Parameters.Add(new SqlParameter("@Broken", SqlDbType.Bit) { Value = record.Broken });
                cmd.Parameters.Add(new SqlParameter("@DoNotUse", SqlDbType.Bit) { Value = record.DoNotUse });
                var newIdParam = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(newIdParam);
                var errorNumberParam =
                    new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumberParam);
                var errorMessageParam =
                    new SqlParameter("@errorMessage", SqlDbType.NVarChar, 250)
                    {
                        Direction = ParameterDirection.Output
                    };
                cmd.Parameters.Add(errorMessageParam);

                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumberParam.Value) && 0 != Convert.ToInt32(errorNumberParam.Value))
                {
                    APILogger.Log($"sp_SensorsDigitalOutUpdateInsert failed: {errorMessageParam.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                record.DatabaseId = Convert.ToInt32(newIdParam.Value);
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsDigitalInUpdateInsert failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// retrieves all matching digital output settings in the db
        /// </summary>
        /// <param name="user">user requesting records</param>
        /// <param name="connection">connection to query records on</param>
        /// <param name="Id">Database id of records (can be null)</param>
        /// <param name="serialNumber">serial number/name of setting (can be null)</param>
        /// <param name="records">all matching records</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsDigitalOutGet(
            IUserDbRecord user,
            IConnectionDetails connection,
            int? id,
            string serialNumber,
            out IDigitalOutDbRecord[] records)
        {
            records = new IDigitalOutDbRecord[0];
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsDigitalOutGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                if (id is null)
                {
                    cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int) { Value = null });
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int) { Value = (int)id });
                }

                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = serialNumber });

                var reader = cmd.ExecuteReader();
                var list = new List<IDigitalOutDbRecord>();
                while (reader.Read())
                {
                    var record = new DigitalOutDbRecord(reader);
                    list.Add(record);
                }
                records = list.ToArray();

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsDigitalOutGet failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// inserts or updates a digital input record into the db.  If a record is inserted
        /// then the original record is updated with the database id of the entry
        /// </summary>
        /// <param name="user">user committing record </param>
        /// <param name="connection"></param>
        /// <param name="record"></param>
        /// <returns></returns>
        public ulong SensorsDigitalInUpdateInsert(IUserDbRecord user,
            IConnectionDetails connection,
            IDigitalInDbRecord record)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsDigitalInUpdateInsert");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = null });
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar) { Value = record.SerialNumber });
                cmd.Parameters.Add(new SqlParameter("@SettingMode", SqlDbType.Int) { Value = (int)record.Mode });
                cmd.Parameters.Add(new SqlParameter("@ScaleMultiplier", SqlDbType.NVarChar)
                {
                    Value = record.ScaleMultiplier.ToSerializeDbString()
                });
                cmd.Parameters.Add(new SqlParameter("@IsoCode", SqlDbType.NVarChar, 50) { Value = record.ISOCode });
                cmd.Parameters.Add(new SqlParameter("@IsoChannelName", SqlDbType.NVarChar, 255) { Value = record.ISOChannelName });
                if (null == record.UserCode) { record.UserCode = string.Empty; }
                cmd.Parameters.Add(new SqlParameter("@UserCode", SqlDbType.NVarChar, 50) { Value = record.UserCode });
                if (null == record.UserChannelName) { record.UserChannelName = string.Empty; }
                cmd.Parameters.Add(new SqlParameter("@UserChannelName", SqlDbType.NVarChar, 255) { Value = record.UserChannelName });
                cmd.Parameters.Add(
                    new SqlParameter("@LastModified", SqlDbType.DateTime) { Value = record.LastModified });
                cmd.Parameters.Add(
                    new SqlParameter("@LastModifiedBy", SqlDbType.NVarChar) { Value = record.LastModifiedBy });
                cmd.Parameters.Add(new SqlParameter("@eId", SqlDbType.NVarChar) { Value = record.EID });
                if (null == record.UserValue1) { record.UserValue1 = string.Empty; }
                cmd.Parameters.Add(
                    new SqlParameter("@UserValue1", SqlDbType.NVarChar) { Value = record.UserValue1 });
                if (null == record.UserValue2) { record.UserValue2 = string.Empty; }
                cmd.Parameters.Add(
                    new SqlParameter("@UserValue2", SqlDbType.NVarChar) { Value = record.UserValue2 });
                if (null == record.UserValue3) { record.UserValue3 = string.Empty; }
                cmd.Parameters.Add(
                    new SqlParameter("@UserValue3", SqlDbType.NVarChar) { Value = record.UserValue3 });
                cmd.Parameters.Add(
                    new SqlParameter("@UserTags", SqlDbType.VarBinary) { Value = record.UserTags });
                cmd.Parameters.Add(
                    new SqlParameter("@MeasurementUnit", SqlDbType.NVarChar) { Value = record.MeasurementUnit });
                cmd.Parameters.Add(
                    new SqlParameter("@FilterClass", SqlDbType.NVarChar) { Value = record.FilterClass.ToString() });
                var newIdParam = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(newIdParam);

                cmd.Parameters.Add(new SqlParameter("@Broken", SqlDbType.Bit) { Value = record.Broken });
                cmd.Parameters.Add(new SqlParameter("@DoNotUse", SqlDbType.Bit) { Value = record.DoNotUse });

                var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);

                var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorMessage);

                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumber.Value) && 0 != Convert.ToInt32(errorNumber.Value))
                {
                    APILogger.Log($"sp_SensorsDigitalInUpdateInsert failed: {errorMessage.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                record.Id = Convert.ToInt32(newIdParam.Value);
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsDigitalInUpdateInsert failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// retrieves any digital input settings matching input criteria
        /// in the database
        /// </summary>
        /// <param name="user">user making request</param>
        /// <param name="connection">connection to query over</param>
        /// <param name="id">Database Id of digital input setting (can be null)</param>
        /// <param name="serialNumber">serial number of setting (can be null)</param>
        /// <param name="eId">Electronic id of setting (can be null)</param>
        /// <param name="records">all matching records</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are errors</returns>
        public ulong SensorsDigitalInGet(IUserDbRecord user,
            IConnectionDetails connection,
            int? id,
            string serialNumber,
            string eId,
            out IDigitalInDbRecord[] records)
        {
            records = new IDigitalInDbRecord[0];
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsDigitalInGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                if (id is null)
                {
                    cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int) { Value = null });
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int) { Value = (int)id });
                }

                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = serialNumber });
                cmd.Parameters.Add(new SqlParameter("@EID", SqlDbType.NVarChar, 50) { Value = eId });

                var reader = cmd.ExecuteReader();
                var list = new List<IDigitalInDbRecord>();
                while (reader.Read())
                {
                    var record = new DigitalInDbRecord(reader);
                    list.Add(record);
                }
                records = list.ToArray();

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsDigitalInGet failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// Inserts or updates a squib setting in the db
        /// setting is modified with new db id after execution
        /// </summary>
        /// <param name="user">user inserting setting</param>
        /// <param name="connection">connection for inserting</param>
        /// <param name="record">record to be inserted</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsSquibUpdateInsert(IUserDbRecord user,
            IConnectionDetails connection,
            ISquibDbRecord record)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsSquibUpdateInsert");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("Id", SqlDbType.Int) { Value = null });
                cmd.Parameters.Add(new SqlParameter("IsoChannelName", SqlDbType.NVarChar, 255) { Value = record.IsoChannelName });
                if (null == record.UserCode) { record.UserCode = string.Empty; }
                cmd.Parameters.Add(new SqlParameter("UserCode", SqlDbType.NVarChar, 50) { Value = record.UserCode });
                if (null == record.UserChannelName) { record.UserChannelName = string.Empty; }
                cmd.Parameters.Add(new SqlParameter("UserChannelName", SqlDbType.NVarChar, 255) { Value = record.UserChannelName });
                cmd.Parameters.Add(new SqlParameter("Broken", SqlDbType.Bit) { Value = record.Broken });
                cmd.Parameters.Add(new SqlParameter("DoNotUse", SqlDbType.Bit) { Value = record.DoNotUse });
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = record.SerialNumber });
                cmd.Parameters.Add(new SqlParameter("@BypassCurrentFilter", SqlDbType.Bit) { Value = record.BypassCurrentFilter });
                cmd.Parameters.Add(new SqlParameter("@BypassVoltageFilter", SqlDbType.Bit) { Value = record.BypassVoltageFilter });
                cmd.Parameters.Add(new SqlParameter("@DelayMS", SqlDbType.Float) { Value = record.DelayMs });
                cmd.Parameters.Add(new SqlParameter("@DurationMS", SqlDbType.Float) { Value = record.DurationMs });
                cmd.Parameters.Add(new SqlParameter("@FireMode", SqlDbType.SmallInt) { Value = (short)record.FireMode });
                cmd.Parameters.Add(new SqlParameter("@IsoCode", SqlDbType.NVarChar, 50) { Value = record.IsoCode });
                cmd.Parameters.Add(
                    new SqlParameter("@MeasurementType", SqlDbType.SmallInt)
                    { Value = (short)record.MeasurementType });
                cmd.Parameters.Add(new SqlParameter("@SquibOutputCurrent", SqlDbType.Float) { Value = record.SquibOutputCurrent });
                cmd.Parameters.Add(new SqlParameter("@SquibToleranceLow", SqlDbType.Float) { Value = record.SquibToleranceLow });
                cmd.Parameters.Add(new SqlParameter("@SquibToleranceHigh", SqlDbType.Float) { Value = record.SquibToleranceHigh });
                cmd.Parameters.Add(new SqlParameter("@LimitDuration", SqlDbType.Bit) { Value = record.LimitDuration });
                cmd.Parameters.Add(new SqlParameter("@DefineDelayInTest", SqlDbType.Bit) { Value = record.DefineDelayInTest });
                cmd.Parameters.Add(new SqlParameter("@ArticleId", SqlDbType.NVarChar, 50) { Value = record.ArticleId });
                cmd.Parameters.Add(new SqlParameter("@Version", SqlDbType.Int) { Value = record.Version });
                cmd.Parameters.Add(new SqlParameter("@LastModified", SqlDbType.DateTime) { Value = record.LastModified });
                cmd.Parameters.Add(new SqlParameter("@LastModifiedBy", SqlDbType.NVarChar, 50) { Value = record.LastModifiedBy });
                if (null == record.UserValue1) { record.UserValue1 = string.Empty; }
                cmd.Parameters.Add(new SqlParameter("@UserValue1", SqlDbType.NVarChar, 255) { Value = record.UserValue1 });
                if (null == record.UserValue2) { record.UserValue2 = string.Empty; }
                cmd.Parameters.Add(new SqlParameter("@UserValue2", SqlDbType.NVarChar, 255) { Value = record.UserValue2 });
                if (null == record.UserValue3) { record.UserValue3 = string.Empty; }
                cmd.Parameters.Add(new SqlParameter("@UserValue3", SqlDbType.NVarChar, 255) { Value = record.UserValue3 });
                cmd.Parameters.Add(new SqlParameter("@UserTags", SqlDbType.VarBinary)
                { Value = record.UserTags });

                var newIdParam = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(newIdParam);

                var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);

                var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorMessage);

                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumber.Value) && 0 != Convert.ToInt32(errorNumber.Value))
                {
                    APILogger.Log($"sp_SensorsSquibUpdateInsert failed: {errorMessage.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                record.Id = Convert.ToInt32(newIdParam.Value);
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"sp_SensorsSquibUpdateInsert failed, {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// retrieves all squib settings matching input criteria
        /// </summary>
        /// <param name="user">user requesting squib settings</param>
        /// <param name="connection">connection for retrieving squib settings</param>
        /// <param name="eId">Electronic Id </param>
        /// <param name="Id">Database Id for squib setting</param>
        /// <param name="serialNumber">serial number/name of squib setting</param>
        /// <param name="records">output records discovered</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are errors</returns>
        public ulong SensorsSquibGet(IUserDbRecord user,
            IConnectionDetails connection,
            int? Id,
            string serialNumber,
            string eId,
            out ISquibDbRecord[] records)
        {
            records = new ISquibDbRecord[0];
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsSquibGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                if (Id is null)
                {
                    cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int) { Value = null });
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int) { Value = (int)Id });
                }

                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = serialNumber });
                cmd.Parameters.Add(new SqlParameter("@EID", SqlDbType.NVarChar, 50) { Value = eId });

                var reader = cmd.ExecuteReader();
                var list = new List<ISquibDbRecord>();
                while (reader.Read())
                {
                    var squib = new SquibDbRecord(reader);
                    list.Add(squib);
                }
                records = list.ToArray();

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// Deletes ALL sensors in the db
        /// </summary>
        /// <param name="user">user deleting sensors</param>
        /// <param name="connection">connection sensors to be deleted on</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are errors</returns>
        public ulong SensorsDeleteAll(IUserDbRecord user,
            IConnectionDetails connection)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsDeleteAll");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);

                var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorMessage);

                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumber.Value) && 0 != Convert.ToInt32(errorNumber.Value))
                {
                    APILogger.Log($"sp_SensorsDeleteAll failed: {errorMessage.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// retrieves the bridge resistance for a sensor
        /// </summary>
        /// <param name="user">user making query</param>
        /// <param name="connection">connection query is being made on</param>
        /// <param name="serialNumber">serial number of sensor</param>
        /// <param name="bridgeResistance">output bridge resistance of sensor</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsAnalogBridgeResistanceGet(IUserDbRecord user,
            IConnectionDetails connection,
            string serialNumber,
            out double bridgeResistance)
        {
            bridgeResistance = double.NaN;
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsAnalogBridgeResistanceGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@sensorID", SqlDbType.Int) { Value = null });
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = serialNumber });
                var bridgeResParam = new SqlParameter("@BridgeResistance", SqlDbType.Float) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(bridgeResParam);

                var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);

                var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorMessage);

                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumber.Value) && 0 != Convert.ToInt32(errorNumber.Value))
                {
                    APILogger.Log($"sp_SensorsAnalogBridgeResistanceGet failed: {errorMessage.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                if (DBNull.Value.Equals(bridgeResParam.Value))
                {
                    APILogger.Log($"sp_SensorsAnalogBridgeResistanceGet failed: {errorMessage.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                bridgeResistance = Convert.ToDouble(bridgeResParam.Value);
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// deletes all sensor calibrations matching criteria
        /// nulls are wild cards, so passing in all nulls will
        /// delete all calibrations
        /// </summary>
        /// <param name="user">user deleting calibrations</param>
        /// <param name="connection">connection to use for deletes</param>
        /// <param name="sensorSerialNumber">allows null</param>
        /// <param name="calibrationDate">allows null</param>
        /// <param name="modifyDate">allows null</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are errors</returns>
        public ulong SensorCalibrationsDelete(IUserDbRecord user,
            IConnectionDetails connection,
            string sensorSerialNumber,
            DateTime? calibrationDate,
            DateTime? modifyDate
            )
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorCalibrationsDelete");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                var list = new List<ISensorCalDbRecord>();
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@SensorSerialNumber", SqlDbType.NVarChar, 255) { Value = sensorSerialNumber });
                if (null == calibrationDate)
                {
                    cmd.Parameters.Add(new SqlParameter("@CalibrationDate", SqlDbType.DateTime) { Value = null });
                }
                else { cmd.Parameters.Add(new SqlParameter("@CalibrationDate", SqlDbType.DateTime) { Value = (DateTime)calibrationDate }); }

                if (null == modifyDate)
                {
                    cmd.Parameters.Add(new SqlParameter("@ModifiedDate", SqlDbType.DateTime) { Value = null });
                }
                else { cmd.Parameters.Add(new SqlParameter("@ModifiedDate", SqlDbType.DateTime) { Value = (DateTime)modifyDate }); }

                var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);

                var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorMessage);

                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumber.Value) && 0 != Convert.ToInt32(errorNumber.Value))
                {
                    APILogger.Log($"SensorCalibrationsDelete failed: {errorMessage.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// inserts a new calibration record
        /// </summary>
        /// <param name="user">user submitting record</param>
        /// <param name="connection">connection record is being submitted on</param>
        /// <param name="cal">calibration record</param>
        /// <param name="sensorType">type of sensor</param>
        /// <param name="setCalibrationId"></param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorCalibrationsInsert(IUserDbRecord user,
            IConnectionDetails connection,
            ISensorCalDbRecord cal,
            int sensorType,
            bool setCalibrationId)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (null == cal)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            if (sensorType < 0) { return ErrorCodes.ERROR_MISSING_PARAMETER; }
   

            SqlCommand cmd;

            var ret = Database.Database.PrepareForDbAccess(user, connection, connection.ClientDbVersion,
                "sp_SensorCalibrationsInsert", out var storedProcedureVersionToUse, out cmd);

            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("Id", SqlDbType.TinyInt) { Value = null });
                cmd.Parameters.Add(new SqlParameter("@SensorSerialNumber", SqlDbType.NVarChar, 50) { Value = cal.SerialNumber });
                cmd.Parameters.Add(new SqlParameter("@SensorType", SqlDbType.TinyInt) { Value = sensorType });
                cmd.Parameters.Add(new SqlParameter("@CalibrationDate", SqlDbType.DateTime)
                {
                    Value = cal.CalibrationDate.Date.ToShortDateString()
                });
                cmd.Parameters.Add(new SqlParameter("@Username", SqlDbType.NVarChar, 50) { Value = cal.Username ?? "" });
                cmd.Parameters.Add(new SqlParameter("@LocalOnly", SqlDbType.Bit) { Value = cal.LocalOnly });
                cmd.Parameters.Add(new SqlParameter("@NonLinear", SqlDbType.Bit) { Value = cal.NonLinear });
                cmd.Parameters.Add(new SqlParameter("@ModifyDate", SqlDbType.DateTime) { Value = cal.ModifyDate });
                cmd.Parameters.Add(new SqlParameter("@IsProportional", SqlDbType.Bit) { Value = cal.IsProportional });
                cmd.Parameters.Add(new SqlParameter("@RemoveOffset", SqlDbType.Bit) { Value = cal.RemoveOffset });
                if (storedProcedureVersionToUse >= DTS.Common.Constants.SENSOR_ASSEMBLY_DB_VERSION)
                {
                    cmd.Parameters.Add(new SqlParameter("@SensitivityInspection", SqlDbType.Int) { Value = cal.SensitivityInspection });
                    cmd.Parameters.Add(new SqlParameter("@CalibrationNote", SqlDbType.NVarChar, 2048) { Value = cal.CalibrationNote });
                    cmd.Parameters.Add(new SqlParameter("@UsageCount", SqlDbType.Int) { Value = cal.UsageCount });
                }
                var zero = cal.ZeroMethods;
                if (null == zero) { zero = new ZeroMethods(new ZeroMethod(DTS.Common.Enums.Sensors.ZeroMethodType.None, -.5, -.2)); }
                cmd.Parameters.Add(
                    new SqlParameter("@ZeroMethod", SqlDbType.NVarChar, 255)
                    {
                        Value = zero.ToSerializedString()
                    });
                cmd.Parameters.Add(new SqlParameter("@CertificationDocuments", SqlDbType.NVarChar, 2048)
                {
                    Value = string.Join(CultureInfo.InvariantCulture.TextInfo.ListSeparator,
                        cal.CertificationDocuments.ToArray())
                });
                var offset = cal.InitialOffsets;
                if (null == offset) { offset = new InitialOffsets(new InitialOffset(0)); }
                cmd.Parameters.Add(
                    new SqlParameter("@InitialOffset", SqlDbType.NVarChar)
                    {
                        Value = offset.ToSerializedString()
                    });
                cmd.Parameters.Add(
                    new SqlParameter("@CalibrationRecords", SqlDbType.NVarChar, 255)
                    {
                        Value = cal.Records.ToSerializedString(cal)
                    });
                cmd.Parameters.Add(new SqlParameter("@SetLatestCalibrationId", SqlDbType.Bit)
                { Value = setCalibrationId });
                var newIdParam =
                    new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(newIdParam);
                var errorNumber =
                    new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);
                var errorMessage =
                    new SqlParameter("@errorMessage", SqlDbType.NVarChar, 250)
                    {
                        Direction = ParameterDirection.Output
                    };
                cmd.Parameters.Add(errorMessage);

                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumber.Value) && 0 != Convert.ToInt32(errorNumber.Value))
                {
                    APILogger.Log($"SensorsCalibrationsInsert failed: {errorMessage.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                cal.CalibrationId = Convert.ToInt32(newIdParam.Value);
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// deletes all sensors matching criteria
        /// sensor types are defined in table SensorsType
        /// 0 - analog
        /// 1 - digital in
        /// 2 - digital out
        /// 3 - squib
        /// 4 - UART
        /// </summary>
        /// <param name="user">user deleting sensors</param>
        /// <param name="connection">connection sensors are being deleted on</param>
        /// <param name="sensorId">id in database</param>
        /// <param name="sensorType">type of sensor</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are errors</returns>
        public ulong SensorsDelete(IUserDbRecord user,
            IConnectionDetails connection,
            int sensorId,
            int sensorType)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsDelete");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                var list = new List<ISensorCalDbRecord>();
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@SensorId", SqlDbType.Int) { Value = sensorId });
                cmd.Parameters.Add(new SqlParameter("@SensorType", SqlDbType.Int) { Value = sensorType });

                var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);

                var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorMessage);

                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumber.Value) && 0 != Convert.ToInt32(errorNumber.Value))
                {
                    APILogger.Log($"SensorsDelete failed: {errorMessage.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// retrieves all sensor calibrations matching search criteria
        /// </summary>
        /// <param name="user"></param>
        /// <param name="connection"></param>
        /// <param name="sensorId"></param>
        /// <param name="serialNumber"></param>
        /// <param name="calibrations">null, or calibrations found</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorCalibrationsGet(IUserDbRecord user,
            IConnectionDetails connection,
            int? sensorId,
            string serialNumber,
            out ISensorCalDbRecord[] calibrations)
        {
            calibrations = null;
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
         
            var ret = DbAPI.GetDatabaseVersion(connection, out int actualDbVersion);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorCalibrationsGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }

            try
            {
                var list = new List<ISensorCalDbRecord>();
                cmd.CommandType = CommandType.StoredProcedure;
                if (null != sensorId)
                {
                    cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = (int)sensorId });
                }
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = serialNumber });

                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(new SensorCalDbRecord(reader, actualDbVersion));
                }

                calibrations = list.ToArray();
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// commits an analog sensor to the db
        /// </summary>
        /// <param name="user">DataPRO user making commit</param>
        /// <param name="connection">connection commit is being made on</param>
        /// <param name="record">the record being committed</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsAnalogUpdateInsert(IUserDbRecord user,
            IConnectionDetails connection,
            IAnalogDbRecord record)
        {
            if (null == record || string.IsNullOrWhiteSpace(record.SerialNumber))
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var storedProcedureVersionToUse = 0;
            SqlCommand cmd;

            var ret = Database.Database.PrepareForDbAccess(user, connection, connection.ClientDbVersion,
                "sp_SensorsAnalogUpdateInsert", out storedProcedureVersionToUse, out cmd);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(
                    new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = record.SerialNumber });
                cmd.Parameters.Add(
                    new SqlParameter("@UserSerialNumber", SqlDbType.NVarChar, 50) { Value = record.UserSerialNumber ?? "" });
                cmd.Parameters.Add(
                    new SqlParameter("@DiagnosticsMode", SqlDbType.Bit) { Value = record.DiagnosticsMode });
                cmd.Parameters.Add(
                    new SqlParameter("@SensorModelId", SqlDbType.Int) { Value = 0 });

                if ( null == record.Model) { record.Model = string.Empty; }
                cmd.Parameters.Add(new SqlParameter("@Model", SqlDbType.NVarChar, 50) { Value = record.Model });
                cmd.Parameters.Add(
                    new SqlParameter("@Manufacturer", SqlDbType.NVarChar, 50) { Value = record.Manufacturer });
                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.SmallInt) { Value = (int)record.Status });
                cmd.Parameters.Add(
                    new SqlParameter("@MeasurementUnit", SqlDbType.NVarChar, 50) { Value = record.DisplayUnit });
                cmd.Parameters.Add(
                    new SqlParameter("@OffsetToleranceLow", SqlDbType.Float) { Value = record.OffsetToleranceLow });
                cmd.Parameters.Add(
                    new SqlParameter("@OffsetToleranceHigh", SqlDbType.Float) { Value = record.OffsetToleranceHigh });
                cmd.Parameters.Add(new SqlParameter("@eId", SqlDbType.NVarChar, 50) { Value = record.EId ?? "" });
                cmd.Parameters.Add(new SqlParameter("@Capacity", SqlDbType.Float) { Value = record.Capacity });
                cmd.Parameters.Add(new SqlParameter("@Comment", SqlDbType.NVarChar, 50) { Value = record.Comment ?? "" });
                cmd.Parameters.Add(
                    new SqlParameter("@BridgeType", SqlDbType.SmallInt) { Value = DTS.Common.Enums.Sensors.SensorConstants.ConvertBridgeToInt(record.Bridge) });
                cmd.Parameters.Add(
                    new SqlParameter("@BridgeLegMode", SqlDbType.SmallInt) { Value = record.BridgeLegMode });
                cmd.Parameters.Add(new SqlParameter("@Shunt", SqlDbType.SmallInt) { Value = (int)record.Shunt });
                cmd.Parameters.Add(new SqlParameter("@Invert", SqlDbType.Bit) { Value = record.Invert });
                cmd.Parameters.Add(new SqlParameter("@UserValue1", SqlDbType.NVarChar, 50) { Value = record.UserValue1 ?? "" });
                cmd.Parameters.Add(new SqlParameter("@UserValue2", SqlDbType.NVarChar, 50) { Value = record.UserValue2 ?? "" });
                cmd.Parameters.Add(new SqlParameter("@UserValue3", SqlDbType.NVarChar, 50) { Value = record.UserValue3 ?? "" });

                var filter = record.Filter;
                if (null == filter) { filter = new FilterClass(DTS.Common.Enums.Sensors.FilterClassType.None); }
                cmd.Parameters.Add(
                    new SqlParameter("@FilterClass", SqlDbType.NVarChar, 50) { Value = filter.ToString() });
                cmd.Parameters.Add(
                    new SqlParameter("@BridgeResistance", SqlDbType.Float) { Value = record.BridgeResistance });
                //these parameters are required not null, if they are null in the commit just use string.empty
                //http://manuscript.dts.local/f/cases/38044/EQX-doesn-t-import-sensors-and-doesn-t-say-it-didn-t-import-sensors
                cmd.Parameters.Add(new SqlParameter("@IsoCode", SqlDbType.NVarChar, 50) { Value = record.ISOCode ?? string.Empty });
                cmd.Parameters.Add(new SqlParameter("@IsoChannelName", SqlDbType.NVarChar, 255) { Value = record.ISOChannelName ?? string.Empty });
                cmd.Parameters.Add(new SqlParameter("@UserCode", SqlDbType.NVarChar, 50) { Value = record.UserCode ?? string.Empty });
                cmd.Parameters.Add(new SqlParameter("@UserChannelName", SqlDbType.NVarChar, 255) { Value = record.UserChannelName ?? string.Empty });
                cmd.Parameters.Add(new SqlParameter("@CheckOffset", SqlDbType.Bit) { Value = record.CheckOffset });
                cmd.Parameters.Add(new SqlParameter("@SupportedExcitation", SqlDbType.NVarChar, 50)
                {
                    Value = string.Join(CultureInfo.InvariantCulture.TextInfo.ListSeparator,
                        record.SupportedExcitation.Select(e => e.ToString()).ToArray())
                });
                var initialEU = 0D;
                if (null != record.InitialEu)
                {
                    initialEU = (double)record.InitialEu;
                }
                cmd.Parameters.Add(new SqlParameter("@InitialEU", SqlDbType.Float) { Value = initialEU });

                cmd.Parameters.Add(new SqlParameter("@CalInterval", SqlDbType.Int) { Value = record.CalInterval });
                cmd.Parameters.Add(new SqlParameter("@CalibrationSignal", SqlDbType.Bit) { Value = record.CalibrationSignal });
                cmd.Parameters.Add(
                    new SqlParameter("@InternalShuntResistance", SqlDbType.Float)
                    {
                        Value = record.InternalShuntResistance
                    });
                cmd.Parameters.Add(
                    new SqlParameter("@ExternalShuntResistance", SqlDbType.Float)
                    {
                        Value = record.ExternalShuntResistance
                    });
                cmd.Parameters.Add(new SqlParameter("@UniPolar", SqlDbType.Bit) { Value = record.UniPolar });
                cmd.Parameters.Add(new SqlParameter("@RangeLow", SqlDbType.Float) { Value = record.RangeLow });
                cmd.Parameters.Add(new SqlParameter("@RangeAve", SqlDbType.Float) { Value = record.RangeMedium });
                cmd.Parameters.Add(new SqlParameter("@RangeHigh", SqlDbType.Float) { Value = record.RangeHigh });
                var created = record.Created;
                if (created < SqlDateTime.MinValue.Value) { created = SqlDateTime.MinValue.Value; }
                if (created > SqlDateTime.MaxValue.Value) { created = SqlDateTime.MaxValue.Value; }
                cmd.Parameters.Add(new SqlParameter("@Created", SqlDbType.DateTime) { Value = created });
                cmd.Parameters.Add(new SqlParameter("@TimesUsed", SqlDbType.BigInt) { Value = record.TimesUsed });
                cmd.Parameters.Add(new SqlParameter("@SensorCategory", SqlDbType.Int) { Value = record.SensorCategory });
                cmd.Parameters.Add(new SqlParameter("@BypassFilter", SqlDbType.Bit) { Value = record.BypassFilter });
                cmd.Parameters.Add(new SqlParameter("@CouplingMode", SqlDbType.SmallInt) { Value = (int)record.CouplingMode });
                cmd.Parameters.Add(new SqlParameter("@Version", SqlDbType.Int) { Value = record.Version });
                cmd.Parameters.Add(new SqlParameter("@LastModified", SqlDbType.DateTime) { Value = record.LastModified });
                cmd.Parameters.Add(
                    new SqlParameter("@ModifiedBy", SqlDbType.NVarChar, 50) { Value = record.ModifiedBy });
                cmd.Parameters.Add(new SqlParameter("@LocalOnly", SqlDbType.Bit) { Value = record.LocalOnly });
                cmd.Parameters.Add(new SqlParameter("@AxisNumber", SqlDbType.SmallInt) { Value = record.AxisNumber });
                cmd.Parameters.Add(new SqlParameter("@NumberOfAxes", SqlDbType.SmallInt) { Value = record.NumberOfAxes });
                cmd.Parameters.Add(new SqlParameter("@UserTags", SqlDbType.VarBinary) { Value = record.TagsBlobBytes });
                cmd.Parameters.Add(new SqlParameter("@DoNotUse", SqlDbType.Bit) { Value = record.DoNotUse });
                cmd.Parameters.Add(new SqlParameter("@Broken", SqlDbType.Bit) { Value = record.Broken });
                object o = DBNull.Value;
                if (null != record.FirstUseDate) { o = (DateTime)record.FirstUseDate; }
                cmd.Parameters.Add(new SqlParameter("@FirstUseDate", SqlDbType.DateTime) { Value = o });
                cmd.Parameters.Add(new SqlParameter("@LatestCalibrationId", SqlDbType.Int) { Value = DBNull.Value });
                cmd.Parameters.Add(new SqlParameter("@ACCouplingModeEnabled", SqlDbType.Bit) { Value = record.ACCouplingModeEnabled });
                if (storedProcedureVersionToUse >= DTS.Common.Constants.SENSOR_ASSEMBLY_DB_VERSION)
                {
                    cmd.Parameters.Add(new SqlParameter("@SensitivityTolerancePercent", SqlDbType.Float) { Value = record.SensitivityTolerancePercent });
                    cmd.Parameters.Add(new SqlParameter("@AssemblyName", SqlDbType.NVarChar, 50) { Value = record.AssemblyName });
                    cmd.Parameters.Add(new SqlParameter("@UsageCount", SqlDbType.Int) { Value = record.UsageCount });
                    cmd.Parameters.Add(new SqlParameter("@MaximumUsage", SqlDbType.Int) { Value = record.MaximumUsage });
                }
                var newIdParam = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(newIdParam);
                var errorNumberParam =
                    new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumberParam);
                var errorMessageParam =
                    new SqlParameter("@errorMessage", SqlDbType.NVarChar, 250)
                    {
                        Direction = ParameterDirection.Output
                    };
                cmd.Parameters.Add(errorMessageParam);

                cmd.ExecuteNonQuery();
                if (int.Parse(errorNumberParam.Value.ToString()) != 0)
                {
                    APILogger.Log($"SensorsAnalogUpdateInsert failed: {errorMessageParam.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                record.Id = Convert.ToInt32(newIdParam.Value);

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// retrieves all analog sensors matching search criteria
        /// </summary>
        /// <param name="user">user querying sensors</param>
        /// <param name="connection">connection is using</param>
        /// <param name="sensorId">sensor database id (allows null)</param>
        /// <param name="serialNumber">sensor serial number (allows null)</param>
        /// <param name="eId">electronic Id (DALLAS or TEDS id value)</param>
        /// <param name="sensors">any sensors matching criteria</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsAnalogGet(IUserDbRecord user,
            IConnectionDetails connection,
            int? sensorId,
            string serialNumber,
            string eId,
            out IAnalogDbRecord[] sensors)
        {
            sensors = null;
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = DbAPI.GetDatabaseVersion(connection, out int actualDbVersion);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsAnalogGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                var list = new List<IAnalogDbRecord>();
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = sensorId });
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = serialNumber });
                cmd.Parameters.Add(new SqlParameter("@EID", SqlDbType.NVarChar, 50) { Value = eId });

                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(new AnalogDbRecord(reader, actualDbVersion));
                }

                sensors = list.ToArray();
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        /// <summary>
        /// retrieves all analog sensors matching search criteria
        /// </summary>
        /// <param name="user">user querying sensors</param>
        /// <param name="connection">connection is using</param>
        /// <param name="serialNumber">sensor serial number (allows null)</param>
        /// <param name="sensors">any sensors matching criteria</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong SensorsGet(IUserDbRecord user,
            IConnectionDetails connection,
            string serialNumber,
            out ISensorDbRecord[] sensors)
        {
            sensors = null;
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_SensorsGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                var list = new List<ISensorDbRecord>();
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 50) { Value = serialNumber });

                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(new SensorDbRecord(reader));
                }

                sensors = list.ToArray();
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }
        public ulong UpdateAssemblySensorUsageCount(IUserDbRecord user,
            IConnectionDetails connection,
            string assemblyName,
            int newUsageCount)
        {
            if (string.IsNullOrWhiteSpace(assemblyName) || newUsageCount == 0)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var storedProcedureVersionToUse = 0;
            SqlCommand cmd;

            var ret = Database.Database.PrepareForDbAccess(user, connection, connection.ClientDbVersion,
                "sp_SensorsAnalogAssemblyUsageCountUpdate", out storedProcedureVersionToUse, out cmd);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@AssemblyName", SqlDbType.NVarChar, 50) { Value = assemblyName });
                cmd.Parameters.Add(new SqlParameter("@UsageCount", SqlDbType.Int) { Value = newUsageCount });

                var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int)
                { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);

                var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255)
                { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorMessage);

                cmd.ExecuteNonQuery();
                if (!DBNull.Value.Equals(errorNumber.Value) && Convert.ToInt32(errorNumber.Value) != 0)
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"Error number in {cmd.CommandText} = {errorNumber.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
            }
            finally
            {
                cmd.Connection.Dispose();
            }
            return ErrorCodes.ERROR_SUCCESS;
        }

        public ulong UpdateSensorUsageCount(IUserDbRecord user,
            IConnectionDetails connection, 
            ISensorData sd)
        {
            if (null == sd || sd.DatabaseId == 0 || string.IsNullOrWhiteSpace(sd.DatabaseId.ToString()))
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var storedProcedureVersionToUse = 0;
            SqlCommand cmd;

            var ret = Database.Database.PrepareForDbAccess(user, connection, connection.ClientDbVersion,
                "sp_SensorsAnalogUsageCountUpdate", out storedProcedureVersionToUse, out cmd);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@SensorId", SqlDbType.Int) { Value = sd.DatabaseId });
                cmd.Parameters.Add(new SqlParameter("@UsageCount", SqlDbType.Int) { Value = sd.UsageCount });

                var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int)
                { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);

                var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255)
                { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorMessage);

                cmd.ExecuteNonQuery();
                if (!DBNull.Value.Equals(errorNumber.Value) && Convert.ToInt32(errorNumber.Value) != 0)
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"Error number in {cmd.CommandText} = {errorNumber.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
            }
            finally
            {
                cmd.Connection.Dispose();
            }
            return ErrorCodes.ERROR_SUCCESS;
        }
        public ulong UpdateSensorCalibrationUsageCount(IUserDbRecord user,
            IConnectionDetails connection,
            int sensorId,
            int sensorCalibrationId,
            int newUsageCount)
        {
            if (string.IsNullOrWhiteSpace(sensorCalibrationId.ToString()) || (sensorCalibrationId <= 0))
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var storedProcedureVersionToUse = 0;
            SqlCommand cmd;

            var ret = Database.Database.PrepareForDbAccess(user, connection, connection.ClientDbVersion,
                "sp_SensorCalibrationsUsageCountUpdate", out storedProcedureVersionToUse, out cmd);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@SensorId", SqlDbType.Int) { Value = sensorId });
                cmd.Parameters.Add(new SqlParameter("@SensorCalibrationId", SqlDbType.Int) { Value = sensorCalibrationId });
                cmd.Parameters.Add(new SqlParameter("@UsageCount", SqlDbType.Int) { Value = newUsageCount });

                var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int)
                { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);

                var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255)
                { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorMessage);

                cmd.ExecuteNonQuery();
                if (!DBNull.Value.Equals(errorNumber.Value) && Convert.ToInt32(errorNumber.Value) != 0)
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.Sensors, $"Error number in {cmd.CommandText} = {errorNumber.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
            }
            finally
            {
                cmd.Connection.Dispose();
            }
            return ErrorCodes.ERROR_SUCCESS;
        }
    }
}