Exemple #1
0
        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;
        }
Exemple #2
0
        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");
            }
        }