0027033: New check-box Include zero figures in Trial Balance report

diff --git a/src-db/database/sourcedata/AD_TEXTINTERFACES.xml b/src-db/database/sourcedata/AD_TEXTINTERFACES.xml
--- a/src-db/database/sourcedata/AD_TEXTINTERFACES.xml
+++ b/src-db/database/sourcedata/AD_TEXTINTERFACES.xml
@@ -21899,6 +21899,17 @@
 <!--FD9E7290E18A7584E040007F01015D57-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
 <!--FD9E7290E18A7584E040007F01015D57--></AD_TEXTINTERFACES>
 
+<!--FE5F8537C485BC8EE040007F01012D77--><AD_TEXTINTERFACES>
+<!--FE5F8537C485BC8EE040007F01012D77-->  <AD_TEXTINTERFACES_ID><![CDATA[FE5F8537C485BC8EE040007F01012D77]]></AD_TEXTINTERFACES_ID>
+<!--FE5F8537C485BC8EE040007F01012D77-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--FE5F8537C485BC8EE040007F01012D77-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--FE5F8537C485BC8EE040007F01012D77-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--FE5F8537C485BC8EE040007F01012D77-->  <TEXT><![CDATA[Include Zero Figures]]></TEXT>
+<!--FE5F8537C485BC8EE040007F01012D77-->  <FILENAME><![CDATA[/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html]]></FILENAME>
+<!--FE5F8537C485BC8EE040007F01012D77-->  <ISUSED><![CDATA[Y]]></ISUSED>
+<!--FE5F8537C485BC8EE040007F01012D77-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--FE5F8537C485BC8EE040007F01012D77--></AD_TEXTINTERFACES>
+
 <!--FE9F357DE872420ABD3286CDCAE919D1--><AD_TEXTINTERFACES>
 <!--FE9F357DE872420ABD3286CDCAE919D1-->  <AD_TEXTINTERFACES_ID><![CDATA[FE9F357DE872420ABD3286CDCAE919D1]]></AD_TEXTINTERFACES_ID>
 <!--FE9F357DE872420ABD3286CDCAE919D1-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
diff --git a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html
@@ -166,7 +166,7 @@
 
 function addRows(htmlText, id) {
   var jsonTable = eval(htmlText);
-  if (typeof jsonTable != undefined) {
+  if (typeof jsonTable != undefined && jsonTable.hasOwnProperty("rows")) {
   for (i=0; i&lt;jsonTable.rows.length; i++) {
     var node=document.getElementById('funcEvenOddRow1'+id);
     var elem=document.createElement('TR');
@@ -990,10 +990,13 @@
                     </table>
                   </button>
                 </div>
+              </td>              
+              <td class="TitleCell" colspan="2"><span class="LabelText">Include Zero Figures</span></td>                           
+              <td class="Radio_Check_ContentCell">
+                <span class="Checkbox_container_NOT_Focused">
+                  <input type="checkbox" name="inpIncludeZeroFigures" id="paramIncludeZeroFigures" value="Y"></input>
+                </span>
               </td>
-              <td class="ContentCell"></input>
-              </td>
-              <td class="TitleCell"></td>
               <td class="ContentCell"></td>
               <td class="ContentCell"></td>
             </tr>
diff --git a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java
@@ -11,7 +11,7 @@
  * under the License. 
  * The Original Code is Openbravo ERP. 
  * The Initial Developer of the Original Code is Openbravo SLU 
- * All portions are Copyright (C) 2001-2013 Openbravo SLU 
+ * All portions are Copyright (C) 2001-2014 Openbravo SLU 
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -25,6 +25,7 @@
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.HashMap;
+import java.util.List;
 import java.util.Map;
 import java.util.Vector;
 
@@ -86,6 +87,8 @@
               Utility.getContext(this, vars, "#User_Client", "Account")));
       String strNotInitialBalance = vars.getGlobalVariable("inpNotInitialBalance",
           "ReportTrialBalance|notInitialBalance", "Y");
+      String strIncludeZeroFigures = vars.getGlobalVariable("inpIncludeZeroFigures",
+          "ReportTrialBalance|includeZeroFigures", "");
       String strcElementValueFromDes = "", strcElementValueToDes = "";
       if (!strcElementValueFrom.equals(""))
         strcElementValueFromDes = ReportTrialBalanceData.selectSubaccountDescription(this,
@@ -101,7 +104,7 @@
       printPageDataSheet(response, vars, strDateFrom, strDateTo, strPageNo, strOrg, strLevel,
           strcElementValueFrom, strcElementValueTo, strcElementValueFromDes, strcElementValueToDes,
           strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, strNotInitialBalance,
-          strGroupBy);
+          strGroupBy, strIncludeZeroFigures);
 
     } else if (vars.commandIn("FIND")) {
       String strcAcctSchemaId = vars.getRequestGlobalVariable("inpcAcctSchemaId",
@@ -125,6 +128,8 @@
           "ReportTrialBalance|C_ElementValue_IDTO");
       String strNotInitialBalance = vars.getStringParameter("inpNotInitialBalance", "N");
       vars.setSessionValue("ReportTrialBalance|notInitialBalance", strNotInitialBalance);
+      String strIncludeZeroFigures = vars.getRequestGlobalVariable("inpIncludeZeroFigures",
+          "ReportTrialBalance|includeZeroFigures");
       String strcElementValueFromDes = "", strcElementValueToDes = "";
       if (!strcElementValueFrom.equals(""))
         strcElementValueFromDes = ReportTrialBalanceData.selectSubaccountDescription(this,
@@ -138,7 +143,7 @@
       printPageDataSheet(response, vars, strDateFrom, strDateTo, strPageNo, strOrg, strLevel,
           strcElementValueFrom, strcElementValueTo, strcElementValueFromDes, strcElementValueToDes,
           strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, strNotInitialBalance,
-          strGroupBy);
+          strGroupBy, strIncludeZeroFigures);
 
     } else if (vars.commandIn("PDF", "XLS")) {
       String strcAcctSchemaId = vars.getRequestGlobalVariable("inpcAcctSchemaId",
@@ -171,15 +176,18 @@
       String strPageNo = vars.getRequestGlobalVariable("inpPageNo", "ReportTrialBalance|PageNo");
       String strNotInitialBalance = vars.getStringParameter("inpNotInitialBalance", "N");
       vars.setSessionValue("ReportTrialBalance|notInitialBalance", strNotInitialBalance);
+      String strIncludeZeroFigures = vars.getStringParameter("inpIncludeZeroFigures", "N");
+      vars.setSessionValue("ReportTrialBalance|includeZeroFigures", strIncludeZeroFigures);
+
       if (vars.commandIn("PDF"))
         printPageDataPDF(request, response, vars, strDateFrom, strDateTo, strOrg, strLevel,
             strcElementValueFrom, strcElementValueFromDes, strcElementValueTo,
             strcElementValueToDes, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId,
-            strNotInitialBalance, strGroupBy, strPageNo);
+            strNotInitialBalance, strGroupBy, strPageNo, strIncludeZeroFigures);
       else
         printPageDataXLS(request, response, vars, strDateFrom, strDateTo, strOrg, strLevel,
             strcElementValueFrom, strcElementValueTo, strcBpartnerId, strmProductId, strcProjectId,
-            strcAcctSchemaId, strNotInitialBalance, strGroupBy);
+            strcAcctSchemaId, strNotInitialBalance, strGroupBy, strIncludeZeroFigures);
 
     } else if (vars.commandIn("OPEN")) {
       String strAccountId = vars.getRequiredStringParameter("inpcAccountId");
@@ -278,8 +286,8 @@
       String strDateFrom, String strDateTo, String strPageNo, String strOrg, String strLevel,
       String strcElementValueFrom, String strcElementValueTo, String strcElementValueFromDes,
       String strcElementValueToDes, String strcBpartnerId, String strmProductId,
-      String strcProjectId, String strcAcctSchemaId, String strNotInitialBalance, String strGroupBy)
-      throws IOException, ServletException {
+      String strcProjectId, String strcAcctSchemaId, String strNotInitialBalance,
+      String strGroupBy, String strIncludeZeroFigures) throws IOException, ServletException {
 
     String strMessage = "";
     XmlDocument xmlDocument = null;
@@ -430,6 +438,7 @@
         + "');"));
     xmlDocument.setParameter("groupbyselected", strGroupBy);
     xmlDocument.setParameter("notInitialBalance", strNotInitialBalance);
+    xmlDocument.setParameter("paramZeroFigures", strIncludeZeroFigures);
 
     xmlDocument.setData(
         "reportCBPartnerId_IN",
@@ -453,6 +462,8 @@
             Utility.getContext(this, vars, "#User_Client", ""), strcProjectIdAux));
 
     if (data != null && data.length > 0) {
+      if ("Y".equals(strIncludeZeroFigures))
+        data = includeZeroFigures(data, strcAcctSchemaId, strOrg);
       xmlDocument.setData("structure1", data);
     } else {
       if (vars.commandIn("FIND")) {
@@ -473,7 +484,8 @@
       VariablesSecureApp vars, String strDateFrom, String strDateTo, String strOrg,
       String strLevel, String strcElementValueFrom, String strcElementValueTo,
       String strcBpartnerId, String strmProductId, String strcProjectId, String strcAcctSchemaId,
-      String strNotInitialBalance, String strGroupBy) throws IOException, ServletException {
+      String strNotInitialBalance, String strGroupBy, String strIncludeZeroFigures)
+      throws IOException, ServletException {
 
     response.setContentType("text/html; charset=UTF-8");
     ReportTrialBalanceData[] data = null;
@@ -531,6 +543,9 @@
             Utility.messageBD(this, "ProcessStatus-W", vars.getLanguage()),
             Utility.messageBD(this, "NoDataFound", vars.getLanguage()));
       } else {
+        if ("Y".equals(strIncludeZeroFigures))
+          data = includeZeroFigures(data, strcAcctSchemaId, strOrg);
+
         AcctSchema acctSchema = OBDal.getInstance().get(AcctSchema.class, strcAcctSchemaId);
 
         String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportTrialBalanceExcel.jrxml";
@@ -573,8 +588,8 @@
       String strLevel, String strcElementValueFrom, String strcElementValueFromDes,
       String strcElementValueTo, String strcElementValueToDes, String strcBpartnerId,
       String strmProductId, String strcProjectId, String strcAcctSchemaId,
-      String strNotInitialBalance, String strGroupBy, String strPageNo) throws IOException,
-      ServletException {
+      String strNotInitialBalance, String strGroupBy, String strPageNo, String strIncludeZeroFigures)
+      throws IOException, ServletException {
 
     response.setContentType("text/html; charset=UTF-8");
     ReportTrialBalanceData[] data = null;
@@ -616,6 +631,9 @@
             Utility.messageBD(this, "ProcessStatus-W", vars.getLanguage()),
             Utility.messageBD(this, "NoDataFound", vars.getLanguage()));
       } else {
+        if ("Y".equals(strIncludeZeroFigures))
+          data = includeZeroFigures(data, strcAcctSchemaId, strOrg);
+
         AcctSchema acctSchema = OBDal.getInstance().get(AcctSchema.class, strcAcctSchemaId);
 
         String strLanguage = vars.getLanguage();
@@ -849,6 +867,58 @@
     return Tree.getMembers(this, strTree, strChild);
   }
 
+  /**
+   * Adds zero figures (all CoA) to the report data
+   * 
+   * @param data
+   * @param strcAcctSchemaId
+   * @param strOrg
+   * @return ReportTrialBalanceData array with zero figures.
+   * @throws ServletException
+   */
+  private ReportTrialBalanceData[] includeZeroFigures(ReportTrialBalanceData[] data,
+      String strcAcctSchemaId, String strOrg) throws ServletException {
+    ReportTrialBalanceData[] dataZeroFigures = null;
+    ReportTrialBalanceData[] dataAccountCombinations = ReportTrialBalanceData
+        .selectAccountCombinations(this, strcAcctSchemaId);
+    if (dataAccountCombinations.length > 0) {
+      Vector<Object> vec = new Vector<Object>();
+      List<String> dataAccounts = new ArrayList<String>(data.length);
+      for (int i = 0; i < data.length; i++) {
+        dataAccounts.add(data[i].id);
+      }
+      int j = 0;
+      int extra = 0;
+      for (int i = 0; i < dataAccountCombinations.length; i++) {
+        String accountId = dataAccountCombinations[i].id;
+        if (dataAccounts.contains(accountId)) {
+          int lastAccountMatch = dataAccounts.lastIndexOf(accountId);
+          int extraCount = 0;
+          for (int k = j; k <= lastAccountMatch; k++) {
+            vec.addElement(data[k]);
+            j++;
+            if (extraCount > 0)
+              extra++;
+            extraCount++;
+          }
+        } else {
+          ReportTrialBalanceData[] dataProcess = ReportTrialBalanceData.set();
+          dataProcess[0].accountId = dataAccountCombinations[i].accountId;
+          dataProcess[0].amtacctcr = "0";
+          dataProcess[0].amtacctdr = "0";
+          dataProcess[0].id = dataAccountCombinations[i].id;
+          dataProcess[0].name = dataAccountCombinations[i].name;
+          dataProcess[0].saldoFinal = "0";
+          dataProcess[0].saldoInicial = "0";
+          vec.addElement(dataProcess[0]);
+        }
+      }
+      dataZeroFigures = new ReportTrialBalanceData[dataAccountCombinations.length + extra];
+      vec.copyInto(dataZeroFigures);
+    }
+    return dataZeroFigures;
+  }
+
   public String getServletInfo() {
     return "Servlet ReportTrialBalance. This Servlet was made by Eduardo Argal and mirurita";
   }
diff --git a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.xml b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.xml
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.xml
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.xml
@@ -12,7 +12,7 @@
 * under the License. 
 * The Original Code is Openbravo ERP. 
 * The Initial Developer of the Original Code is Openbravo SLU 
-* All portions are Copyright (C) 2001-2009 Openbravo SLU 
+* All portions are Copyright (C) 2001-2014 Openbravo SLU 
 * All Rights Reserved. 
 * Contributor(s):  ______________________________________.
 ************************************************************************
@@ -52,6 +52,8 @@
   <PARAMETER id="paramPageNo" name="PageNo" attribute="value"/>
   <PARAMETER id="fieldCheck" name="check" default="Y"/>
   <PARAMETER id="fieldNotInitialBalance" name="notInitialBalance" boolean="checked" withId="fieldCheck"/>
+  <PARAMETER id="parameterZeroFigures" name="all" default="Y"/>
+  <PARAMETER id="paramIncludeZeroFigures" name="paramZeroFigures" boolean="checked" withId="parameterZeroFigures" default="N"/>  
 
   <SUBREPORT id="reportCBPartnerId_IN" name="reportCBPartnerId_IN" report="org/openbravo/erpCommon/reference/List">
     <ARGUMENT name="parameterListSelected" withId="paramVacio"/>
diff --git a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql
@@ -12,7 +12,7 @@
  * under the License. 
  * The Original Code is Openbravo ERP. 
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2001-2011 Openbravo SLU
+ * All portions are Copyright (C) 2001-2014 Openbravo SLU
  * All Rights Reserved. 
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -507,4 +507,16 @@
     ]]></Sql>
   <Parameter name="cElementvalueId"/>
   </SqlMethod>
+  
+  <SqlMethod name="selectAccountCombinations"  type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+        SELECT EV.C_ELEMENTVALUE_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME
+          FROM C_VALIDCOMBINATION VC, C_ELEMENTVALUE EV
+          WHERE VC.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID
+          AND VC.ISACTIVE = 'Y'
+          AND EV.ISACTIVE = 'Y'
+          AND VC.C_ACCTSCHEMA_ID = ?
+          ORDER BY EV.VALUE
+    ]]></Sql>
+  <Parameter name="cAcctSchemaId"/>
+  </SqlMethod>
 </SqlClass>