WebDbService.cs 5.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135
  1. using Dapper;
  2. using EVCB_OCPP.Domain.ConnectionFactory;
  3. using EVCB_OCPP.WSServer.Dto;
  4. using EVCB_OCPP.WSServer.Helper;
  5. using EVCB_OCPP.WSServer.Service.WsService;
  6. using Microsoft.Data.SqlClient;
  7. using Microsoft.Extensions.Logging;
  8. using System.Data;
  9. namespace EVCB_OCPP.WSServer.Service;
  10. public class WebDbService
  11. {
  12. private readonly ISqlConnectionFactory<WebDBConetext> webDbConnectionFactory;
  13. private readonly ILogger<WebDbService> logger;
  14. public WebDbService(
  15. ISqlConnectionFactory<WebDBConetext> webDbConnectionFactory
  16. , ILogger<WebDbService> logger)
  17. {
  18. this.webDbConnectionFactory = webDbConnectionFactory;
  19. this.logger = logger;
  20. //this.webConnectionString = configuration.GetConnectionString("WebDBContext");
  21. }
  22. //private readonly string webConnectionString;
  23. public async Task<List<string>> GetDenyModelNames(CancellationToken token = default)
  24. {
  25. using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
  26. string strSql = """
  27. SELECT [Value]
  28. FROM [dbo].[KernelConfig]
  29. where SystemKey = 'DenyModelNames';
  30. """;
  31. var result = await conn.QueryFirstOrDefaultAsync<string>(
  32. new CommandDefinition(strSql, cancellationToken: token)
  33. );
  34. return result.Split(',').ToList();
  35. }
  36. internal async Task<string> SetDefaultFee(WsClientData client)
  37. {
  38. string displayPriceText = string.Empty;
  39. string charingPriceText = string.Empty;
  40. if (string.IsNullOrEmpty(client.ChargeBoxId)) return displayPriceText;
  41. try
  42. {
  43. StationFee stationPrice = await GetStationFee(isAC: client.IsAC, client.MachineId);
  44. if (stationPrice == default)
  45. {
  46. return string.Empty;
  47. }
  48. if (stationPrice.BillingMethod == 1)
  49. {
  50. client.ChargingPrices = await GetChargingPrice(client.IsAC, client.MachineId);
  51. if (string.IsNullOrEmpty(client.ChargingPrices[0].StartTime))
  52. {
  53. client.ChargingPrices = new List<ChargingPrice>();
  54. }
  55. }
  56. displayPriceText = stationPrice.FeeName;
  57. client.BillingMethod = stationPrice.BillingMethod;
  58. client.Currency = stationPrice.Currency;
  59. client.ChargingFeebyHour = stationPrice.ChargingFeebyHour;
  60. client.ParkingFee = stationPrice.ParkingFee;
  61. client.IsBilling = true;
  62. }
  63. catch (Exception ex)
  64. {
  65. logger.LogError("SetDefaultFee", ex.ToString());
  66. }
  67. return displayPriceText;
  68. }
  69. internal Task<StationFee> GetStationFee(bool isAC, string machineId)
  70. {
  71. return isAC ? GetAcStationFee(machineId) : GetDcStationFee(machineId);
  72. }
  73. internal async Task<List<ChargingPrice>> GetChargingPrice(bool isAC, string machineId)
  74. {
  75. var strSql = """
  76. SELECT CAST( [StartTime] as varchar(5)) StartTime,CAST( [EndTime] as varchar(5)) EndTime,[Fee]
  77. FROM [StationMachine]
  78. left join [dbo].[StationFee] on [StationMachine].StationId = StationFee.StationId
  79. WHERE StationMachine.MachineId =@MachineId and StationFee.IsAC=@IsAC;
  80. """;
  81. var parameters = new DynamicParameters();
  82. parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input, 36);
  83. parameters.Add("@IsAC", isAC);
  84. using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
  85. var chargingPriceResult = await conn.QueryAsync<ChargingPrice>(strSql, parameters);
  86. return chargingPriceResult.ToList();
  87. }
  88. private async Task<StationFee> GetAcStationFee(string machineId)
  89. {
  90. var displayPricestrSql = """
  91. SELECT [AC_BillingMethod] as BillingMethod,[AC_FeeName] as FeeName,[AC_Fee] as ChargingFeebyHour, [AC_ParkingFee] as ParkingFee, [Currency]
  92. FROM [StationMachine]
  93. left join[dbo].[Station] on [StationMachine].StationId = Station.[Id]
  94. WHERE StationMachine.MachineId=@MachineId and Station.IsBilling=1;
  95. """;
  96. var parameters = new DynamicParameters();
  97. parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input, 36);
  98. using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
  99. var result = await conn.QueryFirstOrDefaultAsync<StationFee>(displayPricestrSql, parameters);
  100. return result;
  101. }
  102. private async Task<StationFee> GetDcStationFee(string machineId)
  103. {
  104. using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
  105. var displayPricestrSql = """
  106. SELECT [DC_BillingMethod] as BillingMethod,[DC_FeeName] as FeeName,[DC_Fee] as ChargingFeebyHour, [DC_ParkingFee] as ParkingFee, [Currency]
  107. FROM [StationMachine]
  108. left join[dbo].[Station] on [StationMachine].StationId = Station.[Id]
  109. WHERE StationMachine.MachineId=@MachineId and Station.IsBilling=1;
  110. """;
  111. var parameters = new DynamicParameters();
  112. parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input, 36);
  113. var result = await conn.QueryFirstOrDefaultAsync<StationFee>(displayPricestrSql, parameters);
  114. return result;
  115. }
  116. }