Sending Follow Up Reminders

[adamroof]

Hi all,

I have a contact database, and i'm trying to generate an email reminder to "Owners" of items they have flagged for follow up. It could be multiple items from multiple contacts per owner and could be for many owners. I am struggling with the concept to send a group of follow ups to the owners individually.

All the follow ups are recorded in a history table. How would i group them so my email routine would send out a listing to each owner?

The concept statement would be:
- For each owner that has a follow up, send them a list of all contact follow ups due today (or past due)

Should i group them in SQL returning multiple tables for each user, then with simple SqlDataReader use NextResult, or can i group them in code somehow from a single resultset?

my single SQL results table

SELECT hist.ContactID, hist.HistSchedule, hist.HistNotes,
	dbo.ContactFullName(hist.ContactID) AS ContactName,
	dbo.UserNameByID(contact.ContactOwner,1) AS Owner,
	dbo.UserEmailByID(contact.ContactOwner) AS OwnerEmail
FROM tblHistory hist
	LEFT JOIN tblContact contact ON hist.ContactID = contact.ContactID
WHERE hist.HistSchedule IS NOT NULL
	AND hist.FollowUpComplete = 0
	AND hist.HistSchedule < GetDate()
ORDER BY contact.ContactOwner, hist.HistSchedule

233	2010-01-27	Contact Request: From Web	Customer R	Adam R	adamr@domain.com
237	2009-11-13	Send quote for stuff	Keith G	Lauren B	laurenb@domain.com

 

[jmeckley]

select the users and outstanding contracts from the database. group by user. for each user send to emailing object. I like to use defined objects in my applications, so i would define a User and a Contract. a User can have many contracts. here is a real simple example

class User
{
   public string EmailAddress {get;set;}
   public ICollection<Contract> Contracts {get;set;}
}
class Contract
{
}

there is a way to do something similar with datasets, but I don't use them. it would envolve setting up 2 tables (users, contract) with a relation between them. you could then loop through each User table row and filter the contracts from the Contract table.

after loading the user and contract data from the database and populating the User and Contract objects/dataset. I would pass each user to the email service, which would send an email.

var emailer = new MyEmailService();
var users = GetUsers(); // which returns IEnumerable<User>
foreach(var user in users)
{
   emailer.Notify(user);
}

the [tt]Notify[/tt] member of [tt]MyEmailService[/tt] would build a message from the users and contracts and send the email. 1 email per user.

However, you have another issue. Daily reminders are usually considered an automated task. not something a user should have to think about sending. automated tasks are best managed by a windows service, not asp.net.

in it's simplest form you add an windows service project to your solution. add a timer to the service class. configure the timer to execute every 24 hours. define an Elapsed/Tick handler. in this handler query the database and send the emails.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[adamroof]

Can you elaborate a little on your class implementation?
Populate users from db that have follow ups, then populate class FollowUps, then how would i create the listing from within the foreach(user)?

I already have the emailing and a service running and timed and this will be in addition to that daily report.

 

[jmeckley]

there really wouldn't need to be that much more to the classes aside from additional properties and encapsulating the collection Contracts associated with a user.

Mapping objects to a database is known as Object Relational Mapping (ORM). There are a number of frameworks out there which are designed to solve the problem of mapping objects and databases. I use the framework NHibernate. There are others as well: Active Record, Entity Framework, Ling2Sql and LLBL Gen Pro. The way they work is you have a set of configuration files which map objects to the database. you can then write queries (object queries, not sql queries) against the ORM framework which will produce sql statements that are executed against the database.

If you are currently using DataTables and DataSets to load the data into memory then switching to an explicit object model may not be practicle. If you heavily leverage Stored Procedures than ORM may also not be a good option. this is why i mentioned DataSets with tables and relations. DataSets are more of a reflection of the database than the idea of a domain model. DataSets work better with procs too.






Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[adamroof]

I dont understand. I wan't to, and i am interested, however, i dont get how to populate a class with data and then use it in a loop.

It did help me come up with a concept to create a solution to my current situation. Maybe you can help me implement/improve into the class structure?

1. Create a proc that lists the owners that have follow up.

--PROCEDURE [dbo].[contact_GetFollowUpOwners]
SELECT DISTINCT contact.ContactOwner, dbo.UserEmailByID(contact.ContactOwner) AS OwnerEmail	
FROM tblHistory hist
	LEFT JOIN tblContact contact ON hist.ContactID = contact.ContactID
WHERE hist.HistSchedule IS NOT NULL
	AND hist.FollowUpComplete = 0
	AND hist.HistSchedule < GetDate()

2. Create a second proc that lists all the follow ups by owner (similar to initial proc)

ALTER PROCEDURE [dbo].[contact_GetFollowUpList]
	@Owner int
AS
SET NOCOUNT ON;

SELECT hist.ContactID, dbo.ContactFullName(hist.ContactID) AS ContactName,
	hist.HistSchedule, hist.HistNotes
FROM tblHistory hist
	LEFT JOIN tblContact contact ON hist.ContactID = contact.ContactID
WHERE hist.HistSchedule IS NOT NULL
	AND hist.FollowUpComplete = 0
	AND hist.HistSchedule < GetDate()
	AND contact.ContactOwner = @Owner
ORDER BY hist.HistSchedule

3. Create the routine to be called by the service.
- DB call to set the owners and owners email
- DB call to build a table of Follow Ups to be emailed to each owner

public static void SendFollowUpReminders()
{
    cmsData crm = new cmsData();

    int OwnerID = 0;
    string OwnerEmail = string.Empty;
    string FollowUp = string.Empty;
    string RowLinkFormat = "<a href='" + crm.SystemURL + "contact.aspx?id={0}'>{0}</a>";

    string EmailFromName = string.Empty;
    string EmailFromAddress = string.Empty;
    string Subject = string.Empty;
    string TemplateBody = string.Empty;
    string ReminderBody = string.Empty;

    string[] cmsInfo = crm.CompanyInfo();
    EmailFromName = cmsInfo[3];
    EmailFromAddress = cmsInfo[2];

    using (DataSet ds = emailTemplate(10))
    {
        Subject = ds.Tables[0].Rows[0]["templateName"].ToString();
        TemplateBody = ds.Tables[0].Rows[0]["templateContent"].ToString();
    }

    using (SqlDataReader dr = DB.drProc("contact_GetFollowUpOwners", null))
    {
        while (dr.Read())
        {
            OwnerID = dr.GetInt32(dr.GetOrdinal("ContactOwner"));
            OwnerEmail = dr.GetString(dr.GetOrdinal("OwnerEmail"));

            ReminderBody = TemplateBody.Replace("{ContactRequestList}",
                String.Format("<table>{0}{1}</table>", FollowUpHeader, FollowUpListing(OwnerID, RowLinkFormat)));
            email.sendEmail(EmailFromAddress, EmailFromName, OwnerEmail, Subject, ReminderBody);
        }
    }
}

private static string FollowUpListing(int OwnerID, string RowLinkFormat)
{
    string Results = string.Empty;

    using (SqlDataReader dr = DB.drProc("contact_GetFollowUpList",
        new SqlParameter[] { DB.Parameter("@Owner", SqlDbType.Int, OwnerID) }))
    {
        while (dr.Read())
        {
            Results += String.Format(FollowUpFormat,
                String.Format(RowLinkFormat, dr[0].ToString()),
                dr[1].ToString(),
                Convert.ToDateTime(dr[2].ToString()).ToString("MM/dd/yy"),
                dr[3].ToString());
        }
    }

    return Results;
}

private static string FollowUpFormat
{
    get { return "<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td></tr>"; }
}

private static string FollowUpHeader
{
    get { return String.Format(FollowUpFormat.Replace("td", "th"), "ContactID", "Name", "Follow Up", "Notes"); }
}

 

[jmeckley]

i dont get how to populate a class with data and then use it in a loop

this is what ORM frameworks do. how you configure this really depends on the ORM tool you use. Each of the frameworks i mentioned have their own forums on other websites and are much better suited to answer questions specific to the framework.

I dont understand. I wan't to, and i am interested, however

I would start by researching the general concepts of ORM. There's a good chance most information out there relates to a specific ORM tool, but the concepts themselves are framework/language agnostic.

the fact that you are using procs may make the transition to an ORM tool (all of which use dynamic, parametrized queries) more difficult. not impossible, but a shift in mindset.

quickly glancing over the code you are on the right track, but I see 3 things I would red flag for investigation.
1. all members are static. static members usually represent procedural code which is what OOP isn't. it's not wrong, but it severely limits potential.
2. SendFollowUpReminders and FollowUpListing are responsible for too much. they query the database and build the message. separate the fetching of data and the building of messages. this gets into the concept of SoC (separation of concerns).
3. you have a Select N+1 issue with your queries. to remedy this you need to rework how you query the database. to stick with your stored proc approach have the proc return 2 result sets the users and the contracts.

create proc....
as
   select [fields] from [users] where [criteria]
   select [fields] from [contracts] where [criteria]

then load both of these results into a DataSet with 2 tables and create a DataRelation between the user and contract tables. I know it's possible but I don't remember the details.

loop through each row of user DataTable (in the dataset) and pass this to the message builder function. you can then create a DataView of contract table rows that are related to the User row. Again, not working with datasets I forget the exact code, but it's there. something like [tt]GetChildRow(DataRelation relation)[/tt]

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259