DP44/DataPRO/DbAPI/TestSetups/RegionsOfInterest.cs


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

namespace DbAPI.TestSetups
{
    /// <summary>
    /// Handles RegionsOfInterest functions
    /// </summary>
    internal class RegionsOfInterest : IRegionsOfInterest
    {
        /// <summary>
        /// Removes records from the TestSetupROIs and ROIPeriodChannels tables
        /// </summary>
        /// <param name="user"></param>
        /// <param name="connection"></param>
        /// <param name="testSetupId">The value that matches the Primary key of the TestSetups table</param>
        /// <returns></returns>
        public ulong RegionsOfInterestDelete(IUserDbRecord user,
            IConnectionDetails connection,
            int testSetupId)
        {
            return TestSetupROIsDelete(user, connection, testSetupId);
        }
        /// <summary>
        /// Inserts records into the TestSetupROIs and ROIPeriodChannels tables
        /// </summary>
        /// <param name="user"></param>
        /// <param name="connection"></param>
        /// <param name="testSetupId">The value that matches the Primary key of the TestSetups table</param>
        /// <param name="regionOfInterest">The class that is split between the TestSetupROIs and ROIPeriodChannels tables</param>
        /// <returns></returns>
        public ulong RegionsOfInterestInsert(IUserDbRecord user,
            IConnectionDetails connection,
            int clientDbVersion,
            int testSetupId,
            DTS.Common.Interface.RegionOfInterest.IRegionOfInterest regionOfInterest)
        {
            var hResult = TestSetupROIsInsert(user, connection, testSetupId, regionOfInterest, out int testSetupROIId);
            if (hResult == ErrorCodes.ERROR_SUCCESS && testSetupROIId > 0)
            {
                var channelIndex = 0;
                foreach (var channelId in regionOfInterest.ChannelIds)
                {
                    if( channelIndex >= regionOfInterest.ChannelNames.Length) { continue; }
                    var channelName = regionOfInterest.ChannelNames[channelIndex];
                    hResult = ROIPeriodChannelsInsert(user, connection, clientDbVersion, testSetupROIId, channelName, channelId);
                    if (hResult != ErrorCodes.ERROR_SUCCESS)
                    {
                        return hResult;
                    }
                    channelIndex++;
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            else
            {
                return ErrorCodes.ERROR_UNKNOWN;
            }
        }
        /// <summary>
        /// Gets records from the TestSetupROIs and ROIPeriodChannels tables
        /// </summary>
        /// <param name="user"></param>
        /// <param name="connection"></param>
        /// <param name="testSetupId">The value that matches the Primary key of the TestSetups table</param>
        /// <param name="records">The array of records combined from the TetSetupROIs and ROIPeriodChannels tables</param>
        /// <returns></returns>
        public ulong RegionsOfInterestGet(IUserDbRecord user,
            IConnectionDetails connection,
            int clientDbVersion,
            int testSetupId,
            out DTS.Common.Interface.RegionOfInterest.IRegionOfInterest[] records)
        {
            records = new DTS.Common.Interface.RegionOfInterest.IRegionOfInterest[0];
            var list = new List<DTS.Common.Interface.RegionOfInterest.IRegionOfInterest>();

            TestSetupROIsGet(user, connection, testSetupId, out var testSetupROIRecords);
            foreach (var testSetupROIRecord in testSetupROIRecords)
            {
                ROIPeriodChannelsGet(user, connection, clientDbVersion, testSetupROIRecord.TestSetupROIId, out var roiPeriodChannelRecords);
                var channelNameList = new List<string>();
                var channelIdList = new List<long>();
                foreach (var roiPeriodChannelRecord in roiPeriodChannelRecords)
                {
                    var serialNumberIndex = roiPeriodChannelRecord.ChannelName.LastIndexOf("\\");
                    if (serialNumberIndex > -1)
                    {
                        var serialNumber = roiPeriodChannelRecord.ChannelName.Substring(serialNumberIndex + 1);
                        var hardwareChannelName = roiPeriodChannelRecord.ChannelName.Substring(0, serialNumberIndex);
                        var newChannelName = RegionOfInterest.GetChanName(serialNumber, hardwareChannelName);
                        channelNameList.Add(newChannelName);
                    }
                    else
                    {
                        channelNameList.Add(roiPeriodChannelRecord.ChannelName);
                    }
                    channelIdList.Add(roiPeriodChannelRecord.ChannelId);
                }

                list.Add(new RegionOfInterest()
                {
                    Suffix = testSetupROIRecord.Suffix,
                    Start = testSetupROIRecord.ROIStart,
                    End = testSetupROIRecord.ROIEnd,
                    IsEnabled = testSetupROIRecord.IsEnabled,
                    IsDefault = testSetupROIRecord.IsDefault,
                    ChannelNames = channelNameList.ToArray(),
                    ChannelIds = channelIdList.ToArray()
                });
            }
            records = list.ToArray();
            return ErrorCodes.ERROR_SUCCESS;
        }
        /// <summary>
        /// Removes records from the TestSetupROIs and ROIPeriodChannels tables
        /// </summary>
        /// <param name="user"></param>
        /// <param name="connection"></param>
        /// <param name="testSetupId">The value that matches the Primary key of the TestSetups table</param>
        /// <returns></returns>
        public ulong TestSetupROIsDelete(IUserDbRecord user,
            IConnectionDetails connection,
            int testSetupId)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_TestSetupROIsDelete");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }

            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                #region params

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

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

                #endregion params

                cmd.ExecuteNonQuery();
                if (!DBNull.Value.Equals(errorNumber.Value) && 0 != Convert.ToInt32(errorNumber.Value))
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.RegionsOfInterest, $"sp_TestSetupROIsDelete failed: {errorNumber.Value} : {errorMessage.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.RegionsOfInterest, $"sp_TestSetupROIsDelete failed: {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// Inserts records into the TestSetupROIs table
        /// </summary>
        /// <param name="user"></param>
        /// <param name="connection"></param>
        /// <param name="testSetupId">The value that matches the Primary key of the TestSetups table</param>
        /// <param name="regionOfInterest">The class that will have a portion stored in the TestSetupROIs table</param>
        /// <param name="testSetupROIId">The new value of the Primary key of the TestSetupROIs table</param>
        /// <returns></returns>
        public ulong TestSetupROIsInsert(IUserDbRecord user,
            IConnectionDetails connection,
            int testSetupId,
            DTS.Common.Interface.RegionOfInterest.IRegionOfInterest regionOfInterest,
            out int testSetupROIId)
        {
            testSetupROIId = 0;
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (null == regionOfInterest)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_TestSetupROIsInsert");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                #region params
                cmd.Parameters.Add(new SqlParameter("@TestSetupId", SqlDbType.Int) { Value = testSetupId });
                cmd.Parameters.Add(new SqlParameter("@Suffix", SqlDbType.NVarChar, 50) { Value = regionOfInterest.Suffix });
                cmd.Parameters.Add(new SqlParameter("@ROIStart", SqlDbType.Float) { Value = regionOfInterest.Start });
                cmd.Parameters.Add(new SqlParameter("@ROIEnd", SqlDbType.Float) { Value = regionOfInterest.End });
                cmd.Parameters.Add(new SqlParameter("@IsEnabled", SqlDbType.Bit) { Value = regionOfInterest.IsEnabled });
                cmd.Parameters.Add(new SqlParameter("@IsDefault", SqlDbType.Bit) { Value = regionOfInterest.IsDefault });

                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)
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.RegionsOfInterest, errorMessageParam.Value.ToString());
                }
                else
                {
                    //Return the new id so that it can be used to enter this ROI period's channels into the ROIPeriodChannels table
                    testSetupROIId = int.Parse(newIdParam.Value.ToString());
                }
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.RegionsOfInterest, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
            return ErrorCodes.ERROR_SUCCESS;
        }
        /// <summary>
        /// Gets records from the TestSetupROIs table
        /// </summary>
        /// <param name="user"></param>
        /// <param name="connection"></param>
        /// <param name="testSetupId">The value that matches the Primary key of the TestSetups table</param>
        /// <param name="records">The array of records from the TestSetupROIs table</param>
        /// <returns></returns>
        public ulong TestSetupROIsGet(IUserDbRecord user,
            IConnectionDetails connection,
            int testSetupId,
            out ITestSetupROIRecord[] records)
        {
            records = new ITestSetupROIRecord[0];

            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_TestSetupROIsGet");
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

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

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

                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.RegionsOfInterest, $"sp_TestSetupROIsGet failed: {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }
        /// <summary>
        /// Inserts records into the ROIPeriodChannels table
        /// </summary>
        /// <param name="user"></param>
        /// <param name="connection"></param>
        /// <param name="testSetupROIId">The value of the Primary key of the TestSetupROIs table</param>
        /// <param name="channelName">The name of a channel to be stored in the ROIPeriodChannels table</param>
        /// <param name="channelId">The id of a channel to be stored in the ROIPeriodChannels table</param>
        /// <returns></returns>
        public ulong ROIPeriodChannelsInsert(IUserDbRecord user,
            IConnectionDetails connection,
            int clientDbVersion,
            int testSetupROIId,
            string channelName,
            long channelId)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            if (testSetupROIId == 0 || null == channelName)
            {
                return ErrorCodes.ERROR_MISSING_PARAMETER;
            }
            SqlCommand cmd;
            var storedProcedureVersionToUse = 0;
            var ret = Database.Database.PrepareForDbAccess(user, connection, clientDbVersion,
                "sp_ROIPeriodChannelsInsert", out storedProcedureVersionToUse, out cmd);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                #region params
                cmd.Parameters.Add(new SqlParameter("@TestSetupROIId", SqlDbType.Int) { Value = testSetupROIId });
                cmd.Parameters.Add(new SqlParameter("@ChannelName", SqlDbType.NVarChar, 4000) { Value = channelName });
                if (storedProcedureVersionToUse >= Constants.ROIPERIODCHANNELS_CHANNELID_DB_VERSION)
                {
                    cmd.Parameters.Add(new SqlParameter("@ChannelId", SqlDbType.BigInt) { Value = channelId });
                }

                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)
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.RegionsOfInterest, errorMessageParam.Value.ToString());
                }
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.RegionsOfInterest, ex.Message);
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
            return ErrorCodes.ERROR_SUCCESS;
        }
        /// <summary>
        /// Gets records from the ROIPeriodChannels table
        /// </summary>
        /// <param name="user"></param>
        /// <param name="connection"></param>
        /// <param name="testSetupROIId">The value of the Primary key of the TestSetupROIs table</param>
        /// <param name="roiPeriodChannelRecords">The array of records from the ROIPeriodChannels table</param>
        /// <returns></returns>
        public ulong ROIPeriodChannelsGet(IUserDbRecord user,
            IConnectionDetails connection,
            int clientDbVersion,
            int testSetupROIId,
            out IROIPeriodChannelRecord[] roiPeriodChannelRecords)
        {
            roiPeriodChannelRecords = new IROIPeriodChannelRecord[0];

            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            SqlCommand cmd;
            var storedProcedureVersionToUse = 0;
            var ret = Database.Database.PrepareForDbAccess(user, connection, clientDbVersion,
                "sp_ROIPeriodChannelsGet", out storedProcedureVersionToUse, out cmd);
            if (ret != ErrorCodes.ERROR_SUCCESS) { return ret; }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

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

                var reader = cmd.ExecuteReader();
                var list = new List<ROIPeriodChannelRecord>();
                while (reader.Read())
                {
                    var newROIPeriodChannelRecord = new ROIPeriodChannelRecord(reader, storedProcedureVersionToUse);
                    var cleanROIPeriodChannelName = RegionOfInterest.RemoveAssignedByIDFromHardwareString(newROIPeriodChannelRecord.ChannelName);
                    cleanROIPeriodChannelName = RegionOfInterest.RemoveParentDASName(cleanROIPeriodChannelName);
                    newROIPeriodChannelRecord.ChannelName = cleanROIPeriodChannelName;
                    list.Add(newROIPeriodChannelRecord);
                }
                roiPeriodChannelRecords = list.ToArray();

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