PlannedSettlementRepositoryImpl.java
package com.tradecloud.repository.impl;
import com.tradecloud.common.base.HibernateUtils;
import com.tradecloud.domain.base.utils.DateUtils;
import com.tradecloud.domain.document.PaymentState;
import com.tradecloud.domain.document.invoice.CommercialInvoice;
import com.tradecloud.domain.model.ordermanagement.Order;
import com.tradecloud.domain.model.ordermanagement.OrderState;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.domain.model.shipment.ShippingMode;
import com.tradecloud.domain.settlement.PlannedSettlement;
import com.tradecloud.domain.settlement.PlannedSettlementTotals;
import com.tradecloud.domain.settlement.Settleable;
import com.tradecloud.dto.plannedsettlement.PlannedSettlementSearch;
import com.tradecloud.dto.plannedsettlement.PlannedSettlementSearchResult;
import com.tradecloud.repository.PlannedSettlementRepository;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Hibernate;
import org.hibernate.internal.TypeLocatorImpl;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.EnumType;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;
import org.hibernate.type.TypeResolver;
import org.hibernate.type.spi.TypeConfiguration;
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.criteria.*;
import java.io.StringWriter;
import java.math.BigInteger;
import java.util.*;
import java.util.stream.Collectors;
/**
* Default implementation of the {@code PlannedSettlementRepository} interface.
*/
@Repository
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_UNCOMMITTED)
public class PlannedSettlementRepositoryImpl extends RepositoryBaseImpl<PlannedSettlement, Object> implements PlannedSettlementRepository {
private static final long serialVersionUID = 1L;
private static final Logger log = Logger.getLogger(PlannedSettlementRepositoryImpl.class);
@Override
public PlannedSettlementTotals getPlannedSettlementTotals(Settleable settleable) {
final String entityName = getEntityClassName(settleable);
// Note: We use the "max" aggregate function on the currency selections because Hibernate expects an aggregate function to be used because
// all the other fields use them in the query. It's a bit of a hack but doesn't affect the results we want.
StringWriter query = new StringWriter();
if (HibernateUtils.getNonProxyObject(settleable) instanceof CommercialInvoice) {
query.append("select ")
.append("max (stl.currency) as supplyCurrency, ")
.append("round(coalesce(sum(ps.amount), 0), 4) as totalSupplyValue, ")
.append("max(pps.currency) as costingCurrency, ")
.append("round(coalesce(sum(ps.amount * ps.forwardRate), 0), 4) as totalCostingValue, ")
.append("round(coalesce(sum(ps.amount * ps.spotRate) / nullif(sum(ps.amount), 0), 0), 4) as weightedAverageSpot, ")
.append("round(coalesce(sum(ps.amount * ps.forwardRate) / nullif(sum(ps.amount), 0), 0), 4) as weightedAverageForward ")
.append("from " + entityName + " stl join stl.plannedSettlements as pps ")
.append("join pps.plannedSettlementOrders ps where stl.id = :settleableId");
return (PlannedSettlementTotals) getCurrentSession().createQuery(query.toString()).setParameter("settleableId", settleable.getId())
.setResultTransformer(Transformers.aliasToBean(PlannedSettlementTotals.class)).uniqueResult();
} else {
query.append("select ")
.append("max (stl.currency) as supplyCurrency, ")
.append("round(coalesce(sum(ps.amount), 0), 4) as totalSupplyValue, ")
.append("max(ps.currency) as costingCurrency, ")
.append("round(coalesce(sum(ps.amount * ps.forwardRate), 0), 4) as totalCostingValue, ")
.append("round(coalesce(sum(ps.amount * ps.spotRate) / nullif(sum(ps.amount), 0), 0), 4) as weightedAverageSpot, ")
.append("round(coalesce(sum(ps.amount * ps.forwardRate) / nullif(sum(ps.amount), 0), 0), 4) as weightedAverageForward ")
.append("from " + entityName + " stl join stl.plannedSettlements as ps where stl.id = :settleableId");
return (PlannedSettlementTotals) getCurrentSession().createQuery(query.toString()).setParameter("settleableId", settleable.getId())
.setResultTransformer(Transformers.aliasToBean(PlannedSettlementTotals.class)).uniqueResult();
}
}
private String getEntityClassName(Settleable settleable) {
return Hibernate.getClass(settleable).getSimpleName();
}
@Override
public Long countResults(PlannedSettlementSearch search) {
StringBuilder builder = new StringBuilder("Select count(*) from ");
buildQuery(search, builder);
NativeQuery query = getSessionCustom().createNativeQuery(builder.toString());
setParameters(search, query);
return ((BigInteger) query.uniqueResult()).longValue();
}
@Override
public List<PlannedSettlementSearchResult> searchResults(PlannedSettlementSearch search) {
StringBuilder builder = new StringBuilder("Select * from ");
buildQuery(search, builder);
builder.append(" order by orderReference,settlementdate ");
Properties params = new Properties();
params.put("enumClass", ShippingMode.class.getName());
params.put("useNamed", true);
//params.put("type", "12");
final TypeConfiguration tc = new TypeConfiguration();
final TypeResolver tr = new org.hibernate.type.TypeResolver(tc, new org.hibernate.type.TypeFactory(tc));
Type myEnumType = new TypeLocatorImpl(tr).custom(EnumType.class, params);
//EnumType enumType = new EnumType();
//enumType.setParameterValues(params);
//CustomType customType = new CustomType(enumType);
NativeQuery query = getSessionCustom().createNativeQuery(builder.toString());
setParameters(search, query);
query.addScalar("id", StandardBasicTypes.LONG)
.addScalar("invoiceState", StandardBasicTypes.STRING)
.addScalar("orderReference", StandardBasicTypes.STRING)
.addScalar("totalAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("invoiceReference", StandardBasicTypes.STRING)
.addScalar("shipmentReference", StandardBasicTypes.STRING)
.addScalar("currency", StandardBasicTypes.STRING)
.addScalar("paidAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("orderAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("invoiceAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("splitPaymentType", StandardBasicTypes.STRING)
.addScalar("plannedSettlementType", StandardBasicTypes.STRING)
.addScalar("settlementDate", StandardBasicTypes.DATE)
.addScalar("orderId", StandardBasicTypes.LONG)
.addScalar("invoiceId", StandardBasicTypes.LONG)
.addScalar("supplierName", StandardBasicTypes.STRING)
.addScalar("createdDate", StandardBasicTypes.DATE)
.addScalar("organisationName", StandardBasicTypes.STRING)
.addScalar("billOfLadingDate", StandardBasicTypes.DATE)
.addScalar("shipmentCreateDate", StandardBasicTypes.DATE)
.addScalar("deliveredDateToFD", StandardBasicTypes.DATE)
.addScalar("paymentDates", StandardBasicTypes.STRING)
.addScalar("paymentReferences", StandardBasicTypes.STRING)
.addScalar("orderState", StandardBasicTypes.STRING)
.addScalar("paymentTerm", StandardBasicTypes.STRING)
.addScalar("paymentMethod", StandardBasicTypes.STRING)
.addScalar("orderComments", StandardBasicTypes.STRING)
.addScalar("forwardRate", StandardBasicTypes.BIG_DECIMAL)
.addScalar("nominatedBank", StandardBasicTypes.STRING)
.addScalar("shippingReference", StandardBasicTypes.STRING)
.addScalar("shipmentId", StandardBasicTypes.LONG)
.addScalar("shippingMode", myEnumType)
.setResultTransformer(Transformers.aliasToBean(PlannedSettlementSearchResult.class));
if (null != search.getSearchMetaParams() && search.isPaging() && search.isOrdersOnly()) {
query.setFirstResult(search.getSearchMetaParams().getRowIndex());
query.setMaxResults(search.getSearchMetaParams().getRowCount());
}
return query.list();
}
private void setParameters(PlannedSettlementSearch search, NativeQuery query) {
if (search.getPlannedSettlementIdToExclude() != null) {
query.setParameter("PlannedSettlementIdToExclude", search.getPlannedSettlementIdToExclude());
}
if (StringUtils.isNotEmpty(search.getOrderReference())) {
query.setParameter("orderRef", "%" + search.getOrderReference() + "%");
}
if (StringUtils.isNotEmpty(search.getShippingReference())) {
query.setParameter("shippingreference", "%" + search.getShippingReference() + "%");
}
if (StringUtils.isNotEmpty(search.getInvoiceReference())) {
query.setParameter("invoicereference", "%" + search.getInvoiceReference() + "%");
}
if (search.getPaymentTerm() != null) {
query.setParameter("paymentTerm", "%" + search.getPaymentTerm() + "%");
}
if (search.getPaymentMethod() != null) {
query.setParameter("paymentMethod", "%" + search.getPaymentMethod() + "%");
}
if (search.getCurrency() != null) {
query.setParameter("currency", search.getCurrency());
}
if (search.getSettlementDateFrom() != null) {
DateUtils.setToStartOfDay(search.getSettlementDateFrom());
query.setParameter("settlementDateFrom", search.getSettlementDateFrom());
}
if (search.getSettlementDateTo() != null) {
DateUtils.setToEndOfDay(search.getSettlementDateTo());
query.setParameter("settlementDateTo", search.getSettlementDateTo());
}
if (search.getOrderReferences() != null && !search.getOrderReferences().isEmpty()) {
String params = search.getOrderReferences().stream().map(String::toUpperCase).collect(Collectors.joining("|"));
query.setParameter("orderReferences", params);
}
if (search.getShippingReferences() != null && !search.getShippingReferences().isEmpty()) {
String params = search.getShippingReferences().stream().map(String::toUpperCase).collect(Collectors.joining("|"));
query.setParameter("shippingreferences", params);
}
if (search.getCreatedDateFrom() != null) {
query.setParameter("createdDateFrom", search.getCreatedDateFrom());
}
if (search.getCreatedDateTo() != null) {
query.setParameter("createdDateTo", search.getCreatedDateTo());
}
}
private void buildQuery(PlannedSettlementSearch search, StringBuilder builder) {
builder.append("(select ");
appendFields(builder);
builder.append(" from plannedsettlement ps ");
builder.append(" left join orders o on (o.id=ps.order_id and o.id not in");
builder.append(" (select ao.originalid from actualorder ao join actualconsignment ac on (ao.actualconsignment_id=ac.id)");
builder.append(" join costsinvoice inv on (inv.id=ac.costsinvoice_id) join commercialinvoice ci on (ci.id=inv.id) ");
builder.append(" where ao.originalid=o.id and inv.state <>'DELETED') and o.elc = 'f' and o.state in ");
if (null == search.getState()) {
builder.append("('SIGNED_OFF','FINALISED','BOOKED_IN','FREIGHT_RECEIVED','STOCK_RECEIVED'," +
"'TOLERANCE_EXCEEDED','AWAITING_LSP_SIGNOFF','SHIPMENT_CREATED','CANCEL_BOOKING','AWAITING_TARIFFING'," +
" 'AWAITING_COSTING','STOCK_PARTIALLY_RECEIVED'))");
} else if (search.getState().equals(OrderState.AWAITING_COSTING)) {
builder.append("('SIGNED_OFF','FINALISED','BOOKED_IN','AWAITING_COSTING','AWAITING_LSP_SIGNOFF','FREIGHT_RECEIVED'," +
"'SHIPMENT_CREATED','STOCK_RECEIVED','STOCK_PARTIALLY_RECEIVED'))");
/*att-1264
builder.append("('SIGNED_OFF','FINALISED','BOOKED_IN','AWAITING_COSTING','AWAITING_LSP_SIGNOFF','FREIGHT_RECEIVED'," +
"'SHIPMENT_CREATED','STOCK_RECEIVED'))");*/
} else if (search.getState().equals(OrderState.SIGNED_OFF)) {
builder.append("('SIGNED_OFF','BOOKED_IN','AWAITING_LSP_SIGNOFF','FREIGHT_RECEIVED','SHIPMENT_CREATED'," +
"'STOCK_RECEIVED','CANCEL_BOOKING','STOCK_PARTIALLY_RECEIVED'))");
} else {
builder.append("('FINALISED','TOLERANCE_EXCEEDED','AWAITING_LSP_SIGNOFF'))");
}
builder.append(" left join purchaseorder po on (po.id=o.id) ");
builder.append(" left join shippinginformation info on (o.shippinginformation_id=info.id) ");
builder.append(" left join commercialinvoice ci on (ci.id=ps.invoice_id) ");
builder.append(" left join costsinvoice cinv on (cinv.id=ci.id and cinv.state<>'DELETED') ");
builder.append(" left join shipment ship on (cinv.shipment_id=ship.id and ship.state<>'DELETED') ");
builder.append(" left join seashipment sea on (ship.id=sea.id) ");
builder.append(" left join airshipment air on (ship.id=air.id) ");
/*if (!search.isOrdersOnly()) {
builder.append(" where (o.id is not null or cinv.id is not null) ");
} else {
builder.append(" where o.id is not null and cinv.id is null ");
}*/
builder.append(" where po.id is not null or cinv.id is not null ");
builder.append(" ) as result where result.id is not null ");
filterByPaymentDetails(search, builder);
if (search.getCreatedDateFrom() != null) {
builder.append(" and result.createdDate>=:createdDateFrom");
}
if (search.getCreatedDateTo() != null) {
builder.append(" and result.createdDate<=:createdDateTo");
}
if (search.getPlannedSettlementIdToExclude() != null) {
builder.append(" and result.id <> :PlannedSettlementIdToExclude");
}
if (StringUtils.isNotEmpty(search.getOrderReference())) {
builder.append(" and result.orderreference is not null and result.orderreference ilike :orderRef");
}
if (search.getOrderReferences() != null && !search.getOrderReferences().isEmpty()) {
builder.append(" and result.orderreference is not null and result.orderreference ~* (:orderReferences)");
}
if (search.getShippingReferences() != null && !search.getShippingReferences().isEmpty()) {
String params = search.getShippingReferences().stream().map(String::toUpperCase).collect(Collectors.joining("|"));
builder.append(" and result.shippingreference is not null and result.shippingreference ~* (:shippingreferences)");
}
if (StringUtils.isNotEmpty(search.getShippingReference())) {
builder.append(" and result.shippingreference ilike :shippingreference");
}
if (StringUtils.isNotEmpty(search.getInvoiceReference())) {
builder.append(" and result.invoicereference ilike :invoicereference");
}
if (search.getPaymentTerm() != null) {
builder.append(" and result.paymentTerm ilike :paymentTerm");
}
if (search.getPaymentMethod() != null) {
builder.append(" and result.paymentMethod ilike :paymentMethod");
}
if (search.getCurrency() != null) {
builder.append(" and result.currency=:currency");
}
if (search.getState() != null && search.getState().equals(OrderState.FINALISED)) {
builder.append(" and result.orderState in");
builder.append("('FINALISED','TOLERANCE_EXCEEDED','AWAITING_LSP_SIGNOFF')");
}
if (search.getSupplier() != null) {
builder.append(" and result.supplier=" + search.getSupplier().getId());
}
if (search.getSettlementDateFrom() != null) {
builder.append(" and settlementdate>=:settlementDateFrom");
}
if (search.getSettlementDateTo() != null) {
builder.append(" and settlementdate<=:settlementDateTo");
}
if (search.isFilteredByUserOrg()) {
Set<OrganisationalUnit> userOrganisationalUnits = getUserOrganisationalUnits();
String orgIdsAsString = getOrgIdsAsString(userOrganisationalUnits);
builder.append(" and (result.orgunit in (" + orgIdsAsString + ")");
builder.append(getInvoiceOrgsCountQuery(orgIdsAsString) + " )");
} else {
if (search.getOrganisationalUnit() != null) {
builder.append(" and (result.orgunit='" + search.getOrganisationalUnit().getId() + "'"
+ getInvoiceOrgsCountQuery(search.getOrganisationalUnit().getId() + "") + ")");
}
if (search.getOrganisationalUnits() != null && !search.getOrganisationalUnits().isEmpty()) {
String orgIdsAsString = getOrgIdsAsString(search.getOrganisationalUnits());
builder.append(" and (result.orgunit in (" + orgIdsAsString + ")");
builder.append(getInvoiceOrgsCountQuery(orgIdsAsString) + " )");
}
}
if (StringUtils.isNotEmpty(search.getShipmentReference())) {
builder.append(" and result.shipmentreference ilike '%" + search.getShipmentReference() + "%'");
}
boolean payments = search.getPaymentStates() != null && !search.getPaymentStates().isEmpty();
if (search.getPaymentState() != null && !payments) {
if (search.getPaymentState() == PaymentState.UNSETTLED) {
// builder.append(" and paidamount is null or paymentstate is null or paymentstate='"+ PaymentState.UNSETTLED.name()+"'");
builder.append(" and round(COALESCE(paidamount,0),2) < round(totalamount,2) ");
} else if (search.getPaymentState() == PaymentState.SETTLED) {
builder.append(" and paidamount is not null and paymentstate='" + PaymentState.SETTLED.name() + "'");
} else if (search.getPaymentState() == PaymentState.OVER_PAID) {
// builder.append(" and paidamount is not null and paymentstate='" + PaymentState.OVER_PAID.name() + "'");
builder.append(" and round(COALESCE(paidamount,0),2) > round(totalamount,2) ");
}
}else {
if (payments) {
HashSet<PaymentState> paymentStates = new HashSet<>(search.getPaymentStates());
if(paymentStates.size()<3) {
List<String> filters = new ArrayList<>();
if (search.getPaymentStates().contains(PaymentState.UNSETTLED)
||search.getPaymentStates().contains(PaymentState.UNSETTLED.name())) {
// builder.append(" and paidamount is null or paymentstate is null or paymentstate='"+ PaymentState.UNSETTLED.name()+"'");
filters.add(" round(COALESCE(paidamount,0),2) < round(totalamount,2) ");
}
if (search.getPaymentStates().contains(PaymentState.SETTLED)
||search.getPaymentStates().contains(PaymentState.SETTLED.name())) {
filters.add(" paidamount is not null and paymentstate='" + PaymentState.SETTLED.name() + "'");
}
if (search.getPaymentStates().contains(PaymentState.OVER_PAID)
||search.getPaymentStates().contains(PaymentState.OVER_PAID.name())) {
// builder.append(" and paidamount is not null and paymentstate='" + PaymentState.OVER_PAID.name() + "'");
filters.add(" round(COALESCE(paidamount,0),2) > round(totalamount,2) ");
}
if(!filters.isEmpty()) {
builder.append(" and (");
builder.append(String.join("OR", filters));
builder.append(" )");
}
}
}
}
}
private void filterByPaymentDetails(PlannedSettlementSearch search, StringBuilder builder) {
if (StringUtils.isNotEmpty(search.getPaymentReference()) || search.getPaymentDateFrom() != null
|| search.getPaymentDateTo() != null) {
builder.append(" and (select count(*) from payment p2 where p2.plannedsettlement_id =result.id");
if (StringUtils.isNotEmpty(search.getPaymentReference())) {
builder.append(" and p2.reference ilike '%" + search.getPaymentReference() + "%'");
}
if (search.getPaymentDateFrom() != null) {
builder.append(" and p2.paymentdate>='" + DateUtils.toT_YMDFormat(search.getPaymentDateFrom()) + "'");
}
if (search.getPaymentDateTo() != null) {
builder.append(" and p2.paymentdate<='" + DateUtils.toT_YMDFormat(search.getPaymentDateTo()) + "'");
}
builder.append(")>0");
}
}
private String getInvoiceOrgsCountQuery(String orgs) {
StringBuilder builder = new StringBuilder(" OR (select count(*) from Costsinvoice ci");
builder.append(" join actualconsignment ac on (ac.costsinvoice_id=ci.id) ");
builder.append(" join actualorder ao on (ao.actualconsignment_id=ac.id) join orders o on (o.id=ao.originalid) ");
builder.append(" where ci.id= result.invoiceid and o.organisationalunit_id in (" + orgs + ") )>0 ");
return builder.toString();
}
private void appendFields(StringBuilder builder) {
builder.append(" ps.id,");
builder.append("(select state from costsinvoice where id=ci.id) as invoiceState,");
builder.append(" COALESCE(o.orderreference,(select string_agg(ao.reference,',') from actualorder ao");
builder.append(" join actualconsignment ac on (ao.actualconsignment_id=ac.id)");
builder.append(" where costsinvoice_id =ci.id and ao.totalvalue<>0)) as orderreference,");
builder.append("o.orderreference as orderRefSearch, o.state as orderState,");
builder.append("ps.amount as totalamount,");
builder.append("(select reference from costsinvoice where id=ci.id) as invoicereference,");
builder.append("(select reference from shipment where id =(select shipment_id from costsinvoice where id=ci.id)) as shipmentreference,");
builder.append("(select id from shipment where id =(select shipment_id from costsinvoice where id=ci.id)) as shipmentId,");
builder.append("(select shippingmode from shipmentshippinginfo left join shipment on shipment.shippinginfo_id = shipmentshippinginfo.id " +
"where shipment.id =(select shipment_id from costsinvoice where id=ci.id)) as shippingMode,");
builder.append(" ps.currency_code as currency,");
builder.append("(select sum(amount) from payment where plannedsettlement_id =ps.id) as paidamount,");
builder.append("(select string_agg(distinct(reference),'|') from payment where plannedsettlement_id =ps.id) as paymentreferences,");
builder.append("(select string_agg(distinct(to_char(p.paymentdate,'DD.MM.YYYY')),'|') from payment p where " +
"p.plannedsettlement_id =ps.id ) as paymentdates,");
builder.append("totalinvoicevalue as orderamount,");
builder.append("(select grossvalue from costsinvoice where id=ci.id) as invoiceamount,");
builder.append("ps.splitpaymenttype,");
builder.append("ps.type as plannedsettlementtype,");
builder.append("ps.settlementdate,");
builder.append("COALESCE(po.supplier_id,ci.supplier_id) as supplier,");
// builder.append("po.supplier_id as ordersupplier, ");
builder.append("COALESCE(o.organisationalunit_id,ci.organisationalunit_id) as orgunit,");
// builder.append("o.organisationalunit_id as ciorgunit,");
builder.append("ps.paymentstate,");
builder.append("ps.percentage as paymentPercentage,");
builder.append("o.id as orderId,");
builder.append("ci.id as invoiceId,");
builder.append("(select s.name from organisationalunitsupplier os join supplier s on (s.id=os.supplier_id)");
builder.append(" where os.id in (ci.supplier_id,po.supplier_id)) as supplierName,");
builder.append(" COALESCE(o.orderdate,cinv.datecreated) as createdDate, ");
builder.append("COALESCE( (select org.name from organisationalunit org where id in (o.organisationalunit_id)),");
builder.append(" (select string_agg(distinct(org.name),',') from actualorder ao join orders o on (o.id=ao.originalid) ");
builder.append(" join organisationalunit org on (org.id=o.organisationalunit_id)");
builder.append(" join actualconsignment ac on (ao.actualconsignment_id=ac.id)");
builder.append(" where costsinvoice_id =ci.id and ao.totalvalue<>0)) as organisationName, ");
builder.append(" COALESCE( sea.billofladingdate,air.houseairwaybillissuedate ) as billOfLadingDate, ");
builder.append("ship.created as shipmentCreateDate,");
builder.append(" COALESCE(info.shippingreference,(select string_agg(ao.shippingreference,',') from actualorder ao");
builder.append(" join actualconsignment ac on (ao.actualconsignment_id=ac.id)");
builder.append(" where costsinvoice_id =ci.id and ao.totalvalue<>0)) as shippingreference,");
builder.append("(select c.ataatfinaldestination from container c where c.shipment_id =ship.id limit 1) as deliveredDateToFD, ");
//only applicable when searching order
builder.append("(select name from paymentterm where code=o.paymentterm_code) as paymentTerm, ");
builder.append("(select name from paymentmethod where code=o.paymentmethod_code) as paymentMethod, ");
builder.append("(select string_agg(distinct(reason),',') from orders_freetextcomments com where order_id=o.id) as orderComments, ");
builder.append("ps.forwardrate as forwardRate, ");
builder.append("(select nominatedbank_code from payment where plannedsettlement_id =ps.id and nominatedbank_code is not null limit 1) " +
"as nominatedBank ");
}
@Override
public Order getOrderForPlannedSettlement(Long plannedSettlementId) {
CriteriaBuilder criteriaBuilder = getSession().getCriteriaBuilder();
CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
Root<Order> root = criteriaQuery.from(Order.class);
Join<Order, PlannedSettlement> join = root.join("plannedSettlements");
Path path = join.get("id");
Predicate predicate = criteriaBuilder.equal(path, plannedSettlementId);
criteriaQuery.where(predicate);
org.hibernate.query.Query<Order> q = getSession().createQuery(criteriaQuery);
return q.uniqueResult();
}
@Override
public List<PlannedSettlement> findByOrderId(Long id) {
CriteriaBuilder criteriaBuilder = getSession().getCriteriaBuilder();
CriteriaQuery<PlannedSettlement> criteriaQuery = criteriaBuilder.createQuery(PlannedSettlement.class);
Root<PlannedSettlement> c = criteriaQuery.from(PlannedSettlement.class);
criteriaQuery = criteriaQuery.select(c).where(criteriaBuilder.equal(c.get("orderId"), id));
org.hibernate.query.Query<PlannedSettlement> q = getSession().createQuery(criteriaQuery);
return q.getResultList();
}
}