EVCB_OCPP.TaskScheduler/EVCB_OCPP.TaskScheduler/Services/DatabaseService.cs

using Dapper;
using EVCB_OCPP.TaskScheduler.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using Transaction = EVCB_OCPP.TaskScheduler.Models.Transaction;

namespace EVCB_OCPP.TaskScheduler.Services
{
    internal class DatabaseService
    {
        private NLog.ILogger logger = NLog.LogManager.GetCurrentClassLogger();
        private string mainDBConnectString = ConfigurationManager.ConnectionStrings["MainDBContext"].ToString();
        private string onlineDBConnectString = ConfigurationManager.ConnectionStrings["OnlineLogDBContext"].ToString();

        internal DatabaseService()
        {

        }

        internal string GetCustomerName(Guid customerId)
        {
            string name = string.Empty;
            try
            {
                using (var dbConn = new SqlConnection(mainDBConnectString))
                {
                    dbConn.Open();
                    var parameters = new DynamicParameters();
                    parameters.Add("@Id", customerId.ToString(), System.Data.DbType.String);
                    name = dbConn.Query<string>("SELECT Name FROM [dbo].[Customer] where Id=@Id  ", parameters).FirstOrDefault();
                }

            }
            catch (Exception ex)
            {
                logger.Error("Query Data Error " + ex.ToString());
            }

            return name;
        }


        internal bool IsCallParterAPI(Guid customerId)
        {
            bool result = false;
            try
            {
                using (var dbConn = new SqlConnection(mainDBConnectString))
                {
                    dbConn.Open();
                    var parameters = new DynamicParameters();
                    parameters.Add("@Id", customerId.ToString(), System.Data.DbType.String);
                    result = dbConn.ExecuteScalar<bool>("SELECT count(*) FROM [dbo].[Customer] where Id=@Id and CallPartnerApiOnSchedule=1 ", parameters);
                }

            }
            catch (Exception ex)
            {
                logger.Error("Query Data Error " + ex.ToString());
            }

            return result;


        }

        internal CustomerConnectionDto GetAPIConnectionInfo(Guid partnerId)
        {
            CustomerConnectionDto result = new CustomerConnectionDto();
            string key = string.Empty;
            var parameters = new DynamicParameters();
            parameters.Add("@Id", partnerId, DbType.Guid, ParameterDirection.Input);
            using (SqlConnection conn = new SqlConnection(mainDBConnectString))
            {
                string strSql = "Select ApiKey, ApiUrl from [dbo].[Customer] where Id=@Id; ";
                result = conn.Query<CustomerConnectionDto>(strSql, parameters).FirstOrDefault();
            }
            return result;

        }


        internal List<Guid> GetCallParterAPICustomers()
        {
            List<Guid> result = new List<Guid>();
            try
            {
                using (var dbConn = new SqlConnection(mainDBConnectString))
                {
                    dbConn.Open();
                    result = dbConn.Query<Guid>("SELECT Id FROM [dbo].[Customer] where CallPartnerApiOnSchedule=1").ToList();
                }

            }
            catch (Exception ex)
            {
                logger.Error("Query Data Error " + ex.ToString());
            }

            return result;
        }

        internal List<Guid> GetNotifyStopTransactionCustomers()
        {
            List<Guid> result = new List<Guid>();
            try
            {
                using (var dbConn = new SqlConnection(mainDBConnectString))
                {
                    dbConn.Open();
                    result = dbConn.Query<Guid>("SELECT Id FROM [dbo].[Customer] where CallPartnerApiOnSchedule=1 and InstantStopTxReport=0").ToList();
                }

            }
            catch (Exception ex)
            {
                logger.Error("Query Data Error " + ex.ToString());
            }

            return result;
        }


        internal List<Transaction> GetNeedReportSession(Guid customerId, bool isgoing, int size)
        {
            List<Transaction> result = new List<Transaction>();
            try
            {
                using (var dbConn = new SqlConnection(mainDBConnectString))
                {
                    dbConn.Open();
                    var parameters = new DynamicParameters();
                    parameters.Add("@CustomerId", customerId.ToString(), System.Data.DbType.String);
                    string sqlString = string.Empty;

                    // 20220211 revised
                    if (isgoing)
                    {
                        sqlString = "SELECT Top(" + size + ") Id, ChargeBoxId,ConnectorId,StartTime,MeterStart,StartIdTag FROM [dbo].[TransactionRecord] where CustomerId=@CustomerId and StopTime='1991/1/1' and StartTransactionReportedOn='1991/1/1' ";
                    }
                    else
                    {
                        if (customerId == new Guid("009E603C-79CD-4620-A2B8-D9349C0E8AD8"))
                        {
                            sqlString = "SELECT Top(" + size + ") Id,ChargeBoxId,ConnectorId,StartTime,StopTime,MeterStart,MeterStop,StartIdTag ,StopReasonId,Receipt,Cost,Fee FROM [dbo].[TransactionRecord] where CustomerId=@CustomerId and StopTime!='1991/1/1' and StopTransactionReportedOn='1991/1/1' and  BillingDone=1";
                        }
                        else
                        {
                            sqlString = "SELECT Top(" + size + ") Id,ChargeBoxId,ConnectorId,StartTime,StopTime,MeterStart,MeterStop,StartIdTag ,StopReasonId,Receipt,Cost,Fee FROM [dbo].[TransactionRecord] where CustomerId=@CustomerId and StopTime!='1991/1/1' and StopTransactionReportedOn='1991/1/1' ";
                        }
                    }

                    result = dbConn.Query<Transaction>(sqlString, parameters).ToList();
                }

            }
            catch (Exception ex)
            {
                logger.Error("Query Data Error " + ex.ToString());
            }

            return result;
        }

        internal List<MachineOperateRecord> GetNeedReportExecution(Guid customerId, int size)
        {
            List<MachineOperateRecord> result = new List<MachineOperateRecord>();
            try
            {
                using (var dbConn = new SqlConnection(mainDBConnectString))
                {
                    dbConn.Open();
                    var parameters = new DynamicParameters();                  
                    parameters.Add("@CustomerId", customerId.ToString(), System.Data.DbType.String);
                    string sqlString = string.Empty;

                    sqlString = "SELECT Top(" + size + ") Machine.CustomerId, record.Id, record.ChargeBoxId,record.Action,record.SerialNo,record.Status,record.EVSE_Value,record.EVSE_Status FROM[dbo].[MachineOperateRecord] record" +
                        " left join[dbo].[Machine]   on record.ChargeBoxId = Machine.ChargeBoxId " +
                        "  where Machine.CustomerId =@CustomerId and Status!= 0 and RequestType = 1 and ReportedOn = '1991/01/01'";
                    result = dbConn.Query<MachineOperateRecord>(sqlString, parameters).ToList();
                }




            }
            catch (Exception ex)
            {
                logger.Error("GetNeedReportExecution Error " + ex.ToString());
            }

            return result;
        }


        internal void ReportStartTx(Dictionary<int, TransactionResponse> reportResults)
        {

            try
            {
                using (var tranScope = new TransactionScope())
                {
                    using (var dbConn = new SqlConnection(mainDBConnectString))
                    {
                        dbConn.Open();

                        foreach (var kv in reportResults)
                        {
                            var parameters = new DynamicParameters();
                            parameters.Add("@Id", kv.Key, DbType.Int32, ParameterDirection.Input);
                            parameters.Add("@StartTransactionReportedOn", kv.Value.StartTransactionReportedOn, DbType.DateTime, ParameterDirection.Input);
                            parameters.Add("@ErrorMsg", kv.Value.ErrorMsg, DbType.String, ParameterDirection.Input);
                            dbConn.Execute("UPDATE [dbo].[TransactionRecord] set StartTransactionReportedOn=@StartTransactionReportedOn, ErrorMsg=@ErrorMsg  where Id=@Id", parameters);
                        }

                        tranScope.Complete();
                    }
                }

            }
            catch (Exception ex)
            {
                logger.Error("ReportStartTx Error " + ex.ToString());
            }


        }

        internal void ReportStopTx(Dictionary<int, TransactionResponse> reportResults)
        {

            try
            {
                using (var tranScope = new TransactionScope())
                {
                    using (var dbConn = new SqlConnection(mainDBConnectString))
                    {
                        dbConn.Open();

                        foreach (var kv in reportResults)
                        {
                            var parameters = new DynamicParameters();
                            parameters.Add("@Id", kv.Key, DbType.Int32, ParameterDirection.Input);
                            parameters.Add("@StopTransactionReportedOn", kv.Value.StopTransactionReportedOn, DbType.DateTime, ParameterDirection.Input);
                            parameters.Add("@ErrorMsg", kv.Value.ErrorMsg, DbType.String, ParameterDirection.Input);
                            dbConn.Execute("UPDATE [dbo].[TransactionRecord] set StopTransactionReportedOn=@StopTransactionReportedOn, ErrorMsg=@ErrorMsg  where Id=@Id", parameters);
                        }

                        tranScope.Complete();
                    }
                }

            }
            catch (Exception ex)
            {
                logger.Error("ReportStopTx Error " + ex.ToString());
            }


        }


        internal void TurntoTimeoutMachineOperateCommands(int intervalSeconds)
        {

            try
            {
                using (var dbConn = new SqlConnection(mainDBConnectString))
                {
                    dbConn.Open();
                    dbConn.Execute("CheckUnCommitMachineOperateCommand", new { @TimeoutIntervalSeconds = intervalSeconds }, null, null, commandType: CommandType.StoredProcedure);
                }

            }
            catch (Exception ex)
            {
                logger.Error("TurntoTimeoutMachineOperateCommands Error " + ex.ToString());
            }


        }

        internal void ReportExecution(Dictionary<int, BasicResponse> reportResults)
        {
            try
            {
                using (var tranScope = new TransactionScope())
                {
                    using (var dbConn = new SqlConnection(mainDBConnectString))
                    {
                        dbConn.Open();

                        foreach (var kv in reportResults)
                        {
                            var parameters = new DynamicParameters();
                            parameters.Add("@Id", kv.Key, DbType.Int32, ParameterDirection.Input);
                            parameters.Add("@ReportedOn", kv.Value.ReportedOn, DbType.DateTime, ParameterDirection.Input);

                            dbConn.Execute("UPDATE [dbo].[MachineOperateRecord] set ReportedOn=@ReportedOn where Id=@Id", parameters);
                        }

                        tranScope.Complete();
                    }
                }

            }
            catch (Exception ex)
            {
                logger.Error("ReportExecution Error " + ex.ToString());
            }

        }
    }
}