Fixes issue 34031: Return From Customer pick and edit is slow with big data
authorAtul Gaware <atul.gaware@openbravo.com>
Wed, 28 Sep 2016 17:57:08 +0530
changeset 30334 17fbbe90b24e
parent 30333 cfba396452d5
child 30335 fda6c9f97c7f
Fixes issue 34031: Return From Customer pick and edit is slow with big data

Removed unnescessary join with M_Inoutline, M_Inout.
Restructured HQL inserter query for discount using exists clause.
src-db/database/sourcedata/AD_TABLE.xml
src/org/openbravo/common/datasource/ReturnFromCustomerHQLInserter.java
src/org/openbravo/common/datasource/ReturnToFromCustomerVendorHQLTransformer.java
--- a/src-db/database/sourcedata/AD_TABLE.xml	Thu Sep 29 10:36:32 2016 +0200
+++ b/src-db/database/sourcedata/AD_TABLE.xml	Wed Sep 28 17:57:08 2016 +0530
@@ -12095,19 +12095,19 @@
 <!--CDB9DC9655F24DF8AB41AA0ADBD04390-->  <ISTREE><![CDATA[N]]></ISTREE>
 <!--CDB9DC9655F24DF8AB41AA0ADBD04390-->  <HQLQUERY><![CDATA[select iol.id as id, iol.organization as organization, iol.client as client,  iol.product as product,  
 iol.uOM as uOM, iol.id as goodsShipmentLine, iol.movementQuantity as movementQuantity,
-iol.shipmentReceipt.businessPartner as businessPartner, iol.shipmentReceipt.documentNo as shipmentNumber,
-iol.shipmentReceipt.movementDate as movementDate,  attributeSetValue as attributeSetValue,
+io.businessPartner as businessPartner, io.documentNo as shipmentNumber,
+io.movementDate as movementDate,  attributeSetValue as attributeSetValue,
 (@returnedOthersLeftClause@)*(-1) as returnQtyOtherRM,
-(case when (select ('Y') from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol) is null then false else true end) as obSelected,
+(case when (select ('Y') from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol.id) is null then false else true end) as obSelected,
 (@returnedLeftClause@)*(-1) as returned,
-(select ol.returnReason from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol) as returnReason,
-coalesce((select ol.tax.id from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol) ,
+(select ol.returnReason from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol.id) as returnReason,
+coalesce((select ol.tax.id from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol.id) ,
 @taxSubQuery@) as tax,
 (case when @priceIncludeTaxSubQuery@ = true then
- coalesce((select ol.@unitPriceProperty@ from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), 
+ coalesce((select ol.@unitPriceProperty@ from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol.id), 
           @grossUnitPriceSubQuery@)
 else 
- coalesce((select ol.@unitPriceProperty@ from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), 
+ coalesce((select ol.@unitPriceProperty@ from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol.id), 
           @unitPriceSubQuery@)
 end) as unitPrice,
 @orderNoSubQuery@ as orderNo
--- a/src/org/openbravo/common/datasource/ReturnFromCustomerHQLInserter.java	Thu Sep 29 10:36:32 2016 +0200
+++ b/src/org/openbravo/common/datasource/ReturnFromCustomerHQLInserter.java	Wed Sep 28 17:57:08 2016 +0530
@@ -11,7 +11,7 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2014 Openbravo SLU
+ * All portions are Copyright (C) 2014-2016 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  *************************************************************************
@@ -41,7 +41,7 @@
     // discounts
     String buttonOwnerViewTabId = requestParameters.get("buttonOwnerViewTabId");
     if (RETURN_FROM_CUSTOMER_TAB_ID.equals(buttonOwnerViewTabId)) {
-      return " (select iol.salesOrderLine.orderDiscount from MaterialMgmtShipmentInOutLine as e where e.id = iol) is null ";
+      return " exists (select 1 from OrderLine e where e.id = iol.salesOrderLine.id and e.orderDiscount is null) ";
     } else {
       return null;
     }
--- a/src/org/openbravo/common/datasource/ReturnToFromCustomerVendorHQLTransformer.java	Thu Sep 29 10:36:32 2016 +0200
+++ b/src/org/openbravo/common/datasource/ReturnToFromCustomerVendorHQLTransformer.java	Wed Sep 28 17:57:08 2016 +0530
@@ -31,28 +31,28 @@
 
   private static final String returnToVendorTabId = "5A5CCFC8359B4D79BA705DC487FE8173";
 
-  private static final String rtv_unitPriceLeftClause = "(case when (select e.salesOrderLine.salesOrder.priceList.priceIncludesTax from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol) = true then  coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), (select e.salesOrderLine.grossUnitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)) else   coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), (select e.salesOrderLine.unitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)) end)";
-  private static final String rtv_orderNoLeftClause = " coalesce ((select e.salesOrderLine.salesOrder.documentNo from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol), '')";
-  private static final String rfc_unitPriceLeftClause = "(case when (iol.salesOrderLine.salesOrder.priceList.priceIncludesTax) = true then  coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), (iol.salesOrderLine.grossUnitPrice)) else   coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), (coalesce(iol.salesOrderLine.unitPrice,0))) end)";
-  private static final String rfc_orderNoLeftClause = " coalesce((select e.salesOrderLine.salesOrder.documentNo from MaterialMgmtShipmentInOutLine as e where e.id = iol), '')";
-  private static final String returnedLeftClause = " coalesce((select ol.orderedQuantity from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol),0)";
-  private static final String returnedOthersLeftClause = " coalesce((select sum(ol.orderedQuantity) from OrderLine as ol join ol.salesOrder as o where ol.goodsShipmentLine.id = iol and o.processed = true and o.documentStatus <> 'VO'), 0)";
-  private static final String returnReasonLeftClause = " coalesce((select ol.returnReason.id from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), '')";
-  private static final String returnReasonLeftClause2 = " coalesce((select ol.returnReason.name from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), '')";
+  private static final String rtv_unitPriceLeftClause = "(case when (select e.salesOrderLine.salesOrder.priceList.priceIncludesTax from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol.id) = true then  coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol.id), (select e.salesOrderLine.grossUnitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol.id)) else   coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol.id), (select e.salesOrderLine.unitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol.id)) end)";
+  private static final String rtv_orderNoLeftClause = " coalesce ((select e.salesOrderLine.salesOrder.documentNo from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol.id), '')";
+  private static final String rfc_unitPriceLeftClause = "(case when (iol.salesOrderLine.salesOrder.priceList.priceIncludesTax) = true then  coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol.id), (iol.salesOrderLine.grossUnitPrice)) else   coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol.id), (coalesce(iol.salesOrderLine.unitPrice,0))) end)";
+  private static final String rfc_orderNoLeftClause = " coalesce((select e.salesOrderLine.salesOrder.documentNo from MaterialMgmtShipmentInOutLine as e where e.id = iol.id), '')";
+  private static final String returnedLeftClause = " coalesce((select ol.orderedQuantity from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol.id),0)";
+  private static final String returnedOthersLeftClause = " coalesce((select sum(ol.orderedQuantity) from OrderLine as ol join ol.salesOrder as o where ol.goodsShipmentLine.id = iol.id and o.processed = true and o.documentStatus <> 'VO'), 0)";
+  private static final String returnReasonLeftClause = " coalesce((select ol.returnReason.id from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol.id), '')";
+  private static final String returnReasonLeftClause2 = " coalesce((select ol.returnReason.name from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol.id), '')";
   private static final String returnReasonCountQuery = " select count(distinct e.name) from ReturnReason as e where exists (select distinct ol.returnReason from OrderLine as ol where ol.returnReason = e and ol.salesOrder.id = :salesOrderId  and ol.goodsShipmentLine is not null) ";
   private static final String returnReasonDataQuery = " select distinct e, e.name from ReturnReason as e where exists (select distinct ol.returnReason from OrderLine as ol where ol.returnReason = e and ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine is not null) ";
   private static final String unitPriceProperty = "unitPrice";
   private static final String grossUnitPriceProperty = "grossUnitPrice";
 
-  private static final String rtv_orderNo = "(select e.salesOrderLine.salesOrder.documentNo from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)";
-  private static final String rfc_orderNo = "(select e.salesOrderLine.salesOrder.documentNo from MaterialMgmtShipmentInOutLine as e where e.id = iol)";
-  private static final String rtv_unitPrice = "(select e.salesOrderLine.unitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)";
-  private static final String rfc_unitPrice = "(select e.salesOrderLine.unitPrice from MaterialMgmtShipmentInOutLine as e where e.id = iol)";
-  private static final String rtv_grossUnitPrice = "(select e.salesOrderLine.grossUnitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)";
-  private static final String rfc_grossUnitPrice = "(select e.salesOrderLine.grossUnitPrice from MaterialMgmtShipmentInOutLine as e where e.id = iol)";
-  private static final String rtv_tax = "(select e.salesOrderLine.tax.id from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)";
-  private static final String rfc_tax = "(select e.salesOrderLine.tax.id from MaterialMgmtShipmentInOutLine as e where e.id = iol)";
-  private static final String rtv_priceIncludeTax = "(select e.salesOrderLine.salesOrder.priceList.priceIncludesTax from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)";
+  private static final String rtv_orderNo = "(select e.salesOrderLine.salesOrder.documentNo from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol.id)";
+  private static final String rfc_orderNo = "(select e.salesOrderLine.salesOrder.documentNo from MaterialMgmtShipmentInOutLine as e where e.id = iol.id)";
+  private static final String rtv_unitPrice = "(select e.salesOrderLine.unitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol.id)";
+  private static final String rfc_unitPrice = "(select e.salesOrderLine.unitPrice from MaterialMgmtShipmentInOutLine as e where e.id = iol.id)";
+  private static final String rtv_grossUnitPrice = "(select e.salesOrderLine.grossUnitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol.id)";
+  private static final String rfc_grossUnitPrice = "(select e.salesOrderLine.grossUnitPrice from MaterialMgmtShipmentInOutLine as e where e.id = iol.id)";
+  private static final String rtv_tax = "(select e.salesOrderLine.tax.id from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol.id)";
+  private static final String rfc_tax = "(select e.salesOrderLine.tax.id from MaterialMgmtShipmentInOutLine as e where e.id = iol.id)";
+  private static final String rtv_priceIncludeTax = "(select e.salesOrderLine.salesOrder.priceList.priceIncludesTax from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol.id)";
   private static final String rfc_priceIncludeTax = "(select e.priceList.priceIncludesTax from Order as e where e.id = :salesOrderId)";
 
   @Override