/**
* 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;
}
@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 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;
}