using DbAPI.Connections;
using DbAPI.Errors;
using DbAPI.Logging;
using DTS.Common.Classes.Groups;
using DTS.Common.Classes.TestSetups;
using DTS.Common.Interface.Database;
using DTS.Common.Interface.Groups;
using DTS.Common.Interface.TestSetups;
using DTS.Common.Interface.TestSetups.TestSetupsList;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;

namespace DbAPI.TestSetups
{
    /// <summary>
    /// Handles TestSetup functions
    /// <inheritdoc cref="ITestSetups"/>
    /// </summary>
    internal class TestSetups : ITestSetups
    {
        /// <summary>
        /// deletes matching test setup hardware record
        /// at least one parameter (id/dasid/testsetupid) must be specified
        /// </summary>
        /// <param name="user">user making request</param>
        /// <param name="connection">connection request is being made on</param>
        /// <param name="Id">id of test setup hardware record (null for all)</param>
        /// <param name="dasId">id of das (null for all)</param>
        /// <param name="testSetupId">id of test setup (null for all)</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong TestSetupHardwareDelete(IUserDbRecord user,
            IConnectionDetails connection,
            int? Id,
            int? dasId,
            int? testSetupId
            )
        {
            if (null == Id && null == dasId && null == testSetupId)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }

            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_TestSetupHardwareDelete");
            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 }); }

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

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

                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 (null != errorNumber.Value && !DBNull.Value.Equals(errorNumber.Value))
                {
                    if (0 != Convert.ToInt32(errorNumber.Value))
                    {
                        LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupHardwareDelete - Error - {errorNumber.Value} - {errorMessage.Value}");
                        return ErrorCodes.ERROR_UNKNOWN;
                    }
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupHardwareDelete - Error {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// updates a test setup hardware record
        /// </summary>
        /// <param name="user">user committing update</param>
        /// <param name="connection">connection update is being made on</param>
        /// <param name="record">updated record</param>
        /// <returns>0 on success, all other values are error codes</returns>
        public ulong TestSetupHardwareUpdate(IUserDbRecord user,
            IConnectionDetails connection,
            ITestSetupHardwareRecord record)
        {
            if (null == record)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }

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

                cmd.Parameters.Add(new SqlParameter("@DASId", SqlDbType.Int) { Value = record.DASId });
                cmd.Parameters.Add(new SqlParameter("@TestSetupId", SqlDbType.Int) { Value = record.TestSetupId });
                cmd.Parameters.Add(new SqlParameter("@AddOrRemove", SqlDbType.Bit) { Value = record.AddDAS });
                cmd.Parameters.Add(new SqlParameter("@SamplesPerSecond", SqlDbType.Float) { Value = record.SamplesPerSecond });
                cmd.Parameters.Add(new SqlParameter("@IsClockMaster", SqlDbType.Bit) { Value = record.IsClockMaster });
                cmd.Parameters.Add(new SqlParameter("@AntiAliasFilterRate", SqlDbType.Float) { Value = record.AntiAliasFilterRate });
                if (storedProcedureVersionToUse >= DTS.Common.Constants.PTP_DOMAIN_ID_DB_VERSION)
                {
                    cmd.Parameters.Add(new SqlParameter("@PTPDomainId", SqlDbType.Int) { Value = (int)record.PTPDomainId });
                }

                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 (null != errorNumber.Value && !DBNull.Value.Equals(errorNumber.Value))
                {
                    if (0 != Convert.ToInt32(errorNumber.Value))
                    {
                        LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupHardwareUpdate - Error - {errorNumber.Value} - {errorMessage.Value}");
                        return ErrorCodes.ERROR_UNKNOWN;
                    }
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupHardwareUpdate - Error {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// inserts a new record
        /// </summary>
        /// <param name="user">user inserting record</param>
        /// <param name="connection">connection being inserted on</param>
        /// <param name="record">record being inserted</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong TestSetupHardwareInsert(IUserDbRecord user,
            IConnectionDetails connection,
            ITestSetupHardwareRecord record)
        {
            if (null == record)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }

            SqlCommand cmd;
            var ret = PrepareForDbAccess(user, connection, connection.ClientDbVersion,
                "sp_TestSetupHardwareInsert", out int storedProcedureVersionToUse, out cmd);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }

            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@DASId", SqlDbType.Int) { Value = record.DASId });
                cmd.Parameters.Add(new SqlParameter("@TestSetupId", SqlDbType.Int) { Value = record.TestSetupId });
                cmd.Parameters.Add(new SqlParameter("@AddOrRemove", SqlDbType.Bit) { Value = record.AddDAS });
                cmd.Parameters.Add(new SqlParameter("@SamplesPerSecond", SqlDbType.Float) { Value = record.SamplesPerSecond });
                cmd.Parameters.Add(new SqlParameter("@IsClockMaster", SqlDbType.Bit) { Value = record.IsClockMaster });
                cmd.Parameters.Add(new SqlParameter("@AntiAliasFilterRate", SqlDbType.Float) { Value = record.AntiAliasFilterRate });
                if (storedProcedureVersionToUse >= DTS.Common.Constants.PTP_DOMAIN_ID_DB_VERSION)
                {
                    cmd.Parameters.Add(new SqlParameter("@PTPDomainId", SqlDbType.Int) { Value = (int)record.PTPDomainId });
                }

                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 (null != errorNumber.Value && !DBNull.Value.Equals(errorNumber.Value))
                {
                    if (0 != Convert.ToInt32(errorNumber.Value))
                    {
                        LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupHardwareInsert - Error - {errorNumber.Value} - {errorMessage.Value}");
                        return ErrorCodes.ERROR_UNKNOWN;
                    }
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupHardwareInsert - Error {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// retrieves all hardware meta data associated with test
        /// [sample rate, aaf, etc]
        /// </summary>
        /// <param name="user">user retrieving hardware</param>
        /// <param name="connection">connecting hardware is retrieved on</param>
        /// <param name="testSetupId">id of test setup (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 TestSetupHardwareGet(IUserDbRecord user,
            IConnectionDetails connection,
            int clientDbVersion,
            int? testSetupId,
            out ITestSetupHardwareRecord[] records)
        {
            var storedProcedureVersionToUse = 0;
            records = null;
            SqlCommand cmd;

            var ret = PrepareForDbAccess(user, connection, clientDbVersion,
                "sp_TestSetupHardwareGet", out storedProcedureVersionToUse, out cmd);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }

            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

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

                var reader = cmd.ExecuteReader();
                var list = new List<ITestSetupHardwareRecord>();
                while (reader.Read())
                {
                    list.Add(new TestSetupHardwareRecord(reader, storedProcedureVersionToUse));
                }
                records = list.ToArray();
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupHardwareGet - Error {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// inserts a new group/test setup association into db
        /// </summary>
        /// <param name="user">user making request</param>
        /// <param name="connection">connection request is being made on</param>
        /// <param name="record">record to insert</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong TestSetupGroupsInsert(IUserDbRecord user,
            IConnectionDetails connection,
            ITestSetupGroupRecord record)
        {
            if (null == record)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }

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

                cmd.Parameters.Add(new SqlParameter("@GroupId", SqlDbType.Int) { Value = record.GroupId });
                cmd.Parameters.Add(new SqlParameter("@TestSetupId", SqlDbType.Int) { Value = record.TestSetupId });
                cmd.Parameters.Add(new SqlParameter("@DisplayOrder", SqlDbType.Int) { Value = record.DisplayOrder });
                cmd.Parameters.Add(new SqlParameter("@Position", SqlDbType.VarChar, 1) { Value = record.Position });
                cmd.Parameters.Add(new SqlParameter("@TestObjectType", SqlDbType.VarChar, 1) { Value = record.TestObjectType });
                cmd.Parameters.Add(new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output });

                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 (null != errorNumber.Value && !DBNull.Value.Equals(errorNumber.Value))
                {
                    if (0 != Convert.ToInt32(errorNumber.Value))
                    {
                        LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups,
                            $"TestSetupsGroupsInsert Error - {errorNumber.Value} - {errorMessage.Value}");
                        return ErrorCodes.ERROR_UNKNOWN;
                    }
                }

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupsGroupInsert Error - {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }

        }
        /// <summary>
        /// updates a group/test setup association in db
        /// </summary>
        /// <param name="user">user making request</param>
        /// <param name="connection">connection is being made on</param>
        /// <param name="record">updated record</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong TestSetupGroupsUpdate(IUserDbRecord user,
            IConnectionDetails connection,
            ITestSetupGroupRecord record)
        {
            if (null == record)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }

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

                cmd.Parameters.Add(new SqlParameter("@GroupId", SqlDbType.Int) { Value = record.GroupId });
                cmd.Parameters.Add(new SqlParameter("@TestSetupId", SqlDbType.Int) { Value = record.TestSetupId });
                cmd.Parameters.Add(new SqlParameter("@DisplayOrder", SqlDbType.Int) { Value = record.DisplayOrder });
                cmd.Parameters.Add(new SqlParameter("@Position", SqlDbType.VarChar, 1) { Value = record.Position });
                cmd.Parameters.Add(new SqlParameter("@TestObjectType", SqlDbType.VarChar, 1) { Value = record.TestObjectType });

                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 (null != errorNumber.Value && !DBNull.Value.Equals(errorNumber.Value))
                {
                    if (0 != Convert.ToInt32(errorNumber.Value))
                    {
                        LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups,
                            $"TestSetupsGroupsUpdate Error - {errorNumber.Value} - {errorMessage.Value}");
                        return ErrorCodes.ERROR_UNKNOWN;
                    }
                }

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupsGroupInsert Error - {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// retrieves all group records matching search criteria
        /// </summary>
        /// <param name="user">user making request</param>
        /// <param name="connection">connection request is being made on</param>
        /// <param name="groupId">database id of group (use null for all)</param>
        /// <param name="testSetupId">database id of test setup (use null for all)</param>
        /// <param name="testSetupName">test setup name for test setup (use null for all)</param>
        /// <param name="records">matching groups</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong TestSetupGroupsGet(IUserDbRecord user,
            IConnectionDetails connection,
            int? groupId,
            int? testSetupId,
            string testSetupName,
            out ITestSetupGroupRecord[] records
            )
        {
            records = null;
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }

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

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

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

                cmd.Parameters.Add(new SqlParameter("@TestSetupName", SqlDbType.NVarChar, 255) { Value = testSetupName });

                var reader = cmd.ExecuteReader();
                var list = new List<ITestSetupGroupRecord>();
                while (reader.Read())
                {
                    list.Add(new TestSetupGroupRecord(reader));
                }
                records = list.ToArray();
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupsGroupsGet Error - {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// Commits a change to the IsDirty or IsComplete flags in the db for a test
        /// </summary>
        /// <param name="user">user committing change</param>
        /// <param name="connection">connection change is being committed on</param>
        /// <param name="name">name of test setup to modify</param>
        /// <param name="dirty">whether to set dirty flag or not.  Dirty flag indicates
        /// whether the test setup has been checked for completeness and readiness to run</param>
        /// <param name="complete">whether the test setup is ready to run or not</param>
        /// <param name="error">errors associated with test, if any</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong TestSetupsMarkIsDirtyIsComplete(IUserDbRecord user,
            IConnectionDetails connection,
            string name,
            bool dirty,
            bool complete,
            string error)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }

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

                cmd.Parameters.Add(new SqlParameter("@TestSetupName", SqlDbType.NVarChar, 50) { Value = name });
                cmd.Parameters.Add(new SqlParameter("@Dirty", SqlDbType.Bit) { Value = dirty });
                cmd.Parameters.Add(new SqlParameter("@Complete", SqlDbType.Bit) { Value = complete });
                cmd.Parameters.Add(new SqlParameter("@Error", SqlDbType.NVarChar, 255) { Value = error });
                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)
                {
                    var msg = Convert.ToString(errorMessageParam.Value);
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupsMarkIsDirtyIsComplete failed: {errorNumberParam.Value} - {msg}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupsMarkIsDirtyIsComplete failed: {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// deletes all test setups matching criteria
        /// </summary>
        /// <param name="user">user deleting test setups</param>
        /// <param name="connection">connection tests are being deleted on</param>
        /// <param name="date">date of oldest allowed test setup</param>
        /// <returns>0 (ERROR_SUCCESS) on success.  All other values are error codes.</returns>
        public ulong TestSetupsDeleteByDate(IUserDbRecord user,
            IConnectionDetails connection,
            DateTime date)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }

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

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


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

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

                cmd.ExecuteNonQuery();

                var errorNumber = int.Parse(errorNumberParam.Value.ToString());
                if (errorNumber != 0)
                {
                    var message = errorMessageParam.Value.ToString();
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupsDeleteByDate failed: {errorNumber} - {message}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupsDeleteByDate failed: {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// deletes all test setups and groups
        /// </summary>
        /// <param name="user">user requesting deletes</param>
        /// <param name="connection">connection to delete on</param>
        /// <returns>0 (ERROR_SUCCESS) on success.  All other values are error codes</returns>
        public ulong TestSetupsAndGroupsDeleteAll(IUserDbRecord user,
            IConnectionDetails connection)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }

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

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

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

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

                if (DBNull.Value != errorNumberParam.Value)
                {
                    var error = int.Parse(errorNumberParam.Value.ToString());
                    if (error != 0)
                    {
                        var message = int.Parse(errorNumberParam.Value.ToString());
                        var state = int.Parse(errorNumberParam.Value.ToString());
                        LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupsAndGroupsDeleteAll failed: {error} - {state} - {message}");
                        return ErrorCodes.ERROR_UNKNOWN;
                    }
                }

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"TestSetupsAndGroupsDeleteAll failed: {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// Deletes all test setups that match the search criteria
        /// </summary>
        /// <param name="user">user deleting test setups</param>
        /// <param name="connection">connection to delete test setups on</param>
        /// <param name="ids">ids of test setups to delete</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are error codes</returns>
        public ulong TestSetupsDeleteById(IUserDbRecord user,
            IConnectionDetails connection,
            int[] ids)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (null == ids || 0 == ids.Length)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }

            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_TestSetupsDeleteManyById");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@TestSetupIdList", SqlDbType.NVarChar) { Value = string.Join(",", ids) });

                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))
                {
                    var error = int.Parse(errorNumber.Value.ToString());
                    if (error != 0)
                    {
                        LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"sp_TestSetupsDeleteById failed: {error} - {errorMessage.Value}");
                    }
                }

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"sp_TestSetupsDeleteById failed: {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// deletes all test setups that match the search criteria
        /// </summary>
        /// <param name="user">user deleting test setups</param>
        /// <param name="connection">connection to delete test setups on</param>
        /// <param name="names">names of test setups to delete</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are errors</returns>
        public ulong TestSetupsDeleteByName(IUserDbRecord user,
            IConnectionDetails connection,
            string[] names)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (null == names || 0 == names.Length)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_TestSetupsDeleteManyByName");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@TestSetupNameList", SqlDbType.NVarChar) { Value = string.Join(",", names) });
                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))
                {
                    var error = int.Parse(errorNumber.Value.ToString());
                    if (error != 0)
                    {
                        LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"sp_TestSetupsDeleteByName failed: {error} - {errorMessage.Value}");
                    }
                }

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"sp_TestSetupsDeleteByName failed: {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        private ulong PrepareForDbAccess(IUserDbRecord user,
            IConnectionDetails connection,
            int clientDbVersion,
            string storedProcedure,
            out int storedProcedureVersionToUse,
            out SqlCommand cmd)
        {
            storedProcedureVersionToUse = 0;
            cmd = null;

            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = DbAPI.GetStoredProcedureToUse(connection, storedProcedure, clientDbVersion, out storedProcedureVersionToUse);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }

            ret = ConnectionManager.GetSqlCommand(connection, out cmd, storedProcedureVersionToUse == 0 ? storedProcedure : $"{storedProcedure}_{storedProcedureVersionToUse}");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }

            return ret;
        }
        /// <summary>
        /// commits a test setup into the database
        /// </summary>
        /// <param name="user">user committing record</param>
        /// <param name="connection">connection record is being committed on</param>
        /// <param name="clientDbVersion">the db version of the calling client</param>
        /// <param name="record">record being committed</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are errors</returns>
        public ulong TestSetupsUpdateInsert(IUserDbRecord user,
            IConnectionDetails connection,
            int clientDbVersion,
            ref ITestSetupRecord record)
        {
            var storedProcedureVersionToUse = 0;
            SqlCommand cmd;

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

                #region params

                cmd.Parameters.Add(
                    new SqlParameter("@TestSetupName", SqlDbType.NVarChar, 50) { Value = record.Name });
                cmd.Parameters.Add(
                    new SqlParameter("@SetupDescription", SqlDbType.NVarChar, 50) { Value = record.Description ?? "" });
                cmd.Parameters.Add(
                    new SqlParameter("@AutomaticTestProgression", SqlDbType.Bit)
                    {
                        Value = record.AutomaticProgression
                    });
                cmd.Parameters.Add(
                    new SqlParameter("@AutomaticProgressionDelayMS", SqlDbType.Int)
                    {
                        Value = record.AutomaticProgressionDelayMS
                    });
                cmd.Parameters.Add(
                    new SqlParameter("@InvertTrigger", SqlDbType.Bit) { Value = record.InvertTriggerCompletion });
                cmd.Parameters.Add(
                    new SqlParameter("@InvertStart", SqlDbType.Bit) { Value = record.InvertStartRecordCompletion });
                if (storedProcedureVersionToUse >= DTS.Common.Constants.IgnoreShorted_DB_VERSION)
                {
                    //Since we're not calling the older version (91) that doesn't have these parameters, include them
                    cmd.Parameters.Add(new SqlParameter("@IgnoreShortedStart", SqlDbType.Bit) { Value = record.IgnoreShortedStartCompletion });
                    cmd.Parameters.Add(new SqlParameter("@IgnoreShortedTrigger", SqlDbType.Bit) { Value = record.IgnoreShortedTriggerCompletion });
                }
                cmd.Parameters.Add(
                    new SqlParameter("@ViewDiagnostics", SqlDbType.Bit) { Value = record.ViewDiagnostics });
                cmd.Parameters.Add(
                    new SqlParameter("@VerifyChannels", SqlDbType.Bit) { Value = record.VerifyChannels });
                cmd.Parameters.Add(
                    new SqlParameter("@AutoVerifyChannels", SqlDbType.Bit) { Value = record.AutoVerifyChannels });
                cmd.Parameters.Add(new SqlParameter("@VerifyChannelsDelayMS", SqlDbType.Int)
                {
                    Value = Convert.ToInt32(record.AutoVerifyDelaySeconds * 1000)
                });

                cmd.Parameters.Add(
                    new SqlParameter("@RecordingMode", SqlDbType.SmallInt) { Value = record.RecordingMode });

                cmd.Parameters.Add(
                    new SqlParameter("@SamplesPerSecond", SqlDbType.Float) { Value = record.SamplesPerSecondAggregate });
                cmd.Parameters.Add(
                    new SqlParameter("@PreTriggerSeconds", SqlDbType.Float) { Value = record.PreTriggerSeconds });
                cmd.Parameters.Add(
                    new SqlParameter("@PostTriggerSeconds", SqlDbType.Float) { Value = record.PostTriggerSeconds });

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

                cmd.Parameters.Add(
                    new SqlParameter("@StrictDiagnostics", SqlDbType.Bit)
                    {
                        Value = record.StrictDiagnostics ? 255 : 0
                    });
                cmd.Parameters.Add(
                    new SqlParameter("@RequireConfirmationOnErrors", SqlDbType.Bit)
                    {
                        Value = record.RequireUserConfirmationOnErrors
                    });
                cmd.Parameters.Add(
                    new SqlParameter("@ROIDownload", SqlDbType.Bit) { Value = record.DoROIDownload });
                cmd.Parameters.Add(
                    new SqlParameter("@ViewROIDownload", SqlDbType.Bit) { Value = record.ViewROIDownload });
                cmd.Parameters.Add(new SqlParameter("@DownloadAll", SqlDbType.Bit) { Value = record.DownloadAll });
                cmd.Parameters.Add(
                    new SqlParameter("@ViewRealtime", SqlDbType.Bit) { Value = record.ViewRealtime });
                cmd.Parameters.Add(
                    new SqlParameter("@RealtimePlotCount", SqlDbType.SmallInt)
                    {
                        Value = record.DefaultNumberRealtimeGraphs
                    });

                if (storedProcedureVersionToUse < DTS.Common.Constants.ROITables_DB_VERSION)
                {
                    //Since we're calling the older version (91) that has this parameter, include it
                    cmd.Parameters.Add(new SqlParameter("@RegionsOfInterest", SqlDbType.NVarChar, -1)
                    {
                        Value = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(
                            record.RegionsOfInterest)
                    });
                }

                cmd.Parameters.Add(new SqlParameter("@ROIStart", SqlDbType.Float) { Value = record.ROIStart });
                cmd.Parameters.Add(new SqlParameter("@ROIEnd", SqlDbType.Float) { Value = record.ROIEnd });
                cmd.Parameters.Add(
                    new SqlParameter("@ViewDownloadAll", SqlDbType.Bit) { Value = record.ViewDownloadAll });
                cmd.Parameters.Add(new SqlParameter("@Export", SqlDbType.Bit) { Value = record.ViewExport });
                cmd.Parameters.Add(
                    new SqlParameter("@ExportFormat", SqlDbType.BigInt)
                    {
                        Value = Convert.ToUInt64(record.ExportFormats)
                    });
                cmd.Parameters.Add(new SqlParameter("@LabDetails", SqlDbType.NVarChar, 50)
                {
                    Value = record.LabDetails ?? ""
                });
                cmd.Parameters.Add(
                    new SqlParameter("@UseLabDetails", SqlDbType.Bit) { Value = record.UseLabratoryDetails });
                cmd.Parameters.Add(new SqlParameter("@CustomerDetails", SqlDbType.NVarChar, 50)
                {
                    Value = record.CustomerDetails ?? ""
                });
                cmd.Parameters.Add(
                    new SqlParameter("@UseCustomerDetails", SqlDbType.Bit) { Value = record.UseCustomerDetails });
                cmd.Parameters.Add(new SqlParameter("@ExtraProperties", SqlDbType.NVarChar, -1)
                {
                    Value = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(
                        record.ExtraProperties.ToArray())
                });
                cmd.Parameters.Add(new SqlParameter("@AllowMissingSensors", SqlDbType.Bit) { Value = record.AllowMissingSensors });
                cmd.Parameters.Add(new SqlParameter("@AllowSensorIdToBlankChannel", SqlDbType.Bit)
                {
                    Value = record.AllowSensorIdToBlankChannel
                });
                cmd.Parameters.Add(new SqlParameter("@CalibrationBehavior", SqlDbType.SmallInt) { Value = (int)record.CalibrationBehavior });
                cmd.Parameters.Add(new SqlParameter("@LocalOnly", SqlDbType.Bit) { Value = record.LocalOnly });
                cmd.Parameters.Add(new SqlParameter("@LastModified", SqlDbType.DateTime) { Value = record.LastModified });
                cmd.Parameters.Add(new SqlParameter("@LastModifiedBy", SqlDbType.NVarChar, 50) { Value = record.LastModifiedBy });
                cmd.Parameters.Add(new SqlParameter("@TurnOffExcitation", SqlDbType.Bit) { Value = record.TurnOffExcitation });
                cmd.Parameters.Add(new SqlParameter("@TriggerCheckRealtime", SqlDbType.Bit)
                {
                    Value = record.TriggerCheckRealtime
                });
                cmd.Parameters.Add(new SqlParameter("@TriggerCheckStep", SqlDbType.Bit) { Value = record.TriggerCheckStep });
                cmd.Parameters.Add(new SqlParameter("@PostTestDiagnostics", SqlDbType.Int)
                {
                    Value = record.PostTestDiagnosticsLevel
                });
                cmd.Parameters.Add(new SqlParameter("@MeasureSquibResistancesStep", SqlDbType.Bit) { Value = record.MeasureSquibResistancesStep });
                cmd.Parameters.Add(new SqlParameter("@ExportFolder", SqlDbType.NVarChar, 150)
                {
                    Value = ""
                });
                cmd.Parameters.Add(
                    new SqlParameter("@DownloadFolder", SqlDbType.NVarChar, 150)
                    {
                        Value = ""
                    });
                cmd.Parameters.Add(
                    new SqlParameter("@CommonStatusLine", SqlDbType.Bit) { Value = record.CommonStatusLine });
                cmd.Parameters.Add(
                    new SqlParameter("@SameAsDownloadFolder", SqlDbType.Bit)
                    {
                        Value = record.SameAsDownloadFolder
                    });
                cmd.Parameters.Add(new SqlParameter("@UploadData", SqlDbType.Bit) { Value = record.UploadData });
                cmd.Parameters.Add(
                    new SqlParameter("@UploadDataFolder", SqlDbType.NVarChar, 150) { Value = record.UploadFolder ?? "" });
                cmd.Parameters.Add(
                    new SqlParameter("@UploadExportsOnly", SqlDbType.Bit) { Value = record.UploadExportsOnly });
                cmd.Parameters.Add(
                    new SqlParameter("@Settings", SqlDbType.NVarChar, 4000) { Value = record.Settings ?? "" });
                cmd.Parameters.Add(
                    new SqlParameter("@WarnOnBatteryFail", SqlDbType.Bit) { Value = record.WarnOnFailedBattery });
                cmd.Parameters.Add(new SqlParameter("@Dirty", SqlDbType.Bit) { Value = record.Dirty });
                cmd.Parameters.Add(new SqlParameter("@Complete", SqlDbType.Bit) { Value = record.IsComplete });
                cmd.Parameters.Add(
                    new SqlParameter("@Error", SqlDbType.NVarChar, 255) { Value = record.ErrorMessage });
                cmd.Parameters.Add(new SqlParameter("@TestEngineerDetails", SqlDbType.NVarChar, 50)
                {
                    Value = record.TestEngineerDetails ?? ""
                });
                cmd.Parameters.Add(
                    new SqlParameter("@UseTestEngineerDetails", SqlDbType.Bit)
                    {
                        Value = record.UseTestEngineerDetails
                    });
                cmd.Parameters.Add(
                    new SqlParameter("@UserTags", SqlDbType.VarBinary) { Value = record.TagsBlobBytes });
                cmd.Parameters.Add(new SqlParameter("@DoAutoArm", SqlDbType.Bit) { Value = record.DoAutoArm });
                cmd.Parameters.Add(new SqlParameter("@DoStreaming", SqlDbType.Bit) { Value = record.DoStreaming });
                if (DTS.Common.Constants.OnePPSOutProfiles.Contains(record.ClockSyncProfileMaster) && storedProcedureVersionToUse < DTS.Common.Constants.ONEPPS_OUT_DB_VERSION)
                {
                    record.ClockSyncProfileMaster = DTS.Common.ClockSyncProfile.None;
                }
                cmd.Parameters.Add(new SqlParameter("@ClockSyncProfileMaster", SqlDbType.NVarChar, 50) { Value = record.ClockSyncProfileMaster.ToString() });
                if (DTS.Common.Constants.OnePPSOutProfiles.Contains(record.ClockSyncProfileSlave) && storedProcedureVersionToUse < DTS.Common.Constants.ONEPPS_OUT_DB_VERSION)
                {
                    record.ClockSyncProfileSlave = DTS.Common.ClockSyncProfile.None;
                }
                cmd.Parameters.Add(new SqlParameter("@ClockSyncProfileSlave", SqlDbType.NVarChar, 50) { Value = record.ClockSyncProfileSlave.ToString() });
                cmd.Parameters.Add(
                    new SqlParameter("@CheckoutMode", SqlDbType.Bit) { Value = record.CheckoutMode });
                cmd.Parameters.Add(
                    new SqlParameter("@ISFFile", SqlDbType.NVarChar, 4000) { Value = record.ISFFile ?? "" });
                cmd.Parameters.Add(
                    new SqlParameter("@QuitTestWithoutWarning", SqlDbType.Bit)
                    {
                        Value = record.QuitTestWithoutWarning
                    });
                cmd.Parameters.Add(
                    new SqlParameter("@NotAllChannelsRealTime", SqlDbType.Bit)
                    {
                        Value = record.NotAllChannelsRealTime
                    });
                cmd.Parameters.Add(
                    new SqlParameter("@NotAllChannelsViewer", SqlDbType.Bit)
                    {
                        Value = record.NotAllChannelsViewer
                    });
                cmd.Parameters.Add(
                    new SqlParameter("@SuppressMissingSensorsWarning", SqlDbType.Bit)
                    {
                        Value = record.SuppressMissingSensorsWarning
                    });
                //If we got here without either maintaining or generating a new TestSetupUniqueId, generate one here
                if (record.TestSetupUniqueId == null)
                {
                    record.TestSetupUniqueId = Guid.NewGuid().ToString();
                }
                cmd.Parameters.Add(new SqlParameter("@TestSetupUniqueId", SqlDbType.NVarChar, 4000) { Value = record.TestSetupUniqueId });
                cmd.Parameters.Add(
                    new SqlParameter("@TestSetup", SqlDbType.VarBinary) { Value = new byte[0] });

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

                cmd.Parameters.Add(new SqlParameter("@WakeupTrigger", SqlDbType.SmallInt) { Value = (int)record.WakeUpTrigger });

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

                cmd.Parameters.Add(new SqlParameter("@TimedIntervalFrequency", SqlDbType.Int) { Value = Convert.ToInt32(record.IntervalBetweenEventStartsMinutes) });

                if (DateTime.MinValue.Equals(record.RTCScheduleStartDateTime))
                {
                    cmd.Parameters.Add(new SqlParameter("@RTCScheduleStartDateTime", SqlDbType.DateTime) { Value = DBNull.Value });
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@RTCScheduleStartDateTime", SqlDbType.DateTime) { Value = record.RTCScheduleStartDateTime });
                }

                cmd.Parameters.Add(new SqlParameter("@RTCScheduleDuration", SqlDbType.BigInt) { Value = record.RTCScheduleDuration.Ticks });

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

                if (storedProcedureVersionToUse >= DTS.Common.Constants.EnableRepeat_DB_VERSION)
                {
                    //Since we're not calling the older version (91) that doesn't have this parameter, include it
                    cmd.Parameters.Add(new SqlParameter("@RepeatAutoArmOrStreaming", SqlDbType.Bit) { Value = record.DoEnableRepeat });
                }
                if (storedProcedureVersionToUse >= DTS.Common.Constants.ALIGNUDPTOPPS_DB_VERSION)
                {
                    cmd.Parameters.Add(new SqlParameter("@AlignUDPToPPS", SqlDbType.Bit) { Value = record.AlignUDPToPPS });
                }


                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);

                #endregion params

                cmd.ExecuteNonQuery();
                if (int.Parse(errorNumberParam.Value.ToString()) != 0)
                {
                    throw new Exception((string)errorMessageParam.Value);
                }
                record.Id = int.Parse(newIdParam.Value.ToString());
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"sp_TestSetupsUpdateInsert failed: {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// Retrieves all test setups which match given search criteria
        /// </summary>
        /// <param name="user">user making query</param>
        /// <param name="connection">connection over which query is to be made</param>
        /// <param name="clientDbVersion">the db version of the calling client</param>
        /// <param name="testSetupId">database id of test setup (can be null)</param>
        /// <param name="testSetupName">name of test setup (can be null or empty)</param>
        /// <param name="defaultROIEnd">ROI end period (used to populate older test setups that
        /// are missing explicit ROI start/end periods for multiple events)</param>
        /// <param name="defaultROIStart">ROI start period (used to populate older test setups
        /// that are missing explicit ROI start/end periods for multiple events)</param>
        /// <param name="defaultIgnoreShortedStart">whether or not to ignore a shorted start
        /// since we may be using a Version 91 database that doesn't store this</param>
        /// <param name="defaultIgnoreShortedTrigger">whether or not to ignore a shorted trigger
        /// since we may be using a Version 91 database that doesn't store this</param>
        /// <param name="records">matching records</param>
        /// <param name="errors">any errors encountered while retrieving test setups</param>
        /// <returns>0 (ERROR_SUCCESS) on success, all other values are errors</returns>
        public ulong TestSetupsGet(IUserDbRecord user,
            IConnectionDetails connection,
            int clientDbVersion,
            int? testSetupId,
            string testSetupName,
            double defaultROIStart,
            double defaultROIEnd,
            bool defaultIgnoreShortedStart,
            bool defaultIgnoreShortedTrigger,
            out ITestSetupRecord[] records,
            out string[] errors)
        {
            errors = new string[0];
            records = new ITestSetupRecord[0];
            var storedProcedureVersionToUse = 0;
            SqlCommand cmd;

            var ret = PrepareForDbAccess(user, connection, clientDbVersion,
                "sp_TestSetupsGet", out storedProcedureVersionToUse, out cmd);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                if (null == testSetupId)
                {
                    cmd.Parameters.Add(new SqlParameter("@TestSetupId", SqlDbType.Int) { Value = null });
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@TestSetupId", SqlDbType.Int) { Value = (int)testSetupId });
                }
                cmd.Parameters.Add(new SqlParameter("@TestSetupName", SqlDbType.NVarChar, 50) { Value = testSetupName });

                var reader = cmd.ExecuteReader();
                var list = new List<ITestSetupRecord>();
                var lErrors = new List<string>();
                while (reader.Read())
                {
                    try
                    {
                        list.Add(new TestSetupRecord(reader, defaultROIStart, defaultROIEnd, defaultIgnoreShortedStart, defaultIgnoreShortedTrigger, storedProcedureVersionToUse, out var recordErrors));
                        if (null != recordErrors && recordErrors.Any())
                        {
                            lErrors.AddRange(recordErrors);
                            errors = lErrors.ToArray();
                        }
                    }
                    catch (Exception ex)
                    {
                        lErrors.Add($"{DTS.Common.Strings.Strings.FailedToReadTestSetup} - {ex.Message}");
                        errors = lErrors.ToArray();
                    }
                }
                records = list.ToArray();

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"sp_TestSetupsGet failed: {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
    }
}