FCC/Applications/VaporRecoveryOnlineWatchProject/VaporRecoveryOnlineWatchHubApp/Util/VRBoardDbHelper.cs

using Application.VaporRecoveryOnlineWatchHubApp.UnversalApiModels;
using AutoMapper;
using Edge.Core.Database;
using Edge.Core.Database.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Abstractions;
using System;
using System.Collections.Generic;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VaporRecoveryOnlineWatchHubApp.Config;
using VaporRecoveryOnlineWatchHubApp.UnversalApiModels;

namespace VaporRecoveryOnlineWatchHubApp
{
    public class VRBoardDbHelper
    {
        public IServiceProvider Services { get; }
        private IMapper objMapper;
        public ILogger Logger { get; } = NullLogger.Instance;

        public VRBoardDbHelper(IServiceProvider services)
        {
            this.Services = services;
            var loggerFactory = services.GetRequiredService<ILoggerFactory>();
            Logger = loggerFactory.CreateLogger("Application");
            objMapper = this.Services.GetRequiredService<IMapper>();
        }

        /// <summary>
        /// Get the latest single flow data for each nozzles(by site level nozzle id).
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<VRBoardNozzleTrxFlowData>> GetLatestNozzlesTrxFlowDatas()
        {
            using (var scope = this.Services.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
                var data = await dbContext.GenericDatas.Where(gd =>
                      gd.Type == AutoMapperProfile.VRBoardNozzleTrxFlowData_MapToDbEntity_Type
                          && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner)
                    //.ForMember(dest => dest.SiteLevelNozzleId, opt => opt.MapFrom(src => src.IntProperty3))
                    .GroupBy(gd => gd.IntProperty3)
                    .Select(g => new { SiteLevelNozzleId = g.Key, LastCreatedTimestamp = g.Max(g => g.CreatedTimeStamp) })
                    .Join(dbContext.GenericDatas.Where(gd =>
                        gd.Type == AutoMapperProfile.VRBoardNozzleTrxFlowData_MapToDbEntity_Type
                            && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner),
                            outer => outer,
                        inner => new { SiteLevelNozzleId = inner.IntProperty3, LastCreatedTimestamp = inner.CreatedTimeStamp },
                        (outer, inner) => inner).ToListAsync();
                return objMapper.Map<IEnumerable<VRBoardNozzleTrxFlowData>>(data);
            }
        }

        public async Task<IEnumerable<VRBoardNozzleTrxFlowData>> GetNozzlesTrxFlowDatasByTimeRange((DateTime startTime, DateTime endTime) timeRange)
        {
            using (var scope = this.Services.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
                var data = await dbContext.GenericDatas.Where(gd =>
                    gd.Type == AutoMapperProfile.VRBoardNozzleTrxFlowData_MapToDbEntity_Type
                        && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
                        && gd.CreatedTimeStamp >= timeRange.startTime
                        && gd.CreatedTimeStamp <= timeRange.endTime).ToListAsync();
                return objMapper.Map<IEnumerable<VRBoardNozzleTrxFlowData>>(data);
                //return objMapper.Map<IEnumerable<VRBoardNozzleFlowData>>(
                //    dbContext.GenericDatas.AsEnumerable()
                //    .Where(i => i.Type == AutoMapperProfile.VRBoardNozzleTrxFlowData_MapToDbEntity_Type
                //        && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
                //        && i.CreatedTimeStamp >= timeRange.startTime
                //        && i.CreatedTimeStamp <= timeRange.endTime))?.ToList();
            }
        }

        public async Task<IEnumerable<VRBoardAlarmRecord>> GetWarningOrAlarmStateRecordsByTimeRange((DateTime startTime, DateTime endTime) timeRange, int siteLevelNozzleId = 0)
        {
            using (var scope = this.Services.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
                List<GenericData> data;
                if (siteLevelNozzleId != 0)
                    data = await dbContext.GenericDatas.Where(i =>
                         i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
                             && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
                             && i.IntProperty0 == siteLevelNozzleId
                             //dest.AlarmType, Warning=1, Alarm=2; opt => opt.MapFrom(src => src.IntProperty1)
                             && i.IntProperty1 != 0
                             && i.TimeStampProperty0 >= timeRange.startTime
                             && i.TimeStampProperty0 <= timeRange.endTime).ToListAsync();
                else
                    data = await dbContext.GenericDatas.Where(i =>
                        i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
                            && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
                            //dest.AlarmType, Warning=1, Alarm=2;  opt => opt.MapFrom(src => src.IntProperty1)
                            && i.IntProperty1 != 0
                            && i.TimeStampProperty0 >= timeRange.startTime
                            && i.TimeStampProperty0 <= timeRange.endTime).ToListAsync();
                return this.objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(data);
                //return this.objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(
                //    dbContext.GenericDatas.AsEnumerable()
                //    .Where(i => i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
                //        && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
                //        && (nozzleId == 0 ? true : objMapper.Map<VRBoardAlarmRecord>(i).SiteLevelNozzleId == nozzleId)
                //        && objMapper.Map<VRBoardAlarmRecord>(i).AlarmType != VRBoardAlarmType.NONE
                //        && objMapper.Map<VRBoardAlarmRecord>(i).AlarmTime >= timeRange.startTime
                //        && objMapper.Map<VRBoardAlarmRecord>(i).AlarmTime <= timeRange.endTime)).ToList();
            }
        }

        public async Task<IEnumerable<VRBoardNozzleInitParametersData>> GetLatestPersistBoardNozzleInitParameters()
        {
            using (var scope = this.Services.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
                var data = await dbContext.GenericDatas.Where(gd =>
                     gd.Type == AutoMapperProfile.VRBoardNozzleInitParametersData_MapToDbEntity_Type
                         && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner)
                   //.ForMember(dest => dest.SiteLevelNozzleId, opt => opt.MapFrom(src => src.IntProperty0))
                   .GroupBy(gd => gd.IntProperty0)
                   //.ForMember(dest => dest.TimeStamp, opt => opt.MapFrom(src => src.CreatedTimeStamp))
                   .Select(g => new { SiteLevelNozzleId = g.Key, LastCreatedTimestamp = g.Max(g => g.CreatedTimeStamp) })
                   .Join(dbContext.GenericDatas.Where(gd =>
                       gd.Type == AutoMapperProfile.VRBoardNozzleInitParametersData_MapToDbEntity_Type
                           && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner),
                           outer => outer,
                       inner => new { SiteLevelNozzleId = inner.IntProperty0, LastCreatedTimestamp = inner.CreatedTimeStamp },
                       (outer, inner) => inner).ToListAsync();
                return objMapper.Map<IEnumerable<VRBoardNozzleInitParametersData>>(data);
            }
        }

        public async Task<IEnumerable<VRBoardAlarmRecord>> GetLatestNozzlesAlarms()
        {
            using (var scope = this.Services.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
                var data = await dbContext.GenericDatas.Where(gd =>
                     gd.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
                         && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner)
                   //.ForMember(dest => dest.SiteLevelNozzleId, opt => opt.MapFrom(src => src.IntProperty0))
                   .GroupBy(gd => gd.IntProperty0)
                   //.ForMember(dest => dest.AlarmTime, opt => opt.MapFrom(src => src.TimeStampProperty0))
                   .Select(g => new { SiteLevelNozzleId = g.Key, LastCreatedTimestamp = g.Max(g => g.TimeStampProperty0) })
                   .Join(dbContext.GenericDatas.Where(gd =>
                       gd.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
                           && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner),
                           outer => outer,
                       inner => new { SiteLevelNozzleId = inner.IntProperty0, LastCreatedTimestamp = inner.TimeStampProperty0 },
                       (outer, inner) => inner).ToListAsync();
                return objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(data);
                //return objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(
                //   dbContext.GenericDatas.AsEnumerable()
                //   .Where(i => i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
                //       && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner).ToList()
                //   .GroupBy(i => objMapper.Map<VRBoardAlarmRecord>(i).SiteLevelNozzleId)
                //   .Select(g => g.OrderByDescending(i => i.CreatedTimeStamp)).FirstOrDefault()).ToList();
            }
        }

        /// <summary>
        /// return latest 90 days alarms.
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<VRBoardAlarmRecord>> GetAllAlarms()
        {
            using (var scope = this.Services.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
                //for limit the range of data
                var due = DateTime.Now.Subtract(new TimeSpan(90, 0, 0, 0));
                var data = await dbContext.GenericDatas.Where(i =>
                        i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
                            && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
                            //dest.AlarmType, opt => opt.MapFrom(src => src.IntProperty1)
                            && i.IntProperty1 != 0
                            && i.TimeStampProperty0 >= due).ToListAsync();
                return this.objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(data);
                //return this.objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(
                //   dbContext.GenericDatas.AsEnumerable()
                //   .Where(i => i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
                //       && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
                //       && objMapper.Map<VRBoardAlarmRecord>(i).AlarmType != VRBoardAlarmType.NONE
                //       && objMapper.Map<VRBoardAlarmRecord>(i).Acknowledged == 0)).ToList();
            }
        }

        public async Task<IEnumerable<VRBoardAlarmRecord>> GetAlarms(DateTime start, DateTime end, int siteLevelNozzleId = 0, int rowCount = 100)
        {
            using (var scope = this.Services.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
                List<GenericData> data;
                if (siteLevelNozzleId != 0)
                    data = await dbContext.GenericDatas.Where(i =>
                        i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
                            && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
                            && i.IntProperty0 == siteLevelNozzleId
                            //dest.AlarmType, opt => opt.MapFrom(src => src.IntProperty1)
                            && i.IntProperty1 != 0
                            && i.TimeStampProperty0 >= start
                            && i.TimeStampProperty0 <= end
                            && i.IntProperty1 != 0).Take(rowCount).ToListAsync();
                else
                    data = await dbContext.GenericDatas.Where(i =>
                        i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
                            && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
                            //dest.AlarmType, opt => opt.MapFrom(src => src.IntProperty1)
                            && i.IntProperty1 != 0
                            && i.TimeStampProperty0 >= start
                            && i.TimeStampProperty0 <= end
                            && i.IntProperty1 != 0).Take(rowCount).ToListAsync();
                return this.objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(data);
                //return this.objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(
                //    dbContext.GenericDatas.AsEnumerable()
                //    .Where(i => i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
                //        && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
                //        && objMapper.Map<VRBoardAlarmRecord>(i).AlarmTime <= end
                //        && objMapper.Map<VRBoardAlarmRecord>(i).AlarmTime >= start
                //        && objMapper.Map<VRBoardAlarmRecord>(i).AlarmType != VRBoardAlarmType.NONE
                //        && (siteLevelNozzleId == 0 ? true : objMapper.Map<VRBoardAlarmRecord>(i).SiteLevelNozzleId == siteLevelNozzleId)
                //        && objMapper.Map<VRBoardAlarmRecord>(i).Acknowledged == 0).ToList()
                //    .OrderByDescending(gd => gd.CreatedTimeStamp).Take(rowCount)).ToList();

            }
        }

        public async Task<IEnumerable<VRBoardNozzleTrxFlowData>> GetNozzlesTrxFlowData(DateTime start, DateTime end,
            int siteLevelNozzleId, int pageIndex, int singlePageRowCount,
            string tankPressure, string liquidPressure, string gasConcentrations)
        {
            using (var scope = this.Services.CreateScope())
            {
                var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
                var genericDatas = dbContext.GenericDatas.Where(gd =>
                        gd.Type == AutoMapperProfile.VRBoardNozzleTrxFlowData_MapToDbEntity_Type
                        && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
                        && gd.TimeStampProperty0 >= start
                        && gd.TimeStampProperty0 <= end).OrderByDescending(m => m.TimeStampProperty0);
                if (siteLevelNozzleId != 0)
                    genericDatas = genericDatas.Where(gd => gd.IntProperty3 == siteLevelNozzleId).OrderByDescending(m => m.TimeStampProperty0);
                if (!string.IsNullOrEmpty(tankPressure))
                {
                    var tps = tankPressure.Split('~');
                    genericDatas = genericDatas.Where(gd => gd.DoubleProperty6 > double.Parse(tps[0]) && gd.DoubleProperty6 < double.Parse(tps[1])).OrderByDescending(m => m.TimeStampProperty0);
                }

                if (!string.IsNullOrEmpty(liquidPressure))
                {
                    var lps = liquidPressure.Split('~');
                    genericDatas = genericDatas.Where(gd => gd.DoubleProperty7 > double.Parse(lps[0]) && gd.DoubleProperty7 < double.Parse(lps[1])).OrderByDescending(m => m.TimeStampProperty0);
                }

                if (!string.IsNullOrEmpty(gasConcentrations))
                {
                    var gcs = gasConcentrations.Split('~');
                    genericDatas = genericDatas.Where(gd => gd.DoubleProperty8 > double.Parse(gcs[0]) && gd.DoubleProperty8 < double.Parse(gcs[1])).OrderByDescending(m => m.TimeStampProperty0);
                }

                var pagedData = await genericDatas.Skip(pageIndex * singlePageRowCount).Take(singlePageRowCount).ToListAsync();
                //int count = data.Count - pageIndex;
                //singlePageRowCount = count > singlePageRowCount ? singlePageRowCount : count;
                return this.objMapper.Map<IEnumerable<VRBoardNozzleTrxFlowData>>(pagedData);
                //var data = this.objMapper.Map<IEnumerable<VRBoardNozzleFlowData>>(
                //    dbContext.GenericDatas.AsEnumerable().Where(gd =>
                //        gd.Type == AutoMapperProfile.VRBoardNozzleTrxFlowData_MapToDbEntity_Type
                //        && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
                //        && objMapper.Map<VRBoardNozzleFlowData>(gd).TimeStamp <= end
                //        && objMapper.Map<VRBoardNozzleFlowData>(gd).TimeStamp >= start
                //        && (siteLevelNozzleId == 0 ? true : objMapper.Map<VRBoardNozzleFlowData>(gd).SiteLevelNozzleId == siteLevelNozzleId))
                //    .OrderByDescending(gd => gd.TimeStampProperty0).Take(takeCount)).ToList();
                //return data;
            }
        }
    }
}