0 Replies Latest reply on Jul 21, 2013 4:26 PM by Jack_Tauson

    How to approach for downloading a CSV file?

    Jack_Tauson

      I'm running the following query and  QoQ . Could you tell me how should I proceed for the "Download  CSV" file option?

       

          <!--- QoQ for FIRSTCONN --->

         

          <cfquery datasource = "XX.XX.X.XX" name="master1">

               SELECT STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y') as FIRSTCONN

                      , COUNT(Timedetail) as FIRSTOccurances

                      , EVENTS

               FROM  MyDatabase

               WHERE EVENTS = "FIRST"

               GROUP BY FIRSTCONN ;

          </cfquery>

       

          <!--- Detail Query --->

         

          <cfquery dbtype="query" name="detail1">

              SELECT  *

              FROM master1

              WHERE FIRSTCONN  >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar">

              AND   FIRSTCONN  <  <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar">;

          </cfquery> 

         

         

          <!--- QoQ for SECONDCONN --->

         

          <cfquery datasource = "XX.XX.X.XX" name="master2">

              SELECT STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y') as SECONDCONN

                     , COUNT(Timedetail) as SECONDOccurances

                     , EVENTS

              FROM  MyDatabase

              WHERE EVENTS = "SECOND"

              GROUP BY SECONDCONN ;

          </cfquery>

       

         

          <cfquery dbtype="query" name="detail2">

              SELECT  *

              FROM   master2

              WHERE  SECONDCONN  >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar">

              AND    SECONDCONN  <  <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar">;

          </cfquery> 

         

         

          <cfchart format="flash"  chartwidth="1000" chartheight="500" scalefrom="0" scaleto="50000" xAxisTitle="Dates" yaxistitle="Number of Connections">

               <cfchartseries  query="detail1" type="line" itemColumn="FIRSTCONN" valueColumn="FIRSTOccurances" >

               <cfchartseries  query="detail2" type="line" itemColumn="SECONDCONN" valueColumn="SECONDOccurances" >

               </cfchartseries>

          </cfchart>

            

      The cfform code I'm using is as follows:

       

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

       

       

      <cfformgroup type="horizontal">

       

        <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="submitApply" type="submit" value = "Apply">

        <cfinput name="cancel" type="submit" value="Download CSV">

       

      </cfformgroup>

            

      Desired Output:

       

      I have attached the image for the output below. Please find it attached.


       

      Basically, if a date range is 21June to 21 July. The output must be as shown in the image. (I have omitted THIRDCONN etc for the sake of simplicity in my code).

       

      Please let me know how should I go about this problem and let me know if I can answer more questions.

       

       

       

       

       

       

       

      Stats_Screenshot.jpg