MainDbService.cs 43 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979
  1. using Dapper;
  2. using EVCB_OCPP.Domain;
  3. using EVCB_OCPP.Domain.ConnectionFactory;
  4. using EVCB_OCPP.Domain.Models.MainDb;
  5. using EVCB_OCPP.WSServer.Helper;
  6. using Microsoft.Data.SqlClient;
  7. using Microsoft.EntityFrameworkCore;
  8. using Microsoft.Extensions.Caching.Memory;
  9. using Microsoft.Extensions.Configuration;
  10. using Microsoft.Extensions.Hosting;
  11. using Microsoft.Extensions.Logging;
  12. using Newtonsoft.Json;
  13. using OCPPPackage.Profiles;
  14. using System.Data;
  15. namespace EVCB_OCPP.WSServer.Service;
  16. public interface IMainDbService
  17. {
  18. Task<string> GetMachineAuthorizationKey(string ChargeBoxId);
  19. Task<string> GetMachineConfiguration(string ChargeBoxId, string configName);
  20. Task<string> GetMachineHeartbeatInterval(string ChargeBoxId);
  21. Task<MachineAndCustomerInfo> GetMachineIdAndCustomerInfo(string ChargeBoxId);
  22. Task<string> GetMachineSecurityProfile(string ChargeBoxId);
  23. Task UpdateMachineBasicInfo(string ChargeBoxId, Machine machine);
  24. Task AddOCMF(Ocmf oCMF);
  25. ValueTask<ConnectorStatus> GetConnectorStatus(string ChargeBoxId, int ConnectorId);
  26. Task UpdateConnectorStatus(string Id, ConnectorStatus connectorStatus);
  27. ValueTask AddConnectorStatus(string ChargeBoxId, byte ConnectorId, DateTime CreatedOn, int Status,
  28. int ChargePointErrorCodeId, string ErrorInfo, string VendorId, string VendorErrorCode);
  29. Task AddServerMessage(ServerMessage message);
  30. Task AddServerMessage(string ChargeBoxId, string OutAction, object OutRequest, string CreatedBy = "", DateTime? CreatedOn = null, string SerialNo = "", string InMessage = "");
  31. ValueTask AddMachineError(byte ConnectorId, DateTime CreatedOn, int Status, string ChargeBoxId, int ErrorCodeId, string ErrorInfo, int PreStatus, string VendorErrorCode, string VendorId);
  32. ValueTask<Customer> GetCustomer(string id);
  33. ValueTask<Customer> GetCustomer(Guid id);
  34. Task<Guid> GetCustomerIdByChargeBoxId(string chargeboxId);
  35. Task<int?> TryGetDuplicatedTransactionId(string chargeBoxId, Guid customerId, int connectorId, DateTime timestamp);
  36. Task<int> AddNewTransactionRecord(TransactionRecord newTransaction);
  37. Task<TransactionRecord> GetTransactionForStopTransaction(int transactionId, string chargeBoxId);
  38. Task UpdateTransaction(int transactionId, int meterStop, DateTime stopTime, int stopReasonId, string stopReason, string stopIdTag, string receipt, int cost);
  39. Task<bool> UpdateHeartBeats(IEnumerable<Machine> heartBeatsData);
  40. Task UpdateTransactionSOC(int id, string startsoc, string stopsoc);
  41. Task UpdateMachineConnectionType(string chargeBoxId, int v);
  42. Task<bool?> GetChargeBoxIdIsAc(string chargeBoxId);
  43. Task UpdateServerMessage(int id, string inMessage, DateTime receivedOn);
  44. }
  45. public class MainDbService : IMainDbService
  46. {
  47. public MainDbService(
  48. IDbContextFactory<MainDBContext> contextFactory,
  49. ISqlConnectionFactory<MainDBContext> sqlConnectionFactory,
  50. IMemoryCache memoryCache,
  51. IConfiguration configuration,
  52. ILoggerFactory loggerFactory,
  53. ILogger<MainDbService> logger)
  54. {
  55. this.contextFactory = contextFactory;
  56. this.sqlConnectionFactory = sqlConnectionFactory;
  57. this.memoryCache = memoryCache;
  58. this.loggerFactory = loggerFactory;
  59. this.logger = logger;
  60. var startupLimit = GetStartupLimit(configuration);
  61. //this.connectionString = configuration.GetConnectionString("MainDBContext");
  62. this.startupSemaphore = new(startupLimit);
  63. var opLimit = GetOpLimit(configuration);
  64. this.opSemaphore = new SemaphoreSlim(opLimit);
  65. InitUpdateConnectorStatusHandler();
  66. InitUpdateMachineBasicInfoHandler();
  67. InitAddServerMessageHandler();
  68. }
  69. private const string CustomerMemCacheKeyFromat = "Customer_{0}";
  70. private const string ChargeBoxConnectorIdMemCacheKeyFromat = "Connector_{0}{1}";
  71. private readonly IDbContextFactory<MainDBContext> contextFactory;
  72. private readonly ISqlConnectionFactory<MainDBContext> sqlConnectionFactory;
  73. private readonly IMemoryCache memoryCache;
  74. private readonly ILoggerFactory loggerFactory;
  75. private readonly ILogger<MainDbService> logger;
  76. //private string connectionString;
  77. private readonly QueueSemaphore startupSemaphore;
  78. private readonly SemaphoreSlim opSemaphore;
  79. private GroupHandler<StatusNotificationParam> statusNotificationHandler;
  80. private GroupHandler<UpdateMachineBasicInfoParam> updateMachineBasicInfoHandler;
  81. private GroupHandler<ServerMessage> addServerMessageHandler;
  82. public async Task<MachineAndCustomerInfo> GetMachineIdAndCustomerInfo(string ChargeBoxId)
  83. {
  84. using var semaphoreWrapper = await startupSemaphore.GetToken();
  85. using var db = await contextFactory.CreateDbContextAsync();
  86. var machine = await db.Machines.Where(x => x.ChargeBoxId == ChargeBoxId && x.IsDelete == false).Select(x => new { x.CustomerId, x.Id }).AsNoTracking().FirstOrDefaultAsync();
  87. if (machine == null)
  88. {
  89. return new MachineAndCustomerInfo(string.Empty, Guid.Empty, "Unknown");
  90. }
  91. //var customerName = await db.Customer.Where(x => x.Id == machine.CustomerId).Select(x => x.Name).FirstOrDefaultAsync();
  92. var customer = await GetCustomer(machine.CustomerId);
  93. var customerName = customer?.Name;
  94. return new MachineAndCustomerInfo(machine.Id, machine.CustomerId, customerName);
  95. }
  96. public async Task<string> GetMachineConfiguration(string ChargeBoxId, string configName)
  97. {
  98. using var semaphoreWrapper = await startupSemaphore.GetToken();
  99. using var db = await contextFactory.CreateDbContextAsync();
  100. return await db.MachineConfigurations
  101. .Where(x => x.ChargeBoxId == ChargeBoxId && x.ConfigureName == configName)
  102. .Select(x => x.ConfigureSetting).FirstOrDefaultAsync();
  103. }
  104. public Task<string> GetMachineSecurityProfile(string ChargeBoxId)
  105. {
  106. return GetMachineConfiguration(ChargeBoxId, StandardConfiguration.SecurityProfile);
  107. }
  108. public Task<string> GetMachineAuthorizationKey(string ChargeBoxId)
  109. {
  110. return GetMachineConfiguration(ChargeBoxId, StandardConfiguration.AuthorizationKey);
  111. }
  112. public Task<string> GetMachineHeartbeatInterval(string ChargeBoxId)
  113. {
  114. return GetMachineConfiguration(ChargeBoxId, StandardConfiguration.HeartbeatInterval);
  115. }
  116. public Task UpdateMachineBasicInfo(string ChargeBoxId, Machine machine)
  117. {
  118. //return UpdateMachineBasicInfoEF(ChargeBoxId, machine);
  119. return updateMachineBasicInfoHandler.HandleAsync(new UpdateMachineBasicInfoParam(ChargeBoxId, machine));
  120. }
  121. public async Task AddOCMF(Ocmf oCMF)
  122. {
  123. using var db = await contextFactory.CreateDbContextAsync();
  124. await db.Ocmfs.AddAsync(oCMF);
  125. await db.SaveChangesAsync();
  126. }
  127. public async ValueTask AddConnectorStatus(
  128. string ChargeBoxId, byte ConnectorId, DateTime CreatedOn, int Status,
  129. int ChargePointErrorCodeId, string ErrorInfo, string VendorId, string VendorErrorCode)
  130. {
  131. using var db = await contextFactory.CreateDbContextAsync();
  132. var _currentStatus = new ConnectorStatus()
  133. {
  134. ChargeBoxId = ChargeBoxId,
  135. ConnectorId = ConnectorId,
  136. CreatedOn = CreatedOn,
  137. Status = Status,
  138. ChargePointErrorCodeId = ChargePointErrorCodeId,
  139. ErrorInfo = ErrorInfo,
  140. VendorId = VendorId,
  141. VendorErrorCode = VendorErrorCode,
  142. Id = Guid.NewGuid().ToString()
  143. };
  144. await db.ConnectorStatuses.AddAsync(_currentStatus);
  145. await db.SaveChangesAsync();
  146. memoryCache.Set(
  147. string.Format(ChargeBoxConnectorIdMemCacheKeyFromat, ChargeBoxId, ConnectorId)
  148. , _currentStatus, TimeSpan.FromHours(12));
  149. }
  150. public async ValueTask<ConnectorStatus> GetConnectorStatus(string ChargeBoxId, int ConnectorId)
  151. {
  152. var key = string.Format(ChargeBoxConnectorIdMemCacheKeyFromat, ChargeBoxId, ConnectorId);
  153. if (memoryCache.TryGetValue<ConnectorStatus>(key, out var status))
  154. {
  155. return status;
  156. }
  157. using var db = await contextFactory.CreateDbContextAsync();
  158. var statusFromDb = await db.ConnectorStatuses.Where(x => x.ChargeBoxId == ChargeBoxId
  159. && x.ConnectorId == ConnectorId).AsNoTracking().FirstOrDefaultAsync();
  160. memoryCache.Set(key, statusFromDb, TimeSpan.FromHours(12));
  161. return statusFromDb;
  162. }
  163. public async Task UpdateConnectorStatus(string Id, ConnectorStatus Status)
  164. {
  165. //await statusNotificationHandler.HandleAsync(new StatusNotificationParam(Id, Status));
  166. //await UpdateConnectorStatusEF(Id, Status);
  167. await UpdateConnectorStatusDapper(Id, Status);
  168. var key = string.Format(ChargeBoxConnectorIdMemCacheKeyFromat, Status.ChargeBoxId, Status.ConnectorId);
  169. memoryCache.Set(key, Status, TimeSpan.FromHours(12));
  170. return;
  171. }
  172. public Task<Guid> GetCustomerIdByChargeBoxId(string chargeboxId)
  173. {
  174. //return GetCustomerIdByChargeBoxIdEF(chargeboxId);
  175. return GetCustomerIdByChargeBoxIdDapper(chargeboxId);
  176. }
  177. public Task<int?> TryGetDuplicatedTransactionId(string chargeBoxId, Guid customerId, int connectorId, DateTime timestamp)
  178. {
  179. //return TryGetDuplicatedTransactionIdEF(chargeBoxId, customerId, connectorId, timestamp);
  180. return TryGetDuplicatedTransactionIdDapper(chargeBoxId, customerId, connectorId, timestamp);
  181. }
  182. public ValueTask AddMachineError(byte ConnectorId, DateTime CreatedOn, int Status, string ChargeBoxId,
  183. int ErrorCodeId, string ErrorInfo, int PreStatus, string VendorErrorCode, string VendorId)
  184. {
  185. //return AddMachineErrorEF(ConnectorId, CreatedOn, Status, ChargeBoxId, ErrorCodeId, ErrorInfo, PreStatus, VendorErrorCode, VendorId);
  186. return AddMachineErrorDapper(ConnectorId, CreatedOn, Status, ChargeBoxId, ErrorCodeId, ErrorInfo, PreStatus, VendorErrorCode, VendorId);
  187. }
  188. public Task AddServerMessage(string ChargeBoxId, string OutAction, object OutRequest, string CreatedBy = "", DateTime? CreatedOn = null, string SerialNo = "", string InMessage = "")
  189. {
  190. if (string.IsNullOrEmpty(CreatedBy))
  191. {
  192. CreatedBy = "Server";
  193. }
  194. if (string.IsNullOrEmpty(SerialNo))
  195. {
  196. SerialNo = Guid.NewGuid().ToString();
  197. }
  198. var _CreatedOn = CreatedOn ?? DateTime.UtcNow;
  199. logger.LogTrace(JsonConvert.SerializeObject(_CreatedOn));
  200. string _OutRequest = string.Empty;
  201. if (OutRequest is not null)
  202. {
  203. _OutRequest = JsonConvert.SerializeObject(
  204. OutRequest,
  205. new JsonSerializerSettings()
  206. {
  207. NullValueHandling = NullValueHandling.Ignore,
  208. Formatting = Formatting.None
  209. });
  210. }
  211. var data = new ServerMessage()
  212. {
  213. ChargeBoxId = ChargeBoxId,
  214. CreatedBy = CreatedBy,
  215. CreatedOn = _CreatedOn,
  216. OutAction = OutAction,
  217. OutRequest = _OutRequest,
  218. SerialNo = SerialNo,
  219. InMessage = InMessage
  220. };
  221. return AddServerMessage(data);
  222. }
  223. public Task AddServerMessage(ServerMessage message)
  224. {
  225. //return AddServerMessageEF(message);
  226. //return addServerMessageHandler.HandleAsync(message);
  227. return AddServerMessageDapper(message);
  228. }
  229. public ValueTask<Customer> GetCustomer(string id)
  230. => GetCustomer(new Guid(id));
  231. public async ValueTask<Customer> GetCustomer(Guid id)
  232. {
  233. var key = string.Format(CustomerMemCacheKeyFromat, id);
  234. if (memoryCache.TryGetValue<Customer>(key, out var customer))
  235. {
  236. return customer;
  237. }
  238. Customer toReturn = null;
  239. using (var db = await contextFactory.CreateDbContextAsync())
  240. {
  241. toReturn = await db.Customers.FirstOrDefaultAsync(x => x.Id == id);
  242. }
  243. if (toReturn is not null)
  244. {
  245. memoryCache.Set(key, toReturn, TimeSpan.FromSeconds(15));
  246. }
  247. return toReturn;
  248. }
  249. public Task<int> AddNewTransactionRecord(TransactionRecord newTransaction)
  250. {
  251. //return AddNewTransactionRecordEF(newTransaction);
  252. return AddNewTransactionRecordDapper(newTransaction);
  253. }
  254. public Task<TransactionRecord> GetTransactionForStopTransaction(int transactionId, string chargeBoxId)
  255. {
  256. //return GetTransactionForStopTransactionEF(transactionId, chargeBoxId);
  257. return GetTransactionForStopTransactionDapper(transactionId, chargeBoxId);
  258. }
  259. public Task UpdateTransaction(int transactionId, int meterStop, DateTime stopTime, int stopReasonId, string stopReason, string stopIdTag, string receipt, int cost)
  260. {
  261. //return UpdateTransactionEF(transactionId, meterStop, stopTime, stopReasonId, stopReason, stopIdTag, receipt, cost);
  262. return UpdateTransactionDapper(transactionId, meterStop, stopTime, stopReasonId, stopReason, stopIdTag, receipt, cost);
  263. }
  264. public async Task UpdateTransactionSOC(int id, string startSOC, string stopSOC)
  265. {
  266. var parameters = new DynamicParameters();
  267. parameters.Add("@TransactionId", id, DbType.Int32, ParameterDirection.Input);
  268. parameters.Add("@StartSOC", startSOC, DbType.String, ParameterDirection.Input, 3);
  269. parameters.Add("@StopSOC", stopSOC, DbType.String, ParameterDirection.Input, 3);
  270. using var conn = await sqlConnectionFactory.CreateAsync();
  271. var resultCnt = await conn.ExecuteAsync("""
  272. UPDATE TransactionRecord
  273. SET StartSOC = @StartSOC, StopSOC = @StopSOC
  274. WHERE Id = @TransactionId
  275. """, parameters);
  276. if (resultCnt != 1)
  277. {
  278. throw new Exception("Update over one columes");
  279. }
  280. return;
  281. }
  282. private async Task UpdateTransactionEF(int transactionId, int meterStop, DateTime stopTime, int stopReasonId, string stopReason, string stopIdTag, string receipt, int cost)
  283. {
  284. using var db = await contextFactory.CreateDbContextAsync();
  285. var _transaction = db.TransactionRecords.Where(x => x.Id == transactionId //&& x.ChargeBoxId == session.ChargeBoxId
  286. ).FirstOrDefault();
  287. _transaction.MeterStop = meterStop;
  288. _transaction.StopTime = stopTime;
  289. _transaction.StopReasonId = stopReasonId;
  290. _transaction.StopReason = stopReason;
  291. _transaction.StopIdTag = stopIdTag;
  292. _transaction.Receipt = receipt;
  293. _transaction.Cost = cost;
  294. //await db.SaveChangesAsync();
  295. await db.SaveChangesAsync();
  296. }
  297. private async Task UpdateTransactionDapper(int transactionId, int meterStop, DateTime stopTime, int stopReasonId, string stopReason, string stopIdTag, string receipt, int cost)
  298. {
  299. var parameters = new DynamicParameters();
  300. parameters.Add("@TransactionId", transactionId, DbType.Int32, ParameterDirection.Input);
  301. parameters.Add("@MeterStop", meterStop, DbType.Decimal, ParameterDirection.Input, precision: 18, scale: 2);
  302. parameters.Add("@StopTime", stopTime, DbType.DateTime, ParameterDirection.Input);
  303. parameters.Add("@StopReasonId", stopReasonId, DbType.Int32, ParameterDirection.Input);
  304. parameters.Add("@StopReason", stopReason, DbType.String, ParameterDirection.Input, 60);
  305. parameters.Add("@StopIdTag", stopIdTag, DbType.String, ParameterDirection.Input, 20);
  306. parameters.Add("@Receipt", receipt, DbType.String, ParameterDirection.Input, 3000);
  307. parameters.Add("@Cost", cost, DbType.Decimal, ParameterDirection.Input, precision: 18, scale: 2);
  308. using var conn = await sqlConnectionFactory.CreateAsync();
  309. var resultCnt = await conn.ExecuteAsync("""
  310. UPDATE TransactionRecord
  311. SET MeterStop = @MeterStop, StopTime = @StopTime, StopReasonId = @StopReasonId,
  312. StopReason = @StopReason, StopIdTag = @StopIdTag, Receipt = @Receipt, Cost = @Cost
  313. WHERE Id = @TransactionId
  314. """, parameters);
  315. if (resultCnt != 1)
  316. {
  317. throw new Exception("Update over one columes");
  318. }
  319. return;
  320. }
  321. public Task<bool> UpdateHeartBeats(IEnumerable<Machine> heartBeatsData)
  322. {
  323. //return UpdateHeartBeatsEF(heartBeatsData);
  324. return UpdateHeartBeatsDapper(heartBeatsData);
  325. }
  326. public async Task UpdateMachineConnectionType(string chargeBoxId, int connectionType)
  327. {
  328. using (var db = await contextFactory.CreateDbContextAsync())
  329. {
  330. var machine = await db.Machines.Where(x => x.ChargeBoxId == chargeBoxId).FirstOrDefaultAsync();
  331. if (machine != null)
  332. {
  333. machine.ConnectionType = connectionType;
  334. await db.SaveChangesAsync();
  335. }
  336. }
  337. }
  338. public async Task<bool?> GetChargeBoxIdIsAc(string chargeBoxId)
  339. {
  340. using var db = await contextFactory.CreateDbContextAsync();
  341. var machine = await db.Machines.Where(x => x.ChargeBoxId == chargeBoxId).FirstOrDefaultAsync();
  342. if (machine is null)
  343. {
  344. return null;
  345. }
  346. return !machine.ConnectorType.Contains("6") && !machine.ConnectorType.Contains("7") && !machine.ConnectorType.Contains("8") && !machine.ConnectorType.Contains("9");
  347. }
  348. public async Task UpdateServerMessage(int id, string inMessage, DateTime receivedOn)
  349. {
  350. using (var db = await contextFactory.CreateDbContextAsync())
  351. {
  352. var sc = await db.ServerMessages.Where(x => x.Id == id).FirstOrDefaultAsync();
  353. sc.InMessage = inMessage;
  354. sc.ReceivedOn = receivedOn;
  355. await db.SaveChangesAsync();
  356. }
  357. }
  358. private void InitUpdateConnectorStatusHandler()
  359. {
  360. if (statusNotificationHandler is not null)
  361. {
  362. throw new Exception($"{nameof(InitUpdateConnectorStatusHandler)} should only called once");
  363. }
  364. statusNotificationHandler = new GroupHandler<StatusNotificationParam>(
  365. handleFunc: BundleUpdateConnectorStatusDapper,
  366. logger: loggerFactory.CreateLogger("StatusNotificationHandler"),
  367. workerCnt: 1);
  368. }
  369. private void InitAddServerMessageHandler()
  370. {
  371. if (addServerMessageHandler is not null)
  372. {
  373. throw new Exception($"{nameof(InitAddServerMessageHandler)} should only called once");
  374. }
  375. addServerMessageHandler = new GroupHandler<ServerMessage>(
  376. handleFunc: BundleAddServerMessage,
  377. logger: loggerFactory.CreateLogger("AddServerMessageHandler"));
  378. }
  379. private void InitUpdateMachineBasicInfoHandler()
  380. {
  381. if (updateMachineBasicInfoHandler is not null)
  382. {
  383. throw new Exception($"{nameof(InitUpdateMachineBasicInfoHandler)} should only called once");
  384. }
  385. updateMachineBasicInfoHandler = new GroupHandler<UpdateMachineBasicInfoParam>(
  386. handleFunc: BundelUpdateMachineBasicInfo,
  387. logger: loggerFactory.CreateLogger("UpdateMachineBasicInfoHandler"),
  388. workerCnt: 10);
  389. }
  390. private async Task UpdateMachineBasicInfoEF(string chargeBoxId, Machine machine)
  391. {
  392. using var semaphoreWrapper = await startupSemaphore.GetToken();
  393. using var db = await contextFactory.CreateDbContextAsync();
  394. var _machine = await db.Machines.FirstOrDefaultAsync(x => x.ChargeBoxId == chargeBoxId);
  395. _machine.ChargeBoxSerialNumber = machine.ChargeBoxSerialNumber;
  396. _machine.ChargePointSerialNumber = machine.ChargePointSerialNumber;
  397. _machine.ChargePointModel = machine.ChargePointModel;
  398. _machine.ChargePointVendor = machine.ChargePointVendor;
  399. _machine.FwCurrentVersion = machine.FwCurrentVersion;
  400. _machine.Iccid = DateTime.UtcNow.ToString("yy-MM-dd HH:mm");
  401. _machine.Imsi = machine.Imsi;
  402. _machine.MeterSerialNumber = machine.MeterSerialNumber;
  403. _machine.MeterType = machine.MeterType;
  404. await db.SaveChangesAsync();
  405. //using var semaphoreWrapper = await startupSemaphore.GetToken();
  406. }
  407. private async Task BundelUpdateMachineBasicInfo(BundleHandlerData<UpdateMachineBasicInfoParam> bundleHandlerData)
  408. {
  409. using var db = await contextFactory.CreateDbContextAsync();
  410. using var trans = await db.Database.BeginTransactionAsync();
  411. var pams = bundleHandlerData.Datas.DistinctBy(x => x.ChargeBoxId);
  412. foreach (var pam in pams)
  413. {
  414. var _machine = db.Machines.FirstOrDefault(x => x.ChargeBoxId == pam.ChargeBoxId);
  415. _machine.ChargeBoxSerialNumber = pam.machine.ChargeBoxSerialNumber;
  416. _machine.ChargePointSerialNumber = pam.machine.ChargePointSerialNumber;
  417. _machine.ChargePointModel = pam.machine.ChargePointModel;
  418. _machine.ChargePointVendor = pam.machine.ChargePointVendor;
  419. _machine.FwCurrentVersion = pam.machine.FwCurrentVersion;
  420. _machine.Iccid = DateTime.UtcNow.ToString("yy-MM-dd HH:mm");
  421. _machine.Imsi = pam.machine.Imsi;
  422. _machine.MeterSerialNumber = pam.machine.MeterSerialNumber;
  423. _machine.MeterType = pam.machine.MeterType;
  424. }
  425. await db.SaveChangesAsync();
  426. await trans.CommitAsync();
  427. bundleHandlerData.CompletedDatas.AddRange(bundleHandlerData.Datas);
  428. }
  429. private async Task UpdateConnectorStatusEF(string Id, ConnectorStatus Status)
  430. {
  431. using var db = await contextFactory.CreateDbContextAsync();
  432. ConnectorStatus status = new() { Id = Id };
  433. db.ChangeTracker.AutoDetectChangesEnabled = false;
  434. db.ConnectorStatuses.Attach(status);
  435. status.CreatedOn = Status.CreatedOn;
  436. status.Status = Status.Status;
  437. status.ChargePointErrorCodeId = Status.ChargePointErrorCodeId;
  438. status.ErrorInfo = Status.ErrorInfo;
  439. status.VendorId = Status.VendorId;
  440. status.VendorErrorCode = Status.VendorErrorCode;
  441. db.Entry(status).Property(x => x.CreatedOn).IsModified = true;
  442. db.Entry(status).Property(x => x.Status).IsModified = true;
  443. db.Entry(status).Property(x => x.ChargePointErrorCodeId).IsModified = true;
  444. db.Entry(status).Property(x => x.ErrorInfo).IsModified = true;
  445. db.Entry(status).Property(x => x.VendorId).IsModified = true;
  446. db.Entry(status).Property(x => x.VendorErrorCode).IsModified = true;
  447. await db.SaveChangesAsync();
  448. }
  449. private async Task UpdateConnectorStatusDapper(string Id, ConnectorStatus Status)
  450. {
  451. var parameters = new DynamicParameters();
  452. parameters.Add("@Id", Id, DbType.String, ParameterDirection.Input, 36);
  453. parameters.Add("@CreatedOn", Status.CreatedOn, DbType.DateTime, ParameterDirection.Input);
  454. parameters.Add("@Status", Status.Status, DbType.Int32, ParameterDirection.Input);
  455. parameters.Add("@ChargePointErrorCodeId", Status.ChargePointErrorCodeId, DbType.Int32, ParameterDirection.Input);
  456. parameters.Add("@ErrorInfo", Status.ErrorInfo, DbType.String, ParameterDirection.Input, 50);
  457. parameters.Add("@VendorId", Status.VendorId, DbType.String, ParameterDirection.Input, 255);
  458. parameters.Add("@VendorErrorCode", Status.VendorErrorCode, DbType.String, ParameterDirection.Input, 100);
  459. using var conn = await sqlConnectionFactory.CreateAsync();
  460. await conn.ExecuteAsync("""
  461. update ConnectorStatus
  462. set
  463. CreatedOn = @CreatedOn,
  464. Status = @Status,
  465. ChargePointErrorCodeId = @ChargePointErrorCodeId,
  466. ErrorInfo = @ErrorInfo,
  467. VendorId = @VendorId,
  468. VendorErrorCode = @VendorErrorCode
  469. where Id = @Id
  470. """, parameters);
  471. }
  472. private async Task<Guid> GetCustomerIdByChargeBoxIdEF(string chargeboxId)
  473. {
  474. using var db = await contextFactory.CreateDbContextAsync();
  475. var _CustomerId = await db.Machines.Where(x => x.ChargeBoxId == chargeboxId).Select(x => x.CustomerId).FirstOrDefaultAsync();
  476. return _CustomerId;
  477. }
  478. private async Task<Guid> GetCustomerIdByChargeBoxIdDapper(string chargeboxId)
  479. {
  480. var parameters = new DynamicParameters();
  481. parameters.Add("@ChargeBoxId", chargeboxId, DbType.String, ParameterDirection.Input, 50);
  482. using var conn = await sqlConnectionFactory.CreateAsync();
  483. var _existedTx = await conn.QueryFirstOrDefaultAsync<Guid>("""
  484. select CustomerId
  485. from dbo.Machine
  486. where
  487. ChargeBoxId = @ChargeBoxId
  488. """, parameters);
  489. return _existedTx;
  490. }
  491. private async Task<int?> TryGetDuplicatedTransactionIdEF(string chargeBoxId, Guid customerId, int connectorId, DateTime timestamp)
  492. {
  493. using var db = await contextFactory.CreateDbContextAsync();
  494. var _existedTx = await db.TransactionRecords.Where(x => x.CustomerId == customerId && x.ChargeBoxId == chargeBoxId
  495. && x.ConnectorId == connectorId && x.StartTime == timestamp).Select(x => x.Id).FirstOrDefaultAsync();
  496. return _existedTx;
  497. }
  498. private async Task<int?> TryGetDuplicatedTransactionIdDapper(string chargeBoxId, Guid customerId, int connectorId, DateTime timestamp)
  499. {
  500. var parameters = new DynamicParameters();
  501. parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
  502. parameters.Add("@CustomerId", customerId, DbType.Guid, ParameterDirection.Input);
  503. parameters.Add("@ConnectorId", connectorId, DbType.Int16, ParameterDirection.Input);
  504. parameters.Add("@TimeStamp", timestamp, DbType.DateTime, ParameterDirection.Input);
  505. using var conn = await sqlConnectionFactory.CreateAsync();
  506. var _existedTx = await conn.QueryFirstOrDefaultAsync<int?>("""
  507. SELECT Id
  508. FROM dbo.TransactionRecord
  509. WHERE
  510. ChargeBoxId = @ChargeBoxId and
  511. CustomerId = @CustomerId and
  512. ConnectorId = @ConnectorId and
  513. StartTime = @TimeStamp
  514. """, parameters);
  515. return _existedTx;
  516. }
  517. private async ValueTask AddMachineErrorEF(byte connectorId, DateTime createdOn, int status, string chargeBoxId, int errorCodeId, string errorInfo, int preStatus, string vendorErrorCode, string vendorId)
  518. {
  519. using var db = await contextFactory.CreateDbContextAsync();
  520. await db.MachineErrors.AddAsync(new MachineError()
  521. {
  522. ConnectorId = connectorId,
  523. CreatedOn = createdOn,
  524. Status = status,
  525. ChargeBoxId = chargeBoxId,
  526. ErrorCodeId = errorCodeId,
  527. ErrorInfo = errorInfo,
  528. PreStatus = preStatus,
  529. VendorErrorCode = vendorErrorCode,
  530. VendorId = vendorId
  531. });
  532. await db.SaveChangesAsync();
  533. }
  534. private async ValueTask AddMachineErrorDapper(byte connectorId, DateTime createdOn, int status, string chargeBoxId, int errorCodeId, string errorInfo, int preStatus, string vendorErrorCode, string vendorId)
  535. {
  536. var parameters = new DynamicParameters();
  537. parameters.Add("@ConnectorId", connectorId, DbType.Int16, ParameterDirection.Input);
  538. parameters.Add("@PreStatus", preStatus, DbType.Int32, ParameterDirection.Input);
  539. parameters.Add("@Status", status, DbType.Int32, ParameterDirection.Input);
  540. parameters.Add("@ErrorInfo", errorInfo, DbType.String, ParameterDirection.Input, 50);
  541. parameters.Add("@VendorId", vendorId, DbType.String, ParameterDirection.Input, 255);
  542. parameters.Add("@CreatedOn", createdOn, DbType.DateTime, ParameterDirection.Input);
  543. parameters.Add("@ErrorCodeId", errorCodeId, DbType.Int32, ParameterDirection.Input);
  544. parameters.Add("@VendorErrorCode", vendorErrorCode, DbType.String, ParameterDirection.Input, 100);
  545. parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
  546. using var conn = await sqlConnectionFactory.CreateAsync();
  547. await conn.ExecuteAsync("""
  548. INSERT INTO MachineError
  549. (ConnectorId, PreStatus, Status, ErrorInfo, VendorId, CreatedOn, ErrorCodeId, VendorErrorCode, ChargeBoxId)
  550. VALUES (@ConnectorId, @PreStatus, @Status, @ErrorInfo, @VendorId, @CreatedOn, @ErrorCodeId, @VendorErrorCode, @ChargeBoxId)
  551. """, parameters);
  552. }
  553. private async Task BundleUpdateConnectorStatus(IEnumerable<StatusNotificationParam> statusNotifications)
  554. {
  555. using var db = await contextFactory.CreateDbContextAsync();
  556. using var trans = await db.Database.BeginTransactionAsync();
  557. statusNotifications = statusNotifications.OrderBy(x => x.Status.CreatedOn).DistinctBy(x => x.Id);
  558. foreach (var param in statusNotifications)
  559. {
  560. ConnectorStatus status = new() { Id = param.Id };
  561. //db.ChangeTracker.AutoDetectChangesEnabled = false;
  562. db.ConnectorStatuses.Attach(status);
  563. status.CreatedOn = param.Status.CreatedOn;
  564. status.Status = param.Status.Status;
  565. status.ChargePointErrorCodeId = param.Status.ChargePointErrorCodeId;
  566. status.ErrorInfo = param.Status.ErrorInfo;
  567. status.VendorId = param.Status.VendorId;
  568. status.VendorErrorCode = param.Status.VendorErrorCode;
  569. db.Entry(status).Property(x => x.CreatedOn).IsModified = true;
  570. db.Entry(status).Property(x => x.Status).IsModified = true;
  571. db.Entry(status).Property(x => x.ChargePointErrorCodeId).IsModified = true;
  572. db.Entry(status).Property(x => x.ErrorInfo).IsModified = true;
  573. db.Entry(status).Property(x => x.VendorId).IsModified = true;
  574. db.Entry(status).Property(x => x.VendorErrorCode).IsModified = true;
  575. //await db.SaveChangesAsync();
  576. }
  577. await db.SaveChangesAsync();
  578. await trans.CommitAsync();
  579. //db.ChangeTracker.Clear();
  580. }
  581. private async Task BundleUpdateConnectorStatusDapper(BundleHandlerData<StatusNotificationParam> bundleHandlerData)
  582. {
  583. using var conn = await sqlConnectionFactory.CreateAsync();
  584. foreach (var status in bundleHandlerData.Datas)
  585. {
  586. var parameters = new DynamicParameters();
  587. parameters.Add("@Id", status.Id, DbType.String, ParameterDirection.Input, 36);
  588. parameters.Add("@CreatedOn", status.Status.CreatedOn, DbType.DateTime, ParameterDirection.Input);
  589. parameters.Add("@Status", status.Status.Status, DbType.Int32, ParameterDirection.Input);
  590. parameters.Add("@ChargePointErrorCodeId", status.Status.ChargePointErrorCodeId, DbType.Int32, ParameterDirection.Input);
  591. parameters.Add("@ErrorInfo", status.Status.ErrorInfo, DbType.String, ParameterDirection.Input, 50);
  592. parameters.Add("@VendorId", status.Status.VendorId, DbType.String, ParameterDirection.Input, 255);
  593. parameters.Add("@VendorErrorCode", status.Status.VendorErrorCode, DbType.String, ParameterDirection.Input, 100);
  594. await conn.ExecuteAsync("""
  595. update ConnectorStatus
  596. set
  597. CreatedOn = @CreatedOn,
  598. Status = @Status,
  599. ChargePointErrorCodeId = @ChargePointErrorCodeId,
  600. ErrorInfo = @ErrorInfo,
  601. VendorId = @VendorId,
  602. VendorErrorCode = @VendorErrorCode
  603. where Id = @Id
  604. """, parameters);
  605. bundleHandlerData.AddCompletedData(status);
  606. }
  607. }
  608. private async Task BundleAddServerMessage(BundleHandlerData<ServerMessage> bundleHandlerData)
  609. {
  610. using var db = await contextFactory.CreateDbContextAsync();
  611. using var trans = await db.Database.BeginTransactionAsync();
  612. foreach (var message in bundleHandlerData.Datas)
  613. {
  614. await db.ServerMessages.AddAsync(message);
  615. }
  616. await db.SaveChangesAsync();
  617. await trans.CommitAsync();
  618. bundleHandlerData.CompletedDatas.AddRange(bundleHandlerData.Datas);
  619. //db.ChangeTracker.Clear();
  620. }
  621. private async Task AddServerMessageEF(ServerMessage message)
  622. {
  623. using var db = await contextFactory.CreateDbContextAsync();
  624. using var trans = await db.Database.BeginTransactionAsync();
  625. await db.ServerMessages.AddAsync(message);
  626. await db.SaveChangesAsync();
  627. await trans.CommitAsync();
  628. //db.ChangeTracker.Clear();
  629. }
  630. private async Task AddServerMessageDapper(ServerMessage message)
  631. {
  632. var parameters = new DynamicParameters();
  633. parameters.Add("@SerialNo", message.SerialNo, DbType.String, ParameterDirection.Input, 36);
  634. parameters.Add("@OutAction", message.OutAction, DbType.String, ParameterDirection.Input, 30);
  635. parameters.Add("@OutRequest", message.OutRequest, DbType.String, ParameterDirection.Input);
  636. parameters.Add("@InMessage", message.InMessage, DbType.String, ParameterDirection.Input);
  637. parameters.Add("@CreatedOn", message.CreatedOn, DbType.DateTime, ParameterDirection.Input);
  638. parameters.Add("@CreatedBy", message.CreatedBy, DbType.String, ParameterDirection.Input, 36);
  639. parameters.Add("@ReceivedOn", message.ReceivedOn, DbType.DateTime, ParameterDirection.Input);
  640. parameters.Add("@ChargeBoxId", message.ChargeBoxId, DbType.String, ParameterDirection.Input, 30);
  641. parameters.Add("@UpdatedOn", message.UpdatedOn, DbType.DateTime, ParameterDirection.Input);
  642. using var conn = await sqlConnectionFactory.CreateAsync();
  643. var resultCnt = await conn.ExecuteAsync("""
  644. INSERT INTO ServerMessage
  645. (SerialNo, OutAction, OutRequest, InMessage, CreatedOn, CreatedBy, ReceivedOn, ChargeBoxId, UpdatedOn)
  646. VALUES (@SerialNo, @OutAction, @OutRequest, @InMessage, @CreatedOn, @CreatedBy, @ReceivedOn, @ChargeBoxId, @UpdatedOn)
  647. """, parameters);
  648. if (resultCnt != 1)
  649. {
  650. throw new Exception("Insert failed");
  651. }
  652. return;
  653. }
  654. private async Task<int> AddNewTransactionRecordEF(TransactionRecord newTransaction)
  655. {
  656. using var db = await contextFactory.CreateDbContextAsync();
  657. await db.TransactionRecords.AddAsync(newTransaction);
  658. await db.SaveChangesAsync();
  659. return newTransaction.Id;
  660. }
  661. private async Task<int> AddNewTransactionRecordDapper(TransactionRecord newTransaction)
  662. {
  663. var parameters = new DynamicParameters();
  664. parameters.Add("@ChargeBoxId", newTransaction.ChargeBoxId, DbType.String, ParameterDirection.Input, 50);
  665. parameters.Add("@ConnectorId", newTransaction.ConnectorId, DbType.Int16, ParameterDirection.Input);
  666. parameters.Add("@CreatedOn", newTransaction.CreatedOn, DbType.DateTime, ParameterDirection.Input);
  667. parameters.Add("@UpdatedOn", newTransaction.UpdatedOn, DbType.DateTime, ParameterDirection.Input);
  668. parameters.Add("@StartTransactionReportedOn", newTransaction.StartTransactionReportedOn, DbType.DateTime, ParameterDirection.Input);
  669. parameters.Add("@StopTransactionReportedOn", newTransaction.StopTransactionReportedOn, DbType.DateTime, ParameterDirection.Input);
  670. parameters.Add("@StartIdTag", newTransaction.StartIdTag, DbType.String, ParameterDirection.Input, 20);
  671. parameters.Add("@MeterStart", newTransaction.MeterStart, DbType.Decimal, ParameterDirection.Input, precision: 18, scale: 2);
  672. parameters.Add("@MeterStop", newTransaction.MeterStop, DbType.Decimal, ParameterDirection.Input, precision: 18, scale: 2);
  673. parameters.Add("@CustomerId", newTransaction.CustomerId, DbType.Guid, ParameterDirection.Input);
  674. parameters.Add("@StartTime", newTransaction.StartTime, DbType.DateTime, ParameterDirection.Input);
  675. parameters.Add("@StopTime", newTransaction.StopTime, DbType.DateTime, ParameterDirection.Input);
  676. parameters.Add("@ReservationId", newTransaction.ReservationId, DbType.Int32, ParameterDirection.Input);
  677. parameters.Add("@RetryStartTransactionTimes", newTransaction.RetryStartTransactionTimes, DbType.Int32, ParameterDirection.Input);
  678. parameters.Add("@RetryStopTransactionTimes", newTransaction.RetryStopTransactionTimes, DbType.Int32, ParameterDirection.Input);
  679. parameters.Add("@Fee", newTransaction.Fee, DbType.String, ParameterDirection.Input, 1500);
  680. using var conn = await sqlConnectionFactory.CreateAsync();
  681. var id = await conn.QuerySingleAsync<int>("""
  682. INSERT INTO TransactionRecord
  683. (ChargeBoxId, ConnectorId, CreatedOn, UpdatedOn, StartTransactionReportedOn, StopTransactionReportedOn,
  684. StartIdTag, MeterStart, MeterStop, CustomerId, StartTime, StopTime, ReservationId, RetryStartTransactionTimes, RetryStopTransactionTimes, Fee)
  685. OUTPUT INSERTED.Id
  686. VALUES (@ChargeBoxId, @ConnectorId, @CreatedOn, @UpdatedOn, @StartTransactionReportedOn, @StopTransactionReportedOn,
  687. @StartIdTag, @MeterStart, @MeterStop, @CustomerId, @StartTime, @StopTime, @ReservationId, @RetryStartTransactionTimes, @RetryStopTransactionTimes, @Fee)
  688. """, parameters);
  689. return id;
  690. }
  691. private async Task<TransactionRecord> GetTransactionForStopTransactionEF(int transactionId, string chargeBoxId)
  692. {
  693. using var db = await contextFactory.CreateDbContextAsync();
  694. return await db.TransactionRecords.Where(x => x.Id == transactionId
  695. && x.ChargeBoxId == chargeBoxId).FirstOrDefaultAsync();
  696. }
  697. private async Task<TransactionRecord> GetTransactionForStopTransactionDapper(int transactionId, string chargeBoxId)
  698. {
  699. var parameters = new DynamicParameters();
  700. parameters.Add("@TransactionId", transactionId, DbType.Int32, ParameterDirection.Input);
  701. parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
  702. using var conn = await sqlConnectionFactory.CreateAsync();
  703. var record = await conn.QuerySingleAsync<TransactionRecord>("""
  704. SELECT Id, ConnectorId, MeterStop, MeterStart, StartTime, StopTime FROM TransactionRecord
  705. WHERE Id = @TransactionId and ChargeBoxId = @ChargeBoxId
  706. """, parameters);
  707. return record;
  708. }
  709. private Task BulkInsertServerMessage(IEnumerable<ServerMessage> messages)
  710. {
  711. var table = new DataTable();
  712. table.Columns.Add("ChargeBoxId");
  713. table.Columns.Add("SerialNo");
  714. table.Columns.Add("OutAction");
  715. table.Columns.Add("OutRequest");
  716. table.Columns.Add("InMessage");
  717. table.Columns.Add("CreatedOn");
  718. table.Columns.Add("CreatedBy");
  719. table.Columns.Add("UpdatedOn");
  720. table.Columns.Add("ReceivedOn");
  721. foreach (var param in messages)
  722. {
  723. var row = table.NewRow();
  724. row["ChargeBoxId"] = param.ChargeBoxId;
  725. row["SerialNo"] = param.SerialNo;
  726. row["OutAction"] = param.OutAction;
  727. row["OutRequest"] = param.OutRequest;
  728. row["InMessage"] = param.InMessage;
  729. row["CreatedOn"] = param.CreatedOn;
  730. row["CreatedBy"] = param.CreatedBy;
  731. row["UpdatedOn"] = param.UpdatedOn;
  732. row["ReceivedOn"] = param.ReceivedOn;
  733. table.Rows.Add(row);
  734. }
  735. using SqlConnection sqlConnection = sqlConnectionFactory.Create() as SqlConnection;
  736. using SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnection);
  737. sqlBulkCopy.BatchSize = messages.Count();
  738. sqlBulkCopy.DestinationTableName = "ServerMessage";
  739. sqlBulkCopy.ColumnMappings.Add("ChargeBoxId", "ChargeBoxId");
  740. sqlBulkCopy.ColumnMappings.Add("SerialNo", "SerialNo");
  741. sqlBulkCopy.ColumnMappings.Add("OutAction", "OutAction");
  742. sqlBulkCopy.ColumnMappings.Add("OutRequest", "OutRequest");
  743. sqlBulkCopy.ColumnMappings.Add("InMessage", "InMessage");
  744. sqlBulkCopy.ColumnMappings.Add("CreatedOn", "CreatedOn");
  745. sqlBulkCopy.ColumnMappings.Add("CreatedBy", "CreatedBy");
  746. sqlBulkCopy.ColumnMappings.Add("UpdatedOn", "UpdatedOn");
  747. sqlBulkCopy.ColumnMappings.Add("ReceivedOn", "ReceivedOn");
  748. return sqlBulkCopy.WriteToServerAsync(table);
  749. }
  750. private int GetStartupLimit(IConfiguration configuration)
  751. {
  752. var limitConfig = configuration["MainDbStartupLimit"];
  753. int limit = 5;
  754. if (limitConfig != default)
  755. {
  756. int.TryParse(limitConfig, out limit);
  757. }
  758. return limit;
  759. }
  760. private int GetOpLimit(IConfiguration configuration)
  761. {
  762. var limitConfig = configuration["MainDbOpLimit"];
  763. int limit = 500;
  764. if (limitConfig != default)
  765. {
  766. int.TryParse(limitConfig, out limit);
  767. }
  768. return limit;
  769. }
  770. private async Task<bool> UpdateHeartBeatsDapper(IEnumerable<Machine> heartBeatsData)
  771. {
  772. using var conn = await sqlConnectionFactory.CreateAsync();
  773. using var trans = await conn.BeginTransactionAsync();
  774. try
  775. {
  776. foreach (var data in heartBeatsData)
  777. {
  778. var parameters = new DynamicParameters();
  779. parameters.Add("@Id", data.Id, DbType.String, ParameterDirection.Input, 36);
  780. parameters.Add("@HeartbeatUpdatedOn", data.HeartbeatUpdatedOn, DbType.DateTime, ParameterDirection.Input);
  781. parameters.Add("@ConnectionType", data.ConnectionType, DbType.Int32, ParameterDirection.Input);
  782. var resultCnt = await conn.ExecuteAsync("""
  783. UPDATE Machine
  784. SET HeartbeatUpdatedOn = @HeartbeatUpdatedOn, ConnectionType = @ConnectionType
  785. WHERE Id = @Id
  786. """, parameters, trans);
  787. if (resultCnt != 1)
  788. {
  789. throw new Exception("Update over one columes");
  790. }
  791. }
  792. await trans.CommitAsync();
  793. }
  794. catch
  795. {
  796. logger.LogCritical("HeartBeatCheckTrigger update fail, roll back");
  797. await trans.RollbackAsync();
  798. return false;
  799. }
  800. return true;
  801. }
  802. private async Task<bool> UpdateHeartBeatsEF(IEnumerable<Machine> heartBeatsData)
  803. {
  804. using var db = await contextFactory.CreateDbContextAsync();
  805. using var transaction = await db.Database.BeginTransactionAsync();
  806. try
  807. {
  808. foreach (var data in heartBeatsData)
  809. {
  810. var machine = new Machine() { Id = data.Id };
  811. if (machine != null)
  812. {
  813. db.Machines.Attach(machine);
  814. machine.HeartbeatUpdatedOn = DateTime.UtcNow;
  815. machine.ConnectionType = data.ConnectionType;
  816. db.Entry(machine).Property(x => x.HeartbeatUpdatedOn).IsModified = true;
  817. db.Entry(machine).Property(x => x.ConnectionType).IsModified = true;
  818. }
  819. }
  820. await db.SaveChangesAsync();
  821. await transaction.CommitAsync();
  822. db.ChangeTracker.Clear();
  823. }
  824. catch (Exception ex)
  825. {
  826. logger.LogCritical(ex, "HeartBeatCheckTrigger update fail, roll back");
  827. transaction.Rollback();
  828. return false;
  829. }
  830. return true;
  831. }
  832. }
  833. public record MachineAndCustomerInfo (string MachineId, Guid CustomerId, string CustomerName);
  834. public record StatusNotificationParam(string Id, ConnectorStatus Status);
  835. public record UpdateMachineBasicInfoParam(string ChargeBoxId, Machine machine);