PackingListRepositoryImpl.java
package com.tradecloud.repository.impl;
import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.Multimap;
import com.tradecloud.domain.container.*;
import com.tradecloud.domain.item.AdditionalLineItemType;
import com.tradecloud.domain.model.ordermanagement.Consignment;
import com.tradecloud.domain.shipment.Shipment;
import com.tradecloud.dto.base.IdReferenceDto;
import com.tradecloud.dto.base.ReferenceValueDto;
import com.tradecloud.repository.PackingListRepository;
import com.tradecloud.repository.SearchMetaParams;
import org.hibernate.Query;
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 java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;
/**
* Default implementation of the {@code PackingListRepository} interface.
*/
@Repository
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class PackingListRepositoryImpl extends RepositoryBaseImplNoSearch<PackingList> implements PackingListRepository {
private static final long serialVersionUID = 1L;
private static final String PACKING_LIST_TOTALS =
"select 0 as entityId, coalesce(sum(pli.unitquantity), 0.00) as quantity, coalesce(sum(pli.numpackages), 0.00) as packages, "
+ "coalesce(sum(pli.unitvolume * pli.unitQuantity), 0.00) as volume, "
+ "coalesce(sum(pli.unitweight * pli.unitQuantity), 0.00) as weight from "
+ "packinglistitem pli where pli.packinglistorder_id in (select plo.id from packinglistorder plo where "
+ "plo.packinglistcontainer_id in (select plc.id from packinglistcontainer plc where plc.packinglist_id = :packingListId))";
private static final String PACKING_LIST_ORDER_TOTALS =
"select pli.packinglistorder_id as entityId, coalesce(sum(pli.unitquantity), 0.00) as quantity, "
+ "coalesce(sum(pli.numpackages), 0.00) as packages, coalesce(sum(pli.unitvolume * pli.unitQuantity), 0.00) as volume, "
+ "coalesce(sum(pli.unitweight * pli.unitQuantity), 0.00) as weight from "
+ "packinglistitem pli where pli.packinglistorder_id in (select id from packinglistorder where "
+ "packinglistcontainer_id in (select id from packinglistcontainer where packinglist_id = :packingListId)) "
+ "group by entityId";
private static final String PACKING_LIST_CONTAINER_TOTALS =
"select plo.packinglistcontainer_id as entityId, coalesce(sum(pli.unitquantity), 0.00) as quantity, "
+ "coalesce(sum(pli.numpackages), 0.00) as packages, coalesce(sum(pli.unitvolume * pli.unitQuantity), 0.00) as volume, "
+ "coalesce(sum(pli.unitweight * pli.unitQuantity), 0.00) as weight from "
+ "packinglistitem pli, packinglistorder plo, packinglistcontainer plc, packinglist pl where "
+ "pli.packinglistorder_id = plo.id and plo.packinglistcontainer_id = plc.id and plc.packinglist_id = pl.id "
+ "and pl.id = :packingListId group by entityId";
@Override
public List<IdReferenceDto> findOrdersByShipment(Shipment shipment) {
@SuppressWarnings({"unchecked"})
List<IdReferenceDto> results = getCurrentSession().createNativeQuery(
"select o.orderreference as \"reference\", o.id, si.shippingreference from orders o join shippinginformation si " +
"on o.shippinginformation_id = si.id where o.id in (select co.orders_id "
+ "from consignment_orders co where co.consignment_id in (select c.id from consignment c "
+ "where c.shipment_id = :shipmentId order by c.addedToShipmentDate) group by co.consignment_id,"
+ "co.orders_id) order by o.addedtoconsignmentdate").setParameter("shipmentId", shipment.getId())
.setResultTransformer(Transformers.aliasToBean(IdReferenceDto.class)).list();
return results;
}
@Override
public List<IdReferenceDto> findOrdersByConsignment(Consignment consignment) {
@SuppressWarnings({"unchecked"})
List<IdReferenceDto> results = getCurrentSession().createNativeQuery(
"select o.orderreference as \"reference\", o.id, si.shippingreference from orders o join shippinginformation si " +
"on o.shippinginformation_id = si.id where o.id in (select co.orders_id "
+ "from consignment_orders co where co.consignment_id = :consignmentId)"
+ "order by o.addedtoconsignmentdate").setParameter("consignmentId", consignment.getId())
.setResultTransformer(Transformers.aliasToBean(IdReferenceDto.class)).list();
return results;
}
@Override
public List<Consignment> findActiveConsignmentsForShipment(long shipment) {
String queryString = "from Consignment where shipment.id = :shipment and state != 'DELETED' ORDER BY addedToShipmentDate";
Query query = getCurrentSession().createQuery(queryString);
query.setParameter("shipment", shipment);
return query.list();
}
@Override
public List<IdReferenceDto> findContainersByPackingList(PackingList packingList) {
@SuppressWarnings({"unchecked"})
List<IdReferenceDto> results = getCurrentSession().createNativeQuery(
"select c.reference as \"reference\", c.id from packinglistcontainer c where c.packinglist_id = :packingListId "
+ "order by c.created").setParameter("packingListId", packingList.getId())
.setResultTransformer(Transformers.aliasToBean(IdReferenceDto.class)).list();
return results;
}
@Override
public List<PackingListItem> findByOrderAndPackingListContainer(long orderId, long packingListContainerId, SearchMetaParams searchMetaParams) {
Query query = getCurrentSession().createQuery(
"from PackingListItem as pli where pli.packingListOrder.id in (select plo.id from PackingListOrder as plo "
+ "where plo.packingListContainer.id = :packingListContainerId and plo.orderReference = "
+ "(select orderReference from Order where id = :orderId)) order by pli.id asc");
query.setParameter("packingListContainerId", packingListContainerId).setParameter("orderId", orderId)
.setFirstResult(searchMetaParams.getRowIndex()).setMaxResults(searchMetaParams.getRowCount());
@SuppressWarnings("unchecked")
List<PackingListItem> results = (List<PackingListItem>) query.list();
return results;
}
@Override
public long countPackingListItemsByOrderAndPackingListContainer(long orderId, long packingListContainerId) {
String queryString = "select count(*) from PackingListItem as pli where pli.packingListOrder.id " +
"in (select plo.id from PackingListOrder as plo "
+ "where plo.packingListContainer.id = :packingListContainerId and plo.orderReference = "
+ "(select orderReference from Order where id = :orderId))";
return (Long) getCurrentSession().createQuery(
queryString)
.setParameter("packingListContainerId", packingListContainerId).setParameter("orderId", orderId).uniqueResult();
}
@Override
public PackingListTotals getPackingListTotals(PackingList packingList) {
@SuppressWarnings("rawtypes")
List results = getTotals(packingList, PACKING_LIST_TOTALS);
return (PackingListTotals) results.get(0);
}
@Override
public List<PackingListTotals> getPackingListOrderTotals(PackingList packingList) {
return getTotals(packingList, PACKING_LIST_ORDER_TOTALS);
}
@Override
public List<PackingListTotals> getPackingListContainerTotals(PackingList packingList) {
return getTotals(packingList, PACKING_LIST_CONTAINER_TOTALS);
}
@Override
public List<ReferenceValueDto> getPackingListItemUnitQuantityTotals(PackingList packingList) {
String query = """
select plo.orderReference as parentReference,
pli.reference as reference,
COALESCE(pli.supplierItemReference,'') as supplierItemReference,
pli.lineNumber as lineNumber,
COALESCE(pli.organisationalunit,'') as organisationalUnit,
sum(pli.unitquantity) as value
from packinglistitem pli, packinglistorder plo,
packinglistcontainer plc, packinglist pl where pli.packinglistorder_id = plo.id
and plo.packinglistcontainer_id = plc.id and plc.packinglist_id = pl.id and pl.id = :packingListId
group by plo.orderReference, pli.reference, pli.organisationalunit, pli.supplieritemreference, pli.lineNumber
order by plo.orderReference, reference
""";
@SuppressWarnings("unchecked")
List<ReferenceValueDto> results = getCurrentSession().createNativeQuery(
query)
.addScalar("reference", StandardBasicTypes.STRING)
.addScalar("supplierItemReference", StandardBasicTypes.STRING)
.addScalar("lineNumber", StandardBasicTypes.INTEGER)
.addScalar("organisationalUnit", StandardBasicTypes.STRING)
.addScalar("parentReference", StandardBasicTypes.STRING)
.addScalar("value", StandardBasicTypes.BIG_DECIMAL)
.setParameter("packingListId", packingList.getId())
.setResultTransformer(Transformers.aliasToBean(ReferenceValueDto.class)).list();
return results;
}
@Override
public List<Map<String, String>> getPackingListItemUnitQuantityTotals(String orderReference) {
String query = """
select \
pli.reference as reference,
COALESCE(pli.supplierItemReference,'') as supplierItemReference,
pli.lineNumber as lineNumber,
COALESCE(pli.organisationalunit,'') as organisationalUnit,
sum(pli.unitquantity) as unitQuantitySum \
from shipment s \
join packinglist pl on s.id = pl.shipmentid \
join packinglistcontainer plc on pl.id = plc.packinglist_id \
join packinglistorder plo on plc.id = plo.packinglistcontainer_id \
join packinglistitem pli on plo.id = pli.packinglistorder_id \
where plo.orderreference= :orderReference \
and s.state not in ('DELETED') \
group by pli.reference, pli.organisationalunit, pli.supplieritemreference, pli.lineNumber
""";
@SuppressWarnings("unchecked")
List<Map<String, String>> results = getCurrentSession()
.createSQLQuery(query)
.addScalar("reference", StandardBasicTypes.STRING)
.addScalar("supplierItemReference", StandardBasicTypes.STRING)
.addScalar("lineNumber", StandardBasicTypes.STRING)
.addScalar("organisationalUnit", StandardBasicTypes.STRING)
.addScalar("unitQuantitySum", StandardBasicTypes.STRING)
.setParameter("orderReference", orderReference)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
return results;
}
@Override
public List<Map<String, String>> getPackingListItemWeightTotals(String orderReference) {
String query = """
select
pli.reference as reference,
COALESCE(pli.supplierItemReference,'') as supplierItemReference,
pli.lineNumber as lineNumber,
COALESCE(pli.organisationalunit,'') as organisationalUnit,
max(pli.unitweight) as totalWeightSum \
from shipment s \
join packinglist pl on s.id = pl.shipmentid \
join packinglistcontainer plc on pl.id = plc.packinglist_id \
join packinglistorder plo on plc.id = plo.packinglistcontainer_id \
join packinglistitem pli on plo.id = pli.packinglistorder_id \
where plo.orderreference= :orderReference \
and s.state not in ('DELETED') \
group by pli.reference, pli.organisationalunit,pli.supplieritemreference, pli.lineNumber
""";
@SuppressWarnings("unchecked")
List<Map<String, String>> results = getCurrentSession()
.createNativeQuery(query)
.addScalar("reference", StandardBasicTypes.STRING)
.addScalar("supplierItemReference", StandardBasicTypes.STRING)
.addScalar("lineNumber", StandardBasicTypes.STRING)
.addScalar("organisationalUnit", StandardBasicTypes.STRING)
.addScalar("totalWeightSum", StandardBasicTypes.STRING)
.setParameter("orderReference", orderReference)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
return results;
}
@Override
public List<Map<String, String>> getPackingListItemVolumeTotals(String orderReference) {
String query = """
select
pli.reference as reference,
COALESCE(pli.supplierItemReference,'') as supplierItemReference,
pli.lineNumber as lineNumber,
COALESCE(pli.organisationalunit,'') as organisationalUnit,
sum(pli.unitvolume) as totalVolumeSum
from shipment s \
join packinglist pl on s.id = pl.shipmentid \
join packinglistcontainer plc on pl.id = plc.packinglist_id \
join packinglistorder plo on plc.id = plo.packinglistcontainer_id \
join packinglistitem pli on plo.id = pli.packinglistorder_id \
where plo.orderreference= :orderReference \
and pli.unitquantity > 0 \
and s.state not in ('DELETED') \
group by pli.reference, pli.organisationalunit,pli.supplieritemreference, pli.lineNumber
""";
@SuppressWarnings("unchecked")
List<Map<String, String>> results = getCurrentSession()
.createNativeQuery(query)
.addScalar("reference", StandardBasicTypes.STRING)
.addScalar("supplierItemReference", StandardBasicTypes.STRING)
.addScalar("lineNumber", StandardBasicTypes.STRING)
.addScalar("organisationalUnit", StandardBasicTypes.STRING)
.addScalar("totalVolumeSum", StandardBasicTypes.STRING)
.setParameter("orderReference", orderReference)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
return results;
}
@Override
public List<Map<String, String>> getPackingListItemUnitQuantityTotalsWithWeightAndVolume(String orderReference) {
String query = """
select
pli.reference as reference,
COALESCE(pli.supplierItemReference,'') as supplierItemReference,
pli.lineNumber as lineNumber,
COALESCE(pli.organisationalunit,'') as organisationalUnit,
sum(pli.unitquantity) as \
unitQuantitySum,sum(pli.totalvolume) as totalVolumeSum, sum(pli.totalweight) as totalWeightSum from shipment s \
join packinglist pl on s.id = pl.shipmentid \
join packinglistcontainer plc on pl.id = plc.packinglist_id \
join packinglistorder plo on plc.id = plo.packinglistcontainer_id \
join packinglistitem pli on plo.id = pli.packinglistorder_id \
where plo.orderreference= :orderReference \
and s.state not in ('DELETED') \
group by pli.reference, pli.organisationalunit,pli.supplieritemreference, pli.lineNumber
""";
@SuppressWarnings("unchecked")
List<Map<String, String>> results = getCurrentSession()
.createNativeQuery(query)
.addScalar("reference", StandardBasicTypes.STRING)
.addScalar("supplierItemReference", StandardBasicTypes.STRING)
.addScalar("lineNumber", StandardBasicTypes.STRING)
.addScalar("organisationalUnit", StandardBasicTypes.STRING)
.addScalar("unitQuantitySum", StandardBasicTypes.STRING)
.addScalar("totalVolumeSum", StandardBasicTypes.STRING)
.addScalar("totalWeightSum", StandardBasicTypes.STRING)
.setParameter("orderReference", orderReference)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
return results;
}
@Override
public PackingListContainer findContainerByPackingListAndContainerReference(PackingList packingList, String reference) {
@SuppressWarnings("unchecked")
List<PackingListContainer> results = (List<PackingListContainer>)
findByNamedQueryAndNamedParam("packingListContainer.findByPackingListAndReference",
new String[]{"packingList", "reference"}, new Object[]{packingList, reference});
if (!results.isEmpty()) {
return results.get(0);
}
return null;
}
@Override
public PackingList findByShipmentReference(String shipmentReference) {
List<PackingList> results = (List<PackingList>) findByNamedQueryAndNamedParam("packingList.findByShipmentReference",
"shipmentReference", shipmentReference);
if (!results.isEmpty()) {
return results.get(0);
}
return null;
}
@Override
public Map<String, PackingList> findAllByShipmentReferences(Set<String> shipmentReferences) {
if (shipmentReferences == null || shipmentReferences.isEmpty()) {
return Collections.emptyMap();
}
List<PackingList> packingLists = findByShipmentReferenceIn(shipmentReferences);
return packingLists.stream()
.filter(pl -> pl.getShipmentReference() != null)
.collect(Collectors.toMap(PackingList::getShipmentReference, Function.identity()));
}
@Override
public Map<String, Multimap<String, BigDecimal>> getPackingListItemTotalsWithWeightAndVolumeByOrderReferences(Set<String> orderReferences) {
if (orderReferences == null || orderReferences.isEmpty()) {
return Collections.emptyMap();
}
List<Map<String, String>> rawResults = getPackingListItemUnitQuantityTotalsWithWeightAndVolumeBatch(orderReferences);
return rawResults.stream()
.filter(row -> row.containsKey("orderReference") && row.containsKey("reference") && row.containsKey("organisationalUnit"))
.collect(Collectors.groupingBy(
row -> row.get("orderReference"),
Collectors.collectingAndThen(
Collectors.toList(),
rows -> {
Multimap<String, BigDecimal> multimap = ArrayListMultimap.create();
for (Map<String, String> row : rows) {
String itemRef = row.get("reference").trim();
String orgUnit = row.get("organisationalUnit").trim();
String keyPrefix = itemRef + "-" + orgUnit;
BigDecimal quantity = new BigDecimal(row.getOrDefault("unitQuantitySum", "0"));
BigDecimal volume = new BigDecimal(row.getOrDefault("totalVolumeSum", "0"));
BigDecimal weight = new BigDecimal(row.getOrDefault("totalWeightSum", "0"));
multimap.put(keyPrefix + ".quantity", quantity);
multimap.put(keyPrefix + ".volume", volume);
multimap.put(keyPrefix + ".weight", weight);
}
return multimap;
}
)
));
}
@Override
public List<PackingListOrder> findOrdersByReference(String orderReference) {
@SuppressWarnings("unchecked")
List<PackingListOrder> results =
(List<PackingListOrder>) findByNamedQueryAndNamedParam("packingListOrder.findOrdersByReference", "orderReference", orderReference);
return results;
}
@Override
@SuppressWarnings("unchecked")
public List<PackingList> findByShipmentReferenceIn(Set<String> shipmentReferences) {
if (shipmentReferences == null || shipmentReferences.isEmpty()) {
return Collections.emptyList();
}
String hql = "FROM PackingList pl WHERE pl.shipmentReference IN (:refs)";
return getCurrentSession()
.createQuery(hql)
.setParameterList("refs", shipmentReferences)
.list();
}
@Override
@SuppressWarnings("unchecked")
public List<Map<String, String>> getPackingListItemUnitQuantityTotalsWithWeightAndVolumeBatch(Set<String> orderReferences) {
if (orderReferences == null || orderReferences.isEmpty()) {
return Collections.emptyList();
}
String sql = "SELECT plo.orderreference AS orderReference, "
+ "pli.reference AS reference, "
+ "pli.organisationalunit AS organisationalUnit, "
+ "SUM(pli.unitquantity) AS unitQuantitySum, "
+ "SUM(pli.totalvolume) AS totalVolumeSum, "
+ "SUM(pli.totalweight) AS totalWeightSum "
+ "FROM packinglistitem pli "
+ "JOIN packinglistorder plo ON pli.packinglistorder_id = plo.id "
+ "WHERE plo.orderreference IN (:orderReferences) "
+ "GROUP BY plo.orderreference, pli.reference, pli.organisationalunit";
return getCurrentSession()
.createSQLQuery(sql)
.addScalar("orderReference", StandardBasicTypes.STRING)
.addScalar("organisationalUnit", StandardBasicTypes.STRING)
.addScalar("reference", StandardBasicTypes.STRING)
.addScalar("unitQuantitySum", StandardBasicTypes.STRING)
.addScalar("totalVolumeSum", StandardBasicTypes.STRING)
.addScalar("totalWeightSum", StandardBasicTypes.STRING)
.setParameterList("orderReferences", orderReferences)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
.list();
}
@Override
public List<PackingListOrder> findOrdersByPackingListContainer(PackingListContainer packingListContainer) {
@SuppressWarnings("unchecked")
List<PackingListOrder> results =
(List<PackingListOrder>) getNamedQueryAndNamedParam("packingListOrder.findOrdersByPackingListContainer",
"packingListContainerId", packingListContainer.getId());
return results;
}
@Override
public PackingListItem findPackingListItemByOrderReferenceAndItemReference(String orderReference, String itemReference, String organisation) {
@SuppressWarnings("unchecked")
List<PackingListItem> results =
(List<PackingListItem>) getNamedQueryAndNamedParam("packingListItem.findByOrderReferenceAndItemReferenceAndOrganisation",
new String[]{"orderReference", "itemReference", "organisationalUnit"}, new Object[]{orderReference, itemReference, organisation});
return results.isEmpty() ? null : results.get(0);
}
@Override
public List<PackingListItem> findPackingListItemByOrderReferenceAndItemReferenceList(String orderReference,
String itemReference, String organisation) {
@SuppressWarnings("unchecked")
List<PackingListItem> results =
(List<PackingListItem>) getNamedQueryAndNamedParam("packingListItem.findByOrderReferenceAndItemReferenceAndOrganisation",
new String[]{"orderReference", "itemReference", "organisationalUnit"}, new Object[]{orderReference,
itemReference, organisation});
return results.isEmpty() ? null : results;
}
private List<PackingListTotals> getTotals(PackingList packingList, String query) {
@SuppressWarnings("unchecked")
List<PackingListTotals> results =
getCurrentSession().createNativeQuery(query).addScalar("entityId", StandardBasicTypes.LONG)
.addScalar("quantity", StandardBasicTypes.BIG_DECIMAL).addScalar("packages", StandardBasicTypes.BIG_DECIMAL)
.addScalar("volume", StandardBasicTypes.BIG_DECIMAL).addScalar("weight", StandardBasicTypes.BIG_DECIMAL)
.setParameter("packingListId", packingList.getId()).setResultTransformer(Transformers.aliasToBean(PackingListTotals.class))
.list();
return results;
}
@Override
public boolean hasSample(PackingListOrder packingListOrder) {
return hasSampleOrSpare(packingListOrder, AdditionalLineItemType.SAMPLE);
}
@Override
public boolean hasSparePart(PackingListOrder packingListOrder) {
return hasSampleOrSpare(packingListOrder, AdditionalLineItemType.SPARE_PART);
}
private boolean hasSampleOrSpare(PackingListOrder packingListOrder, final AdditionalLineItemType additionalLineItemType) {
final String sql =
"select count(*) from packinglistitem pli where pli.packinglistorder_id = :packingListOrderId and" +
" pli.reference = :reference and pli.additional is true";
int count = ((BigInteger) getCurrentSession().createNativeQuery(sql).
setParameter("reference", additionalLineItemType.getName()).
setParameter("packingListOrderId", packingListOrder.getId()).
uniqueResult()).intValue();
return count > 0;
}
@Override
public List<IdReferenceDto> findOrdersByReferences(long packingListContainerId, List<String> packingListOrderReferences) {
@SuppressWarnings({"unchecked"})
List<IdReferenceDto> results = getCurrentSession().createNativeQuery(
"select plo.orderreference as \"reference\", plo.id from packinglistorder plo where" +
" plo.packinglistcontainer_id = :packingListContainerId and plo.orderreference " +
" in (:packingListOrderReferences)"
+ "order by plo.created").setParameter("packingListContainerId", packingListContainerId).
setParameterList("packingListOrderReferences", packingListOrderReferences)
.setResultTransformer(Transformers.aliasToBean(IdReferenceDto.class)).list();
return results;
}
@Override
public PackingList findByShipmentId(long shipmentId) {
List<PackingList> results = (List<PackingList>) findByNamedQueryAndNamedParam("packingList.findByShipmentId", "shipmentId", shipmentId);
if (!results.isEmpty()) {
return results.get(0);
}
return null;
}
@Override
public void deleteOrdersByReferenceFromPackingList(PackingList packingList, List<String> orderReferences) {
if (orderReferences.size() > 0) {
String packingListOrderQuery = " from " + PackingListOrder.class.getSimpleName()
+ " as plo where plo.orderReference in (:orderReferences)"
+ " and plo.packingListContainer in (:containers)";
//deleting items first.
deletePackingListItems(packingList, orderReferences, packingListOrderQuery);
deletePackingListOrders(packingList, orderReferences, packingListOrderQuery);
}
}
private void deletePackingListOrders(PackingList packingList, List<String> orderReferences, String packingListOrderQuery) {
Query query = getCurrentSession()
.createQuery(
"delete" + packingListOrderQuery);
query.setParameterList("orderReferences", orderReferences).setParameterList("containers", packingList.getContainers());
query.executeUpdate();
}
private void deletePackingListItems(PackingList packingList, List<String> orderReferences, String packingListOrderSubQuery) {
Query query = getCurrentSession()
.createQuery("delete from " + PackingListItem.class.getSimpleName() + " as pli where pli.packingListOrder.id in ("
+ " select plo.id " + packingListOrderSubQuery + ")");
query.setParameterList("orderReferences", orderReferences).setParameterList("containers", packingList.getContainers());
query.executeUpdate();
}
@Override
public PackingListContainerBulkUpload findBulkPackingListContainer(String reference, ContainerType containerType, Long packinglist_id) {
Query query = getCurrentSession()
.createQuery("from " + PackingListContainerBulkUpload.class.getSimpleName()
+ " where reference = :reference "
+ "and containerType = :containerType and packingList_id = :packingList_id");
query.setParameter("reference", reference).setParameter("containerType", containerType.getCode()).
setParameter("packingList_id", packinglist_id);
return (PackingListContainerBulkUpload) query.uniqueResult();
}
@Override
public List<Map<String, String>> getPackingListItemVolumeWeightAverageUnits(String orderReference) {
String query = "select pli.reference as reference, " +
"pli.lineNumber as lineNumber," +
"COALESCE(pli.organisationalunit,'') as organisationalUnit," +
"pli.supplierItemReference as supplierItemReference," +
"COALESCE(sum(pli.unitvolume*pli.unitquantity)/(NULLIF(sum(pli.unitquantity),0)),0) as averageUnitVolume, " +
"COALESCE(sum(pli.unitweight*pli.unitquantity)/(NULLIF(sum(pli.unitquantity),0)),0) as averageUnitWeight from shipment s " +
"join packinglist pl on s.id = pl.shipmentid " +
"join packinglistcontainer plc on pl.id = plc.packinglist_id " +
"join packinglistorder plo on plc.id = plo.packinglistcontainer_id " +
"join packinglistitem pli on plo.id = pli.packinglistorder_id " +
"where plo.orderreference= :orderReference " +
"and pli.unitquantity > 0 " +
"and s.state not in ('DELETED') " +
"group by pli.reference, pli.organisationalunit, pli.lineNumber, pli.supplierItemReference";
//logger.debug(query);
@SuppressWarnings("unchecked")
List<Map<String, String>> results = getCurrentSession()
.createSQLQuery(query)
.addScalar("reference", StandardBasicTypes.STRING)
.addScalar("organisationalUnit", StandardBasicTypes.STRING)
.addScalar("supplierItemReference", StandardBasicTypes.STRING)
.addScalar("lineNumber", StandardBasicTypes.STRING)
.addScalar("averageUnitVolume", StandardBasicTypes.STRING)
.addScalar("averageUnitWeight", StandardBasicTypes.STRING)
.setParameter("orderReference", orderReference)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
return results;
}
}