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); }
private static void ProcessEmailQueue() { try { log.Debug("ProcessEmailQueue Started."); //Retrieve the email from email queue. int retryCount = Convert.ToInt32(Core.GetAppSettingValueEnhanced("retryCount")); log.Debug("Retrieve email from email queue Started."); List <EmailQueue> emailQueues = db.EmailQueues.Where(x => x.SentTries < retryCount && !x.IsSent).ToList(); log.Debug("Retrieved " + emailQueues.Count() + " email(s) from email queue."); foreach (EmailQueue q in emailQueues) { List <Attachment> attachments = new List <Attachment>(); List <EmailAttachment> emailAttachments = db.EmailAttachments.Where(x => x.Id == q.Id).OrderBy(y => y.AttachmentFileName).ToList(); foreach (EmailAttachment ea in emailAttachments) { attachments.Add(new Attachment(new MemoryStream(ea.Attachment), ea.AttachmentFileName)); } q.SentTries++; CommonServiceController.UpdateEmailQueue(q); bool isSuccess = SendMail(q.Id.ToString(), q.From, q.To, q.CC, q.Bcc, q.Subject, q.Body, attachments, q.IsBodyHtml); if (isSuccess) { log.Debug("Update Is Sent Successfully Started." + q.IsSent.ToString()); q.IsSent = true; CommonServiceController.UpdateEmailQueue(q); log.Debug("Update Is Sent Successfully Ended."); } } } catch (Exception ex) { log.Debug("Unable to complete ProcessEmailQueue."); throw ex; } }
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."); }