/**
* Returns the where clause of a selector's field based on the given value.
*
* <p>This method based on the DomainType of the selector field returns the filter clause using
* the clause left part defined on the selector field.
*
* <ul>
* <li>Numeric Domain Type: Returns an equals clause <i>field.clauseLeftPart = value</i>
* <li>Date Domain Type: Returns a multiple clause comparing separately value's day, month and
* year.
* <li>Boolean Domain Type: Returns an equals clause <i>field.clauseLeftPart = value</i>
* <li>Foreign Key Domain Type: Returns an equals clause <i>field.clauseLeftPart.id = value</i>
* <li>Unique Id Domain Type: Returns an equals clause <i>field.clauseLeftPart = value</i>
* <li>String Domain Type: Compares the clause left part with the value using the lower database
* function which to make comparison case insensitive.
* </ul>
*
* @param value String with the value that the selector field's column is filtered by.
* @param field The SelectorField that is filtered.
* @param xmlDateFormat SimpleDateFormat to parse the value in case the field is a Date field.
* @param operatorvalue
* @return a String with the HQL where clause to filter the field by the given value.
*/
private String getWhereClause(
String operator,
String value,
SelectorField field,
SimpleDateFormat xmlDateFormat,
String[] operatorvalue) {
String whereClause = "";
if (operator != null && operator.equals(AdvancedQueryBuilder.EXISTS_QUERY_KEY)) {
String val = "";
for (int i = 1; i < operatorvalue.length; i++) {
val += i > 1 ? " and " : "";
val += operatorvalue[i];
}
return val;
}
DomainType domainType =
ModelProvider.getInstance().getReference(field.getReference().getId()).getDomainType();
if (domainType.getClass().getSuperclass().equals(BigDecimalDomainType.class)
|| domainType.getClass().equals(LongDomainType.class)) {
whereClause = field.getClauseLeftPart() + " = " + value;
} else if (domainType.getClass().equals(DateDomainType.class)) {
try {
final Calendar cal = Calendar.getInstance();
cal.setTime(xmlDateFormat.parse(value));
whereClause = " (day(" + field.getClauseLeftPart() + ") = " + cal.get(Calendar.DATE);
whereClause +=
"\n and month(" + field.getClauseLeftPart() + ") = " + (cal.get(Calendar.MONTH) + 1);
whereClause +=
"\n and year(" + field.getClauseLeftPart() + ") = " + cal.get(Calendar.YEAR) + ") ";
} catch (Exception e) {
// ignore these errors, just don't filter then
// add a dummy whereclause to make the query format correct
whereClause = "1 = 1";
}
} else if (domainType instanceof BooleanDomainType) {
whereClause = field.getClauseLeftPart() + " = " + value;
} else if (domainType instanceof UniqueIdDomainType) {
whereClause = field.getClauseLeftPart() + " = '" + value + "'";
} else if (domainType instanceof ForeignKeyDomainType) {
// Assume left part definition is full object reference from HQL select
whereClause = field.getClauseLeftPart() + ".id = '" + value + "'";
} else if (domainType instanceof StringEnumerateDomainType) {
// For enumerations value can be in two formats:
// 1- VAL: in this case the expression should be property='VAL'
// 2- ["VAL1", "VAL2"] (JSONArray): the expression should be property in ('VAL1', 'VAL2')
JSONArray values = null;
if (value.startsWith("[")) {
try {
values = new JSONArray(value);
} catch (JSONException ignore) {
// It is not a JSONArray: assuming format 1
}
}
if (values == null) {
// format 1
whereClause = field.getClauseLeftPart() + " = '" + value + "'";
} else {
// format 2
whereClause = field.getClauseLeftPart() + " IN (";
for (int i = 0; i < values.length(); i++) {
if (i > 0) {
whereClause += ", ";
}
try {
whereClause += "'" + values.getString(i) + "'";
} catch (JSONException e) {
log.error("Error parsing values as JSONArray:" + value, e);
}
}
whereClause += ")";
}
} else {
if ("iStartsWith".equals(operator)) {
whereClause =
"lower("
+ field.getClauseLeftPart()
+ ") LIKE '"
+ value.toLowerCase().replaceAll(" ", "%")
+ "%'";
} else {
whereClause =
"lower("
+ field.getClauseLeftPart()
+ ") LIKE '%"
+ value.toLowerCase().replaceAll(" ", "%")
+ "%'";
}
}
return whereClause;
}
/**
* Returns the selectors HQL query. In case that it contains the '@additional_filters@' String it
* is replaced by a set of filter clauses.
*
* <p>These include a filter clause:
*
* <ul>
* <li>for the main entity's client by the context's client.
* <li>for the main entity's organization by an organization list see {@link #getOrgs(String)}
* <li>with Selector's default filter expression.
* <li>for each default expression defined on the selector fields.
* <li>for each selector field in case exists a value for it on the parameters param.
* </ul>
*
* @param parameters Map of String values with the request parameters.
* @param sel the selector that it is being retrieved the data.
* @param xmlDateFormat SimpleDataFormat to be used to parse date Strings.
* @return a String with the HQL to be executed.
*/
private String parseOptionalFilters(
Map<String, String> parameters, Selector sel, SimpleDateFormat xmlDateFormat) {
String HQL = sel.getHQL();
if (!HQL.contains(ADDITIONAL_FILTERS)) {
return HQL;
}
final String requestType = parameters.get(SelectorConstants.DS_REQUEST_TYPE_PARAMETER);
StringBuffer additionalFilter = new StringBuffer();
final String entityAlias = sel.getEntityAlias();
// Client filter
additionalFilter
.append(entityAlias + ".client.id in ('0', '")
.append(OBContext.getOBContext().getCurrentClient().getId())
.append("')");
// Organization filter
final String orgs = DataSourceUtils.getOrgs(parameters.get(JsonConstants.ORG_PARAMETER));
if (StringUtils.isNotEmpty(orgs)) {
additionalFilter.append(NEW_FILTER_CLAUSE);
additionalFilter.append(
entityAlias
+ (sel.getTable().getName().equals("Organization")
? ".id in (" + orgs + ")"
: ".organization in (" + orgs + ")"));
}
additionalFilter.append(getDefaultFilterExpression(sel, parameters));
StringBuffer defaultExpressionsFilter = new StringBuffer();
boolean hasFilter = false;
List<SelectorField> fields =
OBDao.getActiveOBObjectList(sel, Selector.PROPERTY_OBUISELSELECTORFIELDLIST);
HashMap<String, String[]> criteria = getCriteria(parameters);
for (SelectorField field : fields) {
if (StringUtils.isEmpty(field.getClauseLeftPart())) {
continue;
}
String operator = null;
String value = null;
String[] operatorvalue = null;
if (criteria != null) {
operatorvalue = criteria.get(field.getDisplayColumnAlias());
if (operatorvalue != null) {
operator = operatorvalue[0];
value = operatorvalue[1];
}
}
if (StringUtils.isEmpty(value)) {
value = parameters.get(field.getDisplayColumnAlias());
}
// Add field default expression on picklist if it is not already filtered. Default expressions
// on selector popup are already evaluated and their values came in the parameters object.
if (field.getDefaultExpression() != null
&& !"Window".equals(requestType)
&& StringUtils.isEmpty(value)) {
try {
String defaultValue = "";
Object defaultValueObject =
ParameterUtils.getJSExpressionResult(
parameters, RequestContext.get().getSession(), field.getDefaultExpression());
if (defaultValueObject != null) {
defaultValue = defaultValueObject.toString();
}
if (StringUtils.isNotEmpty(defaultValue)) {
defaultExpressionsFilter.append(NEW_FILTER_CLAUSE);
defaultExpressionsFilter.append(
getWhereClause(operator, defaultValue, field, xmlDateFormat, operatorvalue));
}
} catch (Exception e) {
log.error("Error evaluating filter expression: " + e.getMessage(), e);
}
}
if (field.isFilterable() && StringUtils.isNotEmpty(value)) {
String whereClause = getWhereClause(operator, value, field, xmlDateFormat, operatorvalue);
if (!hasFilter) {
additionalFilter.append(NEW_FILTER_CLAUSE);
additionalFilter.append(" (");
hasFilter = true;
} else {
if ("Window".equals(requestType)) {
additionalFilter.append(NEW_FILTER_CLAUSE);
} else {
additionalFilter.append(NEW_OR_FILTER_CLAUSE);
}
}
additionalFilter.append(whereClause);
}
}
if (hasFilter) {
additionalFilter.append(")");
}
if (defaultExpressionsFilter.length() > 0) {
additionalFilter.append(defaultExpressionsFilter);
}
HQL = HQL.replace(ADDITIONAL_FILTERS, additionalFilter.toString());
return HQL;
}