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