EVCB_OCPP.OCPPServer.16J/EVCB_OCPP.WSServer/Service/DbService/ConnectorStatusDbService.cs

using Dapper;
using EVCB_OCPP.Domain;
using EVCB_OCPP.Domain.ConnectionFactory;
using EVCB_OCPP.WSServer.Dto;
using EVCB_OCPP.WSServer.Helper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using System.Data;
using System.Data.Common;
using System.Diagnostics;

namespace EVCB_OCPP.WSServer.Service.DbService;

public class ConnectorStatusDbService
{
	private readonly IDbContextFactory<OnlineRecordDBContext> onlineRecordDbContextFactory;
	private readonly ISqlConnectionFactory<OnlineRecordDBContext> sqlConnectionFactory;
	private readonly ILoggerFactory loggerFactory;

	
	private readonly ILogger logger;
	private readonly Queue<string> _existTables = new();


	public ConnectorStatusDbService(
		IDbContextFactory<OnlineRecordDBContext> onlineRecordDbContextFactory,
		ISqlConnectionFactory<OnlineRecordDBContext> sqlConnectionFactory,
		ILogger<ConnectorStatusDbService> logger,
		ILoggerFactory loggerFactory,
		IConfiguration configuration		
		)
	{
		this.onlineRecordDbContextFactory = onlineRecordDbContextFactory;
		this.sqlConnectionFactory = sqlConnectionFactory;
		this.loggerFactory = loggerFactory;	
		this.logger = logger;

	

	}

	public Task InsertAsync(string chargeBoxId, byte connectorId, int status, DateTime createdOn
		   , string errorInfo, string vendorId, string vendorErrorCode, int chargePointErrorCodeId)
	{
		var param = new InsertConnectorStatusParam(chargeBoxId,  connectorId,  status,   errorInfo
			,  vendorId,  createdOn,  vendorErrorCode,  chargePointErrorCodeId);

		return InsertAsync(param);
	}

	public Task InsertAsync(InsertConnectorStatusParam param)
	{	
		return InsertWithDapper(param);
	}
	

	private async Task InsertWithDapper(InsertConnectorStatusParam param)
	{
		var watch = Stopwatch.StartNew();
		long t0, t1;

		if (!await GetTableExist(param.createdOn))
		{
			t0 = watch.ElapsedMilliseconds;
			await InsertWithStoredProcedure(param);
			watch.Stop();
			t1 = watch.ElapsedMilliseconds;
			if (t1 > 500)
			{
				logger.LogWarning("ConnectorStatusRecord InsertWithStoredProcedure {0}/{1}", t0, t1);
			}
			return;
		}

		t0 = watch.ElapsedMilliseconds;
		var tableName = GetTableName(param.createdOn);

		await InsertWithNoCheckDapper(tableName, param);

		watch.Stop();
		t1 = watch.ElapsedMilliseconds;
		if (t1 > 700)
		{
			logger.LogWarning("ConnectorStatusRecord Dapper {0}/{1}", t0, t1);
		}
	}


	private async Task InsertWithNoCheckDapper(string tableName, InsertConnectorStatusParam data, SqlConnection conn = null, DbTransaction trans = null)
	{
		string command = $"""
                INSERT INTO {tableName} ([ChargeBoxId],[ConnectorId]
                ,[Status],[CreatedOn],[ChargePointErrorCodeId],[ErrorInfo]
                ,[VendorId],[VendorErrorCode])
                VALUES (@ChargeBoxId,@ConnectorId, @Status, @CreatedOn,@ChargePointErrorCodeId, @ErrorInfo, @VendorId, @VendorErrorCode);
                """;

		bool isLocalConnection = conn is null;
		SqlConnection connection = isLocalConnection ? await sqlConnectionFactory.CreateAsync() : conn;

		var parameters = new DynamicParameters();
		parameters.Add("ConnectorId", data.connectorId, DbType.Int16);
		parameters.Add("Status", data.status, DbType.Int32);
		parameters.Add("ErrorInfo", data.errorInfo, DbType.String, size: 50);
		parameters.Add("VendorId", data.vendorId, DbType.String, size: 255);
		parameters.Add("CreatedOn", data.createdOn, DbType.DateTime);
		parameters.Add("VendorErrorCode", data.vendorErrorCode, DbType.String, size: 100);
		parameters.Add("ChargePointErrorCodeId", data.chargePointErrorCodeId, DbType.Int32);
		parameters.Add("ChargeBoxId", data.chargeBoxId, DbType.String, size: 50);		

		await connection.ExecuteAsync(command, parameters, trans);

		if (isLocalConnection)
		{
			connection.Dispose();
		}
	}

	

	private async ValueTask<bool> GetTableExist(DateTime tableDateTime)
	{
		var tableName = GetTableName(tableDateTime);
		if (_existTables.Contains(tableName))
		{
			return true;
		}

		FormattableString checkTableSql = $"SELECT Count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = {tableName}";

		using var db = await onlineRecordDbContextFactory.CreateDbContextAsync();
		var resultList = db.Database.SqlQuery<int>(checkTableSql)?.ToList();

		if (resultList is not null && resultList.Count > 0 && resultList[0] > 0)
		{
			_existTables.Enqueue(tableName);
			if (_existTables.Count > 30)
			{
				_existTables.TryDequeue(out _);
			}
			return true;
		}

		return false;
	}

	private async Task InsertWithStoredProcedure(InsertConnectorStatusParam param)
	{
		using var db = await onlineRecordDbContextFactory.CreateDbContextAsync();

		string sp = "[dbo].[uspInsertConnectorStatusRecord] @ChargeBoxId," +
"@ConnectorId,@Status,@CreatedOn,@ChargePointErrorCodeId,@ErrorInfo,@VendorId,@VendorErrorCode";

		List<SqlParameter> parameter = new List<SqlParameter>();
		parameter.AddInsertConnectorStatusRecordSqlParameters(
			chargeBoxId: param.chargeBoxId
			, connectorId: param.connectorId
			, status: param.status
			, errorInfo: param.errorInfo
			, vendorId: param.vendorId
			, createdOn: param.createdOn
			, vendorErrorCode: param.vendorErrorCode
			, chargePointErrorCodeId: param.chargePointErrorCodeId	);

		await db.Database.ExecuteSqlRawAsync(sp, parameter.ToArray());
	}

	private static string GetTableName(DateTime dateTime)
		=> $"ConnectorStatusRecord{dateTime:yyMMdd}";


}

public record InsertConnectorStatusParam(string chargeBoxId, byte connectorId, int status, string  errorInfo
			, string vendorId, DateTime createdOn, string vendorErrorCode, int chargePointErrorCodeId);