NotShippedReportHelper.java
package com.tradecloud.repository.order;
import com.tradecloud.authentication.MultiTenantUtil;
import com.tradecloud.common.base.PersistenceBase;
import com.tradecloud.domain.base.utils.DateUtils;
import com.tradecloud.domain.base.utils.MathUtils;
import com.tradecloud.domain.container.ContainerType;
import com.tradecloud.domain.container.ContainerTypeCode;
import com.tradecloud.domain.costing.clean.CostLineNames;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.dto.order.PfOrderNotShippedSearchResult;
import com.tradecloud.dto.order.PurchaseOrderSearch;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.joda.time.DateTime;
import org.joda.time.Days;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;
/**
* @author jon
*/
public class NotShippedReportHelper {
private static Logger log = Logger.getLogger(NotShippedReportHelper.class);
private static final String STATE = "state";
/**
* As per TTG-1508.
* Method returns the container usage (number at POD) for an order and a specific container type.
*
* @param orderId
* @param containerTypeCode
* @return
*/
private static BigDecimal getContainerUsageForOrder(Session session, Long orderId, String containerTypeCode) {
String hql = "select cu.numberAtPOD from ContainerUsage cu where cu.order.id = :id " +
"and cu.container.containerType.code = :containerTypeCode";
Query query = session.createQuery(hql);
query.setParameter("id", orderId);
query.setParameter("containerTypeCode", containerTypeCode);
BigDecimal totalTeu = BigDecimal.ZERO;
List<BigDecimal> list = query.list();
for (BigDecimal teu : list) {
totalTeu = totalTeu.add(teu);
}
return totalTeu;
}
public static Query createQueryNotShippedTransformed(final Session session, PurchaseOrderSearch search, boolean rowCount) {
Query query = ordersNotShippedSQl(session, search, rowCount);
if (rowCount) {
return query;
}
Date today = DateUtils.getStartOfDay(new Date(System.currentTimeMillis()));
final DateTime todayDateTime = new DateTime(today.getTime()); // ie 2015/02/06 00:00:00
query.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] objects, String[] aliases) {
PfOrderNotShippedSearchResult result = new PfOrderNotShippedSearchResult();
result.setBuyerName(objects[0] + " " + objects[1]);
result.setCarrierName(objects[2] != null ? objects[2] + "" : "");
result.setConsignmentReference(objects[3].toString());
result.setFinalDestination("" + objects[4]);
result.setFreightForwarderName("" + objects[5]);
result.setIncoTerm("" + objects[6]);
result.setLatestShipmentDate((Date) objects[7]);
result.setLcBankName(objects[8] != null ? objects[8] + "" : "");
DateTime lsd = new DateTime(DateUtils.getStartOfDay(result.getLatestShipmentDate()));
int daysToLatestShipmentDate = Days.daysBetween(todayDateTime, lsd).getDays();
result.setNoDaysToLatestShipmentDate(new BigDecimal(daysToLatestShipmentDate));
result.setOrderReference(objects[9].toString());
result.setOrganisationalUnitName("" + objects[10]);
result.setOriginalLatestShipmentDate((Date) objects[11]);
result.setPaymentMethod("Telegraphic Transfer".equals("" + objects[12]) ? "TT" : "" + objects[12]);
result.setPaymentTerm("" + objects[13]);
result.setPlaceOfDischarge("" + objects[14]);
result.setPlaceOfLoading("" + objects[15]);
result.setCountry("" + objects[39]);
result.setProFormaReference(objects[16] != null ? objects[16] + "" : "");
result.setRequiredOnSiteDate((Date) objects[17]);
result.setShippingMode("" + objects[18]);
result.setShippingReference(objects[19] == null ? "" : objects[19] + "");
result.setStatus("" + objects[20]);
result.setBusinessState("" + objects[47]);
result.setSupplierInvoiceCurrency("" + objects[21]);
result.setSupplierName("" + objects[22]);
result.setVesselName(objects[23] != null ? objects[23] + "" : "");
result.setLcBankReference(objects[24] != null ? objects[24] + "" : "");
result.setLcNumber(objects[25] != null ? objects[25] + "" : "");
if (objects[26] != null) {
result.setLcEstablishedDate((Date) objects[26]);
}
if (objects[27] != null) {
result.setOrderId(Long.valueOf(objects[27] + ""));
}
if (objects[28] != null) {
result.setPlannedShipmentDate((Date) objects[28]);
}
if (objects[29] != null) {
result.setEstimatedArrivalDate((Date) objects[29]);
}
if (objects[32] != null) {
result.setBookingDate((Date) objects[32]);
}
if (objects[33] != null) {
result.setFreightReceivedDate((Date) objects[33]);
}
if (objects[34] != null) {
String styleReference = "";
String styleDescription = "";
//LineItem lineItem = g
result.setStyleReference("" + objects[34]);
result.setStyleDescription("" + objects[34]);
styleReference =
styleReference.length() > 0 ? styleReference.substring(0, styleReference.lastIndexOf(",")).trim() : styleReference;
styleDescription =
styleDescription.length() > 0 ? styleDescription.substring(0, styleDescription.lastIndexOf(",")).trim()
: styleDescription;
result.setStyleReference(styleReference);
result.setStyleDescription(styleDescription);
}
result.setLspBookingReference(objects[35] == null ? "" : objects[35] + "");
result.setSupplierInvoiceValue(objects[36] == null ? BigDecimal.ZERO : new BigDecimal(objects[36] + ""));
BigDecimal spotDecimal = new BigDecimal(objects[37] == null ? "0" : objects[37] + "");
spotDecimal = MathUtils.roundTo4DecimalPlaces(spotDecimal);
BigDecimal forwardDecimal = new BigDecimal(objects[38] == null ? "0" : objects[38] + "");
forwardDecimal = MathUtils.roundTo4DecimalPlaces(forwardDecimal);
result.setSpotRate(spotDecimal.toString());
result.setForwardRate(forwardDecimal.toString());
if (objects[40] != null) {
result.setPreviouslyBooked("" + objects[40]);
}
if (objects[41] != null) {
result.setAvailabilityDate((Date) objects[41]);
}
if (objects[42] != null) {
result.setSupplierBookingRequestDate((Date) objects[42]);
}
if (objects[43] != null) {
result.setCargoReadyDate((Date) objects[43]);
}
if (objects[44] != null) {
result.setOriginalCargoReadyDate((Date) objects[44]);
}
if (objects[45] != null) {
result.setPlannedCargoCarrier("" + objects[45]);
}
if (objects[46] != null) {
result.setPlannedPortOfDischarge("" + objects[46]);
}
if (objects[48] != null) {
result.setMultiModalShippingMode(ObjectUtils.firstNonNull("" + objects[48], ""));
}
result.setOrganisationalUnitId(Long.valueOf(objects[49]+""));
//set the containers data
List<Object> containerDataList = setAndGetContainerDataList(objects, session);
BigDecimal[] containerCounts = {BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO,
BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO};
BigDecimal teuCount = BigDecimal.ZERO;
for (Object object : containerDataList) {
List<Object> data = (List<Object>) object;
ContainerType containerType = (ContainerType) data.get(0);
BigDecimal teu = (BigDecimal) data.get(2);
//obtain the usage for each container type
//be carefull to multiply this with the teu amount
//TODO: to revisit seeing that this report is very massmart specific because of the containers used
BigDecimal quantity = getContainerUsageForOrder(session, Long.valueOf("" + objects[27]), containerType.getCode());
if (containerType.getCode().equals(ContainerTypeCode.FCL_20_G_0.value())) {
containerCounts[0] = containerCounts[0].add(quantity);
teuCount = teuCount.add(quantity.multiply(teu));
} else if (containerType.getCode().equals(ContainerTypeCode.FCL_20_R_0.value())) {
containerCounts[1] = containerCounts[1].add(quantity);
teuCount = teuCount.add(quantity.multiply(teu));
} else if (containerType.getCode().equals(ContainerTypeCode.FCL_42_G_0.value())) {
containerCounts[2] = containerCounts[2].add(quantity);
teuCount = teuCount.add(quantity.multiply(teu));
} else if (containerType.getCode().equals(ContainerTypeCode.FCL_42_R_0.value())) {
containerCounts[3] = containerCounts[3].add(quantity);
teuCount = teuCount.add(quantity.multiply(teu));
} else if (containerType.getCode().equals(ContainerTypeCode.FCL_45_G_0.value())) {
containerCounts[4] = containerCounts[4].add(quantity);
teuCount = teuCount.add(quantity.multiply(teu));
} else if (containerType.getCode().equals(ContainerTypeCode.FCL_45_R_1.value())) {
containerCounts[5] = containerCounts[5].add(quantity);
teuCount = teuCount.add(quantity.multiply(teu));
} else if (containerType.getCode().equals(ContainerTypeCode.LCL.value())) {
containerCounts[6] = containerCounts[6].add(quantity).setScale(4, RoundingMode.HALF_UP);
//http://stackoverflow.com/questions/4591206/arithmeticexception-non-terminating-decimal-expansion-no-exact-representable
teuCount = teuCount.add(quantity.divide(teu, 4, RoundingMode.HALF_UP));
}
}
//cnt.quantity,
String orderContainerHql = "select cu.numberAtPOD from Consignment c left join c.containers as cnt " +
"left join cnt.containerUsages cu left join cu.order o where c.id = :consignmentId " +
"and o.id = :orderId and cnt.containerType.code = :containerType";
Query orderContainerQuery = session.createQuery(orderContainerHql);
orderContainerQuery.setParameter("consignmentId", Long.valueOf("" + objects[30]));
orderContainerQuery.setParameter("orderId", Long.valueOf("" + objects[27]));
orderContainerQuery.setParameter("containerType", ContainerTypeCode.LCL.name());
orderContainerQuery.setResultTransformer(new FirstTupleResultTransformer());
result.setTEUs(teuCount.setScale(4, RoundingMode.HALF_UP));
result.setContainerFCL20GPquantity(containerCounts[0].setScale(4, RoundingMode.HALF_UP));
result.setContainerFCL20REEFERGPquantity(containerCounts[1].setScale(4, RoundingMode.HALF_UP));
result.setContainerFCL40GPquantity(containerCounts[2].setScale(4, RoundingMode.HALF_UP));
result.setContainerFCL40REEFERGPquantity(containerCounts[3].setScale(4, RoundingMode.HALF_UP));
result.setContainerFCL40HIGHCUBEquantity(containerCounts[4].setScale(4, RoundingMode.HALF_UP));
result.setContainerFCL40REEFERHIGHCUBEquantity(containerCounts[5].setScale(4, RoundingMode.HALF_UP));
result.setContainerLCLSEAquantity(containerCounts[6].setScale(4, RoundingMode.HALF_UP));
//set the line items data
String hqlLineItems = "select li.unitQuantity, li.unitVolume, li.unitWeight, li.styleReference, li.styleDescription," +
"li.sellPriceExclusiveAmount from Order o left join o.lineItems li where o.id = :id";
Query lineItemsQuery = session.createQuery(hqlLineItems);
lineItemsQuery.setParameter("id", Long.valueOf("" + objects[27]));
lineItemsQuery.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
List<Object> data = new ArrayList<>();
data.add(tuple[0]);
data.add(tuple[1]);
data.add(tuple[2]);
data.add(tuple[3]);
data.add(tuple[4]);
data.add(tuple[5]);
return data;
}
@Override
public List transformList(List collection) {
return collection;
}
});
List<Object> list = lineItemsQuery.list();
BigDecimal totalUnitWeight = BigDecimal.ZERO;
BigDecimal totalUnitVolume = BigDecimal.ZERO;
StringBuilder styleReference = new StringBuilder();
StringBuilder styleDescription = new StringBuilder();
Set<String> referenceSet = new HashSet<>();
Set<String> descriptionSet = new HashSet<>();
BigDecimal totalQuantity = BigDecimal.ZERO;
BigDecimal retailValueOfOrders = BigDecimal.ZERO;
for (Object obj : list) {
List<Object> data = (List<Object>) obj;
BigDecimal unitQuantity = (BigDecimal) data.get(0);
BigDecimal unitVolume = data.get(1) != null ? (BigDecimal) data.get(1) : BigDecimal.ZERO;
BigDecimal unitWeight = data.get(2) != null ? (BigDecimal) data.get(2) : BigDecimal.ZERO;
BigDecimal sellpriceexclusiveamount = data.get(5) != null ? (BigDecimal) data.get(5) : BigDecimal.ZERO;
totalUnitVolume = totalUnitVolume.add(MathUtils.multiply(unitQuantity, unitVolume));
totalUnitWeight = totalUnitWeight.add(MathUtils.multiply(unitQuantity, unitWeight));
totalQuantity = totalQuantity.add(unitQuantity);
retailValueOfOrders = retailValueOfOrders.add(sellpriceexclusiveamount.multiply(unitQuantity));
String referStr = (String) data.get(3);
String descStr = (String) data.get(4);
if (referStr != null && !referenceSet.contains(referStr.trim())) {
styleReference.append(referStr == null ? "" : referStr.trim() + " ,");
referenceSet.add(referStr.trim());
}
if (descStr != null && !descriptionSet.contains(descStr.trim())) {
styleDescription.append(descStr.trim() + " ,");
descriptionSet.add(descStr.trim());
}
}
styleReference = new StringBuilder(styleReference.length() > 0 ? styleReference
.substring(0, styleReference.lastIndexOf(",")).trim() : styleReference.toString());
styleDescription = new StringBuilder(styleDescription.length() > 0 ? styleDescription
.substring(0, styleDescription.lastIndexOf(",")).trim() : styleDescription.toString());
result.setStyleReference(styleReference.toString());
result.setStyleDescription(styleDescription.toString());
result.setTotalVolume(totalUnitVolume.setScale(2, BigDecimal.ROUND_HALF_UP));
result.setTotalWeight(totalUnitWeight.setScale(2, BigDecimal.ROUND_HALF_UP));
result.setTotalQuantity(totalQuantity.setScale(2, BigDecimal.ROUND_HALF_UP));
result.setRetailValueOfOrders(retailValueOfOrders.setScale(2, BigDecimal.ROUND_HALF_UP));
//set up the consignment comments
String hqlConsignmentComments = "select cc.reason from Consignment c left join c.comments cc where c.id = :id";
Query consignmentCommentsQuery = session.createQuery(hqlConsignmentComments);
consignmentCommentsQuery.setParameter("id", Long.valueOf("" + objects[30]));
consignmentCommentsQuery.setResultTransformer(new FirstTupleResultTransformer());
List<String> consignmentCommentList = consignmentCommentsQuery.list();
result.setConsignmentComments(StringUtils.join(consignmentCommentList, ","));
//set up the order comments
String hqlOrderComments = "select oc.reason from Order o left join o.comments oc where o.id = :id";
Query orderCommentsQuery = session.createQuery(hqlOrderComments);
orderCommentsQuery.setParameter("id", Long.valueOf("" + objects[27]));
orderCommentsQuery.setResultTransformer(new FirstTupleResultTransformer());
List<String> orderCommentList = orderCommentsQuery.list();
result.setOrderComments(StringUtils.join(orderCommentList, ","));
result.setConsignmentId(Long.valueOf("" + objects[30]));
result.setPurchaseOrderNumber("" + objects[31]);
setCostingAmounts(objects, result, session);
return result;
}
@Override
public List transformList(List collection) {
return collection;
}
});
return query;
}
private static List<Object> setAndGetContainerDataList(Object[] objects, Session session) {
String containersHql = "select cnt.containerType, cnt.quantity, cnt.containerType.teu " +
"from Consignment c left join c.containers as cnt where c.id = :id";
Query containersQuery = session.createQuery(containersHql);
containersQuery.setParameter("id", Long.valueOf("" + objects[34]));
containersQuery.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
List<Object> data = new ArrayList<Object>();
data.add(tuple[0]);
data.add(tuple[1]);
data.add(tuple[2]);
return data;
}
@Override
public List transformList(List collection) {
return collection;
}
});
List<Object> containerDataList = containersQuery.list();
return containerDataList;
}
private static void setCostingAmounts(Object[] objects, PfOrderNotShippedSearchResult result, Session session) {
String costingQuery = String.format("select c.transactionAmount, c.costLine.costLineTemplate.code " +
"from CostedOrder o join o.costLineCosting.costLineCostingCells c where " +
"o.number = :orderNumber and c.costLine.costLineTemplate.code in (%s)",
Stream.of(CostLineNames.CUSTOMS_CUSTOMS_DUTY, CostLineNames.CUSTOMS_VAT)
.map(s -> "'" + s + "'").collect(Collectors.joining(",")));
Query sessionQuery = session.createQuery(costingQuery);
sessionQuery.setParameter("orderNumber", "" + objects[31]);
List<Object[]> costlines = sessionQuery.list();
if (costlines != null && !costlines.isEmpty()) {
for (Object[] costline : costlines) {
if (costline[1].equals(CostLineNames.CUSTOMS_CUSTOMS_DUTY)) {
result.setCustomsDuty(((BigDecimal) costline[0]).setScale(2, BigDecimal.ROUND_HALF_UP));
} else if (costline[1].equals(CostLineNames.CUSTOMS_VAT)) {
result.setCustomsVat(((BigDecimal) costline[0]).setScale(2, BigDecimal.ROUND_HALF_UP));
}
}
}
}
private static Query ordersNotShippedSQl(final Session session, PurchaseOrderSearch search, boolean rowCount) {
String query = " select\n";
if (rowCount) {
query = query + " count(this_.id) ";
} else {
query = query + " b10_.firstName as y0_,\n" +
" b10_.lastName as y1_,\n" +
" car3_.name as y2_,\n" +
" c1_.reference as y3_,\n" +
" fd6_.name as y4_,\n" +
" ff5_.name as y5_,\n" +
" it7_.code as y6_,\n" +
" this_1_.latestShipmentDate as y7_,\n" +
" bk9_.name as y8_,\n" +
" this_1_.orderReference as y9_,\n" +
" ou11_.name as y10_,\n" +
" this_1_.originalLatestShipmentDate as y11_,\n" +
" pm12_.name as y12_,\n" +
" pt13_.name as y13_,\n" +
" pod14_.name as y14_,\n" +
" pol15_.name as y15_,\n" +
" this_1_.proFormaReference as y16_,\n" +
" this_1_.requiredOnSiteDate as y17_,\n" +
" si4_.shippingMode as y18_,\n" +
" si4_.shippingReference as y19_,\n" +
" this_1_.state as y20_,\n" +
" this_1_.currency_code as y21_,\n" +
" osup17_.name as y22_,\n" +
" this_1_.vesselName as y23_,\n" +
" lc8_.bankReference as y24_,\n" +
" lc8_.number as y25_,\n" +
" lc8_.establishmentDate as y26_,\n" +
" this_.id as y27_,\n" +
" this_1_.plannedShipmentDate as y28_,\n" +
" this_1_.estimatedArrivalDate as y29_,\n" +
" c1_.id as y30_,\n" +
" this_1_.number as y31_,\n" +
" this_1_.bookingDate as y32_,\n" +
" this_1_.freightReceivedDate as y33_,\n" +
" this_.id as y34_,\n" +
" this_1_.lspBookingReference as y35_, \n" +
" this_1_.totalinvoicevalue as y36_," +
" (select sum(ps.spotrate*amount)/GREATEST(sum(amount), 1) from plannedsettlement ps " +
" where ps.order_id=this_.id ) as y37_, \n" +
" (select sum(ps.forwardrate*amount)/GREATEST(sum(amount), 1) from plannedsettlement ps " +
" where ps.order_id=this_1_.id ) as y38_," +
" cou39_.name as y39_,\n" +
" this_1_.previouslyBooked as y40_,\n" +
" this_1_.availabilityDate as y41_, \n" +
" this_1_.supplierBookingRequestDate as y42_, \n" +
" this_1_.cargoreadydate as y43_, \n" +
" this_1_.originalcargoreadydate as y44_, \n" +
" pcc_.name as y45_, \n" +
" ppod_.name as y46_, \n" +
" this_1_.businessState as y47_, \n" +
" si4_.multiModalShippingMode as y48_, \n"+"" +
" ou11_.id as orgunitid";
}
query = query + " from\n" +
" purchaseorder this_ \n" +
" inner join\n" +
" orders this_1_ \n" +
" on this_.id=this_1_.id \n" +
" left outer join\n" +
" letterofcredit_purchaseorder this_2_ \n" +
" on this_.id=this_2_.purchaseorders_id \n" +
" left outer join\n" +
" consignment c1_ \n" +
" on this_1_.consignment_id=c1_.id \n" +
" left outer join\n" +
" consignmentshippinginfo csi2_ \n" +
" on c1_.shippingInfo_id=csi2_.id \n" +
" left outer join\n" +
" serviceprovider car3_ \n" +
" on csi2_.carrier_id=car3_.id \n" +
" left outer join\n" +
" shipmentclientconfig_serviceprovider car3_1_ \n" +
" on car3_.id=car3_1_.serviceprovider_id \n" +
" left outer join\n" +
" serviceprovider_shipmentintegrationproperties car3_2_ \n" +
" on car3_.id=car3_2_.serviceprovider_id \n" +
" left outer join\n" +
" serviceprovider_serviceProviderOrderIntegrationProperties car3_3_ \n" +
" on car3_.id=car3_3_.serviceprovider_id \n" +
" left outer join\n" +
" organisationalunit ou11_ \n" +
" on this_1_.organisationalUnit_id=ou11_.id \n" +
" left outer join\n" +
" paymentmethod pm12_ \n" +
" on this_1_.paymentMethod_code=pm12_.code \n" +
" left outer join\n" +
" paymentterm pt13_ \n" +
" on this_1_.paymentTerm_code=pt13_.code \n" +
" left outer join\n" +
" shippinginformation si4_ \n" +
" on this_1_.shippingInformation_id=si4_.id \n" +
" left outer join\n" +
" FinalDestination fd6_ \n" +
" on si4_.finalDestination_code=fd6_.code \n" +
" left outer join\n" +
" serviceprovider ff5_ \n" +
" on si4_.freightForwarder_id=ff5_.id \n" +
" left outer join\n" +
" serviceprovider pcc_ \n" +
" on this_1_.plannedcargocarrier_id=pcc_.id \n" +
" left outer join\n" +
" placeofdischarge ppod_ \n" +
" on this_1_.plannedplaceofdischarge_code=ppod_.code \n" +
" left outer join\n" +
" shipmentclientconfig_serviceprovider ff5_1_ \n" +
" on ff5_.id=ff5_1_.serviceprovider_id \n" +
" left outer join\n" +
" serviceprovider_shipmentintegrationproperties ff5_2_ \n" +
" on ff5_.id=ff5_2_.serviceprovider_id \n" +
" left outer join\n" +
" serviceprovider_serviceProviderOrderIntegrationProperties ff5_3_ \n" +
" on ff5_.id=ff5_3_.serviceprovider_id \n" +
" left outer join\n" +
" incoterm it7_ \n" +
" on si4_.incoterm_code=it7_.code \n" +
" left outer join\n" +
" placeofdischarge pod14_ \n" +
" on si4_.placeOfDischarge_code=pod14_.code \n" +
" left outer join\n" +
" placeofloading pol15_ \n" +
" on si4_.placeOfLoading_code=pol15_.code \n" +
" left outer join\n" +
" country cou39_ \n" +
" on pol15_.country_code = cou39_.code" +
" left outer join\n" +
" employee b10_ \n" +
" on this_.buyer_id=b10_.id \n" +
" left outer join\n" +
" organisationalunitsupplier sup16_ \n" +
" on this_.supplier_id=sup16_.id \n" +
" left outer join\n" +
" supplier osup17_ \n" +
" on sup16_.supplier_id=osup17_.id \n" +
" left outer join\n" +
" letterofcredit lc8_ \n" +
" on this_2_.letterofcredit_id=lc8_.id \n" +
" left outer join\n" +
" bank bk9_ \n" +
" on lc8_.issuingBank_id=bk9_.id \n";
String whereClause = "";
if (search.getState() == null) {
whereClause = "where this_1_.state in ('BOOKED_IN','FREIGHT_RECEIVED','AWAITING_LSP_SIGNOFF','AWAITING_LSP_UNSIGNOFF','LSP_REJECTED', " +
"'LSP_REJECTED_UNSIGNOFF', 'SIGNED_OFF','SUPPLIER_BOOKING_REQUESTED','CARGO_READY') and c1_.id is not null " +
"and c1_.shipment_id is null";
} else {
whereClause = "where this_1_.state in (:states) and c1_.id is not null and c1_.shipment_id is null";
}
query += whereClause;
query += " and this_1_.elc='f' ";
Collection orgIds = Collections.EMPTY_LIST;
if (search.getOrganisationalUnits() != null && !search.getOrganisationalUnits().isEmpty()) {
orgIds = search.getOrganisationalUnits().stream().map(o -> o.getId()).collect(Collectors.toList());
} else if (search.isFilteredByUserOrg()) {
Set<OrganisationalUnit> organisationalUnits = MultiTenantUtil.getActiveUser().getOrganisationalUnits();
if (organisationalUnits.isEmpty()) {
throw new IllegalArgumentException("User org units not set");
}
orgIds = organisationalUnits.stream()
.map(PersistenceBase::getId).collect(Collectors.toList());
} else if (search.getOrganisationalUnit() != null) {
orgIds = Collections.singletonList(search.getOrganisationalUnit().getId());
}
if (!orgIds.isEmpty()) {
//14
query = query + " and ou11_.id in (:orgIds) \n";
}
if (search.getSupplier() != null) {
//15
query = query + " and sup16_.id = :suppId \n";
}
if (search.getBuyer() != null) {
//16
query = query + " and b10_.id = :buyerId \n";
}
if (search.getNumberOfDaysToShip() != null) {
query = query + " and this_1_.latestShipmentDate <=:latestShipmentDateFrom \n";
}
if (search.getShippingMode() != null) {
//17
query = query + " and si4_.shippingMode = :shippingMode \n";
}
if (search.getFreightForwarder() != null) {
//18
query = query + " and ff5_.id = :freightForwarder \n";
}
if (search.getRequiredOnSiteDateRange().getFrom() != null) {
query = query + " and this_1_.requiredOnSiteDate >= :requiredOnSiteDateFrom \n";
}
if (search.getRequiredOnSiteDateRange().getTo() != null) {
query = query + " and this_1_.requiredOnSiteDate <= :requiredOnSiteDateTo \n";
}
if (search.getCountry() != null) {
//18
query = query + " and cou39_.code = :countryoforgin \n";
}
if (!rowCount) {
query = query + " order by\n" +
" this_1_.latestShipmentDate asc ";
}
Query query1 = session.createSQLQuery(query);
if (search.getState() != null) {
query1.setParameter("states", search.getState().name());
}
if (!orgIds.isEmpty()) {
query1.setParameterList("orgIds", orgIds);
}
if (search.getSupplier() != null) {
query1.setParameter("suppId", search.getSupplier().getId());
}
if (search.getBuyer() != null) {
query1.setParameter("buyerId", search.getBuyer().getId());
}
if (search.getShippingMode() != null) {
query1.setParameter("shippingMode", search.getShippingMode().name());
}
if (search.getFreightForwarder() != null) {
query1.setParameter("freightForwarder", search.getFreightForwarder().getId());
}
if (search.getNumberOfDaysToShip() != null) {
int noOfDays = Integer.parseInt(search.getNumberOfDaysToShip());
Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, noOfDays);
query1.setParameter("latestShipmentDateFrom", cal.getTime());
}
if (search.getRequiredOnSiteDateRange().getFrom() != null) {
DateUtils.setToStartOfDay(search.getRequiredOnSiteDateRange().getFrom());
query1.setParameter("requiredOnSiteDateFrom", search.getRequiredOnSiteDateRange().getFrom());
}
if (search.getRequiredOnSiteDateRange().getTo() != null) {
DateUtils.setToEndOfDay(search.getRequiredOnSiteDateRange().getTo());
query1.setParameter("requiredOnSiteDateTo", search.getRequiredOnSiteDateRange().getTo());
}
if (search.getCountry() != null) {
query1.setParameter("countryoforgin", search.getCountry().getCode());
}
return query1;
}
private static class FirstTupleResultTransformer implements ResultTransformer {
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
return tuple[0];
}
@Override
public List transformList(List collection) {
return collection;
}
}
}