ShipmentRepositoryImpl.java
package com.tradecloud.repository.impl;
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.Currency;
import com.tradecloud.domain.common.Incoterm;
import com.tradecloud.domain.common.IntegratedStaticDataEntityBase;
import com.tradecloud.domain.configuration.CountryGroup;
import com.tradecloud.domain.container.Container;
import com.tradecloud.domain.container.ContainerType;
import com.tradecloud.domain.container.PackingList;
import com.tradecloud.domain.container.ShipmentContainer;
import com.tradecloud.domain.dms.DocumentGroupState;
import com.tradecloud.domain.document.CommercialCreditNote;
import com.tradecloud.domain.document.Document;
import com.tradecloud.domain.document.DocumentState;
import com.tradecloud.domain.document.ServiceProviderCreditNote;
import com.tradecloud.domain.document.invoice.CommercialInvoice;
import com.tradecloud.domain.document.invoice.CostsInvoice;
import com.tradecloud.domain.document.invoice.ServiceProviderInvoice;
import com.tradecloud.domain.event.Event;
import com.tradecloud.domain.event.OrderEventType;
import com.tradecloud.domain.event.ShipmentEvent;
import com.tradecloud.domain.event.ShipmentEventType;
import com.tradecloud.domain.export.ExportParty;
import com.tradecloud.domain.infrastructure.persistence.CriteriaBuilder;
import com.tradecloud.domain.item.ItemType;
import com.tradecloud.domain.item.LineItem;
import com.tradecloud.domain.model.ordermanagement.Consignment;
import com.tradecloud.domain.model.ordermanagement.Order;
import com.tradecloud.domain.model.ordermanagement.PurchaseOrder;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.domain.model.shipment.RequiredType;
import com.tradecloud.domain.model.shipment.ShipmentState;
import com.tradecloud.domain.model.shipment.ShippingMode;
import com.tradecloud.domain.party.ServiceProvider;
import com.tradecloud.domain.place.PlaceOfDischarge;
import com.tradecloud.domain.place.PlaceOfLoading;
import com.tradecloud.domain.sars.Status;
import com.tradecloud.domain.shipment.*;
import com.tradecloud.domain.shipment.clearing.ClearingInstruction;
import com.tradecloud.domain.shipment.clearing.CustomsDeclaration;
import com.tradecloud.dto.api.order.DateCalculationType;
import com.tradecloud.dto.consignment.ShipmentDocumentSearchResult;
import com.tradecloud.dto.invoice.DocumentSearch;
import com.tradecloud.dto.shipment.*;
import com.tradecloud.dto.workload.ShipmentContainerResult;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.ShipmentContainerRepository;
import com.tradecloud.repository.ShipmentRepository;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import com.tradecloud.repository.shipment.ShipmentDailyReportHelper;
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.internal.TypeLocatorImpl;
import org.hibernate.query.NativeQuery;
import org.hibernate.sql.JoinType;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.EnumType;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;
import org.hibernate.type.TypeResolver;
import org.hibernate.type.spi.TypeConfiguration;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.criteria.*;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.Period;
import java.time.ZoneId;
import java.util.*;
import java.util.stream.Collectors;
@Repository(value = "shipmentRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class ShipmentRepositoryImpl extends RepositoryBaseImpl<Shipment, ShipmentSearch> implements ShipmentRepository {
private static final long serialVersionUID = 1L;
private static String ITEM_QUERY = "select count(*) from lineitem li join orders o on (o.id=li.order_id) join consignment " +
"c on (c.id=o.consignment_id) join shipment s on (s.id=c.shipment_id) where {alias}.id=s.id";
private static String CONTAINER_QUERY = "select count(*) from container c where {alias}.id=c.shipment_id";
private static String Order_QUERY = "select count(*) from orders o join purchaseorder po on (po.id=o.id) join consignment " +
"c on (c.id=o.consignment_id) join shipment s on (s.id=c.shipment_id) where {alias}.id=s.id";
private static String SUB_SHIPMENT_QUERY = "select count(*) from subshipment s where {alias}.id=s.shipment_id";
private final long ONE_DAY = 1000 * 60 * 60 * 24;
private Logger log = Logger.getLogger(ShipmentRepositoryImpl.class);
private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
@Autowired
ShipmentContainerRepository shipmentContainerRepository;
@Override
public Shipment findByIdWithConsignments(long id) {
List<Shipment> shipments = (List<Shipment>) findByNamedQueryAndNamedParam("findByIdWithConsignmentsLoaded", "id", id);
return ObjectUtil.first(shipments);
}
@Override
public ShipmentEvent getSignedOffDateFromEvent(long ShipmentID) {
StringBuilder query = new StringBuilder("select * from shipmentevent se ");
query.append("left join shipment_shipmentevent sse on se.id = sse.events_id where sse.shipment_id = :shipmentId");
query.append(" and se.eventtype = :eventType");
NativeQuery<ShipmentEvent> nativeQuery = getSession().createNativeQuery(query.toString(), ShipmentEvent.class);
nativeQuery.setParameter("shipmentId", ShipmentID);
nativeQuery.setParameter("eventType", "SIGNED_OFF");
return nativeQuery.list().get(0);
}
/**
* TODO only supports number and reference for the moment. Search class will
* be set to SeaShipment.class when a voyage number or vessel name has been
* set. Search class will be set to AirShipment.class when a flight number
* or airline name has been set.
*
* @param search
* @return
*/
@Override
public List<Shipment> search(ShipmentSearch search) {
//YFG-560
if (ObjectUtil.anyNotNull(search.getSARSReferenceNumber())) {
return clearingSearch(search);
}
javax.persistence.criteria.CriteriaBuilder criteriaBuilder = getSessionCustom().getCriteriaBuilder();
// Create the main criteria query and root object
javax.persistence.criteria.CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(determineShipmentClass(search));
Root root = criteriaQuery.from(determineShipmentClass(search));
// Create the subquery for distinct IDs
Subquery<Long> subQuery = criteriaQuery.subquery(Long.class);
Root subQueryRoot = subQuery.from(determineShipmentClass(search));
subQuery.select(subQueryRoot.get("id")).distinct(true);
addSearchRestrictions2(criteriaBuilder, subQuery, subQueryRoot, search, true);
// Main query where clause to match IDs from subquery
criteriaQuery.where(root.get("id").in(subQuery));
// Add ordering
SearchMetaParams searchMetaParams = search.getSearchMetaParams();
javax.persistence.criteria.Order orderByCreated = criteriaBuilder.desc(root.get("created"));
if (searchMetaParams != null && searchMetaParams.getOrderBy() != null) {
javax.persistence.criteria.Order order = searchMetaParams.isAsc() ?
criteriaBuilder.asc(root.get(searchMetaParams.getOrderBy())) :
criteriaBuilder.desc(root.get(searchMetaParams.getOrderBy()));
criteriaQuery.orderBy(order);
if (!searchMetaParams.getOrderBy().equals("created")) {
criteriaQuery.orderBy(order, orderByCreated);
}
} else {
criteriaQuery.orderBy(orderByCreated);
}
Query<Shipment> query = getSessionCustom().createQuery(criteriaQuery);
// Handle pagination if applicable
if (searchMetaParams != null) {
query.setFirstResult(searchMetaParams.getRowIndex());
query.setMaxResults(searchMetaParams.getRowCount());
}
return query.getResultList();
}
private void addSearchRestrictions2(javax.persistence.criteria.CriteriaBuilder cb, AbstractQuery cq, Root root, ShipmentSearch search, boolean setDistinct) {
List<Predicate> predicates = new ArrayList<>();
predicates.add(cb.equal(root.get("imports"), search.isImports()));
likePredicate(cb, search.getNumber(), root.get("number"), predicates);
likePredicate(cb, search.getReference(), root.get("reference"), predicates);
if (StringUtils.isNotEmpty(search.getMasterBillOfLadingReference())) {
predicates.add(cb.like(cb.lower(root.get("masterBillOfLadingReference")), "%" + search.getMasterBillOfLadingReference().toLowerCase() + "%"));
}
if (StringUtils.isNotEmpty(search.getHouseWayBillNumber())) {
predicates.add(cb.like(cb.lower(root.get("houseAirwayBillNumber")), "%" + search.getHouseWayBillNumber().toLowerCase() + "%"));
}
if (StringUtils.isNotEmpty(search.getMasterAirwayBillNumber())) {
Predicate disjunction = cb.or(
cb.like(cb.lower(root.get("masterAirwayBillNumber")), "%" + search.getMasterAirwayBillNumber().toLowerCase() + "%"),
cb.like(cb.lower(root.get("houseAirwayBillNumber")), "%" + search.getMasterAirwayBillNumber().toLowerCase() + "%")
);
predicates.add(disjunction);
}
if (StringUtils.isNotEmpty(search.getBillOfLadingReference())) {
Predicate disjunction = cb.or(
cb.like(cb.lower(root.get("billOfLadingReference")), "%" + search.getBillOfLadingReference().toLowerCase() + "%"),
cb.like(cb.lower(root.get("masterBillOfLadingReference")), "%" + search.getBillOfLadingReference().toLowerCase() + "%")
);
predicates.add(disjunction);
}
if (!CollectionUtils.isEmpty(search.getApplicableStates())) {
predicates.add(root.get("state").in(search.getApplicableStates()));
} else if (search.getState() != null) {
// Default active state if no applicable states
predicates.add(cb.equal(root.get("state"), search.getState()));
} else {
predicates.add(cb.notEqual(root.get("state"), ShipmentState.DELETED));
}
if (search.getEvent() != null) {
Join<Shipment, Event> eventJoin = root.joinList("events", javax.persistence.criteria.JoinType.INNER);
predicates.add(cb.equal(eventJoin.get("eventType"), search.getEvent()));
}
CriteriaBuilder.addEventDateRangeSearchCriteria2(cb, predicates, root, search.getSignedOffDateRange(), ShipmentEventType.SIGNED_OFF);
// Add date range for "warehouseDeliveryDate"
CriteriaBuilder.addDateRangeCriteria2(cb, predicates, root, "warehouseDeliveryDate", search.getWarehouseDeliveryDateFrom(),
search.getWarehouseDeliveryDateTo());
if (search.getSignedOffDateFrom() != null && search.getSignedOffDateTo() != null) {
Join<Object, Object> lastSignedOffJoin = root.join("lastSignedOff");
// Create the 'between' condition on the "created" column
Predicate dateRangePredicate = cb.between(lastSignedOffJoin.get("created"), search.getSignedOffDateFrom(), search.getSignedOffDateTo());
predicates.add(dateRangePredicate);
}
CriteriaBuilder.addDateRangeCriteria2(cb, predicates, root, "created", search.getCreatedDateFrom(), search.getCreatedDateTo(), true);
CriteriaBuilder.addDateRangeCriteria2(cb, predicates, root, "estimatedArrivalDateAtPlaceOfDischarge", search.getEstimateArrivalDateAtPODFrom(),
search.getEstimateArrivalDateAtPODTo(), true);
CriteriaBuilder.addDateRangeCriteria2(cb, predicates, root, "billOfLadingDate", search.getBillOfLadingDateFrom(),
search.getBillOfLadingDateTo(), true);
addDateRangeCriteria2(cb, root, predicates, search.getHouseWayBillDate(), "houseAirwayBillIssueDate");
addDateRangeCriteria2(cb, root, predicates, search.getMasterAirwayBillIssueDate(), "masterAirwayBillIssueDate");
addDateRangeCriteria2(cb, root, predicates, search.getVesselBerthedDate(), "vesselBerthedDate");
addDateRangeCriteria2(cb, root, predicates, search.getScheduledDepartureDate(), "scheduledDepartureDate");
addDateRangeCriteria2(cb, root, predicates, search.getActualDateOfDeparture(), "actualDepartureDate");
addDateRangeCriteria2(cb, root, predicates, search.getActualArrivalDateAtPOD(), "arrivalDateAtPlaceOfDischarge");
addDateRangeCriteria2(cb, root, predicates, search.getSettlementDate(), "settlementDate");
addDateRangeCriteria2(cb, root, predicates, search.getShippedOnBoardDate(), "shippedOnBoardDate");
if (search.getVesselName() != null) {
Predicate vesselPredicate = cb.like(cb.lower(root.get("shippingVessel")), "%" + search.getVesselName().toLowerCase() + "%");
predicates.add(vesselPredicate);
}
if (search.getVoyageNumber() != null) {
Predicate voyagePredicate = cb.like(cb.lower(root.get("voyageNumber")), "%" + search.getVoyageNumber().toLowerCase() + "%");
predicates.add(voyagePredicate);
}
if (StringUtils.isNotEmpty(search.getAirlineName())) {
predicates.add(cb.like(cb.lower(root.get("airlineName")), "%" + search.getAirlineName().toLowerCase() + "%"));
}
if (StringUtils.isNotEmpty(search.getFlightNumber())) {
predicates.add(cb.equal(root.get("flightNumber"), search.getFlightNumber()));
}
Join<Shipment, ShipmentShippingInfo> shippingInfoJoin = root.join("shippingInfo", javax.persistence.criteria.JoinType.LEFT);
equalsPredicate(cb, root, search.getPlaceOfDischarge(), predicates, shippingInfoJoin.get("placeOfDischarge"));
equalsPredicate(cb, root, search.getFreightForwarder(), predicates, shippingInfoJoin.get("freightForwarder"));
// Clearing Agent search
equalsPredicate(cb, root, search.getClearingAgent(), predicates, shippingInfoJoin.get("clearingAgent"));
if (search.getMultiModalShippingMode() != null) {
Predicate mmMode = shippingInfoJoin.get("multiModalShippingMode").in(search.getMultiModalShippingMode());
// Combine the conditions using OR
predicates.add(mmMode);
}
if (search.getShippingLine() != null) {
predicates.add(cb.equal(root.get("carrier"), search.getShippingLine()));
}
equalsPredicate(cb, root, search.getPlaceOfLoading(), predicates, shippingInfoJoin.get("placeOfLoading"));
if (search.getSubShipmentReference() != null) {
Join<?, ?> subShipmentsJoin = root.join("subShipments");
Predicate subShipmentReferencePredicate = cb.like(cb.lower(subShipmentsJoin.get("reference")), "%" + search.getSubShipmentReference().toLowerCase() + "%");
predicates.add(subShipmentReferencePredicate);
}
Set<OrganisationalUnit> organisationalUnits = getOrganisationalUnits(search);
boolean orderPathAdded = false;
Join<Shipment, Consignment> consignmentJoin = null;
Join<Consignment, Order> orderJoin = null;
Join<Consignment, PurchaseOrder> purchaseOrderJoin = null;
if (!search.isNoConsignmentLinked()) {
if (!organisationalUnits.isEmpty() || search.getSupplier() != null || search.getBuyer() != null ||
(search.getOrder() != null && !search.getOrder().isEmpty()) || !search.getGrvDateRange().isEmpty()
|| search.getShippingReference() != null) {
consignmentJoin = root.joinSet("consignments", javax.persistence.criteria.JoinType.INNER);
orderJoin = consignmentJoin.joinSet("orders", javax.persistence.criteria.JoinType.INNER);
if (search.getSupplier() != null) {
purchaseOrderJoin = cb.treat(orderJoin, PurchaseOrder.class);
Predicate orderIsPurchaseOrder = cb.equal(orderJoin.type(), cb.literal(PurchaseOrder.class));
predicates.add(orderIsPurchaseOrder);
}
orderPathAdded = true;
}
if (!search.isNoConsignmentLinked() && !organisationalUnits.isEmpty()) {
Expression<OrganisationalUnit> organisationalUnitPath = orderJoin.get("organisationalUnit");
Predicate organisationalUnitPredicate = organisationalUnitPath.in(organisationalUnits);
predicates.add(organisationalUnitPredicate);
}
// Add more restrictions related to orders, supplier, buyer, etc.
if (search.getSupplier() != null) {
Predicate supplierCondition = cb.equal(purchaseOrderJoin.get("supplier").get("id"), search.getSupplier().getId());
predicates.add(supplierCondition);
}
if (search.getBuyer() != null) {
predicates.add(cb.equal(purchaseOrderJoin.get("buyer"), search.getBuyer()));
}
if (orderJoin != null) {
likePredicate(cb, search.getOrder(), orderJoin.get("orderReference"), predicates);
likePredicate(cb, search.getShippingReference(), orderJoin.get("shippingInformation").get("shippingReference"), predicates);
}
if (!search.getGrvDateRange().isEmpty()) {
Path<Date> grvDatePath = orderJoin.get("orderDates").get("goodsReceivedDate");
// Create the between predicate for the GRV date range
Predicate grvDatePredicate = cb.between(
grvDatePath,
search.getGrvDateRange().getFrom(),
search.getGrvDateRange().getTo()
);
predicates.add(grvDatePredicate);
}
} else if (search.isNoConsignmentLinked()) {
Subquery<Long> subquery = cq.subquery(Long.class);
Root<Shipment> subqueryRoot = subquery.from(Shipment.class);
Join<Shipment, Consignment> consignmentJoin2 = subqueryRoot.join("consignments", javax.persistence.criteria.JoinType.LEFT);
subquery.select(subqueryRoot.get("id")).distinct(true)
.where(cb.isNull(consignmentJoin2.get("id")));
Predicate shipmentIdInSubquery = cb.in(root.get("id")).value(subquery);
predicates.add(shipmentIdInSubquery);
}
if (StringUtils.isNotEmpty(search.getContainerReference()) || search.getContainerFinalDestination() != null) {
Join<Shipment, Container> containerJoin = root.joinSet("containers", javax.persistence.criteria.JoinType.LEFT);
likePredicate(cb, search.getContainerReference(), containerJoin.get("reference"), predicates);
equalsPredicate(cb, root, search.getContainerFinalDestination(), predicates, containerJoin.get("finalDestination"));
}
if (search.getItemDescription() != null || search.getItemReference() != null) {
if (!orderPathAdded) {
consignmentJoin = root.joinSet("consignments", javax.persistence.criteria.JoinType.INNER);
orderJoin = consignmentJoin.joinSet("orders", javax.persistence.criteria.JoinType.INNER);
}
Join<Order, LineItem> itemJoin = orderJoin.joinSet("lineItems", javax.persistence.criteria.JoinType.INNER);
likePredicate(cb, search.getItemDescription(), itemJoin.get("description"), predicates);
likePredicate(cb, search.getItemReference(), itemJoin.get("code"), predicates);
}
if (search.getCommercialInvoicingStates() != null && !search.getCommercialInvoicingStates().isEmpty()) {
// Create a left join for commercialInvoices
Join<Shipment, CommercialInvoice> ciJoin = root.joinSet("commercialInvoices", javax.persistence.criteria.JoinType.LEFT);
// Create a disjunction (OR condition) to check either the state is null or it matches one of the states
Predicate stateIsNull = cb.isNull(ciJoin.get("state"));
Predicate stateInList = ciJoin.get("state").in(search.getCommercialInvoicingStates());
// Combine the conditions using OR
predicates.add(cb.or(stateIsNull, stateInList));
}
if (search.getServiceProviderInvoicingStates() != null && !search.getServiceProviderInvoicingStates().isEmpty()) {
Join<Shipment, ServiceProviderInvoice> ciJoin = root.joinSet("serviceProviderInvoices", javax.persistence.criteria.JoinType.LEFT);
// Create a disjunction (OR condition) to check either the state is null or it matches one of the states
Predicate stateIsNull = cb.isNull(ciJoin.get("state"));
Predicate stateInList = ciJoin.get("state").in(search.getServiceProviderInvoicingStates());
// Combine the conditions using OR
predicates.add(cb.or(stateIsNull, stateInList));
}
if (search.getSARSReferenceNumber() != null) {
Subquery<Long> subquery = cq.subquery(Long.class);
Root<Shipment> shipmentRoot = subquery.from(Shipment.class);
Subquery<Long> subquery1 = subquery.subquery(Long.class);
Root<Shipment> s1 = subquery1.from(Shipment.class);
Join<Shipment, ServiceProviderInvoice> spi = s1.joinSet("serviceProviderInvoices", javax.persistence.criteria.JoinType.LEFT);
Join<ServiceProviderInvoice, String> lrnNumbersJoin = spi.join("lrnNumbers", javax.persistence.criteria.JoinType.LEFT);
Join<ServiceProviderInvoice, String> mrnNumbersJoin = spi.join("mrnNumbers", javax.persistence.criteria.JoinType.LEFT);
subquery1.select(s1.get("id"))
.where(cb.or(
cb.like(mrnNumbersJoin, "%" + search.getSARSReferenceNumber() + "%"),
cb.like(lrnNumbersJoin, "%" + search.getSARSReferenceNumber() + "%")
));
Subquery<Long> subquery2 = subquery.subquery(Long.class);
Root<Shipment> s2 = subquery2.from(Shipment.class);
Join<Shipment, CustomsDeclaration> cd = s2.joinSet("customsDeclarations", javax.persistence.criteria.JoinType.LEFT);
Join<Shipment, ShipmentShippingInfo> ssi = s2.join("shippingInfo", javax.persistence.criteria.JoinType.LEFT);
Join<ShipmentShippingInfo, ServiceProvider> ca = ssi.join("clearingAgent", javax.persistence.criteria.JoinType.LEFT);
Expression<String> castToText = cb.function("TEXT", String.class, cd.get("number"));
subquery2.select(s2.get("id"))
.where(cb.or(
cb.like(cb.function("concat", String.class,
ca.get("customsCode"),
cd.get("placeOfCustomsEntry"),
cb.function("regexp_replace", String.class,
cb.function("to_char", String.class, cd.get("declarationDate"),
cb.literal("YYYYMMDD")), cb.literal("[-]"), cb.literal("")),
cb.function("LPAD", String.class, castToText, cb.literal(6), cb.literal("0"))),
"%" + search.getSARSReferenceNumber() + "%"),
cb.like(cd.get("billOfEntryNumber"), "%" + search.getSARSReferenceNumber() + "%")
));
// Combine both subqueries into the main subquery
subquery.select(cb.count(shipmentRoot.get("id")))
.where(cb.or(
cb.in(shipmentRoot.get("id")).value(subquery1),
cb.in(shipmentRoot.get("id")).value(subquery2)
));
predicates.add(cb.gt(subquery, 0));
}
if (search.isHasCommercialInvoices()) {
cq.where(cb.isNotNull(root.get("commercialInvoices")));
}
if (search.getSelectedRequiredTypes() != null) {
List<RequiredType> requiredTypes = Arrays.asList(search.getSelectedRequiredTypes());
if (requiredTypes.contains(RequiredType.CLEARING_INSTRUCTION)) {
Subquery<Long> subquery = cq.subquery(Long.class);
Root<ClearingInstruction> clearingInstructionRoot = subquery.from(ClearingInstruction.class);
subquery.select(cb.literal(1L));
subquery.where(cb.equal(clearingInstructionRoot.get("shipment").get("id"), root.get("id")));
predicates.add(cb.not(cb.exists(subquery)));
}
if (requiredTypes.contains(RequiredType.COMMERCIAL_INVOICE))
predicates.add(cb.isEmpty(root.get("commercialInvoices")));
if (requiredTypes.contains(RequiredType.CUSTOMS_DECLARATION))
predicates.add(cb.isEmpty(root.get("customsDeclarations")));
if (requiredTypes.contains(RequiredType.BILL_OF_LADING_REFERENCE)) {
predicates.add(cb.isNull(root.get("billOfLadingReference")));
predicates.add(cb.equal(root.get("shippingMode"), ShippingMode.SEA));
}
if (requiredTypes.contains(RequiredType.BILL_OF_LADING_DATE)) {
predicates.add(cb.isNull(root.get("billOfLadingDate")));
predicates.add(cb.equal(root.get("shippingMode"), ShippingMode.SEA));
}
if (requiredTypes.contains(RequiredType.PACKING_LIST_INFO)) {
// Create a join for packingList and containers
Join<Shipment, PackingList> pl = root.join("packingList", javax.persistence.criteria.JoinType.LEFT);
Join<PackingList, Container> plc = pl.join("containers", javax.persistence.criteria.JoinType.LEFT);
predicates.add(cb.isEmpty(pl.get("containers")));
predicates.add(cb.isNotEmpty(plc.get("orders")));
}
}
if (search.getSelectedClearingStatus().length > 0) {
Root<ClearingInstruction> ci = cq.from(ClearingInstruction.class);
Join<ClearingInstruction, Shipment> cli = ci.join("shipment", javax.persistence.criteria.JoinType.RIGHT);
}
if (!predicates.isEmpty()) {
Predicate[] toArray = predicates.stream().toArray(Predicate[]::new);
cq.where(toArray);
}
}
private static void likePredicate(javax.persistence.criteria.CriteriaBuilder cb, String find, Path path, List<Predicate> predicates) {
if (StringUtils.isNotEmpty(find)) {
predicates.add(cb.like(cb.lower(path), "%" + find.toLowerCase() + "%"));
}
}
private static void equalsPredicate(javax.persistence.criteria.CriteriaBuilder cb, Root root, ServiceProvider serviceProvider, List<Predicate> predicates,
Path path) {
if (serviceProvider != null) {
Predicate carrierPredicate = cb.equal(path, serviceProvider);
predicates.add(carrierPredicate);
}
}
private static void equalsPredicate(javax.persistence.criteria.CriteriaBuilder cb, Root root, StaticDataEntityBase entityBase, List<Predicate> predicates,
Path path) {
if (entityBase != null) {
Predicate carrierPredicate = cb.equal(path, entityBase);
predicates.add(carrierPredicate);
}
}
private static void equalsPredicate(javax.persistence.criteria.CriteriaBuilder cb, Root root, IntegratedStaticDataEntityBase entityBase, List<Predicate> predicates,
Path path) {
if (entityBase != null) {
Predicate carrierPredicate = cb.equal(path, entityBase);
predicates.add(carrierPredicate);
}
}
private Class<? extends Shipment> determineShipmentClass(ShipmentSearch search) {
if (ShippingMode.SEA.equals(search.getMode())) {
return SeaShipment.class;
} else if (ShippingMode.AIR.equals(search.getMode())) {
return AirShipment.class;
} else if (search.getVoyageNumber() != null || search.getVesselName() != null
|| search.getBillOfLadingReference() != null || search.getBillOfLadingDateFrom() != null
|| search.getBillOfLadingDateTo() != null) {
return SeaShipment.class;
} else if (search.getAirlineName() != null || search.getFlightNumber() != null || search.getMasterAirwayBillNumber() != null
|| (search.getMasterAirwayBillIssueDate() != null && !search.getMasterAirwayBillIssueDate().isEmpty())
|| (search.getHouseWayBillDate() != null && !search.getHouseWayBillDate().isEmpty())
|| search.getHouseWayBillNumber() != null) {
return AirShipment.class;
} else if (search.getCarrier() != null) {
return SeaShipment.class;
} else {
return Shipment.class;
}
}
private void addDateRangeCriteria2(javax.persistence.criteria.CriteriaBuilder cb, Root root, List<Predicate> predicates, DateRange masterAirwayBillIssueDate,
String masterAirwayBillIssueDate1) {
if (masterAirwayBillIssueDate != null) {
CriteriaBuilder.addDateRangeCriteria2(cb, predicates, root, masterAirwayBillIssueDate1, masterAirwayBillIssueDate.getFrom(),
masterAirwayBillIssueDate.getTo(), true);
}
}
private DetachedCriteria getDetachedCriteria(ShipmentSearch search) {
// Set shipping criteria class to SeaShipment.class when search confirms
// a voyage number or vessel name.
// Set shipping criteria class to AirShipment.class when search confirms
// a flight number or airline name.
DetachedCriteria criteria;
if (ShippingMode.SEA.equals(search.getMode())) {
criteria = DetachedCriteria.forClass(SeaShipment.class);
} else if (ShippingMode.AIR.equals(search.getMode())) {
criteria = DetachedCriteria.forClass(AirShipment.class);
} else if (search.getVoyageNumber() != null || search.getVesselName() != null) {
criteria = DetachedCriteria.forClass(SeaShipment.class);
} else if (search.getAirlineName() != null || search.getFlightNumber() != null) {
criteria = DetachedCriteria.forClass(AirShipment.class);
} else if (search.getCarrier() != null) {
criteria = DetachedCriteria.forClass(SeaShipment.class);
} else {
criteria = DetachedCriteria.forClass(getPersistentClass());
}
return criteria;
}
@Override
public List<Long> searchReport(ShipmentSearch search) {
// Set shipping criteria class to SeaShipment.class when search confirms
// a voyage number or vessel name.
// Set shipping criteria class to AirShipment.class when search confirms
// a flight number or airline name.
DetachedCriteria criteria = getDetachedCriteria(search);
addSearchRestrictions(criteria, search, false);
criteria.setProjection(Projections.property("id"));
return criteria.getExecutableCriteria(getSessionCustom()).list();
}
@Override
public List<Shipment> fullSearch(ShipmentSearch search) {
DetachedCriteria criteria;
if (ShippingMode.AIR.equals(search.getMode())) {
criteria = DetachedCriteria.forClass(AirShipment.class);
} else if (ShippingMode.SEA.equals(search.getMode())) {
criteria = DetachedCriteria.forClass(SeaShipment.class);
} else {
criteria = DetachedCriteria.forClass(getPersistentClass());
}
addSearchRestrictions(criteria, search, true);
// ensure distinct results, ie: no duplicates, seems to be a problem
// with the Criteria search.
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
// only set SearchMetaParameters to null to switch off pagination
return getExecutableCriteriaList(criteria, null);
}
@Override
public long count(ShipmentSearch search) {
//YFG-560
if (ObjectUtil.anyNotNull(search.getSARSReferenceNumber())) {
return clearingSearchCount(search);
}
javax.persistence.criteria.CriteriaBuilder builder = getSessionCustom().getCriteriaBuilder();
CriteriaQuery criteriaQuery = builder.createQuery(Long.class);
Root root = criteriaQuery.from(determineShipmentClass(search));
criteriaQuery.select(builder.countDistinct(root));
addSearchRestrictions2(builder, criteriaQuery, root, search, true);
org.hibernate.query.Query<Long> query = getSessionCustom().createQuery(criteriaQuery);
return query.uniqueResult();
}
private long clearingSearchCount(ShipmentSearch search) {
String query = """
select count(distinct(s)) from shipment s
left join customsdeclaration cd on s.id = cd.shipment_id
left join costsinvoice coi on s.id = coi.shipment_id
left join serviceproviderinvoice spi on coi.id = spi.id
left join lrnnumbers lrn on spi.id = lrn.id
left join mrnnumbers mrn on spi.id = mrn.id
where
cd.mrnnumber ilike :field
or cd.casenumber ilike :field
or cd.lrnnumber ilike :field
or lrn.number ilike :field
or mrn.number ilike :field
""";
BigInteger result = (BigInteger) getCurrentSession().createSQLQuery(query)
.setParameter("field", "%" + search.getSARSReferenceNumber() + "%")
.uniqueResult();
return result.longValue();
}
private List<Shipment> clearingSearch(ShipmentSearch search) {
String query = """
select DISTINCT(s.id)
from shipment s
left join customsdeclaration cd on s.id = cd.shipment_id
left join costsinvoice coi on s.id = coi.shipment_id
left join serviceproviderinvoice spi on coi.id = spi.id
left join lrnnumbers lrn on spi.id = lrn.id
left join mrnnumbers mrn on spi.id = mrn.id
where
cd.mrnnumber ilike :field
or cd.casenumber ilike :field
or cd.lrnnumber ilike :field
or lrn.number ilike :field
or mrn.number ilike :field
""";
List<BigInteger> shipmentIds = getCurrentSession().createNativeQuery(query)
.setParameter("field", "%" + search.getSARSReferenceNumber() + "%")
.getResultList();
List<Long> ids = new ArrayList<>();
for (BigInteger integer : shipmentIds) {
ids.add((Long.valueOf(integer.longValue())));
}
org.hibernate.query.Query<Shipment> query1 = getCurrentSession().createQuery("""
select s from Shipment s where s.id in(:ids)
order by s.created desc
""", Shipment.class);
if (search.getSearchMetaParams() != null) {
query1.setMaxResults(search.getSearchMetaParams().getRowCount())
.setFirstResult(search.getSearchMetaParams().getRowIndex());
}
return query1.setParameterList("ids", ids).getResultList();
}
@Override
public List<String> findFreeTextComments(Shipment shipment) {
String query = "SELECT reason FROM shipment_freetextcomments WHERE shipment_id = :shipmentId ORDER BY reason ASC";
@SuppressWarnings("unchecked")
List<String> results = getCurrentSession().createSQLQuery(query).setParameter("shipmentId", shipment.getId()).list();
return results;
}
/**
* Method will add constricting criteria fields to the 'criteria', over and
* above that the method will ensure that no duplicates are returned and
* sets the ordering according to 'created' int descending order.
*
* @param criteria
* @param search
* @param setDistinct
*/
private void addSearchRestrictions(DetachedCriteria criteria, ShipmentSearch search, boolean setDistinct) {
CriteriaBuilder.addEqRestriction(criteria, "imports", search.isImports());//todo
CriteriaBuilder.addiLikeAnyRestriction(criteria, "number", search.getNumber());
CriteriaBuilder.addiLikeAnyRestriction(criteria, "reference", search.getReference());
CriteriaBuilder.addiLikeAnyRestriction(criteria, "masterBillOfLadingReference", search.getMasterBillOfLadingReference());
CriteriaBuilder.addiLikeAnyRestriction(criteria, "houseAirwayBillNumber", search.getHouseWayBillNumber());
if (StringUtils.isNotEmpty(search.getMasterAirwayBillNumber())) {
criteria.add(Restrictions.disjunction().add(Restrictions.ilike("masterAirwayBillNumber",
search.getMasterAirwayBillNumber(), MatchMode.ANYWHERE))
.add(Restrictions.ilike("houseAirwayBillNumber", search.getMasterAirwayBillNumber(), MatchMode.ANYWHERE)));
}
if (StringUtils.isNotEmpty(search.getBillOfLadingReference())) {
criteria.add(Restrictions.disjunction().add(Restrictions.ilike("billOfLadingReference",
search.getBillOfLadingReference(), MatchMode.ANYWHERE))
.add(Restrictions.ilike("masterBillOfLadingReference", search.getBillOfLadingReference(), MatchMode.ANYWHERE)));
}
if (!CollectionUtils.isEmpty(search.getApplicableStates())) {
criteria.add(Restrictions.in("state", search.getApplicableStates()));
} else {
CriteriaBuilder.addEqActiveStateRestriction(criteria, "state", search.getState(), ShipmentState.DELETED);
}
if (search.getEvent() != null) {
criteria.createAlias("events", "ev");
criteria.add(Restrictions.eq("ev.eventType", search.getEvent()));
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
}
CriteriaBuilder.addEventDateRangeSearchCriteria(criteria, search.getSignedOffDateRange(), ShipmentEventType.SIGNED_OFF);
CriteriaBuilder.addDateRangeCriteria(criteria, "warehouseDeliveryDate", search.getWarehouseDeliveryDateFrom(),
search.getWarehouseDeliveryDateTo());
if (search.getSignedOffDateFrom() != null && search.getSignedOffDateTo() != null) {
criteria.createAlias("lastSignedOff", "ls");
criteria.add(Restrictions.between("ls.created", search.getSignedOffDateFrom(), search.getSignedOffDateTo()));
}
CriteriaBuilder.addDateRangeCriteria(criteria, "created", search.getCreatedDateFrom(), search.getCreatedDateTo(), true);
CriteriaBuilder.addDateRangeCriteria(criteria, "estimatedArrivalDateAtPlaceOfDischarge", search.getEstimateArrivalDateAtPODFrom(),
search.getEstimateArrivalDateAtPODTo(), true);
CriteriaBuilder.addDateRangeCriteria(criteria, "billOfLadingDate", search.getBillOfLadingDateFrom(),
search.getBillOfLadingDateTo(), true);
addDateRangeCriteria(criteria, search.getHouseWayBillDate(), "houseAirwayBillIssueDate");
addDateRangeCriteria(criteria, search.getMasterAirwayBillIssueDate(), "masterAirwayBillIssueDate");
addDateRangeCriteria(criteria, search.getVesselBerthedDate(), "vesselBerthedDate");
addDateRangeCriteria(criteria, search.getScheduledDepartureDate(), "scheduledDepartureDate");
addDateRangeCriteria(criteria, search.getActualDateOfDeparture(), "actualDepartureDate");
addDateRangeCriteria(criteria, search.getActualArrivalDateAtPOD(), "arrivalDateAtPlaceOfDischarge");
addDateRangeCriteria(criteria, search.getSettlementDate(), "settlementDate");
addDateRangeCriteria(criteria, search.getShippedOnBoardDate(), "shippedOnBoardDate");
// deal with vessel name and/or voyage number
if (search.getVesselName() != null) {
CriteriaBuilder.addiLikeAnyRestriction(criteria, "shippingVessel", search.getVesselName());
}
// ensure that shipment search pertains to sea shipment or null before
// attempting a search on voyage number
if (search.getVoyageNumber() != null) {
CriteriaBuilder.addiLikeAnyRestriction(criteria, "voyageNumber", search.getVoyageNumber());
}
// deal with airline name and/or flight number
if (search.getAirlineName() != null) {
CriteriaBuilder.addiLikeAnyRestriction(criteria, "airlineName", search.getAirlineName());
}
if (search.getFlightNumber() != null) {
criteria.add(Restrictions.eq("flightNumber", search.getFlightNumber()));
}
if (search.getShippingLine() != null) {
criteria.add(Restrictions.eq("carrier", search.getShippingLine()));
}
criteria.createAlias("shippingInfo", "si");
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.getClearingAgent() != null) {
criteria.add(Restrictions.eq("si.clearingAgent", search.getClearingAgent()));
}
if (search.getCarrier() != null) {
criteria.add(Restrictions.eq("carrier", search.getCarrier()));
}
if (search.getPlaceOfLoading() != null) {
criteria.add(Restrictions.eq("si.placeOfLoading", search.getPlaceOfLoading()));
}
if (search.getSubShipmentReference() != null) {
criteria.createAlias("subShipments", "ss");
CriteriaBuilder.addiLikeAnyRestriction(criteria, "ss.reference", search.getSubShipmentReference());
}
if (search.getMultiModalShippingMode() != null) {
criteria.add(Restrictions.eq("si.multiModalShippingMode", search.getMultiModalShippingMode()));
}
Set<OrganisationalUnit> organisationalUnits = getOrganisationalUnits(search);//todo
boolean orderPathAdded = false;
if (!search.isNoConsignmentLinked()) {
if (!organisationalUnits.isEmpty() || search.getSupplier() != null || search.getBuyer() != null ||
(search.getOrder() != null && !search.getOrder().isEmpty()) || !search.getGrvDateRange().isEmpty()
|| search.getShippingReference() != null) {
//left join doesn't work, hibernate automatically add sorting which breaks the query
criteria.createAlias("consignments", "c");
criteria.createAlias("c.orders", "porder");
orderPathAdded = true;
}
if (!search.isNoConsignmentLinked() && !organisationalUnits.isEmpty()) {
criteria.add(Restrictions.in("porder.organisationalUnit", organisationalUnits));
}
if (search.getSupplier() != null) {
criteria.add(Restrictions.eq("porder.supplier", search.getSupplier()));
}
if (search.getBuyer() != null) {
criteria.add(Restrictions.eq("porder.buyer", search.getBuyer()));
}
if (search.getOrder() != null && !search.getOrder().isEmpty()) {
CriteriaBuilder.addiLikeAnyRestriction(criteria, "porder.orderReference", search.getOrder());
}
if (search.getShippingReference() != null && !search.getShippingReference().isEmpty()) {
criteria.createAlias("porder.shippingInformation", "psi");
CriteriaBuilder.addiLikeAnyRestriction(criteria, "psi.shippingReference",
search.getShippingReference());
}
if (!search.getGrvDateRange().isEmpty()) {
criteria.add(Restrictions.between("porder.orderDates.goodsReceivedDate",
search.getGrvDateRange().getFrom(), search.getGrvDateRange().getTo()));
}
} else if (search.isNoConsignmentLinked()) {
criteria.add(Restrictions.sqlRestriction(" {alias}.id in (select distinct(s.id) from shipment s " +
"left join consignment c on (s.id=c.shipment_id) where c.id is null)"));
}
if ((null != search.getContainerReference() && search.getContainerReference().trim().length() > 0) ||
search.getContainerFinalDestination() != null) {
criteria.createAlias("containers", "cont");
if ((null != search.getContainerReference() && search.getContainerReference().trim().length() > 0)) {
CriteriaBuilder.addiLikeAnyRestriction(criteria, "cont.reference", search.getContainerReference());
}
if (search.getContainerFinalDestination() != null) {
CriteriaBuilder.addEqRestriction(criteria, "cont.finalDestination", search.getContainerFinalDestination());
}
}
if (search.getItemDescription() != null || search.getItemReference() != null) {
if (!orderPathAdded) {
criteria.createAlias("consignments", "c");
criteria.createAlias("c.orders", "porder");
}
criteria.createAlias("porder.lineItems", "item");
StringBuilder itemQueryBuilder = new StringBuilder(ITEM_QUERY);
if (search.getItemDescription() != null) {
CriteriaBuilder.addiLikeAnyRestriction(criteria, "item.description", search.getItemDescription());
}
if (search.getItemReference() != null) {
CriteriaBuilder.addiLikeAnyRestriction(criteria, "item.code", search.getItemReference());
}
}
if (search.getCommercialInvoicingStates() != null && !search.getCommercialInvoicingStates().isEmpty()) {
criteria.createAlias("commercialInvoices", "ci", JoinType.LEFT_OUTER_JOIN);
// CriteriaBuilder.addDisjunction(criteria, "ci.state", search.getInvoicingStates());
criteria.add(Restrictions.disjunction().add(Restrictions.isNull("ci.state")).add(Restrictions.in("ci.state",
search.getCommercialInvoicingStates())));
}
if (search.getServiceProviderInvoicingStates() != null && !search.getServiceProviderInvoicingStates().isEmpty()) {
criteria.createAlias("serviceProviderInvoices", "spi", JoinType.LEFT_OUTER_JOIN);
criteria.add(Restrictions.disjunction().add(Restrictions.isNull("spi.state")).add(Restrictions.in("spi.state",
search.getServiceProviderInvoicingStates())));
}
if (search.getSARSReferenceNumber() != null) {
criteria.add(Restrictions.disjunction().add(
Restrictions.sqlRestriction(("(SELECT COUNT(*) FROM shipment " +
"WHERE {alias}.id IN ((SELECT s1.id FROM shipment AS s1 " +
"LEFT JOIN shipment_serviceproviderinvoice AS s_spi ON s_spi.shipment_id = s1.id " +
"LEFT JOIN serviceproviderinvoice AS spi ON s_spi.serviceproviderinvoices_id = spi.id " +
"LEFT JOIN lrnnumbers AS lrn ON lrn.id = spi.id " +
"LEFT JOIN mrnnumbers AS mrn ON mrn.id = spi.id " +
"WHERE " +
"mrn.number LIKE '#' " +
"OR lrn.number LIKE '#' " +
")) OR {alias}.id IN ((SELECT s2.id FROM shipment AS s2 " +
"LEFT JOIN shipment_customsdeclaration AS scd ON scd.shipment_id = s2.id " +
"LEFT JOIN customsdeclaration AS cd ON scd.customsdeclarations_id = cd.id " +
"LEFT JOIN shipmentshippinginfo ssi on ssi.id = s2.shippinginfo_id " +
"LEFT JOIN serviceprovider ca on ssi.clearingagent_id = ca.id " +
"WHERE cd.mrnnumber LIKE '%#%' " +
"OR concat(ca.customscode, cd.placeofcustomsentry_code, " +
"regexp_replace(to_char(cd.declarationdate, 'YYYYMMDD'), '[-]', ''), " +
"LPAD(cd.number::text, 6, '0')) LIKE '%#%' " +
"OR cd.billofentrynumber LIKE '%#%' " +
"))) > 0 ")
.replaceAll("[#]", search.getSARSReferenceNumber())))
.add(Restrictions.sqlRestriction(("(SELECT COUNT(*) FROM shipment " +
"WHERE {alias}.id IN ((SELECT s2.id FROM shipment AS s2 " +
"LEFT JOIN shipment_customsdeclaration AS scd ON scd.shipment_id = s2.id " +
"LEFT JOIN customsdeclaration AS cd ON scd.customsdeclarations_id = cd.id " +
"LEFT JOIN shipmentshippinginfo ssi on ssi.id = s2.shippinginfo_id " +
"LEFT JOIN serviceprovider ca on ssi.clearingagent_id = ca.id " +
"WHERE cd.casenumber LIKE '%#%' " +
"))) > 0 ")
.replaceAll("[#]", search.getSARSReferenceNumber()))));
}
if (search.isHasCommercialInvoices()) {
criteria.add(Restrictions.isNotNull("commercialInvoices"));
}
if (search.getSelectedRequiredTypes() != null) {
List<RequiredType> requiredTypes = Arrays.asList(search.getSelectedRequiredTypes());
if (requiredTypes.contains(RequiredType.CLEARING_INSTRUCTION)) {
criteria.add(Restrictions.isEmpty("clearingInstructions"));
}
if (requiredTypes.contains(RequiredType.COMMERCIAL_INVOICE)) {
criteria.add(Restrictions.isEmpty("commercialInvoices"));
}
if (requiredTypes.contains(RequiredType.CUSTOMS_DECLARATION)) {
criteria.add(Restrictions.isEmpty("customsDeclarations"));
}
if (requiredTypes.contains(RequiredType.BILL_OF_LADING_REFERENCE)) {
criteria.add(Restrictions.isNull("billOfLadingReference"));
criteria.add(Restrictions.eq("shippingMode", ShippingMode.SEA));
}
if (requiredTypes.contains(RequiredType.BILL_OF_LADING_DATE)) {
criteria.add(Restrictions.isNull("billOfLadingDate"));
criteria.add(Restrictions.eq("shippingMode", ShippingMode.SEA));
}
if (requiredTypes.contains(RequiredType.PACKING_LIST_INFO)) {
criteria.createAlias("packingList", "pl");
criteria.createAlias("pl.containers", "plc");
criteria.add(Restrictions.isEmpty("pl.containers"));
criteria.add(Restrictions.isNotEmpty("plc.orders"));
}
}
if (search.getSelectedClearingStatus().length > 0) {
String statusus = Arrays.stream(search.getSelectedClearingStatus())
.map(status -> "'" + status.name() + "'")
.collect(Collectors.joining(","));
criteria.add(Restrictions.sqlRestriction(("select cli.status from clearinginstruction cli where " +
"cli.shipment_id = shipment.id AND cli.status in (#status)")
.replaceAll("#status", statusus)));
}
if (setDistinct) {
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
}
}
private Set<OrganisationalUnit> getOrganisationalUnits(ShipmentSearch search) {
Set<OrganisationalUnit> organisationalUnits = Collections.EMPTY_SET;
if (CollectionUtils.isNotEmpty(search.getOrganisationalUnitList())) {
organisationalUnits = new HashSet<>(search.getOrganisationalUnitList());
} else if (search.isFilteredByUserOrg()) {
organisationalUnits = getUserOrganisationalUnits();
} else if (search.getOrganisationalUnit() != null) {
organisationalUnits = Collections.singleton(search.getOrganisationalUnit());
}
return organisationalUnits;
}
private void addDateRangeCriteria(DetachedCriteria criteria, DateRange masterAirwayBillIssueDate, String masterAirwayBillIssueDate1) {
if (masterAirwayBillIssueDate != null) {
CriteriaBuilder.addDateRangeCriteria(criteria, masterAirwayBillIssueDate1, masterAirwayBillIssueDate.getFrom(),
masterAirwayBillIssueDate.getTo(), true);
}
}
@Override
public Shipment findByNumber(String shipmentNumber) {
Criteria searchCriteria = getSessionCustom().createCriteria(Shipment.class);
searchCriteria.add(Restrictions.like("number", shipmentNumber));
return (Shipment) searchCriteria.uniqueResult();
}
@Override
public Shipment findByReference(String shipimentReference) {
Criteria searchCriteria = getSessionCustom().createCriteria(Shipment.class);
searchCriteria.add(Restrictions.like("reference", shipimentReference));
return (Shipment) searchCriteria.uniqueResult();
}
@Override
public SeaShipment findSeaShipment(Long id) {
return (SeaShipment) getCurrentSession().load(SeaShipment.class, id);
}
@Override
public AirShipment findAirShipment(Long id) {
return (AirShipment) getCurrentSession().load(AirShipment.class, id);
}
@Override
public LandShipment findLandShipment(Long id) {
return (LandShipment) getSession().load(LandShipment.class, id);
}
@Override
public List<Order> findOrdersByShipment(Shipment shipment) {
@SuppressWarnings("unchecked")
List<Order> orders = (List<Order>) getNamedQueryAndNamedParam("order.findAllByShipment", "id", shipment.getId());
return orders;
}
@Override
public Shipment findByIdWithEvents(long shipmentId) {
List<Shipment> shipments = (List<Shipment>) findByNamedQueryAndNamedParam("findByIdWithEventsLoaded", "id", shipmentId);
return ObjectUtil.first(shipments);
}
@Override
public List<CommercialInvoice> searchActiveCommercialInvoices(Shipment shipment, SearchMetaParams searchMetaParams) {
String queryString = "from CommercialInvoice where shipment = :shipment and state != 'DELETED' ORDER BY addedToShipmentDate";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("shipment", shipment);
initQueryParams(query, searchMetaParams);
return query.list();
}
@Override
public long countActiveCommercialInvoices(Shipment shipment) {
String queryString = "select count(*) from CommercialInvoice where shipment.id = :shipmentId and state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("shipmentId", shipment.getId());
return (Long) query.uniqueResult();
}
@Override
public long countActiveInvoicesInState(Long shipmentId, DocumentState documentState, Class<? extends CostsInvoice> className) {
String queryString =
"select count(*) from " + className.getSimpleName() + " where shipment.id = :shipmentId and state =:documentState "
+ "and state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("shipmentId", shipmentId);
query.setParameter("documentState", documentState);
return (Long) query.uniqueResult();
}
@Override
public long countActiveInvoicesInStates(Long shipmentId, List<DocumentState> documentStates, Class<? extends CostsInvoice> className) {
String queryString =
"select count(*) from " + className.getSimpleName() + " where shipment.id = :shipmentId and state in :documentStates "
+ "and state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("shipmentId", shipmentId);
query.setParameterList("documentStates", documentStates);
return (Long) query.uniqueResult();
}
@Override
public List<CommercialCreditNote> searchActiveCommercialCreditNotes(Shipment shipment, SearchMetaParams searchMetaParams) {
String queryString = "from CommercialCreditNote where shipment = :shipment and state != 'DELETED'";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("shipment", shipment);
initQueryParams(query, searchMetaParams);
return query.list();
}
@Override
public long countActiveCommercialCreditNotes(Shipment shipment) {
String queryString = "select count(*) from CommercialCreditNote where shipment = :shipment and state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("shipment", shipment);
return (Long) query.uniqueResult();
}
@Override
public List<ServiceProviderInvoice> searchActiveServiceProviderInvoices(Shipment shipment, SearchMetaParams searchMetaParams) {
String queryString = "from ServiceProviderInvoice where shipment = :shipment and state != 'DELETED' ORDER BY addedToShipmentDate";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("shipment", shipment);
initQueryParams(query, searchMetaParams);
return query.list();
}
@Override
public List<ServiceProviderCreditNote> searchActiveServiceProviderCreditNotes(Shipment shipment, SearchMetaParams searchMetaParams) {
String queryString = "from ServiceProviderCreditNote where shipment = :shipment and state != 'DELETED' ORDER BY addedToShipmentDate";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("shipment", shipment);
initQueryParams(query, searchMetaParams);
return query.list();
}
@Override
public long countActiveServiceProviderInvoices(Shipment shipment) {
String queryString = "select count(*) from ServiceProviderInvoice where shipment = :shipment and state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("shipment", shipment);
return (Long) query.uniqueResult();
}
@Override
public long countActiveServiceProviderCreditNotes(Shipment shipment) {
String queryString = "select count(*) from ServiceProviderCreditNote where shipment = :shipment and state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("shipment", shipment);
return (Long) query.uniqueResult();
}
@Override
public Shipment findByContainer(ShipmentContainer shipmentContainer) {
String hql = "select s from Shipment s join s.containers as containers where containers.id = :shipmentContainerId";
Query query = getSessionCustom().createQuery(hql);
query.setParameter("shipmentContainerId", shipmentContainer.getId());
return (Shipment) query.uniqueResult();
}
@Override
public List<String> findComments(Shipment shipment) {
String queryString = "select sc.reason from shipment_freetextcomments sc " + "where sc.shipment_id = :shipmentId";
@SuppressWarnings("unchecked")
List<String> results = getCurrentSession().createSQLQuery(queryString).setParameter("shipmentId", shipment.getId()).list();
return results;
}
@Override
public List<Consignment> searchActiveConsignments(Shipment shipment, SearchMetaParams searchMetaParams) {
String queryString = "from Consignment where shipment = :shipment and state != 'DELETED'";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("shipment", shipment);
initQueryParams(query, searchMetaParams);
return query.list();
}
@Override
public long countActiveConsignments(Shipment shipment) {
String queryString = "select count(*) from Consignment where shipment = :shipment and state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("shipment", shipment);
return (Long) query.uniqueResult();
}
@Override
public List<Order> searchActiveOrders(Shipment shipment, SearchMetaParams searchMetaParams) {
String queryString = "from Order where consignment.shipment = :shipment and state != 'DELETED'";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("shipment", shipment);
initQueryParams(query, searchMetaParams);
return query.list();
}
@Override
public long countActiveOrders(Shipment shipment) {
String queryString = "select count(*) from Order where consignment.shipment = :shipment and state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("shipment", shipment);
return (Long) query.uniqueResult();
}
@Override
public Shipment findByReferenceActive(String reference) {
return (Shipment) ObjectUtil.first(findByNamedQueryAndNamedParam("findByReferenceNotDeleted", "reference", reference));
}
@Override
public SeaShipment findByMasterBillOfLadingReferenceActive(String reference) {
return (SeaShipment) ObjectUtil.first(findByNamedQueryAndNamedParam("findByMWBReferenceNotDeleted", "reference", reference));
}
@Override
public SeaShipment findByBillOfLadingReferenceActive(String reference) {
List<SeaShipment> list = (List<SeaShipment>) findByNamedQueryAndNamedParam("findByWBReferenceNotDeleted", "reference", reference);
Shipment shipment = ObjectUtil.first(list);
return (SeaShipment) (HibernateUtils.getNonProxyObject(shipment));
}
@Override
public long count(Incoterm incoterm, OrganisationalUnit organisationalUnit, ShipmentState... shipmentStates) {
Query query = createQuery(incoterm, organisationalUnit, shipmentStates, true);
return ((Long) query.uniqueResult());
}
@Override
public List<Shipment> search(Incoterm incoterm, OrganisationalUnit organisationalUnit, ShipmentState... shipmentStates) {
Query query = createQuery(incoterm, organisationalUnit, shipmentStates, false);
return query.list();
}
private Query createQuery(Incoterm incoterm, OrganisationalUnit organisationalUnit, ShipmentState[] shipmentStates, boolean count) {
return createQuery(incoterm, organisationalUnit, shipmentStates, count, false);
}
private Query createQuery(Incoterm incoterm, OrganisationalUnit organisationalUnit, ShipmentState[] shipmentStates, boolean count,
boolean asColumns) {
ObjectUtil.validateNotNull(incoterm, "Incoterm");
ObjectUtil.validateNotEmpty(shipmentStates, "Shipment State");
String stateNames = null;
for (ShipmentState shipmentState : shipmentStates) {
if (stateNames != null) {
stateNames += ",'" + shipmentState.name() + "'";
} else {
stateNames = "'" + shipmentState.name() + "'";
}
}
String sql =
"select " + (!asColumns ? ((count) ? "count(s)" : "s") : ("s.*, o.*")) +
" from Shipment s join s.consignments as c" + " where s.state in (" + stateNames + ")"
+ " and s.shippingInfo.incoterm = :incoterm" + " and s.commercialInvoices.size = 0"
+ " and s.commercialCreditNotes.size = 0" + " and s.serviceProviderInvoices.size = 0";
if (organisationalUnit != null) {
sql += " and c.organisationalUnit = :orgUnit";
}
Query query = getSessionCustom().createQuery(sql);
query.setParameter("incoterm", incoterm);
if (organisationalUnit != null) {
query.setParameter("orgUnit", organisationalUnit);
}
return query;
}
@Override
public List<Shipment> findByIncotermOrganisationalUnitsAndStateNoDocuments(Incoterm incoterm, List<OrganisationalUnit> organisationalUnits,
List<ShipmentState> shipmentstates, String shipmentReference,
String shipmentNumber, PlaceOfLoading placeOfLoading,
PlaceOfDischarge placeOfDischarge, CountryGroup countryGroup) {
ObjectUtil.validateNotNull(incoterm, "Incoterm");
ObjectUtil.validateNotNull(organisationalUnits, "Organisational Unit");
ObjectUtil.validateNotNull(shipmentstates, "Shipment State");
String shipmentRefSql = " and upper(s.reference) like upper(:shipmentReference)";
String shipmentNumSql = " and s.number like (:shipmentNumber)";
String placeOfLoadingSql = " and s.shippingInfo.placeOfLoading = :placeOfLoading";
String placeOfDischargeSql = " and s.shippingInfo.placeOfDischarge = :placeOfDischarge";
String organisationalUnitsSql = " and c.organisationalUnit in (:organisationalUnits)";
String statesSql = " and s.state in (:shipmentStates) ";
StringBuilder sql = new StringBuilder("select distinct s from Shipment s join s.consignments as c left join c.orders o").
append(" where s.shippingInfo.incoterm = :incoterm");
// .append(" and s.commercialInvoices.size = 0")
// .append(" and s.commercialCreditNotes.size = 0")
// .append(" and s.serviceProviderInvoices.size = 0");
if (shipmentReference != null) {
sql.append(shipmentRefSql);
}
if (shipmentNumber != null) {
sql.append(shipmentNumSql);
}
if (placeOfLoading != null) {
sql.append(placeOfLoadingSql);
}
if (placeOfDischarge != null) {
sql.append(placeOfDischargeSql);
}
if (!organisationalUnits.isEmpty()) {
sql.append(organisationalUnitsSql);
}
if (!shipmentstates.isEmpty()) {
sql.append(statesSql);
}
if (countryGroup != null && countryGroup.getCountries() != null && !countryGroup.getCountries().isEmpty()) {
sql.append(" and o.supplier.supplier.physicalAddress.country in (:countries)");
}
Query query = getSessionCustom().createQuery(sql.toString());
query.setParameter("incoterm", incoterm);
if (shipmentReference != null) {
query.setParameter("shipmentReference", LIKE + shipmentReference + LIKE);
}
if (shipmentNumber != null) {
query.setParameter("shipmentNumber", LIKE + shipmentNumber + LIKE);
}
if (placeOfLoading != null) {
query.setParameter("placeOfLoading", placeOfLoading);
}
if (placeOfDischarge != null) {
query.setParameter("placeOfDischarge", placeOfDischarge);
}
if (!organisationalUnits.isEmpty()) {
query.setParameterList("organisationalUnits", organisationalUnits);
}
if (!shipmentstates.isEmpty()) {
query.setParameterList("shipmentStates", shipmentstates);
}
if (countryGroup != null && countryGroup.getCountries() != null && !countryGroup.getCountries().isEmpty()) {
query.setParameterList("countries", countryGroup.getCountries());
}
return query.list();
}
@Override
public List<SeaShipment> search(Incoterm incoterm, ServiceProvider freightForwarder, ServiceProvider shippingLine, ContainerType containerType) {
String sql =
"select s from SeaShipment s join s.containers as c" + " where s.shippingInfo.incoterm = :incoterm"
+ " and s.shippingInfo.freightForwarder = :freightForwarder" + " and s.carrier = :shippingLine"
+ " and c.containerType = :containerType";
Query query = getSessionCustom().createQuery(sql);
query.setParameter("incoterm", incoterm);
query.setParameter("freightForwarder", freightForwarder);
query.setParameter("shippingLine", shippingLine);
query.setParameter("containerType", containerType);
query.setParameter("containerType", containerType);
return query.list();
}
@Override
public List<SeaShipment> search(ServiceProvider shippingLine, PlaceOfLoading placeOfLoading, PlaceOfDischarge placeOfDischarge) {
String sql = "select s from SeaShipment s" + " where s.shippingInfo.placeOfLoading = :placeOfLoading"
+ " and s.shippingInfo.placeOfDischarge = :placeOfDischarge" + " and s.carrier = :shippingLine";
Query query = getSessionCustom().createQuery(sql);
query.setParameter("placeOfLoading", placeOfLoading);
query.setParameter("placeOfDischarge", placeOfDischarge);
query.setParameter("shippingLine", shippingLine);
return query.list();
}
@Override
public Shipment findByReferenceActive(long id) {
String queryString = "SELECT shipment FROM Shipment shipment WHERE shipment.id = :id AND shipment.state NOT IN ('DELETED')";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("id", id);
return (Shipment) query.uniqueResult();
}
public Criteria createShipmentStatusCriteria(ShipmentSearch search, boolean count) {
Criteria criteria = getSession().createCriteria(Shipment.class);
criteria.createAlias("shippingInfo", "si");
criteria.createAlias("events", "e");
if (!count) {
criteria.setProjection(Projections.projectionList().add(Projections.distinct(Projections.property("number")))
.add(Projections.groupProperty("reference")).add(Projections.groupProperty("si.shippingMode"))
.add(Projections.groupProperty("si.freightForwarder")).add(Projections.groupProperty("si.placeOfLoading"))
.add(Projections.groupProperty("created")).add(Projections.groupProperty("state")).add(Projections.groupProperty("events"))
.add(Projections.groupProperty("id")));
} else {
criteria.setProjection(Projections.projectionList().add(Projections.countDistinct("number")));
}
if (search.getNumber() != null) {
criteria.add(Restrictions.like("number", "%" + search.getNumber() + "%"));
}
if (search.getReference() != null) {
criteria.add(Restrictions.like("reference", "%" + search.getReference() + "%"));
}
if (search.getMode() != null) {
criteria.add(Restrictions.eq("si.shippingMode", search.getMode()));
}
if (search.getFreightForwarder() != null) {
criteria.add(Restrictions.eq("si.freightForwarder", search.getFreightForwarder()));
}
if (search.getCreatedDateFrom() != null && search.getCreatedDateTo() != null) {
/*
* Date date = new Date(search.getCreatedDateTo().getTime() + (this.ONE_DAY - 1)); logger.debug("Shipment created date to is now ::::" +
* date);
*/
criteria.add(Restrictions.between("created", search.getCreatedDateFrom(), search.getCreatedDateTo()));
} else if (search.getCreatedDateFrom() != null) {
criteria.add(Restrictions.ge("created", search.getCreatedDateFrom()));
} else if (search.getCreatedDateTo() != null) {
criteria.add(Restrictions.le("created", search.getCreatedDateTo()));
}
if (search.getState() != null) {
if (search.getState().equals(ShipmentState.COMPLETE)) {
criteria.add(Restrictions.eq("e.eventType", ShipmentEventType.COMPLETED));
criteria.add(Restrictions.between("e.createDateTime", new Date(System.currentTimeMillis() - this.ONE_DAY),
new Date(System.currentTimeMillis() + this.ONE_DAY)));
} else {
criteria.add(Restrictions.eq("state", search.getState()));
}
} else {
Criterion criterion1 = Restrictions.in("state", new Object[]{ShipmentState.DELETED, ShipmentState.FINALISED,
ShipmentState.PART_SHIPMENT_CREATED, ShipmentState.SIGNED_OFF, ShipmentState.STOCK_FULLY_RECEIVED,
ShipmentState.STOCK_PARTIALLY_RECEIVED, ShipmentState.VERIFIED});
Date todayStart = setHours(new Date(System.currentTimeMillis()), 0, 0, 0, 0);
Date todayEnd = setHours(new Date(System.currentTimeMillis()), 23, 59, 59, 999);
Criterion criterion2 = Restrictions.and(Restrictions.eq("e.eventType", ShipmentEventType.COMPLETED),
Restrictions.between("e.createDateTime", todayStart, todayEnd));
criteria.add(Restrictions.or(criterion1, criterion2));
}
Set<OrganisationalUnit> organisationalUnits = Collections.EMPTY_SET;
if (search.isFilteredByUserOrg()) {
organisationalUnits = getUserOrganisationalUnits();
} else if (search.getOrganisationalUnit() != null) {
organisationalUnits = Collections.singleton(search.getOrganisationalUnit());
}
if (!organisationalUnits.isEmpty()) {
criteria.createAlias("consignments", "c");
criteria.createAlias("c.orders", "o");
criteria.add(Restrictions.in("o.organisationalUnit", organisationalUnits));
}
if (!count) {
criteria.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] objects, String[] strings) {
Shipment shipment;
// At the writing of this code SEA and AIR are the only
// ShippingMode fields available
if (objects[2].equals(ShippingMode.SEA)) {
shipment = new SeaShipment();
} else {
shipment = new AirShipment();
}
shipment.setNumber(objects[0] + "");
shipment.setReference(objects[1] + "");
ShipmentShippingInfo shipmentShippingInfo = new ShipmentShippingInfo();
shipmentShippingInfo.setShippingMode((ShippingMode) objects[2]);
shipmentShippingInfo.setFreightForwarder((ServiceProvider) objects[3]);
shipmentShippingInfo.setPlaceOfLoading((PlaceOfLoading) objects[4]);
shipmentShippingInfo.setCreated((Date) objects[5]);
shipment.setShippingInfo(shipmentShippingInfo);
shipment.setState((ShipmentState) objects[6]);
if (objects[7] == null) {
shipment.setEvents(new ArrayList<ShipmentEvent>());
} else {
shipment.setEvents((List<ShipmentEvent>) objects[7]);
}
shipment.setId(Long.parseLong("" + objects[8]));
return shipment;
}
@Override
public List transformList(List list) {
return list;
}
});
}
return criteria;
}
@Override
public List<Shipment> shipmentStatusSearch(ShipmentSearch search) {
Criteria criteria = this.createShipmentStatusCriteria(search, false);
SearchMetaParams searchMetaParams = search.getSearchMetaParams();
if (searchMetaParams != null) {
criteria.setFirstResult(searchMetaParams.getRowIndex());
criteria.setMaxResults(searchMetaParams.getRowCount());
criteria.addOrder(org.hibernate.criterion.Order.desc("created"));
} else {
criteria.addOrder(org.hibernate.criterion.Order.desc("created"));
}
return criteria.list();
}
/**
* TODO: count method to be revisited.
*
* @param search
* @return
*/
@Override
public long shipmentStatusCount(ShipmentSearch search) {
Criteria criteria = this.createShipmentStatusCriteria(search, true);
long count = (long) criteria.uniqueResult();
log.debug("COUNT: " + count);
return count;
}
private Date setHours(Date date, int hh, int mm, int ss, int ms) {
GregorianCalendar gc = new GregorianCalendar();
gc.setTime(date);
gc.set(GregorianCalendar.HOUR_OF_DAY, hh);
gc.set(GregorianCalendar.MINUTE, mm);
gc.set(GregorianCalendar.SECOND, ss);
gc.set(GregorianCalendar.MILLISECOND, ms);
return gc.getTime();
}
@Override
public Map<Shipment, String> findShippingReferences(List<Shipment> shipments) {
Map<Shipment, String> result = new HashMap<>();
// Empty in statements are bad
if (!shipments.isEmpty()) {
String queryString =
"SELECT s.id, osi.shippingReference " + "FROM Order o LEFT JOIN o.shippingInformation osi " + "LEFT JOIN o.consignment c "
+ "LEFT JOIN c.shipment s LEFT JOIN s.shippingInfo ssi " + "WHERE s in (:shipments)";
Query query = getSessionCustom().createQuery(queryString);
query.setParameterList("shipments", shipments);
List list = query.list();
Map<Long, List<String>> temp = new HashMap<>();
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<>();
temp.put(shipmentId, s);
}
temp.get(shipmentId).add(shippingReference);
}
for (Shipment shipment : shipments) {
result.put(shipment, StringUtils.join(temp.get(shipment.getId()), ","));
}
}
return result;
}
// serviceProviderInvoices
@Override
public List<String> getServiceProviderInvoiceIdAndReference(Shipment shipment, final String delimeter) {
Criteria criteria = getSession().createCriteria(Shipment.class);
criteria.createAlias("serviceProviderInvoices", "spi");
criteria.setProjection(Projections.projectionList().add(Projections.property("spi.id"), "0").add(Projections.property("spi.reference"), "1"));
criteria.add(Restrictions.eq("id", shipment.getId()));
criteria.add(Restrictions.ne("spi.state", DocumentState.DELETED));
criteria.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
return tuple[0] + delimeter + tuple[1];
}
@Override
public List transformList(List collection) {
return collection;
}
});
return criteria.list();
}
@Override
public Long findIdByShipmentReference(String reference) {
String queryString = "select id from Shipment shipment WHERE shipment.reference = :reference and state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
query.setParameter("reference", reference);
return (Long) query.uniqueResult();
}
@Override
public List<Shipment> findSignedShipmentsByDateRange(Date startDate, Date endDate) {
Criteria criteria = getSession().createCriteria(Shipment.class);
criteria.add(Restrictions.between("updated", startDate, endDate));
return criteria.list();
}
@Override
public List<ShippingRegisterSearchResult> getDailyReportDTO(ShipmentSearch shipmentSearch, String dateFormat) {
String orgUnitFilter = filterOrgUnit(shipmentSearch);
return ShipmentDailyReportHelper.getDailyReportDTO(getCurrentSession(), shipmentSearch.getCreatedDateFrom(), dateFormat, orgUnitFilter);
}
private String filterOrgUnit(ShipmentSearch search) {
Set<OrganisationalUnit> organisationalUnits = Collections.EMPTY_SET;
if (search.isFilteredByUserOrg()) {
organisationalUnits = getUserOrganisationalUnits();
} else if (search.getOrganisationalUnit() != null) {
organisationalUnits = Collections.singleton(search.getOrganisationalUnit());
}
if (!organisationalUnits.isEmpty()) {
List<String> collect = organisationalUnits.stream()
.map(organisationalUnit -> organisationalUnit.getId().toString()).collect(Collectors.toList());
String join = StringUtils.join(collect, ",");
return String.format(" and organisationalunit.id in(%s)", join);
}
return "";
}
@Override
public List<ShipmentFcrNumber> getFcrNumbersForShipment(long shipmentId) {
String queryString = "select po.id as orderid, po.fcrnumber, c.reference as consignmentreference, " +
"si.shippingreference ordersShippingReference,o.orderReference from purchaseorder po " +
"left join orders o on po.id = o.id " +
"left join consignment c on o.consignment_id = c.id " +
"left join shippinginformation si on o.shippinginformation_id = si.id " +
"left join shipment s on s.id = c.shipment_id where s.id = :shipmentid order by ordersshippingreference, consignmentreference";
Query query = getSessionCustom().createSQLQuery(queryString)
.addScalar("orderId", StandardBasicTypes.LONG)
.addScalar("fcrNumber", StandardBasicTypes.STRING)
.addScalar("consignmentReference", StandardBasicTypes.STRING)
.addScalar("ordersShippingReference", StandardBasicTypes.STRING)
.addScalar("orderReference", StandardBasicTypes.STRING);
query.setParameter("shipmentid", shipmentId)
.setResultTransformer(Transformers.aliasToBean(ShipmentFcrNumber.class));
return query.list();
}
@Override
public void saveFcrNumberForShipment(ShipmentFcrNumber fcr) {
String queryString = "update purchaseorder set fcrNumber = :fcr " +
" where id = :orderid";
Query query = getSessionCustom().createSQLQuery(queryString);
query.setParameter("orderid", fcr.getOrderId())
.setParameter("fcr", fcr.getFcrNumber())
.setResultTransformer(Transformers.aliasToBean(ShipmentFcrNumber.class));
query.executeUpdate();
}
@Override
public List goodsInTransitReport(ShipmentSearch search) {
return goodsInTransitHql(search);
}
private List goodsInTransitHql(ShipmentSearch search) {
StringBuilder sb = new StringBuilder(
"select po, ship, ci, aco "
+ "from PurchaseOrder as po "
+ "left join po.consignment.shipment as ship "
+ "left join ship.commercialInvoices as ci "
+ "left join ci.actualConsignments as ac "
+ "left join ac.actualOrders as aco "
+ "where po.consignment.shipment is not null "
+ "and po.consignment.shipment.state not in ('DELETED') "
+ "and po.state not in ('DELETED') "
+ "and (ci is null or (aco.originalId = po.id and"
+ " aco.totalInvoiceValue > 0.0 and ci.state in ('SETTLED','SIGNED_OFF'))) "
);
boolean useReceiptFilter = search.isOrderActualCostingReceipt();
Date date = null;
if (!useReceiptFilter) {
// EXISTING behaviour (unchanged)
if (search.getShipmentCreatedFrom() != null) {
date = search.getShipmentCreatedFrom();
sb.append(" and ship.created <= :fromDate");
} else if (search.getMasterBillOfLadingDateFrom() != null) {
date = search.getMasterBillOfLadingDateFrom();
sb.append(" and (ship.masterBillOfLadingDate <= :fromDate or ship.masterAirwayBillIssueDate <= :fromDate)");
} else if (search.getBillOfLadingDateFrom() != null) {
date = search.getBillOfLadingDateFrom();
sb.append(" and (ship.billOfLadingDate <= :fromDate or ship.houseAirwayBillIssueDate <= :fromDate)");
}
sb.append(" and (po.orderDates.goodsReceivedDate > :goodsReceivedDate or po.orderDates.goodsReceivedDate is null) ");
} else {
sb.append(" and ( ship.state = 'FINALISED' ");
sb.append(" or not exists (");
sb.append(" select 1 from OrdersEvent oe ");
sb.append(" where oe.orderReference = po.orderReference ");
sb.append(" and oe.eventType = :receiptEventType");
sb.append(" )");
sb.append(" ) ");
}
Collection<OrganisationalUnit> organisationalUnits = Collections.EMPTY_SET;
if (search.getOrganisationalUnit() != null) {
organisationalUnits = Collections.singleton(search.getOrganisationalUnit());
} else if (search.isFilteredByUserOrg()) {
organisationalUnits = getUserOrganisationalUnits();
}
if (search.getOrganisationalUnitList() != null && !search.getOrganisationalUnitList().isEmpty()) {
organisationalUnits = search.getOrganisationalUnitList();
}
if (!organisationalUnits.isEmpty()) {
sb.append(" and po.organisationalUnit in (:orgs)");
}
Query query = getSessionCustom().createQuery(sb.toString());
if (!useReceiptFilter) {
query.setParameter("fromDate", DateUtils.getStartOfDay(date));
query.setParameter("goodsReceivedDate", DateUtils.getEndOfDay(search.getBillOfLadingDateFrom()));
} else {
query.setParameter("receiptEventType", OrderEventType.ORDER_ACTUAL_COSTING_RECEIPT);
}
if (!organisationalUnits.isEmpty()) {
query.setParameterList("orgs", organisationalUnits);
}
return query.list();
}
@Override
public List<Shipment> finance60DaysHql(DocumentSearch search) {
StringBuilder sb = new StringBuilder(
"select distinct ship from Shipment ship");
sb.append(" where ship.created >=:fromDate ");
sb.append(" and ship.state not in ('DELETED')");
Query query = getSessionCustom().createQuery(sb.toString());
query.setParameter("fromDate", search.getCreatedDateFrom());
return query.list();
}
@Override
public List<Shipment> shipmentStatusDocumentSearch(ShipmentSearch search) {
Criteria criteria = getShipmentDocumentStatusCriteria(search, false);
SearchMetaParams searchMetaParams = search.getSearchMetaParams();
if (searchMetaParams != null) {
criteria.setFirstResult(searchMetaParams.getRowIndex());
criteria.setMaxResults(searchMetaParams.getRowCount());
criteria.addOrder(org.hibernate.criterion.Order.desc("created"));
} else {
criteria.addOrder(org.hibernate.criterion.Order.desc("created"));
}
return criteria.list();
}
@Override
public long shipmentDocumentStatusCount(ShipmentSearch search) {
Criteria criteria = getShipmentDocumentStatusCriteria(search, true);
// criteria.setProjection(Projections.rowCount());
return (Long) criteria.uniqueResult();
}
private Criteria getShipmentDocumentStatusCriteria(ShipmentSearch search, boolean count) {
Criteria criteria = getSession().createCriteria(Shipment.class);
criteria.createAlias("shippingInfo", "si");
if (!count) {
criteria.setProjection(Projections.projectionList().
add(Projections.distinct(Projections.property("number")))
.add(Projections.groupProperty("reference"))
.add(Projections.groupProperty("si.shippingMode"))
.add(Projections.groupProperty("si.freightForwarder"))
.add(Projections.groupProperty("si.placeOfLoading"))
.add(Projections.groupProperty("created"))
.add(Projections.groupProperty("state"))
.add(Projections.groupProperty("documentGroupStatus"))
// .add(Projections.groupProperty("documentType"))
.add(Projections.groupProperty("billOfLadingDate"))
.add(Projections.groupProperty("houseAirwayBillIssueDate"))
.add(Projections.groupProperty("estimatedArrivalDateAtPlaceOfDischarge"))
.add(Projections.groupProperty("id")));
} else {
criteria.setProjection(Projections.projectionList().add(Projections.countDistinct("number")));
}
criteria.add(Restrictions.ne("state", ShipmentState.DELETED));
if (search.getNumber() != null) {
criteria.add(Restrictions.like("number", "%" + search.getNumber() + "%"));
}
if (search.getReference() != null) {
criteria.add(Restrictions.like("reference", "%" + search.getReference() + "%"));
}
if (search.getState() != null) {
criteria.add(Restrictions.eq("state", search.getState()));
}
if (search.getDocumentGroupState() != null) {
criteria.add(Restrictions.eq("documentGroupStatus", search.getDocumentGroupState()));
}
if (search.getDocumentType() != null) {
criteria.add(Restrictions.eq("documentType", search.getDocumentType()));
}
if (search.getFreightForwarder() != null) {
criteria.add(Restrictions.eq("si.freightForwarder", search.getFreightForwarder()));
}
if (search.getCreatedDateFrom() != null && search.getCreatedDateTo() != null) {
criteria.add(Restrictions.between("created", search.getCreatedDateFrom(), search.getCreatedDateTo()));
} else if (search.getCreatedDateFrom() != null) {
criteria.add(Restrictions.ge("created", search.getCreatedDateFrom()));
} else if (search.getCreatedDateTo() != null) {
criteria.add(Restrictions.le("created", search.getCreatedDateTo()));
}
if (search.getEstimateArrivalDateAtPODFrom() != null && search.getEstimateArrivalDateAtPODTo() != null) {
criteria.add(Restrictions.between("estimatedArrivalDateAtPlaceOfDischarge", search.getEstimateArrivalDateAtPODFrom(),
search.getEstimateArrivalDateAtPODTo()));
} else if (search.getEstimateArrivalDateAtPODFrom() != null) {
criteria.add(Restrictions.ge("estimatedArrivalDateAtPlaceOfDischarge", search.getEstimateArrivalDateAtPODFrom()));
} else if (search.getEstimateArrivalDateAtPODTo() != null) {
criteria.add(Restrictions.le("estimatedArrivalDateAtPlaceOfDischarge", search.getEstimateArrivalDateAtPODTo()));
}
if (search.getBillOfLadingDateFrom() != null && search.getBillOfLadingDateTo() != null) {
criteria.add(Restrictions.disjunction(Restrictions.between("billOfLadingDate", search.getBillOfLadingDateFrom(),
search.getBillOfLadingDateTo()), Restrictions.between("houseAirwayBillIssueDate", search.getBillOfLadingDateFrom(),
search.getBillOfLadingDateTo())));
} else if (search.getBillOfLadingDateFrom() != null) {
criteria.add(Restrictions.disjunction(Restrictions.ge("billOfLadingDate", search.getBillOfLadingDateFrom()),
Restrictions.ge("houseAirwayBillIssueDate", search.getBillOfLadingDateFrom())));
} else if (search.getBillOfLadingDateTo() != null) {
criteria.add(Restrictions.disjunction(Restrictions.le("billOfLadingDate", search.getBillOfLadingDateTo()),
Restrictions.le("houseAirwayBillIssueDate", search.getBillOfLadingDateTo())));
}
Set<OrganisationalUnit> organisationalUnits = Collections.EMPTY_SET;
if (search.isFilteredByUserOrg()) {
organisationalUnits = getUserOrganisationalUnits();
} else if (search.getOrganisationalUnit() != null) {
organisationalUnits = Collections.singleton(search.getOrganisationalUnit());
}
if (!organisationalUnits.isEmpty()) {
criteria.createAlias("consignments", "c");
criteria.createAlias("c.orders", "o");
criteria.add(Restrictions.in("o.organisationalUnit", organisationalUnits));
}
if (!count) {
criteria.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] objects, String[] strings) {
Shipment shipment;
// At the writing of this code SEA and AIR are the only
// ShippingMode fields available
if (objects[2].equals(ShippingMode.SEA)) {
shipment = new SeaShipment();
} else {
shipment = new AirShipment();
}
shipment.setNumber(objects[0] + "");
shipment.setReference(objects[1] + "");
ShipmentShippingInfo shipmentShippingInfo = new ShipmentShippingInfo();
shipmentShippingInfo.setShippingMode((ShippingMode) objects[2]);
shipmentShippingInfo.setFreightForwarder((ServiceProvider) objects[3]);
shipmentShippingInfo.setPlaceOfLoading((PlaceOfLoading) objects[4]);
shipmentShippingInfo.setCreated((Date) objects[5]);
shipment.setShippingInfo(shipmentShippingInfo);
shipment.setState((ShipmentState) objects[6]);
shipment.setDocumentGroupStatus((DocumentGroupState) objects[7]);
if (shipment.getShippingMode() == ShippingMode.SEA) {
((SeaShipment) shipment).setBillOfLadingDate((Date) objects[8]);
} else {
((AirShipment) shipment).setHouseAirwayBillIssueDate((Date) objects[9]);
}
shipment.setEstimatedArrivalDateAtPlaceOfDischarge((Date) objects[10]);
shipment.setId(Long.parseLong("" + objects[11]));
return shipment;
}
@Override
public List transformList(List list) {
return list;
}
});
}
return criteria;
}
@Override
@Transactional(readOnly = true)
public Date getLatestSettlementDate(Long shipmentId) {
Query query = getSessionCustom().createQuery("select COALESCE(max(ipso.settlementDate),max(ops.settlementDate)) from Shipment s " +
"left join s.commercialInvoices ci left " +
" join ci.plannedSettlements ips left join ips.plannedSettlementOrders ipso join s.consignments c join c.orders o " +
"join o.plannedSettlements ops where s.id = :shipmentId ")
.setParameter("shipmentId", shipmentId);
return (Date) query.uniqueResult();
}
@Override
public long shipmentDocumentMissingCount(ShipmentSearch search) {
String queryString = getMissingDocumentsQueryString(true, search);
Query query = getSessionCustom().createSQLQuery(queryString);
setParameters(query, search);
//log.debug(query.getQueryString());
List list = query.list();
if (list.isEmpty()) {
return 0;
}
return ((BigInteger) list.get(0)).longValue();
}
@Override
public List<ShipmentDocumentSearchResult> shipmentDocumentMissingSearch(ShipmentSearch search) {
String queryString = getMissingDocumentsQueryString(false, search);
Properties params = new Properties();
params.put("enumClass", ShipmentState.class.getCanonicalName());
params.put("type", "12");
final TypeConfiguration tc = new TypeConfiguration();
final TypeResolver tr = new TypeResolver(tc, new org.hibernate.type.TypeFactory(tc));
Type myEnumType = new TypeLocatorImpl(tr).custom(EnumType.class, params);
NativeQuery sqlQuery = getSessionCustom().createSQLQuery(queryString);
setParameters(sqlQuery, search);
Query query = sqlQuery
.addScalar("shipmentReference", StandardBasicTypes.STRING)
.addScalar("shipmentNumber", StandardBasicTypes.STRING)
.addScalar("shipmentId", StandardBasicTypes.LONG)
.addScalar("shippingMode", StandardBasicTypes.STRING)
.addScalar("shipmentState", myEnumType)
.addScalar("supplier", StandardBasicTypes.STRING)
.addScalar("clearingAgent", StandardBasicTypes.STRING)
.addScalar("documentType", StandardBasicTypes.STRING)
.addScalar("freightForwarder", StandardBasicTypes.STRING)
.addScalar("originalETAAtPOD", StandardBasicTypes.DATE)
.addScalar("etaAtFD", StandardBasicTypes.DATE)
.addScalar("orderReferences", StandardBasicTypes.STRING);
initQueryParams(query, search.getSearchMetaParams());
query.setResultTransformer(Transformers.aliasToBean(ShipmentDocumentSearchResult.class));
//log.debug(query.getQueryString());
return (List<ShipmentDocumentSearchResult>) query.list();
}
@Override
public void clearContainerJoinTable(Shipment existingShipment) {
StringBuilder builder = new StringBuilder("delete from shipment_container where shipment_id=:ship_id");
Query query = getSessionCustom().createSQLQuery(builder.toString());
query.setParameter("ship_id", existingShipment.getId());
query.executeUpdate();
}
@Override
public List<ShipmentStatesDTO> getPeriods(ShipmentStateCountDTORequest shipmentStateCountDTORequest) {
StringBuilder queryString = new StringBuilder
("SELECT NEW com.tradecloud.dto.shipment.ShipmentStatesDTO(e.state, count(e.state))" +
" FROM Shipment e where ");
switch (shipmentStateCountDTORequest.getDateFieldReference()) {
case STATE:
if (!shipmentStateCountDTORequest.getDateCalculationType().equals(DateCalculationType.PREVIOUS)) {
throw new IllegalArgumentException("Required : Date calculation type should be previous");
}
queryString.append(" e.stateDate BETWEEN :startDate AND :endDate ");
break;
default:
throw new IllegalArgumentException("DateFieldReference Not Found");
}
boolean excludeStates = shipmentStateCountDTORequest.getShipmentStates() !=
null && !shipmentStateCountDTORequest.getExcludedShipmentStates().isEmpty();
if (excludeStates) {
queryString.append(" and e.state not in :excludedStates");
}
queryString.append(" GROUP BY e.state");
List<ShipmentState> shipmentStates = new ArrayList<>();
Query query = getSession().createQuery(queryString.toString());
query.setDate("startDate", shipmentStateCountDTORequest.getDateRange().getFrom());
query.setDate("endDate", shipmentStateCountDTORequest.getDateRange().getTo());
if (excludeStates) {
shipmentStates = shipmentStateCountDTORequest.getExcludedShipmentStates();
query.setParameterList("excludedStates", shipmentStates);
}
return query.list();
}
@Override
public List<Shipment> findShipmentState(ShipmentStateSearchDTO shipmentStateSearchDTO) {
StringBuilder queryString = new StringBuilder("SELECT e from Shipment e where ");
shipmentStateFilter(shipmentStateSearchDTO, queryString);
queryString.append(" ORDER BY e.created,e.reference asc");
Query query = getSession().createQuery(queryString.toString());
shipmentStateParams(shipmentStateSearchDTO, query);
query.setFirstResult((shipmentStateSearchDTO.getPageNumber()));
query.setMaxResults(shipmentStateSearchDTO.getPageSize());
return query.list();
}
private void shipmentStateParams(ShipmentStateSearchDTO shipmentStateSearchDTO, Query query) {
query.setDate("startDate", shipmentStateSearchDTO.getDateFrom());
query.setDate("endDate", shipmentStateSearchDTO.getDateTo());
if (shipmentStateSearchDTO.getState() != null) {
query.setString("state", shipmentStateSearchDTO.getState().toString());
}
}
private void shipmentStateFilter(ShipmentStateSearchDTO shipmentStateSearchDTO, StringBuilder queryString) {
switch (shipmentStateSearchDTO.getShipmentDateFieldReference()) {
case STATE:
queryString.append("e.stateDate BETWEEN :startDate AND :endDate");
break;
default:
throw new IllegalArgumentException("DateFieldReference Not Found");
}
if (shipmentStateSearchDTO.getState() != null) {
queryString.append(" AND e.state= :state");
}
}
@Override
public long countSearch(ShipmentStateSearchDTO shipmentStateSearchDTO) {
StringBuilder queryString = new StringBuilder("SELECT count(*) from Shipment e where ");
shipmentStateFilter(shipmentStateSearchDTO, queryString);
Query query = getSession().createQuery(queryString.toString());
shipmentStateParams(shipmentStateSearchDTO, query);
return (Long) query.uniqueResult();
}
@Override
public List<CommercialInvoice> findCIByItemType(Shipment shipment, ItemType itemType) {
StringBuilder sb = new StringBuilder("select distinct ci from CommercialInvoice as ci ");
sb.append(" join ci.actualConsignments as ac ");
sb.append(" join ac.actualOrders as aco ");
sb.append(" join aco.actualLineItems as it ");
sb.append(" where ci.shipment = :shipment and ci.state != 'DELETED' and it.itemType=:itemType and it.invoiceQuantity>0");
Query query = getCurrentSession().createQuery(sb.toString());
query.setParameter("shipment", shipment);
query.setParameter("itemType", itemType);
return query.list();
}
private String getMissingDocumentsQueryString(boolean count, ShipmentSearch search) {
StringBuilder query = new StringBuilder("select ");
if (count) {
query.append("count(*) ");
} else {
query.append("DISTINCT s.reference as shipmentReference, ")
.append("s.number as shipmentNumber, ")
.append("s.state as shipmentState, ")
.append("s.id as shipmentId, ")
.append("ssi.shippingmode as shippingMode, ")
.append("supplier.name as supplier, ")
.append("dgt.name as documentGroupType, ")
.append("ca.name as clearingAgent, ")
.append("ff.name as freightForwarder, ")
.append("(select string_agg(orders.orderreference, ' ') from orders ")
.append(" left join consignment on orders.consignment_id = consignment.id ")
.append(" left join shipment on consignment.shipment_id = shipment.id ")
.append(" where s.id = shipment.id) as orderReferences, ")
.append("dt.name as documentType, ")
.append("s.originalestimatedarrivaldateatplaceofdischarge as originalETAAtPOD, ")
.append("s.estimatedarrivaldateatplaceofdischarge as etaAtFD ");
}
query.append("from ")
.append("documenttype dt, ")
.append("documentgrouptype dgt, ")
.append("shipment s ")
.append("left join documentgroup dg on s.reference = dg.key ")
.append("left join seashipment on seashipment.id = s.id ")
.append("left join shipmentshippinginfo ssi on ssi.id = s.shippinginfo_id ")
.append("left join serviceprovider ca on ssi.clearingagent_id = ca.id ")
.append("left join serviceprovider ff on ssi.freightforwarder_id = ff.id ")
.append("left join consignment on consignment.shipment_id = s.id ")
.append("left join orders on orders.consignment_id = consignment.id ")
.append("left join purchaseorder on orders.id = purchaseorder.id ")
.append("left join organisationalunitsupplier on purchaseorder.supplier_id = organisationalunitsupplier.id ")
.append("left join supplier on organisationalunitsupplier.supplier_id = supplier.id ")
.append("where dgt.id = dg.documentgrouptype_id ")
.append("and dt.mandatory = true ")
.append(" AND dt.name NOT IN (")
.append(" SELECT d1t.name")
.append(" FROM document d1")
.append(" JOIN documenttype d1t ON d1.documenttype_id = d1t.id")
.append(" JOIN documentgroup d ON d.id = d1.documentgroup_id")
.append(" JOIN documentgrouptype dgt ON d.documentgrouptype_id = dgt.id")
.append(" JOIN shipment ss ON ss.reference = d.key")
.append(" WHERE ss.id = s.id")
.append(" AND (")
.append(" (ssi.shippingmode = 'SEA' AND dgt.name = 'SHIPMENT SEA') OR")
.append(" (ssi.shippingmode = 'AIR' AND dgt.name = 'SHIPMENT AIR')")
.append(" )")
.append(")")
.append(" AND dt.documentgrouptype_id = dgt.id ");
;
// Filters
if (search.getDocumentType() != null) {
query.append("and dt.name = :documentType ");
}
if (search.getState() != null) {
query.append("and s.state = :state ");
} else {
query.append("and s.state <> 'DELETED' ");
}
if (search.getReference() == null && search.getNumber() == null &&
search.getBillOfLadingDateTo() == null && search.getBillOfLadingDateFrom() == null &&
search.getEstimateArrivalDateAtPODFrom() == null && search.getEstimateArrivalDateAtPODTo() == null &&
search.getCreatedDateFrom() != null && search.getCreatedDateTo() != null) {
query.append("and s.created between :createdDateFrom and :createdDateTo ");
}
if (search.getNumber() != null) {
query.append("and s.number ilike :number ");
}
if (search.getReference() != null) {
query.append("and s.reference ilike :reference ");
}
if (search.getClearingAgent() != null) {
query.append("and ssi.clearingagent_id = :clearingAgentId ");
}
if (search.getFreightForwarder() != null) {
query.append("and ssi.freightforwarder_id = :freightForwarderId ");
}
if (search.getBillOfLadingDateFrom() != null && search.getBillOfLadingDateTo() != null) {
query.append("and seashipment.billofladingdate between :billOfLadingDateFrom and :billOfLadingDateTo ");
}
if (search.getEstimateArrivalDateAtPODFrom() != null && search.getEstimateArrivalDateAtPODTo() != null) {
query.append("and s.estimatedarrivaldateatplaceofdischarge between :etaPODFrom and :etaPODTo ");
}
if (search.getSupplier() != null) {
query.append("and organisationalunitsupplier.id=:supplierId");
}
Set<OrganisationalUnit> organisationalUnits = getUnits(search);
if (!organisationalUnits.isEmpty()) {
query.append("and orders.organisationalunit_id in (:organisationalUnitIds) ");
}
return query.toString();
}
private Set<OrganisationalUnit> getUnits(ShipmentSearch search) {
Set<OrganisationalUnit> organisationalUnits = Collections.emptySet();
if (search.isFilteredByUserOrg()) {
organisationalUnits = getUserOrganisationalUnits();
} else if (search.getOrganisationalUnit() != null) {
organisationalUnits = Collections.singleton(search.getOrganisationalUnit());
} else if (!CollectionUtils.isEmpty(search.getOrganisationalUnitList())) {
organisationalUnits = new HashSet<>(search.getOrganisationalUnitList());
}
return organisationalUnits;
}
private void setParameters(Query query, ShipmentSearch search) {
if (search.getDocumentType() != null) {
query.setParameter("documentType", search.getDocumentType().getName());
}
if (search.getState() != null) {
query.setParameter("state", search.getState().name());
}
if (search.getCreatedDateFrom() != null && search.getCreatedDateTo() != null) {
query.setParameter("createdDateFrom", search.getCreatedDateFrom());
query.setParameter("createdDateTo", search.getCreatedDateTo());
}
if (search.getNumber() != null) {
query.setParameter("number", "%" + search.getNumber() + "%");
}
if (search.getReference() != null) {
query.setParameter("reference", "%" + search.getReference() + "%");
}
if (search.getClearingAgent() != null) {
query.setParameter("clearingAgentId", search.getClearingAgent().getId());
}
if (search.getFreightForwarder() != null) {
query.setParameter("freightForwarderId", search.getFreightForwarder().getId());
}
if (search.getBillOfLadingDateFrom() != null && search.getBillOfLadingDateTo() != null) {
query.setParameter("billOfLadingDateFrom", search.getBillOfLadingDateFrom());
query.setParameter("billOfLadingDateTo", search.getBillOfLadingDateTo());
}
if (search.getEstimateArrivalDateAtPODFrom() != null && search.getEstimateArrivalDateAtPODTo() != null) {
query.setParameter("etaPODFrom", search.getEstimateArrivalDateAtPODFrom());
query.setParameter("etaPODTo", search.getEstimateArrivalDateAtPODTo());
}
if (search.getSupplier() != null) {
query.setParameter("supplierId", search.getSupplier().getId());
}
Set<OrganisationalUnit> organisationalUnits = getUnits(search);
if (!organisationalUnits.isEmpty()) {
query.setParameter("organisationalUnitIds", organisationalUnits.stream()
.map(OrganisationalUnit::getId)
.collect(Collectors.toList()));
}
}
//Limit date range to 3 months
private Date transformDate(Date dateFrom, Date dateTo) {
LocalDate fromDate = dateFrom.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
LocalDate toDate = dateTo.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
Period period = Period.between(fromDate, toDate);
int months = period.getMonths();
int days = period.getDays();
Calendar calendar = Calendar.getInstance();
Calendar calendar2 = Calendar.getInstance();
calendar.setTime(dateFrom);
calendar2.setTime(dateTo);
if (months > 0 && days > 0) {
calendar.add(Calendar.MONTH, 3);
dateTo.setTime(calendar.getTime().getTime());
}
return dateTo;
}
@Override
public Set<Long> findShipmentsToTariff() {
StringBuilder stringBuilder = new StringBuilder("select distinct(s.id) from Shipment s ");
stringBuilder.append("left join s.serviceProviderInvoices spi join s.consignments c");
stringBuilder.append(" where s.state not in (:shipStates)");
stringBuilder.append(" and (spi is null or spi.state not in (:spiStates))");
Query query = getSessionCustom().createQuery(stringBuilder.toString());
query.setParameter("shipStates", Shipment.NON_EDITABLE_STATES);
query.setParameter("spiStates", Document.NON_EDITABLE_STATES);
return new HashSet<>(query.list());
}
@Override
public List<ShipmentEvent> getEvents(Long id, ShipmentEventType eventType) {
StringBuilder query = new StringBuilder("select * from shipmentevent se " +
"left join shipment_shipmentevent sse on se.id = sse.events_id where sse.shipment_id = :shipmentId");
if (eventType != null)
query.append(" and se.eventtype = :eventType");
NativeQuery<ShipmentEvent> nativeQuery = getSession().createNativeQuery(query.toString(), ShipmentEvent.class);
nativeQuery.setParameter("shipmentId", id);
if (eventType != null)
nativeQuery.setParameter("eventType", eventType.name());
return nativeQuery.list();
}
@Override
public Currency findFirstCurrencyOnFirstOrder(Shipment shipment) {
String query = "select * from currency left join orders o on o.currency_code = currency.code " +
"left join consignment c on o.consignment_id = c.id where c.shipment_id = :shipmentId limit 1";
org.hibernate.query.Query<Currency> currencyQuery = getSession().createNativeQuery(query, Currency.class);
currencyQuery.setParameter("shipmentId", shipment.getId());
return currencyQuery.uniqueResult();
}
@Override
public Currency findFirstCurrencyOnFirstOrder(Long consignmentId) {
String query = "select * from currency left join orders o on o.currency_code = currency.code " +
"left join consignment c on o.consignment_id = c.id where c.id = :consignmentId limit 1";
org.hibernate.query.Query<Currency> currencyQuery = getSession().createNativeQuery(query, Currency.class);
currencyQuery.setParameter("consignmentId", consignmentId);
return currencyQuery.uniqueResult();
}
@Override
public Map<String, String> findShippingReferenceOnOrder(Long shipmentId) {
String queryString = "select o.orderreference, si.shippingreference from shippinginformation si " +
"left join orders o on o.shippinginformation_id = si.id " +
"left join consignment c on o.consignment_id = c.id where c.shipment_id = :shipmentId";
List resultList = getCurrentSession()
.createNativeQuery(queryString)
.addScalar("orderreference", StandardBasicTypes.STRING)
.addScalar("shippingreference", StandardBasicTypes.STRING)
.setParameter("shipmentId", shipmentId)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
Map<String, String> results = (Map<String, String>) resultList.get(0);
return results;
}
@Override
public List<ExportParty> findExportPartiesForOrders(Long shipmentId) {
String query = "select * from exportParty left join salesorder so on so.exportparty_id = exportParty.id " +
"left join orders o on o.id = so.id " +
"left join consignment c on o.consignment_id = c.id where c.shipment_id = :shipmentId";
org.hibernate.query.Query<ExportParty> currencyQuery = getSession().createNativeQuery(query, ExportParty.class);
currencyQuery.setParameter("shipmentId", shipmentId);
return currencyQuery.list();
}
@Override
public List<Map<String, String>> findFromLastestTriffCode(String tariffNoCntrl) {
String query = """
select l.weight,l.volume from shipment s join actualshipment sact on (s.actualshipment_id=sact.id)\s
join actualconsignment c on (c.actualshipment_id=sact.id)\s
join actualorder o on (o.actualconsignment_id=c.id) join actuallineitem l on (l.actualorder_id=o.id)\s
where s.state in (:states)\s
and l.tariffheading ilike :tariffCode order by s.created desc,l.invoicequantity desc limit 1""";
@SuppressWarnings("unchecked")
List<Map<String, String>> results = getCurrentSession()
.createNativeQuery(query)
.addScalar("weight", StandardBasicTypes.STRING)
.addScalar("volume", StandardBasicTypes.STRING)
.setParameterList("states", Shipment.NON_EDITABLE_STATES.stream()
.filter(st -> !st.equals(ShipmentState.DELETED))
.map(st -> st.name()).collect(Collectors.toList()))
.setParameter("tariffCode", tariffNoCntrl + "%")
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
return results;
}
@Override
public boolean isInvoiceOrDraftDeclaration(long shipmentId) {
String query = new String("""
select ci.id as invoiceId, cd.id as declarationId, cd.status, di.id as diId from
costsinvoice\s
left join commercialinvoice ci on ci.id = costsinvoice.id
left join declarationinvoice di on ci.id = di.commercialinvoice_id
left join customsdeclaration cd on cd.id = di.customsdeclaration_id
where costsinvoice.state not in ('INITIALISED','DELETED')
and costsinvoice.shipment_id = :shipmentId
group by invoiceId,declarationId, cd.status, diId;
""");
List<Object[]> list = getCurrentSession().createNativeQuery(query).setParameter("shipmentId", shipmentId)
.unwrap(org.hibernate.query.Query.class).getResultList();
//no commercial invoices
if (list.isEmpty())
return false;
int declarationCount = 0;
int notDraftCount = 0;
for (Object[] obj : list) {
if (obj[1] != null)
declarationCount++;
if (obj[1] != null && obj[2] != null && !obj[2].equals(Status.DRAFT.name()))
notDraftCount++;
}
if (notDraftCount > 0)
return false;
return true;
}
@Override
public List<MultiModalShipment> getMultiModalShipments(Shipment shipment) {
javax.persistence.criteria.CriteriaBuilder criteriaBuilder = getCurrentSession().getCriteriaBuilder();
CriteriaQuery<MultiModalShipment> builderQuery = criteriaBuilder.createQuery(MultiModalShipment.class);
Root<MultiModalShipment> from = builderQuery.from(MultiModalShipment.class);
org.hibernate.query.Query<MultiModalShipment> query = getSessionCustom()
.createQuery(builderQuery
.select(from)
.where(criteriaBuilder.equal(from.get("shipment"), shipment))
.orderBy(criteriaBuilder.desc(from.get("created"))));
return query.getResultList();
}
@Override
public List<ShipmentContainerResult> findFShipContainerTransporter(Set<Long> shipIds) {
String querySql = """
select c.id,
c.reference as containerReference,
c.shipment_id as shipmentId,
c.transporter_id as transporterId,
s.state as shipmentState
from container c
join shipment s on s.id = c.shipment_id
where s.id in (:shipIds)
""";
NativeQuery query = getSessionCustom().createNativeQuery(querySql);
query.addScalar("id", StandardBasicTypes.LONG)
.addScalar("containerReference", StandardBasicTypes.STRING)
.addScalar("shipmentId", StandardBasicTypes.LONG)
.addScalar("transporterId", StandardBasicTypes.LONG)
.addScalar("shipmentState", StandardBasicTypes.STRING)
.setResultTransformer(Transformers.aliasToBean(ShipmentContainerResult.class));
query.setParameterList("shipIds", shipIds);
return query.list();
}
@Override
public void saveTransporterContainer(long shipmentId, String fieldName, Object fieldValue) {
Long transporterId = null;
if (fieldValue != null) {
transporterId = (fieldValue instanceof Long)
? (Long) fieldValue
: Long.valueOf(fieldValue.toString());
}
Set<Long> shipIds = new HashSet<>();
shipIds.add(shipmentId);
// IMPORTANT: call the local method, not shipmentRepository.*
List<ShipmentContainerResult> containers = findFShipContainerTransporter(shipIds);
Set<String> references = containers.stream()
.map(ShipmentContainerResult::getContainerReference)
.filter(r -> r != null && !r.trim().isEmpty())
.collect(Collectors.toSet());
if (references.isEmpty()) {
saveTransporterContainerSingleShipment(shipmentId, transporterId);
return;
}
for (String reference : references) {
List<Long> linkedShipmentIds =
shipmentContainerRepository.findShipmentIdsByContainerReference(reference);
if (linkedShipmentIds == null || linkedShipmentIds.isEmpty()) {
saveTransporterContainerSingleShipment(shipmentId, transporterId);
continue;
}
saveTransporterForContainerReference(reference, transporterId, linkedShipmentIds);
}
}
@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();
// ---- ShipmentContainer update ----
try {
final String hql;
if (transporterId == null) {
hql = "UPDATE ShipmentContainer sc "
+ "SET sc.transporter = null, sc.updated = :updated "
+ "WHERE sc.reference = :reference "
+ "AND sc.shipment.id IN (:shipmentIds)";
} else {
hql = "UPDATE ShipmentContainer sc "
+ "SET sc.transporter.id = :transporterId, sc.updated = :updated "
+ "WHERE sc.reference = :reference "
+ "AND sc.shipment.id IN (:shipmentIds)";
}
Query<?> q = getSessionCustom().createQuery(hql);
q.setParameter("updated", updated);
q.setParameter("reference", reference);
q.setParameterList("shipmentIds", shipmentIdsInScope);
if (transporterId != null) {
q.setParameter("transporterId", transporterId);
}
q.executeUpdate();
} catch (RuntimeException e) {
log.error(
"Failed updating ShipmentContainer for container reference=" + reference
+ ", transporterId=" + transporterId
+ ", shipmentIds=" + shipmentIdsInScope,
e
);
throw e;
}
// ---- TransporterContainer sync (no joins) ----
try {
List<Long> containerIds = getSessionCustom().createQuery(
"select sc.id "
+ "from ShipmentContainer sc "
+ "where sc.reference = :reference "
+ "and sc.shipment.id in (:shipmentIds)",
Long.class
).setParameter("reference", reference)
.setParameterList("shipmentIds", shipmentIdsInScope)
.list();
if (containerIds == null || containerIds.isEmpty()) {
return; // nothing to sync
}
final String hqlTc;
if (transporterId == null) {
hqlTc = "UPDATE TransporterContainer tc "
+ "SET tc.transporter = null, tc.updated = :updated "
+ "WHERE tc.shipmentContainer.id IN (:containerIds)";
} else {
hqlTc = "UPDATE TransporterContainer tc "
+ "SET tc.transporter.id = :transporterId, tc.updated = :updated "
+ "WHERE tc.shipmentContainer.id IN (:containerIds)";
}
Query<?> qTc = getSessionCustom().createQuery(hqlTc);
qTc.setParameter("updated", updated);
qTc.setParameterList("containerIds", containerIds);
if (transporterId != null) {
qTc.setParameter("transporterId", transporterId);
}
qTc.executeUpdate();
} catch (RuntimeException e) {
log.error(
"Failed updating TransporterContainer (by containerIds) for container reference=" + reference
+ ", transporterId=" + transporterId
+ ", shipmentIds=" + shipmentIdsInScope,
e
);
throw e;
} finally {
// Bulk updates bypass the persistence context and can leave managed entities stale.
// Clearing prevents optimistic-lock failures later in the same request.
getSessionCustom().flush();
getSessionCustom().clear();
}
}
@Override
public void fillMissingTransporterForContainerReference(String reference,
Long transporterId,
List<Long> shipmentIdsInScope) {
if (reference == null || reference.trim().isEmpty()) return;
if (transporterId == null) return;
if (shipmentIdsInScope == null || shipmentIdsInScope.isEmpty()) return;
Date updated = new Date();
// 1) Update ShipmentContainer only where transporter is NULL (no overwrite)
String hql = "UPDATE ShipmentContainer sc "
+ "SET sc.transporter.id = :transporterId, sc.updated = :updated "
+ "WHERE sc.reference = :reference "
+ "AND sc.shipment.id IN (:shipmentIds) "
+ "AND sc.transporter IS NULL";
Query<?> q = getSessionCustom().createQuery(hql);
q.setParameter("transporterId", transporterId);
q.setParameter("updated", updated);
q.setParameter("reference", reference.trim());
q.setParameterList("shipmentIds", shipmentIdsInScope);
q.executeUpdate();
// 2) Sync existing TransporterContainer rows only where transporter is NULL
List<Long> containerIds = getSessionCustom().createQuery(
"select sc.id from ShipmentContainer sc "
+ "where sc.reference = :reference "
+ "and sc.shipment.id in (:shipmentIds)",
Long.class
).setParameter("reference", reference.trim())
.setParameterList("shipmentIds", shipmentIdsInScope)
.list();
if (containerIds != null && !containerIds.isEmpty()) {
String hqlTc = "UPDATE TransporterContainer tc "
+ "SET tc.transporter.id = :transporterId, tc.updated = :updated "
+ "WHERE tc.shipmentContainer.id IN (:containerIds) "
+ "AND tc.transporter IS NULL";
Query<?> qTc = getSessionCustom().createQuery(hqlTc);
qTc.setParameter("transporterId", transporterId);
qTc.setParameter("updated", updated);
qTc.setParameterList("containerIds", containerIds);
qTc.executeUpdate();
}
// Prevent optimistic-lock issues later in the request
getSessionCustom().flush();
getSessionCustom().clear();
}
private void saveTransporterContainerSingleShipment(long shipmentId, Long transporterId) {
Date updated = new Date();
// ---- ShipmentContainer ----
try {
final String hql;
if (transporterId == null) {
hql = "UPDATE ShipmentContainer sc "
+ "SET sc.transporter = null, sc.updated = :updated "
+ "WHERE sc.shipment.id = :id";
} else {
hql = "UPDATE ShipmentContainer sc "
+ "SET sc.transporter.id = :transporterId, sc.updated = :updated "
+ "WHERE sc.shipment.id = :id";
}
Query<?> q = getSessionCustom().createQuery(hql);
q.setParameter("updated", updated);
q.setParameter("id", shipmentId);
if (transporterId != null) {
q.setParameter("transporterId", transporterId);
}
q.executeUpdate();
} catch (RuntimeException e) {
log.error(
"Failed single-shipment ShipmentContainer update. shipmentId=" + shipmentId
+ ", transporterId=" + transporterId,
e
);
throw e;
}
try {
// ---- TransporterContainer (NO joins in UPDATE) ----
List<Long> containerIds = getSessionCustom().createQuery(
"select sc.id from ShipmentContainer sc where sc.shipment.id = :shipmentId",
Long.class
).setParameter("shipmentId", shipmentId)
.list();
if (containerIds != null && !containerIds.isEmpty()) {
final String hqlTc;
if (transporterId == null) {
hqlTc = "UPDATE TransporterContainer tc "
+ "SET tc.transporter = null, tc.updated = :updated "
+ "WHERE tc.shipmentContainer.id IN (:containerIds)";
} else {
hqlTc = "UPDATE TransporterContainer tc "
+ "SET tc.transporter.id = :transporterId, tc.updated = :updated "
+ "WHERE tc.shipmentContainer.id IN (:containerIds)";
}
Query<?> qTc = getSessionCustom().createQuery(hqlTc);
qTc.setParameter("updated", updated);
qTc.setParameterList("containerIds", containerIds);
if (transporterId != null) qTc.setParameter("transporterId", transporterId);
qTc.executeUpdate();
}
} catch (RuntimeException e) {
log.error(
"Failed single-shipment TransporterContainer update. shipmentId=" + shipmentId
+ ", transporterId=" + transporterId,
e
);
throw e;
}
}
@Override
public boolean showOrderActualCostingReceiptCheckbox() {
String sql =
"SELECT EXISTS ( " +
" SELECT 1 " +
" FROM users u " +
" JOIN integrationuser_costingevents iu ON iu.integrationuser_id = u.id " +
" JOIN costingevents ce ON ce.id = iu.costingevent_id " +
" JOIN costingevents_costingeventsmetadata cecm ON cecm.costingevent_id = ce.id " +
" JOIN costingeventsmetadata cem ON cem.id = cecm.metadata_id " +
" WHERE u.integratedsystem_id IS NOT NULL " +
" AND cem.event = :event " +
" AND ce.inuse = TRUE " +
")";
Object raw = getSessionCustom()
.createNativeQuery(sql)
.setParameter("event", com.tradecloud.domain.model.events.CostingEvent.CostingEventType
.ORDER_ACTUAL_COSTING_RECEIPT.name())
.getSingleResult();
if (raw instanceof Boolean) {
return (Boolean) raw;
}
if (raw instanceof Number) {
return ((Number) raw).intValue() != 0;
}
return Boolean.parseBoolean(String.valueOf(raw));
}
}