Send email notification using due date condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Send email notification using due date condition

Author

Topic

scantoria
Starting Member

15 Posts

Posted - 2009-07-23 : 15:31:41

The email notification class is created and working. I need to create a query that will give me all tickets that are due 5 days from current date. Then I need to send an email to the assigned to user email address. I need to show ticket_id, assign_to, due_date. What if the query contains 5 ticket, 3 to one user and 2 to another. How do I send only 2 email notifications instead of 5?

Thanks,

Stephen Cantoria
scantoria@msn.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 15:57:04

Can you show us what you have done so far please?

No, you're never too old to Yak'n'Roll if you're too young to die.

scantoria
Starting Member

15 Posts

Posted - 2009-07-23 : 16:17:28

I created a windows service and using a timer to trigger the email notification once daily.
Below are the project code scripts:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.ServiceProcess;
using System.Text;
using System.IO;
using System.Timers;

namespace IRISEmailNotifier
{
public partial class IRISEmailNotifier : ServiceBase
{
private System.Timers.Timer timer = new System.Timers.Timer();

public IRISEmailNotifier()
{
InitializeComponent();
}

protected override void OnStart(string[] args)
{
try
{
LogEntry le = new LogEntry("Start - " + DateTime.Now.ToString() + "\n");
ActivateTimer();
}
catch (Exception ex)
{
LogEntry le = new LogEntry(ex.ToString());
}
}

protected override void OnStop()
{
try
{
LogEntry le = new LogEntry("Stop - " + DateTime.Now.ToString() + "\n");
this.timer.Stop();
}
catch (Exception ex)
{
LogEntry le = new LogEntry(ex.ToString());
}
}

private void ActivateTimer()
{
LogEntry le = new LogEntry("Activate Timer - " + DateTime.Now.ToString() + "\n");

this.timer.Interval = 900000; //84400000; //24 hours
this.timer.Elapsed += new ElapsedEventHandler(onTimer_Elapsed);
this.timer.Start();
}

void onTimer_Elapsed(object sender, ElapsedEventArgs e)
{
//throw new Exception("The method or operation is not implemented.");

try
{
LogEntry le = new LogEntry("Timer Elapsed - " + DateTime.Now.ToString() + "\n");

SendEmailNotification();
}
catch (Exception ex)
{
LogEntry le = new LogEntry(ex.ToString());
}
}

private void SendEmailNotification()
{
string strFrom;
string strTo;
string strBcc;
string strSubject;
string strBody;
try
{
/// get information to send
///

/// Send email notification
///
LogEntry le = new LogEntry("Before Send Email - " + DateTime.Now.ToString() + "\n");

strFrom = "heops.support@heops.com";
strTo = "heops.support@heops.com";
strBcc = "SCantoria@heops.com";
strSubject = "Test Email Notification every 24 hours";
strBody = "<html><head><title></title></head>" +
"<body>Test email! HEOPS, Inc. " +
"(615) 376-4677 heops.support@heops.com" +
"</body></html>";

EmailSender email = new EmailSender(strFrom, strTo, strBcc, strSubject, strBody);

LogEntry le1 = new LogEntry("After Send Email - " + DateTime.Now.ToString() + "\n");
}
catch (Exception ex)
{
LogEntry le = new LogEntry(ex.ToString());
}
}
}
}

===============================
email class
===============================
using System;
using System.Data;
using System.Configuration;
using System.Net.Mail;

namespace IRISEmailNotifier
{
class EmailSender
{
// private members
string m_strFrom;
string m_strTo;
string m_strCc;
string m_strBcc;
string m_strSubject;
string m_strBody;

//empty constructor
public EmailSender()
{
//
// TODO: Add constructor logic here
//
}

// SendEmail1 constructor
public EmailSender(string From, string To, string Subject, string Body)
{
this.m_strFrom = From;
this.m_strTo = To;
this.m_strSubject = Subject;
this.m_strBody = Body;

/// instantiate MailMessage class
///
MailMessage mail = new MailMessage();

/// set addresses
///
mail.From = new MailAddress(m_strFrom);
mail.To.Add(m_strTo);

/// set mail content
///
mail.Subject = m_strSubject;
mail.Body = m_strBody;

/// set mail body type to HTML
///
AlternateView htmlmail = AlternateView.CreateAlternateViewFromString(m_strBody, null, "text/html");
mail.AlternateViews.Add(htmlmail);

/// instantiate SmtpClient class
/// send mail to defined smtp server
///
SmtpClient smtp = new SmtpClient("heops-s01.heops.local");
smtp.Send(mail);
}

// SendEmail2 constructor
public EmailSender(string From, string To, string Bcc,
string Subject, string Body)
{
this.m_strFrom = From;
this.m_strTo = To;
this.m_strBcc = Bcc;
this.m_strSubject = Subject;
this.m_strBody = Body;

/// instantiate MailMessage class
///
MailMessage mail = new MailMessage();

/// set addresses
///
mail.From = new MailAddress(m_strFrom);
mail.To.Add(m_strTo);
mail.Bcc.Add(m_strBcc);

/// set mail content
///
mail.Subject = m_strSubject;
mail.Body = m_strBody;

/// set mail body type to HTML
///
AlternateView htmlmail = AlternateView.CreateAlternateViewFromString(m_strBody, null, "text/html");
mail.AlternateViews.Add(htmlmail);

/// instantiate SmtpClient class
/// send mail to defined smtp server
///
SmtpClient smtp = new SmtpClient("heops-s01.heops.local");
smtp.Send(mail);
}

// SendEmail3 constructor
public EmailSender(string From, string To, string Cc, string Bcc,
string Subject, string Body)
{
this.m_strFrom = From;
this.m_strTo = To;
this.m_strCc = Cc;
this.m_strBcc = Bcc;
this.m_strSubject = Subject;
this.m_strBody = Body;

/// instantiate MailMessage class
///
MailMessage mail = new MailMessage();

/// set addresses
///
mail.From = new MailAddress(m_strFrom);
mail.To.Add(m_strTo);
mail.CC.Add(m_strCc);
mail.Bcc.Add(m_strBcc);

/// set mail content
///
mail.Subject = m_strSubject;
mail.Body = m_strBody;

/// set mail body type to HTML
///
AlternateView htmlmail = AlternateView.CreateAlternateViewFromString(m_strBody, null, "text/html");
mail.AlternateViews.Add(htmlmail);

/// instantiate SmtpClient class
/// send mail to defined smtp server
///
SmtpClient smtp = new SmtpClient("heops-s01.heops.local");
smtp.Send(mail);
}

// public accessors
public string From
{
get { return m_strFrom; }
set { m_strFrom = value; }
}
public string To
{
get { return m_strTo; }
set { m_strTo = value; }
}
public string Cc
{
get { return m_strCc; }
set { m_strCc = value; }
}
public string Bcc
{
get{ return m_strBcc; }
set{ m_strBcc = value; }
}
public string Subject
{
get { return m_strSubject; }
set { m_strSubject = value; }
}
public string Body
{
get { return m_strBody; }
set { m_strBody = value; }
}
}
}

===============================
query for one day over due
===============================

SELECT CASE WHEN datediff(d, ticket.date_due, getdate()) BETWEEN 0 AND 1 THEN ticket.date_due END AS dueDate, ticket.assign_to, ticket.owner,
ticket.ticket_id, [user].fname, [user].lname, [user].email
FROM ticket INNER JOIN
[user] ON ticket.assign_to = [user].username
WHERE (NOT (ticket.ticket_status_code = 'Close'))

I hope this helps.

Stephen Cantoria
scantoria@msn.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources