ShipmentContainerRepositoryImpl.java
package com.tradecloud.repository.impl;
import com.tradecloud.common.base.HibernateUtils;
import com.tradecloud.domain.container.ContainerTotals;
import com.tradecloud.domain.container.ShipmentContainer;
import com.tradecloud.domain.infrastructure.persistence.CriteriaBuilder;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.domain.party.ServiceProvider;
import com.tradecloud.domain.shipment.SeaShipment;
import com.tradecloud.domain.shipment.Shipment;
import com.tradecloud.dto.demurrage.DemurragePreAlertSearch;
import com.tradecloud.repository.ShipmentContainerRepository;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.query.NativeQuery;
import org.hibernate.query.Query;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Set;
/**
* Default implementation of the {@code ShipmentContainerRepository}.
*/
@Repository(value = "shipmentContainerRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class ShipmentContainerRepositoryImpl extends RepositoryBaseImplNoSearch<ShipmentContainer> implements ShipmentContainerRepository {
private static final long serialVersionUID = 1L;
private final static String REFERENCE = "reference";
private final static String SHIPMENT_REFERENCE = "s.reference";
private final static String SHIPMENT_NUMBER = "s.number";
private final static String CARRIER = "s.carrier";
private final static String FREIGHT_FORWARDER = "si.freightForwarder";
private final static String CLEARING_AGENT = "si.clearingAgent";
private final static String PLACE_OF_DISCHARGE = "placeOfDischarge";
private final static String TURN_IN_DEPOT = "turnInDepot";
private final static String OVERSTAY_AND_STORAGE_TRANSACTION = "overstayAndStorageTransaction";
private final static String TURN_IN_TRANSACTION = "turnInTransaction";
private final static String OVERSTAY_AND_STORAGE_FREE_PERIOD_END_DATE = "overstayAndStorageFreePeriodEnd";
private final static String TURN_IN_FREE_PERIOD_END_DATE = "turnInFreePeriodEnd";
private final static String CONTAINER_TOTALS_QUERY = "select coalesce(sum(sc.tareWeight), 0.00) as tareWeight, "
+ "coalesce(sum(sc.grossWeight), 0.00) as grossWeight, coalesce(sum(sc.grossVolume), 0.00) as grossVolume from "
+ "ShipmentContainer sc where sc.shipment.id = :shipmentId";
@Override
@Transactional(readOnly = true)
public List<ShipmentContainer> findByShipment(Shipment shipment) {
Query query = getSession().createQuery("from ShipmentContainer where shipment = :shipment");
query.setParameter("shipment", shipment);
return query.list();
}
@Override
@Transactional(readOnly = true)
public ShipmentContainer findByShipmentAndReference(Shipment shipment, String reference) {
Long shipmentId = shipment.getId();
String duplicateMessage = "There should only be one container with the unique reference " + reference + " on the shipment "
+ shipment.getReference();
return getShipmentContainer(reference, shipmentId, duplicateMessage);
}
@Override
@Transactional(readOnly = true)
public List<ShipmentContainer> findByReference(String reference) {
String query = "from ShipmentContainer sc where sc.reference = :reference";
List<ShipmentContainer> results =
getCurrentSession().createQuery(query).setParameter("reference", reference).list();
return results;
}
@Override
@Transactional(readOnly = true)
public List<Long> findShipmentIdsByContainerReference(String reference) {
if (reference == null || reference.trim().isEmpty()) {
return Collections.emptyList();
}
String hql = "select distinct sc.shipment.id "
+ "from ShipmentContainer sc "
+ "where sc.reference = :reference";
return (List<Long>) getCurrentSession()
.createQuery(hql)
.setParameter("reference", reference.trim())
.list();
}
@Override
public void saveTransporterForContainerReference(String reference, Long transporterId, List<Long> shipmentIdsInScope) {
if (reference == null || reference.trim().isEmpty()) {
return;
}
if (shipmentIdsInScope == null || shipmentIdsInScope.isEmpty()) {
return;
}
Date updated = new Date();
// 1) Update the actual STORAGE of ShipmentContainer: the `container` table
final String sqlContainer;
if (transporterId == null) {
sqlContainer =
"UPDATE container c " +
"SET transporter_id = null, updated = :updated " +
"WHERE c.containersubtype = 'SHIPMENT' " +
" AND c.reference = :reference " +
" AND ( " +
" c.shipment_id IN (:shipmentIds) " +
" OR EXISTS ( " +
" SELECT 1 FROM shipment_container sc " +
" WHERE sc.containers_id = c.id " +
" AND sc.shipment_id IN (:shipmentIds) " +
" ) " +
" )";
} else {
sqlContainer =
"UPDATE container c " +
"SET transporter_id = :transporterId, updated = :updated " +
"WHERE c.containersubtype = 'SHIPMENT' " +
" AND c.reference = :reference " +
" AND ( " +
" c.shipment_id IN (:shipmentIds) " +
" OR EXISTS ( " +
" SELECT 1 FROM shipment_container sc " +
" WHERE sc.containers_id = c.id " +
" AND sc.shipment_id IN (:shipmentIds) " +
" ) " +
" )";
}
org.hibernate.query.NativeQuery<?> q1 = getSessionCustom().createNativeQuery(sqlContainer);
q1.setParameter("updated", updated);
q1.setParameter("reference", reference);
q1.setParameterList("shipmentIds", shipmentIdsInScope);
if (transporterId != null) {
q1.setParameter("transporterId", transporterId);
}
int updatedContainers = q1.executeUpdate();
// 2) Keep transporter portal table in sync (if rows exist)
final String sqlTc;
if (transporterId == null) {
sqlTc =
"UPDATE transporter_container tc " +
"SET transporter_id = null, updated = :updated " +
"WHERE tc.shipmentcontainer_id IN ( " +
" SELECT c.id FROM container c " +
" WHERE c.containersubtype = 'SHIPMENT' " +
" AND c.reference = :reference " +
" AND ( " +
" c.shipment_id IN (:shipmentIds) " +
" OR EXISTS ( " +
" SELECT 1 FROM shipment_container sc " +
" WHERE sc.containers_id = c.id " +
" AND sc.shipment_id IN (:shipmentIds) " +
" ) " +
" ) " +
")";
} else {
sqlTc =
"UPDATE transporter_container tc " +
"SET transporter_id = :transporterId, updated = :updated " +
"WHERE tc.shipmentcontainer_id IN ( " +
" SELECT c.id FROM container c " +
" WHERE c.containersubtype = 'SHIPMENT' " +
" AND c.reference = :reference " +
" AND ( " +
" c.shipment_id IN (:shipmentIds) " +
" OR EXISTS ( " +
" SELECT 1 FROM shipment_container sc " +
" WHERE sc.containers_id = c.id " +
" AND sc.shipment_id IN (:shipmentIds) " +
" ) " +
" ) " +
")";
}
org.hibernate.query.NativeQuery<?> q2 = getSessionCustom().createNativeQuery(sqlTc);
q2.setParameter("updated", updated);
q2.setParameter("reference", reference);
q2.setParameterList("shipmentIds", shipmentIdsInScope);
if (transporterId != null) {
q2.setParameter("transporterId", transporterId);
}
int updatedTransporterPortalRows = q2.executeUpdate();
// Important: bulk updates bypass session cache
getSessionCustom().flush();
getSessionCustom().clear();
}
private ShipmentContainer getShipmentContainer(String reference, Long shipmentId, String duplicateMessage) {
@SuppressWarnings("unchecked")
String query = "from ShipmentContainer where shipment.id = :shipmentId and reference = :reference";
List<ShipmentContainer> results =
getCurrentSession().createQuery(query).setParameter("shipmentId", shipmentId)
.setParameter("reference", reference).list();
if (results.size() > 1) {
throw new IllegalStateException(duplicateMessage);
}
if (results.size() == 1) {
return results.get(0);
} else {
return null;
}
}
@Override
public List<ShipmentContainer> findByElapsedOverstayAndStorageFreePeriod(Date currentDate) {
return (List<ShipmentContainer>) findByNamedQueryAndNamedParam("shipmentContainer.findWithElapsedOverstayAndStorageFreePeriod",
"currentDate", currentDate);
}
@Override
public List<ShipmentContainer> findByElapsedTurnInFreePeriod(Date currentDate) {
return (List<ShipmentContainer>) findByNamedQueryAndNamedParam("shipmentContainer.findWithElapsedTurnInFreePeriod",
"currentDate", currentDate);
}
@Override
public ContainerTotals getContainerTotals(Shipment shipment) {
@SuppressWarnings("rawtypes")
List results = getCurrentSession()
.createQuery(CONTAINER_TOTALS_QUERY).setParameter("shipmentId", shipment.getId())
.setResultTransformer(Transformers.aliasToBean(ContainerTotals.class)).list();
return (ContainerTotals) results.get(0);
}
@Override
public List<ShipmentContainer> searchOverstayAndStoragePreAlert(DemurragePreAlertSearch search) {
DetachedCriteria criteria = DetachedCriteria.forClass(ShipmentContainer.class);
criteria.createAlias("shipment", "s");
criteria.createAlias("s.shippingInfo", "si");
populateCommonDemurragePreAlertCriteria(search, criteria);
// There mustn't already be a transaction
criteria.add(Restrictions.isNull(OVERSTAY_AND_STORAGE_TRANSACTION));
CriteriaBuilder.addEqRestriction(criteria, PLACE_OF_DISCHARGE, search.getPlaceOfDischarge());
CriteriaBuilder.addEqRestriction(criteria, CLEARING_AGENT, search.getClearingAgent());
CriteriaBuilder.addDateRangeCriteria(criteria, OVERSTAY_AND_STORAGE_FREE_PERIOD_END_DATE, search.getFreePeriodEndDateRange());
Set<OrganisationalUnit> organisationalUnits = Collections.EMPTY_SET;
if (search.getOrganisationalUnit() != null) {
organisationalUnits = Collections.singleton(search.getOrganisationalUnit());
} else if (search.isFilteredByUserOrg()) {
organisationalUnits = getUserOrganisationalUnits();
}
if (!organisationalUnits.isEmpty()) {
criteria.createAlias("s.consignments", "c");
criteria.createAlias("c.orders", "porder");
criteria.add(Restrictions.in("porder.organisationalUnit", organisationalUnits));
}
criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
criteria.setProjection(Projections.distinct(Projections.property("id")));
DetachedCriteria criteria1 = DetachedCriteria.forClass(ShipmentContainer.class);
criteria1.add(Property.forName("id").in(criteria));
List<ShipmentContainer> list = getExecutableCriteriaList(criteria1, ShipmentContainer.class);
return hackRemoveCarriers(list, search.getCarrier());
}
@Override
public List<ShipmentContainer> searchTurnInPreAlert(DemurragePreAlertSearch search) {
DetachedCriteria criteria = DetachedCriteria.forClass(ShipmentContainer.class);
criteria.createAlias("shipment", "s");
criteria.createAlias("s.shippingInfo", "si");
populateCommonDemurragePreAlertCriteria(search, criteria);
// There mustn't already be a transaction
criteria.add(Restrictions.isNull(TURN_IN_TRANSACTION));
CriteriaBuilder.addEqRestriction(criteria, TURN_IN_DEPOT, search.getTurnInDepot());
CriteriaBuilder.addDateRangeCriteria(criteria, TURN_IN_FREE_PERIOD_END_DATE, search.getFreePeriodEndDateRange());
Set<OrganisationalUnit> organisationalUnits = Collections.EMPTY_SET;
if (search.getOrganisationalUnit() != null) {
organisationalUnits = Collections.singleton(search.getOrganisationalUnit());
} else if (search.isFilteredByUserOrg()) {
organisationalUnits = getUserOrganisationalUnits();
}
if (!organisationalUnits.isEmpty()) {
criteria.createAlias("s.consignments", "c");
criteria.createAlias("c.orders", "porder");
criteria.add(Restrictions.in("porder.organisationalUnit", organisationalUnits));
}
criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
criteria.setProjection(Projections.distinct(Projections.property("id")));
DetachedCriteria criteria1 = DetachedCriteria.forClass(ShipmentContainer.class);
criteria1.add(Property.forName("id").in(criteria));
List<ShipmentContainer> list = getExecutableCriteriaList(criteria1, ShipmentContainer.class);
return hackRemoveCarriers(list, search.getCarrier());
}
private void populateCommonDemurragePreAlertCriteria(DemurragePreAlertSearch search, DetachedCriteria criteria) {
if (search.getContainerReference() != null) {
criteria.add(Restrictions.ilike(REFERENCE, search.getContainerReference(), MatchMode.ANYWHERE));
}
if (search.getShipmentReference() != null) {
criteria.add(Restrictions.ilike(SHIPMENT_REFERENCE, search.getShipmentReference(), MatchMode.ANYWHERE));
}
if (search.getShipmentNumber() != null) {
criteria.add(Restrictions.ilike(SHIPMENT_NUMBER, search.getShipmentNumber(), MatchMode.ANYWHERE));
}
// TODO. Might break for other types of shipment
// TODO. Could not get this to work
//CriteriaBuilder.addEqRestriction(criteria, CARRIER, search.getCarrier());
}
// Couldn't get the criteria to work on subclasses from SeaShipment
private List<ShipmentContainer> hackRemoveCarriers(List<ShipmentContainer> shipmentContainers, ServiceProvider carrier) {
List<ShipmentContainer> newShipmentContainers = new ArrayList<ShipmentContainer>();
if (carrier != null) {
for (ShipmentContainer shipmentContainer : shipmentContainers) {
HibernateUtils.getNonProxyObject(shipmentContainer.getShipment());
if (HibernateUtils.proxyClassIs(shipmentContainer.getShipment(), SeaShipment.class)) {
SeaShipment seaShipment = (SeaShipment) shipmentContainer.getShipment();
if (seaShipment.getCarrier() != null) {
if (seaShipment.getCarrier().equals(carrier)) {
newShipmentContainers.add(shipmentContainer);
}
}
}
}
} else {
newShipmentContainers.addAll(shipmentContainers);
}
return newShipmentContainers;
}
@Override
public List<ShipmentContainer> findByShipmentWithOverstayAndStorageTransaction(Shipment shipment) {
return (List<ShipmentContainer>) findByNamedQueryAndNamedParam("shipmentContainer.findByShipmentWithOverstayAndStorageTransaction",
"shipment", shipment);
}
@Override
public List<ShipmentContainer> findByShipmentWithTurnInTransaction(Shipment shipment) {
return (List<ShipmentContainer>) findByNamedQueryAndNamedParam("shipmentContainer.findByShipmentWithTurnInTransaction",
"shipment", shipment);
}
private <T> List<T> getExecutableCriteriaList(DetachedCriteria criteria, Class<T> c) {
// The class parameter is not strictly needed, but it will ensure you have don't have the wrong type of list.
return (List<T>) criteria.getExecutableCriteria(getSessionCustom()).list();
}
@Override
public ShipmentContainer findByReferenceAndShipmentId(long shipmentId, String reference) {
String duplicateMessage = "There should only be one container with the unique reference " + reference + " on the shipment with id "
+ shipmentId;
return getShipmentContainer(reference, shipmentId, duplicateMessage);
}
@Override
public String findContainerReferenceById(long containerId) {
return (String) getCurrentSession()
.createSQLQuery(
"select reference from container where"
+ " id = :containerId").setParameter("containerId", containerId).uniqueResult();
}
@Override
public ShipmentContainer findFromMultiModalShipment(Shipment shipment, String reference) {
String queryString = """
select c.* from container c
left join multimodalshipment_container mc on mc.containers_id = c.id
left join multimodalshipment ms on ms.id = mc.multimodalshipment_id
left join shipment s on s.id = ms.shipment_id
where s.id = :shipmentid
and c.reference = :reference
""";
NativeQuery<ShipmentContainer> nativeQuery = getCurrentSession().createNativeQuery(queryString, ShipmentContainer.class);
nativeQuery
.setParameter("shipmentid", shipment.getId())
.setParameter("reference", reference);
return nativeQuery.uniqueResult();
}
@Override
@SuppressWarnings("unchecked")
public List<ShipmentContainer> findByIdIn(List<Long> ids) {
if (ids == null || ids.isEmpty()) {
return Collections.emptyList();
}
return getCurrentSession().createQuery(
"SELECT sc FROM ShipmentContainer sc WHERE sc.id IN :ids")
.setParameter("ids", ids)
.getResultList();
}
}