private void timer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
timer.Stop();
string sql;
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Begin timed activity : Logging to email account and processing the rules", EventLogEntryType.Information);
MySqlConnection dataConnection = null;
try
{
//1. LOOP ALL EMAIL ACCOUNTS
dataConnection = CoreFeature.getInstance().getDataConnection();
sql = "select name, server,port,use_ssl,username,password,active from account where active=1";
MySqlCommand cmdAccount = new MySqlCommand(sql, dataConnection);
MySqlDataReader rdrAccount = cmdAccount.ExecuteReader();
List<Account> listAccount = new List<Account>();
while (rdrAccount.Read())
{
Account emailAccount = new Account(rdrAccount.GetString(rdrAccount.GetOrdinal("name")), rdrAccount.GetString(rdrAccount.GetOrdinal("server")), rdrAccount.GetInt32(rdrAccount.GetOrdinal("port")), rdrAccount.GetString(rdrAccount.GetOrdinal("username")), Cryptho.Decrypt(rdrAccount.GetString(rdrAccount.GetOrdinal("password"))), rdrAccount.GetByte(rdrAccount.GetOrdinal("use_ssl")) == 1, rdrAccount.GetByte(rdrAccount.GetOrdinal("active")) == 1);
listAccount.Add(emailAccount);
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Will use active email " + emailAccount.name, EventLogEntryType.Information, emailAccount.name);
}
rdrAccount.Close();
//2. LOOP ALL NEW MESSAGES
foreach (Account emailAccount in listAccount)
{
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Logging in to email account " + emailAccount.name, EventLogEntryType.Information,emailAccount.name);
MySqlDataReader rdrInbox = null;
sql = "SELECT count(*) FROM inbox i, account a where i.account_name = a.name and a.name=@name";
MySqlCommand cmdInbox = new MySqlCommand(sql, dataConnection);
cmdInbox.Parameters.AddWithValue("name", emailAccount.name);
rdrInbox = cmdInbox.ExecuteReader();
if (rdrInbox.Read())
{
if (rdrInbox.GetInt32(0) == 0)
{
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "No messages in inbox, try to fetch all last 30 days message to test the rule", EventLogEntryType.Information,emailAccount.name);
rdrInbox.Dispose();
CoreFeature.getInstance().FetchRecentMessages(emailAccount, true);
}
else
{
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Inbox already consisted of previous fetched message, now only fetch new message", EventLogEntryType.Information, emailAccount.name);
rdrInbox.Dispose();
CoreFeature.getInstance().FetchRecentMessages(emailAccount, false);
}
}
rdrInbox.Close();
cmdInbox.Dispose();
//3. APPLY THE RULES
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "About to apply notification rules", EventLogEntryType.Information, emailAccount.name);
MySqlCommand cmdRule;
MySqlDataReader rdrRule;
string sqlRule = "SELECT * FROM rule r";
cmdRule = new MySqlCommand(sqlRule, dataConnection);
rdrRule = cmdRule.ExecuteReader();
List<SupportAlerterLibrary.model.Rule> listRule = new List<SupportAlerterLibrary.model.Rule>();
while (rdrRule.Read())
{
SupportAlerterLibrary.model.Rule rule =
new SupportAlerterLibrary.model.Rule(
rdrRule.GetString(rdrRule.GetOrdinal("name")),
rdrRule.GetString(rdrRule.GetOrdinal("contains")),
rdrRule.GetByte(rdrRule.GetOrdinal("send_sms")) == 1,
rdrRule.GetByte(rdrRule.GetOrdinal("voice_call")) == 1,
rdrRule.GetByte(rdrRule.GetOrdinal("use_body")) == 1,
rdrRule.GetByte(rdrRule.GetOrdinal("use_sender")) == 1,
rdrRule.GetString(rdrRule.GetOrdinal("sender_contains")),
rdrRule.GetByte(rdrRule.GetOrdinal("use_subject")) == 1,
rdrRule.GetString(rdrRule.GetOrdinal("subject_contains"))
);
listRule.Add(rule);
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Will use the rule " + rule.name + ". Use body=" + rule.use_body + ", body contains " + rule.contains + ". Use sender=" + rule.use_sender + ", sender contains=" + rule.sender_contains + ". Use subject=" + rule.use_subject + ", subject contains=" + rule.subject_contains + ". Send sms = " + rule.send_sms + ". Voice call = " + rule.voice_call, EventLogEntryType.Information, emailAccount.name);
}
rdrRule.Close();
cmdRule.Dispose();
foreach (SupportAlerterLibrary.model.Rule rule in listRule)
{
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Using the rule " + rule.name, EventLogEntryType.Information, emailAccount.name);
string use_rules = "";
if (rule.use_body)
{
use_rules = " body like '%" + rule.contains + "%' ";
}
if (rule.use_sender)
{
if(use_rules.Equals(""))
use_rules += " sender like '%" + rule.sender_contains + "%' ";
else
use_rules += " and sender like '%" + rule.sender_contains + "%' ";
}
if (rule.use_subject)
{
if (use_rules.Equals(""))
use_rules += " subject like '%" + rule.subject_contains + "%' ";
else
use_rules += " and subject like '%" + rule.subject_contains + "%' ";
}
sql = "SELECT idinbox, account_name,date,sender,subject FROM inbox where " + use_rules + " and handled=0";
cmdInbox = new MySqlCommand(sql, dataConnection);
rdrInbox = cmdInbox.ExecuteReader();
List<Inbox> listInbox = new List<Inbox>();
while (rdrInbox.Read())
{
Inbox inbox = new Inbox(rdrInbox.GetInt32(rdrInbox.GetOrdinal("idinbox")), rdrInbox.GetString(rdrInbox.GetOrdinal("subject")));
listInbox.Add(inbox);
if (RegistrySettings.loggingLevel.Equals("Normal"))
{
CoreFeature.getInstance().LogActivity(LogLevel.Normal, "Email information : Date=" + inbox.date + ", Sender=" + inbox.sender + ", Subject=" + inbox.subject, EventLogEntryType.Information, emailAccount.name);
}
else if (RegistrySettings.loggingLevel.Equals("Debug"))
{
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Email information : Date=" + inbox.date + ", Sender=" + inbox.sender + ", Subject=" + inbox.subject + ", Body = " + inbox.body, EventLogEntryType.Information, emailAccount.name);
}
}
rdrInbox.Close();
cmdInbox.Dispose();
foreach(Inbox inbox in listInbox)
{
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Acted upon the rule " + rule.name + " of message " + inbox.subject, EventLogEntryType.Information, emailAccount.name);
if (rule.send_sms)
{
string smsContent = "You have warning notification from SENDER";
MySqlCommand cmdSend = new MySqlCommand("insert into send_sms(idinbox,content,status,account_name) values(" + inbox.idinbox + ",'" + smsContent + "','Draft','" + emailAccount.name + "')", dataConnection);
cmdSend.ExecuteNonQuery();
CoreFeature.getInstance().LogActivity(LogLevel.Normal, "Inserting into SMS table. Content : " + smsContent, EventLogEntryType.Information, emailAccount.name);
}
if (rule.voice_call)
{
MySqlCommand cmdSend = new MySqlCommand("insert into voice_call(idinbox,status, account_name) values(" + inbox.idinbox + ",'Draft','" + emailAccount.name + "')", dataConnection);
cmdSend.ExecuteNonQuery();
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Inserting into Voice_call table ", EventLogEntryType.Information, emailAccount.name);
}
MySqlCommand cmdUpdateInbox = new MySqlCommand("update inbox set handled=1 where idinbox=" + inbox.idinbox, dataConnection);
cmdUpdateInbox.ExecuteNonQuery();
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Set HANDLED=1 for the inbox entry ID = " + inbox.idinbox, EventLogEntryType.Information, emailAccount.name);
}
}
rdrRule.Close();
cmdRule.Dispose();
}
//4. PROCESS, IF ANY, VOICE_CALL / SMS THAT NEED TO BE DELIVERED
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Processing of SMS/Voice Call", EventLogEntryType.Information);
MySqlCommand cmdSms = new MySqlCommand("select idsend_sms, content,account_name from send_sms where status='Draft'", dataConnection);
MySqlDataReader rdrSms = cmdSms.ExecuteReader();
List<SendSms> listSendSms = new List<SendSms>();
while (rdrSms.Read())
{
listSendSms.Add(new SendSms(rdrSms.GetInt32(rdrSms.GetOrdinal("idsend_sms")), rdrSms.GetString(rdrSms.GetOrdinal("content")), rdrSms.GetString(rdrSms.GetOrdinal("account_name"))));
}
rdrSms.Close();
cmdSms.Dispose();
foreach(SendSms sendSms in listSendSms)
{
SmsGateway.getInstance().processSmsNotification(sendSms);//once processed, the status will change into DELIVERED/FAILED
CoreFeature.getInstance().LogActivity(LogLevel.Normal, "Sending SMS. Content : '" + sendSms.content + "'", EventLogEntryType.Information, sendSms.account_name);
}
MySqlCommand cmdVoiceCall = new MySqlCommand("select idvoice_call,status,account_name from voice_call where status='Draft'", dataConnection);
MySqlDataReader rdrVoiceCall = cmdVoiceCall.ExecuteReader();
List<VoiceCall> listVoiceCall = new List<VoiceCall>();
while (rdrVoiceCall.Read())
{
listVoiceCall.Add(new VoiceCall(rdrVoiceCall.GetInt32(rdrVoiceCall.GetOrdinal("idvoice_call")), rdrVoiceCall.GetString(rdrVoiceCall.GetOrdinal("status")), rdrVoiceCall.GetString(rdrVoiceCall.GetOrdinal("account_name"))));
}
rdrVoiceCall.Close();
cmdVoiceCall.Dispose();
foreach (VoiceCall voiceCall in listVoiceCall)
{
SmsGateway.getInstance().processCallNotification(voiceCall);//once processed, the status will change into DELIVERED/FAILED
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Voice call for voiceCall.id=" + voiceCall.idvoice_call, EventLogEntryType.Information, voiceCall.account_name);
}
CoreFeature.getInstance().getDataConnection().Close();
}
catch (Exception ex)
{
CoreFeature.getInstance().LogActivity(LogLevel.Normal, "[Internal Application Error] " + ex.Message, EventLogEntryType.Error);
CoreFeature.getInstance().getDataConnection().Close();
}
dataConnection.Close();
timer.Start();
}
//focusing first on gMail account using recent: in username
public void FetchRecentMessages(Account emailAccount, bool isFetchLast30days)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
string emailUsername = null;
if (isFetchLast30days)
{
emailUsername = "******" + emailAccount.username;
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Fetching *last 30 days* message", EventLogEntryType.Information, emailAccount.name);
}
else
{
emailUsername = emailAccount.username;
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Fetching *new* message", EventLogEntryType.Information, emailAccount.name);
}
if (SupportAlerterLibrary.CoreFeature.getInstance().Connect(emailAccount.name, emailAccount.server, emailAccount.port, emailAccount.use_ssl, emailUsername, emailAccount.password))
{
int count = SupportAlerterLibrary.CoreFeature.getInstance().getPop3Client().GetMessageCount();
for (int i = 1; i <= count; i++)
{
//Regards to : http://hpop.sourceforge.net/exampleSpecificParts.php
OpenPop.Mime.Message message = SupportAlerterLibrary.CoreFeature.getInstance().getPop3Client().GetMessage(i);
MessagePart messagePart = message.FindFirstPlainTextVersion();
if (messagePart == null) messagePart = message.FindFirstHtmlVersion();
string messageBody = null;
if (messagePart != null) messageBody = messagePart.GetBodyAsText();
messageBody = Regex.Replace(messageBody, "<.*?>", string.Empty);
//save to appropriate inbox
connection = CoreFeature.getInstance().getDataConnection();
string sql = "insert into inbox(account_name,sender,subject,body,date, handled) values (@account_name,@sender,@subject,@body,@date, 0)";
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@account_name", emailAccount.name);
cmd.Parameters.AddWithValue("@sender", message.Headers.From);
cmd.Parameters.AddWithValue("@subject", message.Headers.Subject);
cmd.Parameters.AddWithValue("@body", messageBody);
cmd.Parameters.AddWithValue("@date", message.Headers.Date);
try
{
int rowAffected = cmd.ExecuteNonQuery();
CoreFeature.getInstance().LogActivity(LogLevel.Normal, "Inserting email to inbox table. Sender : " + message.Headers.From + "\nSubject : " + message.Headers.Subject + "\nBody : " + messageBody, EventLogEntryType.Information, emailAccount.name);
}
catch (Exception ex)
{
CoreFeature.getInstance().LogActivity(LogLevel.Normal, "[Internal Application Error] FetchRecentMessages " + ex.Message, EventLogEntryType.Information);
}
cmd.Dispose();
connection.Close();
}
}
else
{
CoreFeature.getInstance().LogActivity(LogLevel.Debug, "Unable to login to your email", EventLogEntryType.Information);
}
}