| Attached Files |  30788.diff [^] (36,944 bytes) 2015-09-15 14:06 [Show Content] [Hide Content]# HG changeset patch
# User Alvaro Ferraz <alvaro.ferraz@openbravo.com>
# Date 1442231616 -7200
# Mon Sep 14 13:53:36 2015 +0200
# Node ID 90139faa63bb70b773c3b022e47b27edd4f8cf03
# Parent 7b7fbea88f7a0e21f6f4f7646a013564d8e7e19c
Fixes issue 30788: Add filter by document no. in Journal Entries Report
Document No. field has been added to Journal Entries Report.
It will be shown only in case of Document selected has a document number property. Otherwise, it will be hidden and its value will be empty.
If it is filled with any value, report query will retrieve only records of this document with selected document number.
Query to filter by documentno will be dynamically generated from ReportGeneralLedgerJournal.java and insterted into ReportGeneralLedgerJournalData.java query.
diff --git a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.html b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.html
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.html
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.html
@@ -119,6 +119,8 @@
displayLogicElement('advancedFiltersrow3', true);
displayLogicElement('advancedFiltersrow4', true);
displayLogicElement('advancedFiltersrow5', true);
+ displayLogicElement('advancedFiltersrow6', true);
+ displayDocumentNo();
}
else{
displayLogicElement('advancedFiltersrow0', false);
@@ -127,6 +129,40 @@
displayLogicElement('advancedFiltersrow3', false);
displayLogicElement('advancedFiltersrow4', false);
displayLogicElement('advancedFiltersrow5', false);
+ displayLogicElement('advancedFiltersrow6', false);
+ }
+}
+
+function displayDocumentNo(){
+ switch (inpDocument.options[inpDocument.selectedIndex].value) {
+ case "APC":
+ case "API":
+ case "APP":
+ case "ARC":
+ case "ARI":
+ case "ARI_RM":
+ case "ARR":
+ case "BSF":
+ case "CAD":
+ case "DDB":
+ case "GLJ":
+ case "LDC":
+ case "REC":
+ case "REM_REM":
+ displayLogicElement('advancedFiltersrow2', true);
+ break;
+ case "AMZ":
+ case "CMC":
+ case "FAT":
+ case "LCC":
+ case "REM_REMCANCEL":
+ case "REM_REMRETURN":
+ displayLogicElement('advancedFiltersrow2', false);
+ paramDocumentNo.value = "";
+ break;
+ default:
+ displayLogicElement('advancedFiltersrow2', false);
+ paramDocumentNo.value = "";
}
}
@@ -253,6 +289,7 @@
updateMenuIcon('buttonMenu');
setWindowElementFocus('firstElement');
+ displayDocumentNo();
displayAdvancedFilters();
selectDocument();
openTabWhenPost();
@@ -550,14 +587,18 @@
</tr>
<tr id=advancedFiltersrow1>
<td class="TitleCell"><span class="LabelText">Document</span></td>
- <td class="Combo_ContentCell" colspan="2"> <select name="inpDocument" id="inpDocument" class="Combo Combo_TwoCells_width">
+ <td class="Combo_ContentCell" colspan="2"> <select name="inpDocument" id="inpDocument" class="Combo Combo_TwoCells_width" onchange="displayDocumentNo();">
<option value=""> <div id="reportDocument"></div></option>
</select></td>
<td class="TitleCell"><span class="LabelText">Initial Page Number</span></td>
<td class="TextBox_ContentCell"> <input dojoType="openbravo:Textbox" class="dojoValidateValid TextBox_OneCell_width" type="text" name="inpPageNo" id="paramPageNo" size="10" maxlength="10" value=""></input><script>djConfig.searchIds.push("paramPageNo");</script>
</td>
</tr>
- <tr id=advancedFiltersrow2>
+ <tr id=advancedFiltersrow2>
+ <td class="TitleCell"><span class="LabelText">Document No.</span></td>
+ <td class="TextBox_ContentCell"><input dojoType="openbravo:Textbox" class="dojoValidateValid TextBox_OneCell_width" type="text" name="inpDocumentNo" id="paramDocumentNo" size="10" maxlength="10" value=""></input><script>djConfig.searchIds.push("paramPageNo");</script></td>
+ </tr>
+ <tr id=advancedFiltersrow3>
<td class="TitleCell"><span class="LabelText">Initial Entry Number</span></td>
<td class="TextBox_ContentCell">
<input dojoType="openbravo:Textbox" class="dojoValidateValid TextBox_OneCell_width" type="text" name="inpEntryNo"
@@ -569,14 +610,14 @@
<td class="Radio_Check_ContentCell"><input TYPE="checkbox" id="paramShowRegular" name="inpShowRegular" value="Y"/></td>
</tr>
- <tr id=advancedFiltersrow3>
+ <tr id=advancedFiltersrow4>
<td class="TitleCell"><span class="LabelText">Show P&L Closing Entries</span></td>
<td class="Radio_Check_ContentCell"><input TYPE="checkbox" id="paramShowReg" name="inpShowReg" value="Y"/></td>
<td class="ContentCell"></td>
<td class="TitleCell"><span class="LabelText">Show Opening Entries</span></td>
<td class="Radio_Check_ContentCell"><input TYPE="checkbox" id="paramShowOpening" name="inpShowOpening" value="Y"/></td>
</tr>
- <tr id=advancedFiltersrow4>
+ <tr id=advancedFiltersrow5>
<td class="TitleCell"><span class="LabelText">Show Closing Entries</span></td>
<td class="Radio_Check_ContentCell"><input TYPE="checkbox" id="paramShowClosing" name="inpShowClosing" value="Y"/></td>
<td class="ContentCell"></td>
@@ -584,7 +625,7 @@
<td class="Radio_Check_ContentCell"><input TYPE="checkbox" id="paramShowDescription" name="inpShowDescription" value="Y"/></td>
<td class="ContentCell"></td>
</tr>
- <tr id=advancedFiltersrow5>
+ <tr id=advancedFiltersrow6>
<td class="TitleCell"><span class="LabelText">Show Divide Up Entries</span></td>
<td class="Radio_Check_ContentCell"><input TYPE="checkbox" id="paramShowDivideUp" name="inpShowDivideUp" value="Y"/></td>
<td class="ContentCell"></td>
diff --git a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.java b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.java
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.java
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.java
@@ -33,9 +33,12 @@
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
+import org.apache.commons.lang.StringEscapeUtils;
+import org.apache.commons.lang.StringUtils;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;
+import org.hibernate.Query;
import org.openbravo.base.secureApp.HttpSecureAppServlet;
import org.openbravo.base.secureApp.VariablesHistory;
import org.openbravo.base.secureApp.VariablesSecureApp;
@@ -54,6 +57,8 @@
import org.openbravo.erpCommon.utility.OBError;
import org.openbravo.erpCommon.utility.ToolBar;
import org.openbravo.erpCommon.utility.Utility;
+import org.openbravo.model.ad.datamodel.Table;
+import org.openbravo.model.ad.system.Client;
import org.openbravo.model.common.enterprise.DocumentType;
import org.openbravo.model.financialmgmt.accounting.coa.AcctSchema;
import org.openbravo.model.financialmgmt.accounting.coa.AcctSchemaTable;
@@ -93,6 +98,8 @@
"");
String strDocument = vars.getGlobalVariable("inpDocument",
"ReportGeneralLedgerJournal|Document", "");
+ String strDocumentNo = vars.getGlobalVariable("inpDocumentNo",
+ "ReportGeneralLedgerJournal|DocumentNo", "");
String strOrg = vars.getGlobalVariable("inpOrg", "ReportGeneralLedgerJournal|Org", "0");
String strShowClosing = vars.getGlobalVariable("inpShowClosing",
"ReportGeneralLedgerJournal|ShowClosing", "Y");
@@ -143,10 +150,11 @@
strcelementvaluetodes = (strcelementvaluetodes.equals("null")) ? "" : strcelementvaluetodes;
vars.setSessionValue("inpElementValueIdFrom_DES", strcelementvaluefromdes);
vars.setSessionValue("inpElementValueIdTo_DES", strcelementvaluetodes);
- printPageDataSheet(response, vars, strDateFrom, strDateTo, strDocument, strOrg, strTable,
- strRecord, "", strcAcctSchemaId, strShowClosing, strShowReg, strShowOpening, strPageNo,
- strEntryNo, strShowDescription, strShowRegular, strShowDivideUp, "", "",
- strcelementvaluefrom, strcelementvalueto, strcelementvaluefromdes, strcelementvaluetodes);
+ printPageDataSheet(response, vars, strDateFrom, strDateTo, strDocument, strDocumentNo,
+ strOrg, strTable, strRecord, "", strcAcctSchemaId, strShowClosing, strShowReg,
+ strShowOpening, strPageNo, strEntryNo, strShowDescription, strShowRegular,
+ strShowDivideUp, "", "", strcelementvaluefrom, strcelementvalueto,
+ strcelementvaluefromdes, strcelementvaluetodes);
} else if (vars.commandIn("DIRECT")) {
String strTable = vars.getGlobalVariable("inpTable", "ReportGeneralLedgerJournal|Table");
String strRecord = vars.getGlobalVariable("inpRecord", "ReportGeneralLedgerJournal|Record");
@@ -172,15 +180,15 @@
}
setHistoryCommand(request, "DIRECT");
vars.setSessionValue("ReportGeneralLedgerJournal.initRecordNumber", "0");
- printPageDataSheet(response, vars, "", "", "", "", strTable, strRecord, "", strcAcctSchemaId,
- "", "", "", "1", "1", "", "Y", "", schemas, strPosted, "", "", "", "");
+ printPageDataSheet(response, vars, "", "", "", "", "", strTable, strRecord, "",
+ strcAcctSchemaId, "", "", "", "1", "1", "", "Y", "", schemas, strPosted, "", "", "", "");
} else if (vars.commandIn("DIRECT2")) {
String strFactAcctGroupId = vars.getGlobalVariable("inpFactAcctGroupId",
"ReportGeneralLedgerJournal|FactAcctGroupId");
setHistoryCommand(request, "DIRECT2");
vars.setSessionValue("ReportGeneralLedgerJournal.initRecordNumber", "0");
- printPageDataSheet(response, vars, "", "", "", "", "", "", strFactAcctGroupId, "", "", "",
- "", "1", "1", "", "Y", "", "", "", "", "", "", "");
+ printPageDataSheet(response, vars, "", "", "", "", "", "", "", strFactAcctGroupId, "", "",
+ "", "", "1", "1", "", "Y", "", "", "", "", "", "", "");
} else if (vars.commandIn("FIND")) {
String strcAcctSchemaId = vars.getRequestGlobalVariable("inpcAcctSchemaId",
"ReportGeneralLedger|cAcctSchemaId");
@@ -190,6 +198,8 @@
"ReportGeneralLedgerJournal|DateTo");
String strDocument = vars.getRequestGlobalVariable("inpDocument",
"ReportGeneralLedgerJournal|Document");
+ String strDocumentNo = vars.getRequestGlobalVariable("inpDocumentNo",
+ "ReportGeneralLedgerJournal|DocumentNo");
String strOrg = vars.getGlobalVariable("inpOrg", "ReportGeneralLedgerJournal|Org", "0");
String strShowClosing = vars.getRequestGlobalVariable("inpShowClosing",
"ReportGeneralLedgerJournal|ShowClosing");
@@ -254,10 +264,10 @@
strcelementvalueto);
vars.setSessionValue("inpElementValueIdFrom_DES", strcelementvaluefromdes);
vars.setSessionValue("inpElementValueIdTo_DES", strcelementvaluetodes);
- printPageDataSheet(response, vars, strDateFrom, strDateTo, strDocument, strOrg, "", "", "",
- strcAcctSchemaId, strShowClosing, strShowReg, strShowOpening, strPageNo, strEntryNo,
- strShowDescription, strShowRegular, strShowDivideUp, "", "", strcelementvaluefrom,
- strcelementvalueto, strcelementvaluefromdes, strcelementvaluetodes);
+ printPageDataSheet(response, vars, strDateFrom, strDateTo, strDocument, strDocumentNo,
+ strOrg, "", "", "", strcAcctSchemaId, strShowClosing, strShowReg, strShowOpening,
+ strPageNo, strEntryNo, strShowDescription, strShowRegular, strShowDivideUp, "", "",
+ strcelementvaluefrom, strcelementvalueto, strcelementvaluefromdes, strcelementvaluetodes);
} else if (vars.commandIn("PDF", "XLS")) {
if (log4j.isDebugEnabled())
log4j.debug("PDF");
@@ -269,6 +279,8 @@
"ReportGeneralLedgerJournal|DateTo");
String strDocument = vars.getRequestGlobalVariable("inpDocument",
"ReportGeneralLedgerJournal|Document");
+ String strDocumentNo = vars.getRequestGlobalVariable("inpDocumentNo",
+ "ReportGeneralLedgerJournal|DocumentNo");
String strOrg = vars.getGlobalVariable("inpOrg", "ReportGeneralLedgerJournal|Org", "0");
String strShowClosing = vars.getRequestGlobalVariable("inpShowClosing",
"ReportGeneralLedgerJournal|ShowClosing");
@@ -341,10 +353,10 @@
"ReportGeneralLedgerJournal|C_ElementValue_IDFROM");
String strcelementvalueto = vars.getRequestGlobalVariable("inpcElementValueIdTo",
"ReportGeneralLedgerJournal|C_ElementValue_IDTO");
- printPagePDF(request, response, vars, strDateFrom, strDateTo, strDocument, strOrg, strTable,
- strRecord, strFactAcctGroupId, strcAcctSchemaId, strShowClosing, strShowReg,
- strShowOpening, strPageNo, strEntryNo, "Y".equals(strShowDescription) ? "Y" : "",
- strShowRegular, strShowDivideUp, strcelementvaluefrom, strcelementvalueto);
+ printPagePDF(request, response, vars, strDateFrom, strDateTo, strDocument, strDocumentNo,
+ strOrg, strTable, strRecord, strFactAcctGroupId, strcAcctSchemaId, strShowClosing,
+ strShowReg, strShowOpening, strPageNo, strEntryNo, "Y".equals(strShowDescription) ? "Y"
+ : "", strShowRegular, strShowDivideUp, strcelementvaluefrom, strcelementvalueto);
} else if (vars.commandIn("PREVIOUS_RELATION")) {
String strInitRecord = vars.getSessionValue("ReportGeneralLedgerJournal.initRecordNumber");
String strPreviousRecordRange = vars.getSessionValue(PREVIOUS_RANGE);
@@ -404,13 +416,13 @@
}
private void printPageDataSheet(HttpServletResponse response, VariablesSecureApp vars,
- String strDateFrom, String strDateTo, String strDocument, String strOrg, String strTable,
- String strRecord, String strFactAcctGroupId, String strcAcctSchemaId, String strShowClosing,
- String strShowReg, String strShowOpening, String strPageNo, String strEntryNo,
- String strShowDescription, String strShowRegular, String strShowDivideUp, String accShemas,
- String strPosted, String strcelementvaluefrom, String strcelementvalueto,
- String strcelementvaluefromdes, String strcelementvaluetodes) throws IOException,
- ServletException {
+ String strDateFrom, String strDateTo, String strDocument, String strDocumentNo,
+ String strOrg, String strTable, String strRecord, String strFactAcctGroupId,
+ String strcAcctSchemaId, String strShowClosing, String strShowReg, String strShowOpening,
+ String strPageNo, String strEntryNo, String strShowDescription, String strShowRegular,
+ String strShowDivideUp, String accShemas, String strPosted, String strcelementvaluefrom,
+ String strcelementvalueto, String strcelementvaluefromdes, String strcelementvaluetodes)
+ throws IOException, ServletException {
String strAllaccounts = "Y";
if (strcelementvaluefrom != null && !strcelementvaluefrom.equals(""))
strAllaccounts = "N";
@@ -447,8 +459,8 @@
Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"),
Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
strDateFrom, DateTimeData.nDaysAfter(this, strDateTo, "1"), strDocument,
- strcAcctSchemaId, strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom,
- strcelementvalueto);
+ getDocumentNo(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
+ strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom, strcelementvalueto);
String strInitAcctEntries = vars.getSessionValue(PREVIOUS_ACCTENTRIES);
int acctEntries = (strInitAcctEntries.equals("") ? 0 : Integer.parseInt(strInitAcctEntries
.split(",")[0]));
@@ -503,15 +515,17 @@
Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"),
Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
strDateFrom, DateTimeData.nDaysAfter(this, strDateTo, "1"), strDocument,
- strcAcctSchemaId, strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom,
- strcelementvalueto, vars.getLanguage(), initRecordNumber, intRecordRangeUsed);
+ getDocumentNo(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
+ strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom, strcelementvalueto,
+ vars.getLanguage(), initRecordNumber, intRecordRangeUsed);
if (data != null && data.length > 0)
strPosition = ReportGeneralLedgerJournalData.selectCount(this,
Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"),
Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
strDateFrom, DateTimeData.nDaysAfter(this, strDateTo, "1"), strDocument,
- strcAcctSchemaId, strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom,
- strcelementvalueto, data[0].dateacct, data[0].identifier);
+ getDocumentNo(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
+ strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom, strcelementvalueto,
+ data[0].dateacct, data[0].identifier);
} else {
data = ReportGeneralLedgerJournalData.selectDirect(this,
"Y".equals(strShowDescription) ? "'Y'" : "'N'",
@@ -654,6 +668,7 @@
vars.setSessionValue("ReportGeneralLedgerJournal|Record", strRecord);
vars.setSessionValue("ReportGeneralLedgerJournal|Table", strTable);
xmlDocument.setParameter("inpPageNo", strPageNo);
+ xmlDocument.setParameter("inpDocumentNo", strDocumentNo);
xmlDocument.setParameter("inpEntryNo", strEntryNo);
// If none of the "show" flags is active, then regular is checked
xmlDocument.setParameter("showRegular", ("".equals(strShowRegular)) ? "N" : strShowRegular);
@@ -686,11 +701,11 @@
private void printPagePDF(HttpServletRequest request, HttpServletResponse response,
VariablesSecureApp vars, String strDateFrom, String strDateTo, String strDocument,
- String strOrg, String strTable, String strRecord, String strFactAcctGroupId,
- String strcAcctSchemaId, String strShowClosing, String strShowReg, String strShowOpening,
- String strPageNo, String strEntryNo, String strShowDescription, String strShowRegular,
- String strShowDivideUp, String strcelementvaluefrom, String strcelementvalueto)
- throws IOException, ServletException {
+ String strDocumentNo, String strOrg, String strTable, String strRecord,
+ String strFactAcctGroupId, String strcAcctSchemaId, String strShowClosing, String strShowReg,
+ String strShowOpening, String strPageNo, String strEntryNo, String strShowDescription,
+ String strShowRegular, String strShowDivideUp, String strcelementvaluefrom,
+ String strcelementvalueto) throws IOException, ServletException {
ReportGeneralLedgerJournalData[] data = null;
@@ -711,7 +726,8 @@
data = ReportGeneralLedgerJournalData.select(this, "Y".equals(strShowDescription) ? "'Y'"
: "'N'", Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"), Utility
.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), strDateFrom,
- DateTimeData.nDaysAfter(this, strDateTo, "1"), strDocument, strcAcctSchemaId,
+ DateTimeData.nDaysAfter(this, strDateTo, "1"), strDocument,
+ getDocumentNo(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom, strcelementvalueto, vars
.getLanguage());
} else
@@ -894,6 +910,35 @@
}
+ private String getDocumentNo(String strClient, String strDocument, String strDocumentNo) {
+ if (StringUtils.isEmpty(strDocument) || StringUtils.isEmpty(strDocumentNo)) {
+ return null;
+ } else {
+ String documentNo = StringEscapeUtils.escapeSql(strDocumentNo);
+ documentNo = documentNo.replaceAll(";", "");
+ String string = "( SELECT 1 FROM ";
+
+ StringBuffer where = new StringBuffer();
+ where.append(" select t." + Table.PROPERTY_DBTABLENAME);
+ where.append(" from " + DocumentType.ENTITY_NAME + " as d");
+ where.append(" join d." + DocumentType.PROPERTY_TABLE + " as t");
+ where.append(" where d." + DocumentType.PROPERTY_DOCUMENTCATEGORY + " = :document");
+ where.append(" and d." + DocumentType.PROPERTY_CLIENT + " = :client");
+ where.append(" group by d." + DocumentType.PROPERTY_DOCUMENTCATEGORY);
+ where.append(" , t." + Table.PROPERTY_DBTABLENAME);
+ Query qry = OBDal.getInstance().getSession().createQuery(where.toString());
+ qry.setMaxResults(1);
+ qry.setParameter("document", strDocument);
+ qry.setParameter("client", OBDal.getInstance().get(Client.class, strClient));
+ String tablename = (String) qry.uniqueResult();
+
+ string += tablename;
+ string += " dt WHERE record_id = dt." + tablename + "_id";
+ string += " AND dt.documentno = '" + documentNo + "' )";
+ return string;
+ }
+ }
+
@Override
public String getServletInfo() {
return "Servlet ReportGeneralLedgerJournal. This Servlet was made by Pablo Sarobe modified by everybody";
diff --git a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.xml b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.xml
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.xml
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.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-2014 Openbravo SLU
+* All portions are Copyright (C) 2001-2015 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************
@@ -47,6 +47,7 @@
<PARAMETER id="inpParamschemas" name="cAcctschemas" attribute="value" default=""/>
<PARAMETER id="posted" name="posted" attribute="value" default=""/>
<PARAMETER id="paramPageNo" name="inpPageNo" attribute="value" default="1"/>
+ <PARAMETER id="paramDocumentNo" name="inpDocumentNo" attribute="value" default=""/>
<PARAMETER id="paramEntryNo" name="inpEntryNo" attribute="value" default="1"/>
<PARAMETER id="fieldCheck" name="check" default="Y"/>
<PARAMETER id="paramShowRegular" name="showRegular" boolean="checked" withId="fieldCheck"/>
diff --git a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_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-2014 Openbravo SLU
+ * All portions are Copyright (C) 2001-2015 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************
@@ -70,6 +70,7 @@
<Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct >= TO_DATE(?)]]></Parameter>
<Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct < TO_DATE(?)]]></Parameter>
<Parameter name="docbasetype" optional="true" after="AND 1=1"><![CDATA[ AND f.DOCBASETYPE = ?]]></Parameter>
+ <Parameter name="documentNo" optional="true" after="AND 1=1" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
<Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
<Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/>
<Parameter name="checks" type="replace" optional="true" after="AND F.FactAcctType IN (" text="'C','N','O','R','D'"/>
@@ -81,6 +82,7 @@
<Parameter name="parDateFrom" optional="true" after="AND 3=3"><![CDATA[ AND dateacct >= TO_DATE(?)]]></Parameter>
<Parameter name="parDateTo" optional="true" after="AND 3=3"><![CDATA[ AND dateacct < TO_DATE(?)]]></Parameter>
<Parameter name="docbasetype" optional="true" after="AND 3=3"><![CDATA[ AND DOCBASETYPE = ?]]></Parameter>
+ <Parameter name="documentNo" optional="true" after="AND 3=3" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
<Parameter name="acctschema" optional="true" after="AND 3=3"><![CDATA[ AND C_ACCTSCHEMA_ID = ?]]></Parameter>
<Parameter name="orgFamily" type="replace" optional="true" after="AND AD_ORG_ID IN(" text="'4'"/>
<Parameter name="checks" type="replace" optional="true" after="AND FactAcctType IN (" text="'C','N','O','R','D'"/>
@@ -137,6 +139,7 @@
<Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct >= TO_DATE(?)]]></Parameter>
<Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct < TO_DATE(?)]]></Parameter>
<Parameter name="docbasetype" optional="true" after="AND 1=1"><![CDATA[ AND f.DOCBASETYPE = ?]]></Parameter>
+ <Parameter name="documentNo" optional="true" after="AND 1=1" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
<Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
<Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/>
<Parameter name="checks" type="replace" optional="true" after="AND F.FactAcctType IN (" text="'C','N','O','R','D'"/>
@@ -148,6 +151,7 @@
<Parameter name="parDateFrom" optional="true" after="AND 3=3"><![CDATA[ AND dateacct >= TO_DATE(?)]]></Parameter>
<Parameter name="parDateTo" optional="true" after="AND 3=3"><![CDATA[ AND dateacct < TO_DATE(?)]]></Parameter>
<Parameter name="docbasetype" optional="true" after="AND 3=3"><![CDATA[ AND DOCBASETYPE = ?]]></Parameter>
+ <Parameter name="documentNo" optional="true" after="AND 3=3" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
<Parameter name="acctschema" optional="true" after="AND 3=3"><![CDATA[ AND C_ACCTSCHEMA_ID = ?]]></Parameter>
<Parameter name="orgFamily" type="replace" optional="true" after="AND AD_ORG_ID IN(" text="'4'"/>
<Parameter name="checks" type="replace" optional="true" after="AND FactAcctType IN (" text="'C','N','O','R','D'"/>
@@ -193,6 +197,7 @@
<Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct >= TO_DATE(?)]]></Parameter>
<Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct < TO_DATE(?)]]></Parameter>
<Parameter name="docbasetype" optional="true" after="AND 1=1"><![CDATA[ AND f.DOCBASETYPE = ?]]></Parameter>
+ <Parameter name="documentNo" optional="true" after="AND 1=1" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
<Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
<Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/>
<Parameter name="checks" type="replace" optional="true" after="AND F.FactAcctType IN (" text="'C','N','O','R','D'"/>
@@ -204,6 +209,7 @@
<Parameter name="parDateFrom" optional="true" after="AND 3=3"><![CDATA[ AND dateacct >= TO_DATE(?)]]></Parameter>
<Parameter name="parDateTo" optional="true" after="AND 3=3"><![CDATA[ AND dateacct < TO_DATE(?)]]></Parameter>
<Parameter name="docbasetype" optional="true" after="AND 3=3"><![CDATA[ AND DOCBASETYPE = ?]]></Parameter>
+ <Parameter name="documentNo" optional="true" after="AND 3=3" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
<Parameter name="acctschema" optional="true" after="AND 3=3"><![CDATA[ AND C_ACCTSCHEMA_ID = ?]]></Parameter>
<Parameter name="orgFamily" type="replace" optional="true" after="AND AD_ORG_ID IN(" text="'4'"/>
<Parameter name="checks" type="replace" optional="true" after="AND FactAcctType IN (" text="'C','N','O','R','D'"/>
# HG changeset patch
# User Víctor Martínez Romanos <victor.martinez@openbravo.com>
# Date 1442318533 -7200
# Tue Sep 15 14:02:13 2015 +0200
# Node ID f9fc2cc0702694e0f1874ebcc7fad5cb6d0deccd
# Parent 90139faa63bb70b773c3b022e47b27edd4f8cf03
Related to issue 30788: code review improvements
Minor modifications:
+ Run query in admin mode
+ Usage of StringUtils.isBlank to control whitespace
+ Usage of StringBuffer to avoid String concatenation
+ Control any possible exception and return null to avoid breaking the report
+ Avoid unnecessary indentation (removed else)
+ Avoid the need of getting the Client object and using directly the id instead
+ Exists clause removed from subquery related to accounts from/to filter because it's useless and affects performance
+ Set the right param to djConfig.searchIds.push
diff --git a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.html b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.html
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.html
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.html
@@ -596,7 +596,7 @@
</tr>
<tr id=advancedFiltersrow2>
<td class="TitleCell"><span class="LabelText">Document No.</span></td>
- <td class="TextBox_ContentCell"><input dojoType="openbravo:Textbox" class="dojoValidateValid TextBox_OneCell_width" type="text" name="inpDocumentNo" id="paramDocumentNo" size="10" maxlength="10" value=""></input><script>djConfig.searchIds.push("paramPageNo");</script></td>
+ <td class="TextBox_ContentCell"><input dojoType="openbravo:Textbox" class="dojoValidateValid TextBox_OneCell_width" type="text" name="inpDocumentNo" id="paramDocumentNo" size="10" maxlength="10" value=""></input><script>djConfig.searchIds.push("paramDocumentNo");</script></td>
</tr>
<tr id=advancedFiltersrow3>
<td class="TitleCell"><span class="LabelText">Initial Entry Number</span></td>
diff --git a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.java b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.java
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.java
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.java
@@ -58,7 +58,6 @@
import org.openbravo.erpCommon.utility.ToolBar;
import org.openbravo.erpCommon.utility.Utility;
import org.openbravo.model.ad.datamodel.Table;
-import org.openbravo.model.ad.system.Client;
import org.openbravo.model.common.enterprise.DocumentType;
import org.openbravo.model.financialmgmt.accounting.coa.AcctSchema;
import org.openbravo.model.financialmgmt.accounting.coa.AcctSchemaTable;
@@ -910,32 +909,46 @@
}
+ /**
+ * Builds dynamic SQL to filter by document No
+ */
private String getDocumentNo(String strClient, String strDocument, String strDocumentNo) {
- if (StringUtils.isEmpty(strDocument) || StringUtils.isEmpty(strDocumentNo)) {
+ if (StringUtils.isBlank(strDocument) || StringUtils.isBlank(strDocumentNo)) {
return null;
- } else {
+ }
+
+ try {
+ OBContext.setAdminMode();
String documentNo = StringEscapeUtils.escapeSql(strDocumentNo);
documentNo = documentNo.replaceAll(";", "");
- String string = "( SELECT 1 FROM ";
StringBuffer where = new StringBuffer();
where.append(" select t." + Table.PROPERTY_DBTABLENAME);
where.append(" from " + DocumentType.ENTITY_NAME + " as d");
where.append(" join d." + DocumentType.PROPERTY_TABLE + " as t");
where.append(" where d." + DocumentType.PROPERTY_DOCUMENTCATEGORY + " = :document");
- where.append(" and d." + DocumentType.PROPERTY_CLIENT + " = :client");
+ where.append(" and d." + DocumentType.PROPERTY_CLIENT + ".id = :client");
where.append(" group by d." + DocumentType.PROPERTY_DOCUMENTCATEGORY);
where.append(" , t." + Table.PROPERTY_DBTABLENAME);
Query qry = OBDal.getInstance().getSession().createQuery(where.toString());
qry.setMaxResults(1);
qry.setParameter("document", strDocument);
- qry.setParameter("client", OBDal.getInstance().get(Client.class, strClient));
+ qry.setParameter("client", strClient);
String tablename = (String) qry.uniqueResult();
- string += tablename;
- string += " dt WHERE record_id = dt." + tablename + "_id";
- string += " AND dt.documentno = '" + documentNo + "' )";
- return string;
+ if (StringUtils.isBlank(tablename)) {
+ return null;
+ }
+
+ StringBuffer existsSubQuery = new StringBuffer("( SELECT 1 FROM ");
+ existsSubQuery.append(tablename);
+ existsSubQuery.append(" dt WHERE f.record_id = dt.").append(tablename).append("_id");
+ existsSubQuery.append(" AND dt.documentno = '").append(documentNo).append("' )");
+ return existsSubQuery.toString();
+ } catch (Exception ignore) {
+ return null;
+ } finally {
+ OBContext.restorePreviousMode();
}
}
diff --git a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql
@@ -82,7 +82,6 @@
<Parameter name="parDateFrom" optional="true" after="AND 3=3"><![CDATA[ AND dateacct >= TO_DATE(?)]]></Parameter>
<Parameter name="parDateTo" optional="true" after="AND 3=3"><![CDATA[ AND dateacct < TO_DATE(?)]]></Parameter>
<Parameter name="docbasetype" optional="true" after="AND 3=3"><![CDATA[ AND DOCBASETYPE = ?]]></Parameter>
- <Parameter name="documentNo" optional="true" after="AND 3=3" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
<Parameter name="acctschema" optional="true" after="AND 3=3"><![CDATA[ AND C_ACCTSCHEMA_ID = ?]]></Parameter>
<Parameter name="orgFamily" type="replace" optional="true" after="AND AD_ORG_ID IN(" text="'4'"/>
<Parameter name="checks" type="replace" optional="true" after="AND FactAcctType IN (" text="'C','N','O','R','D'"/>
@@ -151,7 +150,6 @@
<Parameter name="parDateFrom" optional="true" after="AND 3=3"><![CDATA[ AND dateacct >= TO_DATE(?)]]></Parameter>
<Parameter name="parDateTo" optional="true" after="AND 3=3"><![CDATA[ AND dateacct < TO_DATE(?)]]></Parameter>
<Parameter name="docbasetype" optional="true" after="AND 3=3"><![CDATA[ AND DOCBASETYPE = ?]]></Parameter>
- <Parameter name="documentNo" optional="true" after="AND 3=3" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
<Parameter name="acctschema" optional="true" after="AND 3=3"><![CDATA[ AND C_ACCTSCHEMA_ID = ?]]></Parameter>
<Parameter name="orgFamily" type="replace" optional="true" after="AND AD_ORG_ID IN(" text="'4'"/>
<Parameter name="checks" type="replace" optional="true" after="AND FactAcctType IN (" text="'C','N','O','R','D'"/>
@@ -209,7 +207,6 @@
<Parameter name="parDateFrom" optional="true" after="AND 3=3"><![CDATA[ AND dateacct >= TO_DATE(?)]]></Parameter>
<Parameter name="parDateTo" optional="true" after="AND 3=3"><![CDATA[ AND dateacct < TO_DATE(?)]]></Parameter>
<Parameter name="docbasetype" optional="true" after="AND 3=3"><![CDATA[ AND DOCBASETYPE = ?]]></Parameter>
- <Parameter name="documentNo" optional="true" after="AND 3=3" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
<Parameter name="acctschema" optional="true" after="AND 3=3"><![CDATA[ AND C_ACCTSCHEMA_ID = ?]]></Parameter>
<Parameter name="orgFamily" type="replace" optional="true" after="AND AD_ORG_ID IN(" text="'4'"/>
<Parameter name="checks" type="replace" optional="true" after="AND FactAcctType IN (" text="'C','N','O','R','D'"/>
|
Relationships [ Relation Graph ]
[ Dependency Graph ]
Relationships 