/*
*
* 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();
} else {
doubleSumValue = doubleSumValue + 0.0;
}
sumList.add(i,
Double.valueOf(String.valueOf(
doubleSumValue)));
}
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.getDouble(i)));
}
break;
case Types.DATE:
if ((specificCellBodyFormat != null) &&
specificCellBodyFormat.containsKey(
String.valueOf(i))) {
DateFormat frmt = (DateFormat) specificCellBodyFormat.get(String.valueOf(
i));
returnString.append(frmt.format(
resultSet.getDate(i)));
} else {
returnString.append(String.valueOf(
resultSet.getDate(i)));
}
break;
case Types.DECIMAL:
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();
} else {
doubleSumValue = doubleSumValue + 0.0;
}
sumList.add(i,
Double.valueOf(String.valueOf(
doubleSumValue)));
}
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.LONGVARCHAR:
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.NULL:
returnString.append(" ");
break;
case Types.NUMERIC:
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();
} else {
doubleSumValue = doubleSumValue + 0.0;
}
sumList.add(i,
Double.valueOf(String.valueOf(
doubleSumValue)));
}
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.TIMESTAMP:
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;
default:
returnString.append(resultSet.getString(i));
break;
}
} else {
returnString.append(" ");
}
returnString.append("</td>\n");
}
returnString.append("</tr>\n");
}
//prepare sum row
if (sumList != null) {
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 ((sumList.get(i) != null) ||
(String.valueOf(sumList.get(i)).trim().length() >= 1)) {
returnString.append(String.valueOf(sumList.get(i)));
} else {
returnString.append(" ");
}
returnString.append("</td>");
}
returnString.append("</tr>");
}
returnString.append("</tbody>\n");
returnString.append("</table>\n");
} catch (SQLException e) {
Debug.logError(e.getMessage(), module);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
e.printStackTrace(new PrintWriter(baos));
Debug.logError(new String(baos.toByteArray()), module);
} catch (Exception e) {
e.printStackTrace();
Debug.logError(e.getMessage(), module);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
e.printStackTrace(new PrintWriter(baos));
Debug.logError(new String(baos.toByteArray()), module);
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
Debug.logError(e.getMessage(), module);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
e.printStackTrace(new PrintWriter(baos));
Debug.logError(new String(baos.toByteArray()), module);
}
}
return returnString.toString();
}
/**
* DOCUMENT ME!
*
* @param wholeString
* @param token
* @param replaceString
*
* @return
*/
private static String replace(String wholeString, String token,
String replaceString) {
StringBuffer sb = new StringBuffer(wholeString);
if (wholeString.indexOf(token) >= 1) {
sb.replace(wholeString.indexOf(token),
(wholeString.indexOf(token) + token.length()), replaceString);
return sb.toString();
} else {
return wholeString;
}
}
}