public static void Scheduler(object stateInfo)
{
Com.VerySimple.Phreeze.Phreezer phreeze1 = new Com.VerySimple.Phreeze.Phreezer(ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString);
Com.VerySimple.Phreeze.Phreezer phreeze2 = new Com.VerySimple.Phreeze.Phreezer(ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString);
string id = stateInfo.ToString();
log4net.LogManager.GetLogger("Scheduler").Info("Schedule ID: " + id);
XmlDocument settingsDoc = new XmlDocument();
using (MySqlDataReader reader = phreeze1.ExecuteReader("SELECT ss_data FROM system_setting where ss_code = 'SYSTEM';"))
{
if (reader.Read())
{
settingsDoc.LoadXml(reader["ss_data"].ToString());
}
}
// run sql that checks if the first notification is null or second notification is null and no agent examination has been completed for the given id
using (MySqlDataReader reader = phreeze1.ExecuteReader("select s.s_id, s.a_id, s.o_id, s.r_id, s.att_id, s.ua_id, s.s_first_notification, s.s_second_notification, s.s_search_package_date, s.s_created, s.s_modified, orig.a_id, orig.a_username, orig.a_first_name, orig.a_last_name, orig.a_email, o.o_customer_id, o.o_client_name, o.o_id, o.o_internal_id, r.r_request_type_code from `schedule` s inner join `account` a on a.a_id = s.a_id and a.a_underwriter_codes not like '%dir-%' inner join `order` o on o.o_id = s.o_id inner join `request` r on r.r_id = s.r_id join account orig on orig.a_id = o.o_originator_id left join attachment att on att.att_request_id = r.r_id and (att.att_purpose_code = 'Committment' or att.att_purpose_code = 'ExamSheet') left join upload_log ul on ul.att_id = att.att_id and ul.ua_id <> o.o_originator_id where s.s_id = " + id + " and s.s_search_package_date is not null and att.att_request_id is null and ul.ul_id is null and (s.s_first_notification is null or s.s_second_notification is null) group by s.s_id, s.a_id, s.o_id, s.r_id, s.att_id, s.ua_id, s.s_first_notification, s.s_second_notification, s.s_search_package_date, s.s_created, s.s_modified, orig.a_id, orig.a_username, orig.a_first_name, orig.a_last_name, orig.a_email, o.o_customer_id, o.o_client_name, o.o_id, o.o_internal_id, r.r_request_type_code"))
{
if (reader.Read())
{
bool isFirstNotification = reader.IsDBNull(6);
// send email and update first notification or second notification dates
if (isFirstNotification)
{
log4net.LogManager.GetLogger("Scheduler").Info("First Notification: " + reader["s_id"].ToString());
phreeze2.ExecuteNonQuery("UPDATE `schedule` SET s_first_notification = NOW(), s_modified = NOW() WHERE s_id = " + reader["s_id"].ToString());
string to = reader["a_email"].ToString() + ", [email protected]";
//to = "[email protected], [email protected]";
string workingID = (reader["o_internal_id"].ToString().Equals(""))? "WEB-" + reader["o_id"].ToString() : reader["o_internal_id"].ToString();
string url = settingsDoc.SelectSingleNode("//field[@name='RootUrl']").InnerText + "MyOrder.aspx?id=" + reader["o_id"].ToString();
string subject = "First Notification for Agent Examination for Affinity Order '" + reader["o_client_name"].ToString().Replace("\r", "").Replace("\n", "") + "' #" + workingID;
Com.VerySimple.Email.Mailer mailer = new Com.VerySimple.Email.Mailer(settingsDoc.SelectSingleNode("//field[@name='SmtpHost']").InnerText);
string msg = "Dear " + reader["a_first_name"].ToString() + ",\r\n\r\n"
+ "Your Affinity " + reader["r_request_type_code"].ToString() + " for '" + reader["o_client_name"].ToString() + "', ID #"
+ workingID + " needs a completed Agent Examination form.\r\n\r\n"
+ "Friendly: " + reader["o_client_name"].ToString() + "\r\n"
+ "Tracking Code: " + reader["o_customer_id"].ToString() + "\r\n\r\n"
+ "You may view the full details of your order anytime online at " + url + ". "
+ "If you would prefer to not receive this notification, you may also login "
+ "to your Affinity account and customize your email notification preferences.\r\n\r\n"
+ settingsDoc.SelectSingleNode("//field[@name='EmailFooter']").InnerText;
//+ "\r\n\r\nThis email would have gone to: " + reader["a_email"].ToString();
mailer.Send(
settingsDoc.SelectSingleNode("//field[@name='SendFromEmail']").InnerText
, to
, subject
, msg);
}
else
{
log4net.LogManager.GetLogger("Scheduler").Info("Second Notification: " + reader["s_id"].ToString());
phreeze2.ExecuteNonQuery("UPDATE `schedule` SET s_second_notification = NOW(), s_modified = NOW() WHERE s_id = " + reader["s_id"].ToString());
string to = reader["a_email"].ToString() + ", [email protected]";
//to = "[email protected], [email protected]";
string workingID = (reader["o_internal_id"].ToString().Equals(""))? "WEB-" + reader["o_id"].ToString() : reader["o_internal_id"].ToString();
string url = settingsDoc.SelectSingleNode("//field[@name='RootUrl']").InnerText + "MyOrder.aspx?id=" + reader["o_id"].ToString();
string subject = "Second Notification for Agent Examination for Affinity Order '" + reader["o_client_name"].ToString().Replace("\r", "").Replace("\n", "") + "' #" + workingID;
Com.VerySimple.Email.Mailer mailer = new Com.VerySimple.Email.Mailer(settingsDoc.SelectSingleNode("//field[@name='SmtpHost']").InnerText);
string msg = "Dear " + reader["a_first_name"].ToString() + ",\r\n\r\n"
+ "Your Affinity " + reader["r_request_type_code"].ToString() + " for '" + reader["o_client_name"].ToString() + "', ID #"
+ workingID + " needs a completed Agent Examination form.\r\n\r\n"
+ "Friendly: " + reader["o_client_name"].ToString() + "\r\n"
+ "Tracking Code: " + reader["o_customer_id"].ToString() + "\r\n\r\n"
+ "You may view the full details of your order anytime online at " + url + ". "
+ "If you would prefer to not receive this notification, you may also login "
+ "to your Affinity account and customize your email notification preferences.\r\n\r\n"
+ settingsDoc.SelectSingleNode("//field[@name='EmailFooter']").InnerText
+ "\r\n\r\nThis email would have gone to: " + reader["a_email"].ToString();
mailer.Send(
settingsDoc.SelectSingleNode("//field[@name='SendFromEmail']").InnerText
, to
, subject
, msg);
}
}
}
}
/// <summary>
/// Confirms the order, which means that it is assigned an Affinity Id and all requests are
/// marked as "In Progress". If the customer preference is set, a notification email will
/// be sent out as well
/// </summary>
/// <param name="internalId">Affinity ID to assign to this order</param>
/// <returns>Affinity.WsResponse</returns>
public Affinity.WsResponse Confirm(string internalId, Hashtable systemSettings)
{
Affinity.WsResponse wsr = new WsResponse();
this.InternalId = internalId;
Affinity.Requests rs = this.GetCurrentRequests();
foreach (Affinity.Request r in rs)
{
r.StatusCode = Affinity.RequestStatus.InProgressCode;
r.Update();
}
this.SyncStatus(); // this will also update the order
wsr.IsSuccess = true;
wsr.ActionWasTaken = true;
wsr.Message = "Order was confirmed";
wsr.NotificationMessage = " The customer was *NOT* notified based on their user preference.";
// send the notification email if the originator wants it
if (this.Account.GetPreference("EmailOnConfirmation").Equals("Yes"))
{
string to = this.Account.GetPreference("EmailOnConfirmationAddress", this.Account.Email);
if (!to.Equals(""))
{
string url = systemSettings["RootUrl"].ToString() + "MyOrder.aspx?id=" + this.Id.ToString();
string subject = "Affinity Order '" + this.ClientName.Replace("\r", "").Replace("\n", "") + "' #" + this.WorkingId + " Confirmed";
// send the email
Com.VerySimple.Email.Mailer mailer = new Com.VerySimple.Email.Mailer(systemSettings["SmtpHost"].ToString());
string msg = "Dear " + this.Account.FirstName + ",\r\n\r\n"
+ "Your Affinity order for '" + this.ClientName + "' has been confirmed and assigned the AFF ID "
+ this.InternalId + ". You may use this ID when corresponding with us regarding this order.\r\n\r\n"
+ "Friendly: " + this.ClientName + "\r\n"
+ "Tracking Code: " + this.CustomerId + "\r\n\r\n"
+ "You may view the full details of your order anytime online at " + url + ". "
+ "If you would prefer to not receive this notification, you may also login "
+ "to your Affinity account and customize your email notification preferences.\r\n\r\n"
+ systemSettings["EmailFooter"].ToString();
wsr.NotificationSent = mailer.Send(
systemSettings["SendFromEmail"].ToString()
, to.Replace(";", ",")
, subject
, msg);
if (wsr.NotificationSent)
{
wsr.NotificationMessage = " A notification email was sent to " + to + ".";
}
else
{
wsr.NotificationMessage = " Unable to deliver notification email to " + to + ". Please check the server debug logs for more information.";
}
}
}
return(wsr);
}
