Fixes Issue 28076:InitializeBPCurrency modulescript needs to be improved
authorAtul Gaware <atul.gaware@openbravo.com>
Fri, 14 Nov 2014 14:52:54 +0530
changeset 25233 9eba998e1eb9
parent 25232 542ee51c68bf
child 25234 64f6ff1021bf
Fixes Issue 28076:InitializeBPCurrency modulescript needs to be improved

UpdateBpCurrency method update business partner currency from the invoice
if currency is null and there exists invoices in same currency. A preference
is created inorder to avoid repeatation.
src-util/modulescript/build/classes/org/openbravo/modulescript/InitializeBPCurrency.class
src-util/modulescript/build/classes/org/openbravo/modulescript/InitializeBPCurrencyData.class
src-util/modulescript/build/javasqlc/src/org/openbravo/modulescript/InitializeBPCurrencyData.java
src-util/modulescript/src/org/openbravo/modulescript/InitializeBPCurrency.java
src-util/modulescript/src/org/openbravo/modulescript/InitializeBPCurrency_data.xsql
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/InitializeBPCurrency.class has changed
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/InitializeBPCurrencyData.class has changed
--- a/src-util/modulescript/build/javasqlc/src/org/openbravo/modulescript/InitializeBPCurrencyData.java	Fri Nov 14 16:09:24 2014 +0530
+++ b/src-util/modulescript/build/javasqlc/src/org/openbravo/modulescript/InitializeBPCurrencyData.java	Fri Nov 14 14:52:54 2014 +0530
@@ -122,6 +122,43 @@
     return(updateCount);
   }
 
+  public static int UpdateBpCurrency(ConnectionProvider connectionProvider)    throws ServletException {
+    String strSql = "";
+    strSql = strSql + 
+      "        UPDATE C_Bpartner bp" +
+      " 		SET BP_Currency_ID = (" +
+      " 		                     SELECT DISTINCT i.C_Currency_ID" +
+      " 		                     FROM C_INVOICE i" +
+      " 		                     WHERE i.C_Bpartner_ID = bp.C_Bpartner_ID " +
+      " 		                     AND i.docstatus = 'CO'" +
+      " 		                     AND ((SELECT COUNT(DISTINCT i.C_CURRENCY_ID) FROM C_INVOICE i " +
+      " 		                     WHERE i.docstatus='CO' AND i.C_Bpartner_ID = bp.C_Bpartner_ID) = 1) " +
+      " 		                     )" +
+      " 		WHERE bp.BP_Currency_ID IS NULL";
+
+    int updateCount = 0;
+    PreparedStatement st = null;
+
+    try {
+    st = connectionProvider.getPreparedStatement(strSql);
+
+      updateCount = st.executeUpdate();
+    } catch(SQLException e){
+      log4j.error("SQL error in query: " + strSql + "Exception:"+ e);
+      throw new ServletException("@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
+    } catch(Exception ex){
+      log4j.error("Exception in query: " + strSql + "Exception:"+ ex);
+      throw new ServletException("@CODE=@" + ex.getMessage());
+    } finally {
+      try {
+        connectionProvider.releasePreparedStatement(st);
+      } catch(Exception ignore){
+        ignore.printStackTrace();
+      }
+    }
+    return(updateCount);
+  }
+
   public static boolean isExecuted(ConnectionProvider connectionProvider)    throws ServletException {
     String strSql = "";
     strSql = strSql + 
@@ -257,4 +294,72 @@
     }
     return(updateCount);
   }
+
+  public static boolean isUpdatedFromInvoice(ConnectionProvider connectionProvider)    throws ServletException {
+    String strSql = "";
+    strSql = strSql + 
+      "        SELECT count(*) as exist" +
+      "        FROM DUAL" +
+      "        WHERE EXISTS (SELECT 1 FROM ad_preference" +
+      "                      WHERE attribute = 'isUpdatedFromInvoice')";
+
+    ResultSet result;
+    boolean boolReturn = false;
+    PreparedStatement st = null;
+
+    try {
+    st = connectionProvider.getPreparedStatement(strSql);
+
+      result = st.executeQuery();
+      if(result.next()) {
+        boolReturn = !UtilSql.getValue(result, "exist").equals("0");
+      }
+      result.close();
+    } catch(SQLException e){
+      log4j.error("SQL error in query: " + strSql + "Exception:"+ e);
+      throw new ServletException("@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
+    } catch(Exception ex){
+      log4j.error("Exception in query: " + strSql + "Exception:"+ ex);
+      throw new ServletException("@CODE=@" + ex.getMessage());
+    } finally {
+      try {
+        connectionProvider.releasePreparedStatement(st);
+      } catch(Exception ignore){
+        ignore.printStackTrace();
+      }
+    }
+    return(boolReturn);
+  }
+
+  public static int createUpdateFromInvoicePref(ConnectionProvider connectionProvider)    throws ServletException {
+    String strSql = "";
+    strSql = strSql + 
+      "           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(),'isUpdatedFromInvoice')";
+
+    int updateCount = 0;
+    PreparedStatement st = null;
+
+    try {
+    st = connectionProvider.getPreparedStatement(strSql);
+
+      updateCount = st.executeUpdate();
+    } catch(SQLException e){
+      log4j.error("SQL error in query: " + strSql + "Exception:"+ e);
+      throw new ServletException("@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
+    } catch(Exception ex){
+      log4j.error("Exception in query: " + strSql + "Exception:"+ ex);
+      throw new ServletException("@CODE=@" + ex.getMessage());
+    } finally {
+      try {
+        connectionProvider.releasePreparedStatement(st);
+      } catch(Exception ignore){
+        ignore.printStackTrace();
+      }
+    }
+    return(updateCount);
+  }
 }
--- a/src-util/modulescript/src/org/openbravo/modulescript/InitializeBPCurrency.java	Fri Nov 14 16:09:24 2014 +0530
+++ b/src-util/modulescript/src/org/openbravo/modulescript/InitializeBPCurrency.java	Fri Nov 14 14:52:54 2014 +0530
@@ -37,12 +37,22 @@
         InitializeBPCurrencyData.initializeCurrency(cp);
         InitializeBPCurrencyData.createPreference(cp);
         InitializeBPCurrencyData.createUpdatePreference(cp);
+        // update bp currency if it is not updated by initializeCurrency
+        // and there exists completed invoice in same currency 
+        // for any business partner
+        InitializeBPCurrencyData.UpdateBpCurrency(cp);
+        InitializeBPCurrencyData.createUpdateFromInvoicePref(cp);
       } else {
         boolean isUpdated = InitializeBPCurrencyData.isUpdated(cp);
         if(!isUpdated) {
           InitializeBPCurrencyData.initializeCurrency(cp);
           InitializeBPCurrencyData.createUpdatePreference(cp);
         }
+        boolean isUpdatedFromInv = InitializeBPCurrencyData.isUpdatedFromInvoice(cp);
+        if(!isUpdatedFromInv) {
+          InitializeBPCurrencyData.UpdateBpCurrency(cp);
+          InitializeBPCurrencyData.createUpdateFromInvoicePref(cp);
+        }
       }
     } catch (Exception e) {
       handleError(e);
--- a/src-util/modulescript/src/org/openbravo/modulescript/InitializeBPCurrency_data.xsql	Fri Nov 14 16:09:24 2014 +0530
+++ b/src-util/modulescript/src/org/openbravo/modulescript/InitializeBPCurrency_data.xsql	Fri Nov 14 14:52:54 2014 +0530
@@ -44,6 +44,23 @@
       ]]>
     </Sql>
   </SqlMethod>
+  <SqlMethod name="UpdateBpCurrency" type="preparedStatement" return="rowCount">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+     <![CDATA[
+        UPDATE C_Bpartner bp
+ 		SET BP_Currency_ID = (
+ 		                     SELECT DISTINCT i.C_Currency_ID
+ 		                     FROM C_INVOICE i
+ 		                     WHERE i.C_Bpartner_ID = bp.C_Bpartner_ID 
+ 		                     AND i.docstatus = 'CO'
+ 		                     AND ((SELECT COUNT(DISTINCT i.C_CURRENCY_ID) FROM C_INVOICE i 
+ 		                     WHERE i.docstatus='CO' AND i.C_Bpartner_ID = bp.C_Bpartner_ID) = 1) 
+ 		                     )
+ 		WHERE bp.BP_Currency_ID IS NULL
+      ]]>
+    </Sql>
+  </SqlMethod>
   <SqlMethod name="isExecuted" type="preparedStatement" return="boolean">
     <SqlMethodComment></SqlMethodComment>
     <Sql>
@@ -90,4 +107,27 @@
          ]]>
         </Sql>
    </SqlMethod>
+   <SqlMethod name="isUpdatedFromInvoice" type="preparedStatement" return="boolean">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+        SELECT count(*) as exist
+        FROM DUAL
+        WHERE EXISTS (SELECT 1 FROM ad_preference
+                      WHERE attribute = 'isUpdatedFromInvoice')
+      ]]>
+    </Sql>
+  </SqlMethod>
+   <SqlMethod name="createUpdateFromInvoicePref" 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(),'isUpdatedFromInvoice')
+         ]]>
+        </Sql>
+   </SqlMethod>
 </SqlClass>
\ No newline at end of file