public void GetMail() { timer.Enabled = false; Dictionary <String, String[]> outboundMails = GetMessages(); String updateQuery = "UPDATE {0} SET status = '{1}' WHERE id = {2}"; String status; popMessages = FetchAllMessages(hostName, port, useSSL, username, password); mailMessages = new List <MailMessage>(); if (popMessages.Count > 0) { if (messages == null) { messages = new Dictionary <string, EmailConstruct>(); } foreach (Message m in popMessages) { try { if (!messages.ContainsKey(m.Headers.MessageId)) { if (NewMessageEvent != null) { NewMessageEvent(this, new MessageArgs("Event Fired: MailPopped")); } MailMessage mm = m.ToMailMessage(); String body = mm.Body; String subject = mm.Subject; bool handled = false; foreach (KeyValuePair <String, String[]> kvp in outboundMails) { if (subject.Contains(kvp.Key)) { String statusQuery = String.Format(updateQuery, kvp.Value[1], subject.Replace(kvp.Key, ""), kvp.Value[0]); DataHandler.setData(statusQuery, out status); handled = true; break; } } if (handled) { outboundMails = GetMessages(); } } } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } } //for (int i = messages.Count; i > 0; i--) { // DeleteMessageOnServer(hostName, port, useSSL, username, password, i); //} } timer.Enabled = true; }
private void btnDelete_Click(object sender, EventArgs e) { var sql = "exec usp_ExportStatementDelete " + numFrom.Value.ToString() + " , " + numTo.Value.ToString(); string stats; DataHandler.setData(sql, out stats); MessageBox.Show("Delete executed : " + stats); Close(); }
private void SetStartStatements() { currentDate = DateTime.Now; if (!GetStmtStatus()) { String query = "UPDATE tblRunConfig SET stmtRunStatus = 'True'"; String msg = ""; DataHandler.setData(query, out msg); RaiseEvent("Starting statements..."); } else { RaiseEvent("Statements already running!"); } statusTimer.Start(); }
private void SaveMail(String building, String customer, String message, String sender) { String msg = ""; Dictionary <String, Object> sqlParms = new Dictionary <string, object>(); sqlParms.Add("@building", building); sqlParms.Add("@customer", customer); sqlParms.Add("@message", message); sqlParms.Add("@reference", ""); sqlParms.Add("@sent", DateTime.Now); sqlParms.Add("@sender", sender); String insertQuery = "INSERT INTO tblSMS(building, customer, number, reference, message, billable, bulkbillable, sent, sender, astStatus, batchID, status, nextPolled, pollCount)"; insertQuery += " VALUES(@building, @customer, @number, @reference, @message, 'False', 'False', @sent, @sender, '1', '', 'Received', @sent, 0)"; DataHandler.setData(insertQuery, sqlParms, out msg); }
private void SaveMail(ref EmailConstruct m) { String msg = ""; //first get all references String checkNumber = m.SentFrom; if (m.SentFrom.StartsWith("27")) { checkNumber = m.SentFrom.Replace("27", "0"); } String refQuery = "SELECT distinct building, customer FROM tblSMS where reference = @reference OR number = @actNumber OR number = @checkNumber"; Dictionary <String, Object> sqlParms = new Dictionary <string, object>(); sqlParms.Add("@building", ""); sqlParms.Add("@customer", ""); sqlParms.Add("@message", m.Body); if (String.IsNullOrEmpty(m.Reference)) { m.Reference = GetReference(checkNumber, m.SentFrom); } sqlParms.Add("@reference", m.Reference); sqlParms.Add("@sent", m.ReceivedDate); sqlParms.Add("@direction", true); sqlParms.Add("@sender", m.SentFrom); sqlParms.Add("@actNumber", m.SentFrom); sqlParms.Add("@checkNumber", checkNumber); DataSet ds = DataHandler.getData(refQuery, sqlParms, out msg); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { sqlParms["@building"] = ds.Tables[0].Rows[0]["building"].ToString(); sqlParms["@customer"] = ds.Tables[0].Rows[0]["customer"].ToString(); sqlParms["@reference"] = m.Reference; sqlParms["@message"] = m.Body; String insertQuery = "INSERT INTO tblSMS(building, customer, number, message, reference, sent, sender)"; insertQuery += " VALUES(@building, @customer, @sender, @message, @reference, @sent, @sender)"; DataHandler.setData(insertQuery, sqlParms, out msg); } }
public void SendBulkMails(bool flagQueued = false, bool queued = false) { String mailQuery = "SELECT msg.id, msg.fromAddress, b.Code, b.DataPath, msg.incBCC, msg.bccAddy, msg.subject, msg.message, msg.billBuilding, msg.billAmount FROM tblMsg AS msg "; mailQuery += " INNER JOIN tblBuildings AS b ON msg.buildingID = b.id WHERE (msg.id IN (SELECT DISTINCT msgID FROM tblMsgRecipients WHERE(sentDate IS NULL))) "; if (flagQueued) { mailQuery += " AND msg.queue = '" + queued + "'"; } DataHandler dh = new DataHandler(); String status = String.Empty; DataSet ds = DataHandler.getData(mailQuery, out status); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { int msgID = int.Parse(dr["id"].ToString()); String bCode = dr["Code"].ToString(); String dataPath = dr["DataPath"].ToString(); bool incBCC = bool.Parse(dr["incBCC"].ToString()); String fromAddress = dr["fromAddress"].ToString(); String bccAddy = dr["bccAddy"].ToString(); String subject = dr["subject"].ToString(); String message = dr["message"].ToString(); bool billBuilding = bool.Parse(dr["billBuilding"].ToString()); double billAmount = double.Parse(dr["billAmount"].ToString()); String attachmentQuery = "SELECT Name, Data FROM tblMsgData WHERE msgID = " + msgID.ToString(); DataSet dsAttachment = DataHandler.getData(attachmentQuery, out status); Dictionary <String, byte[]> attachments = new Dictionary <string, byte[]>(); if (dsAttachment != null && dsAttachment.Tables.Count > 0 && dsAttachment.Tables[0].Rows.Count > 0) { foreach (DataRow drA in dsAttachment.Tables[0].Rows) { try { if (!attachments.ContainsKey(drA["Name"].ToString())) { attachments.Add(drA["Name"].ToString(), (byte[])drA["Data"]); } } catch { } } } String billableCustomersQuery = "SELECT distinct accNo FROM tblMsgRecipients WHERE billCustomer = 'True' and msgID = " + msgID.ToString(); String allRecipientsQuery = "SELECT id, accNo, recipient FROM tblMsgRecipients WHERE sentDate is null AND msgID = " + msgID.ToString(); DataSet billableCustomers = DataHandler.getData(billableCustomersQuery, out status); DataSet receivers = DataHandler.getData(allRecipientsQuery, out status); Dictionary <String, bool> emails = new Dictionary <string, bool>(); if (receivers != null && receivers.Tables.Count > 0 && receivers.Tables[0].Rows.Count > 0) { foreach (DataRow rrece in receivers.Tables[0].Rows) { try { String id = rrece["id"].ToString(); String[] emailAddys = rrece["recipient"].ToString().Split(new String[] { ";" }, StringSplitOptions.RemoveEmptyEntries); bool success = MailSender.SendMail(fromAddress, emailAddys, subject, message, false, false, false, out status, attachments); String updQuery = "UPDATE tblMsgRecipients SET sentDate = '" + DateTime.Now.ToString() + "' WHERE id = " + id; RaiseEvent(String.Join(";", emailAddys) + " - " + status); DataHandler.setData(updQuery, out status); if (status != "") { RaiseEvent(updQuery + " - " + status); } try { emails.Add(rrece["accNo"].ToString(), success); } catch { } } catch { } } String bulkUpdateQuery = "UPDATE tblMsgRecipients SET sentDate = '" + DateTime.Now.ToString() + "' WHERE msgID = " + msgID.ToString(); DataHandler.setData(bulkUpdateQuery, out status); if (status != "") { RaiseEvent(bulkUpdateQuery + " - " + status); } } String updateQuery = "UPDATE tblMsg SET queue = 'False' WHERE id = " + msgID.ToString(); DataHandler.setData(updateQuery, out status); message += Environment.NewLine + Environment.NewLine; message += "Send status:" + Environment.NewLine + Environment.NewLine; var builder = new System.Text.StringBuilder(); builder.Append(message); foreach (KeyValuePair <String, bool> statuses in emails) { builder.Append(statuses.Key + " = " + statuses.Value.ToString() + Environment.NewLine); } message = builder.ToString(); if (incBCC) { MailSender.SendMail(fromAddress, new String[] { bccAddy }, subject, message, false, false, false, out status, attachments); } } } }
public void SendImmediateLetters() { DataSet queue = GetQueuedLetters(true); if (queue != null && queue.Tables.Count > 0 && queue.Tables[0].Rows.Count > 0) { RaiseEvent("Mail count = " + queue.Tables[0].Rows.Count.ToString()); foreach (DataRow qDR in queue.Tables[0].Rows) { try { String id = qDR["id"].ToString(); String[] email1 = qDR["toEmail"].ToString().Split(new String[] { ";" }, StringSplitOptions.RemoveEmptyEntries); String subject = qDR["subject"].ToString(); String message = qDR["message"].ToString(); bool html = bool.Parse(qDR["html"].ToString()); bool isPA = html; html = false; bool addcc = bool.Parse(qDR["html"].ToString()); bool readreceipt = bool.Parse(qDR["html"].ToString()); String[] attachments = qDR["attachment"].ToString().Split(new String[] { ";" }, StringSplitOptions.RemoveEmptyEntries); for (int i = 0; i < attachments.Length; i++) { if (attachments[i].Contains("PA Attachments")) { RaiseEvent("Original file name = " + attachments[i], "SendImmediateLetters"); attachments[i] = attachments[i].Replace("K:\\Debtors System\\PA Attachments\\", @"K:\\Pastel11\\Debtors System\\PA Attachments\\"); } } String[] files = new string[attachments.Length]; String accNo = qDR["unitno"].ToString(); String debtorEmail = qDR["fromEmail"].ToString(); String sentDate = qDR["sentDate"].ToString(); String cc = qDR["cc"].ToString(); String bcc = qDR["bcc"].ToString(); for (int i = 0; i < attachments.Length; i++) { String attachment = attachments[i]; String fileName = GetLetter(attachment); files[i] = fileName; attachments[i] = fileName; String actFileTitle = Path.GetFileNameWithoutExtension(fileName); String actFile = Path.GetFileName(fileName); } if (email1.Length > 0 && String.IsNullOrEmpty(sentDate)) { status = String.Empty; if (isPA && MailSender.SendMail(debtorEmail, email1, cc, bcc, subject, message, html, out status, attachments)) { if (!String.IsNullOrEmpty(status)) { RaiseEvent(status); } String update1 = "UPDATE tblLetterRun SET sentDate = getDate(), errorMessage = 'Processed & Sent' WHERE id = " + id; DataHandler.setData(update1, out status); } else if (MailSender.SendMail("*****@*****.**", email1, addcc ? debtorEmail : String.Empty, subject, message, html, out status, files)) { if (!String.IsNullOrEmpty(status)) { RaiseEvent(status); } String update1 = "UPDATE tblLetterRun SET sentDate = getDate(), errorMessage = 'Processed & Sent' WHERE id = " + id; DataHandler.setData(update1, out status); } else { String update1 = "UPDATE tblLetterRun SET sentDate = getDate(), errorMessage = 'Error: " + status + "' WHERE id = " + id; DataHandler.setData(update1, out status); RaiseEvent(status); } } else { RaiseEvent("Can't send mail", "SendImmediateLetters"); String update1 = "UPDATE tblLetterRun SET sentDate = getDate(), errorMessage = 'Error: No email address' WHERE id = " + id; DataHandler.setData(update1, out status); } } catch (Exception ex) { RaiseEvent(ex.Message + " " + ex.StackTrace, "SendImmediateLetters"); } } } }
private void UpdateMessage(String msgID, String batchID) { String query = String.Format("UPDATE tblSMS SET batchID = '{0}', status = '1' WHERE id = '{1}'", batchID, msgID); DataHandler.setData(query, out dataStatus); }
private void UpdateMessage(String batchID, String status, DateTime nextPolled, int pollCount) { String query = String.Format("UPDATE tblSMS SET status = '{0}', nextPolled = getDate(), pollCount = {2} WHERE batchID = '{3}'", status, nextPolled.ToString(), pollCount.ToString(), batchID); DataHandler.setData(query, out dataStatus); }