ZK Pivottable: Sort Pivottable Column Data

Introduction

We have implemented 'Display Data in ZK Pivottable' in previous post (http://ben-bai.blogspot.tw/2012/07/zk-pivottable-display-data-in-zk.html), in this post, we will try to sort the column data in pivottable.

The Composer

TestComposer.java

package test;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;

import org.zkoss.pivot.PivotField;
import org.zkoss.pivot.PivotHeaderNode;
import org.zkoss.pivot.Pivottable;

import org.zkoss.pivot.event.PivotUIEvent;
import org.zkoss.pivot.impl.SimplePivotHeaderTree;
import org.zkoss.pivot.impl.TabularPivotField;
import org.zkoss.pivot.impl.TabularPivotModel;

import org.zkoss.zk.ui.event.MouseEvent;
import org.zkoss.zk.ui.select.SelectorComposer;
import org.zkoss.zk.ui.select.annotation.Listen;
import org.zkoss.zk.ui.select.annotation.Wire;

/**
 * Tested with ZK 6.0.1 CE and ZK Pivottable 2.0.0
 *
 */
@SuppressWarnings("rawtypes")
public class TestComposer extends SelectorComposer {
    /**
     * generated serial version UID
     */
    private static final long serialVersionUID = -2897873399288955635L;
    private TabularPivotModel _pivotModel;

    @Wire("#pivottable")
    private Pivottable pivottable;

    private int _rowLevelToSort = 1;    // the level of rows to sort
    private int _fieldIndexToSort = 0; // the index of data field under _sortColumn
    private PivotHeaderNode _columnNodeToSort; // the column node with respect to _sortColumn

    /**
     * Get pivottable's model
     * @return TabularPivotModel the pivottable's model
     * @throws Exception
     */
    public TabularPivotModel getPivotModel () throws Exception {
        if (_pivotModel == null) {
            _pivotModel = new TabularPivotModel(getData(), getColumns());

            // assign rows, the order matches to the level of row node field
            _pivotModel.setFieldType("Row_Level_001", PivotField.Type.ROW);
            _pivotModel.setFieldType("Row_Level_002", PivotField.Type.ROW);
            _pivotModel.setFieldType("Row_Level_003", PivotField.Type.ROW);
            _pivotModel.setFieldType("Row_Level_004", PivotField.Type.ROW);

            // assign columns, the order matches to the level of column node field
            _pivotModel.setFieldType("Column_Level_001", PivotField.Type.COLUMN);
            _pivotModel.setFieldType("Column_Level_002", PivotField.Type.COLUMN);

            // assign datas, the order matches to the order of data field
            _pivotModel.setFieldType("Data_Field_001", PivotField.Type.DATA);
            _pivotModel.setFieldType("Data_Field_002", PivotField.Type.DATA);
            _pivotModel.setFieldType("Data_Field_003", PivotField.Type.DATA);
        }
        return _pivotModel;
    }
    /**
     * prepare the data for pivottable's model
     * The order of object put into data list matches
     * the order of column name's order
     * @return
     * @throws Exception
     */
    public List<List<Object>> getData() throws Exception {
        List<List<Object>> result = new ArrayList<List<Object>>();
        Random r = new Random();

        for (int i = 0; i < 10000; i++) {
            List<Object> data = new ArrayList<Object>();
            data.add("Row_Level_001 - " + (r.nextInt(10) + 1));
            data.add("Row_Level_002 - " + (r.nextInt(10) + 1));
            data.add("Row_Level_003 - " + (r.nextInt(10) + 1));
            data.add("Row_Level_004 - " + (r.nextInt(10) + 1));
            data.add("Column_Level_001 - " + (r.nextInt(10) + 1));
            data.add("Column_Level_002 - " + (r.nextInt(10) + 1));
            data.add(r.nextInt(10000));
            data.add(r.nextDouble() * 10000.0);
            data.add(r.nextInt(100));
            result.add(data);
        }
        return result;
    }
    /**
     * prepare columns name for pivottable's model
     * @return
     */
    public List<String> getColumns() {
        return Arrays.asList(new String[]{
                "Row_Level_001", "Row_Level_002", "Row_Level_003", "Row_Level_004",
                "Column_Level_001", "Column_Level_002",
                "Data_Field_001", "Data_Field_002", "Data_Field_003"
        });
    }

    /**
     * ** Added **
     * sort column ascending
     */
    @Listen("onClick = #btnSortAsc")
    public void doSortAscending (MouseEvent event) {
        doSort(true);
    }

    /**
     * ** Added **
     * sort column descending
     */
    @Listen("onClick = #btnSortDesc")
    public void doSortDescending (MouseEvent event) {
        doSort(false);
    }
    /**
     * ** Added **
     * sort column
     * @param ascending boolean, true: ascending, false: descending
     */
    private void doSort (boolean ascending) {
        if (_columnNodeToSort != null) {
            // get the rowHeaderTree
            SimplePivotHeaderTree rowHeaderTree = (SimplePivotHeaderTree)_pivotModel.getRowHeaderTree();
            // create comparator
            ColumnHeaderComparator comparator = new ColumnHeaderComparator(_pivotModel,
                    _columnNodeToSort, _fieldIndexToSort, _rowLevelToSort, ascending);
            // do sort
            rowHeaderTree.sort(comparator);
            // reset model to trigger rerender
            pivottable.setModel(_pivotModel);
        }
    }

    /**
     * ** Added **
     * Update sort attributes from click event of pivottable
     */
    @Listen("onPivotPopup = #pivottable")
    public void updateSortAttributes (PivotUIEvent e) {
        PivotField dataField = e.getDataField();

        _rowLevelToSort = e.getRowContext() != null? e.getRowContext().getNode().getDepth() : 0;
        if (dataField != null) {
            _columnNodeToSort = e.getColumnContext().getNode();
            _fieldIndexToSort = getFieldIndexToSort(dataField.getFieldName(), _pivotModel.getDataFields());
        }
    }
    /**
     * ** Added **
     * get the index of the field to sort
     * @param fieldToSort the field to sort
     * @param fields all fields get from pivot model
     * @return int the index of the field to sort
     */
    private int getFieldIndexToSort (String fieldToSort, TabularPivotField[] fields) {
        for (int i = 0; i < fields.length; i++) {
            if (fieldToSort.equals(fields[i].getFieldName()))
                return i;
        }
        return 0;
    }
}

There are two parts regarding to the sort feature, one is 'public void updateSortAttributes (PivotUIEvent e)', it updates the sorting attributes when a data cell or a row head clicked, the other is 'private void doSort (boolean ascending)', it will sort a column's data with respect to the sorting attributes while sort ascending / descending button clicked.

ColumnHeaderComparator.java

package test;

import java.math.BigDecimal;
import java.util.Comparator;

import org.zkoss.pivot.PivotHeaderNode;
import org.zkoss.pivot.impl.TabularPivotModel;

/**
 * ** Added **
 *  the comparator for sort row header node
 *  Tested with ZK 6.0.1 CE and ZK Pivottable 2.0.0
 */
public class ColumnHeaderComparator implements Comparator<PivotHeaderNode> {
    private TabularPivotModel _pivotModel; // the data model
    private PivotHeaderNode _columnNodeToSort; // the column node to sort
    private int _fieldIndexToSort; // the field index of data fields to sort
    private int _rowLevelToSort; // the row level to sort
    private boolean _ascending; // sort direction

    /**
     * Constructor
     * @param pivotModel TabularPivotModel, The pivottable's model
     * @param columnNodeToSort PivotHeaderNode, The column to sort
     * @param fieldIndexToSort int, The index of the field under the columnNodeToSort to sort
     * @param rowLevelToSort int, The level of row header node to sort
     * @param ascending boolean, true: sort ascending, false: sort descending
     */
    public ColumnHeaderComparator (TabularPivotModel pivotModel, PivotHeaderNode columnNodeToSort,
            int fieldIndexToSort, int rowLevelToSort, boolean ascending) {
        _pivotModel = pivotModel;
        _columnNodeToSort = columnNodeToSort;
        _fieldIndexToSort = fieldIndexToSort;
        _rowLevelToSort = rowLevelToSort;
        _ascending = ascending;
    }
    /**
     * compare two node
     */
    public int compare (PivotHeaderNode n1, PivotHeaderNode n2) {
        int result = 0;
        // get the level of two node
        int l1 = n1.getDepth();
        int l2 = n2.getDepth();

        // only compare if node is at sort level
        if (l1 == _rowLevelToSort
            && l2 == _rowLevelToSort) {
            // get the values and compare
            Number v1 = _pivotModel.getValue(n1, -1, _columnNodeToSort, -1, _fieldIndexToSort);
            Number v2 = _pivotModel.getValue(n2, -1, _columnNodeToSort, -1, _fieldIndexToSort);
            result = (_ascending? 1 : -1 ) * (new BigDecimal(v1.toString()).compareTo(new BigDecimal(v2.toString())));
        }

        return result;
    }
}

This is the comparator, the sorting result will depends on how you implement it.

The ZUL Page

index.zul

<zk xmlns:w="client"><!-- ** Added ** namespace for Client Side Programming -->
    <!-- Tested with ZK 6.0.1 CE and ZK Pivottable 2.0.0 -->
    <vlayout>
        <div>
            <!-- ** Added ** the description of how to use sort feature -->
            <vbox>
                <label value="Description:" />
                <label value="Click data cell to choose the column field to sort" />
                <label value="The sort level will be the latest extended level of the clicked row" />
                <label value="Click row header node to change the sort level" />
                <label value="Click 'sort ascending' / 'sort descending' button to sort" />
            </vbox>
        </div>
        <!-- window, apply a SelectorComposer -->
        <window id="win" xmlns:w="client"
            apply="test.TestComposer">
            <!-- pivottable, get model from window's composer -->
            <pivottable id="pivottable" model="${win$composer.pivotModel}">
                <!-- ** Added ** Blank block for position the sort button -->
                <div></div>
                <!-- ** Added ** the sort button to do sort ascending / descending -->
                <div>
                    <!-- The sort ascending button -->
                    <button id="btnSortAsc" label="Sort Ascending" />
                    <!-- The sort descending button -->
                    <button id="btnSortDesc" label="Sort Descending" />
                </div>
            </pivottable>
        </window>
    </vlayout>
    <!-- ** Added ** define some style and override some function
                    for better user experience
                    actually the 'style' fragment and the 'script' fragment
                    are not related to the sort feature -->
    <style>
        <!-- style for focused data cell / row head -->
        .focused-data-cell .z-pivottable-field-wrapper {
            background-color: #CCCCCC;
        }
        .focused-row-head .z-pivottable-field-wrapper {
            background-color: #AADDDD;
        }
    </style>
    <script type="text/javascript"><![CDATA[
        var _Pwgt = {},
            _Bwgt = {};
        // override pivottable js function
        zk.afterLoad("pivot", function () {
            zk.override(pivot.Pivottable.prototype, _Pwgt, {
                // override pivottable bind_ for restore the scrollLeft
                bind_: function(desktop, skipper, after) {
                    _Pwgt.bind_.apply(this, arguments); //call the original method
                    var oldLeft;
                    if (this.id == 'pivottable') { // limit the affect target
                        if (oldLeft = pivot.Pivottable.pivottableOldLeft) {
                            var wgt = this;

                            setTimeout(function (){
                                wgt.$n('scroll').scrollLeft = oldLeft;
                                pivot.Pivottable.pivottableOldLeft = null;
                            }, 200);
                        }
                    }
                },
                // override pivottable doClick_ for style the clicked data cell or row head
                doClick_: function (evt) {
                    _Pwgt.doClick_.apply(this, arguments); //call the original method
                    // clear old scroll if any
                    pivot.Pivottable.pivottableOldLeft = null;
                    if (this.id == 'pivottable') { // limit the affect target
                        var tar = evt.domTarget,
                            jtar = jq(tar),
                            old;
                        if (!jtar.hasClass('z-pivottable-icon')) {
                            // click on data cell
                            if (jtar.hasClass('z-pivottable-cell-field')
                                || ((tar = tar.parentNode) && (jtar = jq(tar)) && jtar.hasClass('z-pivottable-cell-field'))) {
                                // remove old focus
                                if (old = this._oldFocusDataCell) {
                                    jq(old).removeClass('focused-data-cell');
                                }
                                // also remove old row head's style, denotes use latest level as sort level
                                if (old = this._oldFocusRowHead) {
                                    jq(old).removeClass('focused-row-head');
                                }
                                // add focus for denotes use clicked data field to do sort
                                jtar.addClass('focused-data-cell');
                                this._oldFocusDataCell = jtar[0];
                            } else if (jtar.hasClass('z-pivottable-row-field')
                                    || ((tar = tar.parentNode) && (jtar = jq(tar)) && jtar.hasClass('z-pivottable-row-field'))) {
                                // click on row header node
                                // remove old focus
                                if (old = this._oldFocusRowHead) {
                                    jq(old).removeClass('focused-row-head');
                                }
                                // add focus for denotes use specific sort level
                                jtar.addClass('focused-row-head');
                                this._oldFocusRowHead = jtar[0];
                            }
                        }
                    }
                }
            });
        });
        // override button js function
        zk.afterLoad("zul.wgt", function () {
            zk.override(zul.wgt.Button.prototype, _Bwgt, {
                // override doClick_ for store old pivottable scrollLeft
                doClick_: function (evt) {
                    _Bwgt.doClick_.apply(this, arguments); //call the original method
                    var id = this.id;
                    if (id == 'btnSortAsc'
                        || id == 'btnSortDesc') { // limit the affect target
                        // store the old scrollLeft of pivottable
                        pivot.Pivottable.pivottableOldLeft =
                            zk.Widget.$('$pivottable').$n('scroll').scrollLeft;
                    }
                }
            });
        });
    ]]></script>
</zk>

The majore part is the two button added, others are only make things better but not related to the sort feature.

The Result
View the demo flash on line
http://screencast.com/t/eD4z1UZjEWWG

You can find the flash file at github:
https://github.com/benbai123/ZK_Practice/blob/master/Components/demos/addon/SortPivottableColumn.swf

Reference
http://books.zkoss.org/wiki/ZK_Pivottable_Essentials


Download
The full project is at github
https://github.com/benbai123/ZK_Practice/tree/master/Components/projects/Addon_Practice/PivottableTest/SortPivottableColumn