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