001: /*
002: *
003: * Copyright (c) 2004 SourceTap - www.sourcetap.com
004: *
005: * The contents of this file are subject to the SourceTap Public License
006: * ("License"); You may not use this file except in compliance with the
007: * License. You may obtain a copy of the License at http://www.sourcetap.com/license.htm
008: * Software distributed under the License is distributed on an "AS IS" basis,
009: * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
010: * the specific language governing rights and limitations under the License.
011: *
012: * The above copyright notice and this permission notice shall be included
013: * in all copies or substantial portions of the Software.
014: *
015: */
016:
017: package com.sourcetap.sfa.report;
018:
019: import java.io.ByteArrayOutputStream;
020: import java.io.PrintWriter;
021: import java.sql.Connection;
022: import java.sql.PreparedStatement;
023: import java.sql.ResultSet;
024: import java.sql.ResultSetMetaData;
025: import java.sql.SQLException;
026: import java.sql.Types;
027: import java.text.DateFormat;
028: import java.text.DecimalFormat;
029: import java.text.Format;
030: import java.util.ArrayList;
031: import java.util.HashMap;
032: import java.util.Vector;
033:
034: import org.ofbiz.base.util.Debug;
035: import org.ofbiz.entity.GenericDelegator;
036:
037: import com.sourcetap.sfa.sql.SQLUtil;
038:
039: /**
040: * DOCUMENT ME!
041: *
042: */
043: public class ReportBuilder {
044: public static final String module = ReportBuilder.class.getName();
045:
046: /**
047: * DOCUMENT ME!
048: *
049: * @param sql
050: * @param r
051: * @param delegator
052: *
053: * @return
054: */
055: public static String getReportTableHTML(String sql, Report r,
056: GenericDelegator delegator) {
057: StringBuffer returnString = new StringBuffer();
058: returnString.append(r.getTableString());
059:
060: Connection connection = null;
061: PreparedStatement preparedStatement = null;
062: ResultSet resultSet = null;
063:
064: try {
065: SQLUtil sqlUtil = new SQLUtil();
066: connection = sqlUtil.getConnection(delegator);
067: preparedStatement = connection.prepareStatement(sql);
068: resultSet = preparedStatement.executeQuery();
069:
070: ResultSetMetaData rsmd = resultSet.getMetaData();
071: int rptCols = rsmd.getColumnCount();
072:
073: //Create Report Header
074: Vector columnTitles = r.getColumnNames();
075: returnString.append(r.getTableHeaderString());
076: returnString.append(r.getRowHeaderString());
077:
078: String cellHeaderString = "";
079:
080: Vector sumColumns = r.getColumnsToSum();
081: ArrayList sumList = null;
082:
083: if (sumColumns != null) {
084: sumList = new ArrayList();
085:
086: for (int i = 0; i <= rptCols; i++) {
087: if (sumColumns.contains(Integer.valueOf(String
088: .valueOf(i)))) {
089: sumList.add(new Integer(0));
090: } else {
091: sumList.add("");
092: }
093: }
094: }
095:
096: for (int i = 1; i <= rptCols; i++) {
097: cellHeaderString = r.getCellHeaderString();
098:
099: if (cellHeaderString.indexOf("$colCount") >= 1) {
100: cellHeaderString = replace(cellHeaderString,
101: "$colCount", String.valueOf(i));
102: }
103:
104: returnString.append(cellHeaderString);
105:
106: if (columnTitles != null) {
107: returnString.append(columnTitles.get(i - 1));
108: } else {
109: returnString.append(rsmd.getColumnName(i));
110: }
111:
112: returnString.append("</td>");
113: }
114:
115: returnString.append("</tr>");
116: returnString.append("</thead>");
117:
118: //Create Report Body
119: HashMap specificCellBodyString = r
120: .getSpecificCellBodyString();
121: HashMap specificCellBodyFormat = r
122: .getSpecificCellBodyFormat();
123:
124: returnString.append(r.getTableBodyString());
125:
126: int i = 1;
127: int columnType = 0;
128: int intSumValue = 0;
129: double doubleSumValue = 0.0;
130: float floatSumValue = 0.0f;
131:
132: while (resultSet.next()) {
133: returnString.append(r.getRowBodyString());
134:
135: for (i = 1; i <= rptCols; i++) {
136: if ((specificCellBodyString != null)
137: && specificCellBodyString
138: .containsKey(String.valueOf(i))) {
139: returnString.append(specificCellBodyString
140: .get(String.valueOf(i)));
141: } else {
142: returnString.append(r.getCellBodyString());
143: }
144:
145: if (resultSet.getObject(i) != null) {
146: switch (columnType = rsmd.getColumnType(i)) {
147: case Types.VARCHAR:
148:
149: if ((sumColumns != null)
150: && sumColumns
151: .contains(Integer
152: .valueOf(String
153: .valueOf(i)))) {
154: sumList.add(i, (Integer.valueOf(String
155: .valueOf(resultSet.getInt(i)
156: + ((Integer) sumList
157: .get(i))
158: .intValue()))));
159: }
160:
161: if ((specificCellBodyFormat != null)
162: && specificCellBodyFormat
163: .containsKey(String
164: .valueOf(i))) {
165: returnString
166: .append(((Format) specificCellBodyFormat
167: .get(String.valueOf(i)))
168: .format(resultSet
169: .getString(i)));
170: } else {
171: returnString.append(resultSet
172: .getString(i));
173: }
174:
175: break;
176:
177: case Types.INTEGER:
178:
179: if ((sumColumns != null)
180: && sumColumns
181: .contains(Integer
182: .valueOf(String
183: .valueOf(i)))) {
184: intSumValue = resultSet.getInt(i);
185:
186: if (String.valueOf(sumList.get(i))
187: .trim().length() >= 1) {
188: intSumValue = intSumValue
189: + Integer
190: .valueOf(
191: String
192: .valueOf(sumList
193: .get(i)))
194: .intValue();
195: } else {
196: intSumValue = intSumValue + 0;
197: }
198:
199: sumList.add(i, Integer.valueOf(String
200: .valueOf(intSumValue)));
201: }
202:
203: if ((specificCellBodyFormat != null)
204: && specificCellBodyFormat
205: .containsKey(String
206: .valueOf(i))) {
207: DecimalFormat frmt = (DecimalFormat) specificCellBodyFormat
208: .get(String.valueOf(i));
209: returnString
210: .append(frmt.format(resultSet
211: .getDouble(i)));
212: } else {
213: returnString.append(String
214: .valueOf(resultSet.getInt(i)));
215: }
216:
217: break;
218:
219: case Types.FLOAT:
220:
221: if ((sumColumns != null)
222: && sumColumns
223: .contains(Integer
224: .valueOf(String
225: .valueOf(i)))) {
226: floatSumValue = resultSet.getFloat(i);
227:
228: if (String.valueOf(sumList.get(i))
229: .trim().length() >= 1) {
230: floatSumValue = floatSumValue
231: + Float
232: .valueOf(
233: String
234: .valueOf(sumList
235: .get(i)))
236: .floatValue();
237: } else {
238: floatSumValue = floatSumValue + 0.0f;
239: }
240:
241: sumList.add(i, Float.valueOf(String
242: .valueOf(floatSumValue)));
243: }
244:
245: if ((specificCellBodyFormat != null)
246: && specificCellBodyFormat
247: .containsKey(String
248: .valueOf(i))) {
249: DecimalFormat frmt = (DecimalFormat) specificCellBodyFormat
250: .get(String.valueOf(i));
251: returnString
252: .append(frmt.format(resultSet
253: .getDouble(i)));
254: } else {
255: returnString.append(resultSet
256: .getString(i));
257: }
258:
259: break;
260:
261: case Types.DOUBLE:
262:
263: if ((sumColumns != null)
264: && sumColumns
265: .contains(Integer
266: .valueOf(String
267: .valueOf(i)))) {
268: doubleSumValue = resultSet.getDouble(i);
269:
270: if (String.valueOf(sumList.get(i))
271: .trim().length() >= 1) {
272: doubleSumValue = doubleSumValue
273: + Double
274: .valueOf(
275: String
276: .valueOf(sumList
277: .get(i)))
278: .doubleValue();
279: } else {
280: doubleSumValue = doubleSumValue + 0.0;
281: }
282:
283: sumList.add(i, Double.valueOf(String
284: .valueOf(doubleSumValue)));
285: }
286:
287: if ((specificCellBodyFormat != null)
288: && specificCellBodyFormat
289: .containsKey(String
290: .valueOf(i))) {
291: DecimalFormat frmt = (DecimalFormat) specificCellBodyFormat
292: .get(String.valueOf(i));
293: returnString
294: .append(frmt.format(resultSet
295: .getDouble(i)));
296: } else {
297: returnString
298: .append(String
299: .valueOf(resultSet
300: .getDouble(i)));
301: }
302:
303: break;
304:
305: case Types.DATE:
306:
307: if ((specificCellBodyFormat != null)
308: && specificCellBodyFormat
309: .containsKey(String
310: .valueOf(i))) {
311: DateFormat frmt = (DateFormat) specificCellBodyFormat
312: .get(String.valueOf(i));
313: returnString.append(frmt
314: .format(resultSet.getDate(i)));
315: } else {
316: returnString.append(String
317: .valueOf(resultSet.getDate(i)));
318: }
319:
320: break;
321:
322: case Types.DECIMAL:
323:
324: if ((sumColumns != null)
325: && sumColumns
326: .contains(Integer
327: .valueOf(String
328: .valueOf(i)))) {
329: doubleSumValue = resultSet.getDouble(i);
330:
331: if (String.valueOf(sumList.get(i))
332: .trim().length() >= 1) {
333: doubleSumValue = doubleSumValue
334: + Double
335: .valueOf(
336: String
337: .valueOf(sumList
338: .get(i)))
339: .doubleValue();
340: } else {
341: doubleSumValue = doubleSumValue + 0.0;
342: }
343:
344: sumList.add(i, Double.valueOf(String
345: .valueOf(doubleSumValue)));
346: }
347:
348: if ((specificCellBodyFormat != null)
349: && specificCellBodyFormat
350: .containsKey(String
351: .valueOf(i))) {
352: returnString
353: .append(((Format) specificCellBodyFormat
354: .get(String.valueOf(i)))
355: .format(resultSet
356: .getString(i)));
357: } else {
358: returnString.append(resultSet
359: .getString(i));
360: }
361:
362: break;
363:
364: case Types.LONGVARCHAR:
365:
366: if ((specificCellBodyFormat != null)
367: && specificCellBodyFormat
368: .containsKey(String
369: .valueOf(i))) {
370: returnString
371: .append(((Format) specificCellBodyFormat
372: .get(String.valueOf(i)))
373: .format(resultSet
374: .getString(i)));
375: } else {
376: returnString.append(resultSet
377: .getString(i));
378: }
379:
380: break;
381:
382: case Types.NULL:
383: returnString.append(" ");
384:
385: break;
386:
387: case Types.NUMERIC:
388:
389: if ((sumColumns != null)
390: && sumColumns
391: .contains(Integer
392: .valueOf(String
393: .valueOf(i)))) {
394: doubleSumValue = resultSet.getDouble(i);
395:
396: if (String.valueOf(sumList.get(i))
397: .trim().length() >= 1) {
398: doubleSumValue = doubleSumValue
399: + Double
400: .valueOf(
401: String
402: .valueOf(sumList
403: .get(i)))
404: .doubleValue();
405: } else {
406: doubleSumValue = doubleSumValue + 0.0;
407: }
408:
409: sumList.add(i, Double.valueOf(String
410: .valueOf(doubleSumValue)));
411: }
412:
413: if ((specificCellBodyFormat != null)
414: && specificCellBodyFormat
415: .containsKey(String
416: .valueOf(i))) {
417: returnString
418: .append(((Format) specificCellBodyFormat
419: .get(String.valueOf(i)))
420: .format(resultSet
421: .getString(i)));
422: } else {
423: returnString.append(resultSet
424: .getString(i));
425: }
426:
427: break;
428:
429: case Types.TIMESTAMP:
430:
431: if ((specificCellBodyFormat != null)
432: && specificCellBodyFormat
433: .containsKey(String
434: .valueOf(i))) {
435: returnString
436: .append(((Format) specificCellBodyFormat
437: .get(String.valueOf(i)))
438: .format(resultSet
439: .getString(i)));
440: } else {
441: returnString.append((resultSet
442: .getString(i)));
443: }
444:
445: break;
446:
447: default:
448: returnString.append(resultSet.getString(i));
449:
450: break;
451: }
452: } else {
453: returnString.append(" ");
454: }
455:
456: returnString.append("</td>\n");
457: }
458:
459: returnString.append("</tr>\n");
460: }
461:
462: //prepare sum row
463: if (sumList != null) {
464: returnString.append(r.getRowBodyString());
465:
466: for (i = 1; i <= rptCols; i++) {
467: if ((specificCellBodyString != null)
468: && specificCellBodyString
469: .containsKey(String.valueOf(i))) {
470: returnString.append(specificCellBodyString
471: .get(String.valueOf(i)));
472: } else {
473: returnString.append(r.getCellBodyString());
474: }
475:
476: if ((sumList.get(i) != null)
477: || (String.valueOf(sumList.get(i)).trim()
478: .length() >= 1)) {
479: returnString.append(String.valueOf(sumList
480: .get(i)));
481: } else {
482: returnString.append(" ");
483: }
484:
485: returnString.append("</td>");
486: }
487:
488: returnString.append("</tr>");
489: }
490:
491: returnString.append("</tbody>\n");
492: returnString.append("</table>\n");
493: } catch (SQLException e) {
494: Debug.logError(e.getMessage(), module);
495:
496: ByteArrayOutputStream baos = new ByteArrayOutputStream();
497: e.printStackTrace(new PrintWriter(baos));
498: Debug.logError(new String(baos.toByteArray()), module);
499: } catch (Exception e) {
500: e.printStackTrace();
501: Debug.logError(e.getMessage(), module);
502:
503: ByteArrayOutputStream baos = new ByteArrayOutputStream();
504: e.printStackTrace(new PrintWriter(baos));
505: Debug.logError(new String(baos.toByteArray()), module);
506: } finally {
507: try {
508: if (resultSet != null) {
509: resultSet.close();
510: }
511:
512: if (preparedStatement != null) {
513: preparedStatement.close();
514: }
515:
516: if (connection != null) {
517: connection.close();
518: }
519: } catch (SQLException e) {
520: Debug.logError(e.getMessage(), module);
521:
522: ByteArrayOutputStream baos = new ByteArrayOutputStream();
523: e.printStackTrace(new PrintWriter(baos));
524: Debug.logError(new String(baos.toByteArray()), module);
525: }
526: }
527:
528: return returnString.toString();
529: }
530:
531: /**
532: * DOCUMENT ME!
533: *
534: * @param wholeString
535: * @param token
536: * @param replaceString
537: *
538: * @return
539: */
540: private static String replace(String wholeString, String token,
541: String replaceString) {
542: StringBuffer sb = new StringBuffer(wholeString);
543:
544: if (wholeString.indexOf(token) >= 1) {
545: sb.replace(wholeString.indexOf(token), (wholeString
546: .indexOf(token) + token.length()), replaceString);
547:
548: return sb.toString();
549: } else {
550: return wholeString;
551: }
552: }
553: }
|