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