4 Replies Latest reply on Apr 15, 2010 10:41 AM by Dan Bracuk

    cfscript

    coylo Level 1
    coylo Mar 29, 2010 8:12 AM
      ChartDirector Support
      		Forum HomeForum Home SearchSearch

      Message ListMessage List     Post MessagePost Message    Subscribe to forumSubscribe

        cfscript
      Posted by kevin on Mar-29-2010 11:00
      | Reply | Monitor
      I am trying to generate a multi series line graph using 3rd party software. when i loop through the bidders i want data0y and data0x to increment each time i.e. when it goes to bidder 1 it should generate:
      <CFSCRIPT>
      data0Y = Array(AllBids);
      data0X = Array(AllBidDates);
      </CFSCRIPT>

      bidder 2:

      <CFSCRIPT>
      data1Y = Array(AllBids);
      data1X = Array(AllBidDates);
      </CFSCRIPT>

      bidder 3:

      <CFSCRIPT>
      data2Y = Array(AllBids);
      data2X = Array(AllBidDates);
      </CFSCRIPT>

      i have tried things like:
      <!--- Find Bidder Position --->
      <CFSET Bidderpostemp=ListFind(#AllBidders#, "#GetChartData.BidderID#")>
      <CFSET Bidderpos=Bidderpostemp-1>
      <CFSCRIPT>
      data#Bidderpos#Y = Array(AllBids);
      </CFSCRIPT>

      but to no avail.

      the relevant part of the code is....

      <!--- Query to find all the Bidders participating in this Lot --->
      <CFQUERY NAME="GetAllBidders" DATASOURCE="#Application.Datasource#">
      SELECT BidderID
      FROM Bidders
      WHERE BidderID IN (<CFQUERYPARAM VALUE="#GetBidders.Bidders#" LIST="YES" CFSQLTYPE="CF_SQL_VARCHAR">) AND (Activated=<CFQUERYPARAM VALUE="Yes" CFSQLTYPE="CF_SQL_CHAR" MAXLENGTH="3">)
      </CFQUERY>

      <CFSCRIPT>

      // ChartDirector for ColdFusion API Access Point
      cd = CreateObject("java", "ChartDirector.CFChart");

      // A utility to allow us to create arrays with data in one line of code
      function Array() {
           var result = ArrayNew(1);
           var i = 0;
           for (i = 1; i LTE ArrayLen(arguments); i = i + 1)
               result[i] = arguments[i];
           return result;
      }

      </CFSCRIPT>

      <!--- Create a list of all the Bidders --->
      <CFSET AllBidders=ValueList(GetAllBidders.BidderID)>

      <!--- Loop through the Bidders --->
      <CFLOOP INDEX="ListBidders" LIST="#AllBidders#">

      <!--- Query to find Data for the Graph --->
      <CFQUERY NAME="GetChartData" DATASOURCE="#Application.Datasource#">
      SELECT Bids.BidID, Bids.BidderID, Bids.LotID, Bids.Bid, Bids.Bid_Date, Bidders.Company
      FROM Bids INNER JOIN Bidders ON Bids.BidderID = Bidders.BidderID
      WHERE (Bids.LotID=<CFQUERYPARAM VALUE="#URL.LotID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">) AND (Bids.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">)
      ORDER BY Bids.Bid_Date ASC;
      </CFQUERY>

      <!--- Create a list of all the Bids --->
      <CFSET AllBids=ValueList(GetChartData.Bid)>
      <!--- Create a list of all the Bid Date/Times --->
      <CFSET AllBidDates=ValueList(GetChartData.Bid_Date)>

      <CFSCRIPT>
      data0Y = Array(AllBids);
      data0X = Array(AllBidDates);
      </CFSCRIPT>

      </CFLOOP>
      • 1270 Views
      • Tags:
        • 1. Re: cfscript
          Dan Bracuk Level 5
          Dan Bracuk Mar 29, 2010 9:34 AM (in response to coylo)

          You are overwriting your variables each time you go through your loop.

           

          Not related to your question, but, going back to the database inside a loop is very inefficient and could cause problems.  A better strategy would be to make one trip to the db and get all the data you need.  Then you can use Q of Q and other things to build your graph.

          • 2. Re: cfscript
            coylo Level 1
            coylo Apr 15, 2010 8:20 AM (in response to Dan Bracuk)

            thanks for the Q of Q tip.

             

            however i have a subquery that is also inside a cfloop:

             

            <!--- Query to find the latest/current bid of each of the other Bidders --->
            <CFQUERY NAME="GetLastBid" DATASOURCE="#Application.Datasource#">
            SELECT DISTINCT Scores.Non_Price_Score, Scores.Price_Score, Bids.BidderID, Bids.Bid
            FROM (Bids INNER JOIN Bidders ON Bids.BidderID = Bidders.BidderID) INNER JOIN Scores ON (Bids.LotID = Scores.LotID) AND (Bids.BidderID = Scores.BidderID) JOIN
            (Select BidderID, MAX(Bid_Date) as Max_Bid_Date
            FROM Bids
            WHERE (Bids.LotID=<CFQUERYPARAM VALUE="#Form.LotID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">) AND (Bids.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">)
            GROUP BY BidderID) x ON Bids.BidderID = x.BidderID and Bids.Bid_Date = Max_Bid_Date
            WHERE (Bids.LotID=<CFQUERYPARAM VALUE="#Form.LotID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">) AND (Bids.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">)
            </CFQUERY>


            For more efficiency is it possible to do above in Q of Q as in the following:

             

            <CFQUERY NAME="GetBidsScores" DATASOURCE="#Application.Datasource#">
            SELECT Bids.BidID, Bids.BidderID, Bids.LotID, Bids.Bid, Bids.Bid_Date, Scores.Non_Price_Score, Scores.Price_Score
            FROM (Bids INNER JOIN Bidders ON Bids.BidderID = Bidders.BidderID) INNER JOIN Scores ON (Bids.LotID = Scores.LotID) AND (Bids.BidderID = Scores.BidderID)

            WHERE Bids.LotID=<CFQUERYPARAM VALUE="#Form.LotID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">

            </CFQUERY>

             

            <!--- Loop through the other Bidders --->
            <CFLOOP INDEX="ListBidders" LIST="#OtherBidders#">

             

            <!--- Query to find the latest/current bid of each of the Bidders --->
            <CFQUERY NAME="GetLastBid" DBTYPE="QUERY">
            SELECT Non_Price_Score, Price_Score, LotID, BidderID, Bid
            FROM GetBidsScores JOIN
            (Select GetBidsScores.BidderID, MAX(Bid_Date) as Max_Bid_Date
            FROM GetBidsScores
            WHERE GetBidsScores.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">
            GROUP BY BidderID)  x ON GetBidsScores.BidderID = x.BidderID and GetBidsScores.Bid_Date = Max_Bid_Date
            WHERE GetBidsScores.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">
            </CFQUERY>

             

            ............

             

            </CFLOOP>

             

            I tried above and got following error.

             

            Query Of Queries syntax error.
            Encountered "JOIN.

            • 3. Re: cfscript
              Adam Cameron. Level 5
              Adam Cameron. Apr 15, 2010 8:29 AM (in response to coylo)

              One cannot use JOIN syntax or have subqueries in a QoQ.  QoQ supports only a very limited subset of SQL constructs. It might be an idea to give the docs a bit of a read:

              http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd -7ff0.html

               

              --

              Adam

              • 4. Re: cfscript
                Dan Bracuk Level 5
                Dan Bracuk Apr 15, 2010 10:41 AM (in response to coylo)

                You don't need a loop.  The general syntax for getting the most recent or highest, or whatever set of data is as follows.  I'll use most recent as an example.

                 

                select datefield, some_other_fields

                from some_tables

                 

                join (

                select some_other_fields, max(datefield) maxdatefield

                from some_tables

                where whatever

                group by some_other_fields

                ) sq on some_tables.some_other_fields = sq.some_other_fields

                and datefield = maxdatefield

                 

                where whatever