0035216: during cashup, it is possible duplicate invoice lines when one orderline generates two or more invoice lines

diff --git a/src/org/openbravo/retail/posterminal/OrderGroupingProcessor_data.xsql b/src/org/openbravo/retail/posterminal/OrderGroupingProcessor_data.xsql
--- a/src/org/openbravo/retail/posterminal/OrderGroupingProcessor_data.xsql
+++ b/src/org/openbravo/retail/posterminal/OrderGroupingProcessor_data.xsql
@@ -18,6 +18,7 @@
         from c_orderline ol, c_order o
         where ol.c_order_id = o.c_order_id
         and (select count(1) from m_inoutline il where il.c_orderline_id = ol.c_orderline_id) > 1
+        and ol.qtyordered <> ol.qtyinvoiced
         and o.em_obpos_app_cashup_id = ?
     ]]></Sql>
     <Parameter name="cashupId"/>

# HG changeset patch
# User Ranjith S R <ranjith@qualiantech.com>
# Date 1488259674 -19800
#      Tue Feb 28 10:57:54 2017 +0530
# Node ID 63c9cf8589db91d6190bca4c30fa2690f2910557
# Parent  3d4325370ae99bb3d8270743dd770aa0193f62a1
Related to issue 35216 : Invoice Line should not be created again, if it is already created in OrderLoader

* If Invoice Line are already created in OrderLoader, then while doing cashup, Invoice line should not be created/splited again

diff -r 3d4325370ae9 -r 63c9cf8589db src/org/openbravo/retail/posterminal/OrderGroupingProcessor.java
--- a/src/org/openbravo/retail/posterminal/OrderGroupingProcessor.java	Tue Feb 28 10:50:28 2017 +0530
+++ b/src/org/openbravo/retail/posterminal/OrderGroupingProcessor.java	Tue Feb 28 10:57:54 2017 +0530
@@ -128,7 +128,7 @@
 
     // check if there are orderlines splitted by inoutlines
     OrderGroupingProcessorData[] orderLinesToSplit = OrderGroupingProcessorData
-        .selectSplitOrderLines(conn, cashUpId);
+        .selectSplitOrderLines(conn, cashUpId, strCurrentDate);
 
     for (OrderGroupingProcessorData orderLineToSplit : orderLinesToSplit) {
       OrderLine orderLine = OBDal.getInstance().get(OrderLine.class, orderLineToSplit.cOrderlineId);
diff -r 3d4325370ae9 -r 63c9cf8589db src/org/openbravo/retail/posterminal/OrderGroupingProcessor_data.xsql
--- a/src/org/openbravo/retail/posterminal/OrderGroupingProcessor_data.xsql	Tue Feb 28 10:50:28 2017 +0530
+++ b/src/org/openbravo/retail/posterminal/OrderGroupingProcessor_data.xsql	Tue Feb 28 10:57:54 2017 +0530
@@ -14,13 +14,16 @@
     <SqlMethodComment>select orderlines with multiple shipmentlines</SqlMethodComment>
     <Sql>
     <![CDATA[
-        select ol.c_orderline_id, o.c_order_id, '' as c_invoice_id
-        from c_orderline ol, c_order o
-        where ol.c_order_id = o.c_order_id
-        and (select count(1) from m_inoutline il where il.c_orderline_id = ol.c_orderline_id) > 1
+        select ol.c_orderline_id, o.c_order_id, i.c_invoice_id
+        from c_orderline ol
+        join c_order o on ol.c_order_id = o.c_order_id
+        left join c_invoice i on i.c_order_id = o.c_order_id
+        where (select count(1) from m_inoutline il where il.c_orderline_id = ol.c_orderline_id) > 1
         and o.em_obpos_app_cashup_id = ?
+        and (case when (i.c_invoice_id is not null and i.dateinvoiced != to_timestamp(to_char(?), to_char('DD-MM-YYYY HH24:MI:SS'))) then 0 else 1 end) = 1
     ]]></Sql>
     <Parameter name="cashupId"/>
+    <Parameter name="currentDate"/>
   </SqlMethod>
   <SqlMethod name="insertHeaderGrouping" type="preparedStatement" return="rowcount">
     <SqlMethodComment>Insert invoice headers grouping orders</SqlMethodComment>

# HG changeset patch
# User Ranjith S R <ranjith@qualiantech.com>
# Date 1488259964 -19800
#      Tue Feb 28 11:02:44 2017 +0530
# Node ID 05e21a88937e06b11b1be3fdef21967f20ce47fb
# Parent  82ce171b557ae731017375c4fe5924ec7f085f14
Verifies issue 35216 : Added automated test 'I35216_VerifyCashUpSplitLines'

diff -r 82ce171b557a -r 05e21a88937e src-test/org/openbravo/test/mobile/retail/pack/selenium/tests/cashup/I35216_VerifyCashUpSplitLines.java
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-test/org/openbravo/test/mobile/retail/pack/selenium/tests/cashup/I35216_VerifyCashUpSplitLines.java	Tue Feb 28 11:02:44 2017 +0530
@@ -0,0 +1,207 @@
+/*
+ *************************************************************************
+ * 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 S.L.U.
+ * All portions are Copyright (C) 2015 Openbravo S.L.U.
+ * All Rights Reserved.
+ * Contributor(s):
+ ************************************************************************
+ *
+ * @author MAL
+ *
+ */
+
+package org.openbravo.test.mobile.retail.pack.selenium.tests.cashup;
+
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.equalTo;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.Collection;
+
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized.Parameters;
+import org.openbravo.test.mobile.core.junit.RetryRunnerParameterized;
+import org.openbravo.test.mobile.core.utils.OBNumber;
+import org.openbravo.test.mobile.retail.mobilecore.annotations.TestClassAnnotations;
+import org.openbravo.test.mobile.retail.mobilecore.database.ConnectionHelper;
+import org.openbravo.test.mobile.retail.mobilecore.database.DatabaseHelperInsertUpdateOrDelete;
+import org.openbravo.test.mobile.retail.mobilecore.database.DatabaseHelperSelect;
+import org.openbravo.test.mobile.retail.mobilecore.javascript.BackboneHelper;
+import org.openbravo.test.mobile.retail.mobilecore.utils.RetailUtils;
+import org.openbravo.test.mobile.retail.pack.API.WebPOSAPI;
+import org.openbravo.test.mobile.retail.pack.database.WebPOSDatabaseHelper;
+import org.openbravo.test.mobile.retail.pack.database.rows.InvoiceLinesOfDocumentnoRow;
+import org.openbravo.test.mobile.retail.pack.selenium.TestIdPack;
+import org.openbravo.test.mobile.retail.pack.selenium.terminals.WebPOSTerminalHelper;
+
+@TestClassAnnotations(isHighVolumeCompatible = true)
+@RunWith(RetryRunnerParameterized.class)
+public class I35216_VerifyCashUpSplitLines extends WebPOSTerminalHelper {
+  private static final OBNumber lineNetAmt1 = new OBNumber("4.61");
+  private static final OBNumber lineNetAmt2 = new OBNumber("20.7");
+  private static final OBNumber totalPaid = new OBNumber("30.62");
+
+  @Parameters(name = "({index})")
+  public static Collection<String[]> getTestParameters() {
+    final ArrayList<String[]> parameters = new ArrayList<String[]>();
+    parameters.add(new String[] { "CMS-1", "demouser" });
+    return parameters;
+  }
+
+  public I35216_VerifyCashUpSplitLines(final String terminal, final String userName) {
+    super(terminal, userName, "openbravo");
+  }
+
+  @Test
+  public void test() {
+    initInventory();
+
+    WebPOSAPI.cleanCashup(this);
+
+    // Change Customer
+    changeCustomer();
+
+    // Create Receipt
+    tap(TestIdPack.BUTTON_MENU);
+    tap(TestIdPack.BUTTON_MENU_INVOICETHISRECEIPT);
+    // Add Product
+    tap(TestIdPack.BUTTON_SEARCH);
+    write(TestIdPack.FIELD_SEARCH_TEXT, TestIdPack.BUTTON_SEARCHPRODUCT_MINILINTERN.getRowName());
+    tap(TestIdPack.BUTTON_SEARCH_EXECUTE);
+    tap(TestIdPack.BUTTON_SEARCHPRODUCT_MINILINTERN);
+    tap(TestIdPack.BUTTON_EDIT);
+    tap(TestIdPack.BUTTON_KEYPAD_1);
+    tap(TestIdPack.BUTTON_KEYPAD_1);
+    tap(TestIdPack.BUTTON_KEYPAD_QUANTITY);
+    verify(TestIdPack.LABEL_TOTALTOPAY, totalPaid);
+    final String documentNo1 = BackboneHelper.getDocumentNo();
+    tap(TestIdPack.BUTTON_PAY);
+    tap(TestIdPack.BUTTON_PAY_EXACT);
+    tap(TestIdPack.BUTTON_PAY_DONE);
+    verify(TestIdPack.LABEL_TOTALTOPAY, "0.00");
+
+    // verify invoice values
+    verifyFirstReceiptInvoice(documentNo1);
+
+    // Create Another Receipt
+    // Change Customer
+    changeCustomer();
+    tap(TestIdPack.BUTTON_SEARCH);
+    write(TestIdPack.FIELD_SEARCH_TEXT,
+        TestIdPack.BUTTON_SEARCHPRODUCT_HEADLAMPULTRALIGHT.getRowName());
+    tap(TestIdPack.BUTTON_SEARCH_EXECUTE);
+    tap(TestIdPack.BUTTON_SEARCHPRODUCT_HEADLAMPULTRALIGHT);
+    verify(TestIdPack.LABEL_TOTALTOPAY, "18.90");
+    tap(TestIdPack.BUTTON_PAY);
+    tap(TestIdPack.BUTTON_PAY_EXACT);
+    tap(TestIdPack.BUTTON_PAY_DONE);
+    verify(TestIdPack.LABEL_TOTALTOPAY, "0.00");
+
+    // Do final Cashup
+    tap(TestIdPack.BUTTON_MENU);
+    tap(TestIdPack.BUTTON_MENU_CASHUP);
+    // step 1 of 4
+    tap(TestIdPack.BUTTON_CASHUP_COUNTCASH_20);
+    tap(TestIdPack.BUTTON_CASHUP_COUNTCASH_10);
+    tap(TestIdPack.BUTTON_CASHUP_COUNTCASH_2);
+    tap(TestIdPack.BUTTON_CASHUP_NEXT);
+    // step 2 of 4
+    tap(TestIdPack.BUTTON_CASHUP_CARD_OK);
+    tap(TestIdPack.BUTTON_CASHUP_CASH_OK);
+    tap(TestIdPack.BUTTON_CASHUP_VOUCHER_CMS_OK);
+    tap(TestIdPack.BUTTON_CASHUP_NEXT);
+    // step 3 of 4
+    tap(TestIdPack.BUTTON_CASHUP_KEEPNOTHING);
+    tap(TestIdPack.BUTTON_CASHUP_NEXT);
+    tap(TestIdPack.BUTTON_CASHUP_KEEPNOTHING);
+    tap(TestIdPack.BUTTON_CASHUP_NEXT);
+    // step 4 of 4
+    tap(TestIdPack.BUTTON_CASHUP_POSTPRINTANDCLOSE);
+    // done
+    verify(TestIdPack.LABEL_POPUP_TITLE, "Good job!");
+    tap(TestIdPack.BUTTON_POPUP_OK);
+    verify(TestIdPack.LABEL_TOTALTOPAY, "0.00");
+
+    // verify first invoice values after cashup
+    verifyFirstReceiptInvoice(documentNo1);
+  }
+
+  private void changeCustomer() {
+    tap(TestIdPack.BUTTON_RECEIPT_CUSTOMER);
+    write(TestIdPack.FIELD_CUSTOMER_MODAL, "arturo montoro");
+    tap(TestIdPack.BUTTON_CUSTOMER_MODAL_SEARCH);
+    tap(TestIdPack.BUTTON_CUSTOMER_SEARCH_ROW1);
+  }
+
+  private void verifyFirstReceiptInvoice(final String documentNo) {
+    final ArrayList<InvoiceLinesOfDocumentnoRow> ils = WebPOSDatabaseHelper.getInvoiceLinesOf(
+        documentNo, 2, "  ORDER BY c_order_documentno ASC, c_invoiceline.linenetamt ASC");
+
+    final InvoiceLinesOfDocumentnoRow row1_1 = ils.get(0);
+    assertThat(row1_1.c_order_documentno, equalTo(documentNo));
+    assertThat(row1_1.linenetamt, equalTo(lineNetAmt1));
+    assertThat(row1_1.lineNo, equalTo(new OBNumber(20)));
+
+    final InvoiceLinesOfDocumentnoRow row1_2 = ils.get(1);
+    assertThat(row1_2.linenetamt, equalTo(lineNetAmt2));
+    assertThat(row1_2.lineNo, equalTo(new OBNumber(10)));
+  }
+
+  public static void initInventory() {
+    final String strExecutionId = ((Long) System.currentTimeMillis()).toString();
+    final String strId = RetailUtils.getRandomUUID();
+
+    final String sqlHeader = String
+        .format("insert into m_inventory values (get_uuid(),'39363B0921BB4293B48383844325E84C','3B187EC130A549A7A9388F8060EF156D','Y',now(),'100',now(),'100', '"
+            + strExecutionId
+            + "',NULL,'5A61F38D57F147A3A28E4C606B66DC47',NOW(),'N','N','N','N','N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'N')");
+    new DatabaseHelperInsertUpdateOrDelete().execute(sqlHeader, 1);
+
+    final String sqlLine1 = String
+        .format("insert into m_inventoryline values (get_uuid(),'39363B0921BB4293B48383844325E84C','3B187EC130A549A7A9388F8060EF156D','Y',now(),'100',now(),'100', (select m_inventory_id from m_inventory where name = '"
+            + strExecutionId
+            + "'),'05AFFE9C98F34FA08EBECA27A40E33B7','967711F04BF6414299A3259CD44C390A',10,coalesce((select qtyonhand from m_storage_detail  where m_product_id ='967711F04BF6414299A3259CD44C390A' and m_locator_id ='05AFFE9C98F34FA08EBECA27A40E33B7'), 0),9,null,null,null,null,'100',null,null,null)");
+    new DatabaseHelperInsertUpdateOrDelete().execute(sqlLine1, 1);
+
+    final String sqlLine2 = String
+        .format("insert into m_inventoryline values (get_uuid(),'39363B0921BB4293B48383844325E84C','3B187EC130A549A7A9388F8060EF156D','Y',now(),'100',now(),'100', (select m_inventory_id from m_inventory where name = '"
+            + strExecutionId
+            + "'),'C54BD31C164F447DABC24C11E91CD780','967711F04BF6414299A3259CD44C390A',20,coalesce((select qtyonhand from m_storage_detail  where m_product_id ='967711F04BF6414299A3259CD44C390A' and m_locator_id ='C54BD31C164F447DABC24C11E91CD780'), 0),2,null,null,null,null,'100',null,null,null)");
+    new DatabaseHelperInsertUpdateOrDelete().execute(sqlLine2, 1);
+
+    final String sqlAdPinstance = String
+        .format("insert into ad_pinstance values ('"
+            + strId
+            + "', '107', (select m_inventory_id from m_inventory where name='"
+            + strExecutionId
+            + "'), 'N', now(), '100', now(),  1, null, '39363B0921BB4293B48383844325E84C', '0', '0', '0', 'Y' )");
+    new DatabaseHelperInsertUpdateOrDelete().execute(sqlAdPinstance, 1);
+
+    // support to oracle
+    if (ConnectionHelper.isOracle()) {
+      new DatabaseHelperInsertUpdateOrDelete().execute("call m_inventory_post('" + strId + "')", 0);
+    } else {
+      final String sqlInventoryPost = String.format("select * from m_inventory_post('" + strId
+          + "')");
+      new DatabaseHelperSelect() {
+        @Override
+        protected void yieldResultSet(final ResultSet rs) throws SQLException {
+          // do nothing
+        }
+      }.execute(sqlInventoryPost, 1);
+    }
+  }
+}