2 Replies Latest reply on Apr 3, 2008 7:12 AM by flashharry!

    ArrayCollection group filter help

    flashharry! Level 1
      Hi, Great forum and great help.

      I have flex 3 which I believe as new group function for array collections, but I have not installed yet as I'm 2/3rds through a hugh project and wanted to wait until his is done.

      So in Flex 2.01 and have an array collection of complex data. The array collection is built form a returned result set from mysql. Each object in the collection is an event. These events could be one of four types and for clarity we will call them event 1,2,3 & 4. Each object in the collection contains lots of other data like user that fired the event, tStamp it happened, quarter it happened in, month and year event happened.

      There could be hundreds of these events happen each month. One of the displays I wish to show is Total for event type for that month or quarter.

      Need to show total number of event type 1, for each month.
      Need to show total number of event type 2, for each month.
      Need to show total number of event type 3, for each month.
      Need to show total number of event type 4, for each month.

      I can filter data, but I need to calculate data. I can do this via remote calls to mysql and having my sql do the sorting, but I would like to make one init call to mysql and sort the array collection in flex.

      I hope this makes sense :)
      I.e
      For each event add up event type total for each month.
      extract of array collection
      [EventType:1, month:3, quarter:q1, year:2006,user:x]
      [EventType:1, month:3, quarter:q1, year:2006,user:y]
      [EventType:2, month:3, quarter:q1, year:2006,user:y]
      [EventType:1, month:3, quarter:q1, year:2006,user:y]
      [EventType:1, month:4, quarter:q1, year:2006,user:y]
        • 1. Re: ArrayCollection group filter help
          NickNameAllNa
          I've the same problem.
          any suggest?
          • 2. Re: ArrayCollection group filter help
            flashharry! Level 1
            This may help, I'm posting this in another thread
            I'm posting this hoping that it will point others in a direction right or wrong. Also hoping to get some feed back on improvements or even 'dude what where you smoking this code is awful'.


            Test Data I'm playing with from mysql.:
            +------------------+----------+-----------+---------+-------------------+
            | PrinterName | JOBCOUNT | PAGECOUNT | Quarter | printStartQuarter |
            +------------------+----------+-----------+---------+-------------------+
            | AEc2880 | 748 | 1034 | Q2-07 | 2007-04-01 |
            | CreativeFirey240 | 2404 | 5454 | Q2-07 | 2007-04-01 |
            | AEc2880 | 2248 | 6552 | Q3-07 | 2007-07-01 |
            | AEiR5055 | 979 | 2845 | Q3-07 | 2007-07-01 |
            | CreativeFirey240 | 5017 | 12321 | Q3-07 | 2007-07-01 |
            | AEc2880 | 1439 | 6256 | Q4-07 | 2007-10-01 |
            | AEiR5055 | 1753 | 5587 | Q4-07 | 2007-10-01 |
            | CreativeFirey240 | 4731 | 9990 | Q4-07 | 2007-10-01 |
            | AEc2880 | 1817 | 2707 | Q1-08 | 2008-01-01 |
            | AEiR5055 | 1158 | 2817 | Q1-08 | 2008-01-01 |
            | CreativeFirey240 | 3822 | 9665 | Q1-08 | 2008-01-01 |
            +------------------+----------+-----------+---------+-------------------+

            Each row is a printerName, JobCount, PageCount,Quarter, printStartQuarter (first day of each quarter)

            Requirements:

            I need to display summaries in a column chart of Queue (printer) usage. For each Queue summary I need to show total jobs sent (printed) and total pages sent (printed) for a given period.

            The one I'm dealing with here is quarterly view. With the sample data above I want to show for each quarter total job, total pages for AEc2880,CreativeFirey240 and AEIR5055

            If I use the data has is, I only get one queue per quarter. I need to have each queue its own column, twice. One for total jobs and total pages. Then one row per quarter for it to work.


            AEc2880_TJ - AEc2880_TP - CreativeFirey240_TJ - CreativeFirey240_TP - AEIR5055_TJ - AEIR5055_TJ - Quater - printStartQuarter.

            Now for the big issue, the queues are dynamic. The test data is from 3 queues only. There could be 1 to n number of queues and not all quarters will be the same as queues get added and deleted.

            This is how I approached this in AS Flex 2, I have flex 3 but not installed yet, I can not find much info on the new grouping classes to see if they will help.
            Most examples are with the advanced data grid. There is also very little information on charting bar the basics around.

            My data comes in from PHP middle ware as JSON.

            First think we need to do is pass the de-serialized array into a function that finds all the unique queue names.

            [Bindable]
            private var groupQueueEvent:ArrayCollection;
            private function setUpHandler(event:ResultEvent):void{
            //get the raw JSON data and cast to String
            var rawData:String = String(event.result);
            //decode the data to ActionScript using the JSON API
            //in this case, the JSON data is a serialize Array of Objects.
            var arr:Array = (JSON.decode(rawData) as Array);

            //Send array for processing.
            groupPrinters(arr);


            }
            //function that takes quartly events and finds unique queues
            private function populateComboYearArray(eventsArray:Array):Array {
            var queueArrayHashMap : Object = new Object(); //create hass array, used to make sure array unique
            var queueArray : Array = new Array; //create arry to hold queues

            //now run through each row of passed data looking for queue names
            for (var i:int = 0; i < eventsArray.length; i++)
            {
            //check to see if this queue is all ready in hash.
            if (queueArrayHashMap[eventsArray
            .PrinterName] == undefined){
            //it not in hash so add it
            queueArrayHashMap[eventsArray .PrinterName] = new Object();
            // now add the queue array
            queueArray.push(eventsArray
            .PrinterName);
            }
            }
            return queueArray; //send back final array
            }
            private function groupPrinters(data:Array):void {
            //create array to hold unique names
            var printerNames:Array = new Array();
            //process data and get unique queue names back
            printerNames = populateComboYearArray(data);

            var printGroup:Array = new Array;//hold the final array
            var tempRowArray:Object;//used to build each row of new data
            var pgCountColumnName:String;//used to build queue names for page count
            var jobCountColumnName:String;/used to build queue names for job count
            for (var printRow:int = 0; printRow < data.length; ) { //run through each row of data
            tempRowArray = new Array; //create a new row each time
            // we will always want the Quarter and printStartQaurter in every row so add now
            tempRowArray.Quarter = data[printRow].Quarter;
            tempRowArray.printStartQuarter = data[printRow].printStartQuarter;
            // now for the tricky bit, we need to process each queue name
            for (var i:int = 0; i < printerNames.length; i++) {
            // we need to see if we have a queue name match not all quarters will
            // have the same queue names
            if (printerNames .PrinterName == data[printRow].PrinterName) {
            //if match found add to our row
            //make dynamic column name would like to use queue name but may be tainted
            pgCountColumnName = "column" + String(i) + "pgcount";
            jobCountColumnName = "column" + String(i) + "jbcount";
            now create the columns with the names and add the data to them
            tempRowArray[pgCountColumnName] = data[printRow].PAGECOUNT;
            tempRowArray[jobCountColumnName] = data[printRow].JOBCOUNT;
            // we found a match so move along in outer loop
            printRow++;
            }
            // we didn't find a match don't advance outer loop
            }
            // we checked all the queue names advanced outer loop where needed,
            // next time trough we should be in new quarter

            printGroup.push(tempRowArray);// push temporary row to our array

            }
            //assign are data to an array collection
            groupQueueEvent = new ArrayCollection(printGroup);
            //assign data to test grid for checking
            detailedPrintReport.dataProvider = groupQueueEvent;
            //assign data to chart.
            colChart.dataProvider = groupQueueEvent;

            //now make up the dynamic series
            //PLEASE NOTE this code does not work yet still playing with it
            // testing purpose hard coded in mxml
            /*
            var dynamicColumnSeries:ColumnSeries = new ColumnSeries;
            colChart.series = [dynamicColumnSeries];
            for ( i = 0; i < printerNames.length; i++) {
            pgCountColumnName = "column" + String(i) + "pgcount";
            jobCountColumnName = "column" + String(i) + "jbcount";
            dynamicColumnSeries = new ColumnSeries;
            dynamicColumnSeries.yField = 'jobCountColumnName;
            dynamicColumnSeries.xField="Quarter";
            dynamicColumnSeries.displayName = printerNames
            .PrinterName;
            colChart.series.push(dynamicColumnSeries);
            dynamicColumnSeries = new ColumnSeries;
            dynamicColumnSeries.yField = pgCountColumnName;
            dynamicColumnSeries.displayName = printerNames .PrinterName;
            colChart.series.push(dynamicColumnSeries);
            }
            */

            }

            <ColumnChart id="colChart" width="100%" height="100%" showDataTips="true" visible="true" >
            <horizontalAxis>
            <CategoryAxis categoryField="Quarter" title="Quartly" labelFunction="defineLabel"/>
            </horizontalAxis>
            <series>
            <ColumnSeries yField="column0jbcount" xField="Quarter" />
            <ColumnSeries yField="column0pgcount" xField="Quarter" />
            </series>
            </ColumnChart>
            <DataGrid id="detailedPrintReport" visible="true" width="100%" height="100%"/>