0029215: Be able to consolidate the information for all the warehouses

diff --git a/src-db/database/sourcedata/AD_MENU.xml b/src-db/database/sourcedata/AD_MENU.xml --- a/src-db/database/sourcedata/AD_MENU.xml +++ b/src-db/database/sourcedata/AD_MENU.xml @@ -11,6 +11,7 @@  <ACTION><![CDATA[R]]></ACTION>  <AD_PROCESS_ID><![CDATA[BB94F766AB40447BBF57A9A05F486DF1]]></AD_PROCESS_ID>  <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> + <OPENLINKINBROWSER><![CDATA[N]]></OPENLINKINBROWSER> </AD_MENU> </data> diff --git a/src-db/database/sourcedata/AD_MODEL_OBJECT_MAPPING.xml b/src-db/database/sourcedata/AD_MODEL_OBJECT_MAPPING.xml --- a/src-db/database/sourcedata/AD_MODEL_OBJECT_MAPPING.xml +++ b/src-db/database/sourcedata/AD_MODEL_OBJECT_MAPPING.xml @@ -1,5 +1,25 @@ <?xml version='1.0' encoding='UTF-8'?> <data> +<AD_MODEL_OBJECT_MAPPING> + <AD_MODEL_OBJECT_MAPPING_ID><![CDATA[1E5CC18FF2914D8E83323EC1917E9C1C]]></AD_MODEL_OBJECT_MAPPING_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <AD_MODEL_OBJECT_ID><![CDATA[5DC1138145CD4F53840CE6CF63E6862E]]></AD_MODEL_OBJECT_ID> + <MAPPINGNAME><![CDATA[/org.openbravo.warehouse.reportvaluedstockenhanced/ReportValuedStockEnhanced.xls]]></MAPPINGNAME> + <ISDEFAULT><![CDATA[N]]></ISDEFAULT> +</AD_MODEL_OBJECT_MAPPING> + +<AD_MODEL_OBJECT_MAPPING> + <AD_MODEL_OBJECT_MAPPING_ID><![CDATA[2EE99E12968B4EC9B4B2C205DF956991]]></AD_MODEL_OBJECT_MAPPING_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <AD_MODEL_OBJECT_ID><![CDATA[5DC1138145CD4F53840CE6CF63E6862E]]></AD_MODEL_OBJECT_ID> + <MAPPINGNAME><![CDATA[/org.openbravo.warehouse.reportvaluedstockenhanced/ReportValuedStockEnhanced.pdf]]></MAPPINGNAME> + <ISDEFAULT><![CDATA[N]]></ISDEFAULT> +</AD_MODEL_OBJECT_MAPPING> + <AD_MODEL_OBJECT_MAPPING>  <AD_MODEL_OBJECT_MAPPING_ID><![CDATA[FFF2728E5CF04772963829A2AF26310B]]></AD_MODEL_OBJECT_MAPPING_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> diff --git a/src-db/database/sourcedata/AD_MODULE.xml b/src-db/database/sourcedata/AD_MODULE.xml --- a/src-db/database/sourcedata/AD_MODULE.xml +++ b/src-db/database/sourcedata/AD_MODULE.xml @@ -6,7 +6,7 @@  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>  <ISACTIVE><![CDATA[Y]]></ISACTIVE>  <NAME><![CDATA[Report: Valued Stock Enhanced]]></NAME> - <VERSION><![CDATA[1.0.3]]></VERSION> + <VERSION><![CDATA[1.0.7]]></VERSION>  <DESCRIPTION><![CDATA[Report that shows the value of the stock of the warehouse]]></DESCRIPTION>  <TYPE><![CDATA[M]]></TYPE>  <LICENSE><![CDATA[Copyright (C) 2013 Openbravo S.L.U. diff --git a/src-db/database/sourcedata/AD_PROCESS.xml b/src-db/database/sourcedata/AD_PROCESS.xml --- a/src-db/database/sourcedata/AD_PROCESS.xml +++ b/src-db/database/sourcedata/AD_PROCESS.xml @@ -20,6 +20,7 @@  <UIPATTERN><![CDATA[M]]></UIPATTERN>  <ISADVANCEDFEATURE><![CDATA[N]]></ISADVANCEDFEATURE>  <PREVENTCONCURRENT><![CDATA[N]]></PREVENTCONCURRENT> + <IS_EXPLICIT_ACCESS><![CDATA[N]]></IS_EXPLICIT_ACCESS> </AD_PROCESS> </data> 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 @@ -1,5 +1,49 @@ <?xml version='1.0' encoding='UTF-8'?> <data> +<AD_TEXTINTERFACES> + <AD_TEXTINTERFACES_ID><![CDATA[1540C6A26070414488EB1D8E3DC23E28]]></AD_TEXTINTERFACES_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <TEXT><![CDATA[UNIT COST]]></TEXT> + <FILENAME><![CDATA[/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedExcel.jrxml]]></FILENAME> + <ISUSED><![CDATA[Y]]></ISUSED> + <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> +</AD_TEXTINTERFACES> + +<AD_TEXTINTERFACES> + <AD_TEXTINTERFACES_ID><![CDATA[22FDF8E2E8054B288B852AC1C013E4A5]]></AD_TEXTINTERFACES_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <TEXT><![CDATA[VALUATION]]></TEXT> + <FILENAME><![CDATA[/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedPDF.jrxml]]></FILENAME> + <ISUSED><![CDATA[Y]]></ISUSED> + <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> +</AD_TEXTINTERFACES> + +<AD_TEXTINTERFACES> + <AD_TEXTINTERFACES_ID><![CDATA[275E4CDF8A2240A6ADE7A509B8B97C16]]></AD_TEXTINTERFACES_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <TEXT><![CDATA[UNIT]]></TEXT> + <FILENAME><![CDATA[/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedExcel.jrxml]]></FILENAME> + <ISUSED><![CDATA[Y]]></ISUSED> + <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> +</AD_TEXTINTERFACES> + +<AD_TEXTINTERFACES> + <AD_TEXTINTERFACES_ID><![CDATA[3079BCBC37D34AFFA31615DB4CCF2D8D]]></AD_TEXTINTERFACES_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <TEXT><![CDATA[Date :]]></TEXT> + <FILENAME><![CDATA[/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedPDF.jrxml]]></FILENAME> + <ISUSED><![CDATA[Y]]></ISUSED> + <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> +</AD_TEXTINTERFACES> + <AD_TEXTINTERFACES>  <AD_TEXTINTERFACES_ID><![CDATA[3D5C80B91D4D4338824D07440BA7928B]]></AD_TEXTINTERFACES_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> @@ -22,6 +66,28 @@  <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> </AD_TEXTINTERFACES> +<AD_TEXTINTERFACES> + <AD_TEXTINTERFACES_ID><![CDATA[51B57D9DF9B64FE8BCEC68938A139389]]></AD_TEXTINTERFACES_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <TEXT><![CDATA[PRODUCT CATEGORY]]></TEXT> + <FILENAME><![CDATA[/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedExcel.jrxml]]></FILENAME> + <ISUSED><![CDATA[Y]]></ISUSED> + <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> +</AD_TEXTINTERFACES> + +<AD_TEXTINTERFACES> + <AD_TEXTINTERFACES_ID><![CDATA[5269948D29414A96A0B7A78F3845AF34]]></AD_TEXTINTERFACES_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <TEXT><![CDATA[ENHANCED VALUE STOCK REPORT]]></TEXT> + <FILENAME><![CDATA[/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedPDF.jrxml]]></FILENAME> + <ISUSED><![CDATA[Y]]></ISUSED> + <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> +</AD_TEXTINTERFACES> + <AD_TEXTINTERFACES>  <AD_TEXTINTERFACES_ID><![CDATA[58E95FF7CAF442E6A5D489968C5B1BAA]]></AD_TEXTINTERFACES_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> @@ -55,6 +121,17 @@  <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> </AD_TEXTINTERFACES> +<AD_TEXTINTERFACES> + <AD_TEXTINTERFACES_ID><![CDATA[6B7786A394BA4BFF94F2679CC1B9103D]]></AD_TEXTINTERFACES_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <TEXT><![CDATA[UNIT]]></TEXT> + <FILENAME><![CDATA[/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedPDF.jrxml]]></FILENAME> + <ISUSED><![CDATA[Y]]></ISUSED> + <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> +</AD_TEXTINTERFACES> + <AD_TEXTINTERFACES>  <AD_TEXTINTERFACES_ID><![CDATA[6ECEE1F1530646EB93B72A7087C00AD2]]></AD_TEXTINTERFACES_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> @@ -121,6 +198,28 @@  <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> </AD_TEXTINTERFACES> +<AD_TEXTINTERFACES> + <AD_TEXTINTERFACES_ID><![CDATA[A9D71A5290C74FF0B1432DCA6CD0DAB7]]></AD_TEXTINTERFACES_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <TEXT><![CDATA[UNIT COST]]></TEXT> + <FILENAME><![CDATA[/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedPDF.jrxml]]></FILENAME> + <ISUSED><![CDATA[Y]]></ISUSED> + <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> +</AD_TEXTINTERFACES> + +<AD_TEXTINTERFACES> + <AD_TEXTINTERFACES_ID><![CDATA[B3B98B96BFD94DDFB69226BEA2B7B532]]></AD_TEXTINTERFACES_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <TEXT><![CDATA[WAREHOUSE]]></TEXT> + <FILENAME><![CDATA[/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedExcel.jrxml]]></FILENAME> + <ISUSED><![CDATA[Y]]></ISUSED> + <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> +</AD_TEXTINTERFACES> + <AD_TEXTINTERFACES>  <AD_TEXTINTERFACES_ID><![CDATA[C2CE3833E89D4501926D671BC71782DF]]></AD_TEXTINTERFACES_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> @@ -132,4 +231,26 @@  <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> </AD_TEXTINTERFACES> +<AD_TEXTINTERFACES> + <AD_TEXTINTERFACES_ID><![CDATA[CE100F1FE129493DAF6794B53E531BEE]]></AD_TEXTINTERFACES_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <TEXT><![CDATA[VALUATION]]></TEXT> + <FILENAME><![CDATA[/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedExcel.jrxml]]></FILENAME> + <ISUSED><![CDATA[Y]]></ISUSED> + <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> +</AD_TEXTINTERFACES> + +<AD_TEXTINTERFACES> + <AD_TEXTINTERFACES_ID><![CDATA[CF9D8ACF8C78462EA23D0BFF070F58DA]]></AD_TEXTINTERFACES_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <TEXT><![CDATA[Enhanced Value Stock Report]]></TEXT> + <FILENAME><![CDATA[/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedExcel.jrxml]]></FILENAME> + <ISUSED><![CDATA[Y]]></ISUSED> + <AD_MODULE_ID><![CDATA[DB9AE8D87B0D43C68430D5BA415143FD]]></AD_MODULE_ID> +</AD_TEXTINTERFACES> + </data> diff --git a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.html b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.html --- a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.html +++ b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.html @@ -2,7 +2,7 @@ <!DOCTYPE html PUBLIC "-//W3C//DTD Xhtml 1.0 Transitional//EN" "http://www.w3.org/tr/xhtml1/Dtd/xhtml1-transitional.dtd">  </td> </tr> diff --git a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.java b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.java --- a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.java +++ b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.java @@ -1,46 +1,72 @@ /* - ************************************************************************* - * Copyright (C) 2013 Openbravo S.L.U. - * Licensed under the Openbravo Commercial License version 1.0 - * You may obtain a copy of the License at http://www.openbravo.com/legal/obcl.html - * or in the legal folder of this module distribution. - ************************************************************************ - */ +************************************************************************* +* Copyright (C) 2013 - 2015 Openbravo S.L.U. +* Licensed under the Openbravo Commercial License version 1.0 +* You may obtain a copy of the License at http://www.openbravo.com/legal/obcl.html +* or in the legal folder of this module distribution. +************************************************************************ +*/ package org.openbravo.warehouse.reportvaluedstockenhanced; +import java.io.FileOutputStream; import java.io.IOException; import java.io.PrintWriter; +import java.math.BigDecimal; +import java.text.DecimalFormat; +import java.text.DecimalFormatSymbols; +import java.util.ArrayList; +import java.util.Collection; +import java.util.HashMap; +import java.util.Iterator; +import java.util.Locale; +import java.util.Vector; import javax.servlet.ServletException; +import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; +import net.sf.jasperreports.engine.JRException; +import net.sf.jasperreports.engine.JasperExportManager; +import net.sf.jasperreports.engine.JasperFillManager; +import net.sf.jasperreports.engine.JasperPrint; +import net.sf.jasperreports.engine.JasperReport; + import org.openbravo.base.secureApp.HttpSecureAppServlet; import org.openbravo.base.secureApp.VariablesSecureApp; import org.openbravo.costing.CostingStatus; -import org.openbravo.dal.core.OBContext; import org.openbravo.dal.service.OBDal; import org.openbravo.dal.service.OBQuery; +import org.openbravo.data.FieldProvider; import org.openbravo.erpCommon.businessUtility.WindowTabs; import org.openbravo.erpCommon.utility.ComboTableData; import org.openbravo.erpCommon.utility.DateTimeData; +import org.openbravo.erpCommon.utility.JRFieldProviderDataSource; import org.openbravo.erpCommon.utility.LeftTabsBar; import org.openbravo.erpCommon.utility.NavigationBar; import org.openbravo.erpCommon.utility.OBDateUtils; import org.openbravo.erpCommon.utility.OBError; import org.openbravo.erpCommon.utility.OBMessageUtils; +import org.openbravo.erpCommon.utility.PrintJRData; import org.openbravo.erpCommon.utility.ToolBar; import org.openbravo.erpCommon.utility.Utility; import org.openbravo.model.common.enterprise.Locator; import org.openbravo.model.common.enterprise.Organization; -import org.openbravo.model.common.enterprise.Warehouse; import org.openbravo.model.common.plm.Product; import org.openbravo.model.materialmgmt.cost.CostingAlgorithm; import org.openbravo.model.materialmgmt.cost.CostingRule; import org.openbravo.model.materialmgmt.transaction.MaterialTransaction; +import org.openbravo.utils.FileUtility; +import org.openbravo.utils.Replace; import org.openbravo.xmlEngine.XmlDocument; +import com.lowagie.text.Document; +import com.lowagie.text.DocumentException; +import com.lowagie.text.pdf.PdfCopy; +import com.lowagie.text.pdf.PdfImportedPage; +import com.lowagie.text.pdf.PdfReader; + public class ReportValuedStockEnhanced extends HttpSecureAppServlet { private static final long serialVersionUID = 1L; @@ -50,41 +76,76 @@ // Get user Client's base currency String strUserCurrencyId = Utility.stringBaseCurrencyId(this, vars.getClient()); + String strOrganization = null; if (vars.commandIn("DEFAULT", "RELATION")) { String strDate = vars.getGlobalVariable("inpDate", "ReportValuedStockEnhanced|Date", DateTimeData.today(this)); + + strOrganization = vars.getGlobalVariable("inpOrg", "ReportValuedStockEnhanced|Organization", + ""); + String strWarehouse = vars.getGlobalVariable("inpmWarehouseId", "ReportValuedStockEnhanced|Warehouse", ""); String strCategoryProduct = vars.getGlobalVariable("inpCategoryProduct", "ReportValuedStockEnhanced|CategoryProduct", ""); String strCurrencyId = vars.getGlobalVariable("inpCurrencyId", "ReportValuedStockEnhanced|currency", strUserCurrencyId); - printPageDataSheet(request, response, vars, strDate, strWarehouse, strCategoryProduct, - strCurrencyId); + String strWarehouseConsolidation = vars.getGlobalVariable("inpWarehouseConsolidation", + "ReportValuedStockEnhanced|warehouseConsolidation", ""); + printPageDataSheet(request, response, vars, strDate, strOrganization, strWarehouse, + strCategoryProduct, strCurrencyId, strWarehouseConsolidation); } else if (vars.commandIn("FIND")) { String strDate = vars.getGlobalVariable("inpDate", "ReportValuedStockEnhanced|Date", DateTimeData.today(this)); + + strOrganization = vars.getGlobalVariable("inpOrg", "ReportValuedStockEnhanced|Organization"); + String strWarehouse = vars.getRequestGlobalVariable("inpmWarehouseId", "ReportValuedStockEnhanced|Warehouse"); String strCategoryProduct = vars.getRequestGlobalVariable("inpCategoryProduct", "ReportValuedStockEnhanced|CategoryProduct"); String strCurrencyId = vars.getGlobalVariable("inpCurrencyId", "ReportValuedStockEnhanced|currency", strUserCurrencyId); - printPageDataSheet(request, response, vars, strDate, strWarehouse, strCategoryProduct, - strCurrencyId); + String strWarehouseConsolidation = vars.getRequestGlobalVariable("inpWarehouseConsolidation", + "ReportValuedStockEnhanced|warehouseConsolidation"); + printPageDataSheet(request, response, vars, strDate, strOrganization, strWarehouse, + strCategoryProduct, strCurrencyId, strWarehouseConsolidation); + } else if (vars.commandIn("PDF", "XLS")) { + String strDate = vars.getGlobalVariable("inpDate", "ReportValuedStockEnhanced|Date", + DateTimeData.today(this)); + strOrganization = vars.getGlobalVariable("inpOrg", "ReportValuedStockEnhanced|Organization"); + String strWarehouse = vars.getRequestGlobalVariable("inpmWarehouseId", + "ReportValuedStockEnhanced|Warehouse"); + String strCategoryProduct = vars.getRequestGlobalVariable("inpCategoryProduct", + "ReportValuedStockEnhanced|CategoryProduct"); + String strCurrencyId = vars.getGlobalVariable("inpCurrencyId", + "ReportValuedStockEnhanced|currency", strUserCurrencyId); + String strWarehouseConsolidation = vars.getRequestGlobalVariable("inpWarehouseConsolidation", + "ReportValuedStockEnhanced|warehouseConsolidation"); + if (vars.commandIn("PDF")) + printPageDataPDF(request, response, vars, strDate, strOrganization, strWarehouse, + strCategoryProduct, strCurrencyId, strWarehouseConsolidation); + else + printPageDataXLS(request, response, vars, strDate, strOrganization, strWarehouse, + strCategoryProduct, strCurrencyId, strWarehouseConsolidation); } else pageError(response); } + // ++++ private void printPageDataSheet(HttpServletRequest request, HttpServletResponse response, - VariablesSecureApp vars, String strDate, String strWarehouse, String strCategoryProduct, - String strCurrencyId) throws IOException, ServletException { + VariablesSecureApp vars, String strDate, String strOrganization, String strWarehouse, + String strCategoryProduct, String strCurrencyId, String strWarehouseConsolidation) + throws IOException, ServletException { if (log4j.isDebugEnabled()) log4j.debug("Output: dataSheet"); response.setContentType("text/html; charset=UTF-8"); PrintWriter out = response.getWriter(); - String discard[] = { "discard" }; + String discard[] = { "discard", "discard1" }; XmlDocument xmlDocument; + boolean isWarehouseConsolidation = false; + if (strWarehouseConsolidation.equals("on")) + isWarehouseConsolidation = true; // Checks if there is a conversion rate for each of the transactions of // the report @@ -97,15 +158,10 @@ Utility.messageBD(this, "NotUsingNewCost", vars.getLanguage()), ""); return; } - Warehouse wh = null; + Organization legalEntity = null; try { - if (strWarehouse != null && !strWarehouse.isEmpty()) { - wh = OBDal.getInstance().get(Warehouse.class, strWarehouse); - legalEntity = OBContext.getOBContext() - .getOrganizationStructureProvider(wh.getClient().getId()) - .getLegalEntity(wh.getOrganization()); - } + legalEntity = OBDal.getInstance().get(Organization.class, strOrganization); } catch (Exception e) { throw new ServletException(e); } @@ -118,32 +174,29 @@ Utility.messageBD(this, "WarehouseNotInLE", vars.getLanguage()), ""); } ca = getLEsCostingAlgortithm(legalEntity); - String compare = DateTimeData.compare(this, strDate, - ReportValuedStockEnhancedData.getCostingMigrationDate(this, wh.getClient().getId())); - String avCostType; - String stdCostType; - if (compare.equals("-1")) { - avCostType = "AV"; - stdCostType = "ST"; + if (ca != null) { + if (!isWarehouseConsolidation) + data = ReportValuedStockEnhancedData.select(this, vars.getLanguage(), strCurrencyId, + DateTimeData.nDaysAfter(this, strDate, "1"), (legalEntity == null) ? null + : legalEntity.getId(), strOrganization, strWarehouse, strCategoryProduct); + else + data = ReportValuedStockEnhancedData.selectClusteredByWarehouse(this, + vars.getLanguage(), strCurrencyId, DateTimeData.nDaysAfter(this, strDate, "1"), + (legalEntity == null) ? null : legalEntity.getId(), strOrganization, strWarehouse, + strCategoryProduct); + } else { - avCostType = "AVA"; - stdCostType = "STA"; - } - if (ca.getJavaClassName().equalsIgnoreCase("org.openbravo.costing.AverageAlgorithm") - || ca.getJavaClassName().equalsIgnoreCase( - "org.openbravo.costing.algorithm.averageperinvoice.AveragePerInvoice")) { - data = ReportValuedStockEnhancedData.select(this, vars.getLanguage(), strCurrencyId, - DateTimeData.nDaysAfter(this, strDate, "1"), (legalEntity == null) ? null - : legalEntity.getId(), strWarehouse, avCostType, strCategoryProduct); - } else if (ca.getJavaClassName() - .equalsIgnoreCase("org.openbravo.costing.StandardAlgorithm")) { - data = ReportValuedStockEnhancedData.select(this, vars.getLanguage(), strCurrencyId, - DateTimeData.nDaysAfter(this, strDate, "1"), (legalEntity == null) ? null - : legalEntity.getId(), strWarehouse, stdCostType, strCategoryProduct); - } else { - data = ReportValuedStockEnhancedData.selectWithoutCost(this, vars.getLanguage(), - strCurrencyId, (legalEntity == null) ? null : legalEntity.getId(), - DateTimeData.nDaysAfter(this, strDate, "1"), strWarehouse, strCategoryProduct); + if (!isWarehouseConsolidation) + data = ReportValuedStockEnhancedData.selectWithoutCost(this, vars.getLanguage(), + strCurrencyId, (legalEntity == null) ? null : legalEntity.getId(), + DateTimeData.nDaysAfter(this, strDate, "1"), strOrganization, strWarehouse, + strCategoryProduct); + else + data = ReportValuedStockEnhancedData.selectClusteredByWarehouseWithoutCost(this, + vars.getLanguage(), strCurrencyId, + (legalEntity == null) ? null : legalEntity.getId(), + DateTimeData.nDaysAfter(this, strDate, "1"), strOrganization, strWarehouse, + strCategoryProduct); } boolean hasTrxWithNoCost = hasTrxWithNoCost(strDate, strWarehouse, strCategoryProduct); if (hasTrxWithNoCost) { @@ -168,34 +221,17 @@ if (data == null || data.length == 0) { data = ReportValuedStockEnhancedData.set(); discard[0] = "sectionCategoryProduct"; + discard[1] = "sectionWarehouse"; } + xmlDocument = xmlEngine.readXmlTemplate( + "org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced", discard) + .createXmlDocument(); - if (vars.commandIn("DEFAULT")) { - discard[0] = "sectionCategoryProduct"; - xmlDocument = xmlEngine.readXmlTemplate( - "org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced", discard) - .createXmlDocument(); - data = ReportValuedStockEnhancedData.set(); - } else { - - if (ca.getJavaClassName().equalsIgnoreCase("org.openbravo.costing.AverageAlgorithm") - || ca.getJavaClassName().equalsIgnoreCase( - "org.openbravo.costing.algorithm.averageperinvoice.AveragePerInvoice") - || ca.getJavaClassName().equalsIgnoreCase("org.openbravo.costing.StandardAlgorithm")) { - xmlDocument = xmlEngine.readXmlTemplate( - "org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced", - discard).createXmlDocument(); - } else { - xmlDocument = xmlEngine.readXmlTemplate( - "org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced2", - discard).createXmlDocument(); - } - - } - - ToolBar toolbar = new ToolBar(this, vars.getLanguage(), "ReportValuedStockEnhanced", false, - "", "", "", false, "ad_reports", strReplaceWith, false, true); - toolbar.prepareSimpleToolBarTemplate(); + ToolBar toolbar = new ToolBar(this, vars.getLanguage(), "ReportValuedStockEnhanced", true, + "", "", "imprimir();return false;", false, "ad_reports", strReplaceWith, false, true); + toolbar + .prepareRelationBarTemplate(false, false, + "submitCommandForm('XLS', false, frmMain, 'ReportValuedStockEnhanced.xls', 'EXCEL');return false;"); xmlDocument.setParameter("toolbar", toolbar.toString()); try { @@ -224,15 +260,35 @@ xmlDocument.setParameter("messageMessage", myMessage.getMessage()); } } - + xmlDocument.setParameter("mWarehouseId", strWarehouse); xmlDocument.setParameter("calendar", vars.getLanguage().substring(0, 2)); xmlDocument.setParameter("directory", "var baseDirectory = \"" + strReplaceWith + "/\";\n"); xmlDocument.setParameter("paramLanguage", "defaultLang=\"" + vars.getLanguage() + "\";"); xmlDocument.setParameter("date", strDate); + xmlDocument.setParameter("adOrgId", strOrganization); + if (isWarehouseConsolidation) + xmlDocument.setParameter("warehouseConsolidation", ""); + else + xmlDocument.setParameter("warehouseConsolidation", "on"); xmlDocument.setParameter("dateFromdisplayFormat", vars.getSessionValue("#AD_SqlDateFormat")); xmlDocument.setParameter("dateFromsaveFormat", vars.getSessionValue("#AD_SqlDateFormat")); - xmlDocument.setParameter("mWarehouseId", strWarehouse); + xmlDocument.setParameter("categoryProduct", strCategoryProduct); + + try { + ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "AD_Org_ID", "", + "D4DF252DEC3B44858454EE5292A8B836", Utility.getContext(this, vars, + "#AccessibleOrgTree", "ReportValuedStockEnhanced"), Utility.getContext(this, vars, + "#User_Client", "ReportValuedStockEnhanced"), 0); + Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportValuedStockEnhanced", + strOrganization); + xmlDocument.setData("reportAD_ORGID", "liststructure", comboTableData.select(false)); + comboTableData = null; + + } catch (Exception ex) { + throw new ServletException(ex); + } + try { ComboTableData comboTableData = new ComboTableData(vars, this, "TABLE", "M_Warehouse_ID", "M_Warehouse of Client", "", Utility.getContext(this, vars, "#AccessibleOrgTree", ""), @@ -243,6 +299,13 @@ } catch (Exception ex) { throw new ServletException(ex); } + xmlDocument.setParameter( + "warehouses", + Utility.arrayDobleEntrada("arrWh", + ReportValuedStockEnhancedData.selectWhsDouble(this, vars.getClient()))); + xmlDocument.setParameter("warehouseID", strWarehouse); + xmlDocument.setParameter("whwh", strWarehouse); + try { ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "M_Product_Category_ID", "", "", Utility.getContext(this, vars, "#AccessibleOrgTree", @@ -269,26 +332,31 @@ throw new ServletException(ex); } - if (strWarehouse != null && !strWarehouse.isEmpty()) { - if (ca != null - && (ca.getJavaClassName().equalsIgnoreCase("org.openbravo.costing.AverageAlgorithm") || ca - .getJavaClassName().equalsIgnoreCase( - "org.openbravo.costing.algorithm.averageperinvoice.AveragePerInvoice"))) { - xmlDocument.setParameter("costTypeCol", "AVERAGE COST"); - xmlDocument.setParameter("valuationTypeCol", "AVERAGE VALUATION"); - xmlDocument.setData("structure1", data); - } else if (ca != null - && ca.getJavaClassName().equalsIgnoreCase("org.openbravo.costing.StandardAlgorithm")) { - xmlDocument.setParameter("costTypeCol", "STANDARD COST"); - xmlDocument.setParameter("valuationTypeCol", "STANDARD VALUATION"); + if (data != null && data.length > 0) { + if (ca != null) { + xmlDocument.setParameter("costTypeCol", ca.getName() + " COST"); + xmlDocument.setParameter("valuationTypeCol", ca.getName() + " VALUATION"); xmlDocument.setData("structure1", data); } else { + xmlDocument.setParameter("costTypeCol", ""); + xmlDocument.setParameter("valuationTypeCol", ""); xmlDocument.setData("structure1", data); } + } else { + if (vars.commandIn("FIND")) { + // No data has been found. Show warning message. + xmlDocument.setParameter("messageType", "WARNING"); + xmlDocument.setParameter("messageTitle", + Utility.messageBD(this, "ProcessStatus-W", vars.getLanguage())); + xmlDocument.setParameter("messageMessage", + Utility.messageBD(this, "NoDataFound", vars.getLanguage())); + } } + out.println(xmlDocument.print()); out.close(); } + } private CostingAlgorithm getLEsCostingAlgortithm(Organization legalEntity) { @@ -300,8 +368,12 @@ OBQuery<CostingRule> whereQry = OBDal.getInstance().createQuery(CostingRule.class, where.toString()); + if (!whereQry.list().isEmpty()) { + return whereQry.list().get(0).getCostingAlgorithm(); + } else { + return null; + } - return whereQry.list().get(0).getCostingAlgorithm(); } private boolean hasTrxWithNoCost(String strDate, String strWarehouse, String strCategoryProduct) { @@ -311,7 +383,8 @@ where.append(" join trx." + MaterialTransaction.PROPERTY_PRODUCT + " as p"); where.append(" where trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " < :maxDate"); where.append(" and trx." + MaterialTransaction.PROPERTY_ISCOSTCALCULATED + " = false"); - where.append(" and loc." + Locator.PROPERTY_WAREHOUSE + ".id = :wh"); + if (!"".equals(strWarehouse) && strWarehouse != null) + where.append(" and loc." + Locator.PROPERTY_WAREHOUSE + ".id = :wh"); if (!"".equals(strCategoryProduct)) { where.append(" and p." + Product.PROPERTY_PRODUCTCATEGORY + ".id = :prodCategory"); } @@ -327,7 +400,10 @@ // DoNothing parse exception not expected. log4j.error("error parsing date: " + strDate, e); } - whereQry.setNamedParameter("wh", strWarehouse); + if (!"".equals(strWarehouse) && strWarehouse != null) { + whereQry.setNamedParameter("wh", strWarehouse); + } + if (!"".equals(strCategoryProduct)) { whereQry.setNamedParameter("prodCategory", strCategoryProduct); } @@ -338,4 +414,356 @@ public String getServletInfo() { return "Servlet ReportValuedStockEnhanced. This Servlet was made by Pablo Sarobe"; } // end of getServletInfo() method + + private void printPageDataXLS(HttpServletRequest request, HttpServletResponse response, + VariablesSecureApp vars, String strDate, String strOrganization, String strWarehouse, + String strCategoryProduct, String strCurrencyId, String strWarehouseConsolidation) + throws IOException, ServletException { + if (log4j.isDebugEnabled()) + log4j.debug("Output: XLS"); + response.setContentType("text/html; charset=UTF-8"); + boolean isWarehouseConsolidation = false; + if (strWarehouseConsolidation.equals("on")) + isWarehouseConsolidation = true; + + ReportValuedStockEnhancedData[] data = null; + + if (CostingStatus.getInstance().isMigrated() == false) { + advise(request, response, "ERROR", + Utility.messageBD(this, "NotUsingNewCost", vars.getLanguage()), ""); + return; + } + + if (strOrganization == null || strOrganization.isEmpty()) { + try { + advisePopUp(request, response, "WARNING", + Utility.messageBD(this, "ProcessStatus-W", vars.getLanguage()), + Utility.messageBD(this, "NoDataFound", vars.getLanguage())); + } catch (Exception e) { + throw new ServletException(e); + } + } else { + Organization legalEntity = null; + try { + legalEntity = OBDal.getInstance().get(Organization.class, strOrganization); + } catch (Exception e) { + throw new ServletException(e); + } + + CostingAlgorithm ca = null; + if (legalEntity == null) { + advise(request, response, "ERROR", + Utility.messageBD(this, "WarehouseNotInLE", vars.getLanguage()), ""); + } + String paramReport = ""; + ca = getLEsCostingAlgortithm(legalEntity); + if (ca != null) { + paramReport = ca.getName(); + + if (!isWarehouseConsolidation) { + data = ReportValuedStockEnhancedData.select(this, vars.getLanguage(), strCurrencyId, + DateTimeData.nDaysAfter(this, strDate, "1"), (legalEntity == null) ? null + : legalEntity.getId(), strOrganization, strWarehouse, strCategoryProduct); + } else { + data = ReportValuedStockEnhancedData.selectClusteredByWarehouse(this, vars.getLanguage(), + strCurrencyId, DateTimeData.nDaysAfter(this, strDate, "1"), + (legalEntity == null) ? null : legalEntity.getId(), strOrganization, strWarehouse, + strCategoryProduct); + } + + } else { + if (!isWarehouseConsolidation) { + data = ReportValuedStockEnhancedData.selectWithoutCost(this, vars.getLanguage(), + strCurrencyId, (legalEntity == null) ? null : legalEntity.getId(), + DateTimeData.nDaysAfter(this, strDate, "1"), strOrganization, strWarehouse, + strCategoryProduct); + } else { + data = ReportValuedStockEnhancedData.selectClusteredByWarehouseWithoutCost(this, + vars.getLanguage(), strCurrencyId, + (legalEntity == null) ? null : legalEntity.getId(), + DateTimeData.nDaysAfter(this, strDate, "1"), strOrganization, strWarehouse, + strCategoryProduct); + } + } + + if (data == null || data.length == 0) { + advisePopUp(request, response, "WARNING", + Utility.messageBD(this, "ProcessStatus-W", vars.getLanguage()), + Utility.messageBD(this, "NoDataFound", vars.getLanguage())); + } else { + + String strReportName = "@basedesign@/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedExcel.jrxml"; + + HashMap<String, Object> parameters = new HashMap<String, Object>(); + + parameters + .put("ALG_COST", (paramReport.length() > 0) ? paramReport + " COST" : paramReport); + parameters.put("SUM_ALG_COSTO", (paramReport.length() > 0) ? paramReport + " VALUATION" + : paramReport); + + renderJR(vars, response, strReportName, "xls", parameters, data, null); + } + } + } + + private void printPageDataPDF(HttpServletRequest request, HttpServletResponse response, + VariablesSecureApp vars, String strDate, String strOrganization, String strWarehouse, + String strCategoryProduct, String strCurrencyId, String strWarehouseConsolidation) + throws IOException, ServletException { + if (log4j.isDebugEnabled()) + log4j.debug("Output: PDF"); + response.setContentType("text/html; charset=UTF-8"); + boolean isWarehouseConsolidation = false; + if (strWarehouseConsolidation.equals("on")) + isWarehouseConsolidation = true; + + ReportValuedStockEnhancedData[] data = null; + + if (CostingStatus.getInstance().isMigrated() == false) { + advise(request, response, "ERROR", + Utility.messageBD(this, "NotUsingNewCost", vars.getLanguage()), ""); + return; + } + + if (strOrganization == null || strOrganization.isEmpty()) { + try { + advisePopUp(request, response, "WARNING", + Utility.messageBD(this, "ProcessStatus-W", vars.getLanguage()), + Utility.messageBD(this, "NoDataFound", vars.getLanguage())); + } catch (Exception e) { + throw new ServletException(e); + } + } else { + Organization legalEntity = null; + try { + legalEntity = OBDal.getInstance().get(Organization.class, strOrganization); + } catch (Exception e) { + throw new ServletException(e); + } + CostingAlgorithm ca = null; + if (legalEntity == null) { + advise(request, response, "ERROR", + Utility.messageBD(this, "WarehouseNotInLE", vars.getLanguage()), ""); + } + String paramReport = ""; + + ca = getLEsCostingAlgortithm(legalEntity); + if (ca != null) { + paramReport = ca.getName(); + + if (!isWarehouseConsolidation) { + data = ReportValuedStockEnhancedData.select(this, vars.getLanguage(), strCurrencyId, + DateTimeData.nDaysAfter(this, strDate, "1"), (legalEntity == null) ? null + : legalEntity.getId(), strOrganization, strWarehouse, strCategoryProduct); + } else { + data = ReportValuedStockEnhancedData.selectClusteredByWarehouse(this, vars.getLanguage(), + strCurrencyId, DateTimeData.nDaysAfter(this, strDate, "1"), + (legalEntity == null) ? null : legalEntity.getId(), strOrganization, strWarehouse, + strCategoryProduct); + } + + } else { + if (!isWarehouseConsolidation) { + data = ReportValuedStockEnhancedData.selectWithoutCost(this, vars.getLanguage(), + strCurrencyId, (legalEntity == null) ? null : legalEntity.getId(), + DateTimeData.nDaysAfter(this, strDate, "1"), strOrganization, strWarehouse, + strCategoryProduct); + } else { + data = ReportValuedStockEnhancedData.selectClusteredByWarehouseWithoutCost(this, + vars.getLanguage(), strCurrencyId, + (legalEntity == null) ? null : legalEntity.getId(), + DateTimeData.nDaysAfter(this, strDate, "1"), strOrganization, strWarehouse, + strCategoryProduct); + } + } + + if (data == null || data.length == 0) { + advisePopUp(request, response, "WARNING", + Utility.messageBD(this, "ProcessStatus-W", vars.getLanguage()), + Utility.messageBD(this, "NoDataFound", vars.getLanguage())); + } else { + + Vector<SumaryProductCategory> v = new Vector<SumaryProductCategory>(); + boolean esta = false; + BigDecimal total = new BigDecimal(0); + BigDecimal aux; + for (int i = 0; i < data.length; i++) { + + for (int j = 0; j < v.size(); j++) { + if (v.get(j).category.equals(data[i].categoryName)) { + esta = true; + break; + } + } + + if (!esta) { + total = new BigDecimal(0); + for (int j = 0; j < data.length; j++) { + if (data[i] != null && data[j] != null + && data[i].categoryName.equals(data[j].categoryName)) { + try { + aux = new BigDecimal(data[j].totalCost); + } catch (Exception e) { + aux = new BigDecimal(0); + } + total = total.add(aux); + } + } + v.add(new SumaryProductCategory(data[i].categoryName, total.toString())); + } + esta = false; + } + SumaryProductCategory[] datos = new SumaryProductCategory[v.size()]; + + for (int i = 0; i < v.size(); i++) + datos[i] = v.get(i); + + String strReportName = "@basedesign@/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedPDF.jrxml"; + + response.setHeader("Content-disposition", + "inline; filename=ReportValuedStockEnhancedPDF.pdf"); + + HashMap<String, Object> parameters = new HashMap<String, Object>(); + parameters.put("DATE", strDate); + parameters + .put("ALG_COST", (paramReport.length() > 0) ? paramReport + " COST" : paramReport); + + parameters.put("SUM_ALG_COST", (paramReport.length() > 0) ? paramReport + " VALUATION" + : paramReport); + + // agregado + parameters.put("subreport_datasource", datos); + + String strSubReportName = "@basedesign@/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/SumaryProductCategory.jrxml"; + response.setHeader("Content-disposition", "inline; filename=SumaryProductCategory.pdf"); + + HashMap<String, Object> parametersSubReport = new HashMap<String, Object>(); + + // renderJR(vars, response, strReportName, "pdf", parameters, data, null); + try { + + JasperPrint jasperPrint = null; + Collection<JasperPrint> jrPrintReports = new ArrayList<JasperPrint>(); + + try { + jasperPrint = myRenderJR(vars, strReportName, null, parameters, data); + jrPrintReports.add(jasperPrint); + jasperPrint = myRenderJR(vars, strSubReportName, null, parametersSubReport, datos); + jrPrintReports.add(jasperPrint); + + } catch (Exception e) { + log4j.error(e.getMessage()); + e.getStackTrace(); + } + printReports(response, jrPrintReports, "ReportValuedStock-Enhanced.pdf", vars); + } catch (Exception e) { + e.printStackTrace(); + } + } + } + + } + + protected JasperPrint myRenderJR(VariablesSecureApp variables, String strReportName, + String strFileName, HashMap<String, Object> designParameters, FieldProvider[] data) + throws ServletException { + + if (strReportName == null || strReportName.equals("")) + strReportName = PrintJRData.getReportName(this, classInfo.id); + + final String strAttach = globalParameters.strFTPDirectory + "/284-" + classInfo.id; + + final String strLanguage = variables.getLanguage(); + final Locale locLocale = new Locale(strLanguage.substring(0, 2), strLanguage.substring(3, 5)); + + final String strBaseDesign = getBaseDesignPath(strLanguage); + + strReportName = Replace.replace(Replace.replace(strReportName, "@basedesign@", strBaseDesign), + "@attach@", strAttach); + if (strFileName == null) { + strFileName = strReportName.substring(strReportName.lastIndexOf("/") + 1); + } + + // UUID reportId = null; + try { + + final JasperReport jasperReport = Utility.getTranslatedJasperReport(this, strReportName, + strLanguage, strBaseDesign); + if (designParameters == null) + designParameters = new HashMap<String, Object>(); + + designParameters.put("IS_IGNORE_PAGINATION", false); + designParameters.put("BASE_WEB", strReplaceWithFull); + designParameters.put("BASE_DESIGN", strBaseDesign); + designParameters.put("ATTACH", strAttach); + designParameters.put("USER_CLIENT", Utility.getContext(this, variables, "#User_Client", "")); + designParameters.put("USER_ORG", Utility.getContext(this, variables, "#User_Org", "")); + designParameters.put("LANGUAGE", strLanguage); + designParameters.put("LOCALE", locLocale); + designParameters.put("REPORT_TITLE", + PrintJRData.getReportTitle(this, variables.getLanguage(), classInfo.id)); + + final DecimalFormatSymbols dfs = new DecimalFormatSymbols(); + dfs.setDecimalSeparator(variables.getSessionValue("#AD_ReportDecimalSeparator").charAt(0)); + dfs.setGroupingSeparator(variables.getSessionValue("#AD_ReportGroupingSeparator").charAt(0)); + final DecimalFormat numberFormat = new DecimalFormat( + variables.getSessionValue("#AD_ReportNumberFormat"), dfs); + designParameters.put("NUMBERFORMAT", numberFormat); + + return JasperFillManager.fillReport(jasperReport, designParameters, + new JRFieldProviderDataSource(data, variables.getJavaDateFormat())); + + } catch (final JRException e) { + log4j.error("JR: Error: ", e); + throw new ServletException(e.getMessage(), e); + } catch (final Exception e) { + throw new ServletException(e.getMessage(), e); + } + } + + private void printReports(HttpServletResponse response, Collection<JasperPrint> jrPrintReports, + String filename, VariablesSecureApp vars) throws DocumentException { + ServletOutputStream os = null; + int i = 0; + try { + os = response.getOutputStream(); + FileOutputStream oos = new FileOutputStream(globalParameters.strFTPDirectory + "/" + filename); + Document document = null; + PdfCopy writer = null; + int n; + for (Iterator<JasperPrint> iterator = jrPrintReports.iterator(); iterator.hasNext();) { + JasperExportManager.exportReportToPdfFile((JasperPrint) iterator.next(), + globalParameters.strFTPDirectory + "/" + i + ".pdf"); + PdfReader reader = new PdfReader(globalParameters.strFTPDirectory + "/" + i + ".pdf"); + n = reader.getNumberOfPages(); + if (i == 0) { + document = new Document(reader.getPageSizeWithRotation(1)); + writer = new PdfCopy(document, oos); + document.open(); + } + PdfImportedPage page; + for (int j = 0; j < n;) { + ++j; + page = writer.getImportedPage(reader, j); + writer.addPage(page); + } + FileUtility fdelete = new FileUtility(globalParameters.strFTPDirectory, i + ".pdf", false, + true); + if (!fdelete.deleteFile()) + log4j.error("Download report could not delete the file :" + i + ".pdf"); + i++; + } + document.close(); + printPagePopUpDownload(os, filename); + + } catch (IOException e) { + e.printStackTrace(); + } catch (ServletException e) { + e.printStackTrace(); + } catch (JRException e) { + e.printStackTrace(); + } + + } + } diff --git a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.xml b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.xml --- a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.xml +++ b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.xml @@ -1,7 +1,7 @@ <?xml version="1.0" encoding="UTF-8" ?> <!-- ************************************************************************* - * Copyright (C) 2013 Openbravo S.L.U. + * Copyright (C) 2013 - 2015 Openbravo S.L.U. * Licensed under the Openbravo Commercial License version 1.0 * You may obtain a copy of the License at http://www.openbravo.com/legal/obcl.html * or in the legal folder of this module distribution. @@ -13,10 +13,14 @@ <PARAMETER id="fieldCalendar" name="calendar" attribute="src" replace="es" default="en"/> <PARAMETER id="paramDirectory" name="directory" default=""/> <PARAMETER id="paramLanguage" name="paramLanguage" default=""/> - <PARAMETER id="paramDate" name="dateFromdisplayFormat" attribute="displayformat" replace="xx"/> - <PARAMETER id="paramDate" name="dateFromsaveFormat" attribute="saveformat" replace="yy"/> + <PARAMETER id="paramDate" name="dateFromdisplayFormat" attribute="displayformat" replace="xx"/> + <PARAMETER id="paramDate" name="dateFromsaveFormat" attribute="saveformat" replace="yy"/> <PARAMETER id="paramDate" name="date" attribute="value"/> + <PARAMETER id="paramAD_ORGID" name="adOrgId" attribute="value"/> <PARAMETER id="paramM_WAREHOUSE_ID" name="mWarehouseId" attribute="value"/> + <PARAMETER id="paramArrayWh" name="warehouses"/> + <PARAMETER id="paramWhwh" name="whwh" attribute="value"/> + <PARAMETER id="paramSelectedWh" name="warehouseId" attribute="value"/> <PARAMETER id="paramM_PRODUCT_CATEGORY_ID" name="categoryProduct" attribute="value"/> <PARAMETER id="paramCSS" name="theme" attribute="href" replace="Default"/> <PARAMETER id="tdleftTabs" name="leftTabs" default=""/> @@ -33,6 +37,7 @@ <structure name="structure1"> <FIELD id="fieldRownum1">rownum</FIELD> <FUNCTION id="funcEvenOddRow1xx" name="MODULE" arg1="fieldRownum1" arg2="param2" format="integerEdition" attribute="class" replace="yy"/> + <FIELD id="fieldWarehouse">warehouse</FIELD> <FIELD id="fieldCategoryName">categoryName</FIELD> <FIELD id="idfieldmProductId" attribute="onclick" replace="xx">mProductId</FIELD> <FIELD id="fieldProductName">productName</FIELD> @@ -52,9 +57,16 @@ <FUNCTION id="fieldSumCostTotal" name="SUM" format="euroInform">totalCost</FUNCTION> <FUNCTION id="fieldSumValuationType" name="SUM" format="euroInform">valuationType</FUNCTION> <FUNCTION id="fieldSumValuationTypeTotal" name="SUM" format="euroInform">valuationType</FUNCTION> + <FUNCTION id="fieldSumCostWh" name="SUM" format="euroInform">totalCost</FUNCTION> + <FUNCTION id="fieldSumValuationTypeWh" name="SUM" format="euroInform">valuationType</FUNCTION> + <SECTION id="sectionWarehouse" field="warehouse"/> <SECTION id="sectionCategoryProduct" field="categoryName"/> <SECTION id="sectionDetail"/> - </structure> + </structure> + <SUBREPORT id="reportAD_ORGID" name="reportAD_ORGID" report="org/openbravo/erpCommon/reference/List"> + <ARGUMENT name="parameterListSelected" withId="paramAD_ORGID"/> + </SUBREPORT> + <PARAMETER id="inpWarehouseConsolidation" name="warehouseConsolidation" boolean="checked" withId="inpWarehouseConsolidation" default="0"/> <SUBREPORT id="reportM_WAREHOUSEID" name="reportM_WAREHOUSEID" report="org/openbravo/erpCommon/reference/List"> <ARGUMENT name="parameterListSelected" withId="paramM_WAREHOUSE_ID"/> </SUBREPORT> diff --git a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced2.html b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced2.html --- a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced2.html +++ b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced2.html @@ -2,7 +2,7 @@ <!DOCTYPE html PUBLIC "-//W3C//DTD Xhtml 1.0 Transitional//EN" "http://www.w3.org/tr/xhtml1/Dtd/xhtml1-transitional.dtd"> <!-- ************************************************************************* - * Copyright (C) 2013 Openbravo S.L.U. + * Copyright (C) 2013 - 2015 Openbravo S.L.U. * Licensed under the Openbravo Commercial License version 1.0 * You may obtain a copy of the License at http://www.openbravo.com/legal/obcl.html * or in the legal folder of this module distribution. @@ -349,7 +349,7 @@ <div id="sectionCategoryProduct"> <table cellspacing="0" cellpadding="0" width="100%" class="DataGrid_Header_Table DataGrid_Body_Table" style="table-layout: auto;"> <tr class="DataGrid_Body_Row"> - <th class="DataGrid_Header_Cell"> <span id="fieldCategoryName" style="white-space: pre;">xxQuímicos limpieza</span></th> + <th class="DataGrid_Header_Cell"> <span id="fieldCategoryName">xxQuímicos limpieza</span></th> </tr> </table> <table cellspacing="0" cellpadding="0" width="100%" class="DataGrid_Header_Table DataGrid_Body_Table" style="table-layout: auto;"> @@ -363,7 +363,7 @@ <div id="sectionDetail"> <tr class="DataGrid_Body_Row DataGrid_Body_Row_yy" id="funcEvenOddRow1xx"> <td class="DataGrid_Body_Cell" width="58%"> <a class="LabelLink" href="#" onclick="openCostingTab('xx');return false;" onmouseover="window.status='Costs';return true;" onmouseout="window.status='';return true;" class="Link" id="idfieldmProductId"> - <span id="fieldProductName" style="white-space: pre;">xx24/10/2004</span></a></td> + <span id="fieldProductName">xx24/10/2004</span></a></td> <td class="DataGrid_Body_Cell" width="12%" id="fieldQty" style="text-align:right;">xx24/10/2004</td> <td class="DataGrid_Body_Cell" width="10%" id="fieldUomName" >xx12/07/1923</td> <td class="DataGrid_Body_Cell" width="11%" id="fieldAverageCost" style="text-align:right;">xx3456 </td> diff --git a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced_data.xsql b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced_data.xsql --- a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced_data.xsql +++ b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced_data.xsql @@ -1,7 +1,7 @@ <?xml version="1.0" encoding="UTF-8" ?> <!-- ************************************************************************* -* Copyright (C) 2013 Openbravo S.L.U. +* Copyright (C) 2013 - 2015 Openbravo S.L.U. * Licensed under the Openbravo Commercial License version 1.0 * You may obtain a copy of the License at http://www.openbravo.com/legal/obcl.html * or in the legal folder of this module distribution. @@ -18,15 +18,17 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT CATEGORY_NAME, M_PRODUCT_ID, PRODUCT_NAME, SUM(QTY) AS QTY, UOM_NAME, CASE ISCOSTCALCULATED + SELECT CATEGORY_NAME, M_PRODUCT_ID, PRODUCT_NAME, PRODUCT_SEARCHKEY, SUM(QTY) AS QTY, UOM_NAME, CASE ISCOSTCALCULATED WHEN 'Y' THEN SUM(TOTAL_COST) / SUM(QTY) ELSE NULL END AS AVERAGE_COST, SUM(TOTAL_COST) AS TOTAL_COST, COSTTYPE as COST_TYPE, - SUM(QTY) * COSTTYPE as VALUATION_TYPE + SUM(QTY) * COSTTYPE as VALUATION_TYPE, + ZZ.WAREHOUSE as WAREHOUSE, + '' as ID, '' as PADRE , '' AS NAME FROM (SELECT M_PRODUCT_CATEGORY.NAME AS CATEGORY_NAME, A.M_PRODUCT_ID, AD_COLUMN_IDENTIFIER (to_char('M_Product'),to_char(M_PRODUCT.M_PRODUCT_ID),to_char(?)) AS PRODUCT_NAME, - A.QTY, C_UOM.NAME AS UOM_NAME, + M_PRODUCT.VALUE AS PRODUCT_SEARCHKEY, A.QTY, C_UOM.NAME AS UOM_NAME, CASE a.iscostcalculated WHEN 'Y' THEN C_CURRENCY_CONVERT_PRECISION (Suma,A.c_currency_id,?,to_date(?),NULL,A.AD_CLIENT_ID,?,'C') ELSE NULL @@ -34,39 +36,40 @@ CASE a.iscostcalculated WHEN 'Y' THEN C_CURRENCY_CONVERT_PRECISION (P.COST,P.c_currency_id,?,to_date(?),NULL,A.AD_CLIENT_ID,?,'C') ELSE NULL - END AS COSTTYPE + END AS COSTTYPE, A.WAREHOUSE AS WAREHOUSE FROM M_PRODUCT_CATEGORY, (SELECT trx.M_PRODUCT_ID, sum(trx.MOVEMENTQTY) AS QTY, sum(CASE WHEN trx.MOVEMENTQTY < 0 THEN- tc.trxcost ELSE tc.trxcost END) AS Suma, - trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate) as movementdate + trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate) as movementdate , WH.NAME AS WAREHOUSE FROM M_TRANSACTION trx JOIN M_LOCATOR l ON trx.M_LOCATOR_ID = l.M_LOCATOR_ID + INNER JOIN M_WAREHOUSE WH ON l.M_WAREHOUSE_ID = WH.M_WAREHOUSE_ID LEFT JOIN M_INOUTLINE iol ON trx.M_INOUTLINE_ID = iol.M_INOUTLINE_ID LEFT JOIN M_INOUT io ON iol.M_INOUT_ID = io.M_INOUT_ID LEFT JOIN (SELECT sum(cost) AS trxcost, m_transaction_id, c_currency_id FROM M_TRANSACTION_COST - WHERE costdate < to_date(?) - GROUP BY m_transaction_id, c_currency_id) tc ON trx.m_transaction_id = tc.m_transaction_id + WHERE costdate < to_date(?) + GROUP BY m_transaction_id, c_currency_id ) tc ON trx.m_transaction_id = tc.m_transaction_id WHERE trx.MOVEMENTDATE < to_date(?) - AND l.M_WAREHOUSE_ID = ? - GROUP BY trx.M_PRODUCT_ID, trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate)) A, - C_UOM, - M_PRODUCT LEFT JOIN (SELECT mc.COST, mc.DATEFROM, mc.M_PRODUCT_ID, mc.c_currency_id + AND trx.ad_org_id = ? + AND 0=0 + GROUP BY trx.M_PRODUCT_ID, trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate),WH.NAME) A, + C_UOM, + M_PRODUCT LEFT JOIN (SELECT DISTINCT mc.COST, mc.DATEFROM, mc.M_PRODUCT_ID, mc.c_currency_id FROM M_COSTING mc JOIN (SELECT mc2.M_PRODUCT_ID, max(mc2.DATEFROM) AS datefrom FROM M_COSTING mc2 WHERE mc2.DATEFROM < to_date(?) - AND mc2.AD_ORG_ID IN (?,'0') - AND mc2.COSTTYPE = ? - GROUP BY mc2.M_PRODUCT_ID) dc ON (mc.DATEFROM = dc.DATEFROM AND mc.M_PRODUCT_ID = dc.M_PRODUCT_ID)) P + AND mc2.AD_ORG_ID IN (?,'0') + GROUP BY mc2.M_PRODUCT_ID) dc ON (mc.DATEFROM = dc.DATEFROM AND mc.M_PRODUCT_ID = dc.M_PRODUCT_ID AND mc.DATETO>dc.DATEFROM)) P ON M_PRODUCT.M_PRODUCT_ID = P.M_PRODUCT_ID WHERE A.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID AND A.C_UOM_ID = C_UOM.C_UOM_ID AND M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID AND 1 = 1 AND (A.QTY <> 0 OR A.Suma <> 0)) ZZ - GROUP BY ZZ.M_PRODUCT_ID, CATEGORY_NAME, PRODUCT_NAME, UOM_NAME, ISCOSTCALCULATED, COSTTYPE + GROUP BY ZZ.M_PRODUCT_ID, CATEGORY_NAME, ZZ.WAREHOUSE, PRODUCT_NAME, PRODUCT_SEARCHKEY, UOM_NAME, ISCOSTCALCULATED, COSTTYPE HAVING SUM(QTY) <>0 - ORDER BY CATEGORY_NAME, PRODUCT_NAME + ORDER BY WAREHOUSE,CATEGORY_NAME, PRODUCT_NAME ]]></Sql> <Field name="rownum" value="count"/> <Parameter name="adLanguage"/> @@ -78,41 +81,119 @@ <Parameter name="legalEntity"/> <Parameter name="datePlus"/> <Parameter name="datePlus"/> - <Parameter name="warehouse"/> + <Parameter name="organization"/> + <Parameter name="warehouse" optional="true" after="AND 0=0"><![CDATA[ AND l.M_WAREHOUSE_ID = ? ]]></Parameter> <Parameter name="datePlus"/> <Parameter name="legalEntity"/> - <Parameter name="costType"/> <Parameter name="categoryProduct" optional="true" after="AND 1 = 1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_CATEGORY_ID= ? ]]></Parameter> </SqlMethod> + + <SqlMethod name="selectClusteredByWarehouse" type="preparedStatement" return="multiple"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + SELECT CATEGORY_NAME, M_PRODUCT_ID, PRODUCT_NAME, PRODUCT_SEARCHKEY, SUM(QTY) AS QTY, UOM_NAME, CASE ISCOSTCALCULATED + WHEN 'Y' THEN SUM(TOTAL_COST) / SUM(QTY) + ELSE NULL + END AS AVERAGE_COST, + SUM(TOTAL_COST) AS TOTAL_COST, + COSTTYPE as COST_TYPE, + SUM(QTY) * COSTTYPE as VALUATION_TYPE, + 'ALL Warehouses' as WAREHOUSE, + '' as ID, '' as PADRE , '' AS NAME + FROM (SELECT M_PRODUCT_CATEGORY.NAME AS CATEGORY_NAME, A.M_PRODUCT_ID, AD_COLUMN_IDENTIFIER (to_char('M_Product'),to_char(M_PRODUCT.M_PRODUCT_ID),to_char(?)) AS PRODUCT_NAME, + M_PRODUCT.VALUE AS PRODUCT_SEARCHKEY, A.QTY, C_UOM.NAME AS UOM_NAME, + CASE a.iscostcalculated + WHEN 'Y' THEN C_CURRENCY_CONVERT_PRECISION (Suma,A.c_currency_id,?,to_date(?),NULL,A.AD_CLIENT_ID,?,'C') + ELSE NULL + END AS TOTAL_COST, A.ISCOSTCALCULATED, A.AD_CLIENT_ID, A.MOVEMENTDATE, A.C_CURRENCY_ID, + CASE a.iscostcalculated + WHEN 'Y' THEN C_CURRENCY_CONVERT_PRECISION (P.COST,P.c_currency_id,?,to_date(?),NULL,A.AD_CLIENT_ID,?,'C') + ELSE NULL + END AS COSTTYPE, A.WAREHOUSE AS WAREHOUSE + FROM M_PRODUCT_CATEGORY, + (SELECT trx.M_PRODUCT_ID, sum(trx.MOVEMENTQTY) AS QTY, sum(CASE WHEN trx.MOVEMENTQTY < 0 THEN- tc.trxcost ELSE tc.trxcost END) AS Suma, + trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate) as movementdate , WH.NAME AS WAREHOUSE + FROM M_TRANSACTION trx + JOIN M_LOCATOR l ON trx.M_LOCATOR_ID = l.M_LOCATOR_ID + INNER JOIN M_WAREHOUSE WH ON l.M_WAREHOUSE_ID = WH.M_WAREHOUSE_ID + LEFT JOIN M_INOUTLINE iol ON trx.M_INOUTLINE_ID = iol.M_INOUTLINE_ID + LEFT JOIN M_INOUT io ON iol.M_INOUT_ID = io.M_INOUT_ID + LEFT JOIN (SELECT sum(cost) AS trxcost, m_transaction_id, c_currency_id + FROM M_TRANSACTION_COST + WHERE costdate < to_date(?) + GROUP BY m_transaction_id, c_currency_id ) tc ON trx.m_transaction_id = tc.m_transaction_id + WHERE trx.MOVEMENTDATE < to_date(?) + AND trx.ad_org_id = ? + AND 0=0 + GROUP BY trx.M_PRODUCT_ID, trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate),WH.NAME) A, + C_UOM, + M_PRODUCT LEFT JOIN (SELECT DISTINCT mc.COST, mc.DATEFROM, mc.M_PRODUCT_ID, mc.c_currency_id + FROM M_COSTING mc + JOIN (SELECT mc2.M_PRODUCT_ID, max(mc2.DATEFROM) AS datefrom + FROM M_COSTING mc2 + WHERE mc2.DATEFROM < to_date(?) + AND mc2.AD_ORG_ID IN (?,'0') + GROUP BY mc2.M_PRODUCT_ID) dc ON (mc.DATEFROM = dc.DATEFROM AND mc.M_PRODUCT_ID = dc.M_PRODUCT_ID AND mc.DATETO>dc.DATEFROM)) P + ON M_PRODUCT.M_PRODUCT_ID = P.M_PRODUCT_ID + WHERE A.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID + AND A.C_UOM_ID = C_UOM.C_UOM_ID + AND M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID + AND 1 = 1 + AND (A.QTY <> 0 OR A.Suma <> 0)) ZZ + GROUP BY ZZ.M_PRODUCT_ID, CATEGORY_NAME, PRODUCT_NAME, PRODUCT_SEARCHKEY, UOM_NAME, ISCOSTCALCULATED, COSTTYPE + HAVING SUM(QTY) <>0 + ORDER BY WAREHOUSE,CATEGORY_NAME, PRODUCT_NAME + ]]></Sql> + <Field name="rownum" value="count"/> + <Parameter name="adLanguage"/> + <Parameter name="cCurrencyConv"/> + <Parameter name="datePlus"/> + <Parameter name="legalEntity"/> + <Parameter name="cCurrencyConv"/> + <Parameter name="datePlus"/> + <Parameter name="legalEntity"/> + <Parameter name="datePlus"/> + <Parameter name="datePlus"/> + <Parameter name="organization"/> + <Parameter name="warehouse" optional="true" after="AND 0=0"><![CDATA[ AND l.M_WAREHOUSE_ID = ? ]]></Parameter> + <Parameter name="datePlus"/> + <Parameter name="legalEntity"/> + <Parameter name="categoryProduct" optional="true" after="AND 1 = 1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_CATEGORY_ID= ? ]]></Parameter> + </SqlMethod> + <SqlMethod name="selectWithoutCost" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT CATEGORY_NAME, M_PRODUCT_ID, PRODUCT_NAME, SUM(QTY) AS QTY, UOM_NAME, CASE ISCOSTCALCULATED + SELECT CATEGORY_NAME, M_PRODUCT_ID, PRODUCT_NAME, PRODUCT_SEARCHKEY, SUM(QTY) AS QTY, UOM_NAME, CASE ISCOSTCALCULATED WHEN 'Y' THEN SUM(TOTAL_COST) / SUM(QTY) ELSE NULL END AS AVERAGE_COST, - SUM(TOTAL_COST) AS TOTAL_COST + SUM(TOTAL_COST) AS TOTAL_COST, + ZZ.WAREHOUSE as WAREHOUSE FROM (SELECT M_PRODUCT_CATEGORY.NAME AS CATEGORY_NAME, A.M_PRODUCT_ID, AD_COLUMN_IDENTIFIER (to_char('M_Product'),to_char(M_PRODUCT.M_PRODUCT_ID),to_char(?)) AS PRODUCT_NAME, - A.QTY, C_UOM.NAME AS UOM_NAME, + M_PRODUCT.VALUE AS PRODUCT_SEARCHKEY, A.QTY, C_UOM.NAME AS UOM_NAME, CASE a.iscostcalculated WHEN 'Y' THEN C_CURRENCY_CONVERT_PRECISION (Suma,A.c_currency_id,?,A.movementdate,NULL,A.AD_CLIENT_ID,?,'C') ELSE NULL - END AS TOTAL_COST, A.ISCOSTCALCULATED, A.AD_CLIENT_ID, A.MOVEMENTDATE, A.C_CURRENCY_ID + END AS TOTAL_COST, A.ISCOSTCALCULATED, A.AD_CLIENT_ID, A.MOVEMENTDATE, A.C_CURRENCY_ID, A.WAREHOUSE AS WAREHOUSE FROM M_PRODUCT_CATEGORY, (SELECT trx.M_PRODUCT_ID, sum(trx.MOVEMENTQTY) AS QTY, sum(CASE WHEN trx.MOVEMENTQTY < 0 THEN- tc.trxcost ELSE tc.trxcost END) AS Suma, - trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate) as movementdate + trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate) as movementdate, WH.NAME AS WAREHOUSE FROM M_TRANSACTION trx - JOIN M_LOCATOR l ON trx.M_LOCATOR_ID = l.M_LOCATOR_ID + JOIN M_LOCATOR l ON trx.M_LOCATOR_ID = l.M_LOCATOR_ID + INNER JOIN M_WAREHOUSE WH ON l.M_WAREHOUSE_ID = WH.M_WAREHOUSE_ID LEFT JOIN M_INOUTLINE iol ON trx.M_INOUTLINE_ID = iol.M_INOUTLINE_ID LEFT JOIN M_INOUT io ON iol.M_INOUT_ID = io.M_INOUT_ID LEFT JOIN (SELECT sum(cost) AS trxcost, m_transaction_id, c_currency_id FROM M_TRANSACTION_COST - WHERE costdate < to_date(?) + WHERE costdate < to_date(?) GROUP BY m_transaction_id, c_currency_id) tc ON trx.m_transaction_id = tc.m_transaction_id WHERE trx.MOVEMENTDATE < to_date(?) - AND l.M_WAREHOUSE_ID = ? - GROUP BY trx.M_PRODUCT_ID, trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate)) A, + AND trx.ad_org_id = ? + AND 0=0 + GROUP BY trx.M_PRODUCT_ID, trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate),WH.NAME) A, C_UOM, M_PRODUCT WHERE A.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID @@ -120,9 +201,9 @@ AND M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID AND 1 = 1 AND (A.QTY <> 0 OR A.Suma <> 0)) ZZ - GROUP BY ZZ.M_PRODUCT_ID, CATEGORY_NAME, PRODUCT_NAME, UOM_NAME, ISCOSTCALCULATED - HAVING SUM(QTY) <>0 - ORDER BY CATEGORY_NAME, PRODUCT_NAME + GROUP BY ZZ.M_PRODUCT_ID, CATEGORY_NAME, ZZ.WAREHOUSE,PRODUCT_NAME, PRODUCT_SEARCHKEY, UOM_NAME, ISCOSTCALCULATED + HAVING SUM(QTY) <> 0 + ORDER BY WAREHOUSE, CATEGORY_NAME, PRODUCT_NAME ]]></Sql> <Field name="rownum" value="count"/> <Parameter name="adLanguage"/> @@ -130,13 +211,70 @@ <Parameter name="legalEntity"/> <Parameter name="datePlus"/> <Parameter name="datePlus"/> - <Parameter name="warehouse"/> + <Parameter name="organization"/> + <Parameter name="warehouse" optional="true" after="AND 0=0"><![CDATA[ AND l.M_WAREHOUSE_ID = ? ]]></Parameter> <Parameter name="categoryProduct" optional="true" after="AND 1 = 1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_CATEGORY_ID= ? ]]></Parameter> </SqlMethod> + + <SqlMethod name="selectClusteredByWarehouseWithoutCost" type="preparedStatement" return="multiple"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + SELECT CATEGORY_NAME, M_PRODUCT_ID, PRODUCT_NAME, PRODUCT_SEARCHKEY, SUM(QTY) AS QTY, UOM_NAME, CASE ISCOSTCALCULATED + WHEN 'Y' THEN SUM(TOTAL_COST) / SUM(QTY) + ELSE NULL + END AS AVERAGE_COST, + SUM(TOTAL_COST) AS TOTAL_COST, + 'ALL Warehouses' AS WAREHOUSE + FROM (SELECT M_PRODUCT_CATEGORY.NAME AS CATEGORY_NAME, A.M_PRODUCT_ID, AD_COLUMN_IDENTIFIER (to_char('M_Product'),to_char(M_PRODUCT.M_PRODUCT_ID),to_char(?)) AS PRODUCT_NAME, + M_PRODUCT.VALUE AS PRODUCT_SEARCHKEY, A.QTY, C_UOM.NAME AS UOM_NAME, + CASE a.iscostcalculated + WHEN 'Y' THEN C_CURRENCY_CONVERT_PRECISION (Suma,A.c_currency_id,?,A.movementdate,NULL,A.AD_CLIENT_ID,?,'C') + ELSE NULL + END AS TOTAL_COST, A.ISCOSTCALCULATED, A.AD_CLIENT_ID, A.MOVEMENTDATE, A.C_CURRENCY_ID, A.WAREHOUSE AS WAREHOUSE + FROM M_PRODUCT_CATEGORY, + (SELECT trx.M_PRODUCT_ID, sum(trx.MOVEMENTQTY) AS QTY, sum(CASE WHEN trx.MOVEMENTQTY < 0 THEN- tc.trxcost ELSE tc.trxcost END) AS Suma, + trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate) as movementdate, WH.NAME AS WAREHOUSE + FROM M_TRANSACTION trx + JOIN M_LOCATOR l ON trx.M_LOCATOR_ID = l.M_LOCATOR_ID + INNER JOIN M_WAREHOUSE WH ON l.M_WAREHOUSE_ID = WH.M_WAREHOUSE_ID + LEFT JOIN M_INOUTLINE iol ON trx.M_INOUTLINE_ID = iol.M_INOUTLINE_ID + LEFT JOIN M_INOUT io ON iol.M_INOUT_ID = io.M_INOUT_ID + LEFT JOIN (SELECT sum(cost) AS trxcost, m_transaction_id, c_currency_id + FROM M_TRANSACTION_COST + WHERE costdate < to_date(?) + GROUP BY m_transaction_id, c_currency_id) tc ON trx.m_transaction_id = tc.m_transaction_id + WHERE trx.MOVEMENTDATE < to_date(?) + AND trx.ad_org_id = ? + AND 0=0 + GROUP BY trx.M_PRODUCT_ID, trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(io.dateacct,trx.movementdate),WH.NAME) A, + C_UOM, + M_PRODUCT + WHERE A.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID + AND A.C_UOM_ID = C_UOM.C_UOM_ID + AND M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID + AND 1 = 1 + AND (A.QTY <> 0 OR A.Suma <> 0)) ZZ + GROUP BY ZZ.M_PRODUCT_ID, CATEGORY_NAME, PRODUCT_NAME, PRODUCT_SEARCHKEY, UOM_NAME, ISCOSTCALCULATED + HAVING SUM(QTY) <> 0 + ORDER BY WAREHOUSE, CATEGORY_NAME, PRODUCT_NAME + ]]></Sql> + <Field name="rownum" value="count"/> + <Parameter name="adLanguage"/> + <Parameter name="cCurrencyConv"/> + <Parameter name="legalEntity"/> + <Parameter name="datePlus"/> + <Parameter name="datePlus"/> + <Parameter name="organization"/> + <Parameter name="warehouse" optional="true" after="AND 0=0"><![CDATA[ AND l.M_WAREHOUSE_ID = ? ]]></Parameter> + <Parameter name="categoryProduct" optional="true" after="AND 1 = 1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_CATEGORY_ID= ? ]]></Parameter> + </SqlMethod> + <SqlMethod name="set" type="constant" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql></Sql> </SqlMethod> + <SqlMethod name="getCostingMigrationDate" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql> @@ -150,4 +288,21 @@ <Parameter name="adClient"/> <Parameter name="adClient"/> </SqlMethod> + + <SqlMethod name="selectWhsDouble" type="preparedStatement" return="multiple"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + SELECT AD_ORG_ID AS PADRE, M_WAREHOUSE_ID AS ID, NAME + FROM M_WAREHOUSE + WHERE ad_client_id = ? + ]]> + </Sql> + <Parameter name="adClient"/> + </SqlMethod> + </SqlClass> + + + + diff --git a/src/org/openbravo/warehouse/reportvaluedstockenhanced/SumaryProductCategory.java b/src/org/openbravo/warehouse/reportvaluedstockenhanced/SumaryProductCategory.java new file mode 100644 --- /dev/null +++ b/src/org/openbravo/warehouse/reportvaluedstockenhanced/SumaryProductCategory.java @@ -0,0 +1,43 @@ +/* +************************************************************************* +* Copyright (C) 2015 Openbravo S.L.U. +* Licensed under the Openbravo Commercial License version 1.0 +* You may obtain a copy of the License at http://www.openbravo.com/legal/obcl.html +* or in the legal folder of this module distribution. +************************************************************************ +*/ + +package org.openbravo.warehouse.reportvaluedstockenhanced; + +import org.apache.log4j.Logger; +import org.openbravo.data.FieldProvider; + +public class SumaryProductCategory implements FieldProvider { + static Logger log4j = Logger.getLogger(SumaryProductCategory.class); + + public String category; + public String cost; + + public SumaryProductCategory() { + super(); + } + + public SumaryProductCategory(String category, String cost) { + super(); + this.category = category; + this.cost = cost; + } + + @Override + public String getField(String fieldName) { + if (fieldName.equalsIgnoreCase("category")) + return category; + else if (fieldName.equalsIgnoreCase("cost")) + return cost; + else { + log4j.debug("Field does not exist: " + fieldName); + return null; + } + } + +} diff --git a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedExcel.jrxml b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedExcel.jrxml new file mode 100644 --- /dev/null +++ b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedExcel.jrxml @@ -0,0 +1,231 @@ +<?xml version="1.0" encoding="UTF-8"?> +<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="ReportValuedStockEnhancedExcel" printOrder="Horizontal" pageWidth="1300" pageHeight="595" orientation="Landscape" columnWidth="1300" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0"> + <property name="ireport.zoom" value="1.8150000000000042"/> + <property name="ireport.x" value="932"/> + <property name="ireport.y" value="0"/> + <style name="Title" forecolor="#FFFFFF" fontName="Times New Roman" fontSize="50" isBold="false"/> + <style name="SubTitle" forecolor="#CCCCCC" fontName="Times New Roman" fontSize="18" isBold="false"/> + <style name="Column header" forecolor="#FFFFFF" backcolor="#808080" fontName="Times New Roman" fontSize="14" isBold="true"/> + <style name="Detail" mode="Transparent" fontName="Times New Roman"/> + <style name="Row" mode="Transparent" fontName="Times New Roman"> + <conditionalStyle> + <conditionExpression><![CDATA[$V{REPORT_COUNT}%2 == 0]]></conditionExpression> + <style mode="Opaque" backcolor="#F0EFEF"/> + </conditionalStyle> + </style> + <parameter name="ALG_COST" class="java.lang.String"> + <defaultValueExpression><![CDATA["B843C30461EA4501935CB1D125C9C25A"]]></defaultValueExpression> + </parameter> + <parameter name="SUM_ALG_COSTO" class="java.lang.String"/> + <queryString> + <![CDATA[SELECT + m_warehouse."name" AS WAREHOUSE, + m_product_category."name" AS CATEGORY_NAME, + m_product."name" AS PRODUCT_NAME, + m_product."value" AS PRODUCT_SEARCHKEY, + m_transaction."transactioncost" AS QTY, + m_product."name" AS UOM_NAME, + m_transaction."transactioncost" AS AVERAGE_COST, + m_transaction."transactioncost" AS TOTAL_COST, + m_transaction."transactioncost" AS COST_TYPE, + m_transaction."transactioncost" AS VALUATION_TYPE, + m_product."m_product_id" AS M_PRODUCT_ID +FROM + "public"."m_product" m_product RIGHT OUTER JOIN "public"."m_transaction" m_transaction ON m_product."m_product_id" = m_transaction."m_product_id" + LEFT OUTER JOIN "public"."m_locator" m_locator ON m_product."m_locator_id" = m_locator."m_locator_id" + AND m_locator."m_locator_id" = m_transaction."m_locator_id" + LEFT OUTER JOIN "public"."m_warehouse" m_warehouse ON m_locator."m_warehouse_id" = m_warehouse."m_warehouse_id" + AND m_locator."m_locator_id" = m_warehouse."m_returnlocator_id" + LEFT OUTER JOIN "public"."m_product_category" m_product_category ON m_product."m_product_category_id" = m_product_category."m_product_category_id" +ORDER BY + WAREHOUSE ASC, + CATEGORY_NAME ASC, + PRODUCT_NAME ASC]]> + </queryString> + <field name="warehouse" class="java.lang.String"/> + <field name="category_name" class="java.lang.String"/> + <field name="product_name" class="java.lang.String"/> + <field name="product_searchkey" class="java.lang.String"/> + <field name="qty" class="java.math.BigDecimal"/> + <field name="uom_name" class="java.lang.String"/> + <field name="average_cost" class="java.math.BigDecimal"/> + <field name="total_cost" class="java.math.BigDecimal"/> + <field name="cost_type" class="java.math.BigDecimal"/> + <field name="valuation_type" class="java.math.BigDecimal"/> + <field name="m_product_id" class="java.lang.String"/> + <background> + <band splitType="Stretch"/> + </background> + <title> + <band splitType="Stretch"/> + </title> + <pageHeader> + <band splitType="Stretch"/> + </pageHeader> + <columnHeader> + <band height="45" splitType="Stretch"> + <staticText> + <reportElement style="Column header" mode="Opaque" x="195" y="25" width="421" height="20" forecolor="#FFFFFF"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[PRODUCT]]></text> + </staticText> + <staticText> + <reportElement style="Column header" mode="Opaque" x="808" y="25" width="71" height="20" forecolor="#FFFFFF"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[QUANTITY]]></text> + </staticText> + <staticText> + <reportElement style="Column header" mode="Opaque" x="879" y="25" width="56" height="20" forecolor="#FFFFFF"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[UNIT]]></text> + </staticText> + <staticText> + <reportElement style="Column header" mode="Opaque" x="935" y="25" width="74" height="20" forecolor="#FFFFFF"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[UNIT +COST]]></text> + </staticText> + <staticText> + <reportElement style="Column header" mode="Opaque" x="1009" y="25" width="86" height="20" forecolor="#FFFFFF"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[VALUATION]]></text> + </staticText> + <staticText> + <reportElement style="Column header" mode="Opaque" x="0" y="25" width="97" height="20" forecolor="#FFFFFF"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[WAREHOUSE]]></text> + </staticText> + <staticText> + <reportElement style="Column header" mode="Opaque" x="97" y="25" width="98" height="20" forecolor="#FFFFFF"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[PRODUCT +CATEGORY]]></text> + </staticText> + <staticText> + <reportElement x="0" y="0" width="1300" height="25"/> + <textElement> + <font fontName="Bitstream Vera Sans" size="18"/> + </textElement> + <text><![CDATA[Enhanced Value Stock Report]]></text> + </staticText> + <textField isBlankWhenNull="true"> + <reportElement mode="Opaque" x="1095" y="25" width="108" height="20" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$P{ALG_COST}]]></textFieldExpression> + </textField> + <textField isBlankWhenNull="true"> + <reportElement mode="Opaque" x="1203" y="25" width="95" height="20" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$P{SUM_ALG_COSTO}]]></textFieldExpression> + </textField> + <staticText> + <reportElement style="Column header" mode="Opaque" x="616" y="25" width="192" height="20" forecolor="#FFFFFF"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[SEARCHKEY]]></text> + </staticText> + </band> + </columnHeader> + <detail> + <band height="13" splitType="Stretch"> + <textField isStretchWithOverflow="true" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="196" y="0" width="420" height="13"/> + <textElement> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$F{product_name}]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="808" y="0" width="71" height="13"/> + <textElement> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{qty}]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="879" y="0" width="56" height="13"/> + <textElement> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$F{uom_name}]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="935" y="0" width="74" height="13"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{average_cost}]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="1009" y="0" width="86" height="13"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{total_cost}]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="1095" y="0" width="108" height="13"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{cost_type}]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="1203" y="0" width="95" height="13"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{valuation_type}]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" isBlankWhenNull="true"> + <reportElement x="0" y="0" width="97" height="13"/> + <textElement> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$F{warehouse}]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" isBlankWhenNull="true"> + <reportElement x="97" y="0" width="98" height="13"/> + <textElement> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$F{category_name}]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="616" y="0" width="192" height="13"/> + <textElement> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$F{product_searchkey}]]></textFieldExpression> + </textField> + </band> + </detail> + <columnFooter> + <band splitType="Stretch"/> + </columnFooter> + <pageFooter> + <band splitType="Stretch"/> + </pageFooter> + <summary> + <band splitType="Stretch"/> + </summary> +</jasperReport> diff --git a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedPDF.jrxml b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedPDF.jrxml new file mode 100644 --- /dev/null +++ b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedPDF.jrxml @@ -0,0 +1,400 @@ +<?xml version="1.0" encoding="UTF-8"?> +<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="ReportValuedStockEnhancedPDF" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"> + <property name="ireport.zoom" value="1.5"/> + <property name="ireport.x" value="0"/> + <property name="ireport.y" value="0"/> + <style name="Title" forecolor="#FFFFFF" fontName="Times New Roman" fontSize="50" isBold="false"/> + <style name="SubTitle" forecolor="#CCCCCC" fontName="Times New Roman" fontSize="18" isBold="false"/> + <style name="Column header" forecolor="#666666" fontName="Times New Roman" fontSize="14" isBold="true"/> + <style name="Detail" mode="Transparent" fontName="Times New Roman"/> + <style name="Row" mode="Transparent" fontName="Times New Roman"> + <conditionalStyle> + <conditionExpression><![CDATA[$V{REPORT_COUNT}%2 == 0]]></conditionExpression> + <style mode="Opaque" backcolor="#F0EFEF"/> + </conditionalStyle> + </style> + <style name="table"> + <box> + <pen lineWidth="1.0" lineColor="#000000"/> + </box> + </style> + <style name="table_TH" mode="Opaque" backcolor="#000000"> + <box> + <topPen lineWidth="0.5" lineColor="#000000"/> + <bottomPen lineWidth="0.5" lineColor="#000000"/> + </box> + </style> + <style name="table_CH" mode="Opaque" backcolor="#FFE6E6"> + <box> + <topPen lineWidth="0.5" lineColor="#000000"/> + <bottomPen lineWidth="0.5" lineColor="#000000"/> + </box> + </style> + <style name="table_TD" mode="Opaque" backcolor="#FFFFFF"> + <box> + <topPen lineWidth="0.5" lineColor="#000000"/> + <bottomPen lineWidth="0.5" lineColor="#000000"/> + </box> + <conditionalStyle> + <conditionExpression><![CDATA[new Boolean($V{REPORT_COUNT}.intValue()%2==0)]]></conditionExpression> + <style backcolor="#FFF8F8"/> + </conditionalStyle> + </style> + <subDataset name="Table Dataset 1"/> + <parameter name="SUM_ALG_COST" class="java.lang.String"> + <defaultValueExpression><![CDATA["B843C30461EA4501935CB1D125C9C25A"]]></defaultValueExpression> + </parameter> + <parameter name="ALG_COST" class="java.lang.String"/> + <parameter name="DATE" class="java.lang.String"/> + <parameter name="NUMBERFORMAT" class="java.text.DecimalFormat" isForPrompting="false"/> + <queryString> + <![CDATA[SELECT + m_warehouse."name" AS WAREHOUSE, + m_product_category."name" AS CATEGORY_NAME, + m_product."name" AS PRODUCT_NAME, + m_product."value" AS PRODUCT_SEARCHKEY, + m_transaction."transactioncost" AS QTY, + m_product."name" AS UOM_NAME, + m_transaction."transactioncost" AS COST_TYPE, + m_transaction."transactioncost" AS AVERAGE_COST, + m_transaction."transactioncost" AS TOTAL_COST, + m_transaction."transactioncost" AS VALUATION_TYPE, + m_product."m_product_id" AS M_PRODUCT_ID +FROM + "public"."m_product" m_product RIGHT OUTER JOIN "public"."m_transaction" m_transaction ON m_product."m_product_id" = m_transaction."m_product_id" + LEFT OUTER JOIN "public"."m_locator" m_locator ON m_product."m_locator_id" = m_locator."m_locator_id" + AND m_locator."m_locator_id" = m_transaction."m_locator_id" + LEFT OUTER JOIN "public"."m_warehouse" m_warehouse ON m_locator."m_warehouse_id" = m_warehouse."m_warehouse_id" + AND m_locator."m_locator_id" = m_warehouse."m_returnlocator_id" + LEFT OUTER JOIN "public"."m_product_category" m_product_category ON m_product."m_product_category_id" = m_product_category."m_product_category_id" +ORDER BY + WAREHOUSE ASC, + CATEGORY_NAME ASC, + PRODUCT_NAME ASC]]> + </queryString> + <field name="warehouse" class="java.lang.String"/> + <field name="category_name" class="java.lang.String"/> + <field name="product_name" class="java.lang.String"/> + <field name="product_searchkey" class="java.lang.String"/> + <field name="qty" class="java.math.BigDecimal"/> + <field name="uom_name" class="java.lang.String"/> + <field name="cost_type" class="java.math.BigDecimal"/> + <field name="average_cost" class="java.math.BigDecimal"/> + <field name="total_cost" class="java.math.BigDecimal"/> + <field name="valuation_type" class="java.math.BigDecimal"/> + <field name="m_product_id" class="java.lang.String"/> + <variable name="sum_unit_cost" class="java.math.BigDecimal" resetType="Group" resetGroup="m_product_category_name" calculation="Sum"> + <variableExpression><![CDATA[$F{average_cost}]]></variableExpression> + </variable> + <variable name="sum_total_cost" class="java.math.BigDecimal" resetType="Group" resetGroup="m_product_category_name" calculation="Sum"> + <variableExpression><![CDATA[$F{total_cost}]]></variableExpression> + </variable> + <variable name="sum_cost_type" class="java.math.BigDecimal" resetType="Group" resetGroup="m_product_category_name" calculation="Sum"> + <variableExpression><![CDATA[$F{cost_type}]]></variableExpression> + </variable> + <variable name="sum_valuation_type" class="java.math.BigDecimal" resetType="Group" resetGroup="m_product_category_name" calculation="Sum"> + <variableExpression><![CDATA[$F{valuation_type}]]></variableExpression> + </variable> + <variable name="wh_sum_unit_cost" class="java.math.BigDecimal" resetType="Group" resetGroup="m_warehouse_name" calculation="Sum"> + <variableExpression><![CDATA[$F{average_cost}]]></variableExpression> + </variable> + <variable name="wh_sum_total_cost" class="java.math.BigDecimal" resetType="Group" resetGroup="m_warehouse_name" calculation="Sum"> + <variableExpression><![CDATA[$F{total_cost}]]></variableExpression> + </variable> + <variable name="wh_sum_cost_type" class="java.math.BigDecimal" resetType="Group" resetGroup="m_warehouse_name" calculation="Sum"> + <variableExpression><![CDATA[$F{cost_type}]]></variableExpression> + </variable> + <variable name="wh_sum_valuation_type" class="java.math.BigDecimal" resetType="Group" resetGroup="m_warehouse_name" calculation="Sum"> + <variableExpression><![CDATA[$F{valuation_type}]]></variableExpression> + </variable> + <group name="m_warehouse_name"> + <groupExpression><![CDATA[$F{warehouse}]]></groupExpression> + <groupHeader> + <band height="15" splitType="Stretch"> + <frame> + <reportElement mode="Opaque" x="0" y="0" width="802" height="15" forecolor="#B89F7D" backcolor="#000000"/> + <textField isStretchWithOverflow="true"> + <reportElement style="SubTitle" isPrintRepeatedValues="false" x="2" y="0" width="321" height="15" forecolor="#FFFFFF"/> + <textElement> + <font fontName="Bitstream Vera Sans" size="12" isBold="false"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$F{warehouse}]]></textFieldExpression> + </textField> + </frame> + </band> + </groupHeader> + <groupFooter> + <band height="16" splitType="Stretch"> + <staticText> + <reportElement mode="Opaque" x="0" y="0" width="365" height="14" backcolor="#000000"/> + <textElement/> + <text><![CDATA[]]></text> + </staticText> + <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement mode="Opaque" x="365" y="0" width="92" height="14" forecolor="#FFFFFF" backcolor="#000000"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="10" isBold="false"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($V{wh_sum_unit_cost}!=null)?$P{NUMBERFORMAT}.format($V{wh_sum_unit_cost}).toString():new String(" ")]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement mode="Opaque" x="457" y="0" width="92" height="14" forecolor="#FFFFFF" backcolor="#000000"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="10" isBold="false"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($V{wh_sum_total_cost}!=null)?$P{NUMBERFORMAT}.format($V{wh_sum_total_cost}).toString():new String(" ")]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement mode="Opaque" x="665" y="0" width="137" height="14" forecolor="#FFFFFF" backcolor="#000000"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="10" isBold="false"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($V{wh_sum_valuation_type}!=null)?$P{NUMBERFORMAT}.format($V{wh_sum_valuation_type}).toString():new String(" ")]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement mode="Opaque" x="549" y="0" width="116" height="14" forecolor="#FFFFFF" backcolor="#000000"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="10" isBold="false"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($V{wh_sum_cost_type}!=null)?$P{NUMBERFORMAT}.format($V{wh_sum_cost_type}).toString():new String(" ")]]></textFieldExpression> + </textField> + </band> + </groupFooter> + </group> + <group name="m_product_category_name"> + <groupExpression><![CDATA[$F{category_name}]]></groupExpression> + <groupHeader> + <band height="14" splitType="Stretch"> + <textField> + <reportElement positionType="Float" mode="Opaque" x="0" y="0" width="802" height="14" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Left"> + <font fontName="Bitstream Vera Sans" size="10" isBold="false"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$F{category_name}]]></textFieldExpression> + </textField> + </band> + </groupHeader> + <groupFooter> + <band height="16" splitType="Stretch"> + <textField pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement mode="Opaque" x="641" y="0" width="161" height="14" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="10" isBold="false"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($V{sum_valuation_type}!=null)?$P{NUMBERFORMAT}.format($V{sum_valuation_type}).toString():new String(" ")]]></textFieldExpression> + </textField> + <textField pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement mode="Opaque" x="549" y="0" width="116" height="14" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="10" isBold="false"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($V{sum_cost_type}!=null)?$P{NUMBERFORMAT}.format($V{sum_cost_type}).toString():new String(" ")]]></textFieldExpression> + </textField> + <textField pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement mode="Opaque" x="457" y="0" width="92" height="14" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="10" isBold="false"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($V{sum_total_cost}!=null)?$P{NUMBERFORMAT}.format($V{sum_total_cost}).toString():new String(" ")]]></textFieldExpression> + </textField> + <textField pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement mode="Opaque" x="365" y="0" width="92" height="14" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="10" isBold="false"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($V{sum_unit_cost}!=null)?$P{NUMBERFORMAT}.format($V{sum_unit_cost}).toString():new String(" ")]]></textFieldExpression> + </textField> + <staticText> + <reportElement mode="Opaque" x="0" y="0" width="365" height="14" backcolor="#808080"/> + <textElement/> + <text><![CDATA[]]></text> + </staticText> + </band> + </groupFooter> + </group> + <background> + <band splitType="Stretch"/> + </background> + <title> + <band height="40" splitType="Stretch"> + <staticText> + <reportElement x="0" y="0" width="802" height="20"/> + <textElement textAlignment="Center"> + <font fontName="Bitstream Vera Sans" size="15"/> + </textElement> + <text><![CDATA[ENHANCED VALUE STOCK REPORT]]></text> + </staticText> + <textField> + <reportElement x="37" y="20" width="207" height="20"/> + <textElement> + <font fontName="Bitstream Vera Sans"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$P{DATE}]]></textFieldExpression> + </textField> + <staticText> + <reportElement x="2" y="20" width="35" height="20"/> + <textElement> + <font fontName="Bitstream Vera Sans"/> + </textElement> + <text><![CDATA[Date :]]></text> + </staticText> + </band> + </title> + <pageHeader> + <band splitType="Stretch"/> + </pageHeader> + <columnHeader> + <band height="18" splitType="Stretch"> + <staticText> + <reportElement style="Column header" mode="Opaque" x="0" y="0" width="142" height="18" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[PRODUCT]]></text> + </staticText> + <staticText> + <reportElement style="Column header" mode="Opaque" x="206" y="0" width="67" height="18" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[QUANTITY]]></text> + </staticText> + <staticText> + <reportElement style="Column header" mode="Opaque" x="273" y="0" width="92" height="18" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[UNIT]]></text> + </staticText> + <staticText> + <reportElement style="Column header" mode="Opaque" x="365" y="0" width="92" height="18" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[UNIT COST]]></text> + </staticText> + <staticText> + <reportElement style="Column header" mode="Opaque" x="457" y="0" width="92" height="18" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[VALUATION]]></text> + </staticText> + <textField> + <reportElement mode="Opaque" x="665" y="0" width="137" height="18" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$P{SUM_ALG_COST}]]></textFieldExpression> + </textField> + <textField> + <reportElement mode="Opaque" x="549" y="0" width="116" height="18" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$P{ALG_COST}]]></textFieldExpression> + </textField> + <staticText> + <reportElement style="Column header" mode="Opaque" x="142" y="0" width="64" height="18" forecolor="#FFFFFF" backcolor="#808080"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <text><![CDATA[SEARCHKEY]]></text> + </staticText> + </band> + </columnHeader> + <detail> + <band height="11" splitType="Stretch"> + <textField isStretchWithOverflow="true" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" isPrintRepeatedValues="false" x="0" y="0" width="142" height="11"/> + <textElement> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$F{product_name}]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="206" y="0" width="67" height="11"/> + <textElement> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($F{qty}!=null)?$P{NUMBERFORMAT}.format($F{qty}).toString():new String(" ")]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="273" y="0" width="92" height="11"/> + <textElement> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$F{uom_name}]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="365" y="0" width="92" height="11"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($F{average_cost}!=null)?$P{NUMBERFORMAT}.format($F{average_cost}).toString():new String(" ")]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="457" y="0" width="92" height="11"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($F{total_cost}!=null)?$P{NUMBERFORMAT}.format($F{total_cost}).toString():new String(" ")]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="549" y="0" width="116" height="11"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($F{cost_type}!=null)?$P{NUMBERFORMAT}.format($F{cost_type}).toString():new String(" ")]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="665" y="0" width="137" height="11"/> + <textElement textAlignment="Right"> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[($F{valuation_type}!=null)?$P{NUMBERFORMAT}.format($F{valuation_type}).toString():new String(" ")]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" isBlankWhenNull="true"> + <reportElement style="Detail" positionType="Float" x="143" y="0" width="62" height="11"/> + <textElement> + <font fontName="Bitstream Vera Sans" size="8"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$F{product_searchkey}]]></textFieldExpression> + </textField> + </band> + </detail> + <columnFooter> + <band splitType="Stretch"/> + </columnFooter> + <pageFooter> + <band height="25" splitType="Stretch"> + <frame> + <reportElement mode="Opaque" x="0" y="1" width="802" height="24" forecolor="#D0B48E" backcolor="#000000"/> + <textField isStretchWithOverflow="true" evaluationTime="Report"> + <reportElement style="Column header" x="762" y="0" width="40" height="20" forecolor="#FFFFFF"/> + <textElement verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true"> + <reportElement style="Column header" x="682" y="0" width="80" height="20" forecolor="#FFFFFF"/> + <textElement textAlignment="Right" verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="EEEEE dd MMMMM yyyy"> + <reportElement style="Column header" x="2" y="1" width="197" height="20" forecolor="#FFFFFF"/> + <textElement verticalAlignment="Middle"> + <font fontName="Bitstream Vera Sans" size="8" isBold="false"/> + </textElement> + <textFieldExpression class="java.util.Date"><![CDATA[new java.util.Date()]]></textFieldExpression> + </textField> + </frame> + </band> + </pageFooter> + <summary> + <band height="10" splitType="Stretch"/> + </summary> +</jasperReport> diff --git a/src/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/SumaryProductCategory.jrxml b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/SumaryProductCategory.jrxml new file mode 100644 --- /dev/null +++ b/src/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/SumaryProductCategory.jrxml @@ -0,0 +1,67 @@ +<?xml version="1.0" encoding="UTF-8"?> +<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="SumaryProductCategory" printOrder="Horizontal" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"> + <property name="ireport.zoom" value="1.0"/> + <property name="ireport.x" value="0"/> + <property name="ireport.y" value="0"/> + <parameter name="NUMBERFORMAT" class="java.text.DecimalFormat" isForPrompting="false"/> + <queryString> + <![CDATA[select name as category, 0 as cost from m_product_category]]> + </queryString> + <field name="category" class="java.lang.String"/> + <field name="cost" class="java.math.BigDecimal"/> + <background> + <band splitType="Stretch"/> + </background> + <title> + <band height="30" splitType="Stretch"> + <staticText> + <reportElement x="2" y="0" width="800" height="30"/> + <textElement textAlignment="Center"> + <font size="16"/> + </textElement> + <text><![CDATA[Summary : Enhanced Valued Stock Report to Product Category]]></text> + </staticText> + </band> + </title> + <columnHeader> + <band height="25" splitType="Stretch"> + <staticText> + <reportElement mode="Opaque" x="2" y="0" width="542" height="25" forecolor="#FFFFFF" backcolor="#000000"/> + <textElement> + <font size="14"/> + </textElement> + <text><![CDATA[ Product Category]]></text> + </staticText> + <staticText> + <reportElement mode="Opaque" x="526" y="0" width="276" height="25" forecolor="#FFFFFF" backcolor="#000000"/> + <textElement> + <font size="14"/> + </textElement> + <text><![CDATA[Total Cost]]></text> + </staticText> + </band> + </columnHeader> + <detail> + <band height="20" splitType="Stretch"> + <textField> + <reportElement x="122" y="0" width="366" height="20"/> + <textElement/> + <textFieldExpression class="java.lang.String"><![CDATA[$F{category}]]></textFieldExpression> + </textField> + <textField pattern="#,##0.00" isBlankWhenNull="true"> + <reportElement x="587" y="0" width="100" height="20"/> + <textElement textAlignment="Right"/> + <textFieldExpression class="java.lang.String"><![CDATA[($F{cost}!=null)?$P{NUMBERFORMAT}.format($F{cost}).toString():new String(" ")]]></textFieldExpression> + </textField> + </band> + </detail> + <columnFooter> + <band splitType="Stretch"/> + </columnFooter> + <pageFooter> + <band splitType="Stretch"/> + </pageFooter> + <summary> + <band splitType="Stretch"/> + </summary> +</jasperReport>

Notes
(0077792) dmiguelez (viewer) 2015-05-27 10:29	Attached a file to fix this problem: When selecting an Organization, it does not show the Warehouse from it's child Organizations, for example, by selecting US Organization, Warehouses from East Coast and West Coast should be displayed in the filter.

(0081002) hgbot (developer) 2015-10-14 11:48	Repository: erp/pmods/org.openbravo.warehouse.reportvaluedstockenhanced Changeset: b54f434791610e140b584ed43d13412b9f094ff1 Author: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> Date: Wed Oct 14 11:46:45 2015 +0200 URL: http://code.openbravo.com/erp/pmods/org.openbravo.warehouse.reportvaluedstockenhanced/rev/b54f434791610e140b584ed43d13412b9f094ff1 [^] Fixed issue 29215.Several improvements added to the report. - Added option to export results to PDF or Excel. - Added mandatory filter by Organization - Warehouse filter now optional. - Added consolidated flag to show results by warehouse or not when no warehouse is selected in the filter. - Several fixes in the queries -- getting the date to convert the cost amounts. -- performance improvements by using new core columns with precomputed data. --- M src-db/database/sourcedata/AD_MENU.xml M src-db/database/sourcedata/AD_MODEL_OBJECT_MAPPING.xml M src-db/database/sourcedata/AD_MODULE_DEPENDENCY.xml M src-db/database/sourcedata/AD_PROCESS.xml M src-db/database/sourcedata/AD_TEXTINTERFACES.xml M src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.html M src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.java M src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced.xml M src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced2.html M src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced2.xml M src/org/openbravo/warehouse/reportvaluedstockenhanced/ReportValuedStockEnhanced_data.xsql A src-db/database/sourcedata/AD_MESSAGE.xml A src-db/database/sourcedata/AD_MODULE_DBPREFIX.xml A src/org/openbravo/warehouse/reportvaluedstockenhanced/SummaryProductCategory.java A src/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedExcel.jrxml A src/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/ReportValuedStockEnhancedPDF.jrxml A src/org/openbravo/warehouse/reportvaluedstockenhanced/ad_reports/SumaryProductCategory.jrxml ---

(0081050) dmiguelez (viewer) 2015-10-16 09:04	Code Review + Testing Ok

Issue History
Date Modified	Username	Field	Change
2015-03-10 11:43	naiaramartinez	New Issue
2015-03-10 11:43	naiaramartinez	Assigned To	=> jonalegriaesarte
2015-03-10 11:43	naiaramartinez	OBNetwork customer	=> No
2015-03-10 12:26	naiaramartinez	Proposed Solution updated
2015-03-10 12:28	naiaramartinez	File Added: 29215.diff
2015-05-27 10:28	dmiguelez	File Added: ShowsWarehouseInParents.diff
2015-05-27 10:29	dmiguelez	Note Added: 0077792
2015-10-14 11:48	hgbot	Checkin
2015-10-14 11:48	hgbot	Note Added: 0081002
2015-10-14 11:48	hgbot	Status	new => resolved
2015-10-14 11:48	hgbot	Resolution	open => fixed
2015-10-14 11:48	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/pmods/org.openbravo.warehouse.reportvaluedstockenhanced/rev/b54f434791610e140b584ed43d13412b9f094ff1 [^]
2015-10-16 09:04	dmiguelez	Review Assigned To	=> dmiguelez
2015-10-16 09:04	dmiguelez	Note Added: 0081050
2015-10-16 09:04	dmiguelez	Status	resolved => closed

Relationships [ Relation Graph ] [ Dependency Graph ]