EVCB_OCPP.OCPPServer.16J/EVCB_OCPP.WSServer/Message/CoreProfileHandler.cs

using Dapper;
using EVCB_OCPP.Domain;
using EVCB_OCPP.Domain.Models.Database;
using EVCB_OCPP.Packet.Features;
using EVCB_OCPP.Packet.Messages;
using EVCB_OCPP.Packet.Messages.Core;
using EVCB_OCPP.Packet.Messages.SubTypes;
using EVCB_OCPP.WSServer.Dto;
using EVCB_OCPP.WSServer.Helper;
using EVCB_OCPP.WSServer.Service;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using OCPPServer.Protocol;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using SuperSocket.SocketBase;
using Microsoft.AspNetCore.Http;

namespace EVCB_OCPP.WSServer.Message;

public class ID_CreditDeductResult
{
    public int txId { set; get; }

    public string creditNo { set; get; }


    public bool deductResult { set; get; }

    public bool isDonateInvoice { set; get; }


    public decimal amount { set; get; }

    public string approvalNo { set; get; }

}

public class ID_ReaderStatus
{
    public int ConnectorId { set; get; }

    public string creditNo { set; get; }


    public string SerialNo { set; get; }

    public int readerStatus { set; get; }

    public string VEMData { set; get; }

    public DateTime Timestamp { set; get; }




}

internal partial class ProfileHandler
{
    private readonly ILogger logger;
    private readonly BlockingTreePrintService blockingTreePrintService;
    private readonly GoogleGetTimePrintService googleGetTimePrintService;

    //private readonly string webConnectionString;// = ConfigurationManager.ConnectionStrings[].ConnectionString;
    private readonly IDbContextFactory<MainDBContext> maindbContextFactory;
    private readonly SqlConnectionFactory<WebDBConetext> webDbConnectionFactory;
    private readonly MeterValueDbService meterValueDbService;

    //private readonly IDbContextFactory<MeterValueDBContext> metervaluedbContextFactory;
    private readonly IBusinessServiceFactory businessServiceFactory;
    private readonly IMainDbService mainDbService;
    private OuterHttpClient httpClient;

    public ProfileHandler(
        IConfiguration configuration,
        IDbContextFactory<MainDBContext> maindbContextFactory,
        SqlConnectionFactory<WebDBConetext> webDbConnectionFactory,
        //IDbContextFactory<MeterValueDBContext> metervaluedbContextFactory,
        MeterValueDbService meterValueDbService,
        IBusinessServiceFactory businessServiceFactory,
        IMainDbService mainDbService,
        ILogger<ProfileHandler> logger,
        BlockingTreePrintService blockingTreePrintService,
        GoogleGetTimePrintService googleGetTimePrintService,
        OuterHttpClient httpClient)
    {
        //webConnectionString = configuration.GetConnectionString("WebDBContext");

        this.logger = logger;
        this.blockingTreePrintService = blockingTreePrintService;
        this.googleGetTimePrintService = googleGetTimePrintService;
        this.maindbContextFactory = maindbContextFactory;
        this.webDbConnectionFactory = webDbConnectionFactory;
        this.meterValueDbService = meterValueDbService;
        this.mainDbService = mainDbService;
        //this.metervaluedbContextFactory = metervaluedbContextFactory;
        this.businessServiceFactory = businessServiceFactory;
        this.httpClient = httpClient;
    }

    async internal Task<MessageResult> ExecuteCoreRequest(Actions action, ClientData session, IRequest request)
    {
        Stopwatch watch = new Stopwatch();
        //if (action == Actions.Heartbeat || action == Actions.StopTransaction)
        //{
        //    watch.Start();
        //}
        watch.Start();
        MessageResult result = new MessageResult() { Success = false };

        try
        {
            switch (action)
            {
                case Actions.DataTransfer:
                    {
                        DataTransferRequest _request = request as DataTransferRequest;
                        var confirm = new DataTransferConfirmation() { status = DataTransferStatus.UnknownMessageId };

                        if (_request.messageId == "ID_CreditDeductResult")
                        {

                            var creditDeductResult = JsonConvert.DeserializeObject<ID_CreditDeductResult>(_request.data);
                            if (session.CustomerId == new Guid("009E603C-79CD-4620-A2B8-D9349C0E8AD8"))
                            {
                                var report = new
                                {
                                    ChargeBoxId = session.ChargeBoxId,
                                    IsDonateInvoice = creditDeductResult.isDonateInvoice,
                                    CreditNo = creditDeductResult.creditNo,
                                    DeductResult = creditDeductResult.deductResult,
                                    SessionId = creditDeductResult.txId,
                                    ApprovalNo = creditDeductResult.approvalNo,
                                    TotalCost = creditDeductResult.amount,

                                };

                                var response = await httpClient.Post(GlobalConfig.TCC_API_URL + "prepare_issue_invoice", new Dictionary<string, string>()
                                    {
                                        { "PartnerId",session.CustomerId.ToString()}

                                    }, report, GlobalConfig.TCC_SALTKEY);

                                logger.LogDebug(JsonConvert.SerializeObject(response));
                            }


                            confirm.status = DataTransferStatus.Accepted;
                            confirm.data = JsonConvert.SerializeObject(new { txId = creditDeductResult.txId, creditNo = creditDeductResult.creditNo, msgId = _request.messageId });
                        }
                        if (_request.messageId == "ID_ReaderStatus")
                        {
                            if (session.CustomerId == new Guid("009E603C-79CD-4620-A2B8-D9349C0E8AD8"))
                            {
                                var preauth_status = JsonConvert.DeserializeObject<ID_ReaderStatus>(_request.data);
                                var report = new
                                {
                                    ChargeBoxId = session.ChargeBoxId,
                                    ConnectorId = preauth_status.ConnectorId,
                                    CreditNo = preauth_status.creditNo,
                                    ReaderStatus = preauth_status.readerStatus,
                                    SerialNo = preauth_status.SerialNo,
                                    VEMData = preauth_status.VEMData,
                                    Timestamp = preauth_status.Timestamp

                                };

                                var response = await httpClient.Post(GlobalConfig.TCC_API_URL + "preauth_status", new Dictionary<string, string>()
                                    {
                                        { "PartnerId",session.CustomerId.ToString()}

                                    }, report, GlobalConfig.TCC_SALTKEY);


                                confirm.status = DataTransferStatus.Accepted;
                            }
                        }
                        if (_request.messageId == "ID_OCMF")
                        {
                            JObject jo = JObject.Parse(_request.data);

                            logger.LogDebug("{0}\r\n{1}\r\n{2}", jo["txId"].Value<int>(), jo["dataString"].Value<string>(), jo["publicKey"].Value<string>());

                            await mainDbService.AddOCMF(new OCMF()
                            {
                                TransactionId = jo["txId"].Value<int>(),
                                DataString = jo["dataString"].Value<string>(),
                                PublicKey = jo["publicKey"].Value<string>()
                            });

                            confirm.status = DataTransferStatus.Accepted;
                            confirm.data = JsonConvert.SerializeObject(new { txId = jo["txId"].Value<int>(), msgId = _request.messageId });
                        }

                        if (_request.messageId == "Authorize")
                        {
                            string iso15118_token = string.Empty;
                            JObject jo = JObject.Parse(_request.data);
                            if (jo.ContainsKey("idToken"))
                            {
                                iso15118_token = jo["idToken"]["idToken"].Value<string>();

                            }

                            confirm.status = DataTransferStatus.Accepted;
                            confirm.data = JsonConvert.SerializeObject(
                                new { 
                                    certificateStatus = iso15118_token == "12345678901234" ? "Accepted" : "CertificateExpired",
                                    idTokenInfo = new { status = iso15118_token == "12345678901234" ? "Accepted" : "Invalid" 
                                    } });
                        }
                        result.Message = confirm;
                        result.Success = true;
                    }
                    break;
                case Actions.BootNotification:
                    {
                        BootNotificationRequest _request = request as BootNotificationRequest;
                        int heartbeat_interval = GlobalConfig.GetHEARTBEAT_INTERVAL();
                        //var _machine = db.Machine.FirstOrDefault(x => x.ChargeBoxId == session.ChargeBoxId);
                        Machine _machine = new();
                        _machine.ChargeBoxSerialNumber = string.IsNullOrEmpty(_request.chargeBoxSerialNumber) ? string.Empty : _request.chargeBoxSerialNumber;
                        _machine.ChargePointSerialNumber = string.IsNullOrEmpty(_request.chargePointSerialNumber) ? string.Empty : _request.chargePointSerialNumber;
                        _machine.ChargePointModel = string.IsNullOrEmpty(_request.chargePointModel) ? string.Empty : _request.chargePointModel;
                        _machine.ChargePointVendor = string.IsNullOrEmpty(_request.chargePointVendor) ? string.Empty : _request.chargePointVendor;
                        _machine.FW_CurrentVersion = string.IsNullOrEmpty(_request.firmwareVersion) ? string.Empty : _request.firmwareVersion;
                        //_machine.Iccid = string.IsNullOrEmpty(_request.iccid) ? string.Empty : _request.iccid;
                        _machine.Iccid = DateTime.UtcNow.ToString("yy-MM-dd HH:mm");
                        _machine.Imsi = string.IsNullOrEmpty(_request.imsi) ? string.Empty : _request.imsi;
                        _machine.MeterSerialNumber = string.IsNullOrEmpty(_request.meterSerialNumber) ? string.Empty : _request.meterSerialNumber;
                        _machine.MeterType = string.IsNullOrEmpty(_request.meterType) ? string.Empty : _request.meterType;

                        await mainDbService.UpdateMachineBasicInfo(session.ChargeBoxId, _machine);

                        var configValue = await mainDbService.GetMachineHeartbeatInterval(session.ChargeBoxId);

                        if (configValue != null)
                        {
                            int.TryParse(configValue, out heartbeat_interval);
                        }

                        if (session.IsPending == true)
                        {
                            session.IsPending = false;
                        }
                        if (session.IsPending == null)
                        {
                            session.IsPending = true;
                        }

                        var confirm = new BootNotificationConfirmation() { 
                            currentTime = DateTime.UtcNow,
                            interval = session.IsPending.Value ? 5 : heartbeat_interval, 
                            status = session.IsPending.Value ? RegistrationStatus.Pending : RegistrationStatus.Accepted 
                        };

                        result.Message = confirm;
                        result.Success = true;
                    }
                    break;
                case Actions.StatusNotification:
                    {
                        var statusNotificationTimer = Stopwatch.StartNew();
                        long s1 = 0, s2 = 0, s3 = 0, s4 = 0, s5 = 0;
                        //只保留最新上報狀況
                        StatusNotificationRequest _request = request as StatusNotificationRequest;
                        int preStatus = 0;
                        ConnectorStatus _oldStatus;

                        _oldStatus = await mainDbService.GetConnectorStatus(session.ChargeBoxId, _request.connectorId);

                        s1 = statusNotificationTimer.ElapsedMilliseconds;

                        if (_oldStatus != null && (_request.status != (ChargePointStatus)_oldStatus.Status || _request.status == ChargePointStatus.Faulted))
                        {
                            preStatus = _oldStatus.Status;

                            await mainDbService.UpdateConnectorStatus(_oldStatus.Id, new ConnectorStatus()
                            {
                                CreatedOn = _request.timestamp.HasValue ? _request.timestamp.Value : DateTime.UtcNow,
                                Status = (int)_request.status,
                                ChargePointErrorCodeId = (int)_request.errorCode,
                                ErrorInfo = string.IsNullOrEmpty(_request.info) ? string.Empty : _request.info,
                                VendorId = string.IsNullOrEmpty(_request.vendorId) ? string.Empty : _request.vendorId,
                                VendorErrorCode = string.IsNullOrEmpty(_request.vendorErrorCode) ? string.Empty : _request.vendorErrorCode
                            });
                        }

                        s2 = statusNotificationTimer.ElapsedMilliseconds;

                        if (_oldStatus == null)
                        {
                            await mainDbService.AddConnectorStatus(
                                ChargeBoxId: session.ChargeBoxId,
                                ConnectorId: (byte)_request.connectorId,
                                CreatedOn: _request.timestamp.HasValue ? _request.timestamp.Value : DateTime.UtcNow,
                                Status: (int)_request.status,
                                ChargePointErrorCodeId: (int)_request.errorCode,
                                ErrorInfo: string.IsNullOrEmpty(_request.info) ? string.Empty : _request.info,
                                VendorId: string.IsNullOrEmpty(_request.vendorId) ? string.Empty : _request.vendorId,
                                VendorErrorCode: string.IsNullOrEmpty(_request.vendorErrorCode) ? string.Empty : _request.vendorErrorCode);
                        }
                        s3 = statusNotificationTimer.ElapsedMilliseconds;

                        if (_request.status == Packet.Messages.SubTypes.ChargePointStatus.Faulted)
                        {
                            await mainDbService.AddMachineError(ConnectorId: (byte)_request.connectorId,
                                    CreatedOn: _request.timestamp.HasValue ? _request.timestamp.Value : DateTime.UtcNow,
                                    Status: (int)_request.status,
                                    ChargeBoxId: session.ChargeBoxId,
                                    ErrorCodeId: (int)_request.errorCode,
                                    ErrorInfo: string.IsNullOrEmpty(_request.info) ? string.Empty : _request.info,
                                    PreStatus: _oldStatus == null ? -1 : preStatus,
                                    VendorErrorCode: string.IsNullOrEmpty(_request.vendorErrorCode) ? string.Empty : _request.vendorErrorCode,
                                    VendorId: string.IsNullOrEmpty(_request.vendorId) ? string.Empty : _request.vendorId);
                        }

                        s4 = statusNotificationTimer.ElapsedMilliseconds;
                        if (_request.status == Packet.Messages.SubTypes.ChargePointStatus.Faulted)
                        {
                            //var businessService = BusinessServiceFactory.CreateBusinessService(session.CustomerId.ToString());
                            //var businessService = await serviceProvider.GetService<BusinessServiceFactory>().CreateBusinessService(session.CustomerId.ToString());
                            var businessService = await businessServiceFactory.CreateBusinessService(session.CustomerId.ToString());
                            var notification = businessService.NotifyFaultStatus(new ErrorDetails()
                            {
                                ChargeBoxId = session.ChargeBoxId,
                                ConnectorId = _request.connectorId,
                                ErrorCode = _request.errorCode,
                                Info = string.IsNullOrEmpty(_request.info) ? string.Empty : _request.info,
                                OCcuredOn = _request.timestamp ?? DateTime.UtcNow,
                                VendorErrorCode = string.IsNullOrEmpty(_request.vendorErrorCode) ? string.Empty : _request.vendorErrorCode,

                            });
                        }
                        s5 = statusNotificationTimer.ElapsedMilliseconds;

                        var confirm = new StatusNotificationConfirmation() { };
                        result.Message = confirm;
                        result.Success = true;

                        statusNotificationTimer.Stop();
                        if (statusNotificationTimer.ElapsedMilliseconds / 1000 > 1)
                        {
                            logger.LogCritical(string.Format("StatusNotification took {0}/{1}/{2}/{3}/{4}", s1, s2, s3, s4, s5));
                        }
                    }
                    break;
                case Actions.Heartbeat:
                    {

                        var confirm = new HeartbeatConfirmation() { currentTime = DateTime.UtcNow };
                        result.Message = confirm;
                        result.Success = true;
                    }
                    break;
                case Actions.MeterValues:
                    {
                        var meterValueTimer = Stopwatch.StartNew();
                        long s1 = 0, s2 = 0, s3 = 0, s4 = 0, s5 = 0, insertTasksCnt = 0;
                        MeterValuesRequest _request = request as MeterValuesRequest;

                        if (_request.meterValue.Count > 0)
                        {
                            s1 = meterValueTimer.ElapsedMilliseconds;
                            foreach (var item in _request.meterValue)
                            {
                                if (_request.transactionId.HasValue)
                                {
                                    decimal meterStart = 0;
                                    var energy_Register = item.sampledValue.Where(x => x.measurand == Measurand.Energy_Active_Import_Register).FirstOrDefault();

                                    if (energy_Register != null)
                                    {
                                        decimal energyRegister = decimal.Parse(energy_Register.value);
                                        energyRegister = energy_Register.unit.Value == UnitOfMeasure.kWh ? decimal.Multiply(energyRegister, 1000) : energyRegister;


                                        using (var maindb = await maindbContextFactory.CreateDbContextAsync())
                                        {
                                            meterStart = await maindb.TransactionRecord
                                                .Where(x => x.Id == _request.transactionId.Value).Select(x => x.MeterStart)
                                                .FirstOrDefaultAsync();
                                        }

                                        item.sampledValue.Add(new SampledValue()
                                        {
                                            context = ReadingContext.Sample_Periodic,
                                            format = ValueFormat.Raw,
                                            location = Location.Outlet,
                                            phase = item.sampledValue.Where(x => x.measurand == Measurand.Energy_Active_Import_Register).Select(x => x.phase).FirstOrDefault(),
                                            unit = UnitOfMeasure.Wh,
                                            measurand = Measurand.TotalEnergy,
                                            value = decimal.Subtract(energyRegister, meterStart).ToString()
                                        });
                                    }

                                }

                            }

                            s2 = meterValueTimer.ElapsedMilliseconds;
                            //List<Task> insertTasks = new();
                            List <InsertMeterValueParam> datas= new();
                            foreach (var item in _request.meterValue)
                            {
                                foreach (var sampleVaule in item.sampledValue)
                                {
                                    decimal value = Convert.ToDecimal(sampleVaule.value);
                                    datas.Add(new InsertMeterValueParam(
                                        chargeBoxId: session.ChargeBoxId
                                        , connectorId: (byte)_request.connectorId
                                        , value: value
                                        , createdOn: item.timestamp
                                        , contextId: sampleVaule.context.HasValue ? (int)sampleVaule.context : 0
                                        , formatId: sampleVaule.format.HasValue ? (int)sampleVaule.format : 0
                                        , measurandId: sampleVaule.measurand.HasValue ? (int)sampleVaule.measurand : 0
                                        , phaseId: sampleVaule.phase.HasValue ? (int)sampleVaule.phase : 0
                                        , locationId: sampleVaule.location.HasValue ? (int)sampleVaule.location : 0
                                        , unitId: sampleVaule.unit.HasValue ? (int)sampleVaule.unit : 0
                                        , transactionId: _request.transactionId.HasValue ? _request.transactionId.Value : -1));
                                    //var task = meterValueDbService.InsertAsync(
                                    //    chargeBoxId: session.ChargeBoxId
                                    //    , connectorId: (byte)_request.connectorId
                                    //    , value: value
                                    //    , createdOn: item.timestamp
                                    //    , contextId: sampleVaule.context.HasValue ? (int)sampleVaule.context : 0
                                    //    , formatId: sampleVaule.format.HasValue ? (int)sampleVaule.format : 0
                                    //    , measurandId: sampleVaule.measurand.HasValue ? (int)sampleVaule.measurand : 0
                                    //    , phaseId: sampleVaule.phase.HasValue ? (int)sampleVaule.phase : 0
                                    //    , locationId: sampleVaule.location.HasValue ? (int)sampleVaule.location : 0
                                    //    , unitId: sampleVaule.unit.HasValue ? (int)sampleVaule.unit : 0
                                    //    , transactionId: _request.transactionId.HasValue ? _request.transactionId.Value : -1);

                                    //var task = Task.Delay(2_000); 
                                    //insertTasks.Add(task);
                                }
                            }
                            //insertTasksCnt = insertTasks.Count;
                            insertTasksCnt = datas.Count;
                            s3 = meterValueTimer.ElapsedMilliseconds;
                            //await Task.WhenAll(insertTasks);
                            await meterValueDbService.InsertBundleAsync(datas);
                            s4 = meterValueTimer.ElapsedMilliseconds;
                        }

                        //  if (energy_kwh > 0)
                        {
                            try
                            {
                                if (session.IsBilling)
                                {
                                    await mainDbService.AddServerMessage(
                                            ChargeBoxId: session.ChargeBoxId,
                                            OutAction: Actions.DataTransfer.ToString(),
                                            OutRequest:
                                                new DataTransferRequest()
                                                {
                                                    messageId = "ID_TxEnergy",
                                                    vendorId = "Phihong Technology",
                                                    data = JsonConvert.SerializeObject(new { txId = _request.transactionId, ConnectorId = _request.connectorId })
                                                }
                                            );
                                }
                            }
                            catch (Exception ex)
                            {

                                logger.LogTrace(string.Format("{0} :{1}", session.ChargeBoxId + " RunningCost", ex.Message));

                            }

                        }

                        s5 = meterValueTimer.ElapsedMilliseconds;
                        meterValueTimer.Stop();
                        if (meterValueTimer.ElapsedMilliseconds / 1000 > 1)
                        {

                            logger.LogCritical(string.Format("MeterValues took {0}/{1}/{2}/{3}/{4}:{5}", s1 / 1000, s2 / 1000, s3 / 1000, s4 / 1000, s5 / 1000, insertTasksCnt));
                        }

                        var confirm = new MeterValuesConfirmation() { };
                        result.Message = confirm;
                        result.Success = true;
                    }
                    break;
                case Actions.StartTransaction:
                    {
                        var timer = Stopwatch.StartNew();
                        long t0 = 0, t1 = 0, t2 = 0, t3 = 0, t4 = 0, t5 = 0;

                        StartTransactionRequest _request = request as StartTransactionRequest;

                        int _transactionId = -1;

                        var businessService = await businessServiceFactory.CreateBusinessService(session.CustomerId.ToString());
                        t0 = timer.ElapsedMilliseconds;

                        var _idTagInfo = new IdTagInfo() { expiryDate = DateTime.UtcNow.AddDays(1), status = AuthorizationStatus.Accepted };

                        #region PnC 邏輯

                        if (!string.IsNullOrEmpty(_request.idTag))
                        {
                            _request.idTag = _request.idTag.StartsWith("vid:") ? _request.idTag.Replace("vid:", "") : _request.idTag;
                        }

                        #endregion

                        if (_request.idTag != "Backend")
                        {
                            var authorization_result = await businessService.Authorize(session.ChargeBoxId, _request.idTag);
                            _idTagInfo = authorization_result.IdTagInfo;
                            t1 = timer.ElapsedMilliseconds;

                            if (_idTagInfo.status == AuthorizationStatus.Accepted && authorization_result.ChargePointFee != null)
                            {
                                var price = authorization_result.ChargePointFee.Where(x => x.IsAC == session.IsAC).First();
                                if (price != null)
                                {

                                    if (session.UserPrices.ContainsKey(_request.idTag))
                                    {
                                        session.UserPrices[_request.idTag] = price.PerkWhFee.HasValue ? JsonConvert.SerializeObject(new List<ChargingPrice>() { new ChargingPrice() { StartTime = "00:00", EndTime = "23:59", Fee = price.PerkWhFee.Value } }) : price.PerHourFee.Value.ToString();
                                        session.UserPrices[_request.idTag] += "|+" + authorization_result.AccountBalance + "+" + "&" + price.ParkingFee + "&|" + price.Currency;

                                    }
                                    else
                                    {
                                        session.UserPrices.Add(_request.idTag, price.PerkWhFee.HasValue ? JsonConvert.SerializeObject(new List<ChargingPrice>() { new ChargingPrice() { StartTime = "00:00", EndTime = "23:59", Fee = price.PerkWhFee.Value } }) : price.PerHourFee.Value.ToString());
                                        session.UserPrices[_request.idTag] += "|+" + authorization_result.AccountBalance + "+" + "&" + price.ParkingFee + "&|" + price.Currency;

                                    }
                                }
                            }

                        }

                        //特例****飛宏客戶旗下的電樁，若遇到Portal沒回應的狀況 ~允許充電
                        if (session.CustomerId.ToString().ToUpper() == "8456AED9-6DD9-4BF3-A94C-9F5DCB9506F7" && _idTagInfo.status == AuthorizationStatus.ConcurrentTx)
                        {
                            _idTagInfo = new IdTagInfo() { expiryDate = DateTime.UtcNow.AddDays(1), status = AuthorizationStatus.Accepted };
                        }

                        string accountBalance = "0";
                        if (session.CustomerId.ToString().ToUpper() == "10C7F5BD-C89A-4E2A-8611-B617E0B41A73")
                        {
                            using (SqlConnection conn = await webDbConnectionFactory.CreateAsync())
                            {
                                var parameters = new DynamicParameters();
                                parameters.Add("@IdTag", _request.idTag, DbType.String, ParameterDirection.Input, 50);
                                string strSql = "select parentIdTag from [dbo].[LocalListDetail]  where ListId = 27 and IdTag=@IdTag; ";
                                accountBalance = await conn.ExecuteScalarAsync<string>(strSql, parameters);
                            }
                        }

                        var _CustomerId = await mainDbService.GetCustomerIdByChargeBoxId(session.ChargeBoxId);
                        t2 = timer.ElapsedMilliseconds;

                        var _existedTx = await mainDbService.TryGetDuplicatedTransactionId(session.ChargeBoxId, _CustomerId, _request.connectorId, _request.timestamp);
                        t3 = timer.ElapsedMilliseconds;

                        if (_existedTx != null)
                        {
                            _transactionId = _existedTx.Value;
                            logger.LogError("Duplication ***************************************************** " + _existedTx);
                        }
                        else
                        {
                            TransactionRecord _newTransaction;//= new TransactionRecord();
                            _newTransaction = new TransactionRecord()
                            {
                                ChargeBoxId = session.ChargeBoxId,
                                ConnectorId = (byte)_request.connectorId,
                                CreatedOn = DateTime.UtcNow,
                                StartIdTag = _request.idTag,
                                MeterStart = _request.meterStart,
                                CustomerId = _CustomerId,
                                StartTime = _request.timestamp.ToUniversalTime(),
                                ReservationId = _request.reservationId.HasValue ? _request.reservationId.Value : 0,
                            };

                            if (session.UserPrices.ContainsKey(_request.idTag))
                            {
                                _newTransaction.Fee = !session.IsBilling ? string.Empty : session.UserPrices[_request.idTag];

                            }
                            else
                            {
                                _newTransaction.Fee = !session.IsBilling ? string.Empty : session.BillingMethod == 1 ? JsonConvert.SerializeObject(session.ChargingPrices) : session.ChargingFeebyHour.ToString();
                                _newTransaction.Fee += !session.IsBilling ? string.Empty : "|+" + accountBalance + "+" + "&" + session.ParkingFee + "&|" + session.Currency;
                            }

                            //using (var db = await maindbContextFactory.CreateDbContextAsync())
                            //{
                            //    await db.TransactionRecord.AddAsync(_newTransaction);

                            //    await db.SaveChangesAsync();

                            //    _transactionId = _newTransaction.Id;
                            //}

                            _transactionId = await mainDbService.AddNewTransactionRecord(_newTransaction);
                            t4 = timer.ElapsedMilliseconds;

                            logger.LogInformation("***************************************************** ");
                            logger.LogInformation(string.Format("{0} :TransactionId {1} ", session.ChargeBoxId, _transactionId));
                            logger.LogInformation("***************************************************** ");
                        }


                        var confirm = new StartTransactionConfirmation()
                        {
                            idTagInfo = _idTagInfo,
                            transactionId = _transactionId
                        };


                        result.Message = confirm;
                        result.Success = true;

                        timer.Stop();
                        t5 = timer.ElapsedMilliseconds;
                        if (t5 > 1000)
                        {
                            logger.Log(LogLevel.Critical, "{action} {ChargeBoxId} time {t0}/{t1}/{t2}/{t3}/{t4}/{totalTime}", action.ToString(), session.ChargeBoxId, t0, t1, t2, t3, t4, t5);
                        }
                    }
                    break;
                case Actions.StopTransaction:
                    {
                        StopTransactionRequest _request = request as StopTransactionRequest;

                        //遠傳太久以前的停止充電 直接拒絕 避免電樁持續重送~~~~~~~
                        if (_request.timestamp < new DateTime(2021, 11, 1))
                        {
                            var confirm = new StopTransactionConfirmation()
                            {
                                idTagInfo = new IdTagInfo()
                                {
                                    status = AuthorizationStatus.Invalid
                                }
                            };

                            result.Message = confirm;
                            result.Success = true;
                            return result;
                        }

                        long getDateTimeTime, getServiceTime, getTagInfoTime, dbOpTime = 0, meterValueTime = 0;
                        var stopTrasactionTimer = Stopwatch.StartNew();

                        int _ConnectorId = 0;
                        var utcNow = DateTime.UtcNow;

                        getDateTimeTime = stopTrasactionTimer.ElapsedMilliseconds;
                        var businessService = await businessServiceFactory.CreateBusinessService(session.CustomerId.ToString());
                        getServiceTime = stopTrasactionTimer.ElapsedMilliseconds;

                        TransactionRecord transaction;
                        transaction = await mainDbService.GetTransactionForStopTransaction(_request.transactionId, session.ChargeBoxId);

                        var _idTagInfo = string.IsNullOrEmpty(_request.idTag) ? null : (
                            _request.idTag == "Backend" ?
                                new IdTagInfo()
                                {
                                    expiryDate = utcNow.AddDays(1),
                                    status = AuthorizationStatus.Accepted
                                } :
                                (await businessService.Authorize(session.ChargeBoxId, _request.idTag, transaction?.ConnectorId)).IdTagInfo
                            );
                        getTagInfoTime = stopTrasactionTimer.ElapsedMilliseconds;

                        //特例****飛宏客戶旗下的電樁，若遇到Portal沒回應的狀況 ~允許充電
                        if (session.CustomerId.ToString().ToUpper() == "8456AED9-6DD9-4BF3-A94C-9F5DCB9506F7" && _idTagInfo != null && _idTagInfo.status == AuthorizationStatus.ConcurrentTx)
                        {
                            _idTagInfo = new IdTagInfo() { expiryDate = utcNow.AddDays(1), status = AuthorizationStatus.Accepted };
                        }

                        #region PnC 邏輯
                        if (!string.IsNullOrEmpty(_request.idTag))
                        {
                            _request.idTag = _request.idTag.StartsWith("vid:") ? _request.idTag.Replace("vid:", "") : _request.idTag;
                        }
                        #endregion

                        try
                        {
                            if (transaction is null)
                            {
                                result.Exception = new Exception("Can't find transactionId " + _request.transactionId);
                            }
                            else
                            {
                                #region 加入Transaction Start/StopSOC
                                if (!session.IsAC && _request.transactionId > 0)
                                {
                                    var SearchTime = transaction.StartTime;
                                    var txStopTime = _request.timestamp;
                                    List<int> SOCCollection = new List<int>();

                                    while (SearchTime.Date <= txStopTime.Date)
                                    {

                                        var searchResults = await meterValueDbService.GetTransactionSOC(transaction.Id, SearchTime.Date);
                                        SOCCollection.AddRange(searchResults);
                                        SearchTime = SearchTime.AddDays(1);
                                    }

                                    SOCCollection.Sort();
                                    logger.LogDebug(string.Format("SOCCollection:" + String.Join(",", SOCCollection.Select(x => x.ToString()).ToArray())));

                                    await mainDbService.UpdateTransactionSOC(
                                        transaction.Id,
                                        startsoc: SOCCollection.Count == 0 ? "" : SOCCollection.First().ToString("0"),
                                        stopsoc: SOCCollection.Count == 0 ? "" : SOCCollection.Last().ToString("0")
                                        );
                                }
                                #endregion

                                _ConnectorId = transaction.ConnectorId;

                                var confirm = new StopTransactionConfirmation()
                                {
                                    idTagInfo = _idTagInfo

                                };

                                //Avoid rewrite transaction data
                                if (transaction.StopTime != GlobalConfig.DefaultNullTime)
                                {
                                    result.Message = confirm;
                                    result.Success = true;
                                    return result;
                                }

                                await mainDbService.UpdateTransaction(_request.transactionId,
                                    meterStop: _request.meterStop,
                                    stopTime: _request.timestamp.ToUniversalTime(),
                                    stopReasonId: _request.reason.HasValue ? (int)_request.reason.Value : 0,
                                    stopReason: _request.reason.HasValue ? _request.reason.Value.ToString() : Reason.Local.ToString(),
                                    stopIdTag: _request.idTag,
                                    receipt: string.Empty,
                                    cost: session.IsBilling ? -1 : 0);

                                if (_request.transactionData == null || _request.transactionData.Count == 0)
                                {
                                    _request.transactionData = new List<MeterValue>()
                                            {
                                                new MeterValue() {  timestamp= _request.timestamp, sampledValue=new List<SampledValue>()}
                                            };
                                }

                                if (_request.transactionData != null && _request.transactionData.Count > 0)
                                {
                                    //清除 StopTransaction TransactionData 
                                    _request.transactionData[0].sampledValue.Clear();

                                    _request.transactionData[0].sampledValue.Add(new SampledValue()
                                    {
                                        context = ReadingContext.Transaction_End,
                                        format = ValueFormat.Raw,
                                        location = Location.Outlet,
                                        phase = _request.transactionData[0].sampledValue.Where(x => x.context.HasValue).Select(x => x.phase).FirstOrDefault(),
                                        unit = UnitOfMeasure.Wh,
                                        measurand = Measurand.TotalEnergy,
                                        value = decimal.Subtract(transaction.MeterStop, transaction.MeterStart).ToString()
                                    });
                                }

                                if (session.IsBilling)
                                {
                                    await mainDbService.AddServerMessage(
                                        ChargeBoxId: session.ChargeBoxId,
                                        OutAction: Actions.DataTransfer.ToString(),
                                        OutRequest:
                                            new DataTransferRequest()
                                            {
                                                messageId = "ID_TxEnergy",
                                                vendorId = "Phihong Technology",
                                                data = JsonConvert.SerializeObject(new { txId = _request.transactionId, ConnectorId = transaction.ConnectorId })
                                            }
                                        );
                                }

                                result.Message = confirm;
                                result.Success = true;

                            }
                            dbOpTime = watch.ElapsedMilliseconds;

                            #region Save MeterValue

                            if (_request.transactionData != null &&
                                _request.transactionData.Count > 0)
                            {
                                //List<Task> insertTasks = new();
                                List<InsertMeterValueParam> datas = new();
                                foreach (var item in _request.transactionData)
                                {
                                    foreach (var sampleVaule in item.sampledValue)
                                    {
                                        decimal value = Convert.ToDecimal(sampleVaule.value);
                                        datas.Add(new InsertMeterValueParam(
                                            chargeBoxId: session.ChargeBoxId
                                            , connectorId: (byte)_ConnectorId
                                            , value: value
                                            , createdOn: item.timestamp
                                            , contextId: sampleVaule.context.HasValue ? (int)sampleVaule.context : 0
                                            , formatId: sampleVaule.format.HasValue ? (int)sampleVaule.format : 0
                                            , measurandId: sampleVaule.measurand.HasValue ? (int)sampleVaule.measurand : 0
                                            , phaseId: sampleVaule.phase.HasValue ? (int)sampleVaule.phase : 0
                                            , locationId: sampleVaule.location.HasValue ? (int)sampleVaule.location : 0
                                            , unitId: sampleVaule.unit.HasValue ? (int)sampleVaule.unit : 0
                                            , transactionId: _request.transactionId));
                                        //var task = meterValueDbService.InsertAsync(
                                        //    chargeBoxId: session.ChargeBoxId
                                        //        , connectorId: (byte)_ConnectorId
                                        //        , value: value
                                        //        , createdOn: item.timestamp
                                        //        , contextId: sampleVaule.context.HasValue ? (int)sampleVaule.context : 0
                                        //        , formatId: sampleVaule.format.HasValue ? (int)sampleVaule.format : 0
                                        //        , measurandId: sampleVaule.measurand.HasValue ? (int)sampleVaule.measurand : 0
                                        //        , phaseId: sampleVaule.phase.HasValue ? (int)sampleVaule.phase : 0
                                        //        , locationId: sampleVaule.location.HasValue ? (int)sampleVaule.location : 0
                                        //        , unitId: sampleVaule.unit.HasValue ? (int)sampleVaule.unit : 0
                                        //        , transactionId: _request.transactionId);
                                        //insertTasks.Add(task);
                                    }
                                }
                                //await Task.WhenAll(insertTasks);
                                await meterValueDbService.InsertBundleAsync(datas);
                            }
                            #endregion

                            meterValueTime = watch.ElapsedMilliseconds;
                        }
                        catch (Exception ex)
                        {
                            result.Exception = new Exception("TransactionId " + _request.transactionId + " " + ex.Message);
                            result.CallErrorMsg = "Reject Response Message";
                            result.Success = false;

                            logger.LogCritical("StopTransaction {msg} trace:{trace}", ex.Message, ex.StackTrace);
                            // return result;
                        }

                        stopTrasactionTimer.Stop();

                        if (stopTrasactionTimer.ElapsedMilliseconds > 1000)
                        {
                            logger.Log(LogLevel.Critical, "ExecuteCoreRequest {action} {ChargeBoxId} took {time} sec", action.ToString(), session.ChargeBoxId, stopTrasactionTimer.ElapsedMilliseconds / 1000);
                            logger.Log(LogLevel.Critical, "{action} {ChargeBoxId} time {getDateTime}/{serviceTime}/{tagInfoTime}/{dbOpTime}/{meterValueTime}", action.ToString(), session.ChargeBoxId, getDateTimeTime, getServiceTime, getTagInfoTime, dbOpTime, meterValueTime);
                        }

                    }
                    break;
                case Actions.Authorize:
                    {
                        AuthorizeRequest _request = request as AuthorizeRequest;

                        var businessService = await businessServiceFactory.CreateBusinessService(session.CustomerId.ToString());
                        var confirm = new AuthorizeConfirmation()
                        {
                            idTagInfo = new IdTagInfo() { expiryDate = DateTime.UtcNow.AddDays(1), status = AuthorizationStatus.Accepted }
                        };
                        if (_request.idTag != "Backend")
                        {
                            var authorization_result = await businessService.Authorize(session.ChargeBoxId, _request.idTag);
                            confirm.idTagInfo = authorization_result.IdTagInfo;

                            if (confirm.idTagInfo.status == AuthorizationStatus.Accepted && authorization_result.ChargePointFee != null)
                            {
                                var price = authorization_result.ChargePointFee.Where(x => x.IsAC == session.IsAC).First();
                                if (price != null)
                                {

                                    if (session.UserPrices.ContainsKey(_request.idTag))
                                    {
                                        session.UserPrices[_request.idTag] = price.PerkWhFee.HasValue ? JsonConvert.SerializeObject(new List<ChargingPrice>() { new ChargingPrice() { StartTime = "00:00", EndTime = "23:59", Fee = price.PerkWhFee.Value } }) : price.PerHourFee.Value.ToString();
                                        session.UserPrices[_request.idTag] += "|+" + authorization_result.AccountBalance + "+" + "&" + price.ParkingFee + "&|" + price.Currency;

                                    }
                                    else
                                    {
                                        session.UserPrices.Add(_request.idTag, price.PerkWhFee.HasValue ? JsonConvert.SerializeObject(new List<ChargingPrice>() { new ChargingPrice() { StartTime = "00:00", EndTime = "23:59", Fee = price.PerkWhFee.Value } }) : price.PerHourFee.Value.ToString());
                                        session.UserPrices[_request.idTag] += "|+" + authorization_result.AccountBalance + "+" + "&" + price.ParkingFee + "&|" + price.Currency;

                                    }

                                    if (session.UserDisplayPrices.ContainsKey(_request.idTag))
                                    {
                                        session.UserDisplayPrices[_request.idTag] = price.DisplayMessage;
                                    }
                                    else
                                    {
                                        session.UserDisplayPrices.Add(_request.idTag, price.DisplayMessage);
                                    }
                                }
                            }

                        }
                        //特例****飛宏客戶旗下的電樁，若遇到Portal沒回應的狀況 ~允許充電
                        if (session.CustomerId.ToString().ToUpper() == "8456AED9-6DD9-4BF3-A94C-9F5DCB9506F7" && confirm.idTagInfo.status == AuthorizationStatus.ConcurrentTx)
                        {
                            confirm.idTagInfo = new IdTagInfo() { expiryDate = DateTime.UtcNow.AddDays(1), status = AuthorizationStatus.Accepted };
                        }

                        result.Message = confirm;
                        result.Success = true;
                    }
                    break;
                default:
                    {
                        logger.LogWarning(string.Format("Not Implement {0} Logic(ExecuteCoreRequest)", request.GetType().ToString().Replace("OCPPPackage.Messages.Core.", "")));
                    }
                    break;
            }
        }
        catch (Exception ex)
        {
            logger.LogCritical("chargeBoxId:{0} {1}", session?.ChargeBoxId, action);
            logger.LogCritical("Data {0}", request?.ToString());
            logger.LogCritical("Error {0}", ex.ToString());
            result.Exception = ex;
        }


        //if (action == Actions.Heartbeat)
        //{
        watch.Stop();
        if (watch.ElapsedMilliseconds / 1000 > 3)
        {
            logger.LogError("Processing " + action.ToString() + " costs " + watch.ElapsedMilliseconds / 1000 + " seconds"); ;
        }
        //}

        if (watch.ElapsedMilliseconds > 5_000)
        {
            //ThreadPool.GetAvailableThreads(out int workerThreads,out int completionThreads);
            //logger.LogInformation($"ThreadPool workerThreads:{workerThreads} completionThreads:{completionThreads}");

            //await blockingTreePrintService.PrintDbBlockingTree();
            //await googleGetTimePrintService.Print();
        }

        return result;
    }

    async internal Task<MessageResult> ExecuteCoreConfirm(Actions action, ClientData session, IConfirmation confirm, string requestId)
    {
        MessageResult result = new MessageResult() { Success = true };

        try
        {
            switch (action)
            {
                case Actions.DataTransfer:
                    {
                        DataTransferConfirmation _confirm = confirm as DataTransferConfirmation;
                        DataTransferRequest _request = _confirm.GetRequest() as DataTransferRequest;
                        using (var db = await maindbContextFactory.CreateDbContextAsync())
                        {
                            var operation = await db.MachineOperateRecord.Where(x => x.SerialNo == requestId &&
                            x.ChargeBoxId == session.ChargeBoxId && x.Status == 0).FirstOrDefaultAsync();
                            if (operation != null)
                            {
                                operation.FinishedOn = DateTime.UtcNow;
                                operation.Status = 1;//電樁有回覆
                                operation.EVSE_Status = (int)_confirm.status;
                                operation.EVSE_Value = string.IsNullOrEmpty(_confirm.data) ? "" : _confirm.data;
                                await db.SaveChangesAsync();
                            }

                            if (_request.messageId == "ID_FirmwareVersion")
                            {
                                var machine = new Machine() { Id = session.MachineId };
                                if (machine != null)
                                {
                                    db.ChangeTracker.AutoDetectChangesEnabled = false;
                                    //db.Configuration.ValidateOnSaveEnabled = false;
                                    db.Machine.Attach(machine);
                                    machine.BoardVersions = _confirm.data;
                                    db.Entry(machine).Property(x => x.BoardVersions).IsModified = true;
                                    await db.SaveChangesAsync();
                                }
                            }

                            if (_request.messageId == "ID_TxEnergy") //計費
                            {
                                if (_confirm.status == DataTransferStatus.Accepted)
                                {
                                    decimal couponPoint = 0m;
                                    string farewellMessage = string.Empty;
                                    string receipt = string.Empty;
                                    List<ChargingBill> bill = new List<ChargingBill>();
                                    List<ChargingPrice> chargingPrices = new List<ChargingPrice>();
                                    var txEnergy = JsonConvert.DeserializeObject<TransactionEnergy>(_confirm.data);
                                    var feedto = await db.TransactionRecord.Where(x => x.Id == txEnergy.TxId).Select(x => new { Id = x.Id, ConnectorId = x.ConnectorId, Fee = x.Fee, StopTime = x.StopTime, StartTime = x.StartTime }).FirstOrDefaultAsync();
                                    decimal chargedEnergy = 0m;
                                    if (feedto == null || string.IsNullOrEmpty(feedto.Fee)) return result;
                                    string currency = feedto.Fee.Substring(feedto.Fee.Length - 3);
                                    decimal chargingCost = 0;
                                    if (feedto.Fee.Length > 58)
                                    {
                                        chargingPrices = JsonConvert.DeserializeObject<List<ChargingPrice>>(feedto.Fee.Split('|')[0]);
                                        foreach (var item in txEnergy.PeriodEnergy)
                                        {
                                            DateTime dt = new DateTime(2021, 01, 01, int.Parse(item.Key), 0, 0, DateTimeKind.Utc);
                                            string startTime = dt.ToString("hh:mm tt", new CultureInfo("en-us"));
                                            decimal perfee = 0;

                                            //小數點無條件捨去到第4位
                                            var periodEnergy = (decimal)((int)Decimal.Multiply(item.Value, 10000) / (double)10000);
                                            chargedEnergy += periodEnergy;
                                            if (chargingPrices.Count == 1)
                                            {
                                                perfee = Decimal.Multiply(periodEnergy, chargingPrices[0].Fee);
                                                if (bill.Count == 0)
                                                {
                                                    bill.Add(new ChargingBill()
                                                    {
                                                        StartTime = "12:00 AM",
                                                        EndTime = "11:59 PM",
                                                        Fee = chargingPrices[0].Fee
                                                    });
                                                }

                                                bill[0].PeriodEnergy += periodEnergy;

                                            }
                                            else
                                            {
                                                var price = chargingPrices.Where(x => x.StartTime == startTime).FirstOrDefault();
                                                perfee = Decimal.Multiply(periodEnergy, price.Fee);

                                                bill.Add(new ChargingBill()
                                                {
                                                    StartTime = price.StartTime,
                                                    EndTime = price.EndTime,
                                                    PeriodEnergy = periodEnergy,
                                                    Fee = price.Fee,

                                                });
                                            }
                                            if (bill.Count > 0)
                                            {
                                                bill[bill.Count - 1].Total += DollarRounding(perfee, session.Currency);
                                                chargingCost += bill[bill.Count - 1].Total;

                                                if (bill.Count == 1)
                                                {

                                                    bill[bill.Count - 1].Total = DollarRounding(Decimal.Multiply(bill[0].PeriodEnergy, bill[0].Fee), session.Currency);
                                                    chargingCost = bill[bill.Count - 1].Total;
                                                }
                                            }
                                        }
                                    }
                                    else
                                    {
                                        //以小時計費
                                        foreach (var item in txEnergy.PeriodEnergy)
                                        {  //小數點無條件捨去到第4位
                                            var periodEnergy = (decimal)((int)Decimal.Multiply(item.Value, 10000) / (double)10000);
                                            chargedEnergy += periodEnergy;
                                        }

                                        var fee = decimal.Parse(feedto.Fee.Split('|')[0]);
                                        var charging_stoptime = feedto.StopTime == GlobalConfig.DefaultNullTime ? DateTime.Parse(DateTime.UtcNow.ToString("yyyy/MM/dd HH:mm")) : DateTime.Parse(feedto.StopTime.ToString("yyyy/MM/dd HH:mm"));
                                        var charging_starttime = DateTime.Parse(feedto.StartTime.ToString("yyyy/MM/dd HH:mm"));
                                        chargingCost = Decimal.Multiply((decimal)charging_stoptime.Subtract(charging_starttime).TotalHours, fee);
                                        chargingCost = DollarRounding(chargingCost, session.Currency);
                                    }

                                    // 計算停車費
                                    var parkingFee = decimal.Parse(feedto.Fee.Split('&')[1]);
                                    var stoptime = feedto.StopTime == GlobalConfig.DefaultNullTime ? DateTime.Parse(DateTime.UtcNow.ToString("yyyy/MM/dd HH:mm")) : DateTime.Parse(feedto.StopTime.ToString("yyyy/MM/dd HH:mm"));
                                    var starttime = DateTime.Parse(feedto.StartTime.ToString("yyyy/MM/dd HH:mm"));
                                    var totalHours = stoptime.Subtract(starttime).TotalHours;
                                    var parkingCost = Decimal.Multiply((decimal)totalHours, parkingFee);
                                    parkingCost = DollarRounding(parkingCost, session.Currency);

                                    if (feedto.StopTime != GlobalConfig.DefaultNullTime)
                                    {
                                        //var customerInfo = await db.Customer
                                        //    .Where(x => x.Id == session.CustomerId).Select(x => new { x.InstantStopTxReport, x.ApiUrl, x.ApiKey })
                                        //    .FirstOrDefaultAsync();
                                        var customerInfo = await mainDbService.GetCustomer(session.CustomerId);

                                        decimal accountBalance = 0;
                                        decimal.TryParse(feedto.Fee.Split('+')[1], out accountBalance);

                                        var tx = await db.TransactionRecord.Where(x => x.Id == txEnergy.TxId).FirstOrDefaultAsync();
                                        if (tx == null)
                                        {
                                            logger.LogWarning("Tx is empty");
                                            return result;
                                        }

                                        if (tx.BillingDone) return result;


                                        var startTime = new DateTime(tx.StartTime.Year, tx.StartTime.Month, tx.StartTime.Day, tx.StartTime.Hour, 0, 0);
                                        List<ChargingBill> confirmbill = new List<ChargingBill>();
                                        receipt = string.Format("({0} )Energy:", chargedEnergy);

                                        while (startTime < tx.StopTime)
                                        {
                                            if (bill.Count == 1)
                                            {
                                                confirmbill = bill;
                                                receipt += string.Format("| {0} - {1}:| {2} kWh @ ${3}/kWh= ${4}", tx.StartTime.ToString("hh:mm tt", new CultureInfo("en-us")), tx.StopTime.ToString("hh:mm tt", new CultureInfo("en-us")),
                                                    confirmbill[0].PeriodEnergy.ToString("0.0000"), bill[0].Fee, bill[0].Total);

                                                break;
                                            }
                                            if (bill.Count == 0)
                                            {
                                                receipt += string.Format("| {0} - {1} @ ${2}/hr= ${3}", feedto.StartTime.ToString("hh:mm tt", new CultureInfo("en-us")),
                                                   feedto.StopTime.ToString("hh:mm tt", new CultureInfo("en-us")), feedto.Fee.Split('|')[0], chargingCost);

                                                break;
                                            }
                                            if (bill.Count > 1)
                                            {
                                                var time = startTime.ToString("hh:mm tt", new CultureInfo("en-us"));
                                                var tt = bill.Where(x => x.StartTime == time).FirstOrDefault();
                                                confirmbill.Add(tt);
                                                if (confirmbill.Count == 1)
                                                {
                                                    confirmbill[0].StartTime = tx.StartTime.ToString("hh:mm tt", new CultureInfo("en-us"));
                                                }


                                                var stopTimeText = tx.StopTime.ToString("hh:mm tt", new CultureInfo("en-us"));
                                                if (confirmbill[confirmbill.Count - 1].StartTime.Contains(stopTimeText.Split(' ')[1]))
                                                {
                                                    var subHourText = (int.Parse(stopTimeText.Split(':')[0])).ToString();
                                                    subHourText = subHourText.Length == 1 ? "0" + subHourText : subHourText;
                                                    if (confirmbill[confirmbill.Count - 1].StartTime.Contains(subHourText))
                                                    {
                                                        confirmbill[confirmbill.Count - 1].EndTime = stopTimeText;
                                                    }

                                                }
                                                receipt += string.Format("| {0} - {1}:| {2} kWh @ ${3}/kWh= ${4}", confirmbill[confirmbill.Count - 1].StartTime, confirmbill[confirmbill.Count - 1].EndTime,
                                                    confirmbill[confirmbill.Count - 1].PeriodEnergy.ToString("0.0000"), confirmbill[confirmbill.Count - 1].Fee, confirmbill[confirmbill.Count - 1].Total);

                                                if (confirmbill.Count == 24) break;

                                            }
                                            startTime = startTime.AddHours(1);

                                        }

                                        chargingCost = confirmbill.Count > 0 ? confirmbill.Sum(x => x.Total) : chargingCost;
                                        receipt += string.Format("|Total Energy Fee : ${0}", chargingCost);

                                        receipt += string.Format("|Parking Fee: | {0} - {1}: | {2} @ ${3}/hr= ${4}", feedto.StartTime.ToString("hh:mm tt", new CultureInfo("en-us")),
                                        feedto.StopTime.ToString("hh:mm tt", new CultureInfo("en-us")), (totalHours / 1 >= 1) ? string.Format("{0} hours {1} minutes", (int)totalHours / 1, ((totalHours % 1) * 60).ToString("0.0")) : string.Format("{0} minutes", ((totalHours % 1) * 60).ToString("0.0")), parkingFee, parkingCost);
                                        receipt += string.Format("|Stop Reason: {0}", tx.StopReason);

                                        tx.Cost = chargingCost + parkingCost;


                                        if (customerInfo != null && customerInfo.InstantStopTxReport)
                                        {

                                            var request = new
                                            {
                                                ChargeBoxId = tx.ChargeBoxId,
                                                ConnectorId = tx.ConnectorId,
                                                SessionId = tx.Id,
                                                MeterStart = tx.MeterStart,
                                                MeterStop = tx.MeterStop,
                                                IdTag = tx.StartIdTag,
                                                StartTime = tx.StartTime.ToString(GlobalConfig.UTC_DATETIMEFORMAT),
                                                StopTime = tx.StopTime.ToString(GlobalConfig.UTC_DATETIMEFORMAT),
                                                StopReason = tx.StopReasonId < 1 ? "Unknown" : (tx.StopReasonId > 12 ? "Unknown" : ((Reason)tx.StopReasonId).ToString()),
                                                Receipt = tx.Receipt,
                                                TotalCost = tx.Cost,
                                                Fee = tx.Fee

                                            };

                                            logger.LogDebug("completed_session " + JsonConvert.SerializeObject(request));
                                            var response = await httpClient.Post(customerInfo.ApiUrl + "completed_session", new Dictionary<string, string>()
                                            {
                                                { "PartnerId",session.CustomerId.ToString()}

                                            }, request, customerInfo.ApiKey);

                                            var _httpResult = JsonConvert.DeserializeObject<CPOOuterResponse>(response.Response);
                                            logger.LogDebug("completed_session Response" + response.Response);
                                            JObject jo = JObject.Parse(_httpResult.Data);
                                            if (jo.ContainsKey("CouponPoint"))
                                            {
                                                couponPoint = jo["CouponPoint"].Value<Decimal>();

                                            }

                                            if (jo.ContainsKey("FarewellMessage"))
                                            {
                                                farewellMessage = jo["FarewellMessage"].Value<string>();
                                            }
                                        }

                                        tx.Receipt = receipt;
                                        tx.BillingDone = true;
                                        db.ChangeTracker.AutoDetectChangesEnabled = false;
                                        //db.Configuration.ValidateOnSaveEnabled = false;
                                        db.TransactionRecord.Attach(tx);
                                        db.Entry(tx).Property(x => x.Cost).IsModified = true;
                                        db.Entry(tx).Property(x => x.Receipt).IsModified = true;
                                        db.Entry(tx).Property(x => x.BillingDone).IsModified = true;

                                        await db.SaveChangesAsync();

                                        await mainDbService.AddServerMessage(
                                            ChargeBoxId: session.ChargeBoxId,
                                            OutAction: Actions.DataTransfer.ToString(),
                                            OutRequest:
                                                new DataTransferRequest()
                                                {
                                                    messageId = "FinalCost",
                                                    vendorId = "Phihong Technology",
                                                    data = JsonConvert.SerializeObject(new
                                                    {
                                                        txId = txEnergy.TxId,
                                                        description = JsonConvert.SerializeObject(new
                                                        {
                                                            chargedEnergy = chargedEnergy,
                                                            chargingFee = chargingCost,
                                                            parkTime = (int)stoptime.Subtract(starttime).TotalSeconds,
                                                            parkingFee = parkingCost,
                                                            currency = currency,
                                                            couponPoint = couponPoint,
                                                            accountBalance = accountBalance - tx.Cost,
                                                            farewellMessage = farewellMessage
                                                        })
                                                    })

                                                }
                                                );

                                        await meterValueDbService.InsertAsync(
                                            chargeBoxId: session.ChargeBoxId,
                                            connectorId: feedto.ConnectorId,
                                            value: chargingCost,
                                            createdOn: DateTime.UtcNow,
                                            contextId: (int)ReadingContext.Sample_Periodic,
                                            formatId: (int)ValueFormat.Raw,
                                            measurandId: (int)Measurand.TotalCost,
                                            phaseId: -1,
                                            locationId: -1,
                                            unitId: -1,
                                            transactionId: feedto.Id);

                                        using (SqlConnection conn = await webDbConnectionFactory.CreateAsync())
                                        {
                                            var parameters = new DynamicParameters();
                                            parameters.Add("@IdTag", tx.StartIdTag, DbType.String, ParameterDirection.Input, 50);
                                            parameters.Add("@parentIdTag", accountBalance - tx.Cost, DbType.String, ParameterDirection.Input, 50);
                                            string strSql = "update [dbo].[LocalListDetail] set parentIdTag =@parentIdTag  where ListId = 27 and IdTag=@IdTag; ";
                                            await conn.ExecuteAsync(strSql, parameters);

                                        }

                                        #region 提供給PHA 過CDFA認證 使用
                                        if (tx.CustomerId == Guid.Parse("10C7F5BD-C89A-4E2A-8611-B617E0B41A73"))
                                        {
                                            var mail_response = httpClient.PostFormDataAsync("https://evcb.zerovatech.com/CDFA/" + tx.Id, new Dictionary<string, string>()
                                            {
                                                { "email","2"},
                                                { "to","wonderj@phihongusa.com;jessica_tseng@phihong.com.tw"}
                                                //{ "to","jessica_tseng@phihong.com.tw"}
                                           }, null);

                                            logger.LogTrace(JsonConvert.SerializeObject(mail_response));

                                        }
                                        #endregion

                                    }
                                    else
                                    {
                                        await mainDbService.AddServerMessage(
                                            ChargeBoxId: session.ChargeBoxId,
                                            OutAction: Actions.DataTransfer.ToString(),
                                            OutRequest:
                                             new DataTransferRequest()
                                             {
                                                 messageId = "RunningCost",
                                                 vendorId = "Phihong Technology",
                                                 data = JsonConvert.SerializeObject(new
                                                 {
                                                     txId = txEnergy.TxId,
                                                     description = JsonConvert.SerializeObject(new
                                                     {
                                                         chargedEnergy = chargedEnergy,
                                                         chargingFee = chargingCost,
                                                         parkTime = (int)stoptime.Subtract(starttime).TotalSeconds,
                                                         parkingFee = parkingCost,
                                                         currency = currency
                                                     })

                                                 })

                                             }
                                        );

                                        await meterValueDbService.InsertAsync(
                                            chargeBoxId: session.ChargeBoxId,
                                            connectorId: (byte)feedto.ConnectorId,
                                            value: chargingCost,
                                            createdOn: DateTime.UtcNow,
                                            contextId: (int)ReadingContext.Sample_Periodic,
                                            formatId: (int)ValueFormat.Raw,
                                            measurandId: (int)Measurand.ChargingCost,
                                            phaseId: -1,
                                            locationId: -1,
                                            unitId: -1,
                                            transactionId: feedto.Id
                                            );
                                    }
                                }

                            }

                            #region 台泥
                            if (_request.messageId == "ID_GetTxUserInfo")
                            {
                                var txUserInfo = JsonConvert.DeserializeObject<ID_GetTxUserInfo>(_confirm.data);
                                if (session.CustomerId == new Guid("009E603C-79CD-4620-A2B8-D9349C0E8AD8"))
                                {
                                    var request = new
                                    {
                                        ChargeBoxId = session.ChargeBoxId,
                                        ConnectorId = txUserInfo.ConnectorId,
                                        SessionId = txUserInfo.TxId,
                                        SerialNo = txUserInfo.SerialNo,
                                        StartTime = txUserInfo.StartTime.ToString(GlobalConfig.UTC_DATETIMEFORMAT),
                                        VEMData = txUserInfo.VEMData
                                    };

                                    var response = httpClient.Post(GlobalConfig.TCC_API_URL + "start_session", new Dictionary<string, string>()
                                    {
                                        { "PartnerId",session.CustomerId.ToString()}

                                    }, request, GlobalConfig.TCC_SALTKEY);

                                    logger.LogDebug(JsonConvert.SerializeObject(response));
                                }

                            }

                            #endregion
                        }
                    }
                    break;
                case Actions.ChangeAvailability:
                    {
                        ChangeAvailabilityConfirmation _confirm = confirm as ChangeAvailabilityConfirmation;
                        ChangeAvailabilityRequest _request = _confirm.GetRequest() as ChangeAvailabilityRequest;

                        using (var db = await maindbContextFactory.CreateDbContextAsync())
                        {
                            var operation = await db.MachineOperateRecord.Where(x => x.SerialNo == requestId &&
                            x.ChargeBoxId == session.ChargeBoxId && x.Status == 0).FirstOrDefaultAsync();
                            if (operation != null)
                            {
                                operation.FinishedOn = DateTime.UtcNow;
                                operation.Status = 1;//電樁有回覆
                                operation.EVSE_Status = (int)_confirm.status;
                                operation.EVSE_Value = _confirm.status.ToString();
                                await db.SaveChangesAsync();
                            }

                        }
                    }
                    break;
                case Actions.ClearCache:
                    {
                        ClearCacheConfirmation _confirm = confirm as ClearCacheConfirmation;
                        ClearCacheRequest _request = _confirm.GetRequest() as ClearCacheRequest;

                        using (var db = await maindbContextFactory.CreateDbContextAsync())
                        {
                            var operation = await db.MachineOperateRecord.Where(x => x.SerialNo == requestId &&
                            x.ChargeBoxId == session.ChargeBoxId && x.Status == 0).FirstOrDefaultAsync();
                            if (operation != null)
                            {
                                operation.FinishedOn = DateTime.UtcNow;
                                operation.Status = 1;//電樁有回覆
                                operation.EVSE_Status = (int)_confirm.status;
                                operation.EVSE_Value = _confirm.status.ToString();
                                await db.SaveChangesAsync();
                            }

                        }
                    }
                    break;
                case Actions.RemoteStartTransaction:
                    {
                        RemoteStartTransactionConfirmation _confirm = confirm as RemoteStartTransactionConfirmation;
                        RemoteStartTransactionRequest _request = _confirm.GetRequest() as RemoteStartTransactionRequest;

                        using (var db = await maindbContextFactory.CreateDbContextAsync())
                        {
                            var operation = await db.MachineOperateRecord.Where(x => x.SerialNo == requestId &&
                            x.ChargeBoxId == session.ChargeBoxId && x.Status == 0).FirstOrDefaultAsync();
                            if (operation != null)
                            {
                                operation.FinishedOn = DateTime.UtcNow;
                                operation.Status = 1;//電樁有回覆
                                operation.EVSE_Status = (int)_confirm.status;
                                operation.EVSE_Value = _confirm.status.ToString();
                                await db.SaveChangesAsync();
                            }

                        }
                    }
                    break;
                case Actions.RemoteStopTransaction:
                    {
                        RemoteStopTransactionConfirmation _confirm = confirm as RemoteStopTransactionConfirmation;
                        RemoteStopTransactionRequest _request = _confirm.GetRequest() as RemoteStopTransactionRequest;

                        using (var db = await maindbContextFactory.CreateDbContextAsync())
                        {
                            var operation = await db.MachineOperateRecord.Where(x => x.SerialNo == requestId &&
                            x.ChargeBoxId == session.ChargeBoxId && x.Status == 0).FirstOrDefaultAsync();
                            if (operation != null)
                            {
                                operation.FinishedOn = DateTime.UtcNow;
                                operation.Status = 1;//電樁有回覆
                                operation.EVSE_Status = (int)_confirm.status;
                                operation.EVSE_Value = _confirm.status.ToString();
                                await db.SaveChangesAsync();
                            }

                        }
                    }
                    break;
                case Actions.Reset:
                    {
                        ResetConfirmation _confirm = confirm as ResetConfirmation;
                        ResetRequest _request = _confirm.GetRequest() as ResetRequest;

                        using (var db = await maindbContextFactory.CreateDbContextAsync())
                        {
                            var operation = await db.MachineOperateRecord.Where(x => x.SerialNo == requestId &&
                            x.ChargeBoxId == session.ChargeBoxId && x.Status == 0).FirstOrDefaultAsync();
                            if (operation != null)
                            {
                                operation.FinishedOn = DateTime.UtcNow;
                                operation.Status = 1;//電樁有回覆
                                operation.EVSE_Status = (int)_confirm.status;
                                operation.EVSE_Value = _confirm.status.ToString();
                                await db.SaveChangesAsync();
                            }

                        }
                    }
                    break;
                case Actions.ChangeConfiguration:
                    {
                        ChangeConfigurationConfirmation _confirm = confirm as ChangeConfigurationConfirmation;
                        ChangeConfigurationRequest _request = _confirm.GetRequest() as ChangeConfigurationRequest;

                        using (var db = await maindbContextFactory.CreateDbContextAsync())
                        {
                            var operation = await db.MachineOperateRecord.Where(x => x.SerialNo == requestId &&
                     x.ChargeBoxId == session.ChargeBoxId && x.Status == 0).FirstOrDefaultAsync();

                            if (operation != null)
                            {
                                operation.FinishedOn = DateTime.UtcNow;
                                operation.Status = 1;//電樁有回覆
                                operation.EVSE_Status = (int)_confirm.status;
                                operation.EVSE_Value = _confirm.status.ToString();

                            }

                            if (_confirm.status == Packet.Messages.SubTypes.ConfigurationStatus.Accepted || _confirm.status == Packet.Messages.SubTypes.ConfigurationStatus.RebootRequired)
                            {
                                var configure = await db.MachineConfigurations.Where(x => x.ChargeBoxId == session.ChargeBoxId).ToListAsync();

                                var foundConfig = configure.Find(x => x.ConfigureName == _request.key);
                                if (foundConfig != null)
                                {
                                    foundConfig.ReadOnly = false;
                                    foundConfig.ConfigureSetting = _request.value;
                                }
                                else
                                {
                                    await db.MachineConfigurations.AddAsync(new MachineConfiguration()
                                    {
                                        ChargeBoxId = session.ChargeBoxId,
                                        ConfigureName = _request.key,
                                        ReadOnly = false,
                                        ConfigureSetting = _request.value
                                    });
                                }
                            }
                            await db.SaveChangesAsync();
                        }

                    }
                    break;
                case Actions.GetConfiguration:
                    {

                        try
                        {
                            GetConfigurationConfirmation _confirm = confirm as GetConfigurationConfirmation;
                            //  GetConfigurationRequest _request = _confirm.GetRequest() as GetConfigurationRequest;

                            using (var db = await maindbContextFactory.CreateDbContextAsync())
                            {
                                var configure = await db.MachineConfigurations.Where(x => x.ChargeBoxId == session.ChargeBoxId).ToListAsync();

                                if (_confirm.configurationKey != null)
                                {
                                    foreach (var item in _confirm.configurationKey)
                                    {
                                        string oldValue = string.Empty;
                                        if (item.key == null)
                                        {
                                            logger.LogTrace("*********************");
                                        }
                                        var foundConfig = configure.Find(x => x.ConfigureName == item.key);


                                        if (foundConfig != null)
                                        {
                                            if (foundConfig.ConfigureName == null)
                                            {
                                                logger.LogTrace("*********************");
                                            }

                                            if (foundConfig.ConfigureName == "SecurityProfile")
                                            {
                                                oldValue = foundConfig.ConfigureSetting;
                                            }

                                            foundConfig.ReadOnly = item.IsReadOnly;
                                            foundConfig.ConfigureSetting = string.IsNullOrEmpty(item.value) ? string.Empty : item.value;
                                        }
                                        else
                                        {
                                            await db.MachineConfigurations.AddAsync(new MachineConfiguration()
                                            {
                                                ChargeBoxId = session.ChargeBoxId,
                                                ConfigureName = item.key,
                                                ReadOnly = item.IsReadOnly,
                                                ConfigureSetting = string.IsNullOrEmpty(item.value) ? string.Empty : item.value,
                                                Exists = true
                                            });
                                        }


                                    }
                                }
                                if (_confirm.unknownKey != null)
                                {

                                    foreach (var item in _confirm.unknownKey)
                                    {
                                        var foundConfig = configure.Find(x => x.ConfigureName == item);
                                        if (foundConfig != null)
                                        {
                                            foundConfig.ReadOnly = true;
                                            foundConfig.ConfigureSetting = string.Empty;
                                            foundConfig.Exists = false;
                                        }
                                        else
                                        {
                                            await db.MachineConfigurations.AddAsync(new MachineConfiguration()
                                            {
                                                ChargeBoxId = session.ChargeBoxId,
                                                ConfigureName = item
                                            });
                                        }
                                    }
                                }

                                var operation = await db.MachineOperateRecord.Where(x => x.SerialNo == requestId &&
                               x.ChargeBoxId == session.ChargeBoxId && x.Status == 0).FirstOrDefaultAsync();

                                if (operation != null)
                                {
                                    operation.FinishedOn = DateTime.UtcNow;
                                    operation.Status = 1;//電樁有回覆
                                    operation.EVSE_Status = 1;
                                    operation.EVSE_Value = JsonConvert.SerializeObject(_confirm.configurationKey, Formatting.None);

                                }

                                await db.SaveChangesAsync();

                            }
                        }
                        catch (Exception ex)
                        {
                            logger.LogError(ex.ToString());
                        }

                    }
                    break;
                case Actions.UnlockConnector:
                    {
                        UnlockConnectorConfirmation _confirm = confirm as UnlockConnectorConfirmation;
                        UnlockConnectorRequest _request = _confirm.GetRequest() as UnlockConnectorRequest;

                        using (var db = await maindbContextFactory.CreateDbContextAsync())
                        {
                            var operation = await db.MachineOperateRecord.Where(x => x.SerialNo == requestId &&
                            x.ChargeBoxId == session.ChargeBoxId && x.Status == 0).FirstOrDefaultAsync();
                            if (operation != null)
                            {
                                operation.FinishedOn = DateTime.UtcNow;
                                operation.Status = 1;//電樁有回覆
                                operation.EVSE_Status = (int)_confirm.status;
                                operation.EVSE_Value = _confirm.status.ToString();
                                await db.SaveChangesAsync();
                            }

                        }
                    }
                    break;
                default:
                    {
                        logger.LogWarning(string.Format("Not Implement {0} Logic(ExecuteCoreConfirm)", confirm.GetType().ToString().Replace("OCPPPackage.Messages.Core.", "")));
                    }
                    break;
            }
        }
        catch (Exception ex)
        {
            logger.LogDebug("123 " + action + " " + ex.ToString());
        }



        return result;
    }


    internal async Task<MessageResult> ReceivedCoreError(Actions action, string errorMsg, ClientData session, string requestId)
    {
        MessageResult result = new MessageResult() { Success = true };

        switch (action)
        {
            case Actions.ChangeAvailability:
            case Actions.ChangeConfiguration:
            case Actions.ClearCache:
            case Actions.RemoteStartTransaction:
            case Actions.RemoteStopTransaction:
            case Actions.Reset:
            case Actions.GetConfiguration:
            case Actions.UnlockConnector:
            case Actions.DataTransfer:
                {
                    if (action == Actions.DataTransfer)
                    {
                        logger.LogDebug(string.Format("DataTransfer Error {0}: {1}", session.ChargeBoxId, requestId));
                    }

                    using (var db = await maindbContextFactory.CreateDbContextAsync())
                    {
                        var operation = await db.MachineOperateRecord.Where(x => x.SerialNo == requestId &&
                        x.ChargeBoxId == session.ChargeBoxId && x.Status == 0).FirstOrDefaultAsync();
                        if (operation != null)
                        {
                            operation.FinishedOn = DateTime.UtcNow;
                            operation.Status = 1;//電樁有回覆
                            operation.EVSE_Status = (int)255;//錯誤
                            operation.EVSE_Value = errorMsg;
                            await db.SaveChangesAsync();
                        }

                    }

                }
                break;

            default:
                {
                    Console.WriteLine(string.Format("Not Implement {0} Logic(ReceivedCoreError)", action));
                }
                break;
        }
        return result;

    }


    /// <summary>
    /// 依據幣值處理4捨5入
    /// </summary>
    /// <param name="money"></param>
    /// <param name="currency"></param>
    /// <returns></returns>
    private decimal DollarRounding(decimal money, string currency)
    {


        if (currency == "USD" || currency == "EUR")
        {

            //0.4867
            if ((double)((int)(money * 100) + 0.5) <= (double)(money * 100))
            {

                //money = Decimal.Add(money, (decimal)0.01);//0.4967

            }
            money = Math.Round(money, 2, MidpointRounding.AwayFromZero);
            money = Decimal.Parse(money.ToString("0.00"));


        }
        else
        {
            if ((double)((int)(money) + 0.5) <= (double)money)
            {
                //  money = (int) money + 1;
            }
            money = Math.Round(money, 0, MidpointRounding.AwayFromZero);
            money = Decimal.Parse(money.ToString("0"));
        }

        return money;
    }
}