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 Newtonsoft.Json.Linq;
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.QueryFirstOrDefaultAsync<StationFee>(new CommandDefinition(
                    commandText: displayPricestrSql,
                    parameters: parameters,
                    cancellationToken: client.DisconnetCancellationToken
                    ));
                if (result == default)
                {
                    client.IsBilling = false;
                    return string.Empty;
                }
                var stationPrice = result;//.First();

                if (stationPrice.BillingMethod == 1)
                {
                    //var chargingPriceResult = await conn.QueryAsync<ChargingPrice>(strSql, parameters);
                    var chargingPriceResult = await conn.QueryAsync<ChargingPrice>(new CommandDefinition(
                        commandText: strSql,
                        parameters: parameters,
                        cancellationToken: client.DisconnetCancellationToken
                        ));
                    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<Dictionary<string, string>> GetCustomerStationEvseConfig(string chargeBoxId, CancellationToken token = default)
    {
        string strSql = """
                SELECT [ConfigureName], [ConfigureSetting]
                FROM [dbo].[StationMachine]
                INNER Join [dbo].[StationMachineConfig] on [dbo].[StationMachine].StationId = [dbo].[StationMachineConfig].StationId
                WHERE [ChargeBoxId] = @ChargeBoxId
                """;

        using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
        DynamicParameters parameters = new DynamicParameters();
        parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
        var configs = await conn.QueryAsync<StationMachineConfig>(new CommandDefinition(strSql, parameters: parameters, cancellationToken: token));
        return configs.ToDictionary(x=>x.ConfigureName, x=>x.ConfigureSetting);
    }

    internal async Task<Dictionary<string, string>> GetEvseStationConfig(int stationId, CancellationToken token = default)
    {
        string strSql = """
                SELECT [ConfigureName], [ConfigureSetting]
                FROM [dbo].[StationMachineConfig]
                WHERE [StationId] = @StationId
                """;

        using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
        DynamicParameters parameters = new DynamicParameters();
        parameters.Add("@StationId", stationId, DbType.Int32, ParameterDirection.Input);
        var configs = await conn.QueryAsync<StationMachineConfig>(new CommandDefinition(strSql, parameters: parameters, cancellationToken: token));
        return configs.ToDictionary(x => x.ConfigureName, x => x.ConfigureSetting);
    }

    internal async Task<int?> GetEvseStation(string chargeboxId, CancellationToken token = default)
    {
        string getStationStrSql = """
                SELECT [StationId]
                FROM [dbo].[StationMachine]
                WHERE [ChargeBoxId] = @ChargeBoxIds;
                """;
        var parameters = new DynamicParameters();
        parameters.Add("@ChargeBoxIds", chargeboxId, direction: ParameterDirection.Input, size: 50);
        using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
        var stationId = await conn.QueryFirstOrDefaultAsync<int?>(new CommandDefinition(getStationStrSql, parameters, cancellationToken: token));
        return stationId;
    }

    internal async Task<Dictionary<string, int>> GetEvseStationPair(List<string> chargeboxIds)
    {
        string getStationStrSql = """
                SELECT [StationId],[ChargeBoxId]
                FROM [dbo].[StationMachine]
                WHERE [ChargeBoxId] IN @ChargeBoxIds;
                """;
        var parameters = new DynamicParameters();
        parameters.Add("@ChargeBoxIds", chargeboxIds, direction: ParameterDirection.Input, size: 50);
        using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
        var configs = await conn.QueryAsync<StationMachine>(getStationStrSql, parameters);
        return configs.ToDictionary(x => x.ChargeBoxId, x => x.StationId);
    }

    internal async Task<Dictionary<int, Dictionary<string, string>>> GetStationEvseConfigs(List<int> stationIds, CancellationToken token = default)
    {
        string getSql = """
                SELECT [StationId],[ConfigureName],[ConfigureSetting]
                FROM [dbo].[StationMachine]
                WHERE [StationId] in @StationIds
                """;
        using SqlConnection conn = await webDbConnectionFactory.CreateAsync(); 
        var parameters = new DynamicParameters();
        parameters.Add("@StationIds", stationIds, direction: ParameterDirection.Input);
        var configs = await conn.QueryAsync<StationMachineConfig>(new CommandDefinition(getSql, parameters, cancellationToken: token));
        return configs
            .GroupBy(x => x.StationId)
            .ToDictionary(
                x => x.Key,
                x => x.ToDictionary(
                    x => x.ConfigureName, 
                    x => x.ConfigureSetting
            ));
    }

    internal async Task<Dictionary<int, Dictionary<string, string>>> GetStationEvseConfigs(CancellationToken token = default)
    {
        string getSql = """
                SELECT [StationId],[ConfigureName],[ConfigureSetting]
                FROM [dbo].[StationMachineConfig]
                """;
        using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
        try
        {
            var configs = await conn.QueryAsync<StationMachineConfig>(new CommandDefinition(getSql, cancellationToken: token));
            return configs
                .GroupBy(x => x.StationId)
                .ToDictionary(
                    x => x.Key,
                    x => x.ToDictionary(
                        x => x.ConfigureName,
                        x => x.ConfigureSetting
                ));
        }
        catch(Exception e)
        {
            logger.LogWarning("StationMachineConfig get failed");
            return new Dictionary<int, Dictionary<string, string>>();
        }
    }

    internal async Task<bool> GetStationIsPeriodEnergyRequired(string chargeBoxId, CancellationToken token = default)
    {
        var stationId = await GetEvseStation(chargeBoxId, token);
        if (stationId is null)
        {
            return false;
        }

        string getSql = """
                SELECT [IsPeriodEnergyRequired]
                FROM [dbo].[Station]
                WHERE [Id] = @StationId
                """; 

        var parameters = new DynamicParameters();
        parameters.Add("@StationId", stationId, direction: ParameterDirection.Input);

        using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
        try
        {
            bool? isPeriodEnergyRequired = await conn.QueryFirstOrDefaultAsync<bool?>(new CommandDefinition(getSql, parameters, cancellationToken: token));
            return isPeriodEnergyRequired is null ? false : isPeriodEnergyRequired.Value;
        }
        catch (Exception e)
        {
            logger.LogWarning(e.Message);
            logger.LogWarning(e.StackTrace);
            return true;
        }
    }

    internal async Task UpdateProtocalVersion(string chargeBoxId, CancellationToken token = default)
    {
        string setSql = """
                UPDATE [dbo].[StationMachine]
                SET [ChargerProtocol] = '1.6'
                WHERE [ChargeBoxId] = @ChargeBoxId
                """;

        var parameters = new DynamicParameters();
        parameters.Add("@ChargeBoxId", chargeBoxId, direction: ParameterDirection.Input);

        using SqlConnection conn = await webDbConnectionFactory.CreateAsync();
        try
        {
            int rows = await conn.ExecuteAsync(new CommandDefinition(setSql, parameters, cancellationToken: token));
        }
        catch (Exception e)
        {
            logger.LogWarning(e.Message);
            logger.LogWarning(e.StackTrace);
        }
    }
}