potatoes and carrots no bones: ZK Pivottable: Get Distinct Values of Field

Introduction

This article how to get the distinct values of a row or column field when you click on it.

Pre-request

ZK Pivottable: Display Data in ZK Pivottable
http://ben-bai.blogspot.tw/2012/07/zk-pivottable-display-data-in-zk.html

ZK Basic MVC Pattern with SelectorComposer
http://ben-bai.blogspot.tw/2012/10/zk-basic-mvc-pattern-with_31.html

The Program

index.zul

A pivottable, a label that will show the info of clicked row/column field, a listbox that will show the distinct values of clicked row/column field

<zk>
    <!-- Tested with ZK 6.0.2 EE and ZK Pivottable 2.0.0 -->
    <!-- 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}" />
        <!-- label, show info of clicked row or column field -->
        <div style="margin: 15px;">
            <div>
                clicked field: <label id="lb" />
            </div>
            <!-- listbox, show all distinct values of clicked row or column field -->
            values<listbox id="lbx" />
        </div>
    </window>
</zk>

TestComposer.java

Listen to onPivotPopup event (will be fired when clicking on row/column/data cell), update field info and values list as needed.

package test;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.zkoss.pivot.event.PivotUIEvent;
import org.zkoss.pivot.impl.TabularPivotField;
import org.zkoss.pivot.impl.TabularPivotModel;
import org.zkoss.zk.ui.select.SelectorComposer;
import org.zkoss.zk.ui.select.annotation.Listen;
import org.zkoss.zk.ui.select.annotation.Wire;
import org.zkoss.zul.Label;
import org.zkoss.zul.Listbox;
import org.zkoss.zul.Listcell;
import org.zkoss.zul.Listitem;

/**
 * Tested with ZK 6.0.2 EE and ZK Pivottable 2.0.0
 *
 * @author benbai123
 */
@SuppressWarnings("rawtypes")
public class TestComposer extends SelectorComposer {

    private static final long serialVersionUID = -8249566421884806620L;

    @Wire
    Label lb; /** label, contains type and name of clicked row or column */
    @Wire
    Listbox lbx; /** listbox, contains distinct values of clicked row or column field */
    /** pivot model with the 'whole' raw data */
    private TabularPivotModel _pivotModel;
    /** model provider, provide the columns, raw data and pivot model */
    private PivotModelProvider _modelProvider = new PivotModelProvider();

    /**
     * map that contains all field index
     * use field name as the keys
     */
    private Map<String, Integer> _indexMap = new HashMap<String, Integer>();

    /**
     * Get pivottable's model
     * @return TabularPivotModel the pivottable's model
     * @throws Exception
     */
    public TabularPivotModel getPivotModel () throws Exception {
        if (_pivotModel == null) {
            _pivotModel = _modelProvider.getPivotModel();
        }
        return _pivotModel;
    }

    /**
     * update the value list and label if clicked on row or column field
     * @param e
     * @throws Exception 
     */
    @Listen("onPivotPopup = #pivottable")
    public void updateValueList (PivotUIEvent e) throws Exception {
        TabularPivotField field = getClickedField(e);
        if (field != null) {
            updateValueList(field);
        } else {
            lb.setValue("");
            lbx.getChildren().clear();
        }
    }
    /**
     * get the clicked row or column field
     * @param e
     */
    private TabularPivotField getClickedField (PivotUIEvent e) {
        TabularPivotField field = null;
        if (e != null
            && (e.getRowContext() == null
                || e.getColumnContext() == null)) {
            // not clicked on data field

            if (e.getRowContext() != null) {
                // clicked on row field
                field = _pivotModel.getRowFields()[e.getRowContext().getNode().getDepth() - 1];
            } else {
                // clicked on column field
                field = _pivotModel.getColumnFields()[e.getColumnContext().getNode().getDepth() - 1];
            }
        }
        return field;
    }
    /**
     * create the value list of clicked row/column field
     * @param field pivot field, used to get fieldName
     * @throws Exception 
     */
    @SuppressWarnings({ "unchecked" })
    private void updateValueList (TabularPivotField field) throws Exception {
        // clear old children
        lbx.getChildren().clear();

        List<String> columns = _modelProvider.getColumns();
        // index of field name in columns
        int index = getFieldIndex(columns, field.getFieldName());
        // raw data of pivot model
        Iterable<List<Object>> rawData = (Iterable<List<Object>>)getPivotModel().getRawData();
        // distinct values of the given field
        List<Object> distinctValues = getDistinctValues(rawData, columns, index);

        // create listitem for each value
        for (final Object value : distinctValues) {
            Listitem li = new Listitem();
            Listcell lc = new Listcell(value.toString());

            lc.setParent(li);
            li.setParent(lbx);
        }
        // update field info label
        lb.setValue("field type: " + field.getType() + ", field name: " + field.getFieldName());
    }
    /**
     * get all different values of a field
     * @param rawData the raw data to get different values from
     * @param columns all columns in pivot model
     * @param index the index to get value from list
     * @return
     */
    @SuppressWarnings({ "unchecked" })
    private static List getDistinctValues (Iterable<List<Object>> rawData, List<String> columns, int index) {
        // set used to hold distinct values
        Set s = new HashSet();
        // result list
        List result = new ArrayList();

        if (index == -1) return result;
        // add all value to set directly
        for (List<Object> data : rawData) {
            s.add(data.get(index));
        }
        // copy to list then sort the list
        for (Object o : s) {
            result.add(o);
        }
        Collections.sort(result);
        return result;
    }
    /**
     * get the index in raw data of a field
     * @param columns columns used in pivot model
     * @param fieldName name of the field to search index
     * @return int the found index
     */
    private int getFieldIndex (List<String> columns, String fieldName) {
        // search it from index map at first
        if (_indexMap.containsKey(fieldName)) {
            return _indexMap.get(fieldName);
        } else { // not found
            int index = -1;
            // search field name in columns
            for (int i = 0; i < columns.size(); i++) {
                if (columns.get(i).equals(fieldName)) {
                    index = i;
                    break;
                }
            }
            // store it to index map
            _indexMap.put(fieldName, index);
            return index;
        }
    }
}

PivotModelProvider.java

Simply provide pivot model.

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.impl.TabularPivotModel;

public class PivotModelProvider {
    /** pivot model with the 'whole' raw data */
    private TabularPivotModel _pivotModel;
    /**
     * Get pivottable's model
     * @return TabularPivotModel the pivottable's model
     * @throws Exception
     */
    public TabularPivotModel getPivotModel () {
        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 names
     * @return
     * @throws Exception
     */
    public List<List<Object>> getData() {
        List<List<Object>> result = new ArrayList<List<Object>>();
        Random r = new Random();

        for (int i = 0; i < 100; i++) {
            List<Object> data = new ArrayList<Object>();
            data.add("Row_Level_001 - " + (r.nextInt(5) + 1));
            data.add("Row_Level_002 - " + (r.nextInt(5) + 1));
            data.add("Row_Level_003 - " + (r.nextInt(5) + 1));
            data.add("Row_Level_004 - " + (r.nextInt(5) + 1));
            data.add("Column_Level_001 - " + (r.nextInt(5) + 1));
            data.add("Column_Level_002 - " + (r.nextInt(5) + 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"
        });
    }
}