DealRepositoryImpl.java
package com.tradecloud.repository.impl;
import com.tradecloud.domain.base.utils.DateRange;
import com.tradecloud.domain.base.utils.DateUtils;
import com.tradecloud.domain.base.utils.MathUtils;
import com.tradecloud.domain.infrastructure.persistence.CriteriaBuilder;
import com.tradecloud.domain.model.ForexGroup;
import com.tradecloud.domain.model.Money;
import com.tradecloud.domain.model.deal.*;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.domain.treasury.TreasuryBank;
import com.tradecloud.dto.treasury.*;
import com.tradecloud.repository.DealRepository;
import com.tradecloud.repository.GeneralRepository;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.criterion.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.joda.time.LocalDate;
import org.joda.time.LocalDateTime;
import org.springframework.beans.factory.annotation.Autowired;
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;
/**
* Deal repository implementation.
* <p>
* Contains all database code for CRUD and search operations on deals
*/
@Repository(value = "dealRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class DealRepositoryImpl extends RepositoryBaseImpl<Deal, Object> implements DealRepository {
private static final long serialVersionUID = 1L;
@Autowired
private GeneralRepository generalRepository;
private static Logger log = Logger.getLogger(DealRepositoryImpl.class);
private static final String DEAL_FULLY_LINKED = "(select COUNT(*) from (SELECT SUM(amount_value) as linksum from " +
"fec_deal_link where deal_id = {alias}.id) as foo where trunc(abs(abs(linksum) - abs({alias}.amount_value)),2)=0.00)";
@Override
public void store(Deal deal) {
saveOrUpdate(deal);
flush();
}
@Override
public List<Deal> findAll() {
@SuppressWarnings("unchecked")
List<Deal> deals = (List<Deal>) getSessionCustom().createQuery("from Deal").list();
setGroupAmount(deals);
// injectStuff(deals);
return deals;
}
@Override
public Deal find(long id) {
Deal deal = (Deal) getSessionCustom().createQuery("from Deal where id = :id").setParameter("id", id).uniqueResult();
setGroupAmount(Collections.singletonList(deal));
return deal;
}
@Override
public List<Deal> findDealsWithOrderNumber(OrderReference orderReference) {
@SuppressWarnings("unchecked")
List<Deal> deals = getSessionCustom().createQuery("from Deal where orderReference = :orderReference")
.setParameter("orderReference", orderReference).list();
return deals;
}
@Override
public List<Deal> find(OrderReference orderReference, EstimatedSettlementDate estimatedSettlementDate, DealStatus status,
ForexGroup forexGroup) {
@SuppressWarnings("unchecked")
List<Deal> deals = getSessionCustom().createQuery(
"from Deal where orderReference = :orderReference and estimatedSettlementDate = :estimatedSettlementDate"
+ " and status = :status and forexGroup=:forexGroup").setParameter("orderReference", orderReference)
.setParameter("estimatedSettlementDate", estimatedSettlementDate).setParameter("status", status)
.setParameter("forexGroup", forexGroup).list();
return deals;
}
@Override
public List<Deal> find(OrderReference orderReference, EstimatedSettlementDate estimatedSettlementDate, Currency currency, DealStatus status,
ForexGroup forexGroup) {
@SuppressWarnings("unchecked")
List<Deal> deals = getSessionCustom().createQuery(
"from Deal where orderReference = :orderReference and estimatedSettlementDate = :estimatedSettlementDate "
+ "and status = :status and amount_currency = :currency and forexGroup=:forexGroup")
.setParameter("orderReference", orderReference).setParameter("estimatedSettlementDate", estimatedSettlementDate)
.setParameter("status", status).setParameter("currency", currency).setParameter("forexGroup", forexGroup).list();
return deals;
}
@Override
public List<Deal> findAllActive() {
@SuppressWarnings("unchecked")
List<Deal> deals = (List<Deal>) getSessionCustom().createQuery("from Deal where status <> :status")
.setParameter("status", DealStatus.AUDIT).list();
setGroupAmount(deals);
return deals;
}
private DetachedCriteria getDetachedCriteria(OrganisationalUnit organisationalUnit, List<DealStatus> dealStatuses, Currency currency,
Boolean linkedToFEC, LocalDateTime dealDateFrom, LocalDateTime dealDateTo,
LocalDate estimatedSettlementDateFrom,
LocalDate estimatedSettlementDateTo, LocalDate settlementDateFrom, LocalDate settlementDateTo,
LocalDate dealMaturityDateFrom,
LocalDate dealMaturityDateTo, LocalDate invoicedDateFrom, LocalDate invoicedDateTo,
OrderReference orderReference,
DealLinkReference dealLinkReference, ShippingReference shippingReference, SortBy sortBy,
boolean showAllAssociatedDeals,
TradePurpose tradePurpose, LocalDate dealLinkCreatedDateFrom, LocalDate dealLinkCreatedDateTo,
ForexGroup forexGroup,
String supplierName, BuySellDealType buySellDealType, String source,
DealTransactionType dealTransactionType,
String buyer,
TreasuryBank nominatedBank,
String shipmentNumber, LocalDate billOfLadingDateFrom, LocalDate billOfLadingDateTo,
Boolean includeEarlyOrders) {
DetachedCriteria criteria = DetachedCriteria.forClass(Deal.class, "deal");
if (organisationalUnit != null) {
criteria.add(Restrictions.eq("organisationalUnit", organisationalUnit));
}
if (dealStatuses != null && !dealStatuses.isEmpty()) {
criteria.add(Restrictions.in("status", dealStatuses));
} else {
criteria.add(Restrictions.ne("status", DealStatus.AUDIT));
}
if (currency != null) {
criteria.add(Restrictions.eq("amount.currency", currency));
}
if (forexGroup != null) {
criteria.add(Restrictions.eq("forexGroup", forexGroup));
}
if (nominatedBank != null) {
criteria.add(Restrictions.eq("nominatedBank", nominatedBank));
}
if (supplierName != null) {
criteria.add(Restrictions.eq("supplier", supplierName));
}
if (source != null) {
criteria.add(Restrictions.eq("source", source));
}
if (buyer != null && !buyer.isEmpty()) {
criteria.add(Restrictions.eq("buyer", buyer));
}
if (dealTransactionType != null) {
criteria.add(Restrictions.eq("transactionType", dealTransactionType.getDescription()));
}
CriteriaBuilder.addDateRangeCriteria(criteria, "dealDate", dealDateFrom, dealDateTo);
CriteriaBuilder.addDateRangeCriteria(criteria, "estimatedSettlementDate.localDate", estimatedSettlementDateFrom, estimatedSettlementDateTo);
CriteriaBuilder.addDateRangeCriteria(criteria, "settlementDate", settlementDateFrom, settlementDateTo);
CriteriaBuilder.addDateRangeCriteria(criteria, "maturityDate", dealMaturityDateFrom, dealMaturityDateTo);
CriteriaBuilder.addDateRangeCriteria(criteria, "invoicedDate", invoicedDateFrom, invoicedDateTo);
CriteriaBuilder.addDateRangeCriteria(criteria, "shipmentBillDate", billOfLadingDateFrom, billOfLadingDateTo);
if (orderReference != null) {
criteria.add(Restrictions.ilike("orderReference.reference", "%" + orderReference.getReference() + "%"));
}
if (dealLinkReference != null) {
criteria.add(Restrictions.ilike("dealLinkReference.reference", "%" + dealLinkReference.getReference() + "%"));
}
if (shippingReference != null) {
criteria.add(Restrictions.ilike("shippingReference.reference", "%" + shippingReference.getReference() + "%"));
}
if (shipmentNumber != null) {
criteria.add(Restrictions.ilike("shipmentNumber", "%" + shipmentNumber + "%"));
}
if (linkedToFEC != null) {
if (linkedToFEC) {
criteria.add(Restrictions.eq("fullLinked", true));
}
if (!linkedToFEC) {
criteria.add(Restrictions.eq("fullLinked", false));
}
}
if (dealLinkCreatedDateFrom != null || dealLinkCreatedDateTo != null) {
criteria.add(Restrictions.sqlRestriction(createDealLinkCreationDateRestriction(dealLinkCreatedDateFrom, dealLinkCreatedDateTo)));
}
if (sortBy != null) {
criteria.addOrder(Property.forName("orderReference").asc()).addOrder(Property.forName("paymentType").asc()).
addOrder(Property.forName(sortBy.getSortPropertyName()).asc());
}
if (tradePurpose != null) {
criteria.add(Restrictions.eq("tradePurpose", tradePurpose));
}
if (buySellDealType != null) {
criteria.add(Restrictions.eq("buySellDealType", buySellDealType));
}
if (showAllAssociatedDeals) {
criteria.setProjection(Property.forName("orderReference.reference"));
DetachedCriteria associatedDealsCriteria = DetachedCriteria.forClass(Deal.class);
associatedDealsCriteria.add(Subqueries.propertyIn("orderReference.reference", criteria));
criteria = associatedDealsCriteria;
}
if (includeEarlyOrders != null) {
criteria.add(Restrictions.eq("earlyOrder", includeEarlyOrders));
}
criteria = criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
return criteria;
}
private static String createDealLinkCreationDateRestriction(LocalDate dealLinkCreatedDateFrom, LocalDate dealLinkCreatedDateTo) {
String date_part = "";
if (dealLinkCreatedDateFrom != null & dealLinkCreatedDateTo != null) {
date_part += "and creation_date >= '" + dealLinkCreatedDateFrom + "' and creation_date <= '" + dealLinkCreatedDateTo
+ " 23:59:59'";
} else {
if (dealLinkCreatedDateFrom != null) {
date_part += " and creation_date >= '" + dealLinkCreatedDateFrom + "'";
}
if (dealLinkCreatedDateTo != null) {
date_part += " and creation_date <= '" + dealLinkCreatedDateTo + "'";
}
}
String restrictionSQL = "{alias}.id = (SELECT DISTINCT deal_id from fec_deal_link where deal_id = {alias}.id " + date_part + ") ";
return restrictionSQL;
}
@Override
public List<Deal> findByOrderReferenceAndDealLinkReference(OrderReference orderReference, DealLinkReference dealLinkReference) {
@SuppressWarnings("unchecked")
List<Deal> results = getSessionCustom().createQuery(
"from Deal where orderReference = :orderReference and "
+ "dealLinkReference = :dealLinkReference and status != :auditStatus")
.setParameter("orderReference", orderReference).setParameter("dealLinkReference", dealLinkReference)
.setParameter("auditStatus", DealStatus.AUDIT).list();
return results;
}
private void setGroupAmount(List<Deal> deals) {
if (deals == null || deals.isEmpty()) {
return;
}
Map<String, BigDecimal> groupAmountMap = getGroupAmounts(deals);
for (Deal deal : deals) {
String key = generateGroupKey(deal);
BigDecimal groupAmount = groupAmountMap.get(key);
if (groupAmount != null) {
deal.setGroupAmount(Money.valueOf(groupAmount, deal.getCurrency()));
}
}
}
private Map<String, BigDecimal> getGroupAmounts(List<Deal> deals) {
Set<Long> dealIds = deals.stream()
.map(Deal::getId)
.collect(Collectors.toSet());
String sql = """
select order_reference, forexgroup_id, sum(amount_value)
from deal
where id in (:ids)
group by order_reference, forexgroup_id
""";
List<Object[]> rows = getSessionCustom()
.createSQLQuery(sql)
.setParameterList("ids", dealIds)
.list();
Map<String, BigDecimal> groupAmountMap = new HashMap<>();
for (Object[] row : rows) {
String orderReference = (String) row[0];
if (row[1] == null) {
continue;
}
Long forexGroupId = ((Number) row[1]).longValue();
BigDecimal sumAmount = (BigDecimal) row[2];
String key = generateGroupKey(orderReference, forexGroupId);
groupAmountMap.put(key, sumAmount);
}
return groupAmountMap;
}
private String generateGroupKey(String orderReference, Long forexGroupId) {
return orderReference + ":" + forexGroupId;
}
/**
* TTM-1489: Display the sum of the deal amounts for all deals with the same
* order reference in a column per deal.
*
* @param deal
* @return List of deals including a group amount if there is more than one
* deal with the same order reference
*/
// private Deal setGroupAmount(Deal deal) {
// if (deal != null) {
// // total all related deals
// List<BigDecimal> groupValueList = findRelatedDealsTotal(deal.getOrderReference(), deal.getForexGroup());
//
// if (groupValueList != null && groupValueList.size() > 0) {
// BigDecimal groupValue = groupValueList.get(0);
// // set the groupAmount
// deal.setGroupAmount(new Money(groupValue, deal.getAmount().getCurrency()));
// }
// }
// return deal;
// }
@Override
public Money getGroupAmount(Deal deal) {
List<BigDecimal> groupValueList = findRelatedDealsTotal(deal.getOrderReference(), deal.getForexGroup());
if (groupValueList != null && groupValueList.size() > 0) {
BigDecimal groupValue = groupValueList.get(0);
return (new Money(groupValue, deal.getAmount().getCurrency()));
}
return null;
}
private List<BigDecimal> findRelatedDealsTotal(OrderReference orderReference, ForexGroup forexGroup) {
// only sum if more than one related Deal
if (countAllByOrderRefAndForexGroup(orderReference, forexGroup, null) > 1) {
@SuppressWarnings("unchecked")
List<BigDecimal> results = (List<BigDecimal>) getSessionCustom()
.createQuery("select sum(amount.value) from Deal where orderReference = :orderReference and forexGroup = :forexGroup")
.setParameter("orderReference", orderReference).setParameter("forexGroup", forexGroup).list();
return results;
}
return null;
}
@Override
public Money getGroupAmountPaymentType(Deal deal) {
List<BigDecimal> groupValueList = findRelatedDealsTotal(deal.getOrderReference(), deal.getForexGroup(), deal.getPaymentType());
if (groupValueList != null && groupValueList.size() > 0) {
BigDecimal groupValue = groupValueList.get(0);
return (new Money(groupValue, deal.getAmount().getCurrency()));
}
return deal.getAmount();
}
private List<BigDecimal> findRelatedDealsTotal(OrderReference orderReference, ForexGroup forexGroup, String paymentType) {
// only sum if more than one related Deal
if (countAllByOrderRefAndForexGroup(orderReference, forexGroup, null) > 1) {
@SuppressWarnings("unchecked")
List<BigDecimal> results = (List<BigDecimal>) getSessionCustom()
.createQuery("select sum(amount.value) from Deal where orderReference = :orderReference and forexGroup = :forexGroup " +
"and paymentType=:paymentType")
.setParameter("orderReference", orderReference)
.setParameter("forexGroup", forexGroup)
.setParameter("paymentType", paymentType).list();
return results;
}
return null;
}
@Override
public List<Deal> findAllMatching(List<OrganisationalUnit> organisationalUnits, Currency currency, Collection<DealStatus> dealStatuses,
DateRange dateRange, ForexGroup forexGroup, TreasuryBank bank, boolean ealyOrdersOnly) {
DetachedCriteria criteria = DetachedCriteria.forClass(Deal.class, "d");
Date maturityDateFrom = dateRange.getFrom();
Date maturityDateTo = dateRange.getTo();
LocalDate ldFrom = maturityDateFrom != null ? new LocalDate(maturityDateFrom.getTime()) : null;
LocalDate ldTo = maturityDateTo != null ? new LocalDate(maturityDateTo.getTime()) : null;
if (!organisationalUnits.isEmpty()) {
criteria.add(Restrictions.in("organisationalUnit", organisationalUnits));
}
if (forexGroup != null) {
criteria.add(Restrictions.eq("forexGroup", forexGroup));
}
if (dealStatuses != null) {
criteria.add(Restrictions.in("status", dealStatuses.toArray()));
}
if (currency != null) {
criteria.add(Restrictions.eq("amount.currency", currency));
}
if (bank != null) {
criteria.add(Restrictions.eq("nominatedBank", bank));
}
if (ealyOrdersOnly)
criteria.add(Restrictions.eq("earlyOrder", true));
CriteriaBuilder.addDateRangeCriteria(criteria, "maturityDate", ldFrom, ldTo);
return (List<Deal>) findByCriteria(criteria);
}
@Override
public List<Deal> findAllMatching(OrganisationalUnit organisationalUnit, Currency currency, DealStatus dealStatus, DateRange dateRange,
ForexGroup forexGroup, boolean includeEarlyOrders) {
return findAllMatching(Collections.singletonList(organisationalUnit), currency, Collections.singleton(dealStatus), dateRange, forexGroup,
null, includeEarlyOrders);
}
@Override
public List<Deal> findAllByOrderRefAndForexGroup(OrderReference orderRef, ForexGroup forexGroup, String paymentType) {
return find(orderRef, forexGroup, null, null, paymentType);
}
@Override
public Long countAllByOrderRefAndForexGroup(OrderReference orderRef, ForexGroup forexGroup, String paymentType) {
String sqlStatement = "select count(*) from Deal where orderReference = :orderReference and forexGroup = :forexGroup";
if (StringUtils.isNotEmpty(paymentType)) {
sqlStatement = sqlStatement + " and paymentType= :paymentType";
}
Query query = getSessionCustom().createQuery(sqlStatement).setParameter("orderReference", orderRef).setParameter("forexGroup", forexGroup);
if (StringUtils.isNotEmpty(paymentType)) {
query = query.setParameter("paymentType", paymentType);
}
return (Long) query.iterate().next();
}
@Override
public List<Deal> findDealsOrderedByDealDateAsc(OrderReference orderRef, ForexGroup forexGroup, String paymentType) {
return find(orderRef, forexGroup, "dealDate", "asc", paymentType);
}
private List<Deal> find(OrderReference orderRef, ForexGroup forexGroup, String orderField, String order, String paymentType) {
String sqlStatement = "from Deal where orderReference = :orderReference and forexGroup = :forexGroup";
if (StringUtils.isNotEmpty(paymentType)) {
sqlStatement = sqlStatement + " and paymentType= :paymentType";
}
if (StringUtils.isNotEmpty(orderField)) {
sqlStatement = sqlStatement + " order by " + orderField;
if (StringUtils.isNotEmpty(order)) {
sqlStatement = sqlStatement + " " + order;
}
}
Query query = getSessionCustom().createQuery(sqlStatement).setParameter("orderReference", orderRef).setParameter("forexGroup", forexGroup);
if (StringUtils.isNotEmpty(paymentType)) {
query = query.setParameter("paymentType", paymentType);
}
// log.debug("Rows found: " + query.list().size() );
return (List<Deal>) query.list();
}
public List<Deal> findByOrderRef(String orderReference) {
String sqlStatement = "from Deal where orderReference.reference = :orderReference";
Query query = getSessionCustom().createQuery(sqlStatement).setParameter("orderReference", orderReference);
return (List<Deal>) query.list();
}
@Override
public List<String> getAllDealsSupplierNames() {
//Hack as deals are not linked to Supplier object yet,this good enough for now for filtering purpose.
Query query = getSessionCustom().createQuery("select distinct(supplier) from Deal where supplier is not null order by supplier");
List<String> list = query.list();
return list;
}
@Override
public List<String> getAllDealsSource() {
Query query = getSessionCustom().createQuery("select distinct(source) from Deal where source is not null order by source");
List<String> list = query.list();
return list;
}
@Override
public List<String> getAllDealBuyer() {
//Hack as deals are not linked to buyer object yet
Query query = getSessionCustom().createQuery("select distinct(buyer) from Deal where buyer is not null order by buyer");
List<String> list = query.list();
return list;
}
@Override
public void updateOrderReference(String orderReference, String shippingReference) {
Query query = getSessionCustom().createQuery("update Deal set orderReference.reference= :orderReference " +
"where shippingReference.reference= :shippingReference");
query.setParameter("orderReference", orderReference).setParameter("shippingReference", shippingReference);
query.executeUpdate();
}
@Override
public void updateDealReference(String orderReference, String orderNumber) {
Query query = getSessionCustom().createQuery("update Deal set orderReference.reference= :orderReference " +
"where orderNumber= :orderNumber");
query.setParameter("orderReference", orderReference).setParameter("orderNumber", orderNumber);
query.executeUpdate();
}
@Override
public void updateShipmentInfo(List<String> dealsToUpdate, Date billOfLadingDate, String reference, String number) {
if (dealsToUpdate == null || dealsToUpdate.isEmpty()) {
log.debug("No deals to update");
return;
}
// Query query = getSessionCustom().createSQLQuery("update Deal set shipment_date=:billOfLadingDate " +
// " where order_reference in (:dealsToUpdate) ");
Query query = getSessionCustom().createQuery("update Deal set shipmentBillDate=:billOfLadingDate, shipmentNumber=:shipmentNumber," +
"dealLinkReference.reference=:shipReference" +
" where orderReference.reference in (:dealsToUpdate) ");
query.setDate("billOfLadingDate", billOfLadingDate)
.setParameter("shipmentNumber", number)
.setParameterList("dealsToUpdate", dealsToUpdate)
.setParameter("shipReference", reference);
query.executeUpdate();
}
@Override
public long count(DealSearchDTO search) {
SortBy sortBy = null;//don't sort on count.
DetachedCriteria detachedCriteria = getDetachedCriteria(search, sortBy);
return getExecutableCriteriaCount(detachedCriteria);
}
private DetachedCriteria getDetachedCriteria(DealSearchDTO search, SortBy sortBy) {
DefaultDTO organisationalUnit1 = search.getOrganisationalUnit();
OrganisationalUnit organisationalUnit = organisationalUnit1 != null ?
generalRepository.retrieve(OrganisationalUnit.class, organisationalUnit1.getId()) : null;
List<DealStatus> dealStatuses = search.getStatus() != null ? Arrays.asList(DealStatus.get(search.getStatus())) : null;
Currency currency = search.getCurrency() != null ? Currency.getInstance(search.getCurrency().getCode()) : null;
Boolean linkedToFECValue = getLinkedAsBoolean(search);
LocalDateTime dealDateFrom = DateUtils.toLocalDateTime(search.getDealDate().getFrom());
LocalDateTime dealDateTo = DateUtils.toLocalDateTime(search.getDealDate().getTo());
LocalDate estimateSettlementDateFrom = DateUtils.toLocalDate(search.getEstimateSettlementDate().getFrom());
LocalDate estimateSettlementDateTo = DateUtils.toLocalDate(search.getEstimateSettlementDate().getTo());
LocalDate settlementDateFrom = DateUtils.toLocalDate(search.getSettlementDate().getFrom());
LocalDate settlementDateTo = DateUtils.toLocalDate(search.getSettlementDate().getTo());
LocalDate maturityDateFrom = DateUtils.toLocalDate(search.getMaturityDate().getFrom());
LocalDate maturityDateTo = DateUtils.toLocalDate(search.getMaturityDate().getTo());
LocalDate invoiceDateFrom = DateUtils.toLocalDate(search.getInvoiceDate().getFrom());
LocalDate invoiceDateTo = DateUtils.toLocalDate(search.getInvoiceDate().getTo());
LocalDate billOfLadingDateFrom = DateUtils.toLocalDate(search.getBillOfLadingDate().getFrom());
LocalDate billOfLadingDateTo = DateUtils.toLocalDate(search.getBillOfLadingDate().getTo());
String shipmentNumber = search.getShipmentNumber();
OrderReference orderReference = search.getOrderReference() != null ? OrderReference.valueOf(search.getOrderReference()) : null;
DealLinkReference dealLinkReference = search.getDealLinkReference() != null ? DealLinkReference.valueOf(search.getDealLinkReference()) : null;
ShippingReference shippingReference = search.getShippingReference() != null ? new ShippingReference(search.getShippingReference()) : null;
TradePurpose tradePurpose = search.getPurpose() != null ? TradePurpose.get(search.getPurpose()) : null;
DefaultDTO forexGroup1 = search.getForexGroup();
ForexGroup forexGroup = forexGroup1 != null ? generalRepository.retrieve(ForexGroup.class, forexGroup1.getId()) : null;
DefaultDTO nominatedBank = search.getNominatedBank();
TreasuryBank treasuryBank = nominatedBank != null ? generalRepository.retrieve(TreasuryBank.class, nominatedBank.getCode()) : null;
return getDetachedCriteria(organisationalUnit, dealStatuses, currency, linkedToFECValue, dealDateFrom, dealDateTo,
estimateSettlementDateFrom, estimateSettlementDateTo, settlementDateFrom, settlementDateTo, maturityDateFrom, maturityDateTo,
invoiceDateFrom, invoiceDateTo, orderReference, dealLinkReference, shippingReference, sortBy,
search.isShowAllAssociatedDeals(), tradePurpose, null, null, forexGroup, search.getSupplier(),
search.getBuySellDealType(), search.getSource(), search.getTransactionType(), search.getBuyer(), treasuryBank, shipmentNumber,
billOfLadingDateFrom, billOfLadingDateTo, search.getIncludeEarlyOrders());
}
@Override
public List<Deal> search(DealSearchDTO search) {
SortBy sortBy = search.getSortBy();
DetachedCriteria detachedCriteria = getDetachedCriteria(search, sortBy);
//SearchMetaParams searchMetaParams = search.isSelectAll() ? null : search.getSearchMetaParams();
return getExecutableCriteriaList(detachedCriteria, search.getSearchMetaParams());
}
private Boolean getLinkedAsBoolean(DealSearchDTO search) {
String linked = search.getLinked();
if (linked == null || linked.isEmpty()) {
return null;
}
return linked.equalsIgnoreCase("YES");
}
@Override
public Long countAllByOrderReference(OrderReference orderRef) {
String sqlStatement = "select count(*) from Deal where orderReference = :orderReference ";
Query query = getSessionCustom().createQuery(sqlStatement).setParameter("orderReference", orderRef);
return (Long) query.iterate().next();
}
@Override
public void updateDealFromPayment(OrderReference orderReference, Date paymentDate, ForexGroup forexGroup, TreasuryBank nominatedBank) {
Query query = getSessionCustom().createQuery("update Deal set settlementDate= :paymentDate, nominatedBank=:nominatedBank " +
"where forexGroup = :forexGroup and orderReference= :orderReference and status= :status");
query.setParameter("orderReference", orderReference)
.setParameter("forexGroup", forexGroup)
.setParameter("paymentDate", LocalDate.fromDateFields(paymentDate))
.setParameter("nominatedBank", nominatedBank)
.setParameter("status", DealStatus.SETTLED);
query.executeUpdate();
}
@Override
public void updateDealOrderSettledAmount(OrderReference orderReference, ForexGroup forexGroup, String splitPaymentType,
BigDecimal settledAmount) {
Query query = getSessionCustom().createQuery("update Deal set orderSettledAmount= :orderSettledAmount " +
"where forexGroup = :forexGroup and orderReference= :orderReference and paymentType= :paymentType");
query.setParameter("orderReference", orderReference)
.setParameter("forexGroup", forexGroup)
.setParameter("orderSettledAmount", settledAmount)
.setParameter("paymentType", splitPaymentType);
query.executeUpdate();
}
@Override
public void updateDealNominatedBank(OrderReference orderReference, ForexGroup forexGroup, TreasuryBank nominatedBank) {
Query query = getSessionCustom().createQuery("update Deal set nominatedBank=:nominatedBank " +
"where forexGroup = :forexGroup and orderReference= :orderReference ");
query.setParameter("orderReference", orderReference)
.setParameter("forexGroup", forexGroup)
.setParameter("nominatedBank", nominatedBank);
query.executeUpdate();
}
@Override
public BigDecimal calculateOrderDealPosition(OrderReference orderRef, long forexGroupId) {
String sqlStatement = "select sum(amount.value) from Deal where orderReference = :orderReference and forexGroup.id = :forexGroupId";
Query query = getSessionCustom().createQuery(sqlStatement).setParameter("orderReference", orderRef)
.setParameter("forexGroupId", forexGroupId);
final Object next = query.iterate().next();
return next == null ? BigDecimal.ZERO : (BigDecimal) next;
}
@Override
public boolean isNotInStatus(OrderReference orderReference, DealStatus dealStatus) {
String sqlStatement = "select count(*) from Deal where orderReference = :orderReference and status<> :dealStatus";
Query query = getSessionCustom().createQuery(sqlStatement).setParameter("orderReference", orderReference).
setParameter("dealStatus", dealStatus);
return (Long) query.iterate().next() > 0;
}
@Override
public List<String> getDealPaymentTypes(String orderReference, ForexGroup forexGroup) {
String sqlStatement = "select distinct(paymentType) from Deal where orderReference.reference = :orderReference and " +
"forexGroup = :forexGroup order by paymentType";
Query query = getSessionCustom().createQuery(sqlStatement).setParameter("orderReference", orderReference).
setParameter("forexGroup", forexGroup);
return query.list();
}
@Override
public List<NetPositionOrdersDTO> netPositionOrdersReport(ReportSearchDTO reportSearchDTO) {
StringBuilder builder = new StringBuilder("Select ");
buildQuery(reportSearchDTO, builder);
builder.append(" order by d.order_reference; ");
// builder.append(" order by orderreference,invoicereference");
NativeQuery sqlQuery = getSessionCustom().createSQLQuery(builder.toString());
if (reportSearchDTO.getMaturityDate() != null) {
if (reportSearchDTO.getMaturityDate().getFrom() != null) {
sqlQuery.setParameter("maturity_date_from", reportSearchDTO.getMaturityDate().getFrom());
}
if (reportSearchDTO.getMaturityDate().getTo() != null) {
sqlQuery.setParameter("maturity_date_to", reportSearchDTO.getMaturityDate().getTo());
}
}
if (reportSearchDTO.getOrganisationalUnit() != null) {
sqlQuery.setParameter("orgId", reportSearchDTO.getOrganisationalUnit().getId());
}
if (reportSearchDTO.getAmount() != null && reportSearchDTO.getAmount().getCurrency() != null &&
reportSearchDTO.getAmount().getCurrency().getCode() != null) {
sqlQuery.setParameter("currency", reportSearchDTO.getAmount().getCurrency().getCode());
}
if (reportSearchDTO.getForexGroup() != null && reportSearchDTO.getForexGroup().getId() != null) {
sqlQuery.setParameter("forexgroup_id", reportSearchDTO.getForexGroup().getId());
}
Query query = sqlQuery
.addScalar("orderReference", StandardBasicTypes.STRING)
.addScalar("shipmentReference", StandardBasicTypes.STRING)
.addScalar("supplier", StandardBasicTypes.STRING)
.addScalar("currentSettlementDate", StandardBasicTypes.DATE)
.addScalar("orderState", StandardBasicTypes.STRING)
.addScalar("orderOrganisationalUnit", StandardBasicTypes.STRING)
.addScalar("foreignOrderAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("forexGroup", StandardBasicTypes.STRING)
.addScalar("fecMaturityDate", StandardBasicTypes.DATE)
.addScalar("orderAmountLinkedToFec", StandardBasicTypes.BIG_DECIMAL)
.addScalar("bankReference", StandardBasicTypes.STRING)
.addScalar("fecSpotRate", StandardBasicTypes.BIG_DECIMAL)
.addScalar("fecForwardRate", StandardBasicTypes.BIG_DECIMAL)
.addScalar("fecCashFlowCostingRate", StandardBasicTypes.BIG_DECIMAL)
.addScalar("fecAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("fecUnlinkedAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("fecOrganisationalUnit", StandardBasicTypes.STRING)
.addScalar("fecCurrency", StandardBasicTypes.STRING)
.addScalar("shippingReference", StandardBasicTypes.STRING)
.setResultTransformer(Transformers.aliasToBean(NetPositionOrdersDTO.class));
List<NetPositionOrdersDTO> list = query.list();
List<NetPositionOrdersDTO> finalList = new ArrayList<>();
for (NetPositionOrdersDTO netPositionOrdersDTO : list) {
if (!MathUtils.areNumbersCloselyEqual(netPositionOrdersDTO.getOrderAmountLinkedToFec().abs(), BigDecimal.ZERO, 3)) {
finalList.add(netPositionOrdersDTO);
}
}
return finalList;
}
private void buildQuery(ReportSearchDTO reportSearchDTO, StringBuilder builder) {
appendFields(builder);
appendTables(builder);
appendWhereClause(reportSearchDTO, builder);
appendGrouping(builder);
}
private void appendWhereClause(ReportSearchDTO reportSearchDTO, StringBuilder builder) {
builder.append(" where f.id is not null ");
if (reportSearchDTO.getMaturityDate() != null) {
if (reportSearchDTO.getMaturityDate().getFrom() != null) {
builder.append(" and f.maturity_date>=:maturity_date_from");
}
if (reportSearchDTO.getMaturityDate().getTo() != null) {
builder.append(" and f.maturity_date<=:maturity_date_to");
}
}
if (reportSearchDTO.getOrganisationalUnit() != null) {
builder.append(" and d.organisationalunit_id=:orgId");
}
if (reportSearchDTO.getAmount() != null && reportSearchDTO.getAmount().getCurrency() != null &&
reportSearchDTO.getAmount().getCurrency().getCode() != null) {
builder.append(" and d.amount_currency=:currency");
}
if (reportSearchDTO.getForexGroup() != null && reportSearchDTO.getForexGroup().getId() != null) {
builder.append(" and d.forexgroup_id=:forexgroup_id");
}
}
private void appendGrouping(StringBuilder builder) {
builder.append(" group by d.order_reference,d.forexgroup_id,d.organisationalunit_id,f.id,d.shippingreference ");
}
private void appendTables(StringBuilder builder) {
builder.append(" from deal d ");
builder.append(" join fec_deal_link fdl on (d.id=fdl.deal_id) ");
builder.append(" join fec f on (f.id=fdl.fec_id) ");
}
private void appendFields(StringBuilder builder) {
builder.append("d.order_reference as orderReference,");
builder.append("d.shippingReference ,");
builder.append("(select deal_link_reference from deal where order_reference=d.order_reference and forexgroup_id=d.forexgroup_id limit 1) " +
"as shipmentReference,");
builder.append("(select supplier from deal where order_reference=d.order_reference and forexgroup_id=d.forexgroup_id limit 1) as supplier,");
builder.append("(select estimated_settlement_date from deal where order_reference=d.order_reference and forexgroup_id=d.forexgroup_id " +
"order by estimated_settlement_date desc limit 1) as currentSettlementDate,");
builder.append("(select status from deal where order_reference=d.order_reference and forexgroup_id=d.forexgroup_id limit 1) as orderState,");
builder.append("(select name from organisationalunit where id=d.organisationalunit_id limit 1) as orderOrganisationalUnit,");
builder.append("(select sum(amount_value) from deal where order_reference=d.order_reference and forexgroup_id=d.forexgroup_id) " +
"as foreignOrderAmount,");
builder.append("(select name from forexgroup where id=d.forexgroup_id) as forexGroup,");
builder.append("f.maturity_date as fecMaturityDate,");
builder.append("sum(fdl.amount_value) as orderAmountLinkedToFec,");
builder.append("f.bank_reference as bankReference,");
builder.append("f.spot_rate_value as fecSpotRate,");
builder.append("f.forward_rate_value as fecForwardRate,");
builder.append("f.costing_rate_value as fecCashFlowCostingRate,");
builder.append("f.amount_value as fecAmount,");
builder.append("f.unlinked_amount_value as fecUnlinkedAmount,");
builder.append("f.amount_currency as fecCurrency,");
builder.append("(select name from organisationalunit where id=f.organisationalunit_id limit 1) as fecOrganisationalUnit");
}
@Override
public List<ShipmentDealLinkedResult> shipmentDealsLinkedToFEC(ReportSearchDTO reportSearchDTO) {
String orderby = " order by d.order_reference,fg.name";
String QUERY_SELECT = "select d.shippingReference, fg.name as forexGroup,fou.name as tradingCompany,ou.name as division," +
"d.amount_currency as currency," +
"f.bank_reference as bankReference,f.forward_rate_value as FECForwardRate,rate_value as CashFlowCostingRate," +
"d.order_reference as purchaseOrderReference,f.maturity_date as FecMaturityDate,round(l.amount_value,2) as Amount," +
" round((l.amount_value*rate_value),2) as CashflowFECCostingRateLocalCurrencyAmount,TO_CHAR(d.created,'yyyy-mm-dd') as DealDate," +
"TO_CHAR(d.shipment_date,'yyyy-mm-dd') as billofladingdate,d.deal_link_reference as shipmentreference,shipnumber as shipmentNumber," +
"estimated_settlement_date as estimatedSettlementDate,d.invoice_reference as InvoiceReference," +
"TO_CHAR(now(),'yyyy-mm-dd HH24:MI:SS') as dateandtime from deal d join fec_deal_link l on (l.deal_id=d.id) " +
"join fec f on (f.id=l.fec_id) left join organisationalunit ou on ou.id=d.organisationalunit_id " +
"join forexgroup fg on d.forexgroup_id=fg.id left join organisationalunit fou on fou.id=f.organisationalunit_id " +
"where d.deal_link_reference is not null and d.shipment_date is not null ";
StringBuilder builder = new StringBuilder(QUERY_SELECT);
setPredicates(reportSearchDTO, builder);
if (reportSearchDTO.getForexGroup() != null && reportSearchDTO.getForexGroup().getId() != null) {
builder.append(" and d.forexgroup_id=:forexGroup");
}
builder.append(orderby);
NativeQuery sqlQuery = getSessionCustom().createNativeQuery(builder.toString());
setParameter(reportSearchDTO, builder, sqlQuery);
sqlQuery
.addScalar("forexGroup", StandardBasicTypes.STRING)
.addScalar("tradingCompany", StandardBasicTypes.STRING)
.addScalar("division", StandardBasicTypes.STRING)
.addScalar("currency", StandardBasicTypes.STRING)
.addScalar("bankReference", StandardBasicTypes.STRING)
.addScalar("fecForwardRate", StandardBasicTypes.BIG_DECIMAL)
.addScalar("cashFlowCostingRate", StandardBasicTypes.BIG_DECIMAL)
.addScalar("purchaseOrderReference", StandardBasicTypes.STRING)
.addScalar("fecMaturityDate", StandardBasicTypes.DATE)
.addScalar("amount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("cashflowFECCostingRateLocalCurrencyAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("dealDate", StandardBasicTypes.DATE)
.addScalar("billofLadingDate", StandardBasicTypes.DATE)
.addScalar("shipmentReference", StandardBasicTypes.STRING)
.addScalar("shipmentNumber", StandardBasicTypes.STRING)
.addScalar("estimatedSettlementDate", StandardBasicTypes.DATE)
.addScalar("invoiceReference", StandardBasicTypes.STRING)
.addScalar("shippingReference", StandardBasicTypes.STRING)
.setResultTransformer(Transformers.aliasToBean(ShipmentDealLinkedResult.class));
List<ShipmentDealLinkedResult> list = sqlQuery.list();
return list;
}
private void setPredicates(ReportSearchDTO reportSearchDTO, StringBuilder builder) {
DateRange billOfLadingDate = reportSearchDTO.getShipmentBillOfLadingDate();
if (billOfLadingDate != null) {
if (billOfLadingDate.getFrom() != null) {
builder.append(" and d.shipment_date>=:billDateFrom");
}
if (billOfLadingDate.getTo() != null) {
builder.append(" and d.shipment_date<=:billDateTo");
}
}
if (reportSearchDTO.getOrganisationalUnit() != null) {
builder.append(" and d.organisationalunit_id=:orgId");
}
}
private void setParameter(ReportSearchDTO reportSearchDTO, StringBuilder builder, NativeQuery sqlQuery) {
DateRange billOfLadingDate = reportSearchDTO.getShipmentBillOfLadingDate();
if (billOfLadingDate != null) {
if (billOfLadingDate.getFrom() != null) {
sqlQuery.setParameter("billDateFrom", billOfLadingDate.getFrom());
}
if (billOfLadingDate.getTo() != null) {
sqlQuery.setParameter("billDateTo", billOfLadingDate.getTo());
}
}
if (reportSearchDTO.getOrganisationalUnit() != null) {
sqlQuery.setParameter("orgId", reportSearchDTO.getOrganisationalUnit().getId());
}
if (reportSearchDTO.getForexGroup() != null && reportSearchDTO.getForexGroup().getId() != null) {
sqlQuery.setParameter("forexGroup", reportSearchDTO.getForexGroup().getId());
}
}
@Override
public List<Deal> find(Set<Long> ids) {
if (ids == null || ids.isEmpty()) {
return Collections.emptyList();
}
List<Deal> deals = getSessionCustom()
.createQuery("from Deal d where d.id in (:ids)", Deal.class)
.setParameter("ids", ids)
.list();
setGroupAmount(deals);
return deals;
}
private String generateGroupKey(Deal deal) {
String orderRef = (deal.getOrderReference() != null) ? deal.getOrderReference().getReference() : "NO_ORDER";
Long forexGroupId = (deal.getForexGroup() != null) ? deal.getForexGroup().getId() : null;
return generateGroupKey(orderRef, forexGroupId);
}
}