CommercialInvoiceRepositoryImpl.java

package com.tradecloud.repository.impl;

import com.tradecloud.domain.document.Document;
import com.tradecloud.domain.document.DocumentState;
import com.tradecloud.domain.document.PaymentState;
import com.tradecloud.domain.document.invoice.ActualConsignment;
import com.tradecloud.domain.document.invoice.ActualOrder;
import com.tradecloud.domain.document.invoice.CommercialInvoice;
import com.tradecloud.domain.document.invoice.ServiceProviderInvoice;
import com.tradecloud.domain.model.ordermanagement.Consignment;
import com.tradecloud.domain.shipment.Shipment;
import com.tradecloud.domain.supplier.OrganisationalUnitSupplier;
import com.tradecloud.dto.base.ReferenceValueDto;
import com.tradecloud.dto.invoice.SupplyPaymentSearch;
import com.tradecloud.repository.CommercialInvoiceRepository;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
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.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * Default implementation of the {@literal CommercialInvoiceRepository} interface.
 */
@Repository(value = "commercialInvoiceRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class CommercialInvoiceRepositoryImpl extends CostsInvoiceRepositoryImpl<CommercialInvoice> implements CommercialInvoiceRepository {

    private static final long serialVersionUID = 1L;

    @Override
    public List<CommercialInvoice> findAllByReference(String reference) {
        return findAllByReference("commercialInvoice.byReference", reference);
    }

    @Override
    public List<CommercialInvoice> findByReferenceAndSupplier(String reference, OrganisationalUnitSupplier supplier) {
        return (List<CommercialInvoice>) findByNamedQueryAndNamedParam("commercialInvoice.byReferenceAndSupplier",
                new String[]{"reference", "supplier"}, new Object[]{reference, supplier});
    }

    @Override
    public CommercialInvoice findByIdWithPlannedSettlements(long id) {
        List<CommercialInvoice> commercialInvoices = (List<CommercialInvoice>)
                findByNamedQueryAndNamedParam("commercialInvoice.byIdWithPlannedSettlementsLoaded", "id", id);
        if (!commercialInvoices.isEmpty()) {
            return commercialInvoices.get(0);
        }
        return null;
    }

    @Override
    public List<CommercialInvoice> findByServiceProviderInvoice(ServiceProviderInvoice serviceProviderInvoice) {
        return (List<CommercialInvoice>) findByNamedQueryAndNamedParam("commercialInvoice.byShipment", "id",
                serviceProviderInvoice.getShipment().getId());
    }

    // OLD
//    @Override
    public List<ReferenceValueDto> getLindfeItemInvoiceQuantityTotals(Shipment shipment) {
        @SuppressWarnings("unchecked")
        List<ReferenceValueDto> results = getCurrentSession().createSQLQuery("""
                        select alo.reference as parentReference,
                        ali.code as reference,
                        COALESCE(ali.supplierItemReference,'') as supplierItemReference,
                        ali.originalItemId as originalItemId,
                        COALESCE(ali.organisationalunit,'') as organisationalunit,
                        sum(ali.invoicequantity) as value
                        from actuallineitem ali, lineitem li, actualorder alo, actualconsignment ac, costsinvoice inv, commercialinvoice ci
                        where ali.actualorder_id = alo.id
                        and ali.originalid = li.id
                        and alo.actualconsignment_id = ac.id and ac.costsinvoice_id = inv.id and ci.id = inv.id
                        and inv.shipment_id = :shipmentId and inv.state != 'DELETED'
                        group by alo.reference, ali.code, ali.originalid, ali.organisation, ali.supplieritemreference, ali.originalitemid
                        order by alo.reference, ali.code
                        """)
                .addScalar("reference", StandardBasicTypes.STRING)
                .addScalar("supplierItemReference", StandardBasicTypes.STRING)
                .addScalar("originalItemId", StandardBasicTypes.STRING)
                .addScalar("organisationalUnit", StandardBasicTypes.STRING)
                .addScalar("value", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("parentReference", StandardBasicTypes.STRING)
                .setParameter("shipmentId", shipment.getId()).setResultTransformer(Transformers.aliasToBean(ReferenceValueDto.class)).list();
        return results;
    }

    @Override
    public List<ReferenceValueDto> getLineItemInvoiceQuantityTotals(Shipment shipment) {
        @SuppressWarnings("unchecked")
        List<ReferenceValueDto> results = getCurrentSession().createSQLQuery("""
                        select alo.reference as parentReference,
                        ali.code as reference,
                        COALESCE(ali.supplierreference,'') as supplierItemReference,
                        ali.originalid as originalItemId,
                        COALESCE(ali.organisation,'') as organisationalunit,
                        sum(ali.invoicequantity) as value
                        from actuallineitem ali, lineitem li, actualorder alo, actualconsignment ac, costsinvoice inv, commercialinvoice ci 
                        where ali.actualorder_id = alo.id 
                        and ali.originalid = li.id 
                        and alo.actualconsignment_id = ac.id and ac.costsinvoice_id = inv.id and ci.id = inv.id 
                        and inv.shipment_id = :shipmentId and inv.state != 'DELETED' 
                        group by alo.reference, ali.code, ali.originalid, ali.organisation, ali.supplierreference 
                        order by alo.reference, ali.code
                        """)
                .addScalar("reference", StandardBasicTypes.STRING)
                .addScalar("supplierItemReference", StandardBasicTypes.STRING)
                .addScalar("originalItemId", StandardBasicTypes.STRING)
                .addScalar("organisationalUnit", StandardBasicTypes.STRING)
                .addScalar("value", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("parentReference", StandardBasicTypes.STRING)
                .setParameter("shipmentId", shipment.getId()).setResultTransformer(Transformers.aliasToBean(ReferenceValueDto.class)).list();
        return results;
    }

    @Override
    public BigDecimal getTotalAmountPaidOnCommercialInvoice(CommercialInvoice invoice) {
        Object uniqueResult = getCurrentSession().createCriteria(CommercialInvoice.class, "ci").createAlias("plannedSettlements", "ps")
                .createAlias("ps.payments", "payment").add(Restrictions.eq("ci.id", invoice.getId()))
                .setProjection(Projections.projectionList().add(Projections.sum("payment.amount"))).uniqueResult();

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

    @Override
    public boolean readyForPayment(long commercialInvoiceId) {
        String payStates = CommercialInvoice.PAYABLE_STATES.stream().map(documentState -> "'" + documentState.name() + "'")
                .collect(Collectors.joining(","));
        int count = ((Long) getCurrentSession().createQuery(
                        "select count(*) from CommercialInvoice where "
                                + "state in (" + payStates + ")"
                                + "and id = :commercialInvoiceId")
                .setParameter("commercialInvoiceId", commercialInvoiceId).iterate().next()).intValue();
        return count == 1;
    }

    // Pass the shipment separately because it might not have been initialised from the UI
    @Override
    public BigDecimal getConsignmentInvoicedValue(Shipment shipment, Consignment consignment) {
        String query = "select COALESCE(sum(actualconsignment.totalvalue),0) from actualconsignment \n" +
                " left join costsinvoice on actualconsignment.costsinvoice_id = costsinvoice.id\n" +
                " right outer join commercialinvoice on commercialinvoice.id = costsinvoice.id\n" +
                " where costsinvoice.state <> 'DELETED'\n" +
                " and costsinvoice.shipment_id = :shipmentId " +
                " and actualconsignment.reference = :consignmentReference";

        return ((BigDecimal) getCurrentSession().createSQLQuery(query)
                .setParameter("shipmentId", shipment.getId())
                .setParameter("consignmentReference", consignment.getReference()).uniqueResult());
    }

    @Override
    public List<CommercialInvoice> findByShipmentId(Long shipmentId) {
        return (List<CommercialInvoice>) findByNamedQueryAndNamedParam("commercialInvoice.byShipment", "id", shipmentId);
    }

    @Override
    public List<CommercialInvoice> findAllByState(DocumentState[] state) {
        String hql = "from CommercialInvoice ci where ci.state in (:states)";
        Query query = getSession().createQuery(hql);
        query.setParameterList("states", state);
        return query.list();
    }

    @Override
    public List<ActualOrder> findActualOrdersByInvoice(CommercialInvoice invoice) {
        List<ActualOrder> actualOrders = new ArrayList<>();
        // This is required depending on where this method is called from
        Hibernate.initialize(invoice.getActualConsignments());
        for (ActualConsignment ac : invoice.getActualConsignments()) {
            actualOrders.addAll(ac.getActualOrderList());
        }
        return actualOrders;
    }

    @Override
    public long supplyPaymentReportCount(SupplyPaymentSearch search) {
        Criteria criteria = this.createPaymentReportCriteria(search);
        criteria.setProjection(Projections.rowCount());
        return ((Long) criteria.uniqueResult());
    }

    //---------------------------------------------------

    @Override
    public List<Document> supplyPaymentReportSearch(SupplyPaymentSearch search) {
        Criteria criteria = this.createPaymentReportCriteria(search);

        criteria.setProjection(Projections.distinct(Projections.property("id")));

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

        if (list.isEmpty()) {
            return new ArrayList<>();
        }

        Criteria entitySearch = getSessionCustom().createCriteria(CommercialInvoice.class);
        entitySearch.add(Restrictions.in("id", list));
        entitySearch.addOrder(Order.asc("estimatedSettlementDate"));

        return entitySearch.list();
    }

    private Criteria createPaymentReportCriteria(SupplyPaymentSearch search) {
        Criteria criteria = getSessionCustom().createCriteria(CommercialInvoice.class);
        criteria.createAlias("shipment", "s");
        criteria.createAlias("plannedSettlements", "ps");
        criteria.createAlias("s.consignments", "c");
        criteria.createAlias("c.orders", "o");
        criteria.createAlias("o.shippingInformation", "si");

        if (search.getShipmentReference() != null) {
            criteria.add(Restrictions.ilike("s.reference", "%" + search.getShipmentReference() + "%", MatchMode.ANYWHERE));
        }

        if (search.getOrderReference() != null) {
            criteria.add(Restrictions.ilike("o.orderReference", "%" + search.getOrderReference() + "%", MatchMode.ANYWHERE));
        }

        if (search.getShippingReference() != null) {
            criteria.add(Restrictions.ilike("si.shippingReference", "%" + search.getShippingReference() + "%", MatchMode.ANYWHERE));
        }

        if (search.getOrganisationalUnit() != null) {
            criteria.add(Restrictions.eq("organisationalUnit", search.getOrganisationalUnit()));
        }

        if (search.getDocumentReference() != null) {
            criteria.add(Restrictions.ilike("reference", "%" + search.getDocumentReference() + "%", MatchMode.ANYWHERE));
        }

        if (search.getDocumentCurrency() != null) {
            criteria.add(Restrictions.eq("currency", search.getDocumentCurrency()));
        }

        if (search.getSettlementDateFrom() != null && search.getSettlementDateTo() != null) {
            criteria.add(Restrictions.between("estimatedSettlementDate", search.getSettlementDateFrom(), search.getSettlementDateTo()));
        }

        if (search.getPaymentState() != null) {
            criteria.add(Restrictions.eq("ps.paymentState", search.getPaymentState()));
        } else {
            criteria.add(Restrictions.in("ps.paymentState",
                    new PaymentState[]{PaymentState.SETTLED, PaymentState.UNSETTLED}));
        }

        if (search.getDocumentState() != null) {
            criteria.add(Restrictions.eq("state", search.getDocumentState()));
        } else {
            criteria.add(Restrictions.in("state", new DocumentState[]{DocumentState.ACCEPTED_BY_ERP,
                    DocumentState.AWAITING_TREASURY_RATES,
                    DocumentState.COMPLETE, DocumentState.DELETED, DocumentState.DOCUMENT_TOLERANCE_EXCEEDED,
                    DocumentState.FINALISED,
                    DocumentState.INITIALISED, DocumentState.REJECTED_BY_ERP, DocumentState.SENT_TO_ERP,
                    DocumentState.SETTLED, DocumentState.SIGNED_OFF}));
        }

        if (search.getSupplier() != null) {
            criteria.add(Restrictions.eq("supplier", search.getSupplier()));
        }

        return criteria;
    }

    @Override
    @Deprecated
    public boolean isCILinkedToCCD(CommercialInvoice ci) {
        return false;
     /*   StringBuilder sb = new StringBuilder("select COUNT(*) from DeclarationInvoice di where di.commercialinvoice_id =:ciId");
        Query query = getCurrentSession().createNativeQuery(sb.toString());
        query.setParameter("ciId", ci.getId());
        BigInteger count = (BigInteger) query.uniqueResult();
        return count.intValue() > 0;*/
    }

    @Override
    public long countInState(Set<String> invoiceReferences, String shipmentReference, Set<DocumentState> states) {
        StringBuilder sb = new StringBuilder("select count(*) from CostsInvoice c join c.shipment s  " +
                "where s.reference=:shipmentReference and c.reference ")
                .append("in (:invoiceReferences) ")
                .append(" and c.state in (:states)");
        return (long) getCurrentSession()
                .createQuery(sb.toString())
                .setParameter("shipmentReference", shipmentReference)
                .setParameter("invoiceReferences", invoiceReferences)
                .setParameter("states", states).uniqueResult();
    }

    @Override
    public List<String> findOrderInCIS(Long shipmentId, List<DocumentState> states) {
        StringBuilder sb = new StringBuilder("select distinct o.originalId from CommercialInvoice ci join ci.actualConsignments c " +
                "join c.actualOrders o join o.actualLineItems i  " +
                "where ci.shipment.id=:shipmentId  and i.invoiceQuantity>0 ")
                .append(" and ci.state in (:states) ");
        return getCurrentSession()
                .createQuery(sb.toString())
                .setParameter("shipmentId", shipmentId)
                .setParameter("states", states).list();
    }

    @Override
    public List<String> findOrderWithoutShipRef(Long ciId) {
        StringBuilder sb = new StringBuilder("select distinct o.reference from CommercialInvoice ci join ci.actualConsignments c " +
                "join c.actualOrders o join o.actualLineItems i join ci.shipment s join s.consignments sc join sc.orders eo " +
                "where eo.id=o.originalId and ci.id=:ciId  and i.invoiceQuantity>0 and eo.shippingInformation.shippingReference is null");

        return getCurrentSession()
                .createQuery(sb.toString())
                .setParameter("ciId", ciId)
                .list();
    }
}