0034017: [SER QA 1730] Function getLastDocumentNumber relies on the separator '/' to process Document numbers

# HG changeset patch
# User Ranjith S R <ranjith@qualiantech.com>
# Date 1476769075 -19800
#      Tue Oct 18 11:07:55 2016 +0530
# Node ID 8808f6c7f0cdc628206490602a7bac6199a6a200
# Parent  c48e23f334d41f62f08a8ab921926a2d56e7d146
Related to issue 34017 : Modified Document number regex to get max document number based on document seperator

diff -r c48e23f334d4 -r 8808f6c7f0cd src/org/openbravo/retail/posterminal/POSUtils.java
--- a/src/org/openbravo/retail/posterminal/POSUtils.java	Thu Oct 13 12:50:21 2016 +0530
+++ b/src/org/openbravo/retail/posterminal/POSUtils.java	Tue Oct 18 11:07:55 2016 +0530
@@ -339,13 +339,13 @@
     Long lastDocNum = terminal.getLastassignednum();
     if (lastDocNum == null) {
       if (curDbms.equals("POSTGRE")) {
-        sqlToExecute = "select max(a.docno) from (select to_number(substring(documentno, '/([0-9]+)$')) docno from c_order where em_obpos_applications_id= (select obpos_applications_id from obpos_applications where value = ?) and c_doctype_id in ("
-            + doctypeIds
-            + ") and documentno like (select orderdocno_prefix from obpos_applications where value = ?)||'%') a";
+        sqlToExecute = "select max(a.docno) from (select to_number(substring(replace(co.documentno, app.orderdocno_prefix, ''), '^/{0,1}([0-9]+)$')) docno from c_order co "
+            + "inner join obpos_applications app on app.obpos_applications_id = co.em_obpos_applications_id and app.value = ? "
+            + "where co.c_doctype_id in (" + doctypeIds + ")) a";
       } else if (curDbms.equals("ORACLE")) {
-        sqlToExecute = "select max(a.docno) from (select to_number(substr(REGEXP_SUBSTR(documentno, '/([0-9]+)$'), 2)) docno from c_order where em_obpos_applications_id= (select obpos_applications_id from obpos_applications where value = ?) and c_doctype_id in ("
-            + doctypeIds
-            + ") and documentno like (select orderdocno_prefix from obpos_applications where value = ?)||'%' ) a";
+        sqlToExecute = "select max(a.docno) from (select to_number(substr(REGEXP_SUBSTR(REPLACE(co.documentno, app.orderdocno_prefix), '^/{0,1}([0-9]+)$'), 2)) docno from c_order co "
+            + "inner join obpos_applications app on app.obpos_applications_id = co.em_obpos_applications_id and app.value = ? "
+            + "where co.c_doctype_id in (" + doctypeIds + ")) a";
       } else {
         // unknow DBMS
         // shouldn't happen
@@ -354,7 +354,6 @@
       }
       SQLQuery query = OBDal.getInstance().getSession().createSQLQuery(sqlToExecute);
       query.setString(0, searchKey);
-      query.setString(1, searchKey);
 
       Object result = query.uniqueResult();
       if (result == null) {
@@ -434,13 +433,13 @@
     Long quotationlastDocNum = terminal.getQuotationslastassignednum();
     if (quotationlastDocNum == null) {
       if (curDbms.equals("POSTGRE")) {
-        sqlToExecute = "select max(a.docno) from (select to_number(substring(documentno, '/([0-9]+)$')) docno from c_order where em_obpos_applications_id= (select obpos_applications_id from obpos_applications where value = ?) and c_doctype_id in ("
-            + doctypeIds
-            + ") and documentno like (select quotationdocno_prefix from obpos_applications where value = ?)||'%') a";
+        sqlToExecute = "select max(a.docno) from (select to_number(substring(replace(co.documentno, app.quotationdocno_prefix, ''), '^/{0,1}([0-9]+)$')) docno from c_order co "
+            + "inner join obpos_applications app on app.obpos_applications_id = co.em_obpos_applications_id and app.value = ? "
+            + "where co.c_doctype_id in (" + doctypeIds + ")) a";
       } else if (curDbms.equals("ORACLE")) {
-        sqlToExecute = "select max(a.docno) from (select to_number(substr(REGEXP_SUBSTR(documentno, '/([0-9]+)$'), 2)) docno from c_order where em_obpos_applications_id= (select obpos_applications_id from obpos_applications where value = ?) and c_doctype_id in ("
-            + doctypeIds
-            + ") and documentno like (select quotationdocno_prefix from obpos_applications where value = ?)||'%' ) a";
+        sqlToExecute = "select max(a.docno) from (select to_number(substr(REGEXP_SUBSTR(REPLACE(co.documentno, app.quotationdocno_prefix), '^/{0,1}([0-9]+)$'), 2)) docno from c_order co "
+            + "inner join obpos_applications app on app.obpos_applications_id = co.em_obpos_applications_id and app.value = ? "
+            + "where co.c_doctype_id in (" + doctypeIds + ")) a";
       } else {
         // unknow DBMS
         // shouldn't happen
@@ -449,7 +448,6 @@
       }
       SQLQuery query = OBDal.getInstance().getSession().createSQLQuery(sqlToExecute);
       query.setString(0, searchKey);
-      query.setString(1, searchKey);
 
       Object result = query.uniqueResult();
       if (result == null) {
@@ -528,13 +526,13 @@
     Long returnlastDocNum = terminal.getReturnslastassignednum();
     if (returnlastDocNum == null) {
       if (curDbms.equals("POSTGRE")) {
-        sqlToExecute = "select max(a.docno) from (select to_number(substring(documentno, '/([0-9]+)$')) docno from c_order where em_obpos_applications_id= (select obpos_applications_id from obpos_applications where value = ?) and c_doctype_id in ("
-            + doctypeIds
-            + ") and documentno like (select returndocno_prefix from obpos_applications where value = ?)||'%') a";
+        sqlToExecute = "select max(a.docno) from (select to_number(substring(replace(co.documentno, app.returndocno_prefix, ''), '^/{0,1}([0-9]+)$')) docno from c_order co "
+            + "inner join obpos_applications app on app.obpos_applications_id = co.em_obpos_applications_id and app.value = ? "
+            + "where co.c_doctype_id in (" + doctypeIds + ")) a";
       } else if (curDbms.equals("ORACLE")) {
-        sqlToExecute = "select max(a.docno) from (select to_number(substr(REGEXP_SUBSTR(documentno, '/([0-9]+)$'), 2)) docno from c_order where em_obpos_applications_id= (select obpos_applications_id from obpos_applications where value = ?) and c_doctype_id in ("
-            + doctypeIds
-            + ") and documentno like (select returndocno_prefix from obpos_applications where value = ?)||'%' ) a";
+        sqlToExecute = "select max(a.docno) from (select to_number(substr(REGEXP_SUBSTR(REPLACE(co.documentno, app.returndocno_prefix), '^/{0,1}([0-9]+)$'), 2)) docno from c_order co "
+            + "inner join obpos_applications app on app.obpos_applications_id = co.em_obpos_applications_id and app.value = ? "
+            + "where co.c_doctype_id in (" + doctypeIds + ")) a";
       } else {
         // unknow DBMS
         // shouldn't happen
@@ -543,7 +541,6 @@
       }
       SQLQuery query = OBDal.getInstance().getSession().createSQLQuery(sqlToExecute);
       query.setString(0, searchKey);
-      query.setString(1, searchKey);
       Object result = query.uniqueResult();
       if (result == null) {
         maxDocNo = 0;