0012587: wrong price in process "copy lines" of invoice

diff -r 6a836f38131f src/org/openbravo/erpCommon/ad_actionButton/CopyFromInvoice.java
--- a/src/org/openbravo/erpCommon/ad_actionButton/CopyFromInvoice.java	Wed Oct 28 10:19:13 2009 +0100
+++ b/src/org/openbravo/erpCommon/ad_actionButton/CopyFromInvoice.java	Tue Mar 09 09:10:12 2010 +0100
@@ -20,6 +20,7 @@
 
 import java.io.IOException;
 import java.io.PrintWriter;
+import java.math.BigDecimal;
 import java.sql.Connection;
 
 import javax.servlet.ServletConfig;
@@ -29,13 +30,15 @@
 
 import org.openbravo.base.secureApp.HttpSecureAppServlet;
 import org.openbravo.base.secureApp.VariablesSecureApp;
+import org.openbravo.erpCommon.businessUtility.Tax;
+import org.openbravo.erpCommon.utility.DateTimeData;
 import org.openbravo.erpCommon.utility.OBError;
-import org.openbravo.erpCommon.utility.SequenceIdData;
 import org.openbravo.erpCommon.utility.Utility;
 import org.openbravo.xmlEngine.XmlDocument;
 
 public class CopyFromInvoice extends HttpSecureAppServlet {
   private static final long serialVersionUID = 1L;
+  private static final BigDecimal ZERO = BigDecimal.ZERO;
 
   public void init(ServletConfig config) {
     super.init(config);
@@ -76,20 +79,92 @@
     Connection conn = null;
     try {
       conn = getTransactionConnection();
-      CopyFromInvoiceData[] data = CopyFromInvoiceData.select(conn, this, strInvoice, Utility
+      CopyFromInvoiceData[] originInvLines = CopyFromInvoiceData.select(this, strInvoice, Utility
           .getContext(this, vars, "#User_Client", windowId), Utility.getContext(this, vars,
           "#User_Org", windowId));
-      if (data != null && data.length != 0) {
-        for (i = 0; i < data.length; i++) {
-          String strSequence = SequenceIdData.getUUID();
-          try {
-            CopyFromInvoiceData.insert(conn, this, strSequence, strKey, vars.getClient(), vars
-                .getOrg(), vars.getUser(), data[i].cInvoicelineId);
-          } catch (ServletException ex) {
-            myError = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage());
-            releaseRollbackConnection(conn);
+      CopyFromInvoiceData[] currentInv = CopyFromInvoiceData.selectInvoice(this, strKey);
+      for (i = 0; i < originInvLines.length; i++) {
+        String strDateInvoiced = "";
+        String strInvPriceList = "";
+        String strBPartnerId = "";
+        String strPricePrecision = "0";
+        String strmProductId = "";
+        String strQty = "";
+        String priceactual = "";
+        String pricestd = "";
+        String pricelist = "";
+        String pricelimit = "";
+        String linenetamt = "";
+        strDateInvoiced = currentInv[0].dateinvoiced;
+        strInvPriceList = currentInv[0].mPricelistId;
+        strBPartnerId = currentInv[0].mPricelistId;
+        strmProductId = originInvLines[i].mProductId;
+        strQty = originInvLines[i].qtyinvoiced;
+        String strWharehouse = Utility.getContext(this, vars, "#M_Warehouse_ID", windowId);
+
+        // Select tax
+        String strCTaxID = Tax.get(this, originInvLines[i].mProductId, currentInv[0].dateinvoiced,
+            currentInv[0].adOrgId, strWharehouse.equals("") ? vars.getWarehouse() : strWharehouse,
+            currentInv[0].cBpartnerLocationId, currentInv[0].cBpartnerLocationId,
+            currentInv[0].cProjectId, currentInv[0].issotrx.equals("Y") ? true : false);
+        if (strCTaxID.equals("")) {
+          myError = Utility.translateError(this, vars, vars.getLanguage(), Utility.messageBD(this,
+              "TaxNotFound", vars.getLanguage()));
+          return myError;
+        }
+
+        // Select prices
+        CopyFromInvoiceData[] invoicelineprice = CopyFromInvoiceData.selectPriceForProduct(this,
+            strmProductId, strInvPriceList);
+        for (int j = 0; invoicelineprice != null && j < invoicelineprice.length; j++) {
+          if (invoicelineprice[j].validfrom == null
+              || invoicelineprice[j].validfrom.equals("")
+              || !DateTimeData.compare(this, DateTimeData.today(this),
+                  invoicelineprice[j].validfrom).equals("-1")) {
+            pricestd = invoicelineprice[j].pricestd;
+            pricelist = invoicelineprice[j].pricelist;
+            pricelimit = invoicelineprice[j].pricelimit;
+            CopyFromInvoiceData[] invoicePriceList = CopyFromInvoiceData.selectInvoicePricelist(
+                this, strKey);
+            if (invoicePriceList != null && invoicePriceList.length > 0) {
+              strPricePrecision = invoicePriceList[0].priceprecision.equals("") ? "0"
+                  : invoicePriceList[0].priceprecision;
+            }
+            int PricePrecision = Integer.valueOf(strPricePrecision).intValue();
+
+            BigDecimal priceStd, priceActual, qtyInvoiced, lineNetAmt;
+
+            priceStd = (pricestd.equals("") ? ZERO : (new BigDecimal(pricestd))).setScale(
+                PricePrecision, BigDecimal.ROUND_HALF_UP);
+            qtyInvoiced = (originInvLines[i].qtyinvoiced.equals("") ? ZERO : new BigDecimal(
+                originInvLines[i].qtyinvoiced));
+            // Calculate price adjustments (offers)
+            priceActual = new BigDecimal(CopyFromInvoiceData.getOffersStdPrice(this, strBPartnerId,
+                pricestd, strmProductId, strDateInvoiced, strQty, strInvPriceList, strKey));
+            if (priceActual.scale() > PricePrecision)
+              priceActual = priceActual.setScale(PricePrecision, BigDecimal.ROUND_HALF_UP);
+            // Calculate line net amount
+            lineNetAmt = qtyInvoiced.multiply(priceActual);
+            if (lineNetAmt.scale() > PricePrecision)
+              lineNetAmt = lineNetAmt.setScale(PricePrecision, BigDecimal.ROUND_HALF_UP);
+            pricestd = priceStd.toString();
+            priceactual = priceActual.toString();
+            linenetamt = lineNetAmt.toString();
           }
         }
+        if (pricestd.equals(""))
+          pricestd = "0";
+        if (pricelist.equals(""))
+          pricelist = "0";
+        if (pricelimit.equals(""))
+          pricelimit = "0";
+        if (priceactual.equals(""))
+          priceactual = "0";
+        CopyFromInvoiceData.insertLines(conn, this, strKey, currentInv[0].adClientId,
+            currentInv[0].adOrgId, vars.getUser(), originInvLines[i].description,
+            originInvLines[i].mProductId, originInvLines[i].qtyinvoiced, pricestd, pricelist,
+            priceactual, pricelimit, originInvLines[i].cUomId, strCTaxID, linenetamt,
+            originInvLines[i].cInvoicelineId);
       }
       releaseCommitConnection(conn);
     } catch (Exception e) {
diff -r 6a836f38131f src/org/openbravo/erpCommon/ad_actionButton/CopyFromInvoice_data.xsql
--- a/src/org/openbravo/erpCommon/ad_actionButton/CopyFromInvoice_data.xsql	Wed Oct 28 10:19:13 2009 +0100
+++ b/src/org/openbravo/erpCommon/ad_actionButton/CopyFromInvoice_data.xsql	Tue Mar 09 09:10:12 2010 +0100
@@ -18,26 +18,94 @@
  ************************************************************************
 -->
 
-
-
-
-
 <SqlClass name="CopyFromInvoiceData" package="org.openbravo.erpCommon.ad_actionButton">
-  <SqlMethod name="select" type="preparedStatement" connection="true" return="multiple">
+  <SqlMethod name="select" type="preparedStatement" return="multiple">
     <SqlMethodComment></SqlMethodComment>
     <Sql>
       <![CDATA[
-        SELECT C_InvoiceLine_ID FROM C_InvoiceLine 
-        WHERE C_Invoice_ID = ? 
+        SELECT C_INVOICELINE_ID, C_UOM_ID, M_PRODUCT_ID, QTYINVOICED, DESCRIPTION,
+        '' AS C_BPARTNER_ID, '' AS C_BPARTNER_LOCATION_ID, '' AS C_CURRENCY_ID, '' AS C_PROJECT_ID,
+        '' AS M_PRICELIST_ID, '' AS LINE, '' AS AD_ORG_ID, '' AS AD_CLIENT_ID, '' AS DATEINVOICED,
+        '' AS ISSOTRX, '' AS TAXDATE,
+        '' AS PRICESTD, '' AS PRICELIST, '' AS PRICELIMIT, '' AS VALIDFROM,
+        '' AS StdPrecision, '' AS PricePrecision, '' AS EnforcePriceLimit
+        FROM C_INVOICELINE
+        WHERE C_INVOICE_ID = ?
         AND AD_CLIENT_ID IN ('1') 
         AND AD_ORG_ID IN ('1') 
         AND ISACTIVE = 'Y'
+        ORDER BY LINE ASC
       ]]>
     </Sql>
     <Parameter name="cInvoiceId"/>
     <Parameter name="userclient" optional="true" type="replace" after="AND AD_CLIENT_ID IN (" text="'1'"/>
     <Parameter name="userorg" optional="true" type="replace" after="AND AD_ORG_ID IN (" text="'1'"/>
   </SqlMethod>
+  <SqlMethod name="selectInvoice" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+        SELECT I.C_BPARTNER_ID, I.C_BPARTNER_LOCATION_ID, I.C_CURRENCY_ID, I.C_PROJECT_ID,
+        I.M_PRICELIST_ID, COALESCE(MAX(IL.LINE),0) AS LINE, I.AD_ORG_ID, I.AD_CLIENT_ID, I.DATEINVOICED,
+        I.ISSOTRX AS ISSOTRX, I.TAXDATE
+        FROM C_INVOICE I LEFT JOIN C_INVOICELINE IL ON I.C_INVOICE_ID = IL.C_INVOICE_ID
+        WHERE I.C_INVOICE_ID = ?
+        GROUP BY I.C_BPARTNER_ID, I.C_BPARTNER_LOCATION_ID, I.C_CURRENCY_ID, I.C_PROJECT_ID,
+        I.M_PRICELIST_ID, I.AD_ORG_ID, I.AD_CLIENT_ID, I.DATEINVOICED, I.ISSOTRX, I.TAXDATE
+      ]]>
+    </Sql>
+    <Parameter name="cInvoiceId"/>
+  </SqlMethod>
+  <SqlMethod name="selectPriceForProduct" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+        SELECT M_BOM_PriceStd(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceStd,
+        M_BOM_PriceList(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceList,
+        M_BOM_PriceLimit(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceLimit,
+        p.C_UOM_ID,pv.ValidFrom,pl.C_Currency_ID 
+        FROM M_Product p, M_ProductPrice pp, M_Pricelist pl, M_PriceList_Version pv 
+        WHERE p.M_Product_ID=pp.M_Product_ID
+        AND pp.M_PriceList_Version_ID=pv.M_PriceList_Version_ID
+        AND pv.M_PriceList_ID=pl.M_PriceList_ID
+        AND pv.IsActive='Y'
+        AND p.M_Product_ID = ?
+        AND pl.M_PriceList_ID = ?
+        ORDER BY pv.ValidFrom DESC
+      ]]>
+    </Sql>
+    <Parameter name="MProductID"/>
+    <Parameter name="MPriceListID"/>
+  </SqlMethod>
+  <SqlMethod name="selectInvoicePricelist" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      SELECT C_Currency.StdPrecision, C_Currency.PricePrecision, M_PriceList.EnforcePriceLimit, M_PriceList.M_PriceList_ID 
+      FROM C_Invoice, M_PriceList, C_Currency 
+      WHERE C_Invoice.M_PriceList_ID = M_PriceList.M_PriceList_ID
+      AND M_PriceList.C_Currency_ID = C_Currency.C_Currency_ID
+      AND C_Invoice.C_Invoice_ID = ?
+    </Sql>
+    <Parameter name="cInvoiceId"/>
+  </SqlMethod>
+  <SqlMethod name="getOffersStdPrice" type="preparedStatement" return="String" default="0">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      SELECT ROUND(M_GET_OFFERS_STD_PRICE(?,TO_NUMBER(?),?,TO_DATE(?), TO_NUMBER(?), ?),
+                    (SELECT PRICEPRECISION 
+                       FROM C_CURRENCY C,
+                            C_INVOICE    I 
+                      WHERE C.C_CURRENCY_ID = I.C_CURRENCY_ID
+                        AND I.C_INVOICE_ID = ?)) as TOTAL FROM DUAL
+     </Sql>
+    <Parameter name="cBpartnerId"/>
+    <Parameter name="pricestd"/>
+    <Parameter name="mProductId"/>
+    <Parameter name="dateinvoiced"/>
+    <Parameter name="qty"/>
+    <Parameter name="pricelist"/>
+    <Parameter name="invoiceid"/>
+  </SqlMethod>
   <SqlMethod name="insert" type="preparedStatement" connection="true" return="rowCount">
     <SqlMethodComment></SqlMethodComment>
     <Sql>
@@ -65,4 +133,40 @@
     <Parameter name="cInvoicelineId"/>
     <Parameter name="cInvoicelineId"/>
   </SqlMethod>
+  <SqlMethod name="insertLines" type="preparedStatement" connection="true" return="rowCount">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+        INSERT INTO C_INVOICELINE (C_InvoiceLine_ID, C_Invoice_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, 
+        CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
+        LINE, DESCRIPTION, M_PRODUCT_ID, 
+        QTYINVOICED, PRICESTD, PRICELIST, PRICEACTUAL, PRICELIMIT, C_UOM_ID, C_TAX_ID, LINENETAMT,
+        S_RESOURCEASSIGNMENT_ID, M_ATTRIBUTESETINSTANCE_ID, ISDESCRIPTION, QUANTITYORDER, M_PRODUCT_UOM_ID)
+        SELECT get_uuid(), ?, ?, ?, 'Y', 
+        now(), ?, now(), ?,
+        (SELECT COALESCE(MAX(LINE),0)+10 FROM C_InvoiceLine WHERE C_Invoice_ID = ?), ?, ?, 
+        ?, ?, ?, ?, ?, ?, ?, ?,
+        S_RESOURCEASSIGNMENT_ID, M_ATTRIBUTESETINSTANCE_ID, ISDESCRIPTION, QUANTITYORDER, M_PRODUCT_UOM_ID
+        FROM C_INVOICELINE 
+        WHERE C_INVOICELINE_ID = ? 
+      ]]>
+    </Sql>
+    <Parameter name="cInvoiceId"/>
+    <Parameter name="adClientId"/>
+    <Parameter name="adOrgId"/>
+    <Parameter name="adUserId"/>
+    <Parameter name="adUserId"/>
+    <Parameter name="cInvoiceId"/>
+    <Parameter name="description"/>
+    <Parameter name="mProductId"/>
+    <Parameter name="qty"/>
+    <Parameter name="pricestd"/>
+    <Parameter name="pricelist"/>
+    <Parameter name="priceactual"/>
+    <Parameter name="pricelimit"/>
+    <Parameter name="uomId"/>
+    <Parameter name="taxId"/>
+    <Parameter name="linenetamt"/>
+    <Parameter name="cInvoicelineId"/>
+  </SqlMethod>
 </SqlClass>