CODEWITHSUNDEEP
mysqlcoldfusioncoldfusion-8
Tan
Tan

Reputation: 1585

Playing around with date range in ColdFusion 8

Please consider the following code:

In this code, a user will be asked to select a date range from the two options and click on apply button so that the result can be displayed on the web browser for the selected date range. I'm wondering how should I proceed. Please throw some light on this.

2) How can I make sure that I can enable the option of downloading the data using "Download CSV" option?

Please let me know what changes are required. Any help would be appreciated.

<body>

<cfparam name="Form.startdate" default="#dateformat(now()-5, 'mm/dd/yyyy')#">
<cfparam name="Form.enddate" default="#dateformat(now()-1, 'mm/dd/yyyy')#">
<cfparam name="Form.selectdate" default="#dateformat(now(), 'mm/dd/yyyy')#">


<!--- <cfquery datasource = "xx.xx.x.x" name="qCheck">

SELECT * 
FROM   mydatabase
ORDER BY DTSId_bi LIMIT 10;

</cfquery>
 --->

<cfquery datasource = "xx.xx.x.x" name="qDatabase">

SELECT    (SELECT count(*) FROM mydatabase) AS TOTAL_CONNECTIONS,
          (SELECT count(*)FROM mydatabase WHERE event_vc = "open") AS OPEN_CONNECTIONS,
          (SELECT count(*)FROM mydatabase WHERE event_vc = "BOUNCE") AS BOUNCE_CONNECTIONS,
          (SELECT count(*) from mydatabase where event_vc = "DEFERRED") AS DEFERRED_CONNECTIONS,
          (SELECT count(*) from mydatabase where event_vc = "DELIVERED") AS DELIVERED_CONNECTIONS,
          (SELECT count(*) from mydatabase where event_vc = "DROPPED") AS DROPPED_CONNECTIONS,
          (SELECT count(*) from mydatabase where event_vc = "PROCESSED") AS PROCESSED_CONNECTIONS,
          (ROUND((SELECT OPEN_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "OPEN",
          (ROUND((SELECT BOUNCE_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "BOUNCE" ,
          (ROUND((SELECT DEFERRED_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "DEFERRED",
          (ROUND((SELECT DELIVERED_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "DELIVERED",
          (ROUND((SELECT DROPPED_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "DROPPED",
          (ROUND((SELECT PROCESSED_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "PROCESSED";
</cfquery>



<cfform format="flash" preloader ="false">

<!--- Arranging the two buttons adjacent to each other using cfformgroup tag --->
<cfformgroup type="horizontal">


<p>&nbsp;</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</p>
<p>
  <cfinput type="dateField" name="startdate" label="Start Date" width="100" value="#Form.startdate#">
  <cfinput type="dateField" name="enddate" label="End Date" width="100" value="#Form.enddate#">
  <cfinput name="submit" type="submit" value = "Apply">
  <cfinput name="cancel" type="submit" value="Download CSV">
</p>
</cfformgroup>

<!--- For Horizontal Line --->
<cfformitem type = "hrule" style=""></cfformitem>


</cfform>

<cfchart
         format="png"
         chartwidth="500"
         chartheight="500"
         scalefrom="0"
         scaleto="1200000"
         title="Email Reporting "
         pieslicestyle="solid">

         <cfchartseries type="line"
                         >

        <cfchartdata item="% OPEN" value="#qDatabase.OPEN#">
        <cfchartdata item="% BOUNCE" value="#qDatabase.BOUNCE#">
        <cfchartdata item="% DEFERRED" value="#qDatabase.DEFERRED#">
        <cfchartdata item="% DELIVERED" value="#qDatabase.DELIVERED#">
        <cfchartdata item="% DROPPED" value="#qDatabase.DROPPED#">
        <cfchartdata item="% PROCESSED" value="#qDatabase.PROCESSED#"> 


    </cfchartseries>
</cfchart>





</body>

Upvotes: 0

Views: 257

Answers (1)

Lance
Lance

Reputation: 3213

If I were you I would really consider re-working the qDatabase query it is VERY inefficient! As far as downloading a csv the best way is to write a file to a temp area that frequestly gets cleaned out so it doesn't fill up you HD. I will help with writing the file but not the clean up lol. You can do something like this: 

<cfscript>
    var tl ='';
    var nl = (Chr( 13 ) & Chr( 10 ));
    var fileContent = createObject("java","java.lang.StringBuffer").init();
    var counter =1;
    fileContent.append( 'OPEN,BOUNCE,DEFERRED,DELIVERED, etc');
     fileContent.append(nl);
            for(i=1;i<=qDatabase.recordCount;i=i+1){
                tl = qDatabase.OPEN&','qDatabase.BOUNCE&','qDatabase.DEFERRED&','qDatabase.DELIVERED&',' etc;
                fileContent.append(tl);
                fileContent.append(nl);
            }

</cfscript>
<cffile action="write" file="#absoluteFilePathAndName#" mode="775" output="#fileContent.toString()#"/>
<a href="#realtiveFilePathAndName#>Download</a>

This will write a csv file to the absolute path every time someone hits the page so you will want some conditional logic around it to prevent that happening.

Over all you need to relook at the code you have provided and rethink what you are doing but once you do this code will get you started IT IS NOT your final solution.

Upvotes: 1

Related Questions