RemittanceAdviceServiceRepositoryImpl.java

package com.tradecloud.repository.costingdocument.impl;

import com.tradecloud.domain.model.ordermanagement.OrderState;
import com.tradecloud.domain.model.ordermanagement.PurchaseOrder;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.dto.invoice.RemittanceAdviceSearch;
import com.tradecloud.dto.invoice.RemittanceAdviceSearchResult;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import com.tradecloud.repository.costingdocument.RemittanceAdviceRepository;
import org.apache.commons.collections.CollectionUtils;
import org.hibernate.Criteria;
import org.hibernate.criterion.*;
import org.hibernate.query.Query;
import org.hibernate.transform.Transformers;
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.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

@Repository(value = "remittanceAdviceRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class RemittanceAdviceServiceRepositoryImpl extends RepositoryBaseImpl<RemittanceAdviceSearchResult,
        RemittanceAdviceSearch> implements RemittanceAdviceRepository {

    @Override
    public long count(RemittanceAdviceSearch search) {
        DetachedCriteria criteria = getOrdersCriteria(search);
        return getExecutableCriteriaCount(criteria);
    }

    @Override
    public List findDestinationOrdersRef(Set<Long> paymentIds) {
        final String qs = new StringBuilder()
                .append("select py.id,o.orderreference from payment py join plannedsettlement ps  on (py.plannedsettlement_id=ps.id) ")
                .append(" left join orders o on (o.id=ps.order_id) where py.id in (:destinationIds)").toString();
        Query query = getSessionCustom().createSQLQuery(qs).setParameter("destinationIds", paymentIds);
        return query.list();
    }

    @Override
    public String findDestinationInvoiceOrdersRef(Set<Long> paymentIds) {
        final String qs = new StringBuilder()
                .append("select string_agg(ao.reference, ',') as references from actualorder ao\n" +
                        "left join actualconsignment ac on ac.id = ao.actualconsignment_id\n" +
                        "left join commercialinvoice ci on ac.costsinvoice_id = ci.id\n" +
                        "left join plannedsettlement ps on ps.invoice_id = ci.id \n" +
                        "left join payment p on ps.id = p.plannedsettlement_id \n" +
                        "where p.reallocationsourceid in(:destinationIds)").toString();
        Query query = getSessionCustom().createNativeQuery(qs).setParameter("destinationIds", paymentIds);
        return (String) query.uniqueResult();
    }

    @Override
    public List<PurchaseOrder> findOrders(Set<Long> collect) {
//        final String qs = new StringBuilder().append("select o.orderreference,o.currency_code,org.name,s.name from purchaseorder ")
//                .append(" join orders o on (o.id=po.id) join organisationalunit org on (o.organisationalunit_id=org.id ")
//                .append(" join organisationalunitsupplier os on (po.supplier_id=os.id) join supplier s
//                on (s.id=os.supplier_id) order o.orderreference").toString();
//        Query query = getSessionCustom().createSQLQuery(qs).setParameter("ordersIds", collect);
        DetachedCriteria criteria = DetachedCriteria.forClass(com.tradecloud.domain.model.ordermanagement.PurchaseOrder.class);
        criteria.add(Restrictions.in("id", collect));
        criteria.addOrder(Order.asc("orderReference"));
        Criteria criteria2 = criteria.getExecutableCriteria(getSessionCustom());
        return criteria2.list();
    }

    //only return orders with payments
    @Override
    public List<RemittanceAdviceSearchResult> search(RemittanceAdviceSearch search) {
        DetachedCriteria criteria = getOrdersCriteria(search);
        if (search.getSearchMetaParams() == null) {
            criteria.addOrder(Order.asc("orderReference"));
        } else {
            search.getSearchMetaParams().setOrderBy("orderReference");
        }
        criteria.setProjection(createSearchProjection());
        criteria.setResultTransformer(Transformers.aliasToBean(RemittanceAdviceSearchResult.class));
        return getExecutableCriteriaList(criteria, search.getSearchMetaParams());
    }

    private Projection createSearchProjection() {
        return Projections.projectionList()
                .add(Projections.property("id"), "id")
                .add(Projections.property("orderReference"), "orderReference")
                .add(Projections.property("organisationalUnit"), "organisationalUnit");
//               .add(Projections.property("supplier"), "supplier")
//               .add(Projections.property("c.code"), "currency");
    }

    private DetachedCriteria getOrdersCriteria(RemittanceAdviceSearch search) {
        DetachedCriteria criteria =
                DetachedCriteria.forClass(com.tradecloud.domain.model.ordermanagement.PurchaseOrder.class);
        criteria.createAlias("supplier", "so");
        criteria.createAlias("currency", "c");
        criteria.createAlias("so.supplier", "su");
        criteria.createAlias("organisationalUnit", "org");

        criteria.add(Restrictions.ne("state", OrderState.DELETED));
        criteria.add(Restrictions.isNotNull("consignment"));


        if (search.getSupplier() != null) {
            criteria.add(Restrictions.eq("supplier", search.getSupplier()));
        }
        if (CollectionUtils.isNotEmpty(search.getOrderReferences())) {
            final String s = search.getOrderReferences().stream().collect(Collectors.joining("|")).toLowerCase();
            criteria.add(Restrictions.sqlRestriction("lower(orderreference) similar to '%(" + s + ")%'"));
        }
        final Set<OrganisationalUnit> organisationalUnits = getOrganisationalUnits(search);
        if (!organisationalUnits.isEmpty()) {
            criteria.add(Restrictions.in("organisationalUnit", organisationalUnits));
        }
        if (search.getCurrency() != null) {
            criteria.add(Restrictions.in("currency", search.getCurrency()));
        }
        return criteria;
    }

    private Set<OrganisationalUnit> getOrganisationalUnits(RemittanceAdviceSearch search) {
        Set<OrganisationalUnit> organisationalUnits = Collections.EMPTY_SET;
        if (!CollectionUtils.isEmpty(search.getOrganisationalUnits())) {
            organisationalUnits = new HashSet<>(search.getOrganisationalUnits());
        } else if (search.isFilteredByUserOrg()) {
            organisationalUnits = getUserOrganisationalUnits();
        } else if (search.getOrganisationalUnit() != null) {
            organisationalUnits = Collections.singleton(search.getOrganisationalUnit());

        }
        return organisationalUnits;
    }
}