using Dapper;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
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;
ConcurrentDictionary<int, object> _lockDic = new ConcurrentDictionary<int, object>();
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;
lock (GetLock(stationId))
{
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 object GetLock(int stationId)
{
if (!_lockDic.ContainsKey(stationId))
{
_lockDic.TryAdd(stationId, new object());
}
return _lockDic[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);
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;
lock (GetLock(stationId))
{
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;
lock (GetLock(stationId))
{
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);
}
}
}