EVCB_OCPP.WEBAPI/EVCB_OCPP.WEBAPI/Services/ChargePointService.cs

using Dapper;
using EVCB_OCPP.Packet.Messages.SubTypes;
using EVCB_OCPP.WEBAPI.Models;
using EVCB_OCPP.WEBAPI.Models.WebAPI;
using EVCB_OCPP.WEBAPI.Models.WebAPI.Dto;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Microsoft.Data.SqlClient;
using EVCB_OCPP.Domain;
using EVCB_OCPP.WEBAPI.Helpers;
using System.Threading.Tasks;
using System.Diagnostics;
using Microsoft.Extensions.Logging;
using NLog;

namespace EVCB_OCPP.WEBAPI.Services
{
    public interface IChargePointService
    {
        int GetNumberofConnectors(string chargeBoxId);

    }


    public class ChargePointService : IChargePointService
    {
        //readonly string mainConnectionString;
        //readonly string meterConnectionString;
        private readonly IServiceProvider serviceProvider;
        private readonly SqlConnectionFactory<MainDBContext> mainDbConneciotnFactory;
        private readonly SqlConnectionFactory<MeterValueDBContext> meterValueDbConnectionFactory;
        private readonly MainDbService mainDbService;
        private readonly MeterValueDbService meterValueDbService;
        private readonly ILogger<ChargePointService> logger;

        public ChargePointService(
            IServiceProvider serviceProvider,
            SqlConnectionFactory<MainDBContext> mainDbConneciotnFactory,
            SqlConnectionFactory<MeterValueDBContext> meterValueDbConnectionFactory,
            MainDbService mainDbService,
            MeterValueDbService meterValueDbService,
            ILogger<ChargePointService> logger)
        {
            //mainConnectionString = configuration.GetConnectionString("MainDBContext");
            //meterConnectionString = configuration.GetConnectionString("MeterValueDBContext");
            this.serviceProvider = serviceProvider;
            this.mainDbConneciotnFactory = mainDbConneciotnFactory;
            this.meterValueDbConnectionFactory = meterValueDbConnectionFactory;
            this.mainDbService = mainDbService;
            this.meterValueDbService = meterValueDbService;
            this.logger = logger;
        }

        public DateTime GetLastUpdatedTimebyMachineId(string machineId)
        {
            return GetLastUpdatedTimebyMachineIdAsync(machineId).Result;
        }

        public async Task<DateTime> GetLastUpdatedTimebyMachineIdAsync(string machineId)
        {
            DateTime lastUpdatedOn = DateTime.UtcNow;
            using (SqlConnection conn = await mainDbConneciotnFactory.CreateAsync())
            {
                var parameters = new DynamicParameters();

                parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input, 36);
                parameters.Add("@LastUpdatedTime", lastUpdatedOn, DbType.DateTime, ParameterDirection.Output);
                await conn.ExecuteAsync("GetBasicInfoLastUpdatedTimeById", parameters, commandType: System.Data.CommandType.StoredProcedure);
                DateTime? time = null;
                time = parameters.Get<DateTime?>("@LastUpdatedTime");

                lastUpdatedOn = DateTime.SpecifyKind(time ?? DateTime.UtcNow, DateTimeKind.Utc);

            }

            return lastUpdatedOn;

        }

        public async Task<Dictionary<string, DateTime>> GetLastUpdatedTimebyMachineIdAsync(IEnumerable<string> machineIds)
        {
            string cmd = """
                SELECT Id, MAX(LastUpdatedTime) as LastUpdatedTime
                FROM 
                (
                	SELECT BasicMachine.Id,
                	CASE
                		WHEN cs.CreatedOn > BasicMachine.CreatedOn
                			THEN cs.CreatedOn
                		WHEN  BasicMachine.CreatedOn > BasicMachine.OfflineOn
                			THEN BasicMachine.CreatedOn
                		ELSE  BasicMachine.OfflineOn
                		END AS LastUpdatedTime 
                	FROM
                	(SELECT Id, OfflineOn, CreatedOn, ChargeBoxId FROM [dbo].[Machine] WHERE Id IN @MachineIds) BasicMachine
                	LEFT JOIN [dbo].[ConnectorStatus] cs
                	On cs.ChargeBoxId = BasicMachine.ChargeBoxId 
                ) t
                GROUP BY t.Id
                """;

            var parameters = new DynamicParameters();
            parameters.Add("@MachineIds", machineIds);

            using SqlConnection conn = await mainDbConneciotnFactory.CreateAsync();
            var tmp = await conn.QueryAsync<LastUpdatedTimeDto>(cmd, parameters);
            //return tmp.Select(x => new KeyValuePair<string, DateTime>(x.Id, x.LastUpdatedTime));
            return tmp.ToDictionary(x => x.Id, x => x.LastUpdatedTime);
        }

        public string GetMachineIdbyChargeBoxId(string chargeBoxId)
        {
            string machineId = string.Empty;
            using (SqlConnection conn = mainDbConneciotnFactory.Create())
            {
                var parameters = new DynamicParameters();
                parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
                string strSql = "Select Id from [dbo].[Machine] where ChargeBoxId=@ChargeBoxId and IsDelete=0; ";
                machineId = conn.QueryFirstOrDefault<string>(strSql, parameters);
            }

            return machineId;

        }


        public string GetVersionbyChargeBoxId(string chargeBoxId)
        {
            string version = string.Empty;
            using (SqlConnection conn = mainDbConneciotnFactory.Create())
            {
                var parameters = new DynamicParameters();
                parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
                string strSql = "Select BoardVersions from [dbo].[Machine] where ChargeBoxId=@ChargeBoxId and IsDelete=0; ";
                version = conn.QueryFirstOrDefault<string>(strSql, parameters);
            }
            return version;
        }

        public EVSE GetEVSEsbyChargeBoxId(string chargeboxid, DateTime? dateFrom, DateTime? dateTo)
        {
            string machineId = GetMachineIdbyChargeBoxId(chargeboxid);
            ChargePointService _CPService = serviceProvider.GetRequiredService<ChargePointService>();//new ChargePointService();

            var _machineUpdateOn = _CPService.GetLastUpdatedTimebyMachineId(machineId);

            var _machine = _CPService.GetBasicInfobyId(machineId);
            _machine.LastUpdated = _machineUpdateOn;


            return _machine;
        }


        public EVSE GetBasicInfobyId(string machineId)
        {
            return GetBasicInfobyIdAsync(machineId).Result;
        }

        public async Task<EVSE> GetBasicInfobyIdAsync(string machineId)
        {
            EVSE cp = new EVSE();
            Machine _machine = new Machine();
            using (SqlConnection conn = await mainDbConneciotnFactory.CreateAsync())
            {
                var parameters = new DynamicParameters();
                parameters.Add("@Id", machineId, DbType.String, ParameterDirection.Input);
                string strSql = "Select ChargeBoxId,ChargePointSerialNumber,ChargePointModel,RatedPower,Online,OfflineOn ,GunAmt,Latitude,Longitude,ConnectorType from [dbo].[Machine] where Id=@Id and IsDelete=0; ";
                _machine = await conn.QueryFirstOrDefaultAsync<Machine>(strSql, parameters);
            }
            if (_machine != null)
            {
                cp.ChargeBoxSystemID = _machine.ChargePointModel + _machine.ChargePointSerialNumber;
                cp.ChargeBoxId = _machine.ChargeBoxId;
                cp.NumberofConnectors = _machine.GunAmt;
                cp.RatedPower = _machine.RatedPower;
                cp.Status = _machine.Online ? Status.Available : Status.Offline;
                cp.LastUpdated = _machine.CreatedOn > _machine.OfflineOn ? _machine.CreatedOn : _machine.OfflineOn;
                cp.Coordinates = new GeoLocation() { Latitude = _machine.Latitude, Longitude = _machine.Longitude };
                cp.Connectors = new List<Connector>();
                var _Connectors = await GetConnectorStatusAsync(_machine.ChargeBoxId);
                for (int i = 1; i <= _machine.GunAmt; i++)
                {
                    var _RefConnector = _Connectors.Where(x => x.ConnectorId == i).FirstOrDefault();
                    cp.Connectors.Add(new Connector()
                    {
                        ConnectorId = _RefConnector is null ? i : _RefConnector.ConnectorId,
                        Status = cp.Status == Status.Offline ? Status.Offline : _RefConnector is null ? Status.Unknown : ConvertConnectorStatus(_RefConnector.Status),
                        ConnectorType = ConvertConnectorType(int.Parse(_machine.ConnectorType.Split(',')[i - 1])),
                        FaultMessage = _RefConnector is null ? "Connector not found" : ((ChargePointErrorCode)_RefConnector.ChargePointErrorCodeId).ToString() + (string.IsNullOrEmpty(_RefConnector.VendorErrorCode) ? "" : "-" + _RefConnector.VendorErrorCode)

                    });
                }
            }
            return _machine == null ? null : cp;
        }

        public async Task<Dictionary<string, EVSE>> GetBasicInfobyIdAsync(IEnumerable<string> machineIds)
        {
            Dictionary<string, EVSE> cps = new();
            Dictionary<string , Machine> _machines = new();

            var parameters = new DynamicParameters();
            parameters.Add("@MachineIds", machineIds);
            string strSql = """
                    SELECT Id,ChargeBoxId,ChargePointSerialNumber,ChargePointModel,RatedPower,Online,OfflineOn ,GunAmt,Latitude,Longitude,ConnectorType 
                    FROM [dbo].[Machine]
                    WHERE Id IN @MachineIds and IsDelete=0; 
                    """;

            using (SqlConnection conn = await mainDbConneciotnFactory.CreateAsync())
            {
                var result = await conn.QueryAsync<Machine>(strSql, parameters);
                _machines = result.ToDictionary(x => x.Id, x => x);
            }

            var MachineConnectors = await GetConnectorStatusAsync(machineIds);

            foreach (var machineId in machineIds)
            {
                if (!_machines.ContainsKey(machineId))
                {
                    cps.Add(machineId, null);
                    continue;
                }

                var _machine = _machines[machineId];
                EVSE cp = new();

                cp.ChargeBoxSystemID = _machine.ChargePointModel + _machine.ChargePointSerialNumber;
                cp.ChargeBoxId = _machine.ChargeBoxId;
                cp.NumberofConnectors = _machine.GunAmt;
                cp.RatedPower = _machine.RatedPower;
                cp.Status = _machine.Online ? Status.Available : Status.Offline;
                cp.LastUpdated = _machine.CreatedOn > _machine.OfflineOn ? _machine.CreatedOn : _machine.OfflineOn;
                cp.Coordinates = new GeoLocation() { Latitude = _machine.Latitude, Longitude = _machine.Longitude };
                cp.Connectors = new List<Connector>();
                //var _Connectors = await GetConnectorStatusAsync(_machine.ChargeBoxId);
                var _Connectors = MachineConnectors[machineId];
                for (int i = 1; i <= _machine.GunAmt; i++)
                {
                    var _RefConnector = _Connectors.Where(x => x.ConnectorId == i).FirstOrDefault();
                    cp.Connectors.Add(new Connector()
                    {
                        ConnectorId = _RefConnector is null ? i : _RefConnector.ConnectorId,
                        Status = cp.Status == Status.Offline ? Status.Offline : _RefConnector is null ? Status.Unknown : ConvertConnectorStatus(_RefConnector.Status),
                        ConnectorType = ConvertConnectorType(int.Parse(_machine.ConnectorType.Split(',')[i - 1])),
                        FaultMessage = _RefConnector is null ? "Connector not found" : ((ChargePointErrorCode)_RefConnector.ChargePointErrorCodeId).ToString() + (string.IsNullOrEmpty(_RefConnector.VendorErrorCode) ? "" : "-" + _RefConnector.VendorErrorCode)

                    });
                }

                cps.Add(_machine.Id, cp);
            }

            return cps;
        }


        public List<ConnectorStatus> GetConnectorStatus(string chargeBoxId)
        {
            return GetConnectorStatusAsync(chargeBoxId).Result;
        }

        public async Task<List<ConnectorStatus>> GetConnectorStatusAsync(string chargeBoxId)
        {

            List<ConnectorStatus> _Connectors = new List<ConnectorStatus>();
            using (SqlConnection conn = await mainDbConneciotnFactory.CreateAsync())
            {
                var parameters = new DynamicParameters();
                parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
                string strSql = "Select ConnectorId,Status,ChargePointErrorCodeId,VendorErrorCode from [dbo].[ConnectorStatus] where ChargeBoxId=@ChargeBoxId order by  ConnectorId; ";
                _Connectors = (await conn.QueryAsync<ConnectorStatus>(strSql, parameters)).SkipWhile(x => x.ConnectorId == 0).ToList();
            }

            return _Connectors;
        }

        public async Task<Dictionary<string,List<ConnectorStatus>>> GetConnectorStatusAsync(IEnumerable<string> chargeBoxIds)
        {
            Dictionary<string, List<ConnectorStatus>> toReturn = new();
            IEnumerable<ConnectorStatus> _Connectors;// = new List<ConnectorStatus>();
            var parameters = new DynamicParameters();
            parameters.Add("@ChargeBoxIds", chargeBoxIds);
            string strSql = """
                SELECT ChargeBoxId,ConnectorId,Status,ChargePointErrorCodeId,VendorErrorCode 
                FROM [dbo].[ConnectorStatus] 
                WHERE ChargeBoxId IN @ChargeBoxIds
                ORDER BY ConnectorId; 
                """;

            using (SqlConnection conn = await mainDbConneciotnFactory.CreateAsync())
            {
                _Connectors = (await conn.QueryAsync<ConnectorStatus>(strSql, parameters)).SkipWhile(x => x.ConnectorId == 0);//.ToList();
            }

            var tempDic = _Connectors.GroupBy(x => x.ChargeBoxId).ToDictionary(x => x.Key, x => x.ToList());
            foreach (var chargeBoxId in chargeBoxIds)
            {
                if (tempDic.ContainsKey(chargeBoxId))
                {
                    toReturn.Add(chargeBoxId, tempDic[chargeBoxId]);
                    continue;
                }

                toReturn.Add(chargeBoxId, new List<ConnectorStatus>());
            }

            return toReturn;

            //return _Connectors;
        }


        public int GetNumberofConnectors(string chargeBoxId)
        {
            int count = 0;

            if (string.IsNullOrEmpty(chargeBoxId)) return -1;
            if (Exists(chargeBoxId))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
                using (SqlConnection conn = mainDbConneciotnFactory.Create())
                {
                    string strSql = "Select GunAmt from [dbo].[Machine] where ChargeBoxId=@ChargeBoxId  and IsDelete=0; ";
                    count = conn.ExecuteScalarAsync<int>(strSql, parameters, null, EVCBConfiguration.DB_DefaultConnectionTimeout).Result;
                }

            }
            return count;
        }

        public bool Exists(string chargeBoxId)
        {
            bool exists = false;

            if (string.IsNullOrEmpty(chargeBoxId)) return exists;
            var parameters = new DynamicParameters();
            parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
            using (SqlConnection conn = mainDbConneciotnFactory.Create())
            {
                string strSql = "Select count(*) from [dbo].[Machine] where ChargeBoxId=@ChargeBoxId  and IsDelete=0; ";
                exists = conn.ExecuteScalar<bool>(strSql, parameters, null, EVCBConfiguration.DB_DefaultConnectionTimeout);
            }
            return exists;
        }

        public int GetConnectorwithOngoingTransaction(string chargeBoxId, int transactionId)
        {
            int connectorId = -1;

            if (string.IsNullOrEmpty(chargeBoxId)) return connectorId;

            var parameters = new DynamicParameters();
            parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
            parameters.Add("@TransactionId", transactionId, DbType.Int32, ParameterDirection.Input);
            using (SqlConnection conn = mainDbConneciotnFactory.Create())
            {
                string strSql = "Select ConnectorId from [dbo].[TransactionRecord] where ChargeBoxId=@ChargeBoxId and Id= @TransactionId and StopTime='1991/01/01'; ";
                connectorId = conn.QueryFirstOrDefault<Int32>(strSql, parameters);
            }

            return connectorId;
        }




        public bool IsTransactionRunning(string chargeBoxId, int transactionId)
        {
            bool exists = false;

            if (string.IsNullOrEmpty(chargeBoxId)) return exists;

            var parameters = new DynamicParameters();
            parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
            parameters.Add("@TransactionId", transactionId, DbType.Int32, ParameterDirection.Input);
            using (SqlConnection conn = mainDbConneciotnFactory.Create())
            {
                string strSql = "Select Id from [dbo].[TransactionRecord] where ChargeBoxId=@ChargeBoxId and Id= @TransactionId and StopTime='1991/01/01'; ";
                int id = conn.QueryFirstOrDefault<Int32>(strSql, parameters);//.FirstOrDefault();
                exists = id > 0 ? true : false;
            }
            return exists;
        }

        /// <summary>
        /// 取得電樁或充電槍的狀態 
        /// </summary>
        /// <param name="chargeBoxId">Charge Box Id</param>
        /// <param name="connectorId">充電槍號(0代表樁/槍號從1開始)</param>
        /// <returns>依據OCPP狀態回覆 NULL表示離線</returns>
        public ChargePointStatus? GetChargePointCurrentSatus(string chargeBoxId, int connectorId = 0)
        {
            ChargePointStatus? _status = null;

            if (string.IsNullOrEmpty(chargeBoxId)) return _status;

            var parameters = new DynamicParameters();
            parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
            parameters.Add("@ConnectorId", connectorId, DbType.Int32, ParameterDirection.Input);

            using (SqlConnection conn = mainDbConneciotnFactory.Create())
            {
                string strSql = "Select online from [dbo].[Machine] where ChargeBoxId=@ChargeBoxId  and IsDelete=0; ";
                bool online = conn.ExecuteScalar<bool>(strSql, parameters, null, EVCBConfiguration.DB_DefaultConnectionTimeout);

                if (online)
                {
                    string connectorStrSql = "Select Status from [dbo].[ConnectorStatus] where ChargeBoxId=@ChargeBoxId and ConnectorId=@ConnectorId; ";

                    int _statusfromdb = conn.QueryFirstOrDefault<Int32>(connectorStrSql, parameters);//.FirstOrDefault();

                    _status = (_statusfromdb <= 0 && connectorId == 0) ? ChargePointStatus.Available : (_statusfromdb <= 0 ? ((ChargePointStatus?)null) : ((ChargePointStatus)_statusfromdb));

                }

            }

            return _status;
        }

        public bool IsOnline(string chargeBoxId)
        {
            bool online = false;

            if (string.IsNullOrEmpty(chargeBoxId)) return online;

            var parameters = new DynamicParameters();
            parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);


            using (SqlConnection conn = mainDbConneciotnFactory.Create())
            {
                string strSql = "Select online from [dbo].[Machine] where ChargeBoxId=@ChargeBoxId  and IsDelete=0; ";
                online = conn.ExecuteScalar<bool>(strSql, parameters, null, EVCBConfiguration.DB_DefaultConnectionTimeout);
            }

            return online;
        }


        public ValueTask<bool> ContainsChargePointAsync(string chargeBoxId, string customerId)
        {
            return mainDbService.ContainsChargePointAsync(chargeBoxId, customerId);
        }

        public List<TransasctionData> GetActiveSessionInfo(string chargeBoxId, List<Measurand> requiredMeasurands, int? sessionId = null, string idTag = "")
        {
            return GetActiveSessionInfoAsync(chargeBoxId, requiredMeasurands, sessionId: sessionId,idTag: idTag).Result;
        }

        public async Task<List<TransasctionData>> GetActiveSessionInfoAsync(string chargeBoxId, List<Measurand> requiredMeasurands,string customerId = null, int? sessionId = null, string idTag = null)
        {
            List<ConnectorMeterValue> meterValues = new List<ConnectorMeterValue>();
            ConnectorMeterValueModel meterModel = null;
            List<TransasctionData> transactionDatas = null;

            if (string.IsNullOrEmpty(chargeBoxId)) 
                return transactionDatas;

            var watch = Stopwatch.StartNew();
            var times = new List<long>();

            transactionDatas = await mainDbService.GetActiveTransactionAsync(chargeBoxId, customerId, sessionId, idTag);
            times.Add(watch.ElapsedMilliseconds);

            if (transactionDatas.Count == 0)
            {
                return new List<TransasctionData>();
            }

            List<ConnectorMeterValueModel> metervalues = new();
            var minStartTime = transactionDatas.Select(x => x.StartTime).Min();
            while (minStartTime <= DateTime.Now)
            {
                var result = await meterValueDbService.GetChargeBoxTransactionMeterValues(
                    minStartTime, chargeBoxId, transactionDatas.Select(x => x.Id), requiredMeasurands.Select(x => (int)x));
                metervalues.AddRange(result);

                minStartTime = minStartTime.AddDays(1);

                times.Add(watch.ElapsedMilliseconds);
            }

            var transIdMeterValuesPair = metervalues.GroupBy(x => x.TransactionId).ToDictionary(x => x.Key, x => x.ToList());

            foreach (var trans in transactionDatas)
            {
                if (trans.MeterValues == null)
                {
                    trans.MeterValues = new List<ConnectorMeterValue>();
                }

                if (!transIdMeterValuesPair.ContainsKey(trans.Id))
                {
                    continue;
                }
                var transMeterValues = transIdMeterValuesPair[trans.Id];

                foreach(var _meterModel in transMeterValues)
                {
                    trans.MeterValues.Add(new ConnectorMeterValue()
                    {
                        ChargeBoxId = _meterModel.ChargeBoxId,
                        ConnectorId = _meterModel.ConnectorId,
                        CreatedOn = _meterModel.CreatedOn,
                        Context = _meterModel.ContextId < 1 ? (ReadingContext?)null : (ReadingContext?)_meterModel.ContextId,
                        Format = _meterModel.FormatId < 1 ? (ValueFormat?)null : (ValueFormat?)_meterModel.FormatId,
                        Location = _meterModel.LocationId < 1 ? (Location?)null : (Location?)_meterModel.LocationId,
                        Measurand = _meterModel.MeasurandId < 1 ? (Measurand?)null : (Measurand?)_meterModel.MeasurandId,
                        Phase = _meterModel.PhaseId < 1 ? (Phase?)null : (Phase?)_meterModel.PhaseId,
                        Unit = _meterModel.UnitId < 1 ? (UnitOfMeasure?)UnitOfMeasure.Wh : (UnitOfMeasure?)_meterModel.UnitId,
                        Value = _meterModel.Value,
                        TransactionId = _meterModel.TransactionId

                    });
                }
            }

            times.Add(watch.ElapsedMilliseconds);
            watch.Stop();
            if (watch.ElapsedMilliseconds > 1000)
            {
                logger.LogWarning($"{nameof(GetActiveSessionInfoAsync)} {string.Join("/", times)}");
            }

            //for (int i = 0; i < requiredMeasurands.Count; i++)
            //{
            //    for (int j = 0; j < transactionDatas.Count; j++)
            //    {
            //        var parameters = new DynamicParameters();
            //        parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
            //        parameters.Add("@TransactionId", transactionDatas[j].Id, DbType.Int32, ParameterDirection.Input);
            //        parameters.Add("@MeasurandId", requiredMeasurands[i], DbType.Int32, ParameterDirection.Input);

            //        try
            //        {
            //            int retry = 0;
            //            string date = DateTime.UtcNow.ToString("yyMMdd");

            //            while (retry < 2)
            //            {
            //                retry++;
            //                using (SqlConnection conn = await meterValueDbConnectionFactory.CreateAsync())
            //                {
            //                    string strSql = $"""
            //                        SELECT Top(1) * from [dbo].[ConnectorMeterValueRecord{date}]
            //                        WHERE ChargeBoxId=@ChargeBoxId and TransactionId=@TransactionId and MeasurandId=@MeasurandId 
            //                        order by CreatedOn desc;
            //                        """;
            //                    meterModel = await conn.QueryFirstOrDefaultAsync<ConnectorMeterValueModel>(strSql, parameters, commandTimeout:EVCBConfiguration.DB_DefaultConnectionTimeout);//.FirstOrDefault();
            //                    if (meterModel == null)
            //                    {
            //                        date = transactionDatas[j].StartTime.ToString("yyMMdd");
            //                    }
            //                    else
            //                    {
            //                        retry = 2;
            //                    }

            //                }
            //            }

            //            if (meterModel != null)
            //            {
            //                if (transactionDatas[j].MeterValues == null)
            //                {
            //                    transactionDatas[j].MeterValues = new List<ConnectorMeterValue>();
            //                }

            //                transactionDatas[j].MeterValues.Add(new ConnectorMeterValue()
            //                {
            //                    ChargeBoxId = meterModel.ChargeBoxId,
            //                    ConnectorId = meterModel.ConnectorId,
            //                    CreatedOn = meterModel.CreatedOn,
            //                    Context = meterModel.ContextId < 1 ? (ReadingContext?)null : (ReadingContext?)meterModel.ContextId,
            //                    Format = meterModel.FormatId < 1 ? (ValueFormat?)null : (ValueFormat?)meterModel.FormatId,
            //                    Location = meterModel.LocationId < 1 ? (Location?)null : (Location?)meterModel.LocationId,
            //                    Measurand = meterModel.MeasurandId < 1 ? (Measurand?)null : (Measurand?)meterModel.MeasurandId,
            //                    Phase = meterModel.PhaseId < 1 ? (Phase?)null : (Phase?)meterModel.PhaseId,
            //                    Unit = meterModel.UnitId < 1 ? (UnitOfMeasure?)UnitOfMeasure.Wh : (UnitOfMeasure?)meterModel.UnitId,
            //                    Value = meterModel.Value,
            //                    TransactionId = meterModel.TransactionId

            //                });



            //            }
            //        }
            //        catch (Exception ex)
            //        {
            //            break;
            //        }
            //    }


            //}

            return transactionDatas;
        }


        public List<SessionDetail> GetSessionDetail(string chargeBoxId, int sessionId, string idTag, DateTime startTime, DateTime stopTime)
        {
            List<SessionDetail> detail = new List<SessionDetail>();
            List<TransactionRecordModel> transactionModel = null;
            if (string.IsNullOrEmpty(chargeBoxId)) return detail;


            var parameters = new DynamicParameters();

            try
            {

                bool restrictedRange = false;
                if (startTime != new DateTime(1991, 1, 1))
                {
                    restrictedRange = true;
                    parameters.Add("@startTime", startTime, DbType.DateTime, ParameterDirection.Input);
                    parameters.Add("@stopTime", stopTime, DbType.DateTime, ParameterDirection.Input);
                }

                using (SqlConnection conn = mainDbConneciotnFactory.Create())
                {
                    string strSql = string.Empty;
                    if (sessionId >= 0 && string.IsNullOrEmpty(idTag))
                    {
                        parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
                        parameters.Add("@TransactionId", sessionId, DbType.Int32, ParameterDirection.Input);
                        strSql = "Select Top(1) * from [dbo].[TransactionRecord] where ChargeBoxId=@ChargeBoxId and Id=@TransactionId and StopTime!='1991-01-01 00:00:00.000' " + (restrictedRange ? " and StartTime >=@startTime and  StartTime <=@stopTime" : "") + " Order by Id desc ;";
                    }
                    else if (!string.IsNullOrEmpty(idTag) && sessionId < 0)
                    {
                        if (!string.IsNullOrEmpty(chargeBoxId))
                        {
                            parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);
                        }

                        parameters.Add("@StartIdTag", idTag, DbType.String, ParameterDirection.Input, 20);
                        strSql = "Select  * from [dbo].[TransactionRecord] where " + (!string.IsNullOrEmpty(chargeBoxId) ? " ChargeBoxId=@ChargeBoxId and  " : "") + "  StartIdTag=@StartIdTag   and StopTime!='1991-01-01 00:00:00.000' " + (restrictedRange ? " and StartTime >=@startTime and  StartTime <=@stopTime" : "") + " Order by Id desc  ;";

                    }
                    else
                    {

                        parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input, 50);

                        strSql = "Select  * from [dbo].[TransactionRecord] where ChargeBoxId=@ChargeBoxId  and StopTime!='1991-01-01 00:00:00.000' " + (restrictedRange ? " and StartTime >=@startTime and  StartTime <=@stopTime" : "") + " Order by Id desc  ;";

                    }

                    transactionModel = conn.Query<TransactionRecordModel>(strSql, parameters, null, true, EVCBConfiguration.DB_DefaultConnectionTimeout).ToList();


                }

                foreach (var item in transactionModel)
                {
                    detail.Add(new SessionDetail()
                    {
                        ChargeBoxId = item.ChargeBoxId,
                        ConnectorId = (int)item.ConnectorId,
                        IdTag = item.StartIdTag,
                        MeterStart = item.MeterStart,
                        MeterStop = item.MeterStop,
                        StartTime = item.StartTime.ToString(EVCBConfiguration.UTC_DATETIMEFORMAT),
                        StopTime = item.StopTime.ToString(EVCBConfiguration.UTC_DATETIMEFORMAT),
                        StopReason = item.StopReasonId < 1 ? Reason.Local.ToString() : ((Reason)item.StopReasonId).ToString()
                    });

                }

            }
            catch (Exception ex)
            {

            }


            return detail;
        }


        private Status ConvertConnectorStatus(int value)
        {
            Status result = Status.Unknown;

            switch (value)
            {
                case 1://Available
                    {
                        result = Status.Available;
                    }
                    break;
                case 2://Preparing
                    {
                        result = Status.Preparing;
                    }
                    break;
                case 3://Charging
                    {
                        result = Status.Charging;
                    }
                    break;
                case 4://SuspendedEVSE
                    {
                        result = Status.SuspendedEVSE;
                    }
                    break;
                case 5://SuspendedEV
                    {
                        result = Status.SuspendedEV;
                    }
                    break;
                case 6://Finishing
                    {
                        result = Status.Finishing;
                    }
                    break;
                case 8://Unavailable
                    {
                        result = Status.Unavailable;
                    }
                    break;
                case 7://Reserved
                    {
                        result = Status.Reserved;
                    }
                    break;
                case 9://Faulted
                    {
                        result = Status.Faulted;
                    }
                    break;
                default:
                    break;



            }
            return result;

        }



        private ConnectorType ConvertConnectorType(int value)
        {
            ConnectorType result = (ConnectorType)value;

            return result;
        }

    }

    file class LastUpdatedTimeDto
    {
        public DateTime LastUpdatedTime { get; set;}

        public string Id;
    }
}