EVCB_OCPP.OCPPServer.16J/EVCB_OCPP.WSServer/Service/DbService/WebDbService.cs

using Dapper;
using EVCB_OCPP.Domain.ConnectionFactory;
using EVCB_OCPP.WSServer.Dto;
using EVCB_OCPP.WSServer.Helper;
using EVCB_OCPP.WSServer.Service.WsService;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EVCB_OCPP.WSServer.Service.DbService;

public class WebDbService
{
    private readonly ISqlConnectionFactory<WebDBConetext> webDbConnectionFactory;
    private readonly ILogger<WebDbService> logger;

    public WebDbService(ISqlConnectionFactory<WebDBConetext> webDbConnectionFactory, ILogger<WebDbService> logger)
    {
        this.webDbConnectionFactory = webDbConnectionFactory;
        this.logger = logger;
        //this.webConnectionString = configuration.GetConnectionString("WebDBContext");
    }

    //private readonly string webConnectionString;

    public async Task<List<string>> GetDenyModelNames(CancellationToken token = default)
    {
        using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
        string strSql = """
                SELECT [Value] 
                FROM [dbo].[KernelConfig]
                where SystemKey = 'DenyModelNames';
                """;

        var result = await conn.QueryFirstOrDefaultAsync<string>(
            new CommandDefinition(strSql, cancellationToken: token)
            );

        return result.Split(',').ToList();

    }

    async public Task<string> SetDefaultFee(WsClientData client)
    {
        string displayPriceText = string.Empty;
        string charingPriceText = string.Empty;

        if (string.IsNullOrEmpty(client.ChargeBoxId)) return displayPriceText;

        try
        {
            using (SqlConnection conn = await webDbConnectionFactory.CreateAsync())
            {
                var parameters = new DynamicParameters();
                parameters.Add("@MachineId", client.MachineId, DbType.String, ParameterDirection.Input, 36);
                string displayPricestrSql = "";
                string strSql = "";

                if (client.IsAC)
                {
                    displayPricestrSql = """
                    SELECT [AC_BillingMethod] as BillingMethod,[AC_FeeName] as FeeName,[AC_Fee] as ChargingFeebyHour ,[AC_ParkingFee] as ParkingFee, [Currency] 
                    FROM[StationMachine] left join[dbo].[Station]
                    on[StationMachine].StationId = Station.[Id] 
                    where StationMachine.MachineId=@MachineId and Station.IsBilling=1;
                    """;

                    strSql = """
                    SELECT CAST( [StartTime] as varchar(5)) StartTime,CAST( [EndTime] as varchar(5)) EndTime,[Fee] 
                    FROM[StationMachine] left join [dbo].[StationFee]
                    on[StationMachine].StationId = StationFee.StationId  
                    where StationMachine.MachineId =@MachineId and StationFee.IsAC=1; 
                    """;
                }
                else
                {
                    displayPricestrSql = """
                    SELECT [DC_BillingMethod] as BillingMethod,[DC_FeeName] as FeeName,[DC_Fee] as ChargingFeebyHour ,[DC_ParkingFee] as ParkingFee, [Currency] 
                    FROM[StationMachine] left join[dbo].[Station] 
                    on[StationMachine].StationId = Station.[Id]
                    where StationMachine.MachineId=@MachineId and Station.IsBilling=1; 
                    """;

                    strSql = """
                    SELECT CAST( [StartTime] as varchar(5)) StartTime,CAST( [EndTime] as varchar(5)) EndTime,[Fee]
                    FROM[StationMachine] left join [dbo].[StationFee]
                    on[StationMachine].StationId = StationFee.StationId
                    where StationMachine.MachineId =@MachineId and StationFee.IsAC=0;
                    """;

                }
                var result = await conn.QueryAsync<StationFee>(displayPricestrSql, parameters);
                if (result.Count() == 0)
                {
                    return string.Empty;
                }
                var stationPrice = result.First();

                if (stationPrice.BillingMethod == 1)
                {
                    var chargingPriceResult = await conn.QueryAsync<ChargingPrice>(strSql, parameters);
                    client.ChargingPrices = chargingPriceResult.ToList();
                    if (string.IsNullOrEmpty(client.ChargingPrices[0].StartTime))
                    {
                        client.ChargingPrices = new List<ChargingPrice>();
                    }
                }

                displayPriceText = stationPrice.FeeName;
                client.BillingMethod = stationPrice.BillingMethod;
                client.Currency = stationPrice.Currency;
                client.ChargingFeebyHour = stationPrice.ChargingFeebyHour;
                client.ParkingFee = stationPrice.ParkingFee;
                client.IsBilling = true;
            }
        }
        catch (Exception ex)
        {
            logger.LogError("SetDefaultFee", ex.ToString());
        }

        return displayPriceText;
    }

    internal async Task<List<KeyValuePair<string, string>>> GetCustomerStationEvseConfig(string chargeBoxId)
    {
        string getStationStrSql = """
                SELECT [StationId] 
                FROM [dbo].[StationMachine]
                where [ChargeBoxId] = @ChargeBoxId;
                """;
        string getConfigStrSql = """
                SELECT [ConfigureName],[ConfigureSetting]
                FROM [dbo].[StationMachineConfig]
                where [StationId] = @StationId;
                """;

        using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
        DynamicParameters parameters = new DynamicParameters();
        parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 25);
        int stationId = await conn.QueryFirstOrDefaultAsync<int>(getStationStrSql, parameters);
        if (stationId == default)
        {
            return null;
        }
        parameters = new DynamicParameters();
        parameters.Add("@StationId", stationId, DbType.Int32, ParameterDirection.Input);
        var configs = await conn.QueryAsync<StationMachineConfig>(getConfigStrSql, parameters);
        return configs.Select(x => new KeyValuePair<string, string>(x.ConfigureName, x.ConfigureSetting)).ToList();
    }
}