BaseConsignmentRepositoryImpl.java
package com.tradecloud.repository.impl;
import com.tradecloud.domain.base.utils.ObjectUtil;
import com.tradecloud.domain.common.Incoterm;
import com.tradecloud.domain.configuration.CountryGroup;
import com.tradecloud.domain.costing.CostingContextType;
import com.tradecloud.domain.costing.clean.CostedConsignment;
import com.tradecloud.domain.document.invoice.CommercialInvoice;
import com.tradecloud.domain.document.invoice.ServiceProviderInvoice;
import com.tradecloud.domain.dto.base.SearchBase;
import com.tradecloud.domain.infrastructure.persistence.CriteriaBuilder;
import com.tradecloud.domain.model.ordermanagement.Consignment;
import com.tradecloud.domain.model.ordermanagement.ConsignmentState;
import com.tradecloud.domain.model.ordermanagement.Order;
import com.tradecloud.domain.model.ordermanagement.OrderState;
import com.tradecloud.domain.model.organisationalunit.OrgUnitTraversal;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.domain.party.ServiceProvider;
import com.tradecloud.domain.place.PlaceOfDischarge;
import com.tradecloud.domain.place.PlaceOfLoading;
import com.tradecloud.dto.consignment.ConsignmentSearch;
import com.tradecloud.dto.consignment.ConsignmentStatusSearchResult;
import com.tradecloud.repository.ConsignmentRepository;
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.*;
import org.hibernate.sql.JoinType;
import org.hibernate.transform.ResultTransformer;
import java.util.*;
/**
* Created by ds on 2015/11/23.
*/
public abstract class BaseConsignmentRepositoryImpl<T extends Consignment> extends RepositoryBaseImpl<T, ConsignmentSearch>
implements ConsignmentRepository<T, ConsignmentSearch> {
private final static transient Logger log = Logger.getLogger(ConsignmentRepositoryImpl.class);
private static final long serialVersionUID = 1L;
private final static String NUMBER = "number";
private final static String REFERENCE = "reference";
private final static String STATE = "state";
private final static String SHIPPING_MODE = "shippingInfo.shippingMode";
private final static String PLACE_OF_LOADING = "shippingInfo.placeOfLoading";
private final static String PLACE_OF_DISCHARGE = "shippingInfo.placeOfDischarge";
private final static String INCOTERM = "shippingInfo.incoterm";
private final static String FREIGHT_FORWARDER = "shippingInfo.freightForwarder";
private final static String ESTIMATED_DEPARTURE_DATE = "estimatedDepartureDate";
private final static String LATEST_SHIPMENT_DATE = "latestShipmentDate";
private final static String LITE_CONSIGNMENT = "liteConsignment";
private final static String ARRIVAL_DATE_AT_PLACE_OF_DISCHARGE = "arrivalDateAtPlaceOfDischarge";
private final static String CREATED = "created";
private final static String SHIPMENT_ID = "shipment_id";
private final static String CARRIER = "shippingInfo.carrier";
private final static String ACTUAL_DEPARTURE_DATE = "shipment.actualDepartureDate";
private final static String SHIPMENT_NUMBER = "consignment.shipment.number";
private final static String SHIPMENT_REFERENCE = "consignment.shipment.reference";
private final static String SHIPMENT_STATE = "consignment.shipment.state";
private final static String SHIPMENT_CREATION_DATE = "consignment.shipment.created";
private final static String CONS_SHIPPING_MODE = "consignment.shippingInfo.shippingMode";
private final static String CONS_FREIGHT_FORWARDER = "consignment.shippingInfo.freightForwarder";
private static final String SHIPMENT = "shipment";
private final static String CURRENCY = "currency";
private final static String DISCRIMINATOR_VALUE = "discriminator_value";
private final static String ORGANISATIONAL_UNIT = "consignment.organisationalUnit";
private static String Order_QUERY = "select count(*) from orders o join consignment " +
"c on (c.id=o.consignment_id) where {alias}.id=o.consignment_id";
public static String getCreated() {
return CREATED;
}
@Override
public T findByIdWithContainers(long id) {
return retrieve(id);
}
@Override
public List<T> findByShipmentId(long shipmentId) {
Criteria searchCriteria = getSessionCustom().createCriteria(getConsignmentClass());
searchCriteria.createCriteria("shipment");
searchCriteria.add(Restrictions.eq("shipment.id", shipmentId));
return searchCriteria.list();
}
protected abstract Class getConsignmentClass();
protected void addSearchCriteria(DetachedCriteria searchCriteria, ConsignmentSearch search) {
searchCriteria.add(Restrictions.eq(DISCRIMINATOR_VALUE, getDiscriminator()));
if (ObjectUtil.anyNotNull(search.getShippingMode(), search.getIncoterm(), search.getFreightForwarder(), search.getPlaceOfDischarge(),
search.getPlaceOfLoading(), search.getMultiModalShippingMode())) {
searchCriteria.createAlias("consignment.shippingInfo", "shippingInfo");
}
searchCriteria.add(Restrictions.eq("elc", search.isElc()));
if (search.getNumber() != null) {
searchCriteria.add(Restrictions.ilike(NUMBER, search.getNumber(), MatchMode.ANYWHERE));
}
if (search.getReference() != null) {
searchCriteria.add(Restrictions.ilike(REFERENCE, search.getReference(), MatchMode.ANYWHERE));
}
//Search by reference without ignore case and like and matchmode functionality
if (search.getStrictReference() != null) {
searchCriteria.add(Restrictions.eq(REFERENCE, search.getStrictReference()));
}
CriteriaBuilder.addEqActiveStateRestriction(searchCriteria, STATE, search.getState(), ConsignmentState.DELETED);
CriteriaBuilder.addEqRestriction(searchCriteria, SHIPPING_MODE, search.getShippingMode());
CriteriaBuilder.addEqRestriction(searchCriteria, "shippingInfo.multiModalShippingMode", search.getMultiModalShippingMode());
if (search.getCurrency() != null) {
searchCriteria.add(Restrictions.eq(CURRENCY, search.getCurrency()));
}
if (search.getShipment() != null) {
searchCriteria.add(Restrictions.eq("shipment", search.getShipment()));
} else if (search.isEmptyShipment()) {
searchCriteria.add(Restrictions.isNull("shipment"));
}
if (search.getPlaceOfLoading() != null) {
searchCriteria.add(Restrictions.eq(PLACE_OF_LOADING, search.getPlaceOfLoading()));
}
if (search.getPlaceOfDischarge() != null) {
searchCriteria.add(Restrictions.eq(PLACE_OF_DISCHARGE, search.getPlaceOfDischarge()));
}
if (search.getIncoterm() != null) {
searchCriteria.add(Restrictions.eq(INCOTERM, search.getIncoterm()));
}
if (search.getFreightForwarder() != null) {
searchCriteria.add(Restrictions.eq(FREIGHT_FORWARDER, search.getFreightForwarder()));
}
if (search.getEstimatedDepartureDateFrom() != null && search.getEstimatedDepartureDateTo() != null) {
searchCriteria.add(Restrictions.between(ESTIMATED_DEPARTURE_DATE, search.getEstimatedDepartureDateFrom(),
search.getEstimatedDepartureDateTo()));
}
if (search.getArrivalDateAtPlaceOfDischargeFrom() != null && search.getArrivalDateAtPlaceOfDischargeTo() != null) {
searchCriteria.add(Restrictions.between(ARRIVAL_DATE_AT_PLACE_OF_DISCHARGE, search.getArrivalDateAtPlaceOfDischargeFrom(),
search.getArrivalDateAtPlaceOfDischargeTo()));
} else if (search.getArrivalDateAtPlaceOfDischargeFrom() != null && search.getArrivalDateAtPlaceOfDischargeTo() == null) {
searchCriteria.add(Restrictions.ge(ARRIVAL_DATE_AT_PLACE_OF_DISCHARGE, search.getArrivalDateAtPlaceOfDischargeFrom()));
} else if (search.getArrivalDateAtPlaceOfDischargeTo() != null && search.getArrivalDateAtPlaceOfDischargeFrom() == null) {
searchCriteria.add(Restrictions.le(ARRIVAL_DATE_AT_PLACE_OF_DISCHARGE, search.getArrivalDateAtPlaceOfDischargeTo()));
}
if (search.getLatestShipmentDateFrom() != null && search.getLatestShipmentDateTo() != null) {
searchCriteria.add(Restrictions.between(LATEST_SHIPMENT_DATE, search.getLatestShipmentDateFrom(),
search.getLatestShipmentDateTo()));
} else if (search.getLatestShipmentDateFrom() != null && search.getLatestShipmentDateTo() == null) {
searchCriteria.add(Restrictions.ge(LATEST_SHIPMENT_DATE, search.getLatestShipmentDateFrom()));
} else if (search.getLatestShipmentDateTo() != null && search.getLatestShipmentDateFrom() == null) {
searchCriteria.add(Restrictions.le(LATEST_SHIPMENT_DATE, search.getLatestShipmentDateTo()));
}
Set<OrganisationalUnit> organisationalUnits = Collections.EMPTY_SET;
if (search.isLiteSearch()) {
if (search.getOrganisationalUnit() != null) {
organisationalUnits = new HashSet<>(search.getOrganisationalUnit().getChildren());
}
} else 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());
}
if (!organisationalUnits.isEmpty()) {
searchCriteria.createAlias("orders", "porder", JoinType.LEFT_OUTER_JOIN);
searchCriteria.add(Restrictions.or(Restrictions.in("porder.organisationalUnit", organisationalUnits),
Restrictions.isNull("porder.organisationalUnit")));
}
if (search.isUserModeLite()) {
searchCriteria.add(Restrictions.eq(LITE_CONSIGNMENT, search.isLiteConsignment()));
}
}
protected abstract String getDiscriminator();
@Override
public List<T> search(ConsignmentSearch search) {
//log.debug("Search. " + search);
DetachedCriteria searchCriteria1 = DetachedCriteria.forClass(getPersistentClass(), "consignment");
addSearchCriteria(searchCriteria1, search);
searchCriteria1.setProjection(Projections.distinct(Projections.property("id")));
DetachedCriteria searchCriteria = DetachedCriteria.forClass(getPersistentClass());
searchCriteria.add(Property.forName("id").in(searchCriteria1));
searchCriteria.addOrder(org.hibernate.criterion.Order.desc(CREATED));
// ensure distinct results, no duplicates when joining to Orders
// table that can have multiple results for each Consignment
searchCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
List<T> consignmentList = getExecutableCriteriaList(searchCriteria, search.getSearchMetaParams());
return consignmentList;
}
@Override
public List<Consignment> consignmentSearch(ConsignmentSearch search) {
String sql = "select * from consignment c " +
"left join orders o on c.id=o.consignment_id ";
//default imports
if (search.isImports() == null || search.isImports()) {
sql = sql + "right join purchaseorder po on o.id=po.id " +
"left join organisationalunitsupplier ous on po.supplier_id=ous.id " +
"left join supplier s on ous.supplier_id=s.id ";
} else {
sql = sql + "right join salesorder so on o.id=so.id ";
}
sql = sql + "left join consignmentshippinginfo cso on c.shippinginfo_id = cso.id " +
"where c.shipment_id IS NULL and c.state='SIGNED_OFF' ";
if (search.getSupplier() != null) {
sql = sql + " and s.id=" + search.getSupplier().getSupplier().getId();
}
if (StringUtils.isNotEmpty(search.getReference())) {
sql = sql + " and c.reference ilike '%" + search.getReference() + "%'";
}
if (search.getShippingMode() != null) {
sql = sql + " and cso.shippingmode ='" + search.getShippingMode().name() + "'";
}
if (search.getMultiModalShippingMode() != null) {
sql = sql + " and cso.multimodalshippingmode ='" + search.getMultiModalShippingMode().name() + "'";
}
if (search.getIncoterm() != null) {
sql = sql + " and cso.incoterm_code='" + search.getIncoterm().getCode() + "'";
}
sql = sql + " and c.elc='" + search.isElc() + "' ";
sql = sql + " and o.id is not null";
sql += " order by c.reference";
return (List<Consignment>) getCurrentSession().createSQLQuery(sql).addEntity(Consignment.class).list();
}
@Override
public long count(ConsignmentSearch search) {
log.debug("Search. " + search);
DetachedCriteria searchCriteria = DetachedCriteria.forClass(getPersistentClass(), "consignment");
addSearchCriteria(searchCriteria, search);
searchCriteria.setProjection(Projections.distinct(Projections.property("id")));
// ensure distinct results, no duplicates when joining to Orders
// table that can have multiple results for each Consignment
// searchCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
return getExecutableCriteriaCount(searchCriteria);
}
@Override
public T findByNumber(String consignmentNumber) {
Criteria searchCriteria = getSessionCustom().createCriteria(getConsignmentClass());
searchCriteria.add(Restrictions.eq(NUMBER, consignmentNumber));
return (T) searchCriteria.uniqueResult();
}
@Override
public T findByReference(String consignmentReference) {
Criteria searchCriteria = getSessionCustom().createCriteria(getConsignmentClass());
searchCriteria.add(Restrictions.eq(REFERENCE, consignmentReference));
searchCriteria.add(Restrictions.not(Restrictions.in(STATE, ConsignmentState.DELETED, ConsignmentState.ARCHIVED)));
return (T) searchCriteria.uniqueResult();
}
public List<String> countByReference(String consignmentReference) {
String query = "select reference from consignment where reference = :consignmentReference" +
" and state <> 'DELETED'";
List<String> results =
getCurrentSession().createSQLQuery(query).
setParameter("consignmentReference", consignmentReference).list();
return results;
}
@Override
public T findByReferenceInactive(String consignmentReference) {
Criteria searchCriteria = getSessionCustom().createCriteria(getConsignmentClass());
searchCriteria.add(Restrictions.like(REFERENCE, consignmentReference));
searchCriteria.add(Restrictions.eq(STATE, ConsignmentState.DELETED));
searchCriteria.add(Restrictions.eq(STATE, ConsignmentState.ARCHIVED));
return (T) searchCriteria.uniqueResult();
}
@Override
public T findByReferenceAndNumber(String reference, String number) {
Criteria searchCriteria = getSessionCustom().createCriteria(getConsignmentClass());
searchCriteria.add(Restrictions.like(REFERENCE, reference));
searchCriteria.add(Restrictions.like(NUMBER, number));
return (T) searchCriteria.uniqueResult();
}
@Override
public List<String> findFreeTextComments(Consignment consignmenrt) {
String query = "SELECT reason FROM consignment_freetextcomments WHERE consignment_id = :consignmentId ORDER BY reason ASC";
@SuppressWarnings("unchecked")
List<String> results =
getCurrentSession().createSQLQuery(query).
setParameter("consignmentId", consignmenrt.getId()).list();
return results;
}
@Override
public List<Order> searchActiveOrders(Consignment consignment, SearchMetaParams searchMetaParams) {
String queryString = "from Order where consignment = :consignment and state != 'DELETED' order by addedToConsignmentDate";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("consignment", consignment);
initQueryParams(query, searchMetaParams);
return query.list();
}
@Override
public List<Order> searchActiveOrdersLite(String consignmentReference, SearchMetaParams searchMetaParams) {
String queryString = "from PurchaseOrder where liteConsignmentReference = :consignmentReference and state !=" +
" 'DELETED' order by addedToConsignmentDate";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("consignmentReference", consignmentReference.trim());
initQueryParams(query, searchMetaParams);
return query.list();
}
@Override
public long countActiveOrders(Consignment consignment) {
String queryString = "select count(*) from Order where consignment = :consignment and state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("consignment", consignment);
return (Long) query.uniqueResult();
}
@Override
public long countActiveOrdersLite(String consignmentReference) {
String queryString = "select count(*) from PurchaseOrder where liteConsignmentReference = :consignmentReference and state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("consignmentReference", consignmentReference);
return (Long) query.uniqueResult();
}
@Override
public long countConsignmentOrderInState(Long consignmentId, OrderState orderState, ConsignmentState consignmentState) {
String queryString = "select count(*) from Order where consignment.id = :consignment and consignment.state =:consignmentState and " +
"state = :orderState";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("consignment", consignmentId);
query.setParameter("consignmentState", consignmentState);
query.setParameter("orderState", orderState);
return (Long) query.uniqueResult();
}
protected Collection<CriteriaValue> consignmentStatusCriteriaValues(ConsignmentSearch search) {
Collection<CriteriaValue> fields = new LinkedHashSet<CriteriaValue>();
fields.add(CriteriaValue.like(REFERENCE, search.getReference()));
fields.add(CriteriaValue.eq(SHIPPING_MODE, search.getShippingMode()));
fields.add(CriteriaValue.eq("shippingInfo.multiModalShippingMode", search.getMultiModalShippingMode()));
fields.add(CriteriaValue.eq(PLACE_OF_LOADING, search.getPlaceOfLoading()));
fields.add(CriteriaValue.eq(PLACE_OF_DISCHARGE, search.getPlaceOfDischarge()));
fields.add(CriteriaValue.eq(FREIGHT_FORWARDER, search.getFreightForwarder()));
fields.add(CriteriaValue.eq(STATE, search.getState()));
fields.add(CriteriaValue.isNull(SHIPMENT));
return fields;
}
public Criteria createConsignmentStatusCriteria(ConsignmentSearch search) {
Criteria criteria = this.getSession().createCriteria(getConsignmentClass());
criteria.createAlias("shippingInfo", "si");
criteria.setProjection(Projections.projectionList()
.add(Projections.distinct(Projections.property("number")))
.add(Projections.property("reference"), "reference")
.add(Projections.property("state"), "state")
.add(Projections.property("si.shippingMode"), "shippingMode")
.add(Projections.property("si.placeOfLoading"), "placeOfLoading")
.add(Projections.property("si.placeOfDischarge"), "placeOfDischarge")
.add(Projections.property("si.freightForwarder"), "freightForwarder")
.add(Projections.property(LATEST_SHIPMENT_DATE), "latestShipmentDate")
.add(Projections.property("id"), "id")
);
criteria.add(Restrictions.eq("elc", search.isElc()));
if (search.getReference() != null) {
if (search.isReferenceStrict()) {
criteria.add(Restrictions.eq(REFERENCE, search.getReference()));
} else {
criteria.add(Restrictions.ilike(REFERENCE, "%" + search.getReference() + "%"));
}
}
if (search.getShippingMode() != null) {
criteria.add(Restrictions.eq("si.shippingMode", search.getShippingMode()));
}
if (search.getMultiModalShippingMode() != null) {
criteria.add(Restrictions.eq("si.multiModalShippingMode", search.getMultiModalShippingMode()));
}
if (search.getPlaceOfLoading() != null) {
criteria.add(Restrictions.eq("si.placeOfLoading", search.getPlaceOfLoading()));
}
if (search.getPlaceOfDischarge() != null) {
criteria.add(Restrictions.eq("si.placeOfDischarge", search.getPlaceOfDischarge()));
}
if (search.getFreightForwarder() != null) {
criteria.add(Restrictions.eq("si.freightForwarder", search.getFreightForwarder()));
}
if (search.getState() != null) {
criteria.add(Restrictions.eq(STATE, search.getState()));
}
criteria.add(Restrictions.isNull(SHIPMENT));
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());
}
if (!organisationalUnits.isEmpty()) {
criteria.createAlias("orders", "porder");
criteria.add(Restrictions.in("porder.organisationalUnit", organisationalUnits));
}
criteria.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] objects, String[] strings) {
ConsignmentStatusSearchResult result = new ConsignmentStatusSearchResult();
PlaceOfLoading loading = (PlaceOfLoading) objects[4];
PlaceOfDischarge discharge = (PlaceOfDischarge) objects[5];
ServiceProvider serviceProvider = (ServiceProvider) objects[6];
String placeOfLoading = loading.getName();
String placeOfDischarge = discharge.getName();
String freightForwarder = serviceProvider.getName();
result.setNumber("" + objects[0]);
result.setReference("" + objects[1]);
result.setState((ConsignmentState) objects[2]);
result.setShippingMode("" + objects[3]);
result.setPlaceOfLoading(placeOfLoading);
result.setPlaceOfDischarge(placeOfDischarge);
result.setFreightForwarder(freightForwarder);
result.setLatestShipmentDate((Date) objects[7]);
result.setId(new Long("" + objects[8]));
return result;
}
@Override
public List transformList(List list) {
return list;
}
});
return criteria;
}
@Override
public List<ConsignmentStatusSearchResult> consignmentStatusSearchTransformed(ConsignmentSearch search) {
Criteria criteria = this.createConsignmentStatusCriteria(search);
if (search.getSearchMetaParams() != null) {
SearchMetaParams searchMetaParams = search.getSearchMetaParams();
criteria.setMaxResults(searchMetaParams.getRowCount());
criteria.setFirstResult(searchMetaParams.getRowIndex());
criteria.addOrder(org.hibernate.criterion.Order.desc(LATEST_SHIPMENT_DATE));
} else {
criteria.addOrder(org.hibernate.criterion.Order.desc(LATEST_SHIPMENT_DATE));
}
return criteria.list();
}
@Override
public long consignmentStatusCountTransformed(ConsignmentSearch search) {
Criteria criteria = this.createConsignmentStatusCriteria(search);
criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
return Long.parseLong(criteria.list().get(0) + "");
}
@Override
public List<T> consignmentStatusSearch(ConsignmentSearch search) {
// Query query = createQuery(search, false, consignmentStatusCriteriaValues(search));
Query query = createSortedQuery(search, "", consignmentStatusCriteriaValues(search), LATEST_SHIPMENT_DATE, false);
return getQueryList(query, search.getSearchMetaParams());
}
@Override
public long consignmentStatusCount(ConsignmentSearch search) {
Query query = createQuery(search, true, consignmentStatusCriteriaValues(search));
return getQueryCount(query);
}
protected DetachedCriteria carrierUtilisationCriteriaValues(ConsignmentSearch search) {
DetachedCriteria criteria = DetachedCriteria.forClass(getConsignmentClass());
criteria.createAlias("shippingInfo", "si");
criteria.createAlias("shipment", "s");
criteria.add(Restrictions.isNotNull("shipment"));
if (search.getCarrier() != null) {
criteria.add(Restrictions.eq("si.carrier", search.getCarrier()));
}
if (search.getActualDepartureDateFrom() != null) {
criteria.add(Restrictions.ge("s.actualDepartureDate", search.getActualDepartureDateFrom()));
}
if (search.getActualDepartureDateTo() != null) {
criteria.add(Restrictions.le("s.actualDepartureDate", search.getActualDepartureDateTo()));
}
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());
}
if (!organisationalUnits.isEmpty()) {
criteria.createAlias("orders", "porder");
criteria.add(Restrictions.in("porder.organisationalUnit", organisationalUnits));
}
return criteria;
}
@Override
public List<T> carrierUtilisationSearch(ConsignmentSearch search) {
DetachedCriteria criteria1 = carrierUtilisationCriteriaValues(search);
criteria1.setProjection(Projections.distinct(Projections.property("id")));
DetachedCriteria criteria = DetachedCriteria.forClass(getConsignmentClass());
criteria.createAlias("shipment", "s");
criteria.add(Property.forName("id").in(criteria1));
SearchMetaParams searchMetaParams = search.getSearchMetaParams();
if (searchMetaParams != null) {
searchMetaParams.setOrderBy("s.actualDepartureDate");
}
return getExecutableCriteriaList(criteria, searchMetaParams);
}
@Override
public long carrierUtilisationCount(ConsignmentSearch search) {
DetachedCriteria criteria = carrierUtilisationCriteriaValues(search);
criteria.setProjection(Projections.distinct(Projections.property("id")));
return getExecutableCriteriaCount(criteria);
}
public String createJoinStringForShipmentStatus(ConsignmentSearch search) {
String tableName = ((SearchBase) search).getTableName();
Calendar cal = Calendar.getInstance();
StringBuilder sb = new StringBuilder(" left join " + tableName.toLowerCase() + ".shipment.events as ev ");
sb.append(" where ( ev.eventType != 'COMPLETED' or :cal > ev.createDateTime ) ");
sb.append(" and " + tableName.toLowerCase() + ".shipment is not null ");
return sb.toString();
}
// Paging the commercialInvoices
@Override
public List<CommercialInvoice> searchActiveCommercialInvoices(Consignment consignment, SearchMetaParams searchMetaParams) {
String queryString = "SELECT ci from CommercialInvoice ci WHERE consignment = :consignment AND state != 'DELETED'"
+ " ORDER BY addedToConsignmentDate";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("consignment", consignment);
initQueryParams(query, searchMetaParams);
return query.list();
}
@Override
public long countActiveCommercialInvoices(Consignment consignment) {
String queryString = "SELECT count(ci) from CommercialInvoice ci WHERE consignment = :consignment AND state != 'DELETED'";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("consignment", consignment);
return (Long) query.uniqueResult();
}
// Paging the service provider invoices
@Override
public List<ServiceProviderInvoice> searchActiveServiceProviderInvoices(Consignment consignment, SearchMetaParams searchMetaParams) {
String queryString = "SELECT spi from ServiceProviderInvoice spi WHERE consignment = :consignment AND state != 'DELETED'"
+ " ORDER BY addedToConsignmentDate";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("consignment", consignment);
initQueryParams(query, searchMetaParams);
return query.list();
}
@Override
public long countActiveServiceProviderInvoices(Consignment consignment) {
String queryString = "SELECT count(spi) from ServiceProviderInvoice spi WHERE consignment = :consignment AND state != 'DELETED'";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("consignment", consignment);
return (Long) query.uniqueResult();
}
@Override
public long countItemsWithoutSABCode(Long id) {
String sql =
"select count(*) from Consignment c join c.orders as ords join ords.lineItems li" +
" where c.id= :consignmentId and li.sabsTariff is null";
Query query = getSessionCustom().createQuery(sql);
query.setParameter("consignmentId", id);
return (Long) query.uniqueResult();
}
@Override
public CostedConsignment findEstimateFromNumber(String consignmentNumber) {
String sql =
"select c from CostedConsignment c " +
" where c.number= :consignmentNumber";
Query query = getSessionCustom().createQuery(sql);
query.setParameter("consignmentNumber", consignmentNumber);
return (CostedConsignment) query.uniqueResult();
}
@Override
public List<T> findWithOrdersInStatesAndUnLinkableCostDefinition(Incoterm incoterm,
CostingContextType costingContextType,
OrderState... orderStates) {
ObjectUtil.validateNotNull(incoterm, "Incoterm");
ObjectUtil.validateNotNull(orderStates, "Order State");
ObjectUtil.validateNotNull(costingContextType, "Costing Context Type");
String sql =
"select c from Consignment c join c.orders as ords" +
" where ords.state in (:orderStates)" +
" and c.shippingInfo.incoterm = :incoterm" +
" and c.organisationalUnit not in (select distinct cd.organisationalUnit from CostDefinition cd" +
" where cd.incoterm = :incoterm and cd.costingContextType = :ctx)";
Query query = getSessionCustom().createQuery(sql);
query.setParameter("incoterm", incoterm);
query.setParameter("ctx", costingContextType);
query.setParameterList("orderStates", orderStates);
return query.list();
}
@Override
public List<T> findByIncotermOrganisationalUnitIncludingChildrenAndOrderStates(Incoterm incoterm,
OrganisationalUnit organisationalUnit,
List<OrderState> orderStates) {
ObjectUtil.validateNotNull(incoterm, "Incoterm");
ObjectUtil.validateNotNull(organisationalUnit, "Organisational Unit");
ObjectUtil.validateNotNull(orderStates, "Order State");
Set<OrganisationalUnit> organisationalUnitSet = new HashSet<OrganisationalUnit>();
OrgUnitTraversal.getAllOrgUnitsDown(organisationalUnit, organisationalUnitSet);
String sql =
"select c from Consignment c join c.orders as ords" +
" where ords.state in (:orderStates)" +
" and c.shippingInfo.incoterm = :incoterm" +
" and c.organisationalUnit in (:organisationalUnits)";
Query query = getSessionCustom().createQuery(sql);
query.setParameter("incoterm", incoterm);
query.setParameterList("organisationalUnits", organisationalUnitSet);
query.setParameterList("orderStates", orderStates);
return query.list();
}
@Override
public List<T> findByIncotermOrganisationalUnitsAndOrderStates(Incoterm incoterm,
List<OrganisationalUnit> organisationalUnits,
List<OrderState> orderStates,
String consignmentReference,
PlaceOfLoading placeOfLoading,
PlaceOfDischarge placeOfDischarge,
CountryGroup countryGroup) {
ObjectUtil.validateNotNull(incoterm, "Incoterm");
ObjectUtil.validateNotNull(organisationalUnits, "Organisational Unit");
ObjectUtil.validateNotNull(orderStates, "Order State");
String consignmentRefSql = " and upper(c.reference) like upper(:consignmentReference)";
String placeOfLoadingSql = " and c.shippingInfo.placeOfLoading = :placeOfLoading";
String placeOfDischargeSql = " and c.shippingInfo.placeOfDischarge = :placeOfDischarge";
String organisationalUnitsSql = " and c.organisationalUnit in (:organisationalUnits)";
String orderStatesSql = " and ords.state in (:orderStates)";
String excludeElcSql = " and ords.elc = false";
StringBuilder sql = new StringBuilder(
"select distinct c from Consignment c join c.orders as ords")
.append(" where c.shippingInfo.incoterm = :incoterm");
if (consignmentReference != null) {
sql.append(consignmentRefSql);
}
if (placeOfLoading != null) {
sql.append(placeOfLoadingSql);
}
if (placeOfDischarge != null) {
sql.append(placeOfDischargeSql);
}
if (!organisationalUnits.isEmpty()) {
sql.append(organisationalUnitsSql);
}
if (!orderStates.isEmpty()) {
sql.append(orderStatesSql);
}
sql.append(excludeElcSql);
if (countryGroup != null && countryGroup.getCountries() != null && !countryGroup.getCountries().isEmpty()) {
sql.append(" and ords.supplier.supplier.physicalAddress.country in (:countries)");
}
Query query = getSessionCustom().createQuery(sql.toString());
query.setParameter("incoterm", incoterm);
if (consignmentReference != null) {
query.setParameter("consignmentReference", LIKE + consignmentReference + LIKE);
}
if (placeOfLoading != null) {
query.setParameter("placeOfLoading", placeOfLoading);
}
if (placeOfDischarge != null) {
query.setParameter("placeOfDischarge", placeOfDischarge);
}
if (!organisationalUnits.isEmpty()) {
query.setParameterList("organisationalUnits", organisationalUnits);
}
if (!orderStates.isEmpty()) {
query.setParameterList("orderStates", orderStates);
}
if (countryGroup != null && countryGroup.getCountries() != null && !countryGroup.getCountries().isEmpty()) {
query.setParameterList("countries", countryGroup.getCountries());
}
return query.list();
}
@Override
public Map<Consignment, String> findShippingReferences(List<Consignment> consignments) {
Map<Consignment, String> result = new HashMap<Consignment, String>();
// Empty in statements are bad
if (!consignments.isEmpty()) {
String queryString
= "SELECT c.id, osi.shippingReference "
+ "FROM Order o LEFT JOIN o.shippingInformation osi "
+ "LEFT JOIN o.consignment c "
+ "WHERE c in (:consignments)";
Query query = getSessionCustom().createQuery(queryString);
query.setParameterList("consignments", consignments);
List list = query.list();
Map<Long, List<String>> temp = new HashMap<Long, List<String>>();
for (Object row : list) {
Long shipmentId = (Long) ((Object[]) row)[0];
String shippingReference = (String) ((Object[]) row)[1];
if (!temp.containsKey(shipmentId)) {
List<String> s = new ArrayList<String>();
temp.put(shipmentId, s);
}
temp.get(shipmentId).add(shippingReference);
}
for (Consignment consignment : consignments) {
result.put(consignment, StringUtils.join(temp.get(consignment.getId()), ","));
}
}
return result;
}
@Override
public boolean hasIncotermChanged(Incoterm incoterm, Long consignmentId) {
Criteria criteria = getSessionCustom().createCriteria(getConsignmentClass());
criteria.createAlias("shippingInfo", "si");
criteria.setProjection(Projections.property("si.incoterm"));
criteria.add(Restrictions.eq("id", consignmentId));
Incoterm originalIncoterm = (Incoterm) criteria.uniqueResult();
return !originalIncoterm.equals(incoterm);
}
@Override
public T findByOrderNumber(String orderNumber) {
String hql = "select o.consignment from Order o where o.number = :orderNumber";
Query query = getSession().createQuery(hql);
query.setParameter("orderNumber", orderNumber);
return (T) query.uniqueResult();
}
@Override
public T findByNumberInactive(String number) {
Criteria searchCriteria = getSessionCustom().createCriteria(getConsignmentClass());
searchCriteria.add(Restrictions.like(NUMBER, number));
searchCriteria.add(Restrictions.eq(STATE, ConsignmentState.ARCHIVED));
return (T) searchCriteria.uniqueResult();
}
@Override
public long countOrdersInState(Consignment consignment, OrderState... orderState) {
String queryString = "select count(*) from Order where consignment = :consignment and state in :orderState";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("consignment", consignment);
query.setParameterList("orderState", Arrays.asList(orderState));
return (Long) query.uniqueResult();
}
}