ColdFusion Sample - Building a daily scheduled report

This post is more than 2 years old.

A typical ColdFusion web site consists of a set of files that are either directly requested by a browser or run via other CFM files requested by the user. But ColdFusion also includes the ability to run files on a schedule. Within your ColdFusion Administrator you will find a "Scheduled Tasks" page. This tool lets you create, edit, and delete various tasks you can set up to make ColdFusion run files on a certain date or even based on a recurring schedule. In this blog post I'll demonstrate one simple example of how you can use this feature.

For this sample I'm going to use ColdFusion's Scheduled Tasks system to create a "Comments Report" for this blog. The idea being that once a day, perhaps around midnight, my blog will automatically create a report of the comments added to the blog that day and email it to me. (My blog already sends me emails on new comments, but obviously this type of report could apply to any site with user generated content.) ColdFusion Scheduled Tasks are simply requests to CFM files, so to begin, I create a new file with a few of my defaults.

<!--- Create date range ---> <cfset from = dateAdd("d", -1, now())> <cfset to = now()>

<!--- who gets the email ---> <cfset sendTo= "ray@camdenfamily.com">

I begin by creating a date range. This is something that can be done purely in SQL as well, but I liked having the explicit variables to make it more obvious. Now for the query. Don't get too focused on this. It's specific to our example.

<!--- Now let's get our comments ---> <cfquery name="getComments" datasource="myblog"> select c.id as commentid, e.id as entryid, c.name as commentor, c.email as commentoremail, c.comment, e.title, c.posted, c.website as commentorurl from tblblogcomments c left join tblblogentries e on c.entryidfk = e.id where c.posted >= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#from#"> and c.posted <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#to#"> order by c.posted desc </cfquery>

It's possible that no comments were posted in the past day, so I add a quick exit to end the template.

<cfif getComments.recordCount is 0> No comments to email. <cfexit> </cfif>

Why did I add text? Remember that I'm going to create a Scheduled Task for this. No "real" user will see this. But there's a few reasons why you may want to add information like this. First - during testing you will be running the task manually. Second - ColdFusion's Scheduled Tasks feature allows you to save the results to a file. This is also helpful for debugging once your task is live. Now let's continue down the script.

<cfmail to="#sendTo#" from="#sendTo#" subject="Comment Report" type="html"> <h2>Comment Report</h2>

<p> Here are the comments posted to your blog over the past 24 hours. There were a total of #getComments.recordCount# comment(s) posted from #dateFormat(from)# #timeFormat(from)# to #dateFormat(to)# #timeFormat(to)#. </p>

<p> <table cellpadding="10"> <cfloop query="getComments"> <tr valign="top"> <td bgcolor="##80ff00" align="center" style="color:black" width="100"> <img src="http://www.gravatar.com/avatar/#lcase(hash(lcase(commentoremail)))#?s=64&r=pg" title="#commentor#'s Gravatar" height="64" width="64" /><br/> <cfif len(commentorurl)><a href="#commentorurl#" style="color:black">#commentor#</a><cfelse>#commentor#</cfif> </td> <td style="color:black"> <b>Post: #title#</b><br/> <b>Posted: #dateFormat(posted)# #timeFormat(posted)#</b><br/> <br/> #paragraphformat(comment)# </td> </tr> </cfloop> </table> </p>

</cfmail>

Comment report email sent.

What we have here is a simple HTML email. I used a lot of inline styling since - unfortunately - HTML email is still stuck back in 1990. Obviously my design skills leave a lot to be desired, but check out this screen shot from my GMail account. (Note - my blog traffic was a bit low yesterday so I extended the range a bit to get some content.)

I'll paste the entire template at the of this post, but now let's talk about how to actually schedule this report. First, I went to my ColdFusion Administrator and clicked on the Scheduled Tasks link (under Debugging and Logging, which doesn't make much sense)

I then clicked the button to create a new task. There's a lot of options here but for the most part it should be self-explanatory. Here's the settings I used for my report.

Once scheduled, you can test it right away. Back in the lists, the first icon will run it right now.

I definitely recommend running the task manually at least once to ensure it will work. And that's it! Here's the template, and as always, I've got some notes at the end.

<!--- Create date range ---> <cfset from = dateAdd("d", -1, now())> <cfset to = now()> <cfset from = dateAdd("d", -2, now())>

<!--- who gets the email ---> <cfset sendTo= "ray@camdenfamily.com">

<!--- Now let's get our comments ---> <cfquery name="getComments" datasource="myblog"> select c.id as commentid, e.id as entryid, c.name as commentor, c.email as commentoremail, c.comment, e.title, c.posted, c.website as commentorurl from tblblogcomments c left join tblblogentries e on c.entryidfk = e.id where c.posted >= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#from#"> and c.posted <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#to#"> order by c.posted desc </cfquery>

<cfif getComments.recordCount is 0> No comments to email. <cfexit> </cfif>

<cfmail to="#sendTo#" from="#sendTo#" subject="Comment Report" type="html"> <h2>Comment Report</h2>

<p> Here are the comments posted to your blog over the past 24 hours. There were a total of #getComments.recordCount# comment(s) posted from #dateFormat(from)# #timeFormat(from)# to #dateFormat(to)# #timeFormat(to)#. </p>

<p> <table cellpadding="10"> <cfloop query="getComments"> <tr valign="top"> <td bgcolor="##80ff00" align="center" style="color:black" width="100"> <img src="http://www.gravatar.com/avatar/#lcase(hash(lcase(commentoremail)))#?s=64&r=pg" title="#commentor#'s Gravatar" height="64" width="64" /><br/> <cfif len(commentorurl)><a href="#commentorurl#" style="color:black">#commentor#</a><cfelse>#commentor#</cfif> </td> <td style="color:black"> <b>Post: #title#</b><br/> <b>Posted: #dateFormat(posted)# #timeFormat(posted)#</b><br/> <br/> #paragraphformat(comment)# </td> </tr> </cfloop> </table> </p>

</cfmail>

Comment report email sent.

Notes:

In order for a scheduled task to run, the file it executes has to be under your web root. You can password protect this with your web server, but if do a 'traditional' forms based login system than your task won't be able to bypass that. I'd recommend web server level authentication instead.

Scheduled tasks are sometimes used to generate reports that take a while to process. Don't forget you can specify a higher than normal timeout using the cfsetting tag.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Jason Fisher posted on 6/12/2011 at 11:38 PM

Such a useful tool and a good one to document for folks. One of our .NET developers prefers to write his server schedules in CF to use this feature, since he finds it easier than creating and scheduling compiled Console apps.

Also, just a note that the timeout can also be set on the Scheduled Task screen itself with the Timeout (sec) setting.

Comment 2 by Rich Hefter posted on 6/13/2011 at 2:17 AM

I'm not yet at CF9 (I hear there are improvements) but it would be nice to define dependencies, predecessors, success or failure detecture by the scheduler. But that's a lot to ask of an application web server.

Comment 3 by Raymond Camden posted on 6/13/2011 at 2:22 AM

Actually, Adobe has already announced that "Zeus", the next version of ColdFusion, will have greatly expanded features in this area.

Comment 4 by Matt Gifford posted on 6/13/2011 at 3:29 PM

Ah, this brings back memories :)

I used to have a scheduled task running on an intranet site I built 5 years ago. This template was calling a web service that needed to be updated, but every page refresh was too much and too slow. The task ran the template every 15 minutes, generating a static .html file that could be read and placed into the final HTML output with the cached details. Superb stuff. Zeus is going to improve on this feature ten-fold and it's going to be fantastic.

Comment 5 by Jim Papaleo posted on 6/13/2011 at 4:01 PM

I've been using this feature for years. I have some tasks that run every few minutes others every few hours that check for files on a ftp server or folder and then process those files.

Comment 6 by Dinesh posted on 6/13/2011 at 4:51 PM

Nice post Ray. My applications run on shared server provided by crystaltech and using the scheduler services provided by their control center. Can you explain a bit about (differences/similarities, advantages/disadvantages and how to decide what to use) about CF admin scheduler, cfschedule and window scheduler)? Also, is there any way to make a scheduler run again if it missed its execution, which happens quite frequently on a shared server.

Comment 7 by Raymond Camden posted on 6/13/2011 at 5:15 PM

In terms of the cfschedule tag versus the admin, the only thing the tag doesn't do is provide a List interface. But it is ok to 'reschedule' a task to the same time, so just having cfschedule in your onAppStart is harmless if run N times.

As for the Windows Scheduling service -I believe it is more complex in terms of the _types_ of scheduling it can do. I know there was at least one case - a few years back - where I preferred it. Just running my Windows schedular now - it also has nicer support for failure too it seems.

Comment 8 by Doug posted on 6/13/2011 at 5:55 PM

Great post. Our scheduled tasks have become critical to several of our processes here. We use them to add (missing) functionality to our accounting and HRIS systems:

- nightly delta reports
- detect ledger errors
- monthly expense/revenue audits
- build import files
- flag missing timesheets

Several departments are so dependent on them now, that they also serve a great secondary purpose - if the emails are not delivered, we know there's an undetected problem somewhere!

Comment 9 by James Moberg posted on 6/13/2011 at 7:28 PM

I had some issues in the past where the scheduled tasks weren't running. I also had complicated schedules and would have to make multiple entries in order to create a schedule (ie, First Monday of each month, at 9 am). As an alternative, we started using nnCron LITE. http://www.nncron.ru/

The configuration format is text-based and very easy to backup, clone & migrate to other servers:
http://www.nncron.ru/nncron...
(Since it's text-based, it wouldn't be very difficult to create a CF-based admin tool for it.)

To perform HTTP requests of unknown duration (and not tie up a CF thread), we use CURL:
http://curl.haxx.se/

To generate a non-CF based email message when the task was done, we used Blat (or built an alert into the CF script we were running):
http://www.blat.net/

This solution is portable, inexpensive and has been very reliable over the years. I'm looking forward to seeing the new functionality that will be introduced with Zeus.

Comment 10 by Rob Barthle posted on 6/13/2011 at 7:59 PM

One way we use CFSCHEDULE a lot is for long running processes that would cause our web server to time out because it did not receive data back to the browser to keep the stream open. We use CFSCHEDULE to create a task, and the last part of the task after a successful completion of the job is to use CFSCHEDULE to delete the task from the list to keep the task list uncluttered. It has worked like a charm for us for years now.

Comment 11 by tof posted on 6/14/2011 at 6:13 AM

We use them a lot where I work, for things like:
- reporting.
- long-running tasks that need to take place at night.
- consumption of web services, every x minutes.
- Auto update of statuses. We have job ads that have an opening and closing date, so there's a task that runs every 30 minutes to see if the closing date has passed and update the job ad status.
- find undelivered mail.
- auto-resolve "unknown exception condition"
- clean up temp directory
- and so on... We also have a scheduled task that runs a report on whether or not scheduled tasks ran properly overnight.

also, @ray: your left join should be an inner join since there shouldn't be any comments without a entryidfk. It should only be a left join if you started from the blogEntries table and wanted to also return entries without comments. Or a right outer join from tblblogcomments, but right joins suck and should be avoided.

Comment 12 by Raymond Camden posted on 6/14/2011 at 3:21 PM

Thanks tof - I always forget that.

Comment 13 by test posted on 6/14/2011 at 4:38 PM

Just wanted to see how your site reloaded

Comment 14 by Jaana Gilbert posted on 6/14/2011 at 5:09 PM

I've been utilizing tasks for years and some of the sites depend on it for example to close pending reservations if payment hasn't been made within x amount of minutes, nightly updates to inventory from several suppliers (new items) etc. Very useful tool. Gotta love CF :)