OrderRepositoryImpl.java
package com.tradecloud.repository.impl;
import com.tradecloud.authentication.MultiTenantUtil;
import com.tradecloud.common.base.HibernateUtils;
import com.tradecloud.common.base.StaticDataEntityBase;
import com.tradecloud.domain.base.utils.DateRange;
import com.tradecloud.domain.base.utils.DateUtils;
import com.tradecloud.domain.base.utils.ObjectUtil;
import com.tradecloud.domain.common.Incoterm;
import com.tradecloud.domain.costing.CostingContextType;
import com.tradecloud.domain.costing.clean.CostedOrder;
import com.tradecloud.domain.costing.clean.TotalsDistributionType;
import com.tradecloud.domain.document.invoice.ActualOrder;
import com.tradecloud.domain.dto.base.SearchBase;
import com.tradecloud.domain.event.OrderEventType;
import com.tradecloud.domain.event.OrdersEvent;
import com.tradecloud.domain.infrastructure.persistence.CriteriaBuilder;
import com.tradecloud.domain.item.AdditionalLineItemType;
import com.tradecloud.domain.item.LineItem;
import com.tradecloud.domain.letterofcredit.LetterOfCreditUtils;
import com.tradecloud.domain.model.ordermanagement.*;
import com.tradecloud.domain.model.ordermanagement.Order;
import com.tradecloud.domain.model.ordermanagement.Order.UnlinkedOrdersType;
import com.tradecloud.domain.model.ordermanagement.OrderType;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnitTier;
import com.tradecloud.domain.party.ServiceProvider;
import com.tradecloud.domain.shipment.Shipment;
import com.tradecloud.domain.shipment.ShippingInformation;
import com.tradecloud.dto.freetext.FreeTextSearch;
import com.tradecloud.dto.order.*;
import com.tradecloud.repository.GeneralRepository;
import com.tradecloud.repository.OrderRepository;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.base.impl.CriteriaOperation;
import com.tradecloud.repository.base.impl.CriteriaValue;
import com.tradecloud.repository.base.impl.CriteriaValueUtils;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import com.tradecloud.repository.order.NotShippedReportHelper;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.NotImplementedException;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
import org.hibernate.proxy.HibernateProxy;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.time.YearMonth;
import java.util.*;
import java.util.stream.Collectors;
/**
* Default implementation of the {@code OrderRepository} interface.
*
* @param <T> The order
* @param <S> The search
*/
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public abstract class OrderRepositoryImpl<T extends Order, S extends OrderSearch> extends RepositoryBaseImpl<T, S> implements OrderRepository<T, S> {
private static final long serialVersionUID = 1L;
private final long ONE_DAY = 1000 * 60 * 60 * 24;
private final static Logger log = Logger.getLogger(OrderRepositoryImpl.class);
/**
* Use constants for the search field names to avoid any typos etc when
* creating queries. All methods that use these fields should use the
* appropriate constant.
*/
private static final String NUMBER = "number";
private static final String ORDER_REFERENCE = "orderReference";
private static final String CURRENCY = "currency";
private static final String ORGANISATIONAL_UNIT = "organisationalUnit";
private static final String ELC_ORDER = "elc";
private static final String STATE = "state";
private static final String CREATED = "created";
private static final String SHIPPING_MODE = "purchaseorder.shippingInformation.shippingMode";
private static final String PLACE_OF_LOADING = "purchaseorder.shippingInformation.placeOfLoading";
protected static final String BUYER = "buyer";
protected static final String SHIPPING_REFERENCE = "purchaseorder.shippingInformation.shippingReference";
protected static final String SALES_SHIPPING_REFERENCE = "salesorder.shippingInformation.shippingReference";
private static final String LATEST_SHIPMENT_DATE = "purchaseorder.orderDates.latestShipmentDate";
private static final String ORDER_DATES_LATEST_SHIPMENT_DATE = "purchaseorder.orderDates.latestShipmentDate";
private static final String ORDER_DATE = "orderdate";
private static final String PAYMENT_METHOD_CODE = "purchaseorder.paymentMethod.code";
private static final String CONSIGNMENT_STATE = "purchaseorder.consignment.state";
private static final String SUPPLIER = "purchaseorder.supplier.organisationalUnit";
protected static final String SET_SUPPLIER = "supplier";
protected static final String STYLE_REFERENCE = "styleReference";
protected static final String STYLE_DESCRIPTION = "styleDescription";
private static final String SHIPMENT_CREATION_DATE = "purchaseorder.consignment.shipment.created";
private static final String FREIGHT_FORWARDER = "purchaseorder.shippingInformation.freightForwarder";
private final static String ORGANISATIONAL_UNIT_PARENT = "purchaseorder.organisationalUnit.parent";
private static final String PROFORMA_REFERENCE = "proFormaReference";
private static final String EVENTS = "events";
private static final String SHIPPING_INFORMATION = "shippingInformation";
private static final String ORDER_LATEST_SHIPMENT_DATE = "orderDates.latestShipmentDate";
private static final String CONFIRMED = "confirmed";
public int availabilityDateSpread;
@Autowired
private GeneralRepository generalRepository;
// example, your count returns 1 consignment, 7 orders, 1 supplier, 6
// shipments, and you want to return
// results 6-10. Totals 1, 8, 9, 15.
private Criterion createFreeTextCriterion(FreeTextSearch search) {
return Restrictions.or(Restrictions.eq(ORDER_REFERENCE, search.getText()), Restrictions.eq(NUMBER, search.getText()));
}
@Override
public long countFreeText(FreeTextSearch search) {
DetachedCriteria searchCriteria = DetachedCriteria.forClass(getPersistentClass());
searchCriteria.add(createFreeTextCriterion(search));
return getExecutableCriteriaCount(searchCriteria);
}
@Override
public List<T> searchFreeText(FreeTextSearch search) {
DetachedCriteria searchCriteria = DetachedCriteria.forClass(getPersistentClass());
searchCriteria.add(createFreeTextCriterion(search));
return getExecutableCriteriaList(searchCriteria, search.getSearchMetaParams());
}
@Override
public T findByReference(String reference) {
return (T) ObjectUtil.first(findByNamedQueryAndNamedParam("order.findByReference", "orderReference", reference));
}
@Override
public List<T> findAllByReference(String reference) {
return (List<T>) findByNamedQueryAndNamedParam("order.findByReference", "orderReference", reference);
}
@Override
public List<T> findAllByQuote(String reference) {
return (List<T>) findByNamedQueryAndNamedParam("order.findByQuote", "quoteReference", reference);
}
@Override
public T findByReferenceNotDeleted(String reference) {
return (T) ObjectUtil.first(findByNamedQueryAndNamedParam("order.findByReferenceNotDeleted", "orderReference", reference));
}
@Override
public T findByReferenceActive(String reference) {
return (T) ObjectUtil.first(findByNamedQueryAndNamedParam("order.findByReferenceNotDeleted", "orderReference", reference));
}
@Override
public String findReferenceByIdNotDeleted(long orderId) {
Criteria criteria = getCurrentSession().createCriteria(Order.class);
criteria.add(Restrictions.eq("id", orderId));
criteria.add(Restrictions.ne("state", OrderState.DELETED));
criteria.setProjection(Projections.property("orderReference"));
String reference = (String) criteria.list().get(0);
return reference;
}
@Override
public Page<T> findAllArchivedByReference(String reference, Pageable search) {
Query query = getCurrentSession().getNamedQuery("order.findAllArchivedByReference");
query.setParameter("reference", reference);
query.setFetchSize(search.getPageSize());
query.setFirstResult(search.getPageNumber());
return new PageImpl<T>(query.list(), PageRequest.of(query.getFirstResult(), query.getFetchSize()), query.getMaxResults());
}
@Override
public T findByIdWithLineItems(long id) {
return (T) ObjectUtil.first(findByNamedQueryAndNamedParam("order.findById", "id", id));
}
@Override
public T findByIdWithEventsAndPlannedSettlements(Long id) {
return (T) ObjectUtil.first(findByNamedQueryAndNamedParam("order.findByIdWithEventsAndPlannedSettlements", "id", id));
}
// TODO. This must be refactored to use paging.
@Override
public Map<UnlinkedOrdersType, List<T>> findAllUnlinkedOrders(OrderSearch search) {
/*
* Using a generic findAllUnlinkedOrders as the SQL could not know the populated column of the abstract Place table between city_code,
* port_code, country_code and depot_code as used by the Order.shippingInformation.placeOfDischarge/placeOfLoading. Instead we loop through
* the unlinked orders and remove the non-matching ones according to the search param
*/
Map<UnlinkedOrdersType, List<T>> unlinkedOrdersMap = new EnumMap<UnlinkedOrdersType, List<T>>(UnlinkedOrdersType.class);
@SuppressWarnings("unchecked")
List<T> unlinkedOrders;
if (search.isFilteredByUserOrg()) {
Set<OrganisationalUnit> organisationalUnits = getUserOrganisationalUnits();
List<OrganisationalUnit> organisationalUnitList = new ArrayList<>();
if (!organisationalUnits.isEmpty()) {
for (OrganisationalUnit orgUnit : organisationalUnits) {
organisationalUnitList.add(orgUnit);
}
}
unlinkedOrders = (List<T>) findByNamedQueryAndNamedParam(getEntityName() +
".findAllUnlinkedWithItemsForOrganisationalUnitStructure", "organisationalUnit", organisationalUnitList);
} else if (search instanceof PurchaseOrderSearch && search.getOrganisationalUnit() == null) {
unlinkedOrders = (List<T>) getNamedQuery(getEntityName() + ".findAllUnlinkedWithItems");
} else if (search instanceof PurchaseOrderSearch) {
List<OrganisationalUnit> organisationalUnitList = new ArrayList<>();
for (OrganisationalUnit organisationalUnit : search.getOrganisationalUnit().getChildren()) {
organisationalUnitList.add(organisationalUnit);
}
organisationalUnitList.add(search.getOrganisationalUnit());
unlinkedOrders = (List<T>) findByNamedQueryAndNamedParam(getEntityName() +
".findAllUnlinkedWithItemsForOrganisationalUnitStructure", "organisationalUnit", organisationalUnitList);
} else {
unlinkedOrders = (List<T>) findByNamedQueryAndNamedParam(getEntityName() + ".findAllUnlinkedWithItemsForOrganisationalUnit",
"organisationalUnit", search.getOrganisationalUnit());
}
List<T> ineligibleUnlinkedOrders = new ArrayList<T>();
List<T> deletedOrders = new ArrayList<T>();
for (T order : unlinkedOrders) {
ShippingInformation shippingInfo = order.getShippingInformation();
// Have split the following checks up code clarity sakes rather than
// one big if stmt
// incoterm
if (!matchesSearchParamSD(order.getShippingInformation().getIncoterm(), search.getIncoterm())) {
ineligibleUnlinkedOrders.add(order);
} // shipping information fields are required for linking
else if (shippingInfo == null) {
ineligibleUnlinkedOrders.add(order);
} else if (!matchesSearchParam(shippingInfo.getPlaceOfLoading(), search.getPlaceOfLoading())) {
ineligibleUnlinkedOrders.add(order);
} else if (!matchesSearchParam(shippingInfo.getPlaceOfDischarge(), search.getPlaceOfDischarge())) {
ineligibleUnlinkedOrders.add(order);
} else if (!matchesSearchParam(shippingInfo.getShippingMode(), search.getShippingMode())) {
ineligibleUnlinkedOrders.add(order);
} else if (!matchesSearchParam(shippingInfo.getFreightForwarder(), search.getFreightForwarder())) {
ineligibleUnlinkedOrders.add(order);
} else if (!matchesSearchParam(order.getOrderDates().getAvailabilityDate(), search.getAvailabilityDateRange())) {
ineligibleUnlinkedOrders.add(order);
} else if (!matchesSearchParam(order.getOrderDates().getLatestShipmentDate(), search.getLatestShipmentDateRange())) {
ineligibleUnlinkedOrders.add(order);
} else if (!matchesSearchParam(order.getOrderDates().getRequiredOnSiteDate(), search.getRequiredOnSiteDateRange())) {
ineligibleUnlinkedOrders.add(order);
} else if (order.getState().equals(OrderState.DELETED)) {
deletedOrders.add(order);
} else if (search.getCurrency() != null && !order.getCurrency().equals(search.getCurrency())) {
ineligibleUnlinkedOrders.add(order);
} else if (search.getOrganisationalUnit() != null &&
!matchesSearchParam(order.getOrganisationalUnit(), search.getOrganisationalUnit())) {
ineligibleUnlinkedOrders.add(order);
}
}
// remove all ineligible orders
unlinkedOrders.removeAll(ineligibleUnlinkedOrders);
unlinkedOrders.removeAll(deletedOrders);
unlinkedOrdersMap.put(UnlinkedOrdersType.ELIGIBLE, unlinkedOrders);
unlinkedOrdersMap.put(UnlinkedOrdersType.INELIGIBLE, ineligibleUnlinkedOrders);
return unlinkedOrdersMap;
}
protected abstract String getEntityName();
@Override
public T findByNumber(String number) {
Object o = ObjectUtil.first(findByNamedQueryAndNamedParam("order.findByNumber", "number", number));
if (o instanceof HibernateProxy) {
return (T) (HibernateUtils.initializeAndUnproxy(o));
}
return (T) o;
}
@Override
public T findByNumberAndReference(String orderNumber, String orderReference) {
ObjectUtil.validateNotNull(orderNumber, "Order Number");
ObjectUtil.validateNotNull(orderReference, "Order Reference");
return (T) ObjectUtil.first(findByNamedQueryAndNamedParam("order.findByNumberAndReference", new String[]{"orderNumber", "orderReference"},
new Object[]{orderNumber, orderReference}));
}
@Override
public List<T> findAllByShipment(Shipment shipment) {
@SuppressWarnings("unchecked")
List<T> orders = (List<T>) getNamedQueryAndNamedParam("order.findAllByShipment", "id", shipment.getId());
return orders;
}
@Override
public List<T> search(S search) {
return searchByCriteria(search);
}
@Override
public long count(S search) {
return countByCriteria(search);
}
/**
* https://connect.devstream.net/display/Dev/Orders+not+yet+Consigned+Report
* .
*
* @param search
* @return
*/
@Override
public List<OrderNotConsignedDTO> notConsignedSearch(S search) {
Criteria notConsignedCriteria = createNotConsignedCriteria(search);
// The Orders not Consigned Report should be sorted by Order Created
// Date (Latest date appearing last in the search result).
if (search.getSearchMetaParams() != null) {
notConsignedCriteria.setMaxResults(search.getSearchMetaParams().getRowCount());
notConsignedCriteria.setFirstResult(search.getSearchMetaParams().getRowIndex());
notConsignedCriteria.addOrder(org.hibernate.criterion.Order.desc("created"));
} else {
notConsignedCriteria.addOrder(org.hibernate.criterion.Order.desc("created"));
}
return notConsignedCriteria.list();
}
public Criteria createNotConsignedCriteria(S search) {
Criteria criteria = getSession().createCriteria(Order.class);
criteria.createAlias("shippingInformation", "si", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("si.freightForwarder", "ff", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("si.incoterm", "it", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("si.placeOfLoading", "pol", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("si.placeOfDischarge", "pod", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("si.finalDestination", "fd", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("paymentTerm", "pt", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("supplier", "sup", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("sup.supplier", "osup", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("sup.organisationalUnit", "sou", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("buyer", "b", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("organisationalUnit", "ou", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("currency", "cur", CriteriaSpecification.LEFT_JOIN);
criteria.add(Restrictions.eq(ELC_ORDER, search.isElc()));
// optional
if (search.getOrderReference() != null) {
criteria.add(Restrictions.ilike(ORDER_REFERENCE, search.getOrderReference(), MatchMode.ANYWHERE));
}
applyOrgUnitCriteria(search, criteria, "organisationalUnit");
if (((PurchaseOrderSearch) search).getSupplier() != null) {
criteria.add(Restrictions.eq(SET_SUPPLIER, ((PurchaseOrderSearch) search).getSupplier()));
}
if (((PurchaseOrderSearch) search).getBuyer() != null) {
criteria.add(Restrictions.eq(BUYER, ((PurchaseOrderSearch) search).getBuyer()));
}
if (search.getShippingMode() != null) {
criteria.add(Restrictions.eq("si.shippingMode", search.getShippingMode()));
}
if (search.getPlaceOfLoading() != null) {
criteria.add(Restrictions.eq("si.placeOfLoading", search.getPlaceOfLoading()));
}
// mandatory
criteria.add(Restrictions.isNull("consignment"));
criteria.add(Restrictions.in(STATE, new OrderState[]{OrderState.UNFINALISED}));
criteria.add(Restrictions.isNotNull("lineItems"));
criteria.setProjection(Projections.projectionList().add(Projections.property("id"), "0").add(Projections.property("orderReference"), "1")
.add(Projections.property("si.shippingReference"), "2").add(Projections.property("proFormaReference"), "3")
.add(Projections.property("ff.name"), "4").add(Projections.property("orderDates.latestShipmentDate"), "5")
.add(Projections.property("si.shippingMode"), "6").add(Projections.property("it.code"), "7")
.add(Projections.property("pt.name"), "8").add(Projections.property("pol.name"), "9").add(Projections.property("pod.name"), "10")
.add(Projections.property("fd.name"), "11").add(Projections.property("ou.name"), "12").add(Projections.property("osup.name"), "13")
.add(Projections.property("b.firstName"), "14").add(Projections.property("b.lastName"), "15")
.add(Projections.property("cur.code"), "16").add(Projections.property("created"), "17"));
criteria.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
OrderNotConsignedDTO result = new OrderNotConsignedDTO();
result.setId((Long) tuple[0]);
result.setOrderId((Long) tuple[0]);
result.setOrderReference((String) tuple[1]);
result.setShippingReference((String) tuple[2]);
result.setProFormaReference((String) tuple[3]);
result.setFreightForwarder((String) tuple[4]);
result.setLatestShipmentDate((Date) tuple[5]);
result.setShippingMode("" + tuple[6]);
result.setIncoTerm((String) tuple[7]);
result.setPaymentTerm((String) tuple[8]);
result.setPlaceOfLoading((String) tuple[9]);
result.setPlaceOfDischarge((String) tuple[10]);
result.setFinalDestination((String) tuple[11]);
result.setDivision((String) tuple[12]);
result.setSupplierName((String) tuple[13]);
result.setBuyerName(tuple[14] + " " + tuple[15]);
List<List<BigDecimal>> quantities = getItemQuantityVolumeWeight(result.getId());
BigDecimal totalVolume = BigDecimal.ZERO;
BigDecimal totalWeight = BigDecimal.ZERO;
for (List<BigDecimal> values : quantities) {
BigDecimal quantity = values.get(0);
BigDecimal volume = values.get(1);
BigDecimal weight = values.get(2);
if (volume != null) {
totalVolume = totalVolume.add(quantity.multiply(volume));
}
if (weight != null) {
totalWeight = totalWeight.add(quantity.multiply(weight));
}
}
result.setTotalVolume(totalVolume);
result.setTotalWeight(totalWeight);
List<String> orderComments = getOrderComments(result.getId());
StringBuilder comments = null;
boolean isFirst = true;
for (String orderComment : orderComments) {
if (isFirst) {
comments = new StringBuilder("");
comments.append(orderComment);
isFirst = false;
} else {
comments.append("," + orderComment);
}
}
if (!comments.toString().equals("null")) {
result.setComments(comments.toString());
}
result.setConsignKey(tuple[16] + "-" + result.getIncoTerm() + "-" + result.getShippingMode() + "-" + result.getPlaceOfLoading()
+ "-" + result.getPlaceOfDischarge() + "-" + result.getFreightForwarder());
result.setOrderType(OrderType.PURCHASE_ORDER);
result.setCreated((Date) tuple[17]);
return result;
}
@Override
public List transformList(List collection) {
return collection;
}
});
return criteria;
}
protected void applyOrgUnitCriteria(S search, Criteria criteria, String propertyName) {
Set<OrganisationalUnit> organisationalUnits = getOrganisationalUnits(search);
if (!organisationalUnits.isEmpty()) {
criteria.add(Restrictions.in(propertyName, organisationalUnits));
}
}
protected Set<OrganisationalUnit> getOrganisationalUnits(S 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;
}
public List<String> getOrderComments(Long id) {
String hqlOrderComments = "select oc.reason from Order o left join o.comments oc " + "where o.id = :id";
Query orderCommentsQuery = getSessionCustom().createQuery(hqlOrderComments);
orderCommentsQuery.setParameter("id", id);
orderCommentsQuery.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
return tuple[0];
}
@Override
public List transformList(List collection) {
return collection;
}
});
return orderCommentsQuery.list();
}
public List<List<BigDecimal>> getItemQuantityVolumeWeight(Long id) {
String hqlLineItems = "select li.unitQuantity, li.unitVolume, li.unitWeight from Order o " + "left join o.lineItems li where o.id = :id";
Query lineItemsQuery = getSessionCustom().createQuery(hqlLineItems);
lineItemsQuery.setParameter("id", id);
lineItemsQuery.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
List<BigDecimal> data = new ArrayList<BigDecimal>();
data.add((BigDecimal) tuple[0]);
data.add((BigDecimal) tuple[1]);
data.add((BigDecimal) tuple[2]);
return data;
}
@Override
public List transformList(List collection) {
return collection;
}
});
return lineItemsQuery.list();
}
@Override
public long notConsignedCount(S search) {
Criteria criteria = createNotConsignedCriteria(search);
criteria.setProjection(Projections.rowCount());
long count = ((Long) criteria.uniqueResult()).longValue();
return count;
}
/**
* TODO: revisit hard coded '10000' field.
*
* @param search
* @return
*/
@Override
public List<T> awaitingCostingSearch(S search) {
// pagination off, ordering on.
if (search.getSearchMetaParams() != null) {
search.getSearchMetaParams().setOrderBy("created");
search.getSearchMetaParams().setAsc(true);
}
// Query query = createQuery(search, false, awaitingCostingCriteriaValues(search));
//ensures that there is no duplicates
DetachedCriteria criteria1 = awaitingCostingCriteria(search);
criteria1.setProjection(Projections.distinct(Projections.property("id")));
DetachedCriteria criteria = DetachedCriteria.forClass(PurchaseOrder.class);
criteria.add(Property.forName("id").in(criteria1));
return getExecutableCriteriaList(criteria, search.getSearchMetaParams());
}
@Override
public long awaitingCostingCount(S search) {
DetachedCriteria criteria = awaitingCostingCriteria(search);
return getExecutableCriteriaCount(criteria, Projections.countDistinct("id"));
}
@Deprecated
protected void addSearchAwaitingSignoffRestrictions(DetachedCriteria criteria, S orderSearch) {
criteria.add(Restrictions.in("state", new OrderState[]{OrderState.FINALISED}));
CriteriaBuilder.addEqRestriction(criteria, "organisationalUnit", orderSearch.getOrganisationalUnit());
}
private DetachedCriteria awaitingCostingCriteria(S search) {
DetachedCriteria criteria = DetachedCriteria.forClass(PurchaseOrder.class);
criteria.add(Restrictions.in(STATE, new OrderState[]{OrderState.AWAITING_COSTING}));
criteria.add(Restrictions.eq(ELC_ORDER, false));
Set<OrganisationalUnit> organisationalUnits = getOrganisationalUnits(search);
if (!organisationalUnits.isEmpty()) {
criteria.add(Restrictions.in(ORGANISATIONAL_UNIT, organisationalUnits));
}
final PurchaseOrderSearch search1 = (PurchaseOrderSearch) search;
if (search1.getBuyer() != null) {
criteria.add(Restrictions.eq(BUYER, search1.getBuyer()));
}
if (search1.getSupplier() != null) {
criteria.add(Restrictions.eq(SET_SUPPLIER, search1.getSupplier()));
}
if (search1.getLatestShipmentDateRange() != null) {
if (search.getLatestShipmentDateRange().getFrom() != null) {
criteria.add(Restrictions.ge("orderDates.latestShipmentDate", search1.getLatestShipmentDateRange().getFrom()));
}
if (search.getLatestShipmentDateRange().getTo() != null) {
criteria.add(Restrictions.le("orderDates.latestShipmentDate", search1.getLatestShipmentDateRange().getTo()));
}
}
if (search.getAwaitingCostingDateEnd() != null) {
final Date awaitingCostingDateEnd = search.getAwaitingCostingDateEnd();
lastEventDateFilter(criteria, awaitingCostingDateEnd);
}
if (null != search.getStyleReference() && search.getStyleReference().trim().length() > 0 && null != search.getStyleDescription()
&& search.getStyleDescription().trim().length() > 0) {
criteria.createCriteria("lineItems").add(Restrictions.ilike("styleReference", search.getStyleReference().trim(), MatchMode.ANYWHERE))
.add(Restrictions.ilike("styleDescription", search.getStyleDescription().trim(), MatchMode.ANYWHERE));
} else if (null != search.getStyleReference() && search.getStyleReference().trim().length() > 0) {
criteria.createCriteria("lineItems").add(Restrictions.ilike("styleReference", search.getStyleReference().trim(), MatchMode.ANYWHERE));
} else if (null != search.getStyleDescription() && search.getStyleDescription().trim().length() > 0) {
criteria.createCriteria("lineItems")
.add(Restrictions.ilike("styleDescription", search.getStyleDescription().trim(), MatchMode.ANYWHERE));
}
return criteria;
}
private void lastEventDateFilter(DetachedCriteria criteria, Date awaitingCostingDateEnd) {
criteria.add(Restrictions.sqlRestriction(String.format(" (select max (e.createdatetime) as d from orders_ordersevent ooe " +
"join ordersevent e on (e.id=ooe.events_id) where ooe.orders_id={alias}.id )<='%s' ",
DateUtils.toT_YMDFormat(awaitingCostingDateEnd))));
}
protected DetachedCriteria awaitingSignoffCriteriaValues(S search) {
DetachedCriteria criteria = DetachedCriteria.forClass(PurchaseOrder.class);
criteria.add(Restrictions.eq(ELC_ORDER, search.isElc()));
if (search.getState() != null) {
criteria.add(Restrictions.eq(STATE, search.getState()));
} else {
criteria.add(Restrictions.in(STATE, new OrderState[]{OrderState.FINALISED, OrderState.TOLERANCE_EXCEEDED}));
}
Set<OrganisationalUnit> organisationalUnits = getOrganisationalUnits(search);
if (!organisationalUnits.isEmpty()) {
criteria.add(Restrictions.in(ORGANISATIONAL_UNIT, organisationalUnits));
}
if (search.getConsignmentState() != null) {
criteria.createCriteria("consignment").add(Restrictions.eq("state", search.getConsignmentState()));
}
final PurchaseOrderSearch search1 = (PurchaseOrderSearch) search;
if (search1.getBuyer() != null) {
criteria.add(Restrictions.eq(BUYER, search1.getBuyer()));
}
if (search1.getSupplier() != null) {
criteria.add(Restrictions.eq(SET_SUPPLIER, search1.getSupplier()));
}
if (search1.getFinalDestination() != null) {
criteria.createAlias("shippingInformation", "si");
criteria.add(Restrictions.eq("si.finalDestination", search1.getFinalDestination()));
}
if (search1.getLatestShipmentDateRange() != null) {
if (search.getLatestShipmentDateRange().getFrom() != null) {
criteria.add(Restrictions.ge("orderDates.latestShipmentDate", search1.getLatestShipmentDateRange().getFrom()));
}
if (search.getLatestShipmentDateRange().getTo() != null) {
criteria.add(Restrictions.le("orderDates.latestShipmentDate", search1.getLatestShipmentDateRange().getTo()));
}
}
if (search.getFinalisationEndDate() != null) {
lastEventDateFilter(criteria, search.getFinalisationEndDate());
}
if (null != search.getStyleReference() && search.getStyleReference().trim().length() > 0 && null != search.getStyleDescription()
&& search.getStyleDescription().trim().length() > 0) {
criteria.createCriteria("lineItems").add(Restrictions.ilike("styleReference", search.getStyleReference().trim(), MatchMode.ANYWHERE))
.add(Restrictions.ilike("styleDescription", search.getStyleDescription().trim(), MatchMode.ANYWHERE));
} else if (null != search.getStyleReference() && search.getStyleReference().trim().length() > 0) {
criteria.createCriteria("lineItems").add(Restrictions.ilike("styleReference", search.getStyleReference().trim(), MatchMode.ANYWHERE));
} else if (null != search.getStyleDescription() && search.getStyleDescription().trim().length() > 0) {
criteria.createCriteria("lineItems")
.add(Restrictions.ilike("styleDescription", search.getStyleDescription().trim(), MatchMode.ANYWHERE));
}
return criteria;
}
protected Collection<CriteriaValue> notPackedCriteriaValues(S search) {
Collection<CriteriaValue> fields = new LinkedHashSet<CriteriaValue>();
if (((PurchaseOrderSearch) search).getShipmentCreatedFrom() != null && ((PurchaseOrderSearch) search).getShipmentCreatedTo() != null) {
fields.add(CriteriaValue.gt(SHIPMENT_CREATION_DATE, ((PurchaseOrderSearch) search).getShipmentCreatedFrom()));
fields.add(CriteriaValue.lt(SHIPMENT_CREATION_DATE, ((PurchaseOrderSearch) search).getShipmentCreatedTo()));
}
fields.add(CriteriaValue.eq(ELC_ORDER, search.isElc()));
if (search.getFreightForwarder() != null) {
ServiceProvider freightForwarder = search.getFreightForwarder();
fields.add(CriteriaValue.eq(FREIGHT_FORWARDER, freightForwarder));
}
return fields;
}
@Override
public List<T> awaitingSignoffSearch(S search) {
DetachedCriteria criteria1 = awaitingSignoffCriteriaValues(search);
criteria1.setProjection(Projections.distinct(Projections.property("id")));
DetachedCriteria criteria = DetachedCriteria.forClass(PurchaseOrder.class);
criteria.add(Property.forName("id").in(criteria1));
return getExecutableCriteriaList(criteria, search.getSearchMetaParams());
}
@Override
public long awaitingSignoffCount(S search) {
DetachedCriteria criteria = awaitingSignoffCriteriaValues(search);
return getExecutableCriteriaCount(criteria, Projections.countDistinct("id"));
}
/**
* Create the sql command that will perform the required search:. 1) order
* must be linked to a shipment 2) order must fulfill the freightforwarder
* criteria or not 3) shipment must have been created between supplied dates
* 4) shipment's packing list must not contain the order in question
*
* @param search
* @param count
* @return
*/
private String createOrdersNotPackedSQLCommand(S search, boolean count) {
long one_day = 1000 * 60 * 60 * 24;
SimpleDateFormat format = new SimpleDateFormat();
format.applyPattern("yyyy-MM-dd");
// adjust the createdTo date, add one day to make it inclusive.
Date createdFrom = ((PurchaseOrderSearch) search).getShipmentCreatedFrom();
Date createdTo = new Date(((PurchaseOrderSearch) search).getShipmentCreatedTo().getTime() + one_day);
String sqlCommand =
"select o.number from orders o " + "join shippinginformation si on si.id = o.shippinginformation_id "
+ "join serviceprovider sp on sp.id = si.freightforwarder_id " + "join consignment c on c.id = o.consignment_id "
+ "join shipment s on s.id = c.shipment_id where s.id is not null and o.orderreference not in ("
+ "select plo.orderreference from packinglistorder plo "
+ "join packinglistcontainer plc on plc.id = plo.packinglistcontainer_id "
+ "join packinglist pl on pl.id = plc.packinglist_id " + "join shipment s on s.packinglist_id = pl.id) ";
sqlCommand += "and s.created between '" + format.format(createdFrom) + "' and '" + format.format(createdTo) + "'";
if (search.getFreightForwarder() != null) {
sqlCommand += " and sp.id = '" + search.getFreightForwarder().getId() + "'" + " order by s.created asc";
}
Collection orgIds = Collections.EMPTY_LIST;
if (search.isFilteredByUserOrg()) {
Set<OrganisationalUnit> organisationalUnits = MultiTenantUtil.getActiveUser().getOrganisationalUnits();
if (organisationalUnits.isEmpty()) {
throw new IllegalArgumentException("User org units not set");
}
orgIds = organisationalUnits.stream()
.map(organisationalUnit -> organisationalUnit.getId()).collect(Collectors.toList());
} else if (search.getOrganisationalUnit() != null) {
orgIds = Collections.singletonList(search.getOrganisationalUnit().getId());
}
if (!orgIds.isEmpty()) {
sqlCommand += String.format(" and o.organisationalunit_id in (%s)", StringUtils.join(orgIds, ","));
}
if (count) {
sqlCommand = sqlCommand.replace("select o.number", "select count(o.number)");
sqlCommand = sqlCommand.replace(" order by s.created asc", "");
}
log.debug("xxxxxxxxxxxxxxxx" + sqlCommand);
return sqlCommand;
}
/**
* Search method creates and executes a query that will return all orders
* that are not on a container of a packing list. See
* https://connect.devstream.net/display/Dev/Orders+not+packed.
*
* @param search
* @return
*/
@Override
public List<Order> notPackedSearch(S search) {
String sqlCommand = createOrdersNotPackedSQLCommand(search, false);
// obtain the order numbers.
Query query = getSessionCustom().createSQLQuery(sqlCommand).setFirstResult(0);
List orderNumberList = getQueryList(query, search.getSearchMetaParams());
// obtain the orders through the order numbers.
List<Order> orderList = Collections.EMPTY_LIST;
if (!orderNumberList.isEmpty()) {
orderList = new ArrayList<Order>();
for (String orderNumber : (List<String>) orderNumberList) {
Order order = findByNumber(orderNumber);
orderList.add(order);
}
}
return orderList;
}
@Override
public long notPackedCount(S search) {
String sqlCommand = createOrdersNotPackedSQLCommand(search, true);
List idList = getSessionCustom().createSQLQuery(sqlCommand).list();
return Long.parseLong(idList.get(0) + "");
}
/**
* Notice the second state check: 1) on order and 2) on order event. Double
* results would be returned without the second check because of the
* ordering done by order event creation date.
*
* @param criteria
* @param search
*/
public void setOrdersDeletedCriteriaRestrictions(Criteria criteria, S search) {
criteria.add(Restrictions.eq(STATE, OrderState.DELETED));
criteria.createAlias(this.EVENTS, "e");
criteria.add(Restrictions.in("e.eventType", OrderEventType.DELETED, OrderEventType.REJECTED));
if (search.getOrderReference() != null) {
criteria.add(Restrictions.ilike(ORDER_REFERENCE, search.getOrderReference(), MatchMode.ANYWHERE));
}
if (search.getProFormaReference() != null) {
criteria.add(Restrictions.ilike(PROFORMA_REFERENCE, search.getProFormaReference(), MatchMode.ANYWHERE));
}
if (search.getShippingReference() != null) {
criteria.createAlias(this.SHIPPING_INFORMATION, "si");
criteria.add(Restrictions.ilike("si.shippingReference", search.getShippingReference(), MatchMode.ANYWHERE));
}
if (((PurchaseOrderSearch) search).getSupplier() != null) {
criteria.createAlias("supplier", "sup");
criteria.add(Restrictions.eq("supplier", ((PurchaseOrderSearch) search).getSupplier()));
}
if (((PurchaseOrderSearch) search).getBuyer() != null) {
criteria.add(Restrictions.eq(BUYER, ((PurchaseOrderSearch) search).getBuyer()));
}
Set<OrganisationalUnit> organisationalUnits = getOrganisationalUnits(search);
if (!organisationalUnits.isEmpty()) {
criteria.add(Restrictions.in(ORGANISATIONAL_UNIT, organisationalUnits));
}
if (search.getFromDate() != null && search.getToDate() != null) {
criteria.add(Restrictions.and(Restrictions.in
("e.eventType", OrderEventType.DELETED, OrderEventType.REJECTED),
Restrictions.between("e.createDateTime", search.getFromDate(), search.getToDate())));
} else if (search.getFromDate() != null) {
criteria.add(Restrictions.and(Restrictions.in
("e.eventType", OrderEventType.DELETED, OrderEventType.REJECTED),
Restrictions.ge("e.createDateTime", search.getFromDate())));
} else if (search.getToDate() != null) {
criteria.add(Restrictions.and(Restrictions.in
("e.eventType", OrderEventType.DELETED, OrderEventType.REJECTED),
Restrictions.le("e.createDateTime", search.getToDate())));
}
}
@Override
public List<T> deletedSearch(S search) {
Criteria criteria = getSessionCustom().createCriteria(Order.class);
setOrdersDeletedCriteriaRestrictions(criteria, search);
criteria.setProjection(Projections.property("id"));
if (search.getSearchMetaParams() != null) {
if (search.getSearchMetaParams().getOrderBy() == null) {
criteria.addOrder(org.hibernate.criterion.Order.asc("e.createDateTime"));
} else {
criteria.addOrder(org.hibernate.criterion.Order.asc(search.getSearchMetaParams().getOrderBy()));
}
} else {
criteria.addOrder(org.hibernate.criterion.Order.asc("e.createDateTime"));
}
List<Long> list = criteria.list();
if (CollectionUtils.isNotEmpty(list)) {
Criteria criteria2 = getSessionCustom().createCriteria(Order.class);
Set<Long> collect = new LinkedHashSet<>(list);
criteria2.add(Restrictions.in("id", collect));
if (search.getSearchMetaParams() != null) {
criteria2.setMaxResults(search.getSearchMetaParams().getRowCount());
criteria2.setFirstResult(search.getSearchMetaParams().getRowIndex());
}
return criteria2.list();
} else {
return Collections.EMPTY_LIST;
}
}
@Override
public long deletedCount(S search) {
Criteria criteria = getSessionCustom().createCriteria(Order.class);
setOrdersDeletedCriteriaRestrictions(criteria, search);
criteria.setProjection(Projections.countDistinct("id"));
return ((Long) criteria.uniqueResult()).longValue();
}
// TODO Refactor this to a PlannedSettlementRepository. It should be
// applicable to PlannedSettlements on both orders and invoice level.
@Override
public BigDecimal getTotalPlannedSettlementAmount(T order) {
Object uniqueResult =
getCurrentSession().createCriteria(Order.class, "ord").createAlias("plannedSettlements", "ps")
.add(Restrictions.eq("ord.id", order.getId())).setProjection(Projections.projectionList().add(Projections.sum("ps.amount")))
.uniqueResult();
if (uniqueResult == null) {
return BigDecimal.ZERO.setScale(4);
}
return (BigDecimal) uniqueResult;
}
@Override
public BigDecimal getTotalPlannedSettlementCostingAmount(T order) {
String hql = "select sum(ps.amount * ps.forwardRate) from Order ord join ord.plannedSettlements as " + "ps where ord.id = :orderId";
Object uniqueResult = getCurrentSession().createQuery(hql).setParameter("orderId", order.getId()).uniqueResult();
if (uniqueResult == null) {
return BigDecimal.ZERO.setScale(4);
}
return (BigDecimal) uniqueResult;
}
@Override
public boolean hasSampleLineItem(T order) {
return hasSpecialItem(order, AdditionalLineItemType.SAMPLE);
}
@Override
public boolean hasSparePartLineItem(T order) {
return hasSpecialItem(order, AdditionalLineItemType.SPARE_PART);
}
/**
* Determines if the supplied order already has an additional line item of
* the type supplied.
*
* <strong>Note</strong>: This method should only be used for the types
* {@code AdditionalLineItemType#SAMPLE} and
* {@code AdditionalLineItemType#SPARE_PART}.
*
* @param order the order to check if it already has an item of the supplied
* type
* @param additionalLineItemType the additional item type, only SAMPLE and SPARE_PART are
* handled
* @return true if the supplied order has an item of the type supplied,
* false otherwise
* @throws IllegalArgumentException if the type supplied is not one of SAMPLE or SPARE_PART
* @see <[CDATA[https://connect.devstream.net/display/Dev/Samples+and+Spare+
* Parts]]>
*/
private boolean hasSpecialItem(T order, AdditionalLineItemType additionalLineItemType) {
if (AdditionalLineItemType.SAMPLE != additionalLineItemType && AdditionalLineItemType.SPARE_PART != additionalLineItemType) {
throw new IllegalArgumentException("SAMPLE or SPARE_PART types only");
}
Query query =
getCurrentSession().createQuery(
"select count(*) from LineItem i where i.additional = true and i.code = :type and i.order.id = :orderId");
int count =
((Long) query.setParameter("orderId", order.getId()).setParameter("type", additionalLineItemType.getName()).iterate().next())
.intValue();
return count == 1;
}
public void addDefaultSearchOrder(DetachedCriteria criteria) {
// Default ordering, can be overridden by the SearchMetaParams
criteria.addOrder(org.hibernate.criterion.Order.desc("number"));
}
protected String notShippedSuffix() {
return "consignment is not null and consignment.shipment is null and state in ('" + OrderState.SIGNED_OFF + "')";
}
/**
* As per TTG-1508. Method returns the container usage (number at POD) for
* an order and a specific container type.
*
* @param orderId
* @param containerTypeCode
* @return
*/
public BigDecimal getContainerUsageForOrder(Long orderId, String containerTypeCode) {
String hql =
"select cu.numberAtPOD from ContainerUsage cu where cu.order.id = :id " + "and cu.container.containerType.code = :containerTypeCode";
Query query = getSession().createQuery(hql);
query.setParameter("id", orderId);
query.setParameter("containerTypeCode", containerTypeCode);
BigDecimal totalTeu = BigDecimal.ZERO;
List<BigDecimal> list = query.list();
for (BigDecimal teu : list) {
totalTeu = totalTeu.add(teu);
}
return totalTeu;
}
protected abstract void setOrderPlannedSettlementSpecificfields(S search, DetachedCriteria criteria);
@Override
public List<PfOrderNotShippedSearchResult> notShippedSearch(S search) {
Query criteria = NotShippedReportHelper.createQueryNotShippedTransformed(getSession(), (PurchaseOrderSearch) search, false);
if (search.getSearchMetaParams() == null) {
//criteria.addOrder(org.hibernate.criterion.Order.asc("orderDates.latestShipmentDate"));
} else {
//criteria.addOrder(org.hibernate.criterion.Order.asc("orderDates.latestShipmentDate"));
criteria.setFirstResult(search.getSearchMetaParams().getRowIndex());
criteria.setMaxResults(search.getSearchMetaParams().getRowCount());
}
return criteria.list();
}
@Override
public List<OrderConfirmationSearchResult> insuranceReportSearchTransformed(S search) {
Criteria criteria = createOrderConfirmationCriteriaTransformed(search);
if (search.getSearchMetaParams() != null) {
SearchMetaParams searchMetaParams = search.getSearchMetaParams();
criteria.setMaxResults(searchMetaParams.getRowCount());
criteria.setFirstResult(searchMetaParams.getRowIndex());
criteria.addOrder(org.hibernate.criterion.Order.asc("o.orderDates.latestShipmentDate"));
} else {
criteria.addOrder(org.hibernate.criterion.Order.asc("o.orderDates.latestShipmentDate"));
}
List<OrderConfirmationSearchResult> list = criteria.list();
return list;
}
@Override
public List<OrderConfirmationSearchResult> confirmationSearchTransformed(S search) {
Criteria criteria = createOrderConfirmationCriteriaTransformed(search);
if (search.getSearchMetaParams() != null) {
SearchMetaParams searchMetaParams = search.getSearchMetaParams();
criteria.setMaxResults(searchMetaParams.getRowCount());
criteria.setFirstResult(searchMetaParams.getRowIndex());
criteria.addOrder(org.hibernate.criterion.Order.asc("o.orderDates.goodsReceivedDate"));
} else {
criteria.addOrder(org.hibernate.criterion.Order.asc("o.orderDates.goodsReceivedDate"));
}
List<OrderConfirmationSearchResult> list = criteria.list();
return list;
}
private Criteria createOrderConfirmationCriteriaTransformed(S search) {
Criteria criteria = getSession().createCriteria(LineItem.class);
criteria.createAlias("order", "o");
criteria.createAlias("o.currency", "cur");
criteria.createAlias("o.shippingInformation", "si");
criteria.createAlias("si.freightForwarder", "ff");
criteria.createAlias("o.organisationalUnit", "ou");
criteria.createAlias("ou.parent", "oup");
criteria.createAlias("o.buyer", "b");
criteria.createAlias("o.supplier", "sup");
criteria.createAlias("sup.supplier", "osup");
criteria.createAlias("sup.organisationalUnit", "sou");
criteria.createAlias("o.consignment", "c");
criteria.setProjection(Projections
.projectionList()
.add(Projections.property("c.reference"), "consignmentReference")
.add(Projections.property("o.orderReference"), "orderReference")
.add(Projections.property("code"), "itemReference")
.add(Projections.property("osup.name"), "supplierName")
// get name
.add(Projections.property("unitPrice"), "unitPrice")
.add(Projections.property("cur.code"), "currencyCode")
.add(Projections.property("unitQuantity"), "itemUnitQuantity")
.add(Projections.property("o.orderDates.latestShipmentDate"), "latestShipmentDate")
.add(Projections.property("o.orderDates.earliestShipmentDate"), "earliestShipmentDate")
.add(Projections.property("ou.name"), "businessUnit")
// get parent name
.add(Projections.property("oup.name"), "division").add(Projections.property("b.firstName"),
"buyerFirstName")
.add(Projections.property("b.lastName"), "buyerLastName").add(Projections.property("ff.name"), "freightForwarderName")
.add(Projections.property("o.lspBookingReference"), "lspBookingReference").add(Projections.property("o.state"),
"state")
.add(Projections.property("o.id"), "id").add(Projections.property("si.shippingReference"),
"shippingReference")
.add(Projections.property("styleReference"), "styleReference").add(Projections.property("styleDescription"),
"styleDescription")
);
criteria.add(Restrictions.eq(ELC_ORDER, search.isElc()));
if (search.getOrderReference() != null) {
criteria.add(Restrictions.ilike("o.orderReference", search.getOrderReference(), MatchMode.ANYWHERE));
}
if (search.getShippingReference() != null) {
criteria.add(Restrictions.ilike("si.shippingReference", search.getShippingReference(), MatchMode.ANYWHERE));
}
Set<OrganisationalUnit> organisationalUnits = getOrganisationalUnits(search);
if (!organisationalUnits.isEmpty()) {
criteria.add(Restrictions.in("o.organisationalUnit", organisationalUnits));
}
if (((PurchaseOrderSearch) search).getBuyer() != null) {
criteria.add(Restrictions.eq("o.buyer", ((PurchaseOrderSearch) search).getBuyer()));
}
if (search.getShippingMode() != null) {
criteria.add(Restrictions.eq("si.shippingMode", search.getShippingMode()));
}
if (search.getFreightForwarder() != null) {
criteria.add(Restrictions.eq("si.freightForwarder", search.getFreightForwarder()));
}
if (search.getState() != null) {
criteria.add(Restrictions.eq("o.state", search.getState()));
} else {
OrderState[] acceptableOrderStates = {OrderState.SIGNED_OFF, OrderState.AWAITING_LSP_SIGNOFF, OrderState.AWAITING_LSP_UNSIGNOFF,
OrderState.LSP_REJECTED, OrderState.LSP_REJECTED_UNSIGNOFF, OrderState.BOOKED_IN, OrderState.FREIGHT_RECEIVED};
criteria.add(Restrictions.in("o.state", acceptableOrderStates));
}
if (search.getLatestShipmentDateRange().getFrom() != null && search.getLatestShipmentDateRange().getTo() != null) {
DateRange latestShipmentDateRange = search.getLatestShipmentDateRange();
criteria.add(Restrictions.between("o.orderDates.latestShipmentDate", latestShipmentDateRange.getFrom(), latestShipmentDateRange.getTo()));
}
if (search.getEarliestShipmentDateRange().getFrom() != null
&& search.getEarliestShipmentDateRange().getTo() != null) {
DateRange earliestShipmentDateRange = search.getEarliestShipmentDateRange();
criteria.add(Restrictions.between("o.orderDates.earliestShipmentDate", earliestShipmentDateRange.getFrom(),
earliestShipmentDateRange.getTo()));
}
if (((PurchaseOrderSearch) search).getContainerPickupDateRange() != null) {
if (((PurchaseOrderSearch) search).getContainerPickupDateRange().getFrom() != null
&& ((PurchaseOrderSearch) search).getContainerPickupDateRange().getTo() != null) {
DateRange containerPickupDateRange = ((PurchaseOrderSearch) search).getContainerPickupDateRange();
criteria.createAlias("c.shipment", "s");
criteria.createAlias("s.containers", "cnt");
criteria.add(Restrictions.between("cnt.containerDates.pickUpDate", containerPickupDateRange.getFrom(),
containerPickupDateRange.getTo()));
}
}
if (null != search.getStyleReference() && search.getStyleReference().trim().length() > 0) {
criteria.add(Restrictions.ilike("styleReference", search.getStyleReference().trim(), MatchMode.ANYWHERE));
}
if (null != search.getStyleDescription() && search.getStyleDescription().trim().length() > 0) {
criteria.add(Restrictions.ilike("styleDescription", search.getStyleDescription().trim(), MatchMode.ANYWHERE));
}
criteria.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] objects, String[] strings) {
OrderConfirmationSearchResult result = new OrderConfirmationSearchResult();
result.setConsignmentReference("" + objects[0]);
result.setOrderReference("" + objects[1]);
result.setItemReference("" + objects[2]);
result.setSupplierName("" + objects[3]);
BigDecimal unitPrice = new BigDecimal("" + objects[4]);
result.setInvoiceCurrency("" + objects[5]);
BigDecimal itemQuantity = ((BigDecimal) objects[6]).setScale(2, BigDecimal.ROUND_HALF_UP);
result.setItemQuantity(itemQuantity);
result.setItemInvoiceValue(itemQuantity.multiply(unitPrice));
result.setLatestShipmentDate((Date) objects[7]);
result.setEarliestShipmentDate((Date) objects[8]);
result.setDivisionName("" + objects[9]);
result.setBusinessUnitName("" + objects[10]);
result.setBuyer(objects[11] + " " + objects[12]);
result.setFreightForwarder(objects[13] + "");
result.setLspBookingReference(objects[14] != null ? "" + objects[14] : "");
result.setStatus("" + objects[15]);
result.setOrderId(new Long("" + objects[16]));
Order order = getById(result.getOrderId());
List<OrdersEvent> events = order.getEvents();
String rejectReason = "";
if (events != null) {
for (OrdersEvent event : events) {
if (event.getEventType().equals(OrderEventType.LSP_REJECTED_SIGNOFF)
|| event.getEventType().equals(OrderEventType.LSP_REJECTED_UNSIGNOFF)) {
rejectReason = event.getReason();
}
}
}
result.setRejectReason(rejectReason);
if (null != objects[17]) {
result.setShippingReference("" + objects[17]);
} else {
result.setShippingReference("");
}
if (null != objects[18]) {
result.setStyleReference("" + objects[18]);
} else {
result.setStyleReference("");
}
if (null != objects[19]) {
result.setStyleDescription("" + objects[19]);
} else {
result.setStyleDescription("");
}
return result;
}
@Override
public List transformList(List list) {
return list;
}
});
return criteria;
}
protected Collection<CriteriaValue> letterOfCreditCriteriaValues(S search) {
Collection<CriteriaValue> fields = new LinkedHashSet<CriteriaValue>();
fields.add(CriteriaValue.isNull("letterOfCredit"));
if (search.getState() != null) {
fields.add(CriteriaValue.eq(STATE, search.getState()));
} else {
fields.add(CriteriaValue.in(STATE, LetterOfCreditUtils.LC_ELIGIBLE_ORDER_STATES));
}
fields.add(CriteriaValue.in(PAYMENT_METHOD_CODE, LetterOfCreditUtils.LC_ELIGIBLE_ORDER_PAYMENT_METHOD_CODES));
fields.add(CriteriaValue.like(ORDER_REFERENCE, search.getOrderReference()));
fields.add(CriteriaValue.like(SHIPPING_REFERENCE, search.getShippingReference()));
if (search.getOrganisationalUnit() != null) {
hackOrgUnit(fields, search.getOrganisationalUnit());
}
// fields.add(CriteriaValue.eq(ORGANISATIONAL_UNIT,
// search.getOrganisationalUnit()));
fields.add(CriteriaValue.eq("lcRequired", true));
CriteriaValueUtils.addDateRangeCriteriaValue(fields, LATEST_SHIPMENT_DATE, search.getLatestShipmentDateRange());
return fields;
}
@Override
public long letterOfCreditCount(S orderSearch) {
Query query = createQuery(orderSearch, true, letterOfCreditCriteriaValues(orderSearch));
return getQueryCount(query);
}
@Override
public List<T> letterOfCreditSearch(S search) {
Query query = createQuery(search, false, letterOfCreditCriteriaValues(search));
return getQueryList(query, search.getSearchMetaParams());
}
@Override
public long notShippedCount(S orderSearch) {
Query criteria = NotShippedReportHelper.createQueryNotShippedTransformed(getSession(), (PurchaseOrderSearch) orderSearch, true);
long count = ((BigInteger) (criteria.uniqueResult())).longValue();
return count;
}
@Override
public long confirmationCount(S orderSearch) {
Criteria criteria = createOrderConfirmationCriteriaTransformed(orderSearch);
criteria.setProjection(Projections.rowCount());
return Long.parseLong(criteria.list().get(0) + "");
}
/**
* TODO - this method doesn't work properly. Brings back purchase AND sales
* orders.
*/
@Override
public List<T> findByState(OrderState state) {
List<T> orders = (List<T>) findByNamedQueryAndNamedParam("order.findAllByState", "state", state);
return orders;
}
@Override
public T getById(Long id) {
T order = super.getById(id);
for (LineItem lineItem : order.getLineItems()) {
getCurrentSession().get(LineItem.class, lineItem.getId());
}
return order;
}
// WTF!!!!!
private void hackOrgUnit(Collection<CriteriaValue> fields, OrganisationalUnit orgUnit) {
// TODO. Remove this! Do not have tiers hardcoded here.
OrganisationalUnitTier tier = orgUnit.getTier();
if (tier != null) {
if (!tier.getCode().equals("HoldingCompany")) {
if (tier.getCode().equals("BUSINESS_UNIT")) {
fields.add(CriteriaValue.eq(ORGANISATIONAL_UNIT_PARENT, orgUnit));
} else {
fields.add(CriteriaValue.eq(ORGANISATIONAL_UNIT, orgUnit));
}
}
}
}
/**
* 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(S search) {
String tableName = ((SearchBase) search).getTableName();
String alias = tableName.toLowerCase() + ".";
Collection<CriteriaValue> fields = new LinkedHashSet<CriteriaValue>();
fields.add(new CriteriaValue(CriteriaOperation.LIKE, alias + NUMBER, search.getOrderNumber()));
fields.add(new CriteriaValue(CriteriaOperation.LIKE, alias + ORDER_REFERENCE, search.getOrderReference()));
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, alias + CURRENCY, search.getCurrency()));
CriteriaValueUtils.addEqActiveStateCriteriaValue(fields, alias + STATE, search.getState(), OrderState.DELETED);
fields.add(new CriteriaValue(CriteriaOperation.GREATER_THAN, alias + CREATED, search.getFromDate()));
fields.add(new CriteriaValue(CriteriaOperation.LESS_THAN, alias + CREATED, getToDate(search.getToDate())));
fields.add(new CriteriaValue(CriteriaOperation.GREATER_THAN, alias + ORDER_DATE, search.getOrderDateFrom()));
fields.add(new CriteriaValue(CriteriaOperation.LESS_THAN, alias + ORDER_DATE, search.getOrderDateTo()));
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, alias + CONSIGNMENT_STATE, search.getConsignmentState()));
filterOrgUnit(search, fields, alias);
return fields;
}
private void filterOrgUnit(S search, Collection<CriteriaValue> fields, String alias) {
if (search.isFilteredByUserOrg()) {
fields.add(CriteriaValue.in(alias + ORGANISATIONAL_UNIT, getUserOrganisationalUnits()));
} else if (search.getOrganisationalUnit() != null) {
fields.add(CriteriaValue.eq(alias + ORGANISATIONAL_UNIT, search.getOrganisationalUnit()));
} else if (search.getOrganisationalUnits() != null && !search.getOrganisationalUnits().isEmpty()) {
fields.add(CriteriaValue.in(alias + ORGANISATIONAL_UNIT, search.getOrganisationalUnits()));
}
}
private Date getToDate(Date toDate) {
// Below will increase the 'toDate' value by one day seeing that, for
// example: if one searches from '17-02-2014' to
// '17-02-2014' that person would expect results for one day but,
// without the below adjustment, would received no
// results seeing that if the order was not created on '17-02-2014
// 00:00:00' then the order would not be rendered.
if (toDate != null) {
return new Date(toDate.getTime() + ONE_DAY - 1000);
}
return toDate;
}
private boolean matchesSearchParam(Object orderObj, Object searchObj) {
log.debug("Comparing " + orderObj + " and " + searchObj);
if (searchObj == null) {
return true;
} else {
if (orderObj == null) {
return false;
} else {
return searchObj.equals(orderObj);
}
}
}
// Needed because of javaassist and equals
private boolean matchesSearchParamSD(StaticDataEntityBase orderObj, StaticDataEntityBase searchObj) {
if (searchObj == null) {
return true;
} else {
if (orderObj == null) {
return false;
} else {
return searchObj.getCode().equals(orderObj.getCode());
}
}
}
private boolean matchesSearchParam(Date objDate, DateRange searchDateRange) {
if (searchDateRange == null || searchDateRange.isEmpty()) {
return true;
} else {
if (objDate == null) {
return false;
} else {
return searchDateRange.inRange(objDate);
}
}
}
@Override
public List<T> fullSearch(S search) {
Query query = this.createQuery(search, false);
return query.list();
}
@Override
public List<T> findOrdersForConsignment(Consignment consignment) {
DetachedCriteria criteria = DetachedCriteria.forClass(getPersistentClass());
criteria.add(Restrictions.eq("consignment", consignment));
return (List<T>) findByCriteria(criteria);
}
@Override
public T findByShippingReference(String shippingReference) {
List list = findByNamedQueryAndNamedParam("order.findByShippingReference", "shippingReference", shippingReference);
List<T> orders = Collections.checkedList(list, getPersistentClass());
return orders.isEmpty() ? null : orders.get(0);
}
@Override
public List<ActualOrder> findActualOrderByNumber(String number) {
String[] references = new String[]{"number"};
Object[] values = new Object[]{number};
@SuppressWarnings("unchecked")
List<ActualOrder> orders = (List<ActualOrder>) findByNamedParam("findActualOrderByNumber", references, values);
return orders.isEmpty() ? null : orders;
}
@Override
public long countUntariffedLineItems(T order) {
String queryString =
"SELECT count(i) " + "FROM LineItem i "
+ "WHERE i.order = :order AND state IN ('AWAITING_TARIFFING', 'SIGNED_OFF', 'UNFINALISED', 'UNTARIFFED', 'UNVERIFIED')";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("order", order);
return (Long) query.uniqueResult();
}
@Override
public List<LineItem> searchActiveLineItems(T order, SearchMetaParams searchMetaParams) {
String orderBy = "created asc";
if (searchMetaParams != null && StringUtils.isNotEmpty(searchMetaParams.getOrderBy())) {
orderBy = searchMetaParams.getOrderBy();
}
String queryString = "SELECT i FROM LineItem i WHERE i.order = :order AND state != 'DELETED' ORDER BY " + orderBy;
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("order", order);
initQueryParams(query, searchMetaParams);
return query.list();
}
@Override
public long countActiveLineItems(T order) {
String queryString = "SELECT count(i) FROM LineItem i WHERE i.order = :order AND state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("order", order);
return (Long) query.uniqueResult();
}
@Override
public long countByIncotermOrganisationalUnitAndOrderState(Incoterm incoterm, OrganisationalUnit organisationalUnit, OrderState... orderState) {
ObjectUtil.validateNotNull(incoterm, "Incoterm");
ObjectUtil.validateNotNull(orderState, "Order State");
String stateNames = getStateNamesAsSql(orderState);
String sql =
"select count(*) from Order o where o.state in (" + stateNames + ") and o.shippingInformation.incoterm = :incoterm"
+ " and o.consignment is not null";
if (organisationalUnit != null) {
sql += " and o.organisationalUnit = :orgUnit";
}
Query query = getSessionCustom().createQuery(sql);
query.setParameter("incoterm", incoterm);
if (organisationalUnit != null) {
query.setParameter("orgUnit", organisationalUnit);
}
return ((Long) query.uniqueResult());
}
private String getStateNamesAsSql(OrderState[] orderState) {
String stateNames = null;
for (OrderState state : orderState) {
if (stateNames != null) {
stateNames += ",'" + state.name() + "'";
} else {
stateNames = "'" + state.name() + "'";
}
}
return stateNames;
}
@Override
public long countForAllOrgUnitsInCostDefinition(Incoterm incoterm, OrderState orderState, CostingContextType costingContextType) {
ObjectUtil.validateNotNull(incoterm, "Incoterm");
ObjectUtil.validateNotNull(orderState, "Order State");
ObjectUtil.validateNotNull(costingContextType, "Costing Context Type");
String sql =
"select count(*) from Order o where o.state = :state and o.shippingInformation.incoterm = :incoterm"
+ " and o.organisationalUnit in (select distinct cd.organisationalUnit from CostDefinition cd"
+ " where cd.incoterm = :incoterm and cd.costingContextType = :ctx)" + " and o.consignment is not null";
Query query = getSessionCustom().createQuery(sql);
query.setParameter("state", orderState);
query.setParameter("incoterm", incoterm);
query.setParameter("ctx", costingContextType);
return ((Long) query.uniqueResult());
}
@Override
public List<T> findNotInCostDefinitionOrganisationalUnits(Incoterm incoterm, CostingContextType costingContextType, OrderState... orderState) {
ObjectUtil.validateNotNull(incoterm, "Incoterm");
ObjectUtil.validateNotNull(orderState, "Order States");
ObjectUtil.validateNotNull(costingContextType, "Costing Context Type");
String stateNames = getStateNamesAsSql(orderState);
String sql =
"select o from Order o where o.state in (" + stateNames + ")" + " and o.shippingInformation.incoterm = :incoterm"
+ " and o.organisationalUnit not in (select distinct cd.organisationalUnit from CostDefinition cd"
+ " where cd.incoterm = :incoterm and cd.costingContextType = :ctx)" + " and o.consignment is not null";
Query query = getSessionCustom().createQuery(sql);
query.setParameter("incoterm", incoterm);
query.setParameter("ctx", costingContextType);
return query.list();
}
@Override
public BigDecimal getTotalUnitQuantity(T order) {
String sql = "select sum(li.unitQuantity) from LineItem li where li.order = :order";
Query query = getSessionCustom().createQuery(sql);
query.setParameter("order", order);
BigDecimal result = (BigDecimal) query.uniqueResult();
return result != null ? result : BigDecimal.ZERO;
}
@Override
public List<Long> getBrotherOrderIds(Long orderId, String ordering) {
SQLQuery sqlQuery =
getSession().createSQLQuery(
"select o.id from orders o where "
+ "o.consignment_id in (select c.id from consignment c join orders o1 on o1.consignment_id = c.id "
+ "where o1.id = " + orderId.longValue() + ") order by id " + ordering);
List<BigInteger> list = sqlQuery.list();
List<Long> ids = new ArrayList<Long>();
for (BigInteger integer : list) {
ids.add((new Long(integer.longValue())));
}
return ids;
}
/**
* The only mandatory fields are orderId and maxResults which if < 1 then it
* is ignored.
*
* @param orderId
* @param orderEventType
* @param ordering
* @param maxResults
* @return
*/
@Override
public List<OrdersEvent> getOrdersEvent(Long orderId, OrderEventType orderEventType, String ordering, int maxResults) {
StringBuilder hql = new StringBuilder("select event from Order o join o.events as event where o.id = :orderId ");
if (orderEventType != null) {
hql.append(" and event.eventType = :eventType ");
}
if (ordering != null) {
if (ordering.equalsIgnoreCase("desc")) {
hql.append(" order by event.createDateTime desc ");
} else if (ordering.equalsIgnoreCase("asc")) {
hql.append(" order by event.createDateTime asc ");
}
}
Query query = getSession().createQuery(hql.toString());
query.setParameter("orderId", orderId);
if (orderEventType != null) {
query.setParameter("eventType", orderEventType);
}
if (maxResults > 0) {
query.setMaxResults(maxResults);
}
return query.list();
}
@Override
public Map<T, BigDecimal> getTEUMap(List<T> orders) {
// Not sure why "SELECT o FROM..." didn't work, hibernate then wanted me
// to add lots of group by clauses
String s = "SELECT o.id, SUM(c.containerType.teu) FROM Order AS o JOIN o.consignment.containers AS c WHERE o IN :orders GROUP BY o ";
Query query = getSession().createQuery(s);
query.setParameterList("orders", orders);
List<Object[]> list = query.list();
Map<T, BigDecimal> map = new HashMap<T, BigDecimal>();
for (T order : orders) {
boolean found = false;
for (Object o[] : list) {
if (((Long) o[0]).equals(order.getId())) {
map.put(order, (BigDecimal) o[1]);
found = true;
}
}
if (!found) {
map.put(order, BigDecimal.ZERO);
}
}
return map;
}
@Override
public List orderSignedOffReport(S search) {
String tableName = search.getTableName().toLowerCase();
String[] orderBy =
new String[]{tableName + ".created ASC", tableName + ".orderReference"};
Query query =
getQuery(search, false, "", ordersSignedCriteriaValues(search),
createJoinStringForOrdersSignedOff(search), orderBy, "select distinct " + tableName + " ");
return getQueryList(query, search.getSearchMetaParams());
}
private Collection<CriteriaValue> ordersSignedCriteriaValues(S search) {
Collection<CriteriaValue> fields = new LinkedHashSet<CriteriaValue>();
fields.add(CriteriaValue.eq(ELC_ORDER, search.isElc()));
DateRange eventDateRange = search.getEventDateRange();
if (eventDateRange != null) {
fields.add(CriteriaValue.gt("event.createDateTime", eventDateRange.getFrom()));
fields.add(CriteriaValue.lt("event.createDateTime", eventDateRange.getTo()));
}
return fields;
}
private String createJoinStringForOrdersSignedOff(S search) {
String tableName = ((SearchBase) search).getTableName().toLowerCase();
StringBuilder hql = new StringBuilder(" join " + tableName + ".events as event where " + tableName + ".state<>'DELETED'");
hql.append(" and event.eventType='SIGNED_OFF' and " + tableName + ".elc='" + search.isElc() + "'");
return hql.toString();
}
private BigDecimal calculateTotalSuppliersInvoice(String orderNumber) {
CostedOrder costedOrder = generalRepository.findByNumberIgnoreCase(CostedOrder.class, orderNumber);
if (costedOrder == null) {
return new BigDecimal(0);
} else {
return costedOrder.getTotalsDistribution().get(TotalsDistributionType.DIRECT_COST);
}
}
public String getGeneratedOrderReference(String shippingReference) {
String orderReference = "select generate_sequence(:shippingReference)";
Query orderReferenceQuery = getSessionCustom().createSQLQuery(orderReference);
orderReferenceQuery.setParameter("shippingReference", shippingReference);
String refResult = (String) orderReferenceQuery.uniqueResult();
return refResult;
}
@Override
public List orderInsuranceDeclaration(S search) {
throw new NotImplementedException("not impemented for order type");
}
@Override
public List<ChildOrderDTO> searchChildOrders(T order, SearchMetaParams searchMetaParams) {
StringBuilder query = new StringBuilder("select o.id, o.orderreference,o.state,o.totalInvoiceValue as value,")
.append("(select sum(unitquantity) from lineitem i where i.order_id=o.id) as quantity ")
.append("from orders o where o.bulkOrderId=:orderId and o.state<>'DELETED' order by created desc ");
NativeQuery nativeQuery = getSession().createNativeQuery(query.toString()).setParameter("orderId", order.getId());
if (searchMetaParams != null) {
if (searchMetaParams.getRowCount() != -1) {
nativeQuery.setMaxResults(searchMetaParams.getRowCount());
}
if (searchMetaParams.getRowIndex() != -1) {
nativeQuery.setFirstResult(searchMetaParams.getRowIndex());
}
}
return nativeQuery
.addScalar("id", StandardBasicTypes.LONG)
.addScalar("orderReference", StandardBasicTypes.STRING)
.addScalar("state", StandardBasicTypes.STRING)
.addScalar("value", StandardBasicTypes.BIG_DECIMAL)
.addScalar("quantity", StandardBasicTypes.BIG_DECIMAL)
.setResultTransformer(Transformers.aliasToBean(ChildOrderDTO.class)).list();
}
@Override
public long countChildOrders(T order) {
StringBuilder query = new StringBuilder("select count(*) from orders o where o.bulkOrderId=:orderId and o.state<>'DELETED'");
NativeQuery nativeQuery = getSession().createNativeQuery(query.toString())
.setParameter("orderId", order.getId());
return ((BigInteger) nativeQuery.uniqueResult()).longValue();
}
@Override
public boolean isOrderId(long id) {
StringBuilder query = new StringBuilder("select count(*) from orders o where o.id=:orderId");
NativeQuery nativeQuery = getSession().createNativeQuery(query.toString())
.setParameter("orderId", id);
return ((BigInteger) nativeQuery.uniqueResult()).longValue() > 0;
}
@Override
public void recycleOrder(String reference) {
int year = YearMonth.now().getYear();
int month = YearMonth.now().getMonthValue();
String orderRef = String.format("RECY-%s%s-%s",
StringUtils.right("" + year, 2),
StringUtils.leftPad("" + month, 2, "0"),
reference);
List<String[]> tables = new ArrayList<>();
//orderreference
tables.add(new String[]{"orders", "orderreference"});
tables.add(new String[]{"packinglistorder", "orderreference"});
tables.add(new String[]{"consignment", "reference"});
tables.add(new String[]{"actualorder", "reference"});
tables.add(new String[]{"ship_treasury_report_info", "orderreference"});
tables.add(new String[]{"pstoupdate", "orderreference"});
tables.add(new String[]{"packinglistorderbulkupload", "orderreference"});
tables.add(new String[]{"ordersevent", "orderreference"});
tables.add(new String[]{"activitylogs", "orderreference"});
tables.add(new String[]{"alchistoricalcostitem", "orderreference"});
tables.add(new String[]{"integratedgoodreceivedreceipt", "purchaseorderreference"});
tables.add(new String[]{"pstoupdate", "orderreference"});
tables.add(new String[]{"ship_treasury_report_info", "orderreference"});
//order_reference
tables.add(new String[]{"deal", "order_reference"});
//TODO: what are these? drop tables?
//tables.add(new String[]{"deal_00610584fos_parent", "order_reference"});
//tables.add(new String[]{"deal_12010", "order_reference"});
//tables.add(new String[]{"deal_15178", "order_reference"});
//tables.add(new String[]{"deal_67949", "order_reference"});
//tables.add(new String[]{"deal_backup_14409_1", "order_reference"});
//tables.add(new String[]{"deal_backup_14409_2", "order_reference"});
//tables.add(new String[]{"deal_backup_14409_3", "order_reference"});
//tables.add(new String[]{"deal_backup_14409_4", "order_reference"});
//tables.add(new String[]{"deal_backup_14409_5", "order_reference"});
//tables.add(new String[]{"lm3_deal", "order_reference"});
//tables.add(new String[]{"lm_deal2", "order_reference"});
//tables.add(new String[]{"tfg_archive", "order_reference"});
for (String[] tableInfo : tables) {
String query = String.format("update %s set %s = :orderref where %s = :oldref", tableInfo[0], tableInfo[1], tableInfo[1]);
log.debug(query);
getCurrentSession().createNativeQuery(query)
.setParameter("orderref", orderRef)
.setParameter("oldref", reference)
.executeUpdate();
}
}
protected void addStyleSearchCriteria(OrderSearch search, Criteria criteria) {
if (search.getStyleReference() != null || search.getStyleDescription() != null || search.getItemDescription() != null
|| search.getItemReference() != null) {
criteria.createAlias("lineItems", "lineitems");
criteria.setFetchMode("lineItems", FetchMode.SELECT);
}
if (search.getStyleReference() != null) {
criteria.add(Restrictions.ilike("lineitems.styleReference", "%" + search.getStyleReference() + "%"));
}
if (search.getStyleDescription() != null) {
criteria.add(Restrictions.ilike("lineitems.styleDescription", "%" + search.getStyleDescription() + "%"));
}
if (search.getItemDescription() != null) {
criteria.add(Restrictions.ilike("lineitems.description", "%" + search.getItemDescription() + "%"));
}
if (search.getItemReference() != null) {
criteria.add(Restrictions.ilike("lineitems.code", "%" + search.getItemReference() + "%"));
}
}
@Override
public long orderWithSameRefDiffNumber(String orderReference, String number) {
StringBuilder query = new StringBuilder("select count(*) from orders o where o.orderReference=:orderReference and o.number<>:number " +
"and o.state not in ('ARCHIVED','DELETED')");
NativeQuery nativeQuery = getSession().createNativeQuery(query.toString())
.setParameter("orderReference", orderReference).setParameter("number",number);
return ((BigInteger) nativeQuery.uniqueResult()).longValue();
}
@Override
public long orderWithSameRefQuoteDiffNumber(String quoteReference, String number) {
StringBuilder query = new StringBuilder("select count(*) from orders o where o.quotereference=:quoteReference and o.number<>:number " +
"and o.state not in ('ARCHIVED','DELETED')");
NativeQuery nativeQuery = getSession().createNativeQuery(query.toString())
.setParameter("quoteReference", quoteReference).setParameter("number",number);
return ((BigInteger) nativeQuery.uniqueResult()).longValue();
}
}