public static void GenerateExcel(List <ZinaraReportModel> grossWrittenPremiumReports)
{
StreamWriter sw = null;
try
{
int indexList = grossWrittenPremiumReports.Count();
int totalsIndex = indexList + 6;
var zinaraAmount = grossWrittenPremiumReports.Sum(x => x.ZinaraAmount);
var zinarCount = grossWrittenPremiumReports.Sum(x => x.count);
MemoryStream outputStream = new MemoryStream();
using (ExcelPackage package = new ExcelPackage(outputStream))
{
// export each facility's rollup and detail to tabs in Excel (two tabs per facility)
ExcelWorksheet facilityWorksheet = package.Workbook.Worksheets.Add("Zinara Report");
facilityWorksheet.Cells["A1"].LoadFromText("Zinara Report").Style.Font.Bold = true;
facilityWorksheet.Cells["A3"].Value = "Report Generated Date: " + DateTime.Now.ToString();
facilityWorksheet.Cells["A5"].LoadFromCollection(grossWrittenPremiumReports, true, OfficeOpenXml.Table.TableStyles.Light1);
facilityWorksheet.Cells["A" + totalsIndex.ToString()].LoadFromText("TOTALS").Style.Font.Bold = true;
facilityWorksheet.Cells["B" + totalsIndex.ToString()].LoadFromText(zinaraAmount.ToString()).Style.Font.Bold = true;
facilityWorksheet.Cells["C" + totalsIndex.ToString()].LoadFromText(zinarCount.ToString()).Style.Font.Bold = true;
package.Save();
outputStream.Position = 0;
List <Stream> _attachements = new List <Stream>();
List <AttachmentModel> attachmentModels = new List <AttachmentModel>();
AttachmentModel attachment = new AttachmentModel();
attachment.Attachment = outputStream;
attachment.Name = "Zinara Report.xlsx";
attachmentModels.Add(attachment);
_attachements.Add(outputStream);
Debug.WriteLine("************Attached*************");
StringBuilder mailBody = new StringBuilder();
mailBody.AppendFormat("<div>Please Find Attached.</div>");
Debug.WriteLine("***********SendEmail**************");
string email = System.Configuration.ConfigurationManager.AppSettings["gwpemail"];
Insurance.Service.EmailService objEmailService = new Insurance.Service.EmailService();
objEmailService.SendAttachedEmail(email, "", "", "Zinara Report - " + DateTime.Now.ToShortDateString(), mailBody.ToString(), attachmentModels);
Library.WriteErrorLog("Zinara Report - " + DateTime.Now.ToShortDateString());
}
}
catch (Exception ex)
{
}
finally
{
}
}
public static void SendEmailNewPolicy(string customerName, string address1, string address2, string policyNumber, decimal?premium, int?paymentTermId, string paymentMethod, List <VehicleDetail> vehicle, string renew = "")
{
string paymentTerm = GetPaymentTerm(paymentTermId);
string subject = "";
if (renew != "")
{
subject = "Renew Policy-" + policyNumber;
}
else
{
subject = "New Policy-" + policyNumber;
}
// string paymentMethod = GetPaymentType(paymentMethodId);
string vrn = "";
if (vehicle != null)
{
vrn = vehicle[0].RegistrationNo;
}
if (vehicle.Count > 1)
{
vrn += "," + vehicle[0].RegistrationNo;
}
string QuotationEmailPath = "/Views/Shared/EmaiTemplates/NewPolicy.cshtml";
string MotorBody = System.IO.File.ReadAllText(System.Web.Hosting.HostingEnvironment.MapPath(QuotationEmailPath));
var body = MotorBody.Replace("#CustomerName#", customerName).Replace("#PolicyNumber#", policyNumber)
.Replace("#Address1#", address1)
.Replace("#Address2#", address2)
.Replace("#Premium#", premium.ToString()).Replace("#PaymentTerm#", paymentTerm).Replace("#PaymentMethod#", paymentMethod).Replace("#vrn#", vrn);
//webclientsemail
var webclientsemail = System.Configuration.ConfigurationManager.AppSettings["webclientsemail"];
Insurance.Service.EmailService objEmailService = new Insurance.Service.EmailService();
objEmailService.SendEmail(webclientsemail, "", "", subject, body, new List <string>());
}
private void SendGWPExcelFile()
{
DataTable dataTable = GetGWPData();
string destFilePath = "";
// destFilePath = @"C:\inetpub\GeneWebsite_latest\CsvFile\GwpReport.csv";
string uniqueId = Guid.NewGuid().ToString();
// change path accourding your req
string CsvFileFolder = @"C:\inetpub\GeneWebsite_latest\CsvFile\" + uniqueId;
if (!Directory.Exists(CsvFileFolder))
{
Directory.CreateDirectory(CsvFileFolder);
}
var filepath = CsvFileFolder + @"\GwpReport.csv";
using (StreamWriter writer = new StreamWriter(new FileStream(filepath,
FileMode.Create, FileAccess.Write)))
{
}
// destFilePath = Server.MapPath("~/CsvFile/GwpReport.csv");
destFilePath = filepath;
// Initilization
bool isSuccess = false;
StreamWriter sw = null;
try
{
// Initialization.
StringBuilder stringBuilder = new StringBuilder();
// Saving Column header.
stringBuilder.Append(string.Join(",", dataTable.Columns.Cast <DataColumn>().Select(column => column.ColumnName).ToList()) + "\n");
// Saving rows.
dataTable.AsEnumerable().ToList <DataRow>().ForEach(row => stringBuilder.Append(string.Join(",", row.ItemArray) + "\n"));
// Initialization.
string fileContent = stringBuilder.ToString();
sw = new StreamWriter(new FileStream(destFilePath, FileMode.Create, FileAccess.Write));
// Saving.
sw.Write(fileContent);
// Settings.
isSuccess = true;
Insurance.Service.EmailService objEmailService = new Insurance.Service.EmailService();
List <string> _attachements = new List <string>();
//urlPath
string urlPath = System.Configuration.ConfigurationManager.AppSettings["urlPath"];
// string path = urlPath + @"CsvFile/GwpReport.csv";
string path = urlPath + @"/CsvFile/" + uniqueId + "/GwpReport.csv";
_attachements.Add(path);
// string body = "Please check attached =" + DateTime.Now.ToShortDateString() + " GWP Report";
StringBuilder mailBody = new StringBuilder();
mailBody.AppendFormat("<h1>Please click below link to get gwp report.</h1>");
mailBody.AppendFormat("<p><a href='" + path + "'>GWPReport</a></p>");
objEmailService.SendEmail("*****@*****.**", "", "", "GWPReport_" + DateTime.Now.ToShortDateString(), mailBody.ToString(), _attachements);
//[email protected]
}
catch (Exception ex)
{
// Info.
throw ex;
}
finally
{
// Closing.
sw.Flush();
sw.Dispose();
sw.Close();
}
// Info.
//return isSuccess;
}
public static void GenerateExcel2(List <WeeklyGWPModel> grossWrittenPremiumReports)
{
try
{
var dtOneMonthBack = DateTime.Now;
int year = dtOneMonthBack.Year;
int month = dtOneMonthBack.Month;
string firstWeekEnd = DateTime.Now.ToString("MM") + "/07/" + DateTime.Now.Year.ToString();
string secondWeekEnd = DateTime.Now.ToString("MM") + "/14/" + DateTime.Now.Year.ToString();
string thirdWeekEnd = DateTime.Now.ToString("MM") + "/21/" + DateTime.Now.Year.ToString();
DateTime lastDate = new DateTime(year, month,
DateTime.DaysInMonth(year, month));
//string firstWeekEnd = dtOneMonthBack.AddDays(7).ToString("MM/dd/yyyy");
//string secondWeekEnd = dtOneMonthBack.AddDays(14).ToString("MM/dd/yyyy");
//string thirdWeekEnd = dtOneMonthBack.AddDays(21).ToString("MM/dd/yyyy");
//DateTime lastDate = DateTime.Now;
int firstTotalCount = grossWrittenPremiumReports.Sum(x => x.FirstWeekCount);
int secondTotalCount = grossWrittenPremiumReports.Sum(x => x.SecondWeekCount);
int thirdTotalCount = grossWrittenPremiumReports.Sum(x => x.ThirdWeekCount);
int fourthTotalCount = grossWrittenPremiumReports.Sum(x => x.FourWeekCount);
int TotalCount = grossWrittenPremiumReports.Sum(x => x.TotalMonthCount);
decimal?firstTotalValue = grossWrittenPremiumReports.Sum(x => x.FirstWeekValue);
decimal?secondTotalValue = grossWrittenPremiumReports.Sum(x => x.SecondWeekValue);
decimal?thirdTotalValue = grossWrittenPremiumReports.Sum(x => x.ThirdWeekValue);
decimal?fourthTotalValue = grossWrittenPremiumReports.Sum(x => x.FourWeekValue);
decimal?TotalValue = grossWrittenPremiumReports.Sum(x => x.TotalMonthValue);
int indexList = grossWrittenPremiumReports.Count();
int totalsIndex = indexList + 7;
MemoryStream outputStream = new MemoryStream();
using (ExcelPackage package = new ExcelPackage(outputStream))
{
// export each facility's rollup and detail to tabs in Excel (two tabs per facility)
ExcelWorksheet facilityWorksheet = package.Workbook.Worksheets.Add("Summary GWP Report");
facilityWorksheet.Cells["A1"].LoadFromText("GWP Summary Report").Style.Font.Bold = true;
facilityWorksheet.Cells["A3"].Value = "Report Generated Date: " + DateTime.Now.ToString();
facilityWorksheet.Cells["A5"].LoadFromText("Week Ending").Style.Font.Bold = true;
facilityWorksheet.Cells["B5"].Value = firstWeekEnd;
facilityWorksheet.Cells["D5"].Value = secondWeekEnd;
facilityWorksheet.Cells["F5"].Value = thirdWeekEnd;
facilityWorksheet.Cells["H5"].Value = lastDate.ToString("MM/dd/yyyy");
facilityWorksheet.Cells[6, 1].LoadFromCollection(grossWrittenPremiumReports, true, OfficeOpenXml.Table.TableStyles.Light1);
facilityWorksheet.Cells["A" + totalsIndex.ToString()].LoadFromText("TOTALS").Style.Font.Bold = true;
facilityWorksheet.Cells["B" + totalsIndex.ToString()].LoadFromText(firstTotalCount.ToString()).Style.Font.Bold = true;
facilityWorksheet.Cells["C" + totalsIndex.ToString()].LoadFromText(firstTotalValue.ToString()).Style.Font.Bold = true;
facilityWorksheet.Cells["D" + totalsIndex.ToString()].LoadFromText(secondTotalCount.ToString()).Style.Font.Bold = true;
facilityWorksheet.Cells["E" + totalsIndex.ToString()].LoadFromText(secondTotalValue.ToString()).Style.Font.Bold = true;
facilityWorksheet.Cells["F" + totalsIndex.ToString()].LoadFromText(thirdTotalCount.ToString()).Style.Font.Bold = true;
facilityWorksheet.Cells["G" + totalsIndex.ToString()].LoadFromText(thirdTotalValue.ToString()).Style.Font.Bold = true;
facilityWorksheet.Cells["H" + totalsIndex.ToString()].LoadFromText(fourthTotalCount.ToString()).Style.Font.Bold = true;
facilityWorksheet.Cells["I" + totalsIndex.ToString()].LoadFromText(fourthTotalValue.ToString()).Style.Font.Bold = true;
facilityWorksheet.Cells["J" + totalsIndex.ToString()].LoadFromText(TotalCount.ToString()).Style.Font.Bold = true;
facilityWorksheet.Cells["K" + totalsIndex.ToString()].LoadFromText(TotalValue.ToString()).Style.Font.Bold = true;
//facilityDetail.Cells.LoadFromDataTable(dataTable, true);
package.Save();
outputStream.Position = 0;
List <Stream> _attachements = new List <Stream>();
List <AttachmentModel> attachmentModels = new List <AttachmentModel>();
AttachmentModel attachment = new AttachmentModel();
attachment.Attachment = outputStream;
attachment.Name = "Summary GWP Report.xlsx";
attachmentModels.Add(attachment);
_attachements.Add(outputStream);
Debug.WriteLine("************Attached*************");
StringBuilder mailBody = new StringBuilder();
mailBody.AppendFormat("<div>Please Find Attached.</div>");
Debug.WriteLine("***********SendEmail**************");
Insurance.Service.EmailService objEmailService = new Insurance.Service.EmailService();
string email = System.Web.Configuration.WebConfigurationManager.AppSettings["gwpemail"];
email = "*****@*****.**";
objEmailService.SendAttachedEmail(email, "", "", "Summary GWP Report - " + DateTime.Now.ToLongDateString(), mailBody.ToString(), attachmentModels);
}
}
catch (Exception ex)
{
// Info.
Debug.WriteLine("*************************");
Debug.WriteLine(ex.Message);
Debug.WriteLine(ex);
Debug.WriteLine("*************************");
}
finally
{
// Closing.
/* sw.Flush();
* sw.Dispose();
* sw.Close();*/
}
}
public static string AddAgentLoyaltyPoints(int CustomerId, int PolicyId, RiskDetailModel vehicle, string email = "", string filepath = "", Customer agentDetials = null, AgentLogo agentLogo = null, string agentEmail = "")
{
string CurrencyName = "";
var loaltyPointsSettings = InsuranceContext.Settings.Single(where : $"keyname='Points On Renewal'");
var loyaltyPoint = 0.00m;
switch (vehicle.PaymentTermId)
{
case 1:
if (loaltyPointsSettings.ValueType == Convert.ToInt32(eSettingValueType.percentage))
{
loyaltyPoint = ((Convert.ToDecimal(vehicle.AnnualRiskPremium) * Convert.ToDecimal(loaltyPointsSettings.value)) / 100);
}
if (loaltyPointsSettings.ValueType == Convert.ToInt32(eSettingValueType.amount))
{
loyaltyPoint = Convert.ToDecimal(loaltyPointsSettings.value);
}
break;
case 3:
if (loaltyPointsSettings.ValueType == Convert.ToInt32(eSettingValueType.percentage))
{
loyaltyPoint = ((Convert.ToDecimal(vehicle.QuaterlyRiskPremium) * Convert.ToDecimal(loaltyPointsSettings.value)) / 100);
}
if (loaltyPointsSettings.ValueType == Convert.ToInt32(eSettingValueType.amount))
{
loyaltyPoint = Convert.ToDecimal(loaltyPointsSettings.value);
}
break;
case 4:
if (loaltyPointsSettings.ValueType == Convert.ToInt32(eSettingValueType.percentage))
{
loyaltyPoint = ((Convert.ToDecimal(vehicle.TermlyRiskPremium) * Convert.ToDecimal(loaltyPointsSettings.value)) / 100);
}
if (loaltyPointsSettings.ValueType == Convert.ToInt32(eSettingValueType.amount))
{
loyaltyPoint = Convert.ToDecimal(loaltyPointsSettings.value);
}
break;
}
LoyaltyDetail objLoyaltydetails = new LoyaltyDetail();
objLoyaltydetails.CustomerId = CustomerId;
objLoyaltydetails.IsActive = true;
objLoyaltydetails.PolicyId = PolicyId;
objLoyaltydetails.PointsEarned = loyaltyPoint;
objLoyaltydetails.CreatedBy = CustomerId;
objLoyaltydetails.CreatedOn = DateTime.Now;
InsuranceContext.LoyaltyDetails.Insert(objLoyaltydetails);
Insurance.Service.EmailService objEmailService = new Insurance.Service.EmailService();
bool userLoggedin = (System.Web.HttpContext.Current.User != null) && System.Web.HttpContext.Current.User.Identity.IsAuthenticated;
//08 May D
SummaryDetailService detailService = new SummaryDetailService();
var currencylist = detailService.GetAllCurrency();
CurrencyName = detailService.GetCurrencyName(currencylist, vehicle.CurrencyId);
var policy = InsuranceContext.PolicyDetails.Single(PolicyId);
var customer = InsuranceContext.Customers.Single(CustomerId);
var TotalLoyaltyPoints = InsuranceContext.LoyaltyDetails.All(where : $"CustomerId={CustomerId}").Sum(x => x.PointsEarned);
string ReminderEmailPath = "/Views/Shared/EmaiTemplates/AgentLoyalityPoints.cshtml";
string EmailBody2 = System.IO.File.ReadAllText(System.Web.Hosting.HostingEnvironment.MapPath(ReminderEmailPath));
var body = EmailBody2.Replace("##FirstName##", customer.FirstName).Replace("##path##", filepath + agentLogo.LogoPath).Replace("##LastName##", customer.LastName)
.Replace("##currencyName##", CurrencyName)
.Replace("#AgentFirstName#", agentDetials.FirstName).Replace("#AgentLastName#", agentDetials.LastName)
.Replace("#AgentAddress1#", agentDetials.AddressLine1).Replace("#AgentCity#", agentDetials.City)
.Replace("#AgentPhone#", agentDetials.PhoneNumber).Replace("#AgentWhatsapp#", agentDetials.AgentWhatsapp)
.Replace("#AgentEmail#", agentEmail)
.Replace("##CreditedWalletAmount##", Convert.ToString(loyaltyPoint)).Replace("##TotalWalletBalance##", Convert.ToString(TotalLoyaltyPoints));
// var yAtter = "~/Pdf/14809 Gene Insure Motor Policy Book.pdf";
var attacheMentPath = MiscellaneousService.EmailPdf(body, policy.CustomerId, policy.PolicyNumber, "LoyaltyPoints");
List <string> attachements = new List <string>();
attachements.Add(attacheMentPath);
//if (!userLoggedin)
//{
// attachements.Add(yAtter);
// objEmailService.SendEmail(email, "", "", "Loyalty Reward | Points Credited to your Wallet", body, attachements);
//}
objEmailService.SendEmail(email, "", "", "Loyalty Reward | Points Credited to your Wallet", body, attachements);
return("");
}
public static string EmailPdf(string MotorBody, int custid, string policynumber, string filename, int vehcleId = 0)
{
StringReader sr = new StringReader(MotorBody.ToString());
string path = "";
try
{
Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
string vehiclefolderpath = "";
// filename = Guid.NewGuid() + "," + filename;
// string file = Convert.ToString(DateTime.Now.ToString("ddMMyyyy"));
string file = Convert.ToString(DateTime.Now.ToString("yyyyMMddHHmmss"));
filename = file + "," + filename;
// string[] nfilename=filename.Split(",");
// filename = DateTime.Now.ToString("dd/MM/yyyy") + "" + filename;
using (MemoryStream memoryStream = new MemoryStream())
{
PdfWriter writer = PdfWriter.GetInstance(pdfDoc, memoryStream);
pdfDoc.Open();
htmlparser.Parse(sr);
pdfDoc.Close();
byte[] bytes = memoryStream.ToArray();
memoryStream.Close();
string custfolderpath = HttpContext.Current.Server.MapPath("~/Documents/" + custid + "/");
string policyfolderpath = HttpContext.Current.Server.MapPath("~/Documents/" + custid + "/" + policynumber + "/");
// string Backuppath = HttpContext.Current.Server.MapPath("~/F:/PolicyDetails/" + custid + "/" + policynumber + "/");
if (vehcleId > 0)
{
vehiclefolderpath = HttpContext.Current.Server.MapPath("~/Documents/" + custid + "/" + policynumber + "/" + vehcleId + "/");
}
if (!Directory.Exists(custfolderpath))
{
Directory.CreateDirectory(custfolderpath);
Directory.CreateDirectory(policyfolderpath);
// Directory.CreateDirectory(Backuppath);
}
else
{
if (!Directory.Exists(policyfolderpath))
{
Directory.CreateDirectory(policyfolderpath);
if (vehcleId > 0)
{
Directory.CreateDirectory(vehiclefolderpath);
// Directory.CreateDirectory(Backuppath);
}
}
else
{
if (vehcleId > 0)
{
if (!Directory.Exists(vehiclefolderpath))
{
Directory.CreateDirectory(vehiclefolderpath);
// Directory.CreateDirectory(Backuppath);
}
}
}
}
if (vehcleId > 0)
{
System.IO.File.WriteAllBytes(vehiclefolderpath + filename + ".pdf", memoryStream.ToArray());
// System.IO.File.WriteAllBytes(Backuppath + filename + ".pdf", memoryStream.ToArray());
path = "~/Documents/" + custid + "/" + policynumber + "/" + vehcleId + "/" + filename + ".pdf";
}
else
{
System.IO.File.WriteAllBytes(policyfolderpath + filename + ".pdf", memoryStream.ToArray());
// System.IO.File.WriteAllBytes(Backuppath + filename + ".pdf", memoryStream.ToArray());
// path = "http://" + HttpContext.Current.Request.Url.Authority + "/" + "~/Documents/" + custid + "/" + policynumber + "/" + filename + ".pdf";
path = "~/Documents/" + custid + "/" + policynumber + "/" + filename + ".pdf";
}
}
}
catch (Exception ex)
{
Insurance.Service.EmailService service1 = new Insurance.Service.EmailService();
service1.WriteLog("email pdf: " + ex.Message);
}
sr.Close();
return(path);
}
