DocumentSearchHelper.java

package com.tradecloud.repository.helper;

import com.tradecloud.domain.base.utils.DateRange;
import com.tradecloud.domain.document.DocumentType;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.dto.invoice.CostingDocumentSearch;
import com.tradecloud.repository.SearchMetaParams;

import java.util.Optional;
import java.util.Set;

public class DocumentSearchHelper {
    private static void handleDateRange(StringBuilder builder, boolean isFromEmpty, boolean isToEmpty) {
        if (!isFromEmpty && !isToEmpty) {
            builder.append(" and costInv.created between :from and :to");
        } else if (!isToEmpty) {
            builder.append(" and costInv.created <= :to");
        } else if (!isFromEmpty) {
            builder.append(" and costInv.created >= :from");
        }
    }

    public static String getQuery(CostingDocumentSearch search, boolean count, Set<OrganisationalUnit> organisationalUnits) {
        boolean isTypeEmpty = search.getDocumentType() == null;
        boolean isReferenceEmpty = search.getReference() == null;
        boolean isCreditorEmpty = search.getCreditors() == null || search.getCreditors().isEmpty();
        boolean isStateEmpty = search.getStates() == null || search.getStates().isEmpty();
        boolean isShipmentRefEmpty = search.getShipmentReference() == null;
        boolean isCurrencyEmpty = search.getCurrency() == null;

        boolean isFromEmpty = search.getDateRange() == null || search.getDateRange().getFrom() == null;
        boolean isToEmpty = search.getDateRange() == null || search.getDateRange().getTo() == null;
        boolean isInvFromEmpty = search.getInvoiceDate() == null || search.getInvoiceDate().getFrom() == null;
        boolean isInvToEmpty = search.getInvoiceDate() == null || search.getInvoiceDate().getTo() == null;
        boolean isInvSignOffFromEmpty = search.getSignOffDateRange() == null || search.getSignOffDateRange().getFrom() == null;
        boolean isInvSignOffToEmpty = search.getSignOffDateRange() == null || search.getSignOffDateRange().getTo() == null;
        boolean isEoaDateToEmpty = search.getEstimatedArrivalDateAtPlaceOfDischargeFromTo() == null ||
                search.getEstimatedArrivalDateAtPlaceOfDischargeFromTo().getTo() == null;
        boolean isEoaDateFromEmpty = search.getEstimatedArrivalDateAtPlaceOfDischargeFromTo() == null ||
                search.getEstimatedArrivalDateAtPlaceOfDischargeFromTo().getFrom() == null;

        StringBuilder builder = new StringBuilder();
        if (count) {
            builder.append(" select count(*) ");
        } else {
            builder.append(" select costInv ");
        }

        builder.append(" from CostsInvoice costInv ");

        /*if (search.getExportParty() != null)
            builder.append(" join ci.exportParty party ");*/

        DateRange signOffDateRange = search.getSignOffDateRange();
        if (!signOffDateRange.isEmpty()) {
            builder.append(" join costInv.events de ");
            builder.append(" where de.eventType = 'SIGNED_OFF'  and costInv.id is not null ");
        } else {
            builder.append(" where costInv.id is not null ");
        }

        if (!signOffDateRange.isEmpty()) {
            if (signOffDateRange.getFrom() != null && signOffDateRange.getTo() != null) {
                builder.append(" and de.created between :invSignOffFrom and :invSignOffTo ");
            } else if (!isInvSignOffToEmpty) {
                builder.append(" and de.created <= :invSignOffTo ");
            } else if (!isInvSignOffFromEmpty) {
                builder.append(" and de.created >= :invSignOffFrom ");
            }
        }

        if (search.getDocumentType() == null) {
            builder.append(" and costInv.class != DeclarationInvoice ");
        }

        if (!isStateEmpty) {
            builder.append(" and costInv.state in (:states) ");
        }

        if (!isCurrencyEmpty) {
            builder.append(" and costInv.currency = :currency ");
        }

        if (!isShipmentRefEmpty) {
            builder.append(" and lower(costInv.shipment.reference) like  lower(:shipmentReference)");
        }

        if (search.getShipmentNumber() != null) {
            builder.append(" and lower(costInv.shipment.number) like  lower(:shipmentNumber)");
        }
        builder.append(" and costInv.shipment.imports = :imports");

        if (!isInvFromEmpty && !isInvToEmpty) {
            builder.append(" and costInv.dateCreated between :invfrom and :invto");
        } else if (!isInvToEmpty) {
            builder.append(" and costInv.dateCreated <= :invto");
        } else if (!isInvFromEmpty) {
            builder.append(" and costInv.dateCreated >= :invfrom");
        }

        if (!isReferenceEmpty) {
            builder.append(" and lower(costInv.reference) like lower(:reference) ");
        }

        if (search.getIncludeLinkedToCompleteShipments() != null && !search.getIncludeLinkedToCompleteShipments()) {
            builder.append(" and costInv.shipment.state != 'COMPLETE' ");
        }
        Optional.ofNullable(search.getInvoiceNumber()).ifPresent(s -> {
            builder.append(" and lower(costInv.number) like lower(:number) ");
        });

        DateRange settlementDate = search.getSettlementDate();
        if (!settlementDate.isEmpty()) {
            builder.append(
                    " and costInv.id in (select ci.id from CommercialInvoice ci left join ci.plannedSettlements");
            builder.append(" ps where ci.id is not null");
            if (settlementDate.getFrom() != null) {
                builder.append(" and ps.settlementDate>=:settlementDateFrom ");
            }
            if (settlementDate.getTo() != null) {
                builder.append(" and ps.settlementDate<=:settlementDateTo ");
            }

            builder.append(")");
        }

        if (search.getWithSubShipments() != null && search.getWithSubShipments()) {
            builder.append(" and ( select count(*) from SubShipment sub where  sub.shipment.id= costInv.shipment.id )>0");
        }

        handleDateRange(builder, isFromEmpty, isToEmpty);

        if (!isTypeEmpty && search.getDocumentType().equals(DocumentType.COMMERCIAL_INVOICE)) {
            if (!isCreditorEmpty) {
                builder.append(
                        " and costInv.id in (SELECT id from CommercialInvoice ci where ci.supplier in (:suppliers)");
                if (!isStateEmpty)
                    builder.append(" and ci.state in (:states) ");
                builder.append(")");
            } else if (search.getExportParty() != null) {
                builder.append(
                        " and costInv.id in (SELECT id from CommercialInvoice ci where ci.exportParty = :exportParty");
                if (!isStateEmpty)
                    builder.append(" and ci.state in (:states) ");
                builder.append(")");
            } else {
                builder.append(" and costInv.id in (SELECT id from CommercialInvoice)");
            }

        } else if (!isTypeEmpty && search.getDocumentType().equals(DocumentType.COMMERCIAL_CREDIT_NOTE)) {
            if (!isCreditorEmpty) {
                builder.append(
                        " and costInv.id in (SELECT id from CommercialCreditNote ccn where ccn.supplier in (:suppliers))");
            } else if (search.getExportParty() != null) {
                builder.append(
                        " and costInv.id in (SELECT id from CommercialCreditNote ccn where ccn.exportParty = :exportParty)");
            } else {
                builder.append(" and costInv.id in (SELECT id from CommercialCreditNote)");
            }

        } else if (!isTypeEmpty && search.getDocumentType().equals(DocumentType.SERVICE_PROVIDER_CREDIT_NOTE)) {
            if (!isCreditorEmpty) {
                builder.append(
                        " and costInv.id in (SELECT id from ServiceProviderCreditNote spicn where spicn.supplier in (:suppliers))");
            } else {
                builder.append(" and costInv.id in (SELECT id from ServiceProviderCreditNote)");
            }

        } else if (!isTypeEmpty && search.getDocumentType().equals(DocumentType.SERVICE_PROVIDER_INVOICE)) {
            if (!isCreditorEmpty) {
                builder.append(
                        " and costInv.id in (SELECT id from ServiceProviderInvoice spi where spi.serviceProvider in (:suppliers)");
                if (!isStateEmpty)
                    builder.append(" and spi.state in (:states) ");
                builder.append(")");
            } else {
                builder.append(" and costInv.id in (SELECT id from ServiceProviderInvoice)");
            }

        } else if (isTypeEmpty) {
            if (!isCreditorEmpty) {
                builder.append(" and costInv.id in (select ci.id from CommercialInvoice ci where ci.supplier in (:suppliers)) OR ");
                builder.append("costInv.id in (select spi.id from ServiceProviderInvoice spi where spi.serviceProvider in (:suppliers)");
                if (!isStateEmpty)
                    builder.append(" and spi.state in (:states) ");
                builder.append(") OR ");
                builder.append("costInv.id in (select ccn.id from CommercialCreditNote ccn where ccn.supplier in (:suppliers)");
                if (!isStateEmpty)
                    builder.append(" and ccn.state in (:states) ");
                builder.append(") ");
            }
            if (search.getExportParty() != null) {
                builder.append(" and costInv.id in (select ci.id from CommercialInvoice ci where ci.exportParty = :exportParty ");
                if (!isStateEmpty)
                    builder.append(" and costsInvoice.state in (:states) ");
                builder.append(") OR ");
                builder.append("costInv.id in (select ccn.id from CommercialCreditNote ccn where ccn.exportParty = :exportParty");
                if (!isStateEmpty)
                    builder.append(" and ccn.state in (:states) ");
                builder.append(") ");
            }
        }

        if (!organisationalUnits.isEmpty()) {
            builder.append("""
                     and (select count(*) from Shipment s left join s.consignments as cons
                     left join cons.orders as ord where ord.organisationalUnit in (:organisationalUnit)
                     and costInv.shipment.id = s.id) > 0
                    """);
        }

        if (search.getAdditionalReference() != null) {
            builder.append(" and lower(costInv.additionalReference) like lower(:additionalReference) ");
        }

        if (!isEoaDateFromEmpty && !isEoaDateToEmpty) {
            builder.append(
                    " and costInv.id in (select ci.id from CommercialInvoice ci left join ci.shipment");
            builder.append(" sh where ci.id is not null");
            if (!isEoaDateFromEmpty && !isEoaDateToEmpty) {
                builder.append(" and sh.estimatedArrivalDateAtPlaceOfDischarge between :eoafrom and :eoato ");
            } else if (!isEoaDateToEmpty) {
                builder.append(" and sh.estimatedArrivalDateAtPlaceOfDischarge <= :eoato");
            } else if (!isEoaDateFromEmpty) {
                builder.append(" and sh.estimatedArrivalDateAtPlaceOfDischarge >= :eoafrom");
            }
            builder.append(")");
        }

        if (search.isLinkedToShipment()) {
            builder.append(" and costInv.id in (select ci.id from CommercialInvoice ci left join ci.shipment"
                    + " sh where ci.shipment.id = sh.id)");
        }

        // exclude export's invoices as they are not supported TRM-3064
        //builder.append(" and costInv.id not in (select id from ExportInvoice) ");
        if (search.getDocumentType() == null) {
            builder.append(" and costInv.class != ExportInvoice ");
        }

        if (!count) {
            SearchMetaParams searchMetaParams = search.getSearchMetaParams();
            if (searchMetaParams == null || searchMetaParams.getOrderBy() == null) {
                builder.append("order by costInv.created desc");
            } else {
                builder.append(String.format("order by costInv.%s %s", searchMetaParams.getOrderBy(),
                        searchMetaParams.isAsc() ? "asc" : "desc"));
            }
        }

        return builder.toString();
    }
}