LineItemRepositoryImpl.java

package com.tradecloud.repository.impl;

import com.tradecloud.domain.base.utils.DateUtils;
import com.tradecloud.domain.base.utils.MathUtils;
import com.tradecloud.domain.base.utils.ObjectUtil;
import com.tradecloud.domain.costing.clean.CostedLineItem;
import com.tradecloud.domain.document.invoice.ActualLineItem;
import com.tradecloud.domain.exception.EntityAccessException;
import com.tradecloud.domain.infrastructure.persistence.CriteriaBuilder;
import com.tradecloud.domain.item.LineItem;
import com.tradecloud.domain.item.LineItemState;
import com.tradecloud.domain.model.ordermanagement.Order;
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.shipment.Shipment;
import com.tradecloud.domain.supplier.OrganisationalUnitSupplier;
import com.tradecloud.dto.lineitem.LineItemSearch;
import com.tradecloud.repository.LineItemRepository;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
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.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Set;

@Repository(value = "lineItemRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class LineItemRepositoryImpl extends RepositoryBaseImpl<LineItem, LineItemSearch> implements LineItemRepository {

    private static final long serialVersionUID = 1L;
    private static final Logger log = Logger.getLogger(LineItemRepositoryImpl.class);

    private static final String CODE = "code";
    private static final String COUNTRY_OF_ORIGIN = "countryOfOrigin";
    private static final String SAMPLE = "SAMPLE";
    private static final String SPARE_PART = "SPARE PART";
    private static final String ORGANISATIONAL_UNIT = "organisationalUnit";
    private static final String SUPPLIER = "supplier";
    private static final String STATE = "state";
    private static final String ORDER = "order";

    @Override
    public LineItem findByReference(String reference) {
        throw new UnsupportedOperationException("The line item reference/code is not unique in the system so we cannot be guaranteed to return a "
                + "single result from the method");
    }

    @Override
    public List<LineItem> findSampleAndSpareItems(Order order) {
        @SuppressWarnings("unchecked")
        List<LineItem> list = (List<LineItem>) getNamedQuery("lineItem.findSampleAndSpareItems", new String[]{"order,sample,spare"},
                new Object[]{order, SAMPLE, SPARE_PART});
        return list;
    }

    /**
     * Rather use findCorrespondingActualLineItem(LineItem lineItem).
     *
     * @param reference
     * @return
     */
    @Deprecated
    @Override
    public ActualLineItem findActualLineItemByReference(String reference) {
        String[] references = new String[]{"code"};
        Object[] values = new Object[]{reference};
        List<ActualLineItem> list = (List<ActualLineItem>) findByNamedParam("actualLineItem.findByCode", references, values);
        if (list == null || list.isEmpty()) {
            return null;
        }
        return list.get(0);
    }

    /**
     * An actual line item object will be returned for the existing shipment (if any) and one for each invoice attached
     * to the shipment (if any).
     * NOT TESTED
     *
     * @param lineItem
     * @return
     */
    @Override
    public List<ActualLineItem> findCorrespondingActualLineItem(LineItem lineItem) {
        String hql = "FROM ActualLineItem WHERE originalId = :originalId";
        Query query = getSession().createQuery(hql);
        query.setParameter("originalId", lineItem.getId());

        return query.list();
    }

    @Override
    public LineItem findOriginalByCodeAndOrderReference(String code, String orderReference) {
        return (LineItem) ObjectUtil.first(findByNamedQueryAndNamedParam("lineItem.findOriginalByCodeAndOrderReference",
                new String[]{"code", "orderReference"}, new Object[]{code, orderReference}));
    }

    @Override
    public LineItem findCopyByItemCodeAndOrderReference(String code, String orderReference) {
        return (LineItem) ObjectUtil.first(findByNamedQueryAndNamedParam("lineItem.findCopyByCodeAndOrderReference",
                new String[]{"code", "orderReference"}, new Object[]{code, orderReference}));
    }

    @Override
    public Long countItemsByCodeAndOrderReference(String code, String orderReference) {
        Query query = getCurrentSession().getNamedQuery("lineItem.countFindCopyByCodeAndOrderReference");
        query.setParameter("code", code);
        query.setParameter("orderReference", orderReference);
        return (Long) query.uniqueResult();
    }

    @Override
    public boolean isLineItemUnique(String itemReference, String supplierReference, String countryOfOriginCode, String orgUnitCode, Long orderId) {
        String queryString = "select count(i.id) from LineItem i where i.code =:itemReference and "
                + "i.supplierReference =:supplierReference and i.countryOfOrigin.code =:countryOfOriginCode and "
                + "i.organisationalUnit.code =:orgUnitCode and i.order.id =:orderId";
        Query query = getSessionCustom().createQuery(queryString);
        query.setParameter("itemReference", itemReference);
        query.setParameter("supplierReference", supplierReference);
        query.setParameter("countryOfOriginCode", countryOfOriginCode);
        query.setParameter("orgUnitCode", orgUnitCode);
        query.setParameter("orderId", orderId);
        Long result = (Long) query.uniqueResult();
        return result == 0;
    }

    @Override
    public List<LineItem> search(LineItemSearch search) {

        Criteria searchCriteria = getSessionCustom().createCriteria(LineItem.class);
        boolean validSearch = false;

        if (search.getCode() != null) {
            validSearch = true;
            searchCriteria.add(Restrictions.like(CODE, search.getCode()));
        }

        // Make sure we have a valid parent order reference (number and orderReference are mandatory)
        if (search.getParentOrder() != null && search.getParentOrder().getNumber() != null && search.getParentOrder().getOrderReference() != null) {
            validSearch = true;
            searchCriteria.add(Restrictions.eq("order", search.getParentOrder()));
        }
        if (search.getParentOrder() == null && search.getOrderReference() != null) {
            validSearch = true;
            searchCriteria.add(Restrictions.eq("order", search.getOrderReference()));
        }
        searchCriteria.add(Restrictions.eq("lineNumber", search.getLineNumber()));
        if (validSearch) {
            return searchCriteria.list();
        } else {
            log.error("Not a valid search, no search criteria specified.");
            return new ArrayList<LineItem>();
        }

    }

    @Override
    public List<LineItem> findByOrder(Order order) {
        @SuppressWarnings("unchecked")
        List<LineItem> list = (List<LineItem>) getNamedQueryAndNamedParam("lineItem.findItemsByOrder", "order", order);
        return list;
    }

    @Override
    @Transactional(readOnly = true)
    public LineItem findByOrderAndReference(Order order, String reference, OrganisationalUnit organisationalUnit) {
        List<LineItem> list = null;
        if (organisationalUnit == null) {
            List<LineItem> lineItems = order.getActiveLineItems().stream().filter(i -> i.getCode().equals(reference)).toList();

            if (lineItems.size() == 1) {
                return lineItems.get(0);
            }
            if (lineItems.size() > 1) {
                throw new RuntimeException(String.format("Found more  than one item with reference: %s", reference));
            }
        } else {
            list = (List<LineItem>) getNamedQueryAndNamedParam("lineItem.findItemByOrderIdAndCodeAndOrg",
                    new String[]{"order", "code", "organisationalUnit"}, new Object[]{order, reference, organisationalUnit});
        }

        if (list.size() == 1) {
            return list.get(0);
        }

        if (list.size() > 1) {
            throw new EntityAccessException("More than one item:" + reference + " found for order: " + order.getOrderReference() +
                    (organisationalUnit != null ? " organisationalUnit: " + organisationalUnit.getName() : ""));
        }

        return null;
    }

    @Override
    @Transactional(readOnly = true)
    public LineItem findByReferenceCode(String reference) {
        List<LineItem> list = (List<LineItem>) getNamedQueryAndNamedParam("lineItem.findItemByCode", "code", reference);
        if (!list.isEmpty()) {
            return list.get(0);
        }
        return null;
    }

    @Override
    @Transactional(readOnly = true)
    public LineItem findById(Long id) {
        List<LineItem> list = (List<LineItem>) getNamedQueryAndNamedParam("lineItem.findItemById", "id", id);
        if (!list.isEmpty()) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public List<LineItem> untariffedSearch(LineItemSearch search) {
        String queryBuilder = getUntariffedQuery(search, false);
        Query query = getLineItems(queryBuilder, search, false);
        return query.list();
    }

    private List<LineItem> getPurchaseOrderCriteriaList(Criteria criteria, SearchMetaParams searchMetaParams) {
        if (searchMetaParams != null && searchMetaParams.getOrderBy() != null) {
            criteria.addOrder(CriteriaBuilder.createOrdering(searchMetaParams.getOrderBy(), searchMetaParams.isAsc()));
        }

        if (searchMetaParams != null) {
            criteria.setFirstResult(searchMetaParams.getRowIndex());
            criteria.setMaxResults(searchMetaParams.getRowCount());
        }

        @SuppressWarnings("unchecked")
        List<PurchaseOrder> results = criteria.list();
        List<LineItem> lineItems = new ArrayList<>();
        for (PurchaseOrder purchaseOrder : results) {
            lineItems.addAll(purchaseOrder.getLineItems());
        }

        return lineItems;
    }

    @Override
    public long untariffedCount(LineItemSearch search) {
        String queryBuilder = getUntariffedQuery(search, true);
        Query query = getLineItems(queryBuilder, search, true);
        return (Long) query.uniqueResult();
    }

    private Query getLineItems(String queryBuilder, LineItemSearch search, boolean isCount) {
        Query query = getSession().createQuery(queryBuilder.toString());

        query.setParameterList("states", Arrays.asList(LineItemState.UNTARIFFED, LineItemState.UNVERIFIED));
        query.setParameterList("orderState", Arrays.asList(OrderState.DELETED, OrderState.ARCHIVED));
        if (search.isFilteredByUserOrg()) {
            Set<OrganisationalUnit> userOrganisationalUnits = getUserOrganisationalUnits();
            query.setParameterList("organisationalUnits", userOrganisationalUnits);
        } else if (null != search.getOrderOrganisationalUnit()) {
            query.setParameter("organisationalUnit", search.getOrderOrganisationalUnit());
        } else if (CollectionUtils.isNotEmpty(search.getOrganisationalUnits())) {
            query.setParameterList("organisationalUnits", search.getOrganisationalUnits());
        }
        if (null != search.getPurchaseOrderSupplier()) {
            query.setParameter("supplier", search.getPurchaseOrderSupplier());
        }
        if (null != search.getPurchaseOrderBuyer()) {
            query.setParameter("buyer", search.getPurchaseOrderBuyer());
        }
        if (null != search.getStyleReference() && !search.getStyleReference().trim().isEmpty()) {
            query.setParameter("styleReference", search.getStyleReference());
        }
        if (null != search.getStyleDescription() && !search.getStyleDescription().trim().isEmpty()) {
            query.setParameter("styleDescription", search.getStyleDescription());
        }
        if (null != search.getClearingAgent()) {
            query.setParameter("clearingAgent", search.getClearingAgent());
        }
        if (StringUtils.isNotEmpty(search.getOrderReference())) {
            query.setParameter("orderReference", search.getOrderReference());
        }

        if (StringUtils.isNotEmpty(search.getProFormaReference())) {
            query.setParameter("proFormaReference", search.getProFormaReference());
        }

        if (search.getLatestShipmentDateRange() != null) {
            if (search.getLatestShipmentDateRange().getFrom() != null) {
                query.setParameter("latestShipDateFrom", search.getLatestShipmentDateRange().getFrom());
            }
            if (search.getLatestShipmentDateRange().getTo() != null) {
                query.setParameter("latestShipDateTo", search.getLatestShipmentDateRange().getTo());
            }
        }

        if (null != search.getSearchMetaParams() && !isCount) {
            query.setFirstResult(search.getSearchMetaParams().getRowIndex());
            query.setMaxResults(search.getSearchMetaParams().getRowCount());
        }
        return query;
    }

    private String getUntariffedQuery(LineItemSearch search, boolean isCount) {
        StringBuilder queryBuilder = null;
        if (isCount) {
            queryBuilder = new StringBuilder("Select count(li.id)");
        } else {
            queryBuilder = new StringBuilder("Select li");
        }
        queryBuilder.append(" from LineItem li, PurchaseOrder po where "
                + " li.order.id=po.id and li.order != null and li.order.orderDates.requiredOnSiteDate != null "
                + " and li.state IN (:states) and li.order.state NOT IN (:orderState) "
                + " and li.elc = 'f'");

        if (search.isFilteredByUserOrg()) {
            queryBuilder.append(" and po.organisationalUnit in (:organisationalUnits)");
        } else if (null != search.getOrderOrganisationalUnit()) {
            queryBuilder.append(" and po.organisationalUnit =:organisationalUnit");
        } else if (CollectionUtils.isNotEmpty(search.getOrganisationalUnits())) {
            queryBuilder.append(" and po.organisationalUnit in (:organisationalUnits)");
        }
        if (null != search.getPurchaseOrderSupplier()) {
            queryBuilder.append(" and po.supplier =:supplier");
        }
        if (null != search.getPurchaseOrderBuyer()) {
            queryBuilder.append(" and po.buyer =:buyer");
        }
        if (null != search.getStyleReference() && !search.getStyleReference().trim().isEmpty()) {
            queryBuilder.append(" and li.styleReference =:styleReference");
        }
        if (null != search.getStyleDescription() && !search.getStyleDescription().trim().isEmpty()) {
            queryBuilder.append(" and li.styleDescription =:styleDescription ");
        }

        if (null != search.getClearingAgent()) {
            queryBuilder.append(" and po.shippingInformation.clearingAgent =:clearingAgent");
        }
        if (StringUtils.isNotEmpty(search.getOrderReference())) {
            queryBuilder.append(" and lower(po.orderReference) like lower(:orderReference)");
        }

        if (StringUtils.isNotEmpty(search.getProFormaReference())) {
            queryBuilder.append(" and lower(po.proFormaReference) like lower(:proFormaReference)");
        }

        if (search.getLatestShipmentDateRange() != null) {
            if (search.getLatestShipmentDateRange().getFrom() != null) {
                queryBuilder.append(" and po.orderDates.latestShipmentDate >= :latestShipDateFrom");
            }
            if (search.getLatestShipmentDateRange().getTo() != null) {
                queryBuilder.append(" and po.orderDates.latestShipmentDate <= :latestShipDateTo");
            }
        }
        if (search.getAwaitingCostingDateEnd() != null) {
//            queryBuilder.append(String.format(" and ( select max (e.createDateTime) as d from Order o join o.events as e " +
//                            "  where   o.id=po.id )<='%s' ",
//                    DateUtils.toT_YMDFormat(search.getAwaitingCostingDateEnd())));

            queryBuilder.append(String.format(" and ( select max (e.createDateTime) from Order o join o.events as e " +
                            "  where  o.id=po.id )<='%s' ",
                    DateUtils.toT_YMDFormat(search.getAwaitingCostingDateEnd())));
        }

        if (!isCount) {
            queryBuilder.append(" order by li.order.orderDates.latestShipmentDate ");
        }

        return queryBuilder.toString();
    }

    @Override
    public List<Object[]> findBasicFieldsByOrderId(long orderId) {
        Criteria criteria = getCurrentSession().createCriteria(LineItem.class);
        criteria.createAlias("order", "o");
        criteria.add(Restrictions.eq("o.id", orderId));
        criteria.setProjection(Projections.projectionList()
                .add(Projections.property("code")) //0
                .add(Projections.property("description")) //1
                .add(Projections.property("packageQuantity")) //2
                .add(Projections.property("supplierReference")) //3
                .add(Projections.property("unitQuantity")) //4
                .add(Projections.property("unitsPerPackage")) //5
                .add(Projections.property("unitVolume")) //6
                .add(Projections.property("unitWeight")) //7
                .add(Projections.property("o.orderReference")) //8
                .add(Projections.property("weightUOM")) //9
                .add(Projections.property("volumeUOM")) //10
                .add(Projections.property("additional")) //11
                .add(Projections.property("unitPrice")) //12
                .add(Projections.property("currency")) //13
                .add(Projections.property("addedToOrderDate")) //14
                .add(Projections.property("organisationalUnit")) //15
                .add(Projections.property("lineNumber"))); //16

        criteria.addOrder(Property.forName("addedToOrderDate").asc());

        @SuppressWarnings("unchecked")
        List<Object[]> rows = criteria.list();
        return rows;
    }

    @Override
    public List<LineItem> findAdditionalItemsOnShipment(Shipment shipment) {
        return (List<LineItem>) getNamedQueryAndNamedParam("lineItem.findAdditionalItemsByOrder", "shipmentId", shipment.getId());
    }

    @Override
    @Transactional(readOnly = true)
    public List<LineItem> findLineItemBySupplierReferenceAndReference(Order order, String supplierReference, String reference) {
        @SuppressWarnings("unchecked")
        List<LineItem> list = (List<LineItem>) getNamedQuery("lineItem.findLineItemBySupplierReferenceAndReference",
                new String[]{"order, supplierReference, code"}, new Object[]{order, supplierReference, reference});
        return list;

    }

    public void addUntariffedSearchRestrictions(DetachedCriteria criteria, LineItemSearch search) {
        criteria.add(Restrictions.isNotNull("order"));
        // The state criteria now includes SIGNED_OFF to cater for line items added at actual level (that haven't been tariffed yet).
        // See https://connect.devstream.net/display/Dev/Add+new+items+at+shipment+level.
        // criteria.add(Restrictions.in("o.state", new OrderState[] { OrderState.AWAITING_TARIFFING, OrderState.SIGNED_OFF,
        // OrderState.UNFINALISED }));
        criteria.add(Restrictions.not(Restrictions.in("o.state", new OrderState[]{OrderState.DELETED, OrderState.ARCHIVED})));
        CriteriaBuilder.addEqRestriction(criteria, "o.organisationalUnit", search.getOrderOrganisationalUnit());
        CriteriaBuilder.addEqRestriction(criteria, "o.supplier", search.getPurchaseOrderSupplier());
        CriteriaBuilder.addEqRestriction(criteria, "o.buyer", search.getPurchaseOrderBuyer());
        if (null != search.getStyleReference() && !search.getStyleReference().trim().isEmpty()) {
            criteria.add(Restrictions.ilike("styleReference", search.getStyleReference().trim(), MatchMode.ANYWHERE));
        }
        if (null != search.getStyleDescription() && !search.getStyleDescription().trim().isEmpty()) {
            criteria.add(Restrictions.ilike("styleDescription", search.getStyleDescription().trim(), MatchMode.ANYWHERE));
        }
    }

    @Override
    @Transactional(readOnly = true)
    public List<LineItem> updateDutiesSearch(LineItemSearch search) {
        // Obtain the CriteriaBuilder instance
        javax.persistence.criteria.CriteriaBuilder cb = getSessionCustom().getCriteriaBuilder();
        // Create the CriteriaQuery for LineItem
        CriteriaQuery<LineItem> cq = cb.createQuery(LineItem.class);
        Root<LineItem> root = cq.from(LineItem.class);

        // Join with the "order" entity
        Join<LineItem, Order> order = root.join("order");

        // Predicate list for dynamic query conditions
        List<Predicate> predicates = new ArrayList<>();

        // Add restrictions
        predicates.add(cb.equal(root.get(CODE), search.getCode()));
        predicates.add(cb.equal(root.get(COUNTRY_OF_ORIGIN), search.getCountryOfOrigin()));
        predicates.add(cb.equal(root.get(ORGANISATIONAL_UNIT), search.getOrderOrganisationalUnit()));

        if (search.getPurchaseOrderSupplier() != null) {
            predicates.add(cb.equal(root.get(SUPPLIER), search.getPurchaseOrderSupplier()));
        }

        predicates.add(cb.isNotNull(root.get("order")));

        // Restrict to specific order states
        predicates.add(order.get("state").in(OrderState.UNFINALISED, OrderState.AWAITING_COSTING, OrderState.AWAITING_TARIFFING));

        // Restrict to specific line item states
        predicates.add(root.get("state").in(LineItemState.UNTARIFFED, LineItemState.UNVERIFIED, LineItemState.TARIFFED));

        // Apply the predicates to the CriteriaQuery
        cq.where(predicates.toArray(new Predicate[0]));

        // Execute the query and get the results
        return getSessionCustom().createQuery(cq).getResultList();
    }

    @Override
    public CostedLineItem findCostedLineItem(LineItem lineItem, String summaryReference) {
        StringBuilder stringBuilder = new StringBuilder("from CostedLineItem where lineItem.id = :id");
        boolean hasSummary = StringUtils.isNotEmpty(summaryReference);
        if (hasSummary) {
            stringBuilder.append(" and summaryReference=:summaryReference ");
        }
        stringBuilder.append(" order by updated desc");
        javax.persistence.Query query = getSession().createQuery(stringBuilder.toString());
        query.setParameter("id", lineItem.getId());
        if (hasSummary) {
            query.setParameter("summaryReference", summaryReference);
        }
        query.setMaxResults(1);
        List results = query.getResultList();
        if (results == null || results.isEmpty())
            return null;
        return (CostedLineItem) results.get(0);
    }

    @Override
    public List<CostedLineItem> findAllCostedLineItem(LineItem lineItem) {
        Query query = getSession().createQuery("from CostedLineItem where lineItem.id = :id");
        query.setParameter("id", lineItem.getId());
        return query.list();
    }

    @Override
    public LineItem findNonCopiedLineItem(String orderReference, String itemReference, String supplierReference) {
        List<LineItem> lineItems = (List<LineItem>)
                findByNamedQueryAndNamedParam("lineItem.findNonCopiedByCodeAndSupplierReferenceAndOrderReference",
                        new String[]{"code", "supplierReference", "orderReference"},
                        new Object[]{itemReference, supplierReference, orderReference});
        return ObjectUtil.first(lineItems);
    }

    @Override
    public List<Long> getBrotherLineItemIds(Long lineItemId) {
        SQLQuery sqlQuery = getSession().createSQLQuery("select li.id from orders o " +
                "join lineitem li on li.order_id = o.id where o.id in " +
                "(select o.id from orders o join lineitem i on i.order_id = o.id where i.id = " +
                lineItemId + ") order by id asc");

        List<BigInteger> list = sqlQuery.list();
        List<Long> ids = new ArrayList<>();
        for (BigInteger integer : list) {
            ids.add((Long.valueOf(integer.longValue())));
        }

        return ids;
    }

    @Override
    public OrganisationalUnitSupplier findOrganisationalUnitSupplier(LineItem lineItem) {
        String queryString = "SELECT supplier FROM LineItem li WHERE li.id = :id";
        Query query = getSessionCustom().createQuery(queryString);
        query.setParameter("id", lineItem.getId());
        return (OrganisationalUnitSupplier) query.uniqueResult();
    }

    @Override
    public BigDecimal sumLineItemsWeightForOrder(Long purchaseOrderId) {
        String queryString = "select sum(i.unitWeight * i.unitQuantity) from LineItem i where i.order.id =:purchaseOrderId";
        Query query = getSessionCustom().createQuery(queryString);
        query.setParameter("purchaseOrderId", purchaseOrderId);
        BigDecimal result = (BigDecimal) query.uniqueResult();

        if (result != null) {
            return (result).setScale(2, MathUtils.ROUNDING_MODE);
        }
        return BigDecimal.ZERO;
    }

    @Override
    public BigDecimal sumLineItemVolumeForOrder(Long purchaseOrderId) {
        String queryString = "select sum(i.unitVolume * i.unitQuantity) from LineItem i where i.order.id =:purchaseOrderId";
        Query query = getSessionCustom().createQuery(queryString);
        query.setParameter("purchaseOrderId", purchaseOrderId);
        BigDecimal result = (BigDecimal) query.uniqueResult();

        if (result != null) {
            return (result).setScale(2, MathUtils.ROUNDING_MODE);
        }
        return BigDecimal.ZERO;
    }

    @Override
    public BigDecimal sumLineItemQuantityForOrder(Long purchaseOrderId) {
        Object uniqueResult = getCurrentSession().createCriteria(LineItem.class, "li").createAlias("order", "o")
                .add(Restrictions.eq("o.id", purchaseOrderId))
                .setProjection(Projections.projectionList().add(Projections.sum("li.unitQuantity"))).uniqueResult();

        if (uniqueResult == null) {
            return BigDecimal.ZERO.setScale(4);
        }
        return ((BigDecimal) uniqueResult).setScale(4, MathUtils.ROUNDING_MODE);
    }

    @Override
    public BigDecimal sumLineItemPriceForOrder(Long purchaseOrderId) {
        Object uniqueResult = getCurrentSession().createCriteria(LineItem.class, "li").createAlias("order", "o")
                .add(Restrictions.eq("o.id", purchaseOrderId))
                .setProjection(Projections.projectionList().add(Projections.sum("li.unitPrice"))).uniqueResult();

        if (uniqueResult == null) {
            return BigDecimal.ZERO.setScale(4);
        }
        return ((BigDecimal) uniqueResult).setScale(4, MathUtils.ROUNDING_MODE);
    }

    @Override
    public List<String> findAllCopies(List<Long> collect) {
        return getSessionCustom().createNativeQuery("select i.code from lineitem i join orders o on (o.id=i.order_id) " +
                "where i.copy and o.consignment_id in (:ids)").setParameter("ids", collect).list();
    }

    @Override
    public List<LineItem> findAllForShipment(Long id) {
        String stringBuilder = "select a from LineItem a join a.order o join o.consignment c " +
                " where c.shipment.id=:shipmentId";
        return getCurrentSession().createQuery(stringBuilder).setParameter("shipmentId", id).list();
    }

    @Override
    public LineItem retrieveWithOrderLoaded(Long id) {
        return (LineItem) getSessionCustom().createQuery("SELECT li FROM LineItem li JOIN FETCH li.order o JOIN FETCH o.lineItems " +
                        "t WHERE li.id = :id and t is not null")
                .setParameter("id", id).uniqueResult();
    }
}