ExportRepositoryImpl.java

package com.tradecloud.repository.export.impl;

import com.tradecloud.domain.base.utils.DateRange;
import com.tradecloud.domain.base.utils.DateUtils;
import com.tradecloud.domain.export.DocumentType;
import com.tradecloud.domain.export.Export;
import com.tradecloud.domain.sars.Status;
import com.tradecloud.dto.export.DivShipSummaryDTO;
import com.tradecloud.dto.export.DivShipSummarySearch;
import com.tradecloud.dto.export.ExportSearch;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import com.tradecloud.repository.export.ExportRepository;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.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 java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

@Repository(value = "exportRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class ExportRepositoryImpl extends RepositoryBaseImpl<Export, ExportSearch> implements ExportRepository {

    private static final long serialVersionUID = 1L;
    private static final Logger log = Logger.getLogger(ExportRepositoryImpl.class);
    private static final String SHIPMENT_NUMBER = "shipmentNumber";
    private static final String CUSDEC_NUMBER = "cusdecId";
    private static final String CUSCAR_NUMBER = "cuscarId";
    private static final String CONSIGNEE = "exportConsignee";
    private static final String BILLOFENTRYNUMBER = "billOfEntryNumber";
    private static final String LRN_NUMBER = "lrnNumber";
    private static final String MRN_NUMBER = "mrnNumber";
    private static final String CREATED = "created";
    private static final String DEPOT = "placeOfLoading";
    private static final String EXPORT_STATUS = "exportStatus";
    private static final String REFERENCE = "reference";
    private static final String INVOICE_REFERENCE = "reference";
    private static final String SHIPMENT_TYPE = "shipmentType";
    private static final String EDIFACTSTATUS = "edifactStatus";
    private static final String EXPORTER_REFERENCE = "exportersReference";

    public List<Export> fullSearch(ExportSearch search) {
        Query query = createExportSearchHQLQuery(search, false);

        return query.list();
    }

    private Query createExportSearchHQLQuery(ExportSearch search, boolean count) {
        StringBuilder hql = new StringBuilder();

        if (search.getInvoiceNumber() == null) {
            if (count) {
                hql.append("select count(e) from Export e where e.id is not null ");
            } else {
                hql.append("from Export e where e.id is not null ");
            }
        } else {
            if (count) {
                hql.append("select count(e) from Export e, ExportInvoice ei where ei.export.id = e.id ");
            } else {
                hql.append("select e from Export e, ExportInvoice ei where ei.export.id = e.id ");
            }
        }

        if (search.getShipmentNumber() != null) {
            hql.append(" and lower(e." + SHIPMENT_NUMBER + ") like :shipmentNumber ");
        }

        if (search.getExporterReference() != null) {
            hql.append(" and lower(e." + EXPORTER_REFERENCE + ") like :exporterReference ");
        }
        if (search.getConsignee() != null) {
            hql.append(" and e." + CONSIGNEE + " = :consignee ");
        }
        if (search.getMode() != null) {
            hql.append(" and e." + SHIPMENT_TYPE + " = :mode ");
        }
        if (search.getInvoiceNumber() != null) {
            hql.append(" and lower(ei." + REFERENCE + ") like :invoiceNumber ");
        }
        if (search.getBillOfEntryNumber() != null) {
            hql.append(" and lower(e." + BILLOFENTRYNUMBER + ") like :billOfEntryNumber ");
        }
        if (search.getLrnNumber() != null) {
            hql.append(" and lower(e." + LRN_NUMBER + ") like :lrnNumber ");
        }
        if (search.getExportStatus() != null) {
            hql.append(" and e." + EXPORT_STATUS + " = :status");
        }
        if (ArrayUtils.isNotEmpty((search.getExportStatuses()))) {
            hql.append(" and e." + EXPORT_STATUS + " in (:status)");
        } else {
            hql.append(" and e." + EXPORT_STATUS + " != '" + Status.CLOSED + "'");
        }
        if (search.getCreatedDateFrom() != null) {
            hql.append(" and e." + CREATED + " >= :createdFrom");
        }
        if (search.getCreatedDateTo() != null) {
            hql.append(" and e." + CREATED + " <= :createdTo");
        }
        if (search.getDepot() != null) {
            hql.append(" and e." + DEPOT + " = :placeOfLoading");
        }
        if (search.getCusdecId() != null) {
            hql.append(" and e." + CUSDEC_NUMBER + " = :cusdecId");
        }
        if (search.getCuscarId() != null) {
            hql.append(" and e." + CUSCAR_NUMBER + " = :cuscarId");
        }
        if (search.getEdifactStatus() != null) {
            hql.append(" and e." + EDIFACTSTATUS + " = :edifactStatus");
        }
        if (search.getEdiNumber() != null) {
            hql.append(" and (e." + LRN_NUMBER + " = :ediNumber or e." + MRN_NUMBER + " = :ediNumber or e." + BILLOFENTRYNUMBER + " = :ediNumber)");
        }

        if (!count) {
            hql.append(" order by e.created desc");
        }

        Query query = getSession().createQuery(hql.toString());

        if (search.getShipmentNumber() != null) {
            query.setParameter("shipmentNumber", "%" + search.getShipmentNumber().toLowerCase() + "%");
        }
        if (search.getConsignee() != null) {
            query.setParameter("consignee", search.getConsignee());
        }
        if (search.getMode() != null) {
            query.setParameter("mode", search.getMode().toString());
        }
        if (search.getInvoiceNumber() != null) {
            query.setParameter("invoiceNumber", "%" + search.getInvoiceNumber().toLowerCase() + "%");
        }
        if (search.getBillOfEntryNumber() != null) {
            query.setParameter("billOfEntryNumber", "%" + search.getBillOfEntryNumber().toLowerCase() + "%");
        }
        if (search.getLrnNumber() != null) {
            query.setParameter("lrnNumber", "%" + search.getLrnNumber().toLowerCase() + "%");
        }
        if (search.getExportStatus() != null) {
            query.setParameter("status", search.getExportStatus());
        } else if (ArrayUtils.isNotEmpty(search.getExportStatuses())) {
            query.setParameterList("status", search.getExportStatuses());
        }
        if (search.getCreatedDateFrom() != null) {
            query.setParameter("createdFrom", DateUtils.getStartOfDay(search.getCreatedDateFrom()));
        }
        if (search.getCreatedDateTo() != null) {
            query.setParameter("createdTo", DateUtils.getEndOfDay(search.getCreatedDateTo()));
        }
        if (search.getDepot() != null) {
            query.setParameter("placeOfLoading", search.getDepot());
        }
        if (search.getCusdecId() != null) {
            query.setParameter("cusdecId", search.getCusdecId());
        }
        if (search.getCuscarId() != null) {
            query.setParameter("cuscarId", search.getCuscarId());
        }
        if (search.getEdifactStatus() != null) {
            query.setParameter("edifactStatus", search.getEdifactStatus());
        }
        if (search.getEdiNumber() != null) {
            query.setParameter("ediNumber", search.getEdiNumber());
        }
        if (search.getExporterReference() != null) {
            query.setParameter("exporterReference", "%" + search.getExporterReference().toLowerCase() + "%");
        }

        return query;
    }

    @Override
    public List<Export> search(ExportSearch search) {
        Query query = createExportSearchHQLQuery(search, false);

        if (search.getSearchMetaParams() != null) {
            query.setFirstResult(search.getSearchMetaParams().getRowIndex());
            query.setMaxResults(search.getSearchMetaParams().getRowCount());
        }

        return query.list();
    }

    @Override
    public List<Export> findAll() {
        return findAll(null);
    }

    @Override
    public Export findByShipmentNumber(String shipmentNumber) {
        ExportSearch exportSearch = new ExportSearch();
        exportSearch.setShipmentNumber(shipmentNumber);

        StringBuilder hql = new StringBuilder();
        hql.append("from Export e where e.id is not null ");
        hql.append(" and e." + SHIPMENT_NUMBER + " =:shipmentNumber ");
        Query query = getSession().createQuery(hql.toString());
        query.setParameter("shipmentNumber", shipmentNumber.trim());

        return (Export) query.uniqueResult();
    }

    @Override
    public Export findByExporterReference(String reference) {
        ExportSearch exportSearch = new ExportSearch();
        exportSearch.setShipmentNumber(reference);

        StringBuilder hql = new StringBuilder();
        hql.append("from Export e where e.id is not null ");
        hql.append(" and e.exportersReference =:reference ");
        Query query = getSession().createQuery(hql.toString());
        query.setParameter("reference", reference.trim());

        return (Export) query.uniqueResult();
    }

    @Override
    public Long countByExporterReference(String reference) {
        ExportSearch exportSearch = new ExportSearch();
        exportSearch.setShipmentNumber(reference);

        StringBuilder hql = new StringBuilder();
        hql.append("select count(*) from Export e where e.id is not null ");
        hql.append(" and e.exportersReference =:reference ");
        Query query = getSession().createQuery(hql.toString());
        query.setParameter("reference", reference.trim());

        return (Long) query.uniqueResult();
    }

    @Override
    public Export findBySarsEdifactNumber(Long number, DocumentType documentType) {
        ExportSearch exportSearch = new ExportSearch();
        switch (documentType) {
            case Edifact_CUSDEC:
                exportSearch.setCusdecId(number);
                break;
            case Edifact_CUSCAR:
                exportSearch.setCuscarId(number);
            default:
        }
        Query query = createExportSearchHQLQuery(exportSearch, false);

        return (Export) query.uniqueResult();
    }

    @Override
    public Export findById(Long id) {
        String queryString = "from Export export left join fetch export.events where export.id=:id";
        org.hibernate.query.Query<Export> query = getSession().createQuery(queryString, Export.class);
        query.setParameter("id", id);

        return query.uniqueResult();
    }

    @Override
    public Export findByIdWithCostLines(Long id) {
        String queryString = "select distinct e from Export e "
                + "left join fetch e.exportCostLines "
                + "where e.id = :id";

        return getSession()
                .createQuery(queryString, Export.class)
                .setParameter("id", id)
                .uniqueResult();
    }

    @Override
    public long count(ExportSearch search) {
        Query query = createExportSearchHQLQuery(search, true);

        return new Long("" + query.list().get(0));
    }

    @Override
    public long countShipWithTransDocNumber(String transportDocumentNumber, Long id) {
        StringBuilder queryString = new StringBuilder();
        queryString.append("select count (*) from Export where transportDocumentNumber=:transportDocumentNumber");
        if (id != null) {
            queryString.append(" and id<>:exportId");
        }
        Query query = getSessionCustom().createQuery(queryString.toString());
        query.setParameter("transportDocumentNumber", transportDocumentNumber);
        if (id != null) {
            query.setParameter("exportId", id);
        }
        return (Long) query.uniqueResult();
    }

    @Override
    public List<DivShipSummaryDTO> getMerchandiseCategoryShipSummaryReport(DivShipSummarySearch search, boolean groupByOrg) {

        StringBuilder queryString = new StringBuilder("""
        select max(e.actualDepartureDate) as actualDepartureDate,
               max(e.shipmentnumber) as shipmentNo,
               max(u.name) as company,
               max(c.name) as country,
               sum(ec.totalInvoiceQuantity) as quantity,
               count(distinct(ei.id)) as noOfCartons,
               sum(ec.factoryValue) as cost,
               sum(ec.fobValue) as fob,
               sum(ec.freightAmount) as freight,
               sum(ec.insuranceAmount) as insurance,
               sum(ei.volume) as cube,
               sum(ec.cartageLocal) as cartageLocal,
               sum(ec.fuelSurchargeLocal) as fuelSurchargeLocal,
               sum(ec.cartageForeign) as cartageForeign,
               sum(ec.fuelSurchargeForeign) as fuelSurchargeForeign,
               sum(ec.documentationFee) as documentationFee,
               sum(ec.interCompanyMarkupAmount) as interCompanyMarkup,
               sum(ec.transactionValue) as transaction,
               sum(ec.customDutyAmount) as dutydrawback,
               e.ucrNumber,
               ai.merchandiseCategory
        from exportcosting ec
          left join actuallineitem ai on ai.exportcosting_id = ec.id
          left join actualorder ao on ai.actualorder_id = ao.id
          left join actualconsignment ac on ac.id = ao.actualconsignment_id
          left join costsinvoice ci on ac.costsinvoice_id = ci.id
          left join exportinvoice ei on ei.id = ci.id
          left join export e on ei.export_id = e.id
          left join organisationalunit u on (u.id = ei.organisationalUnitById_id)
          left join country c on (c.code = e.countryOfDestination_code)
        where (:shipRef is null or e.shipmentnumber = :shipRef)
        """);

        DateRange created = search.getCreated();
        if (created != null) {
            if (created.getFrom() != null)
                queryString.append(" and e.created >= :fromDate ");
            if (created.getTo() != null)
                queryString.append(" and e.created <= :toDate ");
        }

        DateRange actualDep = search.getActualDepartureDate();
        if (actualDep != null) {
            if (actualDep.getFrom() != null)
                queryString.append(" and e.actualDepartureDate >= :fromActualDepartureDate ");
            if (actualDep.getTo() != null)
                queryString.append(" and e.actualDepartureDate <= :toActualDepartureDate ");
        }

        if (search.getDestinationCountry() != null) {
            queryString.append(" and e.countryOfDestination_code = :destCountry ");
        }

        if (search.getStatus() != null && search.getStatus().length > 0) {
            queryString.append(" and e.exportstatus in (:states) ");
        }

        if (search.getOrganisationalUnit() != null) {
            queryString.append(" and ei.organisationalUnitById_id = :orgId ");
        }

        queryString.append(" group by merchandiseCategory, e.ucrnumber ");
        if (groupByOrg) {
            queryString.append(" ,u.id");
        }

        org.hibernate.query.Query query = getSessionCustom().createNativeQuery(queryString.toString())
                .addScalar("shipmentNo", StandardBasicTypes.STRING)
                .addScalar("company", StandardBasicTypes.STRING)
                .addScalar("country", StandardBasicTypes.STRING)
                .addScalar("quantity", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("noOfCartons", StandardBasicTypes.INTEGER)
                .addScalar("cost", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("fob", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("freight", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("insurance", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("cartageLocal", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("fuelSurchargeLocal", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("cartageForeign", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("fuelSurchargeForeign", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("documentationFee", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("interCompanyMarkup", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("transaction", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("cube", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("dutyDrawBack", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("ucrNumber", StandardBasicTypes.STRING)
                .addScalar("merchandiseCategory", StandardBasicTypes.STRING)
                .addScalar("actualDepartureDate", StandardBasicTypes.DATE)
                .setResultTransformer(Transformers.aliasToBean(DivShipSummaryDTO.class));

        // Set parameters
        query.setParameter("shipRef", search.getShipmentNumber(), StandardBasicTypes.STRING);

        if (created != null) {
            if (created.getFrom() != null) query.setParameter("fromDate", created.getFrom());
            if (created.getTo() != null)   query.setParameter("toDate", created.getTo());
        }

        if (actualDep != null) {
            if (actualDep.getFrom() != null) query.setParameter("fromActualDepartureDate", actualDep.getFrom());
            if (actualDep.getTo() != null)   query.setParameter("toActualDepartureDate", actualDep.getTo());
        }

        if (search.getDestinationCountry() != null) {
            query.setParameter("destCountry", search.getDestinationCountry().getCode());
        }

        if (search.getStatus() != null && search.getStatus().length > 0) {
            query.setParameterList(
                    "states",
                    Arrays.stream(search.getStatus()).map(Enum::name).collect(Collectors.toList())
            );
        }

        if (search.getOrganisationalUnit() != null) {
            query.setParameter("orgId", search.getOrganisationalUnit().getId());
        }

        return query.list();
    }

    @Override
    public List<DivShipSummaryDTO> getDivShipSummaryReport(DivShipSummarySearch search) {
        StringBuilder queryString = new StringBuilder();
        final DateRange created = search.getCreated();
        queryString.append("with shipf as (select id from export where id is not null ");
        if (created != null && created.getFrom() != null) {
            queryString.append(" and created>=:fromDate  ");
        }
        if (created != null && created.getTo() != null) {
            queryString.append(" and created<=:toDate  ");
        }
        DateRange actualDepartureDate = search.getActualDepartureDate();
        if (actualDepartureDate != null && actualDepartureDate.getFrom() != null) {
            queryString.append(" and actualDepartureDate>=:fromActualDepartureDate  ");
        }
        if (actualDepartureDate != null && actualDepartureDate.getTo() != null) {
            queryString.append(" and actualDepartureDate<=:toActualDepartureDate  ");
        }
        if (search.getDestinationCountry() != null) {
            queryString.append(" and countryOfDestination_code=:destCountry  ");
        }
        if (search.getStatus() != null && search.getStatus().length > 0) {
            queryString.append(" and exportstatus in (:states)  ");
        }

        if (StringUtils.isNotEmpty(search.getShipmentNumber())) {
            if (search.isExactMatch()) {
                queryString.append(" and shipmentnumber =:shipNoPattern");
            } else {
                queryString.append(" and shipmentnumber ilike :shipNoPattern");
            }
        }
        queryString.append(")");
        queryString.append("select max(e.actualDepartureDate) as actualDepartureDate, max(e.shipmentnumber) as shipmentNo,max(u.name) as company," +
                        "max(ec.name) as country,")
                .append(" sum(eic.totalInvoiceQuantity)as quantity, count(distinct(ei.id)) as noOfCartons,sum(eic.factoryValue) as cost,")
                .append("sum(eic.fobValue) as fob,sum(eic.freightAmount) as freight,sum(eic.insuranceAmount) as insurance,sum(ei.volume) as cube")
                .append(",sum(eic.cartageLocal) as cartageLocal")
                .append(",sum(eic.fuelSurchargeLocal) as fuelSurchargeLocal")
                .append(",sum(eic.cartageForeign) as cartageForeign")
                .append(",sum(eic.fuelSurchargeForeign) as fuelSurchargeForeign")
                .append(",sum(eic.documentationFee) as documentationFee")
                .append(",sum(eic.interCompanyMarkupAmount) as interCompanyMarkup")
                .append(",sum(eic.transactionValue) as transaction")
                .append(",max(u.id) as companyId")
                .append(" ,sum(eic.customDutyAmount) as dutydrawback,e.ucrNumber  from export e")
                .append(" join exportinvoice ei on (ei.export_id=e.id) join exportcosting eic on (ei.exportcosting_id=eic.id)")
                .append(" join organisationalunit u on (u.id=ei.organisationalUnitById_id)")
                .append(" left join country ec on (ec.code=e.countryOfDestination_code)  where ei.organisationalUnitById_id is not null");

        if (search.getOrganisationalUnit() != null) {
            queryString.append(" and u.id=:orgId  ");
        }
        queryString.append(" and e.id in (select id from shipf) group by e.id,ei.organisationalUnitById_id ;");
        org.hibernate.query.Query query = getSessionCustom().createNativeQuery(queryString.toString())
                .addScalar("shipmentNo", StandardBasicTypes.STRING)
                .addScalar("company", StandardBasicTypes.STRING)
                .addScalar("country", StandardBasicTypes.STRING)
                .addScalar("quantity", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("noOfCartons", StandardBasicTypes.INTEGER)
                .addScalar("cost", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("fob", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("freight", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("insurance", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("cartageLocal", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("fuelSurchargeLocal", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("cartageForeign", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("fuelSurchargeForeign", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("documentationFee", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("interCompanyMarkup", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("transaction", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("cube", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("dutyDrawBack", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("ucrNumber", StandardBasicTypes.STRING)
                .addScalar("companyId", StandardBasicTypes.LONG)
                .addScalar("actualDepartureDate", StandardBasicTypes.DATE)
                .setResultTransformer(Transformers.aliasToBean(DivShipSummaryDTO.class));
        if (created != null && created.getFrom() != null) {
            query.setParameter("fromDate", created.getFrom());
        }
        if (created != null && created.getTo() != null) {
            query.setParameter("toDate", created.getTo());
        }
        if (actualDepartureDate != null && actualDepartureDate.getFrom() != null) {
            query.setParameter("fromActualDepartureDate", actualDepartureDate.getFrom());
        }
        if (actualDepartureDate != null && actualDepartureDate.getTo() != null) {
            query.setParameter("toActualDepartureDate", actualDepartureDate.getTo());
        }
        if (search.getDestinationCountry() != null) {
            query.setParameter("destCountry", search.getDestinationCountry().getCode());
        }
        if (search.getStatus() != null && search.getStatus().length > 0) {
            queryString.append(" and exportstatus in (:states)  ");
            query.setParameterList("states", Arrays.stream(search.getStatus()).map(s -> s.name()).collect(Collectors.toList()));
        }
        if (StringUtils.isNotEmpty(search.getShipmentNumber())) {
            if (search.isExactMatch()) {

                query.setParameter("shipNoPattern", search.getShipmentNumber());
            } else {
                query.setParameter("shipNoPattern", "%" + search.getShipmentNumber() + "%");
            }
        }

        if (search.getOrganisationalUnit() != null) {
            query.setParameter("orgId", search.getOrganisationalUnit().getId());
        }

        return query.list();
    }
}