BaseClearingInstructionRepositoryImpl.java

package com.tradecloud.repository.impl;

import com.tradecloud.domain.base.utils.ObjectUtil;
import com.tradecloud.domain.document.invoice.ActualLineItem;
import com.tradecloud.domain.shipment.Shipment;
import com.tradecloud.domain.shipment.clearing.BaseClearingInstruction;
import com.tradecloud.domain.shipment.clearing.ClearingInstruction;
import com.tradecloud.domain.shipment.clearing.CustomsDeclaration;
import com.tradecloud.dto.clearing.BaseClearingInstructionSearch;
import com.tradecloud.repository.BaseClearingInstructionRepository;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import org.apache.commons.collections4.CollectionUtils;
import org.hibernate.query.NativeQuery;
import org.hibernate.query.Query;
import org.hibernate.transform.DistinctRootEntityResultTransformer;
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.time.LocalDate;
import java.time.ZoneId;
import java.util.*;

@Repository
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class BaseClearingInstructionRepositoryImpl extends RepositoryBaseImpl<BaseClearingInstruction, BaseClearingInstructionSearch>
        implements BaseClearingInstructionRepository {

    @Override
    public List<BaseClearingInstruction> search(BaseClearingInstructionSearch search) {
        CriteriaBuilder criteriaBuilder = getSession().getCriteriaBuilder();
        CriteriaQuery<BaseClearingInstruction> criteriaQuery = criteriaBuilder.createQuery(BaseClearingInstruction.class);
        Root<BaseClearingInstruction> c = criteriaQuery.from(BaseClearingInstruction.class);
        List<Predicate> predicates = new ArrayList<>();
        //Date Range Search Restrictions
        if (ObjectUtil.allNotNull(search.getFromDate(), search.getToDate())) {
            Join<BaseClearingInstruction, Shipment> shipment = c.join("shipment", JoinType.INNER);
            predicates.add(criteriaBuilder.between(
                    shipment.get("estimatedArrivalDateAtPlaceOfDischarge"), search.getFromDate(), search.getToDate()));
        }
        //Status Search Criteria
        if (search.getSelectedStatuses() != null && search.getSelectedStatuses().length > 0) {
            predicates.add(c.get("status").in(Arrays.asList(search.getSelectedStatuses())));
        }
        return CollectionUtils.isNotEmpty(predicates)
                ? getSession().createQuery(criteriaQuery.select(c).where(predicates.toArray(new Predicate[]{}))).getResultList()
                : getSession().createQuery(criteriaQuery.select(c)).getResultList();
    }

    @Override
    public CustomsDeclaration findDeclarationBy(Long number, LocalDate date, String placeOfCustomEntryCode) {
/*        String sql = """
                select cd.*, ce.*, se.*, pc.* from customsdeclaration cd
                left join CustomsDeclaration_clearingevent cd_ce on cd_ce.customsdeclaration_id = cd.id
                left join clearingevent ce on cd_ce.events_id = ce.id
                left join customsdeclaration_sarsevent cd_se on cd.id = cd_se.customsdeclaration_id
                left join SarsEvent se on se.id = cd_se.events_id
                left join placeofcustom pc on cd.placeOfCustomsEntry_code = pc.code
                where
                cd.number = :number and
                DATE(cd.declarationDate) = :date
                """;*/


        String sql = """
                select cd.*,
                    (
                        select i.reference
                        from costsinvoice i
                        left join declarationinvoice di on i.id = di.id
                        where di.customsdeclaration_id = cd.id
                        limit 1
                    ) as invoiceNumber,
                (SELECT MAX(CustomsDeclaration_clearingevent.events_id)
                                FROM CustomsDeclaration_clearingevent left
                                join ClearingEvent on CustomsDeclaration_clearingevent.events_id = ClearingEvent.id
                                WHERE CustomsDeclaration_clearingevent.customsdeclaration_id = id) as lastEvent,
                (SELECT MAX(customsdeclaration_sarsevent.events_id)
                                FROM customsdeclaration_sarsevent left
                                join SarsEvent on customsdeclaration_sarsevent.events_id = SarsEvent.id
                                WHERE customsdeclaration_sarsevent.customsdeclaration_id = id) as lastSarsEvent
                from customsdeclaration cd
                left join placeofcustom pc on cd.placeOfCustomsEntry_code = pc.code
                where
                cd.number = :number and
                DATE(cd.declarationDate) = :date
                """;

        if (placeOfCustomEntryCode != null)
            sql = sql + " and pc.code = :pc";

        Date declarationDate = Date.from(date.atStartOfDay(ZoneId.systemDefault()).toInstant());
        NativeQuery<CustomsDeclaration> nativeQuery = getSession().createNativeQuery(sql, CustomsDeclaration.class);
        nativeQuery
                .setParameter("number", number)
                .setParameter("date", date);
        if (placeOfCustomEntryCode != null)
            nativeQuery.setParameter("pc", placeOfCustomEntryCode);
        nativeQuery.setResultTransformer(DistinctRootEntityResultTransformer.INSTANCE);
        nativeQuery.addEntity(CustomsDeclaration.class);
        return nativeQuery.uniqueResult();
    }

    @Override
    public int getItemCount(CustomsDeclaration customsDeclaration) {
        String queryString = "select count(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 " +
                "left join declarationinvoice di on costsinvoice.id = di.id" +
                "WHERE " +
                "costsinvoice.state != 'DELETED' " +
                "AND costsinvoice.id = :invoiceid " +
                "AND di.customsdeclaration_id=:id";

        return getCurrentSession().createNativeQuery(queryString)
                .setParameter("id", customsDeclaration.getId()).getFirstResult();
    }

    @Override
    public List<ActualLineItem> getItems(CustomsDeclaration customsDeclaration) {
        String queryString = "select ali.* " +
                "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 " +
                "left join declarationinvoice di on costsinvoice.id = di.id" +
                "WHERE " +
                "costsinvoice.state != 'DELETED' " +
                "AND costsinvoice.id = :invoiceid " +
                "AND di.customsdeclaration_id=:id";

        return getCurrentSession().createNativeQuery(queryString, ActualLineItem.class)
                .setParameter("id", customsDeclaration.getId()).getResultList();
    }

    @Override
    public boolean isShipmentDeclarationsInReopenedState(Long shipmentId) {
        String queryString = "select count(cd.id) " +
                "FROM customsdeclaration cd " +
                "left join shipment s on s.id = cd.shipment_id " +
                "WHERE " +
                "cd.status in ('NEW','DRAFT','REOPENED','REJECTED') " +
                "AND cd.shipment_id = :shipmentId ";

        List<BigInteger> list = getCurrentSession().createNativeQuery(queryString)
                .setParameter("shipmentId", shipmentId).getResultList();
        if (list.size() > 0) {
            return (list.get(0).intValue()) > 0;
        }
        return false;
    }

    @Override
    public List<Object[]> findDeclarationShipment(String lrnNumber, long customsDeclarationId) {
        String queryString = "select s.id, s.reference " +
                "FROM customsdeclaration cd " +
                "left join shipment s on s.id = cd.shipment_id " +
                "WHERE " +
                "cd.id <> :id " +
                "and (cd.lrnnumber ilike :lrn or cd.mrnnumber ilike :lrn)";

        List list = getCurrentSession().createNativeQuery(queryString)
                .setParameter("lrn", lrnNumber).setParameter("id", customsDeclarationId).getResultList();

        return list;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Set<ClearingInstruction> findClearingInstructions(Shipment shipment) {
        CriteriaBuilder builder = getCurrentSession().getCriteriaBuilder();
        CriteriaQuery<ClearingInstruction> criteria = builder.createQuery(ClearingInstruction.class);
        Root<ClearingInstruction> root = criteria.from(ClearingInstruction.class);

        criteria.select(root);
        criteria.where(builder.equal(root.get("shipment"), shipment));
        criteria.orderBy(builder.asc(root.get("created")));
        Query query = getCurrentSession().createQuery(criteria);
        return new HashSet<>(query.getResultList());
    }

    @Override
    public boolean hasClearingInstructions(Shipment shipment) {
        Number count = (Number) getCurrentSession()
                .createNativeQuery("select count(*) from clearinginstruction where shipment_id = :shipment_id")
                .setParameter("shipment_id", shipment.getId())
                .getSingleResult();

        return count.longValue() > 0;
    }

    @Override
    public CustomsDeclaration getCustomsDeclarationByLRN(String lrn) {
        CustomsDeclaration declaration = getCurrentSession()
                .createNativeQuery("select * from customsdeclaration where lrnnumber = :lrn ", CustomsDeclaration.class)
                .setParameter("lrn", lrn).uniqueResult();
        return declaration;
    }

    @Override
    public Map<Long, Date> shipmentClearingInstructionLastSignOff(Set<Long> shipIds) {
        if (shipIds == null || shipIds.isEmpty()) {
            return Collections.emptyMap();
        }
        String query = """ 
            SELECT c.shipment_id, MAX(e.created)
            FROM clearinginstruction c 
            JOIN clearinginstruction_clearingevent cie ON (cie.clearinginstruction_id = c.id) 
            JOIN clearingevent e ON (e.id = cie.events_id) 
            WHERE e.eventtype = 'SIGNED_OFF' 
            AND c.shipment_id IN (:shipIds) 
            GROUP BY c.shipment_id
            """;

        List<Object[]> results = getCurrentSession()
                .createNativeQuery(query)
                .setParameter("shipIds", shipIds)
                .list();

        Map<Long, Date> resultMap = new HashMap<>();

        for (Object[] row : results) {
            // Native queries often return BigInteger for IDs, so Number is a safe cast
            Long shipmentId = ((Number) row[0]).longValue();
            Date date = (Date) row[1];
            resultMap.put(shipmentId, date);
        }

        return resultMap;
    }
}