ActualLineItemRepositoryImpl.java

package com.tradecloud.repository.export.impl;

import com.tradecloud.domain.base.utils.MathUtils;
import com.tradecloud.domain.document.invoice.*;
import com.tradecloud.domain.exception.InvalidEntityException;
import com.tradecloud.domain.item.LineItem;
import com.tradecloud.domain.shipment.Shipment;
import com.tradecloud.dto.invoice.InvoiceActualLineItemDTO;
import com.tradecloud.dto.order.ActualLineItemSearch;
import com.tradecloud.repository.base.impl.CriteriaOperation;
import com.tradecloud.repository.base.impl.CriteriaValue;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import com.tradecloud.repository.export.ActualLineItemRepository;
import org.apache.commons.collections4.CollectionUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Restrictions;
import org.hibernate.query.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 javax.persistence.criteria.*;
import java.math.BigInteger;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

@Repository(value = "actualLineItemRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class ActualLineItemRepositoryImpl extends RepositoryBaseImpl<ActualLineItem, ActualLineItemSearch> implements ActualLineItemRepository {

    private static final long serialVersionUID = 1L;
    private static final String REFERENCE = "code";
    private static final String DESCRIPTION = "description";
    private static final String TARIFF_HEADING = "tariffHeading";
    private static final String STYLE_REFERENCE = "styleReference";
    private static final String ORDER_REFERENCE = "reference";
    private static final String INVOICE_ID = "id";
    private static final String INVOICE_REFERENCE = "invoiceReference";
    private static final String SHIPMENT_ID = "costsInvoice.shipment.id";

    @Override
    public List<ActualLineItem> search(ActualLineItemSearch search) {

        CriteriaBuilder criteriaBuilder = getSession().getCriteriaBuilder();
        CriteriaQuery<ActualLineItem> criteriaQuery = criteriaBuilder.createQuery(ActualLineItem.class);
        Root<ActualLineItem> c = criteriaQuery.from(ActualLineItem.class);
        List<Predicate> predicateList = new ArrayList<>();

        if (search.getSearchMetaParams() != null && search.getSearchMetaParams().getOrderBy() != null) {
            criteriaQuery.orderBy(criteriaBuilder.asc(c.get(search.getSearchMetaParams().getOrderBy())));
        }
        criteriaQuery.orderBy(criteriaBuilder.asc(c.get("id")));

        if (search.getOrderReference() != null) {
            Join<ActualLineItem, ActualOrder> actualOrder = c.join("actualOrder", JoinType.INNER);
            predicateList.add(criteriaBuilder.like(actualOrder.get(ORDER_REFERENCE), search.getOrderReference()));
        }
        if (search.getStyleReference() != null) {
            predicateList.add(criteriaBuilder.like(c.get(STYLE_REFERENCE), search.getStyleReference()));
        }
        if (search.getTariffHeading() != null) {
            predicateList.add(criteriaBuilder.like(c.get(TARIFF_HEADING), search.getTariffHeading()));
        }
        if (search.getReference() != null) {
            predicateList.add(criteriaBuilder.like(c.get(REFERENCE), search.getReference()));
        }
        if (search.getInvoiceReference() != null) {
            predicateList.add(criteriaBuilder.like(c.get(INVOICE_REFERENCE), search.getInvoiceReference()));
        }
        Join<ActualLineItem, ActualOrder> order = c.join("actualOrder", JoinType.INNER);
        Join<ActualOrder, ActualConsignment> consignment = order.join("actualConsignment", JoinType.INNER);
        Join<ActualConsignment, CostsInvoice> costsInvoice = consignment.join("costsInvoice", JoinType.INNER);
        if (search.getInvoiceId() != null) {
            predicateList.add(criteriaBuilder.equal(costsInvoice.get(INVOICE_ID), Long.parseLong(search.getInvoiceId())));
        }
        if (search.getShipmentId() != null) {
            Join<CostsInvoice, Shipment> shipment = costsInvoice.join("shipment", JoinType.INNER);
            predicateList.add(criteriaBuilder.equal(shipment.get("id"), Long.parseLong(search.getShipmentId())));
        }
        return CollectionUtils.isNotEmpty(predicateList)
                ? getSession().createQuery(criteriaQuery.select(c).where(predicateList.toArray(new Predicate[]{}))).getResultList()
                : getSession().createQuery(criteriaQuery.select(c)).stream().distinct().collect(Collectors.toList());
    }

    @Override
    protected Collection<CriteriaValue> mapFieldsToValues(ActualLineItemSearch search) {
        Collection<CriteriaValue> fields = new LinkedHashSet<>();
        if (search.getReference() != null) {
            fields.add(new CriteriaValue(CriteriaOperation.EQUALS, REFERENCE, search.getReference()));
        }
        if (search.getDescription() != null) {
            fields.add(new CriteriaValue(CriteriaOperation.LIKE, DESCRIPTION, search.getDescription()));
        }
        if (search.getTariffHeading() != null) {
            fields.add(new CriteriaValue(CriteriaOperation.LIKE, TARIFF_HEADING, search.getTariffHeading()));
        }
        if (search.getStyleReference() != null) {
            fields.add(new CriteriaValue(CriteriaOperation.LIKE, STYLE_REFERENCE, search.getStyleReference()));
        }
        if (search.getOrderReference() != null) {
            fields.add(new CriteriaValue(CriteriaOperation.LIKE, ORDER_REFERENCE, search.getOrderReference()));
        }
        if (search.getInvoiceId() != null) {
            fields.add(new CriteriaValue(CriteriaOperation.EQUALS, INVOICE_ID, search.getInvoiceId()));
        }
        if (search.getShipmentId() != null) {
            fields.add(new CriteriaValue(CriteriaOperation.EQUALS, SHIPMENT_ID, search.getShipmentId()));
        }
        return fields;
    }

    public LineItem findCorrespondingLineItem(ActualLineItem actualLineItem) {
        Criteria criteria = getSession().createCriteria(LineItem.class);
        criteria.add(Restrictions.eq("id", actualLineItem.getOriginalId()));

        List<LineItem> list = criteria.list();

        return list.get(0);
    }

    @Override
    public List<InvoiceActualLineItemDTO> getActualLineItemsForOtherCIOnShipment(Long invoiceId, Long shipmentId) {
        String queryString = new StringBuilder("select ali.code as actualItemCode, actualorder.reference as orderReference, ")
                .append(" actualconsignment.reference as consignmentReference, ali.invoicequantity as invoiceQuantity,")
                .append(" ali.organisation as organisation , ali.originalId as originalId ")
                .append("FROM actuallineitem ali left join actualorder on ")
                .append("actualorder.id = ali.actualorder_id left join actualconsignment on ")
                .append("actualconsignment.id = actualorder.actualconsignment_id left join costsinvoice on ")
                .append("costsinvoice.id = actualconsignment.costsinvoice_id ")
                .append(" join commercialinvoice ci on (ci.id=costsinvoice.id) ")
                .append("WHERE costsinvoice.state != 'DELETED' AND costsinvoice.id != :invoiceid AND costsinvoice.shipment_id = :shipmentid")
                .toString();

        return (List<InvoiceActualLineItemDTO>) getCurrentSession().createSQLQuery(queryString)
                .addScalar("actualItemCode", StandardBasicTypes.STRING)
                .addScalar("orderReference", StandardBasicTypes.STRING)
                .addScalar("organisation", StandardBasicTypes.STRING)
                .addScalar("consignmentReference", StandardBasicTypes.STRING)
                .addScalar("invoiceQuantity", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("originalId", StandardBasicTypes.LONG)
                .setParameter("invoiceid", invoiceId)
                .setParameter("shipmentid", shipmentId)
                .setResultTransformer(Transformers.aliasToBean(InvoiceActualLineItemDTO.class)).list();
    }

    @Override
    public List<InvoiceActualLineItemDTO> getLineItemsForActualLineItemsTariffStatus(Long invoiceId) {
        String queryString = "select ali.code as actualItemCode, li.code as itemCode, li.state as state, ali.organisation as organisation " +
                ", ali.originalId as originalId " +
                "FROM " +
                "actuallineitem ali left join lineitem li on ali.originalid = li.id " +
                "left join actualorder on " +
                "actualorder.id = ali.actualorder_id left join actualconsignment on " +
                "actualconsignment.id = actualorder.actualconsignment_id left join costsinvoice on " +
                "costsinvoice.id = actualconsignment.costsinvoice_id " +
                "WHERE costsinvoice.state != 'DELETED' AND costsinvoice.id = :invoiceid";

        return (List<InvoiceActualLineItemDTO>) getCurrentSession().createSQLQuery(queryString)
                .addScalar("actualItemCode", StandardBasicTypes.STRING)
                .addScalar("itemCode", StandardBasicTypes.STRING)
                .addScalar("organisation", StandardBasicTypes.STRING)
                .addScalar("state", StandardBasicTypes.STRING)
                .addScalar("originalId", StandardBasicTypes.LONG)
                .setParameter("invoiceid", invoiceId)
                .setResultTransformer(Transformers.aliasToBean(InvoiceActualLineItemDTO.class)).list();
    }

    @Override
    public List<ActualLineItem> findAllActualShipItems(Long id) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select a from ActualLineItem a join a.actualOrder o join o.actualConsignment c join c.actualShipment s ")
                .append(" where s.id=:actualShipmentId");
        return getCurrentSession().createQuery(stringBuilder.toString()).setParameter("actualShipmentId", id).list();
    }

    @Override
    public List<InvoiceActualLineItemDTO> getActualLineItemsInvoiceOnShipment(Long invoiceId, Long shipmentId) {
        String queryString = new StringBuilder("select ali.code as actualItemCode, actualorder.reference as orderReference, ")
                .append(" actualconsignment.reference as consignmentReference, ali.invoicequantity as invoiceQuantity,")
                .append(" ali.organisation as organisation, ali.originalId as originalId ")
                .append("FROM actuallineitem ali left join actualorder on ")
                .append("actualorder.id = ali.actualorder_id left join actualconsignment on ")
                .append("actualconsignment.id = actualorder.actualconsignment_id left join costsinvoice on ")
                .append("costsinvoice.id = actualconsignment.costsinvoice_id ")
                .append(" join commercialinvoice ci on (ci.id=costsinvoice.id) ")
                .append("WHERE costsinvoice.state != 'DELETED' AND costsinvoice.id = :invoiceid AND costsinvoice.shipment_id = :shipmentid")
                .toString();

        List<InvoiceActualLineItemDTO> result = getCurrentSession().createSQLQuery(queryString)
                .addScalar("actualItemCode", StandardBasicTypes.STRING)
                .addScalar("orderReference", StandardBasicTypes.STRING)
                .addScalar("organisation", StandardBasicTypes.STRING)
                .addScalar("consignmentReference", StandardBasicTypes.STRING)
                .addScalar("invoiceQuantity", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("originalId", StandardBasicTypes.LONG)
                .setParameter("invoiceid", invoiceId)
                .setParameter("shipmentid", shipmentId)
                .setResultTransformer(Transformers.aliasToBean(InvoiceActualLineItemDTO.class)).list();
        return result;
    }

    @Override
    public List<Long> getBrotherLineItemIdsFromInvoice(Long invoiceid) {
        String queryString = "select ali.id " +
                "FROM actuallineitem ali " +
                "left join actualorder on " +
                "actualorder.id = ali.actualorder_id left join actualconsignment on " +
                "actualconsignment.id = actualorder.actualconsignment_id left join costsinvoice on " +
                "costsinvoice.id = actualconsignment.costsinvoice_id " +
                "WHERE costsinvoice.state != 'DELETED' AND costsinvoice.id = :invoiceid order by ali.id asc";
        SQLQuery sqlQuery = getSession().createSQLQuery(queryString);
        sqlQuery.setParameter("invoiceid", invoiceid);

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

        return ids;
    }

    @Override
    public Long getInvoiceIdForItem(long lineItemId) {
        String queryString = "select costsinvoice.id from costsinvoice " +
                "left join actualconsignment on costsinvoice.id = actualconsignment.costsinvoice_id " +
                "left join actualorder on actualconsignment.id = actualorder.actualconsignment_id " +
                "left join actuallineitem ali on actualorder.id = ali.actualorder_id where ali.id =:lineItemId";
        SQLQuery sqlQuery = getSession().createSQLQuery(queryString);
        sqlQuery.setParameter("lineItemId", lineItemId);
        Object result = sqlQuery.uniqueResult();
        if (result != null) {
            return ((BigInteger) result).longValue();
        }
        return null;
    }

    public void updateByAllocation(List<ActualLineItem> actualLineItemList, ActualLineItemAllocation actualLineItemAllocation, Long invoiceId) {
        switch (actualLineItemAllocation.getApplyType()) {
            case ITEMS_WITH_SAME_TARIFF_HEADING-> {
                Query qry = getSession().createQuery("select ali from ActualLineItem ali " +
                        "join ali.actualOrder o join o.actualConsignment c join c.costsInvoice coi " +
                        "where coi.id=:invoiceId " +
                        "and ali.tariffHeading in(:tariffHeading) " +
                        "or ali.id in(:id)");
                qry.setParameterList("tariffHeading", actualLineItemList.stream().map(ActualLineItem::getTariffHeading)
                        .collect(Collectors.toList()));
                qry.setParameterList("id", actualLineItemList.stream().map(ActualLineItem::getId).collect(Collectors.toList()));
                qry.setParameter("invoiceId", invoiceId);
                updateMatchingActualLineItems(qry, actualLineItemAllocation);
            }
            case ITEMS_WITH_SAME_STYLE_REFERENCE-> {
                Query qry = getSession().createQuery("select ali from ActualLineItem ali " +
                        "join ali.actualOrder o join o.actualConsignment c join c.costsInvoice coi " +
                        "where coi.id=:invoiceId " +
                        "and ali.styleReference in(:styleRef) or ali.id in(:id)");

                qry.setParameterList("styleRef", actualLineItemList.stream().map(ActualLineItem::getStyleReference).collect(Collectors.toList()));
                qry.setParameterList("id", actualLineItemList.stream().map(ActualLineItem::getId).collect(Collectors.toList()));
                qry.setParameter("invoiceId", invoiceId);
                updateMatchingActualLineItems(qry, actualLineItemAllocation);
            }
            case INCREMENT_ITEM_BOE_LINE_NUMBER -> {
                Integer boeNumber = actualLineItemAllocation.getBOENumber();

                if (boeNumber == null)
                    throw new InvalidEntityException("Bill of Entry Line Number is empty");

                AtomicInteger boeInc = new AtomicInteger(boeNumber);

                actualLineItemList.sort(Comparator.comparing(ActualLineItem::getCreated).thenComparingLong(ActualLineItem::getId));
                Collections.reverse(actualLineItemList);

                actualLineItemList.forEach(item -> {
                    setActualItem(item, actualLineItemAllocation, MathUtils.isNonZero(item.getQuantity()), boeInc.getAndIncrement());
                    merge(item);
                });
            }
            default -> throw new IllegalStateException("Unexpected value: " + actualLineItemAllocation.getApplyType());
        }
    }

    private void updateMatchingActualLineItems(Query qry, ActualLineItemAllocation actualLineItemAllocation) {
        List<ActualLineItem> matchingItems = (List<ActualLineItem>) qry.getResultList();
        matchingItems.forEach(item -> {
            setActualItem(item, actualLineItemAllocation, !MathUtils.isZero(item.getQuantity()), actualLineItemAllocation.getBOENumber());
            merge(item);
        });
    }

    private  void setActualItem(ActualLineItem item, ActualLineItemAllocation actualLineItemAllocation,
                                      boolean itemInvoiced, Integer boeNumber) {
        item.setMrnNumber(actualLineItemAllocation.getMRN() != null ? actualLineItemAllocation.getMRN() :
                item.getMrnNumber());
        item.setLrnNumber(actualLineItemAllocation.getLRN() != null ? actualLineItemAllocation.getLRN() :
                item.getLrnNumber());
        item.setCpcCode(actualLineItemAllocation.getCPC() != null ? actualLineItemAllocation.getCPC() :
                item.getCpcCode());
        item.setBillOfEntryDate(actualLineItemAllocation.getBOEDate() != null ? actualLineItemAllocation.getBOEDate() :
                item.getBillOfEntryDate());
        item.setBillOfEntryLineNumber(itemInvoiced ? (actualLineItemAllocation.getBOENumber() != null ?
                boeNumber :
                item.getBillOfEntryLineNumber()) : 0);
        item.setInvoiceLineNumber(item.getBillOfEntryLineNumber());
        if (actualLineItemAllocation.getStatisticalUnit() != null && item.getStatisticalUnit() != null) {
            item.getStatisticalUnit().setUnit(actualLineItemAllocation.getStatisticalUnit());
        }
        if(actualLineItemAllocation.getStatisticalUnitValue()!=null){
            item.getStatisticalUnit().setQuantity(actualLineItemAllocation.getStatisticalUnitValue());
        }
    }
}