reportbuilder.java

国外的一套开源CRM

/*
 * 
 * Copyright (c) 2004 SourceTap - www.sourcetap.com
 *
 *  The contents of this file are subject to the SourceTap Public License 
 * ("License"); You may not use this file except in compliance with the 
 * License. You may obtain a copy of the License at http://www.sourcetap.com/license.htm
 * Software distributed under the License is distributed on an  "AS IS"  basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
 * the specific language governing rights and limitations under the License.
 *
 * The above copyright notice and this permission notice shall be included
 * in all copies or substantial portions of the Software.
 *
 */

package com.sourcetap.sfa.report;

import java.io.ByteArrayOutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.Format;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Vector;

import org.ofbiz.base.util.Debug;
import org.ofbiz.entity.GenericDelegator;

import com.sourcetap.sfa.sql.SQLUtil;


/**
 * DOCUMENT ME!
 *
 */
public class ReportBuilder {
	public static final String module = ReportBuilder.class.getName();
    /**
     * DOCUMENT ME!
     *
     * @param sql 
     * @param r 
     * @param delegator 
     *
     * @return 
     */
    public static String getReportTableHTML(String sql, Report r,
        GenericDelegator delegator) {
        StringBuffer returnString = new StringBuffer();
        returnString.append(r.getTableString());

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            SQLUtil sqlUtil = new SQLUtil();
            connection = sqlUtil.getConnection(delegator);
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            ResultSetMetaData rsmd = resultSet.getMetaData();
            int rptCols = rsmd.getColumnCount();

            //Create Report Header
            Vector columnTitles = r.getColumnNames();
            returnString.append(r.getTableHeaderString());
            returnString.append(r.getRowHeaderString());

            String cellHeaderString = "";

            Vector sumColumns = r.getColumnsToSum();
            ArrayList sumList = null;

            if (sumColumns != null) {
                sumList = new ArrayList();

                for (int i = 0; i <= rptCols; i++) {
                    if (sumColumns.contains(Integer.valueOf(String.valueOf(i)))) {
                        sumList.add(new Integer(0));
                    } else {
                        sumList.add("");
                    }
                }
            }

            for (int i = 1; i <= rptCols; i++) {
                cellHeaderString = r.getCellHeaderString();

                if (cellHeaderString.indexOf("$colCount") >= 1) {
                    cellHeaderString = replace(cellHeaderString, "$colCount",
                            String.valueOf(i));
                }

                returnString.append(cellHeaderString);

                if (columnTitles != null) {
                    returnString.append(columnTitles.get(i - 1));
                } else {
                    returnString.append(rsmd.getColumnName(i));
                }

                returnString.append("</td>");
            }

            returnString.append("</tr>");
            returnString.append("</thead>");

            //Create Report Body
            HashMap specificCellBodyString = r.getSpecificCellBodyString();
            HashMap specificCellBodyFormat = r.getSpecificCellBodyFormat();

            returnString.append(r.getTableBodyString());

            int i = 1;
            int columnType = 0;
            int intSumValue = 0;
            double doubleSumValue = 0.0;
            float floatSumValue = 0.0f;

            while (resultSet.next()) {
                returnString.append(r.getRowBodyString());

                for (i = 1; i <= rptCols; i++) {
                    if ((specificCellBodyString != null) &&
                            specificCellBodyString.containsKey(String.valueOf(i))) {
                        returnString.append(specificCellBodyString.get(
                                String.valueOf(i)));
                    } else {
                        returnString.append(r.getCellBodyString());
                    }

                    if (resultSet.getObject(i) != null) {
                        switch (columnType = rsmd.getColumnType(i)) {
                        case Types.VARCHAR:

                            if ((sumColumns != null) &&
                                    sumColumns.contains(Integer.valueOf(
                                            String.valueOf(i)))) {
                                sumList.add(i,
                                    (Integer.valueOf(String.valueOf(resultSet.getInt(
                                                i) +
                                            ((Integer) sumList.get(i)).intValue()))));
                            }

                            if ((specificCellBodyFormat != null) &&
                                    specificCellBodyFormat.containsKey(
                                        String.valueOf(i))) {
                                returnString.append(((Format) specificCellBodyFormat.get(
                                        String.valueOf(i))).format(
                                        resultSet.getString(i)));
                            } else {
                                returnString.append(resultSet.getString(i));
                            }

                            break;

                        case Types.INTEGER:

                            if ((sumColumns != null) &&
                                    sumColumns.contains(Integer.valueOf(
                                            String.valueOf(i)))) {
                                intSumValue = resultSet.getInt(i);

                                if (String.valueOf(sumList.get(i)).trim()
                                              .length() >= 1) {
                                    intSumValue = intSumValue +
                                        Integer.valueOf(String.valueOf(
                                                sumList.get(i))).intValue();
                                } else {
                                    intSumValue = intSumValue + 0;
                                }

                                sumList.add(i,
                                    Integer.valueOf(String.valueOf(intSumValue)));
                            }

                            if ((specificCellBodyFormat != null) &&
                                    specificCellBodyFormat.containsKey(
                                        String.valueOf(i))) {
                                DecimalFormat frmt = (DecimalFormat) specificCellBodyFormat.get(String.valueOf(
                                            i));
                                returnString.append(frmt.format(
                                        resultSet.getDouble(i)));
                            } else {
                                returnString.append(String.valueOf(
                                        resultSet.getInt(i)));
                            }

                            break;

                        case Types.FLOAT:

                            if ((sumColumns != null) &&
                                    sumColumns.contains(Integer.valueOf(
                                            String.valueOf(i)))) {
                                floatSumValue = resultSet.getFloat(i);

                                if (String.valueOf(sumList.get(i)).trim()
                                              .length() >= 1) {
                                    floatSumValue = floatSumValue +
                                        Float.valueOf(String.valueOf(
                                                sumList.get(i))).floatValue();
                                } else {
                                    floatSumValue = floatSumValue + 0.0f;
                                }

                                sumList.add(i,
                                    Float.valueOf(String.valueOf(floatSumValue)));
                            }

                            if ((specificCellBodyFormat != null) &&
                                    specificCellBodyFormat.containsKey(
                                        String.valueOf(i))) {
                                DecimalFormat frmt = (DecimalFormat) specificCellBodyFormat.get(String.valueOf(
                                            i));
                                returnString.append(frmt.format(
                                        resultSet.getDouble(i)));
                            } else {
                                returnString.append(resultSet.getString(i));
                            }

                            break;

                        case Types.DOUBLE:

                            if ((sumColumns != null) &&
                                    sumColumns.contains(Integer.valueOf(
                                            String.valueOf(i)))) {
                                doubleSumValue = resultSet.getDouble(i);

                                if (String.valueOf(sumList.get(i)).trim()
                                              .length() >= 1) {
                                    doubleSumValue = doubleSumValue +
                                        Double.valueOf(String.valueOf(
                                                sumList.get(i))).doubleValue();