Java tutorial
/********************************************************************************* * The contents of this file are subject to the Common Public Attribution * License Version 1.0 (the "License"); you may not use this file except in * compliance with the License. You may obtain a copy of the License at * http://www.openemm.org/cpal1.html. The License is based on the Mozilla * Public License Version 1.1 but Sections 14 and 15 have been added to cover * use of software over a computer network and provide for limited attribution * for the Original Developer. In addition, Exhibit A has been modified to be * consistent with Exhibit B. * 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 Original Code is OpenEMM. * The Original Developer is the Initial Developer. * The Initial Developer of the Original Code is AGNITAS AG. All portions of * the code written by AGNITAS AG are Copyright (c) 2007 AGNITAS AG. All Rights * Reserved. * * Contributor(s): AGNITAS AG. ********************************************************************************/ package org.agnitas.web; import org.agnitas.beans.ExportPredef; import org.agnitas.dao.ExportPredefDao; import org.agnitas.dao.MailinglistDao; import org.agnitas.dao.TargetDao; import org.agnitas.target.Target; import org.agnitas.util.AgnUtils; import org.agnitas.util.CsvTokenizer; import org.agnitas.util.SafeString; import org.agnitas.web.forms.ExportWizardForm; import org.apache.commons.lang.StringUtils; import org.apache.struts.action.*; import org.springframework.context.ApplicationContext; import org.springframework.jdbc.datasource.DataSourceUtils; import javax.servlet.ServletException; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.sql.DataSource; import java.io.*; import java.sql.Connection; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.LinkedList; import java.util.List; import java.util.Locale; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream; /** * Implementation of <strong>Action</strong> that handles customer exports * * @author Martin Helff */ public class ExportWizardAction extends StrutsActionBase { public static final int ACTION_QUERY = ACTION_LAST + 1; public static final int ACTION_COLLECT_DATA = ACTION_LAST + 2; public static final int ACTION_VIEW_STATUS = ACTION_LAST + 3; public static final int ACTION_DOWNLOAD = ACTION_LAST + 4; public static final int ACTION_VIEW_STATUS_WINDOW = ACTION_LAST + 5; public static final int ACTION_CONFIRM_DELETE = ACTION_LAST + 6; public static final int ACTION_SAVE_QUESTION = ACTION_LAST + 7; public static final int NO_MAILINGLIST = -1; private ExportPredefDao exportPredefDao; private TargetDao targetDao; private MailinglistDao mailinglistDao; private DataSource dataSource; // --------------------------------------------------------- Public Methods /** * Process the specified HTTP request, and create the corresponding HTTP * response (or forward to another web component that will create it). * Return an <code>ActionForward</code> instance describing where and how * control should be forwarded, or <code>null</code> if the response has * already been completed. * <br> * ACTION_LIST: forwards to predefined export definition list page. * <br><br> * ACTION_QUERY: loads chosen predefined export definition data into form or, if there was "Back" button pressed,<br> * clears form data; loads lists of target group and mailing lists into form; <br> * forwards to predefined export definition query page. * <br><br> * ACTION_COLLECT_DATA: proceeds exporting recipients from database according to the export definition;<br> * provides storing the export result in temporary zip file, stores name of the temporary file in form; * forwards to export view page. * <br><br> * ACTION_VIEW_STATUS_WINDOW: forwards to export view page. * <br><br> * ACTION_DOWNLOAD: provides downloading prepared zip file with list of recipients for export; sends notification <br> * email with export report for admin if the current admin have this option. * <br><br> * ACTION_SAVE_QUESTION: forwards to page for edit export definition name and description. * <br><br> * ACTION_SAVE: checks the name of export definition:<br> * if it is not filled or its length is less than 3 chars - forwards to page for editing export definition * name and description and shows validation error message<br> * if the name is valid, checks the export definition id value. If id of export definition is 0, inserts new * export definition db entry, otherwise updates db entry with given id.<br> * Forwards to export definition list page. * <br><br> * ACTION_CONFIRM_DELETE: checks if an ID of export definition is given and loads the export definition data;<br> * forwards to jsp with question to confirm deletion. * <br><br> * ACTION_DELETE: marks the chosen predefined export definition as deleted and saves the changes in database;<br> * forwards to predefined export definition list page. * <br><br> * Any other ACTION_* would cause a forward to "query" * <br><br> * @param form ActionForm object, data for the action filled by the jsp * @param req HTTP request * @param res HTTP response * @param mapping The ActionMapping used to select this instance * @exception IOException if an input/output error occurs * @exception ServletException if a servlet exception occurs * @return destination specified in struts-config.xml to forward to next jsp */ public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest req, HttpServletResponse res) throws IOException, ServletException { // Validate the request parameters specified by the user ExportWizardForm aForm = null; ActionMessages errors = new ActionMessages(); ActionForward destination = null; ApplicationContext aContext = this.getWebApplicationContext(); if (!AgnUtils.isUserLoggedIn(req)) { return mapping.findForward("logon"); } if (form != null) { aForm = (ExportWizardForm) form; } else { aForm = new ExportWizardForm(); } AgnUtils.userlogger().info("Action: " + aForm.getAction()); if (!allowed("wizard.export", req)) { errors.add(ActionMessages.GLOBAL_MESSAGE, new ActionMessage("error.permissionDenied")); saveErrors(req, errors); return null; } try { switch (aForm.getAction()) { case ExportWizardAction.ACTION_LIST: destination = mapping.findForward("list"); break; case ExportWizardAction.ACTION_QUERY: if (req.getParameter("exportPredefID").toString().compareTo("0") != 0) { loadPredefExportFromDB(aForm, aContext, req); } else { // clear form data if the "back" button has not been pressed: if (!AgnUtils.parameterNotEmpty(req, "exp_back.x")) { aForm.clearData(); } } int companyID = AgnUtils.getCompanyID(req); aForm.setTargetGroups(targetDao.getTargets(companyID, false)); aForm.setMailinglistObjects(mailinglistDao.getMailinglists(companyID)); aForm.setAction(ExportWizardAction.ACTION_COLLECT_DATA); destination = mapping.findForward("query"); break; case ExportWizardAction.ACTION_COLLECT_DATA: if (aForm.tryCollectingData()) { aForm.setAction(ExportWizardAction.ACTION_VIEW_STATUS); // RequestDispatcher dp=req.getRequestDispatcher(mapping.findForward("view").getPath()); // dp.forward(req, res); // res.flushBuffer(); // destination=null; destination = mapping.findForward("view"); collectContent(aForm, aContext, req); aForm.resetCollectingData(); AgnUtils.userlogger().info(AgnUtils.getAdmin(req).getUsername() + ": do export " + aForm.getLinesOK() + " recipients"); } else { errors.add("global", new ActionMessage("error.export.already_exporting")); } break; case ExportWizardAction.ACTION_VIEW_STATUS_WINDOW: destination = mapping.findForward("view_status"); break; case ExportWizardAction.ACTION_DOWNLOAD: byte bytes[] = new byte[16384]; int len = 0; File outfile = aForm.getCsvFile(); if (outfile != null && aForm.tryCollectingData()) { if (req.getSession().getAttribute("notify_email") != null) { String to_email = (String) req.getSession().getAttribute("notify_email"); if (to_email.trim().length() > 0) { AgnUtils.sendEmail("openemm@localhost", to_email, "EMM Data-Export", this.generateReportText(aForm, req), null, 0, "iso-8859-1"); } } aForm.resetCollectingData(); FileInputStream instream = new FileInputStream(outfile); res.setContentType("application/zip"); res.setHeader("Content-Disposition", "attachment; filename=\"" + outfile.getName() + "\";"); res.setContentLength((int) outfile.length()); ServletOutputStream ostream = res.getOutputStream(); while ((len = instream.read(bytes)) != -1) { ostream.write(bytes, 0, len); } destination = null; } else { errors.add("global", new ActionMessage("error.export.file_not_ready")); } break; case ExportWizardAction.ACTION_SAVE_QUESTION: aForm.setAction(ExportWizardAction.ACTION_SAVE); destination = mapping.findForward("savemask"); break; case ExportWizardAction.ACTION_SAVE: if (aForm.getShortname() == null || aForm.getShortname().length() < 3) { errors.add("shortname", new ActionMessage("error.nameToShort")); //aForm.setAction(ExportWizardAction.ACTION_SAVE); destination = mapping.findForward("savemask"); } else if (aForm.getExportPredefID() != 0) { saveExport(aForm, aContext, req); destination = mapping.findForward("list"); } else { insertExport(aForm, aContext, req); destination = mapping.findForward("list"); } break; case ExportWizardAction.ACTION_CONFIRM_DELETE: if (!"0".equals(req.getParameter("exportPredefID"))) { loadPredefExportFromDB(aForm, aContext, req); } aForm.setAction(ExportWizardAction.ACTION_DELETE); destination = mapping.findForward("delete_question"); break; case ExportWizardAction.ACTION_DELETE: if (!req.getParameter("exportPredefID").equals("0")) { markExportDeletedInDB(aForm, aContext, req); } destination = mapping.findForward("list"); break; default: aForm.setAction(ExportWizardAction.ACTION_QUERY); destination = mapping.findForward("query"); } List<ExportPredef> exports = exportPredefDao .getAllByCompany(AgnUtils.getAdmin(req).getCompany().getId()); aForm.setExportPredefList(exports); aForm.setExportPredefCount(exports.size()); } catch (Exception e) { AgnUtils.userlogger().error("execute: " + e + "\n" + AgnUtils.getStackTrace(e)); errors.add(ActionMessages.GLOBAL_MESSAGE, new ActionMessage("error.exception")); } // Report any errors we have discovered back to the original form if (!errors.isEmpty()) { saveErrors(req, errors); if (destination == null) return new ActionForward(mapping.getInput()); } return destination; } /** * Loads chosen predefined export data from database into form. * * @param aForm ExportWizardForm object * @param aContext application context * @param req HTTP request * @return true==success * false==error */ protected boolean loadPredefExportFromDB(ExportWizardForm aForm, ApplicationContext aContext, HttpServletRequest req) { CsvTokenizer aTok = null; ExportPredef exportPredef = exportPredefDao.get(aForm.getExportPredefID(), AgnUtils.getAdmin(req).getCompany().getId()); aForm.setShortname(exportPredef.getShortname()); aForm.setDescription(exportPredef.getDescription()); aForm.setCharset(exportPredef.getCharset()); aForm.setDelimiter(exportPredef.getDelimiter()); aForm.setSeparatorInternal(exportPredef.getSeparator()); aForm.setTargetID(exportPredef.getTargetID()); aForm.setMailinglistID(exportPredef.getMailinglistID()); aForm.setUserStatus(exportPredef.getUserStatus()); aForm.setUserType(exportPredef.getUserType()); SimpleDateFormat inputDateFormat = new SimpleDateFormat(getDatesParameterFormat()); Date timestampStart = exportPredef.getTimestampStart(); if (timestampStart != null) { aForm.setTimestampStart(inputDateFormat.format(timestampStart)); } Date timestampEnd = exportPredef.getTimestampEnd(); if (timestampEnd != null) { aForm.setTimestampEnd(inputDateFormat.format(timestampEnd)); } Date creationDateStart = exportPredef.getCreationDateStart(); if (creationDateStart != null) { aForm.setCreationDateStart(inputDateFormat.format(creationDateStart)); } Date creationDateEnd = exportPredef.getCreationDateEnd(); if (creationDateEnd != null) { aForm.setCreationDateEnd(inputDateFormat.format(creationDateEnd)); } Date mailinglistBindStart = exportPredef.getMailinglistBindStart(); if (mailinglistBindStart != null) { aForm.setMailinglistBindStart(inputDateFormat.format(mailinglistBindStart)); } Date mailinglistBindEnd = exportPredef.getMailinglistBindEnd(); if (mailinglistBindEnd != null) { aForm.setMailinglistBindEnd(inputDateFormat.format(mailinglistBindEnd)); } // process columns: try { aTok = new CsvTokenizer(exportPredef.getColumns(), ";"); aForm.setColumns(aTok.toArray()); if (exportPredef.getMailinglists().trim().length() > 0) { aTok = new CsvTokenizer(exportPredef.getMailinglists(), ";"); aForm.setMailinglists(aTok.toArray()); } } catch (Exception e) { AgnUtils.userlogger().error("loadPredefExportFromDB: " + e); return false; } return true; } /** * Creates new predefined export definition database entry * * @param aForm ExportWizardForm object * @param aContext application context * @param req HTTP request * @return true==success * false==error */ protected boolean insertExport(ExportWizardForm aForm, ApplicationContext aContext, HttpServletRequest req) { ExportPredef exportPredef = exportPredefDao.get(0, AgnUtils.getAdmin(req).getCompany().getId()); // perform insert: exportPredef.setShortname(aForm.getShortname()); exportPredef.setDescription(aForm.getDescription()); exportPredef.setCharset(aForm.getCharset()); exportPredef.setColumns(CsvTokenizer.join(aForm.getColumns(), ";")); exportPredef.setMailinglists(CsvTokenizer.join(aForm.getMailinglists(), ";")); exportPredef.setMailinglistID(aForm.getMailinglistID()); exportPredef.setDelimiter(aForm.getDelimiter()); String separator = aForm.getSeparator(); separator = "\t".equals(separator) ? "t" : separator; exportPredef.setSeparator(separator); exportPredef.setTargetID(aForm.getTargetID()); exportPredef.setUserStatus(aForm.getUserStatus()); exportPredef.setUserType(aForm.getUserType()); try { loadDateParametersFromFormToBean(aForm, exportPredef); } catch (ParseException e) { AgnUtils.userlogger().error(e); } exportPredefDao.save(exportPredef); return true; } /** * Updates predefined export definition database entry * * @param aForm ExportWizardForm object * @param aContext application context * @param req HTTP request * @return true==success * false==error */ protected boolean saveExport(ExportWizardForm aForm, ApplicationContext aContext, HttpServletRequest req) { ExportPredef exportPredef = exportPredefDao.get(aForm.getExportPredefID(), AgnUtils.getAdmin(req).getCompany().getId()); // perform update in db: exportPredef.setShortname(aForm.getShortname()); exportPredef.setDescription(aForm.getDescription()); exportPredef.setCharset(aForm.getCharset()); exportPredef.setColumns(CsvTokenizer.join(aForm.getColumns(), ";")); exportPredef.setMailinglists(CsvTokenizer.join(aForm.getMailinglists(), ";")); exportPredef.setMailinglistID(aForm.getMailinglistID()); exportPredef.setDelimiter(aForm.getDelimiter()); String separator = aForm.getSeparator(); separator = "\t".equals(separator) ? "t" : separator; exportPredef.setSeparator(separator); exportPredef.setTargetID(aForm.getTargetID()); exportPredef.setUserStatus(aForm.getUserStatus()); exportPredef.setUserType(aForm.getUserType()); try { loadDateParametersFromFormToBean(aForm, exportPredef); } catch (ParseException e) { AgnUtils.userlogger().error(e); } exportPredefDao.save(exportPredef); return true; } /** * Loads date values into given bean from the form; parsed dates by certain format before loading. * * @param aForm ExportWizardForm object * @param exportPredef ExportPredef bean object (is filling with data from the form inside the method) * @throws ParseException */ protected void loadDateParametersFromFormToBean(ExportWizardForm aForm, ExportPredef exportPredef) throws ParseException { SimpleDateFormat inputDateFormat = new SimpleDateFormat(getDatesParameterFormat()); String timestampStart = aForm.getTimestampStart(); if (StringUtils.isNotEmpty(timestampStart)) { exportPredef.setTimestampStart(inputDateFormat.parse(timestampStart)); } else { exportPredef.setTimestampStart(null); } String timestampEnd = aForm.getTimestampEnd(); if (StringUtils.isNotEmpty(timestampEnd)) { exportPredef.setTimestampEnd(inputDateFormat.parse(timestampEnd)); } else { exportPredef.setTimestampEnd(null); } String creationDateStart = aForm.getCreationDateStart(); if (StringUtils.isNotEmpty(creationDateStart)) { exportPredef.setCreationDateStart(inputDateFormat.parse(creationDateStart)); } else { exportPredef.setCreationDateStart(null); } String creationDateEnd = aForm.getCreationDateEnd(); if (StringUtils.isNotEmpty(creationDateEnd)) { exportPredef.setCreationDateEnd(inputDateFormat.parse(creationDateEnd)); } else { exportPredef.setCreationDateEnd(null); } String mailinglistBindStart = aForm.getMailinglistBindStart(); if (StringUtils.isNotEmpty(mailinglistBindStart)) { exportPredef.setMailinglistBindStart(inputDateFormat.parse(mailinglistBindStart)); } else { exportPredef.setMailinglistBindStart(null); } String mailinglistBindEnd = aForm.getMailinglistBindEnd(); if (StringUtils.isNotEmpty(mailinglistBindEnd)) { exportPredef.setMailinglistBindEnd(inputDateFormat.parse(mailinglistBindEnd)); } else { exportPredef.setMailinglistBindEnd(null); } } /** * Marks chosen export definition as deleted and updates its database entry. * * @param aForm ExportWizardForm object * @param aContext application context * @param req HTTP request * @return true */ protected boolean markExportDeletedInDB(ExportWizardForm aForm, ApplicationContext aContext, HttpServletRequest req) { ExportPredef exportPredef = exportPredefDao.get(aForm.getExportPredefID(), AgnUtils.getAdmin(req).getCompany().getId()); exportPredef.setDeleted(1); exportPredefDao.save(exportPredef); return true; } /** * Creates sql query for getting recipients according to the export definition conditions; * gets recipients from database; * stores the export result in temporary zip file. * * @param aForm ExportWizardForm object * @param aContext application context * @param req HTTP request */ protected void collectContent(ExportWizardForm aForm, ApplicationContext aContext, HttpServletRequest req) { int companyID = AgnUtils.getAdmin(req).getCompany().getId(); Locale loc_old = Locale.getDefault(); aForm.setDbExportStatusMessages(new LinkedList<String>()); aForm.setDbExportStatus(100); aForm.setLinesOK(0); Target aTarget = null; if (aForm.getTargetID() != 0) { aTarget = targetDao.getTarget(aForm.getTargetID(), companyID); aForm.setTargetID(aTarget.getId()); } String charset = aForm.getCharset(); if (charset == null || charset.trim().equals("")) { charset = "UTF-8"; aForm.setCharset(charset); // charset also in form } StringBuffer usedColumnsString = new StringBuffer(); int exportStartColumn = 2; for (String columnName : aForm.getColumns()) { // customer_id is selected by default in the the base sql statement if ("customer_id".equalsIgnoreCase(columnName)) { // mark customer_id to be exported too exportStartColumn = 1; } else { usedColumnsString.append(", cust." + columnName + " " + columnName); } } if (aForm.getMailinglists() != null) { for (int i = 0; i < aForm.getMailinglists().length; i++) { String ml = aForm.getMailinglists()[i]; usedColumnsString.append(", (SELECT m" + ml + ".user_status FROM customer_" + companyID + "_binding_tbl m" + ml + " WHERE m" + ml + ".customer_id = cust.customer_id AND m" + ml + ".mailinglist_id = " + ml + " AND m" + ml + ".mediatype = 0) as Userstate_Mailinglist_" + ml); usedColumnsString.append(", (SELECT m" + ml + "." + AgnUtils.changeDateName() + " FROM customer_" + companyID + "_binding_tbl m" + ml + " WHERE m" + ml + ".customer_id = cust.customer_id AND m" + ml + ".mailinglist_id = " + ml + " AND m" + ml + ".mediatype = 0) as Mailinglist_" + ml + "_Timestamp"); } } StringBuffer whereString = new StringBuffer(""); StringBuffer customerTableSql = new StringBuffer("SELECT * FROM (SELECT DISTINCT cust.customer_id" + usedColumnsString.toString() + " FROM customer_" + companyID + "_tbl cust"); if (aForm.getMailinglistID() != -1 && (aForm.getMailinglistID() > 0 || !aForm.getUserType().equals("E") || aForm.getUserStatus() != 0)) { customerTableSql.append(", customer_" + companyID + "_binding_tbl bind"); whereString.append(" cust.customer_id = bind.customer_id AND bind.mediatype=0"); } if (aForm.getMailinglistID() > 0) { whereString.append(" and bind.mailinglist_id = " + aForm.getMailinglistID()); } if (aForm.getMailinglistID() == NO_MAILINGLIST) { whereString.append(" NOT EXISTS (SELECT 1 FROM customer_" + companyID + "_binding_tbl bind WHERE cust.customer_id = bind.customer_id) "); } else { if (!aForm.getUserType().equals("E")) { whereString .append(" AND bind.user_type = '" + SafeString.getSQLSafeString(aForm.getUserType()) + "'"); } if (aForm.getUserStatus() != 0) { whereString.append(" AND bind.user_status = " + aForm.getUserStatus()); } } if (aForm.getTargetID() != 0) { if (aForm.getMailinglistID() != 0 || !aForm.getUserType().equals("E") || aForm.getUserStatus() != 0) { whereString.append(" AND "); } whereString.append(" (" + aTarget.getTargetSQL() + ")"); } String datesParametersString = getDatesParametersString(aForm); if (!StringUtils.isEmpty(whereString.toString())) { whereString.append(" and "); } whereString.append(datesParametersString); if (whereString.length() > 0) { customerTableSql.append(" WHERE " + whereString); } AgnUtils.userlogger().info("Generated export SQL query: " + customerTableSql); Connection con = DataSourceUtils.getConnection(dataSource); aForm.setCsvFile(null); PrintWriter out = null; Statement stmt = null; ResultSet rset = null; try { File systemUploadDirectory = AgnUtils.createDirectory(AgnUtils.getDefaultValue("system.upload")); File outFile = File.createTempFile("exp" + companyID + "_", ".zip", systemUploadDirectory); ZipOutputStream aZip = new ZipOutputStream(new FileOutputStream(outFile)); AgnUtils.userlogger().info("Export file <" + outFile.getAbsolutePath() + ">"); stmt = con.createStatement(); rset = stmt.executeQuery(customerTableSql.toString()); aZip.putNextEntry(new ZipEntry("emm_export.csv")); Locale.setDefault(new Locale("en")); out = new PrintWriter(new BufferedWriter(new OutputStreamWriter(aZip, charset))); ResultSetMetaData mData = rset.getMetaData(); int columnCount = mData.getColumnCount(); // Write CSV-Header line for (int i = exportStartColumn; i <= columnCount; i++) { if (i > exportStartColumn) { out.print(aForm.getSeparator()); } String columnName = mData.getColumnName(i); out.print(aForm.getDelimiter() + escapeChars(columnName, aForm.getDelimiter()) + aForm.getDelimiter()); } out.print("\n"); // Write data lines while (rset.next()) { for (int i = exportStartColumn; i <= columnCount; i++) { if (i > exportStartColumn) { out.print(aForm.getSeparator()); } String aValue; try { aValue = rset.getString(i); } catch (Exception ex) { aValue = null; // Exceptions should not break the export, but should be logged AgnUtils.userlogger().error("Exception in export:collectContent:", ex); } if (aValue == null) { // null values should be displayed as empty string aValue = ""; } else { aValue = escapeChars(aValue, aForm.getDelimiter()); aValue = aForm.getDelimiter() + aValue + aForm.getDelimiter(); } out.print(aValue); } out.print("\n"); aForm.setLinesOK(aForm.getLinesOK() + 1); } aForm.setCsvFile(outFile); } catch (Exception e) { AgnUtils.userlogger().error("collectContent: " + e); e.printStackTrace(); } finally { if (out != null) { out.close(); } if (rset != null) { try { rset.close(); } catch (SQLException e) { e.printStackTrace(); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } DataSourceUtils.releaseConnection(con, dataSource); aForm.setDbExportStatus(1001); Locale.setDefault(loc_old); } } private String getDatesParameterFormat() { return "dd.MM.yyyy"; } protected String getDatesDBFormat() { return "yyyy-MM-dd"; } protected String getTimestampColumnName() { return "change_date"; } private String getDatesParametersString(ExportWizardForm aForm) { String datesParameters = " 1 = 1 "; String timestampStart = aForm.getTimestampStart(); if (StringUtils.isNotEmpty(timestampStart)) { String dbDate = formatFormDateToDB(timestampStart); datesParameters += " and cust." + getTimestampColumnName() + " >= " + getDateSqlParam(dbDate); } String timestampEnd = aForm.getTimestampEnd(); if (StringUtils.isNotEmpty(timestampEnd)) { if (!datesParameters.isEmpty()) { datesParameters += " and "; } String dbDate = formatFormDateToDB(timestampEnd); datesParameters += "cust." + getTimestampColumnName() + " <= " + getDateSqlParam(dbDate); } String creationDateStart = aForm.getCreationDateStart(); if (StringUtils.isNotEmpty(creationDateStart)) { if (!datesParameters.isEmpty()) { datesParameters += " and "; } String dbDate = formatFormDateToDB(creationDateStart); datesParameters += "cust.creation_date >= " + getDateSqlParam(dbDate); } String creationDateEnd = aForm.getCreationDateEnd(); if (StringUtils.isNotEmpty(creationDateEnd)) { if (!datesParameters.isEmpty()) { datesParameters += "and "; } String dbDate = formatFormDateToDB(creationDateEnd); datesParameters += "cust.creation_date <= " + getDateSqlParam(dbDate); } String[] mailinglists = aForm.getMailinglists(); datesParameters += " )" + getSubqueryAlias(); if (mailinglists != null && mailinglists.length > 0) { String mailinglistBindStart = aForm.getMailinglistBindStart(); boolean andRequired = false; if (StringUtils.isNotEmpty(mailinglistBindStart)) { datesParameters += " WHERE "; for (int i = 0; i < mailinglists.length; i++) { if (i != 0) { datesParameters += " and "; } String dbDate = formatFormDateToDB(mailinglistBindStart); datesParameters += "Mailinglist_" + mailinglists[i] + "_Timestamp >= " + getDateSqlParam(dbDate); } andRequired = true; } String mailinglistBindEnd = aForm.getMailinglistBindEnd(); if (StringUtils.isNotEmpty(mailinglistBindEnd)) { if (andRequired) { datesParameters += " and "; } for (int i = 0; i < mailinglists.length; i++) { if (i != 0) { datesParameters += " and "; } String dbDate = formatFormDateToDB(mailinglistBindEnd); datesParameters += "Mailinglist_" + mailinglists[i] + "_Timestamp <= " + getDateSqlParam(dbDate); } } } return datesParameters; } protected String getSubqueryAlias() { return " AS customer_data "; } protected String getDateSqlParam(String dateString) { return "'" + dateString + "'"; } private String formatFormDateToDB(String dateString) { SimpleDateFormat formatter = new SimpleDateFormat(getDatesParameterFormat()); try { Date parsed = formatter.parse(dateString); formatter.applyPattern(getDatesDBFormat()); return formatter.format(parsed); } catch (ParseException e) { AgnUtils.userlogger().error("formatFormDateToDB: " + e); return ""; } } /** * Separates special characters from input string. * @param input input string * @param sepChar separation string * @return input string with separations */ protected String escapeChars(String input, String sepChar) { int pos = 0; StringBuffer tmp = new StringBuffer(input); while ((pos = input.indexOf(sepChar, pos)) != -1) { tmp = new StringBuffer(input); tmp.insert(pos, sepChar); pos += sepChar.length() + 1; input = tmp.toString(); } return input; } /** * Creates report with description of export to be sent to admin in notification email. * * @param aForm : ExportWizardForm object * @param req : request * @return report text */ protected String generateReportText(ExportWizardForm aForm, HttpServletRequest req) { StringBuffer report = new StringBuffer(""); report.append("Target-Group: " + aForm.getTargetID() + "\n"); report.append("Mailing-List: " + aForm.getMailinglistID() + "\n"); report.append("Number of Records: " + aForm.getLinesOK() + "\n"); report.append("IP-Adress while download: " + req.getRemoteAddr() + "\n"); report.append("Admin-ID: " + req.getSession().getAttribute("adminID") + "\n"); report.append("Filename: " + aForm.getDownloadName() + "\n"); return report.toString(); } public ExportPredefDao getExportPredefDao() { return exportPredefDao; } public void setExportPredefDao(ExportPredefDao exportPredefDao) { this.exportPredefDao = exportPredefDao; } public TargetDao getTargetDao() { return targetDao; } public void setTargetDao(TargetDao targetDao) { this.targetDao = targetDao; } public DataSource getDataSource() { return dataSource; } public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } public void setMailinglistDao(MailinglistDao mailinglistDao) { this.mailinglistDao = mailinglistDao; } }