RequestForQuoteRepositoryImpl.java

package com.tradecloud.repository.impl;

import com.tradecloud.domain.base.utils.DateRange;
import com.tradecloud.domain.base.utils.DateUtils;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.domain.model.requestforquote.*;
import com.tradecloud.dto.base.IdReferenceDto;
import com.tradecloud.dto.order.OrderNotConsignedDTO;
import com.tradecloud.dto.requestforquote.RequestForQuoteSearch;
import com.tradecloud.repository.RequestForQuoteRepository;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
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 javax.persistence.TypedQuery;
import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Set;

@Repository(value = "requestForQuoteRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class RequestForQuoteRepositoryImpl extends RepositoryBaseImpl<RequestForQuote, RequestForQuoteSearch> implements RequestForQuoteRepository {

    private final static String REQUIRED_CLOSURE_DATE = "requiredClosureDate";
    private final static String REQUIRED_BRANCH_SUBMIT_DATE = "branchSubmissionDate";

    private final static String NAME = "name";

    private final static String STATE = "state";

    @Override
          public long count(RequestForQuoteSearch search) {
            CriteriaBuilder cb = getSession().getCriteriaBuilder();
            CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
            Root<RequestForQuote> root = countQuery.from(RequestForQuote.class);

            List<Predicate> predicates = getPredicates(search, root, cb);
            countQuery.select(cb.countDistinct(root.get("id")));
            countQuery.where(predicates.toArray(new Predicate[0]));
            return getSession().createQuery(countQuery).getSingleResult();

    }

    @Override
    public void removeRFQSupplierOrderLink(Long id) {
        Query query = getSessionCustom().createQuery("update RFQOrganisationalUnitSupplier set orderId= null " +
                "where orderId= :orderId");
        query.setParameter("orderId", id);
        query.executeUpdate();
    }

    @Override
    public List<RequestForQuote> search(RequestForQuoteSearch search) {
        return requestForQuoteList(search, false);
    }

    public List<RequestForQuote> requestForQuoteList(RequestForQuoteSearch search, boolean count) {
        javax.persistence.criteria.CriteriaBuilder cb = getSession().getCriteriaBuilder();

        // Main query: select from RequestForQuote
        javax.persistence.criteria.CriteriaQuery<RequestForQuote> mainQuery = cb.createQuery(RequestForQuote.class);
        Root<RequestForQuote> mainRoot = mainQuery.from(RequestForQuote.class);
        mainQuery.select(mainRoot);

        // Subquery for filtering IDs
        Subquery<Long> subquery = mainQuery.subquery(Long.class);
        Root<RequestForQuote> subRoot = subquery.from(RequestForQuote.class);
        subquery.select(subRoot.get("id"));

        List<Predicate> predicates = getPredicates(search, subRoot, cb);

        // Apply all predicates to subquery
        subquery.where(cb.and(predicates.toArray(new Predicate[0])));

        // Final outer query: only select if ID is in subquery
        mainQuery.where(mainRoot.get("id").in(subquery));
        mainQuery.orderBy(cb.desc(mainRoot.get(REQUIRED_CLOSURE_DATE)));

        // Apply pagination
        TypedQuery<RequestForQuote> query = getSession().createQuery(mainQuery);

        SearchMetaParams meta = search.getSearchMetaParams();
        if (!count && meta != null) {
            if (meta.getRowCount() > -1) {
                query.setMaxResults(meta.getRowCount());
            }
            if (meta.getRowIndex() > -1) {
                query.setFirstResult(meta.getRowIndex());
            }
        }

        return query.getResultList();
    }

    private List<Predicate> getPredicates(RequestForQuoteSearch search, Root<RequestForQuote> subRoot, CriteriaBuilder cb) {
        List<Predicate> predicates = new ArrayList<>();

        // Filter by user's organisational units
        if (search.isFilteredByUserOrg()) {
            Set<OrganisationalUnit> units = getUserOrganisationalUnits();
            if (!units.isEmpty()) {
                Join<RequestForQuote, OrganisationalUnit> join = subRoot.join("organisationalUnits");
                predicates.add(join.in(units));
            }
        } else if (search.getOrganisationalUnit() != null) {
            OrganisationalUnit org = search.getOrganisationalUnit();
            String tier = org.getTier().getName();

            Join<RequestForQuote, OrganisationalUnit> join = subRoot.join("organisationalUnits");

            if (!"HoldingCompany".equals(tier)) {
                if ("BUSINESS_UNIT".equals(tier)) {
                    predicates.add(cb.equal(join.get("parent"), org));
                } else {
                    predicates.add(cb.equal(join, org));
                }
            }
        }

        // Name filter
        if (search.getName() != null) {
            predicates.add(cb.like(cb.lower(subRoot.get("name")), "%" + search.getName().toLowerCase() + "%"));
        }

        // State filter
        if (search.getState() != null) {
            predicates.add(cb.equal(subRoot.get("state"), search.getState()));
        } else {
            List<RequestForQuoteState> excludedStates = new ArrayList<>();
            excludedStates.add(RequestForQuoteState.DELETED);

            if (!search.isIncludeAllOrdersCreatedState()) {
                excludedStates.add(RequestForQuoteState.ALL_ORDERS_CREATED);
            }

            predicates.add(cb.not(subRoot.get("state").in(excludedStates)));
        }

        if (search.getFinalDestination() != null|| search.getFinalDestinations()!=null) {
            Join<RequestForQuote, RFQStyle> stockLevelStyleJoin = subRoot.join("rfqStyles");
            Join<RFQStyle, RFQRegion> rfqRegionJoin = stockLevelStyleJoin.join("rfqRegions");
            Join<RFQRegion, RFQProduct> stockLevelStyleProductJoin = rfqRegionJoin.join("rfqProducts");
            if(search.getFinalDestination()!=null) {
                predicates.add(cb.equal(stockLevelStyleProductJoin.get("finalDestination"), search.getFinalDestination()));
            } else if (search.getFinalDestinations()!=null && !search.getFinalDestinations().isEmpty()) {
                predicates.add(stockLevelStyleProductJoin.get("finalDestination").in(search.getFinalDestinations()));
            }
        }

        // Closure date range
        DateRange closureDateRange = search.getRequiredClosureDate();
        if (closureDateRange != null) {
            if (closureDateRange.getFrom() != null) {
                predicates.add(cb.greaterThanOrEqualTo(subRoot.get(REQUIRED_CLOSURE_DATE),
                        DateUtils.getStartOfDay(closureDateRange.getFrom())));
            }
            if (closureDateRange.getTo() != null) {
                predicates.add(cb.lessThanOrEqualTo(subRoot.get(REQUIRED_CLOSURE_DATE),
                        DateUtils.getEndOfDay(closureDateRange.getTo())));
            }
        }
        // Closure date range
        DateRange requiredSubmissionDate = search.getRequiredSubmissionDate();
        if (requiredSubmissionDate != null) {
            if (requiredSubmissionDate.getFrom() != null) {
                predicates.add(cb.greaterThanOrEqualTo(subRoot.get(REQUIRED_BRANCH_SUBMIT_DATE),
                        DateUtils.getStartOfDay(requiredSubmissionDate.getFrom())));
            }
            if (requiredSubmissionDate.getTo() != null) {
                predicates.add(cb.lessThanOrEqualTo(subRoot.get(REQUIRED_BRANCH_SUBMIT_DATE),
                        DateUtils.getEndOfDay(requiredSubmissionDate.getTo())));
            }
        }

        return predicates;
    }

    public RequestForQuote findRFQOrgByOrderId(Long orderID) {
        Session session = getSession();
        Criteria criteria;
        RFQOrganisationalUnitSupplier rfqOrganisationalUnitSupplier = null;

        if (orderID != null) {
            criteria = session.createCriteria(RFQOrganisationalUnitSupplier.class);
            criteria.add(Restrictions.eq("orderId", orderID));
            rfqOrganisationalUnitSupplier = (RFQOrganisationalUnitSupplier) criteria.uniqueResult();
            return rfqOrganisationalUnitSupplier != null ?
                    rfqOrganisationalUnitSupplier.getRequestForQuote() : null;
        } else {
            return null;
        }
    }

    @Override
    public List<OrderNotConsignedDTO> findRfqOrders(long requestForQuoteId) {
        String sql = """
    SELECT 
        o.id AS id,
        o.orderReference AS orderReference,
        s.name AS supplierName,
        fd.name AS finalDestination,
        o.number AS number,
        (
            SELECT SUM(i.unitQuantity)
            FROM lineitem i
            WHERE i.order_id = o.id
        ) AS orderQuantity,
        o.totalInvoiceValue as totalInvoiceValue
    FROM rfqorder rfqo 
     JOIN rfqorderregion r on (r.rfqorder_id=rfqo.id)
    JOIN orders o ON r.orderId = o.id   
    JOIN OrganisationalUnitSupplier ous ON rfqo.organisationalUnitSupplier_id = ous.id
    JOIN Supplier s ON ous.supplier_id = s.id
    JOIN shippinginformation si on si.id=o.shippinginformation_id
    JOIN FinalDestination fd ON si.finaldestination_code = fd.code
    WHERE rfqo.requestForQuote_id = :requestForQuoteId
""";

        Query query = getSession().createSQLQuery(sql)
                .addScalar("id", StandardBasicTypes.LONG)
                .addScalar("orderReference", StandardBasicTypes.STRING)
                .addScalar("supplierName", StandardBasicTypes.STRING)
                .addScalar("finalDestination", StandardBasicTypes.STRING)
                .addScalar("orderQuantity", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("totalInvoiceValue", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("number", StandardBasicTypes.STRING)
                .setParameter("requestForQuoteId", requestForQuoteId)
                .setResultTransformer(Transformers.aliasToBean(OrderNotConsignedDTO.class));

        return query.list();
    }

    @Override
    public IdReferenceDto orderRequestForQuoteDetails(Long orderId) {
        String sql = """
    SELECT q.id, q.name as reference
    FROM rfqorder o
    JOIN rfqorderregion r ON r.rfqorder_id = o.id
    JOIN requestforquote q ON q.id = o.requestforquote_id
    WHERE r.orderid = :orderId
    """;

        Query query = getSession().createSQLQuery(sql).setParameter("orderId",orderId)
                .addScalar("id", StandardBasicTypes.BIG_INTEGER)
                .addScalar("reference", StandardBasicTypes.STRING)
                .setResultTransformer(Transformers.aliasToBean(IdReferenceDto.class));

        return (IdReferenceDto) query.uniqueResult();
    }

    @Override
    public void updateRowQuantitiesPatch(StockLevelRow levelRow) {
        if(levelRow instanceof RFQStyle rfqStyle){
            updateStockLevelRow(rfqStyle, "RFQStyle");
        }
        if(levelRow instanceof RFQRegion rfqStyle){
            updateStockLevelRow(rfqStyle, "RFQRegion");
        }
        if(levelRow instanceof RFQProduct rfqStyle){
            updateStockLevelRow(rfqStyle, "RFQProduct");
        }
    }

    private void updateStockLevelRow(StockLevelRow row, String entityName) {

        boolean isProductOrRegion = row.isProduct()||row.isRegion();

        StringBuilder jpql = new StringBuilder();
        jpql.append("UPDATE ").append(entityName).append(" s SET ")
                .append("s.originalQuantity = :originalQuantity, ")
                .append("s.branchQuantity = :branchQuantity, ")
                .append("s.rfqQuantity = :rfqQuantity, ")
                .append("s.reason = :reason, ")
                .append("s.updated = :updated ");

        if (isProductOrRegion) {
            jpql.append(", s.originalQuantitySelected = :originalQtySel ")
                    .append(", s.branchQuantitySelected = :branchQtySel ");
        }

        jpql.append(" WHERE s.id = :id");

        var query = getSessionCustom().createQuery(jpql.toString());

        query.setParameter("originalQuantity", row.getOriginalQuantity());
        query.setParameter("branchQuantity", row.getBranchQuantity());
        query.setParameter("rfqQuantity", row.getRfqQuantity());
        query.setParameter("reason", row.getReason());
        query.setParameter("updated", new Date());
        query.setParameter("id", row.getId());

        if (isProductOrRegion) {
            query.setParameter("originalQtySel", row.isOriginalQuantitySelected());
            query.setParameter("branchQtySel", row.isBranchQuantitySelected());
        }

        query.executeUpdate();
    }

    @Override
    public void makeNotSaved(Long rfqId) {
        String jpql = "UPDATE  RequestForQuote rfq SET rfq.notSavedChanges =:notSavedChanges  WHERE rfq.id = :id";

        org.hibernate.query.Query<?> query = getSessionCustom().createQuery(jpql);
        query.setParameter("notSavedChanges", true);
        query.setParameter("id", rfqId);
        query.executeUpdate();
        getSessionCustom().clear();
    }
}