using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
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 param = new DynamicParameters();
param.Add("ChargeBoxId", chargeboxid, DbType.String, ParameterDirection.Input, 50);
var txId = await conn.ExecuteScalarAsync<Int64>(txSql, param);
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 param = new DynamicParameters();
param.Add("ChargeBoxId", charger, DbType.String, ParameterDirection.Input, 50);
var txId = await conn.ExecuteScalarAsync<Int64>(txSql, param);
if (txId > 0)
{
string ratedPowerSql = "Select Sum(RatedPower) from [dbo].[Machine] where ChargeBoxId=@ChargeBoxId and [Online]=0; ";
ratedPowers += await conn.ExecuteScalarAsync<int>(ratedPowerSql, param);
}
}
}
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;
}
}
}