@Override
protected List<Map<String, Object>> getData(
Map<String, String> parameters, int startRow, int endRow) {
// creation of formats is done here because they are not thread safe
final SimpleDateFormat xmlDateFormat = JsonUtils.createDateFormat();
final SimpleDateFormat xmlDateTimeFormat = JsonUtils.createDateTimeFormat();
final List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
String selectorId = parameters.get(SelectorConstants.DS_REQUEST_SELECTOR_ID_PARAMETER);
if (StringUtils.isEmpty(selectorId)) {
return result;
}
OBContext.setAdminMode();
try {
Selector sel = OBDal.getInstance().get(Selector.class, selectorId);
List<SelectorField> fields =
OBDao.getActiveOBObjectList(sel, Selector.PROPERTY_OBUISELSELECTORFIELDLIST);
// Forcing object initialization to prevent LazyInitializationException in case session is
// cleared when number of records is big enough
Hibernate.initialize(fields);
// Parse the HQL in case that optional filters are required
String HQL = parseOptionalFilters(parameters, sel, xmlDateFormat);
String sortBy = parameters.get("_sortBy");
HQL += getSortClause(sortBy, sel);
Query selQuery = OBDal.getInstance().getSession().createQuery(HQL);
String[] queryAliases = selQuery.getReturnAliases();
if ("true".equals(parameters.get(JsonConstants.NOCOUNT_PARAMETER))) {
int totalRows = 0, queryListSize = 0, clearEachLoop = 100;
// Defaulted to endRow + 2 to check for more records while scrolling.
totalRows = endRow + 2;
ScrollableResults queryResults = selQuery.scroll(ScrollMode.FORWARD_ONLY);
try {
while (queryResults.next()) {
queryListSize++;
if (queryListSize % clearEachLoop == 0) {
OBDal.getInstance().getSession().clear();
}
}
} finally {
queryResults.close();
}
if (startRow < endRow) {
if (queryListSize < endRow) {
totalRows = queryListSize;
}
parameters.put(JsonConstants.RESPONSE_TOTALROWS, String.valueOf(totalRows));
}
}
if (startRow > 0) {
selQuery.setFirstResult(startRow);
}
if (endRow > startRow) {
selQuery.setMaxResults(endRow - startRow + 1);
}
for (Object objResult : selQuery.list()) {
final Map<String, Object> data = new LinkedHashMap<String, Object>();
Object[] resultList = new Object[1];
if (objResult instanceof Object[]) {
resultList = (Object[]) objResult;
} else {
resultList[0] = objResult;
}
for (SelectorField field : fields) {
// TODO: throw an exception if the display expression doesn't match any returned alias.
for (int i = 0; i < queryAliases.length; i++) {
if (queryAliases[i].equals(field.getDisplayColumnAlias())) {
Object value = resultList[i];
if (value instanceof Date) {
value = xmlDateFormat.format(value);
}
if (value instanceof Timestamp) {
value = xmlDateTimeFormat.format(value);
value = JsonUtils.convertToCorrectXSDFormat((String) value);
}
data.put(queryAliases[i], value);
}
}
}
result.add(data);
}
} finally {
OBContext.restorePreviousMode();
}
return result;
}
/**
* 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;
}
/**
* Generates the HQL Sort By Clause to append to the query being executed. If no sort options is
* set on the sortBy parameter the result is ordered by the first shown grid's column.
*
* @param sortBy String of grid's field names concatenated by
* JsonConstants.IN_PARAMETER_SEPARATOR.
* @param sel the selector that it is being displayed.
* @return a String with the HQL Sort By clause.
*/
private String getSortClause(String sortBy, Selector sel) {
StringBuffer sortByClause = new StringBuffer();
boolean sortByDesc = false;
if (sortBy != null && sortBy.startsWith("-")) {
sortByDesc = true;
}
// If grid is manually filtered sortBy is not empty
if (StringUtils.isNotEmpty(sortBy)) {
if (sortBy.contains(JsonConstants.IN_PARAMETER_SEPARATOR)) {
final String[] fieldNames = sortBy.split(JsonConstants.IN_PARAMETER_SEPARATOR);
for (String fieldName : fieldNames) {
if (sortByDesc) {
fieldName = fieldName.substring(1, fieldName.length());
}
int fieldSortIndex = getFieldSortIndex(fieldName, sel);
if (fieldSortIndex > 0) {
if (sortByClause.length() > 0) {
sortByClause.append(", ");
}
if (sortByDesc) {
sortByClause.append(fieldSortIndex + " desc");
} else {
sortByClause.append(fieldSortIndex);
}
}
}
} else {
String fieldName = null;
if (sortByDesc) {
fieldName = sortBy.substring(1, sortBy.length());
} else {
fieldName = sortBy;
}
int fieldSortIndex = getFieldSortIndex(fieldName, sel);
if (fieldSortIndex > 0) {
if (sortByDesc) {
sortByClause.append(fieldSortIndex + " desc");
} else {
sortByClause.append(fieldSortIndex);
}
}
}
}
// If sortByClause is empty set default sort options.
if (sortByClause.length() == 0) {
OBCriteria<SelectorField> selFieldsCrit =
OBDao.getFilteredCriteria(
SelectorField.class,
Restrictions.eq(SelectorField.PROPERTY_OBUISELSELECTOR, sel),
Restrictions.eq(SelectorField.PROPERTY_SHOWINGRID, true));
selFieldsCrit.addOrderBy(SelectorField.PROPERTY_SORTNO, true);
for (SelectorField selField : selFieldsCrit.list()) {
int fieldSortIndex = getFieldSortIndex(selField.getDisplayColumnAlias(), sel);
if (fieldSortIndex > 0) {
sortByClause.append(fieldSortIndex + ", ");
}
}
// Delete last 2 characters: ", "
if (sortByClause.length() > 0) {
sortByClause.delete(sortByClause.length() - 2, sortByClause.length() - 1);
}
}
String result = "";
if (sortByClause.length() > 0) {
result = "\n ORDER BY " + sortByClause.toString();
}
return result;
}
/**
* 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;
}
