details:   https://code.openbravo.com/erp/devel/pi/rev/2c1823b1139d
changeset: 31102:2c1823b1139d
user:      Rafael Queralta Pozo <rqueralta <at> nauta.cu>
date:      Tue Jan 10 07:24:48 2017 -0500
summary:   Fixed issue 34877: Some changes needed to improve the performance in 
Pending GR

To improve the performance in Pending Goods Receipt window, the extra query was
remove and the message was corrected.

diffstat:

 src/org/openbravo/erpCommon/ad_forms/MaterialReceiptPending.java      |  27 
++---
 src/org/openbravo/erpCommon/ad_forms/MaterialReceiptPending_data.xsql |  43 
+--------
 2 files changed, 17 insertions(+), 53 deletions(-)

diffs (144 lines):

diff -r 8508f2e8710d -r 2c1823b1139d 
src/org/openbravo/erpCommon/ad_forms/MaterialReceiptPending.java
--- a/src/org/openbravo/erpCommon/ad_forms/MaterialReceiptPending.java  Mon Jan 
09 22:24:16 2017 -0500
+++ b/src/org/openbravo/erpCommon/ad_forms/MaterialReceiptPending.java  Tue Jan 
10 07:24:48 2017 -0500
@@ -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) 2001-2016 Openbravo SLU
+ * All portions are Copyright (C) 2001-2017 Openbravo SLU
  * All Rights Reserved. 
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -132,11 +132,6 @@
     String discard[] = { "sectionDetail" };
     XmlDocument xmlDocument = null;
 
-    // String strMessage =
-    // vars.getSessionValue("MaterialReceiptPending|message");
-    // vars.removeSessionValue("MaterialReceiptPending|message");
-    String tot;
-    int intTotal = 0;
     int limit = 0;
     MaterialReceiptPendingData[] data = null;
     String strTreeOrg = MaterialReceiptPendingData.treeOrg(this, 
vars.getClient());
@@ -145,20 +140,22 @@
           "org/openbravo/erpCommon/ad_forms/MaterialReceiptPending", 
discard).createXmlDocument();
       data = MaterialReceiptPendingData.set();
     } else {
-      tot = MaterialReceiptPendingData.countLines(this, vars.getLanguage(),
-          Utility.getContext(this, vars, "#User_Client", 
"MaterialReceiptPending"),
-          Tree.getMembers(this, strTreeOrg, strAD_Org_ID), strDateFrom,
-          DateTimeData.nDaysAfter(this, strDateTo, "1"), strC_BPartner_ID, 
strDocumentNo);
-      intTotal = new Integer(tot).intValue();
-      limit = Integer.parseInt(Utility.getPreference(vars, "FormsLimit", ""));
       xmlDocument = xmlEngine.readXmlTemplate(
           
"org/openbravo/erpCommon/ad_forms/MaterialReceiptPending").createXmlDocument();
 
+      limit = Integer.parseInt(Utility.getPreference(vars, "FormsLimit", ""));
+      String pgLimit = null, oraLimit = null;
+      if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) {
+        oraLimit = String.valueOf(limit + 1);
+      } else {
+        pgLimit = String.valueOf(limit + 1);
+      }
       String strDateFormat = vars.getSessionValue("#AD_SqlDateFormat");
       data = MaterialReceiptPendingData.selectLines(this, strDateFormat, 
vars.getLanguage(),
           Utility.getContext(this, vars, "#User_Client", 
"MaterialReceiptPending"),
           Tree.getMembers(this, strTreeOrg, strAD_Org_ID), strDateFrom,
-          DateTimeData.nDaysAfter(this, strDateTo, "1"), strC_BPartner_ID, 
strDocumentNo, 0, limit);
+          DateTimeData.nDaysAfter(this, strDateTo, "1"), strC_BPartner_ID, 
strDocumentNo, pgLimit,
+          oraLimit);
     }
 
     boolean preference = UOMUtil.isUomManagementEnabled();
@@ -210,12 +207,12 @@
     }
     {
       myMessage = vars.getMessage("MaterialReceiptPending");
-      if (intTotal > limit) {
+      if (limit > 0 && data.length > limit) {
         myMessage = new OBError();
         myMessage.setType("Warning");
         myMessage.setTitle("");
         String msgbody = Utility.messageBD(this, "OldFormsLimit", 
vars.getLanguage());
-        msgbody = msgbody.replace("@limit@", Integer.toString(limit));
+        msgbody = msgbody.replace("@limit@", Integer.toString(limit + 1));
         myMessage.setMessage(msgbody);
       }
       vars.removeMessage("MaterialReceiptPending");
diff -r 8508f2e8710d -r 2c1823b1139d 
src/org/openbravo/erpCommon/ad_forms/MaterialReceiptPending_data.xsql
--- a/src/org/openbravo/erpCommon/ad_forms/MaterialReceiptPending_data.xsql     
Mon Jan 09 22:24:16 2017 -0500
+++ b/src/org/openbravo/erpCommon/ad_forms/MaterialReceiptPending_data.xsql     
Tue Jan 10 07:24:48 2017 -0500
@@ -12,7 +12,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) 2001-2016 Openbravo SLU 
+ * All portions are Copyright (C) 2001-2017 Openbravo SLU 
  * All Rights Reserved. 
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -59,6 +59,7 @@
     <SqlMethodComment></SqlMethodComment>
     <Sql>
     <![CDATA[
+      SELECT B.* FROM (
       SELECT ID, C_ORDER_ID, DOCUMENTNO, DATEORDERED, C_BPARTNER_ID, 
PARTNER_NAME, PRODUCT_NAME, DESCRIPTION, TOTAL_QTY, QTYORDERED, ISACTIVE, ? AS 
DATE_FORMAT, AD_ORG_ID, AUMQTY, C_AUM, C_DOCTYPE_ID, M_PRODUCT_ID, C_UOM_ID
       FROM(  
       SELECT C_ORDERLINE.C_ORDERLINE_ID AS ID, C_ORDER.C_ORDER_ID AS 
C_ORDER_ID, C_ORDER.DOCUMENTNO AS DOCUMENTNO, C_ORDER.DATEORDERED AS 
DATEORDERED, 
@@ -86,8 +87,8 @@
       ) A
       WHERE (CASE WHEN Total_Qty < 0 THEN -1 ELSE 1 END) = (CASE WHEN 
Qtyordered < 0 THEN -1 ELSE 1 End) AND Qtyordered <> 0
       ORDER BY C_BPARTNER_ID, DOCUMENTNO, LINE
+      ) B
       ]]></Sql>
-       <Field name="rownum" value="count"/>
     <Parameter name="dateFormat"/>
     <Parameter name="adLanguage"/>
     <Parameter name="adUserClient" type="replace" optional="true" after="AND 
C_ORDER.AD_CLIENT_ID IN (" text="'1'"/>
@@ -96,42 +97,8 @@
     <Parameter name="parDateTo" optional="true" after="AND 
C_ORDER.ISSOTRX='N'"><![CDATA[ AND C_ORDER.DATEORDERED < 
TO_DATE(?)]]></Parameter>
     <Parameter name="parBPartner" optional="true" after="AND 
C_ORDER.ISSOTRX='N'"> AND C_BPARTNER.C_BPARTNER_ID = ?</Parameter>
     <Parameter name="parDocumentNo" optional="true" after="AND 
C_ORDER.ISSOTRX='N'"> AND C_ORDER.DOCUMENTNO LIKE ?</Parameter>
-  </SqlMethod>
-    <SqlMethod name="countLines" type="preparedStatement" return="String">
-    <SqlMethodComment></SqlMethodComment>
-    <Sql>
-    <![CDATA[
-      SELECT count(*)
-      FROM(  
-      SELECT C_ORDERLINE.C_ORDERLINE_ID AS ID, C_ORDER.C_ORDER_ID AS 
C_ORDER_ID, C_ORDER.DOCUMENTNO AS DOCUMENTNO, C_ORDER.DATEORDERED AS 
DATEORDERED, 
-      C_BPARTNER.C_BPARTNER_ID AS C_BPARTNER_ID, C_BPARTNER.NAME AS 
PARTNER_NAME, 
-      AD_COLUMN_IDENTIFIER(TO_CHAR('M_Product'), 
TO_CHAR(C_ORDERLINE.M_PRODUCT_ID), TO_CHAR(?)) AS PRODUCT_NAME, 
M_ATTRIBUTESETINSTANCE.DESCRIPTION AS DESCRIPTION, C_ORDERLINE.QTYORDERED AS 
TOTAL_QTY,
-      C_ORDERLINE.QTYORDERED-SUM(COALESCE(M_MATCHPO.QTY,0)) AS QTYORDERED, 
'-1' AS ISACTIVE, C_ORDERLINE.LINE
-      FROM C_ORDERLINE left join M_MATCHPO on C_ORDERLINE.C_ORDERLINE_ID = 
M_MATCHPO.C_ORDERLINE_ID
-                                           and M_MATCHPO.M_INOUTLINE_ID IS NOT 
NULL
-                       left join M_ATTRIBUTESETINSTANCE on 
C_ORDERLINE.M_ATTRIBUTESETINSTANCE_ID = 
M_ATTRIBUTESETINSTANCE.M_ATTRIBUTESETINSTANCE_ID,
-      C_ORDER,   C_BPARTNER, C_DOCTYPE
-      WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
-      AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
-      AND C_ORDER.AD_CLIENT_ID IN ('1')
-      AND C_ORDER.AD_ORG_ID IN ('1')
-      AND C_ORDER.ISSOTRX='N'
-      AND C_ORDER.DOCSTATUS = 'CO'
-      AND C_ORDER.C_DOCTYPE_ID = C_DOCTYPE.C_DOCTYPE_ID
-      AND C_DOCTYPE.ISRETURN = 'N'
-      GROUP BY C_ORDERLINE.C_ORDERLINE_ID, C_ORDER.C_ORDER_ID, 
C_ORDER.DOCUMENTNO, C_ORDER.DATEORDERED, C_BPARTNER.C_BPARTNER_ID,
-      C_BPARTNER.NAME, C_ORDERLINE.M_PRODUCT_ID, 
M_ATTRIBUTESETINSTANCE.DESCRIPTION, C_ORDERLINE.QTYORDERED, C_ORDERLINE.LINE
-      ) A
-      WHERE (CASE WHEN Total_Qty < 0 THEN -1 ELSE 1 END) = (CASE WHEN 
Qtyordered < 0 THEN -1 ELSE 1 End) AND Qtyordered <> 0
-      ]]></Sql>
-    <Field name="rownum" value="count"/>
-    <Parameter name="adLanguage"/>
-    <Parameter name="adUserClient" type="replace" optional="true" after="AND 
C_ORDER.AD_CLIENT_ID IN (" text="'1'"/>
-    <Parameter name="adOrgId" optional="true" type="replace" after="AND 
C_ORDER.AD_ORG_ID IN (" text="'1'"/>
-    <Parameter name="parDateFrom" optional="true" after="AND 
C_ORDER.ISSOTRX='N'"><![CDATA[ AND C_ORDER.DATEORDERED >= 
TO_DATE(?)]]></Parameter>
-    <Parameter name="parDateTo" optional="true" after="AND 
C_ORDER.ISSOTRX='N'"><![CDATA[ AND C_ORDER.DATEORDERED < 
TO_DATE(?)]]></Parameter>
-    <Parameter name="parBPartner" optional="true" after="AND 
C_ORDER.ISSOTRX='N'"> AND C_BPARTNER.C_BPARTNER_ID = ?</Parameter>
-    <Parameter name="parDocumentNo" optional="true" after="AND 
C_ORDER.ISSOTRX='N'"> AND C_ORDER.DOCUMENTNO LIKE ?</Parameter>
+    <Parameter name="pgLimit" type="argument" optional="true" after=") 
B"><![CDATA[LIMIT ]]></Parameter>
+    <Parameter name="oraLimit" type="argument" optional="true" after=") 
B"><![CDATA[WHERE ROWNUM <= ]]></Parameter>
   </SqlMethod>
   <SqlMethod name="insert" connection="true" type="preparedStatement" 
return="rowCount">
     <SqlMethodComment></SqlMethodComment>

------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits

Reply via email to