CostsInvoiceRepositoryImpl.java

package com.tradecloud.repository.impl;

import com.tradecloud.domain.document.DocumentState;
import com.tradecloud.domain.document.invoice.CostsInvoice;
import com.tradecloud.repository.CostsInvoiceRepository;
import org.hibernate.Query;
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.BigInteger;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Default implementation of the {@literal CostsInvoiceRepository} interface.
 * 
 * @param <T> Any sublcass of {@literal CostsInvoice}
 */
@Repository(value = "costsInvoiceRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public abstract class CostsInvoiceRepositoryImpl<T extends CostsInvoice> extends RepositoryBaseImplNoSearch<T> implements CostsInvoiceRepository<T> {

    private static final long serialVersionUID = 1L;

    protected List<T> findAllByReference(String namedQuery, String reference) {
        return (List<T>) findByNamedQueryAndNamedParam(namedQuery, "reference", reference);
    }

    @Override
    public T findByIdWithCostLineCostingCells(long id) {
        List<T> invoices = (List<T>) findByNamedQueryAndNamedParam("findInvoiceById", "id", id);
        if (!invoices.isEmpty()) {
            return invoices.get(0);
        }
        return null;
    }

    @Override
    public int countOnShipmentNotDeleted(long shipmentId) {
        return ((Long) getCurrentSession()
                .createQuery("select count(*) from CostsInvoice where shipment.id = :shipmentId and state != 'DELETED'")
                .setParameter("shipmentId", shipmentId).iterate().next()).intValue();
    }

    @Override
    public int countOnConsignmentNotDeleted(long consignmentId) {
        return ((Long) getCurrentSession()
                .createQuery("select count(*) from CostsInvoice where consignment.id = :consignmentId and state != 'DELETED'")
                .setParameter("consignmentId", consignmentId).iterate().next()).intValue();
    }

    @Override
    public List<String> findOrderReferences(T costsInvoice) {
        String queryString =
                "SELECT ao.reference " +
                "FROM ActualOrder ao LEFT JOIN ao.actualConsignment ac LEFT JOIN ac.costsInvoice ci " +
                "WHERE ci=:costsInvoice";

        Query query = getSessionCustom().createQuery(queryString);
        query.setParameter("costsInvoice", costsInvoice);
        return (List<String>) query.list();
    }

    @Override
    public Map<Long, String> findOrderReferences(List<Long> costsInvoiceIds) {
        if (costsInvoiceIds == null || costsInvoiceIds.isEmpty()) {
            return Collections.emptyMap();
        }

        String sql = """
        SELECT ci.id AS invoice_id,
               string_agg(ao.reference, ' ' ORDER BY ao.reference) AS order_references
        FROM actualorder ao
        JOIN actualconsignment ac ON ao.actualconsignment_id = ac.id
        JOIN costsinvoice ci ON ac.costsinvoice_id = ci.id
        WHERE ci.id IN (:costsInvoiceIds)
        GROUP BY ci.id
        """;

        @SuppressWarnings("unchecked")
        List<Object[]> results = getSessionCustom().createNativeQuery(sql)
                .setParameterList("costsInvoiceIds", costsInvoiceIds)
                .getResultList();

        Map<Long, String> orderReferencesByInvoice = new HashMap<>();
        for (Object[] row : results) {
            Long invoiceId = ((Number) row[0]).longValue();
            String orderReferences = (String) row[1];
            orderReferencesByInvoice.put(invoiceId, orderReferences);
        }

        return orderReferencesByInvoice;
    }

    @Override
    public long countInState(long invoiceId, DocumentState documentState) {
        return ((BigInteger) getCurrentSession()
                .createNativeQuery("select count(*) from costsInvoice where id =:invoiceId and state =:documentState")
                .setParameter("invoiceId", invoiceId)
                .setParameter("documentState",documentState.name()).uniqueResult()).intValue();
    }

    @Override
    public List findConsigmentsLinkedJoinTable(long invoiceId) {
        return getCurrentSession().createNativeQuery("select actualconsignments_id from costsinvoice_actualconsignment ss " +
                "  where  ss.costsinvoice_id=" + invoiceId).list();
    }

    @Override
    public List findAllConsignmentsLinked(long invoiceId) {
        return getCurrentSession().createNativeQuery("select id from actualconsignment ss " +
                "  where  costsinvoice_id=" + invoiceId).list();
    }

    @Override
    public void insertJoinEntry(long invoiceId, Long conId) {
        getCurrentSession().createNativeQuery(String.format("insert into costsinvoice_actualconsignment(costsinvoice_id,actualconsignments_id) " +
                "VALUES(%s,%s) ", invoiceId, conId)).executeUpdate();
    }
}