TCC_OCPP16.TaskScheduler/EVCB_OCPP.TaskScheduler/Jobs/TriggerBillJob.cs

using Dapper;
using Quartz;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.InteropServices;
using System.Runtime.Remoting.Metadata.W3cXsd2001;
using System.Text;
using System.Threading.Tasks;

namespace EVCB_OCPP.TaskScheduler.Jobs
{
    [DisallowConcurrentExecution]
    internal class TriggerBillJob : IJob
    {
        private NLog.ILogger _logger = NLog.LogManager.GetCurrentClassLogger();
        private string mainDBConnectString = ConfigurationManager.ConnectionStrings["MainDBContext"].ToString();
        public async Task Execute(IJobExecutionContext context)
        {

            _logger.Debug(this.ToString() + " :Starting........");
            List<TriggerTransaction> transactions = await GetTriggerTransaction();


            foreach (TriggerTransaction transaction in transactions)
            {
                string sqlString = string.Format("INSERT INTO [dbo].[ServerMessage]([SerialNo],[OutAction],[OutRequest],[InMessage],[CreatedOn],[CreatedBy],[ReceivedOn],[ChargeBoxId],[UpdatedOn]) " +
           "VALUES(@SerialNo,@OutAction,@OutRequest,@InMessage,@CreatedOn,@CreatedBy,@ReceivedOn,@ChargeBoxId,@UpdatedOn);");

                string content = @"{""vendorId"":""Phihong Technology"",""messageId"":""ID_TxEnergy"",""data"":""{\""txId\"":"+ transaction.Id + @",\""ConnectorId\"":" + transaction.ConnectorId + @"}""}";

                using (var dbConn = new SqlConnection(mainDBConnectString))
                {
                    dbConn.Open();
                    var parameters = new DynamicParameters();
                    parameters.Add("@SerialNo", Guid.NewGuid().ToString(), System.Data.DbType.String);
                    parameters.Add("@OutAction", "DataTransfer", System.Data.DbType.String);
                    parameters.Add("@OutRequest", content, System.Data.DbType.String);
                    parameters.Add("@InMessage", string.Empty, System.Data.DbType.String);
                    parameters.Add("@CreatedOn", DateTime.UtcNow, System.Data.DbType.DateTime);
                    parameters.Add("@CreatedBy", "Task", System.Data.DbType.String);
                    parameters.Add("@ReceivedOn", DefaultSetting.DefaultNullTime, System.Data.DbType.DateTime);
                    parameters.Add("@ChargeBoxId", transaction.ChargeBoxId, System.Data.DbType.String);
                    parameters.Add("@UpdatedOn", DefaultSetting.DefaultNullTime, System.Data.DbType.DateTime);

                    await dbConn.ExecuteAsync(sqlString, parameters);

                }

            }
            _logger.Debug(this.ToString() + " :End........");
        }




        private async Task<List<TriggerTransaction>> GetTriggerTransaction()
        {
            List<TriggerTransaction> result = new List<TriggerTransaction>();
            //從10/12開始

            string sqlString = string.Format("SELECT TOP(100) Id, ChargeBoxId,ConnectorId, StopTime  FROM [StandardOCPP_Main].[dbo].[TransactionRecord] " +
            "where ChargeBoxId != 'EA702C1TF8P3D214600098A0' and  StopTime != '1991-01-01 00:00:00.000' and len(Fee) > 0 and  len(Receipt) = 0 and CreatedOn > '2023-10-12' and UploadedtoTTIA=0");

            using (var dbConn = new SqlConnection(mainDBConnectString))
            {
                dbConn.Open();
                var dbresult = await dbConn.QueryAsync<TriggerTransaction>(sqlString);
                result = dbresult.ToList();
            }
            return result.Where(x=>x.StopTime.AddMinutes(5) < DateTime.UtcNow).ToList();

        }


        public class TriggerTransaction
        {
            public string ChargeBoxId { set; get; }
            public int Id { set; get; }

            public DateTime StopTime { set; get; }

            public int ConnectorId { set; get; }


        }

    }

}