ShipmentDailyReportHelper.java
package com.tradecloud.repository.shipment;
import com.tradecloud.dto.shipment.ShippingRegisterSearchResult;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
/**
* @author jonwork
*/
public class ShipmentDailyReportHelper {
public static List<ShippingRegisterSearchResult> getDailyReportDTO(Session session, Date from, String dateFormat, String orgUnitFilter) {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("" + dateFormat + "");
String query = "select distinct(shipment.reference) as shipmentReference, \n"
+ "shipment.state as shipmentStatus, \n"
+ "(select string_agg(reason,', ') from shipment_freetextcomments where shipment_id = shipment.id) as comments,\n"
+ "orders.orderreference as orderReference, \n"
+ "to_char(orders.created, '" + dateFormat + "') as orderCreationDate, \n"
+ "to_char((select max(created) from ordersevent where id in (select events_id from orders_ordersevent where "
+ "orders_id = orders.id)"
+ " and eventtype = 'SIGNED_OFF'), '" + dateFormat + "') as orderSignOffDate, \n"
+ "concat(employee.firstname, ' ', employee.lastname) as buyer, \n"
+ "organisationalunit.name as division, supplier.name as supplier, \n"
+ "country.name as countryFrom, \n"
+ "incoterm.name as incoterm, \n"
+ "paymentterm.name as paymentTerm, \n"
+ "paymentmethod.name as paymentMethod, \n"
+ "paymentbasis.name as paymentBasis1, \n"
+ estimateSettlementDate(dateFormat) + " as estimatedSettlementDate, \n"
+ "(select to_char(actualsettlementdate, '" + dateFormat + "') from costsinvoice where \n"
+ "costsinvoice.shipment_id = consignment.shipment_id and state <> 'DELETED' limit 1) as settlementDate, \n"
+ "to_char(orders.availabilityDate, '" + dateFormat + "') as availabilityDate, \n"
+ "to_char(orders.originallatestshipmentdate, '" + dateFormat + "') as orderOriginalLatestShipmentDate, \n"
+ "to_char(orders.latestshipmentdate, '" + dateFormat + "') as latestShipmentDate, \n"
+ "to_char(orders.requiredonsitedate, '" + dateFormat + "') as orderRequiredOnSiteDate, \n"
+ "to_char(shipment.exfactorydate, '" + dateFormat + "') as exFactoryDate, \n"
+ "shipmentshippinginfo.shippingmode, \n"
+ getPlaceOfLoading()
+ getPlaceOfDischarge()
+ "finaldestination.name as finalDestination, to_char(orders.bookingdate, '" + dateFormat + "') as bookingDate, \n"
+ "orders.lspbookingreference as lSPBookingRef, \n"
+ "to_char(orders.plannedshipmentdate, '" + dateFormat + "') as plannedshipmentdate, \n"
+ "to_char(orders.estimatedarrivaldate, '" + dateFormat + "') as estimateArrivalDate, \n"
+ "(select string_agg(reason,', ') from orders_freetextcomments where order_id = orders.id) AS orderComments, \n"
+ "to_char(shipment.scheduleddeparturedate, '" + dateFormat + "') as shipmentScheduledDateOfDeparture, \n"
+ "to_char(shipment.actualdeparturedate, '" + dateFormat + "') as shipmentActualDateOfDeparture, \n"
+ "to_char(shipment.estimatedarrivaldateatplaceofdischarge, '" + dateFormat + "') as shipmentEstimateArrivalDateAtPOD, \n"
+ "to_char(shipment.arrivaldateatplaceofdischarge, '" + dateFormat + "') as shipmentActualArrivalDateAtPOD, \n"
+ "to_char(shipment.copydocumentsreceiveddate, '" + dateFormat + "') as copyDocsReceived, \n"
+ "to_char(shipment.originaldocumentsreceiveddate, '" + dateFormat + "') as originalDocsReceived, \n"
+ "serviceprovider.name as carrier, \n"
+ "seashipment.shippingvessel as vesselNameAndVoyage, seashipment.voyagenumber as voyageNumber, \n"
+ "seashipment.billofladingreference as billOfLadingNo, airshipment.masterairwaybillnumber as airWayBillNo, "
+ "airshipment.flightnumber as flightNumber, airshipment.airlinename as airline,\n"
+ "airshipment.houseairwaybillnumber as houseWayBillNo, \n"
+ "(select string_agg(reference,', ') from container where shipment_id = shipment.id) AS containerReference, \n"
+ getContainerTypeCount()
+ "(select sum(grossWeight) from container where shipment_id = shipment.id) as grossWeight,\n"
+ "(select sum(grossVolume) from container where shipment_id = shipment.id) as grossVolume, \n"
+ "(select sum(cartonspercontainer) from container where shipment_id = shipment.id) as noOfCartonsPerShipment, \n"
+ getDirectInvoiceReference()
+ getDirectInvoiceDate(dateFormat)
+ getDirectInvoiceCurrency()
+ getDirectInvoiceValue()
+ "(select to_char(customsentrydate, '" + dateFormat + "') from container where \n"
+ "shipment_id = shipment.id limit 1) as customsEntryDate, \n"
+ "(select to_char(customsexitdate, '" + dateFormat + "') from container where \n"
+ "shipment_id = shipment.id limit 1) as customsExitDate, \n"
+ "(select to_char(dischargedate, '" + dateFormat + "') from container where \n"
+ "shipment_id = shipment.id limit 1) as vesselDischargeDate, \n"
+ "(select to_char(pickupdate, '" + dateFormat + "') from container where \n"
+ "shipment_id = shipment.id limit 1) as pickUpDate, \n"
+ "(select to_char(scheduleddateatfinaldestination, '" + dateFormat + "') from container where \n"
+ "shipment_id = shipment.id limit 1) as scheduledDateAtFinalDestination, \n"
+ "(select to_char(ataatfinaldestination, '" + dateFormat + "') from container where \n"
+ "shipment_id = shipment.id limit 1) as aTAAtFinalDestination, (select string_agg(reason,', ') from "
+ "consignment_freetextcomments where consignment_id = consignment.id) AS consignmentComment, \n"
+ "orders.goodsreceiveddate as goodsReceivedDate, orders.inwarehousedate as inWarehouseDate, \n"
+ "to_char((select ordersevent.created from ordersevent left join orders_ordersevent on ordersevent.id = "
+ "orders_ordersevent.events_id where ordersevent.eventtype = 'FINALISED' and orders_ordersevent.orders_id = orders.id order "
+ "by ordersevent.created desc limit 1) , '" + dateFormat + "') as actualCostingDate, \n" +
invoicesOrderAmountPerGroup("USD", "indirectFreightInvoiceCosts", "FreightCosts.")
+ " 'USD' as freightCurrency," +
invoicesOrderAmountPerGroup("ZAR", "indirectClearingInvoiceCosts", "ClearingCosts.")
+ " 'ZAR' as clearingCurrency,"
+ invoicesOrderAmountPerGroup("ZAR", "indirectCustomsDutyCosts", "CustomsDuty")
+ " 'ZAR' as dutyCurrency,"
+ invoicesOrderAmountPerGroup("ZAR", "indirectCustomsVATCosts", "VAT")
+ " (select string_agg(costline.transactioncurrency_code, ', ') from serviceproviderinvoice left join \n"
+ "costsinvoice on serviceproviderinvoice.id = costsinvoice.id left join costsinvoice_costlinecostingcell \n"
+ "on costsinvoice.id = costsinvoice_costlinecostingcell.costsinvoice_id left join costlinecostingcell \n"
+ "on costsinvoice_costlinecostingcell.costlinecostingcells_id = costlinecostingcell.id left join costline \n"
+ "on costlinecostingcell.costline_id = costline.id where costline.costlinetemplate_code like '%VAT%' \n"
+ "and transactionamount is not null and state <> 'DELETED'\n"
+ "and transactionamount != 0 and costsinvoice.shipment_id = consignment.shipment_id) as vATCurrency,\n"
+ "(select string_agg(COALESCE(to_char(datereceived, '" + dateFormat + "'), ''),', ') from costsinvoice where \n"
+ "costsinvoice.shipment_id = consignment.shipment_id and state <> 'DELETED' and \n"
+ "costsinvoice.id in (select serviceproviderinvoice.id from serviceproviderinvoice left join \n"
+ "costsinvoice on serviceproviderinvoice.id = costsinvoice.id left join costsinvoice_costlinecostingcell \n"
+ "on costsinvoice.id = costsinvoice_costlinecostingcell.costsinvoice_id left join costlinecostingcell \n"
+ "on costsinvoice_costlinecostingcell.costlinecostingcells_id = costlinecostingcell.id left join costline \n"
+ "on costlinecostingcell.costline_id = costline.id where costline.costlinetemplate_code ilike '%CustomsCosts%' \n"
+ "and transactionamount is not null and transactionamount != 0)) as indDocReceivedDate, \n"
+ " (select string_agg(finaldestination.name,', ') from container left join finaldestination on \n"
+ " container.finaldestination_code = finaldestination.code where shipment_id = shipment.id) AS finalDestFromContainer, \n"
+ "to_char ((select costlinecostingcell.updated from serviceproviderinvoice left join \n"
+ "costsinvoice on serviceproviderinvoice.id = costsinvoice.id left join costsinvoice_costlinecostingcell \n"
+ "on costsinvoice.id = costsinvoice_costlinecostingcell.costsinvoice_id left join costlinecostingcell \n"
+ "on costsinvoice_costlinecostingcell.costlinecostingcells_id = costlinecostingcell.id left join costline \n"
+ "on costlinecostingcell.costline_id = costline.id where costline.costlinetemplate_code like '%VAT%' and \n"
+ "transactionamount is not null and state <> 'DELETED'\n"
+ "and transactionamount != 0 and costsinvoice.shipment_id = consignment.shipment_id limit 1) , '" + dateFormat + "') "
+ "as costingDate, \n"
+ lineItemsQuantity() + " as unitQuantity,"
+ orderCurrency() + " as estimateSupplyCostCurrency,"
+ getPlannedSettlementsPercentage() + " as plannedSettlementsPercentage,"
+ getEstimateSupplyCost() + " as estimateSupplyCosts "
+ " from orders \n"
+ " left join organisationalunit on orders.organisationalunit_id = organisationalunit.id \n"
+ " left join purchaseorder on orders.id =purchaseorder.id \n"
+ " left join organisationalunitsupplier on purchaseorder.supplier_id = organisationalunitsupplier.id \n"
+ " left join supplier on organisationalunitsupplier.supplier_id = supplier.id \n"
+ " left join employee on purchaseorder.buyer_id = employee.id \n"
+ " left join shippinginformation on orders.shippinginformation_id = shippinginformation.id \n"
+ " left join placeofloading on shippinginformation.placeofloading_code = placeofloading.code \n"
+ " left join placeofdischarge on shippinginformation.placeofdischarge_code = placeofdischarge.code \n"
+ " left join finaldestination on shippinginformation.finaldestination_code = finaldestination.code \n"
+ " left join country on shippinginformation.countryoforigin_code = country.code \n"
+ " left join incoterm on shippinginformation.incoterm_code = incoterm.code \n"
+ " left join paymentterm on orders.paymentterm_code = paymentterm.code \n"
+ " left join paymentmethod on orders.paymentmethod_code = paymentmethod.code \n"
+ " left join consignment on orders.consignment_id = consignment.id left join shipment on consignment.shipment_id = "
+ "shipment.id \n"
+ " left join seashipment on shipment.id = seashipment.id left join airshipment on shipment.id = airshipment.id \n"
+ " left join shipmentshippinginfo on shipment.shippinginfo_id =shipmentshippinginfo.id \n"
+ " left join shipment_freetextcomments on shipment.id = shipment_freetextcomments.shipment_id \n"
+ " left join paymentbasis on paymentbasis.code = supplier.estimatedpaymentbasis_code \n"
+ " left join serviceprovider on serviceprovider.id = seashipment.carrier_id \n"
+ " where orders.state in('SIGNED_OFF', 'BOOKED_IN', 'FREIGHT_RECEIVED','SHIPMENT_CREATED','STOCK_RECEIVED'," +
"'STOCK_PARTIALLY_RECEIVED') "
+ orgUnitFilter
+ " and (shipment.id is null OR shipment.imports = 't')"
+ " and orders.created > date '" + simpleDateFormat.format(from) + "'"
+ "order by shipment.reference;\n";
//log.debug(query);
return session.createSQLQuery(query).addScalar("shipmentReference", StandardBasicTypes.STRING)
.addScalar("shipmentStatus", StandardBasicTypes.STRING)
.addScalar("comments", StandardBasicTypes.STRING)
.addScalar("orderReference", StandardBasicTypes.STRING)
.addScalar("orderCreationDate", StandardBasicTypes.STRING)
.addScalar("orderSignOffDate", StandardBasicTypes.STRING)
.addScalar("buyer", StandardBasicTypes.STRING)
.addScalar("division", StandardBasicTypes.STRING)
.addScalar("supplier", StandardBasicTypes.STRING)
.addScalar("countryFrom", StandardBasicTypes.STRING)
.addScalar("incoterm", StandardBasicTypes.STRING)
.addScalar("paymentTerm", StandardBasicTypes.STRING)
.addScalar("paymentMethod", StandardBasicTypes.STRING)
.addScalar("paymentBasis1", StandardBasicTypes.STRING)
.addScalar("estimatedSettlementDate", StandardBasicTypes.STRING)
.addScalar("settlementDate", StandardBasicTypes.STRING)
.addScalar("availabilityDate", StandardBasicTypes.STRING)
.addScalar("orderOriginalLatestShipmentDate", StandardBasicTypes.STRING)
.addScalar("latestShipmentDate", StandardBasicTypes.STRING)
.addScalar("orderRequiredOnSiteDate", StandardBasicTypes.STRING)
.addScalar("exFactoryDate", StandardBasicTypes.STRING)
.addScalar("shippingMode", StandardBasicTypes.STRING)
.addScalar("portOfLoading", StandardBasicTypes.STRING)
.addScalar("portOfDocking", StandardBasicTypes.STRING)
.addScalar("finalDestination", StandardBasicTypes.STRING)
.addScalar("bookingDate", StandardBasicTypes.STRING)
.addScalar("lSPBookingRef", StandardBasicTypes.STRING)
.addScalar("plannedShipmentDate", StandardBasicTypes.STRING)
.addScalar("estimateArrivalDate", StandardBasicTypes.STRING)
.addScalar("orderComments", StandardBasicTypes.STRING)
.addScalar("shipmentScheduledDateOfDeparture", StandardBasicTypes.STRING)
.addScalar("shipmentActualDateOfDeparture", StandardBasicTypes.STRING)
.addScalar("shipmentEstimateArrivalDateAtPOD", StandardBasicTypes.STRING)
.addScalar("ShipmentActualArrivalDateAtPOD", StandardBasicTypes.STRING)
.addScalar("copyDocsReceived", StandardBasicTypes.STRING)
.addScalar("originalDocsReceived", StandardBasicTypes.STRING)
.addScalar("carrier", StandardBasicTypes.STRING)
.addScalar("vesselNameAndVoyage", StandardBasicTypes.STRING)
.addScalar("voyageNumber", StandardBasicTypes.STRING)
.addScalar("billOfLadingNo", StandardBasicTypes.STRING)
.addScalar("airWayBillNo", StandardBasicTypes.STRING)
.addScalar("flightNumber", StandardBasicTypes.STRING)
.addScalar("Airline", StandardBasicTypes.STRING)
.addScalar("houseWayBillNo", StandardBasicTypes.STRING)
.addScalar("containerReference", StandardBasicTypes.STRING)
.addScalar("total20", StandardBasicTypes.STRING)
.addScalar("total40", StandardBasicTypes.STRING)
.addScalar("totalFCL40GOH", StandardBasicTypes.STRING)
.addScalar("totalLCL", StandardBasicTypes.STRING)
.addScalar("totalLCLSea", StandardBasicTypes.STRING)
.addScalar("totalLCLConsol", StandardBasicTypes.STRING)
.addScalar("totalTEU", StandardBasicTypes.STRING)
.addScalar("grossWeight", StandardBasicTypes.STRING)
.addScalar("grossVolume", StandardBasicTypes.STRING)
.addScalar("noOfCartonsPerShipment", StandardBasicTypes.STRING)
.addScalar("directInvoiceNo", StandardBasicTypes.STRING)
.addScalar("directInvoiceDate", StandardBasicTypes.STRING)
.addScalar("currency", StandardBasicTypes.STRING)
.addScalar("directInvoiceValue", StandardBasicTypes.STRING)
.addScalar("customsEntryDate", StandardBasicTypes.STRING)
.addScalar("customsExitDate", StandardBasicTypes.STRING)
.addScalar("vesselDischargeDate", StandardBasicTypes.STRING)
.addScalar("pickUpDate", StandardBasicTypes.STRING)
.addScalar("scheduledDateAtFinalDestination", StandardBasicTypes.STRING)
.addScalar("aTAAtFinalDestination", StandardBasicTypes.STRING)
.addScalar("consignmentComment", StandardBasicTypes.STRING)
.addScalar("goodsReceivedDate", StandardBasicTypes.STRING)
.addScalar("inWarehouseDate", StandardBasicTypes.STRING)
.addScalar("actualCostingDate", StandardBasicTypes.STRING)
.addScalar("indirectFreightInvoiceCosts", StandardBasicTypes.STRING)
.addScalar("freightCurrency", StandardBasicTypes.STRING)
.addScalar("indirectClearingInvoiceCosts", StandardBasicTypes.STRING)
.addScalar("clearingCurrency", StandardBasicTypes.STRING)
.addScalar("indirectCustomsDutyCosts", StandardBasicTypes.STRING)
.addScalar("dutyCurrency", StandardBasicTypes.STRING)
.addScalar("indirectCustomsVATCosts", StandardBasicTypes.STRING)
.addScalar("vATCurrency", StandardBasicTypes.STRING)
.addScalar("indDocReceivedDate", StandardBasicTypes.STRING)
.addScalar("finalDestFromContainer", StandardBasicTypes.STRING)
.addScalar("costingDate", StandardBasicTypes.STRING)
.addScalar("unitQuantity", StandardBasicTypes.STRING)
.addScalar("estimateSupplyCostCurrency", StandardBasicTypes.STRING)
.addScalar("plannedSettlementsPercentage", StandardBasicTypes.STRING)
.addScalar("estimateSupplyCosts", StandardBasicTypes.STRING)
.setResultTransformer(Transformers.aliasToBean(ShippingRegisterSearchResult.class)).list();
}
private static String invoicesOrderAmountPerGroup(final String currency, final String alias, final String group) {
return "(select sum(COALESCE(ccc.transactionamount)) from serviceproviderinvoice spi " +
"join costsinvoice ci on (spi.id=ci.id) " +
"join actualconsignment con on (con.costsinvoice_id=ci.id) " +
"join actualorder ao on (ao.actualconsignment_id=con.id) " +
"join actualorder_costlinecostingcell acc on ( ao.id=acc.actualorder_id) " +
"join costlinecostingcell ccc on acc.costlinecostingcells_id = ccc.id " +
"join costline on ccc.costline_id = costline.id " +
"where " +
"state <> 'DELETED' and ci.shipment_id = consignment.shipment_id and costline.costlinetemplate_code ilike '%"
+ group + "%' and ccc.transactionamount " +
"is not null and ccc.transactionamount != 0 and ci.currency_code = '" + currency + "'" +
"and ao.reference = orders.orderReference ) as " + alias + ",\n";
}
private static String getDirectInvoiceValue() {
return "(select sum(transactionamount) from costlinecostingcell\n"
+ "left join actualorder_costlinecostingcell on costlinecostingcell.id = actualorder_costlinecostingcell.costlinecostingcells_id\n"
+ "left join actualorder on actualorder.id = actualorder_costlinecostingcell.actualorder_id\n"
+ "left join actualconsignment on actualorder.actualconsignment_id = actualconsignment.id\n"
+ "left join costsinvoice on actualconsignment.costsinvoice_id = costsinvoice.id\n"
+ "left join commercialinvoice on commercialinvoice.id = costsinvoice.id \n"
+ "where actualorder.reference = orders.orderreference\n"
+ "and transactionamount is not null\n"
+ "and actualorder.totalvalue <> 0\n"
+ "and costsinvoice.state <> 'DELETED' "
+ "and costsinvoice.shipment_id = shipment.id) as directInvoiceValue,";
/*return " (COALESCE(" + getCostlineValue(new String[]{"SupplyCosts.Supply","Exworks."}, null) + ",0) - " +
"COALESCE(" + getCostlineValue(new String[]{"Exworks.Discount"}, null) + ",0)) as directInvoiceValue, ";*/
}
private String getCostlineValue(String[] costlines, String excludeCostline) {
String query = "(select sum(transactionamount) from costlinecostingcell\n"
+ "left join costline on costlinecostingcell.costline_id = costline.id\n"
+ "left join actualorder_costlinecostingcell on costlinecostingcell.id = actualorder_costlinecostingcell.costlinecostingcells_id\n"
+ "left join actualorder on actualorder.id = actualorder_costlinecostingcell.actualorder_id\n"
+ "left join actualconsignment on actualorder.actualconsignment_id = actualconsignment.id\n"
+ "left join costsinvoice on actualconsignment.costsinvoice_id = costsinvoice.id\n"
+ "left join commercialinvoice on commercialinvoice.id = costsinvoice.id "
+ "where actualorder.reference = orders.orderreference "
+ "and transactionamount is not null "
+ "and transactionamount != 0 ";
int i = 0;
for (String costline : costlines) {
if (i == 0) {
query = query + " and (";
} else {
query = query + " or ";
}
query = query + " costline.costlinetemplate_code like '%" + costline + "%' ";
i++;
}
query = query + ") "
+ "and actualorder.totalvalue <> 0 "
+ "and costsinvoice.state <> 'DELETED'";
if (excludeCostline != null && excludeCostline != "") {
query = query + " and costline.costlinetemplate_code <> '" + excludeCostline + "'";
}
query = query + " and state <> 'DELETED' "
+ " and costsinvoice.shipment_id = consignment.shipment_id)";
return query;
}
private static String getDirectInvoiceCurrency() {
return "(select string_agg(currency_code,', ') from costsinvoice where costsinvoice.shipment_id = consignment.shipment_id \n"
+ " and state <> 'DELETED'\n"
+ "and costsinvoice.id in (select commercialinvoice.id from commercialinvoice left join costsinvoice on \n"
+ "commercialinvoice.id = costsinvoice.id)) as currency, \n";
}
private static String getDirectInvoiceDate(String dateFormat) {
return " (select string_agg(COALESCE(to_char(costsinvoice.datecreated, '" + dateFormat + "'), ''),', ') from actualorder \n"
+ "left join actualconsignment on actualconsignment.id = actualorder.actualconsignment_id \n"
+ "left join costsinvoice on costsinvoice.id = actualconsignment.costsinvoice_id \n"
+ "right join commercialinvoice on commercialinvoice.id = costsinvoice.id \n"
+ "where actualorder.reference = orders.orderreference and actualorder.totalvalue <> 0 "
+ "and costsinvoice.state <> 'DELETED') as directInvoiceDate, ";
}
private static String getDirectInvoiceReference() {
return "(select string_agg(''||costsinvoice.reference,', ') from actualorder \n"
+ "left join actualconsignment on actualconsignment.id = actualorder.actualconsignment_id \n"
+ "left join costsinvoice on costsinvoice.id = actualconsignment.costsinvoice_id \n"
+ "right join commercialinvoice on commercialinvoice.id = costsinvoice.id \n"
+ "where actualorder.reference = orders.orderreference and actualorder.totalvalue <> 0 "
+ "and costsinvoice.state <> 'DELETED') as directInvoiceNo, ";
}
private static String getCustomsDutyValue(String costline, String alias) {
return "(select sum(transactionamount) from serviceproviderinvoice left join \n"
+ "costsinvoice on serviceproviderinvoice.id = costsinvoice.id left join costsinvoice_costlinecostingcell \n"
+ "on costsinvoice.id = costsinvoice_costlinecostingcell.costsinvoice_id left join costlinecostingcell \n"
+ "on costsinvoice_costlinecostingcell.costlinecostingcells_id = costlinecostingcell.id left join costline \n"
+ "on costlinecostingcell.costline_id = costline.id where costline.costlinetemplate_code like '%" + costline + "%' \n"
+ "and transactionamount is not null and state <> 'DELETED'\n"
+ "and transactionamount != 0 and costsinvoice.shipment_id = consignment.shipment_id) as " + alias + ", ";
}
private static String getContainerTypeCount() {
return "(select count(*) from container where shipment_id = shipment.id and containertype_code = 'FCL_20G0') as total20, \n"
+ "(select count(*) from container where shipment_id = shipment.id and containertype_code = 'FCL_42G0') as total40, \n"
+ "(select count(*) from container where shipment_id = shipment.id and containertype_code = 'FCL_45G0') as totalFCL40GOH, \n"
+ "(select count(*) from container where shipment_id = shipment.id and containertype_code = 'LCL') as totalLCL, \n"
+ "(select count(*) from container where shipment_id = shipment.id and containertype_code = 'LCL SEA') as totalLCLSea, \n"
+ "(select count(*) from container where shipment_id = shipment.id and containertype_code = 'FCL_CONSOL') as totalLCLConsol, \n"
+ "((select count(*) from container where shipment_id = shipment.id and containertype_code = 'FCL_20G0')+ \n"
+ "(2*(select count(*) from container where shipment_id = shipment.id and containertype_code = 'FCL_42G0'))) as totalTEU, \n";
}
private static String getPlaceOfDischarge() {
return "(CASE WHEN (select name from placeofdischarge where code in (select placeofdischarge_code"
+ " from shipmentshippinginfo where id in (select shippinginfo_id from shipment s where s.id = shipment.id))) is NULL\n"
+ "THEN (select name from placeofdischarge where code in (select placeofdischarge_code"
+ " from shippinginformation where id in (select shippinginformation_id from orders o where o.id = orders.id)))\n"
+ "ELSE (select name from placeofdischarge where code in (select placeofdischarge_code"
+ " from shipmentshippinginfo where id in (select shippinginfo_id from shipment s where s.id = shipment.id)))\n"
+ "END) as portOfDocking, ";
}
private static String getPlaceOfLoading() {
return "(CASE WHEN (select name from placeofloading where code in (select placeofloading_code"
+ " from shipmentshippinginfo where id in (select shippinginfo_id from shipment s where s.id = shipment.id))) is NULL\n"
+ "THEN (select name from placeofloading where code in (select placeofloading_code"
+ " from shippinginformation where id in (select shippinginformation_id from orders o where o.id = orders.id)))\n"
+ "ELSE (select name from placeofloading where code in (select placeofloading_code"
+ " from shipmentshippinginfo where id in (select shippinginfo_id from shipment s where s.id = shipment.id)))\n"
+ "END) as portOfLoading,\n";
}
private static String estimateSettlementDate(String dateFormat) {
return "(select string_agg(COALESCE(to_char(plannedsettlement.settlementdate, '" + dateFormat + "'), ''),', ')" +
getJoinedPlannedSettlement() + ") ";
}
private static String getJoinedPlannedSettlement() {
return " from plannedsettlement \n"
+ " where order_id = orders.id";
}
private static String lineItemsQuantity() {
return "(select sum(unitquantity) from lineitem where orders.id = lineitem.order_id) ";
}
private static String orderCurrency() {
return "orders.currency_code ";
}
private static String getEstimateSupplyCost() {
//will return comma separated percentage of PS linked to order.
String roundToTwoDecimal = "round(amount,2)";
String convertToString = "CAST(" + roundToTwoDecimal + "AS VARCHAR )";
return "(select string_agg(" + convertToString + ",',')" + getJoinedPlannedSettlement() + ") ";
}
private static String getPlannedSettlementsPercentage() {
//will return comma separated percentage of PS linked to order.
String percent = "(amount/totalinvoicevalue)*100";
String roundToTwoDecimal = "round(" + percent + ",2)";
String convertToString = "CAST(" + roundToTwoDecimal + "AS VARCHAR )";
return "(select string_agg(" + convertToString + ",',')" + getJoinedPlannedSettlement() + ") ";
}
}