@Override
  public List<OrderProcessModel> findProcessesByActionIds(
      final String processName, final String processCurrentActions[]) {
    final String query =
        "select {bp.PK} "
            + "from {OrderProcess AS bp  JOIN ProcessTask AS pt ON {bp.pk} = {pt.process} } "
            + "WHERE {bp.processDefinitionName} = ?processDefinitionName and {pt.action} in (?processCurrentActions)";

    final FlexibleSearchQuery searchQuery = new FlexibleSearchQuery(query);
    searchQuery.addQueryParameter("processDefinitionName", processName);
    searchQuery.addQueryParameter("processCurrentActions", Arrays.asList(processCurrentActions));
    final SearchResult<OrderProcessModel> processes = flexibleSearchService.search(searchQuery);
    return processes.getResult();
  }
  @Override
  public List<ProductModel> findProductsRelatedToPurchasedProductsByCategory(
      final CategoryModel category,
      final List<ProductReferenceTypeEnum> referenceTypes,
      final UserModel user,
      final boolean excludePurchased,
      final Integer limit) {
    Assert.notNull(category);
    Assert.notNull(user);

    final int maxResultCount = limit == null ? DEFAULT_LIMIT : limit.intValue();

    final Map<String, Object> params = new HashMap<String, Object>();
    final StringBuilder builder = new StringBuilder(REF_QUERY_CATEGORY_START);
    if (excludePurchased) {
      builder.append(REF_QUERY_SUB);
    }
    if (CollectionUtils.isNotEmpty(referenceTypes)) {
      builder.append(REF_QUERY_TYPES);
      params.put(REF_QUERY_PARAM_TYPES, referenceTypes);
    }
    builder.append(REF_QUERY_CATEGORY_ORDER);

    params.put(REF_QUERY_PARAM_USER, user);
    params.put(REF_QUERY_PARAM_CATEGORY, category);

    final FlexibleSearchQuery query = new FlexibleSearchQuery(builder.toString());
    query.addQueryParameters(params);
    query.setNeedTotal(false);
    query.setCount(maxResultCount);

    final SearchResult<ProductModel> result = getFlexibleSearchService().search(query);
    return result.getResult();
  }
 @Override
 public List<OrderModel> findAllOrdersInStatus(final OrderStatus orderStatus) {
   final String query =
       "SELECT {pk} FROM {Order} WHERE  {status} = ?status AND {versionID} IS NULL";
   final FlexibleSearchQuery searchQuery = new FlexibleSearchQuery(query);
   searchQuery.addQueryParameter("status", orderStatus);
   final SearchResult<OrderModel> orderList = flexibleSearchService.search(searchQuery);
   return orderList.getResult();
 }
 /** Find products by a filter. */
 @Override
 public SearchResult<ProductModel> findProductsByAttributeValues(
     final CategoryModel category,
     final Map<ClassAttributeAssignmentModel, Object> attributeValues,
     final int start,
     final int count) {
   final Map<String, Object> params = new HashMap<String, Object>();
   final String query =
       createQueryToFindProductsByAttributeValues(category, attributeValues, params, true);
   final FlexibleSearchQuery flexibleSearchQuery = new FlexibleSearchQuery(query);
   flexibleSearchQuery.addQueryParameters(params);
   flexibleSearchQuery.setCount(count);
   flexibleSearchQuery.setStart(start);
   flexibleSearchQuery.setNeedTotal(true);
   return search(flexibleSearchQuery);
 }
  @Override
  public CreditCardPaymentInfoModel findCreditCartPaymentBySubscription(
      final String subscriptionId) {
    validateParameterNotNull(subscriptionId, "subscriptionId must not be null!");

    final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(PAYMENT_QUERY);
    fQuery.addQueryParameter("subscriptionId", subscriptionId);

    final SearchResult<CreditCardPaymentInfoModel> searchResult =
        getFlexibleSearchService().search(fQuery);
    final List<CreditCardPaymentInfoModel> results = searchResult.getResult();

    if (results != null && results.iterator().hasNext()) {
      return results.iterator().next();
    }

    return null;
  }
  @Override
  public CCPaySubValidationModel findSubscriptionValidationBySubscription(
      final String subscriptionId) {
    validateParameterNotNull(subscriptionId, "subscriptionId must not be null!");

    final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(SUBSCRIPTION_QUERY);
    fQuery.addQueryParameter("subscriptionId", subscriptionId);

    final SearchResult<CCPaySubValidationModel> searchResult =
        getFlexibleSearchService().search(fQuery);
    final List<CCPaySubValidationModel> results = searchResult.getResult();

    if (results != null && results.iterator().hasNext()) {
      return results.iterator().next();
    }

    return null;
  }
  @Override
  public List<ProductModel> findAccessoriesByVendorCompatibility(
      final String manufacturerName,
      final String classificationClassCode,
      final String classificationAttributeCode,
      final String productTypeCode) {
    validateParameterNotNullStandardMessage("classificationClassCode", classificationClassCode);
    validateParameterNotNullStandardMessage(
        "classificationAttributeCode", classificationAttributeCode);
    validateParameterNotNullStandardMessage("manufacturerName", manufacturerName);
    validateParameterNotNullStandardMessage("productTypeCode", productTypeCode);

    final FlexibleSearchQuery query = new FlexibleSearchQuery(FIND_PRODUCTS_BY_VENDORCDOE_QUERY);
    query.addQueryParameter("brand", manufacturerName);
    query.addQueryParameter("classificationClassCode", classificationClassCode);
    query.addQueryParameter("classificationAttributeCode", classificationAttributeCode);
    query.addQueryParameter("itemType", getItemType(productTypeCode));
    return this.getFlexibleSearchService().<ProductModel>search(query).getResult();
  }
  public List<OrderProcessModel> findAllProcessModelsToRepair(
      final String processName, final String endMessage) {
    final String query =
        "select {PK} from {OrderProcess} WHERE {"
            + BusinessProcess.PROCESSDEFINITIONNAME
            + "} = ?processDefinitionName "
            + "and {"
            + BusinessProcess.STATE
            + "} = ?statusValue and {"
            + BusinessProcess.ENDMESSAGE
            + "} = ?processResult";

    final FlexibleSearchQuery searchQuery = new FlexibleSearchQuery(query);
    searchQuery.addQueryParameter("processDefinitionName", processName);
    searchQuery.addQueryParameter("processResult", endMessage);
    searchQuery.addQueryParameter("statusValue", ProcessState.ERROR);
    final SearchResult<OrderProcessModel> processes = flexibleSearchService.search(searchQuery);
    return processes.getResult();
  }
  @Override
  public List<ClassificationAttributeUnitModel> findAttributeUnits(
      final ClassificationSystemVersionModel systemVersion) {
    final Map<String, Object> params = new HashMap<String, Object>();
    params.put("code", systemVersion.getPk());

    final String query =
        "SELECT {"
            + Item.PK
            + "} FROM {"
            + CatalogConstants.TC.CLASSIFICATIONATTRIBUTEUNIT
            + "} "
            + "WHERE {"
            + ClassificationAttributeUnit.SYSTEMVERSION
            + "}= ?code ";

    final FlexibleSearchQuery flexibleSearchQuery = new FlexibleSearchQuery(query);
    flexibleSearchQuery.addQueryParameters(params);
    flexibleSearchQuery.setCount(-1);
    flexibleSearchQuery.setStart(0);
    flexibleSearchQuery.setNeedTotal(true);
    final SearchResult<ClassificationAttributeUnitModel> results = search(flexibleSearchQuery);
    return results.getResult();
  }
  @Override
  public List<PossibleAttributeValue> findPossibleAttributeValues(
      final CategoryModel category,
      final Collection<ClassAttributeAssignmentModel> assignments,
      final Map<ClassAttributeAssignmentModel, Object> filteredAttributeValues) {
    final boolean useToCharForClob = Config.isOracleUsed() || Config.isHanaUsed();
    final Map<String, Object> filterParams = new HashMap<String, Object>();

    // We select the ClassAttributeAssignment, the value of the product feature, the unit
    // and a count of all product feature value. The latter is achieved by adding a group by
    // statement
    // later on
    final StringBuilder stringBuilder =
        new StringBuilder("SELECT {pf.classificationAttributeAssignment},");
    if (useToCharForClob) {
      stringBuilder.append(" to_char({pf.stringValue}),");
    } else {
      stringBuilder.append(" {pf.stringValue},");
    }
    stringBuilder.append(" {pf.unit}, COUNT(*) ");
    stringBuilder.append("FROM {").append(ProductFeatureModel._TYPECODE).append(" AS pf ");
    stringBuilder
        .append("JOIN ")
        .append(ProductModel._TYPECODE)
        .append(" ON {Product.pk} = {pf.product} } ");

    // Restricting the search to the given ClassAttributeAssignments
    stringBuilder.append("WHERE {pf.classificationAttributeAssignment} IN (?assignments) ");

    // Add a subquery that select all products with the given filter
    // This is necessary to only find attribute values contained in the filtered attributes
    final String filterQuery =
        createQueryToFindProductsByAttributeValues(
            category, filteredAttributeValues, filterParams, false);
    stringBuilder.append(" AND {Product.pk} IN ({{").append(filterQuery).append("}}) ");

    // Group the result to count the values
    stringBuilder.append("GROUP BY {pf.classificationAttributeAssignment} ");
    if (useToCharForClob) {
      stringBuilder.append(", to_char({pf.stringValue})");
    } else {
      stringBuilder.append(", {pf.stringValue}");
    }
    stringBuilder.append(", {pf.unit}");

    // Create the actual flexible search query
    final FlexibleSearchQuery query = new FlexibleSearchQuery(stringBuilder.toString());
    query.setResultClassList(Arrays.asList(Item.class, String.class, Item.class, Long.class));
    query.addQueryParameter("assignments", assignments);
    query.addQueryParameters(filterParams);
    final SearchResult<List<Object>> result = search(query);
    final List<PossibleAttributeValue> possibleAttributeValues =
        new ArrayList<PossibleAttributeValue>(result.getCount());
    for (final List<Object> row : result.getResult()) {
      if (row.size() != 4) {
        throw new IllegalStateException("Invalid size of row: " + row.size());
      }
      final ClassAttributeAssignmentModel assignment = (ClassAttributeAssignmentModel) row.get(0);
      final String stringValue = (String) row.get(1);
      final Object value = convertValue(assignment, stringValue);
      final ClassificationAttributeUnitModel unit = (ClassificationAttributeUnitModel) row.get(2);
      final Long count = (Long) row.get(3);
      final PossibleAttributeValue possibleValue =
          new PossibleAttributeValue(assignment, value, unit, count);
      possibleAttributeValues.add(possibleValue);
    }
    return possibleAttributeValues;
  }
  @Override
  public Collection<ZoneDeliveryModeValueModel> findDeliveryValues(
      final DeliveryModeModel mode, final DistrictModel district) {
    final StringBuilder strBdr = new StringBuilder();
    strBdr
        .append("SELECT {dmv.")
        .append(ZoneDeliveryModeValueModel.PK)
        .append("} ")
        .append("FROM {")
        .append(ZoneDeliveryModeValueModel._TYPECODE)
        .append(" AS dmv}, ")
        .append("{")
        .append(ZoneModel._TYPECODE)
        .append(" AS z}, ")
        .append("{")
        .append(ZoneDeliveryModeModel._TYPECODE)
        .append(" AS zdm}, ")
        .append("{")
        .append(CurrencyModel._TYPECODE)
        .append(" AS cur}, ")
        .append("{")
        .append(DistrictModel._TYPECODE)
        .append(" AS d} ")
        .append("WHERE {dmv.")
        .append(ZoneDeliveryModeValueModel.CURRENCY)
        .append("} = {cur.")
        .append(CurrencyModel.PK)
        .append("} ")
        .append("AND {dmv.")
        .append(ZoneDeliveryModeValueModel.DELIVERYMODE)
        .append("} = {zdm.")
        .append(ZoneDeliveryModeModel.PK)
        .append("} ")
        .append("AND {dmv.")
        .append(ZoneDeliveryModeValueModel.ZONE)
        .append("} = {z.")
        .append(ZoneModel.PK)
        .append("} ")
        .append("AND {z.")
        .append(ZoneModel.DISTRICT)
        .append("} = {d.")
        .append(DistrictModel.PK)
        .append("} ")
        .append("AND {cur.")
        .append(CurrencyModel.ISOCODE)
        .append("} = 'CNY' ")
        .append("AND {d.")
        .append(DistrictModel.PK)
        .append("} = ?district ")
        .append("AND {zdm.")
        .append(ZoneDeliveryModeModel.PK)
        .append("} = ?mode ")
        .append("AND {z.")
        .append(ZoneModel.REGION)
        .append("} IS NULL ")
        .append("AND {z.")
        .append(ZoneModel.CITY)
        .append("} IS NULL");

    final HashMap<String, Object> params = new HashMap<>();
    params.put("mode", mode);
    params.put("district", district);

    final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(strBdr.toString());
    fQuery.addQueryParameters(params);
    fQuery.setResultClassList(Collections.singletonList(ZoneDeliveryModeValueModel.class));
    final SearchResult<ZoneDeliveryModeValueModel> result =
        getFlexibleSearchService().search(fQuery);
    final Collection<ZoneDeliveryModeValueModel> vs1 = result.getResult();

    if (CollectionUtils.isNotEmpty(vs1)) {
      return vs1;
    } else {
      final CityModel city = district.getCity();
      final Collection<ZoneDeliveryModeValueModel> vs2 = findDeliveryValues(mode, city);

      if (CollectionUtils.isNotEmpty(vs2)) {
        return vs2;
      } else {
        final RegionModel region = city.getRegion();
        final Collection<ZoneDeliveryModeValueModel> vs3 = findDeliveryValues(mode, region);

        if (CollectionUtils.isNotEmpty(vs3)) {
          return vs3;
        } else {
          return findDeliveryValues(mode);
        }
      }
    }
  }
  /**
   * Finds all delivery cost values for a given city in CNY for the specified delivery mode
   *
   * @param mode the delivery mode
   * @param city the city
   * @return all found {@link de.hybris.platform.deliveryzone.model.ZoneDeliveryModeValueModel}s, or
   *     empty list if not found.
   */
  protected Collection<ZoneDeliveryModeValueModel> findDeliveryValues(
      final DeliveryModeModel mode, final CityModel city) {
    final StringBuilder strBdr = new StringBuilder();
    strBdr
        .append("SELECT {dmv.")
        .append(ZoneDeliveryModeValueModel.PK)
        .append("} ")
        .append("FROM {")
        .append(ZoneDeliveryModeValueModel._TYPECODE)
        .append(" AS dmv}, ")
        .append("{")
        .append(ZoneModel._TYPECODE)
        .append(" AS z}, ")
        .append("{")
        .append(ZoneDeliveryModeModel._TYPECODE)
        .append(" AS zdm}, ")
        .append("{")
        .append(CurrencyModel._TYPECODE)
        .append(" AS cur}, ")
        .append("{")
        .append(CityModel._TYPECODE)
        .append(" AS c} ")
        .append("WHERE {dmv.")
        .append(ZoneDeliveryModeValueModel.CURRENCY)
        .append("} = {cur.")
        .append(CurrencyModel.PK)
        .append("} ")
        .append("AND {dmv.")
        .append(ZoneDeliveryModeValueModel.DELIVERYMODE)
        .append("} = {zdm.")
        .append(ZoneDeliveryModeModel.PK)
        .append("} ")
        .append("AND {dmv.")
        .append(ZoneDeliveryModeValueModel.ZONE)
        .append("} = {z.")
        .append(ZoneModel.PK)
        .append("} ")
        .append("AND {z.")
        .append(ZoneModel.CITY)
        .append("} = {c.")
        .append(CityModel.PK)
        .append("} ")
        .append("AND {cur.")
        .append(CurrencyModel.ISOCODE)
        .append("} = 'CNY' ")
        .append("AND {c.")
        .append(CityModel.PK)
        .append("} = ?city ")
        .append("AND {zdm.")
        .append(ZoneDeliveryModeModel.PK)
        .append("} = ?mode ")
        .append("AND {z.")
        .append(ZoneModel.REGION)
        .append("} IS NULL ")
        .append("AND {z.")
        .append(ZoneModel.DISTRICT)
        .append("} IS NULL");

    final HashMap<String, Object> params = new HashMap<>();
    params.put("mode", mode);
    params.put("city", city);

    final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(strBdr.toString());
    fQuery.addQueryParameters(params);
    fQuery.setResultClassList(Collections.singletonList(ZoneDeliveryModeValueModel.class));
    final SearchResult<ZoneDeliveryModeValueModel> result =
        getFlexibleSearchService().search(fQuery);
    final Collection<ZoneDeliveryModeValueModel> values = result.getResult();
    return values;
  }
  /**
   * Finds all delivery cost values for China in CNY for the specified delivery mode
   *
   * @param mode the delivery mode
   * @return all found {@link de.hybris.platform.deliveryzone.model.ZoneDeliveryModeValueModel}s, or
   *     empty list if not found.
   */
  protected Collection<ZoneDeliveryModeValueModel> findDeliveryValues(
      final DeliveryModeModel mode) {
    final StringBuilder strBdr = new StringBuilder();
    strBdr
        .append("SELECT {dmv.")
        .append(ZoneDeliveryModeValueModel.PK)
        .append("} ")
        .append("FROM {")
        .append(ZoneDeliveryModeValueModel._TYPECODE)
        .append(" AS dmv JOIN ")
        .append(ZoneDeliveryModeConstants.Relations.ZONECOUNTRYRELATION)
        .append(" z2cRel ON {dmv.")
        .append(ZoneDeliveryModeValueModel.ZONE)
        .append("} = {z2cRel.")
        .append(Link.SOURCE)
        .append("}}, ")
        .append("{")
        .append(ZoneDeliveryModeModel._TYPECODE)
        .append(" AS zdm}, ")
        .append("{")
        .append(CurrencyModel._TYPECODE)
        .append(" AS cur}, ")
        .append("{")
        .append(CountryModel._TYPECODE)
        .append(" AS c} ")
        .append("WHERE {dmv.")
        .append(ZoneDeliveryModeValueModel.CURRENCY)
        .append("} = {cur.")
        .append(CurrencyModel.PK)
        .append("} ")
        .append("AND {dmv.")
        .append(ZoneDeliveryModeValueModel.DELIVERYMODE)
        .append("} = {zdm.")
        .append(ZoneDeliveryModeModel.PK)
        .append("} ")
        .append("AND {z2cRel.")
        .append(Link.TARGET)
        .append("} = {c.")
        .append(CountryModel.PK)
        .append("} ")
        .append("AND {cur.")
        .append(CurrencyModel.ISOCODE)
        .append("} = 'CNY' ")
        .append("AND {c.")
        .append(CountryModel.ISOCODE)
        .append("} = 'CN' ")
        .append("AND {zdm.")
        .append(ZoneDeliveryModeModel.PK)
        .append("} = ?mode ");

    final HashMap<String, Object> params = new HashMap<>();
    params.put("mode", mode);

    final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(strBdr.toString());
    fQuery.addQueryParameters(params);
    fQuery.setResultClassList(Collections.singletonList(ZoneDeliveryModeValueModel.class));
    final SearchResult<ZoneDeliveryModeValueModel> result =
        getFlexibleSearchService().search(fQuery);
    final Collection<ZoneDeliveryModeValueModel> values = result.getResult();
    return values;
  }