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

using Dapper;
using EVCB_OCPP.Packet.Messages.SubTypes;
using EVCB_OCPP.WEBAPI.Models;
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
{
    public interface IChargePointService
    {
        int GetNumberofConnectors(string chargeBoxId);

    }


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

        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; ";
                    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; ";
                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; ";
                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; ";
                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; ";
                existed = conn.ExecuteScalar<bool>(strSql, parameters, null, EVCBConfiguration.DB_DefaultConnectionTimeout);
            }

            return existed;
        }

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

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

                try
                {
                    using (SqlConnection conn = new SqlConnection(mainConnectionString))
                    {
                        string date = DateTime.UtcNow.ToString("yyMMdd");
                        string strSql = "Select Top(1) * from [dbo].[ConnectorMeterValueRecord" + date + "] where ChargeBoxId=@ChargeBoxId and Id=@TransactionId and MeasurandId=@MeasurandId order by CreatedOn desc;";
                        meterModel = conn.Query<ConnectorMeterValueModel>(strSql, parameters, null, true, EVCBConfiguration.DB_DefaultConnectionTimeout).FirstOrDefault();

                    }

                    if (meterModel != null)
                    {
                        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
                {
                    break;
                }

            }

            return meterValues;
        }


        public SessionDetail GetSessionDetail(string chargeBoxId, string sessionId)
        {
            SessionDetail detail = null;
            TransactionRecordModel transactionModel = null;
            if (string.IsNullOrEmpty(chargeBoxId)) return detail;

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

            try
            {
                using (SqlConnection conn = new SqlConnection(mainConnectionString))
                {
                    string date = DateTime.UtcNow.ToString("yyMMdd");
                    string strSql = "Select Top(1) * from [dbo].[TransactionRecord] where ChargeBoxId=@ChargeBoxId and Id=@TransactionId and StopTime!='1991-01-01 00:00:00.000' ;";
                    transactionModel = conn.Query<TransactionRecordModel>(strSql, parameters, null, true, EVCBConfiguration.DB_DefaultConnectionTimeout).FirstOrDefault();

                }

                if (transactionModel != null)
                {
                    detail = new SessionDetail()
                    {
                        ConnectorId = (int)transactionModel.ConnectorId,
                        IdTag = transactionModel.StartIdTag,
                        MeterStart = transactionModel.MeterStart,
                        MeterStop = transactionModel.MeterStop,
                        StartTime = transactionModel.StartTime.ToString(EVCBConfiguration.UTC_DATETIMEFORMAT),
                        StopTime = transactionModel.StopTime.ToString(EVCBConfiguration.UTC_DATETIMEFORMAT),
                        StopReason = transactionModel.StopReasonId < 1 ? Reason.Local : (Reason)transactionModel.StopReasonId
                    };

                }

            }
            catch
            {

            }


            return detail;
        }

    }
}