public bool TourPackageEnquiry(ProductTourPackage prodTour, string travelerdata, string enquiryQuestion, string tripid)
{
CheckoutProduct checkout = (CheckoutProduct)Core.GetSession(Enumeration.SessionName.CheckoutProduct, tripid);
bool successSend = false;
try
{
if (checkout != null)
{
checkout.TourPackage.ContactPerson = prodTour.ContactPerson;
var tourdtl = checkout.TourPackage.TourPackageDetails;
var tourinfo = checkout.TourPackage.TourPackagesInfo;
var hotelSelected = "";
if (tourinfo.RoomListSelected != null)
{
int star = UtilitiesService.GetTourRoomRating(tourinfo.RoomListSelected.FirstOrDefault().RoomTypeID);
hotelSelected = star + (star > 1 ? " stars" : " star") + "<br/>" + string.Join("<br/>", tourinfo.TourRoomPackages.Select(x => x.RoomTypeName + " x " + x.Qty));
}
decimal ttlprice = checkout.CheckOutSummary.GrandTtlAmt_BeforeDiscount;
decimal deposit = ttlprice;
decimal depositrate = tourdtl.DepositRate ?? 0;
if ((tourdtl.IsDepositOnly ?? false) && depositrate > 0)
{
deposit = tourdtl.DepositTypeCode == "FIX" ? depositrate * tourinfo.NoOfPax : ttlprice * depositrate / 100;
}
Hashtable ht = new Hashtable();
ht.Add("<#UserName>", prodTour.ContactPerson.GivenName + " " + prodTour.ContactPerson.Surname);
ht.Add("<#PackageName>", tourdtl.TourPackageName);
ht.Add("<#TravelDate>", tourinfo.TravelDateFrom.ToString("dd/MM/yyyy") + " - " + tourinfo.TravelDateTo.ToString("dd/MM/yyyy"));
ht.Add("<#HotelSelection>", hotelSelected);
ht.Add("<#TourCode>", tourdtl.TourPackageCode);
ht.Add("<#TtlPrice>", ttlprice.ToString("n2"));
ht.Add("<#Email>", checkout.TourPackage.ContactPerson.Email);
ht.Add("<#EnquiryQ>", enquiryQuestion);
var CSemail = Core.GetSettingValue("MayflowerCSEmail");
successSend = CommonServiceController.SendEmail(CSemail, "Ground Package Enquiry Email", Core.getMailTemplate("tourpackageenquiry", ht));
if (!successSend)
{
logger.Fatal("errmsg", "Tour Package send enquiry email error." + " - " + DateTime.Now.ToLoggerDateTime());
}
}
}
catch (Exception ex)
{
logger.Fatal(ex, "Tour Package enquiry email error" + " - " + DateTime.Now.ToLoggerDateTime());
}
return(successSend);
}
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("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("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.");
}
private static void GenerateReport(FlightSearchBatch batch)
{
try
{
log.Debug("Generate Report Started.");
List <FlightSearchResult> results = db.FlightSearchResults.Where(x => x.BatchID == batch.BatchID).ToList();
List <FlightSearchResult> searchPeriod = results.OrderBy(x => x.OutboundDepartureDate).GroupBy(x => x.OutboundDepartureDate).Select(x => x.First()).ToList();
List <CheapeastModel> cheapestGroup = results
.GroupBy(x => new
{
x.AirlineCode,
x.OutboundDepartureDate,
x.ResBookDesignCode
})
.Select(y => new CheapeastModel
{
OutboundDepartureDate = y.Key.OutboundDepartureDate,
AirlineCode = y.Key.AirlineCode + " (" + y.Key.ResBookDesignCode + ")",
TotalPrice = y.Min(z => z.TotalPrice)
})
.ToList();
//export into excell file
//string FileName = @"F:\AlpharedsTFS\Mayflower\FrontEnd\ApplicationSource\BatchPrograms\SabreFlightSearchHandler\bin\Debug\" + System.DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx";
//FileInfo workbook = new FileInfo(FileName);
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Result ");
int counter = 1;
for (int col = 1; col < searchPeriod.Count() * 2 + 1; col += 2)
{
worksheet.SetValue(1, col + 1, searchPeriod[col - counter].OutboundDepartureDate.Value.ToString("dd MMM yyyy"));
worksheet.Cells[1, col + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[1, col + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[1, col + 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DarkGray);
worksheet.Cells[1, col + 1].Style.Font.Color.SetColor(System.Drawing.Color.White);
worksheet.Cells[1, col + 1].Style.Font.Bold = true;
worksheet.Cells[1, col + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells[1, col + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[1, col + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[1, col + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells.AutoFitColumns();
worksheet.Cells[1, col + 1, 1, col + 2].Merge = true;
worksheet.SetValue(2, col + 1, "Airline");
worksheet.Cells[2, col + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[2, col + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[2, col + 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DarkGray);
worksheet.Cells[2, col + 1].Style.Font.Color.SetColor(System.Drawing.Color.White);
worksheet.Cells[2, col + 1].Style.Font.Bold = true;
worksheet.Cells[2, col + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells[2, col + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[2, col + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[2, col + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells.AutoFitColumns();
worksheet.SetValue(2, col + 2, "Total Price");
worksheet.Cells[2, col + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[2, col + 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[2, col + 2].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DarkGray);
worksheet.Cells[2, col + 2].Style.Font.Color.SetColor(System.Drawing.Color.White);
worksheet.Cells[2, col + 2].Style.Font.Bold = true;
worksheet.Cells[2, col + 2].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells[2, col + 2].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[2, col + 2].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[2, col + 2].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells.AutoFitColumns();
List <CheapeastModel> cheapestGroupForTheDay = cheapestGroup.Where(x => x.OutboundDepartureDate == searchPeriod[col - counter].OutboundDepartureDate).OrderBy(x => x.TotalPrice).ToList();
for (int row = 3; row < cheapestGroupForTheDay.Count() + 3; row++)
{
if (worksheet.Cells[row, 1].Value == null)
{
worksheet.SetValue(row, 1, (row - 2).ToString());
worksheet.Cells[row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[row, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DarkGray);
worksheet.Cells[row, 1].Style.Font.Color.SetColor(System.Drawing.Color.White);
worksheet.Cells[row, 1].Style.Font.Bold = true;
}
worksheet.SetValue(row, col + 1, cheapestGroupForTheDay[row - 3].AirlineCode);
worksheet.Cells[row, col + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells[row, col + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[row, col + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[row, col + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.SetValue(row, col + 2, cheapestGroupForTheDay[row - 3].TotalPrice);
worksheet.Cells[row, col + 2].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[row, col + 2].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[row, col + 2].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells.AutoFitColumns();
}
counter++;
}
List <FlightSearchBatch> list = new List <FlightSearchBatch>();
list.Add(batch);
var worksheet2 = package.Workbook.Worksheets.Add("Condition");
worksheet2.SetValue(1, 1, "BatchID");
worksheet2.SetValue(1, 2, "From");
worksheet2.SetValue(1, 3, "To");
worksheet2.SetValue(1, 4, "SeachStayDuration");
worksheet2.SetValue(1, 5, "SearchPeriodFrom");
worksheet2.SetValue(1, 6, "SearchPeriodTo");
worksheet2.SetValue(1, 7, "PreferredAirlineCode");
worksheet2.SetValue(1, 8, "TotalAdult");
worksheet2.SetValue(1, 9, "TotalChild");
worksheet2.SetValue(1, 10, "TotalInfant");
worksheet2.SetValue(1, 11, "CabinClass");
worksheet2.SetValue(2, 1, batch.BatchID);
worksheet2.SetValue(2, 2, batch.FROM);
worksheet2.SetValue(2, 3, batch.TO);
worksheet2.SetValue(2, 4, batch.SeachStayDuration);
worksheet2.SetValue(2, 5, batch.SeachPeriodFrom.Value.ToString("dd MMM yyyy"));
worksheet2.SetValue(2, 6, batch.SeachPeriodTo.Value.ToString("dd MMM yyyy"));
worksheet2.SetValue(2, 7, batch.PreferredAirlineCode);
worksheet2.SetValue(2, 8, batch.TotalAdult);
worksheet2.SetValue(2, 9, batch.TotalChild);
worksheet2.SetValue(2, 10, batch.TotalInfant);
worksheet2.SetValue(2, 11, batch.CabinClass);
worksheet2.Cells.AutoFitColumns();
//package.Save();
var stream = new MemoryStream(package.GetAsByteArray());
List <Attachment> attachments = new List <Attachment>();
attachments.Add(new Attachment(stream, batch.BatchID.ToString() + "_" + System.DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx"));
CommonServiceController.SendEmail(Core.GetAppSettingValueEnhanced("ReportingEmail").ToString(), Core.GetAppSettingValueEnhanced("ReportingEmailCC").ToString(), Core.GetAppSettingValueEnhanced("ReportingEmailBCC").ToString(), batch.BatchID.ToString() + " - Sabre Flight Search Result (" + batch.FROM + batch.TO + "_" + batch.SeachPeriodFrom.Value.ToString("ddMMMyyyy") + "_" + batch.SeachPeriodTo.Value.ToString("ddMMMyyyy"),
"Please find the attached. <br/>" +
"BatchID:" + batch.BatchID + "<br/>" +
"From:" + batch.FROM + "<br/>" +
"To:" + batch.TO + "<br/>" +
"SeachStayDuration:" + batch.SeachStayDuration + "<br/>" +
"SearchPeriodFrom:" + batch.SeachPeriodFrom.Value.ToString("dd MMM yyyy") + "<br/>" +
"SearchPeriodTo:" + batch.SeachPeriodTo.Value.ToString("dd MMM yyyy") + "<br/>" +
"PreferredAirlineCode:" + batch.PreferredAirlineCode + "<br/>" +
"TotalAdult:" + batch.TotalAdult + "<br/>" +
"TotalChild:" + batch.TotalChild + "<br/>" +
"TotalInfant:" + batch.TotalInfant + "<br/>" +
"CabinClass:" + batch.CabinClass + "<br/>"
, attachments);
}
log.Debug("Generate Report Completed.");
}
catch (Exception ex)
{
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.");
}