PurchaseOrderRepositoryImpl.java
package com.tradecloud.repository.impl;
import com.tradecloud.common.base.HibernateUtils;
import com.tradecloud.domain.base.utils.DateRange;
import com.tradecloud.domain.document.PaymentState;
import com.tradecloud.domain.dto.base.SearchBase;
import com.tradecloud.domain.event.OrderEventType;
import com.tradecloud.domain.infrastructure.persistence.CriteriaBuilder;
import com.tradecloud.domain.item.Product;
import com.tradecloud.domain.model.ordermanagement.BusinessState;
import com.tradecloud.domain.model.ordermanagement.Consignment;
import com.tradecloud.domain.model.ordermanagement.OrderState;
import com.tradecloud.domain.model.ordermanagement.PurchaseOrder;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.domain.model.shipment.ShipmentState;
import com.tradecloud.dto.api.order.DateCalculationType;
import com.tradecloud.dto.api.order.StateCountDTO;
import com.tradecloud.dto.invoice.ClcAlcOrderVarianceReportSearch;
import com.tradecloud.dto.order.*;
import com.tradecloud.repository.PurchaseOrderRepository;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.base.impl.CriteriaOperation;
import com.tradecloud.repository.base.impl.CriteriaValue;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.EntityGraph;
import javax.persistence.TemporalType;
import java.io.StringWriter;
import java.math.BigInteger;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.temporal.TemporalAdjusters;
import java.util.*;
import java.util.stream.Collectors;
import static com.tradecloud.domain.model.ordermanagement.OrderState.STOCK_PARTIALLY_RECEIVED;
/**
* Purchase Order specific repository.
*/
@Repository(value = "purchaseOrderRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class PurchaseOrderRepositoryImpl extends OrderRepositoryImpl<PurchaseOrder, PurchaseOrderSearch> implements PurchaseOrderRepository {
private static final long serialVersionUID = 1L;
private static final String SUPPLIER = "supplier";
private static final String BUYER = "buyer";
private static final String ELC_ORDER = "elc";
private static final String PROFORMA_REFERENCE = "proFormaReference";
private static final List<BusinessState> BUSINESS_STATE_LIST = List.of(
BusinessState.ON_ORDER,
BusinessState.NOT_BOOKED,
BusinessState.SUPPLIER_BOOKING_REQUEST,
BusinessState.BOOKING_CONFIRMED,
BusinessState.FREIGHT_RECEIVED,
BusinessState.SHIPPED,
BusinessState.ACTUAL_COSTING_COMPLETED,
BusinessState.ARRIVAL_AT_PORT,
BusinessState.DELIVERED,
BusinessState.STOCK_RECEIVED_OR_PARTIALLY
);
private final long ONE_DAY = 1000 * 60 * 60 * 24;
private String sql;
@Override
protected Collection<CriteriaValue> mapFieldsToValues(PurchaseOrderSearch search) {
String tableName = ((SearchBase) search).getTableName();
String alias = tableName.toLowerCase() + ".";
Collection<CriteriaValue> fields = super.mapFieldsToValues(search);
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, alias + SUPPLIER, search.getSupplier()));
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, alias + BUYER, search.getBuyer()));
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, alias + ELC_ORDER, search.isElc()));
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, alias + PROFORMA_REFERENCE, search.getProFormaReference()));
fields.add(new CriteriaValue(CriteriaOperation.LIKE, alias + SHIPPING_REFERENCE, search.getShippingReference()));
return fields;
}
@Override
public void addSearchAwaitingSignoffRestrictions(DetachedCriteria criteria, PurchaseOrderSearch search) {
super.addSearchAwaitingSignoffRestrictions(criteria, search);
purchaseOrderFields(criteria, search);
}
private void purchaseOrderFields(DetachedCriteria criteria, PurchaseOrderSearch search) {
CriteriaBuilder.addEqRestriction(criteria, "supplier", search.getSupplier());
CriteriaBuilder.addEqRestriction(criteria, "buyer", search.getBuyer());
CriteriaBuilder.addEqRestriction(criteria, ELC_ORDER, search.isElc());
}
@Override
protected Collection<CriteriaValue> letterOfCreditCriteriaValues(PurchaseOrderSearch search) {
Collection<CriteriaValue> fields = super.letterOfCreditCriteriaValues(search);
fields.add(CriteriaValue.eq(BUYER, search.getBuyer()));
fields.add(CriteriaValue.eq(SUPPLIER, search.getSupplier()));
fields.add(CriteriaValue.eq(ELC_ORDER, search.isElc()));
return fields;
}
@Override
public List<OrderConfirmationSearchResult> confirmationSearchTransformed(PurchaseOrderSearch orderSearch) {
return super.confirmationSearchTransformed(orderSearch);
}
@Override
protected void setOrderPlannedSettlementSpecificfields(PurchaseOrderSearch search, DetachedCriteria criteria) {
if (search.getSupplier() != null) {
criteria.add(Restrictions.eq("supplier", search.getSupplier()));
}
}
@Override
protected String getEntityName() {
return PurchaseOrder.class.getSimpleName();
}
@Override
public List<PurchaseOrder> search(PurchaseOrderSearch search) {
return purchaseOrderList(search, false);
}
@Override
public long count(PurchaseOrderSearch search) {
return purchaseOrderList(search, true).size();
}
protected List<PurchaseOrder> purchaseOrderList(PurchaseOrderSearch search, boolean count) {
Session session = getSession();
List<PurchaseOrder> resultList;
Criteria criteria = session.createCriteria(PurchaseOrder.class, "porder");
criteria.createAlias("porder.shippingInformation", "shippinginformation");
//criteria.createAlias("lineItems", "lineitems");
//criteria.setFetchMode("lineItems", FetchMode.SELECT);
criteria.add(Restrictions.eq("porder.elc", search.isElc()));
if (search.getOrderNumber() != null) {
criteria.add(Restrictions.like("porder.number", "%" + search.getOrderNumber() + "%"));
}
if (search.getOrderReference() != null) {
if (search.isExactMatch()) {
criteria.add(Restrictions.eq("porder.orderReference", search.getOrderReference()));
} else {
criteria.add(Restrictions.ilike("porder.orderReference", "%" + search.getOrderReference() + "%"));
}
}
if (search.getCurrency() != null) {
criteria.add(Restrictions.eq("porder.currency", search.getCurrency()));
}
if (search.getShippingReference() != null) {
if (search.isExactMatch()) {
criteria.add(Restrictions.eq("shippinginformation.shippingReference", search.getShippingReference()));
} else {
criteria.add(Restrictions.ilike("shippinginformation.shippingReference", "%" + search.getShippingReference() + "%"));
}
}
if (search.getFreightForwarder() != null) {
criteria.add(Restrictions.eq("shippinginformation.freightForwarder", search.getFreightForwarder()));
}
if (search.getClearingAgent() != null) {
criteria.add(Restrictions.eq("shippinginformation.clearingAgent", search.getClearingAgent()));
}
if (search.getShippingMode() != null) {
criteria.add(Restrictions.eq("shippinginformation.shippingMode", search.getShippingMode()));
}
if (search.getMultiModalShippingMode() != null) {
criteria.add(Restrictions.eq("shippinginformation.multiModalShippingMode", search.getMultiModalShippingMode()));
}
if (search.getIncoterm() != null) {
criteria.add(Restrictions.eq("shippinginformation.incoterm", search.getIncoterm()));
}
DateRange requiredOnSite = search.getRequiredOnSiteDateRange();
if (requiredOnSite != null && requiredOnSite.getFrom() != null) {
criteria.add(Restrictions.ge("porder.orderDates.requiredOnSiteDate", requiredOnSite.getFrom()));
}
if (CollectionUtils.isNotEmpty(search.getActivityTypes())) {
criteria.createAlias("porder.lastActivity", "activity");
criteria.add(Restrictions.in("activity.eventType", search.getActivityTypes()));
}
if (search.isICP()) {
criteria.add(Restrictions.isNotNull("porder.supplierContact"));
}
if (requiredOnSite != null && requiredOnSite.getTo() != null) {
criteria.add(Restrictions.le("porder.orderDates.requiredOnSiteDate", requiredOnSite.getTo()));
}
DateRange latestShipDate = search.getLatestShipmentDateRange();
if (latestShipDate != null && latestShipDate.getFrom() != null) {
criteria.add(Restrictions.ge("porder.orderDates.latestShipmentDate", latestShipDate.getFrom()));
}
if (latestShipDate != null && latestShipDate.getTo() != null) {
criteria.add(Restrictions.le("porder.orderDates.latestShipmentDate", latestShipDate.getTo()));
}
DateRange availabilityDate = search.getAvailabilityDateRange();
if (availabilityDate != null && availabilityDate.getFrom() != null) {
criteria.add(Restrictions.ge("porder.orderDates.availabilityDate", availabilityDate.getFrom()));
}
if (availabilityDate != null && availabilityDate.getTo() != null) {
criteria.add(Restrictions.le("porder.orderDates.availabilityDate", availabilityDate.getTo()));
}
if (search.getFromDate() != null) {
criteria.add(Restrictions.gt("porder.created", search.getFromDate()));
}
if (search.getToDate() != null) {
criteria.add(Restrictions.le("porder.created", new Date(search.getToDate().getTime() + ONE_DAY - 1000)));
}
if (search.getOrderDateFrom() != null) {
criteria.add(Restrictions.gt("porder.orderdate", search.getOrderDateFrom()));
}
if (search.getOrderDateTo() != null) {
criteria.add(Restrictions.le("porder.orderdate", search.getOrderDateTo()));
}
if (search.getLatestShipmentDateRange().getFrom() != null) {
criteria.add(Restrictions.ge("porder.orderDates.latestShipmentDate", search.getLatestShipmentDateRange().getFrom()));
}
if (search.getLatestShipmentDateRange().getTo() != null) {
criteria.add(Restrictions.le("porder.orderDates.latestShipmentDate", search.getLatestShipmentDateRange().getTo()));
}
if (search.getGrvDate().getFrom() != null) {
criteria.add(Restrictions.ge("porder.orderDates.goodsReceivedDate", search.getGrvDate().getFrom()));
}
if (search.getGrvDate().getTo() != null) {
criteria.add(Restrictions.le("porder.orderDates.goodsReceivedDate", search.getGrvDate().getTo()));
}
if (search.getConsignmentState() != null) {
criteria.createAlias("porder.consignment", "consignment");
criteria.add(Restrictions.eq("consignment.state", search.getConsignmentState()));
}
if (CollectionUtils.isNotEmpty(search.getShipmentIds()) || search.getVesselOrFlight() != null ||
search.getBolOrAirwayBill() != null || search.getContainerReference() != null) {
criteria.createAlias("porder.consignment", "consignment");
criteria.createAlias("consignment.shipment", "shipment");
if (CollectionUtils.isNotEmpty(search.getShipmentIds()))
criteria.add(Restrictions.in("shipment.id", search.getShipmentIds()));
if (search.getVesselOrFlight() != null) {
criteria.add(Restrictions.disjunction()
.add(Restrictions.ilike("shipment.shippingVessel", "%" + search.getVesselOrFlight() + "%"))
.add(Restrictions.ilike("shipment.flightNumber", "%" + search.getVesselOrFlight() + "%")));
}
if (search.getBolOrAirwayBill() != null) {
criteria.add(Restrictions.ilike("shipment.flightNumber", "%" + search.getBolOrAirwayBill() + "%"));
}
if (search.getContainerReference() != null) {
criteria.createAlias("shipment.packingList", "packingList");
criteria.createAlias("packingList.containers", "packingListContainer");
criteria.createAlias("packingListContainer.orders", "packingOrder");
criteria.add(Restrictions.ilike("packingListContainer.reference", "%" + search.getContainerReference() + "%"));
}
}
if (search.isFilteredByUserOrg()) {
Set<OrganisationalUnit> organisationalUnits = getUserOrganisationalUnits();
criteria.add(Restrictions.in("porder.organisationalUnit", organisationalUnits));
} else {
if (search.getOrganisationalUnit() != null) {
if (search.isLiteSearch()) {
Junction conditionGroup = Restrictions.disjunction();
for (OrganisationalUnit organisationalUnit : search.getOrganisationalUnit().getChildren()) {
conditionGroup.add(Restrictions.eq("porder.organisationalUnit", organisationalUnit));
}
conditionGroup.add(Restrictions.eq("porder.organisationalUnit", search.getOrganisationalUnit()));
criteria.add(conditionGroup);
if (search.getOrderStateList() != null && !search.getOrderStateList().isEmpty()) {
criteria.add(Restrictions.in("porder.state", search.getOrderStateList()));
}
} else {
criteria.add(Restrictions.eq("porder.organisationalUnit", search.getOrganisationalUnit()));
}
} else if (!CollectionUtils.isEmpty(search.getOrganisationalUnits())) {
criteria.add(Restrictions.in("porder.organisationalUnit", search.getOrganisationalUnits()));
}
}
if (!CollectionUtils.isEmpty(search.getStates())) {
criteria.add(Restrictions.in("porder.state", search.getStates()));
} else if (search.getState() != null) {
criteria.add(Restrictions.eq("porder.state", search.getState()));
} else {
criteria.add(Restrictions.ne("porder.state", OrderState.DELETED));
}
if (!CollectionUtils.isEmpty(search.getBusinessStates())) {
criteria.add(Restrictions.in("porder.businessState", search.getBusinessStates()));
} else if (search.getBusinessState() != null) {
criteria.add(Restrictions.eq("porder.businessState", search.getBusinessState()));
} else {
// criteria.add(Restrictions.in("porder.businessState", BUSINESS_STATE_LIST));
}
if (!CollectionUtils.isEmpty(search.getSuppliers())) {
criteria.add(Restrictions.in("porder.supplier", search.getSuppliers()));
} else if (search.getSupplier() != null) {
criteria.add(Restrictions.eq("porder.supplier", search.getSupplier()));
}
if (!CollectionUtils.isEmpty(search.getBuyers())) {
criteria.add(Restrictions.in("porder.buyer", search.getBuyers()));
} else if (search.getBuyer() != null) {
criteria.add(Restrictions.eq("porder.buyer", search.getBuyer()));
}
if (search.getProFormaReference() != null) {
if (search.isExactMatch()) {
criteria.add(Restrictions.eq("porder.proFormaReference", search.getProFormaReference()));
} else {
criteria.add(Restrictions.ilike("porder.proFormaReference", "%" + search.getProFormaReference() + "%"));
}
}
if (search.getEarliestShipmentDate() != null) {
criteria.add(Restrictions.lt("porder.orderDates.earliestShipmentDate", search.getEarliestShipmentDate()));
}
if (search.getLatestShipmentDate() != null) {
criteria.add(Restrictions.gt("porder.orderDates.latestShipmentDate", search.getLatestShipmentDate()));
}
if (search.getConfirmed() != null && !search.getConfirmed().equals(PurchaseOrderSearch.OrderConfirmedType.ANY)) {
criteria.add(Restrictions.eq("porder.confirmed", search.getConfirmed().equals(PurchaseOrderSearch.OrderConfirmedType.CONFIRMED)));
}
if (search.getDescription() != null) {
criteria.add(Restrictions.ilike("porder.description", "%" + search.getDescription() + "%"));
}
if (search.getOrderEventTypeList() != null && !search.getOrderEventTypeList().isEmpty()) {
criteria.createAlias("events", "events");
criteria.setFetchMode("events", FetchMode.SELECT);
Junction conditionGroup = Restrictions.disjunction();
for (OrderEventType ordersEventType : search.getOrderEventTypeList()) {
conditionGroup.add(Restrictions.eq("events.eventType", ordersEventType));
}
criteria.add(conditionGroup);
}
addStyleSearchCriteria(search, criteria);
addContainerCriteria(search, criteria);
addSignOffDateSearchCriteria(search, criteria);
addShippingInformationCriteria(search, criteria);
ProjectionList projList = Projections.projectionList();
projList.add(Projections.property("porder.id"));
projList.add(Projections.property("porder.created"));
projList.add(Projections.property("porder.orderReference"));
projList.add(Projections.property("porder.state"));
projList.add(Projections.property("porder.orderDates.estimatedArrivalDate"));
projList.add(Projections.property("porder.businessState"));
criteria.setProjection(Projections.distinct(projList));
Order order;
SearchMetaParams searchMetaParams = search.getSearchMetaParams();
if (searchMetaParams != null && searchMetaParams.getOrderBy() != null) {
order = searchMetaParams.isAsc() ? Order.asc(searchMetaParams.getOrderBy()) : Order.desc(searchMetaParams.getOrderBy());
criteria.addOrder(order);
if (!searchMetaParams.getOrderBy().equals("created") || !searchMetaParams.getOrderBy().equals("porder.created")) {
//will resolve paging issue, when 2 order have the same column value.
order = Order.desc("porder.created");
criteria.addOrder(order);
}
} else {
order = Order.desc("porder.created");
criteria.addOrder(order);
}
if (!count) {
if (searchMetaParams != null) {
if (searchMetaParams.getRowCount() != -1) {
criteria.setMaxResults(searchMetaParams.getRowCount());
}
if (searchMetaParams.getRowIndex() != -1) {
criteria.setFirstResult(searchMetaParams.getRowIndex());
}
}
}
resultList = (List<PurchaseOrder>) criteria.list();
if (count) {
return resultList;
}
return purchaseOrderList(resultList);
}
private void addContainerCriteria(PurchaseOrderSearch search, Criteria criteria) {
if (search.getContainerTypeCode() != null) {
criteria.createAlias("consignment.containers", "container");
criteria.setFetchMode("container", FetchMode.SELECT);
criteria.add(Restrictions.eq("container.containerType.code", search.getContainerTypeCode()));
}
}
private void addShippingInformationCriteria(PurchaseOrderSearch search, Criteria criteria) {
// if (search.getPlaceOfLoading() != null || search.getPlaceOfDischarge() != null) {
// criteria.createAlias("porder.shippingInformation", "shippingInformation");
// }
if (search.getPlaceOfLoading() != null) {
criteria.add(Restrictions.eq("shippingInformation.placeOfLoading", search.getPlaceOfLoading()));
}
if (search.getPlaceOfDischarge() != null) {
criteria.add(Restrictions.eq("shippingInformation.placeOfDischarge", search.getPlaceOfDischarge()));
}
/*if (search.getShippingMode() != null) {
criteria.add(Restrictions.eq("porder.shippingInformation.shippingMode", search.getShippingMode()));
}*/
}
private void addSignOffDateSearchCriteria(PurchaseOrderSearch search, Criteria criteria) {
DateRange signedOffDateRange = search.getSignedOffDateRange();
if (signedOffDateRange != null && signedOffDateRange.getFrom() != null) {
List<OrderState> signedOffOrderStates = Arrays.asList(OrderState.SIGNED_OFF, OrderState.FREIGHT_RECEIVED, OrderState.BOOKED_IN);
criteria.add(Restrictions.in("porder.state", signedOffOrderStates));
// TODO. Use the below, once you have converted to DetachedCriteria
//CriteriaBuilder.addEventDateRangeSearchCriteria(criteria, signedOffDateRange, OrderEventType.SIGNED_OFF);
criteria.createAlias("events", "events");
criteria.setFetchMode("events", FetchMode.SELECT);
criteria.add(Restrictions.like("events.eventType", OrderEventType.SIGNED_OFF));
if (signedOffDateRange.getFrom() != null) {
criteria.add(Restrictions.ge("events.createDateTime", signedOffDateRange.getFrom()));
}
if (signedOffDateRange.getTo() != null) {
criteria.add(Restrictions.le("events.createDateTime", new Date(signedOffDateRange.getTo().getTime() + ONE_DAY - 1000)));
}
}
}
public List<PurchaseOrder> purchaseOrderList(List idList) {
Session session = getSession();
List<PurchaseOrder> purchaseOrderList = new ArrayList();
Criteria criteria = null;
List<PurchaseOrder> resultList = null;
if (idList != null) {
Iterator it = idList.iterator();
while (it.hasNext()) {
Object[] ob = (Object[]) it.next();
criteria = session.createCriteria(PurchaseOrder.class);
criteria.add(Restrictions.eq("id", ob[0]));
resultList = criteria.list();
purchaseOrderList.add(resultList.get(0));
}
}
return purchaseOrderList;
}
@Override
public List<PurchaseOrder> searchActiveOrders(SearchMetaParams searchMetaParams) {
String queryString = "from Order where state != 'DELETED' order by addedToConsignmentDate";
Query query = getCurrentSession().createQuery(queryString);
initQueryParams(query, searchMetaParams);
return query.list();
}
@Override
public List<PurchaseOrder> searchConsolidationOrders(PurchaseOrderSearch purchaseOrderSearch, SearchMetaParams
searchMetaParams) {
return purchaseOrderList(purchaseOrderSearch, false);
}
@Override
public long countActiveOrders() {
String queryString = "select count(*) from Order where state != 'DELETED'";
Query query = getSessionCustom().createQuery(queryString);
return (Long) query.uniqueResult();
}
@Override
public long countConsolidationOrders(PurchaseOrderSearch purchaseOrderSearch) {
String s = "SELECT count(*) FROM Order AS o JOIN o.consignment as con JOIN con.containers AS c JOIN c.containerType AS ct " +
"WHERE ct.code = :containerCode AND con.state = :state";
Query query = getSession().createQuery(s);
query.setParameter("containerCode", purchaseOrderSearch.getContainerTypeCode());
query.setParameter("state", purchaseOrderSearch.getConsignmentState());
query.setParameter(ELC_ORDER, purchaseOrderSearch.isElc());
return (Long) query.uniqueResult();
}
@Override
public List<PurchaseOrder> findOrdersForLiteConsignment(Consignment consignment) {
DetachedCriteria criteria = DetachedCriteria.forClass(getPersistentClass());
criteria.add(Restrictions.eq("liteConsignmentReference", consignment.getReference()));
return (List<PurchaseOrder>) findByCriteria(criteria);
}
@Override
public List<PurchaseOrder> findOrdersForProduct(Product product) {
String s = "select o.*, po.*, locj.* from orders o " +
"right join purchaseorder po on po.id = o.id " +
"left join letterofcredit_purchaseorder locj on locj.purchaseorders_id = po.id " +
"right join consignment on consignment.id = o.consignment_id " +
"left join lineitem on o.id = lineitem.order_id " +
"left join product on lineitem.code = product.code " +
"and lineitem.countryoforigin_code = product.countryoforigin_code " +
"and lineitem.supplier_id = product.supplier_id " +
"and lineitem.organisationalunit_id = product.organisationalunit_id " +
"where o.state in ('UNFINALISED','AWAITING_COSTING') and product.id = :id";
Query query = getSession().createSQLQuery(s)
.addEntity("po", PurchaseOrder.class)
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
query.setParameter("id", product.getId());
List<PurchaseOrder> list = query.list();
if (CollectionUtils.isNotEmpty(list)) {
for (PurchaseOrder purchaseOrder : list) {
HibernateUtils.initializeAndUnproxy(purchaseOrder);
HibernateUtils.initializeAndUnproxy(purchaseOrder.getConsignment());
}
}
return list;
}
@Override
public List<PurchaseOrder> findOrdersState(OrderStateSearchDTO orderStateSearchDTO) {
StringBuilder queryString = new StringBuilder("SELECT e from Order e where ");
ordersStateFilter(orderStateSearchDTO, queryString);
queryString.append(" ORDER BY e.created,e.orderReference asc");
Query query = getSession().createQuery(queryString.toString());
orderStateParams(orderStateSearchDTO, query);
query.setFirstResult((orderStateSearchDTO.getPageNumber()));
query.setMaxResults(orderStateSearchDTO.getPageSize());
return query.list();
}
@Override
public long countSearch(OrderStateSearchDTO orderStateSearchDTO) {
StringBuilder queryString = new StringBuilder("SELECT count(*) from Order e where ");
ordersStateFilter(orderStateSearchDTO, queryString);
Query query = getSession().createQuery(queryString.toString());
orderStateParams(orderStateSearchDTO, query);
return (Long) query.uniqueResult();
}
private void orderStateParams(OrderStateSearchDTO orderStateSearchDTO, Query query) {
query.setParameter("startDate", orderStateSearchDTO.getDateFrom(), TemporalType.DATE);
query.setParameter("endDate", orderStateSearchDTO.getDateTo(), TemporalType.DATE);
query.setParameter("elc", orderStateSearchDTO.getELC());
if (orderStateSearchDTO.getState() != null) {
query.setParameter("state", orderStateSearchDTO.getState());
}
}
private void ordersStateFilter(OrderStateSearchDTO orderStateSearchDTO, StringBuilder queryString) {
switch (orderStateSearchDTO.getDateFieldReference()) {
case STATE:
queryString.append("DATE(e.stateDate) BETWEEN DATE(:startDate) AND DATE(:endDate) AND e.elc= :elc");
break;
case LATEST_SHIPMENT_DATE:
queryString.append("e.orderDates.latestShipmentDate BETWEEN :startDate AND :endDate AND e.elc= :elc");
break;
case AVAILABILITY_DATE:
queryString.append(" e.orderDates.availabilityDate BETWEEN :startDate AND :endDate AND e.elc= :elc");
break;
case EARLIEST_SHIPMENT_DATE:
queryString.append(" e.orderDates.earliestShipmentDate BETWEEN :startDate AND :endDate AND e.elc= :elc");
break;
case ESTIMATE_ARRIVAL_AT_POD:
queryString.append(" e.orderDates.estimatedArrivalDate BETWEEN :startDate AND :endDate AND e.elc= :elc");
break;
case REQUIRED_ON_SITE_DATE:
queryString.append(" e.orderDates.requiredOnSiteDate BETWEEN :startDate AND :endDate AND e.elc= :elc");
break;
default:
throw new IllegalArgumentException("DateFieldReference Not Found");
}
if (orderStateSearchDTO.getState() != null) {
queryString.append(" AND e.state= :state");
}
}
@Override
public List<StatesDTO> getPeriods(StateCountDTO stateCountDTO) {
if (stateCountDTO.getELC() == null) {
throw new IllegalArgumentException("Required : isELC Field");
}
StringBuilder queryString = new StringBuilder("SELECT NEW com.tradecloud.dto.order.StatesDTO(e.state, count(e.state)) FROM Order e where ");
switch (stateCountDTO.getDateFieldReference()) {
case STATE:
if (!stateCountDTO.getDateCalculationType().equals(DateCalculationType.PREVIOUS)) {
throw new IllegalArgumentException("Required : Date calculation type should be previous");
}
queryString.append(" e.stateDate BETWEEN :startDate AND :endDate AND e.elc= :elc");
break;
case LATEST_SHIPMENT_DATE:
queryString.append(" e.orderDates.latestShipmentDate BETWEEN :startDate AND :endDate AND e.elc= :elc");
break;
case AVAILABILITY_DATE:
queryString.append(" e.orderDates.availabilityDate BETWEEN :startDate AND :endDate AND e.elc= :elc");
break;
case EARLIEST_SHIPMENT_DATE:
queryString.append(" e.orderDates.earliestShipmentDate BETWEEN :startDate AND :endDate AND e.elc= :elc");
break;
case ESTIMATE_ARRIVAL_AT_POD:
queryString.append(" e.orderDates.estimatedArrivalDate BETWEEN :startDate AND :endDate AND e.elc= :elc ");
break;
case REQUIRED_ON_SITE_DATE:
queryString.append(" e.orderDates.requiredOnSiteDate BETWEEN :startDate AND :endDate AND e.elc= :elc");
break;
default:
throw new IllegalArgumentException("DateFieldReference Not Found");
}
boolean excludeStates = stateCountDTO.getExcludedOrderStates() != null && !stateCountDTO.getExcludedOrderStates().isEmpty();
if (excludeStates) {
queryString.append(" and e.state not in :excludedStates");
}
boolean includedStates = stateCountDTO.getOrderStates() != null && !stateCountDTO.getOrderStates().isEmpty();
if (includedStates) {
queryString.append(" and e.state in :includedStates");
}
queryString.append(" GROUP BY e.state");
List<OrderState> orderStates = new ArrayList<>();
Query query = getSession().createQuery(queryString.toString());
query.setDate("startDate", stateCountDTO.getDateRange().getFrom());
query.setDate("endDate", stateCountDTO.getDateRange().getTo());
query.setBoolean("elc", stateCountDTO.getELC());
if (excludeStates) {
orderStates = stateCountDTO.getExcludedOrderStates();
query.setParameterList("excludedStates", orderStates);
}
if (includedStates) {
query.setParameterList("includedStates", stateCountDTO.getOrderStates());
}
return query.list();
}
@Override
public List orderInsuranceDeclaration(PurchaseOrderSearch search) {
StringWriter stringWriter = new StringWriter();
stringWriter.append("select o,c from ActualCostSummary c join c.originalCostable s join s.consignments cc " +
"join cc.orders o where c.id is not null");
if (search.getGrvDate().getFrom() != null) {
stringWriter.append(" and o.orderDates.goodsReceivedDate>=:grvFrom");
}
if (search.getGrvDate().getTo() != null) {
stringWriter.append(" and o.orderDates.goodsReceivedDate<=:grvTo");
}
Set<OrganisationalUnit> organisationalUnits = getOrganisationalUnits(search);
if (CollectionUtils.isNotEmpty(organisationalUnits)) {
stringWriter.append(" and o.organisationalUnit in (:orgUnit)");
}
if (search.getSupplier() != null) {
stringWriter.append(" and o.supplier=:orgSupplier");
}
stringWriter.append(" order by s.actualDepartureDate desc ");
org.hibernate.query.Query query = getSessionCustom().createQuery(stringWriter.toString());
if (search.getGrvDate().getFrom() != null) {
query.setParameter("grvFrom", search.getGrvDate().getFrom());
}
if (search.getGrvDate().getTo() != null) {
query.setParameter("grvTo", search.getGrvDate().getTo());
}
if (search.getOrganisationalUnit() != null) {
query.setParameter("orgUnit", organisationalUnits);
}
if (search.getSupplier() != null) {
query.setParameter("orgSupplier", search.getSupplier());
}
return query.list();
}
@Override
public List<Object[]> findOrdersItemStyle(Set<Long> ordersId) {
StringBuilder stringBuilder = new StringBuilder(" select distinct on (i.order_id) i.order_id, stylereference,styledescription" +
" from lineitem i where ")
.append("i.order_id in (:ordersId) order by i.order_id, i.created;");
return getSessionCustom().createNativeQuery(stringBuilder.toString()).setParameterList("ordersId", ordersId).list();
}
@Override
public List<OrderVarianceDTO> searchOrderVariance(ClcAlcOrderVarianceReportSearch search) {
StringBuilder stringBuilder = new StringBuilder(" select po.id ,o.orderreference,o.number as orderNumber,s.reference as ")
.append("shipmentReference,u.name as organisationName,oss.name as supplierName,si.shippingReference,")
.append("COALESCE(e.title,'')||' '||COALESCE(e.firstName,'')||' '||COALESCE(e.lastName,'') as buyerName,")
.append("item.stylereference as stylereference,item.styledescription as styledescription,s.id as shipmentId ")
.append("from PurchaseOrder po join orders o on (o.id=po.id) join consignment c on (c.id=o.consignment_id) ")
.append("join shipment s on (s.id=c.shipment_id) join organisationalunit u on (u.id=o.organisationalunit_id) ")
.append("join organisationalunitsupplier os on (os.id=po.supplier_id) ")
.append("join shippinginformation si on (si.id=o.shippinginformation_id) ")
.append("join supplier oss on (oss.id=os.supplier_id) join employee e on (e.id=po.buyer_id) ")
.append("join (select distinct on (i.order_id) stylereference,styledescription,order_id from lineitem i order by i.order_id )")
.append(" item on (item.order_id=po.id)")
.append(" where po.id is not null and s.state in (:shipmentStates) ");
if (CollectionUtils.isNotEmpty(search.getShipIdsSet())) {
stringBuilder.append(" and s.id in (:shipIds)");
}
if (search.getOrganisationalUnit() != null) {
stringBuilder.append(" and u.id=:orgId");
} else if (CollectionUtils.isNotEmpty(search.getOrganisationalUnits2())) {
stringBuilder.append(" and u.id in (:orgIds)");
}
if (search.getBuyer() != null) {
stringBuilder.append(" and e.id=:buyerId");
}
if (StringUtils.isNotEmpty(search.getOrderReference())) {
stringBuilder.append(" and o.orderreference=:orderRef");
}
if (!search.getGrnDateRange().isEmpty()) {
stringBuilder.append(" and o.goodsReceivedDate between :grnFrom and :grnTo");
}
if (!search.getSignOffDateRange().isEmpty()) {
stringBuilder.append(" and (select count (*) FROM shipment_shipmentevent left join shipmentevent " +
"on shipment_shipmentevent.events_id = shipmentevent.id ");
stringBuilder.append("WHERE shipment_shipmentevent.shipment_id = s.id and shipmentevent.eventtype = 'SIGNED_OFF'");
stringBuilder.append(" and shipmentevent.created between :signOffFrom and :signOffTo)>0");
}
NativeQuery nativeQuery = getSessionCustom().createNativeQuery(stringBuilder.toString());
final List<String> states = Arrays.asList(ShipmentState.SIGNED_OFF,
ShipmentState.STOCK_PARTIALLY_RECEIVED, ShipmentState.COMPLETE).stream().map(s -> s.name()).collect(Collectors.toList());
nativeQuery.setParameterList("shipmentStates", states);
if (CollectionUtils.isNotEmpty(search.getShipIdsSet())) {
nativeQuery.setParameterList("shipIds", search.getShipIdsSet());
}
if (search.getOrganisationalUnit() != null) {
nativeQuery.setParameter("orgId", search.getOrganisationalUnit().getId());
} else if (CollectionUtils.isNotEmpty(search.getOrganisationalUnits2())) {
Set<Long> ids = search.getOrganisationalUnits2().stream().map(OrganisationalUnit::getId).collect(Collectors.toSet());
nativeQuery.setParameter("orgIds", ids);
}
if (StringUtils.isNotEmpty(search.getOrderReference())) {
nativeQuery.setParameter("orderRef", search.getOrderReference());
}
if (search.getBuyer() != null) {
nativeQuery.setParameter("buyerId", search.getBuyer().getId());
}
if (!search.getGrnDateRange().isEmpty()) {
nativeQuery.setParameter("grnFrom", search.getGrnDateRange().getFrom());
nativeQuery.setParameter("grnTo", search.getGrnDateRange().getTo());
}
if (!search.getSignOffDateRange().isEmpty()) {
nativeQuery.setParameter("signOffFrom", search.getSignOffDateRange().getFrom());
nativeQuery.setParameter("signOffTo", search.getSignOffDateRange().getTo());
}
return nativeQuery.addScalar("id", StandardBasicTypes.LONG)
.addScalar("orderReference", StandardBasicTypes.STRING)
.addScalar("orderNumber", StandardBasicTypes.STRING)
.addScalar("shipmentReference", StandardBasicTypes.STRING)
.addScalar("organisationName", StandardBasicTypes.STRING)
.addScalar("supplierName", StandardBasicTypes.STRING)
.addScalar("buyerName", StandardBasicTypes.STRING)
.addScalar("styleReference", StandardBasicTypes.STRING)
.addScalar("styleDescription", StandardBasicTypes.STRING)
.addScalar("shippingReference", StandardBasicTypes.STRING)
.addScalar("shipmentId", StandardBasicTypes.LONG)
.setResultTransformer(Transformers.aliasToBean(OrderVarianceDTO.class)).list();
}
private DetachedCriteria getOrderPlannedSettlementCriteria(PurchaseOrderSearch search) {
DetachedCriteria criteria = DetachedCriteria.forClass(PurchaseOrder.class);
criteria.createAlias("plannedSettlements", "ps");
criteria.createAlias("shippingInformation", "si");
Set<OrganisationalUnit> organisationalUnits = getOrganisationalUnits(search);
if (!organisationalUnits.isEmpty()) {
criteria.add(Restrictions.in("organisationalUnit", organisationalUnits));
}
criteria.add(Restrictions.eq(ELC_ORDER, search.isElc()));
if (search.getCurrency() != null) {
criteria.add(Restrictions.eq("currency", search.getCurrency()));
}
if (StringUtils.isNoneEmpty(search.getOrderReference())) {
criteria.add(Restrictions.ilike("orderReference", search.getOrderReference(), MatchMode.ANYWHERE));
}
if (StringUtils.isNoneEmpty(search.getShippingReference())) {
criteria.add(Restrictions.ilike("si.shippingReference", search.getShippingReference(), MatchMode.ANYWHERE));
}
if (search.getPaymentMethod() != null) {
criteria.add(Restrictions.eq("paymentMethod", search.getPaymentMethod()));
}
if (search.getPaymentTerm() != null) {
criteria.add(Restrictions.eq("paymentTerm", search.getPaymentTerm()));
}
if (search.getSettledInFull() != null && search.getSettledInFull().equalsIgnoreCase("YES")) {
criteria.add(Restrictions.eq("ps.paymentState", PaymentState.SETTLED));
}
if (search.getSettledInFull() != null && search.getSettledInFull().equalsIgnoreCase("NO")) {
criteria.add(Restrictions.disjunction().add(Restrictions.isNull("ps.paymentState"))
.add(Restrictions.eq("ps.paymentState", PaymentState.UNSETTLED)));
}
OrderState[] states = null;
if (null == search.getState()) {
states = new OrderState[]{OrderState.BOOKED_IN, OrderState.FREIGHT_RECEIVED, OrderState.AWAITING_LSP_SIGNOFF, OrderState.SIGNED_OFF,
OrderState.STOCK_RECEIVED, OrderState.SHIPMENT_CREATED, OrderState.CANCEL_BOOKING, OrderState.FINALISED,
OrderState.TOLERANCE_EXCEEDED, STOCK_PARTIALLY_RECEIVED};
} else if (search.getState().equals(OrderState.FINALISED)) {
states = new OrderState[]{OrderState.AWAITING_LSP_SIGNOFF, OrderState.FINALISED, OrderState.TOLERANCE_EXCEEDED};
} else if (search.getState().equals(OrderState.SIGNED_OFF)) {
states = new OrderState[]{OrderState.BOOKED_IN, OrderState.FREIGHT_RECEIVED, OrderState.SIGNED_OFF,
OrderState.STOCK_RECEIVED, OrderState.SHIPMENT_CREATED, OrderState.CANCEL_BOOKING, STOCK_PARTIALLY_RECEIVED};
}
criteria.add(Restrictions.in("state", states));
// exclude all invoiced orders
criteria.add(Restrictions.sqlRestriction(" {alias}.id not in (select distinct(ao.originalid) from actualorder ao "
+ "join actualconsignment ac on (ac.id=ao.actualconsignment_id) where ac.costsinvoice_id is not null)"));
setOrderPlannedSettlementSpecificfields(search, criteria);
return criteria;
}
@Override
public long orderReCostCount(PurchaseOrderSearch search) {
StringBuilder stringBuilder = new StringBuilder("select count(*) ");
NativeQuery nativeQuery = getOrderReCostNativeQuery(search, stringBuilder, false);
return ((BigInteger) nativeQuery.uniqueResult()).longValue();
}
@Override
public List<OrderReCostResult> orderReCostSearch(PurchaseOrderSearch search) {
StringBuilder stringBuilder = new StringBuilder(" select po.id,c.id as consignmentId ,o.orderreference,c.reference as consignmentReference ")
.append(",u.name as organisationalUnitName,oss.name as supplierName,o.currency_code as currency,")
.append("si.shippingmode as shippingMode,o.latestshipmentdate,o.arrivalatplaceofdischargedate as estimatedArrivalDate,")
.append("freightf.name as freightForwarderName,cagent.name as clearingAgentName, ")
.append("pol.name as placeOfLoading,pod.name as placeOfDischarge,o.state as orderState ");
NativeQuery nativeQuery = getOrderReCostNativeQuery(search, stringBuilder, true);
SearchMetaParams searchMetaParams = search.getSearchMetaParams();
if (searchMetaParams != null) {
if (searchMetaParams.getRowCount() != -1) {
nativeQuery.setMaxResults(searchMetaParams.getRowCount());
}
if (searchMetaParams.getRowIndex() != -1) {
nativeQuery.setFirstResult(searchMetaParams.getRowIndex());
}
}
return nativeQuery.addScalar("id", StandardBasicTypes.LONG)
.addScalar("consignmentId", StandardBasicTypes.LONG)
.addScalar("orderReference", StandardBasicTypes.STRING)
.addScalar("consignmentReference", StandardBasicTypes.STRING)
.addScalar("organisationalUnitName", StandardBasicTypes.STRING)
.addScalar("supplierName", StandardBasicTypes.STRING)
.addScalar("currency", StandardBasicTypes.STRING)
.addScalar("shippingMode", StandardBasicTypes.STRING)
.addScalar("latestShipmentDate", StandardBasicTypes.DATE)
.addScalar("estimatedArrivalDate", StandardBasicTypes.DATE)
.addScalar("freightForwarderName", StandardBasicTypes.STRING)
.addScalar("clearingAgentName", StandardBasicTypes.STRING)
.addScalar("placeOfLoading", StandardBasicTypes.STRING)
.addScalar("placeOfDischarge", StandardBasicTypes.STRING)
.addScalar("orderState", StandardBasicTypes.STRING)
.setResultTransformer(Transformers.aliasToBean(OrderReCostResult.class)).list();
}
private NativeQuery getOrderReCostNativeQuery(PurchaseOrderSearch search, StringBuilder stringBuilder, boolean searchMode) {
stringBuilder.append(" from PurchaseOrder po join orders o on (o.id=po.id) join consignment c on (c.id=o.consignment_id) ")
.append("join organisationalunitsupplier os on (os.id=po.supplier_id) ")
.append("join supplier oss on (oss.id=os.supplier_id) ")
.append(" join organisationalunit u on (u.id=o.organisationalunit_id) ")
.append("join shippinginformation si on (si.id=o.shippinginformation_id) ")
.append("left join serviceprovider freightf on (freightf.id=si.freightforwarder_id) ")
.append("left join serviceprovider cagent on (cagent.id=si.clearingagent_id) ")
.append("left join placeofloading pol on (pol.code=si.placeofloading_code) ")
.append("left join placeofloading pod on (pod.code=si.placeofdischarge_code) ")
.append(" where po.id is not null and o.elc='false' and (o.state in (:orderStates) " +
"or c.state in ('FINALISED','AWAITING_TREASURY_RATES') )");
if (StringUtils.isNotEmpty(search.getConsignmentReference())) {
stringBuilder.append(" and c.reference ilike :consignmentReference");
}
if (StringUtils.isNotEmpty(search.getOrderReference())) {
stringBuilder.append(" and o.orderreference ilike :orderRef");
}
if (search.getOrganisationalUnit() != null) {
stringBuilder.append(" and u.id=:orgId");
}
if (search.getSupplier() != null) {
stringBuilder.append(" and os.id=:supplierId");
}
if (search.getCurrency() != null) {
stringBuilder.append(" and o.currency_code=:currency");
}
if (search.getShippingMode() != null) {
stringBuilder.append(" and si.shippingmode=:shippingmode");
}
if (search.getFreightForwarder() != null) {
stringBuilder.append(" and si.freightforwarder_id=:freightforwarderId");
}
if (search.getClearingAgent() != null) {
stringBuilder.append(" and si.clearingagent_id=:clearingagentId");
}
if (search.getPlaceOfLoading() != null) {
stringBuilder.append(" and pol.code=:placeofloadingCode");
}
if (search.getPlaceOfDischarge() != null) {
stringBuilder.append(" and pod.code=:placeofdischargeCode");
}
if (search.getLatestShipmentDateRange().getFrom() != null) {
stringBuilder.append(" and o.latestshipmentdate>=:latestshipmentdateFrom");
}
if (search.getLatestShipmentDateRange().getTo() != null) {
stringBuilder.append(" and o.latestshipmentdate<=:latestshipmentdateTo");
}
if (search.getEstimatedArrivalDateRange().getFrom() != null) {
stringBuilder.append(" and o.arrivalatplaceofdischargedate>=:estimatedarrivaldateFrom");
}
if (search.getEstimatedArrivalDateRange().getTo() != null) {
stringBuilder.append(" and o.arrivalatplaceofdischargedate<=:estimatedarrivaldateTo");
}
if (searchMode) {
stringBuilder.append(" order by o.created desc");
}
NativeQuery nativeQuery = getSessionCustom().createNativeQuery(stringBuilder.toString());
nativeQuery.setParameterList("orderStates", search.getOrderStateList().stream()
.map(orderState -> orderState.name()).collect(Collectors.toList()));
if (StringUtils.isNotEmpty(search.getConsignmentReference())) {
nativeQuery.setParameter("consignmentReference", "%" + search.getConsignmentReference() + "%");
}
if (StringUtils.isNotEmpty(search.getOrderReference())) {
nativeQuery.setParameter("orderRef", "%" + search.getOrderReference() + "%");
}
if (search.getOrganisationalUnit() != null) {
nativeQuery.setParameter("orgId", search.getOrganisationalUnit().getId());
}
if (search.getSupplier() != null) {
nativeQuery.setParameter("supplierId", search.getSupplier().getId());
}
if (search.getCurrency() != null) {
nativeQuery.setParameter("currency", search.getCurrency().getCode());
}
if (search.getShippingMode() != null) {
nativeQuery.setParameter("shippingmode", search.getShippingMode().name());
}
if (search.getFreightForwarder() != null) {
nativeQuery.setParameter("freightforwarderId", search.getFreightForwarder().getId());
}
if (search.getClearingAgent() != null) {
nativeQuery.setParameter("clearingagentId", search.getClearingAgent().getId());
}
if (search.getPlaceOfLoading() != null) {
nativeQuery.setParameter("placeofloadingCode", search.getPlaceOfLoading().getCode());
}
if (search.getPlaceOfDischarge() != null) {
nativeQuery.setParameter("placeofdischargeCode", search.getPlaceOfDischarge().getCode());
}
if (search.getLatestShipmentDateRange().getFrom() != null) {
nativeQuery.setParameter("latestshipmentdateFrom", search.getLatestShipmentDateRange().getFrom());
}
if (search.getLatestShipmentDateRange().getTo() != null) {
nativeQuery.setParameter("latestshipmentdateTo", search.getLatestShipmentDateRange().getTo());
}
if (search.getEstimatedArrivalDateRange().getFrom() != null) {
nativeQuery.setParameter("estimatedarrivaldateFrom", search.getEstimatedArrivalDateRange().getFrom());
}
if (search.getEstimatedArrivalDateRange().getTo() != null) {
nativeQuery.setParameter("estimatedarrivaldateTo", search.getEstimatedArrivalDateRange().getTo());
}
return nativeQuery;
}
@Override
public PurchaseOrder retrieveWithConsignment(long id) {
EntityGraph<?> graph = getCurrentSession().getEntityGraph("graph.PurchaseOrderConsignment");
return (PurchaseOrder) getCurrentSession()
.createQuery("SELECT o FROM PurchaseOrder o where o.id=:id", getPersistentClass())
.setHint("HINT_SPEC_FETCH_GRAPH", graph)
.setParameter("id", id)
.uniqueResult();
}
@Override
public void updateOrdersNotBookedBusState() {
String sql = """
UPDATE orders p
SET businessstate = 'NOT_BOOKED', updated = NOW() from Orders o join consignment c on (o.consignment_id=c.id)
WHERE p.id=o.id AND c.shipment_id is null and CURRENT_DATE >= DATE(o.availabilitydate - INTERVAL '14 DAY')
AND o.cargoreadydate IS NULL
AND o.supplierbookingrequestdate IS NULL
AND o.bookingdate IS NULL
AND (o.businessstate <> 'NOT_BOOKED' OR o.businessstate is null)
""";
Query query = getSessionCustom().createSQLQuery(sql);
int rowsUpdated = query.executeUpdate();
logger.info("Rows updated: " + rowsUpdated);
}
@Override
public List<OrderCountDTO> countNotShippedForMonth(Date date, Set<Long> productIds, boolean noStartDateFilter) {
return countOrdersForMonth(date, productIds, false,noStartDateFilter);
}
@Override
public List<OrderCountDTO> countShippedForMonth(Date date, Set<Long> productIds) {
return countOrdersForMonth(date, productIds, true, false);
}
@SuppressWarnings("unchecked")
private List<OrderCountDTO> countOrdersForMonth(
Date date,
Set<Long> productIds,
boolean shipped,
boolean noStartDateFilter
) {
// Convert java.util.Date → LocalDate
LocalDate month = date.toInstant()
.atZone(ZoneId.systemDefault())
.toLocalDate();
LocalDate startOfMonth = month.withDayOfMonth(1);
LocalDate endOfMonth = month.with(TemporalAdjusters.lastDayOfMonth());
String shipmentCondition = shipped ? "IS NOT NULL" : "IS NULL";
String dateRangeFilter;
if (shipped) {
dateRangeFilter = ""; // shipped does not use arrival date filter
} else if (noStartDateFilter) {
dateRangeFilter = "AND o.requiredonsitedate <= :endDate";
} else {
dateRangeFilter = "AND o.requiredonsitedate BETWEEN :startDate AND :endDate";
}
// Create SQL query dynamically
String query = """
SELECT
p.id AS product_id,
fd.code AS finalDestCode,
ROUND(SUM(i.unitquantity)) AS order_count
FROM lineitem i
JOIN product p
ON p.code = i.code
JOIN orders o ON i.order_id = o.id
JOIN consignment c ON c.id = o.consignment_id
JOIN shippinginformation si ON si.id = o.shippinginformation_id
JOIN finaldestination fd ON fd.code = si.finaldestination_code
WHERE c.shipment_id %s
%s
AND o.state IN (:states)
AND p.id IN (:productIds)
GROUP BY p.id, fd.code
""".formatted(shipmentCondition, dateRangeFilter);
// Build allowed states
Set<String> states = com.tradecloud.domain.model.ordermanagement.Order.NON_EDITABLE_STATES.stream()
.filter(s -> s != OrderState.DELETED && s != OrderState.ARCHIVED)
.filter(s -> shipped ? s != OrderState.STOCK_RECEIVED : true)
.map(Enum::name)
.collect(Collectors.toUnmodifiableSet());
NativeQuery sqlQuery = getSessionCustom().createSQLQuery(query);
// Apply date parameters *only when needed*
if (!shipped) {
if (!noStartDateFilter) {
sqlQuery.setParameter("startDate", startOfMonth);
}
sqlQuery.setParameter("endDate", endOfMonth);
}
sqlQuery.setParameterList("productIds", productIds);
sqlQuery.setParameterList("states", states);
// Execute
List<Object[]> results = sqlQuery.list();
// Map to DTOs
return results.stream()
.map(row -> new OrderCountDTO(
((Number) row[0]).longValue(),
(String) row[1],
((Number) row[2]).intValue()
))
.toList();
}
@Override
public void updateAllOrdersLinkedToProduct(Long productID) {
String updatedQuery = """
UPDATE lineitem i
SET homologationStatus = h.status,
updated = NOW()
FROM product p
JOIN homologation h ON h.id = p.homologation_id
JOIN orders o ON TRUE
JOIN consignment c ON c.id = o.consignment_id
LEFT JOIN shipment s ON s.id = c.shipment_id
WHERE o.id = i.order_id
AND p.id = :productID
AND p.code = i.code
AND p.countryoforigin_code = i.countryoforigin_code
AND p.organisationalunit_id = i.organisationalunit_id
AND p.supplier_id = i.supplier_id
AND (
s.id IS NULL
OR s.state NOT IN ('COMPLETED','STOCK_FULLY_RECEIVED')
) """;
int i = getSessionCustom()
.createNativeQuery(updatedQuery)
.setParameter("productID", productID)
.executeUpdate();
logger.info("updated match %s for productid %s".formatted(i, productID));
}
}