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(); }
private void btnSaveTest_Click(object sender, EventArgs e) { Cursor = Cursors.WaitCursor; lvEmails.Items.Clear(); int no = 0; if (saveRule()) { //check whether inbox is empty. If it's do the recent: connection to get lat 30days messages MySqlDataReader rdr = null; try { MySqlCommand cmd = CoreFeature.getInstance().getDataConnection().CreateCommand(); cmd.CommandText = "select name, server,port,use_ssl,username,password,active from account where active=1"; cmd.CommandType = CommandType.Text; rdr = cmd.ExecuteReader(); List<Account> listAccount = new List<Account>(); while (rdr.Read()) { listAccount.Add(new Account(rdr.GetString(rdr.GetOrdinal("name")), rdr.GetString(rdr.GetOrdinal("server")), rdr.GetInt32(rdr.GetOrdinal("port")), rdr.GetString(rdr.GetOrdinal("username")), Cryptho.Decrypt(rdr.GetString(rdr.GetOrdinal("password"))), rdr.GetByte(rdr.GetOrdinal("use_ssl")) == 1, rdr.GetByte(rdr.GetOrdinal("active")) == 1)); } rdr.Close(); foreach (Account emailAccount in listAccount) { //Application.DoEvents(); MySqlDataReader rdrInbox = null; MySqlCommand cmdInbox = CoreFeature.getInstance().getDataConnection().CreateCommand(); cmdInbox.CommandText = "SELECT count(*) FROM inbox i, account a where i.account_name = a.name and a.name='" + emailAccount.name + "'"; cmdInbox.CommandType = CommandType.Text; rdrInbox = cmdInbox.ExecuteReader(); if (rdrInbox.Read()) { if (rdrInbox.GetInt32(0) == 0) { //no messages in inbox, try to fetch all last 30 days message to test the rule rdrInbox.Dispose(); CoreFeature.getInstance().FetchRecentMessages(emailAccount,true); } else { //already done that, now only fetch new message rdrInbox.Dispose(); CoreFeature.getInstance().FetchRecentMessages(emailAccount, false); } } cmdInbox.Dispose(); SupportAlerterLibrary.model.Rule rule = new SupportAlerterLibrary.model.Rule (txtName.Text, txtBodyContains.Text, chkSmsAlert.Checked, chkVoiceCall.Checked, chkBody.Checked, chkSender.Checked, txtSenderContains.Text, chkSubject.Checked, txtSubjectContains.Text); 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 + "%' "; } string sql = "SELECT account_name,date,sender,subject FROM inbox where " + use_rules; cmdInbox = new MySqlCommand(sql, CoreFeature.getInstance().getDataConnection()); rdrInbox = cmdInbox.ExecuteReader(); string[] sub = new string[5]; while (rdrInbox.Read()) { ListViewItem item = new ListViewItem(); item.Text = "" + ++no; item.SubItems.Add(rdrInbox.GetString(rdrInbox.GetOrdinal("account_name"))); item.SubItems.Add(rdrInbox.GetString(rdrInbox.GetOrdinal("date"))); item.SubItems.Add(rdrInbox.GetString(rdrInbox.GetOrdinal("sender"))); item.SubItems.Add(rdrInbox.GetString(rdrInbox.GetOrdinal("subject"))); lvEmails.Items.Add(item); } lvEmails.Refresh(); rdrInbox.Close(); cmdInbox.Dispose(); } CoreFeature.getInstance().getDataConnection().Close(); } catch (Exception ex) { CoreFeature.getInstance().LogActivity(LogLevel.Debug, "This is my error " + ex.Message, EventLogEntryType.Information); rdr.Close(); CoreFeature.getInstance().getDataConnection().Close(); } } Cursor = Cursors.Default; }