using C4IT.FASD.Base;
using C4IT.FileImport;
using C4IT.Logging;
using C4IT.Nexthink.NXQL;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using static C4IT.DataHistoryProvider.cNxqlDailyCollector;
using static C4IT.Logging.cLogManager;

namespace C4IT.DataHistoryProvider
{
    public class cDataHistoryCollectorNxql : cDataHistoryCollectorModule
    {
        public const string constConnectorName = "Nexthink NXQL conntector";
        public const string constLicenseId = "51EA7764-3AD3-4F90-89DB-DEB0C60D655C";

        public cNxqlColumnsValidationResult LastValidationResults = null;

        public cDataHistoryCollectorNxql(cDataHistoryCollector Collector) : base(Collector, enumDataHistoryOrigin.NexthinkNxql, constConnectorName, constLicenseId)
        {
        }

        public static bool IsNxqlColumn(cDataHistoryConfigColumnBase Entry)
        {
            if (Entry is cDataHistoryConfigColumnNxtAction)
                return true;
            if (Entry is cDataHistoryConfigColumnNxtCampaign)
                return true;
            if (Entry is cDataHistoryConfigColumnNxtCategory)
                return true;
            if (Entry is cDataHistoryConfigColumnNxtScore)
                return true;
            if (Entry is cDataHistoryConfigColumnNxtCompute)
                return true;
            if (Entry is cDataHistoryConfigColumn)
                return true;
            return false;
        }

        private static string GetTimeFilter(DateTime? time, bool isLeft)
        {
            if (time == null)
                return "now";

            var time2 = (DateTime)time;
            time2 = time2.AddTicks(-(time2.Ticks % 10000000));

            if (isLeft)
                time2.AddSeconds(-1);

            string strFrom = string.Format("{0:yyyy-MM-dd HH:mm:ss}", time2);
            strFrom = strFrom.Replace(' ', '@');
            return strFrom;
        }

        private static string GetTimeFilter(DateTime? From, DateTime? To)
        {
            var strFrom = GetTimeFilter(From, true);
            var strTo = GetTimeFilter(To, false);
            return string.Format("(between {0} {1})", strFrom, strTo);
        }

        private static string GetNxqlColumnName(string Column, bool isCustom)
        {
            var RV = Column;
            if (isCustom)
            {
                RV = Column.Replace("\"", "\"\"");
                RV = $"#\"{RV}\"";
            }
            return RV;
        }

        public static string GetNxqlColumnName(cDataHistoryConfigColumnBase Entry)
        {
            var strCol = GetNxqlColumnName2(Entry, true);
            if (!string.IsNullOrEmpty(Entry.SourceTable))
                strCol = $"({Entry.SourceTable} {strCol})";

            return strCol;
        }

        public static string GetNxqlColumnResultName(cDataHistoryConfigColumnBase Entry)
        {
            var strCol = GetNxqlColumnName2(Entry, false).ToLowerInvariant();
            if (strCol.StartsWith("*"))
                strCol = strCol.Remove(0, 1);
            if (!string.IsNullOrEmpty(Entry.SourceTable))
                strCol = $"{Entry.SourceTable.ToLowerInvariant()}/{strCol}";

            return strCol;
        }

        private static string GetNxqlColumnName2(cDataHistoryConfigColumnBase Entry, bool withCustom)
        {
            if (Entry is cDataHistoryConfigColumnNxtAction _ac)
            {
                return GetNxqlColumnName($"action:{_ac.ActionName}/{_ac.ValueName}", withCustom);
            }
            else if (Entry is cDataHistoryConfigColumnNxtScore _sc)
            {
                return GetNxqlColumnName($"score:{_sc.ScoreName}/{_sc.ValueName}{(_sc.IsPayload ? "/payload" : "")}", withCustom);
            }
            else if (Entry is cDataHistoryConfigColumnNxtCategory _ca)
            {
                return GetNxqlColumnName(_ca.SourceName, withCustom);
            }
            else if (Entry is cDataHistoryConfigColumnNxtCampaign _cn)
            {
                var strIsText = "";
                if (_cn.AsText)
                    strIsText = " (text)";
                return GetNxqlColumnName($"campaign:{_cn.CampaignName}/{_cn.ValueName}{strIsText}", withCustom);
            }
            else if (Entry is cDataHistoryConfigColumnNxtCompute _cp)
            {
                return GetNxqlColumnName(_cp.SourceName, false);
            }
            else if (Entry is cDataHistoryConfigColumn _co)
            {
                return GetNxqlColumnName(_co.SourceName, false);
            }
            return null;
        }

        public static string getNxqlStatement(string NxqlQuery, enumFasdInformationClass Class, List<object> KeyValues, List<cDataHistoryConfigColumnBase> KeyColumns, uint Limit, DateTime? From, DateTime? To, List<cDataHistoryConfigColumnBase> Columns, string TableName)
        {
            try
            {
                var strLimit = "";
                if (Limit >= 0)
                    strLimit = $"(limit {Limit})";

                var strTimeFilter = GetTimeFilter(From, To);

                var strKeyFilter = "";
                if (KeyColumns != null && KeyValues != null && KeyValues.Count > 0)
                {
                    var keyVal = Convert.ToInt64(KeyValues[0]);
                    var tbn = "device";
                    if (KeyColumns[0]?.ParentTable?.ParentTable?.ParentCluster?.InformationClass == enumFasdInformationClass.User)
                        tbn = "user";
                    strKeyFilter = $"(where {tbn} (eq id (identifier \"{keyVal}\")))";
                }

                var strColumns = "";
                var strCompus = "";
                var cols = new HashSet<string>();
                foreach (var Entry in Columns)
                {
                    if (Entry is cDataHistoryConfigColumn _col && _col.SourceType == eDataHistoryQueryType.Static)
                        continue;

                    string strCol = GetNxqlColumnName(Entry);
                    if (strCol == null)
                        continue;
                    if (cols.Contains(strCol))
                        continue;
                    cols.Add(strCol);
                    if (Entry is cDataHistoryConfigColumnNxtCompute)
                    {
                        if (strCompus != "")
                            strCompus += " ";
                        strCompus += strCol;
                    }
                    else
                    {
                        if (strColumns != "")
                            strColumns += " ";
                        strColumns += strCol;
                    }
                }

                if (strColumns == "")
                {
                    cDataHistoryConfigColumnBase firstCol = null;
                    if (KeyColumns != null)
                        foreach (var Col in KeyColumns)
                        {
                            if (Col is cDataHistoryConfigColumn _c && _c.SourceType == eDataHistoryQueryType.Static)
                                continue;
                            firstCol = Col;
                            break;
                        }

                    if (firstCol == null)
                    {
                        LogEntry($"The NXQL query with table name {TableName} has no valid columns nor a valid key column", LogLevels.Warning);
                        return null;
                    }

                    strColumns = GetNxqlColumnName(firstCol);
                }
                strColumns = "(" + strColumns + ")";
                if (!string.IsNullOrEmpty(strCompus))
                    strCompus = $"(compute {strCompus})";

                var RetVal = NxqlQuery.Replace("#Columns#", strColumns);
                RetVal = RetVal.Replace("#KeyFilter#", strKeyFilter);
                RetVal = RetVal.Replace("#Limit#", strLimit);
                RetVal = RetVal.Replace("#TimeFilter#", strTimeFilter);
                RetVal = RetVal.Replace("#Compute#", strCompus);

                return RetVal;
            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
            }

            return null;
        }

        public override async Task<List<object>> GetIds(cF4sdConnectorIds IdEntry, enumFasdInformationClass InfoClass, cF4sdWebRequestInfo requestInfo, int LogDeep)
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            if (cPerformanceLogger.IsActive && requestInfo != null) { if (CM == null) CM = MethodBase.GetCurrentMethod(); cPerformanceLogger.LogPerformanceStart(LogDeep, CM, requestInfo.id, requestInfo.created); }
            var _startTime = DateTime.UtcNow;

            var _retVal = new List<object>();

            try
            {
                Dictionary<string, object> Params;
                string QuerName;
                switch (InfoClass)
                {
                    case enumFasdInformationClass.Computer:
                        Params = new Dictionary<string, object>() { { "NAME", IdEntry.name.ToUpperInvariant() } };
                        QuerName = "FindNxtIdsComputer";
                        break;
                    case enumFasdInformationClass.User:
                        Params = new Dictionary<string, object>() { { "SID", IdEntry.sid } };
                        QuerName = "FindNxtIdsUser";
                        break;
                    default:
                        return _retVal;
                }

                if (!DataHistorySqlHelper.GetWellKnownSqlStatement(QuerName, out var Query))
                    return _retVal;

                var sqlerror = 0;
                var sqlStartTime = DateTime.UtcNow;


                try
                {
                    using (var Conn = new cDbConnection(Collector.mainDbConnection))
                    {
                        if (!Conn.IsOpen)
                        {
                            LogEntry($"Could not open main sql database '{Collector.mainDbConnection.Database}', aborting saving NXQL column validation result", LogLevels.Error);
                            return _retVal;
                        }

                        using (var Reader = await DataHistorySqlHelper.GetTableResultAsync(Conn, Query, Params, CancellationToken.None))
                        {
                            if (Reader == null)
                                return _retVal;

                            try
                            {
                                if (Reader.HasRows)
                                {
                                    if (await Reader.ReadAsync())
                                    {
                                        try
                                        {
                                            var Engine = Reader.GetString(0);
                                            var NxtId = Reader.GetInt64(1);

                                            return new List<object>() { Engine, NxtId };
                                        }
                                        catch (Exception E)
                                        {
                                            cLogManager.DefaultLogger.LogException(E);
                                        }
                                    }
                                }
                            }
                            catch (Exception E)
                            {
                                cLogManager.DefaultLogger.LogException(E);
                            }
                        }
                    }
                }
                catch (Exception E)
                {
                    sqlerror = E.HResult;
                    LogException(E);
                }
                finally
                {
                    if (DataHistorySqlHelper.LogSql) DataHistorySqlHelper.SaveSqlTimingEntry(Query.Name, sqlStartTime, Params, sqlerror, requestInfo?.requestName);
                }

                using (var Conn = new cDbConnection(Collector.mainDbConnection))
                {
                    if (!Conn.IsOpen)
                    {
                        LogEntry($"Could not open main sql database '{Collector.mainDbConnection.Database}', aborting saving NXQL column validation result", LogLevels.Error);
                        return _retVal;
                    }

                    using (var Reader = await DataHistorySqlHelper.GetTableResultAsync(Conn, Query, Params, CancellationToken.None))
                    {
                        if (Reader == null)
                            return _retVal;

                        try
                        {
                            if (Reader.HasRows)
                            {
                                if (await Reader.ReadAsync())
                                {
                                    try
                                    {
                                        var Engine = Reader.GetString(0);
                                        var NxtId = Reader.GetInt64(1);

                                        return new List<object>() { Engine, NxtId };
                                    }
                                    catch (Exception E)
                                    {
                                        cLogManager.DefaultLogger.LogException(E);
                                    }
                                }
                            }
                        }
                        catch (Exception E)
                        {
                            cLogManager.DefaultLogger.LogException(E);
                        }
                    }
                }
            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                if (cPerformanceLogger.IsActive && requestInfo != null) { cPerformanceLogger.LogPerformanceEnd(LogDeep, CM, requestInfo.id, requestInfo.created, _startTime); }
                if (CM != null) LogMethodEnd(CM);
            }

            return _retVal;
        }

        public override async Task<cScanTimeInfo> GetScanTimeInfoAsync(cF4sdWebRequestInfo requestInfo, int LogDeep, CancellationToken Token)
        {
            return await GetScanTimeInfoAsync(Collector.InfrastructureConfig.Nexthink.ScanTiming, "NxqlScan-all", requestInfo, LogDeep, Token);
        }

        public override void TablePostConfig(cDataHistoryConfigTable Table)
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            try
            {
                if (Table.Cached == eDataHistoryTableCached.Default)
                {
                    if (Table.Type != eDataHistoryTableType.Static)
                        Table.Cached = eDataHistoryTableCached.Yes;
                    else
                        Table.Cached = eDataHistoryTableCached.No;
                }
                Table.IsVirtual = true;
            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                if (CM != null) LogMethodEnd(CM);
            }

        }

        public override async Task<bool> DoScanAsync(bool Always, bool Rescan, cF4sdWebRequestInfo requestInfo, int LogDeep, CancellationToken Token, bool EnhancedDebug = false)
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            if (cPerformanceLogger.IsActive && requestInfo != null) { if (CM == null) CM = MethodBase.GetCurrentMethod(); cPerformanceLogger.LogPerformanceStart(LogDeep, CM, requestInfo.id, requestInfo.created); }
            var _startTime = DateTime.UtcNow;

            var ShowFinished = false;
            try
            {
                await Task.Run<bool>(async () =>
                {
                    try
                    {
                        Collector.DoProcessUiMessage(0, "");

                        var scanInfo = await GetScanTimeInfoAsync(requestInfo, LogDeep + 1, Token);
                        if (scanInfo != null)
                        {
                            if (scanInfo.NextScan == null)
                            {
                                Collector.DoProcessUiMessage(0, "not valid next scan time info for NXQL connector");
                                return false;
                            }

                            var nextScan = (DateTime)scanInfo.NextScan;
                            if (!(scanInfo.LastScan is DateTime lastScan) || (lastScan >= nextScan))
                            {
                                Collector.DoProcessUiMessage(0, $"currently no scan needed for NXQL connector, lastScan={scanInfo.LastScan} (UTC), nextScan={scanInfo.NextScan} (UTC)");
                                return false;
                            }
                        }
                        else
                        {
                            if (!Always)
                            {
                                Collector.DoProcessUiMessage(0, "no valid NXQL scan done yet, please start a scan manually.");
                                return false;
                            }

                            scanInfo = new cScanTimeInfo(DateTime.MinValue);
                            scanInfo.GetFirstScanTime(Collector.InfrastructureConfig.Nexthink.ScanTiming);
                        }

                        ShowFinished = true;
                        Collector.DoProcessUiMessage(0, "Starting NXQL scan...");
                        Collector.DoProcessUiMessage(0, "");

                        if (LastValidationResults == null)
                        {
                            Collector.DoProcessUiMessage(0, "Starting NXQL column validation...");
                            Collector.DoProcessUiMessage(0, "");
                            var ColumnsValidator = new cNxqlColumnValidator(Collector);
                            await ColumnsValidator.DoValidationAsync(requestInfo, LogDeep + 1, Token).ConfigureAwait(false);
                            LastValidationResults = ColumnsValidator.ValidationResults;
                            Collector.DoProcessUiMessage(0, "NXQL column validation finished");
                            Collector.DoProcessUiMessage(0, "");
                        }
                        if (LastValidationResults == null)
                            return false;

                        foreach (var ScanInterval in scanInfo.Intervals)
                        {
                            if (Token.IsCancellationRequested)
                                return false;

                            await DoScanIntervalAsync("NxqlScan-all", ScanInterval.Key, ScanInterval.Value, requestInfo, LogDeep + 1, Token, EnhancedDebug);
                        }

                    }
                    catch (Exception E)
                    {
                        LogException(E);
                    }

                    return false;
                });

            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                Collector.DoProcessUiMessage(0, "");
                if (ShowFinished)
                    Collector.DoProcessUiMessage(0, "NXQL scan finished.");
                if (cPerformanceLogger.IsActive && requestInfo != null) { cPerformanceLogger.LogPerformanceEnd(LogDeep, CM, requestInfo.id, requestInfo.created, _startTime); }
                if (CM != null) LogMethodEnd(CM);
            }

            return false;
        }

        public async Task<bool> DoScanIntervalAsync(string ScanName, DateTime FromTime, DateTime ToTime, cF4sdWebRequestInfo requestInfo, int LogDeep, CancellationToken Token, bool EnhancedDebug)
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            if (cPerformanceLogger.IsActive && requestInfo != null) { if (CM == null) CM = MethodBase.GetCurrentMethod(); cPerformanceLogger.LogPerformanceStart(LogDeep, CM, requestInfo.id, requestInfo.created); }
            var _startTime = DateTime.UtcNow;

            try
            {
                // create the scan history entry and get the id
                var ScanId = await Collector.CreateScanHistoryEntry(ScanName, FromTime, ToTime, requestInfo, LogDeep + 1, Token);

                if (Token.IsCancellationRequested)
                    return false;

                var Validator = new cNxqlDataCollector(Collector, LastValidationResults, ScanId, EnhancedDebug);
                Validator.CreateJobs(FromTime, ToTime, requestInfo, LogDeep + 1);

                await Validator.Orchestrator.ProcessAsync(CancellationToken.None).ConfigureAwait(false);

                if (Token.IsCancellationRequested)
                    return false;

                var _sum = Validator.validResultCount + Validator.validResultCount;
                if (_sum > 0 && (Validator.errorResultCount * 1000 / _sum) > 100)
                {
                    LogEntry($"To many invalid NXQL scan to complete scan (valid: {Validator.validResultCount}, errors: {Validator.validResultCount}.", LogLevels.Error);
                    return false;
                }

                await Collector.ConfirmScanHistoryEntry(ScanId, requestInfo, LogDeep + 1, Token);

                return true;
            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                if (cPerformanceLogger.IsActive && requestInfo != null) { cPerformanceLogger.LogPerformanceEnd(LogDeep, CM, requestInfo.id, requestInfo.created, _startTime); }
                if (CM != null) LogMethodEnd(CM);
            }

            return false;

        }

        public override bool CheckIfLateDelivery(cDataHistoryConfigTable Table)
        {
            return Table.Cached == eDataHistoryTableCached.Yes;
        }

        private async Task GetTableValuesSqlAsync(List<Task<List<cF4SDHealthCardRawData.cHealthCardTable>>> lstTasks, Dictionary<string, cF4SDHealthCardRawData.cHealthCardTable> RetVal, cF4sdWebRequestInfo requestInfo, int LogDeep)
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            if (cPerformanceLogger.IsActive && requestInfo != null) { if (CM == null) CM = MethodBase.GetCurrentMethod(); cPerformanceLogger.LogPerformanceStart(LogDeep, CM, requestInfo.id, requestInfo.created); }
            var _startTime = DateTime.UtcNow;

            try
            {
                var Result = await Task.WhenAll(lstTasks).ConfigureAwait(false);
                if (Result != null)
                {
                    LogEntry($"SQL task result for NXQL tables: {Result.Length}", LogLevels.Debug);
                    foreach (var Entry in Result)
                    {
                        if (Entry != null)
                        {
                            if (Entry.Count == 0)
                                LogEntry($"No table result for SQL task result for NXQL tables.", LogLevels.Debug);
                            foreach (var Entry2 in Entry)
                                RetVal[Entry2.Name] = Entry2;
                        }
                        else
                            LogEntry($"Empty result in SQL task result for NXQL tables.", LogLevels.Warning);
                    }
                }
                else
                    LogEntry($"Could not get a valid SQL result for the NXQL tables", LogLevels.Warning);
            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                if (cPerformanceLogger.IsActive && requestInfo != null) { cPerformanceLogger.LogPerformanceEnd(LogDeep, CM, requestInfo.id, requestInfo.created, _startTime); }
                if (CM != null) LogMethodEnd(CM);
            }

        }

        private async Task<Dictionary<string, cTableResult>> GetTableValuesNxqlAsync(List<object> ComputerIds, List<object> UserIds, List<cDataHistoryConfigTable> lstTables, DateTime TodayFrom, DateTime TodayTo, CancellationToken Token, cF4sdWebRequestInfo requestInfo, int LogDeep)
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            if (cPerformanceLogger.IsActive && requestInfo != null) { if (CM == null) CM = MethodBase.GetCurrentMethod(); cPerformanceLogger.LogPerformanceStart(LogDeep, CM, requestInfo.id, requestInfo.created); }
            var _startTime = DateTime.UtcNow;

            try
            {
                if ((ComputerIds != null && ComputerIds.Count >= 2) ||
                    (UserIds != null && UserIds.Count >= 2))
                {
                    var ColVaidation = new cNxqlColumnsValidationResult();
                    await ColVaidation.LoadFromSqlAsync(Collector, Token, requestInfo, LogDeep + 1);

                    if (ColVaidation == null || ColVaidation.Count == 0)
                        LogEntry($"No valid column validation entries found.", LogLevels.Warning);

                    var NxqlCollector = new cNxqlDailyCollector(Collector, ColVaidation);

                    if ((ComputerIds != null && ComputerIds.Count >= 2))
                        NxqlCollector.CreateJobs(lstTables, TodayFrom, TodayTo, ComputerIds[0].ToString(), Convert.ToInt64(ComputerIds[1]), enumFasdInformationClass.Computer);
                    if ((UserIds != null && UserIds.Count >= 2))
                        NxqlCollector.CreateJobs(lstTables, TodayFrom, TodayTo, UserIds[0].ToString(), Convert.ToInt64(UserIds[1]), enumFasdInformationClass.User);

                    await NxqlCollector.Orchestrator.ProcessAsync(Token);
                    return NxqlCollector.Results;
                }

            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                if (cPerformanceLogger.IsActive && requestInfo != null) { cPerformanceLogger.LogPerformanceEnd(LogDeep, CM, requestInfo.id, requestInfo.created, _startTime); }
                if (CM != null) LogMethodEnd(CM);
            }

            return null;
        }

        public override async Task<List<cF4SDHealthCardRawData.cHealthCardTable>> GetTableResultsVirtualAsync(List<cDataHistoryConfigTable> Tables, Dictionary<enumFasdInformationClass, cF4sdConnectorIds> Identities, DateTime RefTime, int MaxAge, bool instantly, Guid? CacheId, CancellationToken Token, cF4sdWebRequestInfo requestInfo, int LogDeep)
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            if (cPerformanceLogger.IsActive && requestInfo != null) { if (CM == null) CM = MethodBase.GetCurrentMethod(); cPerformanceLogger.LogPerformanceStart(LogDeep, CM, requestInfo.id, requestInfo.created); }
            var _startTime = DateTime.UtcNow;

            try
            {
                var lstNxqlTables = new List<cDataHistoryConfigTable>();
                foreach (var Table in Tables)
                {
                    if (!this.Origins.Contains(Table.ParentCluster.Origin))
                        continue;

                    lstNxqlTables.Add(Table);
                }
                if (lstNxqlTables.Count == 0)
                    return null;

                if (!instantly && CacheId == null)
                {
                    var RetValCached = new List<cF4SDHealthCardRawData.cHealthCardTable>();
                    foreach (var Table in lstNxqlTables)
                    {
                        RetValCached.Add(new cF4SDHealthCardRawData.cHealthCardTable()
                        {
                            Name = Table.Name,
                            InformationClass = Table.ParentCluster.InformationClass,
                            Origin = Table.ParentCluster.Origin,
                            IsIncomplete = true
                        }); ;
                    }

                    return RetValCached;
                }

                List<object> ComputerIds = null;
                List<object> UserIds = null;

                var lstTasks = new List<Task<List<cF4SDHealthCardRawData.cHealthCardTable>>>(Tables.Count);
                var lstTables = new List<cDataHistoryConfigTable>();

                var TodayTo = DateTime.UtcNow;
                var _sci = new cScanTimeInfo(TodayTo);
                var TodayFrom = _sci.GetScanTime(TodayTo, Collector.InfrastructureConfig.Nexthink.ScanTiming);

                LogEntry($"Todays timeframe: {TodayFrom} - {TodayTo}", LogLevels.Debug);

                foreach (var Table in lstNxqlTables)
                {
                    List<object> lstFilter;

                    switch (Table.ParentCluster.InformationClass)
                    {
                        case enumFasdInformationClass.Computer:
                            if (ComputerIds == null)
                                if (Identities.TryGetValue(enumFasdInformationClass.Computer, out var idsComputer))
                                    ComputerIds = await this.GetIds(idsComputer, enumFasdInformationClass.Computer, requestInfo, LogDeep + 1);
                            lstFilter = ComputerIds;
                            if (!lstTables.Contains(Table))
                                lstTables.Add(Table);
                            break;
                        case enumFasdInformationClass.User:
                            if (UserIds == null)
                                if (Identities.TryGetValue(enumFasdInformationClass.User, out var idsUser))
                                    UserIds = await this.GetIds(idsUser, enumFasdInformationClass.User, requestInfo, LogDeep + 1);
                            lstFilter = UserIds;
                            if (!lstTables.Contains(Table))
                                lstTables.Add(Table);
                            break;
                        default:
                            continue;
                    }

                    if (!(lstFilter?.Count >= 2))
                    {
                        LogEntry($"No valid filter properties for NXQL table '{Table?.Name}'", LogLevels.Warning);
                        continue;
                    }

                    var Filter = new Dictionary<string, object>(2) { { "engine", lstFilter[0] }, { "id_int", lstFilter[1] } };
                    lstTasks.Add(Collector.GetCachedHistoricTable(Table, Filter, RefTime, MaxAge, Collector.InfrastructureConfig.Nexthink.ScanTiming, Token, requestInfo, LogDeep + 1));
                }

                object ComputerId1 = null;
                object ComputerId2 = null;
                if (ComputerIds != null)
                {
                    ComputerId1 = ComputerIds.Count >= 1 ? ComputerIds[0] : null;
                    ComputerId2 = ComputerIds.Count >= 1 ? ComputerIds[1] : null;
                }
                LogEntry($"Computer IDs: {ComputerId1}, {ComputerId2}", LogLevels.Debug);

                object UserId1 = null;
                object UserId2 = null;
                if (UserIds != null)
                {
                    UserId1 = UserIds.Count >= 1 ? UserIds[0] : null;
                    UserId2 = UserIds.Count >= 1 ? UserIds[1] : null;
                }
                LogEntry($"User IDs: {UserId1}, {UserId2}", LogLevels.Debug);

                var RetVal = new Dictionary<string, cF4SDHealthCardRawData.cHealthCardTable>();
                Dictionary<string, cTableResult> DailyResult = null;

                var tsk1 = Task.Run(async () =>
                {
                    await GetTableValuesSqlAsync(lstTasks, RetVal, requestInfo, LogDeep + 1);
                });

                var tsk2 = Task.Run(async () =>
                {
                    DailyResult = await GetTableValuesNxqlAsync(ComputerIds, UserIds, lstTables, TodayFrom, TodayTo, Token, requestInfo, LogDeep + 1);
                });

                await Task.WhenAll(new Task[] { tsk1, tsk2 });

                if (!tsk1.IsCompleted)
                    LogEntry("The SQL task results for NXQL tables did not complete.", LogLevels.Warning);
                if (!tsk2.IsCompleted)
                    LogEntry("The todays NXQL task results for NXQL tables did not complete.", LogLevels.Warning);

                if (DailyResult == null)
                    return null;

                foreach (var DailyResultEntry in DailyResult)
                {
                    try
                    {
                        var TableName = DailyResultEntry.Key;
                        var DailyValues = DailyResultEntry.Value;

                        if (DefaultLogger.IsDebug) LogEntry($"Including daily values in NXQL result for table '{TableName}'.", LogLevels.Debug);

                        if (RetVal.TryGetValue(TableName, out var SqlValues))
                        {
                            if (DefaultLogger.IsDebug) LogEntry($"Including daily values in existing SQL result for '{TableName}'.", LogLevels.Debug);

                            if (SqlValues.StartingIndex == int.MaxValue)
                                SqlValues.StartingIndex = 1;

                            foreach (var DailyValueEntry in DailyValues)
                            {
                                if (SqlValues.Columns.TryGetValue(DailyValueEntry.Key, out var SqlValueEntry))
                                {
                                    if (SqlValues.StartingIndex > 0)
                                    {
                                        for (int i = 0; i < SqlValues.StartingIndex - 1; i++)
                                            SqlValueEntry.Values.Insert(0, null);
                                        SqlValueEntry.Values.Insert(0, DailyValueEntry.Value);
                                    }
                                }
                                else
                                {
                                    SqlValueEntry = new cF4SDHealthCardRawData.cHealthCardTableColumn(SqlValues)
                                    {
                                        ColumnName = DailyValueEntry.Key,
                                        Values = new List<object>(1) { DailyValueEntry.Value }
                                    };
                                    SqlValues.Columns.Add(DailyValueEntry.Key, SqlValueEntry);
                                }
                            }

                            foreach (var SqlValueEntry in SqlValues.Columns.Values)
                            {
                                if (DailyValues.ContainsKey(SqlValueEntry.ColumnName))
                                    continue;

                                if (SqlValues.StartingIndex > 0)
                                {
                                    for (int i = 0; i < SqlValues.StartingIndex - 1; i++)
                                        SqlValueEntry.Values.Insert(0, null);
                                    SqlValueEntry.Values.Insert(0, null);
                                }
                            }

                            if (DefaultLogger.IsDebug) LogEntry($"Including timeframe in existing SQL result for '{TableName}'.", LogLevels.Debug);
                            if (SqlValues.StartingIndex > 0 && SqlValues.TimeFrames != null)
                            {
                                try
                                {
                                    var lstTimeframes = new List<KeyValuePair<DateTime, DateTime>>
                                    {
                                        new KeyValuePair<DateTime, DateTime>(TodayFrom, TodayTo)
                                    };
                                    for (int i = 0; i < SqlValues.StartingIndex - 1; i++)
                                        lstTimeframes.Add(new KeyValuePair<DateTime, DateTime>(DateTime.MinValue, DateTime.MinValue));
                                    for (int j = 0; j < SqlValues.TimeFrames.Length / 2; j++)
                                        lstTimeframes.Add(new KeyValuePair<DateTime, DateTime>(SqlValues.TimeFrames[j, 0], SqlValues.TimeFrames[j, 1]));
                                    SqlValues.TimeFrames = new DateTime[lstTimeframes.Count, 2];
                                    for (int i = 0; i < lstTimeframes.Count; i++)
                                    {
                                        SqlValues.TimeFrames[i, 0] = lstTimeframes[i].Key;
                                        SqlValues.TimeFrames[i, 1] = lstTimeframes[i].Value;
                                    }
                                }
                                catch (Exception E)
                                {
                                    LogException(E);
                                }
                            }

                            SqlValues.StartingIndex = Math.Min(SqlValues.StartingIndex, 0);
                        }
                        else
                        {
                            if (DefaultLogger.IsDebug) LogEntry($"No valid SQL data found for NXQL results for table '{TableName}', creating a new table result with todays values only", LogLevels.Debug);
                            var TableResult = new cF4SDHealthCardRawData.cHealthCardTable()
                            {
                                Name = TableName,
                                InformationClass = DailyValues.InformationClass,
                                Origin = enumDataHistoryOrigin.NexthinkNxql,
                                IsIncomplete = true,
                                IsStatic = false,
                                TableType = eDataHistoryTableType.History,
                                StartingIndex = 0,
                                TimeFrames = new DateTime[1, 2] { { TodayFrom, TodayTo } },
                                Columns = new Dictionary<string, cF4SDHealthCardRawData.cHealthCardTableColumn>()
                            };

                            foreach (var ColEntry in DailyValues)
                            {
                                var _col = new cF4SDHealthCardRawData.cHealthCardTableColumn(TableResult)
                                {
                                    ColumnName = ColEntry.Key,
                                    Values = new List<object>(1) { ColEntry.Value }
                                };
                                TableResult.Columns[_col.ColumnName] = _col;
                            }

                            RetVal[TableName] = TableResult;
                        }
                    }
                    catch (Exception E)
                    {
                        LogException(E);
                    }
                }

                if (RetVal.Count > 0)
                    return RetVal.Values.ToList();

            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                if (cPerformanceLogger.IsActive && requestInfo != null) { cPerformanceLogger.LogPerformanceEnd(LogDeep, CM, requestInfo.id, requestInfo.created, _startTime); }
                if (CM != null) LogMethodEnd(CM);
            }

            return null;
        }

    }

    public class cNxqlColumnValidator
    {
        public readonly cDataHistoryCollector Collector;
        public readonly cNxqlOrchestration Orchestrator;

        public readonly cNxqlColumnsValidationResult ValidationResults = new cNxqlColumnsValidationResult();

        public cNxqlColumnValidator(cDataHistoryCollector Collector)
        {
            this.Collector = Collector;
            Orchestrator = new cNxqlOrchestration("ColumnValidator");
        }

        public int SetNxqlJobsTables()
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            try
            {
                var QueryCount = 0;
                foreach (var Portal in Collector.InfrastructureConfig.Nexthink.Portals.Values)
                {
                    var lstServer = Portal.Engines.Values.ToList();
                    if (lstServer.Count == 0)
                        continue;

                    var PortalResult = new cNxqlPortalValidationResult();
                    var indexServer = 0;
                    foreach (var Table in Collector.ClusterConfig.Tables.Values)
                    {
                        if (Table.ParentCluster.Origin != enumDataHistoryOrigin.NexthinkNxql)
                            continue;

                        foreach (var SubTable in Table.SubTables.Values)
                        {
                            if (!(SubTable.Template is cDataHistoryConfigQueryTemplateNxql NxqlTemplate))
                                continue;

                            bool isValidTable = true;
                            foreach (var Column in SubTable.ParentTable.KeyColumns)
                            {
                                if (!cDataHistoryCollectorNxql.IsNxqlColumn(Column) && (Column is cDataHistoryConfigColumn _C && _C.SourceType != eDataHistoryQueryType.Static))
                                    isValidTable = false;
                            }

                            if (!isValidTable)
                            {
                                AddResultTable(Portal, SubTable, false, "", "the table key columns are not valid.");
                            }
                            else
                            {
                                var Server = lstServer[indexServer];

                                var strNxqlQuery = cDataHistoryCollectorNxql.getNxqlStatement(NxqlTemplate.Query, Table.ParentCluster.InformationClass, null, Table.KeyColumns, 0, null, null, SubTable.ParentTable.KeyColumns, SubTable.Name);
                                if (string.IsNullOrEmpty(strNxqlQuery))
                                    AddResultTable(Portal, SubTable, false, "", "could not get a valid NXQL statement for queriing the key columns.");
                                else
                                {
                                    var Job = new cNxqlColumnValidatorTableJob(this, SubTable, Portal, strNxqlQuery);
                                    Orchestrator.AddJob($"{Portal.Name}|{SubTable.Name}", Server.Address, strNxqlQuery, Portal.Credential, Port: Server.Port.ToString(), PreProcessing: Job.PreProcessing, PostProcessing: Job.PostProcessing, ErrorProcessing: Job.ErrorProcessing);
                                    indexServer++;
                                    if (indexServer >= lstServer.Count)
                                        indexServer = 0;
                                    QueryCount++;
                                }

                            }
                        }
                    }
                }

                return QueryCount;

            }
            catch (Exception E)
            {
                LogException(E);
                return 0;
            }
            finally
            {
                if (CM != null) LogMethodEnd(CM);
            }

        }

        public int SetNxqlJobsColumns()
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            try
            {
                var QueryCount = 0;
                foreach (var PortalResult in ValidationResults.Values)
                {
                    var Portal = PortalResult.Portal;
                    var lstServer = Portal.Engines.Values.ToList();
                    if (lstServer.Count == 0)
                        continue;

                    var indexServer = 0;
                    foreach (var SubTableResult in PortalResult.TableResults.Values)
                    {
                        if (!SubTableResult.IsValid)
                            continue;

                        var SubTable = SubTableResult.Table;
                        if (!(SubTable.Template is cDataHistoryConfigQueryTemplateNxql NxqlTemplate))
                            continue;

                        var tableInfo = new cNxqlColumnValidatorColumnTableInfo();
                        foreach (var Column in SubTable.Columns.Values)
                        {
                            if (!cDataHistoryCollectorNxql.IsNxqlColumn(Column))
                                continue;

                            if (Column is cDataHistoryConfigColumn _C && _C.SourceType == eDataHistoryQueryType.Static)
                                continue;

                            var Server = lstServer[indexServer];

                            var strNxqlQuery = cDataHistoryCollectorNxql.getNxqlStatement(NxqlTemplate.Query, SubTable.ParentTable.ParentCluster.InformationClass, null, SubTable.ParentTable.KeyColumns, 0, null, null, new List<cDataHistoryConfigColumnBase>() { Column }, SubTable.Name);
                            if (string.IsNullOrEmpty(strNxqlQuery))
                                AddResultColumn(Portal, Column, int.MaxValue, false, "", "Could not get valid NXQL query for the table definition.");
                            else
                            {
                                var Job = new cNxqlColumnValidatorColumnJob(this, tableInfo, Column, Portal, strNxqlQuery);
                                Orchestrator.AddJob($"{Portal.Name}|{SubTable.Name}|{Column.Name}", Server.Address, strNxqlQuery, Portal.Credential, Port: Server.Port.ToString(), PostProcessing: Job.PostProcessing, ErrorProcessing: Job.ErrorProcessing);
                                indexServer++;
                                if (indexServer >= lstServer.Count)
                                    indexServer = 0;
                                QueryCount++;
                            }

                        }
                    }
                }

                return QueryCount;

            }
            catch (Exception E)
            {
                LogException(E);
                return 0;
            }
            finally
            {
                if (CM != null) LogMethodEnd(CM);
            }

        }

        public async Task DoValidationAsync(cF4sdWebRequestInfo requestInfo, int LogDeep, CancellationToken Token)
        {
            if (!(Collector?.InfrastructureConfig?.Nexthink?.Portals?.Count > 0))
                return;

            var QueryCount = SetNxqlJobsTables();
            if (QueryCount > 0)
                await Orchestrator.ProcessAsync(Token);

            QueryCount = SetNxqlJobsColumns();
            if (QueryCount > 0)
                await Orchestrator.ProcessAsync(Token);

            await ValidationResults.UpdateToSqlAsync(Collector, requestInfo, LogDeep + 1, Token);
        }

        public void AddResultTable(cDataHistoryNxtPortal Portal, cDataHistoryConfigSubTable Table, bool IsValid, string NXQL, string Message)
        {
            lock (ValidationResults)
            {
                if (!ValidationResults.TryGetValue(Portal.Name, out var ResultPortal))
                {
                    ResultPortal = new cNxqlPortalValidationResult() { Portal = Portal };
                    ValidationResults.Add(Portal.Name, ResultPortal);
                }

                if (!ResultPortal.TableResults.TryGetValue(Table.Name, out var TableResult))
                {
                    TableResult = new cNxqlTableValidationResult() { Table = Table, IsValid = IsValid, ErrorMessage = Message, NXQL = NXQL };
                    ResultPortal.TableResults.Add(Table.Name, TableResult);
                }
            }
        }

        public void AddResultColumn(cDataHistoryNxtPortal Portal, cDataHistoryConfigColumnBase Column, int columnCount, bool IsValid, string NXQL, string Message)
        {
            lock (ValidationResults)
            {
                if (!ValidationResults.TryGetValue(Portal.Name, out var PortalResults))
                    return;

                if (!PortalResults.TableResults.TryGetValue(Column.ParentTable?.Name, out var TableResult))
                    return;

                TableResult.ColumnResults[Column.Name] = new cNxqlColumnValidationResult() { Column = Column, IsValid = IsValid, ErrorMessage = Message, NXQL = NXQL };

                if (!IsValid)
                    this.Collector?.DoProcessUiMessage(0, $"Error : column validation of '{Column.ParentTable.Name}/{Column.Name}' on portal '{Portal.Name}' failed.");

                if (TableResult.ColumnResults.Count == columnCount)
                    this.Collector?.DoProcessUiMessage(0, $"Column validation of NXQL table '{Column.ParentTable.Name}' on portal '{Portal.Name}' finished.");

            }
        }

    }

    public class cNxqlColumnValidatorTableJob
    {
        public readonly cNxqlColumnValidator Validator;
        public readonly cDataHistoryConfigSubTable Table;
        public readonly cDataHistoryNxtPortal Portal;
        public readonly string NXQL;

        public cNxqlColumnValidatorTableJob(cNxqlColumnValidator Validator, cDataHistoryConfigSubTable Table, cDataHistoryNxtPortal Portal, string NXQL)
        {
            this.Validator = Validator;
            this.Table = Table;
            this.Portal = Portal;
            this.NXQL = NXQL;
        }

        public async Task PreProcessing(string Content, CancellationToken Token)
        {
            //Validator.Collector?.DoProcessUiMessage(0, $"Start validation NXQL table '{Table.Name}' on portal '{Portal.Name}'.");
            await Task.CompletedTask;
        }

        public async Task PostProcessing(string Content, CancellationToken Token)
        {
            var Valid = !string.IsNullOrEmpty(Content);
            Validator.AddResultTable(Portal, Table, Valid, NXQL, Valid ? null : "empty result returned.");
            if (Valid)
                Validator.Collector?.DoProcessUiMessage(0, $"Base validation of NXQL table '{Table.Name}' on portal '{Portal.Name}' succesfull.");
            else
                Validator.Collector?.DoProcessUiMessage(0, $"Error: Base validation of NXQL table '{Table.Name}' on portal '{Portal.Name}' failed.");

            await Task.CompletedTask;
        }

        public async Task ErrorProcessing(string Content, CancellationToken Token)
        {
            Validator.AddResultTable(Portal, Table, false, NXQL, Content);
            Validator.Collector?.DoProcessUiMessage(0, $"Error: Base validation of NXQL table '{Table.Name}' on portal '{Portal.Name}' failed.");
            await Task.CompletedTask;
        }
    }

    public class cNxqlColumnValidatorColumnTableInfo
    {
        public int ColCount = 0;
    }

    public class cNxqlColumnValidatorColumnJob
    {
        public readonly cNxqlColumnValidator Validator;
        public readonly cDataHistoryConfigColumnBase TableColumn;
        public readonly cDataHistoryNxtPortal Portal;
        public readonly cNxqlColumnValidatorColumnTableInfo TableInfo;
        public readonly string NXQL;

        public cNxqlColumnValidatorColumnJob(cNxqlColumnValidator Validator, cNxqlColumnValidatorColumnTableInfo TableInfo, cDataHistoryConfigColumnBase TableColumn, cDataHistoryNxtPortal Portal, string NXQL)
        {
            this.Validator = Validator;
            this.TableInfo = TableInfo;
            TableInfo.ColCount++;
            this.TableColumn = TableColumn;
            this.Portal = Portal;
            this.NXQL = NXQL;
        }

        public async Task PostProcessing(string Content, CancellationToken Token)
        {
            var Valid = !string.IsNullOrEmpty(Content);
            Validator.AddResultColumn(Portal, TableColumn, TableInfo.ColCount, Valid, NXQL, Valid ? null : "empty result returned.");
            await Task.CompletedTask;
        }

        public async Task ErrorProcessing(string Content, CancellationToken Token)
        {
            Validator.AddResultColumn(Portal, TableColumn, int.MaxValue, false, NXQL, Content);
            await Task.CompletedTask;
        }

    }

    public class cNxqlColumnsValidationResult : Dictionary<string, cNxqlPortalValidationResult>
    {
        public async Task<bool> LoadFromSqlAsync(cDataHistoryCollector Collector, CancellationToken Token, cF4sdWebRequestInfo requestInfo, int LogDeep)
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            if (cPerformanceLogger.IsActive && requestInfo != null) { if (CM == null) CM = MethodBase.GetCurrentMethod(); cPerformanceLogger.LogPerformanceStart(LogDeep, CM, requestInfo.id, requestInfo.created); }
            var _startTime = DateTime.UtcNow;

            try
            {
                if (!DataHistorySqlHelper.GetWellKnownSqlStatement("LoadColumnsValidationResult", out var Query))
                    return false;

                this.Clear();

                foreach (var Portal in Collector.InfrastructureConfig.Nexthink.Portals.Values)
                {
                    var Instance = Portal.Name;
                    var InstanceResult = new cNxqlPortalValidationResult();
                    this[Instance] = InstanceResult;

                    foreach (var Table in Collector.ClusterConfig.Tables.Values)
                    {
                        if (Table.ParentCluster.Origin != enumDataHistoryOrigin.NexthinkNxql)
                            continue;

                        foreach (var SubTable in Table.SubTables.Values)
                        {
                            var SubTableName = SubTable.Name;
                            var SubTableResult = new cNxqlTableValidationResult() { Table = SubTable };

                            InstanceResult.TableResults[SubTableName] = SubTableResult;

                            foreach (var Column in SubTable.Columns.Values)
                            {
                                if (Column is cDataHistoryConfigColumnComputation)
                                    continue;

                                var ColumnResult = new cNxqlColumnValidationResult() { Column = Column };
                                SubTableResult.ColumnResults[Column.Name] = ColumnResult;
                            }
                        }
                    }
                }

                var Params = new Dictionary<string, object>
                    {
                        { "Connector", "NXQL" }
                    };

                var sqlerror = 0;
                var sqlStartTime = DateTime.UtcNow;

                try
                {
                    using (var Conn = new cDbConnection(Collector.mainDbConnection))
                    {
                        if (!Conn.IsOpen)
                        {
                            LogEntry($"Could not open main sql database '{Collector.mainDbConnection.Database}', aborting saving NXQL column validation result", LogLevels.Error);
                            return false;
                        }

                        using (var Reader = await DataHistorySqlHelper.GetTableResultAsync(Conn, Query, Params, CancellationToken.None))
                        {
                            if (Reader == null)
                                return false;

                            try
                            {
                                if (Reader.HasRows)
                                {
                                    while (await Reader.ReadAsync())
                                    {
                                        try
                                        {
                                            var Instance = Reader.GetString(0);
                                            var SubTableName = Reader.GetString(1);
                                            var ColumnName = Reader.GetString(2);
                                            var IsValid = Reader.GetByte(3);

                                            if (this.TryGetValue(Instance, out var InstanceResult))
                                                if (InstanceResult.TableResults.TryGetValue(SubTableName, out var TableResult))
                                                    if (TableResult.ColumnResults.TryGetValue(ColumnName, out var ColumnResult))
                                                        ColumnResult.IsValid = IsValid == 1;
                                        }
                                        catch (Exception E)
                                        {
                                            cLogManager.DefaultLogger.LogException(E);
                                        }
                                    }
                                }
                            }
                            catch (Exception E)
                            {
                                cLogManager.DefaultLogger.LogException(E);
                            }
                        }
                    }

                }
                catch (Exception E)
                {
                    sqlerror = E.HResult;
                    LogException(E);
                }
                finally
                {
                    if (DataHistorySqlHelper.LogSql) DataHistorySqlHelper.SaveSqlTimingEntry(Query.Name, sqlStartTime, Params, sqlerror, requestInfo?.requestName);
                }

                foreach (var InstanceResult in this.Values)
                {
                    foreach (var TableResult in InstanceResult.TableResults.Values)
                    {
                        TableResult.IsValid = false;
                        foreach (var ColumnResult in TableResult.ColumnResults.Values)
                            TableResult.IsValid |= ColumnResult.IsValid;
                        if (!TableResult.IsValid)
                            TableResult.ColumnResults.Clear();
                    }
                }
            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                if (cPerformanceLogger.IsActive && requestInfo != null) { cPerformanceLogger.LogPerformanceEnd(LogDeep, CM, requestInfo.id, requestInfo.created, _startTime); }
                if (CM != null) LogMethodEnd(CM);
            }

            return false;
        }

        public async Task UpdateToSqlAsync(cDataHistoryCollector Collector, cF4sdWebRequestInfo requestInfo, int LogDeep, CancellationToken Token)
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            try
            {
                using (var Conn = new cDbConnection(Collector.mainDbConnection))
                {
                    if (!Conn.IsOpen)
                    {
                        LogEntry($"Could not open main sql database '{Collector.mainDbConnection.Database}', aborting saving NXQL column validation result", LogLevels.Error);
                        return;
                    }

                    foreach (var PortalEntry in this.Values)
                    {
                        foreach (var SubtableEntry in PortalEntry.TableResults.Values)
                        {
                            if (!SubtableEntry.IsValid)
                            {
                                foreach (var ColumnEntry in SubtableEntry.Table.Columns.Values)
                                    await SaveEntryAsync(Conn, PortalEntry.Portal.Name, ColumnEntry, false, requestInfo, LogDeep + 1, Token);

                            }
                            else
                            {
                                foreach (var ColumnEntry in SubtableEntry.ColumnResults.Values)
                                    await SaveEntryAsync(Conn, PortalEntry.Portal.Name, ColumnEntry.Column, ColumnEntry.IsValid, requestInfo, LogDeep + 1, Token);
                            }
                        }
                    }
                }
            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                if (CM != null) LogMethodEnd(CM);
            }
        }

        private async Task SaveEntryAsync(cDbConnection Conn, string Portal, cDataHistoryConfigColumnBase Column, bool IsValid, cF4sdWebRequestInfo requestInfo, int LogDeep, CancellationToken Token)
        {
            try
            {
                var Params = new Dictionary<string, object>() { { "Connector", "NXQL" }, { "Instance", Portal }, { "SubTable", Column.ParentTable.Name }, { "Column", Column.Name }, { "isValid", IsValid } };
                await DataHistorySqlHelper.DoUpdateOrInsertAsync(Conn, "main-column-validation", Params, new List<string>() { "Connector", "Instance", "SubTable", "Column" }, requestInfo, LogDeep + 1, Token);
            }
            catch (Exception E)
            {
                LogException(E);
            }
        }
    }

    public class cNxqlPortalValidationResult
    {
        public cDataHistoryNxtPortal Portal;
        public Dictionary<string, cNxqlTableValidationResult> TableResults = new Dictionary<string, cNxqlTableValidationResult>();
    }

    public class cNxqlTableValidationResult
    {
        public cDataHistoryConfigSubTable Table;
        public bool IsValid;
        public string ErrorMessage;
        public string NXQL;
        public Dictionary<string, cNxqlColumnValidationResult> ColumnResults = new Dictionary<string, cNxqlColumnValidationResult>();
    }

    public class cNxqlColumnValidationResult
    {
        public cDataHistoryConfigColumnBase Column;
        public bool IsValid;
        public string NXQL;
        public string ErrorMessage;
    }

    public class cNxqlDailyCollector
    {
        public class cTableResult : Dictionary<string, object>
        {
            public enumFasdInformationClass InformationClass = enumFasdInformationClass.Unknown;
        }

        public readonly cDataHistoryCollector Collector;
        public readonly cNxqlOrchestration Orchestrator;
        public readonly cNxqlColumnsValidationResult ColumnsValidationResults;

        public Dictionary<string, cTableResult> Results = new Dictionary<string, cTableResult>();

        public cNxqlDailyCollector(cDataHistoryCollector Collector, cNxqlColumnsValidationResult ColumnsValidationResults)
        {
            this.Collector = Collector;
            this.ColumnsValidationResults = ColumnsValidationResults;
            Orchestrator = new cNxqlOrchestration("DailyCollector");
        }

        public void CreateJobs(List<cDataHistoryConfigTable> Tables, DateTime From, DateTime To, string strEngine, Int64 Nxt_Id, enumFasdInformationClass InfoClass)
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            try
            {
                // get the engine, portal & column validation result for the engine
                cDataHistoryNxtEngine EngineComputer = null;
                cDataHistoryNxtPortal PortalComputer = null;
                cNxqlPortalValidationResult PortalValidationResult = null; ;

                foreach (var PortalEntry in Collector.InfrastructureConfig.Nexthink.Portals.Values)
                {
                    foreach (var EngineEntry in PortalEntry.Engines.Values)
                    {
                        if (EngineEntry.Name == strEngine)
                        {
                            EngineComputer = EngineEntry;
                            PortalComputer = PortalEntry;
                            if (!ColumnsValidationResults.TryGetValue(PortalComputer.Name, out PortalValidationResult))
                            {
                                LogEntry($"no valid colum validation result for portal '{PortalComputer.Name}' available.", LogLevels.Error);
                                return;
                            }
                        }
                    }
                }

                if (EngineComputer == null)
                {
                    LogEntry($"no valid engine configuration entry found for engine name '{strEngine}' available.", LogLevels.Error);
                    return;
                }

                if (PortalValidationResult == null)
                {
                    LogEntry($"no valid colum validation result for portal '{PortalComputer.Name}' available.", LogLevels.Error);
                    return;
                }

                foreach (var Table in Tables)
                {
                    if (!(Table.ParentCluster.Origin == enumDataHistoryOrigin.NexthinkNxql))
                        continue;

                    if (Table.ParentCluster.InformationClass != InfoClass)
                        continue;

                    var TableCollector = new cNxqlDailyCollectorTable(this, EngineComputer, Table);

                    foreach (var SubTable in Table.SubTables.Values)
                    {
                        if (!PortalValidationResult.TableResults.TryGetValue(SubTable.Name, out var SubTableValidation))
                            continue;

                        if (!(SubTable.Template is cDataHistoryConfigQueryTemplateNxql NxqlTemplate))
                            continue;

                        var Cols = new List<cDataHistoryConfigColumnBase>();
                        foreach (var _res in SubTableValidation.ColumnResults.Values)
                            if (_res.IsValid)
                                Cols.Add(_res.Column);
                        if (!SubTable.Columns.TryGetValue("id_int", out var _keyCol))
                        {
                            LogEntry($"no valid column 'id_int' found in subtable configuration '{SubTable.Name}'", LogLevels.Warning);
                            return;
                        }

                        var strNxqlQuery = cDataHistoryCollectorNxql.getNxqlStatement(NxqlTemplate.Query, Table.ParentCluster.InformationClass, new List<object>(1) { Nxt_Id }, new List<cDataHistoryConfigColumnBase>(1) { _keyCol }, NxqlTemplate.Limit, From, To, Cols, SubTable.Name);

                        var SubTableCollector = new cNxqlDailyCollectorSubTable(strNxqlQuery, SubTable, TableCollector, EngineComputer);

                        Orchestrator.AddJob($"{EngineComputer.Name}|{SubTable.Name}", EngineComputer.Address, strNxqlQuery, PortalComputer.Credential, Port: EngineComputer.Port.ToString(), PostProcessing: SubTableCollector.PostProcessing, ErrorProcessing: SubTableCollector.ErrorProcessing);

                    }
                }

            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                if (CM != null) LogMethodEnd(CM);
            }
        }

    }

    public class cNxqlDailyCollectorTable
    {
        public readonly cNxqlDailyCollector DataCollector;
        public readonly cDataHistoryConfigTable Table;
        public readonly cDataHistoryNxtEngine Engine;

        private readonly Dictionary<string, cNxqlDailyCollectorSubTable> SubTableCollectors = new Dictionary<string, cNxqlDailyCollectorSubTable>();

        public cNxqlDailyCollectorTable(cNxqlDailyCollector DataCollector, cDataHistoryNxtEngine Engine, cDataHistoryConfigTable Table)
        {
            this.DataCollector = DataCollector;
            this.Engine = Engine;
            this.Table = Table;
        }

        public void RegisterSubTableCollector(cNxqlDailyCollectorSubTable Collector)
        {
            SubTableCollectors[Collector.SubTable.Name] = Collector;
        }

        public async Task ProcessResult(string SubTable, CancellationToken Token)
        {

            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            try
            {
                if (!SubTableCollectors.TryGetValue(SubTable, out var Collector))
                    return;

                var isFinished = true;
                lock (SubTableCollectors)
                {
                    Collector.finished = true;
                    foreach (var SubCollector in SubTableCollectors.Values)
                        isFinished &= SubCollector.finished;
                }
                if (!isFinished)
                    return;

                var Cols = new cTableResult();

                foreach (var SubTableResult in SubTableCollectors.Values)
                {
                    if (SubTableResult.Result == null)
                        continue;

                    // get the infos, which csv column refers to which subtable column
                    var ColInfos = new Dictionary<string, cNxqlColumnInfo>();
                    for (int i = 0; i < SubTableResult.Result.Columns.Length; i++)
                    {
                        var colNameCsv = SubTableResult.Result.Columns[i].ToLowerInvariant();
                        bool found = false;
                        foreach (var SubTableColumnEntry in SubTableResult.SubTable.Columns.Values)
                        {
                            if (SubTableColumnEntry is cDataHistoryConfigColumnComputation)
                                continue;
                            var colNameST = cDataHistoryCollectorNxql.GetNxqlColumnResultName(SubTableColumnEntry);
                            if (colNameST == colNameCsv)
                            {
                                var colInfo = new cNxqlColumnInfo()
                                {
                                    Column = SubTableColumnEntry,
                                    ColIndex = i
                                };
                                ColInfos[SubTableColumnEntry.Name] = colInfo;
                                found = true;
                            }
                        }

                        if (!found)
                            LogEntry($"No matching column could be found for NXQL column '{colNameCsv}' in sub-table '{SubTableResult.SubTable.Name}'", LogLevels.Warning);

                        // ad the static value to the column list
                        foreach (var SubTableColumnEntry in SubTableResult.SubTable.Columns.Values)
                        {
                            if (SubTableColumnEntry is cDataHistoryConfigColumn col)
                            {
                                if (col.SourceType == eDataHistoryQueryType.Static && col.SourceName.ToLowerInvariant() == "engine")
                                {
                                    var colInfo = new cNxqlColumnInfo()
                                    {
                                        Column = SubTableColumnEntry,
                                        ColIndex = -1,
                                        StaticValue = Engine.Name
                                    };
                                    ColInfos[SubTableColumnEntry.Name] = colInfo;
                                }
                            }
                        }
                    }

                    if (SubTableResult.SubTable.Aggregation)
                    {
                        var Aggs = new Dictionary<string, cNxqlAggregationEntry>(ColInfos.Count);

                        foreach (var _colInfo in ColInfos.Values)
                        {
                            if (!(_colInfo.Column is cDataHistoryConfigColumn _col))
                                continue;

                            Aggs.Add(_colInfo.Column.Name, new cNxqlAggregationEntry(_col));
                        }

                        foreach (var DataRecord in SubTableResult.Result.Data)
                        {
                            foreach (var _colInfo in ColInfos.Values)
                            {
                                if (!Aggs.TryGetValue(_colInfo.Column.Name, out var agg))
                                    continue;

                                if (_colInfo.ColIndex < 0)
                                    agg.addValue(_colInfo.StaticValue, null);
                                else
                                    agg.addValue(DataRecord[_colInfo.ColIndex], null);
                            }
                        }

                        foreach (var _colInfo in ColInfos.Values)
                        {
                            if (!Aggs.TryGetValue(_colInfo.Column.Name, out var agg))
                                continue;
                            if (!(_colInfo.Column is cDataHistoryConfigColumn _col))
                                continue;

                            var _aggVal = agg.getValue();
                            var dtype = cDataHistoryConfigClusters.ResultingAggregationType(_col.ValueType, _col.AggregationType);
                            var sqlVals = DataHistorySqlHelper.GetSqlDataValueFromHistoryType(_aggVal, dtype, _col.Cardinal);
                            if (sqlVals != null)
                            {
                                if (sqlVals.Length > 0)
                                {
                                    Cols[_colInfo.Column.Name] = sqlVals[0];
                                    /*
                                    if (sqlVals.Length > 1)
                                        Cols[_colInfo.Column.Name + "_bin"] = sqlVals[1];
                                    */
                                }
                            }
                        }
                    }
                    else
                    {
                        if (SubTableResult.Result.Data.Count > 0)
                        {
                            var DataRecord = SubTableResult.Result.Data[0];
                            foreach (var _colInfo in ColInfos.Values)
                            {
                                string val = null;
                                if (_colInfo.ColIndex < 0)
                                    val = _colInfo.StaticValue;
                                else
                                    val = DataRecord[_colInfo.ColIndex];

                                var sqlVals = DataHistorySqlHelper.GetSqlDataValueFromHistoryType(val, _colInfo.Column.ValueType, _colInfo.Column.Cardinal);
                                if (sqlVals != null)
                                {
                                    if (sqlVals.Length > 0)
                                    {
                                        Cols[_colInfo.Column.Name] = sqlVals[0];
                                        if (sqlVals.Length > 1)
                                            Cols[_colInfo.Column.Name + "_bin"] = sqlVals[1];
                                    }
                                }
                            }
                        }
                    }
                }

                lock (this.DataCollector.Results)
                    this.DataCollector.Results[this.Table.Name] = Cols;

            }
            catch (Exception E)
            {
                await Task.CompletedTask;
                LogException(E);
            }
            finally
            {
                if (CM != null) LogMethodEnd(CM);
            }
        }
    }

    public class cNxqlDailyCollectorSubTable
    {
        public readonly cDataHistoryConfigSubTable SubTable;
        public readonly cNxqlDailyCollectorTable TableCollector;
        public readonly cDataHistoryNxtEngine Server;
        public readonly string NXQL;

        public cCsvFile Result = null;
        public bool finished = false;

        public cNxqlDailyCollectorSubTable(string NXQL, cDataHistoryConfigSubTable SubTable, cNxqlDailyCollectorTable TableCollector, cDataHistoryNxtEngine Server)
        {
            this.NXQL = NXQL;
            this.SubTable = SubTable;
            this.TableCollector = TableCollector;
            this.Server = Server;
            TableCollector.RegisterSubTableCollector(this);
        }

        private void LogContent(string Content)
        {
            var Lines = Content.Split('\n');
            var arrDebug = new List<string>(5)
                {
                    $"NXQL query for sub-table '{SubTable.Name}': {NXQL}",
                    "Resulting lines: " + Lines.Length.ToString()
                };
            string str = "Line1: ";
            if (Lines.Length >= 1)
                str += Lines[0].Substring(0, Math.Min(255, Lines[0].Length));
            arrDebug.Add(str);
            str = "Line2: ";
            if (Lines.Length >= 2)
                str += Lines[1].Substring(0, Math.Min(255, Lines[1].Length));
            arrDebug.Add(str);
            str = "Line3: ";
            if (Lines.Length >= 3)
                str += Lines[2].Substring(0, Math.Min(255, Lines[2].Length));
            arrDebug.Add(str);
            cLogManager.DefaultLogger.LogList(LogLevels.Debug, arrDebug);
        }

        public async Task PostProcessing(string Content, CancellationToken Token)
        {
            var csvResult = new cCsvFile();
            if (cLogManager.DefaultLogger.IsDebug)
                LogContent(Content);

            if (csvResult.ImportFromString(Content))
            {
                Result = csvResult;

                var lines = Result.Data.Count;
                TableCollector?.DataCollector?.Collector?.DoProcessUiMessage(0, $"NXQL query for sub-table {SubTable.Name} on server {Server.Name}({Server.Address}) returned {lines} lines", LogLevels.Debug);

                await TableCollector.ProcessResult(SubTable.Name, Token);
            }
            else
            {
                LogEntry($"Could not convert result of NXQL query for subtable {SubTable.Name} on engine {Server.Name}({Server.Address}) to csv.", LogLevels.Error);
            }

        }

        public async Task ErrorProcessing(string Content, CancellationToken Token)
        {
            await Task.CompletedTask;
            LogEntry($"Could not get result of NXQL query for subtable {SubTable.Name} on engine {Server.Name}({Server.Address})", LogLevels.Error);
        }


    }

    public class cNxqlDataCollector
    {
        public readonly cDataHistoryCollector Collector;
        public readonly cNxqlOrchestration Orchestrator;
        public readonly Guid ScanId;

        public readonly string FileSavePath = null;

        public readonly cNxqlColumnsValidationResult ColumnsValidationResults;

        public int validResultCount { get; internal set; } = 0;
        public int errorResultCount { get; internal set; } = 0;

        public cNxqlDataCollector(cDataHistoryCollector Collector, cNxqlColumnsValidationResult ColumnsValidationResults, Guid ScanId, bool SaveResultsToFile)
        {
            this.Collector = Collector;
            this.ColumnsValidationResults = ColumnsValidationResults;
            this.ScanId = ScanId;
            if (SaveResultsToFile)
            {
                if (cLogManager.Instance is cLogManagerFile log)
                {
                    try
                    {
                        var fn = log.GetLogFileName();
                        fn = Path.Combine(fn, "..\\NxqlQueryResults");
                        if (Directory.Exists(fn))
                        {
                            var di = new DirectoryInfo(fn);
                            foreach (FileInfo file in di.EnumerateFiles())
                                file.Delete();
                            foreach (DirectoryInfo dir in di.EnumerateDirectories())
                                dir.Delete(true);
                        }
                        Directory.CreateDirectory(fn);
                        if (Directory.Exists(fn))
                            FileSavePath = fn;
                    }
                    catch (Exception E)
                    {
                        LogException(E);
                    }
                }
            }
            Orchestrator = new cNxqlOrchestration("DataCollector");
        }

        public void CreateJobs(DateTime From, DateTime To, cF4sdWebRequestInfo requestInfo, int LogDeep)
        {
            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            if (cPerformanceLogger.IsActive && requestInfo != null) { if (CM == null) CM = MethodBase.GetCurrentMethod(); cPerformanceLogger.LogPerformanceStart(LogDeep, CM, requestInfo.id, requestInfo.created); }
            var _startTime = DateTime.UtcNow;

            try
            {
                foreach (var Portal in Collector.InfrastructureConfig?.Nexthink?.Portals?.Values)
                {
                    if (!ColumnsValidationResults.TryGetValue(Portal.Name, out var PortalValidationResult))
                        continue;

                    foreach (var Engine in Portal.Engines.Values)
                    {
                        foreach (var Table in Collector.ClusterConfig.Tables.Values)
                        {
                            if (!(Table.ParentCluster.Origin == enumDataHistoryOrigin.NexthinkNxql))
                                continue;

                            if (Table.Cached == eDataHistoryTableCached.No && FileSavePath == null)
                                continue;

                            var TableCollector = new cNxqlDataCollectorTable(this, Engine, Table);

                            foreach (var SubTable in Table.SubTables.Values)
                            {
                                if (!PortalValidationResult.TableResults.TryGetValue(SubTable.Name, out var SubTableValidation))
                                    continue;

                                if (!(SubTable.Template is cDataHistoryConfigQueryTemplateNxql NxqlTemplate))
                                    continue;

                                var Cols = new List<cDataHistoryConfigColumnBase>();
                                foreach (var _res in SubTableValidation.ColumnResults.Values)
                                    if (_res.IsValid)
                                        Cols.Add(_res.Column);

                                var strNxqlQuery = cDataHistoryCollectorNxql.getNxqlStatement(NxqlTemplate.Query, Table.ParentCluster.InformationClass, null, null, NxqlTemplate.Limit, From, To, Cols, SubTable.Name);

                                var SubTableCollector = new cNxqlDataCollectorSubTable(strNxqlQuery, SubTable, TableCollector, Engine, requestInfo, LogDeep + 1);

                                Orchestrator.AddJob($"{Engine.Name}|{SubTable.Name}", Engine.Address, strNxqlQuery, Portal.Credential, Port: Engine.Port.ToString(), PostProcessing: SubTableCollector.PostProcessing, ErrorProcessing: SubTableCollector.ErrorProcessing);

                            }
                        }
                    }
                }


            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                if (cPerformanceLogger.IsActive && requestInfo != null) { cPerformanceLogger.LogPerformanceEnd(LogDeep, CM, requestInfo.id, requestInfo.created, _startTime); }
                if (CM != null) LogMethodEnd(CM);
            }
        }
    }

    public class cNxqlDataCollectorTable
    {
        public readonly cNxqlDataCollector DataCollector;
        public readonly cDataHistoryConfigTable Table;
        public readonly cDataHistoryNxtEngine Engine;

        private readonly Dictionary<string, cNxqlDataCollectorSubTable> SubTableCollectors = new Dictionary<string, cNxqlDataCollectorSubTable>();

        public cNxqlDataCollectorTable(cNxqlDataCollector DataCollector, cDataHistoryNxtEngine Engine, cDataHistoryConfigTable Table)
        {
            this.DataCollector = DataCollector;
            this.Engine = Engine;
            this.Table = Table;
        }

        public void RegisterSubTableCollector(cNxqlDataCollectorSubTable Collector)
        {
            SubTableCollectors[Collector.SubTable.Name] = Collector;
        }

        public async Task ProcessResult(string SubTable, cF4sdWebRequestInfo requestInfo, int LogDeep, CancellationToken Token)
        {

            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            if (cPerformanceLogger.IsActive && requestInfo != null) { if (CM == null) CM = MethodBase.GetCurrentMethod(); cPerformanceLogger.LogPerformanceStart(LogDeep, CM, requestInfo.id, requestInfo.created); }
            var _startTime = DateTime.UtcNow;

            try
            {
                if (!SubTableCollectors.TryGetValue(SubTable, out var Collector))
                    return;

                var isFinished = true;
                lock (SubTableCollectors)
                {
                    Collector.finished = true;
                    foreach (var SubCollector in SubTableCollectors.Values)
                        isFinished &= SubCollector.finished;
                }
                if (!isFinished)
                    return;


                using (var Conn = new cDbConnection(DataCollector.Collector.mainDbConnection))
                {
                    var TableName = SubTableCollectors?.Values?.First()?.SubTable?.ParentTable.SourceName;
                    if (!Conn.IsOpen)
                    {
                        LogEntry($"Could not open main sql database '{DataCollector.Collector.mainDbConnection.Database}', aborting saving result for nxql table '{TableName}'", LogLevels.Error);
                        DataCollector.errorResultCount++;
                        return;
                    }

                    var firstSubTable = true;

                    foreach (var SubTableResult in SubTableCollectors.Values)
                    {
                        if (SubTableResult.Result == null)
                            continue;

                        if (SubTableResult.SubTable.ParentTable.Cached != eDataHistoryTableCached.Yes)
                            continue;

                        // get the infos, which csv column refers to which subtable column
                        var ColInfos = new Dictionary<string, cNxqlColumnInfo>();
                        for (int i = 0; i < SubTableResult.Result.Columns.Length; i++)
                        {
                            var colNameCsv = SubTableResult.Result.Columns[i].ToLowerInvariant();
                            bool found = false;
                            foreach (var SubTableColumnEntry in SubTableResult.SubTable.Columns.Values)
                            {
                                if (SubTableColumnEntry is cDataHistoryConfigColumnComputation)
                                    continue;
                                var colNameST = cDataHistoryCollectorNxql.GetNxqlColumnResultName(SubTableColumnEntry);
                                if (colNameST == colNameCsv)
                                {
                                    var colInfo = new cNxqlColumnInfo()
                                    {
                                        Column = SubTableColumnEntry,
                                        ColIndex = i
                                    };
                                    ColInfos[SubTableColumnEntry.Name] = colInfo;
                                    found = true;
                                }
                            }

                            if (!found)
                                LogEntry($"No matching column could be found for NXQL column '{colNameCsv}' in sub-table '{SubTableResult.SubTable.Name}'", LogLevels.Warning);
                        }

                        // ad the static value to the column list
                        foreach (var SubTableColumnEntry in SubTableResult.SubTable.Columns.Values)
                        {
                            if (SubTableColumnEntry is cDataHistoryConfigColumn col)
                            {
                                if (col.SourceType == eDataHistoryQueryType.Static && col.SourceName.ToLowerInvariant() == "engine")
                                {
                                    var colInfo = new cNxqlColumnInfo()
                                    {
                                        Column = SubTableColumnEntry,
                                        ColIndex = -1,
                                        StaticValue = Engine.Name
                                    };
                                    ColInfos[SubTableColumnEntry.Name] = colInfo;
                                }
                            }
                        }


                        // check if we have all necessary key columns
                        var keyValid = true;
                        var IDs = new List<string>(SubTableResult.SubTable.ParentTable.KeyColumns.Count);
                        var idInfos = new List<cNxqlColumnInfo>(SubTableResult.SubTable.ParentTable.KeyColumns.Count);
                        if (SubTableResult.SubTable.ParentTable.Type == eDataHistoryTableType.History)
                            IDs.Add("ScanId");

                        foreach (var Entry in SubTableResult.SubTable.ParentTable.KeyColumns)
                        {
                            if (!ColInfos.TryGetValue(Entry.Name, out var _ci))
                            {
                                keyValid = false;
                                LogEntry($"Could not store the results for sub-table '{SubTableResult.SubTable.Name}' because the key column '{Entry.Name} could not be found in the NXQL result on engine '{Engine.Name}({Engine.Address})'", LogLevels.Error);
                                break;
                            }
                            IDs.Add(Entry.NameBin);
                            idInfos.Add(_ci);
                        }
                        if (!keyValid)
                            continue;

                        var RecordCount = 0;
                        var ErrorCount = 0;

                        if (SubTableResult.SubTable.Aggregation)
                        {
                            var groupingDic = new Dictionary<string, List<string[]>>();
                            foreach (var DataRecord in SubTableResult.Result.Data)
                            {
                                var strKey = "";
                                foreach (var colInfo in idInfos)
                                {
                                    if (strKey != null)
                                        strKey += '|';
                                    if (colInfo.ColIndex >= 0)
                                        strKey += DataRecord[colInfo.ColIndex];
                                }
                                if (!groupingDic.TryGetValue(strKey, out var listVals))
                                {
                                    listVals = new List<string[]>();
                                    groupingDic.Add(strKey, listVals);
                                }
                                listVals.Add(DataRecord);

                            }

                            foreach (var groupEntry in groupingDic.Values)
                            {
                                var Cols = new Dictionary<string, object>(ColInfos.Count)
                                {
                                    { "ScanId", DataCollector.ScanId }
                                };
                                foreach (var _colInfo in ColInfos.Values)
                                {
                                    if (!(_colInfo.Column is cDataHistoryConfigColumn _col))
                                        continue;

                                    var agg = new cNxqlAggregationEntry(_col);

                                    foreach (var ds in groupEntry)
                                    {
                                        if (_colInfo.ColIndex < 0)
                                            agg.addValue(_colInfo.StaticValue, null);
                                        else
                                            agg.addValue(ds[_colInfo.ColIndex], null);
                                    }


                                    var _aggVal = agg.getValue();
                                    var dtype = cDataHistoryConfigClusters.ResultingAggregationType(_col.ValueType, _col.AggregationType);

                                    var sqlVals = DataHistorySqlHelper.GetSqlDataValueFromHistoryType(_aggVal, dtype, _colInfo.Column.Cardinal);

                                    if (sqlVals != null)
                                    {
                                        if (sqlVals.Length > 0)
                                        {
                                            Cols[_colInfo.Column.Name] = sqlVals[0];
                                            if (sqlVals.Length > 1)
                                                Cols[_colInfo.Column.Name + "_bin"] = sqlVals[1];
                                        }
                                    }

                                }

                                // insert or update the data record
                                bool isValid = false;
                                if (firstSubTable && SubTableResult.SubTable.ParentTable.Type != eDataHistoryTableType.Static)
                                    isValid = await DataHistorySqlHelper.DoInsertAsync(Conn, TableName, Cols, Token, requestInfo, LogDeep + 1, LogSilent: true);
                                else if (SubTableResult.SubTable.ParentTable.Type != eDataHistoryTableType.Events)
                                    isValid = await DataHistorySqlHelper.DoUpdateOrInsertAsync(Conn, TableName, Cols, IDs, requestInfo, LogDeep + 1, Token, LogSilent: true);

                                if (isValid)
                                    RecordCount++;
                                else
                                    ErrorCount++;
                            }
                        }
                        else
                        {
                            foreach (var DataRecord in SubTableResult.Result.Data)
                            {
                                var Cols = new Dictionary<string, object>(ColInfos.Count)
                                {
                                    { "ScanId", DataCollector.ScanId }
                                };
                                foreach (var _colInfo in ColInfos.Values)
                                {
                                    string val = null;
                                    if (_colInfo.ColIndex < 0)
                                        val = _colInfo.StaticValue;
                                    else
                                        val = DataRecord[_colInfo.ColIndex];

                                    var sqlVals = DataHistorySqlHelper.GetSqlDataValueFromHistoryType(val, _colInfo.Column.ValueType, _colInfo.Column.Cardinal);
                                    if (sqlVals != null)
                                    {
                                        if (sqlVals.Length > 0)
                                        {
                                            Cols[_colInfo.Column.Name] = sqlVals[0];
                                            if (sqlVals.Length > 1)
                                                Cols[_colInfo.Column.Name + "_bin"] = sqlVals[1];
                                        }
                                    }
                                }

                                // insert or update the data record
                                bool isValid = false;
                                if (firstSubTable && SubTableResult.SubTable.ParentTable.Type != eDataHistoryTableType.Static)
                                    isValid = await DataHistorySqlHelper.DoInsertAsync(Conn, TableName, Cols, Token, requestInfo, LogDeep + 1, LogSilent: true);
                                else if (SubTableResult.SubTable.ParentTable.Type != eDataHistoryTableType.Events)
                                    isValid = await DataHistorySqlHelper.DoUpdateOrInsertAsync(Conn, TableName, Cols, IDs, requestInfo, LogDeep + 1, Token, LogSilent: true);

                                if (isValid)
                                    RecordCount++;
                                else
                                    ErrorCount++;
                            }

                        }

                        LogEntry($"{RecordCount}/{SubTableResult?.Result?.Data?.Count} records inserted or updated for sub-table {SubTableResult?.SubTable?.Name} in table {SubTableResult?.SubTable?.ParentTable?.Name}", LogLevels.Debug);
                        if (ErrorCount != 0)
                            LogEntry($"{ErrorCount}/{SubTableResult?.Result?.Data?.Count} errors on inserts or updates for sub-table {SubTableResult?.SubTable?.Name} in table {SubTableResult?.SubTable?.ParentTable?.Name}", LogLevels.Error);

                        var _sum = ErrorCount + RecordCount;
                        var isValidResult = false;
                        if (_sum > 0 && (ErrorCount * 1000 / _sum) >= 100)
                            DataCollector.errorResultCount++;
                        else
                        {
                            DataCollector.validResultCount++;
                            isValidResult = true;
                        }

                        var msg = $"Result for nxql-subtable '{SubTableResult?.SubTable?.Name}' on server '{Engine.Name}': {RecordCount} records inserted";
                        if (ErrorCount != 0)
                            msg += $", {ErrorCount} records failed";
                        msg += ".";
                        DataCollector?.Collector?.DoProcessUiMessage(0, msg);
                        if (isValidResult && ErrorCount != 0)
                            DataCollector?.Collector?.DoProcessUiMessage(1, $"The result is considered valid.");
                        else if (ErrorCount != 0)
                            DataCollector?.Collector?.DoProcessUiMessage(1, $"The result is considered invalid.");

                        firstSubTable = false;
                    }
                }
            }
            catch (Exception E)
            {
                DataCollector.errorResultCount++;
                LogException(E);
            }
            finally
            {
                if (cPerformanceLogger.IsActive && requestInfo != null) { cPerformanceLogger.LogPerformanceEnd(LogDeep, CM, requestInfo.id, requestInfo.created, _startTime); }
                if (CM != null) LogMethodEnd(CM);
            }
        }
    }

    public class cNxqlDataCollectorSubTable
    {
        public readonly cDataHistoryConfigSubTable SubTable;
        public readonly cNxqlDataCollectorTable TableCollector;
        public readonly cDataHistoryNxtEngine Server;
        public readonly string NXQL;

        public cCsvFile Result = null;
        public bool finished = false;

        private cF4sdWebRequestInfo requestInfo;
        private int LogDeep;

        public cNxqlDataCollectorSubTable(string NXQL, cDataHistoryConfigSubTable SubTable, cNxqlDataCollectorTable TableCollector, cDataHistoryNxtEngine Server, cF4sdWebRequestInfo requestInfo, int LogDeep)
        {
            this.NXQL = NXQL;
            this.SubTable = SubTable;
            this.TableCollector = TableCollector;
            this.Server = Server;
            this.requestInfo = requestInfo;
            this.LogDeep = LogDeep;
            TableCollector.RegisterSubTableCollector(this);
        }

        private void LogContent(string Content)
        {
            var Lines = Content.Split('\n');
            var arrDebug = new List<string>(5)
                {
                    $"NXQL query for sub-table '{SubTable.Name}': {NXQL}",
                    "Resulting lines: " + Lines.Length.ToString()
                };
            string str = "Line1: ";
            if (Lines.Length >= 1)
                str += Lines[0].Substring(0, Math.Min(255, Lines[0].Length));
            arrDebug.Add(str);
            str = "Line2: ";
            if (Lines.Length >= 2)
                str += Lines[1].Substring(0, Math.Min(255, Lines[1].Length));
            arrDebug.Add(str);
            str = "Line3: ";
            if (Lines.Length >= 3)
                str += Lines[2].Substring(0, Math.Min(255, Lines[2].Length));
            arrDebug.Add(str);
            cLogManager.DefaultLogger.LogList(LogLevels.Debug, arrDebug);
        }

        public async Task PostProcessing(string Content, CancellationToken Token)
        {

            MethodBase CM = null; if (cLogManager.DefaultLogger.IsDebug) { CM = MethodBase.GetCurrentMethod(); LogMethodBegin(CM); }
            if (cPerformanceLogger.IsActive && requestInfo != null) { if (CM == null) CM = MethodBase.GetCurrentMethod(); cPerformanceLogger.LogPerformanceStart(LogDeep, CM, requestInfo.id, requestInfo.created); }
            var _startTime = DateTime.UtcNow;

            try
            {
                if (TableCollector.DataCollector.FileSavePath != null)
                {
                    try
                    {
                        var fn = Path.Combine(TableCollector.DataCollector.FileSavePath, $"NXQL-{SubTable.Name}-{Server.Name}({Server.Address}).txt");
                        File.WriteAllText(fn, Content, Encoding.UTF8);
                    }
                    catch (Exception E)
                    {
                        LogException(E);
                    }
                }
                var csvResult = new cCsvFile();
                if (cLogManager.DefaultLogger.IsDebug)
                    LogContent(Content);

                if (csvResult.ImportFromString(Content))
                {
                    Result = csvResult;

                    var lines = Result.Data.Count;
                    TableCollector?.DataCollector?.Collector?.DoProcessUiMessage(0, $"NXQL query for sub-table {SubTable.Name} on server {Server.Name}({Server.Address}) returned {lines} lines");

                    await TableCollector.ProcessResult(SubTable.Name, requestInfo, LogDeep + 1, Token);
                }
                else
                {
                    TableCollector.DataCollector.errorResultCount++;
                    LogEntry($"Could not convert result of NXQL query for subtable {SubTable.Name} on engine {Server.Name}({Server.Address}) to csv.", LogLevels.Error);
                }
            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                if (cPerformanceLogger.IsActive && requestInfo != null) { cPerformanceLogger.LogPerformanceEnd(LogDeep, CM, requestInfo.id, requestInfo.created, _startTime); }
                if (CM != null) LogMethodEnd(CM);
            }


        }

        public async Task ErrorProcessing(string Content, CancellationToken Token)
        {
            TableCollector.DataCollector.errorResultCount++;
            await Task.CompletedTask;
            LogEntry($"Could not get result of NXQL query for subtable {SubTable.Name} on engine {Server.Name}({Server.Address})", LogLevels.Error);
        }


    }

    public class cNxqlColumnInfo
    {
        public cDataHistoryConfigColumnBase Column;
        public int ColIndex;
        public string StaticValue;
    }

    public class cNxqlAggregationEntry
    {
        internal cDataHistoryConfigColumn Column;

        internal object LatestValue = null;
        internal DateTime? LastestEvent = null;

        internal Int64? aggInt = null;
        internal Double? aggDouble = null;
        internal Double? weightDouble = null;
        internal String aggString = null;
        internal List<string> listString = null;
        internal Boolean? aggBoolean = null;
        internal DateTime? aggEvent = null;
        internal DateTime? aggTime = null;
        internal Version aggVersion = null;

        internal cNxqlAggregationEntry(cDataHistoryConfigColumn Column)
        {
            this.Column = Column;
        }

        private static string getStingList(List<string> L)
        {
            if (L == null || L.Count == 0)
                return null;

            var RetVal = "";
            foreach (var Entry in L)
            {
                if (string.IsNullOrEmpty(Entry))
                    continue;
                if (RetVal != "")
                    RetVal += ',';
                RetVal += Entry;
            }
            return RetVal;
        }

        internal void addValue(object Value, DateTime? EventTime2)
        {
            try
            {
                DateTime EventTime;
                if (EventTime2 == null)
                    EventTime = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
                else
                    EventTime = (DateTime)EventTime2;

                switch (Column.AggregationType)
                {
                    case eDataHistoryAggregationType.average:
                        if (Value != null)
                        {
                            switch (Column.ValueType)
                            {
                                case enumFasdValueType.INT:
                                case enumFasdValueType.BIGINT:
                                case enumFasdValueType.FLOAT:
                                    var val01 = Convert.ToDouble(Value);
                                    if (weightDouble == null)
                                        weightDouble = 1;
                                    else
                                        weightDouble++;
                                    if (aggDouble == null)
                                        aggDouble = val01;
                                    else
                                        aggDouble += val01;
                                    return;
                            }
                        }
                        return;
                    case eDataHistoryAggregationType.latestTime:
                        if (aggEvent == null || aggEvent < EventTime)
                            aggEvent = EventTime;
                        return;
                    case eDataHistoryAggregationType.count:
                        if (aggInt == null)
                            aggInt = 1;
                        else
                            aggInt++;
                        return;
                    case eDataHistoryAggregationType.valuecount:
                        if (Value != null)
                        {
                            if (aggInt == null)
                                aggInt = 1;
                            else
                                aggInt++;
                        }
                        return;
                }

                switch (Column.ValueType)
                {
                    case enumFasdValueType.INT:
                    case enumFasdValueType.BIGINT:
                        if (Value != null)
                        {
                            var val02 = Convert.ToInt64(Value);
                            switch (Column.AggregationType)
                            {
                                case eDataHistoryAggregationType.min:
                                    if (aggInt == null)
                                        aggInt = val02;
                                    else
                                        aggInt = Math.Min((Int64)aggInt, val02);
                                    return;
                                case eDataHistoryAggregationType.max:
                                    if (aggInt == null)
                                        aggInt = val02;
                                    else
                                        aggInt = Math.Max((Int64)aggInt, val02);
                                    return;
                                case eDataHistoryAggregationType.sum:
                                    if (aggInt == null)
                                        aggInt = val02;
                                    else
                                        aggInt += val02;
                                    return;
                                case eDataHistoryAggregationType.first:
                                case eDataHistoryAggregationType.Unknown:
                                    if (aggEvent == null)
                                    {
                                        aggEvent = EventTime;
                                        aggInt = val02;
                                    }
                                    else
                                    {
                                        if (EventTime < aggEvent)
                                        {
                                            aggEvent = EventTime;
                                            aggInt = val02;
                                        }
                                    }
                                    return;
                                case eDataHistoryAggregationType.latestValue:
                                    if (aggEvent == null)
                                    {
                                        aggEvent = EventTime;
                                        aggInt = val02;
                                    }
                                    else
                                    {
                                        if (EventTime >= aggEvent)
                                        {
                                            aggEvent = EventTime;
                                            aggInt = val02;
                                        }
                                    }
                                    return;
                            }
                        }
                        return;
                    case enumFasdValueType.FLOAT:
                        if (Value != null)
                        {
                            var val03 = Convert.ToDouble(Value);
                            switch (Column.AggregationType)
                            {
                                case eDataHistoryAggregationType.min:
                                    if (aggDouble == null)
                                        aggDouble = val03;
                                    else
                                        aggDouble = Math.Min((Double)aggDouble, val03);
                                    return;
                                case eDataHistoryAggregationType.max:
                                    if (aggDouble == null)
                                        aggDouble = val03;
                                    else
                                        aggDouble = Math.Max((Double)aggDouble, val03);
                                    return;
                                case eDataHistoryAggregationType.sum:
                                    if (aggDouble == null)
                                        aggDouble = val03;
                                    else
                                        aggDouble += val03;
                                    return;
                                case eDataHistoryAggregationType.first:
                                case eDataHistoryAggregationType.Unknown:
                                    if (aggEvent == null)
                                    {
                                        aggEvent = EventTime;
                                        aggDouble = val03;
                                    }
                                    else
                                    {
                                        if (EventTime < aggEvent)
                                        {
                                            aggEvent = EventTime;
                                            aggDouble = val03;
                                        }
                                    }
                                    return;
                                case eDataHistoryAggregationType.latestValue:
                                    if (aggEvent == null)
                                    {
                                        aggEvent = EventTime;
                                        aggDouble = val03;
                                    }
                                    else
                                    {
                                        if (EventTime >= aggEvent)
                                        {
                                            aggEvent = EventTime;
                                            aggDouble = val03;
                                        }
                                    }
                                    return;
                            }
                        }
                        return;
                    case enumFasdValueType.DATETIME:
                        if (Value != null)
                        {
                            var val04 = Convert.ToDateTime(Value);
                            switch (Column.AggregationType)
                            {
                                case eDataHistoryAggregationType.min:
                                    if (aggTime == null)
                                        aggTime = val04;
                                    else if (val04 < aggTime)
                                        aggTime = val04;
                                    return;
                                case eDataHistoryAggregationType.max:
                                    if (aggTime == null)
                                        aggTime = val04;
                                    else if (val04 > aggTime)
                                        aggTime = val04;
                                    return;
                                case eDataHistoryAggregationType.sum:
                                    return;
                                case eDataHistoryAggregationType.first:
                                case eDataHistoryAggregationType.Unknown:
                                    if (aggEvent == null)
                                    {
                                        aggEvent = EventTime;
                                        aggTime = val04;
                                    }
                                    else
                                    {
                                        if (EventTime < aggEvent)
                                        {
                                            aggEvent = EventTime;
                                            aggTime = val04;
                                        }
                                    }
                                    return;
                                case eDataHistoryAggregationType.latestValue:
                                    if (aggEvent == null)
                                    {
                                        aggEvent = EventTime;
                                        aggTime = val04;
                                    }
                                    else
                                    {
                                        if (EventTime >= aggEvent)
                                        {
                                            aggEvent = EventTime;
                                            aggTime = val04;
                                        }
                                    }
                                    return;
                            }
                        }
                        return;
                    case enumFasdValueType.VERSION:
                        if (Value != null && Version.TryParse(Value.ToString(), out var val05))
                        {
                            switch (Column.AggregationType)
                            {
                                case eDataHistoryAggregationType.min:
                                    if (aggVersion == null)
                                        aggVersion = val05;
                                    else if (val05 < aggVersion)
                                        aggVersion = val05;
                                    return;
                                case eDataHistoryAggregationType.max:
                                    if (aggVersion == null)
                                        aggVersion = val05;
                                    else if (val05 > aggVersion)
                                        aggVersion = val05;
                                    return;
                                case eDataHistoryAggregationType.sum:
                                    var strVal05 = val05.ToString();
                                    if (listString == null)
                                        listString = new List<string>() { strVal05 };
                                    else if (listString.Count < 32 && !listString.Contains(strVal05))
                                        listString.Add(strVal05);
                                    return;
                                case eDataHistoryAggregationType.first:
                                case eDataHistoryAggregationType.Unknown:
                                    if (aggEvent == null)
                                    {
                                        aggEvent = EventTime;
                                        aggVersion = val05;
                                    }
                                    else
                                    {
                                        if (EventTime < aggEvent)
                                        {
                                            aggEvent = EventTime;
                                            aggVersion = val05;
                                        }
                                    }
                                    return;
                                case eDataHistoryAggregationType.latestValue:
                                    if (aggEvent == null)
                                    {
                                        aggEvent = EventTime;
                                        aggVersion = val05;
                                    }
                                    else
                                    {
                                        if (EventTime >= aggEvent)
                                        {
                                            aggEvent = EventTime;
                                            aggVersion = val05;
                                        }
                                    }
                                    return;
                            }
                        }
                        return;
                    case enumFasdValueType.BOOLEAN:
                        if (Value == null)
                            return;

                        bool val06 = false;
                        if (Value is bool v)
                            val06 = v;
                        else if (Value is string val06Str)
                        {
                            switch (val06Str.Trim().ToLower())
                            {
                                case "1":
                                case "true":
                                case "yes":
                                    val06 = true;
                                    break;
                                case "0":
                                case "false":
                                case "no":
                                    break;
                                default:
                                    return;
                            }
                        }
                        else if (Value is sbyte
                                || Value is byte
                                || Value is short
                                || Value is ushort
                                || Value is int
                                || Value is uint
                                || Value is long
                                || Value is ulong)
                        {
                            var val06Int = Convert.ToInt64(Value);
                            if (val06Int == 0) { }
                            else if (val06Int == 1)
                                val06 = true;
                            return;
                        }
                        else if (Value is float
                                || Value is double
                                || Value is decimal)
                        {
                            var val06Double = Convert.ToDouble(Value);
                            if (val06Double == 0) { }
                            else if (val06Double == 1)
                                val06 = true;
                            else
                                return;
                        }
                        else
                            return;

                        switch (Column.AggregationType)
                        {
                            case eDataHistoryAggregationType.min:
                                if (aggBoolean == null)
                                    aggBoolean = val06;
                                else
                                    aggBoolean &= val06;
                                return;
                            case eDataHistoryAggregationType.max:
                                if (aggBoolean == null)
                                    aggBoolean = val06;
                                else
                                    aggBoolean |= val06;
                                return;
                            case eDataHistoryAggregationType.sum:
                                return;
                            case eDataHistoryAggregationType.first:
                            case eDataHistoryAggregationType.Unknown:
                                if (aggEvent == null)
                                {
                                    aggEvent = EventTime;
                                    aggBoolean = val06;
                                }
                                else
                                {
                                    if (EventTime < aggEvent)
                                    {
                                        aggEvent = EventTime;
                                        aggBoolean = val06;
                                    }
                                }
                                return;
                            case eDataHistoryAggregationType.latestValue:
                                if (aggEvent == null)
                                {
                                    aggEvent = EventTime;
                                    aggBoolean = val06;
                                }
                                else
                                {
                                    if (EventTime >= aggEvent)
                                    {
                                        aggEvent = EventTime;
                                        aggBoolean = val06;
                                    }
                                }
                                return;
                        }
                        return;
                    default:
                        if (Value == null)
                            return;
                        var val07 = Value.ToString();
                        if (val07 == "")
                            return;
                        switch (Column.AggregationType)
                        {
                            case eDataHistoryAggregationType.sum:
                                if (listString == null)
                                    listString = new List<string>() { val07 };
                                else if (listString.Count < 32 && !listString.Contains(val07))
                                    listString.Add(val07);
                                return;
                            case eDataHistoryAggregationType.first:
                            case eDataHistoryAggregationType.Unknown:
                                if (aggEvent == null)
                                {
                                    aggEvent = EventTime;
                                    aggString = val07;
                                }
                                else
                                {
                                    if (EventTime < aggEvent)
                                    {
                                        aggEvent = EventTime;
                                        aggString = val07;
                                    }
                                }
                                return;
                            case eDataHistoryAggregationType.latestValue:
                                if (aggEvent == null)
                                {
                                    aggEvent = EventTime;
                                    aggString = val07;
                                }
                                else
                                {
                                    if (EventTime >= aggEvent)
                                    {
                                        aggEvent = EventTime;
                                        aggString = val07;
                                    }
                                }
                                return;
                        }
                        return;
                }
            }
            catch (Exception E)
            {
                LogException(E);
            }
        }

        public object getValue()
        {
            try
            {
                switch (Column.AggregationType)
                {
                    case eDataHistoryAggregationType.average:
                        switch (Column.ValueType)
                        {
                            case enumFasdValueType.INT:
                            case enumFasdValueType.BIGINT:
                            case enumFasdValueType.FLOAT:
                                if (weightDouble == 0 || aggDouble == null)
                                    return null;
                                else
                                    return (aggDouble / weightDouble);
                        }
                        return null;
                    case eDataHistoryAggregationType.latestTime:
                        return aggEvent;
                    case eDataHistoryAggregationType.count:
                    case eDataHistoryAggregationType.valuecount:
                        return aggInt;
                }

                switch (Column.ValueType)
                {
                    case enumFasdValueType.INT:
                    case enumFasdValueType.BIGINT:
                        switch (Column.AggregationType)
                        {
                            case eDataHistoryAggregationType.min:
                            case eDataHistoryAggregationType.max:
                            case eDataHistoryAggregationType.sum:
                            case eDataHistoryAggregationType.first:
                            case eDataHistoryAggregationType.latestValue:
                            case eDataHistoryAggregationType.Unknown:
                                if (Column.ValueType == enumFasdValueType.INT)
                                    return Convert.ToInt32(aggInt);
                                return aggInt;
                        }
                        return null;
                    case enumFasdValueType.FLOAT:
                        switch (Column.AggregationType)
                        {
                            case eDataHistoryAggregationType.min:
                            case eDataHistoryAggregationType.max:
                            case eDataHistoryAggregationType.sum:
                            case eDataHistoryAggregationType.first:
                            case eDataHistoryAggregationType.latestValue:
                            case eDataHistoryAggregationType.Unknown:
                                return Convert.ToSingle(aggDouble);
                        }
                        return null;
                    case enumFasdValueType.DATETIME:
                        switch (Column.AggregationType)
                        {
                            case eDataHistoryAggregationType.min:
                            case eDataHistoryAggregationType.max:
                            case eDataHistoryAggregationType.first:
                            case eDataHistoryAggregationType.latestValue:
                            case eDataHistoryAggregationType.Unknown:
                                return aggTime;
                        }
                        return null;
                    case enumFasdValueType.VERSION:
                        switch (Column.AggregationType)
                        {
                            case eDataHistoryAggregationType.min:
                            case eDataHistoryAggregationType.max:
                            case eDataHistoryAggregationType.first:
                            case eDataHistoryAggregationType.latestValue:
                            case eDataHistoryAggregationType.Unknown:
                                return aggVersion;
                            case eDataHistoryAggregationType.sum:
                                return getStingList(listString);
                        }
                        return null;
                    case enumFasdValueType.BOOLEAN:
                        switch (Column.AggregationType)
                        {
                            case eDataHistoryAggregationType.min:
                            case eDataHistoryAggregationType.max:
                            case eDataHistoryAggregationType.first:
                            case eDataHistoryAggregationType.latestValue:
                            case eDataHistoryAggregationType.Unknown:
                                return aggBoolean;
                        }
                        return null;
                    default:
                        switch (Column.AggregationType)
                        {
                            case eDataHistoryAggregationType.first:
                            case eDataHistoryAggregationType.latestValue:
                            case eDataHistoryAggregationType.Unknown:
                                return aggString;
                            case eDataHistoryAggregationType.sum:
                                return getStingList(listString);
                        }
                        return null;
                }

            }
            catch (Exception E)
            {
                LogException(E);
            }

            return null;
        }
    }

}