001: /*
002: * File : $Source: /usr/local/cvs/alkacon/com.alkacon.opencms.formgenerator/src/com/alkacon/opencms/formgenerator/database/export/CmsCvsExportBean.java,v $
003: * Date : $Date: 2008-02-07 11:52:02 $
004: * Version: $Revision: 1.2 $
005: *
006: * This file is part of the Alkacon OpenCms Add-On Module Package
007: *
008: * Copyright (c) 2007 Alkacon Software GmbH (http://www.alkacon.com)
009: *
010: * The Alkacon OpenCms Add-On Module Package is free software:
011: * you can redistribute it and/or modify
012: * it under the terms of the GNU General Public License as published by
013: * the Free Software Foundation, either version 3 of the License, or
014: * (at your option) any later version.
015: *
016: * The Alkacon OpenCms Add-On Module Package is distributed
017: * in the hope that it will be useful,
018: * but WITHOUT ANY WARRANTY; without even the implied warranty of
019: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
020: * GNU General Public License for more details.
021: *
022: * You should have received a copy of the GNU General Public License
023: * along with the Alkacon OpenCms Add-On Module Package.
024: * If not, see http://www.gnu.org/licenses/.
025: *
026: * For further information about Alkacon Software GmbH, please see the
027: * company website: http://www.alkacon.com.
028: *
029: * For further information about OpenCms, please see the
030: * project website: http://www.opencms.org.
031: */
032:
033: package com.alkacon.opencms.formgenerator.database.export;
034:
035: import com.alkacon.opencms.formgenerator.CmsFormHandler;
036: import com.alkacon.opencms.formgenerator.database.CmsFormDataAccess;
037: import com.alkacon.opencms.formgenerator.database.CmsFormDataBean;
038:
039: import java.sql.SQLException;
040: import java.text.Collator;
041: import java.util.Collections;
042: import java.util.Date;
043: import java.util.Iterator;
044: import java.util.List;
045:
046: /**
047: * Bean that supports the data export.<p>
048: *
049: * Works together with
050: * /system/modules/com.alkacon.opencms.formgenerator/elements/datadownload.jsp.<p>
051: *
052: * @author Achim Westermann
053: *
054: * @version $Revision: 1.2 $
055: *
056: * @since 7.0.4
057: *
058: */
059: public class CmsCvsExportBean {
060:
061: /** Request parameter for the start time of the data to export. */
062: public static final String PARAM_EXPORT_DATA_TIME_END = "endtime";
063:
064: /** Request parameter for the start time of the data to export. */
065: public static final String PARAM_EXPORT_DATA_TIME_START = "starttime";
066:
067: /** The end time for data sets to export. */
068: private Date m_endTime;
069:
070: /** The form that was used to input the data to export. */
071: private CmsFormHandler m_formHandler;
072:
073: /** The start time for data sets to export. */
074: private Date m_startTime;
075:
076: /**
077: * Creates an instance based upon data that was entered with the given form.<p>
078: *
079: * @param formHandler the origin of the data
080: */
081: public CmsCvsExportBean(final CmsFormHandler formHandler) {
082:
083: m_formHandler = formHandler;
084: m_startTime = new Date(0);
085: m_endTime = new Date(Long.MAX_VALUE);
086: }
087:
088: /**
089: * Escapes CSV values for excel.<p>
090: *
091: * @param value the value to escape
092: *
093: * @return the escaped excel value.
094: */
095: private String escapeExcelCsv(final String value) {
096:
097: String result = value;
098: StringBuffer buffer = new StringBuffer();
099: buffer.append("\"");
100: char[] chars = value.toCharArray();
101: for (int i = 0; i < chars.length; i++) {
102: // escape double quote escape delimiter within value:
103: if ('"' == chars[i]) {
104: buffer.append("\"");
105: }
106: buffer.append(chars[i]);
107: }
108: buffer.append("\"");
109: result = buffer.toString();
110: return result;
111: }
112:
113: /** The default value delimiter for CSV files in Excel. */
114: public static final char EXCEL_DEFAULT_CSV_DELMITER = ';';
115:
116: /**
117: * Returns the csv export file content.<p>
118: *
119: * @return the csv export file content
120: *
121: * @throws SQLException if sth goes wrong
122: */
123: public String exportData() throws SQLException {
124:
125: /*
126: * TODO: Access the CmsForm (or CmsFormHandler) and put out all
127: * fields in the exact order - put fields that do not exist any longer
128: * to the back (note: readAllFormFieldNames is required for the old values).
129: */
130:
131: StringBuffer result = new StringBuffer();
132: List columnNames = CmsFormDataAccess.getInstance()
133: .readAllFormFieldNames(
134: getForm().getFormConfiguration().getFormId(),
135: getStartTime(), getEndTime());
136: Collections.sort(columnNames, Collator
137: .getInstance(m_formHandler.getRequestContext()
138: .getLocale()));
139:
140: List dataEntries = CmsFormDataAccess.getInstance()
141: .readFormData(
142: getForm().getFormConfiguration().getFormId(),
143: getStartTime(), getEndTime());
144:
145: // loop 1 - write the headers:
146: result.append("Creation date");
147: result.append(EXCEL_DEFAULT_CSV_DELMITER);
148: result.append("Resource path");
149: result.append(EXCEL_DEFAULT_CSV_DELMITER);
150: Iterator itColumns = columnNames.iterator();
151: while (itColumns.hasNext()) {
152: String columnName = (String) itColumns.next();
153: columnName = escapeExcelCsv(columnName);
154: result.append(columnName);
155: if (itColumns.hasNext()) {
156: result.append(EXCEL_DEFAULT_CSV_DELMITER);
157: }
158: }
159: result.append("\r\n");
160: // loop 2 - write the data:
161: Iterator itRows = dataEntries.iterator();
162: while (itRows.hasNext()) {
163: CmsFormDataBean row = (CmsFormDataBean) itRows.next();
164: // create an entry for each column, even if some rows (data sets)
165: // do not contain the field value because it was
166: // a) not entered
167: // b) the form was changed in structure over time
168: // c) developer errors, hw /sw problems...
169: result.append(new Date(row.getDateCreated()));
170: result.append(EXCEL_DEFAULT_CSV_DELMITER);
171: result.append(row.getResourcePath());
172: result.append(EXCEL_DEFAULT_CSV_DELMITER);
173: itColumns = columnNames.iterator();
174: while (itColumns.hasNext()) {
175: String columnName = (String) itColumns.next();
176: String value = row.getFieldValue(columnName);
177: if (value != null) {
178: value = escapeExcelCsv(value);
179: result.append(value);
180: }
181: if (itColumns.hasNext()) {
182: result.append(EXCEL_DEFAULT_CSV_DELMITER);
183: }
184: }
185: result.append("\r\n");
186: }
187: return result.toString();
188: }
189:
190: /**
191: * Returns the endTime.<p>
192: *
193: * @return the endTime
194: */
195: public Date getEndTime() {
196:
197: return m_endTime;
198: }
199:
200: /**
201: * Returns the form handler.<p>
202: *
203: * @return the form handler
204: */
205: public CmsFormHandler getForm() {
206:
207: return m_formHandler;
208: }
209:
210: /**
211: * Returns the startTime.<p>
212: *
213: * @return the startTime
214: */
215: public Date getStartTime() {
216:
217: return m_startTime;
218: }
219:
220: /**
221: * Sets the endTime.<p>
222: *
223: * @param endTime the endTime to set
224: */
225: public void setEndTime(Date endTime) {
226:
227: m_endTime = endTime;
228: }
229:
230: /**
231: * Sets the startTime.<p>
232: *
233: * @param startTime the startTime to set
234: */
235: public void setStartTime(Date startTime) {
236:
237: m_startTime = startTime;
238: }
239:
240: }
|