public void SendMail(string from_Email, string to_Email, string CCList, string body, string from_Name, string Subject, string imagePath,string bodyImagePath)
{
try
{
MailMessage mail = new MailMessage();
mail.IsBodyHtml = true;
AlternateView htmlView = AlternateView.CreateAlternateViewFromString(body, null, "text/html");
LinkedResource theEmailImage = new LinkedResource(imagePath);
theEmailImage.ContentId = "myImageID";
LinkedResource theBodyImage = new LinkedResource(bodyImagePath);
theBodyImage.ContentId = "myBodyId";
htmlView.LinkedResources.Add(theEmailImage);
htmlView.LinkedResources.Add(theBodyImage);
mail.AlternateViews.Add(htmlView);
mail.From = new MailAddress(from_Email, from_Name);
mail.To.Add(to_Email);
mail.CC.Add(CCList);
mail.Subject = Subject;
System.Net.NetworkCredential cred = new System.Net.NetworkCredential(fromEmailIdStr, password);
SmtpClient smtp = new SmtpClient(mailServerName, port);
smtp.EnableSsl = true;
smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
smtp.UseDefaultCredentials = false;
smtp.Credentials = cred;
smtp.Send(mail);
}
catch (Exception ex)
{
ExceptionLogger log = new ExceptionLogger();
log.DoLog(ex);
}
}
public List<Employees> getAllEmpDataTable()
{
DataSet recDS;
DataTable allEmpDatatable;
recDS = new DataSet();
makeConnection = new OleDbConnection(pathconn);
List<Employees> allemp = new List<Employees>();
try
{
myadapter = new OleDbDataAdapter("select * from [Sheet1$] where IsDeleted='no'", makeConnection);
myadapter.Fill(recDS, "emp");
allEmpDatatable = recDS.Tables["emp"];
empDataTable = allEmpDatatable;
foreach (DataRow row in allEmpDatatable.Rows)
{
empid = Convert.ToInt32(row["empid"]);
empname = Convert.ToString(row["empname"]);
emplastname = Convert.ToString(row["emplastname"]);
dateofbirth = Convert.ToDateTime(row["dateofbirth"]);
dateofjoining = Convert.ToDateTime(row["dateofjoining"]);
designation = Convert.ToString(row["designation"]);
empgender = Convert.ToString(row["empgender"]);
empprifix = Convert.ToString(row["empprifix"]);
empimagepath = Convert.ToString(row["empimagepath"]);
emailid = Convert.ToString(row["emailid"]);
empmiddlename = Convert.ToString(row["empmiddlename"]);
allemp.Add(new Employees(empid, empname, emplastname, dateofbirth, dateofjoining, emailid, designation, empgender, empprifix, empmiddlename, empimagepath));
}
}
catch (Exception ex)
{
ExceptionLogger log = new ExceptionLogger();
log.DoLog(ex);
}
return allemp;
}
public List<Employees> getFifteenDayIntervalBdys(int day)
{
List<Employees> allFifteenDayIntervalList = new List<Employees>();
DataSet recDS;
DataTable fifteenDaybdyTable;
string strDate = todayDate.ToString("MM");
try
{
makeConnection = new OleDbConnection(pathconn);
recDS = new DataSet();
myadapter = new OleDbDataAdapter("select * from [Sheet1$] where Format(dateofbirth,'mm') in (" + strDate + ") and IsDeleted='no'", makeConnection);
myadapter.Fill(recDS, "emp");
fifteenDaybdyTable = recDS.Tables["emp"];
foreach (DataRow row in fifteenDaybdyTable.Rows)
{
empid = Convert.ToInt32(row["empid"]);
empname = Convert.ToString(row["empname"]);
emplastname = Convert.ToString(row["emplastname"]);
dateofbirth = Convert.ToDateTime(row["dateofbirth"]);
dateofjoining = Convert.ToDateTime(row["dateofjoining"]);
designation = Convert.ToString(row["designation"]);
empgender = Convert.ToString(row["empgender"]);
empprifix = Convert.ToString(row["empprifix"]);
empimagepath = Convert.ToString(row["empimagepath"]);
emailid = Convert.ToString(row["emailid"]);
empmiddlename = Convert.ToString(row["empmiddlename"]);
if (day == 1)
{
if (dateofbirth.Day > day && dateofbirth.Day < 16)
allFifteenDayIntervalList.Add(new Employees(empid, empname, emplastname, dateofbirth, dateofjoining, emailid, designation, empgender, empprifix, empmiddlename, empimagepath));
}
if (day == 15)
{
if (dateofbirth.Day > day)
allFifteenDayIntervalList.Add(new Employees(empid, empname, emplastname, dateofbirth, dateofjoining, emailid, designation, empgender, empprifix, empmiddlename, empimagepath));
}
}
}
catch (Exception ex)
{
ExceptionLogger log = new ExceptionLogger();
log.DoLog(ex);
}
return allFifteenDayIntervalList;
}
public List<Employees> getEmpYrsCompleted(int years, DateTime todayDate)
{
DateTime today = todayDate.AddYears(-years);
string strToday = today.ToString("ddMMyyyy");
List<Employees> threeYearsEmpList = new List<Employees>();
try
{
if (today.DayOfWeek.ToString() != "Sunday" || today.DayOfWeek.ToString() != "Saturday")
{
// MessageBox.Show("holyday");
DataSet empWithThreeDS = new DataSet();
DataTable empThreeYrDT;
OleDbConnection conn = new OleDbConnection(pathconn);
if (todayDate.DayOfWeek.ToString() == "Monday")
{
strToday = "'" + strToday + "','" + today.AddDays(-1).ToString("ddMMyyyy") + "','" + today.AddDays(-2).ToString("ddMMyyyy") + "'";
}
myadapter = new OleDbDataAdapter("select * from [Sheet1$] where Format(dateofjoining,'ddmmyyyy') in (" + strToday + ")", conn);
myadapter.Fill(empWithThreeDS, "emp");
empThreeYrDT = empWithThreeDS.Tables["emp"];
foreach (DataRow row in empThreeYrDT.Rows)
{
empid = Convert.ToInt32(row["empid"]);
empname = Convert.ToString(row["empname"]);
emplastname = Convert.ToString(row["emplastname"]);
dateofbirth = Convert.ToDateTime(row["dateofbirth"]);
dateofjoining = Convert.ToDateTime(row["dateofjoining"]);
designation = Convert.ToString(row["designation"]);
empgender = Convert.ToString(row["empgender"]);
empprifix = Convert.ToString(row["empprifix"]);
empimagepath = Convert.ToString(row["empimagepath"]);
emailid = Convert.ToString(row["emailid"]);
empmiddlename = Convert.ToString(row["empmiddlename"]);
threeYearsEmpList.Add(new Employees(empid, empname, emplastname, dateofbirth, dateofjoining, emailid, designation, empgender, empprifix, empmiddlename, empimagepath));
}
}
}
catch (Exception ex)
{
ExceptionLogger log = new ExceptionLogger();
log.DoLog(ex);
}
return threeYearsEmpList;
}
public void UpdateEmployeeDetails(Employees updateEmployee)
{
OleDbConnection con = new OleDbConnection(pathconn);
try
{
con.Open();
OleDbCommand cmd = new OleDbCommand("update [Sheet1$] set empname=?, emplastname=?, dateofbirth=?, dateofjoining=?, emailid=?, designation=?, empgender=?, empprifix=?, empmiddlename=?, empimagepath=? where empid=" + updateEmployee.EmpID + "", con);
string date = DateTime.Now.ToString();
// cmd.Parameters.Add("empid", OleDbType.Integer).Value = updateEmployee.EmpID;
cmd.Parameters.Add("empname", OleDbType.VarChar).Value = updateEmployee.EmpName;
cmd.Parameters.Add("emplastname", OleDbType.VarChar).Value = updateEmployee.EmpLastName;
cmd.Parameters.Add("dateofbirth", OleDbType.Date).Value = updateEmployee.DateOfBirth;
cmd.Parameters.Add("dateofjoining", OleDbType.Date).Value = updateEmployee.DateOfJoining;
cmd.Parameters.Add("emailid", OleDbType.VarChar).Value = updateEmployee.EmpID;
cmd.Parameters.Add("designation", OleDbType.VarChar).Value = updateEmployee.Designation;
cmd.Parameters.Add("empgender", OleDbType.VarChar).Value = updateEmployee.EmpGender;
cmd.Parameters.Add("empprifix", OleDbType.VarChar).Value = updateEmployee.EmpPrefix;
cmd.Parameters.Add("empmiddlename", OleDbType.VarChar).Value = updateEmployee.EmpMiddleName;
cmd.Parameters.Add("empimagepath", OleDbType.VarChar).Value = updateEmployee.EmpImagePath;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ExceptionLogger log = new ExceptionLogger();
log.DoLog(ex);
}
finally
{
if (con.State != ConnectionState.Closed)
con.Close();
}
}
public void AddEmployee(Employees newEmployee)
{
Int32 empid = 0;
OleDbConnection con = new OleDbConnection(pathconn);
try
{
con.Open();
OleDbCommand cm2 = new OleDbCommand("select MAX(empid) from [Sheet1$]", con);
empid = Convert.ToInt32(cm2.ExecuteScalar()) + 1;
OleDbCommand cmd = new OleDbCommand("insert into [Sheet1$] (empid,empname,empmiddlename, emplastname, dateofbirth, dateofjoining, emailid, designation, empgender, empprifix, empimagepath,IsDeleted) values(?,?,?,?,?,?,?,?,?,?,?,?)", con);
string date = DateTime.Now.ToString();
cmd.Parameters.Add("empid", OleDbType.Integer).Value = empid;
cmd.Parameters.Add("empname", OleDbType.VarChar).Value = newEmployee.EmpName;
cmd.Parameters.Add("empmiddlename", OleDbType.VarChar).Value = newEmployee.EmpMiddleName;
cmd.Parameters.Add("emplastname", OleDbType.VarChar).Value = newEmployee.EmpLastName;
cmd.Parameters.Add("dateofbirth", OleDbType.Date).Value = newEmployee.DateOfBirth.Date;
cmd.Parameters.Add("dateofjoining", OleDbType.Date).Value = newEmployee.DateOfJoining.Date;
cmd.Parameters.Add("emailid", OleDbType.VarChar).Value = newEmployee.EmailId;
cmd.Parameters.Add("designation", OleDbType.VarChar).Value = newEmployee.Designation;
cmd.Parameters.Add("empgender", OleDbType.VarChar).Value = newEmployee.EmpGender;
cmd.Parameters.Add("empprifix", OleDbType.VarChar).Value = newEmployee.EmpPrefix;
cmd.Parameters.Add("empimagepath", OleDbType.VarChar).Value = newEmployee.EmpImagePath;
cmd.Parameters.Add("IsDeleted", OleDbType.VarChar).Value = "no";
cmd.ExecuteNonQuery();
Template temp = new Template();
string templatestr = temp.templateNewBieBody(newEmployee.EmpName);
string _imagePath = "";
_imagePath = (string)ConfigurationSettings.AppSettings["ImagePath"] + newEmployee.EmpImagePath;
MailingClass sendmail = new MailingClass();
//sendmail.SendMail(fromEmailID, newEmployee.EmailId, ccLIst, templatestr, "Welcome To tavisca", "Welcome To Tavisca", _imagePath, _bodyImagePath);
}
catch (Exception ex)
{
ExceptionLogger log = new ExceptionLogger();
log.DoLog(ex);
}
finally
{
if (con.State != ConnectionState.Closed)
con.Close();
}
}
public List<Employees> GetTodaysBirthdays(DateTime todayDate)
{
List<Employees> bdayList = new List<Employees>();
string strToday = todayDate.ToString("ddMM");
try
{
if (todayDate.DayOfWeek.ToString() != "Sunday" || todayDate.DayOfWeek.ToString() != "Saterday")
{
DataSet recDS = new DataSet();
DataTable bdyDataTable = new DataTable();
OleDbConnection conn = new OleDbConnection(pathconn);
if (todayDate.DayOfWeek.ToString() == "Friday")
{
strToday = "'" + strToday + "','" + todayDate.AddDays(1).ToString("ddMM") + "','" + todayDate.AddDays(2).ToString("ddMM") + "'";
}
myadapter = new OleDbDataAdapter("select * from [Sheet1$] where Format(dateofbirth,'ddmm') in (" + strToday + ") and IsDeleted='no'", conn);
myadapter.Fill(recDS, "bdayEmp");
bdyDataTable = recDS.Tables["bdayEmp"];
foreach (DataRow row in bdyDataTable.Rows)
{
empid = Convert.ToInt32(row["empid"]);
empname = Convert.ToString(row["empname"]);
emplastname = Convert.ToString(row["emplastname"]);
dateofbirth = Convert.ToDateTime(row["dateofbirth"]);
dateofjoining = Convert.ToDateTime(row["dateofjoining"]);
designation = Convert.ToString(row["designation"]);
empgender = Convert.ToString(row["empgender"]);
empprifix = Convert.ToString(row["empprifix"]);
empimagepath = Convert.ToString(row["empimagepath"]);
emailid = Convert.ToString(row["emailid"]);
empmiddlename = Convert.ToString(row["empmiddlename"]);
bdayList.Add(new Employees(empid, empname, emplastname, dateofbirth, dateofjoining, emailid, designation, empgender, empprifix, empmiddlename, empimagepath));
}
}
}
catch (Exception ex)
{
ExceptionLogger log = new ExceptionLogger();
log.DoLog(ex);
}
return bdayList;
}
public List<Employees> GetTodaysBirthdays(DateTime date)
{
List<Employees> bdayList = new List<Employees>();
todayDate = date;
string strToday = todayDate.ToString("ddMM");
try
{
if (todayDate.DayOfWeek.ToString() != "Sunday" || todayDate.DayOfWeek.ToString() != "Saterday")
{
SqlDataReader recDR;
SqlConnection sqlcon = new SqlConnection(pathconn);
if (todayDate.DayOfWeek.ToString() == "Friday")
{
strToday = "'" + strToday + "','" + todayDate.AddDays(1).ToString("ddMM") + "','" + todayDate.AddDays(2).ToString("ddMM") + "'";
}
sqlcon.Open();
SqlCommand sqlcmd = new SqlCommand("TodayBdayProcedure",sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
// sqlcmd.Parameters.Add("@listDate",SqlDbType.VarChar).Value = strToday;
recDR = sqlcmd.ExecuteReader();
if (recDR.HasRows)
{
while (recDR.Read())
{
empid = Convert.ToInt32(recDR.GetValue(0));
empname = Convert.ToString(recDR.GetString(1));
emplastname = Convert.ToString(recDR.GetString(3));
dateofbirth = Convert.ToDateTime(recDR.GetValue(4));
dateofjoining = Convert.ToDateTime(recDR.GetValue(5));
designation = Convert.ToString(recDR.GetString(10));
empgender = Convert.ToString(recDR.GetString(6));
empprifix = Convert.ToString(recDR.GetString(7));
empimagepath = Convert.ToString(recDR.GetString(11));
emailid = Convert.ToString(recDR.GetString(8));
empmiddlename = Convert.ToString(recDR.GetString(2));
bdayList.Add(new Employees(empid, empname, emplastname, dateofbirth, dateofjoining, emailid, designation, empgender, empprifix, empmiddlename, empimagepath));
}
recDR.NextResult();
}
recDR.Close();
}
}
catch (Exception ex)
{
ExceptionLogger log = new ExceptionLogger();
log.DoLog(ex);
}
return bdayList;
}
public List<Employees> getEmpYrsCompleted(int years, DateTime date)
{
DateTime today = todayDate.AddYears(-years);
string strToday = today.ToString("MM/dd/yyyy");
List<Employees> threeYearsEmpList = new List<Employees>();
try
{
if (today.DayOfWeek.ToString() != "Sunday" || today.DayOfWeek.ToString() != "Saturday")
{
SqlDataReader recDR;
SqlConnection sqlconn = new SqlConnection(pathconn);
SqlCommand sqlcmd= new SqlCommand("ThreeYearsCompletionProcedure", sqlconn);
if(years==5)
{
sqlcmd = new SqlCommand("FiveYearCompletionProcedure", sqlconn);
}
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.Add("@years", SqlDbType.Int).Value = years;
sqlconn.Open();
recDR = sqlcmd.ExecuteReader();
if (recDR.HasRows)
{
while (recDR.Read())
{
empid = Convert.ToInt32(recDR.GetValue(0));
empname = Convert.ToString(recDR.GetString(1));
empmiddlename = Convert.ToString(recDR.GetString(2));
emplastname = Convert.ToString(recDR.GetString(3));
dateofbirth = Convert.ToDateTime(recDR.GetValue(4));
dateofjoining = Convert.ToDateTime(recDR.GetValue(5));
empgender = Convert.ToString(recDR.GetString(6));
empprifix = Convert.ToString(recDR.GetString(7));
emailid = Convert.ToString(recDR.GetString(8));
designation = Convert.ToString(recDR.GetString(10));
empimagepath = Convert.ToString(recDR.GetString(11));
threeYearsEmpList.Add(new Employees(empid, empname, emplastname, dateofbirth, dateofjoining, emailid, designation, empgender, empprifix, empmiddlename, empimagepath));
}
recDR.NextResult();
}
recDR.Close();
}
}
catch (Exception ex)
{
ExceptionLogger log = new ExceptionLogger();
log.DoLog(ex);
}
return threeYearsEmpList;
}
public void SendToAdmin(string from_Email, string to_Email, string body, string from_Name, string Subject)
{
try
{
MailMessage mail = new MailMessage();
//set the HTML format to true
mail.IsBodyHtml = true;
mail.From = new MailAddress(from_Email, from_Name);
mail.Body = body;
//set the "to" email address
mail.To.Add(to_Email);
//set the Email subject
mail.Subject = Subject;
//set the SMTP info
System.Net.NetworkCredential cred = new System.Net.NetworkCredential(fromEmailIdStr, password);
SmtpClient smtp = new SmtpClient(mailServerName, port);
smtp.EnableSsl = true;
smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
smtp.UseDefaultCredentials = false;
smtp.Credentials = cred;
//send the email
smtp.Send(mail);
}
catch (Exception ex)
{
ExceptionLogger log = new ExceptionLogger();
log.DoLog(ex);
}
}