VRBoardDbHelper.cs 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279
  1. using Application.VaporRecoveryOnlineWatchHubApp.UnversalApiModels;
  2. using AutoMapper;
  3. using Edge.Core.Database;
  4. using Edge.Core.Database.Models;
  5. using Microsoft.EntityFrameworkCore;
  6. using Microsoft.Extensions.DependencyInjection;
  7. using Microsoft.Extensions.Logging;
  8. using Microsoft.Extensions.Logging.Abstractions;
  9. using System;
  10. using System.Collections.Generic;
  11. using System.Diagnostics.CodeAnalysis;
  12. using System.Linq;
  13. using System.Text;
  14. using System.Threading.Tasks;
  15. using VaporRecoveryOnlineWatchHubApp.Config;
  16. using VaporRecoveryOnlineWatchHubApp.UnversalApiModels;
  17. namespace VaporRecoveryOnlineWatchHubApp
  18. {
  19. public class VRBoardDbHelper
  20. {
  21. public IServiceProvider Services { get; }
  22. private IMapper objMapper;
  23. public ILogger Logger { get; } = NullLogger.Instance;
  24. public VRBoardDbHelper(IServiceProvider services)
  25. {
  26. this.Services = services;
  27. var loggerFactory = services.GetRequiredService<ILoggerFactory>();
  28. Logger = loggerFactory.CreateLogger("Application");
  29. objMapper = this.Services.GetRequiredService<IMapper>();
  30. }
  31. /// <summary>
  32. /// Get the latest single flow data for each nozzles(by site level nozzle id).
  33. /// </summary>
  34. /// <returns></returns>
  35. public async Task<IEnumerable<VRBoardNozzleTrxFlowData>> GetLatestNozzlesTrxFlowDatas()
  36. {
  37. using (var scope = this.Services.CreateScope())
  38. {
  39. var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
  40. var data = await dbContext.GenericDatas.Where(gd =>
  41. gd.Type == AutoMapperProfile.VRBoardNozzleTrxFlowData_MapToDbEntity_Type
  42. && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner)
  43. //.ForMember(dest => dest.SiteLevelNozzleId, opt => opt.MapFrom(src => src.IntProperty3))
  44. .GroupBy(gd => gd.IntProperty3)
  45. .Select(g => new { SiteLevelNozzleId = g.Key, LastCreatedTimestamp = g.Max(g => g.CreatedTimeStamp) })
  46. .Join(dbContext.GenericDatas.Where(gd =>
  47. gd.Type == AutoMapperProfile.VRBoardNozzleTrxFlowData_MapToDbEntity_Type
  48. && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner),
  49. outer => outer,
  50. inner => new { SiteLevelNozzleId = inner.IntProperty3, LastCreatedTimestamp = inner.CreatedTimeStamp },
  51. (outer, inner) => inner).ToListAsync();
  52. return objMapper.Map<IEnumerable<VRBoardNozzleTrxFlowData>>(data);
  53. }
  54. }
  55. public async Task<IEnumerable<VRBoardNozzleTrxFlowData>> GetNozzlesTrxFlowDatasByTimeRange((DateTime startTime, DateTime endTime) timeRange)
  56. {
  57. using (var scope = this.Services.CreateScope())
  58. {
  59. var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
  60. var data = await dbContext.GenericDatas.Where(gd =>
  61. gd.Type == AutoMapperProfile.VRBoardNozzleTrxFlowData_MapToDbEntity_Type
  62. && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
  63. && gd.CreatedTimeStamp >= timeRange.startTime
  64. && gd.CreatedTimeStamp <= timeRange.endTime).ToListAsync();
  65. return objMapper.Map<IEnumerable<VRBoardNozzleTrxFlowData>>(data);
  66. //return objMapper.Map<IEnumerable<VRBoardNozzleFlowData>>(
  67. // dbContext.GenericDatas.AsEnumerable()
  68. // .Where(i => i.Type == AutoMapperProfile.VRBoardNozzleTrxFlowData_MapToDbEntity_Type
  69. // && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
  70. // && i.CreatedTimeStamp >= timeRange.startTime
  71. // && i.CreatedTimeStamp <= timeRange.endTime))?.ToList();
  72. }
  73. }
  74. public async Task<IEnumerable<VRBoardAlarmRecord>> GetWarningOrAlarmStateRecordsByTimeRange((DateTime startTime, DateTime endTime) timeRange, int siteLevelNozzleId = 0)
  75. {
  76. using (var scope = this.Services.CreateScope())
  77. {
  78. var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
  79. List<GenericData> data;
  80. if (siteLevelNozzleId != 0)
  81. data = await dbContext.GenericDatas.Where(i =>
  82. i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
  83. && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
  84. && i.IntProperty0 == siteLevelNozzleId
  85. //dest.AlarmType, Warning=1, Alarm=2; opt => opt.MapFrom(src => src.IntProperty1)
  86. && i.IntProperty1 != 0
  87. && i.TimeStampProperty0 >= timeRange.startTime
  88. && i.TimeStampProperty0 <= timeRange.endTime).ToListAsync();
  89. else
  90. data = await dbContext.GenericDatas.Where(i =>
  91. i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
  92. && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
  93. //dest.AlarmType, Warning=1, Alarm=2; opt => opt.MapFrom(src => src.IntProperty1)
  94. && i.IntProperty1 != 0
  95. && i.TimeStampProperty0 >= timeRange.startTime
  96. && i.TimeStampProperty0 <= timeRange.endTime).ToListAsync();
  97. return this.objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(data);
  98. //return this.objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(
  99. // dbContext.GenericDatas.AsEnumerable()
  100. // .Where(i => i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
  101. // && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
  102. // && (nozzleId == 0 ? true : objMapper.Map<VRBoardAlarmRecord>(i).SiteLevelNozzleId == nozzleId)
  103. // && objMapper.Map<VRBoardAlarmRecord>(i).AlarmType != VRBoardAlarmType.NONE
  104. // && objMapper.Map<VRBoardAlarmRecord>(i).AlarmTime >= timeRange.startTime
  105. // && objMapper.Map<VRBoardAlarmRecord>(i).AlarmTime <= timeRange.endTime)).ToList();
  106. }
  107. }
  108. public async Task<IEnumerable<VRBoardNozzleInitParametersData>> GetLatestPersistBoardNozzleInitParameters()
  109. {
  110. using (var scope = this.Services.CreateScope())
  111. {
  112. var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
  113. var data = await dbContext.GenericDatas.Where(gd =>
  114. gd.Type == AutoMapperProfile.VRBoardNozzleInitParametersData_MapToDbEntity_Type
  115. && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner)
  116. //.ForMember(dest => dest.SiteLevelNozzleId, opt => opt.MapFrom(src => src.IntProperty0))
  117. .GroupBy(gd => gd.IntProperty0)
  118. //.ForMember(dest => dest.TimeStamp, opt => opt.MapFrom(src => src.CreatedTimeStamp))
  119. .Select(g => new { SiteLevelNozzleId = g.Key, LastCreatedTimestamp = g.Max(g => g.CreatedTimeStamp) })
  120. .Join(dbContext.GenericDatas.Where(gd =>
  121. gd.Type == AutoMapperProfile.VRBoardNozzleInitParametersData_MapToDbEntity_Type
  122. && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner),
  123. outer => outer,
  124. inner => new { SiteLevelNozzleId = inner.IntProperty0, LastCreatedTimestamp = inner.CreatedTimeStamp },
  125. (outer, inner) => inner).ToListAsync();
  126. return objMapper.Map<IEnumerable<VRBoardNozzleInitParametersData>>(data);
  127. }
  128. }
  129. public async Task<IEnumerable<VRBoardAlarmRecord>> GetLatestNozzlesAlarms()
  130. {
  131. using (var scope = this.Services.CreateScope())
  132. {
  133. var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
  134. var data = await dbContext.GenericDatas.Where(gd =>
  135. gd.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
  136. && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner)
  137. //.ForMember(dest => dest.SiteLevelNozzleId, opt => opt.MapFrom(src => src.IntProperty0))
  138. .GroupBy(gd => gd.IntProperty0)
  139. //.ForMember(dest => dest.AlarmTime, opt => opt.MapFrom(src => src.TimeStampProperty0))
  140. .Select(g => new { SiteLevelNozzleId = g.Key, LastCreatedTimestamp = g.Max(g => g.TimeStampProperty0) })
  141. .Join(dbContext.GenericDatas.Where(gd =>
  142. gd.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
  143. && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner),
  144. outer => outer,
  145. inner => new { SiteLevelNozzleId = inner.IntProperty0, LastCreatedTimestamp = inner.TimeStampProperty0 },
  146. (outer, inner) => inner).ToListAsync();
  147. return objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(data);
  148. //return objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(
  149. // dbContext.GenericDatas.AsEnumerable()
  150. // .Where(i => i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
  151. // && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner).ToList()
  152. // .GroupBy(i => objMapper.Map<VRBoardAlarmRecord>(i).SiteLevelNozzleId)
  153. // .Select(g => g.OrderByDescending(i => i.CreatedTimeStamp)).FirstOrDefault()).ToList();
  154. }
  155. }
  156. /// <summary>
  157. /// return latest 90 days alarms.
  158. /// </summary>
  159. /// <returns></returns>
  160. public async Task<IEnumerable<VRBoardAlarmRecord>> GetAllAlarms()
  161. {
  162. using (var scope = this.Services.CreateScope())
  163. {
  164. var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
  165. //for limit the range of data
  166. var due = DateTime.Now.Subtract(new TimeSpan(90, 0, 0, 0));
  167. var data = await dbContext.GenericDatas.Where(i =>
  168. i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
  169. && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
  170. //dest.AlarmType, opt => opt.MapFrom(src => src.IntProperty1)
  171. && i.IntProperty1 != 0
  172. && i.TimeStampProperty0 >= due).ToListAsync();
  173. return this.objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(data);
  174. //return this.objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(
  175. // dbContext.GenericDatas.AsEnumerable()
  176. // .Where(i => i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
  177. // && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
  178. // && objMapper.Map<VRBoardAlarmRecord>(i).AlarmType != VRBoardAlarmType.NONE
  179. // && objMapper.Map<VRBoardAlarmRecord>(i).Acknowledged == 0)).ToList();
  180. }
  181. }
  182. public async Task<IEnumerable<VRBoardAlarmRecord>> GetAlarms(DateTime start, DateTime end, int siteLevelNozzleId = 0, int rowCount = 100)
  183. {
  184. using (var scope = this.Services.CreateScope())
  185. {
  186. var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
  187. List<GenericData> data;
  188. if (siteLevelNozzleId != 0)
  189. data = await dbContext.GenericDatas.Where(i =>
  190. i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
  191. && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
  192. && i.IntProperty0 == siteLevelNozzleId
  193. //dest.AlarmType, opt => opt.MapFrom(src => src.IntProperty1)
  194. && i.IntProperty1 != 0
  195. && i.TimeStampProperty0 >= start
  196. && i.TimeStampProperty0 <= end
  197. && i.IntProperty1 != 0).Take(rowCount).ToListAsync();
  198. else
  199. data = await dbContext.GenericDatas.Where(i =>
  200. i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
  201. && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
  202. //dest.AlarmType, opt => opt.MapFrom(src => src.IntProperty1)
  203. && i.IntProperty1 != 0
  204. && i.TimeStampProperty0 >= start
  205. && i.TimeStampProperty0 <= end
  206. && i.IntProperty1 != 0).Take(rowCount).ToListAsync();
  207. return this.objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(data);
  208. //return this.objMapper.Map<IEnumerable<VRBoardAlarmRecord>>(
  209. // dbContext.GenericDatas.AsEnumerable()
  210. // .Where(i => i.Type == AutoMapperProfile.VRBoardAlarmRecord_MapToDbEntity_Type
  211. // && i.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
  212. // && objMapper.Map<VRBoardAlarmRecord>(i).AlarmTime <= end
  213. // && objMapper.Map<VRBoardAlarmRecord>(i).AlarmTime >= start
  214. // && objMapper.Map<VRBoardAlarmRecord>(i).AlarmType != VRBoardAlarmType.NONE
  215. // && (siteLevelNozzleId == 0 ? true : objMapper.Map<VRBoardAlarmRecord>(i).SiteLevelNozzleId == siteLevelNozzleId)
  216. // && objMapper.Map<VRBoardAlarmRecord>(i).Acknowledged == 0).ToList()
  217. // .OrderByDescending(gd => gd.CreatedTimeStamp).Take(rowCount)).ToList();
  218. }
  219. }
  220. public async Task<IEnumerable<VRBoardNozzleTrxFlowData>> GetNozzlesTrxFlowData(DateTime start, DateTime end,
  221. int siteLevelNozzleId, int pageIndex, int singlePageRowCount,
  222. string tankPressure, string liquidPressure, string gasConcentrations)
  223. {
  224. using (var scope = this.Services.CreateScope())
  225. {
  226. var dbContext = scope.ServiceProvider.GetRequiredService<SqliteDbContext>();
  227. var genericDatas = dbContext.GenericDatas.Where(gd =>
  228. gd.Type == AutoMapperProfile.VRBoardNozzleTrxFlowData_MapToDbEntity_Type
  229. && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
  230. && gd.TimeStampProperty0 >= start
  231. && gd.TimeStampProperty0 <= end).OrderByDescending(m => m.TimeStampProperty0);
  232. if (siteLevelNozzleId != 0)
  233. genericDatas = genericDatas.Where(gd => gd.IntProperty3 == siteLevelNozzleId).OrderByDescending(m => m.TimeStampProperty0);
  234. if (!string.IsNullOrEmpty(tankPressure))
  235. {
  236. var tps = tankPressure.Split('~');
  237. genericDatas = genericDatas.Where(gd => gd.DoubleProperty6 > double.Parse(tps[0]) && gd.DoubleProperty6 < double.Parse(tps[1])).OrderByDescending(m => m.TimeStampProperty0);
  238. }
  239. if (!string.IsNullOrEmpty(liquidPressure))
  240. {
  241. var lps = liquidPressure.Split('~');
  242. genericDatas = genericDatas.Where(gd => gd.DoubleProperty7 > double.Parse(lps[0]) && gd.DoubleProperty7 < double.Parse(lps[1])).OrderByDescending(m => m.TimeStampProperty0);
  243. }
  244. if (!string.IsNullOrEmpty(gasConcentrations))
  245. {
  246. var gcs = gasConcentrations.Split('~');
  247. genericDatas = genericDatas.Where(gd => gd.DoubleProperty8 > double.Parse(gcs[0]) && gd.DoubleProperty8 < double.Parse(gcs[1])).OrderByDescending(m => m.TimeStampProperty0);
  248. }
  249. var pagedData = await genericDatas.Skip(pageIndex * singlePageRowCount).Take(singlePageRowCount).ToListAsync();
  250. //int count = data.Count - pageIndex;
  251. //singlePageRowCount = count > singlePageRowCount ? singlePageRowCount : count;
  252. return this.objMapper.Map<IEnumerable<VRBoardNozzleTrxFlowData>>(pagedData);
  253. //var data = this.objMapper.Map<IEnumerable<VRBoardNozzleFlowData>>(
  254. // dbContext.GenericDatas.AsEnumerable().Where(gd =>
  255. // gd.Type == AutoMapperProfile.VRBoardNozzleTrxFlowData_MapToDbEntity_Type
  256. // && gd.Owner == AutoMapperProfile.VaporRecoveryOnlineWatchHubApp_MapToDbEntity_Owner
  257. // && objMapper.Map<VRBoardNozzleFlowData>(gd).TimeStamp <= end
  258. // && objMapper.Map<VRBoardNozzleFlowData>(gd).TimeStamp >= start
  259. // && (siteLevelNozzleId == 0 ? true : objMapper.Map<VRBoardNozzleFlowData>(gd).SiteLevelNozzleId == siteLevelNozzleId))
  260. // .OrderByDescending(gd => gd.TimeStampProperty0).Take(takeCount)).ToList();
  261. //return data;
  262. }
  263. }
  264. }
  265. }