EVCB_OCPP.TaskScheduler/EVCB_OCPP.TaskScheduler/Jobs/CheckEVSEOnlineJob.cs

using Dapper;
using EVCB_OCPP.TaskScheduler.Models;
using Quartz;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EVCB_OCPP.TaskScheduler.Jobs
{
    /// <summary>
    /// Update  EVSE's online status
    /// </summary>
    [DisallowConcurrentExecution]
    public class CheckEVSEOnlineJob : IJob
    {
        private NLog.ILogger logger = NLog.LogManager.GetCurrentClassLogger();
        private DateTime latestHeartbeatTime = DateTime.Now;
        private List<EVSEOnlineRecord> updateData = new List<EVSEOnlineRecord>();
        private List<EVSEOnlineRecord> insertData = new List<EVSEOnlineRecord>();
        private string mainDBConnectString = ConfigurationManager.ConnectionStrings["MainDBContext"].ToString();
        private string onlineDBConnectString = ConfigurationManager.ConnectionStrings["OnlineLogDBContext"].ToString();

        public CheckEVSEOnlineJob()
        {
            CreateEVSEOnlineRecordTable();
        }
        //測試方式

        // A: 假裝更新心跳包
        // B: 一直開著程式  / 一段時間關程式(關掉時間點 online/offline)


        public async Task Execute(IJobExecutionContext context)
        {
            logger.Debug(this.ToString() + " :Starting........");
            try
            {
                List<EVSECurrentStatus> _EVSEs = GetEVSEs();
                var checktime = DateTime.UtcNow.AddDays(-3);
                _EVSEs = _EVSEs.Where(x => x.HeartbeatUpdatedOn > checktime).ToList();

                foreach (var evse in _EVSEs)
                {
                    latestHeartbeatTime = evse.HeartbeatUpdatedOn;
                    if (IsOnlineNow(evse))
                    {
                        if (evse.Online)
                        {

                            if (latestHeartbeatTime.Minute % 10 == 0)
                            {
                                //on-on
                                //現在是整點，找上一筆填入end time and insert start time 

                                //  var _pickDate = currentTime.AddHours(-1);
                                var _pickDate = latestHeartbeatTime;
                                bool isCrossDay = false;
                                int _startHour = _pickDate.AddHours(-6).Hour; //往回巡視六個小時前紀錄



                                //跨日處理
                                if (_startHour > _pickDate.Hour)
                                {
                                    isCrossDay = true;

                                }

                                var _picks = await GetOnlineRecords(_pickDate, evse.CustomerId.ToString(), evse.ChargeBoxId, isCrossDay ? 0 : _startHour, _pickDate.Hour);

                                if (isCrossDay)
                                {
                                    var _picksCrossData = await GetOnlineRecords(_pickDate.AddHours(-6), evse.CustomerId.ToString(), evse.ChargeBoxId, _startHour, 23);
                                    _picks.AddRange(_picksCrossData);
                                }

                                _picks = _picks.OrderBy(x => x.OnlineTime).ToList();

                                bool _isIgnore = true;

                                for (int i = 0; i < _picks.Count; i++)
                                {
                                    if (_picks[i].HourIndex != evse.HeartbeatUpdatedOn.Hour && _picks[i].OfflineTime == DefaultSetting.DefaultNullTime)
                                    {
                                        _picks[i].OfflineTime = new DateTime(_picks[i].OnlineTime.AddHours(1).Year,
                                            _picks[i].OnlineTime.AddHours(1).Month, _picks[i].OnlineTime.AddHours(1).Day, _picks[i].OnlineTime.AddHours(1).Hour, 0, 0);

                                        _picks[i].TotalMinute = (int)_picks[i].OfflineTime.Subtract(_picks[i].OnlineTime).TotalMinutes;
                                        updateData.Add(_picks[i]);

                                        var checkTime = _picks[i].OfflineTime;
                                        while ((int)latestHeartbeatTime.Subtract(checkTime).TotalHours > 0)
                                        {
                                            var _existedCount = _picks.Where(x => x.HourIndex == checkTime.Hour).ToList().Count;

                                            if (_existedCount == 0)
                                            {
                                                insertData.Add(new EVSEOnlineRecord()
                                                {
                                                    CustomerId = evse.CustomerId,
                                                    StationId = "0",
                                                    HourIndex = checkTime.Hour,
                                                    ChargeBoxId = evse.ChargeBoxId,
                                                    OnlineTime = new DateTime(checkTime.Year, checkTime.Month, checkTime.Day, checkTime.Hour, 0, 0),
                                                    OfflineTime = new DateTime(checkTime.AddHours(1).Year, checkTime.AddHours(1).Month, checkTime.AddHours(1).Day, checkTime.AddHours(1).Hour, 0, 0),
                                                    TotalMinute = 60

                                                });
                                                checkTime = checkTime.AddHours(1);
                                            }
                                        }
                                    }

                                }

                                _isIgnore = _picks.Where(x => x.HourIndex == latestHeartbeatTime.Hour).ToList().Count == 0 ? false : true;

                                if (!_isIgnore)
                                {
                                    insertData.Add(new EVSEOnlineRecord()
                                    {
                                        CustomerId = evse.CustomerId,
                                        StationId = "0",
                                        HourIndex = evse.HeartbeatUpdatedOn.Hour,
                                        ChargeBoxId = evse.ChargeBoxId,
                                        OnlineTime = new DateTime(latestHeartbeatTime.Year,
                                                latestHeartbeatTime.Month, latestHeartbeatTime.Day, latestHeartbeatTime.Hour, 0, 0),
                                        OfflineTime = DefaultSetting.DefaultNullTime

                                    });
                                }
                            }

                        }
                        else
                        {
                            //off - on 
                            UpdateEVSECurrentStatus(evse.CustomerId.ToString(), evse.ChargeBoxId, true, DefaultSetting.DefaultNullTime);

                            insertData.Add(new EVSEOnlineRecord()
                            {
                                CustomerId = evse.CustomerId,
                                StationId = "0",
                                HourIndex = evse.HeartbeatUpdatedOn.Hour,
                                ChargeBoxId = evse.ChargeBoxId,
                                OnlineTime = evse.HeartbeatUpdatedOn,

                            });


                        }
                    }
                    else
                    {

                        if (evse.Online)
                        {

                            //on -off  
                            UpdateEVSECurrentStatus(evse.CustomerId.ToString(), evse.ChargeBoxId, false, evse.HeartbeatUpdatedOn);

                            var _pickDate = evse.HeartbeatUpdatedOn.Date;
                            var _picks = await GetOnlineRecords(_pickDate, evse.CustomerId.ToString(), evse.ChargeBoxId, _pickDate.Hour, evse.HeartbeatUpdatedOn.Hour);
                            _picks = _picks.Where(x => x.OfflineTime == DefaultSetting.DefaultNullTime).ToList();

                            foreach (var item in _picks)
                            {
                                if (evse.HeartbeatUpdatedOn.Hour - item.OnlineTime.Hour > 0)
                                {
                                    item.OfflineTime = new DateTime(item.OnlineTime.AddHours(1).Year, item.OnlineTime.AddHours(1).Month, item.OnlineTime.AddHours(1).Day, item.OnlineTime.AddHours(1).Hour, 0, 0);
                                    item.TotalMinute = (int)item.OfflineTime.Subtract(item.OnlineTime).TotalMinutes;
                                    updateData.Add(item);

                                    var checkTime = item.OfflineTime;
                                    while (evse.HeartbeatUpdatedOn.Hour - checkTime.Hour >= 0)
                                    {
                                        insertData.Add(new EVSEOnlineRecord()
                                        {
                                            CustomerId = evse.CustomerId,
                                            StationId = "0",
                                            HourIndex = checkTime.Hour,
                                            ChargeBoxId = evse.ChargeBoxId,
                                            OnlineTime = checkTime,
                                            OfflineTime = evse.HeartbeatUpdatedOn.Hour == checkTime.Hour ? evse.HeartbeatUpdatedOn : new DateTime(checkTime.AddHours(1).Year, checkTime.AddHours(1).Month, checkTime.AddHours(1).Day, checkTime.AddHours(1).Hour, 0, 0)

                                        });
                                        checkTime = evse.HeartbeatUpdatedOn.Hour == checkTime.Hour ? insertData.LastOrDefault().OfflineTime.AddHours(1) : insertData.LastOrDefault().OfflineTime;
                                    }

                                }
                                else
                                {
                                    item.OfflineTime = evse.HeartbeatUpdatedOn;
                                    item.TotalMinute = (int)item.OfflineTime.Subtract(item.OnlineTime).TotalMinutes;
                                    updateData.Add(item);

                                }

                            }

                        }
                        else
                        {
                            //off-off do nothing
                        }
                    }

                    if ((insertData.Count + updateData.Count) % 100 == 0)
                    {
                        await UpdateOnlineRecords(updateData, insertData);
                        ClearCache();
                    }


                }


                await UpdateOnlineRecords(updateData, insertData);


            }
            catch (Exception ex)
            {
                logger.Debug("ERROR " + this.ToString() + ex.ToString());
            }



            logger.Debug(this.ToString() + " :Finished........");

        }


        private List<EVSECurrentStatus> GetEVSEs()
        {
            List<EVSECurrentStatus> result = new List<EVSECurrentStatus>();
            try
            {
                using (var dbConn = new SqlConnection(mainDBConnectString))
                {
                    dbConn.Open();
                    string sqlstring = "SELECT m.CustomerId,m.Id,m.ChargeBoxId,m.Online,m.HeartbeatUpdatedOn,MachineConfigurations.ConfigureSetting HeartbeatInterval"
                   + "  FROM [dbo].[Machine]  m,[dbo].[MachineConfigurations] where MachineConfigurations.ConfigureName = 'HeartbeatInterval' and m.ChargeBoxId = MachineConfigurations.ChargeBoxId";
                    result = dbConn.Query<EVSECurrentStatus>(sqlstring).ToList();
                }

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


            return result;
        }

        private void UpdateEVSECurrentStatus(string customerId, string ChargeBoxId, bool turnOn, DateTime offlineTime)
        {
            try
            {
                string sqlString = string.Format("UPDATE [dbo].[Machine] SET Online=@Online {0} WHERE chargeBoxId=@chargeBoxId and customerId=@customerId", turnOn ? "" : " ,OfflineOn=@OfflineOn");
                using (var dbConn = new SqlConnection(mainDBConnectString))
                {
                    dbConn.Open();
                    var parameters = new DynamicParameters();
                    parameters.Add("@Online", turnOn, System.Data.DbType.Boolean);
                    parameters.Add("@chargeBoxId", ChargeBoxId, System.Data.DbType.String);
                    parameters.Add("@customerId", customerId, System.Data.DbType.String);
                    if (!turnOn)
                    {
                        parameters.Add("@OfflineOn", offlineTime, System.Data.DbType.DateTime);
                    }



                    dbConn.Execute(sqlString, parameters);
                }

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


        async private Task UpdateOnlineRecords(List<EVSEOnlineRecord> updateItems, List<EVSEOnlineRecord> insertItems)
        {
            List<EVSEOnlineRecord> records = new List<EVSEOnlineRecord>();
            try
            {
                for (int i = 0; i < updateItems.Count; i++)
                {
                    string sqlString = string.Format("UPDATE [dbo].[EVSEOnlineRecord_{0}] SET OfflineTime=@OfflineTime , TotalMinute=@TotalMinute  WHERE customerId=@customerId and chargeBoxId=@chargeBoxId and " +
                       "OnlineTime=@OnlineTime", updateItems[i].OnlineTime.Date.ToString("yyMMdd"));
                    using (var dbConn = new SqlConnection(onlineDBConnectString))
                    {
                        dbConn.Open();
                        await dbConn.ExecuteAsync(sqlString, updateItems[i]);
                    }

                }

                for (int i = 0; i < insertItems.Count; i++)
                {
                    string sqlString = string.Format("INSERT INTO [dbo].[EVSEOnlineRecord_{0}] (\"CustomerId\",\"StationId\",\"ChargeBoxId\",\"HourIndex\",\"TotalMinute\",\"OnlineTime\",\"OfflineTime\")" +
                    "VALUES(@CustomerId,@StationId, @ChargeBoxId,@HourIndex,@TotalMinute,@OnlineTime,@OfflineTime); ", insertItems[i].OnlineTime.Date.ToString("yyMMdd"));
                    using (var dbConn = new SqlConnection(onlineDBConnectString))
                    {
                        dbConn.Open();
                        await dbConn.ExecuteAsync(sqlString, insertItems[i]);
                    }

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



        async private Task<List<EVSEOnlineRecord>> GetOnlineRecords(DateTime pickDate, string customerId, string chargeBoxId, int startHourCondition, int stopHourCondition)
        {
            List<EVSEOnlineRecord> records = new List<EVSEOnlineRecord>();
            try
            {
                string sqlString = string.Format("SELECT * FROM [dbo].[EVSEOnlineRecord_{0}] WHERE customerId=@customerId and chargeBoxId=@chargeBoxId and " +
                    "HourIndex >= @startHourCondition and HourIndex <= @stopHourCondition and OfflineTime=@OfflineTime", pickDate.ToString("yyMMdd"));
                using (var dbConn = new SqlConnection(onlineDBConnectString))
                {
                    dbConn.Open();
                    var parameters = new DynamicParameters();
                    parameters.Add("@customerId", new Guid(customerId), System.Data.DbType.Guid);
                    parameters.Add("@chargeBoxId", chargeBoxId, System.Data.DbType.String);
                    parameters.Add("@startHourCondition", startHourCondition, System.Data.DbType.Int32);
                    parameters.Add("@stopHourCondition", stopHourCondition, System.Data.DbType.Int32);
                    parameters.Add("@OfflineTime", DefaultSetting.DefaultNullTime, System.Data.DbType.Date);

                    var result = await dbConn.QueryAsync<EVSEOnlineRecord>(sqlString, parameters);
                    records = result.ToList();
                }

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

        private void CreateEVSEOnlineRecordTable()
        {
            try
            {
                string sqlString = string.Format("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME ='EVSEOnlineRecord_{0}'", latestHeartbeatTime.ToString("yyMMdd"));
                using (var dbConn = new SqlConnection(onlineDBConnectString))
                {
                    bool exists = dbConn.ExecuteScalar<bool>(sqlString);
                    if (!exists)
                    {
                        dbConn.Execute(string.Format(@"
                            CREATE TABLE [dbo].[EVSEOnlineRecord_{0}] (
                            [CustomerId] [UNIQUEIDENTIFIER] NOT NULL,
	                        [StationId] [nvarchar](36) NOT NULL,
	                        [ChargeBoxId] [nvarchar](36) NOT NULL,
	                        [HourIndex] [int] NOT NULL,
	                        [TotalMinute] [int] NOT NULL,
	                        [OnlineTime] [datetime] NOT NULL,
	                        [OfflineTime] [datetime] NOT NULL
                        ) ON [PRIMARY]
                            ", latestHeartbeatTime.ToString("yyMMdd")));
                    }
                }

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


        private void ClearCache()
        {
            if (updateData != null)
            {
                updateData.Clear();
            }

            if (insertData != null)
            {
                insertData.Clear();
            }
        }



        private bool IsOnlineNow(EVSECurrentStatus currentEVSE)
        {
            bool isOnline = false;
            int heartbeatInterval = 60;
            int.TryParse(currentEVSE.HeartbeatInterval, out heartbeatInterval);
            var checkTime = DateTime.UtcNow.AddSeconds(-(heartbeatInterval * 2));

            if (currentEVSE.HeartbeatUpdatedOn > checkTime)
            {
                isOnline = true;

            }

            return isOnline;

        }
    }
}