123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279 |
- 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;
- }
- }
- }
- }
|