Below is my code to check customerid have any bill, if have send mail with excel but it is sending infinite loop though Customer total count is 29 it is send infinite loop
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Mail;
using System.Net;
using System.Web;
using System.Web.Services;
using System.EnterpriseServices;
using System.Data;
using System.IO;
using ClosedXML.Excel;
using System.Web.UI.WebControls;
/// <summary>
/// Summary description for WebService2
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class WebService2 : System.Web.Services.WebService
{
private WebProxy objProxy1 = null;
BusinessLogic.BusinessLogic bl_obj = new BusinessLogic.BusinessLogic();
List<string> paraname = new List<string>();
List<string> paravalue = new List<string>();
DataSet ds = new DataSet();
public WebService2()
{
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
public string HelloWorld()
{
return "Hello World";
}
[WebMethod]
public int converttodaysweb(int day, int month, int year)
{
DateTime dt = new DateTime(year, month, day);
int datetodays = DateTime.Now.Subtract(dt).Days;
return datetodays;
}
[WebMethod]
public int Add(int x, int y)
{
return x + y;
}
[WebMethod]
public string Hello()
{
return "Smart Coding";
}
[WebMethod]
public bool SendEmail()
{
try
{
DataSet dss = getdefaultCustomer();
if (dss.Tables[0].Rows.Count > 0)
{
string From = dss.Tables[0].Rows[0]["FromEmail"].ToString().Trim();
//Send Email with Excel attachment.
using (MailMessage mm = new MailMessage(From, From))
{
mm.Subject = "subject";
mm.Body = "Dc Sale Report Excel Attachment";
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = dss.Tables[0].Rows[0]["EmailHost"].ToString().Trim();
smtp.EnableSsl = true;
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
credentials.UserName = dss.Tables[0].Rows[0]["EmailUsername"].ToString().Trim();
credentials.Password = dss.Tables[0].Rows[0]["EmailPassword"].ToString().Trim();
smtp.UseDefaultCredentials = false;
smtp.Credentials = credentials;
smtp.Port = int.Parse(dss.Tables[0].Rows[0]["EmailPort"].ToString());
smtp.Send(mm);
// Log the response from the Windows service
LogRequestTime(true);
return true;
}
}
else
{
// Log the error or handle it appropriately
return false; // Add this return statement
}
}
catch (Exception ex)
{
// Log the exception or handle it appropriately
return false;
}
}
//public bool SendEmails(string responseFromServer)
//{
// try
// {
// DataSet dss = new DataSet();
// dss = getdefaultCustomer();
// if (dss.Tables[0].Rows.Count > 0)
// {
// string From = dss.Tables[0].Rows[0]["FromEmail"].ToString().Trim();
// //Send Email with Excel attachment.
// using (MailMessage mm = new MailMessage(From, From))
// {
// mm.Subject = "subject";
// mm.Body = "Dc Sale Report Excel Attachment";
// mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "GridView.xlsx"));
// mm.IsBodyHtml = true;
// SmtpClient smtp = new SmtpClient();
// smtp.Host = dss.Tables[0].Rows[0]["EmailHost"].ToString().Trim();
// smtp.EnableSsl = true;
// System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
// credentials.UserName = dss.Tables[0].Rows[0]["EmailUsername"].ToString().Trim();
// credentials.Password = dss.Tables[0].Rows[0]["EmailPassword"].ToString().Trim();
// smtp.UseDefaultCredentials = false;
// smtp.Credentials = credentials;
// smtp.Port = int.Parse(dss.Tables[0].Rows[0]["EmailPort"].ToString());
// smtp.Send(mm);
// LogRequestTime(responseFromServer);
// return true;
// }
// }
// }
// catch (Exception ex)
// {
// // Log the exception or handle it appropriately
// return false;
// }
//}
private void LogRequestTime(bool emailSent)
{
paraname.Clear();
paravalue.Clear();
DataSet ds = new DataSet();
ds.Clear();
paraname.Add("@LogMessage");
paravalue.Add(emailSent ? "Email sent successfully" : "Failed to send email");
paraname.Add("@flag");
paravalue.Add("HI");
DataSet Ds = new DataSet();
Ds = bl_obj.getdata_WithParavalue(paraname, paravalue, "SP_DC");
}
private DataSet getdefaultCustomer()
{
List<string> paraname = new List<string>();
List<string> paravalue = new List<string>();
DataSet ds = new DataSet();
paraname.Clear();
paravalue.Clear();
ds.Clear();
paraname.Add("@flag");
paravalue.Add("O");
ds = bl_obj.getdata_WithParavalue(paraname, paravalue, "sp_SaleInvoice");
if (ds.Tables[0].Rows.Count > 0)
{
return ds;
}
else
{
return ds;
}
}
[WebMethod]
public bool SendEmailwithcustomer()
{
var aCode = 65;
DataSet oname = ProcessCustomerWithBills();
// Create a DataTable to hold the data from the DataSet
DataTable dt2 = new DataTable();
// Check if the DataSet contains any tables
// Check if the DataSet contains any tables
if (oname.Tables.Count > 0)
{
// Assuming you want to use the first DataTable in the DataSet
DataTable dataTable = oname.Tables[0];
// Add columns to the DataTable based on the columns of the DataSet
foreach (DataColumn column in dataTable.Columns)
{
dt2.Columns.Add(column.ColumnName);
}
// Add rows to the DataTable
foreach (DataRow row in dataTable.Rows)
{
dt2.Rows.Add(row.ItemArray);
}
}
foreach (DataTable table in oname.Tables)
{
foreach (DataRow row in table.Rows)
{
dt2.Rows.Add();
for (int i = 0; i < table.Columns.Count; i++)
{
object cellValue = row[i];
// Check if the cell value is null or DBNull
if (cellValue == null || cellValue == DBNull.Value)
{
dt2.Rows[dt2.Rows.Count - 1][i] = DBNull.Value; // Set DBNull in the destination DataTable
}
else
{
string cellText = cellValue.ToString();
// Perform your cell value processing here
// For example, replacing special characters
if (cellText.Equals(" "))
{
dt2.Rows[dt2.Rows.Count - 1][i] = cellText.Replace(" ", "");
}
else if (cellText.Equals(" "))
{
dt2.Rows[dt2.Rows.Count - 1][i] = cellText.Replace(" ", "");
}
else if (cellText.Contains("&"))
{
dt2.Rows[dt2.Rows.Count - 1][i] = cellText.Replace("&", "&");
}
else
{
dt2.Rows[dt2.Rows.Count - 1][i] = cellText;
}
}
}
}
}
using (XLWorkbook wb = new XLWorkbook())
{
var ws = wb.Worksheets.Add("Worksheet");
////ws.Cell(1, 1).Value = "DCSale Report";
////ws.Cell(2, 1).Value = string.Format("Report On: {0} To {1}");
////ws.Cell(3, 1).Value = string.Format("Report Generated Date: {0}", DateTime.Now.ToString("dd/MM/yyyy"));
// Add column headers
for (int i = 0; i < dt2.Columns.Count; i++)
{
ws.Cell(6, i + 1).Value = dt2.Columns[i].ColumnName;
}
// Add data rows
for (int i = 0; i < dt2.Rows.Count; i++)
{
for (int j = 0; j < dt2.Columns.Count; j++)
{
ws.Cell(i + 7, j + 1).Value = dt2.Rows[i][j];
}
}
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
byte[] bytes = MyMemoryStream.ToArray();
// Send Email with Excel attachment.
DataSet dss = getdefaultCustomer();
if (dss.Tables[0].Rows.Count > 0)
{
string From = dss.Tables[0].Rows[0]["FromEmail"].ToString().Trim();
using (MailMessage mm = new MailMessage(From, From))
{
mm.Subject = "Dc Sale Report";
mm.Body = "Dc Sale Report Excel Attachment";
//Add Byte array as Attachment.
mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "DCSale_Report.xlsx"));
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = dss.Tables[0].Rows[0]["EmailHost"].ToString().Trim();
smtp.EnableSsl = true;
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
credentials.UserName = dss.Tables[0].Rows[0]["EmailUsername"].ToString().Trim();
credentials.Password = dss.Tables[0].Rows[0]["EmailPassword"].ToString().Trim();
smtp.UseDefaultCredentials = false;
smtp.Credentials = credentials;
smtp.Port = int.Parse(dss.Tables[0].Rows[0]["EmailPort"].ToString());
smtp.Send(mm);
LogRequestTime(true);
}
}
MyMemoryStream.Close();
}
}
return true;
}
//using (XLWorkbook wb = new XLWorkbook())
//{
// var ws = wb.Worksheets.Add("Worksheet");
// ws.Cell(1, 1).Value = oname;
// ws.Cell(2, 1).Value = "DCSale Report";
// ws.Cell(3, 1).Value = string.Format("Report On: {0} To {1}", txt_from.Text, txtTodate.Text);
// ws.Cell(4, 1).Value = string.Format("Report Generated Date: {0}", DateTime.Now.ToString("dd/MM/yyyy"));
// // Add column headers
// for (int i = 0; i < dt2.Columns.Count; i++)
// {
// ws.Cell(6, i + 1).Value = dt2.Columns[i].ColumnName;
// }
// // Add data rows
// for (int i = 0; i < dt2.Rows.Count; i++)
// {
// for (int j = 0; j < dt2.Columns.Count; j++)
// {
// ws.Cell(i + 7, j + 1).Value = dt2.Rows[i][j];
// }
// }
// using (MemoryStream MyMemoryStream = new MemoryStream())
// {
// wb.SaveAs(MyMemoryStream);
// byte[] bytes = MyMemoryStream.ToArray();
// // string attachmentBytes = Mobile.ToString().Trim();
// // string Message = str.ToString().Trim();
// // smcpi.SendEmail(attachmentBytes);
// //MyMemoryStream.WriteTo(Response.OutputStream);
// DataSet dss = new DataSet();
// dss = getdefaultCustomer();
// if (dss.Tables[0].Rows.Count > 0)
// {
// string From = dss.Tables[0].Rows[0]["FromEmail"].ToString().Trim();
// //Send Email with Excel attachment.
// using (MailMessage mm = new MailMessage(From, From))
// {
// mm.Subject = "Dc Sale Report";
// mm.Body = "Dc Sale Report Excel Attachment";
// //Add Byte array as Attachment.
// mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "GridView.xlsx"));
// mm.IsBodyHtml = true;
// SmtpClient smtp = new SmtpClient();
// smtp.Host = dss.Tables[0].Rows[0]["EmailHost"].ToString().Trim();
// smtp.EnableSsl = true;
// System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
// credentials.UserName = dss.Tables[0].Rows[0]["EmailUsername"].ToString().Trim();
// credentials.Password = dss.Tables[0].Rows[0]["EmailPassword"].ToString().Trim();
// smtp.UseDefaultCredentials = false;
// smtp.Credentials = credentials;
// smtp.Port = int.Parse(dss.Tables[0].Rows[0]["EmailPort"].ToString());
// smtp.Send(mm);
// //lbl_message.Text = "Email sent successfully.";
// // mm.Dispose();
// // smtp.Dispose();
// //Response.Flush();
// //fillgrid();
// // Response.End();
// }
// // ScheduleEmailSending();
// }
// MyMemoryStream.Close();
// }
//}
//Emsil send to customer wise
public List<string> CustomerHasBills()
{
List<string> customers = new List<string>();
try
{
// List to store parameter names and values
List<string> paraname = new List<string>();
List<string> paravalue = new List<string>();
// Clear lists and dataset
paraname.Clear();
paravalue.Clear();
// Add parameters for the SQL query
paraname.Add("@flag");
paravalue.Add("O");
// Execute the SQL query to retrieve data
DataSet ds = bl_obj.getdata_WithParavalue(paraname, paravalue, "SP_DC_Reverse");
// Check if the dataset contains any data
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
// Get the customer ID from the current row
string customerId = row["CustomerId"].ToString();
// Add the customer ID to the list
customers.Add(customerId);
}
}
// If no data is found or customer ID is empty, return null
// return null;
}
catch (Exception ex)
{
// Handle exception
throw new Exception("Error executing SQL query: " + ex.Message);
}
return customers;
}
//private DataSet ProcessCustomerWithBills()
//{
// try
// {
// // Retrieve customer ID with bills
// string customerId = CustomerHasBills();
// // If customer ID is not null, further process it
// if (customerId != null)
// {
// paraname.Clear();
// paravalue.Clear();
// ds.Clear();
// paraname.Add("@TTo");
// paravalue.Add(customerId.ToString().Trim());
// // Further process the customer ID (e.g., write data to Excel, send email)
// // Implement your logic here or call another method passing the customer ID
// // For example:
// // WriteDataToExcel(customerId);
// // SendEmailWithAttachment(customerId);
// paraname.Add("@flag");
// paravalue.Add("OK");
// ds = bl_obj.getdata_WithParavalue(paraname, paravalue, "SP_DC_Reverse");
// if (ds.Tables[0].Rows.Count > 0)
// {
// return ds;
// }
// else
// {
// return ds;
// }
// }
// else
// {
// // No customer with bills found
// // Handle this scenario accordingly
// return ds;
// }
// }
// catch (Exception ex)
// {
// // Handle exception
// throw new Exception("Error processing customer with bills: " + ex.Message);
// }
//}
[WebMethod]
public DataSet ProcessCustomerWithBills()
{
try
{
bool anyCustomerWithBills = false; // Declare the flag outside the loop
// Keep looping until a customer with bills is found or no more customers are left
while (true)
{
// Retrieve customer ID with bills
List<string> customerIds = CustomerHasBills();
// string customerId = CustomerHasBills();
// If no customer with bills found, return null
if (customerIds == null || customerIds.Count == 0)
{
return null;
}
foreach (string customerId in customerIds)
{
// Further process the customer ID (e.g., generate report)
paraname.Clear();
paravalue.Clear();
ds.Clear();
paraname.Add("@TTo");
paravalue.Add(customerId.ToString().Trim());
// Further process the customer ID (e.g., generate report)
paraname.Add("@flag");
paravalue.Add("OK");
ds = bl_obj.getdata_WithParavalue(paraname, paravalue, "SP_DC_Reverse");
// If data is found for the customer, return the report data
if (ds.Tables[0].Rows.Count > 0)
{
byte[] reportBytes = GenerateExcelReport(ds);
// Send Email with Excel attachment
SendEmailWithAttachment(reportBytes);
anyCustomerWithBills = true;
// return ds;
}
// If no data found for the customer, continue looping to check next customer
}
if (anyCustomerWithBills)
{
return null;
}
}
}
catch (Exception ex)
{
// Handle exception
throw new Exception("Error processing customer with bills: " + ex.Message);
}
}
[WebMethod]
public void SendEmailWithAttachment(byte[] attachment)
{
try
{
// Get email settings from configuration
DataSet dss = getdefaultCustomer();
if (dss.Tables[0].Rows.Count > 0)
{
string From = dss.Tables[0].Rows[0]["FromEmail"].ToString().Trim();
using (MailMessage mm = new MailMessage(From, From))
{
mm.Subject = "Dc Sale Report";
mm.Body = "Dc Sale Report Excel Attachment";
//Add Byte array as Attachment.
mm.Attachments.Add(new Attachment(new MemoryStream(attachment), "DCSale_Report.xlsx"));
// mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "DCSale_Report.xlsx"));
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = dss.Tables[0].Rows[0]["EmailHost"].ToString().Trim();
smtp.EnableSsl = true;
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
credentials.UserName = dss.Tables[0].Rows[0]["EmailUsername"].ToString().Trim();
credentials.Password = dss.Tables[0].Rows[0]["EmailPassword"].ToString().Trim();
smtp.UseDefaultCredentials = false;
smtp.Credentials = credentials;
smtp.Port = int.Parse(dss.Tables[0].Rows[0]["EmailPort"].ToString());
smtp.Send(mm);
LogRequestTime(true);
}
}
// MyMemoryStream.Close();
// return true;
}
catch (Exception ex)
{
// Handle exception
throw new Exception("Error sending email with attachment: " + ex.Message);
}
}
private byte[] GenerateExcelReport(DataSet reportData)
{
using (XLWorkbook wb = new XLWorkbook())
{
var ws = wb.Worksheets.Add("Worksheet");
// Add column headers
foreach (DataColumn column in reportData.Tables[0].Columns)
{
ws.Cell(1, column.Ordinal + 1).Value = column.ColumnName;
}
// Add data rows
int row = 2;
foreach (DataRow dataRow in reportData.Tables[0].Rows)
{
for (int col = 0; col < reportData.Tables[0].Columns.Count; col++)
{
ws.Cell(row, col + 1).Value = dataRow[col];
}
row++;
}
using (MemoryStream memoryStream = new MemoryStream())
{
wb.SaveAs(memoryStream);
return memoryStream.ToArray();
}
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Mail;
using System.Net;
using System.Web;
using System.Web.Services;
using System.EnterpriseServices;
using System.Data;
using System.IO;
using ClosedXML.Excel;
using System.Web.UI.WebControls;
/// <summary>
/// Summary description for WebService2
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class WebService2 : System.Web.Services.WebService
{
private WebProxy objProxy1 = null;
BusinessLogic.BusinessLogic bl_obj = new BusinessLogic.BusinessLogic();
List<string> paraname = new List<string>();
List<string> paravalue = new List<string>();
DataSet ds = new DataSet();
public WebService2()
{
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
public string HelloWorld()
{
return "Hello World";
}
[WebMethod]
public int converttodaysweb(int day, int month, int year)
{
DateTime dt = new DateTime(year, month, day);
int datetodays = DateTime.Now.Subtract(dt).Days;
return datetodays;
}
[WebMethod]
public int Add(int x, int y)
{
return x + y;
}
[WebMethod]
public string Hello()
{
return "Smart Coding";
}
[WebMethod]
public bool SendEmail()
{
try
{
DataSet dss = getdefaultCustomer();
if (dss.Tables[0].Rows.Count > 0)
{
string From = dss.Tables[0].Rows[0]["FromEmail"].ToString().Trim();
//Send Email with Excel attachment.
using (MailMessage mm = new MailMessage(From, From))
{
mm.Subject = "subject";
mm.Body = "Dc Sale Report Excel Attachment";
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = dss.Tables[0].Rows[0]["EmailHost"].ToString().Trim();
smtp.EnableSsl = true;
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
credentials.UserName = dss.Tables[0].Rows[0]["EmailUsername"].ToString().Trim();
credentials.Password = dss.Tables[0].Rows[0]["EmailPassword"].ToString().Trim();
smtp.UseDefaultCredentials = false;
smtp.Credentials = credentials;
smtp.Port = int.Parse(dss.Tables[0].Rows[0]["EmailPort"].ToString());
smtp.Send(mm);
// Log the response from the Windows service
LogRequestTime(true);
return true;
}
}
else
{
// Log the error or handle it appropriately
return false; // Add this return statement
}
}
catch (Exception ex)
{
// Log the exception or handle it appropriately
return false;
}
}
//public bool SendEmails(string responseFromServer)
//{
// try
// {
// DataSet dss = new DataSet();
// dss = getdefaultCustomer();
// if (dss.Tables[0].Rows.Count > 0)
// {
// string From = dss.Tables[0].Rows[0]["FromEmail"].ToString().Trim();
// //Send Email with Excel attachment.
// using (MailMessage mm = new MailMessage(From, From))
// {
// mm.Subject = "subject";
// mm.Body = "Dc Sale Report Excel Attachment";
// mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "GridView.xlsx"));
// mm.IsBodyHtml = true;
// SmtpClient smtp = new SmtpClient();
// smtp.Host = dss.Tables[0].Rows[0]["EmailHost"].ToString().Trim();
// smtp.EnableSsl = true;
// System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
// credentials.UserName = dss.Tables[0].Rows[0]["EmailUsername"].ToString().Trim();
// credentials.Password = dss.Tables[0].Rows[0]["EmailPassword"].ToString().Trim();
// smtp.UseDefaultCredentials = false;
// smtp.Credentials = credentials;
// smtp.Port = int.Parse(dss.Tables[0].Rows[0]["EmailPort"].ToString());
// smtp.Send(mm);
// LogRequestTime(responseFromServer);
// return true;
// }
// }
// }
// catch (Exception ex)
// {
// // Log the exception or handle it appropriately
// return false;
// }
//}
private void LogRequestTime(bool emailSent)
{
paraname.Clear();
paravalue.Clear();
DataSet ds = new DataSet();
ds.Clear();
paraname.Add("@LogMessage");
paravalue.Add(emailSent ? "Email sent successfully" : "Failed to send email");
paraname.Add("@flag");
paravalue.Add("HI");
DataSet Ds = new DataSet();
Ds = bl_obj.getdata_WithParavalue(paraname, paravalue, "SP_DC");
}
private DataSet getdefaultCustomer()
{
List<string> paraname = new List<string>();
List<string> paravalue = new List<string>();
DataSet ds = new DataSet();
paraname.Clear();
paravalue.Clear();
ds.Clear();
paraname.Add("@flag");
paravalue.Add("O");
ds = bl_obj.getdata_WithParavalue(paraname, paravalue, "sp_SaleInvoice");
if (ds.Tables[0].Rows.Count > 0)
{
return ds;
}
else
{
return ds;
}
}
[WebMethod]
public bool SendEmailwithcustomer()
{
var aCode = 65;
DataSet oname = ProcessCustomerWithBills();
// Create a DataTable to hold the data from the DataSet
DataTable dt2 = new DataTable();
// Check if the DataSet contains any tables
// Check if the DataSet contains any tables
if (oname.Tables.Count > 0)
{
// Assuming you want to use the first DataTable in the DataSet
DataTable dataTable = oname.Tables[0];
// Add columns to the DataTable based on the columns of the DataSet
foreach (DataColumn column in dataTable.Columns)
{
dt2.Columns.Add(column.ColumnName);
}
// Add rows to the DataTable
foreach (DataRow row in dataTable.Rows)
{
dt2.Rows.Add(row.ItemArray);
}
}
foreach (DataTable table in oname.Tables)
{
foreach (DataRow row in table.Rows)
{
dt2.Rows.Add();
for (int i = 0; i < table.Columns.Count; i++)
{
object cellValue = row[i];
// Check if the cell value is null or DBNull
if (cellValue == null || cellValue == DBNull.Value)
{
dt2.Rows[dt2.Rows.Count - 1][i] = DBNull.Value; // Set DBNull in the destination DataTable
}
else
{
string cellText = cellValue.ToString();
// Perform your cell value processing here
// For example, replacing special characters
if (cellText.Equals(" "))
{
dt2.Rows[dt2.Rows.Count - 1][i] = cellText.Replace(" ", "");
}
else if (cellText.Equals(" "))
{
dt2.Rows[dt2.Rows.Count - 1][i] = cellText.Replace(" ", "");
}
else if (cellText.Contains("&"))
{
dt2.Rows[dt2.Rows.Count - 1][i] = cellText.Replace("&", "&");
}
else
{
dt2.Rows[dt2.Rows.Count - 1][i] = cellText;
}
}
}
}
}
using (XLWorkbook wb = new XLWorkbook())
{
var ws = wb.Worksheets.Add("Worksheet");
////ws.Cell(1, 1).Value = "DCSale Report";
////ws.Cell(2, 1).Value = string.Format("Report On: {0} To {1}");
////ws.Cell(3, 1).Value = string.Format("Report Generated Date: {0}", DateTime.Now.ToString("dd/MM/yyyy"));
// Add column headers
for (int i = 0; i < dt2.Columns.Count; i++)
{
ws.Cell(6, i + 1).Value = dt2.Columns[i].ColumnName;
}
// Add data rows
for (int i = 0; i < dt2.Rows.Count; i++)
{
for (int j = 0; j < dt2.Columns.Count; j++)
{
ws.Cell(i + 7, j + 1).Value = dt2.Rows[i][j];
}
}
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
byte[] bytes = MyMemoryStream.ToArray();
// Send Email with Excel attachment.
DataSet dss = getdefaultCustomer();
if (dss.Tables[0].Rows.Count > 0)
{
string From = dss.Tables[0].Rows[0]["FromEmail"].ToString().Trim();
using (MailMessage mm = new MailMessage(From, From))
{
mm.Subject = "Dc Sale Report";
mm.Body = "Dc Sale Report Excel Attachment";
//Add Byte array as Attachment.
mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "DCSale_Report.xlsx"));
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = dss.Tables[0].Rows[0]["EmailHost"].ToString().Trim();
smtp.EnableSsl = true;
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
credentials.UserName = dss.Tables[0].Rows[0]["EmailUsername"].ToString().Trim();
credentials.Password = dss.Tables[0].Rows[0]["EmailPassword"].ToString().Trim();
smtp.UseDefaultCredentials = false;
smtp.Credentials = credentials;
smtp.Port = int.Parse(dss.Tables[0].Rows[0]["EmailPort"].ToString());
smtp.Send(mm);
LogRequestTime(true);
}
}
MyMemoryStream.Close();
}
}
return true;
}
//using (XLWorkbook wb = new XLWorkbook())
//{
// var ws = wb.Worksheets.Add("Worksheet");
// ws.Cell(1, 1).Value = oname;
// ws.Cell(2, 1).Value = "DCSale Report";
// ws.Cell(3, 1).Value = string.Format("Report On: {0} To {1}", txt_from.Text, txtTodate.Text);
// ws.Cell(4, 1).Value = string.Format("Report Generated Date: {0}", DateTime.Now.ToString("dd/MM/yyyy"));
// // Add column headers
// for (int i = 0; i < dt2.Columns.Count; i++)
// {
// ws.Cell(6, i + 1).Value = dt2.Columns[i].ColumnName;
// }
// // Add data rows
// for (int i = 0; i < dt2.Rows.Count; i++)
// {
// for (int j = 0; j < dt2.Columns.Count; j++)
// {
// ws.Cell(i + 7, j + 1).Value = dt2.Rows[i][j];
// }
// }
// using (MemoryStream MyMemoryStream = new MemoryStream())
// {
// wb.SaveAs(MyMemoryStream);
// byte[] bytes = MyMemoryStream.ToArray();
// // string attachmentBytes = Mobile.ToString().Trim();
// // string Message = str.ToString().Trim();
// // smcpi.SendEmail(attachmentBytes);
// //MyMemoryStream.WriteTo(Response.OutputStream);
// DataSet dss = new DataSet();
// dss = getdefaultCustomer();
// if (dss.Tables[0].Rows.Count > 0)
// {
// string From = dss.Tables[0].Rows[0]["FromEmail"].ToString().Trim();
// //Send Email with Excel attachment.
// using (MailMessage mm = new MailMessage(From, From))
// {
// mm.Subject = "Dc Sale Report";
// mm.Body = "Dc Sale Report Excel Attachment";
// //Add Byte array as Attachment.
// mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "GridView.xlsx"));
// mm.IsBodyHtml = true;
// SmtpClient smtp = new SmtpClient();
// smtp.Host = dss.Tables[0].Rows[0]["EmailHost"].ToString().Trim();
// smtp.EnableSsl = true;
// System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
// credentials.UserName = dss.Tables[0].Rows[0]["EmailUsername"].ToString().Trim();
// credentials.Password = dss.Tables[0].Rows[0]["EmailPassword"].ToString().Trim();
// smtp.UseDefaultCredentials = false;
// smtp.Credentials = credentials;
// smtp.Port = int.Parse(dss.Tables[0].Rows[0]["EmailPort"].ToString());
// smtp.Send(mm);
// //lbl_message.Text = "Email sent successfully.";
// // mm.Dispose();
// // smtp.Dispose();
// //Response.Flush();
// //fillgrid();
// // Response.End();
// }
// // ScheduleEmailSending();
// }
// MyMemoryStream.Close();
// }
//}
//Emsil send to customer wise
public List<string> CustomerHasBills()
{
List<string> customers = new List<string>();
try
{
// List to store parameter names and values
List<string> paraname = new List<string>();
List<string> paravalue = new List<string>();
// Clear lists and dataset
paraname.Clear();
paravalue.Clear();
// Add parameters for the SQL query
paraname.Add("@flag");
paravalue.Add("O");
// Execute the SQL query to retrieve data
DataSet ds = bl_obj.getdata_WithParavalue(paraname, paravalue, "SP_DC_Reverse");
// Check if the dataset contains any data
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
// Get the customer ID from the current row
string customerId = row["CustomerId"].ToString();
// Add the customer ID to the list
customers.Add(customerId);
}
}
// If no data is found or customer ID is empty, return null
// return null;
}
catch (Exception ex)
{
// Handle exception
throw new Exception("Error executing SQL query: " + ex.Message);
}
return customers;
}
//private DataSet ProcessCustomerWithBills()
//{
// try
// {
// // Retrieve customer ID with bills
// string customerId = CustomerHasBills();
// // If customer ID is not null, further process it
// if (customerId != null)
// {
// paraname.Clear();
// paravalue.Clear();
// ds.Clear();
// paraname.Add("@TTo");
// paravalue.Add(customerId.ToString().Trim());
// // Further process the customer ID (e.g., write data to Excel, send email)
// // Implement your logic here or call another method passing the customer ID
// // For example:
// // WriteDataToExcel(customerId);
// // SendEmailWithAttachment(customerId);
// paraname.Add("@flag");
// paravalue.Add("OK");
// ds = bl_obj.getdata_WithParavalue(paraname, paravalue, "SP_DC_Reverse");
// if (ds.Tables[0].Rows.Count > 0)
// {
// return ds;
// }
// else
// {
// return ds;
// }
// }
// else
// {
// // No customer with bills found
// // Handle this scenario accordingly
// return ds;
// }
// }
// catch (Exception ex)
// {
// // Handle exception
// throw new Exception("Error processing customer with bills: " + ex.Message);
// }
//}
[WebMethod]
public DataSet ProcessCustomerWithBills()
{
try
{
bool anyCustomerWithBills = false; // Declare the flag outside the loop
// Keep looping until a customer with bills is found or no more customers are left
while (true)
{
// Retrieve customer ID with bills
List<string> customerIds = CustomerHasBills();
// string customerId = CustomerHasBills();
// If no customer with bills found, return null
if (customerIds == null || customerIds.Count == 0)
{
return null;
}
foreach (string customerId in customerIds)
{
// Further process the customer ID (e.g., generate report)
paraname.Clear();
paravalue.Clear();
ds.Clear();
paraname.Add("@TTo");
paravalue.Add(customerId.ToString().Trim());
// Further process the customer ID (e.g., generate report)
paraname.Add("@flag");
paravalue.Add("OK");
ds = bl_obj.getdata_WithParavalue(paraname, paravalue, "SP_DC_Reverse");
// If data is found for the customer, return the report data
if (ds.Tables[0].Rows.Count > 0)
{
byte[] reportBytes = GenerateExcelReport(ds);
// Send Email with Excel attachment
SendEmailWithAttachment(reportBytes);
anyCustomerWithBills = true;
// return ds;
}
// If no data found for the customer, continue looping to check next customer
}
if (anyCustomerWithBills)
{
return null;
}
}
}
catch (Exception ex)
{
// Handle exception
throw new Exception("Error processing customer with bills: " + ex.Message);
}
}
[WebMethod]
public void SendEmailWithAttachment(byte[] attachment)
{
try
{
// Get email settings from configuration
DataSet dss = getdefaultCustomer();
if (dss.Tables[0].Rows.Count > 0)
{
string From = dss.Tables[0].Rows[0]["FromEmail"].ToString().Trim();
using (MailMessage mm = new MailMessage(From, From))
{
mm.Subject = "Dc Sale Report";
mm.Body = "Dc Sale Report Excel Attachment";
//Add Byte array as Attachment.
mm.Attachments.Add(new Attachment(new MemoryStream(attachment), "DCSale_Report.xlsx"));
// mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "DCSale_Report.xlsx"));
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = dss.Tables[0].Rows[0]["EmailHost"].ToString().Trim();
smtp.EnableSsl = true;
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
credentials.UserName = dss.Tables[0].Rows[0]["EmailUsername"].ToString().Trim();
credentials.Password = dss.Tables[0].Rows[0]["EmailPassword"].ToString().Trim();
smtp.UseDefaultCredentials = false;
smtp.Credentials = credentials;
smtp.Port = int.Parse(dss.Tables[0].Rows[0]["EmailPort"].ToString());
smtp.Send(mm);
LogRequestTime(true);
}
}
// MyMemoryStream.Close();
// return true;
}
catch (Exception ex)
{
// Handle exception
throw new Exception("Error sending email with attachment: " + ex.Message);
}
}
private byte[] GenerateExcelReport(DataSet reportData)
{
using (XLWorkbook wb = new XLWorkbook())
{
var ws = wb.Worksheets.Add("Worksheet");
// Add column headers
foreach (DataColumn column in reportData.Tables[0].Columns)
{
ws.Cell(1, column.Ordinal + 1).Value = column.ColumnName;
}
// Add data rows
int row = 2;
foreach (DataRow dataRow in reportData.Tables[0].Rows)
{
for (int col = 0; col < reportData.Tables[0].Columns.Count; col++)
{
ws.Cell(row, col + 1).Value = dataRow[col];
}
row++;
}
using (MemoryStream memoryStream = new MemoryStream())
{
wb.SaveAs(memoryStream);
return memoryStream.ToArray();
}
}
}
}