TransporterContainerRepositoryImpl.java

package com.tradecloud.repository.transporter.impl;

import com.tradecloud.domain.container.ShipmentContainer;
import com.tradecloud.domain.transporter.TransporterContainer;
import com.tradecloud.domain.transporter.TransporterCosting;
import com.tradecloud.dto.transporter.TransporterContainerResult;
import com.tradecloud.dto.transporter.TransporterContainerSearch;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import com.tradecloud.repository.transporter.TransporterContainerRepository;
import org.hibernate.Query;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.AliasToBeanResultTransformer;
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.List;

@Repository
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class TransporterContainerRepositoryImpl extends RepositoryBaseImpl<TransporterContainer, TransporterContainerSearch>
        implements TransporterContainerRepository {
    private static final String TRANSPORTER_QUERY = """
            from container c
             join shipment s on (c.shipment_id=s.id)
             join serviceprovider t on (t.id=c.transporter_id)
             left join finaldestination fd on (fd.code=c.finaldestination_code)
             join shipmentshippinginfo si on (si.id=s.shippinginfo_id)
             left join serviceprovider fwd on (fwd.id=si.freightforwarder_id)
            left join transporter_container tc on (tc.shipmentContainer_id=c.id)
            where t.id is not null and s.state <>'DELETED'
            and (select count(*) from clearinginstruction where shipment_id=s.id) > 0
            """;

    private static final String TRANSPORTER_QUERY_FIELDS = """
            select COALESCE(tc.datesSubmitted,'f') as "datesSubmitted",
                   COALESCE(tc.invoiced,'f') as "invoiced",
                   COALESCE(tc.documentUploaded,'f') as "documentUploaded",
                   c.id as "containerId",
                   c.reference as "containerReference",
                   s.reference as "shipmentReference",
                   s.number as "shipmentNumber",
                   fd.name as "finalDestination",
                   fwd.name as "freightForwarder",
                   c.customsExitDate as "customsExitDate",
                   s.estimatedArrivalDateAtPlaceOfDischarge as "etaAtPod"
            """;

    @Override
    public List<TransporterContainerResult> search2(TransporterContainerSearch transporterContainerSearch) {

        StringBuilder sqlB = new StringBuilder(TRANSPORTER_QUERY_FIELDS +
                TRANSPORTER_QUERY + " ");
        SearchMetaParams searchMetaParams = transporterContainerSearch.getSearchMetaParams();
        boolean search = true;
        NativeQuery query = getNativeQuery(sqlB, transporterContainerSearch, search);

        if (searchMetaParams != null && search) {
            if (searchMetaParams.getRowCount() != -1) {
                query.setMaxResults(searchMetaParams.getRowCount());
            }
            if (searchMetaParams.getRowIndex() != -1) {
                query.setFirstResult(searchMetaParams.getRowIndex());
            }

        }
        query.setResultTransformer(new AliasToBeanResultTransformer(TransporterContainerResult.class));
        return query.list();
    }

    @Override
    public long count2(TransporterContainerSearch searchDTO) {
        StringBuilder queryBuilder = new StringBuilder("select count(*) " + TRANSPORTER_QUERY);
        NativeQuery query = getNativeQuery(queryBuilder, searchDTO, false);
        return ((BigInteger) query.uniqueResult()).longValue();
    }

    @Override
    public TransporterContainer findBy(ShipmentContainer shipmentContainer) {
        Query query = getSession().createQuery("from TransporterContainer where shipmentContainer =:shipmentContainer");
        query.setMaxResults(1);
        query.setParameter("shipmentContainer", shipmentContainer);
        return (TransporterContainer) query.uniqueResult();
    }

    private NativeQuery getNativeQuery(StringBuilder sqlB, TransporterContainerSearch containerSearch, boolean search) {
        if (containerSearch.getContainerReference() != null) {
            sqlB.append(" and c.reference ilike :cReference");
        }

        if (containerSearch.getShipmentReference() != null) {
            sqlB.append(" and s.reference ilike :sReference");
        }

        if (containerSearch.getShipmentNumber() != null) {
            sqlB.append(" and s.number ilike :sNumber");
        }

        if (containerSearch.getEtaAtPod() != null && containerSearch.getEtaAtPod().getFrom()!=null) {
            sqlB.append(" and s.estimatedArrivalDateAtPlaceOfDischarge >=:etaAtPodFrom");
        }

        if (containerSearch.getEtaAtPod() != null && containerSearch.getEtaAtPod().getTo()!=null) {
            sqlB.append(" and s.estimatedArrivalDateAtPlaceOfDischarge <=:etaAtPodTo");
        }

        if (containerSearch.getTransporter() != null) {
            sqlB.append(" and c.transporter_id = :transporter");
        }

        if (!containerSearch.isIncludeInvoicedDocsDated()) {
            sqlB.append(" and (tc.id is null OR tc.documentUploaded='f' OR tc.invoiced='f' OR tc.datesSubmitted='f') ");
        }

        if (search) {
            if(containerSearch.getSortBy()!=null && containerSearch.getSortBy().equals(TransporterContainer.SHIPMENT_REFERENCE)) {
                sqlB.append(" order by s.reference DESC");
            }else{
                sqlB.append(" order by c.reference DESC");
            }
        }

        NativeQuery query = getSessionCustom().createNativeQuery(sqlB.toString());

        setParameters(containerSearch, query);
        return query;
    }

    private void setParameters(TransporterContainerSearch containerSearch, NativeQuery query) {
        if (containerSearch.getContainerReference() != null) {
            query.setParameter("cReference", toLIKE(containerSearch.getContainerReference()));
        }

        if (containerSearch.getShipmentReference() != null) {
            query.setParameter("sReference", toLIKE(containerSearch.getShipmentReference()));
        }

        if (containerSearch.getShipmentNumber() != null) {
            query.setParameter("sNumber", toLIKE(containerSearch.getShipmentNumber()));
        }

        if (containerSearch.getTransporter() != null) {
            query.setParameter("transporter", containerSearch.getTransporter().getId());
        }

        if (containerSearch.getEtaAtPod() != null && containerSearch.getEtaAtPod().getFrom()!=null) {
            query.setParameter("etaAtPodFrom",containerSearch.getEtaAtPod().getFrom());
        }

        if (containerSearch.getEtaAtPod() != null && containerSearch.getEtaAtPod().getTo()!=null) {
            query.setParameter("etaAtPodTo", containerSearch.getEtaAtPod().getTo());
        }
    }

    private void addOrderBy(SearchMetaParams searchMetaParams, StringBuilder queryBuilder) {
//        if (searchMetaParams != null) {
//            if (searchMetaParams.getOrderBy() != null) {
        queryBuilder.append(" order by c.reference DESC");
//                }
//            }
//        }
    }

    @Override
    public TransporterCosting findCosting(String containerReference) {
        Query query = getSession().createQuery("from TransporterCosting where reference =:contRef");
        query.setMaxResults(1);
        query.setParameter("contRef", containerReference);
        return (TransporterCosting) query.uniqueResult();
    }

    @Override
    public TransporterContainer findTransportContainer(ShipmentContainer container) {
        Query query = getSession().createQuery("from TransporterContainer where shipmentContainer =:shipmentContainer");
        query.setMaxResults(1);
        query.setParameter("shipmentContainer", container);
        return (TransporterContainer) query.uniqueResult();
    }

    @Override
    public long stateChangedToCompleted(Long tCostingId) {
        Query query = getSession().createQuery("select count(*) from TransporterCosting where id =:tCostingId " +
                "and state in ('COMPLETED','INVOICE_GENERATION_FAILED','SPLIT_PDF_GENERATE_FAILED','SPLIT_PDF_PER_SHIP_GENERATE_FAILED')");
        query.setMaxResults(1);
        query.setParameter("tCostingId", tCostingId);
        return ((Long) query.uniqueResult()).longValue();
    }
}