AlertWorker.cs

using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Data.SqlClient;
using System.Diagnostics;
using System.Net;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Linq;
using System.IO;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;

namespace MailAlertServer {
    public class AlertWorker : BackgroundService {
        private readonly ILogger<AlertWorker> _logger;
        private string connString;
        private int alertCheckInterval;

        public AlertWorker (ILogger<AlertWorker> logger) {
            _logger = logger;
            LoadConfiguration ();
        }

        private void LoadConfiguration () {
            string configPath = Path.Combine (AppContext.BaseDirectory, "config.txt");
            if (File.Exists (configPath)) {
                string[] lines = File.ReadAllLines (configPath);
                connString = string.Format (
                    "Data Source={0};Initial Catalog={1};User Id={2};Password={3};TrustServerCertificate={4}",
                    getSettingFromArr ("Server", "localhost", lines),
                    getSettingFromArr ("Database", "MailAlertDB", lines),
                    getSettingFromArr ("Username", "dbuser", lines),
                    getSettingFromArr ("Password", "dbpassword", lines),
                    getSettingFromArr ("TrustServerCertificate", "True", lines)
                );
                int value;
                alertCheckInterval = Int32.TryParse (getSettingFromArr ("Interval", "60", lines), out value) ? value * 1000 : 60000;
            } else {
                connString = "Data Source=localhost;Initial Catalog=MailAlertDB;User Id=dbuser;Password=dbpassword;TrustServerCertificate=True";
                alertCheckInterval = 60000;
                _logger.LogWarning ("Config file not found at {Path}. Using defaults.", configPath);
            }
        }

        private string getSettingFromArr (string name, string def, string[] arr) {
            foreach (string line in arr) {
                string[] valuePair = line.Split ("=", 2);
                if (valuePair.Length == 2 && valuePair[0].Trim().ToLower () == name.ToLower ()) {
                    return valuePair[1].Trim();
                }
            }
            return def;
        }

        protected override async Task ExecuteAsync (CancellationToken stoppingToken) {
            _logger.LogInformation ("MailAlertServer started.");
            while (!stoppingToken.IsCancellationRequested) {
                try {
                    CheckAlerts ();
                } catch (Exception e) {
                    _logger.LogError (e, "Unexpected error during alert check.");
                }
                await Task.Delay (alertCheckInterval, stoppingToken);
            }
        }

        private void CheckAlerts () {
            // Record fields.
            int id = 0;
            string query = "";
            string subject = "";
            string message = "";
            string messageQuery = "";
            string recipients = "";
            string recipientsBCC = "";
            string recipientsCC = "";
            bool lastTrue = false;
            DateTime lastTrueTime = DateTime.Now;
            int minutesResend = 0;
            // Alert Status Bits.
            bool noAlertsEnabled = true;
            bool noAlertsToSend = true;
            bool sendMail = false;

            // Make a connection to the database.
            using (SqlConnection conn = new SqlConnection (connString)) {
                // Query the enabled MailAlerts.
                string sqlQuery = @"SELECT
                        ID,
                        Query,
                        Subject,
                        Message,
                        MessageQuery,
                        Recipients,
                        RecipientsCC,
                        RecipientsBCC,
                        LastTrue,
                        LastTrueTime,
                        MinutesResend
                     FROM MailAlerts WHERE Enabled = 1";
                conn.Open ();
                SqlCommand comm = new SqlCommand (sqlQuery, conn);
                SqlDataReader reader = comm.ExecuteReader ();
                // Call Read before accessing data.
                while (reader.Read ()) {
                    DateTime testTime;
                    int testInt;
                    id = Convert.ToInt32 (reader["ID"].ToString ());
                    subject = reader["Subject"].ToString ();
                    message = reader["Message"].ToString ();
                    messageQuery = reader["MessageQuery"].ToString ();
                    recipients = reader["Recipients"].ToString ();
                    recipientsCC = reader["RecipientsCC"].ToString ();
                    recipientsBCC = reader["RecipientsBCC"].ToString ();
                    query = reader["Query"].ToString ();
                    lastTrue = reader["LastTrue"].ToString () == "True";
                    lastTrueTime = DateTime.TryParse (reader["LastTrueTime"].ToString (), out testTime) ? testTime : DateTime.Now;
                    minutesResend = Int32.TryParse (reader["MinutesResend"].ToString (), out testInt) ? testInt : 0;
                    noAlertsEnabled = false;

                    try {
                        // Run the alert query.
                        sendMail = ExecAlertQuery (query);
                        SetLastCheckTime (id);

                        // If the query result contains a field called SendMail that is equal to 1 then
                        // insert a record into MailRequests containing the email to send.
                        if (sendMail) {
                            if (!lastTrue) {
                                SetAlert (id);
                                _logger.LogInformation ("Sending request for {Subject}.", subject);
                                if (messageQuery != "") {
                                    // Execute the messageQuery and encode the results into the message.
                                    message = EncodeMessageQuery (messageQuery, message);
                                }
                                AddRequest (recipients, recipientsCC, recipientsBCC, subject, message);
                                noAlertsToSend = false;
                            } else {
                                int minutesPassed = Convert.ToInt32 (DateTime.Now.Subtract (lastTrueTime).TotalMinutes);
                                if (minutesPassed >= minutesResend && minutesResend != 0) {
                                    SetAlert (id);
                                    _logger.LogInformation ("Sending request for {Subject}.", subject);
                                    if (messageQuery != "") {
                                        // Execute the messageQuery and encode the results into the message.
                                        message = EncodeMessageQuery (messageQuery, message);
                                    }
                                    AddRequest (recipients, recipientsCC, recipientsBCC, subject, message);
                                    noAlertsToSend = false;
                                }
                            }
                        } else {
                            if (lastTrue) {
                                _logger.LogInformation ("Clearing alert for {Subject}.", subject);
                                ResetAlert (id);
                            }
                        }
                        SetLastStatus ("OK", id);
                    }
                    catch (Exception e) {
                        _logger.LogError ("Error occurred: {Message}", e.Message);
                        SetLastStatus (e.Message, id);
                    }
                }

                // Dispose of the command so we can reuse it.
                reader.Close ();
                reader.Dispose ();
                comm.Dispose ();

                if (noAlertsEnabled) {
                    _logger.LogInformation ("No alerts enabled.");
                } else if (noAlertsToSend) {
                    _logger.LogInformation ("No alerts to send.");
                }
            };
        }

        private bool ExecAlertQuery (string sqlQuery) {
            bool sendMail = false;
            // Make a connection to the database.
            using (SqlConnection conn = new SqlConnection (connString)) {
                conn.Open ();
                SqlCommand comm = new SqlCommand (sqlQuery, conn);
                SqlDataReader reader = comm.ExecuteReader ();

                while (reader.Read ()) {
                    sendMail = reader["sendMail"].ToString () == "1";
                }

                // Dispose of the command so we can reuse it.
                reader.Close ();
                reader.Dispose ();
                comm.Dispose ();
            }

            return sendMail;
        }

        private void AddRequest (string recipients, string recipientsCC, string recipientsBCC, string subject, string message) {
            int maxMessageSize = 100000;

            // Fix the message carriage returns.
            if (message.Length > 0) {
                message = message.Replace ("\r\n", "[CRLF]");
                message = message.Replace ("\r", "[CRLF]");
                message = message.Replace ("\n", "[CRLF]");
            }
            using (SqlConnection conn = new SqlConnection (connString)) {
                conn.Open ();
                if (message.Length > maxMessageSize) {
                    message = message.Substring (0, maxMessageSize - 1);
                }
                string sqlInsertRequest = String.Format (@"INSERT INTO MailRequests (RecipientTo, RecipientCC, RecipientBCC, Subject, Message)
                    VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')", recipients, recipientsCC, recipientsBCC, subject, message);
                SqlCommand comm = new SqlCommand (sqlInsertRequest, conn);
                comm.ExecuteNonQuery ();

                // Close the connection and clean up.
                comm.Dispose ();
                conn.Close ();
                conn.Dispose ();
            }
        }

        private void ResetAlert (int ID) {
            // Set lastTrue to false.  This will cause the alarm to resend the next time it is true.
            using (SqlConnection conn = new SqlConnection (connString)) {
                conn.Open ();
                string sqlReset = String.Format (@"UPDATE MailAlerts
                    SET lastTrue = 0
                    WHERE ID = {0}", ID);

                SqlCommand comm = new SqlCommand (sqlReset, conn);
                comm.ExecuteNonQuery ();

                // Close the connection and clean up.
                comm.Dispose ();
                conn.Close ();
                conn.Dispose ();
            }
        }

        private void SetAlert (int ID) {
            // Set lastTrue to true and register now as the lastTrueTime.
            // This will prevent the alert from sending again the next time it is tested.
            using (SqlConnection conn = new SqlConnection (connString)) {
                conn.Open ();
                string sqlReset = String.Format (@"UPDATE MailAlerts
                    SET lastTrue = 1, lastTrueTime = getdate()
                    WHERE ID = {0}", ID);

                SqlCommand comm = new SqlCommand (sqlReset, conn);
                comm.ExecuteNonQuery ();

                // Close the connection and clean up.
                comm.Dispose ();
                conn.Close ();
                conn.Dispose ();
            }
        }

        private void SetLastCheckTime (int ID) {
            using (SqlConnection conn = new SqlConnection (connString)) {
                conn.Open ();
                string sqlSetLastCheckTime = String.Format (@"UPDATE MailAlerts
                    SET lastCheckTime = getdate()
                    WHERE ID = {0}", ID);

                SqlCommand comm = new SqlCommand (sqlSetLastCheckTime, conn);
                comm.ExecuteNonQuery ();

                // Close the connection and clean up.
                comm.Dispose ();
                conn.Close ();
                conn.Dispose ();
            }
        }

        private void SetLastStatus (string status, int ID) {
            using (SqlConnection conn = new SqlConnection (connString)) {
                status = status.Replace ("'", "");
                conn.Open ();
                string sqlSetLastStatus = String.Format (@"UPDATE MailAlerts
                    SET LastStatus = '{0}'
                    WHERE ID = {1}", status, ID);
                SqlCommand comm = new SqlCommand (sqlSetLastStatus, conn);
                comm.ExecuteNonQuery ();

                // Close the connection and clean up.
                comm.Dispose ();
                conn.Close ();
                conn.Dispose ();
            }
        }

        private string EncodeMessageQuery (string msgQ, string msg) {
            // Query for a dataTable using msgQ and then merge this with msg to create the final message.
            string encodedMsg = "";
            string token = "%%";
            int maxTableSize = 100;
            bool maxExceeded = false;

            using (SqlConnection conn = new SqlConnection (connString)) {
                conn.Open ();

                SqlCommand comm = new SqlCommand (msgQ, conn);
                SqlDataReader reader = comm.ExecuteReader ();
                DataTable msgDt = new DataTable ();
                msgDt.Load (reader);
                string[] parseMsg = msg.Split (token);

                // Does the message have a table defined in it?  The placeholder will look like this:
                // %%TABLE:col1, col2, col3, col4...%%
                // %%TABLE%%
                if (msg.Contains (token + "TABLE")) {
                    string[] cols;

                    DataTable dtToRender = msgDt.Copy ();
                    // Reduce the number of rows if the max is exceeded.
                    maxExceeded = dtToRender.Rows.Count > maxTableSize;
                    if (maxExceeded) {
                        for (int i = maxTableSize; i < dtToRender.Rows.Count; i++) {
                            dtToRender.Rows.RemoveAt (i);
                        }
                    }
                    for (int i = 0; i < parseMsg.Length; i++) {
                        if (parseMsg[i].StartsWith ("TABLE")) {
                            // Does the table definition contain column definitions?
                            if (parseMsg[i].StartsWith ("TABLE:")) {
                                parseMsg[i] = parseMsg[i].Substring (6);
                                cols = parseMsg[i].Split (",");
                                foreach (DataColumn dc in msgDt.Columns) {
                                    if (!cols.Contains (dc.ColumnName)) {
                                        dtToRender.Columns.Remove (dc.ColumnName);
                                    }
                                }
                            }
                            if (msgDt.Rows.Count > 0) {
                                parseMsg[i] = ExportDatatableToHtml (dtToRender);
                            }
                            else {
                                parseMsg[i] = "No Records Found";
                            }
                        }
                    }
                }

                // Does the message have any single values defined in it?
                // e.g. The pressure was %%ChambMicrons_Val%% at %%DateTime%%
                for (int i = 0; i < parseMsg.Length; i++) {
                    if (msgDt.Columns.Contains (parseMsg[i])) {
                        parseMsg[i] = msgDt.Rows[0][parseMsg[i]].ToString ();
                    }
                }

                // Put the message back together without the tokens to produce the final message.
                encodedMsg = String.Join ("", parseMsg).Replace ("\n", "[CRLF]");
                encodedMsg = encodedMsg.Replace ("\r", "");
                encodedMsg = maxExceeded ? encodedMsg + string.Format ("[CRLF]Number of Rows Exceeds {0}", maxTableSize) : encodedMsg;

                // Close the connection and clean up.
                comm.Dispose ();
                conn.Close ();
                conn.Dispose ();
            }

            return encodedMsg;
        }

        // Code snippet taken from:
        // https://www.c-sharpcorner.com/UploadFile/deveshomar/export-datatable-to-html-in-C-Sharp/
        private string ExportDatatableToHtml (DataTable dt) {
            StringBuilder strHTMLBuilder = new StringBuilder ();
            strHTMLBuilder.Append ("<html >");
            strHTMLBuilder.Append ("<head>");
            strHTMLBuilder.Append ("</head>");
            strHTMLBuilder.Append ("<body>");
            strHTMLBuilder.Append ("<table width=\"100%\" ");
            strHTMLBuilder.Append ("border=\"1px\" ");
            strHTMLBuilder.Append ("cellpadding=\"5\" ");
            strHTMLBuilder.Append ("cellspacing=\"0\" ");
            strHTMLBuilder.Append ("bgcolor=\"lightyellow\" ");
            strHTMLBuilder.Append ("style=\"font-family:arial,helvetica,sans-serif;\">");

            strHTMLBuilder.Append ("<tr >");
            foreach (DataColumn myColumn in dt.Columns) {
                strHTMLBuilder.Append ("<td style=\"background-color:cyan;font-weight:bold;\">");
                strHTMLBuilder.Append (myColumn.ColumnName);
                strHTMLBuilder.Append ("</td>");
            }
            strHTMLBuilder.Append ("</tr>");

            foreach (DataRow myRow in dt.Rows) {

                strHTMLBuilder.Append ("<tr >");
                foreach (DataColumn myColumn in dt.Columns) {
                    strHTMLBuilder.Append ("<td style=\"background-color:#fbfbe3;\">");
                    strHTMLBuilder.Append (myRow[myColumn.ColumnName].ToString ());
                    strHTMLBuilder.Append ("</td>");
                }
                strHTMLBuilder.Append ("</tr>");
            }

            //Close tags.
            strHTMLBuilder.Append ("</table>");
            strHTMLBuilder.Append ("</body>");
            strHTMLBuilder.Append ("</html>");

            string Htmltext = strHTMLBuilder.ToString ();

            return Htmltext;

        }
    }
}