ColdFusion Sample - Building a daily scheduled report

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.

 <cfset from = dateAdd("d", -1, now())> <cfset to = now()>

 <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.

 <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.

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

 <cfset sendTo= "ray@camdenfamily.com">

<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>

</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

ColdFusion Sample - Building a daily scheduled report

Support this Content!

Webmentions