EVCB_OCPP.OCPPServer.16J/EVCB_OCPP.WSServer/Service/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.Logging;
using System.Data;

namespace EVCB_OCPP.WSServer.Service;

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();
    }

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

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

        try
        {
            StationFee stationPrice = await GetStationFee(isAC: client.IsAC, client.MachineId);
            if (stationPrice == default)
            {
                return string.Empty;
            }

            if (stationPrice.BillingMethod == 1)
            {
                client.ChargingPrices = await GetChargingPrice(client.IsAC, client.MachineId);
                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 Task<StationFee> GetStationFee(bool isAC, string machineId)
    {
        return isAC ? GetAcStationFee(machineId) : GetDcStationFee(machineId);
    }

    internal async Task<List<ChargingPrice>> GetChargingPrice(bool isAC, string machineId)
    {
        var 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=@IsAC; 
            """;
        var parameters = new DynamicParameters();
        parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input, 36);
        parameters.Add("@IsAC", isAC);

        using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
        var chargingPriceResult = await conn.QueryAsync<ChargingPrice>(strSql, parameters);
        return chargingPriceResult.ToList();
    }

    private async Task<StationFee> GetAcStationFee(string machineId)
    {
        var 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;
            """;
        var parameters = new DynamicParameters();
        parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input, 36);

        using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
        var result = await conn.QueryFirstOrDefaultAsync<StationFee>(displayPricestrSql, parameters);
        return result;
    }

    private async Task<StationFee> GetDcStationFee(string machineId)
    {
        using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
        var 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;
            """;
        var parameters = new DynamicParameters();
        parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input, 36);
        var result = await conn.QueryFirstOrDefaultAsync<StationFee>(displayPricestrSql, parameters);
        return result;
    }
}