ActualCostSummaryRepositoryImpl.java

package com.tradecloud.repository.costing.impl;

import com.tradecloud.domain.costing.Costable;
import com.tradecloud.domain.costing.clean.ActualCostSummary;
import com.tradecloud.domain.costing.clean.ActualShipment;
import com.tradecloud.domain.costing.clean.CostingSummarySearch;
import com.tradecloud.domain.costing.clean.SummaryCostType;
import com.tradecloud.domain.document.invoice.ActualConsignment;
import com.tradecloud.domain.document.invoice.ActualOrder;
import com.tradecloud.domain.document.invoice.CostLineCostingCell;
import com.tradecloud.domain.infrastructure.persistence.CriteriaBuilder;
import com.tradecloud.domain.model.ordermanagement.Consignment;
import com.tradecloud.domain.shipment.Shipment;
import com.tradecloud.repository.costing.ActualCostSummaryRepository;
import com.tradecloud.repository.impl.CostingSummaryRepositoryImpl;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.DetachedCriteria;
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 java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Implementation of the {@code CostingSummaryRepository} interface for the ActualCostSummary entity.
 */
@Repository(value = "actualCostSummaryRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class ActualCostSummaryRepositoryImpl extends CostingSummaryRepositoryImpl<ActualCostSummary> implements ActualCostSummaryRepository {

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

    @Override
    public void addSearchRestrictions(DetachedCriteria searchCriteria, CostingSummarySearch costingSummarySearch) {
        Costable originalCostable = costingSummarySearch.getOriginalCostable();
        if (costingSummarySearch.getSummaryCostType() != null) {
            CriteriaBuilder.addEqRestriction(searchCriteria, "summaryCostType", costingSummarySearch.getSummaryCostType());
        }
        if (originalCostable != null) {
            CriteriaBuilder.addEqRestriction(searchCriteria, "originalCostable", originalCostable);
        }
    }

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

    @Override
    public List<ActualCostSummary> find(ActualShipment actualShipment) {
        DetachedCriteria criteria = DetachedCriteria.forClass(ActualCostSummary.class);

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

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

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

        return results;
    }

    @Override
    public ActualCostSummary find(Shipment shipment) {
        Criteria criteria = getSession().createCriteria(ActualCostSummary.class);
        criteria.add(Restrictions.eq("originalCostable", shipment));
        criteria.add(Restrictions.eq("summaryCostType", SummaryCostType.ALC));
        Object results = criteria.uniqueResult();
        return (ActualCostSummary) results;
    }

    @Override
    public List<ActualOrder> findActualOrders(List<Long> orderIds) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select o from ActualCostSummary ac join ac.actualShipment act join act.actualConsignments c join c.actualOrders o ")
                .append(" where  ac.summaryCostType='ALC' and o.originalId in (:orderIds)");
        return getCurrentSession().createQuery(stringBuilder.toString()).setParameter("orderIds", orderIds).list();
    }

    @Override
    public List<ActualCostSummary> findFromActualShipment(Shipment shipment) {
        DetachedCriteria criteria = DetachedCriteria.forClass(ActualCostSummary.class);
        CriteriaBuilder.addEqRestriction(criteria, "summaryCostType", SummaryCostType.ALC);
        CriteriaBuilder.addEqRestriction(criteria, "originalCostable", shipment);

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

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

        return results;
    }

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

        CriteriaBuilder.addEqRestriction(criteria, "originalCostable.id", id);
        CriteriaBuilder.addEqRestriction(criteria, "summaryCostType", SummaryCostType.ALC);
        return criteria.getExecutableCriteria(getSessionCustom()).list();
    }

    @Override
    public BigDecimal getInvoiceQuantity(ActualConsignment actualConsignment) {
        String queryString =
                "SELECT sum(ali.invoiceQuantity) " +
                        "FROM ActualLineItem ali LEFT JOIN ali.actualOrder ao LEFT JOIN ao.actualConsignment ac " +
                        "WHERE ac=:actualConsignment";

        Query query = getSessionCustom().createQuery(queryString);
        query.setParameter("actualConsignment", actualConsignment);
        return (BigDecimal) query.uniqueResult();
    }

    @Override
    public BigDecimal getConsignmentInvoiceQuantity(Consignment consignment) {
        String queryString = "SELECT sum(ali.invoiceQuantity) FROM ActualLineItem ali \n"
                + "LEFT JOIN actualorder ao on ali.actualOrder_id = ao.id \n"
                + "LEFT JOIN actualconsignment ac on ao.actualConsignment_id = ac.id\n"
                + "left join consignment c on c.id = ac.originalid\n"
                + "LEFT JOIN costsinvoice on costsinvoice.id = ac.costsinvoice_id\n"
                + "right join commercialinvoice ci on costsinvoice.id = ci.id\n"
                + "where  costsinvoice.state <> 'DELETED' and c.id =:consignmentId";
        Query query = getSessionCustom().createSQLQuery(queryString);
        query.setParameter("consignmentId", consignment.getId());
        return (BigDecimal) query.uniqueResult();
    }

    @Override
    public BigDecimal getInvoiceValue(ActualConsignment actualConsignment) {
        String queryString =
                "SELECT sum(ali.invoiceValue) "
                        + "FROM ActualLineItem ali LEFT JOIN ali.actualOrder ao LEFT JOIN ao.actualConsignment ac "
                        + "WHERE ac=:actualConsignment";

        Query query = getSessionCustom().createQuery(queryString);
        query.setParameter("actualConsignment", actualConsignment);
        return (BigDecimal) query.uniqueResult();
    }

    @Override
    public List<ActualCostSummary> findByCostableIds(CostingSummarySearch costingSummarySearch) {
        Optional.ofNullable(costingSummarySearch.getSummaryCostType()).orElseThrow(() -> new IllegalArgumentException("summaryCostType is required"));
        Optional.ofNullable(costingSummarySearch.getCostableIdsSet()).orElseThrow(() -> new IllegalArgumentException("costableIdsSet is required"));
        return search(costingSummarySearch);
    }

    @Override
    public <T> T findByNumberAndReferenceIgnoreCase(Class<T> c, String number, String reference, String summaryNumber) {

        Criteria criteria = getCurrentSession().createCriteria(c)
                .add(Restrictions.eq("number", number).ignoreCase())
                .add(Restrictions.eq("reference", reference).ignoreCase());
        if (StringUtils.isNotEmpty(summaryNumber)) {
            criteria.add(Restrictions.eq("summaryNumber", summaryNumber).ignoreCase());
        }
        @SuppressWarnings("unchecked")
        T result = (T) criteria.uniqueResult();
        return result;


    }

    @Override
    public List<ActualOrder> findActualOrders(SummaryCostType summaryCostingType, Set<Long> purchaseOrderIds, List<Long> shipmentIds) {
        StringBuilder stringBuilder = new StringBuilder("select ao from ActualCostSummary sm  join sm.actualShipment ship ")
                .append("join ship.actualConsignments ac join  ac.actualOrders ao  where sm.summaryCostType=:summaryCostType ")
                .append("and sm.originalCostable.id in (:shipmentIds) and ao.originalId in (:purchaseOrderIds) order by ao.created desc");
        return getSessionCustom().createQuery(stringBuilder.toString()).setParameter("summaryCostType", summaryCostingType)
                .setParameterList("shipmentIds", shipmentIds).setParameter("purchaseOrderIds", purchaseOrderIds).list();

    }

    public List<Map<String, String>> getCostsPerItem(Long originalId, List<String> costlines) {
        String query = "  select transactionamount,costlinetemplate_code,cli.organisation as orgunit,cli.code from actuallineitem cli\n"
                + "  join actualorder o on (o.id=cli.actualorder_id) join actualconsignment c on (c.id=o.actualconsignment_id) "
                + "join actualshipment acts on (acts.id=c.actualshipment_id) join actualcostsummary acs on (acs.actualshipment_id=acts.id)  "
                + "join shipment s on (s.id=acs.originalcostable_id) join actuallineitem_costlinecostingcell cc on (cc.actuallineitem_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.originalid =:originalId and"
                + " s.state<>'DELETED' and acs.summarycosttype='ALC' ;";
        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("originalId", originalId)
                .setParameterList("costlines", costlines)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return results;
    }

    @Override
    @SuppressWarnings("unchecked")
    public Map<Long, List<Map<String, String>>> getCostsPerItemBatch(List<Long> orderIds, List<String> costlines) {
        if (orderIds == null || orderIds.isEmpty() || costlines == null || costlines.isEmpty()) {
            return Collections.emptyMap();
        }

        String query = "SELECT o.originalid AS orderId, "
                + "cell.transactionamount AS transactionamount, "
                + "costlinetemplate_code AS costlinetemplate_code, "
                + "cli.organisation AS orgunit, "
                + "cli.code AS code "
                + "FROM actuallineitem cli "
                + "JOIN actualorder o ON o.id = cli.actualorder_id "
                + "JOIN actualconsignment c ON c.id = o.actualconsignment_id "
                + "JOIN actualshipment acts ON acts.id = c.actualshipment_id "
                + "JOIN actualcostsummary acs ON acs.actualshipment_id = acts.id "
                + "JOIN shipment s ON s.id = acs.originalcostable_id "
                + "JOIN actuallineitem_costlinecostingcell cc ON cc.actuallineitem_id = cli.id "
                + "JOIN costlinecostingcell cell ON cell.id = cc.costlinecostingcells_id "
                + "JOIN costline l ON l.id = cell.costline_id "
                + "WHERE o.originalid IN (:orderIds) "
                + "AND costlinetemplate_code IN (:costlines) "
                + "AND s.state <> 'DELETED' "
                + "AND acs.summarycosttype = 'ALC'";

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

        return rawResults.stream()
                .filter(map -> map.get("orderId") != null)
                .collect(Collectors.groupingBy(
                        map -> {
                            Object val = map.get("orderId");
                            if (val instanceof Long) {
                                return (Long) val;
                            } else if (val instanceof String) {
                                return Long.valueOf((String) val);
                            } else {
                                throw new IllegalArgumentException("Unexpected type for orderId: " + val.getClass());
                            }
                        }
                ));
    }

    @Override
    public ActualCostSummary findAlcByOriginalShipmentId(Long shipmentId) {
        if (shipmentId == null) {
            return null;
        }

        Criteria criteria = getSession().createCriteria(ActualCostSummary.class);
        criteria.add(Restrictions.eq("originalCostable.id", shipmentId));
        criteria.add(Restrictions.eq("summaryCostType", SummaryCostType.ALC));
        criteria.setMaxResults(1);

        return (ActualCostSummary) criteria.uniqueResult();
    }

    @Override
    public List<CostLineCostingCell> getCostGroupExcludeFromUnitPerCost(Long actualOrderId) {
        String query = "select c \n"
                + "from com.tradecloud.domain.document.invoice.ActualOrder o \n"
                + "join o.costLineCosting.costLineCostingCells c \n"
                + "where o.id = :actualOrderId \n"
                + "and c.costLine.excludeFromUnitPerCost = true\n";
        return getSessionCustom().createQuery(query).setParameter("actualOrderId", actualOrderId)
                .list();
    }

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