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