Fixed issue 21640.Removed duplicated conversion.
authorGorka Ion Damián <gorkaion.damian@openbravo.com>
Tue, 18 Sep 2012 16:29:47 +0200
changeset 18042 c501ca2ff03f
parent 18041 d21ed23d426e
child 18043 957e0383b0af
Fixed issue 21640.Removed duplicated conversion.
src-util/modulescript/build/classes/org/openbravo/modulescript/Issue21640WrongMatchInvAccounting.class
src-util/modulescript/build/classes/org/openbravo/modulescript/Issue21640WrongMatchInvAccountingData.class
src-util/modulescript/src/org/openbravo/modulescript/Issue21640WrongMatchInvAccounting.java
src-util/modulescript/src/org/openbravo/modulescript/Issue21640WrongMatchInvAccounting_data.xsql
src/org/openbravo/erpCommon/ad_forms/DocMatchInv.java
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/Issue21640WrongMatchInvAccounting.class has changed
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/Issue21640WrongMatchInvAccountingData.class has changed
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-util/modulescript/src/org/openbravo/modulescript/Issue21640WrongMatchInvAccounting.java	Tue Sep 18 16:29:47 2012 +0200
@@ -0,0 +1,94 @@
+/*
+ *************************************************************************
+ * 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.modulescript;
+
+import javax.servlet.ServletException;
+
+import org.openbravo.database.ConnectionProvider;
+
+public class Issue21640WrongMatchInvAccounting extends ModuleScript {
+
+  final static private String ALERT_RULE_SQL1 = "SELECT DISTINCT ad_column_identifier('m_matchinv', fa.record_id, 'en_US') as record_id, fa.record_id as referencekey_id, 0 as ad_role_id, null as ad_user_id, 'This Matched Invoice need to be posted again due to wrong accounting.' as description, 'Y' as isactive, mi.ad_org_id, mi.ad_client_id,  now() as created, 0 as createdBy, now() as updated, 0 as updatedBy FROM fact_acct fa JOIN m_matchinv mi ON fa.record_id = mi.m_matchinv_id WHERE fa.ad_table_id = '472' GROUP BY fa.fact_acct_group_id, mi.m_matchinv_id, mi.ad_org_id, fa.record_id, mi.ad_client_id HAVING count(*) > 3";
+  final static private String ALERT_RULE_SQL2 = "SELECT DISTINCT ad_column_identifier('m_matchinv', fa.record_id, 'en_US') as record_id, fa.record_id as referencekey_id, 0 as ad_role_id, null as ad_user_id, 'This Matched Invoice need to be posted again due to wrong accounting.' as description, 'Y' as isactive, mi.ad_org_id, mi.ad_client_id,  now() as created, 0 as createdBy, now() as updated, 0 as updatedBy FROM fact_acct fa JOIN m_matchinv mi ON fa.record_id = mi.m_matchinv_id LEFT JOIN fact_acct far ON far.line_id = mi.m_inoutline_id AND far.account_id = fa.account_id LEFT JOIN fact_acct fi ON fi.line_id = mi.c_invoiceline_id AND fi.account_id = fa.account_id AND fi.ad_table_id = '318' WHERE fa.ad_table_id = '472' AND (COALESCE(far.amtacctcr + far.amtacctdr, fa.amtacctdr + fa.amtacctcr)  <> (fa.amtacctdr + fa.amtacctcr) OR COALESCE(fi.amtacctcr + fi.amtacctdr, fa.amtacctdr + fa.amtacctcr)  <> (fa.amtacctdr + fa.amtacctcr))";
+  final static private String ALERT_RULE1 = "Wrong Matched Invoice posting. Wrong account.";
+  final static private String ALERT_RULE2 = "Wrong Matched Invoice posting. Wrong currency conversion.";
+  final static private String ALERT_NAME = "This Matched Invoice need to be posted again due to wrong accounting.";
+  final static private String MATCHINV_WINDOW = "107";
+  final static private String MATCHINV_TAB = "408";
+
+  @Override
+  public void execute() {
+    try {
+      ConnectionProvider cp = getConnectionProvider();
+      for (Issue21640WrongMatchInvAccountingData client : Issue21640WrongMatchInvAccountingData
+          .getClients(cp)) {
+        if (!Issue21640WrongMatchInvAccountingData.existsAlertRule(cp, ALERT_RULE1,
+            client.adClientId)) {
+          createAlert1(cp, client.adClientId);
+        }
+        if (!Issue21640WrongMatchInvAccountingData.existsAlertRule(cp, ALERT_RULE2,
+            client.adClientId)) {
+          createAlert2(cp, client.adClientId);
+        }
+      }
+    } catch (Exception e) {
+      handleError(e);
+    }
+  }
+
+  private void createAlert1(ConnectionProvider cp, String clientId) throws ServletException {
+    Issue21640WrongMatchInvAccountingData.insertAlertRule(cp, clientId, ALERT_RULE1, MATCHINV_TAB,
+        ALERT_RULE_SQL1);
+    final String alertRuleId = Issue21640WrongMatchInvAccountingData.getAlertRuleId(cp,
+        ALERT_RULE1, clientId);
+    Issue21640WrongMatchInvAccountingData[] roles = Issue21640WrongMatchInvAccountingData
+        .getRoleIds(cp, MATCHINV_WINDOW, clientId);
+    for (Issue21640WrongMatchInvAccountingData role : roles) {
+      Issue21640WrongMatchInvAccountingData.insertAlertRecipient(cp, clientId, "0", alertRuleId,
+          role.adRoleId);
+    }
+    for (Issue21640WrongMatchInvAccountingData matchInv : Issue21640WrongMatchInvAccountingData
+        .select1(cp, clientId)) {
+      if (!Issue21640WrongMatchInvAccountingData.existsAlert(cp, alertRuleId, matchInv.mMatchinvId)) {
+        Issue21640WrongMatchInvAccountingData.insertAlert(cp, clientId, matchInv.adOrgId,
+            ALERT_NAME, alertRuleId, matchInv.matchinv, matchInv.mMatchinvId);
+      }
+    }
+  }
+
+  private void createAlert2(ConnectionProvider cp, String clientId) throws ServletException {
+    Issue21640WrongMatchInvAccountingData.insertAlertRule(cp, clientId, ALERT_RULE2, MATCHINV_TAB,
+        ALERT_RULE_SQL2);
+    final String alertRuleId = Issue21640WrongMatchInvAccountingData.getAlertRuleId(cp,
+        ALERT_RULE2, clientId);
+    Issue21640WrongMatchInvAccountingData[] roles = Issue21640WrongMatchInvAccountingData
+        .getRoleIds(cp, MATCHINV_WINDOW, clientId);
+    for (Issue21640WrongMatchInvAccountingData role : roles) {
+      Issue21640WrongMatchInvAccountingData.insertAlertRecipient(cp, clientId, "0", alertRuleId,
+          role.adRoleId);
+    }
+    for (Issue21640WrongMatchInvAccountingData matchInv : Issue21640WrongMatchInvAccountingData
+        .select2(cp, clientId)) {
+      if (!Issue21640WrongMatchInvAccountingData.existsAlert(cp, alertRuleId, matchInv.mMatchinvId)) {
+        Issue21640WrongMatchInvAccountingData.insertAlert(cp, clientId, matchInv.adOrgId,
+            ALERT_NAME, alertRuleId, matchInv.matchinv, matchInv.mMatchinvId);
+      }
+    }
+  }
+}
\ No newline at end of file
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-util/modulescript/src/org/openbravo/modulescript/Issue21640WrongMatchInvAccounting_data.xsql	Tue Sep 18 16:29:47 2012 +0200
@@ -0,0 +1,178 @@
+<?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="Issue21640WrongMatchInvAccountingData" package="org.openbravo.modulescript">
+  <SqlClassComment></SqlClassComment>
+  <SqlMethod name="select" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+      SELECT '' AS m_matchinv_id, '' AS ad_client_id, '' AS ad_org_id, '' as matchinv, '' as ad_role_id
+      FROM DUAL
+      ]]>
+    </Sql>
+  </SqlMethod>
+  <SqlMethod name="select2" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+      SELECT DISTINCT mi.m_matchinv_id, mi.ad_org_id, ad_column_identifier('m_matchinv', fa.record_id, 'en_US') as matchinv
+      FROM fact_acct fa JOIN m_matchinv mi ON fa.record_id = mi.m_matchinv_id
+      WHERE fa.ad_table_id = '472'
+        AND fa.ad_client_id = ?
+      GROUP BY fa.fact_acct_group_id, mi.m_matchinv_id, mi.ad_org_id, fa.record_id
+      HAVING count(*) > 3
+      ]]>
+    </Sql>
+    <Parameter name="client"/>
+  </SqlMethod>
+  <SqlMethod name="select1" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+      SELECT DISTINCT mi.m_matchinv_id, mi.ad_org_id, ad_column_identifier('m_matchinv', fa.record_id, 'en_US') as matchinv
+      FROM fact_acct fa
+          JOIN m_matchinv mi ON fa.record_id = mi.m_matchinv_id
+          JOIN m_inoutline iol ON mi.m_inoutline_id = iol.m_inoutline_id
+          JOIN c_invoiceline il ON mi.c_invoiceline_id = il.c_invoiceline_id
+          LEFT JOIN fact_acct far ON far.line_id = iol.m_inoutline_id AND far.account_id = fa.account_id
+          LEFT JOIN fact_acct fi ON fi.line_id = il.c_invoiceline_id AND fi.account_id = fa.account_id AND fi.ad_table_id = '318'
+      WHERE fa.ad_table_id = '472'
+        AND (COALESCE(far.amtacctcr + far.amtacctdr, fa.amtacctdr + fa.amtacctcr) <> (fa.amtacctdr + fa.amtacctcr)
+            OR COALESCE(fi.amtacctcr + fi.amtacctdr, fa.amtacctdr + fa.amtacctcr) <> (fa.amtacctdr + fa.amtacctcr))
+        AND fa.ad_client_id = ?
+      ]]>
+    </Sql>
+    <Parameter name="client"/>
+  </SqlMethod>
+  <SqlMethod name="getClients" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+      SELECT DISTINCT ad_client_id
+      FROM c_acctschema_table
+      WHERE ad_table_id = '472'
+        AND isactive = 'Y'
+      ]]>
+    </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 LIKE ?
+         AND ISACTIVE = 'Y'
+         AND AD_CLIENT_ID = ?
+      ]]></Sql>
+    <Parameter name="name"/>
+    <Parameter name="client"/>
+  </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="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="matchinv"/>
+  </SqlMethod>
+  <SqlMethod name="getRoleIds" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+       SELECT distinct ad_role_id
+       FROM ad_window_access
+       WHERE ad_window_id = ?
+       AND AD_CLIENT_ID = ?
+         AND ISACTIVE = 'Y'
+      ]]></Sql>
+    <Parameter name="window"/>
+    <Parameter name="clientId"/>
+  </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(), ?, '0', 'Y',
+        now(), '100', now(), '100',
+        ?, ?, '', 'D',
+        ?
+      )
+    ]]></Sql>
+    <Parameter name="clientId"/>
+    <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(), ?, ?, 'Y',
+        NOW(), '0', NOW(), '0',
+        ?, ?, ?, ?)
+      ]]>
+    </Sql>
+    <Parameter name="client"/>
+    <Parameter name="org"/>
+    <Parameter name="description" />
+    <Parameter name="adAlertRuleId" />
+    <Parameter name="recordId" />
+    <Parameter name="referencekey_id" />
+  </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>
+</SqlClass>
--- a/src/org/openbravo/erpCommon/ad_forms/DocMatchInv.java	Mon Sep 17 10:22:29 2012 +0200
+++ b/src/org/openbravo/erpCommon/ad_forms/DocMatchInv.java	Tue Sep 18 16:29:47 2012 +0200
@@ -58,8 +58,8 @@
     super(AD_Client_ID, AD_Org_ID, connectionProvider);
   }
 
-  public void loadObjectFieldProvider(ConnectionProvider conn, @SuppressWarnings("hiding")
-  String AD_Client_ID, String Id) throws ServletException {
+  public void loadObjectFieldProvider(ConnectionProvider conn,
+      @SuppressWarnings("hiding") String AD_Client_ID, String Id) throws ServletException {
     setObjectFieldProvider(DocMatchInvData.selectRegistro(conn, AD_Client_ID, Id));
   }
 
@@ -182,7 +182,6 @@
     // Expenses......................................................... Expenses in the Invoice
     // Invoice Price Variance........ Difference of cost and expenses
 
-    boolean changeSign = false;
     FieldProvider[] data = getObjectFieldProvider();
     MaterialTransaction transaction = getTransaction(Record_ID);
     Currency costCurrency = FinancialUtils.getLegalEntityCurrency(OBDal.getInstance().get(
@@ -210,25 +209,15 @@
 
     DocMatchInvData[] invoiceData = DocMatchInvData.selectInvoiceData(conn, vars.getClient(),
         data[0].getField("C_InvoiceLine_Id"));
-    String costCurrencyId = as.getC_Currency_ID();
-    OBContext.setAdminMode(false);
-    try {
-      costCurrencyId = OBDal.getInstance().get(Client.class, AD_Client_ID).getCurrency().getId();
-    } finally {
-      OBContext.restorePreviousMode();
-    }
 
     String strExpenses = invoiceData[0].linenetamt;
     String strInvoiceCurrency = invoiceData[0].cCurrencyId;
     String strDate = invoiceData[0].dateacct;
     String strReceiptDate = data[0].getField("ORDERDATEACCT");
-    strExpenses = getConvertedAmt(strExpenses, strInvoiceCurrency, costCurrencyId, strDate, "",
-        vars.getClient(), vars.getOrg(), conn);
     BigDecimal bdExpenses = new BigDecimal(strExpenses);
     if ((new BigDecimal(data[0].getField("QTYINVOICED")).signum() != (new BigDecimal(
         data[0].getField("MOVEMENTQTY"))).signum())
         && data[0].getField("InOutStatus").equals("VO")) {
-      changeSign = true;
       bdExpenses = bdExpenses.multiply(new BigDecimal(-1));
     }
 
@@ -244,10 +233,10 @@
     }
 
     dr = fact.createLine(docLine, getAccount(AcctServer.ACCTTYPE_NotInvoicedReceipts, as, conn),
-        costCurrency.getId(), bdCost.toString(), Fact_Acct_Group_ID, nextSeqNo(SeqNo),
-        DocumentType, conn);
+        as.m_C_Currency_ID, bdCost.toString(), Fact_Acct_Group_ID, nextSeqNo(SeqNo), DocumentType,
+        conn);
     bdExpenses = new BigDecimal(getConvertedAmt(bdExpenses.toString(), strInvoiceCurrency,
-        costCurrency.getId(), strDate, "", vars.getClient(), vars.getOrg(), conn));
+        as.m_C_Currency_ID, strDate, "", vars.getClient(), vars.getOrg(), conn));
     BigDecimal bdDifference = bdExpenses.subtract(bdCost);
 
     if (dr == null) {
@@ -256,38 +245,28 @@
       return null;
     }
     ProductInfo p = new ProductInfo(data[0].getField("M_Product_Id"), conn);
-    for (DocLine docLineInvoice : p_lines) {
-      String strAmount = "";
-      if (strInvoiceCurrency != costCurrencyId) {
-        strAmount = getConvertedAmt((changeSign) ? new BigDecimal(docLineInvoice.getAmount())
-            .multiply(new BigDecimal(-1)).toString() : docLineInvoice.getAmount(),
-            strInvoiceCurrency, costCurrencyId, strDate, "", vars.getClient(), vars.getOrg(), conn);
-      } else {
-        strAmount = (changeSign) ? new BigDecimal(docLineInvoice.getAmount()).multiply(
-            new BigDecimal(-1)).toString() : docLineInvoice.getAmount();
+
+    cr = fact.createLine(p_lines[0], p.getAccount(ProductInfo.ACCTTYPE_P_Expense, as, conn),
+        as.m_C_Currency_ID, "0", bdExpenses.toString(), Fact_Acct_Group_ID, nextSeqNo(SeqNo),
+        DocumentType, conn);
+    if (cr == null && ZERO.compareTo(bdExpenses) != 0) {
+      log4j.warn("createFact - unable to calculate line with "
+          + " expenses to product expenses account.");
+      return null;
+    }
+    // Set Locations
+    FactLine[] fLines = fact.getLines();
+    for (int i = 0; fLines != null && i < fLines.length; i++) {
+      if (fLines[i] != null) {
+        fLines[i].setLocationFromBPartner(C_BPartner_Location_ID, true, conn); // from Loc
+        fLines[i].setLocationFromOrg(fLines[i].getAD_Org_ID(conn), false, conn); // to Loc
       }
-
-      cr = fact.createLine(docLineInvoice, p.getAccount(ProductInfo.ACCTTYPE_P_Expense, as, conn),
-          costCurrencyId, "0", strAmount, Fact_Acct_Group_ID, nextSeqNo(SeqNo), DocumentType, conn);
-      if (cr == null && ZERO.compareTo(new BigDecimal(strAmount)) != 0) {
-        log4j.warn("createFact - unable to calculate line with "
-            + " expenses to product expenses account.");
-        return null;
-      }
-      // Set Locations
-      FactLine[] fLines = fact.getLines();
-      for (int i = 0; fLines != null && i < fLines.length; i++) {
-        if (fLines[i] != null) {
-          fLines[i].setLocationFromBPartner(C_BPartner_Location_ID, true, conn); // from Loc
-          fLines[i].setLocationFromOrg(fLines[i].getAD_Org_ID(conn), false, conn); // to Loc
-        }
-      }
-      updateProductInfo(as.getC_AcctSchema_ID(), conn, con); // only API
     }
+    updateProductInfo(as.getC_AcctSchema_ID(), conn, con); // only API
 
     if (bdCost.compareTo(bdExpenses) != 0) {
       diff = fact.createLine(docLine, p.getAccount(ProductInfo.ACCTTYPE_P_IPV, as, conn),
-          costCurrency.getId(), (bdDifference.compareTo(BigDecimal.ZERO) == 1) ? bdDifference.abs()
+          as.m_C_Currency_ID, (bdDifference.compareTo(BigDecimal.ZERO) == 1) ? bdDifference.abs()
               .toString() : "0", (bdDifference.compareTo(BigDecimal.ZERO) < 1) ? bdDifference.abs()
               .toString() : "0", Fact_Acct_Group_ID, nextSeqNo(SeqNo), DocumentType, conn);
       if (diff == null) {