EVCB_OCPP.OCPPServer.16J/EVCB_OCPP.WSServer/Service/LoadingBalanceService.cs

using Dapper;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace EVCB_OCPP.WSServer.Service
{
    public class LoadBalanceSetting
    {
        public int StationId { set; get; }

        public int LBMode { set; get; }

        public int LBCurrent { set; get; }

    }

    public class LoadingBalanceService
    {
        //ConcurrentDictionary<int, object> _lockDic = new ConcurrentDictionary<int, object>();
        ConcurrentDictionary<int, SemaphoreSlim> _semaphoreDic = new ConcurrentDictionary<int, SemaphoreSlim>();
        private readonly string mainConnectionString;
        private readonly string webConnectionString;

        public LoadingBalanceService(IConfiguration configuration)
        {
            mainConnectionString = configuration.GetConnectionString("MainDBContext");
            webConnectionString = configuration.GetConnectionString("WebDBContext");
        }

        public async Task<int> GetStationIdByMachineId(string machineId)
        {
            int stationId = 0;
            using (SqlConnection conn = new SqlConnection(webConnectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input, 36);
                string strSql = "Select StationId from [dbo].[StationMachine] where MachineId=@MachineId ; ";
                stationId = await conn.ExecuteScalarAsync<Int32>(strSql, parameters);
            }
            return stationId;
        }


        async public Task<bool> IsNeedtoCancelSetting(int stationId, string machineId, string chargeBoxId)
        {
            var setting = await GetLoadBalance(stationId);
            if (setting == null) return false;

            var semaphore = GetSemaphore(stationId);
            await semaphore.WaitAsync();
            if (setting.LBMode > 0 && setting.LBMode < 3 && !await IsStillInTransactions(chargeBoxId))
            {
                // renew table
                //    UpdateLoadbalanceRecord(stationId, machineId, 0, DateTime.UtcNow);
                return true;

            }

            if (setting.LBMode >= 3 || setting.LBMode < 1)
            {
                //   CloseLoadbalanceRecord(stationId);

            }
            semaphore.Release();

            return false;

        }

        //private object GetLock(int stationId)
        //{

        //    if (!_lockDic.ContainsKey(stationId))
        //    {
        //        _lockDic.TryAdd(stationId, new object());
        //    }

        //    return _lockDic[stationId];

        //}

        private SemaphoreSlim GetSemaphore(int stationId)
        {

            if (!_semaphoreDic.ContainsKey(stationId))
            {
                _semaphoreDic.TryAdd(stationId, new SemaphoreSlim(1));
            }

            return _semaphoreDic[stationId];

        }

        private void CloseLoadbalanceRecord(int stationId)
        {
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {

                var parameters = new DynamicParameters();
                parameters.Add("@StationId", stationId, DbType.Int32, ParameterDirection.Input);
                parameters.Add("@FinishedOn", DateTime.UtcNow, DbType.DateTime, ParameterDirection.Input);
                string strSql = "Update  [dbo].[LoadingBalance]  SET FinishedOn=@FinishedOn where StationId=@StationId and FinishedOn='1991/01/01'; ";
                conn.Execute(strSql, parameters);

            }
        }


        private void UpdateLoadbalanceRecord(int stationId, string machineId, decimal power, DateTime? finishedOn, bool keepgoing = false)
        {
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                if (finishedOn.HasValue)
                {
                    var parameters = new DynamicParameters();
                    parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input, 36);
                    parameters.Add("@FinishedOn", finishedOn.Value, DbType.DateTime, ParameterDirection.Input);
                    string strSql = "Update  [dbo].[LoadingBalance]  SET FinishedOn=@FinishedOn where MachineId=@MachineId and FinishedOn='1991/01/01'; ";
                    conn.Execute(strSql, parameters);
                }
                else
                {
                    if (keepgoing)
                    {
                        var parameters = new DynamicParameters();
                        parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input, 36);
                        parameters.Add("@Power", power, DbType.Decimal, ParameterDirection.Input);
                        string strSql = "Update  [dbo].[LoadingBalance]  SET Power=@Power where MachineId=@MachineId and FinishedOn='1991/01/01'; ";
                        conn.Execute(strSql, parameters);
                    }
                    else
                    {
                        var parameters = new DynamicParameters();
                        parameters.Add("@StationId", stationId, DbType.Int32, ParameterDirection.Input);
                        parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input, 36);
                        parameters.Add("@Power", power, DbType.Decimal, ParameterDirection.Input);
                        parameters.Add("@CreatedOn", DateTime.UtcNow, DbType.DateTime, ParameterDirection.Input);
                        parameters.Add("@FinishedOn", new DateTime(1991, 1, 1, 0, 0, 0, DateTimeKind.Utc), DbType.DateTime, ParameterDirection.Input);

                        string strSql = "INSERT INTO [dbo].[LoadingBalance] " +
                         "([StationId],[MachineId],[Power],[CreatedOn],[FinishedOn]) " +
                         "VALUES(@StationId,@MachineId,@Power,@CreatedOn,@FinishedOn);";

                        conn.Execute(strSql, parameters);
                    }

                }

            }
        }


        private async Task<bool> IsStillInTransactions(string chargeBoxId)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
                string strSql = "Select count(*) from [dbo].[TransactionRecord] where ChargeBoxId=@ChargeBoxId and StopTime='1991/01/01'; ";
                result = await conn.ExecuteScalarAsync<bool>(strSql, parameters);
            }

            return result;
        }

        private decimal? GetCurrentSetting(string machineId)
        {
            decimal? result = (decimal?)null;

            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input, 36);
                string strSql = "Select Power from [dbo].[LoadingBalance] where MachineId=@MachineId and FinishedOn='1991/01/01'; ";
                result = conn.ExecuteScalar<decimal>(strSql, parameters);
            }

            return result;
        }

        async public Task<Dictionary<string, decimal?>> GetSettingPower(int stationId)
        {
            Dictionary<string, decimal?> dic = new Dictionary<string, decimal?>();
            var setting = await GetLoadBalance(stationId);
            if (setting == null) return null;

            var semaphore = GetSemaphore(stationId);
            await semaphore.WaitAsync();
            if (setting != null)
            {
                if (setting.LBMode == 1)
                {
                    dic = await GetAveragePower(stationId, setting.LBCurrent);
                }

            }
            semaphore.Release();

            return dic;
        }


        async public Task<LoadBalanceSetting> GetLoadBalance(int stationId)
        {
            LoadBalanceSetting setting = null;
            using (SqlConnection conn = new SqlConnection(webConnectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@StationId", stationId, DbType.Int32, ParameterDirection.Input);

                string strSql = "Select LBMode,LBCurrent from [dbo].[Station] where Id=@StationId ; ";
                setting = await conn.QueryFirstOrDefaultAsync<LoadBalanceSetting>(strSql, parameters);
                //etting = result.FirstOrDefaultAsync();
            }
            return setting;
        }

        async private Task<List<string>> GetIdsbyStationId(int stationId)
        {
            List<string> machineIds = new List<string>();
            using (SqlConnection conn = new SqlConnection(webConnectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@StationId", stationId, DbType.Int16, ParameterDirection.Input);
                string strSql = "Select MachineId from [dbo].[StationMachine] where StationId=@StationId; ";
                var result = await conn.QueryAsync<String>(strSql, parameters);
                machineIds = result.ToList();
            }
            return machineIds;
        }



        async private Task<Dictionary<string, decimal?>> GetAveragePower(int stationId, int availableCapacity)
        {
            Dictionary<string, decimal?> dic = new Dictionary<string, decimal?>();
            availableCapacity = (int)(availableCapacity * 1000 / 1.05M);
            int keepPower = 0;
            //讀取上一次斷線但還沒充完電的分配量
            (var offlineCPs, keepPower) = await GetChargeBoxIdbyOfflineCharging(stationId);
            //扣除Keep充電功率  =  分配充電量
            var totalPower = availableCapacity - keepPower;

            if (totalPower > 0)
            {
                //總量 * 該樁的額定功率/該站充電中樁的總額定功率
                var onlineChargingCPs = await GetOnlineChargerwithCharging(stationId);
                if (onlineChargingCPs.Count > 0)
                {
                    int singlePower = (int)Decimal.Divide(totalPower, onlineChargingCPs.Count);

                    foreach (var id in onlineChargingCPs)
                    {
                        dic.Add(id, singlePower);
                    }
                }

            }

            return dic;

        }

        async private Task<List<string>> GetOnlineChargerwithCharging(int stationId)
        {
            List<string> results = new List<string>();
            List<string> machineIds = await GetIdsbyStationId(stationId);
            List<string> chargeboxids = new List<string>();
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                string onlineChargerSql = "Select ChargeBoxId from [dbo].[Machine] where Id in @machineIds and [Online]=1; ";
                var onlineResult = await conn.QueryAsync<string>(onlineChargerSql, new { machineIds = machineIds.ToArray() });
                chargeboxids = onlineResult.ToList();
                foreach (var chargeboxid in chargeboxids)
                {
                    string txSql = "SELECT TOP(1) [Id] from [dbo].[TransactionRecord] where ChargeBoxId=@ChargeBoxId and StopTime = '1991-01-01 00:00:00.000'; ";
                    var txId = await conn.ExecuteScalarAsync<Int64>(txSql, new { ChargeBoxId = chargeboxid });
                    if (txId > 0)
                    {
                        results.Add(chargeboxid);
                    }
                }

            }
            return results;
        }

        /// <summary>
        /// 取得斷線樁號
        /// </summary>
        /// <param name="stationId">站點代號</param>
        /// <param name="ratedPowers">總額定功率</param>
        /// <returns></returns>
        private async Task<(List<string>,int ratedPowers)> GetChargeBoxIdbyOfflineCharging(int stationId)
        {
            List<string> machineIds = GetIdsbyStationId(stationId).Result;
            List<string> result = new List<string>();
            int ratedPowers = 0;
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {

                string offlineChargerSql = "Select ChargeBoxId from [dbo].[Machine] where Id in @machineIds and [Online]=0; ";
                result = (await conn.QueryAsync<string>(offlineChargerSql, new { machineIds = machineIds })).ToList();
                foreach (var charger in result)
                {
                    string txSql = "SELECT TOP(1) [Id] from [dbo].[TransactionRecord] where ChargeBoxId=@ChargeBoxId and StopTime = '1991-01-01 00:00:00.000'; ";
                    var txId = await conn.ExecuteScalarAsync<Int64>(txSql, new { ChargeBoxId = charger });
                    if (txId > 0)
                    {
                        string ratedPowerSql = "Select Sum(RatedPower) from [dbo].[Machine] where ChargeBoxId=@ChargeBoxId and [Online]=0; ";
                        ratedPowers += await conn.ExecuteScalarAsync<int>(ratedPowerSql, new { ChargeBoxId = charger });
                    }
                }
            }
            ratedPowers *= 1000;
            return (result, ratedPowers);
        }

        private decimal GetRatedPowerbyChargeBoxId(string chargeBoxId)
        {
            decimal ratedPower = 0;
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@machineId", chargeBoxId, DbType.String, ParameterDirection.Input, 36);
                string strSql = "Select RatedPower from [dbo].[Machine] where Id=@machineId; ";
                ratedPower = conn.ExecuteScalar<Int32>(strSql, parameters);
            }
            return ratedPower;
        }

        private decimal GetRatedPowerbyId(string machineId)
        {
            decimal ratedPower = 0;
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@machineId", machineId, DbType.String, ParameterDirection.Input, 36);
                string strSql = "Select RatedPower from [dbo].[Machine] where Id=@machineId; ";
                ratedPower = conn.ExecuteScalar<Int32>(strSql, parameters);
            }
            return ratedPower;
        }



    }
}