public static void SetSchedule() { Com.VerySimple.Phreeze.Phreezer phreeze = new Com.VerySimple.Phreeze.Phreezer(ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString); // run sql that checks for any search package where the first notification is null or second notification is null and no agent examination has been completed log4net.LogManager.GetLogger("SetSchedule").Info("Start"); using (MySqlDataReader reader = phreeze.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_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")) { while (reader.Read()) { string id = reader["s_id"].ToString(); ScheduledEvents.TimerDelegate p = Scheduler; DateTime nw = DateTime.Now; bool isFirstNotification = reader.IsDBNull(6); // actual date set in database DateTime dt = (DateTime)reader["s_search_package_date"]; //dt = dt.AddHours((isFirstNotification) ? 72 : 168); dt = (isFirstNotification) ? AddWorkDays(dt, 3) : AddWorkDays(dt, 7); TimeSpan ts = dt.Subtract(nw); if (ts.TotalMilliseconds < 0) { log4net.LogManager.GetLogger("SetSchedule").Info("Schedule Now Id: " + id); Scheduler(id); } else if (!timers.Contains(id)) { log4net.LogManager.GetLogger("SetSchedule").Info("Schedule Later Id: " + id + " in " + ts.TotalMilliseconds.ToString() + " milliseconds"); SetTimer(p, ((long)ts.TotalMilliseconds), true, id); } } } log4net.LogManager.GetLogger("SetSchedule").Info("End"); }
void Application_Start(object sender, EventArgs e) { // configure the logger log4net.Config.XmlConfigurator.ConfigureAndWatch(new System.IO.FileInfo(Server.MapPath("log.config.xml"))); log4net.LogManager.GetLogger("Application_Start").Info("Application Started"); // load all the systems settings from the database Com.VerySimple.Phreeze.Phreezer p = new Com.VerySimple.Phreeze.Phreezer(ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString); Affinity.SystemSetting ss = new Affinity.SystemSetting(p); ss.Load(Affinity.SystemSetting.DefaultCode); Application[Affinity.SystemSetting.DefaultCode] = ss.Settings; p.Close(); Application["ACTIVE"] = new Hashtable(); SetSchedule(); }
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); } } } }