SupplyPaymentReportRepositoryImpl.java
package com.tradecloud.repository.costingdocument.impl;
import com.tradecloud.domain.document.Document;
import com.tradecloud.domain.document.DocumentState;
import com.tradecloud.domain.document.PaymentState;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.dto.invoice.SupplyPaymentReportSearchResult;
import com.tradecloud.dto.invoice.SupplyPaymentSearch;
import com.tradecloud.repository.base.impl.CriteriaValue;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import com.tradecloud.repository.costingdocument.SupplyPaymentReportRepository;
import org.hibernate.Query;
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.util.*;
/**
* Created with IntelliJ IDEA.
* User: ds
* Date: 2014/04/11
* Time: 10:07 AM
* To change this template use File | Settings | File Templates.
*/
@Repository(value = "supplyPaymentReportRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class SupplyPaymentReportRepositoryImpl extends RepositoryBaseImpl<Document, SupplyPaymentSearch> implements SupplyPaymentReportRepository {
private static final int BATCH_SIZE = 1000;
@Override
public long supplyPaymentReportCount(SupplyPaymentSearch search) {
String tableName = search.getTableName().toLowerCase();
Query query =
getQuery(search, true, "", supplyPaymentCriteriaValues(search),
createJoinStringForSupplyPayment(search), null, "select count (distinct " + tableName + ") ");
return getQueryCount(query);
}
private String getCostsInvoiceHql() {
String commInvoiceIds = "Select distinct c.id from CommercialInvoice c where c.id in (:ids)";
String commCreditNoteIds = "Select distinct ccn.id from CommercialCreditNote ccn where ccn.commercialInvoice.id in (:ids)";
return "from CostsInvoice ci where ci.id in (" + commInvoiceIds + ") or ci.id in (" + commCreditNoteIds + ") ";
}
@Override
@Transactional(readOnly = true)
public List supplyPaymentSearch(SupplyPaymentSearch search) {
String tableName = search.getTableName().toLowerCase();
String[] orderBy =
new String[]{tableName + ".estimatedSettlementDate ASC", tableName + ".reference", "ps.id"};
Query query =
getQuery(search, false, "", supplyPaymentCriteriaValues(search),
createJoinStringForSupplyPayment(search), orderBy, "select " + " ps.id ");
List<Long> queryList = query.list();
return paymentSqlReport(new HashSet<>(queryList), search);
}
private List<SupplyPaymentReportSearchResult> paymentSqlReport(Set<Long> plannedSettlementIds, SupplyPaymentSearch search) {
if (plannedSettlementIds == null || plannedSettlementIds.isEmpty()) {
return Collections.emptyList();
}
List<SupplyPaymentReportSearchResult> results = new ArrayList<>();
List<Long> idList = new ArrayList<>(plannedSettlementIds);
for (int i = 0; i < idList.size(); i += BATCH_SIZE) {
List<Long> batch = idList.subList(i, Math.min(i + BATCH_SIZE, idList.size()));
results.addAll(executeSingleBatch(batch, search));
}
return results;
}
@SuppressWarnings("unchecked")
private List<SupplyPaymentReportSearchResult> executeSingleBatch(List<Long> batchIds, SupplyPaymentSearch search) {
StringBuilder builder = new StringBuilder("Select ");
appendFields(builder, search);
builder.append(" from plannedsettlement ps ");
builder.append(" join commercialinvoice ci on (ci.id=ps.invoice_id) ");
builder.append(" join costsinvoice cinv on (cinv.id=ci.id and cinv.state<>'DELETED') ");
builder.append(" join shipment ship on (cinv.shipment_id=ship.id and ship.state<>'DELETED') ");
builder.append(" left join organisationalunit org on (org.id=ci.organisationalunit_id ) ");
builder.append(" left join seashipment sea on (ship.id=sea.id) ");
builder.append(" left join airshipment air on (ship.id=air.id) ");
builder.append(" left join organisationalunitsupplier os on ( os.id=ci.supplier_id)");
builder.append(" left join supplier s on (s.id=os.supplier_id)");
builder.append(" where ps.id in ( :psIds )");
Query query = getSessionCustom().createSQLQuery(builder.toString())
.addScalar("documentId", StandardBasicTypes.LONG)
.addScalar("documentReference", StandardBasicTypes.STRING)
.addScalar("documentDate", StandardBasicTypes.DATE)
.addScalar("originalDocumentsReceivedDate", StandardBasicTypes.DATE)
.addScalar("transportDocumentNumber", StandardBasicTypes.STRING)
.addScalar("shipmentReference", StandardBasicTypes.STRING)
.addScalar("shipmentNumber", StandardBasicTypes.STRING)
.addScalar("shippingReference", StandardBasicTypes.STRING)
.addScalar("orderReferences", StandardBasicTypes.STRING)
.addScalar("businessUnit", StandardBasicTypes.STRING)
.addScalar("supplierName", StandardBasicTypes.STRING)
.addScalar("alcForwardRate", StandardBasicTypes.STRING)
.addScalar("documentValue", StandardBasicTypes.BIG_DECIMAL)
.addScalar("documentCurrency", StandardBasicTypes.STRING)
.addScalar("paidAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("paymentReferences", StandardBasicTypes.STRING)
.addScalar("paymentDates", StandardBasicTypes.STRING)
.addScalar("methodTerm", StandardBasicTypes.STRING)
.addScalar("billOfLandingDate", StandardBasicTypes.DATE)
.addScalar("settlementDate", StandardBasicTypes.DATE)
.addScalar("paymentState", StandardBasicTypes.STRING)
.addScalar("documentState", StandardBasicTypes.STRING)
.addScalar("documentComments", StandardBasicTypes.STRING)
.addScalar("nominatedBank", StandardBasicTypes.STRING)
.addScalar("invoiceForwardRateOfExchange", StandardBasicTypes.BIG_DECIMAL)
.addScalar("supplierCode", StandardBasicTypes.STRING)
.setResultTransformer(Transformers.aliasToBean(SupplyPaymentReportSearchResult.class));
query.setParameterList("psIds", batchIds);
return query.list();
}
private void appendFields(StringBuilder builder, SupplyPaymentSearch search) {
builder.append("s.externalreference as supplierCode,");
builder.append("cinv.id as documentId,");
builder.append("cinv.reference as documentReference,");
builder.append("cinv.datecreated as documentDate,");
builder.append("ship.originalDocumentsReceivedDate as originalDocumentsReceivedDate,");
builder.append(" COALESCE( sea.billofladingreference,air.houseairwaybillnumber ) as transportDocumentNumber, ");
builder.append("ship.reference as shipmentReference, ship.number as shipmentNumber, ");
// builder.append(" (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 si.shippingReference from shippinginformation si ");
builder.append(" inner join orders o on o.shippinginformation_id = si.id ");
builder.append(" where o.id = ");
builder.append(" (select ao.originalid from actualorder ao ");
builder.append(" join actualconsignment ac on (ao.actualconsignment_id=ac.id) ");
builder.append(" where costsinvoice_id = ci.id limit 1)) as shippingReference ,");
builder.append(" (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 orderreferences,");
builder.append(" org.name as businessUnit, ");
builder.append(" s.name as supplierName, ");
builder.append("(select string_agg(to_char(forwardrate,'999.9999'),'|') from plannedsettlementorder where" +
" plannedsettlement_id=ps.id and amount>0) as alcForwardRate,");
builder.append("ps.amount as documentValue, ");
builder.append("ps.currency_code as documentCurrency,");
builder.append("(select sum(amount) from payment where plannedsettlement_id =ps.id) as paidAmount,");
builder.append("(select string_agg(reference,'|') from payment where plannedsettlement_id =ps.id) as paymentreferences,");
builder.append("(select string_agg(to_char(p.paymentdate,'" + search.getDateFormat() + "'),'|') from payment p where " +
"p.plannedsettlement_id =ps.id ) as paymentdates,");
builder.append("(select concat((select name from paymentterm where code= paymentterm_code),'|'," +
"(select name from paymentmethod where code= paymentmethod_code)) from orders where id = ");
builder.append(" (select ao.originalid 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 limit 1)) as methodTerm,");
builder.append(" COALESCE( sea.billofladingdate,air.houseairwaybillissuedate ) as billOfLandingDate, ");
builder.append(" ps.settlementdate as settlementdate,");
builder.append(" ps.paymentstate as paymentstate,");
builder.append(" cinv.state as documentState,");
builder.append("(select string_agg(concat(commenttext,';',commenttype),'|') " +
"from comment c join costsinvoice_comment cic on (c.id=cic.costsinvoice_id) where costsinvoice_id=cinv.id) as documentComments,");
builder.append(" COALESCE((select nominatedbank_code from payment where plannedsettlement_id=ps.id and nominatedbank_code " +
"is not null limit 1),s.nominatedbank_code) as nominatedBank, ")
.append("round(ps.forwardrate,4) as invoiceForwardRateOfExchange ");
}
private String createJoinStringForSupplyPayment(SupplyPaymentSearch search) {
String tableName = (search).getTableName().toLowerCase();
StringBuilder sb = new StringBuilder(" left join " + tableName + ".shipment as s ");
sb.append(" left join " + tableName + ".plannedSettlements as ps ");
sb.append(" left join ps.payments as py ");
sb.append(" left join " + tableName + ".actualConsignments as c ");
sb.append(" left join c.actualOrders as o ");
if (search.isFilteredByUserOrg()) {
sb.append(" left join s.consignments as con ");
sb.append(" left join con.orders as ord ");
}
sb.append(" where " + tableName + ".state<>'DELETED'and ps is not null ");
return sb.toString();
}
private Collection<CriteriaValue> supplyPaymentCriteriaValues(SupplyPaymentSearch search) {
String tableName = (search).getTableName().toLowerCase();
Collection<CriteriaValue> fields = new LinkedHashSet<CriteriaValue>();
if (search.getShipmentReference() != null) {
fields.add(CriteriaValue.eq("s.reference", search.getShipmentReference()));
}
if (search.getOrderReference() != null) {
fields.add(CriteriaValue.eq("o.reference", search.getOrderReference()));
}
if (search.getShippingReference() != null) {
fields.add(CriteriaValue.eq("o.shippingReference", "%" + search.getShippingReference()));
}
Set<OrganisationalUnit> organisationalUnits = Collections.EMPTY_SET;
if (search.isFilteredByUserOrg()) {
organisationalUnits = getUserOrganisationalUnits();
fields.add(CriteriaValue.in("ord.organisationalUnit", organisationalUnits));
} else if (search.getOrganisationalUnit() != null) {
fields.add(CriteriaValue.eq(tableName + ".organisationalUnit", search.getOrganisationalUnit()));
}
if (search.getDocumentReference() != null) {
fields.add(CriteriaValue.eq(tableName + ".reference", search.getDocumentReference()));
}
if (search.getDocumentCurrency() != null) {
fields.add(CriteriaValue.eq(tableName + ".currency", search.getDocumentCurrency()));
}
//payment state, from the front end, cannot be null
if (search.getPaymentState() != null) {
fields.add(CriteriaValue.eq("ps.paymentState", search.getPaymentState()));
if (PaymentState.SETTLED.equals(search.getPaymentState())) {
if (search.getSettlementDateFrom() != null && search.getSettlementDateTo() != null) {
fields.add(CriteriaValue.gt("py.paymentDate", search.getSettlementDateFrom()));
fields.add(CriteriaValue.lt("py.paymentDate", search.getSettlementDateTo()));
}
} else if (PaymentState.UNSETTLED.equals(search.getPaymentState())) {
if (search.getSettlementDateFrom() != null && search.getSettlementDateTo() != null) {
fields.add(CriteriaValue.gt("ps.settlementDate", search.getSettlementDateFrom()));
fields.add(CriteriaValue.lt("ps.settlementDate", search.getSettlementDateTo()));
}
}
} else {
fields.add(CriteriaValue.in("ps.paymentState", new PaymentState[]{PaymentState.SETTLED, PaymentState.UNSETTLED}));
//todo why over paid state excluded
if (search.getSettlementDateFrom() != null && search.getSettlementDateTo() != null) {
fields.add(CriteriaValue.gt(tableName + ".estimatedSettlementDate", search.getSettlementDateFrom()));
fields.add(CriteriaValue.lt(tableName + ".estimatedSettlementDate", search.getSettlementDateTo()));
}
}
if (search.getDocumentState() != null) {
fields.add(CriteriaValue.eq(tableName + ".state", search.getDocumentState()));
} else {
fields.add(CriteriaValue.in(tableName + ".state", new DocumentState[]{DocumentState.ACCEPTED_BY_ERP,
DocumentState.AWAITING_TREASURY_RATES,
DocumentState.COMPLETE,
DocumentState.DOCUMENT_TOLERANCE_EXCEEDED,
DocumentState.FINALISED,
DocumentState.INITIALISED,
DocumentState.REJECTED_BY_ERP,
DocumentState.SENT_TO_ERP,
DocumentState.SETTLED,
DocumentState.SIGNED_OFF}));
}
if (search.getSupplier() != null) {
fields.add(CriteriaValue.eq(tableName + ".supplier", search.getSupplier()));
}
return fields;
}
}