EVCB_OCPP.WEBAPI/EVCB_OCPP.WEBAPI/Services/Integration/ChargePoint16Service.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 System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

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

        string GetVersionbyChargeBoxId(string chargeBoxId);

        List<TransasctionData> GetActiveSessionInfo(string chargeBoxId, List<Measurand> requiredMeasurands, string sessionId = "", string idTag = "");

        List<SessionDetail> GetSessionDetail(string chargeBoxId, string sessionId, string idTag, DateTime startTime, DateTime stopTime);

        EVSE GetEVSEsbyChargeBoxId(string chargeboxid, DateTime? dateFrom, DateTime? dateTo);

    }


    public class ChargePoint16Service : IChargePointService
    {
        string mainConnectionString = ConfigurationManager.ConnectionStrings["MainDBContext"].ConnectionString;
        string meterConnectionString = ConfigurationManager.ConnectionStrings["MeterValueDBContext"].ConnectionString;

        public DateTime GetLastUpdatedTimebyMachineId(string machineId)
        {
            DateTime lastUpdatedOn = DateTime.UtcNow;
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                var parameters = new DynamicParameters();

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

                }
                catch (Exception ex)
                {
                    time = DateTime.UtcNow;
                }


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


            }

            return lastUpdatedOn;

        }


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

            }

            return machineId;

        }

        public DateTime GetMachineUpdatedOn(string chargeBoxId)
        {
            DateTime dt = new DateTime();
            string machineId = string.Empty;
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input);
                string strSql = "Select HeartbeatUpdatedOn from [dbo].[Machine] where ChargeBoxId=@ChargeBoxId and IsDelete=0; ";
                dt = conn.Query<DateTime>(strSql, parameters).FirstOrDefault();

            }

            return dt;

        }


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

        public EVSE GetEVSEsbyChargeBoxId(string chargeboxid, DateTime? dateFrom, DateTime? dateTo)
        {
            string machineId = GetMachineIdbyChargeBoxId(chargeboxid);
            ChargePoint16Service _CPService = new ChargePoint16Service();

            var _machineUpdateOn = _CPService.GetLastUpdatedTimebyMachineId(machineId);

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


            return _machine;
        }


        public EVSE GetBasicInfobyId(string machineId)
        {
            EVSE cp = new EVSE();
            Machine _machine = new Machine();
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                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 = conn.Query<Machine>(strSql, parameters).FirstOrDefault();
            }
            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 = GetConnectorStatus(_machine.ChargeBoxId);
                for (int i = 1; i <= _machine.GunAmt; i++)
                {
                    var _RefConnector = _Connectors.Where(x => x.ConnectorId == i).FirstOrDefault();
                    if (_RefConnector == null) continue;

                    cp.Connectors.Add(new Connector()
                    {
                        ConnectorId = _RefConnector == null ? i : _RefConnector.ConnectorId,
                        Status = cp.Status == Status.Offline ? Status.Offline : _RefConnector == null ? Status.Unknown : ConvertConnectorStatus(_RefConnector.Status),
                        ConnectorType = ConvertConnectorType(int.Parse(_machine.ConnectorType.Split(',')[i - 1])),
                        FaultMessage = ((ChargePointErrorCode)_RefConnector.ChargePointErrorCodeId).ToString() + (string.IsNullOrEmpty(_RefConnector.VendorErrorCode) ? "" : "-" + _RefConnector.VendorErrorCode)

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




        public List<ConnectorStatus> GetConnectorStatus(string chargeBoxId)
        {

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



            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);
                using (SqlConnection conn = new SqlConnection(mainConnectionString))
                {
                    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);
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                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);
            parameters.Add("@TransactionId", transactionId, DbType.Int32, ParameterDirection.Input);
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                string strSql = "Select ConnectorId from [dbo].[TransactionRecord] where ChargeBoxId=@ChargeBoxId and Id= @TransactionId and StopTime='1991/01/01'; ";
                connectorId = conn.Query<Int32>(strSql, parameters).FirstOrDefault();
            }

            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);
            parameters.Add("@TransactionId", transactionId, DbType.Int32, ParameterDirection.Input);
            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                string strSql = "Select Id from [dbo].[TransactionRecord] where ChargeBoxId=@ChargeBoxId and Id= @TransactionId and StopTime='1991/01/01'; ";
                int id = conn.Query<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);
            parameters.Add("@ConnectorId", connectorId, DbType.Int32, ParameterDirection.Input);

            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                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.Query<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);


            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                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 bool ContainsChargePoint(string chargeBoxId, string customerId)
        {
            bool existed = false;

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

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


            using (SqlConnection conn = new SqlConnection(mainConnectionString))
            {
                string strSql = "Select Count(*) from [dbo].[Machine] where ChargeBoxId=@ChargeBoxId and CustomerId=@CustomerId  and IsDelete=0; ";
                existed = conn.ExecuteScalar<bool>(strSql, parameters, null, EVCBConfiguration.DB_DefaultConnectionTimeout);
            }

            return existed;
        }

        public List<TransasctionData> GetActiveSessionInfo(string chargeBoxId, List<Measurand> requiredMeasurands, string sessionId = "", string idTag = "")
        {
            List<ConnectorMeterValue> meterValues = new List<ConnectorMeterValue>();
            ConnectorMeterValueModel meterModel = null;
            List<TransasctionData> transactionDatas = null;
            if (string.IsNullOrEmpty(chargeBoxId)) return transactionDatas;


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


                using (SqlConnection conn = new SqlConnection(mainConnectionString))
                {
                    string date = DateTime.UtcNow.ToString("yyMMdd");
                    string strSql = string.Empty;
                    if (string.IsNullOrEmpty(sessionId) && string.IsNullOrEmpty(idTag))
                    {
                        strSql = "Select Id, ConnectorId, StartTime,Fee, StartIdTag from TransactionRecord " +
                            "where StopTime = '1991-01-01 00:00:00.000' and ChargeBoxId = @ChargeBoxId and StartTime　in (select  max(StartTime) from[TransactionRecord] where StopTime = '1991-01-01 00:00:00.000' and ChargeBoxId = @ChargeBoxId group by ConnectorId )";
                    }
                    else
                    {
                        parameters.Add(string.IsNullOrEmpty(sessionId) ? "@StartIdTag" : "@Id", string.IsNullOrEmpty(sessionId) ? idTag : sessionId, DbType.String, ParameterDirection.Input);

                        strSql = "Select Id, ConnectorId, StartTime,Fee, StartIdTag from TransactionRecord" +
                          "where StopTime = '1991-01-01 00:00:00.000' and ChargeBoxId = @ChargeBoxId and StartTime　in (select  max(StartTime) from [TransactionRecord] where  " + (string.IsNullOrEmpty(sessionId) ? "StartIdTag=@StartIdTag" : "Id=@TransactionId") + " and StopTime = '1991-01-01 00:00:00.000' and ChargeBoxId = @ChargeBoxId group by ConnectorId )";

                    }

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


                }

            }
            catch (Exception ex)
            {
                ;
            }


            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);
                    parameters.Add("@TransactionId", transactionDatas[j].Id, DbType.String, ParameterDirection.Input);
                    parameters.Add("@MeasurandId", requiredMeasurands[i], DbType.String, ParameterDirection.Input);


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

                        while (retry < 2)
                        {
                            retry++;
                            using (SqlConnection conn = new SqlConnection(meterConnectionString))
                            {
                                string strSql = "Select Top(1) * from [dbo].[ConnectorMeterValueRecord" + date + "] where ChargeBoxId=@ChargeBoxId and TransactionId=@TransactionId and MeasurandId=@MeasurandId order by CreatedOn desc;";
                                meterModel = conn.Query<ConnectorMeterValueModel>(strSql, parameters, null, true, 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, string 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 = new SqlConnection(mainConnectionString))
                {
                    string strSql = string.Empty;
                    if (!string.IsNullOrEmpty(sessionId) && string.IsNullOrEmpty(idTag))
                    {
                        parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input);
                        parameters.Add("@TransactionId", sessionId, DbType.String, 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) && string.IsNullOrEmpty(sessionId))
                    {
                        if (!string.IsNullOrEmpty(chargeBoxId))
                        {
                            parameters.Add("@ChargeBoxId", chargeBoxId, DbType.String, ParameterDirection.Input);
                        }

                        parameters.Add("@StartIdTag", idTag, DbType.String, ParameterDirection.Input);
                        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);

                        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),
                        SessionId = item.Id.ToString(),
                        StopReason = item.StopReason,
                        Currency = item.Fee.Length > 3 ? item.Fee.Substring(item.Fee.Length - 3, 3) : "",
                        TotalCost = item.Cost
                    });

                }

            }
            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 7://Reserved
                    {
                        result = Status.Reserved;
                    }
                    break;
                case 8://Unavailable
                    {
                        result = Status.Unavailable;
                    }
                    break;
                case 9://Faulted
                    {
                        result = Status.Faulted;
                    }
                    break;
                default:
                    break;



            }
            return result;

        }



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

            return result;
        }

    }
}