/**
* Translates the filter criteria ({@link #addFilterParameter(String, String)}) to a valid HQL
* where clause (without the 'where' keyword). After calling this method the method {@link
* #getNamedParameters()} can be called. Note that currently only filtering on string and boolean
* properties is supported. Also filtering on the identifier of a referenced business object is
* supported.
*
* @return a valid where clause or an empty string if not set.
*/
public String getWhereClause() {
if (whereClause != null) {
return whereClause;
}
// add some default filter parameters
filterParameters.put(
JsonConstants.QUERY_PARAM_USER, OBContext.getOBContext().getUser().getId());
if (!filterParameters.containsKey(JsonConstants.QUERY_PARAM_CLIENT)) {
filterParameters.put(
JsonConstants.QUERY_PARAM_CLIENT, OBContext.getOBContext().getUser().getId());
}
final SimpleDateFormat simpleDateFormat = JsonUtils.createDateFormat();
Check.isNotNull(entity, "Entity must be set");
final StringBuilder sb = new StringBuilder();
boolean addAnd = false;
final StringBuilder orgPart = new StringBuilder();
final List<Property> propertyDone = new ArrayList<Property>();
String whereParameterValue = null;
for (String key : filterParameters.keySet()) {
String value = filterParameters.get(key);
if (key.equals(JsonConstants.WHERE_PARAMETER)) {
// there are cases where null is set as a string
// handle this
if (value.equals("null") || value.length() == 0) {
continue;
}
whereParameterValue = value;
continue;
}
// handle the case that we should filter on the accessible organizations
if (key.equals(JsonConstants.ORG_PARAMETER)) {
if (entity.isOrganizationEnabled() && value != null && value.length() > 0) {
final Set<String> orgs =
OBContext.getOBContext().getOrganizationStructureProvider().getNaturalTree(value);
if (orgs.size() > 0) {
if (getMainAlias() != null) {
orgPart.append(" " + getMainAlias() + ".organization in (");
} else {
orgPart.append(" organization in (");
}
boolean addComma = false;
for (String org : orgs) {
if (addComma) {
orgPart.append(",");
}
orgPart.append("'" + org + "'");
addComma = true;
}
orgPart.append(") ");
}
}
continue;
}
// determine the property
final List<Property> properties = JsonUtils.getPropertiesOnPath(getEntity(), key);
if (properties.isEmpty()) {
continue;
}
final Property property = properties.get(properties.size() - 1);
// invalid propname, ignore this one
// TODO: possibly warn about it
if (property == null || propertyDone.contains(property)) {
continue;
}
propertyDone.add(property);
// we know the property and the string representation of the value...
// do the conversion
if (addAnd) {
if (doOr) {
sb.append(" or ");
} else {
sb.append(" and ");
}
}
String leftWherePart = null;
if (isDoOr()) {
leftWherePart = resolveJoins(properties, key);
} else if (getMainAlias() != null) {
leftWherePart = getMainAlias() + "." + key.trim();
} else {
leftWherePart = key;
}
// get rid of the identifier and replace it with the real property name
// or with the concatenation if there are multiple parts
// NOTE: the if and else check against the key variable and not the leftwherepart
// because the key contains the original string (with the _identifier part).
// Within the if the leftWherePart is used because it contains the join aliases
if (key.equals(JsonConstants.IDENTIFIER) || key.endsWith("." + JsonConstants.IDENTIFIER)) {
// the identifierProperties are read from the owning entity of the
// property, that should work fine, as this last property is always part of the
// identifier
final List<Property> identifierProperties = property.getEntity().getIdentifierProperties();
Check.isTrue(
identifierProperties.contains(property),
"Property " + property + " not part of identifier of " + property.getEntity());
final String prefix;
final int index = leftWherePart.lastIndexOf(".");
if (key.equals(JsonConstants.IDENTIFIER)) {
prefix = getMainAlias() + ".";
} else if (index == -1) {
prefix = "";
} else {
// the + 1 makes sure that the dot is included
prefix = leftWherePart.substring(0, index + 1);
}
leftWherePart = createIdentifierLeftClause(identifierProperties, prefix);
// if the value consists of multiple parts then filtering won't work
// only search on the first part then, is pragmatic but very workable
if (value != null && value.contains(IdentifierProvider.SEPARATOR)) {
final int separatorIndex = value.indexOf(IdentifierProvider.SEPARATOR);
value = value.substring(0, separatorIndex);
}
}
// NOTE: If you change this part, make sure that you sync the changes with the
// SelectorDataSourceFilter. Check issue https://issues.openbravo.com/view.php?id=14239
// NOTE the typedParameters.add call must be done after the call to
// getTypedParameterAlias, this to get the correct alias codes
if (key.equals(JsonConstants.IDENTIFIER)) {
if (textMatching == TextMatching.exact) {
sb.append(leftWherePart + " = " + getTypedParameterAlias());
typedParameters.add(value);
} else if (textMatching == TextMatching.startsWith) {
sb.append(
"upper("
+ leftWherePart
+ ") like "
+ getTypedParameterAlias()
+ " escape '"
+ ESCAPE_CHAR
+ "' ");
typedParameters.add(escapeLike(value.toUpperCase()) + "%");
} else {
sb.append(
"upper("
+ leftWherePart
+ ") like "
+ getTypedParameterAlias()
+ " escape '"
+ ESCAPE_CHAR
+ "' ");
typedParameters.add("%" + escapeLike(value.toUpperCase()).replaceAll(" ", "%") + "%");
}
} else if (!property.isPrimitive()) {
// an in parameter use it...
if (value.contains(JsonConstants.IN_PARAMETER_SEPARATOR)) {
final List<String> values = new ArrayList<String>();
final String[] separatedValues = value.split(JsonConstants.IN_PARAMETER_SEPARATOR);
for (String separatedValue : separatedValues) {
values.add(separatedValue);
}
sb.append(leftWherePart + ".id in (" + getTypedParameterAlias() + ")");
typedParameters.add(values);
} else {
sb.append(leftWherePart + ".id = " + getTypedParameterAlias());
typedParameters.add(value);
}
} else if (String.class == property.getPrimitiveObjectType()) {
if (textMatching == TextMatching.exact) {
sb.append(leftWherePart + " = " + getTypedParameterAlias());
typedParameters.add(value);
} else if (textMatching == TextMatching.startsWith) {
sb.append(
"upper("
+ leftWherePart
+ ") like "
+ getTypedParameterAlias()
+ " escape '"
+ ESCAPE_CHAR
+ "' ");
typedParameters.add(escapeLike(value.toUpperCase()) + "%");
} else {
sb.append(
"upper("
+ leftWherePart
+ ") like "
+ getTypedParameterAlias()
+ " escape '"
+ ESCAPE_CHAR
+ "' ");
typedParameters.add("%" + escapeLike(value.toUpperCase()).replaceAll(" ", "%") + "%");
}
} else if (Boolean.class == property.getPrimitiveObjectType()) {
final String alias = getTypedParameterAlias();
typedParameters.add(new Boolean(value));
sb.append(leftWherePart + " = " + alias);
} else if (property.isNumericType()) {
try {
final String alias = getTypedParameterAlias();
final BigDecimal bdValue = new BigDecimal(value);
if (Long.class == property.getPrimitiveObjectType()) {
typedParameters.add(bdValue.longValue());
} else if (Integer.class == property.getPrimitiveObjectType()) {
typedParameters.add(bdValue.intValue());
} else {
typedParameters.add(bdValue);
}
sb.append(leftWherePart + " = " + alias);
} catch (NumberFormatException e) {
// ignore on purpose, incorrect value entered by user
// add a dummy whereclause to make the query format correct
sb.append(" 1=1 ");
}
} else if (Date.class.isAssignableFrom(property.getPrimitiveObjectType())) {
try {
final Calendar cal = Calendar.getInstance();
cal.setTime(simpleDateFormat.parse(value));
final String alias1 = getTypedParameterAlias();
typedParameters.add(cal.get(Calendar.DATE));
final String alias2 = getTypedParameterAlias();
typedParameters.add(cal.get(Calendar.MONTH) + 1);
final String alias3 = getTypedParameterAlias();
typedParameters.add(cal.get(Calendar.YEAR));
sb.append(
" (day("
+ leftWherePart
+ ") = "
+ alias1
+ " and month("
+ leftWherePart
+ ") = "
+ alias2
+ " and year("
+ leftWherePart
+ ") = "
+ alias3
+ ") ");
} catch (Exception e) {
// ignore these errors, just don't filter then
// add a dummy whereclause to make the query format correct
sb.append(" 1=1 ");
}
// } else if (property.isDate() || property.isDatetime()) {
// NOTE: dates arrive in the format of the user....
// sb.append(leftWherePart + " = ?");
// typedParameters.add(value);
} else {
// TODO: support this....
throw new UnsupportedOperationException(
"Type "
+ property.getPrimitiveObjectType()
+ " not yet supported for parameter "
+ key);
}
addAnd = true;
}
log.debug("Whereclause for entity " + entity.getName());
log.debug(sb.toString());
for (Object param : typedParameters) {
log.debug(param);
}
log.debug("Textmatching " + textMatching);
if (sb.length() == 0) {
whereClause = orgPart.length() > 0 ? orgPart.toString() : "";
} else {
whereClause =
"(" + sb.toString() + ")" + (orgPart.length() > 0 ? " and " + orgPart.toString() : "");
}
if (whereParameterValue != null) {
if (whereClause.length() > 0) {
whereClause = " (" + whereClause + ") and (" + whereParameterValue + ") ";
} else {
whereClause = " " + whereParameterValue;
}
}
if (whereClause.trim().length() > 0) {
whereClause = " where " + whereClause;
}
// handle special transactional range parameter
if (whereClause.contains(JsonConstants.QUERY_PARAM_TRANSACTIONAL_RANGE)) {
final String alias = getTypedParameterAlias();
String windowId = RequestContext.get().getRequestParameter("windowId");
if (windowId == null) {
windowId = "";
}
final String range =
Utility.getTransactionalDate(
new DalConnectionProvider(false),
RequestContext.get().getVariablesSecureApp(),
windowId);
final int rangeNum = Integer.parseInt(range);
final Calendar cal = Calendar.getInstance();
cal.add(Calendar.DAY_OF_MONTH, -1 * rangeNum);
whereClause = whereClause.replace(JsonConstants.QUERY_PARAM_TRANSACTIONAL_RANGE, alias);
typedParameters.add(cal.getTime());
}
if (whereClause.contains(JsonConstants.QUERY_PARAM_CLIENT)) {
final String alias = getTypedParameterAlias();
String clientId = (String) DalUtil.getId(OBContext.getOBContext().getCurrentClient());
whereClause = whereClause.replace(JsonConstants.QUERY_PARAM_CLIENT, alias);
typedParameters.add(clientId);
}
whereClause = setRequestParameters(whereClause);
whereClause = substituteContextParameters(whereClause);
return whereClause;
}
protected String getOrderByClausePart(String orderByParam) {
String localOrderBy = orderByParam;
final boolean asc = !localOrderBy.startsWith("-");
String direction = "";
if (!asc) {
localOrderBy = localOrderBy.substring(1);
direction = " desc ";
}
final List<String> paths = new ArrayList<String>();
// handle the following case:
// table.window.identifier as the sort string
boolean isIdenfitier =
localOrderBy.equals(JsonConstants.IDENTIFIER)
|| localOrderBy.endsWith("." + JsonConstants.IDENTIFIER);
if (isIdenfitier) {
Entity searchEntity = getEntity();
// a path to an entity, find the last entity
final String prefix;
if (!localOrderBy.equals(JsonConstants.IDENTIFIER)) {
// be lazy get the last property, it belongs to the last entity
final Property prop = DalUtil.getPropertyFromPath(searchEntity, localOrderBy);
Check.isNotNull(
prop, "Property path " + localOrderBy + " is not valid for entity " + searchEntity);
searchEntity = prop.getEntity();
prefix = localOrderBy.substring(0, localOrderBy.lastIndexOf(".") + 1);
} else {
prefix = "";
}
for (Property prop : searchEntity.getIdentifierProperties()) {
if (prop.isOneToMany()) {
// not supported ignoring it
continue;
}
if (!prop.isPrimitive()) {
// get identifier properties from target entity
// TODO: currently only supports one level, recursive
// calls have the danger of infinite loops in case of
// wrong identifier definitions in the AD
final Entity targetEntity = prop.getTargetEntity();
for (Property targetEntityProperty : targetEntity.getIdentifierProperties()) {
paths.add(prefix + prop.getName() + "." + targetEntityProperty.getName());
}
} else {
paths.add(prefix + prop.getName());
}
}
} else {
paths.add(localOrderBy);
}
final StringBuilder sb = new StringBuilder();
boolean addComma = false;
for (String path : paths) {
if (addComma) {
sb.append(", ");
}
addComma = true;
final String resolvedPath =
resolveJoins(JsonUtils.getPropertiesOnPath(getEntity(), path), path);
sb.append(resolvedPath);
sb.append(direction);
}
return sb.toString();
}