ShipmentWorkLoadRuleProcessing.java

package com.tradecloud.repository.workload.impl;

import com.tradecloud.domain.party.ServiceProviderType;
import com.tradecloud.domain.workload.AttributeType;
import com.tradecloud.domain.workload.DateFilter;
import com.tradecloud.domain.workload.WorkLoadRule;
import org.hibernate.Session;

import java.util.Map;

public class ShipmentWorkLoadRuleProcessing extends RuleProcessor{

    public static final Map<DateFilter, String> dateFilterMappingsRaw = Map.of(
            DateFilter.ACTUAL_DEPARTURE_DATE, "s.actualDepartureDate",
            DateFilter.EST_ARRIVAL_AT_POD, "s.estimatedarrivaldateatplaceofdischarge",
            DateFilter.EST_ARRIVAL_AT_FD, "c.etaatfinaldestination",
            DateFilter.MASTER_BILL_OF_LADING_DATE, "a.masterBillOfLadingDate",
            DateFilter.HOUSE_BILL_OF_LADING_DATE, "a.billOfLadingDate",
            DateFilter.SCHEDULED_DEPARTURE_DATE, "s.scheduledDepartureDate"
    );

    private static final Map<AttributeType, String> ATTRIBUTE_TYPE_STRING_MAP = Map.of(
            AttributeType.NO_COMMERCIAL_INVOICE, """
        AND NOT EXISTS (
            SELECT 1 FROM costsinvoice inv
            JOIN commercialinvoice ci ON ci.id = inv.id
            WHERE inv.shipment_id = s.id AND inv.state <> 'DELETED'
        )""",

            AttributeType.COMMERCIAL_INVOICES_NOT_SIGNED_OFF, """
        AND EXISTS (
            SELECT 1 FROM costsinvoice inv
            JOIN commercialinvoice ci ON ci.id = inv.id
            WHERE inv.shipment_id = s.id AND inv.state = 'FINALISED'
        )""",

            AttributeType.NO_SERVICE_PROVIDER_INVOICE, """
        AND NOT EXISTS (
            SELECT 1 FROM costsinvoice inv
            JOIN serviceproviderinvoice spi ON spi.id = inv.id
            WHERE inv.shipment_id = s.id AND inv.state <> 'DELETED'
        )""",

            AttributeType.SERVICE_PROVIDER_INVOICES_NOT_SIGNED_OFF, """
        AND EXISTS (
            SELECT 1 FROM costsinvoice inv
            JOIN serviceproviderinvoice spi ON spi.id = inv.id
            WHERE inv.shipment_id = s.id AND inv.state = 'FINALISED'
        )""",

            AttributeType.NO_CLEARING_INSTRUCTION, """
        AND NOT EXISTS (
            SELECT 1 FROM clearinginstruction inv
            WHERE inv.shipment_id = s.id AND inv.status <> 'DELETED'
        )""",
       AttributeType.COMMERCIAL_NOT_SETTLED, """
               AND NOT EXISTS (select 1  from commercialinvoice ci  
                    join plannedsettlement p on (p.invoice_id=ci.id) join costsinvoice inv on (inv.id=ci.id) 
                    join shipment s2 on (s2.id=inv.shipment_id) where inv.shipment_id = s.id AND (paymentstate in ('SETTLED','OVER_PAID')))
                    """

    );

    private static final Map<DateFilter, String> DATE_FILTER_JOINS = Map.of(
            DateFilter.EST_ARRIVAL_AT_FD, " JOIN container c ON c.shipment_id = s.id",
            DateFilter.MASTER_BILL_OF_LADING_DATE, " JOIN seashipment a ON a.id = s.id",
            DateFilter.HOUSE_BILL_OF_LADING_DATE, " JOIN seashipment a ON a.id = s.id"
    );



    public ShipmentWorkLoadRuleProcessing(Session session) {
        super(session);
    }

    @Override
    protected String getBaseTable() {
        return "shipment s";
    }

    @Override
    protected String getExcludeFilter() {
        return " AND s.state not in ('COMPLETE','SIGNED_OFF','DELETED','STOCK_PARTIALLY_RECEIVED','STOCK_FULLY_RECEIVED') " +
                "AND EXISTS (select c.id  from consignment c where c.shipment_id=s.id)";
    }

    @Override
    protected String getQuery() {
        return "SELECT s.id, s.reference, (select ous.supplier_id from orders o join consignment c on (c.id=o.consignment_id) " +
                "join purchaseorder p on (p.id=o.id) join organisationalunitsupplier ous on (ous.id=p.supplier_id) where c.shipment_id=s.id " +
                "order by addedToShipmentDate limit 1) as supplier,s.number as number,date(%s) as ruledate,s.state  FROM %s %s WHERE %s ";
    }

    @Override
    protected Map<DateFilter, String> dateFilterStringMap() {
        return dateFilterMappingsRaw;
    }

    @Override
    protected String addServiceProvideFilter(WorkLoadRule rule, String sql) {
        if(rule.getServiceProviderType()!=null){
            switch (rule.getServiceProviderType()){
                case FREIGHT_FORWARDER -> {
                    sql +=" AND si.freightforwarder_id=:sp_id";
                }
                case CLEARING_AGENT ->
                        sql +=" AND si.clearingagent_id=:sp_id";
                case CARGO_CARRIER ->
                        sql +=" AND a.carrier_id=:sp_id";
                default -> throw new IllegalArgumentException("cannot process for shipment service provider type of:"
                        +rule.getServiceProviderType().getDescription());
            }
        }
        return sql;
    }

    @Override
    protected String filterByRuleAttribute(WorkLoadRule rule, String sql) {
        return sql + ATTRIBUTE_TYPE_STRING_MAP.getOrDefault(rule.getAttribute(), "");
    }

    @Override
    protected String getJoinClause(WorkLoadRule rule) {
        StringBuilder joinClause = new StringBuilder();

        if (rule.getDateFilter() != null) {
            joinClause.append(DATE_FILTER_JOINS.getOrDefault(rule.getDateFilter(), ""));
        }

        if (rule.getServiceProviderType() == ServiceProviderType.CARGO_CARRIER) {
            joinClause.append(" JOIN seashipment a ON a.id = s.id");
        }
            joinClause.append(" JOIN shipmentshippinginfo si ON si.id = s.shippinginfo_id");

        return joinClause.toString();
    }

    @Override
    protected String addShippingModeFilter(WorkLoadRule rule, String sql) {
        sql += " AND si.shippingmode=:shippingMode";
        return sql;
    }

    @Override
    protected String getPriorityFieldsSql(WorkLoadRule rule) {
        return  "select %s as priorityDate from shipment s "+DATE_FILTER_JOINS.getOrDefault(rule.getDateFilter(), "")
                +" where s.id=:entityId";

    }

    @Override
    protected String addPaymentTermFilter(WorkLoadRule rule, String sql) {
        if (rule.getPaymentTerm() != null) {
            sql += " AND exists (select 1 from orders o join consignment c on (c.id=o.consignment_id) " +
                    "where c.shipment_id=s.id and o.paymentTerm_code =:paymentTerm )";
            return sql;
        }
        return sql;
    }

    @Override
    protected String addOrderStatesFilter(WorkLoadRule rule, String sql) {
        return sql;
    }
}