GoodsReceivedReceiptRepositoryImpl.java

package com.tradecloud.repository.goodsreceivedreceipt.impl;

import com.tradecloud.domain.model.goodsreceivedreceipt.GoodsReceivedReceipt;
import com.tradecloud.domain.model.goodsreceivedreceipt.GoodsReceivedReceiptItem;
import com.tradecloud.domain.model.ordermanagement.Order;
import com.tradecloud.dto.goodsreceivedreceipt.GoodsReceivedReceiptResultDTO;
import com.tradecloud.dto.goodsreceivedreceipt.GoodsReceivedReceiptSearch;
import com.tradecloud.dto.goodsreceivedreceipt.GrrValidationDTO;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import com.tradecloud.repository.goodsreceivedreceipt.GoodsReceivedReceiptRepository;
import com.tradecloud.repository.goodsreceivedreceipt.GrrDTOTransformer;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
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.math.BigDecimal;
import java.math.BigInteger;
import java.util.Date;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

@Repository(value = "goodsReceivedReceiptRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class GoodsReceivedReceiptRepositoryImpl extends RepositoryBaseImpl<GoodsReceivedReceipt, GoodsReceivedReceiptSearch>
        implements GoodsReceivedReceiptRepository {

    private final static String fromQuery = """                
            FROM goodsReceivedReceiptItem grri
             join goodsreceivedreceipt_goodsreceivedreceiptitem jj on (jj.items_id = grri.id)
             join goodsreceivedreceipt grv on (grv.id=jj.goodsreceivedreceipt_id)
             join orders o on (o.id=grv.order_id)
             join consignment c on (c.id=o.consignment_id)
             join shipment s on (s.id=c.shipment_id)
             join organisationalunit u on (u.id=o.organisationalunit_id)\s
             left join (select max(e.createdatetime) as shipmentsignedOffDate, max(sse.shipment_id) as  shipment_id  
             from shipment_shipmentevent sse join shipmentevent e on (e.id=sse.events_id) where eventtype='SIGNED_OFF'
             group by sse.shipment_id) event on (event.shipment_id=s.id)
             where jj.goodsreceivedreceipt_id is not null
               {predicates} group by s.id,o.id,jj.goodsreceivedreceipt_id
              order by jj.goodsreceivedreceipt_id desc""";

    private final static String fieldsQuery = """
                        SELECT max(u.name) as orgName,
                         max(grv.receiptLevel) as receiptLevel,
                         max(o.state) as orderState,
                         max(o.orderreference) as orderReference,
                         max(s.number) as shipmentNumber,
                         (SELECT max(created) FROM shipmentevent e left join shipment_shipmentevent se on se.events_id = e.id
                          WHERE se.shipment_id = s.id and e.eventtype = 'SIGNED_OFF') as shipmentsignedOffDate,
                         max(s.reference) as shipmentReference,
                         max(grv.receiptDate) as receiptDate,max(o.id) as orderId,
                         max(grv.reference) as reference,
                         SUM(grri.localUnitCost * grri.quantity) as totalGRNCost,
                         sum(grri.quantity) as totalGRNQuantity 
            """;

    @Override
    public List<GoodsReceivedReceipt> search(GoodsReceivedReceiptSearch search) {
        //search2 being called from service
        return null;
    }

    @Override
    public long count(GoodsReceivedReceiptSearch search) {
        NativeQuery nativeQuery = getNativeQuery(search, true);
        return ((BigInteger) nativeQuery.uniqueResult()).longValue();
    }

    @Override
    public List<GoodsReceivedReceiptResultDTO> search2(GoodsReceivedReceiptSearch search) {
        NativeQuery nativeQuery = getNativeQuery(search, false);

        return nativeQuery
                .addScalar("orderId", StandardBasicTypes.LONG)
                .addScalar("reference", StandardBasicTypes.STRING)
                .addScalar("orderReference", StandardBasicTypes.STRING)
                .addScalar("orgName", StandardBasicTypes.STRING)
                .addScalar("receiptLevel", StandardBasicTypes.STRING)
                .addScalar("orderState", StandardBasicTypes.STRING)
                .addScalar("shipmentNumber", StandardBasicTypes.STRING)
                .addScalar("shipmentReference", StandardBasicTypes.STRING)
                .addScalar("receiptDate", StandardBasicTypes.DATE)
                .addScalar("shipmentsignedOffDate", StandardBasicTypes.DATE)
                .addScalar("totalGRNCost", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("totalGRNQuantity", StandardBasicTypes.BIG_DECIMAL)
                .setResultTransformer(Transformers.aliasToBean(GoodsReceivedReceiptResultDTO.class)).list();
    }

    private NativeQuery getNativeQuery(GoodsReceivedReceiptSearch search, boolean count) {
        StringBuilder sb = new StringBuilder();

        if (count) {
            sb.append("select count(*) from (").append("select o.id ").append(fromQuery).append(") as result");
        } else {

            sb.append(fieldsQuery).append(fromQuery);
        }
        String predicates = buildPredicate(search);

        SearchMetaParams searchMetaParams = search.getSearchMetaParams();
//        if (search.getShipmentSignOffDateRange().getFrom() != null || search.getShipmentSignOffDateRange().getTo() != null)
//            sb.append(" where shipmentsignedoffdate is not null ");
        if (!count) {
            if (searchMetaParams != null && searchMetaParams.getRowCount() != -1)
                sb.append(" limit " + searchMetaParams.getRowCount());
            if (searchMetaParams != null && searchMetaParams.getRowIndex() != -1)
                sb.append(" offset " + searchMetaParams.getRowIndex());
        }
        sb.append(";");

        String query = sb.toString();
        query = query.replace("{predicates}", predicates);
        NativeQuery nativeQuery = getSession().createNativeQuery(query);
        setParameters(search, nativeQuery);
        return nativeQuery;
    }

    private String buildPredicate(GoodsReceivedReceiptSearch search) {
        StringBuilder sb = new StringBuilder();
        if (StringUtils.isNotEmpty(search.getReference())) {
            if (search.isUseWildCard()) {
                sb.append(" and grv.reference ilike :reference");
            } else {
                sb.append(" and lower(grv.reference) =:reference ");
            }
        }

        if (StringUtils.isNotEmpty(search.getOrderReference())) {
            sb.append(" and o.orderReference ilike :orderReference");
        }

        if (StringUtils.isNotEmpty(search.getShipmentReference())) {
            sb.append(" and s.reference ilike :shipmentReference");
        }

        filterOrgUnit(search, sb);

        if (StringUtils.isNotEmpty(search.getItemReference())) {
            sb.append(" and grri.reference ilike :itemReference");
        }

        if (StringUtils.isNotEmpty(search.getShipmentNumber())) {
            sb.append(" and s.number ilike :shipmentNumber");
        }

        if (search.getOrderState() != null) {
            sb.append(" and o.state=:state");
        }

        if (search.getGrrReceiptDateRange().getFrom() != null) {
            sb.append(" and grv.receiptDate>=:receiptDateFrom");
        }
        if (search.getGrrReceiptDateRange().getTo() != null) {
            sb.append(" and grv.receiptDate<=:receiptDateTo");
        }
        if (search.getShipmentSignOffDateRange().getFrom() != null || search.getShipmentSignOffDateRange().getTo() != null) {
            sb.append(" and event.shipmentsignedoffdate is not null ");
        }
        if (search.getShipmentSignOffDateRange().getFrom() != null) {
            sb.append(" and event.shipmentsignedoffdate>=:shipmentSignOffDateRangeFrom");

        }
        if (search.getShipmentSignOffDateRange().getTo() != null) {
            sb.append(" and event.shipmentsignedoffdate<=:shipmentSignOffDateRangeTo");
        }

        return sb.toString();
    }

    private void setParameters(GoodsReceivedReceiptSearch search, NativeQuery nativeQuery) {
        if (StringUtils.isNotEmpty(search.getReference())) {
            if (search.isUseWildCard()) {
                nativeQuery.setParameter("reference", "%" + search.getReference() + "%");
            } else {
                nativeQuery.setParameter("reference", search.getReference());
            }
        }

        if (StringUtils.isNotEmpty(search.getOrderReference())) {
            nativeQuery.setParameter("orderReference", "%" + search.getOrderReference() + "%");
        }

        if (StringUtils.isNotEmpty(search.getShipmentReference())) {
            nativeQuery.setParameter("shipmentReference", "%" + search.getShipmentReference() + "%");
        }

        if (CollectionUtils.isNotEmpty(search.getOrganisationalUnits())) {
            nativeQuery.setParameterList("orgListId", search.getOrganisationalUnits().stream()
                    .map(organisationalUnit -> organisationalUnit.getId()).collect(Collectors.toList()));
        } else if (search.isFilteredByUserOrg()) {
            nativeQuery.setParameterList("orgListId", getUserOrganisationalUnits().stream()
                    .map(organisationalUnit -> organisationalUnit.getId()).collect(Collectors.toList()));
        } else if (search.getOrganisationalUnit() != null) {
            nativeQuery.setParameter("orgId", search.getOrganisationalUnit().getId());
        }

        if (StringUtils.isNotEmpty(search.getItemReference())) {
            nativeQuery.setParameter("itemReference", "%" + search.getItemReference() + "%");
        }

        if (StringUtils.isNotEmpty(search.getShipmentNumber())) {
            nativeQuery.setParameter("shipmentNumber", "%" + search.getShipmentNumber() + "%");
        }

        if (search.getOrderState() != null) {
            nativeQuery.setParameter("state", search.getOrderState().name());
        }

        if (search.getGrrReceiptDateRange().getFrom() != null) {
            nativeQuery.setParameter("receiptDateFrom", search.getGrrReceiptDateRange().getFrom());
        }
        if (search.getGrrReceiptDateRange().getTo() != null) {
            nativeQuery.setParameter("receiptDateTo", search.getGrrReceiptDateRange().getTo());
        }

        if (search.getShipmentSignOffDateRange().getFrom() != null) {
            nativeQuery.setParameter("shipmentSignOffDateRangeFrom", search.getShipmentSignOffDateRange().getFromAsStartOfDay());
        }
        if (search.getShipmentSignOffDateRange().getTo() != null) {
            nativeQuery.setParameter("shipmentSignOffDateRangeTo", search.getShipmentSignOffDateRange().getToAsEndOfDay());
        }
    }

    private void filterOrgUnit(GoodsReceivedReceiptSearch search, StringBuilder sb) {
        if (CollectionUtils.isNotEmpty(search.getOrganisationalUnits())) {
            sb.append(" and u.id in (:orgListId) ");
        } else if (search.isFilteredByUserOrg()) {
            sb.append(" and u.id in (:orgListId) ");
        } else if (search.getOrganisationalUnit() != null) {
            sb.append(" and u.id=:orgId");
        }
    }

    @Override
    public List searchGRVItems(Order order, SearchMetaParams metaParams) {
        Query query = getSessionCustom().createQuery(" from GoodsReceivedReceipt as grr join grr.items as item where grr.order.id=:orderId " +
                "order by grr.reference");
        query.setParameter("orderId", order.getId());
        return getQueryList(query, metaParams);
    }

    @Override
    public List<GoodsReceivedReceiptItem> orderGRVItems(Order order) {
        org.hibernate.query.Query query = getSessionCustom().createQuery("select item from GoodsReceivedReceipt as grr join grr.items as item " +
                "where grr.order.id=:orderId order by grr.reference");
        query.setParameter("orderId", order.getId());
        return query.list();
    }

    @Override
    public List<GoodsReceivedReceipt> find(Order order) {
        org.hibernate.query.Query query = getSessionCustom().createQuery("select grr from GoodsReceivedReceipt as grr where grr.order.id=:orderId");
        query.setParameter("orderId", order.getId());
        return query.list();
    }

    @Override
    public long countGRVItems(Order order) {
        Query query = getSessionCustom().createQuery(" select count(*)  from GoodsReceivedReceipt as grr join grr.items as item " +
                "where grr.order.id=:orderId");
        query.setParameter("orderId", order.getId());
        return getQueryCount(query);
    }

    @Override
    public BigDecimal getTotalGRVQuantity(Order order) {
        Query query = getSessionCustom().createQuery(" select sum(item.quantity)  from GoodsReceivedReceipt as grr join grr.items as item " +
                "where grr.order.id=:orderId");
        query.setParameter("orderId", order.getId());
        return (BigDecimal) query.uniqueResult();
    }

    @Override
    public BigDecimal getTotalGRVCost(Order order) {
        Query query = getSessionCustom().createQuery(" select sum(item.quantity*item.localUnitCost)  " +
                "from GoodsReceivedReceipt as grr join grr.items as item " +
                "where grr.order.id=:orderId");
        query.setParameter("orderId", order.getId());
        return (BigDecimal) query.uniqueResult();
    }

    @Override
    public List<Long> findOrderWithoutDutyItem(Long shipmenId) {
        return getSessionCustom().createQuery(" select o.id from GoodsReceivedReceipt g join g.order o join o.consignment " +
                        "c join c.shipment s where s.id=:shipId and syncWithDutyDrawBack= 'f'")
                .setParameter("shipId", shipmenId)
                .list();
    }

    @Override
    public List<Long> findOrderWithin2Years() {
        Date date = org.apache.commons.lang3.time.DateUtils.addYears(new Date(), -2);
        return getSessionCustom().createQuery(" select o.id from GoodsReceivedReceipt g join g.order o join o.consignment " +
                        "c  join c.shipment s where  s.id is not null and g.receiptDate>=:dateRange " +
                        "and syncWithDutyDrawBack= 'f'")
                .setParameter("dateRange", date)
                .list();
    }

    @Override
    public List<GrrValidationDTO> validationValues(String purchaseOrderReference) {
        return getCurrentSession().createNativeQuery("""
                         select o.id as order_id, o.orderreference as order_reference, i.code as item_reference,\s
                         s.reference as shipment_reference,\s
                         i.id as item_id, s.id as shipment_id, grr.reference as grn_reference\s
                                      from orders o\s
                                      left join lineitem i on o.id = i.order_id\s
                                      left join consignment c on o.consignment_id = c.id\s
                                      left join shipment s on c.shipment_id = s.id\s
                                      left join goodsreceivedreceipt grr on o.id = grr.order_id\s
                                      where o.orderreference = :orderReference
                        """).setParameter("orderReference", purchaseOrderReference)
                .unwrap(org.hibernate.query.Query.class)
                .setResultTransformer(new GrrDTOTransformer())
                .getResultList();
    }

    @Override
    public int grnExists(String grnReference) {
        return ((BigInteger) getCurrentSession().createNativeQuery("""
                         select count(*) from goodsreceivedreceipt\s
                                      where reference = :reference
                        """).setParameter("reference", grnReference)
                .unwrap(org.hibernate.query.Query.class)
                .uniqueResult()).intValue();
    }

    @Override
    public List<GoodsReceivedReceiptResultDTO> findActualCosting(Set<Long> orderId) {
        String costingQuery = """
                select round(sum(i.invoiceQuantity*UnitLandedCost),2) as alcCost,max(a.originalid) as originalId,
                sum(i.invoiceQuantity) as invoiceQuantity
                from actuallineitem i join actualorder a on (a.id=i.actualorder_id)
                join actualconsignment ac on (ac.id = a.actualconsignment_id) 
                join actualshipment x on (x.id= ac.actualshipment_id) 
                join actualcostsummary y on (y.actualshipment_id=x.id) 
                where summarycosttype ='ALC' and a.originalid in (:orderIds ) group by a.originalid
                """;
        NativeQuery nativeQuery = getSession().createNativeQuery(costingQuery).setParameter("orderIds", orderId);
        return nativeQuery
                .addScalar("alcCost", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("invoiceQuantity", StandardBasicTypes.BIG_DECIMAL)
                .addScalar("originalId", StandardBasicTypes.LONG)
                .setResultTransformer(Transformers.aliasToBean(GoodsReceivedReceiptResultDTO.class)).list();
    }
}