TCC_OCPP16.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;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
using System.Web;

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();
        private string webConnectionString = ConfigurationManager.ConnectionStrings["WebDBContext"].ToString();
        public CheckEVSEOnlineJob()
        {
            CreateEVSEOnlineRecordTable();
        }
        //測試方式

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


        public async Task Execute(IJobExecutionContext context)
        {
            await Console.Out.WriteLineAsync(this.ToString() + " :Starting........");


            List<EVSECurrentStatus> _EVSEs = GetEVSEs();

            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 = GetOnlineRecords(_pickDate, evse.CustomerId.ToString(), evse.ChargeBoxId, isCrossDay ? 0 : _startHour, _pickDate.Hour);

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

                            _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,

                        });

                        await NotifyOnlineChanged(evse.CustomerId.ToString(), evse.Id.ToString(), evse.ChargeBoxId, true);
                    }
                }
                else
                {

                    if (evse.Online)
                    {

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

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

                        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);

                            }

                        }

                        await NotifyOnlineChanged(evse.CustomerId.ToString(), evse.Id.ToString(), evse.ChargeBoxId, false);
                    }
                    else
                    {
                        //off-off do nothing
                    }
                }

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


            }


            UpdateOnlineRecords(updateData, insertData);


            await Console.Out.WriteLineAsync(this.ToString() + " :Finished........");

        }

        async private Task NotifyOnlineChanged(string customerId, string machineId, string chargeBoxId, bool turnOn)
        {
            string stationName = await GetStationName(machineId, chargeBoxId);
            string errorMsg = string.Empty;
          
            List<string> sendlist = ConfigurationManager.AppSettings["OnlineChanged_Receivers"].Split(',').ToList();
            foreach (var item in sendlist)
            {
                string message = string.Format("[{0}{1}]\r\n{2} \r\n時間:{3}\r\n ", stationName, turnOn ? "電樁上線通知" : "電樁斷線通知",chargeBoxId,
                    DateTime.UtcNow.AddHours(8).ToString("yyyy/MM/dd HH:mm:ss") );

                SendMessage(item, message, out errorMsg);
            }


        }

        private int SendMessage(string phoneNumber, string message, out string errorMsg)
        {
            int result = 0;
            try
            {
                errorMsg = string.Empty;
                HttpClient client = new HttpClient();
                StringBuilder url = new StringBuilder("http://smexpress.mitake.com.tw:7002/SpSendUtf?");
                url.Append("username=").Append(HttpUtility.UrlEncode("83196607SMS",
                    Encoding.UTF8));
                url.Append("&password=").Append(HttpUtility.UrlEncode("83196607SMS@nhoa.tcc",
                    Encoding.UTF8));
                url.Append("&encoding=UTF8");
                url.Append("&dstaddr=").Append(phoneNumber);
                url.Append("&smbody=").Append(HttpUtility.UrlEncode(message,
                    Encoding.UTF8).Replace("+", "%20"));
                url.Append("&CharsetURL=").Append("utf-8");

                Console.WriteLine(url.ToString());
                var id = client.GetStringAsync(url.ToString()).Result;

                if (!SendResult(id))
                {
                    result = -1;
                    errorMsg = id;
                }
            }
            catch (Exception ex)
            {
                result = -1;
                errorMsg = ex.Message;
            }

            return result;
        }

        private bool SendResult(string response)
        {
            bool result = false;

            if (response.Contains("msgid="))
            {
                result = true;

            }

            return result;
        }

        async private Task<string> GetStationName(string machineId, string chargeBoxId)
        {
            string stationName = string.Empty;
            try
            {
                var parameters = new DynamicParameters();
                parameters.Add("@MachineId", machineId, DbType.String, ParameterDirection.Input);

                string strSql = "SELECT  [Name] as StationName FROM[StationMachine]  left join[dbo].[Station]" +
                    "  on[StationMachine].StationId = Station.[Id]  where StationMachine.MachineId=@MachineId ; ";
                using (SqlConnection conn = new SqlConnection(webConnectionString))
                {
                    var result = await conn.QueryAsync<string>(strSql, parameters);
                    stationName = result.FirstOrDefault();
                }

            }
            catch (Exception ex)
            {

            }

            return stationName;
        }


        private List<EVSECurrentStatus> GetEVSEs()
        {
            List<EVSECurrentStatus> result = new List<EVSECurrentStatus>();
            try
            {
                using (var dbConn = new SqlConnection(mainDBConnectString))
                {
                    dbConn.Open();
                    result = dbConn.Query<EVSECurrentStatus>("SELECT CustomerId,Id,ChargeBoxId,Online,HeartbeatUpdatedOn FROM [dbo].[Machine]").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());
            }
        }


        private void 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();
                        dbConn.Execute(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();
                        dbConn.Execute(sqlString, insertItems[i]);
                    }

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



        private 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);

                    records = dbConn.Query<EVSEOnlineRecord>(sqlString, parameters).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;
            var checkTime = DateTime.Now.AddSeconds(-120);

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

        }
    }
}