ZK Basic MVC Pattern with SelectorComposer

Introduction

From official site:
A composer analogous to GenericForwardComposer. Instead of wiring variables and adding event listeners by naming convention, this composer do the work by annotation and selectors.
...
SelectorComposer, as one of the most popular skeletons, wires components, variables and event listeners automatically based on Java annotations you specify.

This post practice the MVC pattern with SelectorComposer in ZK.

Note: As mentioned in the title, only very basic part in this article.

Pre-request

Please refer to http://ben-bai.blogspot.tw/2012/10/zk-basic-mvc-pattern-with.html
for the basic concept of Composer.

The ZUL Page

basic_SelectorComposer.zul

The selector composer can wire components/listen events by jquery-selector like way, it is not required to assign id to components

<zk>
    <!-- Tested with ZK 6.0.2 EE Eval -->

    <!-- A window, apply a custom SelectorComposer
        the selector composer can wire components/listen events
        by jquery-selector like way, it is not required to assign
        id to components -->
    <window apply="test.basic.mvc.BasicSelectorComposer">
        Input your name then click button:
        <textbox id="tb" />
        <button label="Say Hello" />
        <div height="50px" width="100px"
            style="margin: 10px; background-color: #A6C3EA;">
            <label />
        </div>
    </window>
</zk>

The Composer

BasicSelectorComposer.java

package test.basic.mvc;

import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.event.InputEvent;
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;
import org.zkoss.zul.Label;

/**
 * Tested with ZK 6.0.2 EE Eval
 *
 */
public class BasicSelectorComposer  extends SelectorComposer<Component> {

    private static final long serialVersionUID = -4527376919822049779L;

    private String _name;
    /**
     * Wire the Label with component type path "div > label"
     * this will wire the first label component under the first div component
     */
    @Wire("div > label")
    private Label lb;

    /**
     * Do some initiation task here
     */
    public void doAfterCompose (Component comp) throws Exception {
        super.doAfterCompose(comp);
        _name = "Default Name";
        lb.setValue("The message label");
    }

    /**
     * Listen to the onChange event of a component with id 'tb'
     * since wire/listen based on jquery-selector like way,
     * the function name can be changed
     */
    @Listen("onChange = #tb")
    public void doWhenTextboxChanged (InputEvent event) {
        _name = event.getValue();
        if (_name == null || _name.isEmpty()) {
            _name = "Default Name";
        }
    }

    /**
     * Listen to the onClick event of a button with label 'Say Hello'
     */
    @Listen("onClick = button[label='Say Hello']")
    public void afterButtonClicked (MouseEvent event) {
        // update label lb's value
        lb.setValue("Hello " + _name);
    }
}

The Result

initial page

after input name then click button

Reference

Envisage ZK 6: An Annotation Based Composer For MVC
http://books.zkoss.org/wiki/Small_Talks/2011/January/Envisage_ZK_6:_An_Annotation_Based_Composer_For_MVC

Download

Files at github:
basic_SelectorComposer.zul
https://github.com/benbai123/ZK_Practice/blob/master/Pattern/MVC/BasicMVC/WebContent/basic_SelectorComposer.zul

BasicSelectorComposer.java
https://github.com/benbai123/ZK_Practice/blob/master/Pattern/MVC/BasicMVC/src/test/basic/mvc/BasicSelectorComposer.java

ZK Basic MVC Pattern with GenericForwardComposer

Introduction

Regarding GenericForwardComposer, from official document:
A skeletal composer that you can extend and write intuitive onXxx$myid event handler methods with auto event forwarding and "auto-wired" accessible variable objects such as embedded objects, components, and external resolvable variables in a ZK zuml page; this class will add forward condition to the myid source component and forward source onXxx event received by the source myid component to the target onXxx$myid event (as defined in this composer) of the supervised target component; of course it will also registers onXxx$myid events to the supervised component and wire all accessible variable objects to this composer by calling setXxx() method or set xxx field value directly per the variable name.

This post practice the MVC pattern with GenericForwardComposer in ZK.

Note: As said in the title, only very basic part in this article.

Pre-request

(must)
Please refer to http://ben-bai.blogspot.tw/2012/06/zk-quick-start.html
for setting up the environment to run ZK Web Application.

(optional)
Regarding ID conflict and component namespace, please refer to
http://ben-bai.blogspot.tw/2012/11/zk-id-conflict-and-component-idspace.html

The ZUL Page

note: we usually use an component that is an Id Space
such like window to apply GenericForwardComposer
to prevent id conflict

basic_GenericForwardComposer.zul

<zk>
    <!-- Tested with ZK 6.0.2 EE Eval -->

    <!-- A window, apply a custom GenericForwardComposer
        we usually use an component that is an Id Space
        such like window to apply GenericForwardComposer
        so we can maintain the subset of components separately
        without the need to worry if there is any conflicts
        with other subsets
        -->
    <window apply="test.basic.mvc.BasicGenericForwardComposer">
        Input your name then click button:
        <textbox id="tb" />
        <button id="btn" label="Say Hello" />
        <div height="50px" width="100px"
            style="margin: 10px; background-color: #A6C3EA;">
            <label id="lb" />
        </div>
    </window>
</zk>

The Composer

BasicGenericForwardComposer.java

package test.basic.mvc;

import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.util.GenericForwardComposer;
import org.zkoss.zul.Label;
import org.zkoss.zk.ui.event.InputEvent;
import org.zkoss.zk.ui.event.MouseEvent;
/**
 * Tested with ZK 6.0.2 EE Eval
 *
 */
public class BasicGenericForwardComposer extends GenericForwardComposer<Component> {

    private static final long serialVersionUID = -4734679090474506071L;
    private String _name;
    /**
     * Wire the Label with id "lb" in zul page into composer
     * so you can control it by composer directly
     * 
     * The field name of declared component (Label here)
     * will be mapped to the component id in zul page automatically,
     * 
     * e.g., Label lb will be mapped to <label id="lb" ... />
     */
    private Label lb;

    /**
     * Official: With its GenericForwardComposer.doAfterCompose(Component) method,
     * the ZK components declared in mark up are wired with the component instances
     * declared in the controller for our manipulation, while the events fired are
     * automatically forwarded to this controller for event handling.
     * 
     * In short, you can do some initiation task here
     */
    public void doAfterCompose (Component comp) throws Exception {
        super.doAfterCompose(comp);
        _name = "Default Name";
        lb.setValue("The message label");
    }

    /**
     * Declare an event listener, listen to the "onChange" event
     * of the textbox with id "tb" in zul page,
     * 
     * the function name is combined by
     * event name (onChange) + "$" + component id in zul page(tb)
     * 
     * Do NOT need to wire it before listen its event
     * 
     * @param event the input event fired by the textbox
     * 
     */
    public void onChange$tb (InputEvent event) {
        _name = event.getValue();
        if (_name == null || _name.isEmpty()) {
            _name = "Default Name";
        }
    }

    /**
     * Listen to onClick event of the buttn with id "btn"
     * @param event
     */
    public void onClick$btn (MouseEvent event) {
        // update label lb's value
        lb.setValue("Hello " + _name);
    }
}

The Result

initial page

after input name then click button

References

Small Talks/2008/August/ZK MVC Made Easy
http://books.zkoss.org/wiki/Small_Talks/2008/August/ZK_MVC_Made_Easy

ZK Developer's Reference/UI Composing/ID Space
http://books.zkoss.org/wiki/ZK_Developer's_Reference/UI_Composing/ID_Space

Class GenericForwardComposer<T extends Component>
http://www.zkoss.org/javadoc/latest/zk/org/zkoss/zk/ui/util/GenericForwardComposer.html

Download

Full project at github
https://github.com/benbai123/ZK_Practice/tree/master/Pattern/MVC/BasicMVC

Use Custom Calculator in ZK Pivottable

Introduction

From official document:
In TabularPivotModel, Calculator is implemented by two segments: summarization and context. Given a Calculator as an aggregation operation, the context refers to the intermediate result we keep track of while iterating over the operands (accumulation), while summarization refers to the computation from the context to the end result.

This post practice use a custom calculator, say PercentageCalculator, to calculate a column field as the percentage value of other 2 column fields. Including some simple custom format style.

Pre-request

This post is based on Display Data in ZK Pivottable (http://ben-bai.blogspot.tw/2012/07/zk-pivottable-display-data-in-zk.html), please refer to it at first if you do not familiar with ZK Pivottable

The ZUL Page

index.zul

<zk>
    <!-- Tested with ZK 6.0.1 CE and ZK Pivottable 2.0.0 -->
    <!-- window, apply a SelectorComposer -->
    <window id="win" xmlns:w="client"
    apply="test.TestComposer">
    <!-- pivottable, get model and renderer from window's composer -->
    <pivottable id="pivottable" model="${win$composer.pivotModel}"
        pivotRenderer="${win$composer.pivotRenderer}" />
    </window>
</zk>

The Composer

TestComposer.java

package test;

import java.text.DecimalFormat;
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.PivotHeaderContext;
import org.zkoss.pivot.Pivottable;

import org.zkoss.pivot.impl.SimplePivotRenderer;
import org.zkoss.pivot.impl.TabularPivotField;
import org.zkoss.pivot.impl.TabularPivotModel;
import org.zkoss.pivot.impl.calc.Context;
import org.zkoss.pivot.impl.calc.ContextType;
import org.zkoss.pivot.impl.calc.ContextualCalculator;

import org.zkoss.zk.ui.select.SelectorComposer;

/**
* 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;

    /**
    * 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);
            
            // assign columns, the order matches to the level of column node field
            _pivotModel.setFieldType("Column_Level_001", 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);
            TabularPivotField field = _pivotModel.getField("Data_Field_003");
            _pivotModel.setFieldSummary(field, new PercentageCalculator());
        }
        return _pivotModel;
    }
    /**
     * Custom pivot renderer,
     * use custom format for column field "Data_Field_003"
     * @return
     */
    public SimplePivotRenderer getPivotRenderer () {
        return new SimplePivotRenderer() {
            public String renderCell(Number data, Pivottable table, 
                    PivotHeaderContext rowContext, PivotHeaderContext columnContext,
                    PivotField dataField) {
                DecimalFormat pf = new DecimalFormat("##,###.00 %");
                return "Data_Field_003".equals(dataField.getFieldName())?
                        pf.format(data.doubleValue()) : super.renderCell(data, table, rowContext, columnContext, dataField);
            }
        };
    }
    /**
    * 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>();

            Double dataOne = r.nextDouble() * 10000.0 + 1.0;
            Double dataTwo = r.nextDouble() * 10000.0 + 1.0;
            data.add("Row_Level_001 - " + (r.nextInt(10) + 1));
            data.add("Column_Level_001 - " + (r.nextInt(10) + 1));

            data.add(dataOne);
            data.add(dataTwo);
            data.add(new DataForPercentage(dataOne, dataTwo));
            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",
            "Column_Level_001",
            "Data_Field_001", "Data_Field_002", "Data_Field_003"
        });
    }

    /**
     * The context for PercentageCalculator
     *
     */
    public static class PercentageContext implements Context<PercentageContext> {

        private Double _sumOfDataOne = 0.0;
        private Double _sumOfDataTwo = 0.0;

        // what to do when iterating over raw data.
        @Override
        public void add(Object item) {
            if (item instanceof DataForPercentage) {
                DataForPercentage _item = (DataForPercentage)item;
                _sumOfDataOne += _item.getDataOne();
                _sumOfDataTwo += _item.getDataTwo();
            } else {
                _sumOfDataOne += (Double)item;
                _sumOfDataTwo += (Double)item;
            }
        }
        // what to do when merging from contexts of a partition of its raw data set.
        @Override
        public void merge(PercentageContext ctx) {
            _sumOfDataOne += ctx.getSumOfDataOne();
            _sumOfDataTwo += ctx.getSumOfDataTwo();
        }
        public Double getSumOfDataOne () {
            return _sumOfDataOne;
        }
        public Double getSumOfDataTwo () {
            return _sumOfDataTwo;
        }

        public Double getPercentage() {
            return getSumOfDataOne() / getSumOfDataTwo();
        }

        public static final ContextType<PercentageContext> CONTEXT_TYPE = 
                new ContextType<PercentageContext>() {
            // ContextType has the responsibility as a Context factory
            @Override
            public PercentageContext create() {
                return new PercentageContext();
            }
        };
    }

    /**
     * The custom calculator to calculate percentage value
     * based on the given data
     *
     */
    public static class PercentageCalculator implements ContextualCalculator<PercentageContext> {

        public static final PercentageCalculator INSTANCE = new PercentageCalculator();
        private PercentageCalculator() {}

        // specify the context type
        @Override
        public ContextType<PercentageContext> getContextType() {
            return PercentageContext.CONTEXT_TYPE;
        }
        // summarize the end result from the context
        @Override
        public Number getResult(PercentageContext context) {
            return context.getPercentage();
        }
        @Override
        public String getLabel() {
            return "Percentage";
        }
        @Override
        public String getLabelKey() {
            return "percentage";
        }
    }

    /**
     * Store the data for calculating percentage value
     *
     */
    private class DataForPercentage {
        private Double _dataOne;
        private Double _dataTwo;
        public DataForPercentage(Double dataOne, Double dataTwo) {
            _dataOne = dataOne;
            _dataTwo = dataTwo;
        }
        public Double getDataOne () {
            return _dataOne;
        }
        public Double getDataTwo () {
            return _dataTwo;
        }
    }

}

The Result

Data_Field_003 = Data_Field_001 / Data_Field_002

Reference

ZK Pivottable Essentials/Customization/Custom Calculator
http://books.zkoss.org/wiki/ZK%20Pivottable%20Essentials/Customization/Custom%20Calculator

ZK Pivottable Essentials/Customization/Data Rendering
http://books.zkoss.org/wiki/ZK%20Pivottable%20Essentials/Customization/Data%20Rendering


Download

Full project at github
https://github.com/benbai123/ZK_Practice/tree/master/Components/projects/Addon_Practice/PivottableTest/UseCustomCalculator

Exporting Data to Excel by Apache POI

Introduction

From official site:
The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008).

This post do some practice of Apache POI, including set value to cell, style cell with Date format, Number format, align and font color, merge a range of cells, use Formula to calculate value, evaluate Formula result by FormulaEvaluator.

Required jar

poi-3.8-20120326.jar - http://poi.apache.org/download.html

The Program

POITest.java

Export some Fake data to excel file

package test.poi;

import java.io.FileOutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Random;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class POITest {
    private static SimpleDateFormat _shortDateformatter = new SimpleDateFormat("yyyy-MM-dd");
    // the style for Date column
    private static CellStyle _dateCellStyle;
    // the style for Money cells
    private static CellStyle _moneyCells;
    // the style for Money cells with negative value
    private static CellStyle _moneyCellsNegative;
    // evaluator to evaluate formula cell
    private static FormulaEvaluator _evaluator;

    public static void main (String[] args) {
        try {
            // create a new workbook
            Workbook wb = new HSSFWorkbook();
            // create a sheet with name "Balance"
            Sheet sheet = wb.createSheet("Balance");
            // get fake datas
            List<DateInOut> datas = generateFakeData();

            // add title row
            addTitle(sheet);
            // create cell styles as needed
            createCellStylesAndEvaluator(wb);
            // add datas
            addDatas(sheet, datas);
            
            // adjust column width
            autosizeColumns(sheet);

            // save workbook as .xls file
            saveBalanceReport(wb, "workbook.xls");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private static List<DateInOut> generateFakeData () throws ParseException {
        Calendar c = Calendar.getInstance();
        Random rand = new Random();
        List<DateInOut> datas = new ArrayList<DateInOut>();
        // set start date
        c.setTime(_shortDateformatter.parse("2012-01-01"));
        // generate fake datas within 200 days
        while (c.get(Calendar.DAY_OF_YEAR) < 200) {
            DateInOut dio = new DateInOut(c.getTime());
            // add 1-5 records
            for (int i = 0; i < (rand.nextInt(5) + 1); i++) {
                dio.addInOut(rand.nextInt(1000), rand.nextInt(1000));
            }
            datas.add(dio);
            // increase date
            c.add(Calendar.DAY_OF_YEAR, rand.nextInt(3) + 1);
        }
        return datas;
    }
    // add titles
    private static void addTitle (Sheet sheet) {
        // create row (3rd row)
        Row row = sheet.createRow(2);
        // add value to 3rd cell
        row.createCell(2).setCellValue("Ben Bai's Balance (not real)");
        // merge cells
        sheet.addMergedRegion(// first row (0-based), last row (0-based), first column (0-based), last column (0-based)
            new CellRangeAddress(2, 2, 2, 4));
        // go to 4th row
        row = sheet.createRow(3);
        // add values to cells
        row.createCell(1).setCellValue("Date");
        row.createCell(2).setCellValue("Income");
        row.createCell(3).setCellValue("Expenditure");
        row.createCell(4).setCellValue("Balance");
        row.createCell(6).setCellValue("Grand Total of Balance");
    }
    // create style for Date cell
    private static void createCellStylesAndEvaluator(Workbook wb) {
        // CreationHelper for create CellStyle
        CreationHelper createHelper = wb.getCreationHelper();
        _dateCellStyle = wb.createCellStyle();
        // add date format
        _dateCellStyle.setDataFormat(
            createHelper.createDataFormat().getFormat("yyyy-MM-dd"));
        // vertical align top
        _dateCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);

        // money style ( >= 0)
        _moneyCells = wb.createCellStyle();
        _moneyCells.setDataFormat(
                createHelper.createDataFormat().getFormat("$__##,##0.##"));
        // money style ( < 0)
        Font font = wb.createFont();
        font.setColor(Font.COLOR_RED);
        _moneyCellsNegative = wb.createCellStyle();
        _moneyCellsNegative.setDataFormat(
                createHelper.createDataFormat().getFormat("$__##,##0.##"));
        _moneyCellsNegative.setFont(font);

        _evaluator = wb.getCreationHelper().createFormulaEvaluator();
    }
    private static void addDatas (Sheet sheet, List<DateInOut> datas) {
        int rowIdx = 4; // start from 5th row
        Row firstDataRow = null;
        for (DateInOut dio : datas) {
            Date date = dio.getDate();
            List<InOut> inoutList = dio.getInOut();
            int size = inoutList.size();

            // merge "Date" column as needed
            sheet.addMergedRegion( // first row (0-based), last row (0-based), first column (0-based), last column (0-based)
                new CellRangeAddress(rowIdx, rowIdx + size - 1, 1, 1));
            Row row = sheet.createRow(rowIdx);
            // keep first row for later use
            if (firstDataRow == null)
                firstDataRow = row;
            // set date value
            Cell c = row.createCell(1);
            c.setCellStyle(_dateCellStyle);
            c.setCellValue(date);

            for (InOut io : inoutList) {
                // income and expenditure
                Cell cell = row.createCell(2);
                cell.setCellValue(io.getIncome());
                setNumericStyle(cell, false);

                cell = row.createCell(3);
                cell.setCellValue(io.getExpenditure());
                setNumericStyle(cell, false);

                // formula for calculate balance of one data row
                String formula = "C"+(rowIdx+1) + "-D"+(rowIdx+1);
                cell = row.createCell(4);
                cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                cell.setCellFormula(formula);
                setNumericStyle(cell, true);
                
                // move to next row
                rowIdx++;
                row = sheet.createRow(rowIdx);
            }
            // add two empty column before next date
            rowIdx += 2;
        }
        // formula for calculate grand total of balance column
        String formula = "SUM(E5:E"+(rowIdx)+")";
        Cell gtb = firstDataRow.createCell(6);
        gtb.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        gtb.setCellFormula(formula);
        setNumericStyle(gtb, true);
    }
    // set style to numeric cell
    private static void setNumericStyle (Cell cell, boolean isFormula) {
        double value = isFormula? getFormulaCellValue(cell) : cell.getNumericCellValue();
        if (value >= 0) {
            cell.setCellStyle(_moneyCells);
        } else {
            cell.setCellStyle(_moneyCellsNegative);
        }
    }
    // evaluate formula cell value
    private static double getFormulaCellValue (Cell cell) {
        _evaluator.evaluateFormulaCell(cell);
        return cell.getNumericCellValue();
    }
    // adjust column width
    private static void autosizeColumns (Sheet sheet) {
        // auto size not work with date
        sheet.setColumnWidth(1, 3000);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);
        sheet.autoSizeColumn(6);
    }
    private static void saveBalanceReport (Workbook wb, String fileName) throws Exception {
        // create a new file
        FileOutputStream out = new FileOutputStream(fileName);
        // Write out the workbook
        wb.write(out);
        out.close();
    }
}
// one day with several income/expenditure pare
class DateInOut {
    private Date _date;
    private List<InOut> _inOut;
    public DateInOut (Date date) {
        _date = date;
        _inOut = new ArrayList<InOut>();
    }
    public void addInOut (int income, int expenditure) {
        _inOut.add(new InOut(income, expenditure));
    }

    public Date getDate () {
        return _date;
    }
    public List<InOut> getInOut () {
        return _inOut;
    }
}
// income/expenditure pare
class InOut {
    private int _income;
    private int _expenditure;
    public InOut (int income, int expenditure) {
        _income = income;
        _expenditure = expenditure;
    }

    public int getIncome () {
        return _income;
    }
    public int getExpenditure () {
        return _expenditure;
    }
}

The Result

References

Busy Developers' Guide to HSSF and XSSF Features
http://poi.apache.org/spreadsheet/quick-guide.html#WorkingWithFonts

Formula Evaluation
http://poi.apache.org/spreadsheet/eval.html

POI API Documentation
http://poi.apache.org/apidocs/overview-summary.html


Download

Full project at github
https://github.com/benbai123/JSP_Servlet_Practice/tree/master/Practice/JAVA/POI/POITest

Get Address Information by IP

Introduction

There are many IP to Address services such like http://www.hostip.info/, http://ip-lookup.net/, http://www.iplocation.net/ and so on, this post will talking about how to get address information by given IP via the API provided by Hostip.info.

Prerequest

The sample of this post is based on http://ben-bai.blogspot.tw/2012/02/java-practice-javanet-practice-use.html

Required jar files:


 json-lib: http://sourceforge.net/projects/json-lib/files/json-lib/
 commons-beanutils-1.8.3.jar http://commons.apache.org/beanutils/download_beanutils.cgi
 commons-collections-3.2.1.jar http://commons.apache.org/collections/download_collections.cgi
 commons-lang-2.5.jar http://commons.apache.org/lang/download_lang.cgi
 commons-logging-1.1.1.jar http://commons.apache.org/logging/download_logging.cgi
 ezmorph-1.0.6.jar http://sourceforge.net/projects/ezmorph/files/ezmorph/



The Program

IPLookupService.java

Get position by given IP and then get formatted address by given lat/lng

package test;

import java.io.InputStreamReader;

import net.sf.json.JSONObject;

/**
 * required jar files:
 * json-lib: http://sourceforge.net/projects/json-lib/files/json-lib/
 * commons-beanutils-1.8.3.jar http://commons.apache.org/beanutils/download_beanutils.cgi
 * commons-collections-3.2.1.jar http://commons.apache.org/collections/download_collections.cgi
 * commons-lang-2.5.jar http://commons.apache.org/lang/download_lang.cgi
 * commons-logging-1.1.1.jar http://commons.apache.org/logging/download_logging.cgi
 * ezmorph-1.0.6.jar http://sourceforge.net/projects/ezmorph/files/ezmorph/
 *
 */
public class IPLookupService {
    public static void main (String[] args) {
        AddressInfo info = ipToLocation("98.76.54.32");
        System.out.println(info.getCountryName());
        System.out.println(info.getCountryCode());
        System.out.println(info.getCity());
        System.out.println(info.getLat());
        System.out.println(info.getLng());
        System.out.println(info.getFormattedAddress());
    }
    /**
     * return country, city, address by given IP
     * @param ip
     * @return
     */
    public static AddressInfo ipToLocation (String ip) {
        String country;
        AddressInfo info = null;
        double lat;
        double lng;
        StringBuilder sb = getResponse(
                "http://api.hostip.info/get_json.php?ip=" + ip + "&position=true");
        JSONObject jobj = JSONObject.fromObject(sb.toString());
        lat = jobj.getDouble("lat");
        lng = jobj.getDouble("lng");
        info = new AddressInfo(jobj.getString("country_name"), jobj.getString("country_code"),
                jobj.getString("city"), lat, lng,
                GeocodeService.getAddressByLatLng(lat, lng, "ja"));
        return info;
    }
    public static StringBuilder getResponse(String path){
        try {
            java.net.URL url = new java.net.URL(path);
            java.net.HttpURLConnection uc = (java.net.HttpURLConnection) url.openConnection();
            uc.setRequestProperty("User-agent", "Mozilla/5.0");

            uc.setRequestProperty("Accept-Charset", "UTF-8"); // encoding
            uc.setReadTimeout(30000);// timeout limit
            uc.connect();// connect
            int status = uc.getResponseCode();

            switch (status) {
                case java.net.HttpURLConnection.HTTP_GATEWAY_TIMEOUT://504 timeout
                    break;
                case java.net.HttpURLConnection.HTTP_FORBIDDEN://403 forbidden
                    break;
                case java.net.HttpURLConnection.HTTP_INTERNAL_ERROR://500 server error
                    break;
                case java.net.HttpURLConnection.HTTP_NOT_FOUND://404 not exist
                    break;
                case java.net.HttpURLConnection.HTTP_OK: // ok
                    InputStreamReader reader = new InputStreamReader(uc.getInputStream(), "UTF-8");

                    int ch;
                    StringBuilder sb = new StringBuilder("");
                    while((ch = reader.read())!= -1){
                        sb.append((char)ch);
                    }
                    return sb;
            }

        } catch (java.net.MalformedURLException e) { // invalid address format
            e.printStackTrace();
        } catch (java.io.IOException e) { // connection broken
            e.printStackTrace();
        }
        return null;
    }
}
class AddressInfo {
    private String _countryName;
    private String _countryCode;
    private String _city;
    private double _lat;
    private double _lng;
    private String _formattedAddress;
    AddressInfo (String countryName, String countryCode,
        String city, double lat, double lng, String formattedAddress) {
        _countryName = countryName;
        _countryCode = countryCode;
        _city = city;
        _lat = lat;
        _lng = lng;
        _formattedAddress = formattedAddress;
    }
    public String getCountryName () {
        return _countryName;
    }
    public String getCountryCode () {
        return _countryCode;
    }
    public String getCity () {
        return _city;
    }
    public double getLat () {
        return _lat;
    }
    public double getLng () {
        return _lng;
    }
    public String getFormattedAddress () {
        return _formattedAddress;
    }
}

function added to GeocodeService.java

The new function returns formatted address by given lat/lng

    /**
     * return formatted address by given lat/lng
     * @param lat
     * @param lng
     * @param lang
     * @return
     */
    public static String getAddressByLatLng (double lat, double lng, String lang) {
        StringBuilder sb = getResponse("http://maps.googleapis.com/maps/api/geocode/json?"
                + "latlng="+lat+","+lng
                + "&sensor=true&language="+lang);
        JSONObject jobj = JSONObject.fromObject(sb.toString());
        return jobj.getJSONArray("results").getJSONObject(0).getString("formatted_address");
    }

The Result

http://maps.googleapis.com/maps/api/geocode/json?latlng=33.7629,-84.4226&sensor=true&language=ja
UNITED STATES
US
Atlanta, GA
33.7629
-84.4226
1068 Simpson Rd NW, アトランタ ジョージア 30314 アメリカ合衆国

Reference

http://stackoverflow.com/questions/2362413/any-reliable-api-available-to-determine-users-city-and-country-from-ip-address


Download

sample code at github
https://github.com/benbai123/JSP_Servlet_Practice/blob/master/Practice/JAVA/Net/src/test/IPLookupService.java
https://github.com/benbai123/JSP_Servlet_Practice/blob/master/Practice/JAVA/Net/src/test/GeocodeService.java

Generate Report by ZK Jasperreport Programmatically

Introduction

From the Related Posts below:

JasperReports is a powerful reporting engine which supports various formats including pdf, html, rtf, xls, doc etc, ZK has integrated JasperReports to leverage these powers for ease of reporting.

This post is about how to generate report programmtically, you can also find some different way in the Related Post below.

The Environment

Basic ZK 5.0.11 EE eval jars plus commons-digester-2.1.jar (can be found at http://archive.apache.org/dist/commons/digester/binaries/) and commons-beanutils-1.8.3.jar (downloaded from http://commons.apache.org/beanutils/download_beanutils.cgi, for generate template)

Note: the version of commons-digester should be 2.x or you may see NoClassDefFoundError with respect to org.apache.commons.digester.Rule

The Code

InfoResource.java

This class do two things (this is not good, but just for this post...)

1. call JasperCompileManager.compileReportToFile to compile the template.jrxml to template.jasper (the template used to generate report)

2. provide data to report

package test.report;

import java.util.ArrayList;
import java.util.List;

import net.sf.jasperreports.engine.JRDataSource;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRField;
import net.sf.jasperreports.engine.JasperCompileManager;

public class InfoResource implements JRDataSource {
    private List<FoodInfo> dataList = new ArrayList<FoodInfo>();
    private int index = -1;
    public static void main(String args[]) {
        generateTemplateSrc();
    }
    /**
     * compile the template.jrxml to generate template.jasper
     */
    public static void generateTemplateSrc() {
        try {
            JasperCompileManager.compileReportToFile("WebContent/WEB-INF/template/template.jrxml", "WebContent/WEB-INF/template/template.jasper");
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

    public InfoResource()
    {
        // datas to report
        dataList.add(new FoodInfo("pizza", 1000, 200));
        dataList.add(new FoodInfo("ham",  500, 300));
        dataList.add(new FoodInfo("banana", 200, 20));
    }

    /**
     * method defined in interface JRDataSource,
     * return whether has next data
     */
    public boolean next() throws JRException
    {
        index++;
        return (index < dataList.size());
    }
    /**
     * method defined in interface JRDataSource,
     * return value based on field name
     */
    public Object getFieldValue(JRField field) throws JRException
    {
        Object value = null;
        
        String fieldName = field.getName();
        
        if ("product_name".equals(fieldName))
        {
            value = dataList.get(index).getName();
        }
        else if ("cal".equals(fieldName))
        {
            value = dataList.get(index).getCal();
        }
        else if ("price".equals(fieldName))
        {
            value = dataList.get(index).getPrice();
        }
        
        return value;
    }
}
/**
 * the data class
 *
 */
class FoodInfo {
    private String name;
    private int cal;
    private int price;
    
    public FoodInfo() {
        
    }
    public FoodInfo(String name, int cal, int price) {
        this.name = name;
        this.cal = cal;
        this.price = price;
    }

    public void setName(String name) {
        this.name = name;
    }
    public String getName() {
        return name;
    }
    public void setCal(int cal) {
        this.cal = cal;
    }
    public int getCal() {
        return cal;
    }
    public void setPrice(int price) {
        this.price = price;
    }
    public int getPrice() {
        return price;
    }
}

template.jrxml

This is the human readable xml file for design the report template, run InfoResource.java as java application will compile it to template file template.jasper

<?xml version="1.0"?>
<!DOCTYPE jasperReport
  PUBLIC "-//JasperReports//DTD Report Design//EN"
  "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
 
<jasperReport name="report1">
    <!-- parameters are from test.zul --> 
    <parameter name="reportTitle" class="java.lang.String"/>
    <parameter name="author" class="java.lang.String"/>
    <parameter name="startDate" class="java.lang.String"/>
    <parameter name="image" class="java.io.File"/>
 
     <parameter name="C1" class="java.lang.String">
        <defaultValueExpression>
            new java.lang.String("")
        </defaultValueExpression>
    </parameter>
 
    <parameter name="C2" class="java.lang.String">
        <defaultValueExpression>
            new java.lang.String("")
        </defaultValueExpression>
    </parameter>
 
    <parameter name="C3" class="java.lang.String">
        <defaultValueExpression>
            new java.lang.String("")
        </defaultValueExpression>
    </parameter>
 
     <!-- define fields -->
    <field name="product_name" class="java.lang.String"/>
    <field name="cal" class="java.lang.Integer"/>
    <field name="price" class="java.lang.Integer"/>
 
    <title>
        <band height="120">
            <textField>
                <reportElement x="0" y="0" width="225" height="45"/>
                <textElement textAlignment="Center">
                    <font size="18"/>
                </textElement>
                <textFieldExpression class="java.lang.String">
                    <![CDATA[$P{reportTitle}]]>
                </textFieldExpression>
            </textField>
            <textField>
                <reportElement x="225" y="0" width="225" height="30"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression class="java.lang.String">
                    <![CDATA["Run by: " + $P{author}
                        + " on " + $P{startDate}]]>
                </textFieldExpression>
            </textField>
            <image hyperlinkType="None">  
                <reportElement x="0" y="50" width="50" height="50"/>  
                <imageExpression class="java.io.File">
                    <![CDATA[$P{image}]]>  
                </imageExpression>
            </image>  
        </band>
    </title>
 
 
    <columnHeader>
        <band height="15">

            <textField>
                <reportElement x="0" y="0" width="150" height="15"/>
                <box>
                    <pen lineWidth="2" lineColor="#000000"/>
                    <topPen lineWidth="2" lineColor="#000000"/>
                    <leftPen lineWidth="2" lineColor="#000000"/>
                    <bottomPen lineWidth="0" lineColor="#000000"/>
                    <rightPen lineWidth="0" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" />
                <textFieldExpression class="java.lang.String">
                    <![CDATA[$P{C1}]]>
                </textFieldExpression>
            </textField>
 
            <textField>
                <reportElement x="150" y="0" width="150" height="15"/>
                <box>
                    <pen lineWidth="2" lineColor="#000000"/>
                    <topPen lineWidth="2" lineColor="#000000"/>
                    <leftPen lineWidth="0" lineColor="#000000"/>
                    <bottomPen lineWidth="0" lineColor="#000000"/>
                    <rightPen lineWidth="0" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" />
              <textFieldExpression class="java.lang.String">
                    <![CDATA[$P{C2}]]>
                </textFieldExpression>
            </textField>
 
            <textField>
                <reportElement x="300" y="0" width="150" height="15"/>
                <box>
                    <pen lineWidth="2" lineColor="#000000"/>
                    <topPen lineWidth="2" lineColor="#000000"/>
                    <leftPen lineWidth="0" lineColor="#000000"/>
                    <bottomPen lineWidth="0" lineColor="#000000"/>
                    <rightPen lineWidth="2" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" />
              <textFieldExpression class="java.lang.String">
                    <![CDATA[$P{C3}]]>
                </textFieldExpression>
            </textField>
 
            </band>
    </columnHeader>
 
    <!-- output datas, for more details, please refer to the official document -->
    <detail>
        <band height="15">
            <textField>
                <reportElement x="0" y="0" width="150" height="15" mode="Opaque" forecolor="#ffffff" backcolor="#FF0000"/>
                <box>
                    <pen lineWidth="2" lineColor="#000000"/>
                    <topPen lineWidth="2" lineColor="#000000"/>
                    <leftPen lineWidth="2" lineColor="#000000"/>
                    <bottomPen lineWidth="2" lineColor="#000000"/>
                    <rightPen lineWidth="2" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" />
                <textFieldExpression class="java.lang.String">
                    <![CDATA[$F{product_name}]]>
                </textFieldExpression>
            </textField>
 
            <textField>
                <reportElement x="150" y="0" width="150" height="15" mode="Opaque" forecolor="#ffffff" backcolor="#FF0000"/>
                <box>
                    <pen lineWidth="2" lineColor="#000000"/>
                    <topPen lineWidth="2" lineColor="#000000"/>
                    <leftPen lineWidth="2" lineColor="#000000"/>
                    <bottomPen lineWidth="2" lineColor="#000000"/>
                    <rightPen lineWidth="2" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" />
                <textFieldExpression class="java.lang.Integer">
                    <![CDATA[$F{cal}]]>
                </textFieldExpression>
            </textField>
 
            <textField>
                <reportElement x="300" y="0" width="150" height="15" mode="Opaque" forecolor="#ffffff" backcolor="#FF0000"/>
                <box>
                    <pen lineWidth="2" lineColor="#000000"/>
                    <topPen lineWidth="2" lineColor="#000000"/>
                    <leftPen lineWidth="2" lineColor="#000000"/>
                    <bottomPen lineWidth="2" lineColor="#000000"/>
                    <rightPen lineWidth="2" lineColor="#000000"/>
                </box> 
                <textElement textAlignment="Center" />
                <textFieldExpression class="java.lang.Integer">
                    <![CDATA[$F{price}]]>
                </textFieldExpression>
            </textField>
 
        </band>
    </detail>
 
</jasperReport> 

test.zul

The zul page for report generation

<zk>
    <vbox>
        <hbox>
            <!-- choose the file type for export report -->
            Choose a File Type :
            <listbox id="format" mold="select" >
                <listitem label="PDF" value="pdf" selected="true" />
                <listitem label="HTML" value="html" />
                <listitem label="Word (RTF)" value="rtf" />
                <listitem label="Excel" value="xls" />
                <listitem label="Excel (JXL)" value="jxl" />
                <listitem label="CSV" value="csv" />
                <listitem label="OpenOffice (ODT)" value="odt" unless="false" />
            </listbox>
            <button label="Get Report">
                <attribute name="onClick">
                    String title = titleValue.getValue();
                    String author = authorValue.getValue();
                    Date date = new Date();
                    showReport(title, author, date);
                </attribute>
            </button>
        </hbox>
        <hbox>
            <!-- input field to set title and author param -->
            Input Title: <textbox id="titleValue" />
            Input Author: <textbox id="authorValue" />
        </hbox>
        <hbox>
            <radiogroup id="rgroup">
                <hlayout>
                    <!-- for choose image file using in report -->
                    <radio label="circle" value="testimg.png" selected="true"/>
                    <radio label="cube" value="testimg2.png"/>
                </hlayout>
            </radiogroup>
        </hbox>
        <!-- the report component, used to generate report -->
        <jasperreport id="report" width="1024px" height="768px" />
    </vbox>
    <zscript><![CDATA[
        import test.report.InfoResource;
        import java.io.*;
        
        void showReport(String title, String author, Date date) {
            String realPath = Executions.getCurrent().getDesktop().getSession().getWebApp().getRealPath("/");
            File img = new File(realPath+File.separator+"WEB-INF"+File.separator+"images"+File.separator+rgroup.getSelectedItem().getValue());
            //Preparing parameters
            Map parameters = new HashMap();
            
            // set parameters for generate report
            parameters.put("reportTitle", title);
            parameters.put("author", author);
            parameters.put("startDate", date.toString());
            parameters.put("image", img);
            parameters.put("C1", "product");
            parameters.put("C2", "total cal");
            parameters.put("C3", "product price");

            // get report template
            report.setSrc("/WEB-INF/template/template.jasper");
            // set params
            report.setParameters(parameters);
            // set data (InfoResource.java)
            report.setDatasource(new InfoResource());
            // set report type (pdf, xls, ...)
            report.setType((String) format.getSelectedItem().getValue());
        }
        ]]>
    </zscript>
</zk>

The Result

view demo online
http://screencast.com/t/3aK8Kb2BTU

Related Post

Create a Report with ZK using iReport and JasperReports
http://books.zkoss.org/wiki/Small_Talks/2012/April/Create_a_Report_with_ZK_using_iReport_and_JasperReports

References

ZK Component Reference/Diagrams and Reports/Jasperreport
http://books.zkoss.org/wiki/ZK_Component_Reference/Diagrams_and_Reports/Jasperreport

Jasperreports tutorial
http://community.jaspersoft.com/wiki/jasperreports-library-tutorial

Downloads

full project
https://github.com/benbai123/ZK_Practice/tree/master/Components/projects/Integrated_Components/jasperreportTest

demo flash
https://github.com/benbai123/ZK_Practice/blob/master/Components/demos/Integrated_Components/jasper_report_test.swf

personal sql query notes

simple note

Join 3 tables and use subquery as condition

assume RoleID=2 denotes banned user and Deleted='1' denotes deleted comment, this query will return all deleted comments that posted by banned user

SELECT c.Name, b.Name, a.DateDeleted FROM forum.comment_table AS a
    LEFT JOIN forum.user_table AS b
        ON a.DeleteUserID = b.UserID
    LEFT JOIN forum.discussion_table AS c
        ON a.DiscussionID = c.DiscussionID
    WHERE a.Deleted='1'
        AND a.DateDeleted>='2012-09-01 00:00:00'
        AND a.DateDeleted<='2012-09-30 23:59:59'
        AND a.AuthUserID in (
            SELECT d.UserID FROM forum.user_table AS d
                WHERE d.RoleID=2)
    ORDER BY a.DateDeleted DESC;

Create table if not exists

CREATE TABLE IF NOT EXISTS test_table ( id BIGINT NOT NULL IDENTITY,
    firstName VARCHAR(32),
    lastName VARCHAR(32));