Introduction
This article describe how to update the live data partially as needed and highlight changed cells based on their status.
* Basic concept:
If the values are changed but structure is the same, update data partially
If the structure is changed, set new pivot model to pivottable and sync status of each cell
Pre-request
Get Value from Pivot Model
http://ben-bai.blogspot.tw/2013/02/zk-pivottable-get-value-from-pivot-model.html
Compare Pivotmodel Structure
http://ben-bai.blogspot.tw/2013/02/zk-pivottable-compare-pivotmodel.html
Sync Structure of Pivot Model
http://ben-bai.blogspot.tw/2013/02/zk-pivottable-sync-structure-of-pivot.html
Compare Value of Specific Cell
http://ben-bai.blogspot.tw/2013/02/zk-pivottable-compare-value-of-specific.html
Update Pivottable by Javascript
http://ben-bai.blogspot.tw/2013/02/zk-pivottable-update-pivottable-by.html
The Program
index.zul
A pivottable, a textbox to display the added data, a button to add data, a pivot field control to control the fields in pivottable, also contains the javascript function to update the cells of pivottable.
<zk>
<!-- Tested with ZK 6.0.2 EE and ZK Pivottable 2.0.0 -->
<script><![CDATA[
function update (items) {
if (items && items.length > 0) {
var dataRow = jq('.z-pivottable-cell-field')[0].parentNode,
style,
item,
rowIdx,
colIdx,
val,
idx,
currentRow = 0,
dir;
// for each item
for (idx = 0; idx < items.length; idx++) {
item = items[idx];
rowIdx = item.rowIdx;
colIdx = item.colIdx;
val = item.val;
dir = item.dir;
style = '';
// find row
while (currentRow < rowIdx) {
dataRow = dataRow.nextSibling;
currentRow++;
}
// find cell
cell = jq(dataRow).find('td')[colIdx];
// create style
if (dir) style = (dir == 'up'? 'style="color: green;"' : 'style="color: red;"');
// update cell with style
cell.firstChild.innerHTML = '<span '+style+'>' + val + '</span>';
}
}
}
]]></script>
<!-- window, apply a SelectorComposer -->
<window id="win" apply="test.PVTPartialUpdateComposer">
<vlayout>
<textbox id="tbx" value="Added values: " width="800px" rows="10" />
<!-- pivottable, get model from window's composer -->
<pivottable id="pivottable" model="${win$composer.pivotModel}"
use="test.CustomPivottable"
pageSize="9999" />
<button id="btn" label="test add" />
<pivot-field-control id="pfc" />
</vlayout>
</window>
</zk>
PVTPartialUpdateComposer.java
Update changed cells or sync all cell as needed.
package test;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import org.zkoss.pivot.Calculator;
import org.zkoss.pivot.PivotHeaderNode;
import org.zkoss.pivot.Pivottable;
import org.zkoss.pivot.impl.TabularPivotField;
import org.zkoss.pivot.impl.TabularPivotModel;
import org.zkoss.pivot.ui.PivotFieldControl;
import org.zkoss.zk.ui.Component;
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.zk.ui.util.Clients;
import org.zkoss.zul.Textbox;
/**
* Tested with ZK 6.0.2 EE and ZK Pivottable 2.0.0
* @author benbai123
*
*/
@SuppressWarnings("rawtypes")
public class PVTPartialUpdateComposer extends SelectorComposer {
/**
* generated serial version UID
*/
private static final long serialVersionUID = -2897873399288955635L;
@Wire
private Textbox tbx;
@Wire
private Pivottable pivottable;
@Wire
private PivotFieldControl pfc;
private DecimalFormat floatFormat = new DecimalFormat("##,###.00");
private DecimalFormat intFormat = new DecimalFormat("##,###");
// model used by pivottable
// the model used by pivottable currently
private TabularPivotModel _pivotModel;
// copy of first model
// used to determine the status of cells
private TabularPivotModel _pivotModelFirstSnapshot;
// model contains the newest raw data
private TabularPivotModel _latestPivotModel;
// used to store the value of current view
private List<Number> _currentValues = null;
// newest raw data
private List<List<Object>> _latestRawData;
@SuppressWarnings("unchecked")
public void doAfterCompose (Component comp) throws Exception {
super.doAfterCompose(comp);
// init pivot field control
pfc.setModel((TabularPivotModel)pivottable.getModel());
}
/**
* Get pivottable's model, also make a snapshot of it
* @return TabularPivotModel the pivottable's model
* @throws Exception
*/
@SuppressWarnings("unchecked")
public TabularPivotModel getPivotModel () throws Exception {
if (_pivotModel == null) {
_pivotModel = PVTModelProvider.getPivotModel();
_pivotModelFirstSnapshot = PVTUtils.createModelSnapshot(_pivotModel);
// init latest raw data
_latestRawData = new ArrayList<List<Object>>();
_latestRawData.addAll((List<List<Object>>)_pivotModel.getRawData());
// init latest pivot model
_latestPivotModel = _pivotModel;
}
return _pivotModel;
}
/**
* Display the current content of pivottable
* @throws Exception
*/
@Listen ("onClick = #btn")
public void addData () throws Exception {
// used to collect new data to display in textbox
StringBuilder sb = new StringBuilder("");
List<List<Object>> newData = PVTModelProvider.getNewDatas(sb);
if (newData != null && newData.size() > 0) {
tbx.setValue(tbx.getValue() + "\n" + sb.toString());
_latestRawData.addAll(newData);
_latestPivotModel = PVTUtils.cloneModelWithData(_pivotModel, _latestRawData);
if (PVTUtils.isStructureEqual(_pivotModel, _latestPivotModel, true, false)) {
updateChangedData(_pivotModel, _latestPivotModel);
} else {
// clear stored value if structure is changed
_currentValues = null;
pivottable.setModel(_latestPivotModel);
pfc.setModel((TabularPivotModel)pivottable.getModel());
doUpdate(true);
_pivotModel = _latestPivotModel;
}
}
}
/**
* called when a node is opened/closed
*/
@Listen ("onPivotNodeOpen = #pivottable")
public void onPvtOpen () {
syncOrReplace();
}
/**
* called when the fields of pivottable are changed
*/
@Listen ("onPivotFieldControlChange = #pfc")
public void syncModelStructure () {
PVTUtils.syncModelStructure(_pivotModel, _pivotModelFirstSnapshot);
syncOrReplace();
}
/**
* sync model structure and all cell status as needed
*/
private void syncOrReplace () {
// drop old values however
_currentValues = null;
// sync model structure if not the same instance
if (_pivotModel != _latestPivotModel) {
PVTUtils.syncModelStructure(_pivotModel, _latestPivotModel);
}
if (_pivotModel == _latestPivotModel
|| PVTUtils.isStructureEqual(_pivotModel, _latestPivotModel, true, false)) {
doUpdate(true);
} else {
pivottable.setModel(_latestPivotModel);
pfc.setModel((TabularPivotModel)pivottable.getModel());
doUpdate(true);
_pivotModel = _latestPivotModel;
}
}
/**
* update changed data only
* @param currentModel
* @param newModel
*/
private void updateChangedData (TabularPivotModel currentModel, TabularPivotModel newModel) {
// keep it as long as possible to reduce processing time,
// another choice is drop it each time to reduce memory consumption
if (_currentValues == null) {
storeValue();
}
doUpdate(false);
}
/**
* store values of current view of pivottable
*/
private void storeValue () {
_currentValues = new ArrayList<Number>();
List<PivotHeaderNode> rows = PVTUtils.getRowLeafList(_pivotModel);
List<PivotHeaderNode> columns = PVTUtils.getColumnLeafList(_pivotModel);
rows.add(_pivotModel.getRowHeaderTree().getRoot());
columns.add(_pivotModel.getColumnHeaderTree().getRoot());
for (PivotHeaderNode row : rows) { // for each row
// store original data
storeCurrentValues(_pivotModel, row, columns, -1);
// not first level row node
if (row.getDepth() > 1) {
PivotHeaderNode tmpRow = row;
PivotHeaderNode parentRow = tmpRow.getParent();
// continuously check ancestors
// do if not first level and is last child
while (tmpRow.getDepth() > 1
&& PVTUtils.isLastChild(parentRow, tmpRow)) {
for (int calIdx = 0; calIdx < parentRow.getSubtotalCount(false); calIdx++) {
// store row subtotal
storeCurrentValues(_pivotModel, parentRow, columns, calIdx);
}
tmpRow = parentRow;
parentRow = tmpRow.getParent();
}
}
}
}
/** loop through cells of a row
* store values of current view of pivottable
*
* @param model the model of current pivottable
* @param row the row to loop through each cell
* @param columns columns of row
* @param rowCalIdx calculator index of row
*/
private void storeCurrentValues (TabularPivotModel model,
PivotHeaderNode row, List<PivotHeaderNode> columns, int rowCalIdx) {
// the length of data fields under a column
int dataFieldsLength = model.getDataFields().length;
// for each column
for (PivotHeaderNode column : columns) {
// for each data field
for (int i = 0; i < dataFieldsLength; i++) {
// get data value from pivot model by row node, column node and data index
_currentValues.add(model.getValue(row, rowCalIdx, column, -1, i));
// last data and
// not first level node
if (i+1 == dataFieldsLength
&& column.getDepth() > 1) {
PivotHeaderNode tmpCol = column;
PivotHeaderNode parentColumn = tmpCol.getParent();
// continuously check ancestors
// do if not first level and is last child
while (tmpCol.getDepth() > 1
&& PVTUtils.isLastChild(parentColumn, tmpCol)) {
// for each column calculator
for (int calIdx = 0; calIdx < parentColumn.getSubtotalCount(false); calIdx++) {
// again, for each data field
for (int j = 0; j < dataFieldsLength; j++) {
// get data value from pivot model by row node, column node and data index
_currentValues.add(model.getValue(row, rowCalIdx, parentColumn, calIdx, j));
}
}
tmpCol = parentColumn;
parentColumn = tmpCol.getParent();
}
}
}
}
}
/**
* execute script to update client side cells of pivottable
* @param syncAll true: sync status of each cell, false: update changed cell only
*/
private void doUpdate (boolean syncAll) {
// used to store the update script
StringBuilder sb = new StringBuilder("");
// get row/column nodes from latest pivot model
List<PivotHeaderNode> rows = PVTUtils.getRowLeafList(_latestPivotModel);
List<PivotHeaderNode> columns = PVTUtils.getColumnLeafList(_latestPivotModel);
// add column root for grand total for columns
columns.add(_latestPivotModel.getColumnHeaderTree().getRoot());
// add row root for grand total for rows
rows.add(_latestPivotModel.getRowHeaderTree().getRoot());
// init Position object, track the index of row/cell,
// also track index of current value if not syncAll as needed
Position pos = new Position(0, 0);
// init CellAttributes object, track the attributes of a cell while loop through nodes
CellAttributes cell = new CellAttributes(null, null, null, null, null);
// script start
sb.append("update([");
// iterate through current view to add the elements to update
for (PivotHeaderNode row : rows) { // for each row
// add row keys and clear other attributes
cell.updateAttributes(PVTUtils.getNodeKeys(row), null, null, null, null);
// add elements to update
addUpdateElements(_latestPivotModel, row, columns, -1, sb, pos, cell, syncAll);
// to the first column of next row
pos.toNextRow();
// not first level row node,
// have to check and update parent sub-total if
// current row is the last child
if (row.getDepth() > 1) {
PivotHeaderNode tmpRow = row;
PivotHeaderNode parentRow = tmpRow.getParent();
// continuously check ancestors
// do if not first level and is last child
while (tmpRow.getDepth() > 1
&& PVTUtils.isLastChild(parentRow, tmpRow)) {
parentRow = tmpRow.getParent();
for (int calIdx = 0; calIdx < parentRow.getSubtotalCount(false); calIdx++) {
cell.setRowKeys(PVTUtils.getNodeKeys(parentRow));
cell.setRowCalculatorLabelKey(parentRow.getField().getSubtotals()[calIdx].getLabelKey());
addUpdateElements(_latestPivotModel, parentRow, columns, calIdx, sb, pos, cell, syncAll);
pos.toNextRow();
}
tmpRow = parentRow;
parentRow = tmpRow.getParent();
}
}
}
// remove last comma
if (sb.substring(sb.length()-1).equals(",")) {
sb.replace(sb.length()-1, sb.length(), "");
}
sb.append("]);");
String cmd = sb.toString();
if (!"update([]);".equals(cmd)) {
Clients.evalJavaScript(sb.toString());
}
sb.setLength(0);
}
/**
* loop through whole row to check
* and add elements to update as needed
* @param model the latest model
* @param row the row to loop through
* @param columns all columns of row
* @param rowCalIdx calculator index of row
* @param sb StringBuilder to append elements to update
* @param pos Position class to track current position and pointer of old value list
* @param cell CellAttributes class to track cell information
* @param syncAll whether to sync all cell status or update changed value only
*/
private void addUpdateElements (TabularPivotModel model,
PivotHeaderNode row, List<PivotHeaderNode> columns, int rowCalIdx,
StringBuilder sb, Position pos, CellAttributes cell, boolean syncAll) {
TabularPivotField[] dataFields = model.getDataFields();
// the length of data fields under a column
int dataFieldsLength = dataFields.length;
for (PivotHeaderNode column : columns) { // for each column
cell.setColKeys(PVTUtils.getNodeKeys(column));
// for each data field
for (int i = 0; i < dataFieldsLength; i++) {
cell.setDataFieldName(dataFields[i].getFieldName());
addUpdateElement(_pivotModelFirstSnapshot, _currentValues, cell,
model.getValue(row, rowCalIdx, column, -1, i), pos, sb, syncAll);
// get data value from pivot model by row node, column node and data index
pos.increaseCol();
// last data and
// not first level node
if (i+1 == dataFieldsLength
&& column.getDepth() > 1) {
PivotHeaderNode tmpCol = column;
PivotHeaderNode parentColumn = tmpCol.getParent();
// continuously check ancestors
// do if not first level and is last child
while (tmpCol.getDepth() > 1
&& PVTUtils.isLastChild(parentColumn, tmpCol)) {
cell.setColKeys(PVTUtils.getNodeKeys(parentColumn));
// for each column calculator
for (int calIdx = 0; calIdx < parentColumn.getSubtotalCount(false); calIdx++) {
Calculator cal = parentColumn.getField().getSubtotals()[calIdx];
cell.setColCalculatorLabelKey(cal.getLabelKey());
// again, for each data field
for (int j = 0; j < dataFieldsLength; j++) {
cell.setDataFieldName(dataFields[j].getFieldName());
addUpdateElement(_pivotModelFirstSnapshot, _currentValues, cell,
model.getValue(row, rowCalIdx, parentColumn, calIdx, j), pos, sb, syncAll);
// get data value from pivot model by row node, column node and data index
pos.increaseCol();
}
}
tmpCol = parentColumn;
parentColumn = tmpCol.getParent();
}
// clear column calculator if any before next loop
cell.setColCalculatorLabelKey(null);
}
}
}
}
/**
* add the element to update to StringBuilder as needed
* @param basemodel the model as the compare base
* @param valueList the list of old values (might be null if not syncAll)
* @param cell attributes describe current cell
* @param value latest value of current cell
* @param pos position of the row/column in current view of pivottable
* @param sb StringBuilder contains update script
* @param syncAll whether sync all cell or update changed cell only
*/
private void addUpdateElement (TabularPivotModel basemodel, List<Number> valueList,
CellAttributes cell, Number value, Position pos, StringBuilder sb, boolean syncAll) {
String dir;
// sync all
if (syncAll) {
dir = PVTUtils.getDirection(basemodel, cell, value);
// status 'up' or 'down'
if (dir != null && !dir.isEmpty()) {
addUpdateElement(pos.getRowIdx(), pos.getColIdx(), value, dir, sb);
}
} else {
// sync changed
int ptr = pos.getPtr();
if (value != null) {
Number oldv = valueList.get(ptr);
double valToComp = (oldv == null? 0 : oldv.doubleValue());
// value changed
if (value.doubleValue() != valToComp) {
dir = PVTUtils.getDirection(basemodel, cell, value);
addUpdateElement(pos.getRowIdx(), pos.getColIdx(), value, dir, sb);
// update value list
valueList.set(ptr, value);
}
}
}
}
private void addUpdateElement (int rowIdx, int colIdx, Number val, String dir, StringBuilder sb) {
sb.append("{rowIdx:")
.append(rowIdx).append(",")
.append("colIdx:")
.append(colIdx).append(",")
.append("val:'")
.append(val instanceof Integer ? intFormat.format(val) : floatFormat.format(val)).append("',");
if (dir != null) {
sb.append("dir:")
.append("'").append(dir).append("'");
}
sb.append("},");
}
}
PVTModelProvider.java
Provide first pivot model and add data.
package test;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;
import org.zkoss.pivot.Calculator;
import org.zkoss.pivot.PivotField;
import org.zkoss.pivot.impl.StandardCalculator;
import org.zkoss.pivot.impl.TabularPivotModel;
/**
* Provide pivot model, create new data
* @author benbai123
*
*/
public class PVTModelProvider {
/**
* Get pivottable's model, also make a snapshot of it
* @return TabularPivotModel the pivottable's model
* @throws Exception
*/
public static TabularPivotModel getPivotModel () throws Exception {
List<List<Object>> rawData = getData();
TabularPivotModel pivotModel;
pivotModel = new TabularPivotModel(rawData, getColumns());
// assign rows, the order matches to the level of row node field
pivotModel.setFieldType("RowOne", PivotField.Type.ROW);
pivotModel.setFieldType("RowTwo", PivotField.Type.ROW);
pivotModel.setFieldType("RowThree", PivotField.Type.ROW);
// assign columns, the order matches to the level of column node field
pivotModel.setFieldType("ColumnOne", PivotField.Type.COLUMN);
pivotModel.setFieldType("ColumnTwo", PivotField.Type.COLUMN);
pivotModel.setFieldType("ColumnThree", PivotField.Type.COLUMN);
// assign datas, the order matches to the order of data field
pivotModel.setFieldType("DataOne", PivotField.Type.DATA);
pivotModel.setFieldType("DateTwo", PivotField.Type.DATA);
PivotField field = pivotModel.getField("RowOne");
pivotModel.setFieldSubtotals(field, new Calculator[] {StandardCalculator.SUM, StandardCalculator.MAX});
field = pivotModel.getField("RowTwo");
pivotModel.setFieldSubtotals(field, new Calculator[] {StandardCalculator.SUM, StandardCalculator.MAX});
field = pivotModel.getField("ColumnOne");
pivotModel.setFieldSubtotals(field, new Calculator[] {StandardCalculator.SUM, StandardCalculator.MAX});
field = pivotModel.getField("ColumnTwo");
pivotModel.setFieldSubtotals(field, new Calculator[] {StandardCalculator.SUM, StandardCalculator.MAX});
return pivotModel;
}
/**
* prepare the data for pivottable's model
* The order of object put into data list should match
* the order of column name's
* @return
* @throws Exception
*/
public static List<List<Object>> getData() throws Exception {
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("RowOne - " + (r.nextInt(2) + 1));
data.add("RowTwo - " + (r.nextInt(2) + 1));
data.add("RowThree - " + (r.nextInt(2) + 1));
data.add("ColumnOne - " + (r.nextInt(2) + 1));
data.add("ColumnTwo - " + (r.nextInt(2) + 1));
data.add("ColumnThree - " + (r.nextInt(2) + 1));
data.add(r.nextInt(10));
data.add(r.nextInt(10));
result.add(data);
}
return result;
}
/**
* prepare columns name for pivottable's model
* @return
*/
public static List<String> getColumns() {
return Arrays.asList(new String[]{
"RowOne", "RowTwo", "RowThree",
"ColumnOne", "ColumnTwo", "ColumnThree",
"DataOne", "DateTwo"
});
}
/**
* Generate some random data
* @return
*/
public static List<List<Object>> getNewDatas(StringBuilder sb) {
List<List<Object>> result = new ArrayList<List<Object>>();
Random r = new Random();
int amount = 1;
for (int i = 0; i < amount; i++) {
List<Object> data = new ArrayList<Object>();
Object o;
o = "RowOne - " + (r.nextInt(5) + 1);
sb.append("add: ")
.append(o)
.append("\t");
data.add(o);
o = "RowTwo - " + (r.nextInt(5) + 1);
sb.append(o)
.append("\t");
data.add(o);
o = "RowThree - " + (r.nextInt(5) + 1);
sb.append(o)
.append("\t");
data.add(o);
o = "ColumnOne - " + (r.nextInt(5) + 1);
sb.append(o)
.append("\t");
data.add(o);
o = "ColumnTwo - " + (r.nextInt(5) + 1);
sb.append(o)
.append("\t");
data.add(o);
o = "ColumnThree - " + (r.nextInt(5) + 1);
sb.append(o)
.append("\t");
data.add(o);
o = -5 + r.nextInt(11); // -5 ~ 5
sb.append(o)
.append("\t");
data.add(o);
o = -5 + r.nextInt(11);
sb.append(o)
.append("\t\n");
data.add(o);
result.add(data);
}
return result;
}
}
PVTUtils.java
A bunch of utilities to handle/retrieve status of pivot model, most of them are part of previous posts.
package test;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.zkoss.pivot.Calculator;
import org.zkoss.pivot.PivotField;
import org.zkoss.pivot.PivotHeaderNode;
import org.zkoss.pivot.PivotHeaderTree;
import org.zkoss.pivot.impl.TabularPivotField;
import org.zkoss.pivot.impl.TabularPivotModel;
/**
* Utilities to handle/retrieve pivot model status
* @author benbai123
*
*/
public class PVTUtils {
public static int INDEX_NOT_FOUND = -2;
public static PivotHeaderNode NODE_NOT_FOUND = null;
/**
* Create snapshot of a pivot model
* @param model
* @return
*/
@SuppressWarnings("unchecked")
public static TabularPivotModel createModelSnapshot (TabularPivotModel model) {
// copy raw data
List<List<Object>> rawData = new ArrayList<List<Object>>();
rawData.addAll((List<List<Object>>)model.getRawData());
TabularPivotField[] fields = model.getFields();
// get columns from old model
List<String> columns = new ArrayList<String>();
// set field
for (TabularPivotField tpf : fields) {
columns.add(tpf.getFieldName());
}
TabularPivotModel snapShot = new TabularPivotModel(rawData, columns);
syncModelStructure(model, snapShot);
return snapShot;
}
/**
* Create a new pivot model based on
* current pivot model and new data
* @param model
* @param newData
* @return
*/
public static TabularPivotModel cloneModelWithData (TabularPivotModel model, List<List<Object>>newData) {
TabularPivotField[] fields = model.getFields();
// get columns from old model
List<String> columns = new ArrayList<String>();
// set field
for (TabularPivotField tpf : fields) {
columns.add(tpf.getFieldName());
}
TabularPivotModel newModel = new TabularPivotModel(newData, columns);
PVTUtils.syncModelStructure(model, newModel);
return newModel;
}
/**
* called when the fields of first pivottable are changed
*/
public static void syncModelStructure (TabularPivotModel model, TabularPivotModel modelTwo) {
syncFields(model.getRowFields(), modelTwo);
syncFields(model.getColumnFields(), modelTwo);
syncFields(model.getDataFields(), modelTwo);
syncFields(model.getFields(PivotField.Type.UNUSED), modelTwo);
syncOpenStatus(model.getRowHeaderTree().getRoot(), modelTwo.getRowHeaderTree().getRoot(), false);
syncOpenStatus(model.getColumnHeaderTree().getRoot(), modelTwo.getColumnHeaderTree().getRoot(), false);
}
private static void syncFields (TabularPivotField[] fields, TabularPivotModel model) {
for (TabularPivotField f : fields) {
model.setFieldType(f.getFieldName(), f.getType());
PivotField field = model.getField(f.getFieldName());
model.setFieldSubtotals(field, f.getSubtotals());
}
}
/**
* Synchronize the open status of two pivot header trees
*
* @param root the root of the base pivot header tree (or its sub trees)
* @param rootTwo the root of the pivot header tree (or its sub trees) to sync
* @param checkAll whether sync whole tree, <br>
* true: sync whole tree, put every node of base pivot header tree into open list to sync<br>
* false: sync only current view, only put the displayed node into open list to sync
*/
private static void syncOpenStatus (PivotHeaderNode root, PivotHeaderNode rootTwo, boolean checkAll) {
Map<Object, PivotHeaderNode> originalOpenMap = new HashMap<Object, PivotHeaderNode>();
// sync displayed node only if not checkAll
// so do not need to scan whole header tree
for (PivotHeaderNode node : root.getChildren()) {
// checkAll: sync each node
// !checkAll: sync displayed node
if (checkAll
|| (node.getDepth() == 1 || node.getParent().isOpen())) {
originalOpenMap.put(node.getKey(), node);
}
}
// for each node in children of rootTwo
for (PivotHeaderNode newNode : rootTwo.getChildren()) {
PivotHeaderNode node = originalOpenMap.get(newNode.getKey());
if (node != null) {
newNode.setOpen(node.isOpen());
// recursively sync sub trees
syncOpenStatus(node, newNode, checkAll);
}
}
}
/**
* Check whether two pivot models are structure-equal
* @param modelOne the first pivot model
* @param modelTwo the second pivot model
* @param openedOnly whether only check the opened nodes and leaf
* @param openedOnly whether only check the leaf nodes
* @return boolean
*/
public static boolean isStructureEqual (TabularPivotModel modelOne, TabularPivotModel modelTwo, boolean openedOnly, boolean leafOnly) {
boolean equal = true;
List<PivotHeaderNode> rows = getNodeList(modelOne.getRowHeaderTree(), openedOnly, leafOnly);
List<PivotHeaderNode> columns = getNodeList(modelOne.getColumnHeaderTree(), openedOnly, leafOnly);
List<PivotHeaderNode> rowsTwo = getNodeList(modelTwo.getRowHeaderTree(), openedOnly, leafOnly);
List<PivotHeaderNode> columnsTwo = getNodeList(modelTwo.getColumnHeaderTree(), openedOnly, leafOnly);
TabularPivotField[] dataFields = modelOne.getDataFields();
TabularPivotField[] dataFieldsTwo = modelTwo.getDataFields();
if (rows.size() != rowsTwo.size()
|| columns.size() != columnsTwo.size()
|| dataFields.length != dataFieldsTwo.length) {
equal = false;
} else {
equal = isNodesEqual(rows, rowsTwo, openedOnly) && isNodesEqual(columns, columnsTwo, openedOnly);
}
return equal;
}
/**
* check whether a node is the last child of specified parent node
* @param parent
* @param node
* @return
*/
public static boolean isLastChild (PivotHeaderNode parent, PivotHeaderNode node) {
List<? extends PivotHeaderNode> children = parent.getChildren();
// use == to make sure they are the same instance
return node.getParent() == parent
&& children.get(children.size()-1) == node;
}
public static List<PivotHeaderNode> getRowLeafList (TabularPivotModel model) {
return PVTUtils.getNodeList(model.getRowHeaderTree(), true, true);
}
public static List<PivotHeaderNode> getColumnLeafList (TabularPivotModel model) {
return PVTUtils.getNodeList(model.getColumnHeaderTree(), true, true);
}
/**
* Get pivot nodes in a pivot header tree
* @param headerTree the pivot header tree to get pivot nodes
* @param openedOnly whether get only the opened nodes and leaf
* @param leafOnly whether get only leaf node
* @return
*/
@SuppressWarnings("unchecked")
public static List<PivotHeaderNode> getNodeList (PivotHeaderTree headerTree, boolean openedOnly, boolean leafOnly) {
PivotHeaderNode root = headerTree.getRoot();
List<PivotHeaderNode> all = new ArrayList<PivotHeaderNode>();
List<PivotHeaderNode> nodes = new ArrayList<PivotHeaderNode>();
List<PivotHeaderNode> tmp = new ArrayList<PivotHeaderNode>();
nodes = (List<PivotHeaderNode>)root.getChildren();
// all: all target nodes
// nodes: the node list to loop through
// tmp: temp store the children while loop through nodes
boolean foundAllNodes = false;
while (!foundAllNodes) {
foundAllNodes = true;
for (PivotHeaderNode phn : nodes) {
// get only opened and leaf nodes
// if opened only
if (phn.isOpen() || !openedOnly) {
List<PivotHeaderNode> children = (List<PivotHeaderNode>)phn.getChildren();
if (children != null && children.size() > 0) {
tmp.addAll(children);
foundAllNodes = false;
} else {
// add to all if haven't found any children
// so do not need to loop through them again
if (foundAllNodes && leafOnly) {
all.add(phn);
} else {
// already found some children,
// add to tmp to make the order correct
tmp.add(phn);
}
}
} else {
if (foundAllNodes && leafOnly) {
all.add(phn);
} else {
tmp.add(phn);
}
}
}
if (!leafOnly) {
all.addAll(nodes);
}
nodes = tmp;
tmp = new ArrayList<PivotHeaderNode>();
}
return all;
}
/**
* Compare the nodes under two node lists one by one
* @param list the first node list
* @param listTwo the second node list
* @param openedOnly whether compare calculator according to open status
* @return
*/
private static boolean isNodesEqual (List<PivotHeaderNode> list, List<PivotHeaderNode> listTwo, boolean openedOnly) {
boolean equal = true;
int i, j;
// compare nodes
for (i = 0; i < list.size() && equal; i++) {
PivotHeaderNode node = list.get(i);
PivotHeaderNode nodeTwo = listTwo.get(i);
// key should be equal
// depth should be equal
// subtotal count should be equal
//
// openedOnly: get subtotal count according to the open stats of node
// !openedOnly: get subtotal count as the node is opened
if (!node.getKey().equals(nodeTwo.getKey())
|| node.getDepth() != nodeTwo.getDepth()
|| node.getSubtotalCount(node.isOpen() || !openedOnly) != nodeTwo.getSubtotalCount(nodeTwo.isOpen() || !openedOnly)) {
equal = false;
break;
}
// check calculators if any
// openedOnly: get subtotal count according to the open stats of node
// !openedOnly: get subtotal count as the node is opened
if (node.getSubtotalCount(node.isOpen() || !openedOnly) > 0) {
Calculator[] cals = node.getField().getSubtotals();
Calculator[] calsTwo = nodeTwo.getField().getSubtotals();
for (j = 0; j < cals.length; j++) {
Calculator cal = cals[j];
Calculator calTwo = calsTwo[j];
// label and label key should be euqal
if (!cal.getLabel().equals(calTwo.getLabel())
|| !cal.getLabelKey().equals(calTwo.getLabelKey())) {
equal = false;
break;
}
}
}
}
return equal;
}
/**
* get a list of key to this node
* @param node pivot header node
* @return
*/
public static List<Object> getNodeKeys (PivotHeaderNode node) {
List<Object> keys = new ArrayList<Object>();
if (node != null) {
while (node.getDepth() > 0) {
keys.add(0, node.getKey());
node = node.getParent();
}
}
return keys;
}
/**
* get the status of a cell
* @param model the base pivot model to compare
* @param cell the attributes denote a cell
* @param value the new value
* @return<br>
* "up": new value is larger than old value of that cell in base pivot model<br>
* "down": new value is smaller than old value of that cell in base pivot model<br>
* null: otherwise
*/
public static String getDirection (TabularPivotModel model, CellAttributes cell, Number value) {
String dir = null;
double base = 0.0;
if (value != null) {
Number oldValue = getValue(model, cell);
if (oldValue != null) {
base = oldValue.doubleValue();
}
double newValue = value.doubleValue();
dir = newValue > base? "up" :
newValue < base? "down" : null;
}
return dir;
}
/**
* get value from a model based on cellAttr
* @param model the model to get value
* @param cellAttr the attributes represent a specific cell
* @return
*/
public static Number getValue (TabularPivotModel model, CellAttributes cellAttr) {
PivotHeaderNode row = findNode(model.getRowHeaderTree().getRoot(), cellAttr.getRowKeys());
PivotHeaderNode col = findNode(model.getColumnHeaderTree().getRoot(), cellAttr.getColKeys());
int dataIdx = findDataFieldIndex(model, cellAttr.getDataFieldName());
int rowCalIdx = -1;
int colCalIdx = -1;
if (row != null) {
rowCalIdx = findCalculatorIndex(row, cellAttr.getRowCalculatorLabelKey());
}
if (col != null) {
colCalIdx = findCalculatorIndex(col, cellAttr.getColCalculatorLabelKey());
}
if (row == NODE_NOT_FOUND // row should exists
|| col == NODE_NOT_FOUND // col should exists
|| dataIdx == INDEX_NOT_FOUND // data field should exists
|| rowCalIdx == INDEX_NOT_FOUND // row calculator should exists if _rowCalculatorLabelKey is not null
|| colCalIdx == INDEX_NOT_FOUND) { // column calculator should exists if _colCalculatorLabelKey is not null
return null;
}
return model.getValue(row, rowCalIdx, col, colCalIdx, dataIdx);
}
/**
* find the corresponding node in a pivot model
* @param root The root of pivot header tree to search the corresponding node
* @param keys list of node key
* @return PivotHeaderNode the corresponding node, NODE_NOT_FOUND denotes not found
*/
public static PivotHeaderNode findNode (PivotHeaderNode root, List<Object> keys) {
PivotHeaderNode node = null;
boolean found = true;
node = root;
if (keys == null || keys.size() == 0) {
return root; // grand total
}
// for each key
for (Object o : keys) {
if (found) { // stop if not found in previous loop
found = false;
// search until found a row with the key
for (PivotHeaderNode child : node.getChildren()) {
if (child.getKey().equals(o)) {
node = child;
found = true;
break;
}
}
}
}
return found? node : // header nood
NODE_NOT_FOUND; // not found
}
/**
* Search the corresponding data field index in a pivot model
* @param model the model to search
* @param fieldName the name of data field
* @return int the corresponding data field index, INDEX_NOT_FOUND denotes not found
*/
public static int findDataFieldIndex (TabularPivotModel model, String fieldName) {
// field name should not be null
if (fieldName != null) {
TabularPivotField[] dataFields = model.getDataFields();
for (int idx = 0; idx < dataFields.length; idx++) {
if (dataFields[idx].getFieldName().equals(fieldName)) {
return idx;
}
}
}
return INDEX_NOT_FOUND; // not found
}
/**
* Search the corresponding calculator index of a pivot field
* @param field the field to search
* @param labelKey the labelKey of calculator
* @return int the corresponding calculator index, INDEX_NOT_FOUND denotes not found
*/
public static int findCalculatorIndex (PivotHeaderNode node, String labelKey) {
if (labelKey == null) return -1;
Calculator[] cals = node.getField().getSubtotals();
for (int i = 0; i < cals.length; i++) {
if (cals[i].getLabelKey().equals(labelKey)) {
return i;
}
}
return INDEX_NOT_FOUND; // not found
}
}
CustomPivottable.java
Post the event of node open/close of pivottable.
package test;
import org.zkoss.pivot.Pivottable;
import org.zkoss.zk.au.AuRequest;
import org.zkoss.zk.ui.event.Events;
public class CustomPivottable extends Pivottable {
private static final long serialVersionUID = 4770700380255057252L;
public void service(AuRequest request, boolean everError) {
String cmd = request.getCommand();
super.service(request, everError);
// post onPivotNodeOpen event
if ("onPivotNodeOpen".equals(cmd)) {
Events.postEvent("onPivotNodeOpen", this, request.getData());
}
}
}
Position.java
Attributes used to denote a cell in current view of pivottable
package test;
/**
* attributes used to denote a cell in current view of pivottable
* and the index points to old value while
* loop through values and build update script as needed
* @author benbai123
*
*/
public class Position {
// index of current row in the view
private int _rowIdx = 0;
// index of current column in the view
private int _colIdx = 0;
// index to get stored value
private int _ptr = 0;
// constructor
public Position (int rowIdx, int colIdx) {
_rowIdx = rowIdx;
_colIdx = colIdx;
}
// move to next row
public void toNextRow () {
increaseRow();
resetCol();
}
// control
public void increaseRow () {
_rowIdx++;
}
public void increaseCol () {
_colIdx++;
}
public void resetCol () {
_colIdx = 0;
}
// getters
public int getRowIdx () {
return _rowIdx;
}
public int getColIdx () {
return _colIdx;
}
public int getPtr () {
_ptr ++;
return _ptr - 1;
}
}
CellAttributes.java
Attributes used to denotes a specific cell
package test;
import java.util.List;
/**
* attributes used to denotes a specific cell
* @author benbai123
*
*/
public class CellAttributes {
// keys to denote a row node
private List<Object> _rowKeys;
// keys to denote a column node
private List<Object> _colKeys;
// name to denotes a data field
private String _dataFieldName;
// labelKey to denote a row calculator
private String _rowCalculatorLabelKey;
// labelKey to denote a column calculator
private String _colCalculatorLabelKey;
// constructor
public CellAttributes (List<Object> rowKeys, List<Object> colKeys, String dataFieldName,
String rowCalculatorLabelKey, String colCalculatorLabelKey) {
_rowKeys = rowKeys;
_colKeys = colKeys;
_dataFieldName = dataFieldName;
_rowCalculatorLabelKey = rowCalculatorLabelKey;
_colCalculatorLabelKey = colCalculatorLabelKey;
}
/**
* used to reset all attributes
* @param rowKeys
* @param colKeys
* @param dataFieldName
* @param rowCalculatorLabelKey
* @param colCalculatorLabelKey
*/
public void updateAttributes (List<Object> rowKeys, List<Object> colKeys, String dataFieldName,
String rowCalculatorLabelKey, String colCalculatorLabelKey) {
_rowKeys = rowKeys;
_colKeys = colKeys;
_dataFieldName = dataFieldName;
_rowCalculatorLabelKey = rowCalculatorLabelKey;
_colCalculatorLabelKey = colCalculatorLabelKey;
}
// setters/getters
public void setRowKeys (List<Object> rowKeys) {
_rowKeys = rowKeys;
}
public List<Object> getRowKeys () {
return _rowKeys;
}
public void setColKeys (List<Object> colKeys) {
_colKeys = colKeys;
}
public List<Object> getColKeys () {
return _colKeys;
}
public void setDataFieldName (String dataFieldName) {
_dataFieldName = dataFieldName;
}
public String getDataFieldName () {
return _dataFieldName;
}
public void setRowCalculatorLabelKey (String rowCalculatorLabelKey) {
_rowCalculatorLabelKey = rowCalculatorLabelKey;
}
public String getRowCalculatorLabelKey () {
return _rowCalculatorLabelKey;
}
public void setColCalculatorLabelKey (String colCalculatorLabelKey) {
_colCalculatorLabelKey = colCalculatorLabelKey;
}
public String getColCalculatorLabelKey () {
return _colCalculatorLabelKey;
}
}
The Result
View demo on line
http://screencast.com/t/g4c3BfhCj2Re
Reference
Start point of APIs
http://www.zkoss.org/javadoc/latest/zkpvt/org/zkoss/pivot/impl/TabularPivotModel.html
Download
Full project at github
https://github.com/benbai123/ZK_Practice/tree/master/Components/projects/Addon_Practice/PivottableTest/UpdateLivedataPartially
Demo Flash
https://github.com/benbai123/ZK_Practice/blob/master/Components/demos/addon/UpdateDataPartially.swf
