}
@SuppressWarnings("unchecked")
private void addEntries(StringBuffer sql, DcModule module) {
boolean hasConditions = false;
DcModule entryModule;
List<DataFilterEntry> childEntries = new ArrayList<DataFilterEntry>();
Object value;
int operator;
int counter2;
int counter = 0;
String queryValue = null;
DcField field;
DcModule m = DcModules.get(getModule());
for (DataFilterEntry entry : getEntries()) {
if (!m.isAbstract()) {
entryModule = DcModules.get(entry.getModule());
if (entry.getModule() != getModule()) {
childEntries.add(entry);
continue;
}
} else {
entryModule = module;
}
field = entryModule.getField(entry.getField());
if ( field.isUiOnly() &&
field.getValueType() != DcRepository.ValueTypes._DCOBJECTCOLLECTION &&
field.getValueType() != DcRepository.ValueTypes._PICTURE)
continue;
hasConditions = true;
operator = entry.getOperator().getIndex();
value = entry.getValue() != null ? Utilities.getQueryValue(entry.getValue(), field) : null;
if (value != null) {
queryValue = String.valueOf(value);
if (field.getValueType() == DcRepository.ValueTypes._DATE ||
field.getValueType() == DcRepository.ValueTypes._STRING) {
queryValue = queryValue.replaceAll("\'", "''");
}
}
if (counter > 0) sql.append(entry.isAnd() ? " AND " : " OR ");
if (counter == 0) sql.append(" WHERE ");
boolean useUpper = field.getValueType() == DcRepository.ValueTypes._STRING &&
field.getIndex() != DcObject._ID &&
field.getValueType() != DcRepository.ValueTypes._DCOBJECTREFERENCE &&
field.getValueType() != DcRepository.ValueTypes._DCPARENTREFERENCE &&
field.getValueType() != DcRepository.ValueTypes._DCOBJECTCOLLECTION;
if (field.getValueType() == DcRepository.ValueTypes._STRING) {
if (useUpper) sql.append("UPPER(");
sql.append(field.getDatabaseFieldName());
if (useUpper) sql.append(")");
} else if (field.getValueType() == DcRepository.ValueTypes._DCOBJECTCOLLECTION ||
field.getValueType() == DcRepository.ValueTypes._PICTURE) {
sql.append("ID");
} else {
sql.append(field.getDatabaseFieldName());
}
if (field.getValueType() == DcRepository.ValueTypes._PICTURE) {
if (operator == Operator.IS_EMPTY.getIndex())
sql.append(" NOT");
DcModule picModule = DcModules.get(DcModules._PICTURE);
sql.append(" IN (SELECT OBJECTID FROM " + picModule.getTableName() +
" WHERE " + picModule.getField(Picture._B_FIELD).getDatabaseFieldName() +
" = '" + field.getDatabaseFieldName() + "')");
} else if ( operator == Operator.CONTAINS.getIndex() ||
operator == Operator.DOES_NOT_CONTAIN.getIndex() ||
(operator == Operator.EQUAL_TO.getIndex() && field.getValueType() == DcRepository.ValueTypes._DCOBJECTCOLLECTION) ||
(operator == Operator.NOT_EQUAL_TO.getIndex() && field.getValueType() == DcRepository.ValueTypes._DCOBJECTCOLLECTION)) {
if (field.getValueType() == DcRepository.ValueTypes._DCOBJECTCOLLECTION) {
if (operator == Operator.DOES_NOT_CONTAIN.getIndex() ||
operator == Operator.NOT_EQUAL_TO.getIndex())
sql.append(" NOT");
sql.append(" IN (");
DcModule mapping = DcModules.get(DcModules.getMappingModIdx(entryModule.getIndex(), field.getReferenceIdx(), field.getIndex()));
sql.append("SELECT ");
sql.append(mapping.getField(DcMapping._A_PARENT_ID).getDatabaseFieldName());
sql.append(" FROM ");
sql.append(mapping.getTableName());
sql.append(" WHERE ");
sql.append(mapping.getField(DcMapping._B_REFERENCED_ID).getDatabaseFieldName());
sql.append(" IN (");
if (!(value instanceof Collection)) {
sql.append("'");
sql.append(value);
sql.append("'");
sql.append(")");
} else {
counter2 = 0;
for (Object o : (Collection<DcObject>) value) {
if (counter2 > 0) sql.append(",");
sql.append("'");
if (o instanceof DcObject)
sql.append(((DcObject) o).getID());
else
sql.append(o.toString());
sql.append("'");
counter2++;
}
sql.append(")");
}
sql.append(")");
} else {
if (operator == Operator.DOES_NOT_CONTAIN.getIndex()) sql.append(" NOT");
sql.append(" LIKE ");
if (useUpper) sql.append("UPPER(");
sql.append("'%" + queryValue + "%'");
if (useUpper) sql.append(")");
}
} else if (operator == Operator.ENDS_WITH.getIndex()) {
sql.append(" LIKE ");
if (useUpper) sql.append("UPPER(");
sql.append("'%" + queryValue);
if (useUpper) sql.append(")");
} else if (operator == Operator.EQUAL_TO.getIndex()) {
if (useUpper) {
sql.append(" = UPPER('"+ queryValue +"')");
} else {
sql.append(" = ");
if (value instanceof String) sql.append("'");
sql.append(queryValue);
if (value instanceof String) sql.append("'");
}
} else if (operator == Operator.BEFORE.getIndex() ||
operator == Operator.LESS_THEN.getIndex()) {
sql.append(" < ");
sql.append(queryValue);
} else if (operator == Operator.AFTER.getIndex() ||
operator == Operator.GREATER_THEN.getIndex()) {
sql.append(" > ");
sql.append(queryValue);
} else if (operator == Operator.IS_EMPTY.getIndex()) {
sql.append(" IS NULL");
} else if (operator == Operator.IS_FILLED.getIndex()) {
sql.append(" IS NOT NULL");
} else if (operator == Operator.NOT_EQUAL_TO.getIndex()) {
sql.append(" <> ");
if (useUpper) {
sql.append(" UPPER('"+ queryValue +"')");
} else {
if (value instanceof String) sql.append("'");
sql.append(queryValue);
if (value instanceof String) sql.append("'");
}
} else if (operator == Operator.STARTS_WITH.getIndex()) {
sql.append(" LIKE ");
if (useUpper) sql.append("UPPER(");
sql.append("'%" + queryValue);
if (value instanceof String)
sql.append("'"+ queryValue +"%'");
else
sql.append(queryValue);
if (useUpper) sql.append(")");
} else if (operator == Operator.TODAY.getIndex()) {
sql.append(" = TODAY");
} else if (operator == Operator.DAYS_BEFORE.getIndex()) {
cal.setTime(new Date());
Long days = (Long) entry.getValue();
cal.add(Calendar.DATE, -1 * days.intValue());
sql.append(" = '" + formatter.format(cal.getTime()) + "'");
} else if (operator == Operator.DAYS_AFTER.getIndex()) {
Long days = (Long) entry.getValue();
cal.add(Calendar.DATE, days.intValue());
sql.append(" = '" + formatter.format(cal.getTime()) + "'");
} else if (operator == Operator.MONTHS_AGO.getIndex()) {
Long days = (Long) entry.getValue();
cal.add(Calendar.MONTH, -1 * days.intValue());
cal.set(Calendar.DAY_OF_MONTH, 1);
sql.append(" BETWEEN '" + formatter.format(cal.getTime()) + "'");
cal.set(Calendar.DAY_OF_MONTH, cal.getMaximum(Calendar.DAY_OF_MONTH));
sql.append(" AND '" + formatter.format(cal.getTime()) + "'");
} else if (operator == Operator.YEARS_AGO.getIndex()) {
Long days = (Long) entry.getValue();
cal.add(Calendar.YEAR, -1 * days.intValue());
cal.set(Calendar.MONTH, 1);
cal.set(Calendar.DAY_OF_MONTH, 1);
sql.append(" BETWEEN '" + formatter.format(cal.getTime()) + "'");
cal.set(Calendar.MONTH, 12);
cal.set(Calendar.DAY_OF_MONTH, 31);
sql.append(" AND '" + formatter.format(cal.getTime()) + "'");
}
counter++;
}
if (childEntries.size() > 0) {
DcModule childModule = DcModules.get(childEntries.get(0).getModule());
DataFilter df = new DataFilter(childModule.getIndex());
for (DataFilterEntry entry : childEntries)
df.addEntry(entry);
String subSelect = df.toSQL(new int[] {childModule.getParentReferenceFieldIndex()}, false, false);
if (hasConditions)
sql.append(" AND ID IN (");
else
sql.append(" WHERE ID IN (");