EstimateCostSummaryRepositoryImpl.java

package com.tradecloud.repository.impl;

import com.tradecloud.domain.base.utils.DateUtils;
import com.tradecloud.domain.costing.Costable;
import com.tradecloud.domain.costing.clean.CostedBase;
import com.tradecloud.domain.costing.clean.CostedConsignment;
import com.tradecloud.domain.costing.clean.CostingSummarySearch;
import com.tradecloud.domain.costing.clean.EstimateCostSummary;
import com.tradecloud.domain.infrastructure.persistence.CriteriaBuilder;
import com.tradecloud.domain.model.ordermanagement.Consignment;
import com.tradecloud.domain.party.ServiceProvider;
import com.tradecloud.dto.costing.CostCompareSearch;
import com.tradecloud.dto.costing.CostCompareSearchResult;
import com.tradecloud.repository.EstimateCostSummaryRepository;
import org.apache.log4j.Logger;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.query.NativeQuery;
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.*;

@Repository(value = "estimateCostSummaryRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class EstimateCostSummaryRepositoryImpl extends CostingSummaryRepositoryImpl<EstimateCostSummary> implements EstimateCostSummaryRepository {

    private static final long serialVersionUID = 1L;
    private static final Logger log = Logger.getLogger(EstimateCostSummaryRepositoryImpl.class);

    @Override
    public void addSearchRestrictions(DetachedCriteria searchCriteria, CostingSummarySearch costingSummarySearch) {
        Costable originalCostable = costingSummarySearch.getOriginalCostable();
        CriteriaBuilder.addEqRestriction(searchCriteria, "originalCostable", originalCostable);
        if (costingSummarySearch.getFreightForwarder() != null) {
            CriteriaBuilder.addEqRestriction(searchCriteria, "costSummaryAdditionalInfo.freightForwarder",
                    costingSummarySearch.getFreightForwarder());
        }
        if (costingSummarySearch.getCostComparisonSetupId() != null) {
            CriteriaBuilder.addEqRestriction(searchCriteria, "costSummaryAdditionalInfo.costComparisonSetupId",
                    costingSummarySearch.getCostComparisonSetupId());
        }

        if (costingSummarySearch.getPrimaryCosting() != null) {
            CriteriaBuilder.addEqRestriction(searchCriteria, "primaryCosting", costingSummarySearch.getPrimaryCosting());
        }
//        else{
//            CriteriaBuilder.addEqRestriction(searchCriteria, "primaryCosting",true);
//        }
    }

    @Override
    public EstimateCostSummary findByIdWithCostLineCostingCells(long id) {
        List<EstimateCostSummary> costingSummaries = (List<EstimateCostSummary>)
                findByNamedQueryAndNamedParam("findByIdWithCostLineCostingCellsLoaded", "id", id);
        if (!costingSummaries.isEmpty()) {
            return costingSummaries.get(0);
        }
        return null;
    }

    @Override
    public List<EstimateCostSummary> find(CostedConsignment costedConsignment) {
        DetachedCriteria criteria = DetachedCriteria.forClass(EstimateCostSummary.class);

        CriteriaBuilder.addEqRestriction(criteria, "costedConsignment", costedConsignment);

        // return latest created first
        criteria.addOrder(org.hibernate.criterion.Order.desc("created"));

        @SuppressWarnings("unchecked")
        List<EstimateCostSummary> results = criteria.getExecutableCriteria(getSessionCustom()).list();

        return results;
    }

    @Override
    protected String getCostSummarySimpleName() {
        return EstimateCostSummary.class.getSimpleName();
    }

    @Override
    public List<EstimateCostSummary> findByConsignmentId(Long id) {
        DetachedCriteria criteria = DetachedCriteria.forClass(EstimateCostSummary.class);

        CriteriaBuilder.addEqRestriction(criteria, "originalCostable.id", id);
        return criteria.getExecutableCriteria(getSessionCustom()).list();

    }

    @Override
    public List<ServiceProvider> findCostLsps(Consignment consignment) {
        DetachedCriteria criteria = DetachedCriteria.forClass(EstimateCostSummary.class)
                .setProjection(Projections.property("costSummaryAdditionalInfo.freightForwarder"));
        CriteriaBuilder.addEqRestriction(criteria, "originalCostable", consignment);
        return criteria.getExecutableCriteria(getSessionCustom()).list();
    }

    @Override
    public List<Map<String, String>> getCostsPerItem(String orderReference, List<String> costlines) {
        String query = "  select transactionamount,costlinetemplate_code,u.code as orgunit,i.code from costedlineitem cli\n" +
                "  join lineitem i on (cli.lineitem_id=i.id) join organisationalunit u on (u.id=i.organisationalunit_id)\n" +
                "  join orders o on (o.id=i.order_id) join costedlineitem_costlinecostingcell cc on (cc.costedlineitem_id=cli.id)\n" +
                "  join costlinecostingcell cell on (cell.id=cc.costlinecostingcells_id) join costline l on (l.id=cell.costline_id)\n" +
                "  where costlinetemplate_code in (:costlines) and o.orderreference =:orderReference and" +
                " o.state<>'DELETED' ;";
        List<Map<String, String>> results = getCurrentSession()
                .createSQLQuery(query)
                .addScalar("transactionamount", StandardBasicTypes.STRING)
                .addScalar("costlinetemplate_code", StandardBasicTypes.STRING)
                .addScalar("orgunit", StandardBasicTypes.STRING)
                .addScalar("code", StandardBasicTypes.STRING)
                .setParameter("orderReference", orderReference)
                .setParameterList("costlines", costlines)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return results;
    }

    @Override
    public Map<String, List<Map<String, String>>> getCostsPerItemForOrders(Set<String> orderRefs, List<String> costlines) {
        if (orderRefs == null || orderRefs.isEmpty()) {
            return Collections.emptyMap();
        }

        String query = "SELECT o.orderreference as orderReference, " +
                "cell.transactionamount as transactionamount, " +  // 🔧 fixed here
                "l.costlinetemplate_code as costlinetemplate_code, " +
                "u.code as orgunit, " +
                "i.code as code " +
                "FROM costedlineitem cli " +
                "JOIN lineitem i ON cli.lineitem_id = i.id " +
                "JOIN organisationalunit u ON u.id = i.organisationalunit_id " +
                "JOIN orders o ON o.id = i.order_id " +
                "JOIN costedlineitem_costlinecostingcell cc ON cc.costedlineitem_id = cli.id " +
                "JOIN costlinecostingcell cell ON cell.id = cc.costlinecostingcells_id " +
                "JOIN costline l ON l.id = cell.costline_id " +
                "WHERE l.costlinetemplate_code IN (:costlines) " +
                "AND o.orderreference IN (:orderRefs) " +
                "AND o.state <> 'DELETED'";

        List<Map<String, Object>> rawResults = getCurrentSession()
                .createSQLQuery(query)
                .addScalar("orderReference", StandardBasicTypes.STRING)
                .addScalar("transactionamount", StandardBasicTypes.STRING)
                .addScalar("costlinetemplate_code", StandardBasicTypes.STRING)
                .addScalar("orgunit", StandardBasicTypes.STRING)
                .addScalar("code", StandardBasicTypes.STRING)
                .setParameterList("orderRefs", orderRefs)
                .setParameterList("costlines", costlines)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();

        Map<String, List<Map<String, String>>> resultMap = new HashMap<>();
        for (Map<String, Object> row : rawResults) {
            String orderRef = (String) row.get("orderReference");
            Map<String, String> cleanRow = new HashMap<>();
            for (Map.Entry<String, Object> entry : row.entrySet()) {
                if (!"orderReference".equals(entry.getKey())) {
                    cleanRow.put(entry.getKey(), entry.getValue() != null ? entry.getValue().toString() : null);
                }
            }
            resultMap.computeIfAbsent(orderRef, k -> new ArrayList<>()).add(cleanRow);
        }

        return resultMap;
    }

    @Override
    public <T extends CostedBase> List<T> findPrimaryCostedEntity(Class<T> c, String number) {
        List list =
                getCurrentSession().createCriteria(c)
                        .add(Restrictions.eq("number", number).ignoreCase())
                        .list();
        return list;
    }

    @Override
    public List<CostCompareSearchResult> generateCostComparisonReportList(CostCompareSearch search) {
        StringBuilder queryB = new StringBuilder("select c.id as consignmentId,s.id as summaryId, innerorder.finalisedDate, " +
                "fd.name as finalDestination,pol.name as portOfLoading,pod.name as portOfDischarge," +
                "ofw.name as freightForwarder,otp.name as transporter,\n" +
                "oca.name as clearingAgent ,o.orderreference,c.reference,s.primarycosting,\n" +
                getAmountFields() +
                " from orders o join consignment c on (c.id=o.consignment_id) join estimatecostsummary s on  (c.id=s.originalcostable_id and " +
                " s.primarycosting)\n" +
                "join costedconsignment cc on (cc.id=s.costedconsignment_id)\n" +
                "join costedtotals ct on (ct.id=cc.costedtotals_id) join shippinginformation si on (si.id=o.shippinginformation_id)\n" +
                "left join finaldestination fd on ( fd.code=si.finaldestination_code)\n" +
                "left join placeofdischarge pod on (pod.code=si.placeofdischarge_code)\n" +
                "left join  placeofloading pol on (pol.code=si.placeofloading_code)\n" +
                "left join serviceprovider ofw on (ofw.id=si.freightforwarder_id)\n" +
                "left join serviceprovider otp on (otp.id=si.transporter_id)\n" +
                "left join serviceprovider oca on (oca.id=si.clearingagent_id)\n"
        );
        queryB.append(" join (select  io.id, max(ioee.created) as finalisedDate from orders io  " +
                "join shippinginformation isi on (isi.id=io.shippinginformation_id) "
//                    +"join consignment ic on (ic.id=ioconsignment._id) "
                + "left join finaldestination ifd on ( ifd.code=isi.finaldestination_code)\n" +
                "left join placeofdischarge ipod on (ipod.code=isi.placeofdischarge_code)\n" +
                "left join  placeofloading ipol on (ipol.code=isi.placeofloading_code)\n");
        queryB.append(" join orders_ordersevent ioe on (ioe.orders_id=io.id) join ordersevent ioee on (ioee.id=ioe.events_id) ");
        queryB.append(" where  ioee.eventtype='FINALISED' and io.state" +
                " in ('SIGNED_OFF','FINALISED','BOOKED_IN','FREIGHT_RECEIVED','STOCK_RECEIVED'," +
                "'TOLERANCE_EXCEEDED','AWAITING_LSP_SIGNOFF','SHIPMENT_CREATED','CANCEL_BOOKING','STOCK_PARTIALLY_RECEIVED') ");
        if (search.getPlaceOfDischarge() != null) {
            queryB.append(" and ipod.code=:pod");
        }

        if (search.getPlaceOfLoading() != null) {
            queryB.append(" and ipol.code=:pol");
        }

        if (search.getFinalDestination() != null) {
            queryB.append(" and ifd.code=:fd");
        }
        queryB.append("  group by io.id having io.id<>0 ");
        if (search.getFinalisedDateRange() != null && search.getFinalisedDateRange().getFrom() != null) {
            queryB.append(" and max(ioee.created)>=:fromDate ");
        }
        if (search.getFinalisedDateRange() != null && search.getFinalisedDateRange().getTo() != null) {
            queryB.append(" and max(ioee.created)<=:toDate ");
        }
        queryB.append(" ) as innerorder on (innerorder.id=o.id)  where s.primarycosting order by  finalisedDate desc ");


        NativeQuery query = getSessionCustom().createNativeQuery(queryB.toString());
        if (search.getPlaceOfDischarge() != null) {
            query.setParameter("pod", search.getPlaceOfDischarge().getCode());
        }

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

        if (search.getFinalDestination() != null) {
            query.setParameter("fd", search.getFinalDestination().getCode());
        }
        if (search.getFinalisedDateRange() != null && search.getFinalisedDateRange().getFrom() != null) {
            DateUtils.setToStartOfDay(search.getFinalisedDateRange().getFrom());
            query.setParameter("fromDate", search.getFinalisedDateRange().getFrom());
        }
        if (search.getFinalisedDateRange() != null && search.getFinalisedDateRange().getTo() != null) {
            DateUtils.setToEndOfDay(search.getFinalisedDateRange().getTo());
            query.setParameter("toDate", search.getFinalisedDateRange().getTo());
        }
        query.addScalar("portOfLoading", StandardBasicTypes.STRING)
                .addScalar("portOfDischarge", StandardBasicTypes.STRING)
                .addScalar("finalDestination", StandardBasicTypes.STRING)
                .addScalar("reference", StandardBasicTypes.STRING)
                .addScalar("transporter", StandardBasicTypes.STRING)
                .addScalar("freightForwarder", StandardBasicTypes.STRING)
                .addScalar("clearingAgent", StandardBasicTypes.STRING)
                .addScalar("freightAmount", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("clearingAmount", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("forwardingAmount", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("customsAmount", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("primaryCosting", StandardBasicTypes.BOOLEAN)
                .addScalar("finalisedDate", StandardBasicTypes.DATE)
                .addScalar("summaryId", StandardBasicTypes.LONG)
                .addScalar("consignmentId", StandardBasicTypes.LONG)

                .setResultTransformer(Transformers.aliasToBean(CostCompareSearchResult.class));

        return query.list();
    }

    private String getAmountFields() {
        return "(select max(value) from costedtotals_totalimportcostexvatatforward where costedtotals_id =ct.id and name='FREIGHT')" +
                " as FREIGHTAMOUNT," +
                "(select max(value) from costedtotals_totalimportcostexvatatforward where costedtotals_id =ct.id and name='CLEARING') " +
                "as CLEARINGAMOUNT," +
                "(select max(value) from costedtotals_totalimportcostexvatatforward where costedtotals_id =ct.id and name='FORWARDING') " +
                "as FORWARDINGAMOUNT," +
                "(select max(value+ct.totalVat) from costedtotals c join  costedtotals_totalimportcostexvatatforward t on (t.costedtotals_id=c.id)" +
                "where  c.id =ct.id and name='CUSTOMS') as CUSTOMSAMOUNT";
    }

    @Override
    public List<CostCompareSearchResult> findSecondaryCostCompare(Set<Long> consignments) {
        String queryB = "select s.reference as costReference, " +
                "(sfw.costcompareonly or stp.costcompareonly or sca.costcompareonly) as  useCompareRatesOnly," +
                " c.id as consignmentId,s.id as summaryId,  " +
                "sfw.name as freightForwarder,stp.name as transporter,\n" +
                "sca.name as clearingAgent ,c.reference,s.primarycosting,\n" +
                getAmountFields() +
                " from orders o join consignment c on (c.id=o.consignment_id) join estimatecostsummary s " +
                "on  (c.id=s.originalcostable_id)\n" +
                "join costedconsignment cc on (cc.id=s.costedconsignment_id)\n" +
                "join costedtotals ct on (ct.id=cc.costedtotals_id) " +
                " left join serviceprovider sfw on (sfw.id=s.freightforwarder_id)\n" +
                " left join serviceprovider stp on (stp.id=s.transporter_id)\n" +
                " left join serviceprovider sca on (sca.id=s.clearingagent_id) where s.primarycosting='f'" +
//                " and costcomparisonsetupid=:costcomparisonsetupid " +
                " and s.originalcostable_id in (:consignments) ";
        NativeQuery query = getSessionCustom().createNativeQuery(queryB);
//            query.setParameter("costcomparisonsetupid",costingComparisonSetup.getId());
        query.setParameterList("consignments", consignments);
        query.addScalar("reference", StandardBasicTypes.STRING)
                .addScalar("transporter", StandardBasicTypes.STRING)
                .addScalar("freightForwarder", StandardBasicTypes.STRING)
                .addScalar("clearingAgent", StandardBasicTypes.STRING)
                .addScalar("costReference", StandardBasicTypes.STRING)
                .addScalar("freightAmount", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("clearingAmount", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("forwardingAmount", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("customsAmount", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("primaryCosting", StandardBasicTypes.BOOLEAN)
                .addScalar("summaryId", StandardBasicTypes.LONG)
                .addScalar("consignmentId", StandardBasicTypes.LONG)
                .addScalar("useCompareRatesOnly", StandardBasicTypes.BOOLEAN)

                .setResultTransformer(Transformers.aliasToBean(CostCompareSearchResult.class));

        return query.list();
    }

    @Override
    public List<Object[]> getOrderZeroClcREquiredLineDetails(Long consignmentId) {
        String sql = """
                    SELECT co.reference, t.name, t.costgroup
                    FROM orders o
                    JOIN costedorder co ON (co.reference = o.orderreference AND co.number = o.number)
                    JOIN costedorder_costlinecostingcell c ON (c.costedorder_id = co.id)
                    JOIN costlinecostingcell clc ON (clc.id = c.costlinecostingcells_id)
                    JOIN costline l ON (l.id = clc.costline_id)
                    JOIN costlinetemplate t ON (l.costlinetemplate_code = t.code)
                    WHERE o.consignment_id = :consignmentId
                    AND l.clcminimumcostrequired = 't'
                    AND clc.transactionamount = 0 
                    AND o.integrated
                """;

        NativeQuery query = getSessionCustom().createNativeQuery(sql);
        query.setParameter("consignmentId", consignmentId);
        return query.getResultList();
    }
}