ProductRepositoryImpl.java
package com.tradecloud.repository.impl;
import com.tradecloud.domain.infrastructure.persistence.CriteriaBuilder;
import com.tradecloud.domain.integration.IntegratedMD5;
import com.tradecloud.domain.item.Product;
import com.tradecloud.domain.model.ordermanagement.ProductState;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.domain.place.Country;
import com.tradecloud.domain.product.DuplicateProductException;
import com.tradecloud.domain.supplier.OrganisationalUnitSupplier;
import com.tradecloud.dto.product.ProductResult;
import com.tradecloud.dto.product.ProductSearch;
import com.tradecloud.repository.ProductRepository;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.base.impl.CriteriaValue;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.Access;
import javax.persistence.AccessType;
import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
import javax.xml.bind.annotation.XmlRootElement;
import java.math.BigInteger;
import java.util.*;
import java.util.stream.Collectors;
import static com.tradecloud.dto.product.ProductSearch.BOTH_IMPORT_AND_EXPORT;
/**
* Default implementation of the {@code ProductRepository} interface.
*/
@Repository(value = "productRepository")
@Entity
@Table(name = "integratedMD5")
@Access(AccessType.FIELD)
@XmlAccessorType(XmlAccessType.FIELD)
@XmlRootElement(name = "IntegratedMD5")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class ProductRepositoryImpl extends RepositoryBaseImpl<Product, ProductSearch> implements ProductRepository {
private static final long serialVersionUID = 1L;
private static final Logger log = Logger.getLogger(ProductRepositoryImpl.class);
/**
* Use constants for the search field names to avoid any typos etc when
* creating queries. All methods that use these fields
* <strong>should</strong> use the appropriate constant.
*/
private static final String CODE = "code";
private static final String ID = "id";
private static final String PRODUCTCODE = "code_key";
private static final String DESCRIPTION = "description";
private static final String SUPPLIER_REFERENCE = "supplierReference";
private static final String SUPPLIER = "supplier";
private static final String ORGUNITSUPPLIER = "supplier";
private static final String ORGUNITSUPPLIER_NAME = "supplier.supplier.name";
private static final String ORGANISATIONAL_UNIT = "organisationalUnit";
private static final String ORGANISATIONAL_UNIT_NAME = "organisationalUnit.name";
private static final String ORGANISATIONAL_UNIT_PARENT = "organisationalUnit.parent";
private static final String COUNTRY_OF_ORIGIN = "countryOfOrigin";
private static final String COUNTRY_OF_ORIGIN_NAME = "countryOfOrigin.name";
private static final String CURRENCY = "currency";
private static final String CREATED = "created";
private static final String IMPORTPRODUCT = "importProduct";
private static final String EXPORTPRODUCT = "exportProduct";
private static final String STYLE_REFERENCE = "styleReference";
private static final String STYLE_DESCRIPTION = "styleDescription";
private static final String SCHEDULE1PART1_PART1 = "schedule1Part1A.tariffHeading.part1";
private static final String SCHEDULE1PART1_PART2 = "schedule1Part1A.tariffHeading.part2";
private static final String SCHEDULE1PART1_PART3 = "schedule1Part1A.tariffHeading.part3";
private static final String SCHEDULE1PART1_PART4 = "schedule1Part1A.tariffHeading.part4";
private static final String SCHEDULE1PART2A_PART1 = "schedule1Part2A.tariffHeading.part1";
private static final String SCHEDULE1PART2A_PART2 = "schedule1Part2A.tariffHeading.part2";
private static final String SCHEDULE1PART2A_PART3 = "schedule1Part2A.tariffHeading.part3";
private static final String SCHEDULE1PART2B_PART1 = "schedule1Part2B.tariffHeading.part1";
private static final String SCHEDULE1PART2B_PART2 = "schedule1Part2B.tariffHeading.part2";
private static final String SCHEDULE1PART2B_PART3 = "schedule1Part2B.tariffHeading.part3";
private static final String SCHEDULE2PART1_PART1 = "schedule2Part1.tariffHeading.part1";
private static final String SCHEDULE2PART1_PART2 = "schedule2Part1.tariffHeading.part2";
private static final String SCHEDULE2PART1_PART3 = "schedule2Part1.tariffHeading.part3";
private static final String SCHEDULE2PART2_PART1 = "schedule2Part2.tariffHeading.part1";
private static final String SCHEDULE2PART2_PART2 = "schedule2Part2.tariffHeading.part2";
private static final String SCHEDULE2PART2_PART3 = "schedule2Part2.tariffHeading.part3";
private static final String SCHEDULE1PART3E_PART1 = "schedule1Part3E.tariffHeading.part1";
private static final String SCHEDULE1PART3E_PART2 = "schedule1Part3E.tariffHeading.part2";
private static final String SCHEDULE1PART3E_PART3 = "schedule1Part3E.tariffHeading.part3";
private static final String SCHEDULE3PART1_PART1 = "schedule3Part1.tariffHeading.part1";
private static final String SCHEDULE3PART1_PART2 = "schedule3Part1.tariffHeading.part2";
private static final String SCHEDULE3PART1_PART3 = "schedule3Part1.tariffHeading.part3";
private static final String SCHEDULE3PART1_PART4 = "schedule3Part1.tariffHeading.part4";
private static final String SCHEDULE4PART1_PART1 = "schedule4Part1.tariffHeading.part1";
private static final String SCHEDULE4PART1_PART2 = "schedule4Part1.tariffHeading.part2";
private static final String SCHEDULE4PART1_PART3 = "schedule4Part1.tariffHeading.part3";
private static final String SCHEDULE4PART1_PART4 = "schedule4Part1.tariffHeading.part4";
private static final String SCHEDULE1PART1_PART1_VALID = "schedule1Part1A.valid";
private static final String COMPLETE = "complete";
private static final String SADC = "sadcCertificateAdded";
private static final String STATE = "state";
private static final String HOMOLOGATION_STATE = "homologation.status";
/**
* {@inheritDoc}
* Saves a {@link Product} once it's deemed to be unique.
*
* @throws DuplicateProductException if the product supplied matches the key fields of an existing product.
* @see Product
* @see #findByUniqueFields(Product)
*/
@Override
public void save(final Product entity) {
try {
super.save(entity);
} catch (Exception e) {
throw new DuplicateProductException(e.getMessage());
}
}
@Override
public String getLastMD5ForProduct(String reference, IntegratedMD5.type type) {
Query query = getSession().createQuery("from IntegratedMD5 where reference =:reference and type =:type order by created desc");
query.setMaxResults(1);
query.setParameter("reference", reference);
query.setParameter("type", type);
IntegratedMD5 md5 = (IntegratedMD5) query.uniqueResult();
if (md5 != null) {
return md5.getMd5();
}
return null;
}
@Override
public void storeLastMD5ForProduct(IntegratedMD5 integratedMD5) {
getCurrentSession().persist(integratedMD5);
}
@Override
public void update(final Product entity) {
try {
super.update(entity);
} catch (Exception e) {
throw new DuplicateProductException(e.getMessage());
}
}
/**
* {@inheritDoc}
* The CriteriaOperation API was the initial approach for building up the
* search query but using it was showing unreliable results. Depending on
* the search parameters the result-set could contain more results than
* there were rows in the database. The database only had two rows in it
* while I was testing but the CriteriaOperation query was returning 6 for
* some searches, possibly the Cartesian product. From reading on the web it
* seems that the CriteriaOperation API is not suited to handling
* unidirectional associations such as those in the {@link Product} class.
* Hence the decision to use HQL in this method. Although it is slightly
* cumbersome to dynamically build up the HQL, the fact that there are only
* seven search fields in {@link ProductSearch} makes it somewhat easier.
* Performance should not be affected too much once we start paging the
* results from the database.
* TODO Paging
*/
@Override
public List<Product> elcSearch(ProductSearch search) {
DetachedCriteria criteria = DetachedCriteria.forClass(getPersistentClass());
addElcSearchRestrictions(criteria, search);
return getExecutableCriteriaList(criteria, search.getSearchMetaParams());
}
@Override
public long elcCount(ProductSearch search) {
DetachedCriteria criteria = DetachedCriteria.forClass(getPersistentClass());
addElcSearchRestrictions(criteria, search);
return getExecutableCriteriaCount(criteria);
}
protected void addElcSearchRestrictions(DetachedCriteria criteria, ProductSearch search) {
if (search.isStyleUser()) {
if (!StringUtils.isEmpty(search.getCode())) {
CriteriaBuilder.addiLikeAnyRestriction(criteria, STYLE_REFERENCE, search.getCode());
}
if (!StringUtils.isEmpty(search.getDescription())) {
CriteriaBuilder.addiLikeAnyRestriction(criteria, STYLE_DESCRIPTION, search.getDescription());
}
if (search.getSupplier() != null) {
CriteriaBuilder.addEqRestriction(criteria, SUPPLIER, search.getSupplier());
}
if (search.getOrganisationalUnit() != null && !search.getOrganisationalUnit().getName().equalsIgnoreCase("all")) {
CriteriaBuilder.addEqRestriction(criteria, ORGANISATIONAL_UNIT, search.getOrganisationalUnit());
}
if (search.getElcSupplier() != null && !search.getElcSupplier().getName().equalsIgnoreCase("all")) {
DetachedCriteria supplierCriteria = criteria.createCriteria("supplier");
CriteriaBuilder.addEqRestriction(supplierCriteria, "supplier", search.getElcSupplier());
}
CriteriaBuilder.addDateRangeCriteria(criteria, CREATED, search.getFromDate(), search.getToDate());
} else {
if (!StringUtils.isEmpty(search.getCode())) {
CriteriaBuilder.addiLikeAnyRestriction(criteria, CODE, search.getCode());
}
if (!StringUtils.isEmpty(search.getDescription())) {
CriteriaBuilder.addiLikeAnyRestriction(criteria, DESCRIPTION, search.getDescription());
}
CriteriaBuilder.addDateRangeCriteria(criteria, CREATED, search.getFromDate(), search.getToDate());
CriteriaBuilder.addEqRestriction(criteria, SUPPLIER_REFERENCE, search.getSupplierReference());
CriteriaBuilder.addEqRestriction(criteria, SUPPLIER, search.getSupplier());
CriteriaBuilder.addEqRestriction(criteria, COUNTRY_OF_ORIGIN, search.getCountryOfOrigin());
CriteriaBuilder.addEqRestriction(criteria, CURRENCY, search.getCurrency());
if (search.getElcSupplier() != null && !search.getElcSupplier().getName().equalsIgnoreCase("all")) {
DetachedCriteria supplierCriteria = criteria.createCriteria("supplier");
CriteriaBuilder.addEqRestriction(supplierCriteria, "supplier", search.getElcSupplier());
}
if (search.getOrganisationalUnit() != null && !search.getOrganisationalUnit().getName().equalsIgnoreCase("all")) {
CriteriaBuilder.addEqRestriction(criteria, ORGANISATIONAL_UNIT, search.getOrganisationalUnit());
}
}
}
@Override
public Product findByUniqueSearch(ProductSearch search) {
List<Product> results = searchByCriteria(search);
if (!results.isEmpty()) {
return findMostSpecificProduct(results, search);
}
return null;
}
@Override
public Product findByUniqueSearchBySupplierReference(ProductSearch search) {
List<Product> results = searchByCriteria(search);
if (!results.isEmpty()) {
if (results.size() == 1) {
return results.get(0);
} else {
for (Product product : results) {
return product;
}
}
}
return null;
}
private Product findMostSpecificProduct(List<Product> results, ProductSearch search) {
if (results.size() == 1) {
Product p = results.get(0);
return results.get(0);
} else {
for (Product product : results) {
if (product.getCode().equalsIgnoreCase(search.getCode())) {
return product;
}
}
}
return null;
}
@Override
public List<Product> findAllByCode(String productCode) {
Criteria searchCriteria = getSessionCustom().createCriteria(Product.class);
searchCriteria.add(Restrictions.eq(CODE, productCode));
@SuppressWarnings("unchecked")
List<Product> results = searchCriteria.list();
return results;
}
public List<Product> findAllByOrgUnitSupplier(OrganisationalUnitSupplier orgUnitSupplier) {
Criteria searchCriteria = getSessionCustom().createCriteria(Product.class);
searchCriteria.add(Restrictions.eq(ORGUNITSUPPLIER, orgUnitSupplier));
@SuppressWarnings("unchecked")
List<Product> results = searchCriteria.list();
return results;
}
@Override
public Product findByUniqueFields(Product product) {
final ProductSearch search1 = ProductSearch.uniqueSearch(product);
if (product.isImportProduct() && product.isExportProduct()) {
search1.setProductFlag(BOTH_IMPORT_AND_EXPORT);
}
if (!product.isImportProduct() && product.isExportProduct()) {
search1.setProductFlag("Export");
search1.setSupplier(null);
}
if (product.isImportProduct() && !product.isExportProduct()) {
search1.setProductFlag("Import");
}
List<Product> search = searchByCriteria(search1);
if (!search.isEmpty()) {
log.error("Product search not empty. Create or update would cause duplicate products");
return search.get(0);
}
return null;
}
@Override
public List<Product> search(ProductSearch search) {
String order = null;
SearchMetaParams searchMetaParams = search.getSearchMetaParams() != null ? search.getSearchMetaParams() :
new SearchMetaParams(-1, -1, "created", true);
if (searchMetaParams != null && StringUtils.isNotEmpty(searchMetaParams.getOrderBy()) && !searchMetaParams.getOrderBy().equals("created")) {
String orderBy = searchMetaParams.getOrderBy();
switch (orderBy) {
case COUNTRY_OF_ORIGIN:
orderBy = COUNTRY_OF_ORIGIN_NAME;
break;
case ORGANISATIONAL_UNIT:
orderBy = ORGANISATIONAL_UNIT_NAME;
break;
case SUPPLIER:
orderBy = ORGUNITSUPPLIER_NAME;
break;
}
order = searchMetaParams.isAsc() ? orderBy + " ASC" : orderBy + " DESC";
//will resolve paging issue, when 2 order have the same column value.
order = order + " ,created ";
searchMetaParams.setOrderBy(order);
}
Query query = createQuery(search, false, addSpecialCriteriaAtSuffix(search), mapFieldsToValues(search));
setParameterForSpecialCriteria(query, search);
return getQueryList(query, search.getSearchMetaParams());
}
/**
* Perform a full product search, this means ignore pagination.
*
* @param productSearch
* @return
*/
@Override
public List<Product> fullSearch(ProductSearch productSearch) {
return searchProducts(productSearch, "created", false);
}
@Override
public List<Product> fullExportSearch(ProductSearch productSearch) {
return searchProducts(productSearch, "code", true);
}
private List<Product> searchProducts(ProductSearch productSearch, String orderBy, boolean ascending) {
Query query = createSortedQuery(productSearch, addSpecialCriteriaAtSuffix(productSearch), mapFieldsToValues(productSearch), orderBy,
ascending);
setParameterForSpecialCriteria(query, productSearch);
return query.list();
}
@Override
public long count(ProductSearch search) {
Query query = createQuery(search, true, addSpecialCriteriaAtSuffix(search), mapFieldsToValues(search));
setParameterForSpecialCriteria(query, search);
return getQueryCount(query);
}
/*
* A convenience method that maps each search field from {@code ProductSearch} to the corresponding value from the search.
* @param search The {@code ProductSearch} whose names and values will create the map
* @return A map of search field-name to field-value
*/
@Override
protected Collection<CriteriaValue> mapFieldsToValues(ProductSearch search) {
return mapFieldsToValuesUnique(search, search.isUnique());
}
private Collection<CriteriaValue> mapFieldsToValuesUnique(ProductSearch search, boolean unique) {
Collection<CriteriaValue> fields = new LinkedHashSet<>();
if (search.getCode() != null && (unique || search.isExactMatch())) {
fields.add(CriteriaValue.eqIgnoreCase(CODE, StringUtils.isNotEmpty(search.getCode()) ? search.getCode().toLowerCase() : ""));
} else {
fields.add(CriteriaValue.like(CODE, search.getCode()));
fields.add(CriteriaValue.like(DESCRIPTION, search.getDescription()));
fields.add(CriteriaValue.like(SUPPLIER_REFERENCE, search.getSupplierReference()));
}
if (CollectionUtils.isNotEmpty(search.getSuppliers())) {
fields.add(CriteriaValue.in(SUPPLIER, search.getSuppliers()));
} else if (search.getSupplier() != null) {
fields.add(CriteriaValue.eq(SUPPLIER, search.getSupplier()));
}
if (CollectionUtils.isNotEmpty(search.getCountryOfOriginList())) {
fields.add(CriteriaValue.in(COUNTRY_OF_ORIGIN, search.getCountryOfOriginList()));
} else {
fields.add(CriteriaValue.eq(COUNTRY_OF_ORIGIN, search.getCountryOfOrigin()));
}
if (CollectionUtils.isNotEmpty(search.getCurrencyList())) {
fields.add(CriteriaValue.in(CURRENCY, search.getCurrencyList()));
} else {
fields.add(CriteriaValue.eq(CURRENCY, search.getCurrency()));
}
final Set<OrganisationalUnit> organisationalUnits = getOrganisationalUnits(search);
if (!organisationalUnits.isEmpty()) {
fields.add(CriteriaValue.in(ORGANISATIONAL_UNIT, organisationalUnits));
}
if (search.isExactMatch()) {
fields.add(CriteriaValue.eq(STYLE_REFERENCE, search.getStyleReference()));
} else {
fields.add(CriteriaValue.like(STYLE_REFERENCE, search.getStyleReference()));
}
fields.add(CriteriaValue.like(STYLE_DESCRIPTION, search.getStyleDescription()));
if (search.getTypes() != null && !search.getTypes().isEmpty()) {
fields.add(CriteriaValue.in("type", search.getTypes()));
} else {
fields.add(CriteriaValue.in("type", Arrays.asList(Product.Type.FULL, Product.Type.PRE_PACK)));
}
if (null != search.getProductIdList() && search.getProductIdList().size() > 0) {
fields.add(CriteriaValue.notInKey(ID, search.getProductIdList().toArray()));
}
/*
* if(search.getProductFlag().equals("Import")){ fields.add(CriteriaValue.eq(IMPORTPRODUCT,Boolean.TRUE));
* fields.add(CriteriaValue.eq(EXPORTPRODUCT,Boolean.FALSE)); }
*/
if (search.getProductFlag() != null) {
if (search.getProductFlag().equals("Export")) {
fields.add(CriteriaValue.eq(EXPORTPRODUCT, Boolean.TRUE));
} else if (search.getProductFlag().equals("Import")) {
fields.add(CriteriaValue.eq(IMPORTPRODUCT, Boolean.TRUE));
} else if (search.getProductFlag().equalsIgnoreCase(ProductSearch.BOTH_IMPORT_AND_EXPORT)) {
// fields.add(CriteriaValue.in(EXPORTPRODUCT, Arrays.asList(Boolean.TRUE, Boolean.FALSE)));
// fields.add(CriteriaValue.in(IMPORTPRODUCT, Arrays.asList(Boolean.TRUE, Boolean.FALSE)));
}
/*
* else{ fields.add(CriteriaValue.eq(EXPORTPRODUCT, Boolean.FALSE)); fields.add(CriteriaValue.eq(IMPORTPRODUCT, Boolean.TRUE)); }
*/
} else {
fields.add(CriteriaValue.eq(EXPORTPRODUCT, Boolean.FALSE));
fields.add(CriteriaValue.eq(IMPORTPRODUCT, Boolean.TRUE));
}
if (search.getSchedule1Part1() != null && !search.getSchedule1Part1().equals("../")) {
fields.add(CriteriaValue.like(SCHEDULE1PART1_PART1, getSchedulePart1(search.getSchedule1Part1())));
if (getSchedulePart2(search.getSchedule1Part1()) != null) {
fields.add(CriteriaValue.like(SCHEDULE1PART1_PART2, getSchedulePart2(search.getSchedule1Part1())));
}
if (getSchedulePart3(search.getSchedule1Part1()) != null) {
fields.add(CriteriaValue.like(SCHEDULE1PART1_PART3, getSchedulePart3(search.getSchedule1Part1())));
}
if (getSchedulePart4(search.getSchedule1Part1()) != null) {
fields.add(CriteriaValue.like(SCHEDULE1PART1_PART4, getSchedulePart4(search.getSchedule1Part1())));
}
}
if (search.getSchedule1Part2A() != null && !search.getSchedule1Part2A().equals("..")) {
fields.add(CriteriaValue.like(SCHEDULE1PART2A_PART1, getSchedulePart1(search.getSchedule1Part2A())));
fields.add(CriteriaValue.like(SCHEDULE1PART2A_PART2, getSchedulePart2(search.getSchedule1Part2A())));
fields.add(CriteriaValue.like(SCHEDULE1PART2A_PART3, getSchedulePart3(search.getSchedule1Part2A())));
}
if (search.getSchedule1Part2B() != null && !search.getSchedule1Part2B().equals("..")) {
fields.add(CriteriaValue.like(SCHEDULE1PART2B_PART1, getSchedulePart1(search.getSchedule1Part2B())));
fields.add(CriteriaValue.like(SCHEDULE1PART2B_PART2, getSchedulePart2(search.getSchedule1Part2B())));
fields.add(CriteriaValue.like(SCHEDULE1PART2B_PART3, getSchedulePart3(search.getSchedule1Part2B())));
}
if (search.getSchedule2Part1() != null && !search.getSchedule2Part1().equals("..")) {
fields.add(CriteriaValue.like(SCHEDULE2PART1_PART1, getSchedulePart1(search.getSchedule2Part1())));
fields.add(CriteriaValue.like(SCHEDULE2PART1_PART2, getSchedulePart2(search.getSchedule2Part1())));
fields.add(CriteriaValue.like(SCHEDULE2PART1_PART3, getSchedulePart3(search.getSchedule2Part1())));
}
if (search.getSchedule2Part2() != null && !search.getSchedule2Part2().equals("..")) {
fields.add(CriteriaValue.like(SCHEDULE2PART2_PART1, getSchedulePart1(search.getSchedule2Part2())));
fields.add(CriteriaValue.like(SCHEDULE2PART2_PART2, getSchedulePart2(search.getSchedule2Part2())));
fields.add(CriteriaValue.like(SCHEDULE2PART2_PART3, getSchedulePart3(search.getSchedule2Part2())));
}
if (search.getSchedule1Part3E() != null && !search.getSchedule1Part3E().equals("..")) {
fields.add(CriteriaValue.like(SCHEDULE1PART3E_PART1, getSchedulePart1(search.getSchedule1Part3E())));
fields.add(CriteriaValue.like(SCHEDULE1PART3E_PART2, getSchedulePart2(search.getSchedule1Part3E())));
fields.add(CriteriaValue.like(SCHEDULE1PART3E_PART3, getSchedulePart3(search.getSchedule1Part3E())));
}
if (search.getSchedule1Part7() != null && !search.getSchedule1Part7().equals("..")) {
fields.add(CriteriaValue.like(SCHEDULE1PART3E_PART1, getSchedulePart1(search.getSchedule1Part7())));
fields.add(CriteriaValue.like(SCHEDULE1PART3E_PART2, getSchedulePart2(search.getSchedule1Part7())));
fields.add(CriteriaValue.like(SCHEDULE1PART3E_PART3, getSchedulePart3(search.getSchedule1Part7())));
}
if (search.getSchedule3Part1() != null && !search.getSchedule3Part1().equals("...")) {
fields.add(CriteriaValue.like(SCHEDULE3PART1_PART1, getSchedulePart1(search.getSchedule3Part1())));
if (getSchedulePart2(search.getSchedule3Part1()) != null) {
fields.add(CriteriaValue.like(SCHEDULE3PART1_PART2, getSchedulePart2(search.getSchedule3Part1())));
}
if (getSchedulePart3(search.getSchedule3Part1()) != null) {
fields.add(CriteriaValue.like(SCHEDULE3PART1_PART3, getSchedulePart3(search.getSchedule3Part1())));
}
if (getSchedulePart4(search.getSchedule3Part1()) != null) {
fields.add(CriteriaValue.like(SCHEDULE3PART1_PART4, getSchedulePart4(search.getSchedule3Part1())));
}
}
if (search.getSchedule4Part1() != null && !search.getSchedule4Part1().equals("...")) {
fields.add(CriteriaValue.like(SCHEDULE4PART1_PART1, getSchedulePart1(search.getSchedule4Part1())));
if (getSchedulePart2(search.getSchedule4Part1()) != null) {
fields.add(CriteriaValue.like(SCHEDULE4PART1_PART2, getSchedulePart2(search.getSchedule4Part1())));
}
if (getSchedulePart3(search.getSchedule4Part1()) != null) {
fields.add(CriteriaValue.like(SCHEDULE4PART1_PART3, getSchedulePart3(search.getSchedule4Part1())));
}
if (getSchedulePart4(search.getSchedule4Part1()) != null) {
fields.add(CriteriaValue.like(SCHEDULE4PART1_PART4, getSchedulePart4(search.getSchedule4Part1())));
}
}
if (search.getState() != null) {
fields.add(CriteriaValue.eq(STATE, search.getState()));
}
if (search.getState() != null) {
if (search.getState().equals(ProductState.TARIFFED)) {
fields.add(CriteriaValue.eq(SCHEDULE1PART1_PART1_VALID, true));
fields.add(CriteriaValue.isNotNull(SCHEDULE1PART1_PART1));
}
}
//see addSpecialCriteriaAtSuffix for "Untariffed"
if (search.getComplete() != null && search.getComplete().equals("COMPLETE")) {
fields.add(CriteriaValue.eq(COMPLETE, true));
}
if (search.getComplete() != null && search.getComplete().equals("INCOMPLETE")) {
fields.add(CriteriaValue.eq(COMPLETE, false));
}
if (search.getSadc() != null && search.getSadc().equals("YES")) {
fields.add(CriteriaValue.eq(SADC, true));
}
if (search.getSadc() != null && search.getSadc().equals("NO")) {
fields.add(CriteriaValue.eq(SADC, false));
}
if (search.getHomologationStatus() != null) {
fields.add(CriteriaValue.eq(HOMOLOGATION_STATE, search.getHomologationStatus()));
} else if (search.getHomologationStatuses() != null && !search.getHomologationStatuses().isEmpty()) {
CriteriaValue in = CriteriaValue.in(HOMOLOGATION_STATE, search.getHomologationStatuses());
fields.add(in);
} else if (search.getExludeHomologationStatuses() != null && !search.getExludeHomologationStatuses().isEmpty()) {
CriteriaValue in = CriteriaValue.notIn(HOMOLOGATION_STATE, search.getExludeHomologationStatuses());
fields.add(in);
}
return fields;
}
private Set<OrganisationalUnit> getOrganisationalUnits(ProductSearch search) {
Set<OrganisationalUnit> organisationalUnits = Collections.EMPTY_SET;
if (search.isFilteredByUserOrg()) {
organisationalUnits = getUserOrganisationalUnits();
} else if (search.getOrganisationalUnit() != null) {
organisationalUnits = Collections.singleton(search.getOrganisationalUnit());
} else if (!CollectionUtils.isEmpty(search.getOrganisationalUnits())) {
organisationalUnits = new HashSet<>(search.getOrganisationalUnits());
}
return organisationalUnits;
}
private String addSpecialCriteriaAtSuffix(ProductSearch search) {
List<String> selects = new ArrayList<>();
if (search.getState() != null) {
if (search.getState().equals(ProductState.UNTARIFFED)) {
addSelectIfPresent(selects, Collections.singleton(CriteriaValue.eq(SCHEDULE1PART1_PART1_VALID, false)));
return StringUtils.join(selects, " and ");
}
}
return "";
}
private void setParameterForSpecialCriteria(Query query, ProductSearch search) {
if (search.getState() != null) {
if (search.getState().equals(ProductState.UNTARIFFED)) {
query.setParameter(getParamName(SCHEDULE1PART1_PART1_VALID), false);
}
}
}
/**
* Obtain all sub organisational unit id's belonging to 'orgUnitId'.
*
* @param orgUnitId
* @return
*/
public List<Long> getSubOrgUnitIDs(Long orgUnitId) {
String hql = "select id from OrganisationalUnit where parent.id = :parentId";
Query query = getSession().createQuery(hql);
query.setParameter("parentId", orgUnitId);
return (List<Long>) query.list();
}
private String getSchedulePart1(String tariffHeading) {
if (tariffHeading != null && tariffHeading.contains(".")) {
String[] parts = tariffHeading.split("\\.");
if (parts.length > 0)
return parts[0];
}
return tariffHeading;
}
private String getSchedulePart2(String tariffHeading) {
if (tariffHeading != null && tariffHeading.contains(".")) {
String[] parts = tariffHeading.split("\\.");
if (parts.length > 1)
return parts[1];
}
return null;
}
private String getSchedulePart3(String tariffHeading) {
if (tariffHeading != null && tariffHeading.contains(".")) {
String[] parts = tariffHeading.split("\\.");
if (parts.length > 2)
if (parts[2].contains("/")) {
return parts[2].substring(0, parts[2].indexOf("/"));
} else {
return parts[2];
}
}
return null;
}
private String getSchedulePart4(String tariffHeading) {
if (tariffHeading != null && tariffHeading.contains("/")) {
String[] parts = tariffHeading.split("/");
if (parts.length > 1)
return parts[1];
}
return null;
}
@Override
public List<Product> findProductsByCodes(List<String> itemCodes, Set<Long> orgIds, boolean useForRfq) {
javax.persistence.criteria.CriteriaBuilder cb = getSession().getCriteriaBuilder();
CriteriaQuery<Product> cq = cb.createQuery(Product.class);
Root<Product> root = cq.from(Product.class);
List<Predicate> predicates = new ArrayList<>();
// Always require code IN (:itemCodes)
predicates.add(root.get("code").in(itemCodes));
if(useForRfq){
predicates.add( cb.equal( root.get("useForRfq"),useForRfq));
}
// Optionally require orgId IN (:orgIds)
if (orgIds != null && !orgIds.isEmpty()) {
Join<Product, OrganisationalUnit> ouJoin = root.join("organisationalUnit");
predicates.add(ouJoin.get("id").in(orgIds));
}
cq.select(root).where(cb.and(predicates.toArray(new Predicate[0])));
return getSession().createQuery(cq).list();
}
@Override
public List<Product> findWebServiceProduct(Object reference, OrganisationalUnitSupplier orgUnitSupplier,
OrganisationalUnit organisationalUnitt, Country countryy) {
Criteria searchCriteria = getSessionCustom().createCriteria(Product.class);
searchCriteria.add(Restrictions.eq(CODE, reference));
if (null != orgUnitSupplier) {
searchCriteria.add(Restrictions.eq(ORGUNITSUPPLIER, orgUnitSupplier));
}
if (null != organisationalUnitt) {
searchCriteria.add(Restrictions.eq(ORGANISATIONAL_UNIT, organisationalUnitt));
}
if (null != countryy) {
searchCriteria.add(Restrictions.eq(COUNTRY_OF_ORIGIN, countryy));
}
@SuppressWarnings("unchecked")
List<Product> results = searchCriteria.list();
return results;
}
@Override
public List<Product> findAllShipmentProducts(Long shipmentId) {
log.debug("Start Q1");
Query query = getSession().createNativeQuery("select distinct( p.id) from product p ," +
" lineitem i join orders o on (i.order_id=o.id) j" +
"oin consignment c on (c.id=o.consignment_id) join shipment s on (s.id=c.shipment_id) where c.shipment_id="
+ shipmentId.toString() + " and p.supplier_id=i.supplier_id and" +
" p.countryoforigin_code=i.countryoforigin_code and p.organisationalunit_id=i.organisationalunit_id " +
"and p.code=i.code and (s.lastTariffedDate is null OR p.updated>s.lastTariffedDate)");
List<BigInteger> list = query.list();
log.debug("END Q1");
if (list != null && !list.isEmpty()) {
Criteria searchCriteria = getSessionCustom().createCriteria(Product.class);
searchCriteria.add(Restrictions.in("id", list.stream().map(BigInteger::longValue).collect(Collectors.toSet())));
final List list1 = searchCriteria.list();
log.debug("END Q2");
return list1;
} else {
return Collections.EMPTY_LIST;
}
}
@Override
public List<Product> findAllProducts(Set<Long> itemIds, Long shipmentId, boolean importProduct) {
List<BigInteger> productIds = getSession().createNativeQuery("select p.id from product p, \n" +
"(select i.id, i.supplier_id, i.countryoforigin_code, i.organisationalunit_id, i.code \n" +
"from lineitem i \n" +
"where\n" +
"i.id in (:itemIds)) as items\n" +
"where p.code = items.code \n" +
"and p.organisationalunit_id = items.organisationalunit_id \n" +
"and p.supplier_id = items.supplier_id \n" +
"and p.countryoforigin_code = items.countryoforigin_code")
.setParameterList("itemIds", itemIds).list();
List<Long> ids = productIds.stream().map(BigInteger::longValue).collect(Collectors.toList());
org.hibernate.query.Query<Product> query = getSession().createQuery("from Product p where p.id in (:productIds)", Product.class);
query.setParameterList("productIds", ids);
return query.list();
}
@Override
public List<Product> findAllExportInvoiceProducts(Long exportInvoiceId) {
StringBuilder stringBuilder = new StringBuilder(" select distinct(p.id) from product p ")
.append("join organisationalunit pu on (pu.id=p.organisationalunit_id) , actuallineitem i")
.append(" join actualorder o on (i.actualorder_id=o.id) join actualconsignment c on (c.id=o.actualconsignment_id)")
.append(" join exportinvoice s on (s.id=c.costsinvoice_id) where s.id=:exportInvoiceId and p.supplier_id=i.supplier_id ")
.append("and p.countryoforigin_code=i.countryoforigin_code and pu.code=i.organisation and p.code=i.code and p.exportproduct;");
NativeQuery query = getCurrentSession().createNativeQuery(stringBuilder.toString()).setParameter("exportInvoiceId", exportInvoiceId);
List<BigInteger> list = query.list();
if (list != null && !list.isEmpty()) {
return getSession().createQuery("from Product where id in (:productIds)").setParameterList("productIds", list.stream()
.map(bigInteger -> bigInteger.longValue()).collect(Collectors.toList())).getResultList();
} else {
return Collections.EMPTY_LIST;
}
}
@Override
public Product findByUniqueFields(String code, Long supplierId, String countryCode, String organisationCode, boolean isExport) {
StringBuilder stringBuilder = new StringBuilder("select * from product ");
stringBuilder.append("left join organisationalunit on product.organisationalunit_id = organisationalunit.id ");
stringBuilder.append("where product.code =:code and countryoforigin_code=:countryCode ");
stringBuilder.append("and supplier_id =:supplierId and organisationalunit.code =:organisationCode and exportproduct =:isExport");
NativeQuery<Product> nativeQuery = getSession().createNativeQuery(stringBuilder.toString(), Product.class);
nativeQuery.setParameter("code", code);
nativeQuery.setParameter("supplierId", supplierId);
nativeQuery.setParameter("countryCode", countryCode);
nativeQuery.setParameter("organisationCode", organisationCode);
nativeQuery.setParameter("isExport", isExport);
return nativeQuery.uniqueResult();
}
@Override
public ProductResult retrieveLite(Long entityId) {
StringBuilder stringBuilder = new StringBuilder("select p.code,p.state,countryoforigin_code as countryCode,supplier_id as supplierId," +
" u.code as organisation,exportProduct,p.id " +
"from product p join organisationalunit u on (u.id=p.organisationalunit_id) where p.id=:entityId");
Query query = getSessionCustom().createSQLQuery(stringBuilder.toString()).setParameter("entityId", entityId)
.addScalar("id", StandardBasicTypes.LONG)
.addScalar("supplierId", StandardBasicTypes.LONG)
.addScalar("code", StandardBasicTypes.STRING)
.addScalar("countryCode", StandardBasicTypes.STRING)
.addScalar("state", StandardBasicTypes.STRING)
.addScalar("exportProduct", StandardBasicTypes.BOOLEAN)
.addScalar("organisation", StandardBasicTypes.STRING);
query.setResultTransformer(Transformers.aliasToBean(ProductResult.class));
//log.debug(query.getQueryString());
return (ProductResult) query.uniqueResult();
}
@Override
public List<String> allProductStyles(List<OrganisationalUnitSupplier> supplierList, HashSet<OrganisationalUnit> organisationalUnits) {
List supplierList1 = getSession().createQuery("select distinct styleReference from Product where styleReference is not null" +
" and supplier in (:supplierList) " +
"and organisationalUnit in (:organisationalUnits)")
.setParameterList("supplierList", supplierList).setParameterList("organisationalUnits", organisationalUnits).getResultList();
return supplierList1;
}
@Override
public List<OrganisationalUnitSupplier> findAllOrgUnitSuppliers(Set<Long> productIds) {
return getSession().createQuery("select distinct p.supplier from Product p where p.id in (:prodIds)")
.setParameterList("prodIds", productIds).list();
}
@Override
public List<Product> findAllByIds(Set<Long> productIds, OrganisationalUnitSupplier organisationalUnitSupplier) {
StringBuilder hql = new StringBuilder("select p from Product p where p.id in (:prodIds)");
if (organisationalUnitSupplier != null) {
hql.append(" and p.supplier = :supplier");
}
Query<Product> query = getSession().createQuery(hql.toString(), Product.class)
.setParameterList("prodIds", productIds);
if (organisationalUnitSupplier != null) {
query.setParameter("supplier", organisationalUnitSupplier);
}
return query.list();
}
}