public static void Send(EmailMarketingQueue list)
{
log.Debug("SendTravelCreditExpiredEmail Started.");
try
{
Hashtable ht = new Hashtable();
string hostURL = Core.GetAppSettingValueEnhanced("HostURL");
string checkClickLink = string.Format("{0}/member/CheckEmailLinkClicked?orderId=&userID={1}&FunctionType={2}&activeUserID=&url=", hostURL, list.UserID, "TravelCreditExpired");
ht.Add("<#checkClick>", checkClickLink);
string mailTemplate = Core.getMailTemplateByID(list.TemplateID, ht);
bool isSuccessTest = CommonServiceController.SendEmail(list.Recipient, "Your Mayflower's Travel Credit is almost expired!", mailTemplate);
if (isSuccessTest)
{
mailTemplate = mailTemplate.Replace("'", "''");
var DB = new Service.Database.mysql(ConfigurationManager.ConnectionStrings["MySqlConnector"].ToString());
//Reminder!!!! When deploy production need change table naming """""" Stg_EmailMarketingQueueLog for staging , EmailMarketingQueueLog for production
//Store MYSQL
if (Core.IsForStaging)
{
DB.ExecuteNonQuery(string.Format("INSERT INTO MayflowerLogs.Stg_EmailMarketingQueueLog (EmailMarketingQueueID, EmailContent, TemplateID, EmailMarketingType, FunctionType, TravelCreditExpiredDate, MemberRegistrationDate, FullName, UserID, Recipient, CC, Bcc, CreatedDate) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}');",
list.EmailMarketingQueueID,
mailTemplate,
list.TemplateID,
list.EmailMarketingType,
list.FunctionType,
list.TravelCreditExpiredDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.MemberRegistrationDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.FullName,
list.UserID,
list.Recipient,
list.CC,
list.Bcc,
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff000")
));
DB.Dispose();
}
else
{
DB.ExecuteNonQuery(string.Format("INSERT INTO MayflowerLogs.EmailMarketingQueueLog (EmailMarketingQueueID, EmailContent, TemplateID, EmailMarketingType, FunctionType, TravelCreditExpiredDate, MemberRegistrationDate, FullName, UserID, Recipient, CC, Bcc, CreatedDate) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}');",
list.EmailMarketingQueueID,
mailTemplate,
list.TemplateID,
list.EmailMarketingType,
list.FunctionType,
list.TravelCreditExpiredDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.MemberRegistrationDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.FullName,
list.UserID,
list.Recipient,
list.CC,
list.Bcc,
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff000")
));
DB.Dispose();
}
//remove MSSQL
SqlCommand command = new SqlCommand();
if (command.Connection == null)
{
command = dbADO.OpenConnection(command);
}
var emailQueueInsertQuery = "DELETE FROM [Mayflower].[BOS].[EmailMarketingQueue] WHERE EmailMarketingQueueID = @emailMarketingQueueID";
command.CommandText = emailQueueInsertQuery;
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@EmailMarketingQueueID", list.EmailMarketingQueueID));
command.ExecuteNonQuery();
command.Transaction.Commit();
}
}
catch (Exception ex)
{
log.Debug("Unable to complete SendTravelCreditExpiredEmail.");
throw ex;
}
log.Debug("SendTravelCreditExpiredEmail End.");
}
public static void Send(EmailMarketingQueue list)
{
log.Debug("SendCrossSaleEmail Started.");
try
{
Hashtable ht = new Hashtable();
string hashKeyString = "";
int imageKeyCount = 1;
//string hotelList = "";
var dbHotelList = db.EmailMarketingQueueHotels.Where(x => x.EmailMarketingQueueID == list.EmailMarketingQueueID && x.WebImage != null).ToList();
if (dbHotelList.Count() > 0)
{
for (int i = 1; i <= dbHotelList.Count() && i < 7; i++)
{
hashKeyString = "<#HotelImageUrl" + imageKeyCount + ">";
ht.Add(hashKeyString, dbHotelList[i].WebImage);
imageKeyCount++;
}
if (dbHotelList.Count() < 6) //for place "" for the key in template (remaining position), avoid <img src="tag"> show no/error image
{
for (int i = imageKeyCount + 1; i < 7; i++)
{
hashKeyString = "<#HotelImageUrl" + i + ">";
ht.Add(hashKeyString, "");
imageKeyCount++;
}
}
}
else
{
for (int i = 1; i <= dbHotelList.Count() && i < 7; i++)
{
hashKeyString = "<#HotelImageUrl" + i + ">";
ht.Add(hashKeyString, "");
}
}
string hostURL = Core.GetAppSettingValueEnhanced("HostURL");
string checkClickLink = string.Format("{0}/member/CheckEmailLinkClicked?orderId={1}&userID=&FunctionType={2}&activeUserID=&url=", hostURL, list.OrderID, "CrossSalesFlight");
ht.Add("<#checkClick>", checkClickLink);
string mailTemplate = Core.getMailTemplateByID(list.TemplateID, ht);
bool isSuccessTest = CommonServiceController.SendEmail(list.Recipient, "Good news! We’ll pay RM80 on your hotel booking!", mailTemplate);
if (isSuccessTest)
{
mailTemplate = mailTemplate.Replace("'", "''");
var DB = new Service.Database.mysql(ConfigurationManager.ConnectionStrings["MySqlConnector"].ToString());
//Reminder!!!! When deploy production need change table naming """""" Stg_EmailMarketingQueueLog for staging , EmailMarketingQueueLog for production
//Store MYSQL
if (Core.IsForStaging)
{
if (list.UserID.HasValue)
{
DB.ExecuteNonQuery(string.Format("INSERT INTO MayflowerLogs.Stg_EmailMarketingQueueLog (EmailMarketingQueueID, EmailContent, TemplateID, EmailMarketingType, FunctionType, BookingDate, SuperPNRID, OrderID, SuperPNRNo, FullName, UserID, Recipient, CC, Bcc, FlightDestinationCode, FlightDestination, CreatedDate, PromoID, PromoCode) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}');",
list.EmailMarketingQueueID,
mailTemplate,
list.TemplateID,
list.EmailMarketingType,
list.FunctionType,
list.BookingDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.SuperPNRID,
list.OrderID,
list.SuperPNRNo,
list.FullName,
list.UserID,//list.UserID.HasValue ? list.UserID : null,
list.Recipient,
list.CC,
list.Bcc,
list.FlightDestinationCode,
list.FlightDestination,
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.PromoID,
list.PromoCode
));
DB.Dispose();
}
else
{
DB.ExecuteNonQuery(string.Format("INSERT INTO MayflowerLogs.Stg_EmailMarketingQueueLog (EmailMarketingQueueID, EmailContent, TemplateID, EmailMarketingType, FunctionType, BookingDate, SuperPNRID, OrderID, SuperPNRNo, FullName, Recipient, CC, Bcc, FlightDestinationCode, FlightDestination, CreatedDate, PromoID, PromoCode) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}');",
list.EmailMarketingQueueID,
mailTemplate,
list.TemplateID,
list.EmailMarketingType,
list.FunctionType,
list.BookingDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.SuperPNRID,
list.OrderID,
list.SuperPNRNo,
list.FullName,
//list.UserID,//list.UserID.HasValue ? list.UserID : null,
list.Recipient,
list.CC,
list.Bcc,
list.FlightDestinationCode,
list.FlightDestination,
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.PromoID,
list.PromoCode
));
DB.Dispose();
}
}
else
{
if (list.UserID.HasValue)
{
DB.ExecuteNonQuery(string.Format("INSERT INTO MayflowerLogs.EmailMarketingQueueLog (EmailMarketingQueueID, EmailContent, TemplateID, EmailMarketingType, FunctionType, BookingDate, SuperPNRID, OrderID, SuperPNRNo, FullName, UserID, Recipient, CC, Bcc, FlightDestinationCode, FlightDestination, CreatedDate, PromoID, PromoCode) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}');",
list.EmailMarketingQueueID,
mailTemplate,
list.TemplateID,
list.EmailMarketingType,
list.FunctionType,
list.BookingDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.SuperPNRID,
list.OrderID,
list.SuperPNRNo,
list.FullName,
list.UserID,//list.UserID.HasValue ? list.UserID : null,
list.Recipient,
list.CC,
list.Bcc,
list.FlightDestinationCode,
list.FlightDestination,
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.PromoID,
list.PromoCode
));
DB.Dispose();
}
else
{
DB.ExecuteNonQuery(string.Format("INSERT INTO MayflowerLogs.EmailMarketingQueueLog (EmailMarketingQueueID, EmailContent, TemplateID, EmailMarketingType, FunctionType, BookingDate, SuperPNRID, OrderID, SuperPNRNo, FullName, Recipient, CC, Bcc, FlightDestinationCode, FlightDestination, CreatedDate, PromoID, PromoCode) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}');",
list.EmailMarketingQueueID,
mailTemplate,
list.TemplateID,
list.EmailMarketingType,
list.FunctionType,
list.BookingDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.SuperPNRID,
list.OrderID,
list.SuperPNRNo,
list.FullName,
//list.UserID,//list.UserID.HasValue ? list.UserID : null,
list.Recipient,
list.CC,
list.Bcc,
list.FlightDestinationCode,
list.FlightDestination,
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.PromoID,
list.PromoCode
));
DB.Dispose();
}
}
//remove MSSQL
SqlCommand command = new SqlCommand();
if (command.Connection == null)
{
command = dbADO.OpenConnection(command);
}
var emailQueueInsertQuery = "DELETE FROM [Mayflower].[BOS].[EmailMarketingQueue] WHERE EmailMarketingQueueID = @emailMarketingQueueID";
command.CommandText = emailQueueInsertQuery;
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@EmailMarketingQueueID", list.EmailMarketingQueueID));
command.ExecuteNonQuery();
command.Transaction.Commit();
command.Dispose();
}
string debugMessage = isSuccessTest ? "EmailMarketingQueueID:" + list.EmailMarketingQueueID.ToString() + " email is successfully sent" : "EmailMarketingQueueID:" + list.EmailMarketingQueueID.ToString() + " email is fail sent";
log.Debug(debugMessage);
}
catch (Exception ex)
{
log.Debug("Unable to complete SendCrossSaleEmail.");
throw ex;
}
log.Debug("SendCrossSaleEmail End.");
}
public static void Send(EmailMarketingQueue list)
{
log.Debug("SendActivationReminderEmail Started.");
try
{
string registeredUserID = Alphareds.Module.Cryptography.Cryptography.AES.Encrypt(list.UserID.ToString());
string urlEncodeRegisteredUserID = System.Web.HttpUtility.UrlEncode(registeredUserID);
Hashtable ht = new Hashtable(); // hash need to add things
string hostURL = Core.GetAppSettingValueEnhanced("HostURL");
string ActiveUrlFrontPart = Core.GetAppSettingValueEnhanced("ActiveUrlFrontPart");
string ActiveUrlKeyPart = string.Format("token={0}&Email={1}&atype={2}", urlEncodeRegisteredUserID, list.Recipient, "nopass");
string ActiveUrlFull = hostURL + ActiveUrlFrontPart + ActiveUrlKeyPart;
string encodeUrl = System.Web.HttpUtility.UrlEncode(ActiveUrlFull);
ht.Add("<#ActiveURL>", encodeUrl);
string checkClickLink = string.Format("{0}/member/CheckEmailLinkClicked?orderId=&userID=&activeUserID={1}&FunctionType={2}&url=", hostURL, list.UserID, "ActivationReminder");
ht.Add("<#checkClick>", checkClickLink);
string mailTemplate = Core.getMailTemplateByID(list.TemplateID, ht);
bool isSuccess = CommonServiceController.SendEmail(list.Recipient, "Last 48 hours to update your Mayflower account!", mailTemplate);
if (isSuccess)
{
mailTemplate = mailTemplate.Replace("'", "''");
var DB = new Service.Database.mysql(ConfigurationManager.ConnectionStrings["MySqlConnector"].ToString());
//Reminder!!!! When deploy production need change table naming """""" Stg_EmailMarketingQueueLog for staging , EmailMarketingQueueLog for production
//store MYSQL
if (Core.IsForStaging)
{
DB.ExecuteNonQuery(string.Format("INSERT INTO MayflowerLogs.Stg_EmailMarketingQueueLog (EmailMarketingQueueID, EmailContent, TemplateID, EmailMarketingType, FunctionType, MemberRegistrationDate, FullName, UserID, Recipient, CC, Bcc, CreatedDate) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}');",
list.EmailMarketingQueueID,
mailTemplate,
list.TemplateID,
list.EmailMarketingType,
list.FunctionType,
list.MemberRegistrationDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.FullName,
list.UserID,
list.Recipient,
list.CC,
list.Bcc,
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff000")
));
DB.Dispose();
}
else
{
DB.ExecuteNonQuery(string.Format("INSERT INTO MayflowerLogs.EmailMarketingQueueLog (EmailMarketingQueueID, EmailContent, TemplateID, EmailMarketingType, FunctionType, MemberRegistrationDate, FullName, UserID, Recipient, CC, Bcc, CreatedDate) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}');",
list.EmailMarketingQueueID,
mailTemplate,
list.TemplateID,
list.EmailMarketingType,
list.FunctionType,
list.MemberRegistrationDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.FullName,
list.UserID,
list.Recipient,
list.CC,
list.Bcc,
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff000")
));
DB.Dispose();
}
//remove MSSQL
SqlCommand command = new SqlCommand();
if (command.Connection == null)
{
command = dbADO.OpenConnection(command);
}
var emailQueueInsertQuery = "DELETE FROM [Mayflower].[BOS].[EmailMarketingQueue] WHERE EmailMarketingQueueID = @emailMarketingQueueID";
command.CommandText = emailQueueInsertQuery;
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@EmailMarketingQueueID", list.EmailMarketingQueueID));
command.ExecuteNonQuery();
command.Transaction.Commit();
command.Dispose();
}
string debugMessage = isSuccess ? "UserID:" + list.UserID.ToString() + " email is successfully sent" : "UserID:" + list.UserID.ToString() + " email is fail sent";
log.Debug(debugMessage);
log.Debug("SendActivationReminderEmail Successfully Ended.");
}
catch (Exception ex)
{
log.Debug("Unable to complete SendActivationReminderEmail." + ex.ToString());
throw ex;
}
}
public static void Send(EmailMarketingQueue list)
{
log.Debug("SendCrossSalesInsuranceEmail Started.");
try
{
Hashtable ht = new Hashtable();
string hostURL = Core.GetAppSettingValueEnhanced("HostURL");
string InsuranceUrlFrontPart = Core.GetAppSettingValueEnhanced("InsuranceUrlFrontPart");
string InsuranceUrlKeyPart = string.Format("superPNRNo={0}", list.SuperPNRNo);
string InsuranceUrlFull = hostURL + InsuranceUrlFrontPart + InsuranceUrlKeyPart;
string encodeUrl = System.Web.HttpUtility.UrlEncode(InsuranceUrlFull);
ht.Add("<#InsuranceURL>", encodeUrl);
string checkClickLink = string.Format("{0}/member/CheckEmailLinkClicked?orderId={1}&FunctionType={2}&userID=&activeUserID=&url=", hostURL, list.OrderID, "insurance");
ht.Add("<#checkClick>", checkClickLink);
string mailTemplate = Core.getMailTemplateByID(list.TemplateID, ht);
bool isSuccessSend = CommonServiceController.SendEmail(list.Recipient, "Travel Smarter With CHUBB Insurance", mailTemplate);
if (isSuccessSend)
{
mailTemplate = mailTemplate.Replace("'", "''");
var DB = new Service.Database.mysql(ConfigurationManager.ConnectionStrings["MySqlConnector"].ToString());
//Reminder!!!! When deploy production need change table naming """""" Stg_EmailMarketingQueueLog for staging , EmailMarketingQueueLog for production
//Store MYSQL
if (Core.IsForStaging)
{
DB.ExecuteNonQuery(string.Format("INSERT INTO MayflowerLogs.Stg_EmailMarketingQueueLog (EmailMarketingQueueID, EmailContent, TemplateID, EmailMarketingType, FunctionType, " +
"BookingDate, SuperPNRID, OrderID, SuperPNRNo, FlightDestinationCode, FlightDestination, PromoID, PromoCode, " +
"FullName, UserID, Recipient, CC, Bcc, CreatedDate) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}');",
list.EmailMarketingQueueID,
mailTemplate,
list.TemplateID,
list.EmailMarketingType,
list.FunctionType,
list.BookingDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.SuperPNRID,
list.OrderID,
list.SuperPNRNo,
list.FlightDestinationCode,
list.FlightDestination,
list.PromoID,
list.PromoCode,
list.FullName,
list.UserID,
list.Recipient,
list.CC,
list.Bcc,
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff000")
));
DB.Dispose();
}
else
{
DB.ExecuteNonQuery(string.Format("INSERT INTO MayflowerLogs.EmailMarketingQueueLog (EmailMarketingQueueID, EmailContent, TemplateID, EmailMarketingType, FunctionType, " +
"BookingDate, SuperPNRID, OrderID, SuperPNRNo, FlightDestinationCode, FlightDestination, PromoID, PromoCode, " +
"FullName, UserID, Recipient, CC, Bcc, CreatedDate) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}');",
list.EmailMarketingQueueID,
mailTemplate,
list.TemplateID,
list.EmailMarketingType,
list.FunctionType,
list.BookingDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff000"),
list.SuperPNRID,
list.OrderID,
list.SuperPNRNo,
list.FlightDestinationCode,
list.FlightDestination,
list.PromoID,
list.PromoCode,
list.FullName,
list.UserID,
list.Recipient,
list.CC,
list.Bcc,
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff000")
));
DB.Dispose();
}
//remove MSSQL
SqlCommand command = new SqlCommand();
if (command.Connection == null)
{
command = dbADO.OpenConnection(command);
}
var emailQueueInsertQuery = "DELETE FROM [Mayflower].[BOS].[EmailMarketingQueue] WHERE EmailMarketingQueueID = @emailMarketingQueueID";
command.CommandText = emailQueueInsertQuery;
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@EmailMarketingQueueID", list.EmailMarketingQueueID));
command.ExecuteNonQuery();
command.Transaction.Commit();
}
}
catch (Exception ex)
{
log.Debug("Unable to complete SendCrossSalesInsuranceEmail.");
throw ex;
}
log.Debug("SendCrossSalesInsuranceEmail End.");
}
