Setting up date range picker in flex 3
Filed under CFC , ColdFusion , Flex , T-SQL
I was posed with the task of making little dashboards for various managers lately. One of the things that every dashboard needed was a date range that was dynamically driven through date pickers.
I first setup my coldfusion component .cfc to return a query. I then set up 2 date arguments to be used in my between statement to narrow my query results. An example function can be found below:
<cffunction name="dateRange" access="remote" returntype="query">
<cfargument name="DateField1" required="true" type="date">
<cfargument name="DateField2" required="true" type="date">
<cfquery name="revReport" datasource="dsnScoreCard">
SELECT column1, column2
FROM test
WHERE date1 BETWEEN CONVERT(VARCHAR(10), <cfqueryparam cfsqltype="cf_sql_date" value="#DateField1#">, 101) AND CONVERT(VARCHAR(10), <cfqueryparam cfsqltype="cf_sql_date" value="#DateField2#">, 101)
</cfquery>
<cfreturn revReport>
</cffunction>
After you have your .cfc setup you can then connect flex 3 through 3 different ways. The best way to connect in my opinion is through a remote object but if your site is setup through SSL I have had a lot of trouble getting remote objects to work in internet explorer (That is entirely another topic).
Below is an example of a remoteobject in MXML:
<mx:RemoteObject id="data" destination="ColdFusion" source="revenue.getData">
<mx:method name="dateRange" result="resultHandler(event)" />
</mx:RemoteObject>
It is very important that you give the RemoteObject an ID. Set the destination equal to ColdFusion (case sensative). That is the default destination which is defined in your remoting-config.xml located in C:\ColdFusion8\wwwroot\WEB-INF\flex. I will not go into detail but you can make custom destinations. Next you need to define the source of your remoteobject. The source is the absolute path to your .cfc from the root. The periods are the same as a "/" (For example, revenue.getData = revenue/getData.cfc). Furthermore, you need to setup the method that you will be calling in your .cfc. If you notice the method name is equal to the name of the function in the cfc created above. After the method is specified you set the result of calling that method. The result calls the function resultHandler() and the event being passed by the function. Below is the actionscript function resultHandler():
private function resultHandler(e:ResultEvent):void
{
LineChart.dataProvider = e.result;
}
As you can see, all the function does is set the dataprovider of the line chart. (It is important that you type out the whole function instead of copy and pasting because flex builder creates the following import statement for you (import mx.rpc.events.ResultEvent;)) Now that the function and remote object are set up, it is time to send the date picker dates to the functions in the cfc. Make 2 date picker wherever you would like and specify the start dates. To specify the start dates just set the selectedDate in the built in initialize funtion. DateField2 is set to todays date (DateField2.selectedDate = new Date();) DateField1 is a little more tricky. Dates are calculated in milliseconds so to set the date to 30 days ago you need to multiply the number of milliseconds per day by the number of days you want subtracted from today (DateField1.selectedDate = new Date(new Date().getTime() - (30 * millisecondsPerDay));). To get the number of milliseconds per date you need to create a little actionscript function. Below is the actionscript millisecondsPerDay followed by the datefields:
public static const millisecondsPerDay:int = 1000 * 60 * 60 * 24
<mx:ApplicationControlBar width="100%">
<mx:HBox>
<mx:DateField id="DateField1" showToday="true" change="clickHandler();" initialize="DateField1.selectedDate = new Date(new Date().getTime() - (30 * millisecondsPerDay));"/>
<mx:DateField id="DateField2" showToday="true" change="clickHandler();" initialize="DateField2.selectedDate = new Date();" />
</mx:HBox>
</mx:ApplicationControlBar>
You notice that the DateFields have a clickHandler() function in the change event. So every time one of the dates is changed it calls the actionscript clickHandler which is below:
private function clickHandler():void
{
data.dateRange(DateField1.selectedDate, DateField2.selectedDate);
LineChart.title = dateToMMDDYYYY(DateField1.selectedDate) + " and " + dateToMMDDYYYY(DateField2.selectedDate);
}
The clickHandler function calls your RemoteObject (id=data), then method(dateRange) and passes the values of the datepickers. The .cfc will then receive the 2 date arguments and narrow your selected query and return those values(event) to your resultHandler which then sets the dataprovider to the appropriate chart. I also added a title change to show the date range. I have had a little trouble formatting dates in flex 3 (ColdFusion LSDateFormat()). dateToMMDDYYYY() is a custom function that I found in a blog (sorry I dont remember who to give credit to :( ) to format dates in flex 3 to be displayed in the mm/dd/yyyy format. Belows is the actionscript function:
private function dateToMMDDYYYY(aDate:Date):String
{
var SEPARATOR:String = "/";
var mm:String = (aDate.month + 1).toString();
if (mm.length < 2) mm = "0" + mm;
var dd:String = aDate.date.toString();
if (dd.length « 2) dd = "0" + dd;
var yyyy:String = aDate.fullYear.toString();
return mm + SEPARATOR + dd + SEPARATOR + yyyy;
}
Lastly, you need to load the charts on the load of the flex project. Most applications have a init() function that is called on the creationComplete="init();" of the «mx:Application>. Inside the init() function just call the same RemoteObject as the clickHandler() function. Below is the code:
private function init():void
{
data.dateRange(DateField1.selectedDate, DateField2.selectedDate);
LineChart.title = dateToMMDDYYYY(DateField1.selectedDate) + " and " + dateToMMDDYYYY(DateField2.selectedDate);
}
You could just call the clickHandler() in the creationComplete="clickHandler();" but I like to separate them. I hope someone finds this blog post useful cause it is sometimes hard to find the easy things for us beginning in this field of work ;)
Oct28