private void SendGWPExcelFile()
{
DataTable dataTable = GetGWPData();
string destFilePath = "";
// destFilePath = @"C:\inetpub\GeneWebsite_latest\CsvFile\GwpReport.csv";
string uniqueId = Guid.NewGuid().ToString();
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;
CustomerLogic objsmsemail = new CustomerLogic();
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>");
string ccList = "[email protected], [email protected], [email protected], [email protected]";
objsmsemail.SendEmail("*****@*****.**", ccList, "", "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 void ReminderEmailSms()
{
try
{
Library.WriteErrorLog("EMAIL/SMS window service started");
//string body = ReadEmailMessage();
string filepath = System.Configuration.ConfigurationManager.AppSettings["urlPath"];
//Library.WriteErrorLog("Get File path");
CustomerLogic objsmsemail = new CustomerLogic();
string connectionString = System.Configuration.ConfigurationManager.AppSettings["Insurance"].ToString();
SqlConnection con = new SqlConnection(connectionString);
con.Open();
//DataTable LicenceTickets = GetLicenceTickets();
//Library.WriteErrorLog("LicenceTickets");
DataTable queryString = GetEmailDetail();
DataTable SmsList = GetSMSDetail();
//DataTable VehicleModels = Getvehiclemodel();
//DataTable LicenceTickets = GetLicenceTickets();
//var now = DateTime.Now;
foreach (DataRow dr in queryString.Rows)
{
//VehicleDetail model = new VehicleDetail();
List <VehicleDetail> vehicalList = new List <VehicleDetail>();
Library.WriteErrorLog("Read the Email records and number of records=" + dr.RowState);
//string connectionString = System.Configuration.ConfigurationManager.AppSettings["Insurance"].ToString();
var query = "Select VehicleDetail.Id, VehicleDetail.SumInsured, VehicleDetail.Premium, VehicleDetail.RenewalDate, VehicleMake.MakeDescription, VehicleModel.ModelDescription, Customer.UserID, Customer.FirstName, Customer.LastName, Customer.AddressLine1, Customer.AddressLine2, Customer.IsCustomEmail, PolicyDetail.PolicyNumber, AspNetUsers.Email, AspNetUsers.PhoneNumber";
query += " FROM VehicleDetail INNER JOIN ";
query += " VehicleMake ON VehicleDetail.MakeId = VehicleMake.MakeCode INNER JOIN ";
query += " VehicleModel ON VehicleDetail.ModelId = VehicleModel.ModelCode INNER JOIN";
query += " Customer ON VehicleDetail.CustomerId = Customer.Id INNER JOIN";
query += " PolicyDetail ON VehicleDetail.PolicyId = PolicyDetail.Id AND Customer.Id = PolicyDetail.CustomerId INNER JOIN";
query += " AspNetUsers ON Customer.UserID = AspNetUsers.Id ";
query += " Where CONVERT(varchar,VehicleDetail.RenewalDate, 101) = CONVERT(varchar, DATEADD(day, " + dr["NoOfDays"] + ", GETDATE()), 101) and LicExpiryDate is null";
//SqlCommand cmd = new SqlCommand(query, con);
//Library.WriteErrorLog("Read the records and number of records=" + query);
using (SqlCommand command = new SqlCommand(query, con))
{
SqlDataReader reader = command.ExecuteReader();
// Call Read before accessing data.
while (reader.Read())
{
VehicleDetail model = new VehicleDetail();
model.VehicalId = reader["Id"] == null ? 0 : Convert.ToInt32(reader["Id"]);
model.RenewalDate = reader["RenewalDate"] == null ? DateTime.MinValue : Convert.ToDateTime(reader["RenewalDate"]);
model.MakeDescription = reader["MakeDescription"] == null ? "" : Convert.ToString(reader["MakeDescription"]);
model.ModelDescription = reader["ModelDescription"] == null ? "" : Convert.ToString(reader["ModelDescription"]);
model.FirstName = reader["FirstName"] == null ? "" : Convert.ToString(reader["FirstName"]);
model.LastName = reader["LastName"] == null ? "" : Convert.ToString(reader["LastName"]);
model.AddressLine1 = reader["AddressLine1"] == null ? "" : Convert.ToString(reader["AddressLine1"]);
model.AddressLine2 = reader["AddressLine1"] == null ? "" : Convert.ToString(reader["AddressLine1"]);
model.PolicyNumber = reader["PolicyNumber"] == null ? "" : Convert.ToString(reader["PolicyNumber"]);
model.UserId = reader["UserId"] == null ? "" : Convert.ToString(reader["UserId"]);
model.Email = reader["Email"] == null ? "" : Convert.ToString(reader["Email"]);
model.PhoneNumber = reader["PhoneNumber"] == null ? "" : Convert.ToString(reader["PhoneNumber"]);
model.IsCustomEmail = reader["IsCustomEmail"] == null ? false : Convert.ToBoolean(reader["IsCustomEmail"]);
//message = (string)reader["Message"];
//Library.WriteErrorLog("mesage :" + message);
vehicalList.Add(model);
}
reader.Close();
}
foreach (var item in vehicalList)
{
Library.WriteErrorLog("Read the records and number of records=" + item.UserId);
var paths = (AppDomain.CurrentDomain.BaseDirectory + @"EmailTemplate\RenewalReminderEmail.html");
//string ReminderEmailPath = "/SmaService/SmsService/SmsService/EmailTemplate/RenewalReminderEmail.html";
string EmailBody2 = System.IO.File.ReadAllText(paths);
var _body = EmailBody2.Replace("##RenewDate##", item.RenewalDate.ToString("dd/MM/yyyy")).Replace("##path##", filepath).Replace("##FirstName##", item.FirstName).Replace("##LastName##", item.LastName).Replace("##PolicyNumber##", item.PolicyNumber).Replace("##Make##", item.MakeDescription).Replace("##Model##", item.ModelDescription).Replace("##Address1##", item.AddressLine1).Replace("##Address2##", item.AddressLine2).Replace("##numberofDays##", "" + dr["NoOfDays"] + "").Replace("##PolicyNumber##", item.PolicyNumber);
var email = item.Email;
try
{
if (!item.IsCustomEmail)
{
objsmsemail.SendEmail(email, "", "", "Renew/Repay Next Term Premium of Your Policy | " + dr["NoOfDays"] + " ", _body, null);
}
//Library.WriteErrorLog("EMAIL send successfully");
}
catch (Exception ex)
{
//ReminderFailed(body, user.Email, "Renew/Repay Next Term Premium of Your Policy | 21 Days Left", Convert.ToInt32(ePolicyRenewReminderType.Email));
Library.WriteErrorLog("EMAIL send unsuccessfully");
}
}
vehicalList.Clear();
}
foreach (DataRow dr in SmsList.Rows)
{
Library.WriteErrorLog("Read the SMS records and number of records=" + dr.RowState);
List <VehicleDetail> _vehicalList = new List <VehicleDetail>();
var query = "Select VehicleDetail.Id, VehicleDetail.SumInsured, VehicleDetail.Premium, VehicleDetail.RenewalDate, VehicleMake.MakeDescription, VehicleModel.ModelDescription, Customer.UserID, Customer.FirstName, Customer.LastName, Customer.AddressLine1, Customer.AddressLine2, PolicyDetail.PolicyNumber, AspNetUsers.Email,Customer.CountryCode, AspNetUsers.PhoneNumber";
query += " FROM VehicleDetail INNER JOIN ";
query += " VehicleMake ON VehicleDetail.MakeId = VehicleMake.MakeCode INNER JOIN ";
query += " VehicleModel ON VehicleDetail.ModelId = VehicleModel.ModelCode INNER JOIN";
query += " Customer ON VehicleDetail.CustomerId = Customer.Id INNER JOIN";
query += " PolicyDetail ON VehicleDetail.PolicyId = PolicyDetail.Id AND Customer.Id = PolicyDetail.CustomerId INNER JOIN";
query += " AspNetUsers ON Customer.UserID = AspNetUsers.Id ";
query += " Where CONVERT(varchar,VehicleDetail.RenewalDate, 101) = CONVERT(varchar, DATEADD(day, " + dr["NoOfDays"] + ", GETDATE()), 101) and LicExpiryDate is null";
using (SqlCommand command = new SqlCommand(query, con))
{
SqlDataReader reader = command.ExecuteReader();
// Call Read before accessing data.
while (reader.Read())
{
VehicleDetail _models = new VehicleDetail();
_models.VehicalId = reader["Id"] == null ? 0 : Convert.ToInt32(reader["Id"]);
_models.RenewalDate = reader["RenewalDate"] == null ? DateTime.MinValue : Convert.ToDateTime(reader["RenewalDate"]);
_models.MakeDescription = reader["MakeDescription"] == null ? "" : Convert.ToString(reader["MakeDescription"]);
_models.ModelDescription = reader["ModelDescription"] == null ? "" : Convert.ToString(reader["ModelDescription"]);
_models.FirstName = reader["FirstName"] == null ? "" : Convert.ToString(reader["FirstName"]);
_models.LastName = reader["LastName"] == null ? "" : Convert.ToString(reader["LastName"]);
_models.AddressLine1 = reader["AddressLine1"] == null ? "" : Convert.ToString(reader["AddressLine1"]);
_models.AddressLine2 = reader["AddressLine1"] == null ? "" : Convert.ToString(reader["AddressLine1"]);
_models.PolicyNumber = reader["PolicyNumber"] == null ? "" : Convert.ToString(reader["PolicyNumber"]);
_models.UserId = reader["UserId"] == null ? "" : Convert.ToString(reader["UserId"]);
_models.Email = reader["Email"] == null ? "" : Convert.ToString(reader["Email"]);
_models.PhoneNumber = reader["PhoneNumber"] == null ? "" : Convert.ToString(reader["PhoneNumber"]);
_models.CountryCode = reader["CountryCode"] == null ? "" : Convert.ToString(reader["CountryCode"]);
_vehicalList.Add(_models);
}
reader.Close();
}
foreach (var item in _vehicalList)
{
// string _body = "Hello " + item.FirstName + "\nYour Vehicle " + item.MakeDescription + " " + item.ModelDescription + " will Expire in " + dr["NoOfDays"] + " days i.e on " + item.RenewalDate + ". Please Renew/Repay for your next Payment Term before the Renewal date of " + item.RenewalDate + " to continue your services otherwise your vehicle will get Lapsed." + "\nThank you.";
string _body = "Dear Customer, Please be advised that your motor vehicle insurance is due for renewals. Our renewals team will call you to assist with the renewal process.";
try
{
//objsmsemail.SendSMS(_models.PhoneNumber, _body);
objsmsemail.SendSMS(item.CountryCode.Replace("+", "") + item.PhoneNumber.TrimStart('0'), _body);
Library.WriteErrorLog("SMS send successfully");
}
catch (Exception ex)
{
Library.WriteErrorLog("SMS send unsuccessfully");
}
}
_vehicalList.Clear();
}
//for licensing
foreach (DataRow dr in queryString.Rows)
{
//VehicleDetail model = new VehicleDetail();
List <VehicleDetail> vehicalList = new List <VehicleDetail>();
Library.WriteErrorLog("Read the Email records and number of records=" + dr.RowState);
//string connectionString = System.Configuration.ConfigurationManager.AppSettings["Insurance"].ToString();
var query = "Select VehicleDetail.Id, VehicleDetail.SumInsured, VehicleDetail.Premium, VehicleDetail.RenewalDate, VehicleMake.MakeDescription, VehicleModel.ModelDescription, Customer.UserID, Customer.FirstName, Customer.LastName, Customer.AddressLine1, Customer.AddressLine2, Customer.IsCustomEmail, PolicyDetail.PolicyNumber, AspNetUsers.Email, AspNetUsers.PhoneNumber";
query += " FROM VehicleDetail INNER JOIN ";
query += " VehicleMake ON VehicleDetail.MakeId = VehicleMake.MakeCode INNER JOIN ";
query += " VehicleModel ON VehicleDetail.ModelId = VehicleModel.ModelCode INNER JOIN";
query += " Customer ON VehicleDetail.CustomerId = Customer.Id INNER JOIN";
query += " PolicyDetail ON VehicleDetail.PolicyId = PolicyDetail.Id AND Customer.Id = PolicyDetail.CustomerId INNER JOIN";
query += " AspNetUsers ON Customer.UserID = AspNetUsers.Id ";
query += " Where CONVERT(varchar,CONVERT(datetime, VehicleDetail.LicExpiryDate), 101) = CONVERT(varchar, DATEADD(day, " + dr["NoOfDays"] + ", GETDATE()), 101) and LicExpiryDate is not null";
//SqlCommand cmd = new SqlCommand(query, con);
//Library.WriteErrorLog("Read the records and number of records=" + query);
using (SqlCommand command = new SqlCommand(query, con))
{
SqlDataReader reader = command.ExecuteReader();
// Call Read before accessing data.
while (reader.Read())
{
VehicleDetail model = new VehicleDetail();
model.VehicalId = reader["Id"] == null ? 0 : Convert.ToInt32(reader["Id"]);
model.RenewalDate = reader["RenewalDate"] == null ? DateTime.MinValue : Convert.ToDateTime(reader["RenewalDate"]);
model.MakeDescription = reader["MakeDescription"] == null ? "" : Convert.ToString(reader["MakeDescription"]);
model.ModelDescription = reader["ModelDescription"] == null ? "" : Convert.ToString(reader["ModelDescription"]);
model.FirstName = reader["FirstName"] == null ? "" : Convert.ToString(reader["FirstName"]);
model.LastName = reader["LastName"] == null ? "" : Convert.ToString(reader["LastName"]);
model.AddressLine1 = reader["AddressLine1"] == null ? "" : Convert.ToString(reader["AddressLine1"]);
model.AddressLine2 = reader["AddressLine1"] == null ? "" : Convert.ToString(reader["AddressLine1"]);
model.PolicyNumber = reader["PolicyNumber"] == null ? "" : Convert.ToString(reader["PolicyNumber"]);
model.UserId = reader["UserId"] == null ? "" : Convert.ToString(reader["UserId"]);
model.Email = reader["Email"] == null ? "" : Convert.ToString(reader["Email"]);
model.PhoneNumber = reader["PhoneNumber"] == null ? "" : Convert.ToString(reader["PhoneNumber"]);
model.IsCustomEmail = reader["IsCustomEmail"] == null ? false : Convert.ToBoolean(reader["IsCustomEmail"]);
//message = (string)reader["Message"];
//Library.WriteErrorLog("mesage :" + message);
vehicalList.Add(model);
}
reader.Close();
}
foreach (var item in vehicalList)
{
Library.WriteErrorLog("Read the records and number of records=" + item.UserId);
var paths = (AppDomain.CurrentDomain.BaseDirectory + @"EmailTemplate\RenewalReminderEmail.html");
//string ReminderEmailPath = "/SmaService/SmsService/SmsService/EmailTemplate/RenewalReminderEmail.html";
string EmailBody2 = System.IO.File.ReadAllText(paths);
var _body = EmailBody2.Replace("##RenewDate##", item.RenewalDate.ToString("dd/MM/yyyy")).Replace("##path##", filepath).Replace("##FirstName##", item.FirstName).Replace("##LastName##", item.LastName).Replace("##PolicyNumber##", item.PolicyNumber).Replace("##Make##", item.MakeDescription).Replace("##Model##", item.ModelDescription).Replace("##Address1##", item.AddressLine1).Replace("##Address2##", item.AddressLine2).Replace("##numberofDays##", "" + dr["NoOfDays"] + "").Replace("##PolicyNumber##", item.PolicyNumber);
var email = item.Email;
try
{
if (!item.IsCustomEmail)
{
objsmsemail.SendEmail(email, "", "", "Renew/Repay Next Term Premium of Your Policy | " + dr["NoOfDays"] + " ", _body, null);
}
//Library.WriteErrorLog("EMAIL send successfully");
}
catch (Exception ex)
{
//ReminderFailed(body, user.Email, "Renew/Repay Next Term Premium of Your Policy | 21 Days Left", Convert.ToInt32(ePolicyRenewReminderType.Email));
Library.WriteErrorLog("EMAIL send unsuccessfully");
}
}
vehicalList.Clear();
}
foreach (DataRow dr in SmsList.Rows)
{
Library.WriteErrorLog("Read the SMS records and number of records=" + dr.RowState);
List <VehicleDetail> _vehicalList = new List <VehicleDetail>();
var query = "Select VehicleDetail.Id, VehicleDetail.SumInsured, VehicleDetail.Premium, VehicleDetail.RenewalDate, VehicleMake.MakeDescription, VehicleModel.ModelDescription, Customer.UserID, Customer.FirstName, Customer.LastName, Customer.AddressLine1, Customer.AddressLine2, PolicyDetail.PolicyNumber, AspNetUsers.Email,Customer.CountryCode, AspNetUsers.PhoneNumber";
query += " FROM VehicleDetail INNER JOIN ";
query += " VehicleMake ON VehicleDetail.MakeId = VehicleMake.MakeCode INNER JOIN ";
query += " VehicleModel ON VehicleDetail.ModelId = VehicleModel.ModelCode INNER JOIN";
query += " Customer ON VehicleDetail.CustomerId = Customer.Id INNER JOIN";
query += " PolicyDetail ON VehicleDetail.PolicyId = PolicyDetail.Id AND Customer.Id = PolicyDetail.CustomerId INNER JOIN";
query += " AspNetUsers ON Customer.UserID = AspNetUsers.Id ";
query += " Where CONVERT(varchar,CONVERT(datetime, VehicleDetail.LicExpiryDate), 101) = CONVERT(varchar, DATEADD(day, " + dr["NoOfDays"] + ", GETDATE()), 101) and LicExpiryDate is not null";
using (SqlCommand command = new SqlCommand(query, con))
{
SqlDataReader reader = command.ExecuteReader();
// Call Read before accessing data.
while (reader.Read())
{
VehicleDetail _models = new VehicleDetail();
_models.VehicalId = reader["Id"] == null ? 0 : Convert.ToInt32(reader["Id"]);
_models.RenewalDate = reader["RenewalDate"] == null ? DateTime.MinValue : Convert.ToDateTime(reader["RenewalDate"]);
_models.MakeDescription = reader["MakeDescription"] == null ? "" : Convert.ToString(reader["MakeDescription"]);
_models.ModelDescription = reader["ModelDescription"] == null ? "" : Convert.ToString(reader["ModelDescription"]);
_models.FirstName = reader["FirstName"] == null ? "" : Convert.ToString(reader["FirstName"]);
_models.LastName = reader["LastName"] == null ? "" : Convert.ToString(reader["LastName"]);
_models.AddressLine1 = reader["AddressLine1"] == null ? "" : Convert.ToString(reader["AddressLine1"]);
_models.AddressLine2 = reader["AddressLine1"] == null ? "" : Convert.ToString(reader["AddressLine1"]);
_models.PolicyNumber = reader["PolicyNumber"] == null ? "" : Convert.ToString(reader["PolicyNumber"]);
_models.UserId = reader["UserId"] == null ? "" : Convert.ToString(reader["UserId"]);
_models.Email = reader["Email"] == null ? "" : Convert.ToString(reader["Email"]);
_models.PhoneNumber = reader["PhoneNumber"] == null ? "" : Convert.ToString(reader["PhoneNumber"]);
_models.CountryCode = reader["CountryCode"] == null ? "" : Convert.ToString(reader["CountryCode"]);
_vehicalList.Add(_models);
}
reader.Close();
}
foreach (var item in _vehicalList)
{
// string _body = "Hello " + item.FirstName + "\nYour Vehicle " + item.MakeDescription + " " + item.ModelDescription + " will Expire in " + dr["NoOfDays"] + " days i.e on " + item.RenewalDate + ". Please Renew/Repay for your next Payment Term before the Renewal date of " + item.RenewalDate + " to continue your services otherwise your vehicle will get Lapsed." + "\nThank you.";
string _body = "Dear Customer, Please be advised that your motor vehicle insurance is due for renewals. Our renewals team will call you to assist with the renewal process.";
try
{
//objsmsemail.SendSMS(_models.PhoneNumber, _body);
objsmsemail.SendSMS(item.CountryCode.Replace("+", "") + item.PhoneNumber.TrimStart('0'), _body);
Library.WriteErrorLog("SMS send successfully");
}
catch (Exception ex)
{
Library.WriteErrorLog("SMS send unsuccessfully");
}
}
_vehicalList.Clear();
}
con.Close();
Library.WriteErrorLog("EMAIL/SMS window service successful");
}
catch (Exception ex)
{
Library.WriteErrorLog("EMAIL/SMS window service unsuccessful");
}
}