Fixes issue 20142: Duplicate Exchange Rates in Exchange Rate tab
authorMikel Irurita <mikel.irurita@openbravo.com>
Wed, 09 May 2012 12:42:17 +0200
changeset 16532 47bed64aeb30
parent 16531 83a79c15a605
child 16533 376f55b24a77
Fixes issue 20142: Duplicate Exchange Rates in Exchange Rate tab
src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicateDocExchangeRate.class
src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicateDocExchangeRateData.class
src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicateDocExchangeRate.java
src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicateDocExchangeRate_data.xsql
Binary file src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicateDocExchangeRate.class has changed
Binary file src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicateDocExchangeRateData.class has changed
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicateDocExchangeRate.java	Wed May 09 12:42:17 2012 +0200
@@ -0,0 +1,187 @@
+/*
+ *************************************************************************
+ * 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) 2012 Openbravo SLU
+ * All Rights Reserved.
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+ */
+package org.openbravo.buildvalidation;
+
+import java.util.ArrayList;
+import java.util.List;
+
+import org.openbravo.database.ConnectionProvider;
+
+/**
+ * This validation is related to this issue: https://issues.openbravo.com/view.php?id=20142
+ *  
+ * NULL values are not considered equal for PostgreSQL unique constraints
+ * http://www.postgresql.org/docs/9.0/static/ddl-constraints.html
+ * 
+ * If you are using PostgreSQL database you can have duplicated records in C_CONVERSION_RATE_DOCUMENT table
+ */
+public class DuplicateDocExchangeRate extends BuildValidation {
+  private static final String PAY = "PAY";
+  private static final String INV = "INV";
+  private static final String TRX = "TRX";
+  
+  private static final String SALES_INVOICE_HEADER_TAB = "263";
+  private static final String PURCHASE_INVOICE_HEADER_TAB = "290";
+  private static final String PAYMENT_IN_HEADER_TAB = "C4B6506838E14A349D6717D6856F1B56";
+  private static final String PAYMENT_OUT_HEADER_TAB = "F7A52FDAAA0346EFA07D53C125B40404";
+  private static final String TRANSACTION_HEADER_TAB = "FF8080812F213146012F2135BC25000E";
+  
+  private static final String SALES_INV_WINDOW = "167";
+  private static final String PURCHASE_INV_WINDOW = "183";
+  private static final String PAYMENT_IN_WINDOW = "E547CE89D4C04429B6340FFA44E70716";
+  private static final String PAYMENT_OUT_WINDOW = "6F8F913FA60F4CBD93DC1D3AA696E76E";
+  private static final String FINANCIAL_ACCOUNT_WINDOW = "94EAA455D2644E04AB25D93BE5157B6D";
+    
+  @Override
+  public List<String> execute() {
+    ConnectionProvider cp = getConnectionProvider();
+    ArrayList<String> errors = new ArrayList<String>();
+    try {
+      // Prevent error when upgrading from a pure 2.50
+      if (DuplicateDocExchangeRateData.existAPRMbasetables(cp)) {
+        
+        String errorMessage = "It is not allowed to have the multiple exchange rates defined for the same pair of currencies (Currency From -> Currency To) in the same %s. " +
+            "To fix this problem in your instance, you can know the duplicated entries by reviewing Alerts in your system. " + 
+            "Once you find the duplicated entries you should remove the wrong ones. After fixing all these entries you should be able to apply this MP.";
+        
+        // INVOICES
+        DuplicateDocExchangeRateData[] listOfDupInvoices = DuplicateDocExchangeRateData.selectDupInvoiceExcRate(cp);
+        if (listOfDupInvoices != null && listOfDupInvoices.length > 0) {
+          errors.add(String.format(errorMessage, "INVOICE"));
+        }
+        for (DuplicateDocExchangeRateData dupInv : listOfDupInvoices) {
+          processAlert(cp, dupInv, INV);
+        }
+
+        // PAYMENTS
+        DuplicateDocExchangeRateData[] listOfDupPayments = DuplicateDocExchangeRateData.selectDupPaymentExcRate(cp);
+        if (listOfDupPayments != null && listOfDupPayments.length > 0) {
+          errors.add(String.format(errorMessage, "PAYMENT"));
+        }
+        for (DuplicateDocExchangeRateData dupPay : listOfDupPayments) {
+          processAlert(cp, dupPay, PAY);
+        }
+        
+        // TRANSACTIONS
+        DuplicateDocExchangeRateData[] listOfDupTransactions = DuplicateDocExchangeRateData.selectDupTrxExcRate(cp);
+        if (listOfDupTransactions != null && listOfDupTransactions.length > 0) {
+          errors.add(String.format(errorMessage, "TRANSACTION"));
+        }
+        for (DuplicateDocExchangeRateData dupTrx : listOfDupTransactions) {
+          processAlert(cp, dupTrx, TRX);
+        }
+        
+      }
+    } catch (Exception e) {
+      return handleError(e);
+    }
+    return errors;
+  }
+  
+  private void processAlert(ConnectionProvider cp, DuplicateDocExchangeRateData exchangeRate, String type) throws Exception {
+    String ALERT_RULE_NAME = "Duplicated %s Exchange Rates";
+    String alertDescription = "Duplicated %s Exchange Rate. Please ensure just one entry exists per %s";
+    String strTabId = "";
+    String strWindowId = "";
+    String ALERT_RULE_SQL = "";
+    String strRecordId = "";
+    
+    if (INV.equals(type)) {
+      String invoice = "Y".equals(exchangeRate.issotrx) ? "Sales Invoice" : "Purchase Invoice";
+      ALERT_RULE_NAME = String.format(ALERT_RULE_NAME, invoice);
+      alertDescription = String.format(alertDescription, invoice, invoice);
+      strTabId = "Y".equals(exchangeRate.issotrx) ? SALES_INVOICE_HEADER_TAB : PURCHASE_INVOICE_HEADER_TAB;
+      strWindowId = "Y".equals(exchangeRate.issotrx) ? SALES_INV_WINDOW : PURCHASE_INV_WINDOW;
+      strRecordId = exchangeRate.referencekeyId;
+      
+      ALERT_RULE_SQL = " select crd.c_invoice_id as referencekey_id, ad_column_identifier('C_INVOICE', crd.c_invoice_id,'en_US') as record_id, " +
+          " 0 as ad_role_id, null as ad_user_id, '" + alertDescription + "' as description, " +
+          " 'Y' as isActive, crd.ad_org_id, crd.ad_client_id, now() as created, 0 as createdBy, now() as updated, 0 as updatedBy " +
+          " from c_conversion_rate_document crd join c_invoice i on (crd.c_invoice_id = i.c_invoice_id) " +
+          " where fin_payment_id is null and aprm_finacc_transaction_v_id is null and i.issotrx = '" + exchangeRate.issotrx + "'" +
+          " group by crd.c_currency_id, crd.c_currency_id_to, crd.c_invoice_id, crd.fin_payment_id, crd.aprm_finacc_transaction_v_id, " + 
+          "          crd.ad_org_id, crd.ad_client_id " +
+          " having count(*) > 1";
+      
+    } else if (PAY.equals(type)) {
+      String payment = "Y".equals(exchangeRate.isreceipt) ? "Payment IN" : "Payment OUT";
+      ALERT_RULE_NAME = String.format(ALERT_RULE_NAME, payment);
+      alertDescription = String.format(alertDescription, payment, payment);
+      strTabId = "Y".equals(exchangeRate.isreceipt) ? PAYMENT_IN_HEADER_TAB : PAYMENT_OUT_HEADER_TAB;
+      strWindowId = "Y".equals(exchangeRate.isreceipt) ? PAYMENT_IN_WINDOW : PAYMENT_OUT_WINDOW;
+      strRecordId = exchangeRate.referencekeyId;
+      
+      ALERT_RULE_SQL = " select crd.fin_payment_id as referencekey_id, ad_column_identifier('FIN_PAYMENT', crd.fin_payment_id,'en_US') as record_id, " +
+          " 0 as ad_role_id, null as ad_user_id, '" + alertDescription + "' as description, " +
+          " 'Y' as isActive, crd.ad_org_id, crd.ad_client_id, now() as created, 0 as createdBy, now() as updated, 0 as updatedBy " +
+          " from c_conversion_rate_document crd join fin_payment p on (crd.c_invoice_id = p.fin_payment_id) " +
+          " where c_invoice_id is null and aprm_finacc_transaction_v_id is null and p.isreceipt = '" + exchangeRate.isreceipt +"' " +
+          " group by crd.c_currency_id, crd.c_currency_id_to, crd.c_invoice_id, crd.fin_payment_id, crd.aprm_finacc_transaction_v_id, " +
+          "          crd.ad_org_id, crd.ad_client_id" +
+          " having count(*) > 1";
+      
+    } else if (TRX.equals(type)) {
+      ALERT_RULE_NAME = String.format(ALERT_RULE_NAME, "Transaction");
+      alertDescription = String.format(alertDescription, "Transaction", "Transaction");
+      strTabId = TRANSACTION_HEADER_TAB;
+      strWindowId = FINANCIAL_ACCOUNT_WINDOW;
+      strRecordId = exchangeRate.referencekeyId;
+      
+      ALERT_RULE_SQL = " select crd.aprm_finacc_transaction_v_id as referencekey_id, ad_column_identifier('FIN_FINACC_TRANSACTION', crd.aprm_finacc_transaction_v_id,'en_US') as record_id, " +
+          " 0 as ad_role_id, null as ad_user_id, '" + alertDescription + "' as description, " +
+          " 'Y' as isActive, crd.ad_org_id, crd.ad_client_id, now() as created, 0 as createdBy,  now() as updated, 0 as updatedBy " +
+          " from c_conversion_rate_document crd join fin_finacc_transaction ft on (crd.c_invoice_id = ft.fin_finacc_transaction_id) " + 
+          " where crd.c_invoice_id is null and crd.fin_payment_id is null " + 
+          " group by crd.c_currency_id, crd.c_currency_id_to, crd.c_invoice_id, crd.fin_payment_id, crd.aprm_finacc_transaction_v_id, " +
+          "          crd.ad_org_id, crd.ad_client_id " +
+          " having count(*) > 1";
+      
+    } else {
+      //invalid type
+    }
+    
+    String alertRuleId = "";
+
+    // Check if exists the alert rule
+    if (!DuplicateDocExchangeRateData.existsAlertRule(cp, ALERT_RULE_NAME, exchangeRate.adClientId)) {
+      DuplicateDocExchangeRateData.insertAlertRule(cp, exchangeRate.adClientId, exchangeRate.adOrgId,
+          ALERT_RULE_NAME, strTabId, ALERT_RULE_SQL);
+
+      alertRuleId = DuplicateDocExchangeRateData.getAlertRuleId(cp, ALERT_RULE_NAME,
+          exchangeRate.adClientId);
+      DuplicateDocExchangeRateData[] roles = DuplicateDocExchangeRateData.getRoleId(cp,
+          strWindowId, exchangeRate.adClientId);
+      for (DuplicateDocExchangeRateData role : roles) {
+        DuplicateDocExchangeRateData.insertAlertRecipient(cp, exchangeRate.adClientId,
+            exchangeRate.adOrgId, alertRuleId, role.adRoleId);
+      }
+    } else {
+      alertRuleId = DuplicateDocExchangeRateData.getAlertRuleId(cp, ALERT_RULE_NAME,
+          exchangeRate.adClientId);
+    }
+
+    // Check if exist the concrete alert
+    if (!DuplicateDocExchangeRateData.existsAlert(cp, alertRuleId, strRecordId)) {
+      DuplicateDocExchangeRateData.insertAlert(cp, exchangeRate.adClientId, alertDescription,
+          alertRuleId, exchangeRate.recordinfo, strRecordId);
+    }
+
+  }
+
+}
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicateDocExchangeRate_data.xsql	Wed May 09 12:42:17 2012 +0200
@@ -0,0 +1,185 @@
+<?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) 2012 Openbravo SLU
+ * All Rights Reserved.
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+-->
+
+
+<SqlClass name="DuplicateDocExchangeRateData" package="org.openbravo.buildvalidation">
+  <SqlMethod name="dummy" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+      SELECT '' AS documentno, '' AS referencekey_id, '' AS isreceipt, '' AS ad_client_id,
+             '' AS ad_org_id, '' AS ad_role_id, '' AS issotrx, '' AS recordinfo
+      FROM DUAL
+    ]]></Sql>
+  </SqlMethod>
+  <SqlMethod name="selectDupInvoiceExcRate" type="preparedStatement" return="multiple">
+      <SqlMethodComment></SqlMethodComment>
+      <Sql><![CDATA[
+             select crd.c_invoice_id as referencekey_id, i.issotrx, crd.ad_client_id, crd.ad_org_id,
+                    ad_column_identifier('C_INVOICE', crd.c_invoice_id,'en_US') as recordinfo
+             from c_conversion_rate_document crd join c_invoice i on (crd.c_invoice_id = i.c_invoice_id)
+             where crd.fin_payment_id is null and crd.aprm_finacc_transaction_v_id is null
+             group by crd.c_currency_id, crd.c_currency_id_to, crd.c_invoice_id, crd.fin_payment_id,
+                      crd.aprm_finacc_transaction_v_id, crd.ad_org_id, crd.ad_client_id, i.issotrx
+             having count(*) > 1
+      ]]></Sql>
+   </SqlMethod>
+   <SqlMethod name="selectDupPaymentExcRate" type="preparedStatement" return="multiple">
+      <SqlMethodComment></SqlMethodComment>
+      <Sql><![CDATA[
+             select crd.fin_payment_id as referencekey_id, p.isreceipt, crd.ad_client_id, crd.ad_org_id,
+                    ad_column_identifier('FIN_PAYMENT', crd.fin_payment_id,'en_US') as recordinfo
+             from c_conversion_rate_document crd join fin_payment p on (crd.fin_payment_id = p.fin_payment_id)
+             where crd.c_invoice_id is null and crd.aprm_finacc_transaction_v_id is null
+             group by crd.c_currency_id, crd.c_currency_id_to, crd.c_invoice_id, crd.fin_payment_id, crd.aprm_finacc_transaction_v_id,
+                      crd.ad_org_id, crd.ad_client_id, p.isreceipt
+             having count(*) > 1
+      ]]></Sql>
+   </SqlMethod>
+   <SqlMethod name="selectDupTrxExcRate" type="preparedStatement" return="multiple">
+      <SqlMethodComment></SqlMethodComment>
+      <Sql><![CDATA[
+             select crd.aprm_finacc_transaction_v_id as referencekey_id, crd.ad_client_id, crd.ad_org_id,
+                    ad_column_identifier('FIN_FINACC_TRANSACTION', crd.aprm_finacc_transaction_v_id,'en_US') as recordinfo
+             from c_conversion_rate_document crd join fin_finacc_transaction ft on (crd.aprm_finacc_transaction_v_id = ft.fin_finacc_transaction_id)
+             where crd.c_invoice_id is null and crd.fin_payment_id is null
+             group by crd.c_currency_id, crd.c_currency_id_to, crd.c_invoice_id, crd.fin_payment_id, crd.aprm_finacc_transaction_v_id,
+                      crd.ad_org_id, crd.ad_client_id
+             having count(*) > 1
+      ]]></Sql>
+   </SqlMethod>
+   <SqlMethod name="getAlertRuleId" type="preparedStatement" return="string">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+       SELECT MAX(ad_alertrule_id) AS name
+       FROM AD_ALERTRULE
+       WHERE NAME = ?
+         AND ISACTIVE = 'Y'
+         AND AD_CLIENT_ID = ?
+      ]]></Sql>
+    <Parameter name="name"/>
+    <Parameter name="client"/>
+  </SqlMethod>
+    <SqlMethod name="existsAlert" type="preparedStatement" return="boolean">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+       SELECT COUNT(*) AS EXISTING
+       FROM AD_ALERT
+       WHERE AD_ALERTRULE_ID = ?
+       AND REFERENCEKEY_ID = ?
+       AND ISFIXED = 'N'
+      ]]>
+    </Sql>
+    <Parameter name="alertRule"/>
+    <Parameter name="payment"/>
+  </SqlMethod>
+  <SqlMethod name="existsAlertRule" type="preparedStatement" return="boolean">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+       SELECT COUNT(*) AS EXISTING
+       FROM AD_ALERTRULE
+       WHERE NAME = ?
+         AND ISACTIVE = 'Y'
+         AND AD_CLIENT_ID = ?
+      ]]>
+    </Sql>
+    <Parameter name="alertRule"/>
+    <Parameter name="client"/>
+  </SqlMethod>
+  <SqlMethod name="insertAlertRule" type="preparedStatement" return="rowcount">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+      INSERT INTO AD_ALERTRULE (
+        AD_ALERTRULE_ID, AD_CLIENT_ID, AD_ORG_ID,ISACTIVE,
+        CREATED, CREATEDBY,  UPDATED, UPDATEDBY,
+        NAME, AD_TAB_ID, FILTERCLAUSE, TYPE,
+        SQL
+      ) VALUES (
+        get_uuid(), ?, ?, 'Y',
+        now(), '100', now(), '100',
+        ?, ?, '', 'D',
+        ?
+      )
+    ]]></Sql>
+    <Parameter name="clientId"/>
+    <Parameter name="orgId"/>
+    <Parameter name="name"/>
+    <Parameter name="tabId"/>
+    <Parameter name="sql"/>
+  </SqlMethod>
+  <SqlMethod name="insertAlert" type="preparedStatement" return="rowcount">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+      INSERT INTO AD_Alert (
+        AD_Alert_ID, AD_Client_ID, AD_Org_ID, IsActive,
+        Created, CreatedBy, Updated, UpdatedBy,
+        Description, AD_AlertRule_ID, Record_Id, Referencekey_ID
+      ) VALUES (
+        get_uuid(), ?, '0', 'Y',
+        NOW(), '0', NOW(), '0',
+        ?, ?, ?, ?)
+      ]]>
+    </Sql>
+    <Parameter name="client"/>
+    <Parameter name="description" />
+    <Parameter name="adAlertRuleId" />
+    <Parameter name="recordId" />
+    <Parameter name="referencekey_id" />
+  </SqlMethod>
+  <SqlMethod name="getRoleId" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+       SELECT distinct r.ad_role_id
+       FROM ad_window_access wa join ad_role r on (wa.ad_role_id=r.ad_role_id)
+       WHERE wa.ad_window_id = ?
+             AND wa.ad_client_id = ?
+             AND wa.isactive = 'Y'
+             AND r.isactive = 'Y'
+             AND r.ismanual = 'Y'
+      ]]></Sql>
+    <Parameter name="window"/>
+    <Parameter name="clientId"/>
+  </SqlMethod>
+  <SqlMethod name="insertAlertRecipient" type="preparedStatement" return="rowcount">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+    INSERT INTO ad_alertrecipient(
+            ad_user_id, ad_client_id, ad_org_id, isactive, created, createdby, 
+            updated, updatedby, ad_alertrecipient_id, ad_alertrule_id, ad_role_id, 
+            sendemail)
+    VALUES (null, ?, ?, 'Y', now(), '100', 
+            now(), '100', get_uuid(), ?, ?, 
+            'N')
+      ]]>
+    </Sql>
+    <Parameter name="client"/>
+    <Parameter name="org"/>
+    <Parameter name="adAlertRuleId" />
+    <Parameter name="role" />
+  </SqlMethod>
+  <SqlMethod name="existAPRMbasetables" type="preparedStatement" return="boolean">
+    <SqlMethodComment>Check if the FIN_Finacc_Transaction table exist</SqlMethodComment>
+    <Sql><![CDATA[
+       SELECT count(*) AS EXISTING
+       FROM ad_table
+       WHERE ad_table_id = '4D8C3B3C31D1410DA046140C9F024D17'
+      ]]>
+    </Sql>
+  </SqlMethod>
+</SqlClass>