EVCB_OCPP.WEBAPI/EVCB_OCPP.WEBAPI/Services/ChargingStationService.cs

using Dapper;
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.Configuration;
using System.Data;
using System.Linq;
using Microsoft.Data.SqlClient;
using EVCB_OCPP.WEBAPI.Helpers;
using System.Threading.Tasks;
using System.Diagnostics;
using Microsoft.Extensions.Logging;
using System.Threading;
using static System.Collections.Specialized.BitVector32;
using EVCB_OCPP.WEBAPI.Models.Db;

namespace EVCB_OCPP.WEBAPI.Services
{

    public interface IChargingStationService
    {
        List<Station> GetStationsbyCustomerId(string customerId);

        List<EVSE> GetEVSEsbyStationId(int stationId, DateTime? dateFrom, DateTime? dateTo, int offset, int limit);


        Task<List<Station>> GetStationsbyCustomerIdAsync(string customerId);

    }

    public class ChargingStationService : IChargingStationService
    {
        //string mainConnectionString;
        //string webConnectionString;
        private readonly IServiceProvider serviceProvider;
        private readonly SqlConnectionFactory<WebDBConetext> webConnectionFactory;
        private readonly ILogger<ChargingStationService> logger;

        public ChargingStationService(
            SqlConnectionFactory<WebDBConetext> webConnectionFactory, 
            ILogger<ChargingStationService> logger,
            IServiceProvider serviceProvider)
        {
            //mainConnectionString = configuration.GetConnectionString("MainDBContext");
            //webConnectionString = configuration.GetConnectionString("WebDBContext");
            this.serviceProvider = serviceProvider;
            this.webConnectionFactory = webConnectionFactory;
            this.logger = logger;
        }

        public List<Station> GetStationsbyCustomerId(string customerId)
        {
            return GetStationsbyCustomerIdAsync(customerId).Result;
        }

        public async Task<List<Station>> GetStationsbyCustomerIdAsync(string customerId)
        {
            var watch = Stopwatch.StartNew();
            List<long> times = new();
            List<Station> _stations = new List<Station>();

            var parameters = new DynamicParameters();
            parameters.Add("@CustomerId", customerId, DbType.AnsiString, ParameterDirection.Input, 36);
            using (SqlConnection conn = await webConnectionFactory.CreateAsync())
            {
                string strSql = "Select Id, Name ,Latitude,Longitude,Address from [dbo].[Station] where CustomerId=@CustomerId; ";
                var result = await conn.QueryAsync<Station>(strSql, parameters);
                _stations = result is null ? new List<Station>() : result.ToList();
            }
            times.Add(watch.ElapsedMilliseconds);

            //if (_stations == null)
            //{
            //    return _stations;
            //}

            //List<Task> ts = new List<Task>();
            //foreach (var station in _stations)
            //{
            //    station.Coordinates = new GetLocation() { Latitude = station.Latitude, Longitude = station.Longitude };
            //    ts.Add(Task.Run(async () => { station.EVSEs = await GetEVSEsbyStationIdAsync(station.Id, null, null, -1, 100000); } ));
            //}
            //await Task.WhenAll(ts);

            //await Parallel.ForEachAsync(_stations, async (station, c) => {
            //    station.EVSEs = await GetEVSEsbyStationIdAsync(station.Id, null, null, -1, 100000);
            //});

            Dictionary<int, List<string>> stationMachinePair = await GetStationMachinePairAsync(_stations.Select(x => x.Id));
            times.Add(watch.ElapsedMilliseconds);

            foreach (var station in _stations)
            {
                station.Coordinates = new GetLocation() { Latitude = station.Latitude, Longitude = station.Longitude };
                //station.EVSEs = await GetEVSEsbyStationIdAsync(station.Id, null, null, -1, 100000);
                station.EVSEs = stationMachinePair.ContainsKey(station.Id)
                    ? await GetEVSEsBytEVSEs(stationMachinePair[station.Id], null, null, -1, 100000)
                    : new List<EVSE> { };
                station.EVSEs.ForEach(x => x.StationId = station.Id);

                times.Add(watch.ElapsedMilliseconds);
            }

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

            return _stations;
        }

        public bool ContainsStation(string customerId, int stationId)
        {
            bool isContains = false;

            var parameters = new DynamicParameters();
            parameters.Add("@CustomerId", customerId, DbType.AnsiString, ParameterDirection.Input, 36);
            parameters.Add("@Id", stationId, DbType.Int32, ParameterDirection.Input);
            using (SqlConnection conn = webConnectionFactory.Create())
            {
                string strSql = "Select count(*) from [dbo].[Station] where CustomerId=@CustomerId and Id=@Id; ";
                isContains = conn.ExecuteScalar<Int32>(strSql, parameters) > 0 ? true : false;
            }

            return isContains;
        }


        public List<EVSE> GetEVSEsbyStationId(int stationId, DateTime? dateFrom, DateTime? dateTo, int offset = -1, int limit = 1000)
        {
            return GetEVSEsbyStationIdAsync(stationId,dateFrom,dateTo,offset,limit).Result;
        }

        public async Task<List<EVSE>> GetEVSEsbyStationIdAsync(int stationId, DateTime? dateFrom, DateTime? dateTo, int offset = -1, int limit = 1000)
        {
            //var watch = Stopwatch.StartNew();
            //List<long> time = new();

            List<EVSE> _chargePoints = new List<EVSE>();
            var parameters = new DynamicParameters();
            parameters.Add("@StationId", stationId, DbType.Int16, ParameterDirection.Input);
            List<string> machineIds = new List<string>();
            using (SqlConnection conn = await webConnectionFactory.CreateAsync())
            {
                string strSql = "Select MachineId from [dbo].[StationMachine] where StationId=@StationId; ";
                machineIds = (await conn.QueryAsync<String>(strSql, parameters)).ToList();
            }
            //time.Add(watch.ElapsedMilliseconds);

            var result = await GetEVSEsBytEVSEs(machineIds, dateFrom, dateTo, offset, limit);
            result.ForEach(x => x.StationId = stationId);

            return result;
        }

        public Task<List<EVSE>> GetEVSEsBytEVSEs(List<string> machineIds, DateTime? dateFrom, DateTime? dateTo, int offset = -1, int limit = 1000)
        {
            return GetEVSEsBytEVSEsWithDapper(machineIds, dateFrom, dateTo, offset, limit);
            //return GetEVSEsBytEVSEsWithSP(machineIds, dateFrom, dateTo, offset, limit);
        }

        public async Task<List<EVSE>> GetEVSEsBytEVSEsWithSP(List<string> machineIds, DateTime? dateFrom, DateTime? dateTo, int offset = -1, int limit = 1000)
        {
            var watch = Stopwatch.StartNew();
            List<EVSE> _chargePoints = new List<EVSE>();
            ChargePointService _CPService = serviceProvider.GetRequiredService<ChargePointService>();// new ChargePointService();
            int startIndex = offset == -1 ? 0 : offset;
            limit += startIndex;

            while (startIndex < limit && startIndex < machineIds.Count)
            {
                var _machineUpdateOn = await _CPService.GetLastUpdatedTimebyMachineIdAsync(machineIds[startIndex]);
                //time.Add(watch.ElapsedMilliseconds);

                if (dateFrom.HasValue && _machineUpdateOn < dateFrom.Value.ToUniversalTime())
                {
                    limit++;
                    startIndex++;
                    continue;
                }

                if (dateFrom.HasValue && dateTo.HasValue && (_machineUpdateOn < dateFrom.Value.ToUniversalTime() || _machineUpdateOn > dateTo.Value.ToUniversalTime()))
                {
                    limit++;
                    startIndex++;
                    continue;
                }

                var _machine = await _CPService.GetBasicInfobyIdAsync(machineIds[startIndex]);
                //time.Add(watch.ElapsedMilliseconds);

                if (_machine != null)
                {
                    _machine.LastUpdated = _machineUpdateOn;
                    //_machine.StationId = stationId;
                    _chargePoints.Add(_machine);
                }
                else
                {
                    limit++;
                }

                startIndex++;

            }

            watch.Stop();
            if (watch.ElapsedMilliseconds > 1000)
            {
                logger.LogWarning($"GetEVSEsBytEVSEs {watch.ElapsedMilliseconds}");
            }

            return _chargePoints;
        }

        public async Task<List<EVSE>> GetEVSEsBytEVSEsWithDapper(List<string> machineIds, DateTime? dateFrom, DateTime? dateTo, int offset = -1, int limit = 1000)
        {
            var watch = Stopwatch.StartNew();
            List<long> times = new();

            List<EVSE> _chargePoints = new List<EVSE>();
            ChargePointService _CPService = serviceProvider.GetRequiredService<ChargePointService>();// new ChargePointService();
            int quota = limit;
            int startIndex = offset == -1 ? 0 : offset;
            limit += startIndex;

            var machineIdChunks = machineIds.Chunk(100);
            foreach(var machineIdChunk in machineIdChunks)
            {
                Dictionary<string, DateTime> _machineUpdateOnPairs = await _CPService.GetLastUpdatedTimebyMachineIdAsync(machineIdChunk);
                times.Add(watch.ElapsedMilliseconds);

                _machineUpdateOnPairs = _machineUpdateOnPairs
                    .Where(x => !dateFrom.HasValue || x.Value > dateFrom.Value.ToUniversalTime())
                    .Where(x => !dateTo.HasValue || x.Value < dateTo.Value.ToUniversalTime())
                    .ToDictionary(x=>x.Key,x=>x.Value);


                Dictionary<string, EVSE> pairs = await _CPService.GetBasicInfobyIdAsync(_machineUpdateOnPairs.Select(x=> x.Key));
                times.Add(watch.ElapsedMilliseconds);

                foreach (var pair in pairs)
                {
                    var machine = pair.Value;
                    machine.LastUpdated = _machineUpdateOnPairs[pair.Key];
                    _chargePoints.Add(machine);

                    if (_chargePoints.Count > quota)
                        break;
                }
                
                //foreach (var pair in _machineUpdateOnPairs)
                //{

                //    var _machine = await _CPService.GetBasicInfobyIdAsync(pair.Key);

                //    if (_machine != null)
                //    {
                //        _machine.LastUpdated = pair.Value;
                //        //_machine.StationId = stationId;
                //        _chargePoints.Add(_machine);
                //    }

                //    if (_chargePoints.Count > quota)
                //        break;
                //}

                if (_chargePoints.Count > quota)
                    break;
            }

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

            return _chargePoints;

            while (startIndex < limit && startIndex < machineIds.Count)
            {
                DateTime _machineUpdateOn = await _CPService.GetLastUpdatedTimebyMachineIdAsync(machineIds[startIndex]);
                //time.Add(watch.ElapsedMilliseconds);

                if (dateFrom.HasValue && _machineUpdateOn < dateFrom.Value.ToUniversalTime())
                {
                    limit++;
                    startIndex++;
                    continue;
                }

                if (dateFrom.HasValue && dateTo.HasValue && (_machineUpdateOn < dateFrom.Value.ToUniversalTime() || _machineUpdateOn > dateTo.Value.ToUniversalTime()))
                {
                    limit++;
                    startIndex++;
                    continue;
                }

                var _machine = await _CPService.GetBasicInfobyIdAsync(machineIds[startIndex]);
                //time.Add(watch.ElapsedMilliseconds);

                if (_machine != null)
                {
                    _machine.LastUpdated = _machineUpdateOn;
                    //_machine.StationId = stationId;
                    _chargePoints.Add(_machine);
                }
                else
                {
                    limit++;
                }

                startIndex++;

            }

            watch.Stop();
            if (watch.ElapsedMilliseconds > 1000)
            {
                logger.LogWarning($"GetEVSEsBytEVSEs {watch.ElapsedMilliseconds}");
            }

            return _chargePoints;
        }

        public async Task<Dictionary<int, List<string>>> GetStationMachinePairAsync(IEnumerable<int> stationIds)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@StationIds", stationIds);
            string strSql = "SELECT StationId, MachineId FROM [dbo].[StationMachine] WHERE StationId IN @StationIds; ";

            using SqlConnection conn = await webConnectionFactory.CreateAsync();
            var result = await conn.QueryAsync<StationMachineDto>(strSql, parameters);

            return result.GroupBy(x => x.StationId).ToDictionary(x => x.Key, x => x.Select(x => x.MachineId).ToList());
        }
    }
}