/*********************************************************************************
* 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.dao.impl;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.sql.DataSource;
import org.agnitas.beans.BindingEntry;
import org.agnitas.beans.CustomerImportStatus;
import org.agnitas.beans.ProfileField;
import org.agnitas.beans.Recipient;
import org.agnitas.beans.impl.PaginatedListImpl;
import org.agnitas.beans.impl.RecipientImpl;
import org.agnitas.dao.RecipientDao;
import org.agnitas.util.AgnUtils;
import org.agnitas.util.CaseInsensitiveMap;
import org.agnitas.util.CsvColInfo;
import org.agnitas.util.SafeString;
import org.apache.commons.beanutils.BasicDynaClass;
import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.DynaProperty;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.displaytag.pagination.PaginatedList;
import org.hibernate.dialect.Dialect;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;
/**
*
* @author Nicole Serek, Andreas Rehak
*/
public class RecipientDaoImpl implements RecipientDao {
private static final transient Logger logger = Logger.getLogger(RecipientDaoImpl.class);
private static Integer maxRecipient = null;
private int getMaxRecipient() {
if (maxRecipient == null) {
synchronized (this) {
if (maxRecipient == null) {
maxRecipient = new Integer(AgnUtils.getDefaultIntValue("recipient.maxRows"));
}
}
}
if (maxRecipient == null) {
return 0;
}
return maxRecipient.intValue();
}
@Override
public boolean mayAdd(int companyID, int count) {
if(getMaxRecipient() != 0) {
JdbcTemplate jdbc = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
String sql = "select count(customer_id) from customer_" + companyID + "_tbl";
int current = jdbc.queryForInt(sql);
int max = getMaxRecipient();
if(max == 0 || current+count <= max) {
return true;
}
return false;
} else {
return true;
}
}
@Override
public boolean isNearLimit(int companyID, int count) {
if(getMaxRecipient() != 0) {
JdbcTemplate jdbc = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
String sql = "select count(customer_id) from customer_" + companyID + "_tbl";
int current=jdbc.queryForInt(sql);
int max=(int) (getMaxRecipient()*0.9);
if(max == 0 || current+count <= max) {
return false;
}
return true;
} else {
return true;
}
}
/**
* Inserts new customer record in Database with a fresh customer-id
*
* @return true on success
*/
@Override
public int insertNewCust(Recipient cust) {
StringBuffer Columns = new StringBuffer("(");
StringBuffer Values = new StringBuffer(" VALUES (");
String aColumn = null;
String aParameter = null;
String ColType = null;
int intValue = 0;
int day, month, year;
int hour=0;
int minute=0;
int second=0;
StringBuffer insertCust = new StringBuffer("INSERT INTO customer_" + cust.getCompanyID() + "_tbl ");
boolean appendIt = false;
boolean hasDefault = false;
String appendColumn = null;
String appendValue = null;
NumberFormat aFormat1 = null;
NumberFormat aFormat2 = null;
if(cust.getCustDBStructure() == null) {
cust.loadCustDBStructure();
}
// logic from former method getNewCustomerID
String sqlStatement = null;
int customerID = 0;
int companyID = cust.getCompanyID();
if(companyID == 0) {
return customerID;
}
if(mayAdd(companyID, 1) == false) {
return customerID;
}
try {
// set customerID for Oracle
if(AgnUtils.isOracleDB()) {
JdbcTemplate tmpl = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
sqlStatement = "select customer_" + companyID + "_tbl_seq.nextval FROM dual";
customerID = tmpl.queryForInt(sqlStatement);
cust.setCustomerID(customerID);
}
} catch (Exception e) {
logger.error( "Error inserting new customer", e);
customerID = 0;
return customerID;
}
if( logger.isDebugEnabled()) {
logger.debug("new customerID: "+ customerID);
}
// Oracle: put customerID in SQL statement at first
// (MySQL: no customerID available, yet)
if(AgnUtils.isOracleDB()) {
Columns.append("customer_id");
Values.append(Integer.toString(cust.getCustomerID()));
}
Iterator<String> i = cust.getCustDBStructure().keySet().iterator();
while(i.hasNext()) {
aColumn = i.next();
ColType = cust.getCustDBStructure().get(aColumn);
appendIt = false;
hasDefault = false;
if(!aColumn.equalsIgnoreCase("customer_id")) {
if(aColumn.equalsIgnoreCase("creation_date") || aColumn.equalsIgnoreCase("timestamp") || aColumn.equalsIgnoreCase("change_date")) {
appendValue = "current_timestamp";
appendColumn = aColumn;
appendIt = true;
} else if(ColType.equalsIgnoreCase("DATE")) {
if(cust.getCustParameters(aColumn + "_DAY_DATE") != null && cust.getCustParameters(aColumn + "_MONTH_DATE") != null && cust.getCustParameters(aColumn + "_YEAR_DATE") != null) {
aFormat1 = new DecimalFormat("00");
aFormat2 = new DecimalFormat("0000");
try {
if(!cust.getCustParameters(aColumn + "_DAY_DATE").trim().equals("")) {
day = Integer.parseInt(cust.getCustParameters(aColumn+"_DAY_DATE"));
month = Integer.parseInt(cust.getCustParameters(aColumn+"_MONTH_DATE"));
year = Integer.parseInt(cust.getCustParameters(aColumn+"_YEAR_DATE"));
if((cust.getCustParameters(aColumn + "_HOUR_DATE") != null) && !cust.getCustParameters(aColumn + "_HOUR_DATE").trim().equals("")) {
hour = Integer.parseInt(cust.getCustParameters(aColumn+"_HOUR_DATE"));
}
if((cust.getCustParameters(aColumn + "_MINUTE_DATE") != null) && !cust.getCustParameters(aColumn + "_MINUTE_DATE").trim().equals("")) {
minute = Integer.parseInt(cust.getCustParameters(aColumn+"_MINUTE_DATE"));
}
if((cust.getCustParameters(aColumn + "_SECOND_DATE") != null) && !cust.getCustParameters(aColumn + "_SECOND_DATE").trim().equals("")) {
second = Integer.parseInt(cust.getCustParameters(aColumn+"_SECOND_DATE"));
}
if ( AgnUtils.isOracleDB() ) {
appendValue = "to_date('"+ aFormat1.format(day) +"."+aFormat1.format(month)+"."+aFormat2.format(year)+" "+ aFormat1.format(hour)+":"+aFormat1.format(minute)+":"+aFormat1.format(second)+"', 'DD.MM.YYYY HH24:MI:SS')";
} else {
appendValue = "STR_TO_DATE('"+ aFormat1.format(day) +"-"+aFormat1.format(month)+"-"+aFormat2.format(year)+" "+ aFormat1.format(hour)+":"+aFormat1.format(minute)+":"+aFormat1.format(second)+"', '%d-%m-%Y %H:%i:%s')";
}
appendColumn = aColumn;
appendIt = true;
} else {
ProfileField tmp = cust.getCustDBProfileStructure().get(aColumn);
if (tmp != null) {
String defaultValue = tmp.getDefaultValue();
if (!StringUtils.isBlank(defaultValue)) {
appendValue = createDateDefaultValueExpression( defaultValue);
hasDefault = true;
}
}
if (!hasDefault) {
appendValue = "null";
}
appendColumn = aColumn;
appendIt = true;
}
} catch (Exception e1) {
logger.error("insertNewCust: (" + aColumn + ") " + e1.getMessage(), e1);
}
} else {
ProfileField tmp = cust.getCustDBProfileStructure().get(aColumn);
if (tmp != null) {
String defaultValue = tmp.getDefaultValue();
if (!StringUtils.isBlank(defaultValue)) {
appendValue = createDateDefaultValueExpression( defaultValue);
hasDefault = true;
}
}
if (hasDefault) {
appendColumn = aColumn;
appendIt=true;
}
}
}
if(ColType.equalsIgnoreCase("INTEGER") || ColType.equalsIgnoreCase("DOUBLE")) {
aParameter = cust.getCustParameters(aColumn);
if(!StringUtils.isEmpty(aParameter)) {
try {
intValue = Integer.parseInt(aParameter);
} catch (Exception e1) {
intValue = 0;
}
appendValue = Integer.toString(intValue);
appendColumn = aColumn;
appendIt = true;
} else {
ProfileField tmp = cust.getCustDBProfileStructure().get( aColumn );
if (tmp != null) {
String defaultValue = tmp.getDefaultValue();
if (!StringUtils.isBlank(defaultValue)) {
appendValue = defaultValue;
hasDefault = true;
}
}
if (hasDefault) {
appendColumn = aColumn;
appendIt = true;
}
}
}
if(ColType.equalsIgnoreCase("VARCHAR") || ColType.equalsIgnoreCase("CHAR")) {
aParameter = cust.getCustParameters(aColumn);
if(!StringUtils.isEmpty(aParameter)) {
appendValue = "'" + SafeString.getSQLSafeString(aParameter) + "'";
appendColumn = aColumn;
appendIt = true;
} else {
ProfileField tmp = cust.getCustDBProfileStructure().get(aColumn);
if (tmp != null) {
String defaultValue = tmp.getDefaultValue();
if (!StringUtils.isBlank(defaultValue) ) {
appendValue = "'" + defaultValue + "'";
hasDefault = true;
}
}
if (hasDefault) {
appendColumn = aColumn;
appendIt = true;
}
}
}
if(appendIt) {
// if Columns contains more than "(", i.e. customerID was set
if(!Columns.toString().equals("(")) {
Columns.append(", ");
Values.append(", ");
}
Columns.append(appendColumn.toLowerCase());
Values.append(appendValue);
}
}
}
Columns.append(")");
Values.append(")");
insertCust.append(Columns.toString());
insertCust.append(Values.toString());
if(AgnUtils.isOracleDB()) {
try {
JdbcTemplate tmpl = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
tmpl.execute(insertCust.toString());
if( logger.isDebugEnabled()) {
logger.debug("insertCust: "+insertCust.toString());
}
} catch (Exception e3) {
logger.error( "insertNewCustomer in Oracle", e3);
cust.setCustomerID(0);
return 0;
}
} else {
try {
SqlUpdate sqlUpdate = new SqlUpdate((DataSource)this.applicationContext.getBean("dataSource"), insertCust.toString());
sqlUpdate.setReturnGeneratedKeys(true);
sqlUpdate.compile();
GeneratedKeyHolder key = new GeneratedKeyHolder();
sqlUpdate.update(null, key);
customerID = key.getKey().intValue();
cust.setCustomerID(customerID);
} catch (Exception e3) {
logger.error("insertNewCust in MySQL", e3);
cust.setCustomerID(0);
return 0;
}
}
return cust.getCustomerID();
}
/**
* Updates Customer in DB. customerID must be set to a valid id, customer-data is taken from this.customerData
*
* @return true on success
*/
@Override
public boolean updateInDB(Recipient cust) {
String currentTimestamp=AgnUtils.getSQLCurrentTimestampName();
String aColumn;
String colType = null;
boolean appendIt = false;
StringBuffer updateCust = new StringBuffer("UPDATE customer_" + cust.getCompanyID() + "_tbl SET " + AgnUtils.changeDateName() + "=" + currentTimestamp);
NumberFormat aFormat1 = null;
NumberFormat aFormat2 = null;
int day, month, year;
int hour=0;
int minute=0;
int second=0;
String aParameter = null;
int intValue;
String appendValue = null;
boolean result = true;
if(cust.getCustDBStructure() == null) {
cust.loadCustDBStructure();
}
if(cust.getCustomerID() == 0) {
if( logger.isInfoEnabled()) {
logger.info("updateInDB: creating new customer");
}
if(this.insertNewCust(cust) == 0) {
result = false;
}
} else {
if(cust.isChangeFlag()) { // only if something has changed
Iterator<String> i = cust.getCustDBStructure().keySet().iterator();
while(i.hasNext()) {
aColumn = i.next();
colType = (String) cust.getCustDBStructure().get(aColumn);
appendIt = false;
if(aColumn.equalsIgnoreCase("customer_id") || aColumn.equalsIgnoreCase("change_date") || aColumn.equalsIgnoreCase("timestamp") || aColumn.equalsIgnoreCase("creation_date") || aColumn.equalsIgnoreCase("datasource_id")) {
continue;
}
if(colType.equalsIgnoreCase("DATE")) {
if((cust.getCustParameters().get(aColumn + "_DAY_DATE") != null) && (cust.getCustParameters().get(aColumn + "_MONTH_DATE") != null) && (cust.getCustParameters().get(aColumn + "_YEAR_DATE") != null)) {
aFormat1 = new DecimalFormat("00");
aFormat2 = new DecimalFormat("0000");
try {
if(!((String) cust.getCustParameters().get(aColumn + "_DAY_DATE")).trim().equals("")) {
day = Integer.parseInt((String) cust.getCustParameters().get(aColumn + "_DAY_DATE"));
month = Integer.parseInt((String) cust.getCustParameters().get(aColumn + "_MONTH_DATE"));
year = Integer.parseInt((String) cust.getCustParameters().get(aColumn + "_YEAR_DATE"));
if((cust.getCustParameters().get(aColumn + "_HOUR_DATE") != null) && !cust.getCustParameters(aColumn + "_HOUR_DATE").trim().equals("")) {
hour = Integer.parseInt((String) cust.getCustParameters().get(aColumn + "_HOUR_DATE"));
}
if((cust.getCustParameters().get(aColumn + "_MINUTE_DATE") != null) && !cust.getCustParameters(aColumn + "_MINUTE_DATE").trim().equals("")) {
minute = Integer.parseInt((String) cust.getCustParameters().get(aColumn + "_MINUTE_DATE"));
}
if((cust.getCustParameters().get(aColumn + "_SECOND_DATE") != null) && !cust.getCustParameters(aColumn + "_SECOND_DATE").trim().equals("")) {
second = Integer.parseInt((String) cust.getCustParameters().get(aColumn + "_SECOND_DATE"));
}
if (AgnUtils.isOracleDB()) {
appendValue = aColumn.toLowerCase() + "=to_date('"+ aFormat1.format(day) +"."+aFormat1.format(month)+"."+aFormat2.format(year)+" "+ aFormat1.format(hour)+":"+aFormat1.format(minute)+":"+aFormat1.format(second)+"', 'DD.MM.YYYY HH24:MI:SS')";
} else {
appendValue = aColumn.toLowerCase() + "=STR_TO_DATE('"+ aFormat1.format(day) +"-"+aFormat1.format(month)+"-"+aFormat2.format(year)+" "+ aFormat1.format(hour)+":"+aFormat1.format(minute)+":"+aFormat1.format(second)+"', '%d-%m-%Y %H:%i:%s')";
}
appendIt = true;
} else {
appendValue = aColumn.toLowerCase() + "=null";
appendIt = true;
}
} catch (Exception e1) {
logger.error("updateInDB: Could not parse Date "+aColumn + " because of "+e1.getMessage(), e1);
}
} else {
logger.error("updateInDB: Parameter missing!");
}
} else if(colType.equalsIgnoreCase("INTEGER")) {
aParameter = (String) cust.getCustParameters(aColumn);
if(!StringUtils.isEmpty(aParameter)){
try {
intValue = Integer.parseInt(aParameter);
} catch (Exception e1) {
intValue = 0;
}
appendValue = aColumn.toLowerCase() + "=" + intValue;
appendIt = true;
} else {
appendValue = aColumn.toLowerCase() + "=null";
appendIt = true;
}
} else if(colType.equalsIgnoreCase("DOUBLE")) {
double dValue;
aParameter = (String) cust.getCustParameters(aColumn);
if(!StringUtils.isEmpty(aParameter)){
try {
dValue = Double.parseDouble(aParameter);
} catch(Exception e1) {
dValue = 0;
}
appendValue = aColumn.toLowerCase() + "=" + dValue;
appendIt = true;
} else {
appendValue = aColumn.toLowerCase() + "=null";
appendIt = true;
}
} else /* if(colType.equalsIgnoreCase("VARCHAR") || colType.equalsIgnoreCase("CHAR"))*/ {
aParameter = (String) cust.getCustParameters(aColumn);
if(!StringUtils.isEmpty(aParameter)) {
appendValue = aColumn.toLowerCase() + "='" + SafeString.getSQLSafeString(aParameter) + "'";
appendIt = true;
} else {
appendValue = aColumn.toLowerCase() + "=null";
appendIt = true;
}
}
if(appendIt) {
updateCust.append(", ");
updateCust.append(appendValue);
}
}
updateCust.append(" WHERE customer_id=" + cust.getCustomerID());
try {
JdbcTemplate tmpl = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
if( logger.isInfoEnabled()) {
logger.info("updateInDB: " + updateCust.toString());
}
tmpl.execute(updateCust.toString());
if(cust.getCustParameters("DATASOURCE_ID") != null) {
String sql = "select datasource_id from customer_" + cust.getCompanyID() + "_tbl where customer_id = ?";
@SuppressWarnings("unchecked")
List<Map<String, Object>> list = tmpl.queryForList(sql, new Object[] {new Integer(cust.getCustomerID())});
Iterator<Map<String, Object>> id = list.iterator();
if(!id.hasNext()) {
aParameter = (String) cust.getCustParameters("DATASOURCE_ID");
if(!StringUtils.isEmpty(aParameter)){
try {
intValue = Integer.parseInt(aParameter);
sql = "update customer_" + cust.getCompanyID() + "_tbl set datasource_id = " + intValue + " where customer_id = " + cust.getCustomerID();
tmpl.execute(sql);
} catch (Exception e1) {
logger.error( "Error updating customer", e1);
}
}
}
}
} catch(Exception e3) {
// Util.SQLExceptionHelper(e3,dbConn);
logger.error("updateInDB: " + e3.getMessage(), e3);
result = false;
}
} else {
if( logger.isInfoEnabled()) {
logger.info("updateInDB: nothing changed");
}
}
}
return result;
}
/**
* Find Subscriber by providing a column-name and a value. Only exact machtes possible.
*
* @return customerID or 0 if no matching record found
* @param col Column-Name
* @param value Value to search for in col
*/
@Override
public int findByKeyColumn(Recipient cust, String col, String value) {
int val = 0;
String aType = null;
String getCust = null;
try {
if(cust.getCustDBStructure() == null) {
cust.loadCustDBStructure();
}
if ("email".equalsIgnoreCase(col)) {
value = AgnUtils.normalizeEmail(value);
}
aType = (String) cust.getCustDBStructure().get(col);
if(aType != null) {
if(aType.equalsIgnoreCase("DECIMAL") || aType.equalsIgnoreCase("INTEGER") || aType.equalsIgnoreCase("DOUBLE")) {
try {
val = Integer.parseInt(value);
} catch (Exception e) {
val = 0;
}
getCust = "SELECT customer_id FROM customer_" + cust.getCompanyID() + "_tbl cust WHERE cust." + SafeString.getSQLSafeString(col, 30) + "=" + val;
}
if(aType.equalsIgnoreCase("VARCHAR") || aType.equalsIgnoreCase("CHAR")) {
getCust = "SELECT customer_id FROM customer_" + cust.getCompanyID() + "_tbl cust WHERE cust." + SafeString.getSQLSafeString(col, 30) + "='" + SafeString.getSQLSafeString(value) + "'";
}
if( logger.isInfoEnabled()) {
logger.info("RecipientDaoImpl:findByKeyColumn: "+getCust);
}
JdbcTemplate tmpl = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
// cannot use queryForInt, because of possible existing doublettes
@SuppressWarnings("unchecked")
List<Map<String,Integer>> custList = tmpl.queryForList(getCust);
if(custList.size() > 0) {
Map<String, Object> map = new CaseInsensitiveMap<Object>(custList.get(0));
cust.setCustomerID(((Number) map.get("customer_id")).intValue());
} else {
cust.setCustomerID(0);
}
}
} catch (Exception e) {
logger.error( "findByKeyColumn (sql: " + getCust + ")", e);
cust.setCustomerID(0);
}
return cust.getCustomerID();
}
@Override
public int findByColumn(int companyID, String col, String value) {
Recipient cust = (Recipient) applicationContext.getBean("Recipient");
cust.setCompanyID(companyID);
int custID = 0;
int val = 0;
String aType = null;
String getCust = null;
if(cust.getCustDBStructure() == null) {
cust.loadCustDBStructure();
}
if(col.toLowerCase().equals("email")) {
value=value.toLowerCase();
}
aType = (String) cust.getCustDBStructure().get(col.toLowerCase());
if(aType != null) {
if(aType.equalsIgnoreCase("VARCHAR") || aType.equalsIgnoreCase("CHAR")) {
getCust = "select customer_id from customer_" + companyID + "_tbl cust where lower(cust." + SafeString.getSQLSafeString(col, 30) + ")=lower('" + SafeString.getSQLSafeString(value) + "')";
} else {
try {
val = Integer.parseInt(value);
} catch (Exception e) {
val = 0;
}
getCust = "select customer_id from customer_" + companyID + "_tbl cust where cust."+SafeString.getSQLSafeString(col, 30)+"="+val;
}
try {
JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
//custID = tmpl.queryForInt(getCust);
@SuppressWarnings("unchecked")
List<Map<String, Object>> results = tmpl.queryForList(getCust);
if (results.size() > 0) {
Map<String, Object> map = results.get(0);
custID = ((Number) map.get("customer_id")).intValue();
}
} catch (Exception e) {
custID = 0;
}
}
return custID;
}
/**
* Find Subscriber by providing a username and password. Only exact machtes possible.
*
* @return customerID or 0 if no matching record found
* @param userCol Column-Name for Username
* @param userValue Value for Username
* @param passCol Column-Name for Password
* @param passValue Value for Password
*/
@Override
public int findByUserPassword(int companyID, String userCol, String userValue, String passCol, String passValue) {
String getCust = null;
int customerID = 0;
if(userCol.toLowerCase().equals("email")) {
userValue = userValue.toLowerCase();
}
getCust = "SELECT customer_id FROM customer_" + companyID + "_tbl cust WHERE cust."+SafeString.getSQLSafeString(userCol, 30)+"='"+SafeString.getSQLSafeString(userValue)+"' AND cust."+SafeString.getSQLSafeString(passCol, 30)+"='"+SafeString.getSQLSafeString(passValue)+"'";
try {
JdbcTemplate tmpl = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
customerID = tmpl.queryForInt(getCust);
} catch (Exception e) {
logger.error( "findByUserPassword", e);
customerID = 0;
}
return customerID;
}
/**
* Load complete Subscriber-Data from DB. customerID must be set first for this method.
*
* @return Map with Key/Value-Pairs of customer data
*/
@Override
public CaseInsensitiveMap<Object> getCustomerDataFromDb(int companyID, int customerID) {
String aName = null;
String aValue = null;
int a;
java.sql.Timestamp aTime = null;
Recipient cust = (Recipient) applicationContext.getBean("Recipient");
if(cust.getCustParameters() == null) {
cust.setCustParameters(new CaseInsensitiveMap<Object>());
}
String getCust = "SELECT * FROM customer_" + companyID + "_tbl WHERE customer_id=" + customerID;
if(cust.getCustDBStructure() == null) {
cust.loadCustDBStructure();
}
DataSource ds = (DataSource)this.applicationContext.getBean("dataSource");
Connection con = DataSourceUtils.getConnection(ds);
try {
Statement stmt = con.createStatement();
ResultSet rset = stmt.executeQuery(getCust);
if( logger.isInfoEnabled()) {
logger.info("getCustomerDataFromDb: "+getCust);
}
if(rset.next()) {
ResultSetMetaData aMeta = rset.getMetaData();
for(a = 1; a <= aMeta.getColumnCount(); a++) {
aValue = null;
aName = aMeta.getColumnName(a).toLowerCase();
switch(aMeta.getColumnType(a)) {
case java.sql.Types.TIMESTAMP:
case java.sql.Types.TIME:
case java.sql.Types.DATE:
try {
aTime = rset.getTimestamp(a);
} catch(Exception e) {
aTime = null;
}
if(aTime == null) {
cust.getCustParameters().put(aName + "_DAY_DATE", "");
cust.getCustParameters().put(aName + "_MONTH_DATE", "");
cust.getCustParameters().put(aName + "_YEAR_DATE", "");
cust.getCustParameters().put(aName + "_HOUR_DATE", "");
cust.getCustParameters().put(aName + "_MINUTE_DATE", "");
cust.getCustParameters().put(aName + "_SECOND_DATE", "");
cust.getCustParameters().put(aName, "");
} else {
GregorianCalendar aCal = new GregorianCalendar();
aCal.setTime(aTime);
cust.getCustParameters().put(aName + "_DAY_DATE", Integer.toString(aCal.get(GregorianCalendar.DAY_OF_MONTH)));
cust.getCustParameters().put(aName + "_MONTH_DATE", Integer.toString(aCal.get(GregorianCalendar.MONTH) + 1));
cust.getCustParameters().put(aName + "_YEAR_DATE", Integer.toString(aCal.get(GregorianCalendar.YEAR)));
cust.getCustParameters().put(aName + "_HOUR_DATE", Integer.toString(aCal.get(GregorianCalendar.HOUR_OF_DAY)));
cust.getCustParameters().put(aName + "_MINUTE_DATE", Integer.toString(aCal.get(GregorianCalendar.MINUTE)));
cust.getCustParameters().put(aName + "_SECOND_DATE", Integer.toString(aCal.get(GregorianCalendar.SECOND)));
SimpleDateFormat bdfmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cust.getCustParameters().put(aName, bdfmt.format(aCal.getTime()));
}
break;
default:
aValue = rset.getString(a);
if(aValue == null) {
aValue = "";
}
cust.getCustParameters().put(aName, aValue);
break;
}
}
}
rset.close();
stmt.close();
} catch (Exception e) {
logger.error("getCustomerDataFromDb: " + getCust, e);
AgnUtils.sendExceptionMail("sql:" + getCust, e);
}
DataSourceUtils.releaseConnection(con, ds);
cust.setChangeFlag(false);
Map<String, Object> result = cust.getCustParameters();
if (result instanceof CaseInsensitiveMap) {
return (CaseInsensitiveMap<Object>) result;
} else {
return new CaseInsensitiveMap<Object>(result);
}
}
/**
* Delete complete Subscriber-Data from DB. customerID must be set first for this method.
*/
@Override
public void deleteCustomerDataFromDb(int companyID, int customerID) {
String sql = null;
Object[] params = new Object[] { new Integer(customerID) };
try {
JdbcTemplate tmpl = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
sql = "DELETE FROM customer_" + companyID + "_binding_tbl WHERE customer_id=?";
tmpl.update(sql, params);
sql = "DELETE FROM customer_" + companyID + "_tbl WHERE customer_id=?";
tmpl.update(sql, params);
} catch (Exception e) {
logger.error("deleteCustomerDataFromDb: " + sql, e);
AgnUtils.sendExceptionMail("sql:" + sql, e);
}
}
/**
* Loads complete Mailinglist-Binding-Information for given customer-id from Database
*
* @return Map with key/value-pairs as combinations of mailinglist-id and BindingEntry-Objects
*/
@Override
public Map<Integer, Map<Integer, BindingEntry>> loadAllListBindings(int companyID, int customerID) {
Recipient cust = (Recipient) applicationContext.getBean("Recipient");
cust.setListBindings(new Hashtable<Integer, Map<Integer, BindingEntry>> ()); // MailingList_ID as keys
Map<Integer, BindingEntry> mTable = new Hashtable<Integer, BindingEntry>(); // Media_ID as key, contains rest of data (user type, status etc.)
String sqlGetLists = null;
BindingEntry aEntry = null;
int tmpMLID = 0;
try {
sqlGetLists = "SELECT mailinglist_id, user_type, user_status, user_remark, "+AgnUtils.changeDateName()+", mediatype FROM customer_" + companyID + "_binding_tbl WHERE customer_id=" +
customerID + " ORDER BY mailinglist_id, mediatype";
JdbcTemplate tmpl = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
@SuppressWarnings("unchecked")
List<Map<String, Object>> list = tmpl.queryForList(sqlGetLists);
Iterator<Map<String, Object>> i = list.iterator();
while(i.hasNext()) {
Map<String, Object> map = i.next();
int listID = ((Number) map.get("mailinglist_id")).intValue();
Integer mediaType = new Integer(((Number) map.get("mediatype")).intValue());
aEntry = (BindingEntry) applicationContext.getBean("BindingEntry");
aEntry.setCustomerID(customerID);
aEntry.setMailinglistID(listID);
aEntry.setUserType((String) map.get("user_type"));
aEntry.setUserStatus(((Number) map.get("user_status")).intValue());
aEntry.setUserRemark((String) map.get("user_remark"));
aEntry.setChangeDate((java.sql.Timestamp) map.get(AgnUtils.changeDateName()));
aEntry.setMediaType(mediaType.intValue());
if(tmpMLID != listID) {
if(tmpMLID != 0) {
cust.getListBindings().put(tmpMLID, mTable);
mTable = new Hashtable<Integer, BindingEntry>();
mTable.put(mediaType, aEntry);
tmpMLID = listID;
} else {
mTable.put(mediaType, aEntry);
tmpMLID = listID;
}
} else {
mTable.put(mediaType, aEntry);
}
}
cust.getListBindings().put(tmpMLID, mTable);
} catch (Exception e) {
logger.error("loadAllListBindings: " + sqlGetLists, e);
AgnUtils.sendExceptionMail("sql:" + sqlGetLists, e);
return null;
}
return cust.getListBindings();
}
/**
* Checks if E-Mail-Adress given in customerData-HashMap is registered in blacklist(s)
*
* @return true if E-Mail-Adress is blacklisted
*/
@Override
public boolean blacklistCheck(String email, int companyID) {
boolean returnValue = false;
String sqlSelect = null;
try {
JdbcTemplate tmpl = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
sqlSelect = "SELECT email FROM cust_ban_tbl WHERE '" + SafeString.getSQLSafeString(email) + "' LIKE email";
@SuppressWarnings("unchecked")
List<Map<String, Object>> list = tmpl.queryForList(sqlSelect);
if(list.size() > 0) {
returnValue=true;
}
if (AgnUtils.isProjectEMM()) {
sqlSelect = "SELECT email FROM cust"+ companyID + "_ban_tbl WHERE '" + SafeString.getSQLSafeString(email) + "' LIKE email";
@SuppressWarnings("unchecked")
List<Map<String, Object>> list2 = tmpl.queryForList(sqlSelect);
if(list2.size() > 0) {
returnValue = true;
}
}
} catch (Exception e) {
logger.error( "blacklistCheck: " + sqlSelect, e);
AgnUtils.sendExceptionMail("sql:" + sqlSelect, e);
returnValue = true;
}
return returnValue;
}
/*
* Extract an int parameter from CustParameters
*
* @return the int value or the default value in case of an exception
* @param column Column-Name
* @param defaultValue Value to be returned in case of exception
*
* TODO: Method not used. Remove it, when nobody misses it (Support team?)
private int extractInt(String column, int defaultValue, Recipient cust) {
try {
return Integer.parseInt(cust.getCustParameters(column));
} catch (Exception e1) {
return defaultValue;
}
}
*/
@Override
public String getField(String selectVal, int recipientID, int companyID) {
JdbcTemplate jdbc = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
String sql = "SELECT " + selectVal + " value FROM customer_" + companyID + "_tbl cust WHERE cust.customer_id=?";
try {
@SuppressWarnings("unchecked")
List<Map<String, Object>> list = jdbc.queryForList(sql, new Object[]{ new Integer(recipientID)});
if(list.size() > 0) {
Map<String, Object> map = list.get(0);
Object temp = map.get("value");
if(temp != null) {
return temp.toString();
}
}
} catch (Exception e) {
logger.error("processTag: " + sql, e);
AgnUtils.sendExceptionMail("sql:" + sql, e);
return null;
}
return "";
}
@Override
public Map<Integer, Map<Integer, BindingEntry>> getAllMailingLists(int customerID, int companyID) {
Map<Integer, Map<Integer, BindingEntry>> result = new HashMap<Integer, Map<Integer, BindingEntry>>();
String sql = "SELECT mailinglist_id, user_type, user_status, user_remark, " + AgnUtils.changeDateName()+", mediatype FROM customer_" + companyID + "_binding_tbl WHERE customer_id=? ORDER BY mailinglist_id, mediatype";
JdbcTemplate jdbc = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
if( logger.isInfoEnabled()) {
logger.info("getAllMailingLists: " + sql);
}
try {
@SuppressWarnings("unchecked")
List<Map<String, Object>> list = jdbc.queryForList(sql, new Object[]{new Integer(customerID)});
Iterator<Map<String, Object>> i = list.iterator();
BindingEntry entry = null;
while(i.hasNext()) {
Map<String, Object> map = i.next();
int listID = ((Number) map.get("mailinglist_id")).intValue();
int mediaType = ((Number) map.get("mediatype")).intValue();
Map<Integer, BindingEntry> sub = result.get(new Integer(listID));
if(sub == null) {
sub = new HashMap<Integer, BindingEntry>();
}
entry = (BindingEntry) applicationContext.getBean("BindingEntry");
entry.setCustomerID(customerID);
entry.setMailinglistID(listID);
entry.setUserType((String) map.get("user_type"));
entry.setUserStatus(((Number) map.get("user_status")).intValue());
entry.setUserRemark((String) map.get("user_remark"));
entry.setChangeDate((java.sql.Timestamp) map.get(AgnUtils.changeDateName()));
entry.setMediaType(mediaType);
sub.put(new Integer(mediaType), entry);
result.put(new Integer(listID), sub);
}
} catch(Exception e) {
logger.error("getAllMailingLists (customer ID: " + customerID + "sql: " + sql + ")", e);
AgnUtils.sendExceptionMail("sql:" + sql + ", " + customerID, e);
}
return result;
}
@Override
public boolean createImportTables(int companyID, int datasourceID, CustomerImportStatus status) {
JdbcTemplate jdbc = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource"));
String prefix = "cust_" + companyID + "_tmp";
String tabName = prefix+datasourceID+"_tbl";
String keyIdx = prefix+datasourceID+"$KEYCOL$IDX";
String custIdx = prefix+datasourceID+"$CUSTID$IDX";
String sql = null;
try {
sql = "create temporary table " + tabName + " as (select * from customer_" + companyID + "_tbl where 1=0)";
jdbc.execute(sql);
sql = "alter table " + tabName + " modify change_date timestamp null default null";
jdbc.execute(sql);
sql = "alter table " + tabName + " modify creation_date timestamp null default current_timestamp";
jdbc.execute(sql);
sql = "create index " + keyIdx + " on " + tabName + " (" + SafeString.getSQLSafeString(status.getKeycolumn()) + ")";
jdbc.execute(sql);
sql = "create index " + custIdx +" on " + tabName + " (customer_id)";
jdbc.execute(sql);
} catch (Exception e) {
logger.error( "createTemporaryTables: " + sql, e);
e.printStackTrace();
return false;
}
return true;
}
@Override
public boolean deleteImportTables(int companyID, int datasourceID) {
JdbcTemplate jdbc = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource"));
String tabName = "cust_" + companyID + "_tmp" + datasourceID + "_tbl";
if(AgnUtils.isOracleDB()) {
try {
jdbc.execute("drop table "+tabName);
} catch (Exception e) {
logger.error( "deleteTemporarytables (table: " + tabName + ")", e);
e.printStackTrace();
return false;
}
}
return true;
}
/*
* Retrieves new Datasource-ID for newly imported Subscribers
*
* @return new Datasource-ID or 0
*
* TODO: Method not used, remove when nobody misses it (Support team?)
private DatasourceDescription getNewDatasourceDescription(int companyID, String description) {
HibernateTemplate tmpl = new HibernateTemplate((SessionFactory)applicationContext.getBean("sessionFactory"));
DatasourceDescription dsDescription=(DatasourceDescription) applicationContext.getBean("DatasourceDescription");
dsDescription.setId(0);
dsDescription.setCompanyID(companyID);
dsDescription.setSourcegroupID(2);
dsDescription.setCreationDate(new java.util.Date());
dsDescription.setDescription(description);
tmpl.save("DatasourceDescription", dsDescription);
return dsDescription;
}
*/
@Override
public int sumOfRecipients(int companyID, String target) {
int recipients = 0;
String sql = "select count(customer_id) from customer_" + companyID + "_tbl cust where " + target;
try {
JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
recipients = tmpl.queryForInt(sql);
} catch (Exception e) {
recipients = 0;
}
return recipients;
}
@Override
public boolean deleteRecipients(int companyID, String target) {
boolean returnValue = false;
JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
String sql;
sql= "DELETE FROM customer_" + companyID + "_binding_tbl WHERE customer_id in (select customer_id from customer_" + companyID + "_tbl cust where " + target + ")";
try {
tmpl.execute(sql);
} catch (Exception e) {
logger.error("error deleting recipient bindings", e);
returnValue = false;
}
sql = "delete ";
if(AgnUtils.isMySQLDB()) {
sql = sql + "cust ";
}
sql = sql + "from customer_" + companyID + "_tbl cust where " + target;
try {
tmpl.execute(sql);
returnValue = true;
} catch (Exception e) {
logger.error("error deleting recipients", e);
returnValue = false;
}
return returnValue;
}
@Override
public PaginatedList getRecipientList(Set<String> columns, String sqlStatementForCount, String sqlStatementForRows, String sort, String direction,
int page, int rownums, int previousFullListSize) throws IllegalAccessException, InstantiationException {
return getRecipientList(columns, sqlStatementForCount, null, sqlStatementForRows, null, sort, direction, page, rownums, previousFullListSize);
}
@Override
public PaginatedListImpl<DynaBean> getRecipientList(Set<String> columns, String sqlStatementForCount, Object[] parametersForsCount, String sqlStatementForRows, Object[] parametersForsRows, String sort, String direction,
int page, int rownums, int previousFullListSize) throws IllegalAccessException, InstantiationException {
JdbcTemplate aTemplate = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
int totalRows = aTemplate.queryForInt(sqlStatementForCount, parametersForsCount);
if (previousFullListSize == 0 || previousFullListSize != totalRows) {
page = 1;
}
page = AgnUtils.getValidPageNumber(totalRows, page, rownums);
String sortClause = "";
if (!StringUtils.isBlank(sort)) {
sortClause = " ORDER BY " + "lower(" + sort + ")" ;
if (!StringUtils.isEmpty(direction)) {
sortClause = sortClause + " " + direction;
}
}
int offset = (page - 1) * rownums;
if (AgnUtils.isOracleDB()) {
sqlStatementForRows = "SELECT * from ( select " + StringUtils.join(columns, ", ") + ", rownum r from ( " + sqlStatementForRows + " ) where 1 = 1 " + sortClause
+ ") where r between " + (offset + 1) + " and " + (offset + rownums);
} else {
sqlStatementForRows = sqlStatementForRows + sortClause + " LIMIT " + offset + " , " + rownums;
}
@SuppressWarnings("unchecked")
List<Map<String, Object>> tmpList = aTemplate.queryForList(sqlStatementForRows, parametersForsRows);
List<DynaBean> result = new ArrayList<DynaBean>();
if (tmpList != null && !tmpList.isEmpty()) {
DynaProperty[] properties = new DynaProperty[columns.size()];
int i = 0;
for (String c : columns) {
properties[i++] = new DynaProperty(c.toLowerCase(), String.class);
}
BasicDynaClass dynaClass = new BasicDynaClass("recipient", null, properties);
for (Map<String, Object> row : tmpList) {
DynaBean bean = dynaClass.newInstance();
for (String c : columns) {
bean.set(c.toLowerCase(), row.get(c.toUpperCase()) != null ? row.get(c.toUpperCase()).toString() : "");
}
result.add(bean);
}
}
PaginatedListImpl<DynaBean> paginatedList = new PaginatedListImpl<DynaBean>(result, totalRows, rownums, page, sort, direction);
return paginatedList;
}
/*
* TODO: Method is not used. Remove, when nobody misses it (Support team?)
private String getUpperSort(List<String> charColumns, String sort) {
String upperSort = sort;
if (charColumns.contains( sort )) {
upperSort = "upper( " +sort + " )";
}
return upperSort;
}
*/
/**
* Holds value of property applicationContext.
*/
public void deleteAllNoBindings(int companyID, String toBeDeletedTable) {
JdbcTemplate tmpl = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
String delete =
"delete from customer_"+companyID+"_tbl " +
"where customer_id not in (" +
"select customer_id from customer_" + companyID + "_binding_tbl" +
") " +
"and customer_id in (select * from "+toBeDeletedTable+")";
tmpl.update(delete);
tmpl.execute("drop table " + toBeDeletedTable);
}
public String createTmpTableByMailinglistID(int companyID, int mailinglistID) {
String tableName = "tmp_" +String.valueOf(System.currentTimeMillis())+ "_delete_tbl";
JdbcTemplate tmpl = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
String sql =
"create table " + tableName + " as (" +
"select customer_id from customer_"+companyID+"_tbl where customer_id in (" +
"select customer_id from customer_"+companyID+"_binding_tbl where mailinglist_id = " + mailinglistID +
")" +
")";
tmpl.execute(String.format(sql, mailinglistID));
return tableName;
}
public void deleteRecipientsBindings(int mailinglistID, int companyID, boolean activeOnly, boolean notAdminsAndTests) {
JdbcTemplate jdbc = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource"));
String delete = "delete from customer_"+companyID+"_binding_tbl";
String where = "where mailinglist_id = ?";
StringBuffer sql = new StringBuffer(delete).append(" ").append(where);
if(activeOnly){
sql.append(" ").append(String.format("and user_status = %d", BindingEntry.USER_STATUS_ACTIVE));
}
if(notAdminsAndTests){
sql.append(" ").append(String.format("and user_type <> '%s' and user_type <> '%s' and user_type <> '%s'",
BindingEntry.USER_TYPE_ADMIN,
BindingEntry.USER_TYPE_TESTUSER,
BindingEntry.USER_TYPE_TESTVIP));
}
jdbc.update(sql.toString(), new Object[]{new Integer(mailinglistID)});
}
protected ApplicationContext applicationContext;
/**
* Setter for property applicationContext.
* @param applicationContext New value of property applicationContext.
*/
@Override
public void setApplicationContext(ApplicationContext applicationContext) {
this.applicationContext = applicationContext;
}
@Override
public CaseInsensitiveMap<CsvColInfo> readDBColumns(int companyID) {
String sqlGetTblStruct = "SELECT * FROM customer_" + companyID + "_tbl WHERE 1=0";
CsvColInfo aCol = null;
int colType;
CaseInsensitiveMap<CsvColInfo> dbAllColumns = new CaseInsensitiveMap<CsvColInfo>();
DataSource ds = (DataSource) this.applicationContext.getBean("dataSource");
Connection con = DataSourceUtils.getConnection(ds);
try {
Statement stmt = con.createStatement();
ResultSet rset = stmt.executeQuery(sqlGetTblStruct);
ResultSetMetaData meta = rset.getMetaData();
for (int i = 1; i <= meta.getColumnCount(); i++) {
if (!meta.getColumnName(i).equals("change_date")
&& !meta.getColumnName(i).equals("creation_date")
&& !meta.getColumnName(i).equals("datasource_id")) {
// if (meta.getColumnName(i).equals("customer_id")) {
// if (status == null) {
// initStatus(getWebApplicationContext());
// }
// if (!( mode == ImportWizardServiceImpleImpl.MODE_ONLY_UPDATE && status.getKeycolumn().equals("customer_id"))) {
// continue;
// }
// }
aCol = new CsvColInfo();
aCol.setName(meta.getColumnName(i));
aCol.setLength(meta.getColumnDisplaySize(i));
aCol.setType(CsvColInfo.TYPE_UNKNOWN);
aCol.setActive(false);
aCol.setNullable(meta.isNullable(i) != 0);
colType = meta.getColumnType(i);
aCol.setType(dbTypeToCsvType(colType));
dbAllColumns.put(meta.getColumnName(i), aCol);
}
}
rset.close();
stmt.close();
} catch (Exception e) {
logger.error("readDBColumns (companyID: " + companyID + ")", e);
}
DataSourceUtils.releaseConnection(con, ds);
return dbAllColumns;
}
private static int dbTypeToCsvType(int type) {
switch (type) {
case java.sql.Types.BIGINT:
case java.sql.Types.INTEGER:
case java.sql.Types.SMALLINT:
case java.sql.Types.DECIMAL:
case java.sql.Types.DOUBLE:
case java.sql.Types.FLOAT:
case java.sql.Types.NUMERIC:
case java.sql.Types.REAL:
return CsvColInfo.TYPE_NUMERIC;
case java.sql.Types.CHAR:
case java.sql.Types.VARCHAR:
case java.sql.Types.LONGVARCHAR:
case java.sql.Types.CLOB:
return CsvColInfo.TYPE_CHAR;
case java.sql.Types.DATE:
case java.sql.Types.TIMESTAMP:
case java.sql.Types.TIME:
return CsvColInfo.TYPE_DATE;
default:
return CsvColInfo.TYPE_UNKNOWN;
}
}
@Override
public Set<String> loadBlackList(int companyID) throws Exception {
JdbcTemplate jdbcTemplate = new JdbcTemplate( (DataSource) applicationContext.getBean("dataSource"));
SqlRowSet rset = null;
Object[] params = new Object[] { new Integer(companyID) };
Set<String> blacklist = new HashSet<String>();
try {
rset = jdbcTemplate.queryForRowSet("SELECT email FROM cust_ban_tbl WHERE company_id=? OR company_id=0", params);
while (rset.next()) {
blacklist.add(rset.getString(1).toLowerCase());
}
} catch (Exception e) {
logger.error("loadBlacklist (company ID: " + companyID + ")", e);
throw e;
}
return blacklist;
}
@Override
public Map<Integer, String> getAdminAndTestRecipientsDescription(int companyId, int mailingId) {
String sql = "SELECT bind.customer_id, cust.email, cust.firstname, cust.lastname FROM mailing_tbl mail, " +
"customer_" + companyId + "_tbl cust, customer_" + companyId + "_binding_tbl bind WHERE " +
"bind.user_type in ('A', 'T') AND bind.user_status=1 AND bind.mailinglist_id=" +
"mail.mailinglist_id AND bind.customer_id=cust.customer_id and mail.mailing_id=" + mailingId +
" ORDER BY bind.user_type, bind.customer_id";
JdbcTemplate jdbcTemplate = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource"));
@SuppressWarnings("unchecked")
List<Map<String, Object>> tmpList = jdbcTemplate.queryForList(sql);
HashMap<Integer, String> result = new HashMap<Integer, String>();
for (Map<String, Object> map : tmpList) {
int id = ((Number) map.get("customer_id")).intValue();
String email = (String) map.get("email");
String firstName = (String) map.get("firstname");
String lastName = (String) map.get("lastname");
if( firstName == null)
firstName = "";
if( lastName == null)
lastName = "";
result.put(id, firstName + " " + lastName + " <" + email + ">");
}
return result;
}
@Override
public List<Recipient> getBouncedMailingRecipients(int companyId, int mailingId) {
String sqlStatement = "select cust.email as email, cust.firstname as firstname, cust.lastname as lastname, cust.gender as gender " +
"from customer_" + companyId + "_binding_tbl bind, customer_" + companyId + "_tbl cust where bind.customer_id=cust.customer_id and exit_mailing_id = ? and user_status = 2 " +
"and mailinglist_id=(select mailinglist_id from mailing_tbl where mailing_id = ?)";
JdbcTemplate template = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
@SuppressWarnings("unchecked")
List<Map<String, Object>> tmpList = template.queryForList(sqlStatement, new Object [] {mailingId, mailingId});
List<Recipient> result = new ArrayList<Recipient>();
for (Map<String, Object> row : tmpList) {
Recipient newBean = new RecipientImpl();
Map<String, Object> customerData = new HashMap<String, Object>();
customerData.put("gender", row.get("GENDER"));
customerData.put("firstname", row.get("FIRSTNAME"));
customerData.put("lastname", row.get("LASTNAME"));
customerData.put("email", row.get("EMAIL"));
newBean.setCustParameters(customerData);
result.add(newBean);
}
return result;
}
/**
* Gets new customerID from Database-Sequence an stores it in member-variable "customerID"
*
* @return true on success
*/
@Override
public int getNewCustomerID(int companyID) {
String sqlStatement = null;
int customerID = 0;
Dialect dialect = AgnUtils.getHibernateDialect();
if(companyID == 0) {
return customerID;
}
if(mayAdd(companyID, 1) == false) {
return customerID;
}
try {
if(dialect.supportsSequences()) {
JdbcTemplate tmpl = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
sqlStatement = "select customer_" + companyID + "_tbl_seq.nextval FROM dual";
customerID = tmpl.queryForInt(sqlStatement);
} else {
sqlStatement = "insert into customer_" + companyID + "_tbl_seq () values ()";
SqlUpdate updt = new SqlUpdate((DataSource)this.applicationContext.getBean("dataSource"), sqlStatement);
updt.setReturnGeneratedKeys(true);
GeneratedKeyHolder key = new GeneratedKeyHolder();
customerID = updt.update(null, key);
customerID = key.getKey().intValue();
}
} catch (Exception e) {
customerID = 0;
System.err.println("Exception:" + e);
System.err.println(AgnUtils.getStackTrace(e));
}
if( logger.isDebugEnabled()) {
logger.debug("new customerID: "+ customerID);
}
return customerID;
}
@Override
public void deleteRecipients(int companyID, List<Integer> list) {
if (list == null || list.size() < 1) {
throw new RuntimeException("Invalid customerID list size");
}
StringBuilder sb = new StringBuilder("WHERE customer_id in (");
for (Integer customerId : list) {
sb.append(customerId);
sb.append(",");
}
sb.setCharAt(sb.length() - 1, ')');
String where = sb.toString();
sb = new StringBuilder("DELETE FROM customer_");
sb.append(companyID);
sb.append("_binding_tbl ");
sb.append(where);
String bindingQuery = sb.toString();
sb = new StringBuilder("DELETE FROM customer_");
sb.append(companyID);
sb.append("_tbl ");
sb.append(where);
String customerQuery = sb.toString();
JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
tmpl.batchUpdate(new String[] {bindingQuery, customerQuery});
}
@Override
public boolean exist(int customerId, int companyId) {
JdbcTemplate jdbc = new JdbcTemplate((DataSource)this.applicationContext.getBean("dataSource"));
String sql = "select count(*) from customer_" + companyId + "_tbl where customer_id = ?";
return jdbc.queryForInt(sql, new Object[]{ customerId }) > 0;
}
protected String createDateDefaultValueExpression( String defaultValue) {
if( defaultValue.toLowerCase().equals( "now()")) {
return AgnUtils.getSQLCurrentTimestampName();
} else {
if ( AgnUtils.isOracleDB() ) {
return "to_date('" + defaultValue + "', 'DD.MM.YYYY HH24:MI:SS')";
} else {
return "STR_TO_DATE('" + defaultValue + "', '%d-%m-%Y')";
}
}
}
}