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

using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
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
    {
        string mainConnectionString = ConfigurationManager.ConnectionStrings["MainDBContext"].ConnectionString;
        string webConnectionString = ConfigurationManager.ConnectionStrings["WebDBContext"].ConnectionString;

        public LoadingBalanceService()
        {

        }

        public 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);
                string strSql = "Select StationId from [dbo].[StationMachine] where MachineId=@MachineId ; ";
                stationId = conn.ExecuteScalar<Int32>(strSql, parameters);
            }
            return stationId;
        }


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

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

            }

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


            return false;

        }
        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);
                    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);
                        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);
                        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 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);
                string strSql = "Select count(*) from [dbo].[TransactionRecord] where ChargeBoxId=@ChargeBoxId and StopTime='1991/01/01'; ";
                result = conn.ExecuteScalar<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);
                string strSql = "Select Power from [dbo].[LoadingBalance] where MachineId=@MachineId and FinishedOn='1991/01/01'; ";
                result = conn.ExecuteScalar<decimal>(strSql, parameters);
            }

            return result;
        }



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

            if (setting != null && setting.LBMode == 2)
            {
                string machineId = string.Empty;
                decimal ratedPower = GetRatedPower(machineId);
                //找站內最早要充電的交易 下發充電Power & 填寫新給的Power
                using (SqlConnection conn = new SqlConnection(mainConnectionString))
                {
                    var parameters = new DynamicParameters();
                    parameters.Add("@StationId", stationId, DbType.Int32, ParameterDirection.Input);
                    string strSql = "Select M.Id from [dbo].[LoadingBalance] LB,  [dbo].[Machine] M  where LB.StationId=@StationId and  LB.MachineId=M.Id and LB.Power < M.RatedPower and LB.FinishedOn='1991/01/01' order by LB.Id asc; ";
                    machineId = conn.ExecuteScalar<string>(strSql, parameters);
                }

                if (!string.IsNullOrEmpty(machineId))
                {
                    decimal estimatedPwerValue = GetFCFSPower(stationId, machineId, setting.LBCurrent);

                    // renew table
                    UpdateLoadbalanceRecord(stationId, machineId, estimatedPwerValue, null, true);
                   // UpdateLoadbalanceRecord(stationId, machineId, estimatedPwerValue, null);

                    dic.Add(machineId, estimatedPwerValue);
                }


            }

            if (setting != null && setting.LBMode == 1)
            {
                dic = GetAveragePower(stationId, setting.LBCurrent);
                foreach (var kv in dic)
                {
                    if (kv.Value.HasValue)
                    {
                        UpdateLoadbalanceRecord(stationId, kv.Key, 0, DateTime.UtcNow);
                        UpdateLoadbalanceRecord(stationId, kv.Key, kv.Value.Value, null);
                    }

                }
            }

            return dic;

        }

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

            if (setting != null)
            {
                if (setting.LBMode == 1)
                {
                    dic = GetAveragePower(stationId, setting.LBCurrent, machineId);
                    foreach (var kv in dic)
                    {
                        if (kv.Value.HasValue)
                        {
                            UpdateLoadbalanceRecord(stationId, kv.Key, 0, DateTime.UtcNow);
                            UpdateLoadbalanceRecord(stationId, kv.Key, kv.Value.Value, null);
                        }

                    }
                }
                else if (setting.LBMode == 2)
                {
                    dic.Add(machineId, GetFCFSPower(stationId, machineId, setting.LBCurrent));

                    UpdateLoadbalanceRecord(stationId, machineId, 0, DateTime.UtcNow);
                    UpdateLoadbalanceRecord(stationId, machineId, dic[machineId].Value, null);

                }
                else
                {
                    // 把LB TABLE 關閉
                    CloseLoadbalanceRecord(stationId);

                }
            }

            return dic;
        }


        public 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 = conn.Query<LoadBalanceSetting>(strSql, parameters).FirstOrDefault();
            }
            return setting;
        }

        private 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; ";
                machineIds = conn.Query<String>(strSql, parameters).ToList();
            }
            return machineIds;
        }



        private Dictionary<string, decimal?> GetAveragePower(int stationId, int availableCapacity, string machineId = "")
        {
            Dictionary<string, decimal?> dic = new Dictionary<string, decimal?>();
            //總量 * 該樁的額定功率/該站充電中樁的總額定功率
            List<string> _MachineIds = new List<string>();
            int skipCount = 0;
            int size = 200;
            int takeCount = 0;
            int totalRatePower = 0;

            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@StationId", stationId, DbType.Int32, ParameterDirection.Input);
                string strSql = "Select MachineId from [dbo].[LoadingBalance] where StationId=@StationId and FinishedOn='1991/01/01'; ";
                _MachineIds = conn.Query<string>(strSql, parameters).ToList();
            }

            if (!string.IsNullOrEmpty(machineId) && !_MachineIds.Contains(machineId))
            {
                _MachineIds.Add(machineId);
            }

            while (skipCount < _MachineIds.Count())
            {
                takeCount = _MachineIds.Count() - skipCount > size ? size : _MachineIds.Count() - skipCount;

                using (SqlConnection conn = new SqlConnection(mainConnectionString))
                {
                    string strSql = "Select Sum(RatedPower) from [dbo].[Machine] where Id in @machineIds and [Online]=1; ";
                    totalRatePower += conn.ExecuteScalar<Int32>(strSql, new { machineIds = _MachineIds.ToArray() });
                    skipCount += takeCount;
                }
            }

            foreach (var id in _MachineIds)
            {
                int singleRatePower = (int)GetRatedPower(id);
                var value = totalRatePower == 0 ? 0 : availableCapacity * singleRatePower / totalRatePower;
                dic.Add(id, value);
            }

            return dic;

        }

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

        private decimal GetFCFSPower(int stationId, string machineId, int availableCapacity)
        {

            decimal ongoingPower = 0;
            decimal singleRatePower = GetRatedPower(machineId);

            //先找LB 裡面目前下發的Power
            decimal? currentPower = GetCurrentSetting(machineId);

            if (!currentPower.HasValue) currentPower = 0;

            //總量 - 所有正在進行的Power
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@StationId", stationId, DbType.Int32, ParameterDirection.Input);
                string strSql = "Select Sum(Power) from [dbo].[LoadingBalance] where StationId=@StationId and FinishedOn='1991/01/01'; ";
                ongoingPower = conn.ExecuteScalar<Int32>(strSql, parameters);
            }

            return availableCapacity - (ongoingPower - currentPower.Value) > singleRatePower ? singleRatePower :
                (availableCapacity - (ongoingPower - currentPower.Value) > 0 ? availableCapacity - (ongoingPower - currentPower.Value) : 0);
        }


    }
}