Fixed bug 28860 improve performance in UpdatePaymentPlan modulescript
authorSandra Huguet <sandra.huguet@openbravo.com>
Thu, 12 Mar 2015 09:39:15 +0100
changeset 26209 3d6525efbc70
parent 26208 14b642262f27
child 26210 ddd1f777209c
Fixed bug 28860 improve performance in UpdatePaymentPlan modulescript
src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlan.class
src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlanData.class
src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan.java
src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan_data.xsql
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlan.class has changed
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlanData.class has changed
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan.java	Thu Mar 12 09:39:15 2015 +0100
@@ -0,0 +1,41 @@
+/*
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
+ * Version 1.1  with a permitted attribution clause; you may not  use this
+ * file except in compliance with the License. You  may  obtain  a copy of
+ * the License at http://www.openbravo.com/legal/license.html
+ * Software distributed under the License  is  distributed  on  an "AS IS"
+ * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+ * License for the specific  language  governing  rights  and  limitations
+ * 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-2015 Openbravo SLU
+ * All Rights Reserved.
+ * Contributor(s):  ______________________________________.
+ *************************************************************************
+ */
+package org.openbravo.modulescript;
+
+import java.sql.PreparedStatement;
+import org.apache.log4j.Logger;
+
+import org.openbravo.database.ConnectionProvider;
+import org.openbravo.modulescript.ModuleScript;
+
+public class UpdatePaymentPlan extends ModuleScript {
+  private static final Logger log4j = Logger.getLogger(UpdatePaymentPlan.class);
+  public void execute() {
+    try {
+      ConnectionProvider cp = getConnectionProvider();
+      boolean isExecuted = UpdatePaymentPlanData.isExecuted(cp);
+      if (!isExecuted) {
+        UpdatePaymentPlanData.update(cp);
+        UpdatePaymentPlanData.createPreference(cp);
+      }
+    } catch (Exception e) {
+      handleError(e);
+    }
+  }
+}
\ No newline at end of file
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan_data.xsql	Thu Mar 12 09:39:15 2015 +0100
@@ -0,0 +1,73 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!--
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+ * Version 1.1  with a permitted attribution clause; you may not  use this
+ * file except in compliance with the License. You  may  obtain  a copy of
+ * the License at http://www.openbravo.com/legal/license.html
+ * Software distributed under the License  is  distributed  on  an "AS IS"
+ * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+ * License for the specific  language  governing  rights  and  limitations
+ * 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-2015 Openbravo SLU
+ * All Rights Reserved.
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+-->
+<SqlClass name="UpdatePaymentPlanData" package="org.openbravo.modulescript">
+   <SqlClassComment></SqlClassComment>
+   <SqlMethod name="dummy" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+      SELECT '' AS finpaymentscheduleid, '' AS paidamt FROM DUAL
+      ]]>
+    </Sql>
+    <Field name="rownum" value="count"/>
+  </SqlMethod>
+  <SqlMethod name="update" type="preparedStatement" return="rowCount">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+     update fin_payment_schedule set outstandingamt = amount - (select sum(psd.amount)
+                                                                from fin_payment_scheduledetail psd
+                                                                where isinvoicepaid='Y'
+                                                                and psd.fin_payment_schedule_order = fin_payment_schedule.fin_payment_schedule_id),
+                                                     paidamt = (select sum(psd.amount)
+                                                                from fin_payment_scheduledetail psd
+                                                                where isinvoicepaid='Y'
+                                                                and psd.fin_payment_schedule_order = fin_payment_schedule.fin_payment_schedule_id)
+     WHERE fin_payment_schedule.paidamt/2 = (select sum(psd.amount)
+     from fin_payment_scheduledetail psd
+     where isinvoicepaid='Y'
+     and psd.fin_payment_schedule_order = fin_payment_schedule.fin_payment_schedule_id)
+      ]]>
+    </Sql>
+  </SqlMethod>
+  <SqlMethod name="isExecuted" type="preparedStatement" return="boolean">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+        SELECT count(*) as exist
+        FROM DUAL
+        WHERE EXISTS (SELECT 1 FROM ad_preference
+                      WHERE attribute = 'PaymentPlanUpdatedV2')
+      ]]>
+    </Sql>
+  </SqlMethod>
+   <SqlMethod name="createPreference" type="preparedStatement" return="rowcount">
+      <SqlMethodComment></SqlMethodComment>
+       <Sql> 
+       <![CDATA[ 
+           INSERT INTO ad_preference (
+           ad_preference_id, ad_client_id, ad_org_id, isactive,
+           createdby, created, updatedby, updated,attribute
+           ) VALUES (
+           get_uuid(), '0', '0', 'Y', '0', NOW(), '0', NOW(),'PaymentPlanUpdatedV2')
+         ]]>
+        </Sql>
+   </SqlMethod>
+</SqlClass>