ContainerReportRepositoryImpl.java
package com.tradecloud.repository.impl;
import com.tradecloud.domain.dto.base.SearchBase;
import com.tradecloud.domain.model.ordermanagement.OrderState;
import com.tradecloud.domain.model.ordermanagement.PurchaseOrder;
import com.tradecloud.dto.container.ContainersAvailNotDelivToFDSearch;
import com.tradecloud.dto.invoice.InvoiceOrderValue;
import com.tradecloud.repository.base.impl.CriteriaOperation;
import com.tradecloud.repository.base.impl.CriteriaValue;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import com.tradecloud.repository.utils.ContainerReportRepository;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
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.*;
import java.util.stream.Collectors;
/**
* Container specific repository.
*/
@Repository(value = "containerReportRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class ContainerReportRepositoryImpl extends RepositoryBaseImpl<PurchaseOrder, ContainersAvailNotDelivToFDSearch>
implements ContainerReportRepository<PurchaseOrder> {
private static Logger log = Logger.getLogger(ContainerReportRepositoryImpl.class);
private static final String ASC = "ASC";
private static final long serialVersionUID = 1L;
private static final String NUMBER = "number";
private static final String ORDER_REFERENCE = "orderReference";
private static final String CURRENCY = "currency";
private static final String ORGANISATIONAL_UNIT = "purchaseorder.organisationalUnit.name";
private static final String ORGANISATIONAL_UNIT_ID = "purchaseorder.organisationalUnit.id";
private static final String STATE = "state";
private static final String CREATED = "created";
private static final String SHIPPING_MODE = "purchaseorder.shippingInformation.shippingMode";
private static final String PLACE_OF_LOADING = "purchaseorder.shippingInformation.placeOfLoading";
private static final String BUYER = "buyer";
private static final String SHIPPING_REFERENCE = "purchaseorder.shippingInformation.shippingReference";
private static final String LATEST_SHIPMENT_DATE = "purchaseorder.latestShipmentDate";
private static final String ORDER_DATE = "orderdate";
private static final String PAYMENT_METHOD_CODE = "purchaseorder.paymentMethod.code";
private static final String CONSIGNMENT_STATE = "purchaseorder.consignment.state";
private static final String SUPPLIER = "purchaseorder.supplier";
private static final String SET_SUPPLIER = "supplier";
private static final String SHIPMENT_CREATION_DATE = "purchaseorder.consignment.shipment.created";
private static final String SHIPPED_ON_BOARD_DATE = "purchaseorder.consignment.shipment.shippedOnBoardDate";
private static final String BILL_OF_LADING_DATE = "purchaseorder.consignment.shipment.billOfLadingDate";
private static final String HOUSE_AIRWAY_BILL_ISSUE_DATE = "purchaseorder.consignment.shipment.houseAirwayBillIssueDate";
private static final String ACTUAL_DEPARTURE_DATE = "purchaseorder.consignment.shipment.actualDepartureDate";
private static final String SHIPMENT_REFERENCE = "purchaseorder.consignment.shipment.reference";
private static final String FREIGHT_FORWARDER = "purchaseorder.shippingInformation.freightForwarder";
private static final String CLEARING_AGENT = "purchaseorder.shippingInformation.clearingAgent";
private static final String TRANSPORTER = "purchaseorder.shippingInformation.transporter";
private static final String PICKUP_DATE = "cont.containerDates.pickUpDate";
private static final String GOODS_RECEIVED_DATE = "purchaseorder.orderDates.goodsReceivedDate";
private static final String ETA_POD_DATE = "purchaseorder.consignment.shipment.estimatedArrivalDateAtPlaceOfDischarge";
private static final String PLACE_OF_DISCHARGE = "purchaseorder.shippingInformation.placeOfDischarge";
private static final String DIVISION = "organisationalUnit.parent";
private static final String REQUIRED_ON_SITE_DATE = "orderDates.requiredOnSiteDate";
private static final String ESTIMATED_ARRIVAL_DATE = "orderDates.estimatedArrivalDate";
private static final String SHIPMENT_STATE = "purchaseorder.consignment.shipment.state";
private static final String SHIPMENT_CONTAINERS = "purchaseorder.consignment.shipment.containers";
private static final String FINAL_DESTINATION = "cont.finalDestination.code";
private static final String CONSIGNMENT = "purchaseorder.consignment";
private static final String SHIPMENT = "purchaseorder.consignment.shipment";
private static final String SHIPMENT_PLACE_OF_DISCHARGE = "purchaseorder.consignment.shipment.shippingInfo.placeOfDischarge";
private static final String SHIPMENT_PLACE_OF_LOADING = "purchaseorder.consignment.shipment.shippingInfo.placeOfLoading";
// Containers available not delivered to Final Destination
/**
* A convenience method that maps each search field from {@code ProductSearch} to the corresponding value from the search.
*
* @param search The {@code ProductSearch} whose names and values will create the map
* @return A map of search field-name to field-value
*/
@Override
protected Collection<CriteriaValue> mapFieldsToValues(ContainersAvailNotDelivToFDSearch search) {
Collection<CriteriaValue> fields = new LinkedHashSet<CriteriaValue>();
filterOrgUnit(search, fields);
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, CLEARING_AGENT, search.getClearingAgent()));
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, TRANSPORTER, search.getTransporter()));
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, SHIPMENT_REFERENCE, search.getShippingReference()));
fields.add(new CriteriaValue(CriteriaOperation.GREATER_THAN, SHIPMENT_CREATION_DATE, search.getShipmentCreatedFrom()));
fields.add(new CriteriaValue(CriteriaOperation.LESS_THAN, SHIPMENT_CREATION_DATE, search.getShipmentCreatedTo()));
fields.add(new CriteriaValue(CriteriaOperation.GREATER_THAN, PICKUP_DATE, search.getPickupDateFrom()));
fields.add(new CriteriaValue(CriteriaOperation.LESS_THAN, PICKUP_DATE, search.getPickupDateTo()));
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, SUPPLIER, search.getSupplier()));
return fields;
}
private void filterOrgUnit(ContainersAvailNotDelivToFDSearch search, Collection<CriteriaValue> fields) {
if (search.isFilteredByUserOrg()) {
fields.add(CriteriaValue.in(ORGANISATIONAL_UNIT_ID, getUserOrganisationalUnits().stream()
.map(organisationalUnit -> organisationalUnit.getId()).collect(Collectors.toList())));
} else if (search.getOrganisationalUnit() != null) {
fields.add(CriteriaValue.eq(ORGANISATIONAL_UNIT, search.getOrganisationalUnit().getName()));
} else if (search.getOrganisationalUnitList() != null && !search.getOrganisationalUnitList().isEmpty()) {
fields.add(CriteriaValue.in(ORGANISATIONAL_UNIT_ID, search.getOrganisationalUnitList().stream()
.map(organisationalUnit -> organisationalUnit.getId()).collect(Collectors.toList())));
}
}
protected Collection<CriteriaValue> containersAvailNotDelToFDCriteriaValues(ContainersAvailNotDelivToFDSearch search) {
Collection<CriteriaValue> fields = new LinkedHashSet<>();
filterOrgUnit(search, fields);
if (search.getShippingReference() != null) {
fields.add(CriteriaValue.like(SHIPMENT_REFERENCE, "%" + search.getShippingReference() + "%"));
}
if (search.getSupplier() != null) {
fields.add(CriteriaValue.eq(SUPPLIER, search.getSupplier()));
}
if (search.getShippingMode() != null) {
fields.add(CriteriaValue.eq(SHIPPING_MODE, search.getShippingMode()));
}
if (search.getClearingAgent() != null) {
fields.add(CriteriaValue.eq(CLEARING_AGENT, search.getClearingAgent()));
}
if (search.getTransporter() != null) {
fields.add(CriteriaValue.eq(TRANSPORTER, search.getTransporter()));
}
if (search.getShipmentCreatedFrom() != null) {
fields.add(CriteriaValue.gt(SHIPMENT_CREATION_DATE, search.getShipmentCreatedFrom()));
}
if (search.getShipmentCreatedTo() != null) {
fields.add(CriteriaValue.lt(SHIPMENT_CREATION_DATE, search.getShipmentCreatedTo()));
}
fields.add(CriteriaValue.gt(PICKUP_DATE, search.getPickupDateFrom()));
fields.add(CriteriaValue.lt(PICKUP_DATE, search.getPickupDateTo()));
return fields;
}
String createJoinStringForContainersAvailNotDelToFD(ContainersAvailNotDelivToFDSearch search) {
String tableName = ((SearchBase) search).getTableName();
StringBuilder sb = new StringBuilder(" left join " + tableName.toLowerCase() + ".consignment.shipment.containers as cont ");
sb.append(" left join " + tableName.toLowerCase() + ".consignment.shipment.packingList.containers as packCont ");
sb.append(" left join packCont.orders as packListOrder ");
sb.append(" where cont.containerDates.pickUpDate is not null ");
sb.append(" and " + tableName.toLowerCase() + ".consignment.shipment.state not in ('FINALISED' , 'VERIFIED', 'DELETED') ");
sb.append(" and " + tableName.toLowerCase() + ".orderDates.goodsReceivedDate is null ");
sb.append(" and " + tableName.toLowerCase() + ".consignment.shipment is not null ");
sb.append(" and " + tableName.toLowerCase() + ".consignment is not null ");
sb.append(" and cont.reference is not null ");
sb.append(" and cont.reference = packCont.reference ");
//VVVVVVVV
sb.append(" and packListOrder.orderReference = " + tableName.toLowerCase() + ".orderReference ");
sb.append(" and packListOrder.orderReference is not null ");
sb.append(" and packCont.packingList is not null ");
sb.append(" and " + tableName.toLowerCase() + ".consignment.shipment.packingList is not null ");
//AAAAAAAA
return sb.toString();
}
@Override
public List<PurchaseOrder> searchContainersAvailNotDelToFD(ContainersAvailNotDelivToFDSearch search) {
String tableName = search.getTableName().toLowerCase();
String[] orderBy = new String[]{
tableName + ".consignment.shipment.created ASC", tableName + ".consignment.shipment.reference", "packCont.reference"};
Query query = createQueryWithJoin(search, false, "", containersAvailNotDelToFDCriteriaValues(search),
createJoinStringForContainersAvailNotDelToFD(search), orderBy);
return getQueryList(query, search.getSearchMetaParams());
}
@Override
public long countContainersAvailNotDelToFD(ContainersAvailNotDelivToFDSearch search) {
Query query = createQueryWithJoin(search, true, "", containersAvailNotDelToFDCriteriaValues(search),
createJoinStringForContainersAvailNotDelToFD(search));
return getQueryCount(query);
}
// -----------------
protected Collection<CriteriaValue> containersShippedNotAvailCriteriaValues(ContainersAvailNotDelivToFDSearch search) {
Collection<CriteriaValue> fields = new LinkedHashSet<>();
filterOrgUnit(search, fields);
if (search.getShippingReference() != null) {
fields.add(CriteriaValue.like(SHIPMENT_REFERENCE, "%" + search.getShippingReference() + "%"));
}
if (search.getState() != null) {
fields.add(CriteriaValue.eq(SHIPMENT_STATE, search.getState()));
}
fields.add(CriteriaValue.eq(SUPPLIER, search.getSupplier()));
fields.add(CriteriaValue.eq(SHIPPING_MODE, search.getShippingMode()));
fields.add(CriteriaValue.eq(FREIGHT_FORWARDER, search.getFreightForwarder()));
fields.add(CriteriaValue.gt(SHIPMENT_CREATION_DATE, search.getShipmentCreatedFrom()));
fields.add(CriteriaValue.lt(SHIPMENT_CREATION_DATE, search.getShipmentCreatedTo()));
fields.add(CriteriaValue.gt(ETA_POD_DATE, search.getEstArrivalDateAtPlaceOfDischargeFrom()));
fields.add(CriteriaValue.lt(ETA_POD_DATE, search.getEstArrivalDateAtPlaceOfDischargeTo()));
return fields;
}
//DO NOT DELETE
/*private Criteria createShippedNotAvailableCriteria(ContainersAvailNotDelivToFDSearch search) {
Criteria criteria = getSession().createCriteria(PurchaseOrder.class);
criteria.createAlias("consignment.shipment.containers", "cont");
criteria.createAlias("consignment.shipment.packingList.containers", "packCont");
criteria.createAlias("packCont.orders", "packListOrder");
//criteria.createAlias("", "");
criteria.add(Restrictions.eq("supplier.organisationalUnit", search.getSupplier()));
criteria.add(Restrictions.eq("shippingInformation.shippingMode", search.getShippingMode()));
criteria.add(Restrictions.eq("shippingInformation.freightForwarder", search.getFreightForwarder()));
criteria.add(Restrictions.gt("consignment.shipment.created", search.getShipmentCreatedFrom()));
criteria.add(Restrictions.lt("consignment.shipment.created", search.getShipmentCreatedTo()));
criteria.add(Restrictions.gt("consignment.shipment.estimatedArrivalDateAtPlaceOfDischarge",
search.getEstArrivalDateAtPlaceOfDischargeFrom()));
criteria.add(Restrictions.lt("consignment.shipment.estimatedArrivalDateAtPlaceOfDischarge",
search.getEstArrivalDateAtPlaceOfDischargeTo()));
criteria.add(Restrictions.isNotNull("consignment.shipment"));
criteria.add(Restrictions.eq("cont.reference", "packCont.reference"));
criteria.add(Restrictions.eq("packListOrder.orderReference", "orderReference"));
Criterion criterion1 = Restrictions.isNull("cont.containerDates.pickUpDate");
Criterion criterion2 = Restrictions.in("consignment.shipment.state", new Object[]{
ShipmentState.FINALISED,
ShipmentState.VERIFIED
});
criteria.add(Restrictions.or(criterion1, criterion2));
criteria.add(Restrictions.isNotNull("packListOrder.orderReference"));
criteria.add(Restrictions.isNotNull("packCont.packingList"));
criteria.add(Restrictions.isNotNull("cont.reference"));
criteria.add(Restrictions.isNotNull("consignment"));
criteria.add(Restrictions.isNotNull("consignment.shipment.packingList"));
return criteria;
}*/
public String createJoinStringForContainersShippedNotAvail(ContainersAvailNotDelivToFDSearch search) {
String tableName = ((SearchBase) search).getTableName();
StringBuilder sb = new StringBuilder(" left join " + tableName.toLowerCase()
+ ".consignment.shipment.containers as cont ");
sb.append(" left join " + tableName.toLowerCase()
+ ".consignment.shipment.packingList.containers as packCont ");
sb.append(" left join packCont.orders as packListOrder ");
sb.append(" where " + tableName.toLowerCase() + ".consignment.shipment is not null ");
sb.append(" and cont.reference = packCont.reference ");
sb.append(" and packListOrder.orderReference = " + tableName.toLowerCase() + ".orderReference ");
sb.append(" and ( cont.containerDates.pickUpDate is null ");
sb.append(" or " + tableName.toLowerCase() + ".consignment.shipment.state in ('FINALISED' , 'VERIFIED') ) ");
sb.append(" and packListOrder.orderReference is not null ");
sb.append(" and packCont.packingList is not null ");
sb.append(" and cont.reference is not null ");
sb.append(" and " + tableName.toLowerCase() + ".consignment is not null ");
sb.append(" and " + tableName.toLowerCase() + ".consignment.shipment.packingList is not null ");
return sb.toString();
}
//DO NOT DELETE
/*@Override
public List<PurchaseOrder> searchContainersShippedNotAvail(ContainersAvailNotDelivToFDSearch search) {
Criteria criteria = this.createShippedNotAvailableCriteria(search);
if (search.getSearchMetaParams() != null) {
criteria.setFirstResult(search.getSearchMetaParams().getRowIndex());
criteria.setMaxResults(search.getSearchMetaParams().getRowCount());
}
criteria.addOrder(org.hibernate.criterion.Order.asc("consignment.shipment.created"));
criteria.addOrder(org.hibernate.criterion.Order.asc("shipment.reference"));
criteria.addOrder(org.hibernate.criterion.Order.asc("shipment.containers.reference"));
return criteria.list();
}*/
@Override
public List<PurchaseOrder> searchContainersShippedNotAvail(ContainersAvailNotDelivToFDSearch search) {
String tableName = search.getTableName().toLowerCase();
String[] orderBy = new String[]{
tableName + ".consignment.shipment.created DESC", tableName + ".consignment.shipment.reference", "packCont.reference"};
Query query = createQueryWithJoin(search, false, "", containersShippedNotAvailCriteriaValues(search),
createJoinStringForContainersShippedNotAvail(search), orderBy);
List<PurchaseOrder> queryList = getQueryList(query, search.getSearchMetaParams());
return queryList;
}
//DO NOT DELETE
/*@Override
public long countContainersShippedNotAvail(ContainersAvailNotDelivToFDSearch search) {
Criteria criteria = this.createShippedNotAvailableCriteria(search);
criteria.setProjection(Projections.rowCount());
return ((Long) criteria.uniqueResult()).longValue();
}*/
@Override
public long countContainersShippedNotAvail(ContainersAvailNotDelivToFDSearch search) {
//Query query = createQuery(search, true, containersShippedNotAvailCriteriaValues(search));
Query query = createQueryWithJoin(search, true, "", containersShippedNotAvailCriteriaValues(search)
, createJoinStringForContainersShippedNotAvail(search));
return getQueryCount(query);
}
protected Collection<CriteriaValue> logisticSummaryCriteriaValues(ContainersAvailNotDelivToFDSearch search, boolean orderLevel) {
Collection<CriteriaValue> fields = new LinkedHashSet<CriteriaValue>();
if (!StringUtils.isEmpty(search.getOrderReference())) {
fields.add(CriteriaValue.like(ORDER_REFERENCE, "%" + search.getOrderReference() + "%"));
} else if (search.getOrderReferences() != null && !search.getOrderReferences().isEmpty()) {
fields.add(CriteriaValue.likeAny(ORDER_REFERENCE, search.getOrderReferences()));
}
if (!StringUtils.isEmpty(search.getShippingReference())) {
fields.add(CriteriaValue.like(SHIPMENT_REFERENCE, "%" + search.getShippingReference() + "%"));
}
if (!search.isGoodsReceived()) {
// fields.add(CriteriaValue.isNotNull("goodsReceivedDate"));
// } else {
fields.add(CriteriaValue.isNull("goodsReceivedDate"));
}
if (orderLevel) {
fields.add(CriteriaValue.eq(PLACE_OF_DISCHARGE, search.getPlaceOfDischarge()));
fields.add(CriteriaValue.eq(PLACE_OF_LOADING, search.getPlaceOfLoading()));
fields.add(CriteriaValue.isNull("purchaseorder.consignment.shipment"));
} else {
fields.add(CriteriaValue.eq(SHIPMENT_PLACE_OF_DISCHARGE, search.getPlaceOfDischarge()));
fields.add(CriteriaValue.eq(SHIPMENT_PLACE_OF_LOADING, search.getPlaceOfLoading()));
//fields.add(CriteriaValue.isNotNull(SHIPMENT_PLACE_OF_DISCHARGE));
}
fields.add(CriteriaValue.eq(FREIGHT_FORWARDER, search.getFreightForwarder()));
fields.add(CriteriaValue.eq(SHIPPING_MODE, search.getShippingMode()));
if (CollectionUtils.isNotEmpty(search.getFinalDestinationList()))
fields.add(CriteriaValue.in(FINAL_DESTINATION, search.getFinalDestinationList().stream()
.map(finalDestination -> finalDestination.getCode()).collect(Collectors.toList())));
fields.add(CriteriaValue.gt(GOODS_RECEIVED_DATE, search.getGoodsReceivedDateFrom()));
fields.add(CriteriaValue.lt(GOODS_RECEIVED_DATE, search.getGoodsReceivedDateTo()));
if (search.getShippedOnBoardDateFrom() != null)
fields.add(CriteriaValue.gt(SHIPPED_ON_BOARD_DATE, search.getShippedOnBoardDateFrom()));
if (search.getShippedOnBoardDateTo() != null)
fields.add(CriteriaValue.lt(SHIPPED_ON_BOARD_DATE, search.getShippedOnBoardDateTo()));
if (search.getBillOfLadingDate() != null && search.getBillOfLadingDate().getFrom() != null) {
fields.add(CriteriaValue.paramsOnly("billOfLadingDateFrom", search.getBillOfLadingDate().getFrom()));
}
if (search.getBillOfLadingDate() != null && search.getBillOfLadingDate().getTo() != null) {
fields.add(CriteriaValue.paramsOnly("billOfLadingDateTo", search.getBillOfLadingDate().getTo()));
}
if (search.getActualDepartureDate() != null && search.getActualDepartureDate().getFrom() != null) {
fields.add(CriteriaValue.gt(ACTUAL_DEPARTURE_DATE, search.getActualDepartureDate().getFrom()));
}
if (search.getActualDepartureDate() != null && search.getActualDepartureDate().getTo() != null) {
fields.add(CriteriaValue.lt(ACTUAL_DEPARTURE_DATE, search.getActualDepartureDate().getTo()));
}
filterOrgUnit(search, fields);
return fields;
}
@Override
public List<PurchaseOrder> searchLogisticsSummary(ContainersAvailNotDelivToFDSearch search) {
List<PurchaseOrder> queryList = new ArrayList<PurchaseOrder>();
List<PurchaseOrder> queryList2 = new ArrayList<PurchaseOrder>();
String[] orderBy =
new String[]{"purchaseorder." + ORDER_REFERENCE};
Query query =
getQuery(search, false, "", logisticSummaryCriteriaValues(search, false),
createJoinStringForLogisticSummary(search), orderBy, "select distinct purchaseorder ");
Query query2 =
getQuery(search, false, "", logisticSummaryCriteriaValues(search, true),
createJoinStringForLogisticSummary(search), orderBy, "select distinct purchaseorder ");
try {
queryList = getQueryList(query, search.getSearchMetaParams());
queryList2 = getQueryList(query2, search.getSearchMetaParams());
} catch (Exception e) {
e.printStackTrace();
}
queryList.addAll(queryList2);
Set<PurchaseOrder> s = new LinkedHashSet<>(queryList);
return new ArrayList<>(s);
}
public String createJoinStringForLogisticSummary(ContainersAvailNotDelivToFDSearch search) {
String tableName = ((SearchBase) search).getTableName();
StringBuilder sb = new StringBuilder(" left join " + tableName.toLowerCase() + ".consignment.shipment.containers as cont ");
sb.append(" left join " + tableName.toLowerCase() + ".consignment.shipment as ship ");
sb.append(" where " + tableName.toLowerCase() + ".consignment is not null ");
sb.append(" and " + tableName.toLowerCase() + ".state in ( 'SHIPMENT_CREATED','AWAITING_LSP_SIGNOFF','SIGNED_OFF','BOOKED_IN',"
+ "'FREIGHT_RECEIVED','STOCK_RECEIVED','STOCK_PARTIALLY_RECEIVED')");
// sb.append(" and cont.reference is not null ");
// sb.append(" and " + tableName.toLowerCase() + ".consignment is not null ");
if (search.getBillOfLadingDate() != null && search.getBillOfLadingDate().getFrom() != null) {
// fields.add(CriteriaValue.gt(BILL_OF_LADING_DATE, search.getBillOfLadingDate().getFrom()));
sb.append(" and (ship.billOfLadingDate >=:billOfLadingDateFrom"
+ " or ship.houseAirwayBillIssueDate >=:billOfLadingDateFrom)");
}
if (search.getBillOfLadingDate() != null && search.getBillOfLadingDate().getTo() != null) {
// fields.add(CriteriaValue.lt(BILL_OF_LADING_DATE, search.getBillOfLadingDate().getTo()));
sb.append(" and (ship.billOfLadingDate" + "<=:billOfLadingDateTo"
+ " or ship.houseAirwayBillIssueDate<=:billOfLadingDateTo)");
}
return sb.toString();
}
@Override
public long countLogisticsSummary(ContainersAvailNotDelivToFDSearch search) {
Query query =
getQuery(search, true, "", logisticSummaryCriteriaValues(search, false),
createJoinStringForLogisticSummary(search), null, "select count (distinct purchaseorder) ");
Query query2 =
getQuery(search, true, "", logisticSummaryCriteriaValues(search, false),
createJoinStringForLogisticSummary(search), null, "select count (distinct purchaseorder) ");
return getQueryCount(query) + getQueryCount(query2);
}
// @Override
// public long countLogisticsSummary(ContainersAvailNotDelivToFDSearch search) {
// Criteria criteria = this.createLogisticsSummaryCriteria(search);
// criteria.setProjection(Projections.rowCount());
// return ((Long) criteria.uniqueResult());
// }
protected Collection<CriteriaValue> financeSummaryEstimateCriteriaValues(ContainersAvailNotDelivToFDSearch search) {
Collection<CriteriaValue> fields = new LinkedHashSet<>();
if (search.getOrderReference() != null) {
fields.add(CriteriaValue.like(ORDER_REFERENCE, "%" + search.getOrderReference() + "%"));
}
filterOrgUnit(search, fields);
fields.add(CriteriaValue.eq(PLACE_OF_DISCHARGE, search.getPlaceOfDischarge()));
fields.add(CriteriaValue.eq(BUYER, search.getBuyer()));
fields.add(CriteriaValue.eq(DIVISION, search.getDivision()));
fields.add(CriteriaValue.gt(REQUIRED_ON_SITE_DATE, search.getRequiredOnSiteDateFrom()));
fields.add(CriteriaValue.lt(REQUIRED_ON_SITE_DATE, search.getRequiredOnSiteDateTo()));
fields.add(CriteriaValue.eq(SUPPLIER, search.getSupplier()));
fields.add(CriteriaValue.in("state", new OrderState[]{OrderState.SIGNED_OFF,
OrderState.AWAITING_LSP_SIGNOFF, OrderState.AWAITING_LSP_UNSIGNOFF,
OrderState.LSP_REJECTED_UNSIGNOFF, OrderState.LSP_REJECTED, OrderState.FREIGHT_RECEIVED,
OrderState.BOOKED_IN}));
return fields;
}
@Override
public List<PurchaseOrder> searchFinanceSummaryEstimate(ContainersAvailNotDelivToFDSearch search) {
Query query = createSortedQuery(search, "", financeSummaryEstimateCriteriaValues(search), REQUIRED_ON_SITE_DATE, true);
return getQueryList(query, search.getSearchMetaParams());
}
@Override
public long countFinanceSummaryEstimate(ContainersAvailNotDelivToFDSearch search) {
Query query = createQuery(search, true, financeSummaryEstimateCriteriaValues(search));
return getQueryCount(query);
}
@Override
public List<Object[]> invoiceQuantity(List<Long> poIds) {
StringBuilder sb = new StringBuilder(
"select aco.reference, sum(i.invoiceQuantity) "
+ "from PurchaseOrder as po "
+ "join po.consignment.shipment as ship "
+ "join ship.commercialInvoices as ci "
+ "join ci.actualConsignments as ac "
+ "join ac.actualOrders as aco "
+ "join aco.actualLineItems as i "
+ "where ci.state <> 'DELETED' "
+ "and aco.reference = po.orderReference "
+ "and po.id in (:poList) "
+ "group by aco.reference"
);
Query query = getSessionCustom().createQuery(sb.toString());
query.setParameterList("poList", poIds);
return query.list();
}
@Override
public List<InvoiceOrderValue> invoiceOrderValues(List<Long> orderIds) {
StringBuilder builder = new StringBuilder("Select ");
builder.append("max(aco.originalid) as originalId,sum(pso.amount) as ciOrderAmount,sum(pso.forwardrate*pso.amount) as ciCostingAmount," +
" sum(ccno.totalvalue) as creditNoteOrderAmount,sum(ccno.totalvalue*pso.forwardrate) as creditNoteOrderCostingAmount, " +
"max(cinv.currency_code) as currency ");
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 actualConsignment ac on (ac.costsinvoice_id=cinv.id)");
builder.append(" join actualOrder aco on (aco.actualConsignment_id=ac.id)");
builder.append(" join plannedsettlementorder pso on (pso.plannedsettlement_id=ps.id and pso.reference=aco.reference)");
builder.append(" join shipment ship on (cinv.shipment_id=ship.id and ship.state<>'DELETED') ");
builder.append(" left join (select aco.originalid,aco.totalvalue from commercialcreditnote ccn " +
"join costsinvoice cinv on (ccn.id=cinv.id and cinv.state<>'DELETED') " +
" join actualConsignment ac on (ac.costsinvoice_id=cinv.id) join actualOrder aco on (aco.actualConsignment_id=ac.id)) " +
"as ccno on (ccno.originalid=aco.originalid) ");
builder.append(" where cinv.state in ('SIGNED_OFF','SETTLED') and ship.state in ('FINALISED' , 'VERIFIED') " +
"and aco.originalid in (:poList) group by aco.originalid;");
Query query = getSessionCustom().createNativeQuery(builder.toString())
.addScalar("originalId", StandardBasicTypes.LONG)
.addScalar("ciOrderAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("ciCostingAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("creditNoteOrderAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("creditNoteOrderCostingAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("currency", StandardBasicTypes.STRING)
.setResultTransformer(Transformers.aliasToBean(InvoiceOrderValue.class));
query.setParameterList("poList", orderIds);
return query.list();
}
}